Explain详解和索引实践

科技 08-05 来源：单纯哥编程

1. explain中列

1.1 id

id是select的序列号，有几个select就有几个id，id顺序按照select出现顺序增长

id越大优先级越高，id为NULL最后执行

1.2 select_type

表示对应行是简单还是复杂的查询

1.2.1 simple

简单查询，查询不包含子查询和union

1.2.2 primary

复杂查询中最外层的查询

1.2.3 subquery

包含在select中的子查询（不在from子句中）

1.2.4 derived

包含在from子句中的子查询，MySQL会将结果放到一个临时表中，也称为派生表

1.3 type（重点）

1.3.1 system const

关闭mysql5.7新特性对衍生表的合并优化

SET SESSION optimizer_switch = 'derived_merge=off'

表示关联类型和访问类型

依次最优到最差：system > const > eq_ref > ref > range > index > ALL

一般说，保证达到range，最好是ref

EXPLAIN EXTENDED SELECT FROM (SELECT FROM film WHERE id = 1) a

用于primary key或unique key的所有列与常数比较时，表最多有一个匹配行，读取1次，速度快。system是const的特例，表中只有一个元素匹配为system

1.3.2 eq_ref

EXPLAIN SELECT * FROM film_actor LEFT JOIN film ON film_actor.film_id = film.id

primart key和unique key所有被连接使用，最多只返回一条符合条件。是const之外最好的连接类型

1.3.3 ref

和eq_ref比，不使用唯一索引，而是使用普通索引或唯一性使用的部分前缀

简单select查询，name是普通索引（非唯一索引）

EXPLAIN SELECT * FROM film WHERE name = 'film1';

关联表查询，idx_film_actor_id是film和actor_id的联合索引，这里使用了film_actor的左边前缀film_id部分

EXPLAIN SELECT film_id FROM film LEFT JOIN film_actor ON film.id = film_actor.film_id

1.3.4 range

范围扫描通过出现在in、between、>、<、>=等操作

EXPLAIN SELECT * FROM actor WHERE id > 1;

1.3.5 index

扫描全表索引，比ALL快

EXPLAIN SELECT * FROM film;

1.3.6 ALL

全表扫描，MySQL从头到尾的去查询行，这种情况就需要添加索引优化

EXPLAIN SELECT * FROM actor;

1.4 possible_kyes

可以使用那些索引来查询

1.5 key

MySQL实际采用那个索引来优化对表的访问

1.6 key_len

MySQL在索引里使用的字节数，通过这个值可以确定算出使用了索引的那些列

如：film_actor的联合索引idx_film_actor_id由film_id和acotr_id两个ind组成，并且int是4个字节，通过结果中的key_len=4推断使用了第一个列：film_id

EXPLAIN SELECT * FROM film_actor WHERE film_id = 2

1.6.1 key_len计算规则

字符串

char（n）：n字节长度

varchar（n）：2字节存储字符串长度，如果是utf-8，则长度是3n+2

数值类型

tinyint：1字节

smallint：2字节

int：4字节

bigint：8字节

时间类型

date：3字节

timestamp：4字节

datetime：8字节

如果字段允许为NULL，需要1字节记录是否为NULL

1.7 ref

显示key列记录的索引中，表查询用到的列和常量，如 film.id

1.8 rows

查询读取并坚持的行数，并不是结果集里面的行数

1.9 Extra（重点）

1.9.1 Using index

使用覆盖索引

EXPLAIN SELECT film_id FROM film_actor WHERE film_id = 1

1.9.2 Using where

EXPLAIN SELECT * FROM actor WHERE name = 'a'

1.9.3 Using index condition

查询列表不完全被索引覆盖，where条件中有范围

EXPLAIN SELECT * FROM film_actor WHERE film_id > 1

1.9.4 Using temporary

MySQL创建一张临时表来处理，这种需要优化

EXPLAIN SELECT DISTINCT name FROM actor

如果name建立了索引，extra就是Using index

1.9.5 Using filesort

将用外部排序而不是索引排序，要么从内存，要么从磁盘排序，这种情况也要使用索引优化

name未创建索引，会检索actor整个表

EXPLAIN SELECT * FROM actor ORDER BY name

1.10 全值匹配

EXPLAIN SELECT * FROM employees WHERE name = 'LiLei'

EXPLAIN SELECT * FROM employees WHERE name = 'LiLei' AND age = 22

EXPLAIN SELECT * FROM employees WHERE name = 'LiLei' AND age = 22 AND position = 'manager'

1.11 索引最左前缀法则

如果索引了多列，要遵守最左前缀法则，指的是查询从索引的最左前列开始并且不会跳过索引中的列

EXPLAIN SELECT * FROM employees WHERE age = 22 AND position = 'manager'EXPLAIN SELECT * FROM employees WHERE position = 'manager'EXPLAIN SELECT * FROM employees WHERE name = 'LiLei'

1.12 不在索引列上做任何操作（计算、函数、类型转换），会导致索引失效

EXPLAIN SELECT * FROM employees WHERE LEFT(name,3) = 'LiLei'

1.13 存储引擎不能使用索引中范围最右边的列

EXPLAIN SELECT * FROM employees WHERE name = 'Destin' AND age > 23 AND position = 'dev'

范围查询最右侧postion = ‘manager’ 就没有走索引，不然key_len就是140

1.14 尽量使用覆盖索引（索引包含查询列）

EXPLAIN SELECT name, age FROM employees WHERE name = 'Destin' AND age > 23 AND position = 'dev'

1.15 使用!=、<>无法使用索引

如果查询的'LiLei' 字段很多的话，默认也是走索引，和MySQL内部优化有关

EXPLAIN SELECT * FROM employees WHERE name != 'LiLei'

1.16 is null,is not null也无法使用索引

EXPLAIN SELECT * FROM employees WHERE name IS NULL

1.17 like以通配符开头（$abc）索引失效

EXPLAIN SELECT * FROM employees WHERE name LIKE '%Lei'

EXPLAIN SELECT * FROM employees WHERE name LIKE 'Lei%'

解决办法：

使用覆盖索引，查询字段是使用字段

EXPLAIN SELECT name,age,position FROM employees WHERE name LIKE '%Lei'

如果不能是覆盖索引，就需要借助搜索引擎如：ES

1.18 字符串不加单引号索引失效

EXPLAIN SELECT * FROM employees WHERE name = '1000'

EXPLAIN SELECT * FROM employees WHERE name = 1000

因为：这会涉及到一个类型转换，就涉及到函数操作，导致索引失效

1.19 or,in时，MySQL不一定用索引，MySQL会根据内部优化器检索比例、表大小评估是否使用索引

EXPLAIN SELECT * FROM employees WHERE name = 'Lilei' OR name = 'Destin'

1.20 总结

索引：index(a,b,c)

WHERE语句	索引是否被使用
where a = 3	Y，用到了a
where a = 3 and b = 5	Y，用到了a，b
where a = 3 and b =5 and c =4	Y，用到了a，b，c
where b = 3 或 where b = 3 and c = 4 或 where c =4	N
where a = 3 and c = 5	用到了a，但c不可用，b中断了
where a = 3 and b > 5 and c = 5	用到了a和b，c不能用到范围后，b断了
where a = 3 and b like ‘kk%’ and c = 4	Y，用到了a，b，c
where a = 3 and b like ‘%kk’ and c = 4	Y，只用到了a
where a = 3 and b like ‘%kk%’ and c = 4	Y，只用到了a
where a = 3 and b like ‘k%kk%’ and c = 4	Y，用到了a，b，c

like KK%相当于=常量，%KK和%KK% 相当于范围

2. explain extended + 语句

可以列出SQL执行计划更详细的，比如警告，警告可以展示出为什么没用到索引的原因

2. trace工具

EXPLAIN SELECT * FROM employees WHERE name > 'a'

EXPLAIN SELECT name,age,position FROM employees WHERE name > 'a'

EXPLAIN SELECT * FROM employees WHERE name > 'zzz'

对于这种情况，都是范围搜索，为什么有点走索引，有点没有走索引，和MySQL的内部优化器有关，可以通过trace来查看

2.1 开启trace

SET SESSION optimizer_trace='enabled=on',end_markers_in_json=on;

2.2 查看trace

SELECT * FROM employees WHERE name > 'a' ORDER BY position;SELECT * FROM information_schema.OPTIMIZER_TRACE

{
"steps": [
{
"join_preparation": { -- 第一阶段，SQL准备
"select#": 1,
"steps": [
{
"expanded_query": "/* select#1 */ select `employees`.`id` AS `id`,`employees`.`name` AS `name`,`employees`.`age` AS `age`,`employees`.`position` AS `position`,`employees`.`hire_time` AS `hire_time` from `employees` where (`employees`.`name` > 'a') order by `employees`.`position`"
}
]
}
},
{
"join_optimization": { -- 第二阶段，SQL优化
"select#": 1,
"steps": [
{
"condition_processing": {-- 条件处理
"condition": "WHERE",
"original_condition": "(`employees`.`name` > 'a')",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "(`employees`.`name` > 'a')"
},
{
"transformation": "constant_propagation",
"resulting_condition": "(`employees`.`name` > 'a')"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "(`employees`.`name` > 'a')"
}
]
}
},
{
"substitute_generated_columns": {}
},
{
"table_dependencies": [ -- 表依赖信息
{
"table": "`employees`",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": []
}
]
},
{
"ref_optimizer_key_uses": []
},
{
"rows_estimation": [ -- 预估表访问成本
{
"table": "`employees`",
"range_analysis": {
"table_scan": { -- 全表扫描情况
"rows": 100197, -- 扫描行数
"cost": 20330 -- 查询成本
},
"potential_range_indexes": [ --查询可能使用索引
{
"index": "PRIMARY", -- 主键索引
"usable": false,
"cause": "not_applicable"
},
{
"index": "idx_name_age_position", -- 辅助索引
"usable": true,
"key_parts": [
"name",
"age",
"position",
"id"
]
},
{
"index": "idx_hire_time",
"usable": false,
"cause": "not_applicable"
}
],
"setup_range_conditions": [],
"group_index_range": {
"chosen": false,
"cause": "not_group_by_or_distinct"
},
"analyzing_range_alternatives": { -- 分析各个索引使用成本
"range_scan_alternatives": [
{
"index": "idx_name_age_position",
"ranges": [
"a < name" -- 索引使用范围
],
"index_dives_for_eq_ranges": true,
"rowid_ordered": false, -- 使用改索引获取的记录是否安装主键排序
"using_mrr": false,
"index_only": false, -- 是否使用覆盖索引
"rows": 50098, -- 索引扫描行数
"cost": 60119, -- 索引使用成本
"chosen": false, -- 是否选择该索引
"cause": "cost"
}
],
"analyzing_roworder_intersect": {
"usable": false,
"cause": "too_few_roworder_scans"
}
}
}
}
]
},
{
"considered_execution_plans": [
{
"plan_prefix": [],
"table": "`employees`",
"best_access_path": { -- 最优访问路径
"considered_access_paths": [ -- 最终选择的访问路径
{
"rows_to_scan": 100197,
"access_type": "scan", -- 访问类型：scan，全表扫描
"resulting_rows": 100197,
"cost": 20328,
"chosen": true, -- 确认选择
"use_tmp_table": true
}
]
},
"condition_filtering_pct": 100,
"rows_for_plan": 100197,
"cost_for_plan": 20328,
"sort_cost": 100197,
"new_cost_for_plan": 120525,
"chosen": true
}
]
},
{
"attaching_conditions_to_tables": {
"original_condition": "(`employees`.`name` > 'a')",
"attached_conditions_computation": [],
"attached_conditions_summary": [
{
"table": "`employees`",
"attached": "(`employees`.`name` > 'a')"
}
]
}
},
{
"clause_processing": {
"clause": "ORDER BY",
"original_clause": "`employees`.`position`",
"items": [
{
"item": "`employees`.`position`"
}
],
"resulting_clause_is_simple": true,
"resulting_clause": "`employees`.`position`"
}
},
{
"reconsidering_access_paths_for_index_ordering": {
"clause": "ORDER BY",
"steps": [],
"index_order_summary": {
"table": "`employees`",
"index_provides_order": false,
"order_direction": "undefined",
"index": "unknown",
"plan_changed": false
}
}
},
{
"refine_plan": [
{
"table": "`employees`"
}
]
}
]
}
},
{
"join_execution": { -- 第三阶段，SQL执行阶段
"select#": 1,
"steps": [
{
"filesort_information": [
{
"direction": "asc",
"table": "`employees`",
"field": "position"
}
],
"filesort_priority_queue_optimization": {
"usable": false,
"cause": "not applicable (no LIMIT)"
},
"filesort_execution": [],
"filesort_summary": {
"rows": 100003,
"examined_rows": 100003,
"number_of_tmp_files": 28,
"sort_buffer_size": 262056,
"sort_mode": ""
}
}
]
}
}
]
}
结论：全表扫描的承办地域索引扫描，索引MySQL最终选择全表扫描