MySQL开发规范

最近看到一份MySQL开发规范，受益颇多，规范里隐藏了很多MySQL的原理，大家有兴趣可以分析一下这些规范被如此设定的原因。

命名规范

1. 库名、表名、字段名、索引名必须使用小写字母，并采用下划线分割。

2. 库名、表名、字段名支持最多64个字符但禁止超过32个字符。

3. 库名、表名、字段名必须见名知意。命名与业务、产品线相关联。

4. 库名、表名、字段名禁止使用MySQL保留字(如：add/analyze/check/char/to/and/as/before/between/column...)。

5. 数据库初始化会删除test库 ，库名、表名、字段名禁止使用test开头。

6. 创建一rubbish库，存放无效表、垃圾表，线上系统无异常，确认无误后，定期清理。

7. 创建一archive库，存放本地归档，确认无使用计划后，定期转移到归档备份服务器中。

8. 临时库、表名必须以tmp为前缀，并以日期为后缀。例如 tmp_table_20140401。

9. 禁止使用存储过程、触发器、视图、UDF、EVENT等。

10. 账号命名规则，本业务访问账号：业务名wn/业务名rn；跨业务访问账号：访问组要访问业务名w/访问组要访问业务名r

库表规范

1. 默认使用InnoDB存储引擎。

2. 库、表、字段、client字符集默认使用UTF8，如遇到EMOJ等表情符号的存储需求，可申请使用UTF8MB4字符集。

3. 单库不要超过500个表、单表字段数不要超过50个

4. 表、字段添加comment（除主键）

5. 禁止在数据库中存储图片、文件等大数据。

6. 字符类型居多的表建议控制在3000W以内，整型居多的表建议控制在5000W以内。

7. 不建议或禁止在线上做数据库压力测试。

8. 禁止测试环境直连生产环境调试，如有数据测试需求，请走特别申请流程。

9. 依据数据冷热，分级存储，历史归档

库表设计

表设计

1. 前端禁止使用分区表。

2. 将关键字段、使用率频繁的字段拆分到单独表中，将大字段、访问频率低的字段拆分到单独表中存储，冷热分离。

3. 推荐使用HASH、RANGE进行散表，表名后缀使用数字，数字必须从0开始。

4. 散表或归档分表名称需符合YYYY-MM-DD-HH格式，例如table_201401、table_20140422

字段设计

表字段少而精，尽量选择最小数据类型，表结构设计时适当冗余，不在数据库上做大量的cpu运算

1. 建表默认5字段，主键、创建时间、创建者、修改时间、修改者。id、create_time、create_user、update_time、update_user

2. 建议使用UNSIGNED存储非负数值。

3. 建议使用INT UNSIGNED存储IPV4。

4. 用DECIMAL代替FLOAT和DOUBLE存储精确浮点数，例如支付相关数据。

5. 建议整型字段直接使用INT，INT类型固定占4字节存储， 默认是11，最大有效显示宽度是255，例如INT(4)仅代表字符宽度为4位，不代表存储长度。

6. 区分使用TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT数据类型和取值范围(TINYINT>SMALLINT>MEDIUMINT>INT>BIGINT>DECIMAL---存储空间逐渐变大,而性能却逐渐变小)。

7. 禁止字段使用enum数据类型。ENUM类型不允许修改默认值，只允许顺序添加。

8. 禁止使用TEXT、BLOB类型，如必须使用，建议和主键一起进行拆表处理，可考虑VARCHAR替换。

9. 使用VARBINARY类型默认区分大小写，没有字符集概念，可存储大小写敏感的变长字符串或二进制内容。

10. 使用尽可能小的VARCHAR字段。VARCHAR(N)中的N表示字符数而非字节数。比如VARCHAR(255)，可以最大可存储255个汉字，需要根据实际的宽度来选择N。

11. 区分使用DATETIME和TIMESTAMP。存储年使用YEAR类型。存储日期使用DATE类型。存储时间(精确到秒)建议使用TIMESTAMP类型。

12. 所有字段均定义为NOT NULL。尽量避免使用NULL，要是必须用NULL，那也可考虑使用0、特殊值或空串来进行代替。

索引规范

1. 单张表中索引数量不超过5个，单个索引中的字段数不超过5个。

2. 非唯一索引按照“idx字段名称[字段名称]”进行命名。

3. 唯一索引按照“uk字段名称[字段名称]”进行命名。

4. 联合索引建议包含所有字段名，过长的字段名可以缩写形式。

5. 表必须有主键，推荐使用UNSIGNED自增列作为主键并且该主键为非业务字段。

6. 唯一键由3个以下字段组成，并且字段都是整形时，可使用唯一键作为主键。其他情况下，建议使用自增列或全局生成器作主键。

7. 禁止冗余、重复索引。

8. 禁止索引null列。

9. 禁止使用外键，避免产生死锁，通过程序来保证约束。

10. 避免数据类型转换，联表查询时，JOIN列的数据类型必须相同，并且要建立索引。

11. 选择区分度大的列建立索引。字段的顺序对组合索引有至关重要的作用，组合索引中，区分度大的字段要放在最前面。

12. 不在区分度低的上建立索引，例如“性别”。

13. 对较长的字符串字段、blob、text使用前缀索引，前缀索引长度不超过8个字符。（使用前缀索引，不仅节省存储空间，提高索引缓存命中率，还可减少disk的IO操作。）

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

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

16. 合理使用覆盖索引减少IO，避免排序。

17. 添加优化的索引之后，再删除多余的索引。

18. 单个索引尽可能覆盖更多的sql，更新频率比较高的表要控制索引的数量。

19. 大批量变更数据，索引维护成本很高，因此大批量数据更新要拆分出小粒度。

SQL设计

1. 使用prepared statement，可以提升性能并避免SQL注入。

2. 用IN代替OR。SQL语句中IN包含的值不应过多，应少于500个。

3. 禁止隐式类型转换。数值类型禁止加引号；字符串类型必须加引号。

4. 避免使用JOIN和子查询，禁止使用3表以上的JOIN，必要时推荐用JOIN代替子查询。

5. 避免在MySQL中索引列进行数学运算和函数运算。

6. 减少与数据库交互次数，尽量采用批量提交SQL语句（INSERT INTO table （column1、column2、column3） VALUES(),(),()），不宜过多。

7. 拆分复杂SQL为多个小SQL，避免大事务。

8. 获取大量数据时，建议分批次获取数据，每次获取数据少于500条，结果集应小于1M。

9. 用UNION ALL替换UNION。

10. 禁止在前端业务中使用count(*)，可放在memcache、redis、某一从库或增加统计表来维护。

11. 多表关联时禁止使用select *语句，只取需要列。

12. SQL中避免出现rand()、sysdate()、current_user()等不确定结果的函数。

13. INSERT INTO语句必须显示指明字段名称。

14. 禁止使用 INSERT INTO B SELECT * FROM A。

15. 禁止单条SQL语句同时更新多个表。

16. 建议使用合理的分页方式以提高分页效率。

17. 禁止在生产环境从库进行查询分析统计操作，必要时请申请专用统计服务器，前后台分离。

18. 程序应有捕获SQL异常的处理机制，必要时通过rollback显式回滚。

19. 程序不要修改数据库中事务隔离机制，控制锁的行为。

20. 避免全表扫描，重要SQL必须被索引：update、delete的where条件列、order by、group by、distinct字段、多表join字段。

21. 如果只是分组，用group by a order by null替换group by a去除排序。

22. 禁止使用%前导查询，例如：like “%abc”，无法利用到索引。

23. 禁止使用负向查询，例如 not in、!=、not like、<>。

24. 禁止在 where 子句中对字段进行 null 值判断，字段通过增加默认值处理。

25. 使用EXPLAIN判断SQL语句是否合理使用索引，尽量避免extra列出现：Using File Sort、Using Temporary。

26. 禁止DML语句中出现@num变量替代字段名称。

27. UPDATE或DELETE语句加上WHERE条件索引列更新（如果使用非索引列更新会导致锁全表，造成Lock wait timeout exceeded; try restarting transaction），禁用LIMIT语句。

28. 拼接sql语句时，注意where条件，防止sql注入时表记录被清空（如：delete from where a/a=a/1=1/a=0 or 1=1/exist返回布尔值为恒真）。

29. DML：sql语句中不允许有drop、truncate、delelet all data等语句。

30. DDL：添加字段语句禁止使用after，特别是对于mycat或gaea的分布式代理的数据库，如果加上after，会造成数据错误。例如：alter table t add column a after b;

参考

新建表参考：

create table  tb_cart_goods_special (

id int unsigned not null auto_increment,

area varchar(10) not null default 'cn' comment '区域简称, e.g. cn,hk,tw,sg',

region_name varchar(20) not null default '' comment '区域名称',

primary key (id)

）engine=innodb auto_increment=1 default charset=utf8 comment='购物车下面单个商品的推荐调整’;

添加字段参考：

alter table tb_cart_goods_special add goods_id int unsigned not null default '0' comment '商品编号’;

添加索引参考（idx,uk）：

alter table tb_cart_goods_special add index idx_area(area),add unique index uk_goods_id(goods_id);

注意：

1. 任何ddl/dml操作语句都不要加数据库名称，由DBA切换到对应的数据库来执行。

2. 新建表自增auto_increment从1开始。

3. 工单提交的sql，不要加上特殊字符，如"`"

最后

大家如果喜欢我的文章，可以关注我的公众号（程序员麻辣烫）

我的个人博客为：https://shidawuhen.github.io/