Mysql索引机制

索引用来做什么

  数据库索引的出现用来提高查询效率。像查字典的目录一样，根据目录去拿到相应的内容能大大提高查询效率。

索引是什么

  索引是一种有序的数据结构。可以用B+Tree来实现。对应mysql的内核，事务和索引都是在存储引擎层实现的。

为什么用B+Tree来实现呢？

我们以下分析用于提高读写效率的数据结构

哈希表

哈希表构成

哈希表是一种哈希结构，存的是键 - 值（key-value）对。
前面是数组，后面跟一个链表。

哈希表查询

Key value都放在链表里面，用key算出的哈希值放在数组里。当需要用key查value时，先调用哈希函数算出哈希值，找到指定数组，再用key去遍历后面的链表，因为链表中同时包含key value，就可以找到指定的value了。

哈希表优缺点

优点：哈希表在数组中的顺序并不是递增的，是由哈希函数算出来的。所以追加新数据的时候执行效率快，只需要在后面追加就可以了。

缺点：由于不是递增的，所以哈希索引做区间查询效率慢，不适合区间查询（需要全表遍历），适合适合等值查询，等值查询时间复杂度为O(1)。

适用场景

hash表这种结构适用只有等值查询的场景
比如Memchached引擎以及一些NoSql引擎（Redis）

有序数组

有序数组查询

有序数组按照某顺序依次进行保存，所以

有序数组在等值查询和范围查询场景中的性能都非常优秀。

当等值查询的时候，采用二分法，时间复杂度为O(Log(N))
当区间查询的时候，采用二分法查询到区间开始位置，从该位置向后遍历直至找到结尾位置。

有序数组优缺点

如果仅看查询效率，有序数组是最好的结构。

但是当对存储进行增删的时候，二分查找到定位后挪动后面的所有数据，消耗成本大。

有序数组适用场景

有序数组适用于静态存储引擎场景。也就是，静态数组静态数据。

搜索树

搜索树特点

二叉搜索树的特点是：父节点左子树所有结点的值小于父节点的值，右子树所有结点的值大于父节点的值

为了维持 O(log(N)) 的查询复杂度，你就需要保持这棵树是平衡二叉树（概念之后补）。为了做这个保证，更新的时间复杂度也是 O(log(N))。

二叉树也会畸变成单链表，所以才有了AVL树通过旋转的方式来维持树的平衡，但后来发现大量的旋转实在是性能不好，所以有了红黑树。都是二叉树，只是约束条件不一样。没有最好的方法只有更适合的方法，要维持某一方面的优势需要牺牲另一方面的优势，就看如何选择了。

搜索树查询

为什么数据库存储使用b+树而不是二叉树？
  因为二叉树树高过高，需要高成本的磁盘 IO。每次查询都需要访问过多节点，即访问数据块过多，而从磁盘随机读取数据块过于耗时。

  索引不止存在内存中，还要写到磁盘上，所以实际上大多数的数据库存储并不使用二叉树。

  搜索树每访问一层就读取一个数据块，不同的数据块之间切换需要进行磁盘IO(耗时)，所以层数决定了访问数据块的多少。为了让一个查询尽量少地读磁盘，就必须让查询过程访问尽量少的数据块。那么，我们就不应该使用二叉树，而是要使用“N 叉”树。这里，“N 叉”树中的“N”取决于数据块的大小。

N叉树如何进行计算

  MySql默认一个节点的长度为16K，当在mysql的InnoDB引擎中申明一个整数字段的索引，那么一个整数（bigint）字段索引的长度为 8B,另外每个索引还跟着6B的指向其子树的指针；所以16K/14B ≈ 1170，也就是说，一个整数字段的索引，N约等于1200 。
  也就是说，树的第四层可以存到1200的三次方，17亿个值。由于树根的数据块总是在内存中，所以一个 10 亿行的表上一个整数字段的索引，查找一个值最多只需要访问 3 次磁盘。而且树的第二层也很大概率在内存中，所以访问磁盘的次数可以更少。

搜索树适用场景

N 叉树由于在读写上的性能优点，以及适配磁盘的访问模式，已经被广泛应用在数据库引擎中了。对于最广泛应用的InnoDB引擎来说，每一个索引在存储引擎中对应一棵B+树

数据库技术发展到今天，跳表、LSM 树等数据结构也被用于引擎设计中 。

怎么给字段加索引

当有一个表T要给列K加索引，建表语句如下：

mysql> create table T(
id int primary key,
 k int not null,
  name varchar(16),
  index (k))
  engine=InnoDB;

关于 InnoDB 的表结构：
1.在 InnoDB 中，每一张表其实就是多个 B+ 树，即一个主键索引树和多个非主键索引树。
2.执行查询的效率，使用主键索引 > 使用非主键索引 > 不使用索引。
3.如果不使用索引进行查询，则从主索引 B+ 树的叶子节点进行遍历。

索引如何工作

每一张表其实就是好几个B+树（每一个索引是一个B+树），树结点的key值就是某一行的主键，value是该行的其他数据。新建索引就是新增一个B+树，查询不走索引就是遍历主B+树。

聚簇索引和非聚簇索引

如上所示，索引分为两种类型：聚簇索引（主键索引）和非聚簇索引（非主键索引）

1. 主键索引： key:主键的值，value:整行数据
2. 非主键索引（二级索引）： key：索引列的值， value:主键的值。

注1：MySQL中聚簇索引一般是主键，没有主键一般是第一个非空字段，没有非空字段会有一个隐式的自增长的值做聚簇索引。
注2: B+树主键索引的叶子结点，应该是页，每个页包含多个行
注3: 叶子节点是双向链表形式的页，单个页内部是顺序链表，链表每个节点是一行数据

基于主键索引和普通索引的查询有什么区别？

  如果语句是 select * from T where ID=500，即主键查询方式，则只需要搜索 ID 这棵 B+ 树；
  如果语句是 select * from T where k=5，即普通索引查询方式，则需要先搜索 k 索引树，得到 ID 的值为 500，再到 ID 索引树搜索一次。这个过程称为回表。

也就是说，基于非主键索引的查询需要多扫描一棵索引树。因此，我们在应用中应该尽量使用主键查询。

索引如何维护

B+ 树为了维护索引有序性，在插入新值的时候需要做必要的维护。

• 如果插入新的行 ID 在页最后，则只需要在最后一条记录后面插入一个新记录。
• 如果新插入的 ID 值在中间，就相对麻烦了，需要逻辑上挪动后面的数据，空出位置。

页分裂

• 如果 该ID所在的数据页已经满了，根据 B+ 树的算法（b+树的语法设计规则，如果最大阶数为n那么当一个节点中存放的数据页达到n的时候，b+就会自平衡，保持他的属性），这时候需要申请一个新的数据页，然后挪动部分数据过去。这个过程称为页分裂。这也是索引推荐自增的原因。
• 除了性能外，页分裂操作还影响数据页的利用率。原本放在一个页的数据，插入了一条数据，现在分到两个页中，整体空间利用率降低大约 50%。

所以，
频繁插入和修改的列最好不要建索引；推荐使用自增主键，就可以保证新的ID一定是在叶子节点最右边，不会影响前面的数据。（防止页分裂）

页合并

当相邻两个页由于删除了数据，利用率很低之后，会将数据页做合并。合并的过程，可以认为是分裂过程的逆过程。所以，很多数据表删除数据都是逻辑删除而非物理删除（防止页合并）。

b+树，原本的规则，插入数据是会可能导致分裂的，因为一个节点只能存储m个元素，超过m个，就会分裂，这里老师讲的不会触发叶子节点的分裂，主要是因为，innoDB对于新插入操作，做了特殊处理，如果判断需要发生分裂时，先判断左(右)兄弟节点是否还有空位，如果有就把最左(右)边的数据迁移到左(右)节点，然后更新维护索引页，最后当前节点插入，从而阻止了分裂发生，如果左右都满了，还是会发生分裂

哪些场景下应该使用自增主键，而哪些场景下不应该？

  自增主键是指自增列上定义的主键，在建表语句中一般是这么定义的： NOT NULL PRIMARY KEY AUTO_INCREMENT。插入新记录的时候可以不指定 ID 的值，系统会获取当前 ID 最大值加 1 作为下一条记录的 ID 值。

从性能和存储空间方面考量，自增主键往往是更合理的选择。

性能层面

• 自增主键的插入数据模式，正符合了我们前面提到的递增插入的场景。每次插入一条新记录，都是追加操作，都不涉及到挪动其他记录，也不会触发叶子节点的分裂。
• 而有业务逻辑的字段做主键，则往往不容易保证有序插入，这样写数据成本相对较高。

存储空间层面

• 二级索引存储的值是主键，如果使用业务字段占用大小不好控制，如果业务字段过长可能会导致二级索引占用空间过大，利用率不高。
• 由于每个非主键索引的叶子节点上都是主键的值。主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小（如果用身份证号做主键，那么每个二级索引的叶子节点占用约 20 个字节，而如果用整型做主键，则只要 4 个字节，如果是长整型（bigint）则是 8 个字节。）

什么场景适合用业务字段直接做主键的呢？KV 场景

• 只有一个索引；
• 该索引必须是唯一索引。

这时候我们就要优先考虑上一段提到的“尽量使用主键查询”原则，直接将这个索引设置为主键，可以避免每次查询需要搜索两棵树。

如果重建（删除，新建）主键索引，会同时去修改普通索引对应的主键索引，性能消耗比较大。
删除重建普通索引貌似影响不大，不过要注意在业务低谷期操作，避免影响业务。

索引的查询-回表

当我们有一个查询是根据索引查询k的值在3到5之间的数据时，mysql内核是这样执行的：

1. 在 k 索引树上找到 k=3 的记录，取得 ID = 300；
2. 再到 ID 索引树查到 ID=300 对应的 R3；
3. 在 k 索引树取下一个值 k=5，取得 ID=500；
4. 再回到 ID 索引树查到 ID=500 对应的 R4；
5. 在 k 索引树取下一个值 k=6，不满足条件，循环结束。

以上从非主键索引树回到主键索引树的过程叫做回表

这个查询过程读了 k 索引树的 3 条记录（步骤 1、3 和 5），回表了两次（步骤 2 和 4）。
注：取下一个值的过程，由于叶子结点是由链表串起来的，实际上是找的下一个指针指向的位置。

如何经过索引的优化拒绝回表的过程？

覆盖索引

使用覆盖索引可以减少树的搜索次数，显著地提高查询性能。覆盖索引是一个常用的性能优化手段。

当二级索引查询结果仅仅是主键（或者所要的信息已经存在于这棵树的叶子结点上），此时不需要回表查主键索引–这样的非主键索引称为覆盖索引

比如：如果执行的语句是 select ID from T where k between 3 and 5，这时只需要查 ID 的值，而 ID 的值已经在 k 索引树上了，因此可以直接提供查询结果，不需要回表。

在一个人员信息表，有必要将证件号和姓名建立联合索引吗？

  当访问频次不高时，没有必要。因为可以根据证件号唯一找到一个人，这样只需要将证件号建立索引，再通过证件号索引进行回表查询姓名即可。建立联合索引将发生空间的浪费。
  当有高频的访问请求的时候就有意义了。给id_card 和 name建立联合索引后，name的值也会被保存在id_card索引树的节点上，这样根据给定id_card的值找到的对应行时，就可以直接获取到name了，而不需要拿着对应的主键再进行回表操作。这将会提升效率，但是也会带来字段维护的代价，所以建立冗余索引来支持覆盖索引需要权衡考虑。

最左前缀原则

最左前缀原则解决的问题

单独为一个不频繁的请求创建一个索引，有点浪费；

不创建索引的话，虽然查询频率不高，但又不能让它全表扫描；

以上这种问题，B+ 树这种索引结构，可以利用索引的“最左前缀”，来定位记录。

最左前缀原则是什么

联合索引先根据第一个字段排序，如果第一个字段有相同的，就按照第二个字段排序，注意，这里仅仅有相同的第一个字段情况下，才会根据第二个字段排序。

有（姓名，年龄）联合索引如下图：

• 当我们要查所有名字是“张三”的人时，可以快速定位到 ID4，然后向后遍历得到所有需要的结果。
• 当要查的是所有名字第一个字是“张”的人，也能够用上这个索引，查找到第一个符合条件的记录是 ID3，然后向后遍历，直到不满足条件为止。

由如上例子可以看出：索引项是按照索引定义里面出现的字段顺序排序的。只要满足最左前缀，就可以利用索引来加速检索。这个最左前缀可以是联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符。

为什么可以用最左前缀原则

MYSQL做词法分析语法分析的时候是通过建立最左子树来建立语法树的，解析的过程也是从左到右所以遵循最左前缀的原则。

在建立联合索引的时候，如何安排索引内的字段顺序。

1. 如果能通过调整顺序少维护一个索引，那么此顺序优先考虑