MySQL_SOP

发表于 分类于 Waline： 本文字数： 2.5k 阅读时长 ≈ 2 分钟

MySQL 使用规范

一、基本规范

  1. 使用 INNODB 存储引擎
  2. 表字符集使用 UTF8，如遇到 EMOJI 等表情符号的存储需求，可申请使用 UTF8MB4 字符集
  3. 所有表都需要添加注释
  4. 单表数据量建字符类型居多的表建议控制在 3000W 以内，整型居多的表建议控制在 5000W 以内
  5. 不在数据库中存储图⽚、文件等大数据
  6. 禁止在线上做数据库压力测试
  7. 禁⽌从测试、开发环境直连数据库

二、命名规范

  1. 库名、表名、字段名必须有固定的命名长度，12个字符以内
  2. 库名、表名、字段名禁⽌使⽤ MySQL 保留字，比如订单表不要直接叫做order
  3. 临时库、临时表，命名必须以 tmp 为前缀，并以⽇日期为后缀
  4. 备份库、备份表，命名必须以 bak 为前缀，并以日期为后缀

三、库、表、字段设计规范

  1. 建表默认5字段：主键、创建时间、创建人、修改时间、修改人。id、create_time、creator、update_time、updator
  2. 禁⽌使用分区表
  3. 拆分大字段和访问频率低的字段，分离冷热数据
  4. 按日期时间分表需符合例如 table_201401、table_20140422 格式
  5. 采用合适的分库分表策略
  6. 用 DECIMAL 代替 FLOAT 和 DOUBLE 存储精确浮点数
  7. 越简单越好：将字符转化为数字、使用 TINYINT 来代替 ENUM 类型
  8. 若使用 ENUM 类型，不允许修改默认值，只允许顺序添加
  9. 所有字段均定义为 NOT NULL
  10. INT类型固定占用 4 字节存储
  11. 使用 timestamp 存储时间
  12. 使用 VARBINARY 存储大小写敏感的变长字符串
  13. 禁止在数据库中存储明文密码，把密码加密后存储
  14. 用好数值类型字段
  15. 存储 ip 最好用 int 存储而非 char(15)
  16. 避免使用 NULL， NULL 字段很难查询优化，NULL字段的索引需要额外空间，NULL字段的复合索引无效
  17. 少用 text/blob ， varchar 的性能会比 text 高很多，实在避免不了blob，请拆表
  18. 数据库中不允许存储大文件，或者照片，可以将大对象放到磁盘上，数据库中存储它的路径

四、索引规范

1、索引的数量要控制：

  • 单张表中索引数量不超过 5 个
  • 单个索引中的字段数不超过 5 个
  • 对字符串使⽤用前缀索引，前缀索引长度不超过 8 个字符
  • 建议优先考虑前缀索引，必要时可添加伪列并建立索引

2、主键准则

  • 表必须有主键
  • 不使用更新频繁的列作为主键
  • 尽量不选择字符串列作为主键
  • 不使用 UUID MD5 HASH 这些作为主键(数值太离散了)
  • 默认使⽤非空的唯一键作为主键
  • 建议选择自增或发号器

3、重要的 SQL 必须被索引

  • 比如：UPDATE 、 DELETE 语句的 WHERE 条件列，ORDER BY 、 GROUPBY 、 DISTINCT 的字段

4、多表 JOIN 的字段注意以下

  • 区分度最大的字段放在前面
  • 核⼼ SQL 优先考虑覆盖索引
  • 避免冗余和重复索引
  • 索引要综合评估数据密度和分布以及考虑查询和更新比例

5、索引禁忌

  • 不在低基数列上建立索引，例如“性别”
  • 不在索引列进行数学运算和函数运算

6、尽量不使用外键

  • 外键用来保护参照完整性，可在业务端实现
  • 对父表和子表的操作会相互影响，降低可用性

7、索引命名：

  • 非唯一索引必须以 idx_字段1_字段2 命名，唯一所以必须以 uniq_字段1_字段2 命名，索引名称必须全部小写

8、新建的唯一索引必须不能和主键重复

9、索引字段的默认值不能为 NULL ，要改为其他的 default 或者空。 NULL 非常影响索引的查询效率

10、反复查看与表相关的 SQL

  • 符合最左前缀的特点建立索引。 多条字段重复的语句，要修改语句条件字段的顺序，为其建立一条联合索引，减少索引数量

11、能使用唯一索引就要使用唯一索引，提高查询效率

12、研发要经常使用 explain ，如果发现索引选择性差，必须让他们学会使用 hint

13、合理创建联合索引，(a，b，c) 相当于 (a) 、(a，b) 、(a，b，c)

14、不对过长的 VARCHAR 字段建立索引。 建议优先考虑添加 CRC32 或 MD5 伪列，并对伪列建立索引，减少索引长度，提高效率。

五、SQL规范

  1. 使用 prepared statement，可以提升性能并避免 SQL 注入。
  2. 减少与数据库交互次数，尽量采用批量提交 SQL 语句INSERT INTO table （column1、column2、column3） VALUES(),(),();，不宜过多看字符个数 500-1000 即可。
  3. SQL 语句尽可能简单，大的 SQL 想办法拆成小的 SQL 语句(充分利用句QUERYCACHE 和充分利用多核 CPU)
  4. 事务要简单，整个事务的时间长度不要太长
  5. 避免使用触发器、函数、存储过程
  6. 降低业务耦合度，为 sacle out 、 sharding 留有余地
  7. 避免在数据库中进⾏数学运算(MySQL不擅长数学运算和逻辑判断)
  8. 不要用 SELECT * ，查询哪几个字段就SELECT这几个字段
  9. SQL 中使用到 OR 的改写为用 IN() (OR的效率没有IN的效率高)，里面数字的个数建议控制在 1000 以内。
  10. LIMIT 分页注意效率。
  11. 使用 UNION ALL 替代 UNION
  12. 避免使用大表的 JOIN
  13. 使用 GROUP BY 分组、自动排序
  14. 对数据的更新要打散后批量更新，不要一次更新太多数据
  15. 注意使用性能分析工具
  16. SQL explain / showprofile / mySQLsla，尽量避免 extra 列出现：Using File Sort、Using Temporary。
  17. SQL 语句要求所有研发，SQL 关键字全部是大写，每个词只允许有一个空格。
  18. SQL 语句不可以出现隐式转换，比如SELECT id FROM TABLE WHERE id='1'。
  19. IN 条件里面的数据数量要少，1000 个以内，要学会使用 EXIST 代替IN，EXIST 在一些场景查询会比 IN 快。
  20. NOT IN 能不用就不用，会把空和 NULL 给查出来。
  21. 在 SQL 语句中，禁止使用前缀是 % 的 LIKE
  22. 不使用负向查询，如 NOT IN / NOT LIKE / != / < >
  23. 程序里建议合理使用分页来提高查询效率。
  24. 使⽤预编译语句，只传参数，比传递 SQL 语句更高效；一次解析，多次使用；降低 SQL 注入概率
  25. 禁止使 ORDER BY RAND()
  26. 禁⽌单条 SQL 语句同时更新多个表
  27. 禁止使用 % 前导查询，例如：like “%abc”，无法利用到索引。