Explain 语句和索引

Explain 语句和索引

select_type列:

SIMPLE：简单的select 查询，查询中不包含子查询或者UNION

PRIMARY： 查询钟包含复杂的子部分，最外层查询则被标记为

SUBQUERY： 在SELECT或WHERE列表中包含了子查询

DERIVED： 在FROM列表钟包含的子查询被标记为，MYSQL会递归执行这些子查询，把结果放在临时表里

UNION： 若第二个SELECT出现在UNION 之后，则被标记为UNION

若UNION包含在FROM子句的子查询中，外层SELECT将被标记为DERIVED

UNION RESULT：从UNION表中获取结果的SELECT

---

type列：访问类型排列，从最好到最差依次:

system（查找对象表只有一行数据）>

const（基于主键或者唯一索引唯一值查询，最多返回一条结果）>

eq_ref（表连接时基于主键或非NULL唯一索引完成扫描）>

ref（基于索引的等值查询，或表间等值连接）>

range（利用索引进行范围查询）>

index（执行full_index_scan并且可以通过索引完成结果扫描并且直接从索引中渠道想要的结果数据，可以避免回表）>

ALL（全表扫描）

---

key 列：

实际使用的索引。如果为NULL，则没有使用索引

查询中若使用了覆盖索引，则该索引仅出现在key列表中，possible_key的列为NULL

---

覆盖索引：

查询的字段和所建的索引，数量顺序类型完全一致，所谓“覆盖索引” 就是select的数据列只用从索引中就能够取得，不必从数据表中读取，换句话说查询列要被所使用的索引覆盖。

---

ref 列：

显示索引的哪一列被使用了，如果可能，是一个常数(const)。哪些列或常量被用于查找索引列上的值

---

rows列：

每张表大致有多少列被查询，越小越好

---

一些范围查询，容易出现索引失效的问题



新建一个(catogory_id,views)组合索引，跳过范围查询的comments，然后再执行一次：

优化以后，最后走了索引

---

左连接，小表驱动大表，应该在右表加索引

建索引考虑：

1 建立覆盖索引，可以减少回表的次数。同时Mysql5.6 以后对二级索引做了改进，实现“索引下推”，可以减少二级索引的回表次数，具体可以通过explain 语句的extra列进行查看using index condition

SET optimizer_switch = ‘index_condition_pushdown=off’;

2 MRR(Multi range read )开启。回表的过程涉及到了随机IO,而机械硬盘是很怕随机IO的，因为寻址的开销会很大。开启mrr主要可以通过建立一个buffer，在buffer中根据辅助索引获取的结果集根据主键进行排序，将乱序化为有序，可以用主键顺序访问基表，将随机读转化为顺序读，多页数据记录可一次性读入或根据此次的主键范围分次读入，以减少IO操作，提高查询效率

我们可以通过参数 optimizer_switch 的标记来控制是否使用MRR，当设置mrr=on时，表示启用MRR优化。mrr_cost_based 表示是否通过 cost base的方式来启用MRR.如果选择mrr=on,mrr_cost_based=off,则表示总是开启MRR优化，参数read_rnd_buffer_size 用来控制键值缓冲区的大小。

3 写多读少的服务，可以考虑用普通索引。由于 MySQL 的普通索引中有个 change buffer 的机制。

change buffer的作用是为了降低IO 操作，避免系统负载过高。change buffer将数据写入数据页的过程，叫做merge。

如果需要更新的数据页在内存中时，会直接更新数据页；如果数据不在内存中，会先将更新操作记入change buffer，当下一次读取数据页时，顺带merge到数据页中，change buffer也有定期merge策略。数据库正常关闭的过程中，也会触发merge。

Explain分析的索引一定是最优的吗？

不一定。可能会因为回表，或者排序，让索引走错。
，顺带merge到数据页中，change buffer也有定期merge策略。数据库正常关闭的过程中，也会触发merge。

Explain分析的索引一定是最优的吗？

不一定。可能会因为回表，或者排序，让索引走错。