42 张图带你撸完 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 的参数，可以当作参考手册。

blog.csdn.net/ayay_870621…

定位执行效率较低的 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 列非常重要，关于这个列的内容比较多，读者可以参考一下这篇文章

blog.csdn.net/weixin_3435…

这里面涉及线程的状态、是否锁表等选项，可以实时的查看 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 查询效率的方式就是使用索引，接下来我会讲解一下如何使用索引提高查询效率。

索引

索引是数据库优化中最常用也是最重要的手段，通过使用不同的索引可以解决大多数 SQL 性能问题，也是面试经常会问到的优化方式，围绕着索引，面试官能让你造出火箭来，所以总结一点就是索引非常非常重！要！不只是使用，你还要懂其原！理！

索引介绍

索引的目的就是用于快速查找某一列的数据，对相关数据列使用索引能够大大提高查询操作的性能。不使用索引，MySQL 必须从第一条记录开始读完整个表，直到找出相关的行，表越大查询数据所花费的时间就越多。如果表中查询的列有索引，MySQL 能够快速到达一个位置去搜索数据文件，而不必查看所有数据，那么将会节省很大一部分时间。

索引分类

先来了解一下索引都有哪些分类。

• 全局索引(FULLTEXT)：全局索引，目前只有 MyISAM 引擎支持全局索引，它的出现是为了解决针对文本的模糊查询效率较低的问题，并且只限于 CHAR、VARCHAR 和 TEXT 列。

• 哈希索引(HASH)：哈希索引是 MySQL 中用到的唯一 key-value 键值对的数据结构，很适合作为索引。HASH 索引具有一次定位的好处，不需要像树那样逐个节点查找，但是这种查找适合应用于查找单个键的情况，对于范围查找，HASH 索引的性能就会很低。默认情况下，MEMORY 存储引擎使用 HASH 索引，但也支持 BTREE 索引。

• B-Tree 索引：B 就是 Balance 的意思，BTree 是一种平衡树，它有很多变种，最常见的就是 B+ Tree，它被 MySQL 广泛使用。

• R-Tree 索引：R-Tree 在 MySQL 很少使用，仅支持 geometry 数据类型，支持该类型的存储引擎只有MyISAM、BDb、InnoDb、NDb、Archive几种，相对于 B-Tree 来说，R-Tree 的优势在于范围查找。

从逻辑上来对 MySQL 进行分类，主要分为下面这几种

• 普通索引：普通索引是最基础的索引类型，它没有任何限制 。创建方式如下

  create index normal_index on cxuan003(id);

  

  删除方式

  drop index normal_index on cxuan003;

  

• 唯一索引：唯一索引列的值必须唯一，允许有空值，如果是组合索引，则列值的组合必须唯一，创建方式如下

  create unique index normal_index on cxuan003(id);

  

• 主键索引：是一种特殊的索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引。

作者：程序员cxuan
链接：https://juejin.cn/post/6989045173938290701
来源：掘金
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。