Mysql调优攻略大全（建议收藏）

0.前言

建议与我的八股专栏的jvm八股配合使用。觉得本贴有用的收藏点个赞评论下送朵花什么的吧。下面我会让你看看什么是传说中的mysql调优（八股面试吹b版）。

打个小广告

我的架构设计专栏：https://www.nowcoder.com/creation/manager/columnDetail/0ybvLm

我的八股专栏：https://www.nowcoder.com/creation/manager/columnDetail/j8ZZk0

内有详细苍穹外卖话术哦！

为什么我要开个八股专栏？

一方面有感于现在面试找工作对八股的要求之高，一方面有感于市面上的一些八股资料又乱又复杂难懂，所以我决定自己结合各种市面上优秀的博客、gpt，牛客上的面经和我自己的理解，整合出一份八股资料，我的目标是：让八股文成为真正简单易懂的八股，知识点丰富且浅显易懂，不追求过分的深入，但一定重点层次分明(如果想深入了解知识点的话还是建议自己认真看看javaguide和小林coding，但其实我就是看了他们的全部博客取精华精炼部分融入我的专栏。。。)，一句话就是我的八股专栏主打一个面试速成，一点超纲的知识点都不会有，因为我自己也用这份笔记准备秋招。。。

1.怎么定位慢查询？

2种方法·：

运维监控工具Slywalking

我们当时做压测的时候有的接口非常的慢，接口的响应时间超过了2秒以上，因为我们当时的系统部署了运维的监控系统Skywalking ，在展示的报表中可以看到是哪一个接口比较慢，并且可以分析这个接口哪部分比较慢，这里可以看到SQL的具体的执行时间，所以可以定位是哪个sql出了问题

mysql慢查询日志

2.怎么分析慢sql?

通过EXPLAIN分析SQL执行计划

ALL：表示全表扫描，需要遍历全表来找到对应的行。
possible_keys：可能使用到的索引。
key：实际使用到的索引。
key_len：当前使用的索引的长度。
ref：关联id等信息。
rows：查找到记录所扫描的行数。
filtered：查找到所需记录占总扫描记录数的比例。
Extra：额外的信息。

通过Show Profile分析SQL执行性能

上述通过 EXPLAIN 分析执行计划，仅仅是停留在分析SQL的外部的执行情况，如果我们想要深入到MySQL内核中，从执行线程的状态和时间来分析的话，这个时候我们就可以选择Profile。

Profile除了可以分析执行线程的状态和时间，还支持进一步选择ALL、CPU、MEMORY、BLOCK IO、CONTEXT SWITCHES等类型来查询SQL语句在不同系统资源上所消耗的时间。以下是相关命令的注释：

SHOW PROFILE [type [, type] ... ]
[FOR QUERY n]
[LIMIT row_count [OFFSET offset]]
type参数：
| ALL：显示所有开销信息
| BLOCK IO：阻塞的输入输出次数
| CONTEXT SWITCHES：上下文切换相关开销信息
| CPU：显示CPU的相关开销信息 
| IPC：接收和发送消息的相关开销信息
| MEMORY ：显示内存相关的开销，目前无用
| PAGE FAULTS ：显示页面错误相关开销信息
| SOURCE ：列出相应操作对应的函数名及其在源码中的调用位置(行数) 
| SWAPS：显示swap交换次数的相关开销信息

Show Profiles只显示最近发给服务器的SQL语句，默认情况下是记录最近已执行的15条记录，我们可以重新设置profiling_history_size增大该存储记录，最大值为100。

获取到Query_ID之后，我们再通过Show Profile for Query ID语句，就能够查看到对应Query_ID的SQL语句在执行过程中线程的每个状态所消耗的时间了：

通过以上分析可知：SELECT COUNT(*) FROM order; SQL语句在Sending data状态所消耗的时间最长，这是因为在该状态下，MySQL线程开始读取数据并返回到客户端，此时有大量磁盘I/O操作。

3.怎么用explain分析sql执行计划

我们可以使用 EXPLAIN 命令来分析 SQL 的 执行计划 。

1.什么是执行计划

执行计划 是指一条 SQL 语句在经过 MySQL 查询优化器 的优化会后，具体的执行方式。

通过 EXPLAIN 的结果，可以了解到如数据表的查询顺序、数据查询操作的操作类型、哪些索引可以被命中、哪些索引实际会命中、每个数据表有多少行记录被查询等信息。

2.执行计划常用字段

MySQL 为我们提供了 EXPLAIN 命令，来获取执行计划的相关信息。

需要注意的是，EXPLAIN 语句并不会真的去执行相关的语句，而是通过查询优化器对语句进行分析，找出最优的查询方案，并显示对应的信息。

执行计划有12个字段组成，常用的有：

1.type表示查询表连接类型**，查看索引执行情况的一个重要指标。以下性能从好到坏依次：system > const > eq_ref > ref > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

system：这种类型要求数据库表中只有一条数据，是const类型的一个特例，一般情况下是不会出现的。
const：通过一次索引就能找到数据，一般用于主键或唯一索引作为条件，这类扫描效率极高，，速度非常快。基于主键或唯一索引唯一值查询，最多返回一条结果，比eq_ref略好。
eq_ref：常用于主键或唯一索引扫描，一般指使用主键的关联查询
ref : 常用于非主键和唯一索引扫描。
ref_or_null：这种连接类型类似于ref，区别在于MySQL会额外搜索包含NULL值的行
index_merge：使用了索引合并优化方法，查询使用了两个以上的索引。
unique_subquery：类似于eq_ref，条件用了in子查询
index_subquery：区别于unique_subquery，用于非唯一索引，可以返回重复值。
r