12 - explain详解 分析慢的SQL语句

DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `gender` enum('男','女') CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '男',
  `email` varchar(24) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `index_email`(`email`) USING BTREE
) ENGINE = InnoDB  CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- id 查询顺序标识
  mysql> explain select * from user where id in  (select id from user where id >1 );
  +----+-------------+-------+------------+--------+---------------+---------+---------+--------------+---------+----------+-------------+
  | id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref          | rows    | filtered | Extra       |
  +----+-------------+-------+------------+--------+---------------+---------+---------+--------------+---------+----------+-------------+
  |  1 | SIMPLE      | user  | NULL       | range  | PRIMARY       | PRIMARY | 4       | NULL         | 2381420 |   100.00 | Using where |
  |  1 | SIMPLE      | user  | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | test.user.id |       1 |   100.00 | Using index |
  +----+-------------+-------+------------+--------+---------------+---------+---------+--------------+---------+----------+-------------+
  2 rows in set, 1 warning (0.00 sec)

-- select_type 查询类型
  
  SIMPLE          简单查询
  PRIMARY         最外层查询
  SUBQUERY        映射为子查询
  DERIVED         子查询
  UNION           联合
  UNION RESULT    使用联合的结果
  
-- table 正在访问的表名
-- partitions  匹配的分区

-- type 查询时的访问方式，性能：all < index < range < index_merge < ref_or_null < ref < eq_ref < system/const
  -- ALL  全表扫描，对于数据表从头到尾找一遍
    select * from user;
    mysql> explain select * from user where name = 'lin532@';
    +----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
    | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
    +----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
    |  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 4762840 |    10.00 | Using where |
    +----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
    1 row in set, 1 warning (0.00 sec)
    	
    但是ALL的话不一定最慢(如显示少量的行数)
    如果有limit限制，则找到之后就不在继续向下扫描
    explain select * from user where name = 'lin532' limit 1;
    虽然语句都会进行全表扫描,使用了limit，则找到一个后就不再继续扫描。			
    	
    mysql> explain select * from user where name = 'lin532' limit 1;
    +----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
    | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
    +----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
    |  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 4762840 |    10.00 | Using where |
    +----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
    1 row in set, 1 warning (0.00 sec)
    
  -- INDEX  全索引扫描，对索引从头到尾找一遍
    
    explain select id from user;
    
    mysql> explain select email from user;
    +----+-------------+-------+------------+-------+---------------+-------------+---------+------+---------+----------+-------------+
    | id | select_type | table | partitions | type  | possible_keys | key         | key_len | ref  | rows    | filtered | Extra       |
    +----+-------------+-------+------------+-------+---------------+-------------+---------+------+---------+----------+-------------+
    |  1 | SIMPLE      | user  | NULL       | index | NULL          | index_email | 74      | NULL | 4762840 |   100.00 | Using index |
    +----+-------------+-------+------------+-------+---------------+-------------+---------+------+---------+----------+-------------+
    1 row in set, 1 warning (0.00 sec)
    
  -- RANGE  对索引列进行范围查找(age为普通单列索引)
    
    explain select *  from user where age > '22';
    
    如:	between and      in  >   >=  <   <=
    注意：!= 和 > 符号(普通列不走索引,主键走索引)
    
    mysql> explain select *  from user where id !=1;
    +----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
    | id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows    | filtered | Extra       |
    +----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
    |  1 | SIMPLE      | user  | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL | 2381421 |   100.00 | Using where |
    +----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
    1 row in set, 1 warning (0.00 sec)
    
  -- INDEX_MERGE  合并索引，使用多个单列索引搜索
    
    mysql> explain select *  from user where email='lin521@qq.com' or id=1;
    +----+-------------+-------+------------+-------------+---------------------+---------------------+---------+------+------+----------+-----------------------------------------------+
    | id | select_type | table | partitions | type        | possible_keys       | key                 | key_len | ref  | rows | filtered | Extra                                         |
    +----+-------------+-------+------------+-------------+---------------------+---------------------+---------+------+------+----------+-----------------------------------------------+
    |  1 | SIMPLE      | user  | NULL       | index_merge | PRIMARY,index_email | index_email,PRIMARY | 74,4    | NULL |    2 |   100.00 | Using union(index_email,PRIMARY); Using where |
    +----+-------------+-------+------------+-------------+---------------------+---------------------+---------+------+------+----------+-----------------------------------------------+
    1 row in set, 1 warning (0.00 sec)
    		
    mysql> explain select *  from user where email='lin521@qq.com' or id in (11,22,33);
    +----+-------------+-------+------------+-------------+---------------------+---------------------+---------+------+------+----------+-----------------------------------------------+
    | id | select_type | table | partitions | type        | possible_keys       | key                 | key_len | ref  | rows | filtered | Extra                                         |
    +----+-------------+-------+------------+-------------+---------------------+---------------------+---------+------+------+----------+-----------------------------------------------+
    |  1 | SIMPLE      | user  | NULL       | index_merge | PRIMARY,index_email | index_email,PRIMARY | 74,4    | NULL |    4 |   100.00 | Using union(index_email,PRIMARY); Using where |
    +----+-------------+-------+------------+-------------+---------------------+---------------------+---------+------+------+----------+-----------------------------------------------+
    1 row in set, 1 warning (0.00 sec)
    
  -- REF  根据索引查找一个或多个值
    
    mysql> explain select * from user where email='lin521@qq.com';
    +----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
    | id | select_type | table | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra |
    +----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
    |  1 | SIMPLE      | user  | NULL       | ref  | index_email   | index_email | 74      | const |    1 |   100.00 | NULL  |
    +----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
    1 row in set, 1 warning (0.00 sec)
    
  -- EQ_REF,连接时使用primary key 或 unique类型
    
    select tb2.id,user.name from tb2 left join user on tb2.id = user.id;
    
  -- CONST  常量,表最多有一个匹配行,因为仅有一行(如主键),在这行的列值可被优化器剩余部分认为是常数,const表很快,因为它们只读取一次。
    mysql> explain select * from user where id=3;
    +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
    | id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
    +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
    |  1 | SIMPLE      | user  | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
    +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
    1 row in set, 1 warning (0.00 sec)
    
  -- SYSTEM 系统,表仅有一行(=系统表),这是const联接类型的一个特例。
    explain select * from (select id from user where id = 1) as A;
  	

-- possible_keys  可能使用的索引


-- key  真实使用的


-- key_len  MySQL中使用索引字节长度


-- ref列 这一列显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有：const（常量），func，NULL，字段名（例：film.id）


-- rows  mysql估计为了找到所需的行而要读取的行数 ------ 只是预估值

-- filtered 列给出了一个百分比的,这个百分比值和rows列的值相乘,估算出符合条件的记录数 (rows * filtered/100)

-- extra  该列包含MySQL解决查询的详细信息
    -- Using index      表示mysql将使用覆盖索引
    -- Using where      mysql服务器将在存储引擎检索行后再进行过滤
    -- Using temporary  mysql在对查询结果排序时会使用一个临时表
    -- Using filesort  mysql会对结果使用一个外部索引排序，而不是按索引次序从表里读取行
    -- Range checked for each record(index map: N)  没有好用的索引
    .........