mysql聚簇索引、mysql覆盖索引、mysql最左匹配、mysql回表、mysql索引下推

目录

聚簇索引

覆盖索引

最左匹配

回表

索引下推

---

在前面学习整理mysql数据结果和索引相关知识的时候，遇到了几个概念，像是回表、聚簇索引、最左匹配等，这篇将常听到的概念整理到一起，便于以后翻阅和小伙伴们阅读，各位看到此博客的小伙伴，如有不对的地方请及时通过私信我或者评论此博客的方式指出，以免误人子弟。多谢！

聚簇索引

聚簇索引有人还叫做聚集索引，是不是聚餐索引要看行数据和索引是否在一起，如InnoDB使用的B+Tree树，叶子节点存放的是主键id和数据，通过主键就可以找到整行数据，也就是说InnoDB至少一个聚簇索引，Innodb通过主键聚集数据，在向innodb表中插入数据时，必须包含一个聚餐索引的key，这个key可以是主键，如果没有主键，会选用唯一索引代替。如果没有唯一索引，innodb会隐式的定义一个主键(自生产的6字节的rowid)来作为聚簇索引。类比下myisam引擎，它的数据和索引是分开存储的，所以说myisam是非聚簇索引。聚簇索引还有一个特征：数据库表行中数据的物理顺序与键值的逻辑（索引）顺序相同。

覆盖索引

覆盖索引就是你要查询的数据列只用从索引中就能够取到，不必读取数据行，mysql可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件。

如在一个表中的列col1、col2、col3上建立索引，查询select col1,col2,col3 from table时，需要的数据都在索引树上不需要获取到整行数据，这就叫使用了覆盖索引；而查询select col1,col2,col3,col4 from table时，col4上没有索引，不能通过索引树取到col4的值，需要获取整行数据后再得到col4，这种就不是覆盖索引。

最左匹配

最左优先，以最左边的为起点任何连续的索引都能匹配上，同时遇到范围查询(>、<、between、like)就会停止匹配。如在col1,col2,col3建立一个联合索引idx_col1_col2_col3(col1,col2,col3)，则(col1)、(col1,col2)和(col1,col2,col3)上的查询可以使用到索引，但(col2)、(col3)、(col2，col3)都使用不了索引，最左前缀针对的是联合索引。

回表

如下图：在innodb中，数据是和聚餐索引在一起的，普通索引(又叫二级索引)只存储主键的id，当根据name查询时，需要先根据name找到符合条件的id，然后再根据id查询整行记录，这就是回表查询。简单理解为先定位主键，再定位行记录。

索引下推

索引下推（index condition pushdown ）简称ICP，在Mysql5.6的版本上推出，用于优化查询。

在不使用ICP的情况下，在使用非主键索引（又叫普通索引或者二级索引）进行查询时，存储引擎通过索引检索到主键id，然后根据id回表获取整行数据，返回给mysql服务器(Server层)，mysql服务器然后判断数据是否符合条件，最终返回给客户端。

在使用ICP的情况下，如果where条件可以使用索引，MySQL服务器将这一部分判断条件传递给存储引擎，过滤操作放在了存储引擎层，数据返回给server层后不需要再次过滤直接返回客户端。

看下网上的一个例子：

一张用户表，有字段id、name、age、address，建立联合索引（name，age）。有这样一个查询：

SELECT * from user where  name like '陈%' and age = 20

没有索引下推时是这样的：

执行过程如下：

会忽略age这个字段，直接通过name进行查询，在(name,age)这课树上查找到了两个结果，id分别为2,1，然后拿着取到的id值一次次的回表查询整行数据，然后对这个完整的行利用其余的条件(本例中为age)进行判断看是否符合，在Server层进行过滤和处理，因此这个过程需要回表两次。

有索引下推之后是这样的：

InnoDB并没有忽略age这个字段，而是在索引内部就判断了age是否等于20，对于不等于20的记录直接跳过，因此在(name,age)这棵索引树中只匹配到了一个记录，此时拿着这个id去主键索引树中回表查询全部数据，这个过程只需要回表一次。

索引条件下推优化可以减少存储引擎查询基础表的次数，也可以减少MySQL服务器从存储引擎接收数据的次数。

set @@optimizer_switch = "index_condition_pushdown=on" -- 启用ICP
set @@optimizer_switch = "index_condition_pushdown=off" -- 禁用ICP