MySQL -- 索引性能分析

查看SQL执行频率

MySQL 客户端连接成功后，通过show[session|global] status 命令可以提供服务器状态信息。通过如下指令可以查看当前数据库的INSERT、UPDATE、DELETE、SELECT的访问频次：

1

SHOW GLOBAL STATUS LIKE 'Com_______'; # 一个 '_' 代表一个字符

结果如下：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17


mysql> show global status like 'Com_______';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_binlog    | 0     |
| Com_commit    | 0     |
| Com_delete    | 0     |
| Com_import    | 0     |
| Com_insert    | 0     |
| Com_repair    | 0     | 
| Com_revoke    | 0     |
| Com_select    | 8     |
| Com_signal    | 0     |
| Com_update    | 0     |
| Com_xa_end    | 0     |
+---------------+-------+
11 rows in set (0.00 sec)

查询慢日志

慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认10秒）的所有SQL语句的日志。MySQL的慢查询日志默认没有开启，执行一下SQL查看慢日志开启状态：
1
SHOW VARIABLES LIKE 'slow_query_log';

需要在MySQL的配置文件（/etc/my.cnf）中配置如下信息：

1
2
3
4
5


# 开启MySQL慢日志查询开关
slow_query_log=1

# 设置慢日志的时间为2秒，SQL语句执行时间炒锅2秒就会视为慢查询，记录慢查询日志
long_query_time=2

重启MySQL服务，本机环境为win10的WSL2，直接执行以下命令：

1

sudo service mysql restart

profile详情

show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪去了。通过have_profiling参数，能够看到当前MySQL是否支持profile操作：

1
2


# 查看是否支持
SELECT @@have_profiling;

默认profiling是关闭的，可以通过set语句在session/global级别开启profiling:

1
2
3
4
5


# 查看开关状态
SELECT @@profiling;

# 设置开关状态
SET profiling = 1;

执行完查询语句后再执行show profiles，结果如下：

1
2
3
4
5
6
7
8
9


+----------+------------+------------------------+
| Query_ID | Duration   | Query                  |
+----------+------------+------------------------+
|        1 | 0.00025800 | select @@profiling     |
|        2 | 0.00125875 | show tables            |
|        3 | 0.00014975 | select * form user     |
|        4 | 0.01059300 | select * from user     |
|        5 | 0.00152175 | select * from user_log |
+----------+------------+------------------------+

explain 执行计划

EXPLAIN 或者 DESC 命令获取MySQL如何执行SELECT语句的信息，包括在SELECT语句执行过程中表如何连接和连接的顺序。语法如下：

1
2
3


# 直接在select语句之前加上关键字 explain/desc

EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件;

结果如下：

1
2
3
4
5
6
7


mysql> explain select * from user;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

EXPLAIN 输出列如下：

Column	JSON Name	Meaning
id	`select_id`	`SELECT` 标识符
select_type	`None`	`SELECT` 类型
table	`table_name`	输出行table
partitions	`partitions`	匹配的分区
type	`access_type`	连接类型
possible_keys	`possible_keys`	可能的索引选择
key	`key`	实际选择的索引
key_len	`key_length`	所选键的长度
ref	`ref`	与索引比较的列
rows	`rows`	估计要检查的行
filtered	`filtered`	按table条件过滤的行百分比
Extra	`None`	扩展信息

😀 Note 详细解释可参考 [官方文档]

id 行标识，如果没有子查询或者联合查询，这个值是1，id列值越大越先执行，如果一样大，那么就从上往下依次执行

select_type 查询的类型，可以是下表的任何一种类型

select_type	类型说明
`SIMPLE`	简单`SELECT`（不使用`UNION`或子查询）
`PROMARY`	最外层的`SELECT`
`UNION`	`UNION`中第二个或之后的`SELECT`语句
`DEPENDENT UNION`	`UNION`中第二个或之后的`SELECT`语句取决于外面的查询
`UNION RESULT`	`UNION`的结果
`SUBQUERY`	子查询中的第一个`SELECT`
`DEPENDENT SUBQUERY`	子查询中的第一个`SELECT`，取决于外面的查询
`DERIVED`	衍生表（FROM自居中的子查询）
`MATERIALIZED`	物化子查询
`UNCACHEABLE SUBQUERY`	结果集无法缓存的子查询，必须重新评估外部查询的每一行
`UNCACHEABLE UNION`	`UNION`中第二个或之后的`SELECT`，属于无法缓存的子查询

⭐ DEPENDENT 意味着使用了关联子查询

table：输出行所引用表的名称，也可以是以下值之一：
- <unionM,N>: 引用id为M和N UNION之后的结果。
- : 引用id为N的结果派生出的表，派生表可以是一个结果集，例如派生自FROM中子查询的结果。
- : 引用id为N的子查询结果物化得到的表，即生成一个临时表保存子查询的结果。
partitions：v5.7之前该项是explain partitions显示的选项，v5.7之后成为了默认选项，该列显示的为分区表中的分区情况，非分区表该字段为空（null）。
type( 重要 )：连接类型
- system 表中只有一行数据或者是空表，这是const类型的一个特例，且只能用于MyISAM和Memory表，如果是InnoDB引擎表，type列在这个情况通常都是all或者index
- const 最多只有一行记录匹配。当联合主键或唯一索引的所有字段跟常量值比较时，join类型为const。其他数据库也叫做唯一索引扫描
- eq_ref 多表join时，对于来自前面表的每一行，在当前表中只能找到一行，这可能是除了system和const之外最好的类型，当主键或唯一非NULL索引的所有字段都被当做join连接时会出现此类型。 eq_ref可用于使用’=‘操作符作比较的索引列，比较的值可以是常量，也可以是使用此表之前读取的表的列表达式。
- ref 对于来自前面表的每一行，在此表的索引中可以匹配到多行，若连接只用到索引的最左前缀或索引不是主键或唯一索引时，使用 ref 类型（也就是说，此类型能够匹配到多行连接）。 ref 可用于使用 = 或 <=> 操作符作比较的索引列。
- fulltext 用于全文索引的时候是这个类型，要注意，全文索引的优先级很高，若全文索引和普通索引同时存在，mysql不管代价，优先选择使用全文索引
- ref_or_null 类似于ref类型，只是增加了null值得比较，实际用的不多
- index_merge 此类型表示查询使用两个以上索引，最后取交集或者并集，常见 and, or的条件使用了不同的索引，官方排序这个在ref_or_null之后，但是实际上由于要读取多个索引，性能可能大部分时间都不如range。
- unique_subquery 用于WHERE中的 IN 形式子查询，子查询返回不重复值唯一值，可以完全替换子查询，效率更高。该类型替换了下面形式的 IN 子查询的 ref: value IN (select primary_key FROM single_table WHERE some_expr)
- index_subquery 该连接类型类似于unique_subquery。适用于非唯一索引，可以返回重复值
- range 索引范围查询，常见于使用= <> > >= < <= IS NULL <=> BETWEEN IN 或者 LIKE 等运算符的查询中。
- index 索引全表扫描，把索引从头到尾扫一遍。这里包含两种情况：一种是查询使用了覆盖索引，那么它只需要扫描索引就可以获得数据，这个效率要比全表扫描要快，因为索引通常比数据表小，而且还能避免二次查询。在 extra 中显示 Using index，反之，如果在索引上进行全表扫描，没有 Using index的提示。
- all 全表扫描，性能较差
possible_keys：查询可能用到的索引，如果此列是 NULL，则没有用到索引。
key：表示MySQL实际决定使用的索引，如果MySQL决定使用其中一个 possible_keys 索引来查找行，则该索引将作为键值列出。
key_length：查询用到的索引长度（字节），如果是单列索引，那就整个索引长度算进去，如果是多列索引，那么查询不一定都能使用到所有的列，用多少算多少。例如组合索引为idx_username_create_time，当查询语句为 select * from user where username='qqq'时，只用到了username字段，那么key_len只会计算username字段占用的字节数，假如username字段类型为varchar(50) NOT NULL，字符编码为utf8mb3（utf8编码一个字符占用3个字节，gbk编码一个字符占2字节），记录字符串长度占用2字节，记录字段不为空占用1字节（若没有NOT NULL限制则不占用这1个字节）那么key_len为 50 * 3 + 2 + 1 = 153字节
ref：如果使用的是常数等值查询，这里会显示const，如果是连接查询，被驱动表的执行计划这里会显示驱动表的关联字段，如果是条件使用了表达式或者函数，或者条件列表发生了内部隐式转换，这里可能显示为func。
rows( 重要 ):rows也是一个重要的字段，这是MySQL估算的需要扫描的行数（不是精确值）。这个值非常直观显示SQL的效率好坏，原则上rows越少越好。
filtered：该字段表示存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数量的比例，注意是百分比，不是具体记录数。
Extra( 重要 )：EXPLAIN中很多额外的信息会在Extra字段显示，常见的有以下：
- distinct: 在select部分使用了distinct关键字
- Using filesort: 当Extra中有Using filesort时，表示MySQL需额外的排序操作，不能通过索引顺序到达排序效果，一般有 Using filesort 都建议优化去掉，因为这样的查询CPU资源消耗较大。
- Using index: “覆盖索引扫描”，表示查询在索引树种就可查找所需数据，不用扫描表数据文件，往往说明性能不错。
- Using temporary: 查询有使用临时表，一般出现于排序，分组和多表join的情况，查询效率不高，建议优化。