一文彻底搞懂MySQL建表规范

1. 表设计规范

• 库名、表名、字段名使用小写字母，使用“_”分割，不超过12个字符。
• 使用InnoDB存储引擎。
• 存储精确浮点数使用DECIMAL替代FLOAT和DOUBLE。
• 使用UNSIGNED存储非负数值，使用INT UNSIGNED存储IPv4。
• 整形定义中不添加长度，例如使用INT而不是INT(4)。
• 使用短数据类型，例如取值范围为0-80时，使用TINYINT UNSIGNED。
• 不建议使用ENUM类型，使用TINYINT来代替。
• 尽可能避免使用TEXT、BLOB类型。
• 对于VARCHAR(N)，N表示字符数而不是字节数，需要根据实际宽度选择N，尽可能小以减少内存占用。
• 表字符集选择UTF8。
• 使用VARBINARY存储变长字符串。
• 存储年使用YEAR类型，存储日期使用DATE类型，存储精确到秒的时间使用TIMESTAMP类型。
• 字段定义为NOT NULL。
• 将过大字段拆分到其他表中，避免在数据库中使用VARBINARY、BLOB存储图片、文件等。
• 表结构变更需要通知DBA审核。

2. 索引规范

• 非唯一索引命名规范为“idx_字段名_字段名[ _字段名]”，唯一索引命名规范为“uniq_字段名_字段名[ _字段名]”，索引名称使用小写。
• 索引中的字段数建议不超过5个，单张表的索引数量控制在5个以内。
• 唯一键由3个以下整形字段组成时，可作为主键；否则，使用自增id作为主键。
• 唯一键不与主键重复，索引字段顺序考虑字段值去重后的个数，个数多的放在前面。
• ORDER BY、GROUP BY、DISTINCT的字段需添加在索引后面。
• 使用EXPLAIN判断SQL语句是否合理使用索引，避免extra列出现：Using File Sort、Using Temporary。
• UPDATE、DELETE语句需根据WHERE条件添加索引。
• 避免使用%前缀模糊查询，如LIKE “%weibo”。
• 对长度过长的VARCHAR字段建立索引时，添加crc32或MD5 Hash字段，并对Hash字段建立索引。
• 合理创建联合索引，避免冗余，例如(a,b,c)相当于(a)、(a,b)、(a,b,c)。
• 合理利用覆盖索引。
• SQL变更需确认索引是否需要变更，并通知DBA。

3. SQL语句规范

• 使用prepared statement可提高性能并防止SQL注入。
• SQL语句中IN包含的值不应过多。
• UPDATE、DELETE语句不使用LIMIT。
• WHERE条件中必须使用合适的类型，避免MySQL进行隐式类型转化。
• SELECT语句只获取需要的字段，不使用SELECT *。
• 使用显式字段名称，不使用SELECT *，INSERT INTO table()。
• 避免在SQL语句进行数学或函数运算，减少业务逻辑和DB的耦合。
• 使用batch提交INSERT语句，values个数不应过多。
• 避免使用存储过程、触发器、函数等，可能存在bug且增加耦合。
• 避免使用JOIN，减少与数据库的交互次数。
• 不使用ORDER BY RAND()，使用其他替代方法。
• 使用合理的分页方式以提高效率。
• 统计表中记录数时使用COUNT(*)，而不是COUNT(primary_key)或COUNT(1)。
• 禁止在从库上执行后台管理和统计类型的QUERY。

4. 散表规范

• 控制每张表的数据量在5000万以下。
• 可以结合使用散表方法，包括hash、range、lookup table。
• 对于使用md5等hash算法进行散表，表名后缀使用16进制，例如user_ff。
• 推荐使用CRC32求余或类似算术算法进行散表，表名后缀使用数字，数字从0开始并等宽，例如散100张表，后缀从00到99。
• 使用时间散表时，表名后缀必须使用特定格式，例如按日散表命名为user_20110209，按月散表命名为user_201102。