永夜海 发表于 2018-10-10 07:02:00

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权限。

页: [1]
查看完整版本: MySQL 设计规范(续)