使用背景

当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          -- 不使用索引