聚集索引和非聚集索引(普通索引)
- 聚簇索引:数据行的物理顺序与列值(一般是主键的那一列)的逻辑顺序相同,一个表中只能有一个聚簇索引。叶子节点存储索引和行记录,聚簇索引查询会很快,因为可以直接定位到行记录
- 非聚簇索引:该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同,一个表中可以拥有多个非聚集索引。叶子节点存储聚簇索引值(主键id),需要扫码两遍索引树,先通过普通索引定位到主键值id,再通过聚集索引定位到行记录
回表查询
回表查询可以理解为普通索引的查询,先定位主键值,再定位行记录,它的性能较扫一遍索引树更低。
- 一般我们自己建的索引不管是单列索引还是联合索引,其实一个索引就对应着一颗独立的索引B+树,索引B+树的节点仅仅包含了索引里的几个常见的字段的值以及主键值。
- 即使我们根据索引树按照条件找到了需要的数据,那也仅仅是索引里的几个字段的值和主键值,万一你要查询select * 还需要很多其他的字段,那就还需要一个回表查询:根据主键跑到主键的聚簇索引里去找,聚簇索引的叶子节点是数据页,找到数据页才能把一行数据的所有字段值提取出来。
减少回表查询
覆盖索引
索引覆盖,即将查询sql中的字段添加到联合索引里面,只要保证查询语句里面的字段都在索引文件中,就无需进行回表查询;
索引下推(系统优化)
在MySQL5.6的版本中推出,用于优化查询。在索引遍历的过程中,对索引中包含的字段先做判断,直接过滤掉不满足条件的记录,减少回表次数。
关闭索引下推:set optimizer_switch=‘index_condition_pushdown=off’
子查询
优化超多分页的场景。查询条件放到子查询中,子查询只查主键,然后使用子查询中确认的主键关联其他属性字段。
select 各种字段
from table_name t1
right join
(select 主键 from table_name where 筛选条件 limit 0, 10)t2
on t1.主键 = t2.主键
子查询使用主键id进行查询,则查询出来的记录包含了所有列值,然后利用right join将所需查询结果与子查询进行关联。
问题
非聚簇索引一定会回表查询吗?
不一定。这涉及到查询语句所要求的字段是否全部命中了索引,如果是,那么就不需要回表查询