索引的本质

• 索引是帮助MySQL高效获取数据的排好序的数据结构

• 索引数据结构

  • 二叉树
  • 红黑树
  • Hash表
  • B-Tree（B树）

B-Tree结构

• 叶节点具有相同的深度，叶节点的指针为空
• 所有索引元素不重复
• 节点中的数据索引从左到右递增排序

B+Tree结构

• B+Tree（B-Tree变种）
  • 非叶子节点不存储data，只存储索引（冗余），可以放更多的索引
  • 叶子节点包含所有索引字段
  • 叶子节点用指针连接，提高区间访问的性能

hash结构

• Hash
  • 对索引的key进行一次hash计算就可以定位出数据存储的位置
  • 很多时候hash索引要比B+树更高效
  • 仅能满足 “=” ，“IN” ，不支持范围查询
  • hash冲突问题

MyISAM存储引擎索引实现

• MyISAM索引文件和数据文件是分离的（非聚集）

InnoDB存储引擎索引实现

• InnoDB索引实现（聚集）

  • 表数据文件本身就是按B+Tree组织的一个索引结构文件
  • 聚集索引-叶子节点包含了完整的数据记录
  • 为什么建议InnoDB表必须建主键，并且推荐使用整型的自增主键？（提高效率，如果没有建主键，MySQL会自动加上一列隐式ROWID，而且自增主键在构建B+Tree树的时候效率更高，因为本身自增主键就是有序的）
  • 为什么非主键索引结构叶子节点存储的是主键值？（一致性和节省存储空间）

  主键索引

二级索引（非主键索引）

联合索引

联合索引遵循索引最左前缀原理

什么是索引下推

1. 索引下推（index condition pushdown ）简称ICP，在Mysql5.6的版本上推出，用于优化查询；
2. 在不使用ICP的情况下，在使用非主键索引（又叫普通索引或者二级索引）进行查询时，存储引擎通过索引检索到数据，然后返回给MySQL服务器，服务器然后判断数据是否符合条件 ；
3. 在使用ICP的情况下，如果存在某些被索引的列的判断条件时，MySQL服务器将这一部分判断条件传递给存储引擎， 然后由存储引擎通过判断索引是否符合MySQL服务器传递的条件，只有当索引符合条件时才会将数据检索出来返回给MySQL服务器 ；
4. 索引条件下推优化可以减少存储引擎查询基础表的次数，也可以减少MySQL服务器从存储引擎接收数据的次数。

索引下推使用条件

• 只能用于range、 ref、 eq_ref、ref_or_null访问方法；
• 只能用于InnoDB和 MyISAM存储引擎及其分区表；
• 对存储引擎来说，索引下推只适用于二级索引（也叫辅助索引）;

索引下推的目的是为了减少回表次数，也就是要减少IO操作。对于的聚簇索引来说，数据和索引是在一起的，不存在回表这一说。

• 引用了子查询的条件不能下推；
• 引用了存储函数的条件不能下推，因为存储引擎无法调用存储函数。

问答区

问题1 当复合索引列为（name，age，address）时 以下SQL能使用索引吗？

select * from student where name like 'peng%' and age = 23;

可以，遇到like会中断后续元素的匹配，但只能使用name这个字段，mysql会一直向右匹配直到遇到范围查询（>、<、between、like）就停止匹配。范围列可以用到索引，但是范围列后面的列无法用到索引。即索引最多用于一个范围列，因此如果查询条件中有两个范围列则无法全用到索引。

问题2 索引下推只能存在联合索引里吗？

是的，非联合索引无法使用索引下推。

问题3 索引下推在哪些情况下无法使用？

下推条件遇到子查询

下推条件遇到函数

非InnoDB表和MyISAM表

问题4 索引下推如何开启和关闭？

// 索引下推默认是开启的
set optimizer_switch='index_condition_pushdown=off'; // 关闭
set optimizer_switch='index_condition_pushdown=on'; // 开启

索引下推相关文章：

https://www.cnblogs.com/cy0628/p/16366561.html