SQL优化系列(一) EXPLAIN详解1

一、概述

在日常工作中，我们要看系统中有哪些慢SQL，会开启慢查询去记录一些执行时间比较久的SQL语句，但是这还没完，找到这些慢SQL后，我们要对这些SQL进行分析，比较常用的方法就是使用EXPLAIN去查看这些SQL语句的执行计划，查看该SQL语句有没有使用上了索引，有没有做全表扫描等。今天我们就带大家一起来学习一下EXPLAIN。

 

二、EXPLAIN用法

EXPLAIN用法非常简单，我们只需要在查询的SQL的最前面加上EXPLAIN关键字即可。

mysql> explain select * from t1;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL |    9 | NULL  |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)

EXPLAIN出来的信息有10列，分别是id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra。

 

三、概要描述

id:选择标识符

select_type:表示查询的类型。

table:输出结果集的表

partitions:匹配的分区

type:表示表的连接类型

possible_keys:表示查询时，可能使用的索引

key:表示实际使用的索引

key_len:索引字段的长度

ref:列与索引的比较

rows:扫描出的行数(估算的行数)

filtered:按表条件过滤的行百分比

Extra:执行情况的描述和说明

 

四、id

id表示的其实就是执行的顺序，分为以下三种情况：

1、 id相同

mysql> EXPLAIN select t2.*
    -> from t1, t2, t3
    -> where t1.id = t2.id
    -> and t1.id = t3.id
    -> and t1.col1 = '';
+----+-------------+-------+--------+--------------------+------------+---------+------------+------+--------------------------+
| id | select_type | table | type   | possible_keys      | key        | key_len | ref        | rows | Extra                    |
+----+-------------+-------+--------+--------------------+------------+---------+------------+------+--------------------------+
|  1 | SIMPLE      | t1    | ref    | PRIMARY,idx_t1_id2 | idx_t1_id2 | 9       | const      |    1 | Using where; Using index |
|  1 | SIMPLE      | t3    | eq_ref | PRIMARY            | PRIMARY    | 8       | test.t1.id |    1 | Using index              |
|  1 | SIMPLE      | t2    | eq_ref | PRIMARY            | PRIMARY    | 8       | test.t1.id |    1 | NULL                     |
+----+-------------+-------+--------+--------------------+------------+---------+------------+------+--------------------------+

id相同时，执行顺序由上至下，上例中的执行顺序就是t1,t3,t2。

2、 id不同

mysql> EXPLAIN SELECT t2.*
    -> FROM t2
    ->  WHERE id = (SELECT id
    ->  FROM t1
    ->  WHERE id = (SELECT t3.id
    ->          FROM t3
    ->          WHERE t3.col1 = ''));
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                               |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
|  1 | PRIMARY     | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Impossible WHERE noticed after reading const tables |
|  2 | SUBQUERY    | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | no matching row in const table                      |
|  3 | SUBQUERY    | t3    | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using where                                         |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
3 rows in set (0.00 sec)

id不相同时，执行顺序由大至小，上例中的执行顺序就是先执行子查询。

 

3、 既有id相同，又有id不同​​​​​​​

mysql> EXPLAIN SELECT t2.* FROM(
    ->   SELECT t3.id
    ->   FROM t3
    ->   WHERE t3.col1 = '') s1, t2
    ->   WHERE s1.id = t2.id;
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table      | type   | possible_keys | key     | key_len | ref   | rows | Extra       |
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------------+
|  1 | PRIMARY     | <derived2> | ALL    | NULL          | NULL    | NULL    | NULL  |    2 | NULL        |
|  1 | PRIMARY     | t2         | eq_ref | PRIMARY       | PRIMARY | 8       | s1.id |    1 | NULL        |
|  2 | DERIVED     | t3         | ALL    | NULL          | NULL    | NULL    | NULL  |    2 | Using where |
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------------+
3 rows in set (0.00 sec)

id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行，上例中执行顺序t3,t2,derived2。