MySQL 设计规范（续）

永夜海

1.1    数据库字段设计规范
　　优先选择符合存储需要的最小的数据类型：
　　对于非负数据采用无符号整型进行存储；
　　varchar(N)中N代表字符数，不是字节数（Oracle中是字节数，如中文GBK下2字节存一个汉字），即MySQL中varchar(2)可存储2个汉字，占用6个字节（UTF8）。
　　避免使用text,blob数据类型：
　　常见的text类型可存储64k，一般是使用不到这么大空间；
　　非要使用，建议把blob或text列分离到单独的扩展表中，且避免使用select *;
　　text, blob仅能使用前缀索引，且默认情况下text,blob列均不能有默认值。
　　避免使用ENUM数据类型：
　　修改ENUM值需要alter语句；
　　ENUM类型的ORDER BY操作效率低，需要额外操作；
　　禁止使用数值作为ENUM的枚举值。
　　尽可能把所有列定义为NOTNULL：
　　索引NULL列需占用额外的空间来保存（是否为空），所以占用更多空间；
　　比较和计算对NULL做特别处理，可能索引会失效。
　　使用TIMESTAMP或DATETIME类型存储时间：
　　TIMESTAMP 1970-01-01~2038-01-19，占用4字节和INT存储相同；
　　其他范围使用DATETIME类型存储。
　　金融财务相关类数据，使用DECIMAL类型：
　　占用空间有定义的宽度决定；
　　可用于存储比bigint更大的整数数据。
1.2    数据库SQL开发规范
　　建议使用预编译语句进行数据库操作：
　　只传参数，比传递SQL语句更高效；
　　相同语句可以一次解析，多次使用，提高处理效率。
　　避免数据类型的隐式转换：
　　隐式转换可能导致索引失效。
　　充分利用表上已存在的索引：
　　避免使用双%的查询条件，如’123%’可使用索引；
　　一个SQL只能使用联合索引一列进行范围查询；
　　PS：index(a, b, c)，where a = xxand b > low and b < high可使用到b列的索引，
　　wherea > low and a < high and b = xx，b这列使用不到索引；
　　这个处理和oracle的联合索引是一致的。
　　使用left join或not exists来优化not in操作。
　　禁止跨库查询：
　　程序连接不同数据库使用不同的账号；
　　为数据库迁移和分库分表留出余地；
　　降低业务的耦合度；
　　避免权限过大产生的安全风险，SQL注入也只能看到一个库。
　　禁止使用Select *：
　　消耗过多的CPU, IO, 网络资源；
　　无法使用覆盖索引；
　　可减少表结构变更带来的影响。
　　禁止使用不含字段列表的INSERT：
　　INSERT INTO T(A, B) VALUES(…….);
　　可减少表结构变更带来的影响。
　　尽量避免使用子查询，可以把子查询优化为join操作：
　　当然并不是所有的子查询都能转换为join，如group by等得子查询；
　　通常是IN子查询这样的语句。
　　子查询结果集无法使用索引；子查询会产生临时表操作，如果子查询数据量大将严重影响效率；消耗过多的CPU、IO。
　　避免使用JOIN关联太多的表：
　　每Join一个表多占用一部分内存（join_buffer_size）;
　　会产生临时表操作，影响查询效率；
　　MySQL最多允许关联61个表，建议不超过5个。
　　减少同数据库的交互次数：
　　数据库更适合做批量操作；
　　合并多个相同的操作到一起；
　　PS：如alter table t addcolumn c1 int, change column c2 c2 int…
　　使用in代替or：
　　in的值不要超过500个；
　　in操作可以有效的利用索引。
　　禁止使用order byrand()进行随机排序：
　　会把表中所有满足条件的数据装载到内存中进行排序；
　　消耗大量的CPU/IO/MEM；
　　处理方式：推荐在程序中获取一个随机值，然后在数据库中获取数据的方式（亲认为开发人员愿意去实现多余的算法吗？都希望一句SQL返回结果集）。
　　WHERE从句禁止对列进行函数转换和计算：
　　where date(createtime) = ‘20171010’ à where createtime >= ‘20171010’ and createtime < ‘20171011’
　　UNIONALL OR UNION：
　　这两种排序方式和Oracle的完全一致，UNION ALL不排序，UNION排序。
　　拆分复杂SQL为多个小SQL：
　　MySQL一个SQL只能使用一个CPU进行计算；
　　SQL拆分后可以通过并行查询提高处理效率。
1.3    数据库操作行为规范
　　超过100W行数据批量DML操作，要分批多次进行操作：
　　需考虑主从延迟；
　　binlog日志为row格式产生大量日志；
　　避免大事务的操作，阻塞并行。
　　大表表结构修改：
　　推荐使用pt-online-schema-change修改表结构；
　　可避免大表修改产生的主动延迟；
　　避免在对表字段修改时进行锁表。
　　禁止为程序账号赋予super权限：
　　当达到最大连接数限制时，还允许使用一个带有super权限的用户连接；
　　super权限只能留给DBA处理问题的账号使用。
　　对程序账号，遵循权限最小的原则：
　　程序在使用数据库账号只能在一个DB下使用，不允许跨库使用；
　　程序账号原则上不允许有drop权限。