MySQL性能分析工具（慢查询日志与EXPLAIN关键字）
> 视频教程：[尚硅谷_MySQL](http://www.atguigu.com/download_detail.shtml?v=349) # 系统性能参数 在 `MySQL` 中，可以使用 `SHOW STATUS` 语句查询一些 `MySQL` 数据库服务器的**性能参数**、**执行频率**。`SHOW STATUS` 语句语法如下： ```sql SHOW [GLOBAL|SESSION] STATUS LIKE '参数'; ``` 一些常用的性能参数如下： - `Connections` ：连接 `MySQL` 服务器的次数 - `Uptime` ：`MySQL` 服务器的上线时间 - `Slow_queries` ：慢查询的次数 - `Innodb_rows_read` ：执行 `SELECT` 查询返回的行数 - `Innodb_rows_inserted` ：执行 `INSERT` 操作插入的行数 - `Innodb_rows_updated` ：执行 `UPDATE` 操作更新的行数 - `Innodb_rows_deleted` ：执行 `DELETE` 操作删除的行数 - `Com_select` ：查询操作的次数 - `Com_insert` ：插入操作的次数。对于批量插入的 `INSERT` 操作，只累加一次。 - `Com_update` ：更新操作的次数。 - `Com_delete` ：删除操作的次数。 # 慢查询日志 ## 配置 ### 查询状态 默认情况下，是关闭的。建议本地开发和测试环境开启，生产环境不建议开启，因为会损耗一些性能。 - 查询是否开启：`SHOW VARIABLES LIKE 'slow_query_log';` - 查询慢查询日志文件位置：`SHOW VARIABLES LIKE 'slow_query_log_file';` - 查询慢查询时间阈值：`SHOW VARIABLES LIKE 'long_query_time';` ### 开启 > 临时（以全局为例，当前会话删除 `GLOBAL` ） - 开启：`SET GLOBAL slow_query_log = 'ON';` - 文件：`SET GLOBAL slow_query_log_file = 'xxx'` - 阈值：`SET GLOBAL long_query_time = 1;` > 永久 修改MySQL配置文件，在 `mysqld` 下面添加如下配置，修改完成后重启服务器 ``` [mysqld] # 开启慢查询日志（建议本地和测试环境开启） slow_query_log=ON # 慢查询日志文件位置 slow_query_log_file=C:\development\mysql\log\log-slow.log # 慢查询时间阈值（单位：秒） long_query_time=3 ``` ### 关闭 > 临时（以全局为例，当前会话删除 `GLOBAL` ） `SET GLOBAL slow_query_log = 'OFF';` > 永久 修改MySQL配置文件，去除开启的配置，或者设置为 `OFF` ，修改完成后重启服务器 ``` [mysqld] # 关闭慢查询日志 slow_query_log=OFF ``` ## 查询慢查询数目 ``` SHOW GLOBAL STATUS LIKE 'Slow_queries'; ``` ## 慢查询日志分析工具：mysqldumpslow `mysqldumpslow` 命令的具体参数如下： - `-a` : 不将数字抽象成 `N` ，字符串抽象成 `S` ，即展示具体 `SQL` - `-s` : 是表示按照何种方式排序： - `c` : 访问次数 - `l` : 锁定时间 - `r` : 返回记录 - `t` : 查询时间 - `al` :平均锁定时间 - `ar` :平均返回记录数 - `at` :平均查询时间 （默认方式） - `ac` :平均查询次数 - `-t` : 即为返回前面多少条的数据； - `-g` : 后边搭配一个正则匹配模式，大小写不敏感的； > 举例：要按照查询时间排序，查看前五条 `SQL` 语句： ``` root@0ef201879c21:/var/lib/mysql# mysqldumpslow -s t -t 5 /var/lib/mysql/0ef201879c21-slow.log Reading mysql slow query log from /var/lib/mysql/0ef201879c21-slow.log Count: 1 Time=0.00s (0s) Lock=0.00s (0s) Rows=0.0 (0), 0users@0hosts Died at /usr/bin/mysqldumpslow line 162, <> chunk 1. ``` > 工作常用参考： ```sql #得到返回记录集最多的10个SQL mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log #得到访问次数最多的10个SQL mysqldumpslow -s c -t 10 /var/lib/mysql/atguigu-slow.log #得到按照时间排序的前10条里面含有左连接的查询语句 mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/atguigu-slow.log #另外建议在使用这些命令时结合 | 和more 使用 ，否则有可能出现爆屏情况 mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log | more ``` ## 删除慢查询日志 1. 手动删除文件 2. 使用命令：`mysqladmin -uroot -p flush-logs slow` # 分析查询语句：EXPLAIN ☆ 官方文档： - 5.7: [8.8.2 EXPLAIN Output Format](https://dev.mysql.com/doc/refman/5.7/en/explain-output.html) - 8.0: [8.8.2 EXPLAIN Output Format](https://dev.mysql.com/doc/refman/8.0/en/explain-output.html) ## 语法 `EXPLAIN` 或 `DESCRIBE` 语句的语法形式如下： ```sql EXPLAIN SELECT select_options # 或者 DESCRIBE SELECT select_options ``` 如果想看某个查询的执行计划的话，可以在具体的查询语句前边加一个 `EXPLAIN` ，例如： ``` mysql> EXPLAIN SELECT 1; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+ 1 row in set, 1 warning (0.00 sec) ``` `EXPLAIN` 语句输出的各个列的作用如下： 列名 | 描述 --- | --- id | 在一个大的查询语句中每个 `SELECT`关键字都对应一个唯一的 `id` select_type | `SELECT` 关键字对应的那个查询的类型 table | 表名 partitions | 匹配的分区信息 type | 针对单表的访问方法 possible_keys | 可能用到的索引 key | 实际上使用的索引 key_len | 实际使用到的索引长度 ref | 当使用索引列等值查询时，与索引列进行等值匹配的对象信息 rows | 预估的需要读取的记录条数 filtered | 某个表经过搜索条件过滤后剩余记录条数的百分比 Extra | 一些额外的信息 ## 各个列的作用 > 下文示例中，使用到的 `s1` 和 `s2` 的表结构如下， ```sql CREATE TABLE s1 ( id INT AUTO_INCREMENT, key1 VARCHAR ( 100 ), key2 INT, key3 VARCHAR ( 100 ), key_part1 VARCHAR ( 100 ), key_part2 VARCHAR ( 100 ), key_part3 VARCHAR ( 100 ), common_field VARCHAR ( 100 ), PRIMARY KEY ( id ), INDEX idx_key1 ( key1 ), UNIQUE INDEX idx_key2 ( key2 ), INDEX idx_key3 ( key3 ), INDEX idx_key_part ( key_part1, key_part2, key_part3 ) ) ENGINE = INNODB; ``` ### table 不论查询语句有多复杂，里边包含了多少个表，到最后也是需要对每个表进行 **单表访问** ，所以 `MySQL` 规定 `EXPLAIN` 语句输出的每条记录都对应着某个单表的访问方法，该条记录的 `table` 列代表着该表的表名（有时不是真实的表名字，可能是简称）。 ``` mysql> EXPLAIN SELECT * FROM s1; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ | 1 | SIMPLE | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 9895 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec) mysql> EXPLAIN SELECT * FROM s1 INNER JOIN s2; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------------------------+ | 1 | SIMPLE | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 9895 | 100.00 | NULL | | 1 | SIMPLE | s2 | NULL | ALL | NULL | NULL | NULL | NULL | 9896 | 100.00 | Using join buffer (hash join) | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------------------------+ 2 rows in set, 1 warning (0.01 sec) ``` ### id 查询语句一般都以 `SELECT` 关键字开头，每个 `SELECT` 关键字都对应一个唯一的 `id` ``` mysql> EXPLAIN SELECT * FROM s1; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ | 1 | SIMPLE | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 9895 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec) mysql> EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2) OR key3 = 'a'; +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+ | 1 | PRIMARY | s1 | NULL | ALL | idx_key3 | NULL | NULL | NULL | 9895 | 100.00 | Using where | | 2 | SUBQUERY | s2 | NULL | index | idx_key1 | idx_key1 | 303 | NULL | 9896 | 100.00 | Using index | +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+ 2 rows in set, 1 warning (0.00 sec) ``` 查询优化器可能对涉及子查询的查询语句进行重写，转变为多表查询的操作 ``` mysql> EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key2 FROM s2 ); +----+-------------+-------+------------+--------+---------------+----------+---------+--------------------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+--------+---------------+----------+---------+--------------------+------+----------+--------------------------+ | 1 | SIMPLE | s1 | NULL | ALL | idx_key1 | NULL | NULL | NULL | 9895 | 100.00 | Using where | | 1 | SIMPLE | s2 | NULL | eq_ref | idx_key2 | idx_key2 | 5 | atguigudb1.s1.key1 | 1 | 100.00 | Using where; Using index | +----+-------------+-------+------------+--------+---------------+----------+---------+--------------------+------+----------+--------------------------+ 2 rows in set, 2 warnings (0.01 sec) ``` 当出现 `Union` 关键字时，根据是否去重 ``` mysql> EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2; +------+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+ | 1 | PRIMARY | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 9895 | 100.00 | NULL | | 2 | UNION | s2 | NULL | ALL | NULL | NULL | NULL | NULL | 9896 | 100.00 | NULL | | NULL | UNION RESULT | <union1,2> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary | +------+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+ 3 rows in set, 1 warning (0.00 sec) mysql> EXPLAIN SELECT * FROM s1 UNION ALL SELECT * FROM s2; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ | 1 | PRIMARY | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 9895 | 100.00 | NULL | | 2 | UNION | s2 | NULL | ALL | NULL | NULL | NULL | NULL | 9896 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ 2 rows in set, 1 warning (0.00 sec) ``` > 总结 - `id` 如果相同，可以认为是一组，从上往下顺序执行 - 在所有组中，`id` 值越大，优先级越高，越先执行 - `id` 号每有一个号码，表示一趟独立的查询, 一个 `sql` 的查询趟数越少越好 ### select_type 名称 | 描述 --- | --- SIMPLE | Simple SELECT (not using UNION or subqueries) PRIMARY | Outermost SELECT UNION | Second or later SELECT statement in a UNION UNION RESULT | Result of a UNION SUBQUERY | First SELECT in subquery DEPENDENT SUBQUERY | First SELECT in subquery, dependent on outer query DEPENDENT UNION | Second or later SELECT statement in a UNION, dependent on outer query DERIVED | Derived table MATERIALIZED | Materialized subquery UNCACHEABLE SUBQUERY | A subquery for which the result cannot be cached and must be re-evaluated for each row of the outer query UNCACHEABLE UNION | The second or later select in a UNION that belongs to an uncacheable subquery (see UNCACHEABLE SUBQUERY) > SIMPLE 查询语句中不包含 `UNION` 或者 **子查询** 的查询都算作是 `SIMPLE` 类型，连接查询也算是 `SIMPLE` 类型 ``` mysql> EXPLAIN SELECT * FROM s1; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ | 1 | SIMPLE | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 9895 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec) mysql> EXPLAIN SELECT * FROM s1 INNER JOIN s2; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------------------------+ | 1 | SIMPLE | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 9895 | 100.00 | NULL | | 1 | SIMPLE | s2 | NULL | ALL | NULL | NULL | NULL | NULL | 9896 | 100.00 | Using join buffer (hash join) | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------------------------+ 2 rows in set, 1 warning (0.00 sec) ``` > PRIMARY / UNION / UNION RESULT 对于包含 `UNION` 或者 `UNION ALL` 或者 **子查询** 的查询来说，它是由几个小查询组成的，其中最左边的小查询就是 `PRIMARY` ，其余的小查询就是`UNION` ， `MySQL` 选择使用临时表来完成 `UNION` 查询的去重工作，针对该临时表的小查询就是 `UNION RESULT` ``` mysql> EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2; +------+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+ | 1 | PRIMARY | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 9895 | 100.00 | NULL | | 2 | UNION | s2 | NULL | ALL | NULL | NULL | NULL | NULL | 9896 | 100.00 | NULL | | NULL | UNION RESULT | <union1,2> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary | +----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+ 3 rows in set, 1 warning (0.00 sec) mysql> EXPLAIN SELECT * FROM s1 UNION ALL SELECT * FROM s2; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ | 1 | PRIMARY | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 9895 | 100.00 | NULL | | 2 | UNION | s2 | NULL | ALL | NULL | NULL | NULL | NULL | 9896 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ 2 rows in set, 1 warning (0.00 sec) ``` > SUBQUERY / DEPENDENT SUBQUERY / DEPENDENT UNION 如果包含子查询的查询语句不能够转为对应的 `semi-join` 的形式，并且该子查询是不相关子查询，则该子查询的第一个 `SELECT` 关键字代表的那个查询就是 `SUBQUERY` ``` mysql> EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2) OR key3 = 'a'; +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+ | 1 | PRIMARY | s1 | NULL | ALL | idx_key3 | NULL | NULL | NULL | 9895 | 100.00 | Using where | | 2 | SUBQUERY | s2 | NULL | index | idx_key1 | idx_key1 | 303 | NULL | 9896 | 100.00 | Using index | +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+ 2 rows in set, 1 warning (0.00 sec) ``` 如果包含子查询的查询语句不能够转为对应的 `semi-join` 的形式，并且该子查询是相关子查询，则该子查询的第一个 `SELECT` 关键字代表的那个查询就是 `DEPENDENT SUBQUERY` ，注意的是， `DEPENDENT SUBQUERY` 的查询可能会被执行多次。 ``` mysql> EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2 WHERE s1.key2 = s2.key2) OR key3 = 'a'; +----+--------------------+-------+------------+--------+-------------------+----------+---------+--------------------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------------+-------+------------+--------+-------------------+----------+---------+--------------------+------+----------+-------------+ | 1 | PRIMARY | s1 | NULL | ALL | idx_key3 | NULL | NULL | NULL | 9895 | 100.00 | Using where | | 2 | DEPENDENT SUBQUERY | s2 | NULL | eq_ref | idx_key2,idx_key1 | idx_key2 | 5 | atguigudb1.s1.key2 | 1 | 10.00 | Using where | +----+--------------------+-------+------------+--------+-------------------+----------+---------+--------------------+------+----------+-------------+ 2 rows in set, 2 warnings (0.00 sec) ``` 在包含 `UNION` 或者 `UNION ALL` 的大查询中，如果各个小查询都依赖于外层查询的话，那除了最左边的那个小查询之外，其余的小查询就是 `DEPENDENT UNION` 。 ``` mysql> EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2 WHERE key1 = 'a' UNION SELECT key1 FROM s1 WHERE key1 = 'b'); +------+--------------------+------------+------------+------+---------------+----------+---------+-------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+--------------------+------------+------------+------+---------------+----------+---------+-------+------+----------+--------------------------+ | 1 | PRIMARY | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 9895 | 100.00 | Using where | | 2 | DEPENDENT SUBQUERY | s2 | NULL | ref | idx_key1 | idx_key1 | 303 | const | 1 | 100.00 | Using where; Using index | | 3 | DEPENDENT UNION | s1 | NULL | ref | idx_key1 | idx_key1 | 303 | const | 1 | 100.00 | Using where; Using index | | NULL | UNION RESULT | <union2,3> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary | +----+--------------------+------------+------------+------+---------------+----------+---------+-------+------+----------+--------------------------+ 4 rows in set, 1 warning (0.00 sec) ``` > DERIVED 对于包含 **派生表** 的查询，该派生表对应的子查询就是 `DERIVED` ``` mysql> EXPLAIN SELECT * FROM (SELECT key1, COUNT(*) AS c FROM s1 GROUP BY key1) AS derived_s1 WHERE c > 1; +----+-------------+------------+------------+-------+---------------+----------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+-------+---------------+----------+---------+------+------+----------+-------------+ | 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 9895 | 100.00 | NULL | | 2 | DERIVED | s1 | NULL | index | idx_key1 | idx_key1 | 303 | NULL | 9895 | 100.00 | Using index | +----+-------------+------------+------------+-------+---------------+----------+---------+------+------+----------+-------------+ 2 rows in set, 1 warning (0.00 sec) ``` > MATERIALIZED 当查询优化器在执行包含子查询的语句时，选择将子查询物化之后与外层查询进行连接查询时，该子查询就是 `MATERIALIZED` ``` mysql> EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2); +----+--------------+-------------+------------+--------+---------------------+---------------------+---------+--------------------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------+-------------+------------+--------+---------------------+---------------------+---------+--------------------+------+----------+-------------+ | 1 | SIMPLE | s1 | NULL | ALL | idx_key1 | NULL | NULL | NULL | 9895 | 100.00 | Using where | | 1 | SIMPLE | <subquery2> | NULL | eq_ref | <auto_distinct_key> | <auto_distinct_key> | 303 | atguigudb1.s1.key1 | 1 | 100.00 | NULL | | 2 | MATERIALIZED | s2 | NULL | index | idx_key1 | idx_key1 | 303 | NULL | 9896 | 100.00 | Using index | +----+--------------+-------------+------------+--------+---------------------+---------------------+---------+--------------------+------+----------+-------------+ 3 rows in set, 1 warning (0.00 sec) ``` > UNCACHEABLE SUBQUERY / UNCACHEABLE UNION 不常用 ### partions 匹配的分区信息，略。。。 ### type - system - const - eq_ref - ref - fulltext - ref_or_null - index_merge - unique_subquery - index_subquery - range - index - ALL > system 当表中 `只有一条记录` 并且该表使用的存储引擎的统计数据是精确的，比如 `MyISAM` 、 `Memory` ，那么对该表的访问方法就是 `system` 。 ``` mysql> CREATE TABLE t(i INT) ENGINE=MYISAM; Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO t VALUES(1); Query OK, 1 row affected (0.01 sec) mysql> EXPLAIN SELECT * FROM t; +----+-------------+-------+------------+--------+---------------+------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+--------+---------------+------+---------+------+------+----------+-------+ | 1 | SIMPLE | t | NULL | system | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL | +----+-------------+-------+------------+--------+---------------+------+---------+------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec) ``` > const 当根据主键或者唯一二级索引列与常数进行等值匹配时，对单表的访问方法就是 `const` ``` mysql> EXPLAIN SELECT * FROM s1 WHERE id = 10005; +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ | 1 | SIMPLE | s1 | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL | +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec) mysql> EXPLAIN SELECT * FROM s1 WHERE key2 = 10066; +----+-------------+-------+------------+-------+---------------+----------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+----------+---------+-------+------+----------+-------+ | 1 | SIMPLE | s1 | NULL | const | idx_key2 | idx_key2 | 5 | const | 1 | 100.00 | NULL | +----+-------------+-------+------------+-------+---------------+----------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec) ``` > eq_ref 在连接查询时，如果被驱动表是通过主键或者唯一二级索引列等值匹配的方式进行访问的（如果该主键或者唯一二级索引是联合索引的话，所有的索引列都必须进行等值比较），则对该被驱动表的访问方法就是 `eq_ref` ``` mysql> EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.id = s2.id; +----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-------+ | 1 | SIMPLE | s1 | NULL | ALL | PRIMARY | NULL | NULL | NULL | 9895 | 100.00 | NULL | | 1 | SIMPLE | s2 | NULL | eq_ref | PRIMARY | PRIMARY | 4 | atguigudb1.s1.id | 1 | 100.00 | NULL | +----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-------+ 2 rows in set, 1 warning (0.00 sec) ``` > ref 当通过普通的二级索引列与常量进行等值匹配时来查询某个表，那么对该表的访问方法就可能是 `ref` ``` mysql> EXPLAIN SELECT * FROM s1 WHERE key1 = 'a'; +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+ | 1 | SIMPLE | s1 | NULL | ref | idx_key1 | idx_key1 | 303 | const | 1 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec) ``` > fulltext 全文索引 > ref_or_null 当对普通二级索引进行等值匹配查询，该索引列的值也可以是 `NULL` 值时，那么对该表的访问方法就可能是 `ref_or_null` ``` mysql> EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key1 IS NULL; +----+-------------+-------+------------+-------------+---------------+----------+---------+-------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------------+---------------+----------+---------+-------+------+----------+-----------------------+ | 1 | SIMPLE | s1 | NULL | ref_or_null | idx_key1 | idx_key1 | 303 | const | 2 | 100.00 | Using index condition | +----+-------------+-------+------------+-------------+---------------+----------+---------+-------+------+----------+-----------------------+ 1 row in set, 1 warning (0.00 sec) ``` > index_merge 单表访问方法时在某些场景下可以使用 `Intersection` 、 `Union` 、 `Sort-Union` 这三种索引合并的方式来执行查询，此时对该表的访问方法就是 `index_merge` ``` mysql> EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key3 = 'a'; +----+-------------+-------+------------+-------------+-------------------+-------------------+---------+------+------+----------+---------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------------+-------------------+-------------------+---------+------+------+----------+---------------------------------------------+ | 1 | SIMPLE | s1 | NULL | index_merge | idx_key1,idx_key3 | idx_key1,idx_key3 | 303,303 | NULL | 2 | 100.00 | Using union(idx_key1,idx_key3); Using where | +----+-------------+-------+------------+-------------+-------------------+-------------------+---------+------+------+----------+---------------------------------------------+ 1 row in set, 1 warning (0.01 sec) ``` > unique_subquery `unique_subquery` 是针对在一些包含 `IN` 子查询的查询语句中，如果查询优化器决定将 `IN` 子查询转换为 `EXISTS` 子查询，而且子查询可以使用到主键进行等值匹配的话，那么该子查询就是`unique_subquery` ``` mysql> EXPLAIN SELECT * FROM s1 WHERE key2 IN (SELECT id FROM s2 WHERE s1.key1 = s2.key1) OR key3 = 'a'; +----+--------------------+-------+------------+-----------------+------------------+---------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------------+-------+------------+-----------------+------------------+---------+---------+------+------+----------+-------------+ | 1 | PRIMARY | s1 | NULL | ALL | idx_key3 | NULL | NULL | NULL | 9895 | 100.00 | Using where | | 2 | DEPENDENT SUBQUERY | s2 | NULL | unique_subquery | PRIMARY,idx_key1 | PRIMARY | 4 | func | 1 | 10.00 | Using where | +----+--------------------+-------+------------+-----------------+------------------+---------+---------+------+------+----------+-------------+ 2 rows in set, 2 warnings (0.00 sec) ``` > index_subquery `index_ subquery` 与 `unique_ subquery` 类似，只不过访问子查询中的表时使用的是普通的索引: PS：视频课程里面没讲到这个，只是课件里面有，但是自己测试的时候类型是 `ref` ``` mysql> EXPLAIN SELECT * FROM s1 WHERE common_field IN (SELECT key3 FROM s2 where s1.key1 = s2.key1) OR key3 = 'a'; +----+--------------------+-------+------------+------+-------------------+----------+---------+--------------------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------------+-------+------------+------+-------------------+----------+---------+--------------------+------+----------+-------------+ | 1 | PRIMARY | s1 | NULL | ALL | idx_key3 | NULL | NULL | NULL | 9895 | 100.00 | Using where | | 2 | DEPENDENT SUBQUERY | s2 | NULL | ref | idx_key1,idx_key3 | idx_key1 | 303 | atguigudb1.s1.key1 | 1 | 10.00 | Using where | +----+--------------------+-------+------------+------+-------------------+----------+---------+--------------------+------+----------+-------------+ 2 rows in set, 2 warnings (0.00 sec) ``` > range 如果使用索引获取某些 **范围区间** 的记录，那么就可能使用到 `range` 访问方法 ``` mysql> EXPLAIN SELECT * FROM s1 WHERE key1 IN ('a', 'b', 'c'); +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+ | 1 | SIMPLE | s1 | NULL | range | idx_key1 | idx_key1 | 303 | NULL | 3 | 100.00 | Using index condition | +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+ 1 row in set, 1 warning (0.00 sec) mysql> EXPLAIN SELECT * FROM s1 WHERE key1 > 'a' AND key1 < 'b'; +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+ | 1 | SIMPLE | s1 | NULL | range | idx_key1 | idx_key1 | 303 | NULL | 393 | 100.00 | Using index condition | +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+ 1 row in set, 1 warning (0.00 sec) ``` > index 当可以使用索引覆盖，但需要扫描全部的索引记录时，该表的访问方法就是 `index` ``` mysql> EXPLAIN SELECT key_part2 FROM s1 WHERE key_part3 = 'a'; +----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+ | 1 | SIMPLE | s1 | NULL | index | idx_key_part | idx_key_part | 909 | NULL | 9895 | 10.00 | Using where; Using index | +----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+ 1 row in set, 1 warning (0.00 sec) ``` > ALL 全表扫描 ``` mysql> EXPLAIN SELECT * FROM s1; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ | 1 | SIMPLE | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 9895 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec) ``` ### possible_keys & key 可能用到的索引 和 实际上使用的索引 ``` mysql> EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND key3 = 'a'; +----+-------------+-------+------------+------+-------------------+----------+---------+-------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+-------------------+----------+---------+-------+------+----------+-------------+ | 1 | SIMPLE | s1 | NULL | ref | idx_key1,idx_key3 | idx_key3 | 303 | const | 1 | 5.00 | Using where | +----+-------------+-------+------------+------+-------------------+----------+---------+-------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) ``` ### key_lne `key_len` 是实际使用到的索引长度（即：字节数），帮你检查 **是否充分的利用上了索引** ， **值越大越好** ，主要针对于联合索引，有一定的参考意义。 ``` mysql> EXPLAIN SELECT * FROM s1 WHERE id = 10005; +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ | 1 | SIMPLE | s1 | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL | +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec) mysql> EXPLAIN SELECT * FROM s1 WHERE key_part1 = 'a'; +----+-------------+-------+------------+------+---------------+--------------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+--------------+---------+-------+------+----------+-------+ | 1 | SIMPLE | s1 | NULL | ref | idx_key_part | idx_key_part | 303 | const | 1 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+--------------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec) mysql> EXPLAIN SELECT * FROM s1 WHERE key_part1 = 'a' AND key_part2 = 'b'; +----+-------------+-------+------------+------+---------------+--------------+---------+-------------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+--------------+---------+-------------+------+----------+-------+ | 1 | SIMPLE | s1 | NULL | ref | idx_key_part | idx_key_part | 606 | const,const | 1 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+--------------+---------+-------------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec) mysql> EXPLAIN SELECT * FROM s1 WHERE key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c'; +----+-------------+-------+------------+------+---------------+--------------+---------+-------------------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+--------------+---------+-------------------+------+----------+-------+ | 1 | SIMPLE | s1 | NULL | ref | idx_key_part | idx_key_part | 909 | const,const,const | 1 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+--------------+---------+-------------------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec) mysql> EXPLAIN SELECT * FROM s1 WHERE key_part3 = 'a'; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 9895 | 10.00 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) ``` ### ref 当使用索引列等值查询时，与索引列进行等值匹配的对象信息。比如只是一个常数或者是某个列。 ``` mysql> EXPLAIN SELECT * FROM s1 WHERE key1 = 'a'; +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+ | 1 | SIMPLE | s1 | NULL | ref | idx_key1 | idx_key1 | 303 | const | 1 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec) mysql> EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.id = s2.id; +----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-------+ | 1 | SIMPLE | s1 | NULL | ALL | PRIMARY | NULL | NULL | NULL | 9895 | 100.00 | NULL | | 1 | SIMPLE | s2 | NULL | eq_ref | PRIMARY | PRIMARY | 4 | atguigudb1.s1.id | 1 | 100.00 | NULL | +----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-------+ 2 rows in set, 1 warning (0.00 sec) mysql> EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s2.key1 = UPPER(s1.key1); +----+-------------+-------+------------+------+---------------+----------+---------+------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+----------+---------+------+------+----------+-----------------------+ | 1 | SIMPLE | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 9895 | 100.00 | NULL | | 1 | SIMPLE | s2 | NULL | ref | idx_key1 | idx_key1 | 303 | func | 1 | 100.00 | Using index condition | +----+-------------+-------+------------+------+---------------+----------+---------+------+------+----------+-----------------------+ 2 rows in set, 1 warning (0.00 sec) ``` ### rows 预估的需要读取的记录条数， **值越小越好** ``` mysql> EXPLAIN SELECT * FROM s1 WHERE key1 > 'z'; +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+ | 1 | SIMPLE | s1 | NULL | range | idx_key1 | idx_key1 | 303 | NULL | 413 | 100.00 | Using index condition | +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+ 1 row in set, 1 warning (0.00 sec) ``` ### filtered 某个表经过搜索条件过滤后剩余记录条数的百分比 如果使用的是索引执行的单表扫描，那么计算时需要估计出满足除使用到对应索引的搜索条件外的其他搜索条件的记录有多少条。 ``` mysql> EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND common_field = 'a'; +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+------------------------------------+ | 1 | SIMPLE | s1 | NULL | range | idx_key1 | idx_key1 | 303 | NULL | 413 | 10.00 | Using index condition; Using where | +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+------------------------------------+ 1 row in set, 1 warning (0.01 sec) ``` 对于单表查询来说，这个 `filtered` 列的值没什么意义，更关注在连接查询中驱动表对应的执行计划记录的 `filtered` 值，它决定了被驱动表要执行的次数（即： `rows * filtered` ） ``` mysql> EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.key1 = s2.key1 WHERE s1.common_field = 'a'; +----+-------------+-------+------------+------+---------------+----------+---------+--------------------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+----------+---------+--------------------+------+----------+-------------+ | 1 | SIMPLE | s1 | NULL | ALL | idx_key1 | NULL | NULL | NULL | 9895 | 10.00 | Using where | | 1 | SIMPLE | s2 | NULL | ref | idx_key1 | idx_key1 | 303 | atguigudb1.s1.key1 | 1 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+----------+---------+--------------------+------+----------+-------------+ 2 rows in set, 1 warning (0.00 sec) ``` ### Extra `Extra` 显示一些额外的信息，更准确的理解 `MySQL` 到底将如何执行给定的查询语句 > No tables used 查询语句的没有 `FROM` 子句 ``` mysql> EXPLAIN SELECT 1; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+ 1 row in set, 1 warning (0.00 sec) ``` > Impossible WHERE 查询语句的 `WHERE` 子句永远为 `FALSE` ``` mysql> EXPLAIN SELECT * FROM s1 WHERE 1 != 1; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+ 1 row in set, 1 warning (0.00 sec) ``` > Using where 使用全表扫描来执行对某个表的查询，并且该语句的 `WHERE` 子句中有针对该表的搜索条件 ``` mysql> EXPLAIN SELECT * FROM s1 WHERE common_field = 'a'; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 9895 | 10.00 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) ``` 使用索引访问来执行对某个表的查询，并且该语句的 `WHERE` 子句中有除了该索引包含的列之外的其他搜索条件时 ``` mysql> EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' AND common_field = 'a'; +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+ | 1 | SIMPLE | s1 | NULL | ref | idx_key1 | idx_key1 | 303 | const | 1 | 10.00 | Using where | +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) ``` > No matching min/max row 查询列表处有 `MIN` 或者 `MAX` 聚合函数，但是并没有符合 `WHERE` 子句中的搜索条件的记录时 ``` mysql> EXPLAIN SELECT MIN(key1) FROM s1 WHERE key1 = 'abcdefg'; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No matching min/max row | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------------------+ 1 row in set, 1 warning (0.00 sec) ``` > Using index 查询列表以及搜索条件中只包含属于某个索引的列，也就是在可以使用覆盖索引的情况。比方说下边这个查询中只需要用到 `idx_key1` 而不需要回表操作 ``` mysql> EXPLAIN SELECT key1,id FROM s1 WHERE key1 = 'a'; +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+ | 1 | SIMPLE | s1 | NULL | ref | idx_key1 | idx_key1 | 303 | const | 1 | 100.00 | Using index | +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) ``` > Using index condition 有些搜索条件中虽然出现了索引列，但却不能使用到索引 ``` mysql> EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND key1 LIKE '%a'; +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+ | 1 | SIMPLE | s1 | NULL | range | idx_key1 | idx_key1 | 303 | NULL | 413 | 100.00 | Using index condition | +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+ 1 row in set, 1 warning (0.00 sec) ``` > Using join buffer 在连接查询执行过程中，当被驱动表不能有效的利用索引加快访问速度， `MySQL` 一般会为其分配一块名叫 `join buffer` 的内存块来加快查询速度，也就是我们所讲的 **基于块的嵌套循环算法** ``` mysql> EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.common_field = s2.common_field; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+ | 1 | SIMPLE | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 9895 | 100.00 | NULL | | 1 | SIMPLE | s2 | NULL | ALL | NULL | NULL | NULL | NULL | 9896 | 10.00 | Using where; Using join buffer (hash join) | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+ 2 rows in set, 1 warning (0.00 sec) ``` > Not exists 使用左（外）连接时，如果 `WHERE` 子句中包含要求被驱动表的某个列等于 `NULL` 值的搜索条件，而且那个列又是不允许存储 `NULL` 值 ``` mysql> EXPLAIN SELECT * FROM s1 LEFT JOIN s2 ON s1.key1 = s2.key1 WHERE s2.id IS NULL; +----+-------------+-------+------------+------+---------------+----------+---------+--------------------+------+----------+-------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+----------+---------+--------------------+------+----------+-------------------------+ | 1 | SIMPLE | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 9895 | 100.00 | NULL | | 1 | SIMPLE | s2 | NULL | ref | idx_key1 | idx_key1 | 303 | atguigudb1.s1.key1 | 1 | 10.00 | Using where; Not exists | +----+-------------+-------+------------+------+---------------+----------+---------+--------------------+------+----------+-------------------------+ 2 rows in set, 1 warning (0.00 sec) ``` > Using union / Using intersect / Using sort_union - 如果出现了 `Using intersect(...)` 提示，说明准备使用 `Intersect` 索引合并的方式执行查询，括号中的 `...` 表示需要进行索引合并的索引名称 - 如果出现了 `Using union(...)` 提示，说明准备使用 `Union` 索引合并的方式执行查询 - 如果出现了 `Using sort_union(...)` 提示，说明准备使用 `Sort-Union` 索引合并的方式执行查询。 ``` mysql> EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key3 = 'a'; +----+-------------+-------+------------+-------------+-------------------+-------------------+---------+------+------+----------+---------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------------+-------------------+-------------------+---------+------+------+----------+---------------------------------------------+ | 1 | SIMPLE | s1 | NULL | index_merge | idx_key1,idx_key3 | idx_key1,idx_key3 | 303,303 | NULL | 2 | 100.00 | Using union(idx_key1,idx_key3); Using where | +----+-------------+-------+------------+-------------+-------------------+-------------------+---------+------+------+----------+---------------------------------------------+ 1 row in set, 1 warning (0.00 sec) ``` > Zero limit 当 `LIMIT` 子句的参数为 `0` 时，表示不打算从表中读出任何记录 ``` mysql> EXPLAIN SELECT * FROM s1 LIMIT 0; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Zero limit | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------+ 1 row in set, 1 warning (0.00 sec) ``` > Using filesort 很多情况下排序操作无法使用到索引，只能在内存中（记录较少的时候）或者磁盘中（记录较多的时候）进行排序， `MySQL` 把这种在内存中或者磁盘上进行排序的方式统称为文件排序 某个查询需要使用文件排序的方式执行查询 ``` mysql> EXPLAIN SELECT * FROM s1 ORDER BY common_field LIMIT 10; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+ | 1 | SIMPLE | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 9895 | 100.00 | Using filesort | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+ 1 row in set, 1 warning (0.00 sec) ``` > Using temporary 在许多查询的执行过程中， `MySQL` 可能会借助临时表来完成一些功能，比如去重、排序之类的，比如在执行许多包含 `DISTINCT` 、 `GROUP BY` 、 `UNION` 等子句的查询过程中，如果不能有效利用索引来完成查询， `MySQL` 很有可能寻求通过建立内部的临时表来执行查询。 ``` mysql> EXPLAIN SELECT DISTINCT common_field FROM s1; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+ | 1 | SIMPLE | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 9895 | 100.00 | Using temporary | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+ 1 row in set, 1 warning (0.00 sec) mysql> EXPLAIN SELECT common_field, COUNT(*) AS amount FROM s1 GROUP BY common_field; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+ | 1 | SIMPLE | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 9895 | 100.00 | Using temporary | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+ 1 row in set, 1 warning (0.00 sec) ``` 执行计划中出现 `Using temporary` 并不是一个好的征兆，因为建立与维护临时表要付出很大成本的，所以最好使用索引来替代掉使用临时表。 ## 其他说明 - `EXPLAIN` 不考虑各种 `Cache` - `EXPLAIN` 不能显示 `MySQL` 在执行查询时所作的优化工作 - `EXPLAIN` 不会告诉关于触发器、存储过程的信息或用户自定义函数对查询的影响情况 - 部分统计信息是估算的，并非精确值