- EXPLAIN 用来查看sql的执行计划,其中每个字段的具体含义什么?有哪些值?可以如何帮助我们进行sql优化呢?
- 先来看一个示例:name 上建立了索引。
EXPLAIN select name from t_user A where name='rr';
- 先来看下官方的文档解释:5地址 https://dev.mysql.com/doc/refman/5.7/en/explain-output.html
- 官方文档:
Column JSON Name Meaning(释义) id
select_id
The SELECT
identifier select标识idselect_type
None The SELECT
type select 类型table
table_name
The table for the output row 输出行表 partitions
partitions
The matching partitions 匹配的分区 type
access_type
The join type 连接类型 possible_keys
possible_keys
The possible indexes to choose 可能选择的索引 key
key
The index actually chosen 实际选择的索引 key_len
key_length
The length of the chosen key 选择键的长度 ref
ref
The columns compared to the index 与索引比较的列 rows
rows
Estimate of rows to be examined 预估检查的行数 filtered
filtered
Percentage of rows filtered by table condition 按表条件过滤的行百分比 Extra
None Additional information 额外的信息 - Erxtra 具体值的意思分析;
- Using index: 仅使用索引树中的信息从表中检索列信息,而不必进行其他查找以读取实际行。 对于使用InnoDb表而言,由于主键聚集索引的特性,在extra 列没有看到 Using index 也可能会使用 主键索引。
- Using index condition :测试是否需要读取完整的行记录,需要则根据索引下推到存储引擎进行过滤。
- Using where : 用来限制下一个表匹配的行或者返回客户端的行。表明在索引树中(不包含聚集索引的叶子节点,该叶子节点是存储实际的行记录的)无法找到需要的所有列。
- using where &using index :使用到索引,需要回表查询。
- 聚集索引: Innodb 存储引擎里每个表都有一个默认的或者设置的主键索引,用来构建一棵树,树的叶子节点来保存表记录。叶子节点通过指针连接。
- 索引优化点: 避免回表查询
- 查询语句:
EXPLAIN select name,pass_word from t_user A where name='rr' and pass_word='ss' and is_del=0;
- 结果:
- 分析:(name,pass_word,is_del)建立了一个联合索引,查询的列为name,pass_word 都属于联合索引列,且查询条件 也是使用到了联合索引,故不需要回表查询,在联合索引树上即可得到需要的数据。这也是using_index的意思。
- 下面查询语句改为
EXPLAIN select * from t_user A where name='rr' and pass_word='ss' and is_del=0;
- 分析: 可见也用到了索引,但是extra信息为空。这表明需要回表查询。查询出所有列。
- 我们再来看一下 1(name,index)查询的分析:
-
EXPLAIN select * from t_user A where name='rr' and is_del=0;
-
结果;
-
继续看: 2(name,pass_word)
EXPLAIN select * from t_user A where name='rr' and pass_word='ss';
-
结果:
-
分析: 从结果上看:都用到了索引,但是 1的extra信息为 Using index condition 2 为空。2 的ref 为const,const 1 为const。这可以说明一些问题: 即 2通过索引直接找到查询的数据。1 通过索引查询到数据后,进行了一次索引下推的过滤操作。
-
那么我们可以推断下两种执行情况: 2 通过联合索引直接找到了符合条件的所有叶子节点信息,叶子节点存储了主键的值,再通过主键值在主键的聚集索引上查找到需要的记录,则完成此次查询。
-
1 通过联合索引找到所有符合a的叶子节点,在通过叶子节点的主键值查询到所有记录,然后在得到的所有记录里通过条件is_del=0 过滤记录,然后返回。
-
可见 a c 情况下的效率是低于ab,abc,a.
-
我们再来看 a.b,c 单独建立索引的情况。
-
执行语句:
EXPLAIN select * from t_login_user A where user_name='rr' and user_pwd='ss' and is_del=0;
-
结果:
-
分析: 三列 user_name user_pwd is_del 都建立了单独的索引,但是只是用到了name的索引,using where也代表了使用了回表查询。
-
执行语句:
EXPLAIN select * from t_login_user A where user_name='rr' and is_del=0;
-
结果:
-
分析:也只是用到了name索引。
-
在高性能mysql书里提到,mysql会合并这种单列的索引,这里合并索引的话是通过参数optimizer_switch来控制的。
-
我的mysql版本5.7.10 . 分析结果是没有合并索引,这点有待进一步思考分析。???