Mysql开发规范
一、命名规范
1、库名、表名、字段名、索引名必须使用小写字母，并采用下划线分割。
2、库名、表名、字段名支持最多64个字符但禁止超过32个字符。
3、库名、表名、字段名必须见名知意。命名与业务、产品线相关联。
4、库名、表名、字段名禁止使用MySQL保留字(如：add/analyze/check/char/to/and/as/before/between/column...)( MySQL5.5&5.7保留关键字列表 ； https://dev.mysql.com/doc/refman/5.7/en/keywords.html )。
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个表、单表字段数不要超过40个
4、表、字段添加comment（除主键）
5、禁止在数据库中存储图片、文件等大数据。
6、字符类型居多的表建议控制在3000W以内，整型居多的表建议控制在5000W以内。
7、不建议或禁止在线上做数据库压力测试。
8、禁止测试环境直连生产环境调试，如有数据测试需求，请走特别申请流程。
9、依据数据冷热，分级存储，历史归档
三、库表设计
表设计
1、禁止跨库查询。
2、不建议使用MySQL分区表。
3、将关键字段、使用率频繁的字段拆分到单独表中，将大字段、访问频率低的字段拆分到单独表中存储，冷热分离。
4、推荐使用HASH、RANGE进行散表，表名后缀使用数字，数字必须从0开始。
5、散表或归档分表名称需符合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位，不代表存储长度。禁止使用tinyint ，在JAVA环境有转换问题。
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、程序不要修改数据库中事务隔离机制，控制锁的行为。
19、避免全表扫描，重要SQL必须被索引：update、delete的where条件列、order by、group by、distinct字段、多表join字段。
20、如果只是分组，用group by a order by null替换group by a去除排序。
21、禁止使用%前导查询，例如：like “%abc”，无法利用到索引。
22、禁止使用负向查询，例如 not in、!=、not like、<>。
23、禁止在 where 子句中对字段进行 null 值判断，字段通过增加默认值处理。
24、使用EXPLAIN判断SQL语句是否合理使用索引，尽量避免extra列出现：Using File Sort、Using Temporary。
25、禁止DML语句中出现@num变量替代字段名称。
26、UPDATE或DELETE语句加上WHERE条件索引列更新（如果使用非索引列更新会导致锁全表，造成Lock wait timeout exceeded; try restarting transaction），禁用LIMIT语句。
27、拼接sql语句时，注意where条件，防止sql注入时表记录被清空（如：delete from where a/a=a/1=1/a=0 or 1=1/exist返回布尔值为恒真）。
28、DML：sql语句中不允许有drop、truncate、delelet all data等语句。
29、DDL：添加字段语句禁止使用after，特别是对于mycat或gaea的分布式代理的数据库，如果加上after，会造成数据错误。例如：alter table t add column a after b;
微信分享/微信扫码阅读
