Hey guys，这里是程序员cxuan，欢迎你阅读我最新一期的文章，这篇文章是 MySQL 调优的汇总版，我加了一下日常开发过程中的调优经验，希望对各位小伙伴们有所帮助。下面开始正文。
一般传统互联网公司很少接触到 SQL 优化问题，其原因是数据量小，大部分厂商的数据库性能能够满足日常的业务需求，所以不需要进行 SQL 优化，但是随着应用程序的不断变大，数据量的激增，数据库自身的性能跟不上了，此时就需要从 SQL 自身角度来进行优化，这也是我们这篇文章所讨论的。
SQL 优化步骤
当面对一个需要优化的 SQL 时，我们有哪几种排查思路呢？
通过 show status 命令了解 SQL 执行次数
首先，我们可以使用 show status 命令查看服务器状态信息。show status 命令会显示每个服务器变量 variable_name 和 value，状态变量是只读的。如果使用 SQL 命令，可以使用 like 或者 where 条件来限制结果。like 可以对变量名做标准模式匹配。
图我没有截全，下面还有很多变量，读者可以自己尝试一下。也可以在操作系统上使用 mysqladmin extended-status 命令来获取这些消息。
但是我执行 mysqladmin extended-status 后，出现这个错误。
应该是我没有输入密码的原因，使用 mysqladmin -P3306 -uroot -p -h127.0.0.1 -r -i 1 extended-status 后，问题解决。
这里需要注意一下 show status 命令中可以添加统计结果的级别，这个级别有两个
- session 级：默认当前链接的统计结果
- global 级：自数据库上次启动到现在的统计结果
如果不指定统计结果级别的话，默认使用 session 级别。
对于 show status 查询出来的统计结果，有两类参数需要注意下，一类是以 Com_ 为开头的参数，一类是以 Innodb_ 为开头的参数。
下面是 Com_ 为开头的参数，参数很多，我同样没有截全。
Com_xxx 表示的是每个 xxx 语句执行的次数，我们通常关心的是 select 、insert 、update、delete 语句的执行次数，即
- Com_select：执行 select 操作的次数，一次查询会使结果 + 1。
- Com_insert：执行 INSERT 操作的次数，对于批量插入的 INSERT 操作，只累加一次。
- Com_update：执行 UPDATE 操作的次数。
- Com_delete：执行 DELETE 操作的次数。
以 Innodb_ 为开头的参数主要有
- Innodb_rows_read：执行 select 查询返回的行数。
- Innodb_rows_inserted：执行 INSERT 操作插入的行数。
- Innodb_rows_updated：执行 UPDATE 操作更新的行数。
- Innodb_rows_deleted：执行 DELETE 操作删除的行数。
通过上面这些参数执行结果的统计，我们能够大致了解到当前数据库是以更新（包括插入、删除）为主还是查询为主。
除此之外，还有一些其他参数用于了解数据库的基本情况。
- Connections：查询 MySQL 数据库的连接次数，这个次数是不管连接是否成功都算上。
- Uptime：服务器的工作时间。
- Slow_queries：满查询次数。
- Threads_connected：查看当前打开的连接的数量。
下面这个博客汇总了几乎所有 show status 的参数，可以当作参考手册。
https://blog.csdn.net/ayay_870621/article/details/88633092
定位执行效率较低的 SQL
定位执行效率比较慢的 SQL 语句，一般有两种方式
- 可以通过慢查询日志来定位哪些执行效率较低的 SQL 语句。
MySQL 中提供了一个慢查询的日志记录功能，可以把查询 SQL 语句时间大于多少秒的语句写入慢查询日志，日常维护中可以通过慢查询日志的记录信息快速准确地判断问题所在。用 --log-slow-queries 选项启动时，mysqld 会写一个包含所有执行时间超过 long_query_time 秒的 SQL 语句的日志文件，通过查看这个日志文件定位效率较低的 SQL 。
比如我们可以在 my.cnf 中添加如下代码，然后退出重启 MySQL。
log-slow-queries = /tmp/mysql-slow.log long_query_time = 2
通常我们设置最长的查询时间是 2 秒，表示查询时间超过 2 秒就记录了，通常情况下 2 秒就够了，然而对于很多 WEB 应用来说，2 秒时间还是比较长的。
也可以通过命令来开启：
我们先查询 MySQL 慢查询日志是否开启
show variables like "%slow%";
启用慢查询日志
set global slow_query_log='ON';
然后再次查询慢查询是否开启
如图所示，我们已经开启了慢查询日志。
慢查询日志会在查询结束以后才记录，所以在应用反应执行效率出现问题的时候慢查询日志并不能定位问题，此时应该使用 show processlist 命令查看当前 MySQL 正在进行的线程。包括线程的状态、是否锁表等，可以实时的查看 SQL 执行情况。同样，使用mysqladmin processlist语句也能得到此信息。
下面就来解释一下各个字段对应的概念
- Id ：Id 就是一个标示，在我们使用 kill 命令杀死进程的时候很有用，比如 kill 进程号。
- User：显示当前的用户，如果不是 root，这个命令就只显示你权限范围内的 SQL 语句。
- Host：显示 IP ，用于追踪问题
- Db：显示这个进程目前连接的是哪个数据库，为 null 是还没有 select 数据库。
- Command：显示当前连接锁执行的命令，一般有三种：查询 query，休眠 sleep，连接 connect。
- Time：这个状态持续的时间，单位是秒
- State：显示当前 SQL 语句的状态，非常重要，下面会具体解释。
- Info：显示这个 SQL 语句。
State 列非常重要，关于这个列的内容比较多，读者可以参考一下这篇文章
https://blog.csdn.net/weixin_34357436/article/details/91768402
这里面涉及线程的状态、是否锁表等选项，可以实时的查看 SQL 的执行情况，同时对一些锁表进行优化。
通过 EXPLAIN 命令分析 SQL 的执行计划
通过以上步骤查询到效率低的 SQL 语句后，可以通过 EXPLAIN 或者 DESC 命令获取 MySQL 如何执行 SELECT 语句的信息，包括在 SELECT 语句执行过程中表如何连接和连接的顺序。
比如我们使用下面这条 SQL 语句来分析一下执行计划
explain select * from test1;
上表中涉及内容如下
- select_type：表示常见的 SELECT 类型，常见的有 SIMPLE，SIMPLE 表示的是简单的 SQL 语句，不包括 UNION 或者子查询操作，比如下面这段就是 SIMPLE 类型。
PRIMARY ，查询中最外层的 SELECT（如两表做 UNION 或者存在子查询的外层的表操作为 PRIMARY，内层的操作为 UNION），比如下面这段子查询。
UNION，在 UNION 操作中，查询中处于内层的 SELECT（内层的 SELECT 语句与外层的 SELECT 语句没有依赖关系时）。
SUBQUERY：子查询中首个SELECT（如果有多个子查询存在），如我们上面的查询语句，子查询第一个是 sr（sys_role）表，所以它的 select_type 是 SUBQUERY。
- table ，这个选项表示输出结果集的表。
- type，这个选项表示表的连接类型，这个选项很有深入研究的价值，因为很多 SQL 的调优都是围绕 type 来讲的，但是这篇文章我们主要围绕优化方式来展开的，type 这个字段我们暂时作为了解，这篇文章不过多深入。
type 这个字段会牵扯到连接的性能，它的不同类型的性能由好到差分别是
system ：表中仅有一条数据时，该表的查询就像查询常量表一样。
const ：当表中只有一条记录匹配时，比如使用了表主键（primary key）或者表唯一索引（unique index）进行查询。
eq-ref ：表示多表连接时使用表主键或者表唯一索引，比如
select A.text, B.text where A.ID = B.ID
- 这个查询语句，对于 A 表中的每一个 ID 行，B 表中都只能有唯一的 B.Id 来进行匹配时。
ref ：这个类型不如上面的 eq-ref 快，因为它表示的是因为对于表 A 中扫描的每一行，表 C 中有几个可能的行，C.ID 不是唯一的。
ref_or_null ：与 ref 类似，只不过这个选项包含对 NULL 的查询。
index_merge ：查询语句使用了两个以上的索引，比如经常在有 and 和 or 关键字出现的场景，但是在由于读取索引过多导致其性能有可能还不如 range（后面说）。
unique_subquery ：这个选项经常用在 in 关键字后面，子查询带有 where 关键字的子查询中，用 sql 来表示就是这样
value IN (SELECT primary_key FROM single_table WHERE some_expr)
- range ：索引范围查询，常见于使用 =，<>，>，>=，<，<=，IS NULL，<=>，BETWEEN，IN() 或者 like 等运算符的查询中。
index ：索引全表扫描，把索引从头到尾扫一遍。
all ：这个我们接触的最多了，就是全表查询，select * from xxx ，性能最差。
上面就是 type 内容的大致解释，关于 type 我们经常会在 SQL 调优的环节使用 explain 分析其类型，然后改进查询方式，越靠近 system 其查询效率越高，越靠近 all 其查询效率越低。
- possible_keys ：表示查询时，可能使用的索引。
- key ：表示实际使用的索引。
- key_len ：索引字段的长度。
- rows ：扫描行的数量。
- filtered ：通过查询条件查询出来的 SQL 数量占用总行数的比例。
- extra ：执行情况的描述。
通过上面的分析，我们可以大致确定 SQL 效率低的原因，一种非常有效的提升 SQL 查询效率的方式就是使用索引，接下来我会讲解一下如何使用索引提高查询效率。
