优化

共计 2806 个字符，预计需要花费 8 分钟才能阅读完成。

这篇文章主要介绍“优化 | 重要的 MySQL 开发规范都在这了”，在日常操作中，相信很多人在优化 | 重要的 MySQL 开发规范都在这了问题上存在疑惑，丸趣 TV 小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”优化 | 重要的 MySQL 开发规范都在这了”的疑惑有所帮助！接下来，请跟着丸趣 TV 小编一起来学习吧！

1、默认使用 InnoDB 引擎
【老叶观点】已多次呼吁过了，InnoDB 适用于几乎 99% 的 MySQL 应用场景，而且在 MySQL 5.7 的系统表都改成 InnoDB 了，还有什么理由再死守 MyISAM 呢。

此外，频繁读写的 InnoDB 表，一定要使用具有自增 / 顺序特征的整型作为显式主键。

2、字符集选择 utf-8
【老叶观点】若为了节省磁盘空间，则建议选择 latin1。建议选择 utf- 8 通常是为了所谓的“通用性”，但事实上用户提交的 utf- 8 数据也一样可以以 latin1 字符集存储。

用 latin1 存储 utf- 8 数据可能遇到的麻烦是，如果有基于中文的检索时，可能无法 100% 准确（老叶亲自简单测试常规的中文完检索全不是问题，也就是一般的中文对比是没问题的）。

用 latin1 字符集存储 utf- 8 数据的做法是：在 web 端 (用户端) 的字符集是 utf-8，后端程序也采用 utf- 8 来处理，但 character_set_client、character_set_connection、character_set_results、character_set_database、character_set_server 这几个都是 latin1，且数据表、字段的字符集也是 latin1。或者说数据表采用 latin1，每次连接后执行  SET NAMES LATIN1  即可。

3、InnoDB 表行记录物理长度不超过 8KB

【老叶观点】InnoDB 的 data page 默认是 16KB，基于 B +Tree 的特点，一个 data page 中需要至少存储 2 条记录。因此，当实际存储长度超过 8KB（尤其是 TEXT/BLOB 列）的大列（large column）时会引起“page-overflow 存储”，类似 ORACLE 中的“行迁移”。

因此，如果必须使用大列（尤其是 TEXT/BLOB 类型）且读写频繁的话，则最好把这些列拆分到子表中，不要和主表放在一起存储。如果不太频繁，可以考虑继续保留在主表中。

当然了，如果将 innodb_page_size 选项修改成 8KB，那么行记录物理长度建议不超过 4KB。

【参考】：[MySQL 优化案例]系列 — 优化 InnoDB 表 BLOB 列的存储效率。

4、是否使用分区表
【老叶观点】在一些使用分区表后明显可以提升性能或者运维便利性的场景下，还是建议使用分区表。

比如老叶就在 zabbix 的数据库采用 TokuDB 引擎的前提下，又根据时间维度使用了分区表。这样的好处是保证 zabbix 日常应用不受到影响前提下，方便管理员例行删除过去数据，只需要删除相应分区即可，不需再执行一个非常慢的 DELETE 而影响整体性能。

参考：迁移 Zabbix 数据库到 TokuDB。

5、是否使用存储过程、触发器
【老叶观点】在一些合适的场景下，用存储过程、触发器也完全没问题。

我们以前就是利用存储完成游戏业务逻辑处理，性能上不是问题，而且一旦需求有变更，只需修改存储过程，变更代价很低。我们还利用触发器维护一个频繁更新的表，对这个表的所有变更都将部分字段同步更新到另一个表中（类似物化视图的变相实现），也不存在性能问题。

不要把 MySQL 的存储过程和触发器视为洪水猛兽，用好的话，没有问题的，真遇到问题了再优化也不迟。另外，MySQL 因为没有物化视图，因此视图能不用就尽量少用吧。

6、选择合适的类型
【老叶观点】除了常见的建议外，还有其他几个要点：

6.1、用 INT UNSIGNED 存储 IPV4 地址，用 INET_ATON()、INET_NTOA()进行转换，基本上没必要使用 CHAR(15)来存储。

6.2、枚举类型可以使用 ENUM，ENUM 的内部存储机制是采用 TINYINT 或 SMALLINT（并非 CHAR/VARCHAR），性能一点都不差，记住千万别用 CHAR/VARCHAR 来存储枚举数据。

6.3、还个早前一直在传播的“常识性误导”，建议用 TIMESTAMP 取代 DATETIME。其实从 5.6 开始，建议优先选择 DATETIME 存储日期时间，因为它的可用范围比 TIMESTAMP 更大，物理存储上仅比 TIMESTAMP 多 1 个字节，整体性能上的损失并不大。

6.4、所有字段定义中，默认都加上 NOT NULL 约束，除非必须为 NULL（但我也想不出来什么场景下必须要在数据库中存储 NULL 值，可以用 0 来表示）。在对该字段进行 COUNT()统计时，统计结果更准确（值为 NULL 的不会被 COUNT 统计进去），或者执行 WHERE column IS NULL 检索时，也可以快速返回结果。

6.5、尽可能不要直接 SELECT * 读取全部字段，尤其是表中存在 TEXT/BLOB 大列的时候。可能本来不需要读取这些列，但因为偷懒写成 SELECT * 导致内存 buffer pool 被这些“垃圾”数据把真正需要缓冲起来的热点数据给洗出去了。

8、关于索引
【老叶观点】除了常见的建议外，还有几个要点：

8.1、超过 20 个长度的字符串列，最好创建前缀索引而非整列索引（例如：ALTER TABLE t1 ADD INDEX(user(20))），可以有效提高索引利用率，不过它的缺点是对这个列排序时用不到前缀索引。前缀索引的长度可以基于对该字段的统计得出，一般略大于平均长度一点就可以了。

8.2、定期用 pt-duplicate-key-checker 工具检查并删除重复的索引。比如 index idx1(a, b) 索引已经涵盖了 index idx2(a)，就可以删除 idx2 索引了。

8.3、有多字段联合索引时，WHERE 中过滤条件的字段顺序无需和索引一致，但如果有排序、分组则就必须一致了。

比如有联合索引 idx1(a, b, c)，那么下面的 SQL 都可以完整用到索引：

[MySQL FAQ]系列 — 从 MyISAM 转到 InnoDB 需要注意什么

[MySQL FAQ]系列 — 为什么 InnoDB 表要建议用自增列做主键

小谈 MySQL 字符集

[MySQL 优化案例]系列 — 优化 InnoDB 表 BLOB 列的存储效率

迁移 Zabbix 数据库到 TokuDB

[MySQL 优化案例]系列 — 分页优化

[MySQL 优化案例]系列 — RAND()优化

[MySQL FAQ]系列 — 什么情况下会用到临时表

[MySQL FAQ]系列 — EXPLAIN 结果中哪些信息要引起关注

到此，关于“优化 | 重要的 MySQL 开发规范都在这了”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注丸趣 TV 网站，丸趣 TV 小编会继续努力为大家带来更多实用的文章！