一、表设计
- 数据库对象名称不要超过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(),使用其他方法替换
- 建议使用合理的分页方式提高分页的效率