MySQL B+ 树

问题：

SELECT 0 = 0; // 返回 1
SELECT 0 = 1; // 返回 0
SELECT 0 = 'a'; // ？
SELECT 1 = '1'; // ？

一 Page 页构成 B+ 树

 

表数据

CREATE TABLE t_t (

       id INT PRIMARY KEY AUTO_INCREMENT,

       a INT NOT NULL DEFAULT 0,

       b INT NOT NULL DEFAULT 0,

       c VARCHAR(32) NOT NULL DEFAULT ''

)ENGINE=INNODB;

插入数据

INSERT INTO t_t(a, b, c) VALUES (2, 5, 'a');

INSERT INTO t_t(a, b, c) VALUES (2, 3, 'e');

INSERT INTO t_t(a, b, c) VALUES (5, 6, 'd');

INSERT INTO t_t(a, b, c) VALUES (7, 2, 'f');

INSERT INTO t_t(a, b, c) VALUES (5, 2, 'a');

INSERT INTO t_t(a, b, c) VALUES (3, 8, 'a');

INSERT INTO t_t(a, b, c) VALUES (7, 9, 'a');

INSERT INTO t_t(a, b, c) VALUES (12, 3, 'a');

INSERT INTO t_t(a, b, c) VALUES (2, 5, 'a');

INSERT INTO t_t(a, b, c) VALUES (2, 3, 'a');

INSERT INTO t_t(a, b, c) VALUES (5, 6, 'a');

INSERT INTO t_t(a, b, c) VALUES (7, 2, 'a');

INSERT INTO t_t(a, b, c) VALUES (5, 2, 'a');

INSERT INTO t_t(a, b, c) VALUES (3, 8, 'a');

INSERT INTO t_t(a, b, c) VALUES (7, 9, 'a');

INSERT INTO t_t(a, b, c) VALUES (12,3, 'a');

分析：

• 由于创建表时已经给定id为主键自增。
• 所以，数据往数据页（默认大小为16KB）存储时，会自动按照主键排序（单向链表）。

如上图所示

• 假使每页只能存4条数据，则插入数据超过4条时就要进行分页。
• 每条数据页的页头都会保存向前向后的指针（并且每个数据页会相互连接构成双向链表）。
• 每页会有页目录对数据进行分类，将最小的数据作为目录（用于快速查找数据）。

以此类推

• 当数据页数增多，页目录也会增多，页目录里的数据也会增多。
• 如果要查询某条记录，需要将页目录从第一页遍历到最后一页。
• 所以，需要将页目录单独提取出来，也构成一个数据页（如下图）。

B+ 树构成

二 B+ 树特点

1. 所有的叶子结点中包含了全部元素的信息，及指向含这些元素记录的指针，且叶子结点本身依关键字的大小自小而大顺序链接。且叶子节点间增加了横向的指针，这样顺序遍历所有数据将变得非常容易。
2. 所有的中间节点元素都同时存在于子节点，在子节点元素中是最大（或最小）元素。
3. 单一节点存储更多的元素（因为不含有对应的值，仅仅含有键），使得查询的IO次数更少。
4. 所有查询都要从根节点查找到叶子节点，查询性能稳定，相对于B树更加稳定，因为B+树只有叶子节点存储了对应的值信息。
5. 所有叶子节点形成有序双向链表，对于SQL的范围查询以及排序查询都很方便。
6. B/B+树的共同优点的每个节点有更多的孩子，插入不需要改变树的高度，从而减少重新平衡的次数，非常适合做数据库索引这种需要持久化在磁盘，同时需要大量查询和插入的应用。树中节点存储这指向页的信息，可以快速定位到磁盘对应的页上面。

三 聚簇索引

• InnoDB使用的是聚簇索引，将主键组织到一棵B+树中，而行数据就储存在叶子节点上，若使用"WHERE id = 1"这样的条件查找主键，则按照B+树的检索算法即可查找到对应的叶节点，之后获得行数据。若对Name列进行条件搜索，则需要两个步骤：第一步在辅助索引B+树中检索Name，到达其叶子节点获取对应的主键。第二步使用主键在主索引B+树种再执行一次B+树检索操作，最终到达叶子节点即可获取整行数据。
• 所以，辅助索引B+树叶子节点存的是主键B+树的指针（也称回表）。

四 最左前缀原则

所谓最左前缀原则，可以用上图B+数所示

1. 不论是联合索引 还是 like 模糊查询，其原理都是通过B+数查找，当B+树找不到相匹配的，就需要通过数据页一页一页查询（即全表扫描）。
2. 所以，需要最左边的数据进行匹配。

select * from t_t where a = 1 and b = 1; // √
sleect * from t_t where b = 1; // x 会进行全表扫描，因为a列不确定，无法进行索引匹配

select * from t_t where c like '%a'; // x 会进行全表扫描，因为%不确定，无法进行索引匹配
select * from t_t where c like 'a%'; // √

答案：

SELECT 0 = 'a'; // 1
SELECT 1 = '1'; // 1

1. MySQL 默认只会将 int 类型字符串转为数字比较
2. 'a' 默认为 0