Mysql查看执行计划及索引使用

使用背景

当sql运行比较耗时的时候，可以进行sql优化，比如加索引，调整sql的结构等等。我们看sql运行的状态等信息时，可以通过执行计划来参考。

explain + sql 语句查看 执行计划。

例如：EXPLAIN SELECT * FROM `g_play_log` WHERE `user_id` = '6178962'

上图为sql的执行计划查询结果，通过结果可以看到走的是全表查询（type=ALL），需要涉及到的数据行数643420，这样查询的效率不高，可以对表做一个简单的优化之后，再看看执行计划。因为通过where条件，条件为user_id，可以对user_id建一个索引，建好索引后，再看执行计划的结果如下。

通过两次的结果对比可以看到，加上索引之后，查询会走索引（type=ref，key=user_id_index），需要涉及到的数据行数61，这样查询的效率会大大的提高。

执行计划字段说明

select_type：

SIMPLE（简单查询），
PRIMARY（最外层查询），
SUBQUERY（映射为子查询），
DERIVED（子查询），
UNION（联合），
UNION RESULT（使用联合的结果）

table : 正在访问的表名

type：

ALL（全数据表扫描），
index（全索引表扫描），
RANGE（对索引列进行范围查找），
INDEX_MERGE（合并索引，使用多个单列索引搜索），
REF（根据索引查找一个或多个值），
EQ_REF（搜索时使用primary key 或 unique类型），
CONST（常量，表最多有一个匹配行,因为仅有一行,在这行的列值可被优化器剩余部分认为是常数,const表很快,因为它们只读取一次），
SYSTEM（系统，表仅有一行(=系统表)。这是const联接类型的一个特例）

说明：性能：all < index < range < index_merge < ref_or_null < ref < eq_ref < system/const（性能在 range 之下基本都可以进行调优）

possible_keys：可能使用的索引

key：真实使用的索引

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

rows：mysql 预估为了找到所需的行而要读取的行数

extra：

Using index（此值表示mysql将使用覆盖索引，以避免访问表），
Using where（mysql 将在存储引擎检索行后再进行过滤，许多where条件里涉及索引中的列，当它读取索引时，就能被存储引擎检验，因此不是所有带where子句的查询都会显示“Using where”。有时“Using where”的出现就是一个暗示：查询可受益于不同的索引），
Using temporary（mysql 对查询结果排序时会使用临时表），
Using filesort（mysql会对结果使用一个外部索引排序，而不是按索引次序从表里读取行。mysql有两种文件排序算法，这两种排序方式都可以在内存或者磁盘上完成，explain不会告诉你mysql将使用哪一种文件排序，也不会告诉你排序会在内存里还是磁盘上完成），
Range checked for each record(index map: N)（没有好用的索引，新的索引将在联接的每一行上重新估算，N是显示在possible_keys列中索引的位图，并且是冗余的）

使用索引的情况

1.使用 like 语句时，%在右边才会使用索引。

2.or 条件中有未建立索引的列索引失效。

3.条件的类型不一致索引失效。

4.使用!=时部分情况索引失效。（如果是主键，则会走索引）

5.使用>时部分情况索引失效。（如果是主键或索引是整数类型，则会走索引）

6.order by部分情况索引失效。（如果 order by 的列是主键或索引是整数类型，则会走索引）

7.组合索引（遵循最左前缀）

# 若 name 和 email 组成组合索引
create index ix_name_phone on user(name, phone);
# 使用结果
name and phone -- 使用索引
phone and name -- 不使用索引
name           -- 使用索引
phone          -- 不使用索引