MySQL使用explain检查索引执行计划-白红宇

MySQL使用explain检查索引执行计划

阅读量：2337 次

发布时间：2019-05-10

本文共 3558 字，大约阅读时间需要 11 分钟。

执行计划分析

作用

将优化器选择后的执行计划截取出来.便于管理管判断语句得执行效率.

获取执行

desc SQL语句

explain SQL 语句

这里desc和explain的效果是一样的

mysql> explain select * from test where name=‘tom’\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: test

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 5

Extra: Using where

这里我们看到查寻方式是all全表扫描，possible_keys可能走的索引为空，key实际走的索引页为空，rows扫描了几行才找到指定数据

我们创建一下索引，查看一下效率

mysql> create index name_index on test(name);

mysql> explain select * from test where name=‘tom’\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: test

type: ref

possible_keys: name_index

key: name_index

key_len: 61

ref: const

rows: 1

Extra: Using index condition

查寻方式ref，可能走的索引和实际走的索引均是我们刚刚创建的name_index，rows这里我们只扫描了1行效率大大提升

这里我们最需要关注的几个参数时：type查寻方式 key实际走的索引 row 找到所需数据估计要查寻的行数

select_type

查询类型

SIMPLE 简单查询

PRIMARY 最外层查询

SUBQUERY 映射为子查询

DERIVED 子查询

UNION 联合

UNION RESULT 使用联合的结果

…

table

正在访问的表名

type

查询时的访问方式，性能：all < index < range < index_merge < ref_or_null < ref < eq_ref < system/const

ALL 全表扫描，对于数据表从头到尾找一遍

select * from tb1;

特别的：如果有limit限制，则找到之后就不在继续向下扫描

select * from tb1 where email = ‘seven@live.com’

select * from tb1 where email = ‘seven@live.com’ limit 1;

虽然上述两个语句都会进行全表扫描，第二句使用了limit，则找到一个后就不再继续扫描。

INDEX           全索引扫描，对索引从头到尾找一遍                        select nid from tb1;        RANGE          对索引列进行范围查找                        mysql> desc select * from city where id>2000;                        mysql> desc select  * from city where countrycode like 'CH%';                       对于辅助索引来讲,!= 和not in等语句是不走索引的                       对于主键索引列来讲,!= 和not in等语句是走range                        ===                      mysql> desc select  * from city where countrycode='CHN' or countrycode='USA';                      mysql> desc select  * from city where countrycode in ('CHN','USA');                      一般改写为 union all                     desc                     select  * from city where countrycode='CHN'                     union all                     select  * from city where countrycode='USA';        INDEX_MERGE     合并索引，使用多个单列索引搜索                        select *  from tb1 where name = 'alex' or nid in (11,22,33);        REF             根据索引查找一个或多个值                        select *  from tb1 where name = 'seven';        EQ_REF          连接时使用primary key 或 unique类型                        select tb2.nid,tb1.name from tb2 left join tb1 on tb2.nid = tb1.nid;        CONST           常量                        表最多有一个匹配行,因为仅有一行,在这行的列值可被优化器剩余部分认为是常数,const表很快,因为它们只读取一次。                        select nid from tb1 where nid = 2 ;        SYSTEM          系统                        表仅有一行(=系统表)。这是const联接类型的一个特例。                        select * from (select nid from tb1 where nid = 1) as A;

possible_keys

可能使用的索引

key

真实使用的

key_len

MySQL中使用索引字节长度

varchar(20) utf8mb4

1. 能存20个任意字符

2. 不管存储的时字符,数字,中文,都1个字符最大预留长度是4个字节

3. 对于中文,1个占4个字节

4. 对于数字和字母,1个实际占用大小是1个字节

select length() from test;