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

SQL基础、规范、用户权限管理雷彻 OutlineSQL基础什么是SQL？DDL/DML Data Type sql规范数据库设计数据库操作用户权限管理用户授权与用户信息表安全管理用户注意：本ppt涉及内容仅适合mysql，默认Innodb引擎 本ppt所述仅代表个人观点 What is SQL?SQL，Structure Query Language，结构化查询语言T-SQL，Transact SQL，MSSql对sql标准的增强PL/SQL，Procedural Language SQL， Oracle对SQL的扩展 OutlineSQL基础什么是SQL？DDL/DML Data Type sql规范数据库设计数据库操作用户权限管理用户授权与用户信息表安全管理用户 DDLDDL，Data Definition Language，数据库结构定义语言描述数据库中要存储的现实实体的语言常见的DDLcreate/alter/drop database create/alter/drop/truncate/rename table create/alter/drop view create/drop index 还有trigger,event,procedure,logfile,tablespace,function DDL需要的权限create,drop,alter,event,trigger DMLDML，Data Manipulation Language，数据操纵语言常见的DMLselect/insert/delete/update/replacecall/do/load/subqueryDML的权限select/insert/delete/update Other Statementtransaction&lock statement常见SQL： begin/start read only transaction,commit,rollback,XA transaction lock/unlock 权限：lock replication statement常见SQL： change master to，start/stop slave，start/stop I/O|SQL thread set sql_log_bin，reset master，purge binary logs权限：repelication administation statement常见SQL：grant/revoke/drop/alter user，set，checksum/anlyse/repair table OutlineSQL基础什么是SQL？DDL/DML Data Type sql规范数据库设计数据库操作用户权限管理用户授权与用户信息表安全管理用户 Data TypeMysql的 Data Type Numeric_integer类型TypeStorage(Bytes)Minimum ValueMaximum Value(Signed/Unsigned)(Signed/Unsigned)TINYINT1-2727028SMALLINT2-2150215216223MEDIUMINT3-2230224231232263264INT4-2310BIGINT8-2630哪些字段可用？IP，04244897025，unsigned int而不用char(15)，函数inet_aton()，inet_ntoa() 手机号，unsigned bigint而不用char(11) Numeric_decimalfixed-point decimal(M,D) 整数最大位数（M）为65，小数位数最大（D）为30 floating-point float(p) p=24，4B；25=p=53，8B; double 8B 建议使用decimal，避免float和double Date&Timedate&time datetime 格式，取值范围1000-01-01 00:00:009999-12-31 23:59:59， 8B (5.6.4以前) date 格式，YYYY-MM-DD,3B time 格式，hh:mm:ss,3B timestamp 格式，函数转换，范围19702037年，4B year 格式，YYYY，1B 变化5.6.4以后，不再对timestamp的cunrent_timestamp类型进行限制，默认为null so，选用timestamp，避免datetime；其余按需求； stringvarchar&charvarchar(n) 变长，表中n取值1n(65535-1,2)/字符集需要12个节来存储长度char(n) 定长存储长度相差不大，频繁变化的的类型用char varchar(BN) & blob & text varchar(BN) blob二进制，不可排序 text文本类型，可排序，选择前缀排序 OutlineSQL基础什么是SQL？DDL/DML Data Type sql规范数据库设计数据库操作用户权限管理用户授权与用户信息表安全管理用户 SQL Stantard数据库设计预估范式与反范式字段规范索引规范其他注意点数据库操作拒绝3B 分页limit M,N subquery join 避免count(*) 其他 DB_Design_estimate目的预估数据量，增长速度，热数据，访问量，库表大小，数量，热 库热表，读写比例等（两年内）确定存储(type,size)，内存，架构，带宽，备份，数据物理分布等等减少后期维护成本，避免资源浪费如何确定根据数据量，增长量来确定磁盘大小根据增长速度，访问量，读写比例来确定磁盘类型，raid，文件系统，架构等根据热数据热库热表业务来确定内存大小，配置参数，数据分布根据数据重要性，量，来确定容灾及备份策略本ppt暂不讨论硬件和配置问题。 DB_Design_estimate_IdtcInstance单实例建议不要超过100G。内存制约。200G热数据一般在1520G，内存需求1840G 备份恢复。具体根据业务来确定Database单库不超过300-400个表。单表字段2050。 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),nb Big SQL资源占用(filesort，group，join，subquery)大，耗时长例如:select col_a from table_A where exists (select id from table_B where idxxx) order by col_b limit 10; Big Batch并发高，资源紧张例如:公司批量I/D/U一些数据 DB_Oper_ refuse_3BHow to deal？ Big Trasaction 拆小事务。拆分标准：pk最佳，单行数据事务，多行数据事务均可 Big SQL sql优化，拆分，表拆分，加冗余，程序修改等 Big Batch 拆成小批量，加间隔等，建议分1W条一次，具体和sql效率有关 DB_Oper_ limit_M_Nlimit m,n如何运行？先遍历前m+n行数据，对结果进行排序，再读取(m+1,m+n)区间的数据 M值越大，查询越慢Select * from table_A limit m,n; limit m,n如何优化？拿到第m行的id,按排序顺序取后n行即可连续id：Select * from table_A where id=m limit n; 非连续id：select * from table_A where id=(select id from table_A limit m,1) limit n; select * from table_A inner join (select id from table_A limit m,n) using (id); DB_Oper_ subquerysubquery独立子查询Select a,b,c from table_A where d in (select e from table_B); 相关子查询Select a,b,c from table_A where exists (select 1 from table_B where table_B.e=table_A.d) subquery的优化避免子查询，必要时候在程序中拆分成单句执行独立子查询相关子查询 | join DB_Oper_ join Left join,right join Inner join(内联或等值链接)Select a.col1,b.col2 from table_A a Join table_B b on a.col3=b.col4 Select a.col1,b.col2 from table_A a,table_B b where a.col3=b.col4 执行顺序For each r in Rstore used column as p into join bufferfor each s in Sif s=routput DB_Oper_ join如何优化调整关联顺序，小表在前，大表在后适当添加索引，内表S DB_Oper_ count(*)count(*)如何进行？将数据从外存读入内存，计数避免或在无访问的从库进行其他count类型的运行 count(*)，count(pk)，count(unique)，count(1)速度：count(*)count(1)count(unique)count(pk) DB_Oper_ other尽量使用pk或者unique key进行update,delete操作避免is null或is not null 减少不等值查询避免使用*,选择需要字段进行查询Where子句中,阈值较多的col放在前面如非去重,使用union all代替union 避免使用in 合理利用覆盖索引扫描避免完全模糊匹配 OutlineSQL基础什么是SQL？DDL/DML Data Type sql规范数据库设计数据库操作用户权限管理用户授权与用户信息表安全管理用户 DB_Oper_ privileges线上库对程序员开放的访问权限只读(S，dbname_r)、读写(S/I/D/U，dbname_w) 测试站点根据需求开启ddl权限(create,alter) 其他用户复制 replication slave root权限用户 DB_Oper_ privileges相关表mysql.user 对实例的权限，包含password mysql.db 对库的操作权限，包含db 授权&回收权限&回收用户grant select on dbname.tablename|view to userhost identified by password;flush privileges; revoke privileges on dbname.tablename from userhost; drop user userhost; OutlineSQL基础什么是SQL？DDL/DML Data Type sql规范数据库设计数据库操作用户权限管理用户授权与用户信息表安全管理用户 DB_Oper_ securitylinux的iptables限制网段用户允许访问的ip段限制程序用户权限限制密码复杂度 大小写字母、数字、特殊字符，8位以上 DB_Oper_ securityroot用户的初始化SET PASSWORD FOR rootlocalhost = PASSWORD(123456); SET PASSWORD FOR root127.0.0.1 = PASSWORD(123456); SET PASSWORD FOR root:1 = PASSWORD(123456); SET PASSWORD FOR roothostname = PASSWORD(123456);drop user localhost; drop user mysql-1-1; drop user rootmysql-1-1; drop database test; 丢弃root用户