mysql的SQL基础规范与权限管理

SQL基础、规范、用户权限管理——雷彻OutlineSQL基础什么是SQL？DDL/DMLDataTypesql规范数据库设计数据库操作用户权限管理用户授权与用户信息表安全管理用户注意：本ppt涉及内容仅适合mysql，默认Innodb引擎本ppt所述仅代表个人观点WhatisSQL?SQL，StructureQueryLanguage，结构化查询语言T-SQL，Transact–SQL，MSSql对sql标准的增强PL/SQL，ProceduralLanguageSQL，Oracle对SQL的扩展OutlineSQL基础什么是SQL？DDL/DMLDataTypesql规范数据库设计数据库操作用户权限管理用户授权与用户信息表安全管理用户DDL•DDL，DataDefinitionLanguage，数据库结构定义语言描述数据库中要存储的现实实体的语言•常见的DDLcreate/alter/dropdatabase…create/alter/drop/truncate/renametable…create/alter/dropview…create/dropindex…还有trigger,event,procedure,logfile,tablespace,function•DDL需要的权限create,drop,alter,event,trigger…DML•DML，DataManipulationLanguage，数据操纵语言•常见的DMLselect/insert/delete/update/replacecall/do/load/subquery…•DML的权限select/insert/delete/updateOtherStatement•transaction&&lockstatement─常见SQL：begin/start[readonly]transaction,commit,rollback,XAtransaction…lock/unlock─权限：lock•replicationstatement─常见SQL：changemasterto…，start/stopslave，start/stop[I/O|SQL]threadsetsql_log_bin…，resetmaster，purgebinarylogs…─权限：repelication•administationstatement─常见SQL：grant/revoke/drop/alteruser，set，checksum/anlyse/repairtable……OutlineSQL基础什么是SQL？DDL/DMLDataTypesql规范数据库设计数据库操作用户权限管理用户授权与用户信息表安全管理用户DataTypeMysql的DataTypeNumeric_integer•类型TypeStorage(Bytes)MinimumValueMaximumValue(Signed/Unsigned)(Signed/Unsigned)TINYINT1-2^72^702^8SMALLINT2-2^1502^152^162^23MEDIUMINT3-2^2302^242^312^322^632^64INT4-2^310BIGINT8-2^630•哪些字段可用？IP，0~4244897025，unsignedint而不用char(15)，函数inet_aton()，inet_ntoa()手机号，unsignedbigint而不用char(11)Numeric_decimal•fixed-point─decimal(M,D)─整数最大位数（M）为65，小数位数最大（D）为30•floating-point─float(p)p<=24，4B；25<=p<=53，8B;─double8B建议使用decimal，避免float和doubleDate&&Time•date&&time─datetime格式，取值范围1000-01-0100:00:00~9999-12-3123:59:59，8B(5.6.4以前)─date格式，YYYY-MM-DD,3B─time格式，hh:mm:ss,3B─timestamp格式，函数转换，范围1970~2037年，4B─year格式，YYYY，1B•变化5.6.4以后，不再对timestamp的cunrent_timestamp类型进行限制，默认为nullso，选用timestamp，避免datetime；其余按需求；stringvarchar&&charvarchar(n)变长，表中n取值1<n<(65535-[1,2])/字符集需要1~2个节来存储长度char(n)定长存储长度相差不大，频繁变化的的类型用charvarchar(BN)&&blob&&text─varchar(BN)─blob二进制，不可排序─text文本类型，可排序，选择前缀排序OutlineSQL基础什么是SQL？DDL/DMLDataTypesql规范数据库设计数据库操作用户权限管理用户授权与用户信息表安全管理用户SQLStantard•数据库设计─预估─范式与反范式─字段规范─索引规范─其他注意点•数据库操作─拒绝3B─分页limitM,N─subquery─join─避免count(*)─其他DB_Design_estimate•目的─预估数据量，增长速度，热数据，访问量，库表大小，数量，热库热表，读写比例等（两年内）─确定存储(type,size)，内存，架构，带宽，备份，数据物理分布等等─减少后期维护成本，避免资源浪费•如何确定─根据数据量，增长量来确定磁盘大小─根据增长速度，访问量，读写比例来确定磁盘类型，raid，文件系统，架构等─根据热数据热库热表业务来确定内存大小，配置参数，数据分布─根据数据重要性，量，来确定容灾及备份策略本ppt暂不讨论硬件和配置问题。DB_Design_estimate_IdtcInstance单实例建议不要超过100G。内存制约。200G热数据一般在15~20G，内存需求18~40G备份恢复。具体根据业务来确定Database单库不超过300-400个表。单表字段20~50。DB_Design_form&anti-form•范式─范式定义，举例1NF：字段属性单一，不可再分2NF：实体的属性完全依赖于主关键字3NF：不存在非关键字段对任一候选关键字段存在传递函数依赖•反范式─违反范式设计表─通过适当增加冗余来减少多表join，降低io消耗，内存消耗等。─适用场景•涉及多表查询时，为精简程序，可以适度冗余•存在较多数据统计需求（count,sum等），效率低下DB_Design_column•规则─越小越优，定长较优，满足应用即可•举例─越小越好范围合适的整形；适当转换字符型为整形；emun或set较小时，使用tinyint替换；避免大字段blob,varchar(2000)…─精确时间类型采用精确格式，避免浪费。存储年用year；存储日期用date；存储时间精确到s，使用timestamp或者int(需要转换)。─定长较优(char与varchar)varchar(n),n<255时，不妨用char代替(快)；长度一致时，流水号采用char；DB_Design_column•使用decimal，避免使用double和float•varchar(255)与varchar(256)的不同；•大字段varchar(5000)与blob放在单表中；•自增列使用int或者bigint，标明unsigned；•避免null，字段都必须为notnulldefault‘xxx’•避免uuid；•int和int(8),int(11)有区别吗？•设置comment；DB_Design_Index•索引─idx_column1_column2_column3。普通索引，小写，可以适当缩写─udx_column1_column2_column3。唯一索引，小写，可以适当缩写•规则─最左前缀─组合索引上限5column─必须明确指定pk─长于50的varchar字段使用合适的前缀索引─选哪些字段？顺序如何？根据业务,sql来定OutlineSQL基础什么是SQL？DDL/DMLDataTypesql规范数据库设计数据库操作用户权限管理用户授权与用户信息表安全管理用户DB_Oper_refuse_3Bwhatis3B?─BigTrasaction•资源占用时间长，锁多，日志量大，影响并发，影响数据同步例如:updatetable_Asetcol_a=‘’wherecol_b>‘b’─BigSQL•资源占用(filesort，group，join，subquery)大，耗时长例如:selectcol_afromtable_Awhereexists(selectidfromtable_Bwhereid>’xxx’)orderbycol_blimit10;─BigBatch•并发高，资源紧张例如:公司批量I/D/U一些数据DB_Oper_refuse_3BHowtodeal？─BigTrasaction─拆小事务。拆分标准：pk最佳，单行数据事务，多行数据事务均可─BigSQL─sql优化，拆分，表拆分，加冗余，程序修改等─BigBatch─拆成小批量，加间隔等，建议分1W条一次，具体和sql效率有关DB_Oper_limit_M_N•limitm,n如何运行？−先遍历前m+n行数据，对结果进行排序，再读取(m+1,m+n)区间的数据−M值越大，查询越慢Select*fromtable_Alimitm,n;•limitm,n如何优化？−拿到第m行的id,按排序顺序取后n行即可连续id：Select*fromtable_Awhereid>=mlimitn;非连续id：select*fromtable_Awhereid>=(selectidfromtable_Alimitm,1)limitn;select*fromtable_Ainnerjoin(selectidfromtable_Alimitm,n)using(id);DB_Oper_subquery•subquery−独立子查询Selecta,b,cfromtable_Awheredin(selectefromtable_B);−相关子查询Selecta,b,cfromtable_Awhereexists(select1fromtable_Bwheretable_B.e=table_A.d)•subquery的优化−避免子查询，必要时候在程序中拆分成单句执行−独立子查询相关子查询||joinDB_Oper_join•Leftjoin,rightjoin•Innerjoin(内联或等值链接)Selecta.col1,b.col2fromtable_AaJointable_Bbona.col3=b.col4Selecta.col1,b.col2fromtable_Aa,table_Bbwherea.col3=b.col4•执行顺序ForeachrinRstoreusedcolumnaspintojoinbufferforeachsinSifs=routput<p,s>DB_Oper_join•如何优化−调整关联顺序，小表在前，大表在后−适当添加索引，内表SDB_Oper_count(*)•count(*)如何进行？−将数据从外存读入内存，计数−避免或在无访问的从库进行•其他count类型的运行−count(*)，count(pk)，count(unique)，count(1)−速度：count(*)~count(1)>count(unique)~count(pk)DB_Oper_other尽量使用pk或者uniquekey进行update,delete操作避免isnull或isnotnull减少不等值查询避免使用*,选择需要字段进行查询Where子句中,阈值较多的col放在前面如非去重,使用unionall代替union避免使用in合理利用覆盖索引扫描避免完全模糊匹配…OutlineSQL基础什么是SQL？DDL/DMLDataTypesql规范数据库设计数据库操作用户权限管理用户授权与用户信息表安全管理用户DB_Oper_privileges•线上库对程序员开放的访问权限─只读(S，dbname_r)、读写(S/I/D/U，dbname_w)•测试站点─根据需求开启ddl权限(create,alter)•其他用户─复制replicationslave─root权限用户DB_Oper_privileges•相关表mysql.user对实例的权限，包含passwordmysql.db对库的操作权限，包含db•授权&&回收权限&&回收用户grantselectondbname.[tablename|view]touser@’host’