参考文章：MySQL千万级大表优化

1. 数据库改进方案：

• 规范设计
• 业务层优化
• 架构层优化
• 数据库优化
• 管理优化

黑格尔说：秩序是自由的第一条件。在分工协作的工作场景中尤其重要，否则团队之间互相牵制太多，问题多多。

我想提到如下的几个规范，其实只是属于开发规范的一部分内容，可以作为参考。

规范的本质不是解决问题，而是有效杜绝一些潜在问题，对于千万级大表要遵守的规范，我梳理了如下的一些细则，基本可以涵盖我们常见的一些设计和使用问题。

比如表的字段设计不管三七二十一，都是 varchar（500），其实是很不规范的一种实现方式，我们来展开说一下这几个规范。

2. 配置规范：

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

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

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

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

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

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

3. 建表规范：

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

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

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

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

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

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

• 建议字段定义为 NOT NULL。

• 建议 DBA 提供 SQL 审核工具，建表规范性需要通过审核工具审核后。

4. 命名规范：

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

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

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

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

4.1 表命名规范总结

对于对象命名规范的一个简要总结如下表所示，供参考：

5. 索引规范：

• 索引建议命名规则：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）来提高查询效率。

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

• 确认索引是否需要变更时要联系 DBA。

6. 应用规范：

• 避免使用存储过程、触发器、自定义函数等，容易将业务逻辑和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 的个数不应过多。