问题:
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+ 树特点
- 所有的叶子结点中包含了全部元素的信息,及指向含这些元素记录的指针,且叶子结点本身依关键字的大小自小而大顺序链接。且叶子节点间增加了横向的指针,这样顺序遍历所有数据将变得非常容易。
- 所有的中间节点元素都同时存在于子节点,在子节点元素中是最大(或最小)元素。
- 单一节点存储更多的元素(因为不含有对应的值,仅仅含有键),使得查询的IO次数更少。
- 所有查询都要从根节点查找到叶子节点,查询性能稳定,相对于B树更加稳定,因为B+树只有叶子节点存储了对应的值信息。
- 所有叶子节点形成有序双向链表,对于SQL的范围查询以及排序查询都很方便。
- B/B+树的共同优点的每个节点有更多的孩子,插入不需要改变树的高度,从而减少重新平衡的次数,非常适合做数据库索引这种需要持久化在磁盘,同时需要大量查询和插入的应用。树中节点存储这指向页的信息,可以快速定位到磁盘对应的页上面。
三 聚簇索引
- InnoDB使用的是聚簇索引,将主键组织到一棵B+树中,而行数据就储存在叶子节点上,若使用"WHERE id = 1"这样的条件查找主键,则按照B+树的检索算法即可查找到对应的叶节点,之后获得行数据。若对Name列进行条件搜索,则需要两个步骤:第一步在辅助索引B+树中检索Name,到达其叶子节点获取对应的主键。第二步使用主键在主索引B+树种再执行一次B+树检索操作,最终到达叶子节点即可获取整行数据。
- 所以,辅助索引B+树叶子节点存的是主键B+树的指针(也称回表)。
四 最左前缀原则
所谓最左前缀原则,可以用上图B+数所示
- 不论是联合索引 还是 like 模糊查询,其原理都是通过B+数查找,当B+树找不到相匹配的,就需要通过数据页一页一页查询(即全表扫描)。
- 所以,需要最左边的数据进行匹配。
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
- MySQL 默认只会将 int 类型字符串转为数字比较
- 'a' 默认为 0