Mysql索引

什么是索引

索引的出现就是为了提高查询效率，就相当于字典的目录一样。

索引的优劣势

优势：

• 提高查询效率，降低数据库IO成本
• 通过索引对数据排序，降低CPU消耗

劣势：

• 索引占用磁盘空间
• 更新操作需要额外维护索引文件

索引的数据模型

hash：以key-value形式存放数据，同一个key下拉出链表

优点：
	等值查询或是插入操作效率高。
缺点：
	区间查询效率极低。[X,Y] 
适用场景：
	Memcached或NoSql数据库引擎

有序数组：

优点：
	利用二分法，查询效率高。
缺点：
	除了递增的追加插入外，中间插入效率很低，因为需要挪动后面数据
适用场景：
	特殊的业务如统计人口，以后不会再更新数据

二叉搜索树

• 每个节点最多有2个分叉，左子树和右子树数据顺序左小右大。这样能保证查找折半从而减少IO次数。
• 但二叉查找树可能出现不分叉的极端情况（一边子树远高于另一边）这样查找效率又退化为O(n)。

针对上述情况，我们需要平衡二叉树，它的左右子树高度最多相差1，插入删除数据通过旋转保持平衡。平衡二叉查找树查询性能接近二分法，时间复杂度是 O(log2n)。查询id=6，只需要两次IO。

但平衡二叉树仍然存在不少问题：

1. 时间复杂度与树高相关。每个节点读取对应一次磁盘IO操作（磁盘寻道时间约为10ms）当数据量达到100w，log2n约等于20次IO操作，需要 20*10 = 0.2s，性能较差
2. 对范围查询不友好。需要从根节点多次遍历，效率不高

B树

我们知道磁盘IO是非常耗时的，访问二叉树的每个节点都需要一次IO， 要解决平衡二叉树的第1个问题就要降低树的高度。如何降低？

1. 假设key为bigint=8个字节，每个节点有两个指针（4字节），这样一个节点占用空间为16字节（二叉树节点）
2. 但MySQL的InnoDB的存储单位是数据页，每次IO会读取一页，默认16k的数据量，如果使用二叉树节点那么一页空间只有16字节的有效数据，空间利用率极低。
3. 为了提高空间利用率，在一个节点塞入多个元素，最多可以存入 16k/16 = 1000个索引，这就将二叉树变为多叉树。构建100w数据，只需要2层（1000^2）通过2次IO就可以查询到数据，这种数据结构就是B树。

B树的确在平衡二叉树基础上更进一步，但依然存在优化空间：

• B树依然没有解决平衡二叉树的第2个问题：不支持范围查询，依然需要从根节点多次遍历
• B树的特点之一是节点会存储数据，即key-value都存在节点上，一个元素的占用空间越多，相应的一个节点（一页）存放的元素会越少，树会变高，IO次数变大。

B+树

B+树针对B树存在的问题进行优化：

1. B+树只有叶子节点才会存储数据，非叶子节点只存储键值。叶子节点之间使用双向指针连接，最底层的叶子节点形成了一个双向有序链表。方便范围查找
2. 由于数据存储下沉到叶子节点，非叶子节点可以存放更多的键值，所以相对于B树来说，B+树的树高理论上情况下是比B树要矮的。
3. 因为数据都存储在叶子节点，所以每次查询必定需要读取到叶子节点才结束（相对于B树在非叶子节点就查询到所需数据），但可以通过覆盖索引进行优化

有关B树和B+树的查询示例，可参考下面文章：
一文搞懂MySQL索引所有知识点

InnoDB索引模型

1. InnoDB中表是根据主键顺序以索引的形式存放的，这种存储方式为索引组织表
2. InnoDB索引具体使用B+树，所以数据存放在B+树中
3. 每一个索引都对应一棵B+树
4. InnoDB下每张表默认创建主键索引

1、主键索引（聚簇索引）

InnoDB引擎下建表就建立的索引，也是所有数据存放的地方，因为主键索引的这棵B+树的叶子节点存放的是整行数据

2、非主键索引（普通索引）

有需要就创建，在非主键字段上创建普通索引，它的B+树中叶子节点存放的是主键的值

基于主键和非主键索引查询有何不同？

以主键为条件，只需要搜索主键索引树一棵即可
以非主键为条件，需要搜索普通索引树，找到存放的主键值，再根据主键值到主键树查询，这个过程叫做回表
因此尽量使用主键为条件查询

索引维护

B+树插入删除数据需要必要的维护，例如插入时要挪动后面数据，更麻烦的是当数据页满了，需要页分裂（除了影响性能外有可能空间利用率降低）；删除的时候也会伴随页合并

自增列作为主键到底好不好？
从性能上看，自增列符合递增插入，都在末尾追加插入，不涉及挪动数据和页分裂，成本低；而用业务逻辑字段做主键则不能保证这一点。
从空间利用率上看，如果业务逻辑字段为字符串类型，占用空间一般比int、long大，那么当创建普通索引时，叶子节点存放主键占用空间就会更大。
所以自增列适合作为主键，当然也有业务字段做主键的场景，例如KV场景，只有唯一个索引。

适合随机唯一值作主键的场景：

• 静态数据：常年只做查询，很少更新操作
• 不设置二级索引，就不用考虑存储空间问题
• 直接设置成主键索引，避免回表

另外，UUID（唯一随机）（雪花算法）适合做主键，方便分库分表（分布式）。

3、覆盖索引

基于已存在的索引，如果一个索引能够满足一个请求，不需要回表，就说明这个索引“覆盖”了我们的请求
覆盖索引常常用来优化sql语句。

4、联合索引

基于覆盖索引，如果有一个高频请求，如果不建立联合索引，它就需要回表查询；如果建立联合索引，就刚好形成覆盖索引，不需要回表。
这种创建联合索引也是优化性能的一种手段，但是创建冗余索引必定占用空间，所以收益和代价需要权衡

联合索引的最左前缀原则

它的出现是为了尽可能减少独立的索引，最左前缀可以用于在索引中定位记录。
1、通过调整联合索引内的字段顺序，达到减少一个索引的作用；例如有(a,b)联合索引，一般就不需要单独的a索引，或者说(a,b)，中包括了a索引

(name , age) ---->(“zhangsan”,“20”)

2、当一定需要a , b两个独立索引有需要它们的联合索引时，则考虑空间原则：谁占用的少，谁就在联合索引最前面

5、索引下推

Mysql 5.6以上支持索引下推优化：
在索引遍历过程中，对索引中包含的字段先做判断，过滤掉不符合条件的记录，减少回表次数。
例如再（name，age）索引中，搜索符合条件的主键值时，会顺带判断age符不符合条件，而没有索引下推功能是不会在联合索引遍历时就判断的。

---

B+树索引物理结构

InnoDB的B+树在磁盘上是如何存储的？

数据行

每一行由头部和身体组成。

头部存放额外信息，例如指向下一条记录的next_record

身体存放真实数据。

数据行结构使得行与行之间关联起来，组成有序的单向链表（按照主键排序）

注：行与行之前的单向链表与B+树叶子节点的双向链表不冲突：
这里的单向链表指的是同一个数据页内中的所有行有序排列
数据页之间组成双向链表，具体见下文

数据页

InnoDB一个数据页默认 16 KB大小，可以存放多条数据行。

如何快速定位某行数据？

• 数据页里有个“页目录”
• 页内所有行按规则分成若干组，每组抽出最大主键放入目录中
• 这样就可以通过二分法快速定位某行

页结构：File Header 中的 FIL_PAGE_PREV、FIL_PAGE_NEXT 记录上一个页号和下一个页号，形成双向链表串联所有页

目录项

如何定位在哪一数据页上？

1. 为所有数据页建立目录项（目录项：数据页最小的主键 + 页号）
2. 通过二分法定位即可

另外，由于目录项内容与真实内容类似，可以直接用数据页存储，通过record_type 区分。

二分法定位有前提：必须拿到所有的目录项，但最小单位是页，可能出现一页放不下。

B+树

上面说到，出现一页放不下问题，那就对这些目录项再生成更高一级目录，即大目录嵌小目录，直到最顶级目录只需要一个数据页就能存下。

这就是B+树索引结构。这里的顶级目录就是B+树的根节点，小目录则是B+树的非叶子节点

• 叶子节点：实际用户记录

• 非叶子节点：目录项