mysql索引原理

文章中的索引均为介绍mysql中innodb引擎的索引

什么是索引？

索引是一种数据结构（B+树），索引的目的在于提高查询效率，与我们查阅图书所用的目录是一个道理：先定位到章，然后定位到该章下的一个小节，然后找到页数。

磁盘预读？

需要知道3个概念

1. 时间局部性，如果一个信息项正在被访问，那么在近期它很可能还会被再次访问
2. 空间局部性，在最近的将来将用到的信息很可能与正在使用的信息在空间地址上是临近的。
3. 顺序局部性，大部分指令是顺序进行的

所以计算机操作系统做了一些优化，当一次IO时，不光把当前磁盘地址的数据，而是把相邻的数据也都读取到内存缓冲区内，读取的大小是4k或4k的整数倍。
innodb中读取大小为16k

B+树

不同于二叉树每个节点最多含有两个子树，B+树每个节点可拥有多个子树，每个节点是一个磁盘块 ，16k大小，而且非叶子节点只存储键值信息和下一个指针信息，
叶子节点存放整行的数据，每个数据页都通过一个双向链表来进行链接
B+树的高度一般都在2~4层，3层的B+树结构可以保存千万级别的数据。

聚簇索引

聚簇索引：叶子结点存放的是一整行的信息。
非聚簇索引：叶子结点存放的聚簇索引的键值。

聚簇索引，一般是主键索引，如果没有的话会是第一个非空唯一索引，再没有就是innodb中隐藏主键做为聚簇索引。

根据聚簇索引的概念提出两种概念 回表 和 覆盖索引

回表

回表就是先通过非聚簇索引扫描出数据所在的行的主键id，再通过行主键id取出聚簇索引中提供的完整数据，即基于非聚簇索引的查询需要多扫描一棵聚簇索引树。
以三层B+树为例，一次非聚簇索引需要6次磁盘IO ，而聚簇索引只需要3次
所以想要优化查询速度，就要减少磁盘IO次数，覆盖索引是一种方式

覆盖索引

指一个查询语句的执行只用一个索引中就能够取得，不必从聚簇索引中读取。也可以称之为实现了索引覆盖。
这时你就会疑惑了，非聚簇索引中能只保存了主键id数据，除非是只查询主键id的情况啊
这就要提到 联合索引

联合索引

两个或更多个列上的索引被称作联合索引，联合索引又叫复合索引
1个复合索引最多有16列，索引长度最大为256个字节
这样就能实现 指一个查询语句的执行只用一个索引中就能够取得，不必从聚簇索引中读取。
创建联合索引并不是全都是优点，需要看情况建立。
每次插入数据都会维护多个索引，导致数据加入性能过慢
但是 联合索引并不是随便添加就可以，它需要遵守最左前缀原则

最左前缀原则

以你建立可 联合索引（a,b,c,d）为例
1 . 查询条件精确匹配左边连续一个或多个列时，索引可以被使用
例如： a ，（a,b）,(a,b,c), (a,b,c,d)
如果是（a,b,d）a,b索引会生效。
2 . 模糊查询%search 索引失效，search%右侧失效，通配符右边失效
select * from schema where a='1' and b like '%charles' and c='2' and d='3'
以上：只有联合索引中的 a 会生效，其他失效

select * from schema where a='1' and b like 'charles%' and c='2' and d='3'
以上：只有联合索引中的 a，b 会生效，其他失效
3. mysql优化器会自动优化where后条件的位置，例如颠倒位置：
select * from schema where a='1' and b = ‘3’ and d='2' and c='3'
以上：也是会生效的
4 . 查询没有使用第一列，失效
select * from schema b = ‘3’ and c='2' and d='3'
5. 含有函数，表达式，失效
6 . OR关系失效
7 . >、<、between、like 后面失效
8 . order by

然 在MySQL5.6+中 引入 索引下推 进一步优化 联合索引性能

索引下推

mysql5.6+推出，用于优化查询，默认是打开状态。

在使用非聚簇索引（又叫普通索引或者二级索引）进行查询时，存储引擎通过索引检索到数据，然后返回给MySQL服务器，服务器然后判断数据是否符合条件（联合索引失效的条件） 。

索引条件下推优化可以减少存储引擎查询基础表的次数，也可以减少MySQL服务器从存储引擎接收数据的次数。
根据explain解析结果可以看出Extra的值为Using index condition，表示已经使用了索引下推。