回表和索引覆盖

聚簇索引和非聚簇索引

先理解MySQL B+树的原理
mysql有很多索引,每一个索引自成一个树结构
1.加载数据时,先加载聚簇索引(一般来说,聚簇索引为该表的主键),非叶子节点存储叶子结点的地址,叶子结点存储聚簇索引值和该条数据的所有字段值
2.然后加载非聚簇索引(一般来说是除主键之外的唯一索引 普通索引等所有索引, 非聚簇索引也叫二级索引),非叶子节点存储叶子结点的地址,叶子结点存储索引的值和该条数据对应的聚簇索引的值

 

 

回表
如果索引的列在 select 所需获得的列中（因为在 mysql 中索引是根据索引列的值进行排序的，所以索引节点中存在该列中的部分值）或者根据一次索引查询就能获得记录就不需要回表，
如果 select 所需获得列中有大量的非索引列，索引就需要到表中找到相应的列的信息，这就叫回表。

where后面使用非聚簇索引,并且select的字段有除了该非聚簇索引树之外的字段(也就是在该非聚簇索引的数据, 取不到所有select的字段)就会回表
-->找到非聚簇索引树的叶子结点,拿到聚簇索引的值;再去聚簇索引树根据聚簇索引的值查到叶子结点,拿到该条数据的值(回表)
比如说code是索引 select code,name from table where code = '...'; name值在code索引树拿不到,就会拿着code的叶子结点存储的id值, 去id的索引树在查, 这个就叫回表

题外话(where后面没有使用任何索引:直接遍历聚簇索引树的叶子结点,拿到该条数据的值)

 

 

索引覆盖
为了避免回表,这里就引出了索引覆盖的解决方案
只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表，速度更快。触发索引覆盖,explain的输出结果Extra字段为Using index。
当select字段值为多个时,可以建立联合索引(核心解决方法)
还是这个例子 select code,name from table where code = '...'; 避免回表, 我们可以把code和name设置为联合索引, 这个就可以在code和name的联合索引树中取到select的所有字段,不用再去id树查

 

 

 

题外话(where后面没有使用任何索引:直接遍历聚簇索引树的叶子结点,拿到该条数据的值,不像索引查询,直接命中数据)
顺序查效率高
随机查(随机或者频繁查都会触发随机查)

主键自增(插入数据时,直接在尾部添加,不会移动树结构前面的节点)
UUID会在前面的节点插入,然后树会发生调整,量大时会有问题
业务允许时,可以主键自增,不允许就要慎用了