Mysql开发规范

一、表设计

• 数据库对象名称不要超过30个字母，越短越好
• 数据库对象名称使用小写字母，以'_'下划线分隔，禁止中文拼音、空格
• 数据库对象名称要有意义，建议使用名词，尽量不使用缩写单词（如javaee特殊含义）
• 数据库对象尽量有comment注释标明含义
• 单表的数据量预估，纯INT不超1000万，含CHAR不超500万
• 单表字段数上限控制在20~50个。纯INT字段不超50个，CHAR(10)字段不超20个
• 单行不超过200Byte
• 建议使用UNSIGNED存储非负数值。业务无限制正负数值时，尽量使用非负数值，增加一半的数值容量
• 建议使用INT UNSIGNED存储IPv4。通过MySQL函数inet_ntoa和inet_aton来进行转化
• 整型定义中不添加长度，比如使用INT，而不是INT(5)
• 精确浮点数必须使用DECIMAL替代FLOAT和DOUBLE，因为不受单精度或双精度的影响
• 整型数值，尽量选择适合的字段类型，比如取值范围为0~80时，使用TINYINT UNSIGNED
• 字符串类型值，必须控制输入的字符，比如空格、符号等，因为字符串末尾的空格会有trailing spaces问题
• 建议使用ENUM类型，来替代TINYINT、CHAR(1)保存枚举类型，因为ENUM类型的枚举值会有判断
• 尽量不使用TEXT、BLOB类型
• VARCHAR(M)，M表示字符数而不是字节数，比如VARCHAR(1024)，最大可存储1024个汉字，根据实际业务选择长度M
• VARCHAR(M)，M尽可能小，因为VARCHAR按实际的字符数申请内存时，如果大于255个，需要2bytes，小于等于255个，需要1bytes
• 存储时间（精确到毫秒，如TIMESTAMP(6)）建议使用TIMESTAMP类型，因为TIMESTAMP使用4bytes存储，DATETIME使用8bytes存储。但TIMESTAMP默认值不能为NULL，DATETIME可以为NULL
• 禁止在数据库使用VARBINARY、BLOB存储图片、文件、语音等
• 将过大字段拆分到其他表中，特别注意热表
• 尽量考虑冗余字段，减少表JOIN操作
• 建议字段定义为NOT NULL，字段需要建索引的必须用NOT NULL。默认值，整型为0、字符串为空字符''、
• 必须使用utf8mb4字符集
• 必须使用Innodb存储引擎
• 建议Innodb表时尽量要有主键，而且使用整型作为主键
• 表结构变更需要通知DBA审核

二、索引

• 索引名称必须使用小写字母
• 除主键、唯一键外的普通索引必须按照'ix_表名_字段名'进行命名
• 唯一键必须按照'uk_表名_字段名'进行命名
• 索引中的字段数建议不超过6个
• 不能在区分度低的字段建索引，如性别
• 单张表的索引数尽量不超过6个
• 唯一键不能和主键重复
• 尽量不使用外键功能
• 合理创建联合索引，避免冗余。(x,y,z)相当于(x)、(x,y)、(x,y,z)
• 合理利用覆盖索引
• 不建议使用%前缀模糊查询，比如'%javaee'、'%javaee%'
• ORDER BY、GROUP BY、DISTINCT的字段需要添加在索引的后面
• UPDATE、DELETE语句需要添加WHERE条件进行操作
• 使用EXPLAIN判断SQL语句是否合理使用索引时，尽量避免在extra列出现：Using filesort、Using temporary
• SQL变更需要确认索引是否需要并通知DBA审核

三、SQL语句

• 使用prepared statement，可以提高性能并且避免SQL注入
• 尽量使用合理的SQL语句减少和数据库的交互次数，特别统计类型的查询，比如select count(*)
• 尽量避免子查询、少使用JOIN操作
• 尽量避免大SQL、大事务、大批量
• 避免使用存储过程、触发器、函数等，容易将业务逻辑和数据库耦合在一起
• 避免在SQL语句中进行数学运算或函数运算，容易将业务逻辑和数据库耦合在一起
• SELECT语句只检索需要的字段，不使用SELECT *，不使用INSERT INTO table()
• 使用EXISTS避免使用DISTINCT去重
• WHERE条件中的IN包含的值越少越好
• WHERE条件必须是合适的类型，避免MySQL进行隐式类型转化
• INSERT语句使用batch提交（insert INTO table VALUES(),(),()......），VALUES的个数不应该过多
• UPDATE、DELETE语句不使用LIMIT
• 不使用ORDER BY RAND()，使用其他方法替换
• 建议使用合理的分页方式提高分页的效率