34丨答疑篇：关于索引以及缓冲池的一些解惑

陈旸



该思维导图由 AI 生成，仅供参考

这篇文章是进阶篇的最后一篇，在这一模块中，我主要针对 SQL 运行的底层原理进行了讲解，其中还有很多问题没有回答，我总结了进阶篇中常见的一些问题，希望能对你有所帮助。下面的内容主要包括了索引原则、自适应 Hash、缓冲池机制和存储引擎等。
关于索引（B+ 树索引和 Hash 索引，以及索引原则）什么是自适应 Hash 索引？在回答这个问题前，让我们先回顾下 B+ 树索引和 Hash 索引：
因为 B+ 树可以使用到范围查找，同时是按照顺序的方式对数据进行存储，因此很容易对数据进行排序操作，在联合索引中也可以利用部分索引键进行查询。这些情况下，我们都没法使用 Hash 索引，因为 Hash 索引仅能满足（=）（<>）和 IN 查询，不能使用范围查询。此外，Hash 索引还有一个缺陷，数据的存储是没有顺序的，在 ORDER BY 的情况下，使用 Hash 索引还需要对数据重新排序。而对于联合索引的情况，Hash 值是将联合索引键合并后一起来计算的，无法对单独的一个键或者几个索引键进行查询。
MySQL 默认使用 B+ 树作为索引，因为 B+ 树有着 Hash 索引没有的优点，那么为什么还需要自适应 Hash 索引呢？这是因为 Hash 索引在进行数据检索的时候效率非常高，通常只需要 O(1) 的复杂度，也就是一次就可以完成数据的检索。虽然 Hash 索引的使用场景有很多限制，但是优点也很明显，所以 MySQL 提供了一个自适应 Hash 索引的功能（Adaptive Hash Index）。注意，这里的自适应指的是不需要人工来制定，系统会根据情况自动完成。

公开

同步至部落

取消

完成

0/2000

荧光笔

直线

曲线

笔记

复制

AI

深入了解
翻译
英语
中文简体
中文繁体
法语
德语
日语
韩语
俄语
西班牙语
阿拉伯语
解释
总结

本篇文章深入探讨了SQL运行的底层原理，涉及索引原则、自适应Hash、缓冲池机制和存储引擎等内容。其中，自适应Hash索引和联合索引的最左原则为读者解析了索引的使用顺序对查询效率的影响。此外，文章还介绍了Hash索引和B+树索引的区别以及MySQL存储引擎对索引结构的支持情况。在存储引擎方面，对InnoDB和MyISAM的特点进行了比较，为读者提供了选择存储引擎的参考依据。同时，文章还强调了缓冲池服务于数据库整体的I/O操作，而查询缓存服务于SQL查询和查询结果集，并指出查询缓存在MySQL8.0版本中已被弃用。这篇文章对于想要深入了解SQL底层原理的读者来说，提供了丰富的知识和实用的技术指导。

仅可试看部分内容，如需阅读全部内容，请付费购买文章所属专栏
《SQL 必知必会》，新⼈⾸单¥68

立即购买

登录后留言

全部留言(28)

最新
精选

奕
利用老师的 hexo 表做了一个实现：按照这个顺序在 `hp_max`, `hp_growth`, `mp_max` 这3个字段上建立联合索引上面的 A 情况： explain select * from heros where hp_max > 5000 and hp_growth = 181.6 and mp_max = 200; 是全部扫描，不走索引 type 为 all 上面的 B 情况： explain select * from heros where hp_max = 5000 and hp_growth = 181.6 and mp_max > 200; 走联合索引， type 为 range 上面的 C 情况： explain select * from heros where hp_max = 5000 and hp_growth = 181.6 and mp_max = 200; 走联合索引， type 为 ref 上面的 D 情况： explain select * from heros where hp_max = 5000 and hp_growth > 181.6 and mp_max = 200; 走联合索引， type 为 range
作者回复: 分析的不错
2019-09-01
6
16
asdf100
对于读操作，为什么选择myisam比innobd效率要高？是因为锁的粒度吗？
作者回复: 除了myisam只支持表级锁以外，myisam不是事务安全的，而且不支持外键。你可以理解myisam更简化轻量级
2019-08-31

1
asdf100
Myisam使用的好像是b- 树吧？
作者回复: 也是使用的B+树
2019-08-31
3
1
DZ
ABCD四条语句都会使用索引。 A: WHERE y=2 AND x>1 AND z=3 -- 使用索引(x,y,z)的x列。 x是范围列，索引列最多作用于一个范围列，范围列之后的y列和z列无法使用索引。 B: WHERE y=2 AND x=1 AND z>3 -- 完整使用索引(x,y,z)。 C: WHERE y=2 AND x=1 AND z=3 -- 完整使用索引(x,y,z)。 z是范围列，索引可以被使用。 D: WHERE y>2 AND x=1 AND z=3 -- 使用索引(x,y,z)的x列和y列。 y是范围列，索引列最多作用于一个范围列，范围列之后的z列无法使用索引。感谢老师指正，纠正了我之前的错误理解。
2019-08-29
3
44
Hanqiu_Tan
首先在数据量比较大的前提下，A中x是范围查询最可能做的是全表扫描利用多块读方式，这样效果很好点。B和C应该利用了联合索引（x,y,z）中所有列。D根据最左原则应该利用了联合索引中的x,y列，执行步骤应该是，通过x,y找到rowid,回表，在通过判断z是否等于3，得到结果
2019-08-28
3
7
Ryoma
老师这里说的：查询“z=7 AND y=8 AND x=9”的时候，如果三个字段 x、y、z 在条件查询的时候是乱序的，但采用的是等值查询（=）或者是 IN 查询，那么 MySQL 的优化器可以自动帮我们调整为可以使用联合索引的形式。我的理解是有点问题的，看下面同学的回答也确实误导了一些同学。有的同学可能过于看重“但采用的是等值查询（=）或者是 IN 查询” 这句话了。这里贴上测试方案，有兴趣的可以试试：建表语句： CREATE TABLE `test` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `x` int(11) DEFAULT NULL, `y` int(11) DEFAULT NULL, `z` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `test` (`x`,`y`,`z`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; 存储过程： DELIMITER // CREATE PROCEDURE testInsert(cnt int) BEGIN DECLARE i int DEFAULT 0; START TRANSACTION; WHILE i<cnt DO INSERT INTO test(x,y,z) VALUES(i,i,i); set i=i+1; end WHILE; COMMIT; end // DELIMITER; CALL testInsert(1000000); 分析方法： explain SELECT x, y, z FROM test WHERE y=2 AND x>1 AND z=3; explain SELECT x, y, z FROM test WHERE y=2 AND x=1 AND z>3; explain SELECT x, y, z FROM test WHERE y=2 AND x=1 AND z=3; explain SELECT x, y, z FROM test WHERE y>2 AND x=1 AND z=3;
2020-03-20
2
4
四喜
A-> (x) B-> (x, y) C-> (x,y,z) D-> (x,y)
2020-03-01

4
爬行的蜗牛
A. SELECT x, y, z FROM table WHERE y=2 AND x>1 AND z=3 . 用到联合索引的x 字段；因为x 是最左边的； B. SELECT x, y, z FROM table WHERE y=2 AND x=1 AND z>3 只是用到联合索引的x,y,z 字段； C. SELECT x, y, z FROM table WHERE y=2 AND x=1 AND z=3 用到联合索引， x,y,z 字段都用到； D. SELECT x, y, z FROM table WHERE y>2 AND x=1 AND z=3 使用到 x，y字段的索引；
2019-12-24

4
mickey
A -> y B -> y,x C -> y,x,z D -> None
2019-08-28
1
3
rike
按照heros表加了联合索引后的执行结果，如果范围查询是联合索引的第一列，就无法走索引了？
2020-01-09

2

收起评论