MySQL 覆盖索引、最左前缀原则、索引下推

亦凉 2022-02-01 12:09 370阅读 0赞

## 1、覆盖索引 ##

### 1.1 概念 ###

索引是高效找到行的一个方法，当能通过检索索引就可以读取想要的数据，那就不需要再到数据表中读取行了。如果一个索引包含了（或覆盖了）满足查询语句中字段与条件的数据就叫做覆盖索引。

### 1.2 判断标准 ###

使用explain，可以通过输出的extra列来判断，对于一个索引覆盖查询，显示为using index,MySQL查询优化器在执行查询前会决定是否有索引覆盖查询

### 1.3 注意 ###

不是所有类型的索引都可以成为覆盖索引。覆盖索引必须要存储索引的列，而哈希索引、空间索引和全文索引等都不存储索引列的值，所以MySQL只能使用B+Tree索引做覆盖索引  
不同的存储引擎实现覆盖索引都是不同的，并不是所有的存储引擎都支持它们

### 1.4 优点 ###

覆盖索引是一种非常强大的工具，能大大提高查询性能，只需要读取索引而不用读取数据有以下一些优点  
1、索引项通常比记录要小，所以MySQL访问更少的数据  
2、索引都按值的大小顺序存储，相对于随机访问记录，需要更少的I/O  
3、大多数据引擎能更好的缓存索引，比如MyISAM只缓存索引  
4、覆盖索引对于InnoDB表尤其有用，因为InnoDB使用聚集索引组织数据，如果二级索引中包含查询所需的数据，就不再需要在聚集索引中查找了

## 2、最左前缀原则 ##

mysql建立多列索引（联合索引）有最左前缀的原则，即最左优先，如：  
字段有3列，a,b,c，建立索引：idx\_a\_b\_c  
则生效的查询条件有：

1.  a =x
2.  a =x and b=y 或者（b =y and a=x，即顺序不影响，SQL优化器会自动处理，以下同理）
3.  a =x and c =y
4.  a=x and b=y and c=z

## 3、索引下推 ##

“索引条件下推”，称为 Index Condition Pushdown (ICP)是mysql中一个常用的优化，尤其是当mysql需要从一张表里检索数据时。 如果没有ICP，存储引擎将会根据WHERE子句的条件遍历整个表单数据，然后返回给mysql服务器。启用ICP，如果可以通过使用索引的列来满足WHERE条件，MySQL服务器将WHERE条件的这部分推送到存储引擎。然后，存储引擎通过使用索引来确定推送的条件，并且通过这样的方式从表中读取行。 ICP可以减少存储引擎必须访问基础表的次数以及MySQL服务器必须访问存储引擎的次数。

**不使用ICP的查询SQL流程图**：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW9ob25nX2Jv_size_16_color_FFFFFF_t_70]

**过程解释**：  
①：MySQL Server发出读取数据的命令，这是在执行器中执行如下代码段，通过函数指针和handle接口调用存储引擎的索引读或全表表读。此处进行的是索引读。  
if (in\_first\_read)  
\{  
in\_first\_read= false;  
error= (\*qep\_tab->read\_first\_record)(qep\_tab); //设定合适的读取函数，如设定索引读函数/全表扫描函数  
\}  
else  
error= info->read\_record(info);

②、③：进入存储引擎，读取索引树，在索引树上查找，把满足条件的（经过查找，红色的满足）从表记录中读出（步骤④，通常有IO），从存储引擎返回⑤标识的结果。此处，不仅要在索引行进行索引读取（通常是内存中，速度快。步骤③），还要进行进行步骤④，通常有IO。

⑥：从存储引擎返回查找到的多条元组给MySQL Server，MySQL Server在⑦得到较多的元组。

⑦–⑧：⑦到⑧依据WHERE子句条件进行过滤，得到满足条件的元组。注意在MySQL Server层得到较多元组，然后才过滤，最终得到的是少量的、符合条件的元组。

**使用ICP的查询SQL流程图**：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW9ob25nX2Jv_size_16_color_FFFFFF_t_70 1]  
**过程解释**：  
①：MySQL Server发出读取数据的命令，过程同图一。

②、③：进入存储引擎，读取索引树，在索引树上查找，把满足已经下推的条件的（经过查找，红色的满足）从表记录中读出（步骤④，通常有IO），从存储引擎返回⑤标识的结果。此处，不仅要在索引行进行索引读取（通常是内存中，速度快。步骤③），还要在③这个阶段依据下推的条件进行进行判断，不满足条件的，不去读取表中的数据，直接在索引树上进行下一个索引项的判断，直到有满足条件的，才进行步骤④，这样，较没有ICP的方式，IO量减少。

⑥：从存储引擎返回查找到的少量元组给MySQL Server，MySQL Server在⑦得到少量的元组。因此比较图一无ICP的方式，返回给MySQL Server层的即是少量的、符合条件的元组。

另外，图中的部件层次关系，不再进行解释。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW9ob25nX2Jv_size_16_color_FFFFFF_t_70]: /images/20220201/a300a9c855e44699b8e2d953507e9da2.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW9ob25nX2Jv_size_16_color_FFFFFF_t_70 1]: /images/20220201/0017d005a9674f6e8d9a3f26607612df.png