一丶聚簇索引
InnoDB的索引分为两种:
- 聚簇索引:一般创建表时的主键就会被mysql作为聚簇索引,如果没有主键则选择非空唯一索引作为聚簇索引,都没有则隐式创建一个索引作为聚簇索引;
- 辅助索引:也就是非聚簇索引或二级索引,平时我们添加的索引就是辅助索引
聚簇索引并不是一种单独的索引类型,而是一种数据存储方式,就是按照每张表的主键构造一颗B+树,同时叶子节点存放的就是整张表的行记录数据,所以主键索引就默认使用到了聚簇索引;
二丶二级索引(辅助索引)
创建一张表时默认会为主键创建聚簇索引,聚簇(主键)索引的叶子节点存的是整行数据。除了聚簇(主键)索引之外的所有索引都成为二级索引也就是非主键索引,二级索引的叶子节点内容是主键的值,主键长度越小,二级索引的叶子节点就越小,占用的空间也就越小;二级索引在查询需要多扫描一颗索引树,也就是回表,通过覆盖索引和默认的索引下推机制可以表面回表;
三丶回表
回表:就是先通过索引扫描出数据所在的行,在通过行主键索引获取其他字段数据。简单就是说:查询的字段中既有索引字段,又有非索引字段就会发生回表;比如索引字段为name
# 该查询使用了索引,并且索引中只有name的数据,但是却查询了所有字段,此时就会回表获取其他字段的值
# 通过索引name找到数据行,然后再通过主键找到其他字段值
select * from table where name = '';
- 主键索引查询:主键被作为聚簇索引,索引中保存了所有的列数据,可以直接通过主键定位到数据并返回;
- 辅助索引查询:辅助索引中除了包含了索引对应字段值以及主键值,如果查询的字段全是索引字段,就直接通过索引返回数据;如果查询的字段是除了索引字段,还包含其他的字段,则通过辅助索引查找到主键,在通过主键到聚簇索引中查找对应的数据(也就是回表),可以通过聚簇索引和默认的索引下推机制可以避免回表;
四丶索引下推(Indexing Pushdown,简称CIP)
索引下推(Indexing Pushdown,简称CIP)是一种优化查询处理的技术,尤其在数据库系统中常见。它是在数据查询的过程中,将通常由用户查询表达式处理的计算任务下推到数据存储层,比如从磁盘或内存的索引结构中直接完成部分计算,而不是等到所有的数据都被加载到内存后再进行处理。这种技术可以显著提高查询性能,因为很多复杂的聚合函数、过滤条件等可以在接近数据源的地方就得到初步的结果,减少网络传输的数据量和CPU开销。
CIP通常发生在关系型数据库的查询优化阶段,例如SQL查询。当查询包含复杂索引结构时,如果能够利用这些索引来直接获取计算结果,而无需进一步遍历整个表,就能节省大量资源。然而,CIP也依赖于数据库系统的优化策略以及硬件的性能支持。
CIP就是把索引扫描和索引过滤合并在一起处理,过滤后的记录数据下推到存储引擎后的一种索引优化策略。索引条件下推是默认开启的,可以使用系统参数optimizer_switch来控制是否开启
索引下推有点如下:
- 减少了回表的操作次数
- 减少了上传到 MYSQL SERVER层的数据
索引下推使用条件:
- 只能用途range,ref,eq_ref,ref_or_null访问方法;
- 只能用于InnoDB和MyISAM存储引擎及其分表;
- 对InnoDB引擎来说,索引下推只适用于二级索引(非主键索引);
- 引用子查询的条件不能下推;
- 引用了存储函数的条件不能下推,因为存储引擎五大调用存储函数;
- Mysql服务层:用来解析SQL的语法、语义、生成查询计划、接管Mysql存储引擎层上推的数据进行二次过滤等;
- Mysql存储引擎层:按照Mysql服务层下发的请求,通过索引或者全表扫描等方式把数据上传到Mysql分服务层;
- Mysql索引扫描:根据指定索引过滤条件,遍历索引找到索引键对应的主键值后回表过滤剩余过滤条件;
- Mysql索引过滤:通过索引扫描并且基于索引进行二次条件过滤后在回表;
结尾:喜欢的朋友点个赞吧!!!