5.6之后,MySQL的优化技术

在使用二级索引过滤where条件时,减少回表的次数 以及 MySQL server层和引擎层交互的次数

 

1.数据库如何处理where条件

index key(index first key  index last key)

确定sql查询在索引中的连续范围(起始+终止)

index filter

在起始到终止范围之内,使用索引过滤不符合条件的记录

table filter

不能使用索引处理了,回表(one by one),进行条件过滤

——————————

index first key 的提取规则

  从索引(组合索引)的第一个键值开始

  如果第一个键值存在,并且是=或者>=,则将该条件加入index first key,然后继续判断第二个键值,判断条件一样

  如果遇到一个键值是>(范围之后全失效),则将该条件加入index first key之后,终止

  如果第一个键不存在(带头大哥不能死),终止

index last key 的提取规则

  与index first key 相反

index filter 的提取规则

  从索引的第一个键值开始

  如果索引的第一个键值为=,则跳过,判断第二个键值,判断条件一样

  如果索引的第一列为> >= < <= 中的几种,则,跳过第一列,将其余where 条件中,索引想过的列全部加入index filter中

  如果索引的第一列为> >= < <= 之外的条件,则,将该条件以及where条件中所有与索引相关的列全部加入index filter中

  如果第一列不包含查询条件,则,将所有与索引相关的条件全部加入index filter中

table filter的提取条件

  跟索引无关的列的查询条件,全部加入table filter中

 

2.index condition pushdown

ICP

索引条件下推

提前执行

icp的优化用于 range ref eq_ref等访问方法

innodb 和 myisam

idx_n_a(name,age)

select * from user where name < 'c' and age = 10

(1)不使用ICP

范围之后全失效,仅仅会用到索引的第一列

从索引中one by one地取出符合条件的记录,然后利用主键读取整行

对记录的完整行利用age=10这个条件进行判断

重复上述过程直至没有满足条件的行

(2)使用ICP

从索引中one by one地取出符合条件的记录,然后利用索引的其他字段的条件进行判断

如果符合索引上的全部条件,才利用主键索引读取完整行

使用了ICP之后,explain的结果extra列出现 using indexcondition

 

ICP的使用

(1)需要进行ICP优化的query语句,通常是where条件里面都是范围查询

(2)该查询语句必须用到二级索引

(3)如果是覆盖索引,直接index scan,就没有必要进行ICP了

(4)innodb 和 myisam都可以

(5)ICP优化策略用于range ref eq_ref

(6)5.6之后的版本