[面试]一文搞懂Mysql索引

个人基于go开发博客网站，欢迎访问：rayoluo.top

B+树特点：

• 树中的节点并不存储数据本身，而是只是作为索引；
• 将每个叶子节点串在一条链表上，链表中的数据是从小到大有序的，方便按区间查找；
• 每个节点中子节点的个数不能超过m，也不能小于m/2；根节点的子节点个数可以不超过m/2，这是一个例外；
• 一般情况下，根节点会被存储在内存中，其他节点存储在磁盘中。

B+树的数据结构演进：

• 1.给一亿个数据构建二叉查找树索引，索引中会包含大约1亿个节点，每个节点假设占用16字节，那就需要大约1GB的内存空间，索引占用太多内存；2. 将索引存储在硬盘上，但硬盘访问的速度是毫秒级别的，每个节点的读取，都对应一次磁盘IO操作，树的高度就等于每次查询数据时磁盘IO操作的次数。3. 优化：降低树的高度，m叉树。（不管是内存中的数据，还是磁盘中的数据，操作系统都是按页来读取的，一页大小通常是4KB，一次会读取一页的数据。如果要读取的数据量超过一页的大小，就会触发多次IO操作。）所以我们在选择m大小时，要尽量让每个节点的大小等于一个页的大小。

  

在这种方式下，如果我们要求某个区间的数据，只需要拿区间的起始值，在树中进行查找，当查找到某个叶子节点之后，再顺着链表往后遍历，直到链表中的节点数据值大于区间的终止值为止。所有遍历到的数据，就是符合区间值得所有数据。

B+树结构：

索引对写入数据效率的影响：

对于一个B+树，m值时根据页的大小事先计算好的，也就是说，每个节点最多只能有m个子节点。在往数据库中写入数据的过程中，就有可能使索引中某些节点的子节点个数超过m，这个节点的大小超过了一个页的大小，读取这样一个节点就会导致多次磁盘IO操作。解决办法只需要将这个节点分裂成两个节点。但是，节点分裂之后，其上层父节点的子节点个数就有可能超过m个。同样的方法，父节点也分裂为两个节点，这种级联反应从下往上，一直影响到根节点。下图就是插入节点的分裂过程。

删除操作的例子：我们在删除某个数据的时候，也要对应的更新索引节点。这个处理思路有点类似跳表中删除
数据的处理思路。频繁的数据删除，就会导致某些结点中，子节点的个数变得非常少，长此以往，如果每个节点的子节点都比较少，势必会影响索引的效率。我们可以设置一个阈值。在 B+ 树中，这个阈值等于 m/2。如果某个节点的子节点个数小于 m/2，我们就将它跟相邻的兄弟节点合并。不过，合并之后结点的子节点个数有可能会
超过 m。针对这种情况，我们可以借助插入数据时候的处理方法，再分裂节点。

B树和B+树的区别

参考：https://www.cnblogs.com/nullzx/p/8729425.html

http://xianzilei.cn/blog/31

主键索引和非主键索引

根据叶子节点的内容，索引类型分为主键索引和非主键索引。主键索引的叶子节点存的是整行数据。在InnoDB里，主键索引也被称为聚簇索引。

非主键索引的叶子节点内容是主键的值。在InnoDB里，非主键索引也被称为二级索引。

聚簇索引和非聚簇索引

对于聚簇索引表来说（左图），表数据是和主键一起存储的，主键索引的叶结点存储行数据(包含了主键值)，二级索引的叶结点存储行的主键值。使用的是B+树作为索引的存储结构，非叶子节点都是索引关键字，但非叶子节点中的关键字中不存储对应记录的具体内容或内容地址。叶子节点上的数据是主键与具体记录(数据内容)。

对于非聚簇索引表来说（右图），表数据和索引是分成两部分存储的，主键索引和二级索引存储上没有任何区别。使用的是B+树作为索引的存储结构，所有的节点都是索引，叶子节点存储的是索引+索引对应的记录的数据。

叶子节点data域保存完整数据记录的就是聚簇索引，叶子节点data域只保存主键值或数据地址的就是非聚簇索引。

聚簇索引的优点

1.当你需要取出一定范围内的数据时，用聚簇索引也比用非聚簇索引好。

2.当通过聚簇索引查找目标数据时理论上比非聚簇索引要快，因为非聚簇索引定位到对应主键时还要多一次目标记录寻址,即多一次I/O。

3.使用覆盖索引扫描的查询可以直接使用页节点中的主键值。

聚簇索引的缺点

1.插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID列为主键。

2.更新主键的代价很高，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为不可更新。

3.二级索引访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据。

二级索引的叶节点存储的是主键值，而不是行指针（非聚簇索引存储的是指针或者说是地址），这是为了减少当出现行移动或数据页分裂时二级索引的维护工作，但会让二级索引占用更多的空间。

4.采用聚簇索引插入新值比采用非聚簇索引插入新值的速度要慢很多，因为插入要保证主键不能重复，判断主键不能重复，采用的方式在不同的索引下面会有很大的性能差距，聚簇索引遍历所有的叶子节点，非聚簇索引也判断所有的叶子节点，但是聚簇索引的叶子节点除了带有主键还有记录值，记录的大小往往比主键要大的多。这样就会导致聚簇索引在判定新记录携带的主键是否重复时进行昂贵的I/O代价。

参考：https://www.jianshu.com/p/496e14aacd55

https://juejin.cn/post/6844903845554814983

自增主键?

自增主键是自增列上定义的主键，在建表语句中一般这么定义：NOT NULL PRIMARY KEY AUTO_INCREMENT。插入新纪录时可以不指定ID的值，系统会获取当前ID最大值加1作为下一条记录的ID值。也就是说，自增主键的插入数据模式，每次插入一条新纪录都是追加操作，不设计挪动其他记录，也不会触发叶子结点的分裂。

覆盖索引

• 当sql语句中的select列（查询的字段）和where列（条件字段）都在一个索引中，则不需要进行回表，这就是索引覆盖。
• 例如：select id, name from users where name = ‘jack’; (对name建立辅助索引)。这个示例中由于对name字段建立辅助索引，而辅助索引每个叶子节点的data域保存主键值，则不需要进行回表操作，即可拿到id和name。
• 所有不需要回表的查询操作都是索引覆盖。
• 可利用索引覆盖来减少IO操作，从而提高查询效率。比如select id, name,age from users where name = ‘jack’; 可对name和age建立联合索引，从而避免回表。

联合索引

创建联合索引的例子如create idx_un_userid_username on user(id,name)，联合索引在B+树中如下图所示。可以看到，索引项是按照索引定义里面出现的顺序排序的。B+树这种索引结构，可以利用索引的“最左前缀”来定位记录。

可以看到，索引项是按照索引定义里面出现的字段顺序排序的，当你的逻辑需求是查所有名字是“张三”的人，可以快速定位到ID4，然后向后遍历得到所有需要的结果。如果你要查找的是所有名字第一个字是“张”的人，你的SQL语句的条件是“where name like ‘张%’”，这时你也能够用上这个索引，查找到第一个符合条件的记录时ID3，然后向后遍历，直到不满足条件为止。

可以看到，不只是索引的全部定义，只要满足最左前缀，就可以利用索引来加速检索。这个最左前缀可以是联合索引的最左N个字段，也可以是字符索引的最左M个字符。

因为可以支持最左前缀，所以当已经有了(a,b)这个联合索引，一般就不需要单独在a上建立索引了。

索引下推

索引下推：like 'hello%’and age >10 检索，MySQL5.6版本之前，会对匹配的数据进行回表查询。5.6版本后，会先过滤掉age<10的数据，再进行回表查询，减少回表率，提升检索速度