MySQL开发规范汇总2

  1. 所有的数据库对象名称必须使用小写字母并用下划线分割(MySQL大小写敏感，名称要见名知意，最好不超过32字符)
  1. 所有的数据库对象名称禁止使用MySQL保留关键字(如 desc、range、match、delayed 等 )
  1. 临时库表必须以tmp为前缀并以日期为后缀(tmp_)
  1. 备份库和库必须以bak为前缀并以日期为后缀(bak_)
  2. 所有存储相同数据的列名和列类型必须一致。(在多个表中的字段如user_id，它们类型必须一致)
  3. mysql5.5之前默认的存储的引擎是myisam，没有特殊要求，所有的表必须使用innodb(innodb好处支持失误，行级锁， 
高并发下性能更好，对多核，大内存，ssd等硬件支持更好)
  4. 数据库和表的字符集尽量统一使用utf8(字符集必须统一，避免由于字符集转换产生的乱码，汉字utf8下占3个字节)
  5. 所有表和字段都要添加注释COMMENT，从一开始就进行数据字典的维护
  6. 尽量控制单表数据量的大小在500w以内，超过500w可以使用历史数据归档，分库分表来实现(500万行并不是MySQL数 
据库的限制。过大对于修改表结构，备份，恢复都会有很大问题。MySQL没有对存储有限制，取决于存储设置和文件系 
统)
  7. 谨慎使用mysql分区表(分区表在物理上表现为多个文件，在逻辑上表现为一个表)
  8. 谨慎选择分区键，跨分区查询效率可能更低
  9. 建议使用物理分表的方式管理大数据
  10. 尽量做到冷热数据分离，减小表的宽度(mysql限制最多存储4096列，行数没有限制，但是每一行的字节总数不能超过 
65535。列限制好处:减少磁盘io，保证热数据的内存缓存命中率，避免读入无用的冷数据)
  11. 禁止在表中建立预留字段(无法确认存储的数据类型，对预留字段类型进行修改，会对表进行锁定)
  12. 禁止在数据中存储图片，文件二进制数据(使用文件服务器)
  13. 禁止在线上做数据库压力测试
  14. 禁止从开发环境，测试环境直接连生产环境数据库
  15. 限制每张表上的索引数量，建议单表索引不超过5个(索引会增加查询效率，但是会降低插入和更新的速度)
  16. 避免建立冗余索引和重复索引(冗余:index(a,b,c)index(a,b)index(a))
  17. 禁止给表中的每一列都建立单独的索引
  18. 每个innodb表必须有一个主键，选择自增id(不能使用更新频繁的列作为主键，不适用UUID,MD5,HASH,字符串列作为 
主键)
  19. 区分度最高的列放在联合索引的最左侧
  20. 尽量把字段长度小的列放在联合索引的最左侧
  21. 尽量避免使用外键(禁止使用物理外键，建议使用逻辑外键)
  22. 优先选择符合存储需要的最小数据类型
  23. 优先使用无符号的整形来存储 

    
  24. 优先选择存储最小的数据类型(varchar(N),N代表的是字符数，而不是字节数，N代表能存储多少个汉字)
  25. 避免使用Text或是Blob类型
  26. 避免使用ENUM数据类型(修改ENUM值需要使用ALTER语句，ENUM类型的ORDERBY操作效率低，需要额外操作，禁 
止使用书值作为ENUM的枚举值
  27. 尽量把所有的字段定义为NOT NULL(索引NULL需要额外的空间来保存，所以需要暂用更多的内存，进行比较和计算要 
对NULL值做特别的处理)
  28. 使用timestamp或datetime类型来存储时间
  29. 同财务相关的金额数据，采用decimal类型(不丢失精度，禁止使用 float 和 double)
  30. 避免使用双%号和like，搜索严禁左模糊或者全模糊(如果需要请用搜索引擎来解决。索引文件具有 B-Tree 的最左前缀匹 
配特性，如果左边的值未确定，那么无法使用此索)
  31. 建议使用预编译语句进行数据库操作
  32. 禁止跨库查询(为数据迁移和分库分表留出余地，降低耦合度，降低风险)
  33. 禁止select * 查询(消耗更多的cpu和io及网络带宽资源，无法使用覆盖索引)
  34. 禁止使用不含字段列表的insert语句(不允许insert into t values(‘a’，‘b’，‘c’)不允许)
  35. in 操作能避免则避免，若实在避免不了，需要仔细评估 in 后边的集合元素数量，控制在 1000 个之内
  36. 禁止使用orderbyrand()进行随机排序
  37. 禁止where从句中对列进行函数转换和计算(例如:where date(createtime)=‘20160901’ 会无法使用createtime列上 
索引。改成 where createtime>='20160901' and createtime <'20160902')
  38. 尽量使用 union all 代替 union
  39. 拆分复杂的大SQL为多个小SQL( MySQL一个SQL只能使用一个CPU进行计算)
  40. 尽量避免使用子查询，可以把子查询优化为join操作(子查询的结果集无法使用索引，子查询会产生临时表操作，如果子 
查询数据量大会影响效率，消耗过多的CPU及IO资源)
  41. 超过100万行的批量写操作，要分批多次进行操作(大批量操作可能会造成严重的主从延迟，binlog日志为row格式会产生 
大量的日志，避免产生大事务操作)
  42. 对于大表使用pt—online-schema-change修改表结构(避免大表修改产生的主从延迟，避免在对表字段进行修改时进行 
锁表)
  43. 对于程序连接数据库账号，遵循权限最小原则
  44. 超过三个表禁止 join。(需要 join 的字段，数据类型必须绝对一致;多表关联查询时，保证被关联的字段需要有索引。即 
使双表 join 也要注意表索引、SQL 性能。)
  45. 在varchar字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度即可。
  46. SQL 性能优化的目标:至少要达到 range 级别，要求是 ref 级别，如果可以是 consts最好
  47. 使用 ISNULL()来判断是否为 NULL 值。
  48. 尽量不要使用物理删除(即直接删除，如果要删除的话提前做好备份)，而是使用逻辑删除，使用字段delete_flag做逻辑 
删除，类型为tinyint（1），0表示未删除，1表示已删除
  49. 如果有orderby的场景，请注意利用索引的有序性。orderby最后的字段是组合,索引的一部分，并且放在索引组合顺序 
的最后，避免出现 file_sort 的情况，影响查询性能。
  50. 在代码中写分页查询逻辑时，若 count 为 0 应直接返回，避免执行后面的分页语句
posted @ 2021-12-29 19:19  姚狗蛋  阅读(16)  评论(0)  编辑  收藏  举报