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;

--------EOF---------
微信分享/微信扫码阅读