MySQL调优指南（持续更新）

MySQL数据库优化通常有如下四个维度：硬件、系统配置、数据库表结构、SQL及索引。

• 从优化成本来看：硬件>系统配置>数据库表结构>SQL及索引。
• 从优化效果来看：硬件<系统配置<数据库表结构<SQL及索引。

要想对MySQL进行优化，我们需要知道MySQL各组件之间如何协同工作以及MySQL是如何优化和执行查询的。

MySQL逻辑架构

MySQL逻辑架构整体分为三层，最上层为客户端层，并非MySQL所独有，诸如：连接处理、授权认证、安全等功能均在这一层处理。

MySQL大多数核心服务均在中间这一层，包括查询解析、分析、优化、缓存、内置函数(比如：时间、数学、加密等函数)。所有的跨存储引擎的功能也在这一层实现：存储过程、触发器、视图等。

最下层为存储引擎，其负责MySQL中的数据存储和提取。和Linux下的文件系统类似，每种存储引擎都有其优势和劣势。中间的服务层通过API与存储引擎通信，这些API接口屏蔽了不同存储引擎间的差异。

MySQL查询过程

1. 客户端发送一条查询给服务器。

2. 服务器先检查查询缓存，如果命中了缓存，则立刻返回存储在缓存中的结果.否则,进入下一个阶段。

3. 服务器进行SQL解析.预处理,再由优化器生成对应的执行计划。

4. MySQL根据优化器生成的执行计划,调用存储引擎的API来执行查询。

5. 将结果返回给客户端。

关于查询缓存的说明：

在解析一个查询语句前,如果缓存是打开的,那么MySQL会优先检查这个查询是否命中查询缓存中的数据。

如果没有命中,则进入下一阶段的处理。

如果命中查询缓存,则会检查用户的权限。

如果权限没有问题,MySQL会跳过其他阶段,直接拿数据返回给客户端。

下面我们将从硬件、系统配置、表设计、sql语句等方面来优化MySQL。

硬件层面优化

• CPU：选择多核的CPU，主频高的CPU。
• 内存：选择更大的内存。
• 磁盘：选择更快的转速、RAID、阵列卡，或者在条件允许的情况下，使用SSD。
• 网络环境：尽量部署在局域网，使用光缆、千/万兆网等提供网络，通过双网线提供冗余、使用多端口绑定监听。

系统配置优化

操作系统配置优化

• 使用64位操作系统，更好的使用大内存。
• 优化内核参数。
• 加大文件描述符限制。
• 文件系统选择：XFS,JFS,EXT3/EXT4（文件系统的选择对确保数据的安全性很重要）。

Mysql软件优化

• 开启mysql复制，实现读写分离、负载均衡，将读的负载分摊到多个从服务器上，提高服务器的处理能力。
• 使用推荐的GA版本(正式发布的版本)，提升性能。
• 利用分区新功能进行大数据的数据拆分。

Mysql配置优化

注：全局参数一经设置，随服务器启动预占用资源。

• wait_time_out参数：线程连接的超时时间，尽量不要设置很大，推荐10s。
• thread_concurrency参数：线程并发利用数量( 在5.7.2版本的mysql中被移除)。在InnoDB中，我们可以通过设置参数innodb_thread_concurrency参数限制线程的数量。

innodb_thread_concurrency的使用建议

在官方文档上，对于innodb_thread_concurrency的使用，也给出了一些建议，如下：

如果一个工作负载中，并发用户线程的数量小于64，建议设置innodb_thread_concurrency=0；

如果工作负载一直较为严重甚至偶尔达到顶峰，建议先设置innodb_thread_concurrency=128，并通过不断的降低这个参数，96, 80, 64等等，直到发现能够提供最佳性能的线程数，例如，假设系统通常有40到50个用户，但定期的数量增加至60，70，甚至200。你会发现，性能在80个并发用户设置时表现稳定，如果高于这个数，性能反而下降。在这种情况下，建议设置innodb_thread_concurrency参数为80，以避免影响性能。

• read_buffer_size参数：全表扫描时为查询预留的缓冲大小，根据select_scan判断。
• tmp_table_size参数：临时内存表的设置，如果超过设置就会转化成磁盘表， 根据参数(created_tmp_disk_tables)判断。

Mysql表设计优化

存储引擎的选择

• Myisam：适合并发量不大，读多写少，而且都能很好的用到索引，且sql语句比较简单的应用，比如，数据仓库。
• Innodb：适合并发访问大，写操作比较多，有外键、事务等需求的应用，系统内存较大。

命名规则

• 采用多数开发语言命名规则，比如MyAdress(驼峰原则)
• 采用多数开源思想命名规则，比如my_address，通常采用下划线这种命名规则。
• 避免随便命名，最好能够见名知意。

字段类型选择

根据需求选择合适的字段类型，在满足需求的情况下字段类型尽可能小。只分配满足需求的最小字符数，不要太慷慨。

原因：更小的字段类型和更小的字符数将占用更少的内存，占用更少的磁盘空间，占用更少的磁盘IO，以及占用更少的带宽。

编码选择

• 单字节-latin1
• 多字节-utf8(汉字占3个字节，英文字母占用一个字节)
• 如果含有中文字符的话最好都统一采用utf8类型，避免乱码的情况发生。

主键选择

注：这里说的主键设计主要是针对INNODB引擎。

• 能唯一的表示行。
• 显式的定义一个数值类型自增字段的主键，这个字段可以仅用于做主键，不做其他用途。
• MySQL主键应该是单列的，以便提高连接和筛选操作的效率。
• 主键字段类型尽可能小，能用SMALLINT就不用INT，能用INT就不用BIGINT。
• 尽量保证不对主键字段进行更新修改，防止主键字段发生变化，引发数据存储碎片，降低IO性能。
• MySQL主键不应包含动态变化的数据，如时间戳、创建时间列、修改时间列等。
• MySQL主键应当由自动生成。
• 主键字段放在数据表的第一顺序。

通常，我们推荐采用数值类型做主键并采用auto_increment属性让其自动增长。

Mysql语句层面优化

• 性能差的读语句，在innodb中统计行数,建议另外弄一张统计表，采用myisam，定期做统计。一般对统计的数据不会要求太精准的情况下适用。
• 尽量不要在数据库中做运算。
• 避免 负向查询 和 %前缀模糊查询。
• 不在索引列做运算或者使用函数。
• 不要在生产环境程序中使用select * from的形式查询数据。只查询需要使用的列。
• 查询时，尽可能使用limit减少返回的行数，减少数据传输时间和带宽浪费。
• where子句尽可能避免对查询列使用函数，因为对查询列使用函数用不到索引。
• 避免隐式类型转换，例如字符型一定要用’’，数字型一定不要使用’’。
• 所有的SQL关键词用大写，养成良好的习惯，避免SQL语句重复编译造成系统资源的浪费。
• 联表查询的时候，记得把小结果集放在前面，遵循小结果集驱动大结果集的原则。
• 开启慢查询，定期用explain优化慢查询中的SQL语句。
• 拆分大的delete或insert语句。

总结

从上面看出，MYSQL主要从以下几方面进行优化：

• 表设计：合理的存储引擎，字段类型，范式与逆范式
• 功能：合适的索引，缓存，分区分表。
• 架构：采用主从复制，读写分离，负载均衡。
• 合理SQL：测试及对比同一功能不同sql的查询效率，根据过往的经验编写高效的sql。