Mysql索据-Mysql的innodb引擎为什么要使用b+tree作为索引数据结构？

Promise_J_Z

已于 2023-01-10 09:44:50 修改

阅读量698

点赞数 1

分类专栏： Mysql 文章标签： mysql 数据库

于 2023-01-09 21:47:59 首次发布

本文链接：https://blog.csdn.net/Promise_J_Z/article/details/128621432

版权

Mysql 专栏收录该内容

13 篇文章 0 订阅

订阅专栏

文章详细介绍了数据库索引的概念、优缺点，以及索引的分类，包括普通索引、唯一索引、主键索引等。重点讲解了B树和B+树的数据结构，分析了InnoDB选择B+树的原因，并探讨了索引在查询优化中的作用，如聚簇索引和非聚簇索引的选择，以及何时需要回表查询。此外，还讨论了索引的创建策略和可能导致查询慢的情况。

摘要由CSDN通过智能技术生成

索引？

什么是索引？索引有什么优点？索引有什么缺点？

索引的分类

按照功能分类：

按照数据结构分类

索引？

什么是索引？索引有什么优点？索引有什么缺点？

索引：索引是帮助mysql高效获取数据排好序的数据结构，一般来说索引本身也很大，不能全部存在内存中，因此索引往往保存在硬盘中

优点：提高了检索效率，降低了数据io成本，通过索引对数据进行排序，降低了数据排序的成本，降低了cpu的消耗

缺点：虽然索大大提高了查询速度，但是同时降低了更新表的速度，比如对表进行更新了之后，mysql不仅要更新数据，还要更新索引文件

索引的分类

按照功能分类：

普通索引：最基本的索引，没有任何限制
唯一索引：索引列的值必须唯一，但允许空，如果是组合索引，列值组合必须唯一
主键索引： 一种特殊的唯一索引，不允许空，在建表时主键列同时创建主键索引
联合索引: 将单列索引进行组合
外键索引：只有InnoDB支持，用来保证数据一致性，完整性和实现级联操作
全文索引：快速匹配全部文档的方式，innodb5.6版本后才支持。memory不支持

按照数据结构分类

B Tree索引：Mysql使用最频繁的索引，是Innodb和myisam存储引擎默认的索引类型，底层是基于b+tree
hash索引： Mysql中menmory存储引擎默认支持的索引类型

索引常见问题

什么是聚簇索引？何时使用聚簇索引与非聚簇索引

聚簇索引：将数据存储与索引放到了一块，找到索引也就找到了数据
非聚簇索引：将数据存储于索引分开结构，索引结构的叶子节点指向了数据的对应行，myisam通过key_buffer把索引先缓存到内存中，当需要访问数据时（通过索引访问数据），在内存中直接搜索索引，然后通过索引找到磁盘相应数据，这也就是为什么索引不在key buffer命中时，速度慢的原因
密集索引和稀疏索引对应聚簇索引和非聚簇索引

联合索引是什么？为什么需要注意联合索引中的顺序？

联合索引：MySQL可以使用多个字段同时建立一个索引，叫做联合索引。在联合索引中，如果想要命中索引，需要按照建立索引时的字段顺序挨个使用，否则无法命中索引。

为什么要注意排序？

MySQL使用索引时需要索引有序，假设现在建立了"name，age，school"的联合索引，那么索引的排序为: 先按照name排序，如果name相同，则按照age排序，如果age的值也相等，则按照school进行排序。

当进行查询时，此时索引仅仅按照name严格有序，因此必须首先使用name字段进行等值查询，之后对于匹配到的列而言，其按照age字段严格有序，此时可以使用age字段用做索引查找，以此类推。因此在建立联合索引的时候应该注意索引列的顺序，一般情况下，将查询需求频繁或者字段选择性高的列放在前面。此外可以根据特例的查询或者表结构进行单独的调整。

如上图所示他们是按照a来进行排序，在a相等的情况下，才按b来排序。

因此，我们可以看到a是有序的1，1，2，2，3，3。而b是一种全局无序，局部相对有序状态！什么意思呢？

从全局来看，b的值为1，2，1，4，1，2，是无序的，因此直接执行b = 2这种查询条件没有办法利用索引。

从局部来看，当a的值确定的时候，b是有序的。例如a = 1时，b值为1，2是有序的状态。当a=2时候，b的值为1,4也是有序状态。因此，你执行a = 1 and b = 2是a,b字段能用到索引的。而你执行a > 1 and b = 2时，a字段能用到索引，b字段用不到索引。因为a的值此时是一个范围，不是固定的，在这个范围内b值不是有序的，因此b字段用不上索引。

综上所示，最左匹配原则，在遇到范围查询的时候，就会停止匹配。

b+tree中innodb不需要回表查询吗？myisam一定会回表查询吗？

都不一定，先说innodb，在innodb中主键索引是采用聚簇索引的形式有以下三种情况

如果通过主键查找，那么命中的时候，主键索引中就会包含那一行记录，就不用回表查询
实现覆盖索引，当通过联合索引查询是，查询的数据刚好是联合索引的列，就不用回表查询，例如select name,age from student where name=‘zhangsan’ and age=18，联合索引（name，age），这种情况就不用回表，因为查找的内容就是索引本身
如果通过普通查找所有列例如select * from student where name=‘zhangsan’，索引为name，这时候是需要回表查询的

再说myisam，myisam中都是非聚簇索引，所以仅仅当实现索引覆盖的时候不用回表，其他情况都需要回表查询

什么情况使用了索引，查询还是慢

索引全表扫描
索引过滤性不好
频繁回表的开销

什么情况下适合建索引什么适合下不适合建索引？

适合建立索引的情况

主键自动创建索引
频繁作为查询条件的字段应该创建索引
查询中与其他表关联的字段，外键关系建立索引
排序和分组的字段

不适合建立索引的情况

频繁增删改的字段不适合创建索引
where条件里的字段不适合创建索引
数据重复且分布均匀的数据。
表的记录过少（建立索引无意义）

b+tree中3层树高能存储多少条数据？

假设每个索引使用的是bigint，占8个字节，然后指针占6个字节，也是说第一层中一个索引就占了14个字节，而mysql中有页的概念，一页大小为16kb。第一次能存放的索引就是16kb/14b=1170个，而第一个节点中有1170个索引，也就意味着能有1170棵子树，也就是说第一第二成存放的索引为1170*1170=1368900个，在第三层中，如果是用的innodb的话，假设表中每一行数据1kb，也就是一页能存放16个索引。那么总数就是1170*1170*16=21,902,400个。约为两千万条数据。

innodb通过索引查询数据中比较耗时的是哪一步？

最耗时的一步是将数据从磁盘读取到内存中的这一步，而数据读取到内存中会进行比较与筛选。那么问题来了，为什么不一次性将所有的数据都读取到内存进行比较筛选呢？如果我们数据库中的数据量非常非常大的时候，一次行将所有的数据读取到内存，就会出现两个极端问题

内存占用严重，甚至会出现内存溢出的问题
过大量的数据同时进行比较，效率也不一定会高

Promise_J_Z

关注

1
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
Mysql索据-Mysql的innodb引擎为什么要使用b+tree作为索引数据结构？

什么是索引？索引有什么优点？索引有什么缺点？b-tree和b+tree的区别为什么Innodb要选择B+tree作为数据结构什么是聚簇索引？何时使用聚簇索引与非聚簇索引联合索引是什么？为什么需要注意联合索引中的顺序？b+tree中innodb不需要回表查询吗？myisam一定会回表查询吗？什么情况使用了索引，查询还是慢什么情况下适合建索引什么适合下不适合建索引？
复制链接

扫一扫