MySQL中InnoDB存储B+树的应用

概述

一般情况在, 我们的在线交易系统(OLTP)的每次查询可能只是返回几条甚至说一条数据, 这时候添加索引才是有意义的.

联合索引

联合索引和单列的索引的创建方法是一样的, 不同的只是对多个列进行索引.联合索引的本质上也是一棵B+树, 而B+树都是按照键值(key)排序的, 单列索引的键值只有一个, 而联合索引的键值是有多个.
创建一张表如下:

create table t1 (
	id int(11) auto_increment not null,
    a int(11) not null,
    b int(11) not null,

	primary key(id),
	key idx_a_b(a,b)
);

# 插入数据
INSERT INTO t1 SELECT 1, 1, 1;
INSERT INTO t1 SELECT 2, 1, 2;
INSERT INTO t1 SELECT 3, 2, 1;
INSERT INTO t1 SELECT 4, 2, 2;
INSERT INTO t1 SELECT 5, 3, 1;

此时, 索引idx_a_b存储的键值顺序是(1,1), (1,2),(2,1),(2,2),(3,1). 对于查询SELECT * FROM t1 WHERE a=xxx AND b=xxx则可以使用这个索引, 对于查询单列a的查询SELECT * FROM t1 WHERE a=xxx也可以使用这个联合索引, 然而对于查询SELECT * FROM t1 WHERE b=xxx是不可以使用这个索引的, 因为在索引表的B+树上, 不是按照b列的值来排序的.
联合索引的第二个好处是, 对于第二列也进行了排序, 考虑如下场景:

在购物商城, 需要查找某个用户最近3次的购物记录

建表如下所示:

create table buy_log (
	user_id int(11) not null,
	buy_date DATE not null,
);

# 插入数据
INSERT INTO buy_log VALUES(1, '2019-03-22');
INSERT INTO buy_log VALUES(2, '2019-01-14');
INSERT INTO buy_log VALUES(2, '2019-05-01');
INSERT INTO buy_log VALUES(1, '2019-06-16');
INSERT INTO buy_log VALUES(1, '2019-09-24');
INSERT INTO buy_log VALUES(3, '2019-10-02');
INSERT INTO buy_log VALUES(1, '2019-12-30');

# 分贝建立两个索引测试
ALTER TABLE buy_log add key(user_id);
ALTER TABLE buy_log add key(user_id, buy_date);

执行EXPLAIN SELECT * FROM buy_log WHERE user_id = 3;操作, 发现使用的是uer_id这个索引, 因为user_id=3这个行在存储中只是一个单行数据.
执行EXPLAIN SELECT * FROM buy_log WHERE user_id = 2;操作, 发现使用的是user_id_2这个联合索引, 因为在user=2是一个多值结果,
执行EXPLAIN SELECT * FROM buy_log WHERE user_id = 2 ORDER BY buy_date DESC LIMIT 3;
操作, 发现使用的是user_id_2索引, 因为联合索引上已经对buy_date进行了排序.
因为联合索引(a, b) 是按照a,b排序的, 所以如下操作也可以直接或得结果:

SELECT * FROM TABLE t1 WHERE a=xxx ORDER BY b;

对于联合索引(a,bc)来说, 下列语句同样可以获取到直接的结果:

SELECT * FROM TABLE t1 WHERE a=xxx AND b=xxx ORDER BY c;

但是如下语句, 无法直接通过联合索引获取, 还需要进行一次filesort操作:

SELECT * FROM TABLE t1 WHERE a=xxx ORDER BY c;

覆盖索引

两个作用:

1. 对于通过主键或者索引的键可以直接从索引的B+中获取, 无需查询聚集索引, 减少IO.
2. 对于某些统计问题, 也可以直接通过覆盖索引完成, 而无需访问聚集索引.

InnoDB存储引擎支持覆盖索引.所谓覆盖索引(covering index), 即是直接从辅助索引中获取到需要查询的记录. 使用覆盖索引的好处是, 相对于聚集索引, 不需要查询包含整个行的数据, 而只是需要查询某个列的数据, 所以覆盖索引的IO操作远远小于聚集索引.
InnoDB存储引擎的辅助索引的叶子节点是包含主键的, 其存储形式如下:

(primary key1, primary key2, ..., key1, key2, ...)

primary keyN: 代表主键中的第n个键.
keyN: 代表辅助索引中的第n个键.
得出结论: 我们可以通过以keyN或者primary keyN为查询条件, 相互查询到其他的key的值. 例如:

SELECT primary key2, key3 FROM t1 WHERE key1=xxx;
SELECT key2, key3 FROM t1 WHERE primary key1=xxx;

对于 select count(1) from t1 这样的语句, 可以直接通过覆盖索引完成.

优化器不使用索引的情况

当我们使用EXPLAIN检查查询语句的时候, 发现优化器进行了权标扫描, 而没有使用索引, 这种情况多发生在范围查询或者join连接操作时候.
创建表如下:

create table order_details(
	order_id int(11) not null,
	prod_id int(11) not null,
	
	index idx_order_id(order_id),
	index idx_order_id_prod_id(order_id, prod_id)
);

# 插入数据
INSERT INTO order_details(order_id, prod_id) VALUES(1,2);
INSERT INTO order_details(order_id, prod_id) VALUES(2,3);
INSERT INTO order_details(order_id, prod_id) VALUES(1,2);
INSERT INTO order_details(order_id, prod_id) VALUES(1,5);
INSERT INTO order_details(order_id, prod_id) VALUES(2,7);