MySQL 索引分析除了 EXPLAIN 还有什么方法？

原创

里奥ii 2022-07-21 16:12:49 ©著作权

文章标签 数据库 python linux mysql java 文章分类 运维

©著作权归作者所有：来自51CTO博客作者里奥ii的原创作品，请联系作者获取转载授权，否则将追究法律责任

前言

对于非数据库开发人员而言，难以对MySQL源码进行分析或调试，接近一个黑盒，但MySQL提供了一些命令及系统状态变量，可对索引及其他内容进行分析。掌握这些方法后，可以尽量深入地了解MySQL的一些实现细节。

MySQL索引分析除了EXPLAIN还有什么方法

通过以下方法，可以获得MySQL索引相关的数据，便于分析与理解索引相关的问题。

2.1. 通过慢查询日志记录SQL语句执行时间

参考 https://dev.mysql.com/doc/refman/5.6/en/slow-query-log.html 。

慢查询日志中包含的SQL语句，其执行时间超过long_query_time（系统参数）秒，并且至少检查min_examined_row_limit（系统参数）行。

获取初始锁的时间不包含在执行时间中。mysqld（MySQL服务器）在SQL语句执行且释放所有锁之后，将一条语句写入慢查询日志中，因此日志顺序可能与执行顺序不同。

2.1.1. 慢查询日志的系统参数

long_query_time系统参数的最小值为0，默认值为10（秒）。将慢查询日志记录到文件时，写入的时间包括微秒部分。将慢查询日志记录到表，仅写入整数时间，微秒部分将被忽略。

默认情况下，慢查询日志是禁用的。slow_query_log系统参数可以设置慢查询日志是否启用，参数值为0或OFF表示禁用，1或ON表示启用。

slow_query_log_file系统参数用于设置日志文件名称，默认值为“[主机名]-slow.log”。MySQL服务器会在data目录中创建文件，除非通过绝对路径名指定了其他目录。

log_output系统参数用于设置通用查询日志与慢查询日志的输出位置。其值为一个或多个逗号分隔的单词组成的列表，可选值为TABLE，FILE及NONE。

当指定TABLE时，表示将日志记录到名为mysql的系统数据库的general_log与slow_log表中；
当指定FILE时，表示将日志记录到文件中；
NONE表示禁用日志。

不使用索引对行进行查找的查询，需要在启用log_queries_not_using_indexes系统变量后，才会在写入慢查询日志时被包含进去。该系统默认为关闭。

查询缓存处理的查询不会被MySQL服务器记录。

2.1.2. 慢查询日志内容格式

为了通过慢查询日志记录查询语句的准确耗时，需要将慢查询日志保存至文件，log_output系统参数需要设置为FILE。慢查询日志文件中，每条语句生成的记录都有一行以#开头的数据，并包含以下字段（所有字段在一行中显示）：

Query_time

语句执行时间，单位为秒；

Lock_time

获取锁的时间，单位为秒；

Rows_sent

发送给客户端的行数；

Rows_examined

MySQL服务器层检查的行数（不包含存储引擎内部的处理）。

慢查询日志文件中的每个语句之前都有一个SET语句，该SET语句包括一个时间戳，代表该慢查询语句的记录时间。

2.1.3. 开启慢查询日志的参数配置

为了开启慢查询日志，需要修改MySQL服务器使用的配置文件的“[mysqld]”节点对应的系统参数，修改配置文件并重启MySQL服务器程序后生效。（通过“set GLOBAL 系统变量=xxx”命令修改慢查询日志相关参数时，未生效。）系统参数设置如下所示：

slow_query_log = 1
long_query_time = 0
min_examined_row_limit = 1

设置“slow_query_log = 1”，以开启慢查询日志。

设置“long_query_time = 0”，将每次的查询语句都记录至慢查询日志文件中。

设置“min_examined_row_limit = 1”，可以使explain语句不出现在慢查询日志中，因为explain语句的Rows_examined为0。

slow_query_log = 1
long_query_time = 0

慢查询日志文件默认生成在安装目录的data目录中。

使用可视化数据库管理工具连接MySQL时，可能会定时发送请求，也会生成在慢查询日志文件中。

2.1.4. 慢查询日志内容示例

慢查询日志内容示例如下：

# Time: 200311 19:25:39
# User@Host: test[test] @ localhost [127.0.0.1]
# Thread_id: 3  Schema: testdb  QC_hit: No
Query_time: 0.015360  Lock_time: 0.001452  Rows_sent: 5222  Rows_examined: 5222
use testdb;
SET timestamp=1583925939;
select id,cust_info from test_table_log where cust_info='1881';

经测试，以上Query_time时间仅包含MySQL服务器执行查询操作的耗时，不包含客户端展示数据的耗时。

2.2. 获取SQL语句读取索引次数

通过以下所述的方法，可以获取SQL语句执行时，读取的索引次数及方法，但不会显示读取了哪个索引。

2.2.1. SHOW STATUS语句用法

参考 https://dev.mysql.com/doc/refman/5.6/en/show-status.html 。

SHOW STATUS语句提供服务器状态信息。该语句不需要任何特权，只要能连接到服务器即可。

SHOW STATUS语句用法如下所示：

SHOW [GLOBAL | SESSION] STATUS [LIKE 'pattern' | WHERE expr]

SHOW STATUS语句接受可选的作用域修饰符，可为GLOBAL或SESSION：

当指定作用域为GLOBAL时，SHOW STATUS显示全局的状态值。全局状态变量可以表示服务器本身某些方面的状态，或者表示MySQL所有连接的汇总状态。如果某个变量没有全局值，则显示会话值；
当指定作用域为SESSION时，SHOW STATUS显示当前连接的状态变量值。如果某个变量没有会话值，则显示全局值。LOCAL是SESSION的代名词；
如果没有指定作用域，默认使用SESSION。

每次调用SHOW STATUS语句都会使用一个内部临时表，并增加全局Created_tmp_tables值。

SHOW STATUS语句支持LIKE子句，仅显示变量名称与指定模式匹配的行。

2.2.2. 与读取索引次数相关的服务器状态变量

参考 https://dev.mysql.com/doc/refman/5.6/en/server-status-variables.html 。

与读取索引次数相关的服务器状态变量以“HANDLER_READ”开头，如下所示：

Handler_read_first

索引中第一个条目的读取索引次数。如果该数值很高，则表明服务器正在执行很多全索引扫描（例如，SELECT col1 FROM foo，假设col1列存在索引）；

Handler_read_key

基于索引键读取行的请求数（即通过索引读取行的数量）。如果该数值很高，则表明表的索引很合适查询；

Handler_read_last

读取索引中最后一个键的请求数。使用ORDER BY时，服务器将发出一个first-key请求（对应Handler_read_first），然后发出多个next-key（对应Handler_read_next）请求；使用ORDER BY DESC时，服务器将发出一个last-key（对应Handler_read_last）请求，然后发出多个previous-key（对应Handler_read_prev）请求；

Handler_read_next

按索引键顺序读取下一行的请求数。当对索引列使用范围约束进行查询，或进行索引扫描时，该数值会增加；

Handler_read_prev

按索引键顺序读取上一行的请求数。这种读取方法主要用于优化ORDER BY … DESC；

Handler_read_rnd

基于固定位置读取行的请求数。当执行很多需要对结果进行排序的查询时，该数值会很高。可能有很多查询需要MySQL扫描整个表，或者联接未正确使用索引键；

Handler_read_rnd_next

读取数据文件下一行的请求数（即通过全表扫描读取行的数量）。当进行大量全表扫描时，该数值会变高。通常这表明表未正确建立索引，或者编写的查询未利用索引。

2.2.3. 使用SHOW STATUS获取SQL语句读取索引次数

当需要获取当前会话的SQL语句读取索引次数，可以执行SHOW STATUS查看以“HANDLER_READ”开头的服务器状态变量（以下执行命令中的HANDLER_READ大小写不限）：

SHOW STATUS LIKE 'HANDLER_READ%';

若需要使以上数值重置，可以重新连接MySQL服务器（创建新的会话），或重启MySQL服务器程序。

2.2.4. SQL语句读取索引次数示例

在以下示例中，test_table_log表的create_time字段存在单列索引，id列为主键。执行以下语句时，需要读取索引中第一个记录，Handler_read_first值加1。

select * from test_table_log order by create_time limit 1;

执行以下语句时，需要读取索引中的最后一个记录，Handler_read_last值加1。

select * from test_table_log order by create_time desc limit 1;

执行以下语句时，需要基于索引读取行，Handler_read_key值加1。

select * from test_table_log where create_time = '2020/3/10 12:23:38' limit 1;

执行以下语句时，需要基于索引读取行，Handler_read_key值加1；并需要根据索引读取后续9行，Handler_read_next值增加9。

select * from test_table_log where create_time = '2020/3/10 12:23:38' limit 10;

执行以下语句时，共查询到4条记录需要基于索引读取行，Handler_read_key值加1；并需要根据索引读取后续行，Handler_read_next值增加4。

select * from test_table_log where id >= 'testtime1583814037num1' and id <= 'testtime1583814037num1000'

执行以下语句时，需要读取索引中第一个记录，Handler_read_first值加1；并需要根据索引读取后续9行，Handler_read_next值增加9。

select * from test_table_log order by create_time limit 10;

执行以下语句时，需要读取索引中的最后一个记录，Handler_read_last值加1；并需要根据索引读取之前的9行，Handler_read_prev值增加9。

select * from test_table_log order by create_time desc limit 10;

执行以下语句时，需要通过全表扫描读取数据文件中的行，数量为110，Handler_read_rnd_next值增加110。

select * from test_table_log limit 100,10;

执行以下语句时，需要通过全表扫描读取数据文件中的行，Handler_read_rnd_next值增加超过110；并需要对查询到的10条记录进行排序，Handler_read_rnd值增加10。

select a.* from
(
select * from test_table_log limit 100,10
) as a order by a.create_time;

2.2.5. 查询数据库表获取SQL语句读取索引次数（不使用）

参考 https://dev.mysql.com/doc/refman/5.6/en/status-table.html 。

INFORMATION_SCHEMA.GLOBAL_STATUS与INFORMATION_SCHEMA.SESSION_STATUS表与“SHOW GLOBAL STATUS”及“SHOW SESSION STATUS”命令显示对应的信息。查询示例如下：

select * from INFORMATION_SCHEMA.SESSION_STATUS
where VARIABLE_NAME like 'HANDLER_READ%' and VARIABLE_VALUE <> '0'
order by cast(VARIABLE_VALUE as UNSIGNED INTEGER) desc;

当从上述表查询数据获取SQL语句读取索引次数时，会导致Handler_read_rnd_next变量值增大；当使用排序时，还会导致Handler_read_rnd变量值增大。因此需要获取SQL语句读取索引次数时，不通过上述数据库表查询。

2.3. 获取InnoDB缓冲池中索引页与记录数

InnoDB缓冲池中索引页与记录数，可以反映MySQL在执行SQL语句时，将指定表的索引读取到缓冲池中的索引页及记录数量。

若需要重置InnoDB缓冲池相关数据，需要重启MySQL服务器程序（未找到其他方法清空InnoDB缓冲池）。

2.3.1. INNODB_BUFFER_PAGE表

参考 https://dev.mysql.com/doc/refman/5.6/en/innodb-information-schema-buffer-pool-tables.html 。

InnoDB INFORMATION_SCHEMA缓冲池表提供了缓冲池状态信息，以及有关InnoDB缓冲池页面的元数据。

InnoDB INFORMATION_SCHEMA缓冲池表中包含INNODB_BUFFER_PAGE，该表保存InnoDB缓冲池中每个页的信息。查询INNODB_BUFFER_PAGE或INNODB_BUFFER_PAGE_LRU表可能会影响性能，应避免在生产环境查询。

2.3.2. INNODB_BUFFER_PAGE表字段

参考 https://dev.mysql.com/doc/refman/5.6/en/innodb-buffer-page-table.html 。INNODB_BUFFER_PAGE表中包含以下关注的字段：

POOL_ID

缓冲池ID。是用于区分多个缓冲池实例的标识符。

SPACE

表空间ID。

PAGE_NUMBER

页编号（page number）。

PAGE_TYPE

页的类型。例如“INDEX”代表B+树节点，“BLOB”代表未压缩的BLOB页等。

TABLE_NAME

页所属的表名。该列仅适用于PAGE_TYPE值为INDEX的页。

INDEX_NAME

页所属的索引名。可以是聚簇索引或二级索引的名称。该列仅适用于PAGE_TYPE值为INDEX的页。

NUMBER_RECORDS

页的记录的数量。

2.3.3. 从INNODB_BUFFER_PAGE表获取InnoDB缓冲池中索引页与记录数

从INNODB_BUFFER_PAGE表中可获取InnoDB缓冲池中索引页与记录数，table_name字段的形式为“`[数据库名]`.`[表名]`”。

查询的示例语句如下，page_num为指定表的指定索引在InnoDB缓冲池中的页的总数，record_num为指定表的指定索引在InnoDB缓冲池中记录的总数：

select TABLE_NAME,INDEX_NAME,count(*) as page_num,sum(NUMBER_RECORDS) as record_num
from INFORMATION_SCHEMA.INNODB_BUFFER_PAGE
where table_name = '`testdb`.`test_table_log`'
group by TABLE_NAME,INDEX_NAME
order by page_num desc;

查询结果如下所示：

MySQL 索引分析除了 EXPLAIN 还有什么方法？_mysql

2.4. 获取InnoDB索引页操作次数

InnoDB对索引页的操作包括读取、创建及写入等。

若需要重置上述数据，需要重启MySQL服务器程序。

2.4.1. 使用SHOW STATUS获取InnoDB索引页操作次数

参考 https://dev.mysql.com/doc/refman/5.6/en/server-status-variables.html 。

MySQL服务器状态中，包含以下与InnoDB索引页操作次数相关的状态：

Innodb_buffer_pool_pages_data

InnoDB缓冲池中包含数据的页数。该数量包含脏页与非脏页。

Innodb_pages_created

对InnoDB表操作创建的页数。

Innodb_pages_read

对InnoDB表操作，从InnoDB缓冲池读取的页数。

Innodb_pages_written

对InnoDB表操作导致被写的页数。

使用SHOW STATUS获取InnoDB索引页操作次数时，可执行以下语句：

SHOW STATUS LIKE 'Innodb_pages_%';
SHOW STATUS LIKE 'Innodb_buffer_pool_pages_data';

2.4.2. 查询数据库表获取InnoDB索引页操作次数

参考 https://dev.mysql.com/doc/refman/5.6/en/innodb-buffer-pool-stats-table.html 。

INFORMATION_SCHEMA.INNODB_BUFFER_POOL_STATS表提供了许多与“SHOW ENGINE INNODB STATUS”输出相同的缓冲池信息。

上述表包含以下关注的列：

DATABASE_PAGES

InnoDB缓冲池中包含数据的页数。该数量包含脏页与非脏页。

与“SHOW STATUS”输出中的Innodb_buffer_pool_pages_data相同。

NUMBER_PAGES_READ

读取的页数。

与“SHOW STATUS”输出中的Innodb_pages_read相同。

NUMBER_PAGES_CREATED

创建的页数。

与“SHOW STATUS”输出中的Innodb_pages_created相同。

NUMBER_PAGES_WRITTEN

写入的页数。

与“SHOW STATUS”输出中的Innodb_pages_written相同。

从上述表获取InnoDB索引页操作次数示例语句如下（查询操作不会导致以上参数变化）：

select DATABASE_PAGES, NUMBER_PAGES_READ, NUMBER_PAGES_CREATED, NUMBER_PAGES_WRITTEN from INFORMATION_SCHEMA.INNODB_BUFFER_POOL_STATS;

2.4.3. 使用SHOW ENGINE STATUS获取InnoDB索引页操作次数（不使用）

参考 https://dev.mysql.com/doc/refman/5.6/en/show-engine.html 。

“SHOW ENGINE”显示指定存储引擎的操作信息。执行时需要“PROCESS”权限。

“SHOW ENGINE INNODB STATUS”显示来自标准InnoDB监控器的有关InnoDB存储引擎状态的大量信息。

以上输出信息全部包含在Status字段中，无法进行过滤，不方便单独查看Pages read, created, written等参数。

2.5. 获取索引页分裂次数

参考 https://dev.mysql.com/doc/refman/5.6/en/index-page-merge-threshold.html ，频繁发生页分裂可能对性能产生影响。

2.5.1. INFORMATION_SCHEMA.INNODB_METRICS表

参考 https://dev.mysql.com/doc/refman/5.6/en/innodb-metrics-table.html 。

INFORMATION_SCHEMA.INNODB_METRICS表提供了InnoDB的各种性能信息。

每个监控器代表InnoDB源代码中用于收集计数器（counter）信息的位置。每个计数器可被启动、停止及重置。

默认情况下，收集的数据比较少。为了启动、停止及重置计算器，需要对系统变量innodb_monitor_enable、innodb_monitor_disable、innodb_monitor_reset或innodb_monitor_reset_all进行设置。

INFORMATION_SCHEMA.INNODB_METRICS表包含以下关注的列：

NAME

计数器的唯一名称；

COUNT

计数器启用后的计数值；

STATUS

enabled代表计数器在执行，disabled代表已停止；

COMMENT

计数器描述。

在查询INFORMATION_SCHEMA.INNODB_METRICS表时，需要有PROCESS权限。

在INFORMATION_SCHEMA.INNODB_METRICS表中，存在NAME为“index_page_splits”，COMMENT为“Number of index page splits”的记录，即索引页分裂次数对应的计数器，COUNT列为索引页分裂次数。

select count from INFORMATION_SCHEMA.INNODB_METRICS where name='index_page_splits';

2.5.2. 启用索引页分裂次数计数器

参考 https://dev.mysql.com/doc/refman/5.6/en/innodb-information-schema-metrics-table.html 。

全局变量innodb_monitor_enable、innodb_monitor_disable可分别用于启用、禁用记数器。命令如下所示：

SET GLOBAL innodb_monitor_enable = index_page_splits;

SET GLOBAL innodb_monitor_disable = index_page_splits;

innodb_monitor_enable变量还可以MySQL配置文件的[mysqld]节点进行配置，可使用半角逗号对多项进行分隔。innodb_monitor_disable不支持在MySQL配置文件配置。

经验证，对以上系统变量进行设置时，需要有SUPER权限（root用户）。

2.6. 获取执行计划

参考 https://dev.mysql.com/doc/refman/5.6/en/explain-output.html 。

EXPLAIN语句提供有关MySQL如何执行语句的信息。EXPLAIN适用于SELECT，DELETE，INSERT，REPLACE和UPDATE语句。

EXPLAIN为SELECT语句中使用的每个表返回一行信息。输出中的表的顺序，与MySQL在处理语句时读取表的顺序一致。

2.6.1. EXPLAIN输出列

EXPLAIN输出的列及含义如下所示：

MySQL 索引分析除了 EXPLAIN 还有什么方法？_mysql_02

2.6.2. id

id列为SELECT的标识符，其值为查询中SELECT的顺序号。

即id列反映了SELECT在执行时的顺序（从小到大）。

2.6.3. select_type

select_type列为SELECT的类型，select_type值及含义如下所示：

MySQL 索引分析除了 EXPLAIN 还有什么方法？_mysql_03

DEPENDENT通常表示使用了相关子查询（correlated subquery）。

2.6.4. type

type列为连接类型，描述了表的连接方式。不同的连接类型如下所示：

system

表只有一行（系统表）。system是const连接类型的特例。

const

查询的开头读取的表，最多只有一行匹配。因为只有一行，优化器的其余部分可以将此行中对应列的值视为常量。

const连接类型对应的表查询非常快，因为它们只读取一次。

将主键或唯一索引的所有部分与常量值进行比较时会使用const连接类型。

在以下示例中，tbl_name表可以作为const连接类型使用：

SELECT * FROM tbl_name WHERE primary_key=1;

SELECT * FROM tbl_name WHERE primary_key_part1=1 AND primary_key_part2=2;

eq_ref

对于先前的表的每种行组合，从eq_ref连接类型对应的表中读取一行。除了system和const连接类型外，eq_ref是最好的联接类型。当连接使用索引的所有部分并且索引是主键或非空唯一索引时，将使用eq_ref连接类型。

eq_ref可用于使用=运算符进行比较的索引列。比较值可以是常量，也可以是使用在此表之前读取的表中列的表达式。

在以下示例中，MySQL可以使用eq_ref连接类型处理ref_table表：

SELECT * FROM ref_table,other_table WHERE ref_table.key_column=other_table.column;

SELECT * FROM ref_table,other_table WHERE ref_table.key_column_part1=other_table.column AND ref_table.key_column_part2=1;

对于先前的表的每种行组合，从ref连接类型对应的表读取所有具有匹配索引值的行。

如果连接仅使用索引键的最左前缀或者索引键不是主键或唯一索引（即连接不能基于索引键值选择单行），则使用ref连接类型。

如果使用的键只匹配少量几行，则ref是一个好的连接类型。

ref连接类型可用于使用=或<=>运算符进行比较的索引列。

在以下示例中，MySQL可以使用ref连接类型处理ref_table表：

SELECT * FROM ref_table WHERE key_column=expr;

SELECT * FROM ref_table,other_table WHERE ref_table.key_column=other_table.column;

SELECT * FROM ref_table,other_table WHERE ref_table.key_column_part1=other_table.column AND ref_table.key_column_part2=1;

fulltext

使用全文索引执行连接。

ref_or_null

ref_or_null与ref类似，区别在于使用ref_or_null时，MySQL对包含NULL值的行进行了额外搜索。

ref_or_null连接类型最常用于解决子查询。

在以下示例中，MySQL可以使用ref_or_null连接类型处理ref_table表：

SELECT * FROM ref_table WHERE key_column=expr OR key_column IS NULL;

index_merge

index_merge连接类型表示使用了索引合并优化。在这种情况下，输出行中的key列包含使用的索引列表，key_len列包含所用索引的最长键部分列表。

unique_subquery

unique_subquery连接类型在某些使用IN的子查询的情况下替代eq_ref，如下所示：

value IN (SELECT primary_key FROM single_table WHERE some_expr)

unique_subquery只是一个索引查找函数，可以完全替代子查询以提高效率。

index_subquery

index_subquery连接类型与unique_subquery类似，它替代了IN子查询，支持子查询中的非唯一索引，如下所示：

value IN (SELECT key_column FROM single_table WHERE some_expr)

range

range连接类型表示仅检索给定范围内的行，使用索引选择行。

EXPLAIN输出行中的key列表示使用哪个索引。key_len列包含使用的最长的索引键部分。对于此连接类型，ref列为NULL。

使用=，<>，>，> =，<，<=，IS NULL，<=>，BETWEEN，LIKE或IN()运算符中的任何一个将索引键列与常量进行比较时，可以使用range类型。

index

index连接类型与ALL类型类似，区别在于index连接类型有对索引树进行扫描。有两种方式：a. 如果查询的索引是覆盖索引，并且可满足所需表的所有数据，则仅扫描索引树。在这种情况下，Extra列显示“Using index”。仅扫描索引通常比ALL连接类型快，因为索引的大小通常小于表数据。b. 对指定索引读取数据，进行全表扫描，以按索引顺序查找数据行。Extra列不会显示“Using index”。

当查询仅使用属于单个索引一部分的列时，MySQL可以使用此连接类型。

对之前的表中的每个行组合进行全表扫描。

如果当前表是第一个没有标记为const的表，这通常是不好的，并且通常在所有其他情况下非常糟糕。

通常，可以通过添加索引来避免ALL连接类型，这些索引基于常量值或先前的表的列值，启用从该表的行检索。

2.6.5. possible_keys

possible_keys列表示MySQL可以选择用于查找此表的行的索引。请注意，此列于EXPLAIN输出中显示的表的顺序完全独立。

假如possible_keys列为NULL，说明没有相关的索引。

2.6.6. key

key列表示MySQL实际决定使用的索引键。如果MySQL决定使用其中一个possible_keys索引来查找行，那么该索引将被显示在key列中。

如果key列为NULL，说明MySQL未找到更有效执行查询的索引。

要强制MySQL使用或忽略possible_keys列中列出的索引，请在查询中使用FORCE INDEX，USE INDEX或IGNORE INDEX。

2.6.7. key_len

key_len列表示MySQL决定使用的索引键的长度。根据key_len的值可以确定MySQL实际使用了联合索引的多少部分。

由于索引键的存储格式，可以为NULL的列比不允许为NULL的列的索引键长度大1。

2.6.8. ref

ref列显示了在key列中列出的，用于从表中查询行的索引，与哪些列或常量进行了比较。

如果ref列的值为func，说明使用的值是某些函数的结果。

2.6.9. rows

rows列表示MySQL认为执行查询时必须检查的行数。

对于InnoDB表，该数字是估计值，可能并不总是准确的。

2.6.10. Extra

Extra列包含MySQL如何解决查询的额外信息。以下为Extra列的部分可能的值及说明：

Using filesort

出现该提示说明MySQL在进行排序时需要使用filesort，未使用索引进行排序。

Using index

从表中检索列信息时，仅使用索引树的信息，不需要进行额外的查找以读取实际行。

当仅查询属于单个索引的部分列时，可以使用此策略。

对于具有用户定义的聚簇索引的InnoDB表，即使Extra列中没有出现“Using index”，对应的索引也可能被使用。当type列为index且key列为主键时，对应以上情况。

Using index condition

出现该提示说明MySQL在查询时使用了索引条件下推。

Using sort_union(…), Using union(…), Using intersect(…)

以上显示了对应index_merge连接类型，即使用索引合并时，索引扫描合并时使用的特定算法。

Using temporary

为了解决该查询，MySQL需要创建一个临时表来保存结果。如果查询包含GROUP BY和ORDER BY子句，且列出了不同的列时，通常会发生这种情况。

Using where

WHERE子句用于限制与下一个表匹配的行，或发送到客户端的行。假如Extra列的值不是using where，且表的连接类型为ALL或index时，则说明查询语句存在一些问题，除非是特意为了获取或检查表中所有的行。

3. InnoDB读取索引页时机

InnoDB读取索引页的时机，包括但不限于MySQL启动、执行EXPLAIN语句、执行查询/插入/更新操作等情况。

为了获取InnoDB读取索引页的次数，可参考前文，查询以“Innodb_pages_”开头的系统状态，或从INFORMATION_SCHEMA.INNODB_BUFFER_POOL_STATS表查询。

3.1. MySQL启动时读取索引页

当MySQL启动时，会读取对部分表的索引。此时还未执行任何语句，查看InnoDB读取索引页的次数大于0，查看InnoDB缓冲池中索引页与记录数大于0。

3.2. 执行EXPLAIN语句时读取索引页

在执行EXPLAIN语句时，可能会读取相关表对应的索引页，若所需的索引页数据已被读取至缓冲池中，可能不会读取。对于上述情况，可查看InnoDB读取索引页的次数，执行EXPLAIN语句后次数会增加。

3.3. 执行插入/查询/更新/删除语句时读取索引页

在执行插入/查询/更新/删除语句时，可能会读取相关表对应的索引页，若所需的索引页数据已被读取至缓冲池中，可能不会读取。

4. 参考资料

以上参考的资料如下：https://dev.mysql.com/doc/refman/5.6/en/

原作者：adrninistrat0r

MySQL 索引分析除了 EXPLAIN 还有什么方法？_python_04

上一篇：Java 开发的编程噩梦，这些坑你没踩过算我输

下一篇：嘘！偷偷破解“仅三天可见”的朋友圈啦....

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯