必会的 MySQL 设计规约，都是惨痛的教训啊

前言

  在我们对数据库技术方案设计的时候，我们是否有自己的设计理念或者原则，还是更多的依据自己的直觉去设计，是否曾经懊悔线上发生过的一次低级故障，可能稍微注意点就可以避免，是否想过怎么才能很好的避免，下面规范的价值正是我们工作的检查清单，需要我们不断从错误中积累有效经验来指导未来的工作。

一、安全规范

  首先介绍的是安全规范，因为安全无小事，很多公司都曾经因为自己的数据泄露导致用户的惨痛损失，所以将安全规范放到了第一位。

1. 【强制】禁止在数据库中存储明文密码，需把密码加密后存储。

   说明：对于加密操作建议由公司的中间件团队基于如mybatis的扩展，提供统一的加密算法及密钥管理，避免每个业务线单独开发一套，同时也与具体的业务进行了解耦。

2. 【强制】禁止在数据库中明文存储用户敏感信息，如手机号等。

   说明：对于手机号建议公司搭建统一的手机号查询服务，避免在每个业务线单独存储。

3. 【强制】禁止开发直接给业务同学导出或者查询涉及到用户敏感信息的数据，如需要需上级领导审批。

4. 【强制】涉及到导出数据功能的操作，如包含敏感字段都需加密或脱敏。

5. 【强制】跟数据库交互涉及的敏感数据操作都需有审计日志，必要时要做告警。

6. 【强制】对连接数据库的IP需设置白名单功能，杜绝非法IP接入。

7. 【强制】对重要sql（如订单信息的查询）的访问频率或次数要做历史趋势监控，及时发现异常行为。

8. 【推荐】线上连接数据库的用户名、密码建议定期进行更换。

二、基础规范

1. 【推荐】尽量不在数据库做运算，复杂运算需移到业务应用里完成 。

2. 【推荐】拒绝大sql语句、拒绝大事务、拒绝大批量，可转化到业务端完成。

   说明：大批量操作可能会造成严重的主从延迟，从而会产生大量的日志。

3. 【推荐】避免使用存储过程、触发器、函数等，容易造成业务逻辑与DB耦合。

   说明：数据库擅长存储与索引、要解放数据库CPU，将计算转移到服务层、也具备更好的扩展性

4. 【强制】数据表、数据字段必须加入中文注释。

5. 【强制】不在数据库中存储图片、文件等大数据。

   说明：大文件和图片需要储在文件系统。

6. 【推荐】对于程序连接数据库账号，遵循权限最小原则。

7. 【推荐】数据库设计时，需要问下自己是否对以后的扩展性进行了考虑。

8. 【推荐】利用 pt-query-digest 定期分析slow query log并进行优化。

9. 【推荐】使用内网域名而不是ip连接数据库。

10. 【推荐】如果数据量或数据增长在前期规划时就较大，那么在设计评审时就应加入分表策略。

11. 【推荐】要求所有研发SQL关键字全部是小写，每个词只允许有一个空格。

三、命名规范

1. 【强制】库名、表名、字段名要小写，下划线风格，不超过32个字符，必须见名知意，建议使用名词而不是动词，词义与业务、产品线等相关联，禁止拼音英文混用。
2. 【强制】普通索引命名格式：idx_表名_索引字段名（如果以首个字段名为索引有多个，可以加上第二个字段名，太长可以考虑缩写），唯一索引命名格式：uk_表名_索引字段名（索引名必须全部小写，长度太长可以利用缩写），主键索引命名：pk_字段名。
3. 【强制】库名、表名、字段名禁止使用MySQL保留字。
4. 【强制】临时库表名必须以tmp为前缀，并以日期为后缀。
5. 【强制】备份库表必须以bak为前缀，并以日期为后缀。
6. 【推荐】用HASH进行散表，表名后缀使用16进制数，下标从0开始。
7. 【推荐】按日期时间分表需符合YYYY[MM][DD][HH]格式。
8. 【推荐】散表如果使用md5（或者类似的hash算法）进行散表，表名后缀使用16进制，比如user_ff。
9. 【推荐】使用CRC32求余（或者类似的算术算法）进行散表，表名后缀使用数字，数字必须从0开始并等宽，比如散100张表，后缀从00-99。
10. 【推荐】使用时间散表，表名后缀必须使用特定格式，比如按日散表user_20110209、按月散表user_201102。
11. 【强制】表达是与否概念的字段，使用 is _ xxx 的方式进行命名。

四、库设计规范

1. 【推荐】数据库使用InnoDB存储引擎。

   说明：支持事务、行级锁、并发性能更好、CPU及内存缓存页优化使得资源利用率更高。

2. 【推荐】数据库和表的字符集统一使用UTF8。

   说明：utf8号称万国码，其无需转码、无乱码风险且节省空间。若是有字段需要存储emoji表情之类的，则将表或字段设置成utf8mb4，utf8mb4向下兼容utf8。

3. 【推荐】不同业务，使用不同的数据库，避免互相影响。

4. 【强制】所有线上业务库均必须搭建MHA高可用架构，避免单点问题。

五、表设计规范

1. 【推荐】建表规范示例。

   CREATE TABLE `student_info` (    
       `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',    
       `stu_name` varchar(10) NOT NULL DEFAULT '' COMMENT '姓名',    
       `stu_score` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT '总分',    
       `stu_num` int(11) NOT NULL COMMENT '学号',    
       `gmt_create` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',    
       `gmt_modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',    
       `status` tinyint(4) DEFAULT '1' COMMENT '1代表记录有效，0代表记录无效',      
       PRIMARY KEY (`id`),      
       UNIQUE KEY `uk_student_info_stu_num` (`stu_num`) USING BTREE,    
       KEY `idx_student_info_stu_name` (`stu_name`) USING BTREE
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='学生信息表';
   

2. 【强制】禁止使用外键，如果有外键完整性约束，需要应用程序控制。

3. 【强制】每个Innodb 表必须有一个主键。

   说明：Innodb 是一种索引组织表，其数据存储的逻辑顺序和索引的顺序是相同的。每张表可以有多个索引，但表的存储顺序只能有一种，Innodb 是按照主键索引的顺序来组织表的，因此不要使用更新频繁的列如UUID、MD5、HASH和字符串列作为主键，这些列无法保证数据的顺序增长，主键建议使用自增ID 值。

4. 【推荐】单表列数目最好小于50。

5. 【强制】禁止使用分区表。

   说明：分区表在物理上表现为多个文件，在逻辑上表现为一个表，谨慎选择分区键，跨分区查询效率可能更低，建议采用物理分表的方式管理大数据

6. 【推荐】拆分大字段和访问频率低的字段，分离冷热数据。

7. 【推荐】采用合适的分库分表策略，例如千库十表、十库百表等（建议表大小控制在2G）。

8. 【推荐】单表不超过50个int字段；不超过20个char字段，不超过2个text字段。

9. 【推荐】表默认设置创建时间戳和更改时间戳字段。

10. 【推荐】日志类型的表可以考虑按创建时间水平切割，定期归档历史数据。

11. 【强制】禁止使用order by rand()。

    说明：order by rand()会为表增加一个伪列，然后用rand()函数为每一行数据计算出rand()值，基于该行排序，这通常都会生成磁盘上的临时表，因此效率非常低。

12. 【参考】可以结合使用hash、range、lookup table进行散表。

13. 【推荐】每张表数据量建议控制在500w以下，超过500w可以使用历史数据归档或分库分表来实现（500万行并不是MySQL数据库的限制。过大对于修改表结构，备份，恢复都会有很大问题。MySQL没有对存储有限制，取决于存储设置和文件系统）。

14. 【强制】禁止在表中建立预留字段

    说明：预留字段的命名很难做到见名识义，预留字段无法确认存储的数据类型，所以无法选择合适的类型；对预留字段类型的修改，会对表进行锁定

六、字段设计规范

1. 【强制】必须把字段定义为NOT NULL并且提供默认值。

   说明：NULL字段很难查询优化，NULL字段的索引需要额外空间，NULL字段的复合索引无效。

2. 【强制】禁止使用ENUM，可使用TINYINT代替。

3. 【强制】禁止使用TEXT、BLOB类型(如果表的记录数在万级以下可以考虑)。

4. 【强制】必须使用varchar(20)存储手机号。

5. 【强制】禁止使用小数存储国币、使用“分”作为单位，这样数据库里就是整数了。

6. 【强制】用DECIMAL代替FLOAT和DOUBLE存储精确浮点数。

7. 【推荐】使用UNSIGNED存储非负整数。

   说明：同样的字节数，存储的数值范围更大。

8. 【推荐】建议使用INT UNSIGNED存储IPV4。

   说明：用UNSINGED INT存储IP地址占用4字节，CHAR(15)则占用15字节。另外，计算机处理整数类型比字符串类型快。

9. 【推荐】字段长度尽量按实际需要进行分配，不要随意分配一个很大的容量。

10. 【推荐】核心表字段数量尽可能地少，有大字段要考虑拆分

11. 【推荐】适当考虑一些反范式的表设计，增加冗余字段，减少JOIN。

12. 【推荐】资金字段考虑统一*100处理成整型，避免使用decimal浮点类型存储。

13. 【推荐】使用VARBINARY存储大小写敏感的变长字符串或二进制内容**说明：**VARBINARY默认区分大小写，没有字符集概念，速度快。

14. 【参考】INT类型固定占用4字节存储

    说明：INT(4)仅代表显示字符宽度为4位，不代表存储长度。数值类型括号后面的数字只是表示宽度而跟存储范围没有关系，比如INT(3)默认显示3位，空格补齐，超出时正常显示，Python、Java客户端等不具备这个功能。

15. 【参考】区分使用DATETIME和TIMESTAMP。

    说明：存储年使用YEAR类型、存储日期使用DATE类型、存储时间(精确到秒)建议使用TIMESTAMP类型。DATETIME和TIMESTAMP都是精确到秒，优先选择TIMESTAMP，因为TIMESTAMP只有4个字节，而DATETIME8个字节，同时TIMESTAMP具有自动赋值以及⾃自动更新的特性。

16. 【推荐】将大字段、访问频率低的字段拆分到单独的表中存储，分离冷热数据。

    说明：有利于有效利用缓存，防⽌读入无用的冷数据，较少磁盘IO，同时保证热数据常驻内存提⾼高缓存命中率。

17. 【参考】VARCHAR(N)，N表示的是字符数不是字节数，比如VARCHAR(255)，可以最大可存储255个汉字，需要根据实际的宽度来选择N。

18. 【参考】VARCHAR(N)，N尽可能小，因为MySQL一个表中所有的VARCHAR字段最大长度是65535个字节，进行排序和创建临时表一类的内存操作时，会使用N的长度申请内存。

19. 【推荐】VARCHAR(N)，N>5000时，使用BLOB类型。

20. 【推荐】使用短数据类型，比如取值范围为0~80时，使用TINYINT UNSIGNED。

21. 【强制】存储状态、性别等，用TINYINT。

22. 【强制】所有存储相同数据的列名和列类型必须一致（在多个表中的字段如user_id，它们类型必须一致）。

23. 【推荐】优先选择符合存储需要的最小数据类型。

24. 【推荐】如果存储的字符串长度几乎相等，使用 char 定长字符串类型。

七、索引设计规范

1. 【推荐】单表索引建议控制在5个以内。

   说明：索引可以增加查询效率，但同样也会降低插入和更新的效率，甚至有些情况下会降低查询效率，所以不是越多越好。

2. 【强制】禁止在更新十分频繁，区分度不高的属性上建立索引。

3. 【强制】建立组合索引必须把区分度高的字段放在前面。

4. 【推荐】对字符串使用索引，如果字符串定义长度超过128的，可以考虑前缀索引。

5. 【强制】表必须有主键，并且是auto_increment及not null的，根据表的实际情况定义无符号的tinyint、smallint、int、bigint。

6. 【强制】禁止更新频繁的列作为主键。

7. 【强制】禁止字符串列作为主键。

8. 【强制】禁止UUID MD5 HASH这些作为主键(数值太离散了)。

9. 【推荐】默认使用非空的唯一键作为主键。

10. 【推荐】主键建议选择自增或发号器。

11. 【推荐】核心SQL优先考虑覆盖索引。

12. 【参考】避免冗余和重复索引。

13. 【参考】索引要综合评估数据密度和分布以及考虑查询和更新比例。

14. 【强制】不在索引列进行数学运算和函数运算。

15. 【推荐】研发要经常使用explain，如果发现索引选择性差，必须要学会使用hint。

16. 【推荐】能使用唯一索引就要使用唯一索引，提高查询效率。

17. 【推荐】多条字段重复的语句，要修改语句条件字段的顺序，为其建立一条联合索引，减少索引数量。

18. 【强制】索引字段要保证不为NULL，考虑default value进去。NULL也是占空间，而且NULL非常影响索引的查询效率。

19. 【强制】新建的唯一索引不能和主键重复。

20. 【推荐】尽量不使用外键、外键用来保护参照完整性，可在业务端实现**说明：**避免对父表和子表的操作会相互影响，降低可用性。

21. 【强制】字符串不应做主键。

22. 【强制】表必须有无符号int型自增主键，对应表中id字段。

    说明：必须得有主键的原因：采用RBR模式复制，无主键的表删除，会导致备库夯住。而使用自增的原因：数据写入可以提高插入性能，避免page分裂，减少表碎片。

23. 【推荐】对长度过长的VARCHAR字段建立索引时，添加crc32或者MD5 Hash字段，对Hash字段建立索引。

24. 【推荐】WHERE条件中的非等值条件（IN、BETWEEN、<、<=、>、>=）会导致后面的条件使用不了索引。

25. 【推荐】索引字段的顺序需要考虑字段值去重之后的个数，个数多的放在前面。

26. 【推荐】ORDER BY，GROUP BY，DISTINCT的字段需要添加在索引的后面。

27. 【参考】合理创建联合索引（避免冗余），如(a,b,c) 相当于 (a) 、(a,b) 、(a,b,c)。

28. 【推荐】复合索引中的字段数建议不超过5个。

29. 【强制】不在选择性低的列上建立索引，例如”性别”, “状态”， “类型”。

30. 【推荐】对于单独条件如果走不了索引，可以使用force –index强制指定索引。

31. 【强制】禁止给表中的每一列都建立单独的索引。

32. 【推荐】在varchar字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度即可。

八、SQL使用规范

1. 【强制】禁止使用SELECT *，只获取必要的字段，需要显示说明列属性。

   说明：按需获取可以减少网络带宽消耗，能有效利用覆盖索引，表结构变更对程序基本无影响。

2. 【强制】禁止使用INSERT INTO t_xxx VALUES(xxx)，必须显示指定插入的列属性。

3. 【强制】WHERE条件中必须使用合适的类型，避免MySQL进行隐式类型转化。

   说明：因为MySQL进行隐式类型转化之后，可能会将索引字段类型转化成=号右边值的类型，导致使用不到索引，原因和避免在索引字段中使用函数是类似的，例子 select uid from t_user where phone=15855550101（phone为 varchat 类型，此时查询中使用数字查询，会导致索引失效）。

4. 【强制】禁止在WHERE条件的属性上使用函数或者表达式。

5. 【强制】禁止负向查询，以及%开头的模糊查询。

6. 【强制】应用程序必须捕获SQL异常，并有相应处理。

7. 【推荐】sql语句尽可能简单、大的sql想办法拆成小的sql语句。

   说明：简单的SQL容易使用到MySQL的querycache、减少锁表时间特别是MyISAM、可以使用多核cpu。

8. 【推荐】事务要简单，整个事务的时间长度不要太长。

9. 【强制】避免在数据库中进行数学运算或者函数运算(MySQL不擅长数学运算和逻辑判断，也容易将业务逻辑和DB耦合在一起)。

10. 【推荐】sql中使用到OR的改写为用IN() (or的效率没有in的效率高)。

11. 【参考】SQL语句中IN包含的值不应过多，里面数字的个数建议控制在1000个以内。

12. 【推荐】limit分页注意效率。Limit越大，效率越低。

13. 【推荐】尽量使用union all替代union。

14. 【参考】避免使用大表JOIN。

15. 【推荐】对数据的更新要打散后批量更新，不要一次更新太多数据。

16. 【推荐】使用合理的SQL语句减少与数据库的交互次数。

17. 【参考】注意使用性能分析工具 Sql explain / showprofile / mysqlsla。

18. 【推荐】能不用NOT IN就不用NOT IN，坑太多了，会把空和NULL给查出来。

19. 【推荐】关于分页查询，程序里建议合理使用分页来提高效率，limit、offset较大要配合子查询使用。

20. 【强制】禁止在数据库中跑大查询。

21. 【强制】禁止单条SQL语句同时更新多个表。

22. 【推荐】统计表中记录数时使用COUNT(*)，而不是COUNT(primary_key)和COUNT(1)。

    说明：count( * ) 会统计值为 NULL 的行，而 count( 列名 ) 不会统计此列为 NULL 值的行。

23. 【推荐】INSERT语句使用batch提交（INSERT INTO tableVALUES(),(),()……），values的个数不应过多。

24. 【推荐】获取大量数据时，建议分批次获取数据，每次获取数据少于2000条，结果集应小于1M。

25. 【推荐】在做开发时建议使用数据库框架(如mybatis)或prepared statement，可以提升性能并避免SQL注入。

26. 【强制】禁止跨库查询（为数据迁移和分库分表留出余地，降低耦合度，降低风险）。

27. 【推荐】尽量避免使用子查询，可以把子查询优化为join操作（子查询的结果集无法使用索引，子查询会产生临时表操作，如果子查询数据量大会影响效率，消耗过多的CPU及IO资源）。

28. 【强制】超过三个表禁止 join。（需要 join 的字段，数据类型必须绝对一致；多表关联查询时，保证被关联的字段需要有索引。即使双表 join 也要注意表索引、SQL 性能）。

29. 【推荐】SQL 性能优化的目标：至少要达到 range 级别，要求是 ref 级别，如果可以是 consts最好。

30. 【推荐】尽量不要使用物理删除（即直接删除，如果要删除的话提前做好备份），而是使用逻辑删除，使用字段delete_flag做逻辑删除，类型为tinyint，0表示未删除，1表示已删除。

31. 【强制】在代码中写分页查询逻辑时，若 count 为 0 应直接返回，避免执行后面的分页语句。

32. 【强制】程序连接不同的数据库要使用不同的账号。

33. 【推荐】使用 ISNULL()来判断是否为 NULL 值。

九、行为规范

1. 【强制】禁止使用应用程序配置文件内的帐号手工访问线上数据库。
2. 【强制】禁止非DBA对线上数据库进行写操作，修改线上数据需要提交工单，由DBA执行，提交的SQL语句必须经过测试。
3. 【强制】禁止在线上做数据库压力测试。
4. 【强制】禁止从测试、开发环境直连线上数据库。
5. 【强制】禁止在主库进行后台统计操作，避免影响业务，可以在离线从库上执行后台统计。

十、流程规范

1. 【强制】所有的建表操作需要提前告知该表涉及的查询sql。
2. 【强制】所有的建表需要确定建立哪些索引后才可以建表上线。
3. 【强制】所有的改表结构、加索引操作都需要将涉及到所改表的查询sql发出来告知DBA等相关人员。
4. 【强制】在建新表加字段之前，要求至少要提前3天邮件出来，给dba们评估、优化和审核的时间。
5. 【强制】批量导入、导出数据需要DBA进行审查，并在执行过程中观察服务。
6. 【强制】禁止有super权限的应用程序账号存在。
7. 【强制】推广活动或上线新功能必须提前通知DBA进行流量评估。
8. 【强制】不在业务高峰期批量更新、查询数据库。
9. 【强制】隔离线上线下环境（开发测试程序禁止访问线上数据库）。
10. 【强制】在对大表做表结构变更时，如修改字段属性会造成锁表，并会造成从库延迟，从而影响线上业务，必须在凌晨后业务低峰期执行，另统一用工具pt-online-schema-change避免锁表且降低延迟执行时间。
11. 【强制】核心业务数据库变更需在凌晨执行。
12. 【推荐】汇总库开启Audit审计日志功能，出现问题时方可追溯。
13. 【强制】给业务方开权限时，密码要用MD5加密，至少16位。权限如没有特殊要求，均为select查询权限，并做库表级限制。
14. 【推荐】如果出现业务部门人为误操作导致数据丢失，需要恢复数据，请在第一时间通知DBA，并提供准确时间，误操作语句等重要线索。。
15. 【强制】批量更新数据，如update,delete 操作，需要DBA进行审查，并在执行过程中观察服务。
16. 【强制】业务部门程序出现bug等影响数据库服务的问题，请及时通知DBA便于维护服务稳定。
17. 【强制】线上数据库的变更操作必须提供对应的回滚方案。
18. 【强制】批量清洗数据，需要开发和DBA共同进行审查，应避开业务高峰期时段执行，并在执行过程中观察服务状态。
19. 【强制】数据订正如删除和修改记录时，要先 select ，确认无误才能执行更新语句，避免出现误删除。

小结

把今天最好的表现当作明天最新的起点……．～

  投身于天地这熔炉，一个人可以被毁灭，但绝不会被打败！一旦决定了心中所想，便绝无动摇。迈向光明之路，注定荆棘丛生，自己选择的路，即使再荒谬、再艰难，跪着也要走下去！放弃，曾令人想要逃离，但绝境重生方为宿命。若结果并非所愿，那就在尘埃落定前奋力一搏！