MySQL索引

索引介绍

MySQL是常用的数据库，然后如果存入的大量的数据，mysql要如何查询到想要的数据，索引是mysql给出的答案。

索引分类

在MySQL中，存在两类索引：Hash索引，B+树索引。
两者主要的区别为：

1. 查询效率：Hash索引由于是记录的数据对应的hash，所以对应的复杂度级别是O（1）的，而B+树的查询复杂度为O（logn）级别
2. 支持范围查询，hash索引是记录了hash值，好的hash算法应该是能够均匀打散的，所以可以看出，hash索引是不支持范围查询的，而b+树作为一个平衡二叉树，天然能支持范围查询。
3. 最左匹配原则，和上述一样，hash值导致无法做到最左匹配原则，而b+树是支持的。

B+树索引

为什么是B+树

能够作为索引的数据结构有很多种，为什么要选择B+树？

1. 二叉查找树（左子结点<当前结点<右子结点）：在极端情况下，该数据结构会退化为链表，导致全表扫描。
2. 平衡二叉树（满足二叉查找树的前提下，同时满足每个结点的左右子树高度差不能超过1）：平衡二叉树的特性导致，每个结点只能存一个数据，当数据量很大的时候，树的深度过深，最终导致查询效率过低
3. 红黑树：红黑树的子结点也只有两颗，即便有各种策略保证查找性能，但是树的深度还是很高。
4. B树：b树是一颗M叉树，且树的结点存储着所有的数据，相对上面的三棵树来说，b树已经是相对很矮胖了，相对来说查询效率更高。
5. B+树：b+树是在B树的基础上的一种变种，即b+树非叶子结点只存索引值，不存具体的数据，具体的数据存储在叶子结点中，且b+树的叶子结点中，有指针指向右侧的结点。
   a. 相对b树，b+树更为矮胖，在结合大量数据，能够有效的降低磁盘IO，提升性能;
   b. 对于常用的范围查询，B+树只需要利用叶子结点的指针，遍历叶子结点就能做到，而B树则需要不断的重复中序遍历。

索引数据结构

在B+树中，索引的非叶子结点只存储索引的值，而真正的整行数据则存在叶子结点中：

1. 非叶子结点：只存储索引值以及对应的子树的指针；
2. 叶子结点，存储整行数据，且存储指向右侧结点的指针，便于范围查询。

索引分类

关于B+树索引，结合具体使用，容易有个疑问：一个表中的所以那么多，每个索引都存一遍整行数据，是不是有点多余，mysql中针对这种情况，分为两类索引：主键索引和非主键索引。

主键索引（聚簇索引）

主键索引的非叶子结点存储的是主键值，叶子结点存储整行数据。

非主键索引（二级索引）

非主键索引的非叶子结点存储的是索引值，叶子结点存储的是主键值。

查询链路

根据主键索引和非主键索引，在不同的查询语句上，执行流程是：

# 数据库主键为id
select * from table where id = 5;
# age上创建普通索引
select * from table where age = 15;

上述的语句1使用到了主键索引，即只需要遍历主键索引，找到id为5所在的叶子结点，即可找到整行数据。
针对语句2，先使用age索引树，遍历索引树找到主键id，在利用id去id索引树遍历，找到整行数据，所以对于非主键索引，需要遍历两次索引树，这个过程也叫回表，相对的性能会差一点点。

最左匹配原则

在Mysql建立索引的时候，会遵循最左匹配原则，即在检索数据的时候，从联合索引的最左边开始匹配。即如果创建了（a,b）的联合索引，在mysql中该索引树中，存储的顺序是先以a排序，在以b排序的，天然形成了先a后b的有序索引。

创建了abc联合索引，那么同时拥有了a索引，ab索引和abc索引

索引下推

索引下推是mysql5.6引入的优化：在索引遍历的过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表的次数。

id	name	age
1	王一	10
11	李一	10
12	李二	13
13	李三	14
14	张三	10

例如：

creta index name_age_idx on table (name, age);
select * from table where name like '李%' and age = 10;

无索引下推执行流程

执行流程为，找到第一个满足李开头的记录主键：11，然后利用主键进行回表，在根据叶子结点中的整行数据判断age是否符合标准，可以看出，这里面需要回表3次。

有索引下推执行流程

执行流程为：找到第一个满足条件李开头的记录，判断age是否满足条件，满足，查询主键：11，然后进行回表，接下来判断第二个李开头的覆盖索引age值，不满足，向下查询，所以只需要回表一次。

唯一索引&普通索引

1. 唯一索引：创建索引时，限制索引的值必须是唯一的。
2. 普通索引：创建索引时，不附加任何限制条件(唯一、非空等限制)。

select * from table where k = 5;

从索引树的根结点查询，层次遍历定位到叶子结点，然后判断是否继续向下查询。
如果是唯一索引：索引定义了唯一性，则不会往下查询。
如果是普通索引：继续查找下一个记录，直至碰到第一个不符合k等于5的记录，然后退出。

change buffer

mysql的数据是持久化存储到磁盘中的，然而磁盘IO是很耗时的，mysql使用内存进行优化，按页读取数据入内存中，后续的读取会从内存中读取。
针对上面唯一索引和普通索引的查询来看，对于普通索引来说，如果k=5的数据，都在同一页，那么这页读入了内存，则后续的判断都会从内存中读取，内存操作很快，性能没有区别，如果k=5的还存在下一页中，读取下一页数据入内存，进行判断。

change buffer更新数据页

1. 更新的数据在change buffer中，直接进行内存判断，然后更新。
2. 更新的数据不在内存中，对于唯一索引：将数据页读入内存，判断没有冲突，插入值；对于普通索引：将更新记录在change buffer，语句执行结束。
   访问该数据页会触发merge，后台也会有线程定期merge。
   change buffer的优势为：
3. 减少磁盘IO，提升性能；
4. 数据读入内存，需要占用buffer pool，该操作避免占用内存，提高内存利用率。

change buffer适用场景

change buffer真正用于数据更新的时刻，将记录的变更动作缓存，如果一个数据页merge之前，change buffer记录的变更越多，相对利用率越高。

1. 写多读少场景：页面写完后立刻被访问的概率很小，change buffer的利用率很高；
2. 写入后马上查询：更新存入change buffer，由于马上就要访问数据页，所以需要立刻执行merge过长，反而增加的change buffer的维护代价。

索引更新策略

一颗B+树能存多少数据

mysql一页数据大小

mysql一页数据的大小为16k

mysql一页能存多少数据

叶子结点

假设一行数据大小为1k，16k/1k=16，一页叶子结点大约能存16条数据。

非叶子结点

非叶子结点存储的值为索引值和指针，假设主键为bigint型，长度为8字节，指针在InnoDB中大小设置为6字节，一共14字节。

mysql总共存多少数据

一页非叶子结点存储的数据大约有：16384/14=1170，一颗高度为2的B+树能存放1170*16=18720条数据；
高度为3的B+树可以存放：1170 * 1170 * 16 = 21902400.

千万级别的数据，在mysql中大约三次IO便可以查询到。