https://segmentfault.com/a/1190000040598165

1. 优化步骤概览

1.1. 查看 SQL 执行频率

1.2. 定位低效率执行 SQL

1.3. explain 分析执行计划

1.4. show profile 查看 SQL 耗时

1.5. trace 分析优化器执行计划

2. 执行频率

2.1. show status like ‘Com_______’

1	`show status like 'Com_______';`

当前连接

全局

3. 定位低效 SQL

3.1. show processlist

慢查询日志在查询结束以后才记录，所以在应用反映执行效率出现问题的时候查询慢查询日志并不能定位问题，可以使用 show processlist 命令查看当前 MySQL 在进行的线程，包括线程的状态、是否锁表等，可以实时地查看 SQL 的执行情况，同时对一些锁表操作进行优化。

3.2. 慢查询日志

4. explain- 查看执行计划

4.1. explain

4.2. type

查询执行的类型，描述了查询是如何执行的。所有值的顺序从最优到最差排序为：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

常见的几种类型具体含义如下：

system：如果表使用的引擎对于表行数统计是精确的 (如：MyISAM)，且表中只有一行记录的情况下，访问方法是 system ，是 const 的一种特例。
const：表中最多只有一行匹配的记录，一次查询就可以找到，常用于使用主键或唯一索引的所有字段作为查询条件。
eq_ref：当连表查询时，前一张表的行在当前这张表中只有一行与之对应。是除了 system 与 const 之外最好的 join 方式，常用于使用主键或唯一索引的所有字段作为连表条件。
ref：使用普通索引作为查询条件，查询结果可能找到多个符合条件的行。
index_merge：当查询条件使用了多个索引时，表示开启了 Index Merge 优化，此时执行计划中的 key 列列出了使用到的索引。
range：对索引列进行范围查询，执行计划中的 key 列表示哪个索引被使用了。
index：查询遍历了整棵索引树，与 ALL 类似，只不过扫描的是索引，而索引一般在内存中，速度更快。
ALL：全表扫描。

4.3. extra

这列包含了 MySQL 解析查询的额外信息，通过这些信息，可以更准确的理解 MySQL 到底是如何执行查询的。常见的值如下：

Using FileSort：通过对返回数据进行排序，也就是通常说的 filesort 排序，所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序。
Using temporary：MySQL 需要创建临时表来存储查询的结果，常见于 ORDER BY 和 GROUP BY。
Using index：表明查询使用了覆盖索引，不用回表，查询效率非常高。
Using index condition：表示查询优化器选择使用了索引条件下推这个特性。
Using where：表明查询使用了 WHERE 子句进行条件过滤。一般在没有使用到索引的时候会出现。
Using join buffer (Block Nested Loop)：连表查询的方式，表示当被驱动表的没有使用索引的时候，MySQL 会先将驱动表读出来放到 join buffer 中，再遍历被驱动表与驱动表进行查询。

这里提醒下，当 Extra 列包含 Using filesort 或 Using temporary 时，MySQL 的性能可能会存在问题，需要尽可能避免。

5. profiles- 查看耗时

https://segmentfault.com/a/1190000023470437

Mysql 从 5.0.37 版本开始增加了对 show profiles 和 show profile 语句的支持。show profiles 能够在做 SQL 优化时帮助我们了解时间都耗费到哪里去了。

通过 have_profiling 参数，能够看到当前 MySQL 是否支持 profile：

select version();  -- 先查看mysql版本是否高于5.0.37
show variables like 'have_profiling'; -- 首先查看是否支持该功能
set profiling=on; -- 默认是关闭的，开启该功能
show variables like 'profiling%'; -- 查看开启状态。15表示历史缓存sql的个数

15524884zzz01999

执行完上述命令之后，再执行 show profiles 指令，来查看 SQL 语句执行的耗时：

5.1. show profiles

15png9017940

5.2. show profile for query x

1552489aaa053763

TIP ：
Sending data 状态表示 MySQL 线程开始访问数据行并把结果返回给客户端，而不仅仅是返回个客户端。由于在 Sending data 状态下，MySQL 线程往往需要做大量的磁盘读取操作，所以经常是整个查询中耗时最长的状态。

在获取到最消耗时间的线程状态后，MySQL 支持进一步选择 all、cpu、block io 、context switch、page faults 等明细类型类查看 MySQL 在使用什么资源上耗费了过高的时间。例如，选择查看 CPU 的耗费时间：

1552489aaa671119

6. trace- 分析优化器执行计划

MySQL5.6 提供了对 SQL 的跟踪 trace, 通过 trace 文件能够进一步了解为什么优化器选择 A 计划, 而不是选择 B 计划。

打开 trace ，设置格式为 JSON，并设置 trace 最大能够使用的内存大小，避免解析过程中因为默认内存过小而不能够完整展示。

1 2	`SET optimizer_trace="enabled=on",end_markers_in_json=on; set optimizer_trace_max_mem_size=1000000;`

执行 SQL 语句：

1	`select * from tb_item where id < 4;`

最后，检查 information_schema.optimizer_trace 就可以知道 MySQL 是如何执行 SQL 的：

1	`select * from information_schema.optimizer_trace\G;`

7. 优化场景 (优化内容)⭐️🔴

❕ ^62llgx

7.1. 概览

7.2. 索引使用

MySQL-7、索引原理

7.2.1. 查看使用情况

1 2	`show status like 'Handler_read%'; show global status like 'Handler_read%';`

7.2.2. 防止索引失效

MySQL-7、索引原理

7.3. 主键优化

7.3.1. 页分裂

顺序插入不会出现页分裂，乱序插入会发生页分裂

页分裂过程如下：

7.3.2. 页合并

7.3.3. 主键设计原则

主键要短：一般情况下，主键索引 (聚集索引) 只有 1 个，二级索引有多个。而二级索引的叶子节点存储的就是主键，如果主键长度很长二级索引又很多的情况下，会占据大量的磁盘空间，同时增加 IO，影响性能
主键要自增，可防止页分裂
尽量不要使用 UUID 或者身份证这种，既不能自增又很长的字符串做主键
避免对主键进行修改

7.4. 大批量插入数据

主键顺序插入
关闭唯一性校验
手动提交事务
load 命令插入

7.5. 优化 insert 语句

合并插入，; 加在最后
手动提交
主键顺序插入

7.6. 优化 update 语句

where 条件中必须有索引，且不能失效，否则就会使用表锁，影响并发效率

7.7. 优化 order by 语句

7.7.1. using filesort

通过对返回数据进行排序，也就是通常说的 filesort 排序，所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序。

7.7.1.1. 产生 - 与联合索引失效原因类似

7.7.1.1.1. 跳过联合索引中间值

7.7.1.1.2. 索引顺序打乱

7.7.1.1.3. 与索引默认顺序不一致

7.7.1.1.4. 多个相等条件也是范围查询

7.7.1.2. 分类

https://www.jianshu.com/p/cb9c2edd7f70

通过创建合适的索引，能够减少 Filesort 的出现，但是在某些情况下，条件限制不能让 Filesort 消失，那就需要加快 Filesort 的排序操作。对于 Filesort ， MySQL 有两种排序算法：

7.7.1.2.1. 双路排序 (两次扫描算法)

从索引 name 字段中，查询到满足条件的数据 name=‘jarye’条件主键的 id
根据主键 id，获取排序的字段和主键 id 缓存到 sort buffer 中
重复执行 1,2 步骤流程
对 sort buffer 中的数据实现排序
根据排序好的主键 id 和 position，在从原来表中根据 id 查询数据给客户端。

该操作可能会导致大量随机 I/O 操作

7.7.1.2.2. 单路排序 (一次扫描算法)

从索引 name 字段中，查询到满足条件的数据 name=’jarye’ 条件主键的 id
根据主键 id 取出整行的数据，缓存到 sort buffer 中
重复执行 1,2 步骤流程
对 sort buffer 中的数据实现排序给客户端

一次性取出满足条件的所有字段，然后在排序区 sort buffer 中排序后直接输出结果集。排序时内存开销较大，但是排序效率比两次扫描算法要高。

MySQL 通过比较系统变量 max_length_for_sort_data 的大小和 Query 语句取出的字段总大小，来判定是否那种排序算法，如果 max_length_for_sort_data 更大，那么使用第二种优化之后的算法；否则使用第一种。

可以适当提高 sort_buffer_size 和 max_length_for_sort_data 系统变量，来增大排序区的大小，提高排序的效率。

1556338aaa367593

7.7.1.3. 对比

从磁盘中读取查询需要的所有列，按照 order by 列在 sort_buffer(排序缓存) 缓冲区对他们进行排序，然后扫描排序后的列表输出。因为单路排序效率更快，避免了二次读取数据，把随机 IO 变成了顺序 IO，但是会使用更多的空间。

其实对比两个排序模式，单路排序会把所有需要查询的字段都放到 sort buffer 中，而双路排序只会把主键和需要排序的字段放到 sort buffer 中进行排序，然后再通过主键回到原表查询需要的字段。

7.7.1.4. 优化

至于 mysql 优化器使用双路排序还是单路排序是有自己的算法判断的，如果查询的列字段大于 max_length_for_sort_data 变量，则会使用双路排序，反之则会使用单路排序，单路排序速度是更快的，不过比较占据内存，如果在内存空间允许的情况下想要使用单路排序的话，可以增加 max_length_for_sort_data 变量的大小，max_length_for_sort_data 变量默认为 1024 字节。
如果全部使用 sort_buffer 内存排序一般情况下效率会高于磁盘文件排序，但不能因为这个就随便增大 sort_buffer(默认 1M)，mysql 很多参数设置都是做过优化的，不要轻易调整。

7.7.2. using index

❕ ^2wm6nm

了解了 MySQL 的排序方式，优化目标就清晰了：尽量减少额外的排序，通过索引直接返回有序数据。where 条件和 Order by 使用相同的索引，并且 Order By 的顺序和索引顺序相同，都是升序或者都是降序。否则肯定需要额外的操作，这样就会出现 FileSort。

7.8. 优化 group by 语句

由于 GROUP BY 实际上也同样会进行排序操作，而且与 ORDER BY 相比，GROUP BY 主要只是多了排序之后的分组操作。当然，如果在分组的时候还使用了其他的一些聚合函数，那么还需要一些聚合函数的计算。所以，在 GROUP BY 的实现过程中，与 ORDER BY 一样也可以利用到索引。

如果查询包含 group by 但是用户想要避免排序结果的消耗，则可以执行 order by null 禁止排序。

也可以创建索引进行优化。