配置规范

  • MySQL数据库默认使用InnoDB存储引擎。

  • 保证字符集设置统一，MySQL数据库相关系统、数据库、表的字符集使都用UTF8，应用程序连接、展示等可以设置字符集的地方也都统一设置为UTF8字符集。

注：UTF8格式是存储不了表情类数据，需要使用UTF8MB4，可在MySQL字符集里面设置。在8.0中已经默认为UTF8MB4，可以根据公司的业务情况进行统一或者定制化设置。

  • MySQL数据库的事务隔离级别默认为RR（Repeatable-Read），建议初始化时统一设置为RC（Read-Committed）。

  • 数据库中的表要合理规划，控制单表数据量，对于MySQL数据库来说，建议单表记录数控制在2000W以内。

  • MySQL实例下，数据库、表数量尽可能少；数据库一般不超过50个，每个数据库下，数据表数量一般不超过500个（包括分区表）。

建表规范

  • InnoDB禁止使用外键约束，可以通过程序层面保证。

  • 存储精确浮点数必须使用DECIMAL替代FLOAT和DOUBLE。

  • 整型定义中无需定义显示宽度，比如：使用INT，而不是INT(4)。

  • 不建议使用ENUM类型，可使用TINYINT来代替。

  • 尽可能不使用TEXT、BLOB类型，如果必须使用，建议将过大字段或是不常用的描述型较大字段拆分到其他表中；另外，禁止用数据库存储图片或文件。

  • 存储年时使用YEAR(4)，不使用YEAR(2)。

  • 建议字段定义为NOT NULL。

命名规范

  • 库、表、字段全部采用小写。

  • 库名、表名、字段名、索引名称均使用小写字母，并以“_”分割。

  • 库名、表名、字段名建议不超过12个字符。（库名、表名、字段名支持最多64个字符，但为了统一规范、易于辨识以及减少传输量，统一不超过12字符）

  • 库名、表名、字段名见名知意，不需要添加注释。

对象中文名称 对象英文全称 MySQL对象简写
视图 view view_
函数 function func_
存储过程 procedure proc_
触发器 trigger trig_
普通索引 index idx_
唯一索引 unique index uniq_
主键索引 primary key pk_

索引规范

  • 索引建议命名规则：idx_col1_col2[_colN]、uniq_col1_col2[_colN]（如果字段过长建议采用缩写）。

  • 索引中的字段数建议不超过5个。

  • 单张表的索引个数控制在5个以内。

  • InnoDB表一般都建议有主键列，尤其在高可用集群方案中是作为必须项的。

  • 建立复合索引时，优先将选择性高的字段放在前面。

  • UPDATE、DELETE语句需要根据WHERE条件添加索引。

  • 不建议使用%前缀模糊查询，例如LIKE “%weibo”，无法用到索引，会导致全表扫描。

  • 合理利用组合索引，例如：SELECT email,uid FROM user_email WHERE uid=xx，如果uid不是主键，可以创建组合索引idx_uid_email(uid,email)来提高查询效率。

  • 避免在索引字段上使用函数，否则会导致查询时索引失效。

应用规范

  • 避免使用存储过程、触发器、自定义函数等，容易将业务逻辑和DB耦合在一起，后期做分布式方案时会成为瓶颈。

  • 考虑使用UNION ALL，减少使用UNION，因为UNION ALL不去重，而少了排序操作，速度相对比UNION要快，如果没有去重的需求，优先使用UNION ALL。

  • 考虑使用limit N，少用limit M，N，特别是大表或M比较大的时候。

  • 减少或避免排序，如：group by语句中如果不需要排序，可以增加order by null。

  • 统计表中记录数时使用COUNT()，而不是COUNT(primary_key)和COUNT(1)；InnoDB表避免使用COUNT()操作，计数统计实时要求较强可以使用Memcache或者Redis，非实时统计可以使用单独统计表，定时更新。

  • 做字段变更操作（modify column/change column）的时候必须加上原有的注释属性，否则修改后，注释会丢失。

  • 使用prepared statement可以提高性能并且避免SQL注入。

  • SQL语句中IN包含的值不应过多。

  • UPDATE、DELETE语句一定要有明确的WHERE条件。

  • WHERE条件中的字段值需要符合该字段的数据类型，避免MySQL进行隐式类型转化。

  • SELECT、INSERT语句必须显式的指明字段名称，禁止使用SELECT * 或是INSERT INTO table_name values()。

  • INSERT语句使用batch提交（INSERT INTO table_name VALUES(),(),()……），values的个数不应过多