MySQL 优化

一. 定位慢查询

我们将超过指定时间的SQL语句查询称为“慢查询”。MySQL 的慢查询日志用来记录在 MySQL 中响应时间超过参数 long_query_time（单位秒，默认值 10）设置的值并且扫描记录数不小于 min_examined_row_limit（默认值 0）的语句.定位慢查询有如下两种方式

• 查看慢查询日志确定已经执行完的慢查询
• show processlist 查看正在执行的慢查询

1.1 慢查询日志

• show global variables like 'slow_query_log' 查看是否开启了慢查询
• show global variables like 'datadir' 慢查询日志的路径默认是 MySQL 的数据目录
• show global variables like 'slow_query_log_file' 查看慢查询日志名
• show global variables LIKE 'long_query_time' 查看慢查询时间阈值，单位是秒（s）
• set global slow_query_log = on 设置开启慢查询
• set global long_query_time = 1 设置慢查询时间阀值

慢查询日志的中字段的含义

• Time: 慢查询发生的时间
• User@Host: 客户端用户和IP
• Query_time: 查询时间
• Lock_time: 锁边时间
• Rows_sent: 语句返回的行数
• Rows_examined: 语句执行期间从存储引擎读取的行数

1.2 show processlist

值得注意的是，这里和上面提到的慢查询日志最大的区别是，慢查询日志记录的是已经执行完的查询，而这个命令是正在执行的慢查询，我们可以使用show full processlist 展示全部内容，而不使用，在info字段只显示每个语句的前100个字符。查询出字段的含义如下

• Id : 线程号
• User ：操作用户
• Host ：操作主机IP位置
• db : 操作数据库
• Command ：执行类型
• Time : 操作状态
• State：操作状态
• Info : 操作语句
  这里需要注意，show processlist只应为“show processlist”线程本身报告“State = null”.

1.3 分析慢查询

分析慢查询一般有3中方式，explain、show profile 和 trace，三者各有优缺点，不过一般最常用的还是 explain。

1.3.1 使用 explain 分析

Explain 可以获取 MySQL 中 SQL 语句的执行计划，比如语句是否使用了关联查询、是否使用了索引、扫描行数等。可以帮我们选择更好地索引和写出更优的 SQL 。使用方法也很简单，只需要在执行的SQL语句前，加上 explain 即可
例 EXPLAIN SELECT * from role where id < 1000;
下面对返回的字段进行解释一下

• id：查询编号
• select_type：查询类型，显示本行是简单查询还是复杂查询
• table：设计到的表
• partitions：匹配的分区：查询将匹配记录所在的分区。仅当使用 partition 关键字时才显示该列。对于非分区表，该值为 NULL。
• type：本次查询的表连接类型
• possible_keys：可能选择的索引
• key：实际选择的索引
• key_len：被选择的索引长度：一般用于判断联合索引有多少列被选择了
• ref：与索引比较的列
• rows：预计需要扫描的行数，对 InnoDB 来说，这个值是估值，并不一定准确
• filtered：按条件筛选的行的百分比
• Extra: 附加信息

接下来对上面字段中比较重要列包含的值大致列一下

select_type	解释
SIMPLE	简单查询（不使用关联查询或子查询）
PRIMARY	如果包含关联查询或者子查询，则最外层的查询部分标记为 primary
UNION	联合查询中第二个及后面的查询
DEPENDENT UNION	满足依赖外部的关联查询中第二个及以后的查询
UNION RESULT	联合查询的结果
SUBQUERY	子查询中的第一个查询
DEPENDENT SUBQUERY	子查询中的第一个查询，并且依赖外部查询
DERIVED	用到派生表的查询
MATERIALIZED	被物化的子查询
UNCACHEABLE SUBQUERY	SUBQUERY 一个子查询的结果不能被缓存，必须重新评估外层查询的每一行
UNCACHEABLE UNION	关联查询第二个或后面的语句属于不可缓存的子查询

type	解释
system	查询对象表只有一行数据，且只能用于 MyISAM 和 Memory 引擎的表，这是最好的情况
const	基于主键或唯一索引查询，最多返回一条结果
eq_ref	表连接时基于主键或非 NULL 的唯一索引完成扫描
ref	基于普通索引的等值查询，或者表间等值连接
fulltext	全文检索
ref_or_null	表连接类型是 ref，但进行扫描的索引列中可能包含 NULL 值
index_merge	利用多个索引
unique_subquery	子查询中使用唯一索引
index_subquery	子查询中使用普通索引
range	利用索引进行范围查询
index	全索引扫描
ALL	全表扫描

Extra 常见的值	解释
Using filesort	将用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序
Using temporary	需要创建一个临时表来存储结构，通常发生对没有索引的列进行 GROUP BY 时
Using index	使用覆盖索引
Using where	使用 where 语句来处理结果
Impossible WHERE	对 where 子句判断的结果总是 false 而不能选择任何数据
Using join buffer (Block Nested Loop)	关联查询中，被驱动表的关联字段没索引
Using index condition	先条件过滤索引，再查数据
Select tables optimized away	使用某些聚合函数（比如 max、min）来访问存在索引的某个字段是

1.3.2 show profile 分析慢查询

explain用起来简单易懂，但却不怎么能用来确定到底慢在哪个环节，通过 profile，能够更清楚地了解 SQL 执行过程的资源使用情况，能让我们知道到底慢在哪个环节。
在真正使用之前，我们需要确定
数据库版本是否支持profile,确定是否开启了profile,执行，查看执行完SQL的query id;通过query id 查看SQL的每个状态及耗时时间。
select @@have_profiling; 是否支持profile
select @@profiling; 判断 profiling 参数是否关闭（默认 profiling 是关闭的）
set profiling=1; 开启
show profiles 确定执行过的 SQL 的 query id：
show profile for query 1; 查看执行过的 SQL 每个状态和消耗时间

1.3.3 trace 分析 SQL 优化器

通过trace，我们能够进一步了解为什么优化器选择A执行计划而不是选择B执行计划，或者知道某个排序使用的排序模式，帮助我们更好地理解优化器行为。
如果需要使用，先开启 trace，设置格式为 JSON，再执行需要分析的 SQL，最后查看 trace 分析结果（在 information_schema.OPTIMIZER_TRACE 中）开启该功能，会对 MySQL 性能有所影响，因此只建议分析问题时临时开启。

使用的大致步骤应该分为：

• set session optimizer_trace="enabled=on",end_markers_in_json=on; 开启， optimizer_trace=“enabled=on” 表示开启 trace；end_markers_in_json=on 表示 JSON 输出开启结束标记
• 执行SQL语句
• SELECT * FROM information_schema.OPTIMIZER_TRACE\G 查看分析
• set session optimizer_trace="enabled=off"; 关闭

TRACE 字段中整个文本大致分为三个过程。

• 准备阶段：对应文本中的 join_preparation
• 优化阶段：对应文本中的 join_optimization
• 执行阶段：对应文本中的 join_execution

1.3.4 对比一下三种分析 SQL 方法的特点：

• explain：获取 MySQL 中 SQL 语句的执行计划，比如语句是否使用了关联查询、是否使用了索引、扫描行数等；
• profile：可以清楚了解到SQL到底慢在哪个环节；
• trace：查看优化器如何选择执行计划，获取每个可能的索引选择的代价。

二. 为什么有索引还是很慢

索引就好比我们读书时候的目录，如果想要查找书中的某个内容，最好的方式就是先查目录，然后根据目录给出的信息缩减选项，以达到快速查询的目的。而为什么明明有索引，真正的查询却还是很慢，其大多是因为没走索引，而为什么会不走索引呢，主要有以下几个原因。

2.1 函数操作

对条件字段做函数操作走不了索引。索引树中存储的是列的实际值和主键值，做函数操作将无法定位到索引树中的值，因此放弃走索引，而选择全表扫描

2.2 隐式转换

如果字段类型是varchar类型，查询条件中却是int型的，则可能导致 MySQL 内部会先把varchar转换成int型，再去做判断，则又回到了上面那个条件

2.3 模糊查询

通配符在前面会导致不走索引。这是因为Mysql索引有最左原则，所以通过 like '%XX%'查询的时候一定会造成索引失效。而什么是最左匹配原则，举个例子来讲，比如要比较一个字符串。http 与 httpd，我们从最左边第一个字符开始比较，第一个字符相同后，再比较第二个字符，以此类题，且无法跳过。正是由于这个原因，这种模糊查询是全匹配，也就用不到索引了

2.4 范围查询

明明范围查询的条件字段有索引，但是却全表扫描了。为什么会如此呢？优化器会根据检索比例、表大小、I/O块大小等进行评估是否使用索引。比如单次查询的数据量过大，优化器将不走索引。

2.5 计算操作

对索引字段做运算将使用不了索引，跟函数操作类似。

三 Order By，Group By 优化

在介绍优化语句之前，需要对原理有些大致了解。MySQL的排序方式分为两种

1. 通过有序索引直接返回有序数据
2. 通过Filesort进行的排序

我们可以通过使用 explain 来查看Extra 字段，来确定使用哪种方式的，如果是Using index 则表示是前者，如果是Using filesort，则是后者。不过Filesort 并不一定是在磁盘文件中进行排序的，也有可能在内存中排序，内存排序还是磁盘排序取决于排序的数据大小和 sort_buffer_size 配置的大小，如果排序的数据大小小于“sort_buffer_size” 则是内存排序，反之则为磁盘排序。

而至于优化方案，则就有些老生常谈了。大致如下

1. 通过添加合适索引
2. 去掉不必要的返回字段
3. 调整参数：主要是 max_length_for_sort_data 和 sort_buffer_size
4. 避免几种无法利用索引排序的情况

四. 关联查询以及分页查询优化

4.1 关联查询的优化

1. 关联字段添加索引
2. 小表做驱动表
3. 使用临时表

可以拓展了解下 NLJ 、BNL、和 BKA 这几种 join 算法的原理

4.2 分页查询的优化

1. 根据自增且连续主键排序的分页查询，通过主键来区分分页
   这种写法必须满足两个条件，否则会有其他问题：“主键连续且自增；结果是按照主键排序的”

2. 查询根据非主键字段排序的分页查询
   让排序和分页操作先查出主键，然后根据主键查到对应的记录

五. 优化数据导入

当数据量比较大的时候，导入时间非常长，那怎么可以减少这个时间呢。

5.1 一次插入多行的值

插入行所需的时间由以下因素决定（参考MySQL 5.7参考手册：8.2.4.1优化INSERT语句）

• 连接：30%
• 向服务器发送查询：20%
• 解析查询：20%
• 插入行：10% * 行的大小
• 插入索引：10% * 索引数
• 结束：10%

大部分时间耗费在客户端与服务端通信的时间，因此可以使用 insert 包含多个值来减少客户端和服务器之间的通信

5.2 关闭自动提交

Autocommit 开启时会为每个插入执行提交。和一次插入多行能提高批量插入速度的原因一样，因为批量导入大部分时间耗费在客户端与服务端通信的时间，所以多条 insert 语句合并提交可以减少客户端与服务端通信的时间，并且合并提交还可以减少数据落盘的次数。

5.3 参数调整

影响MySQL写入速度的主要两个参数：innodb_flush_log_at_trx_commit、sync_binlog。

innodb_flush_log_at_trx_commit：控制重做日志刷新到磁盘的策略，有0 、1和2三种值

• 0：master线程每秒把redo log buffer写到操作系统缓存，再刷到磁盘；
• 1：每次提交事务都将redo log buffer写到操作系统缓存，再刷到磁盘；
• 2：每次事务提交都将redo log buffer写到操作系统缓存，由操作系统来管理刷盘。

sync_binlog：控制binlog的刷盘时机，可配置0、1或者大于1的数字。

• 0：二进制日志从不同步到磁盘，依赖OS刷盘机制；
• 1：二进制日志每次提交都会刷盘；
  n(n>1) : 每n次提交落盘一次。

innodb_flush_log_at_trx_commit设置为0、同时sync_binlog设置为0时，写入数据的速度是最快的。如果对数据库安全性要求不高(比如你的测试环境)，可以尝试都设置为0后再导入数据，能大大提升导入速度。

原文大多出自慕课网专栏《一线数据库工程师带你深入理解 MySQL》，有希望了解更多细节的，可以前去购买