第 7 章 B+树索引的使用7.1 索引的代价空间上的代价每建立一个索引都要为它建立一棵B+树，B+树的每一个节点都

7.1 索引的代价

空间上的代价

每建立一个索引都要为它建立一棵B+树，B+树的每一个节点都是一个数据页，一个页默认占用16KB的空间。
时间上的代价

每次对表中的数据进行增、删、改操作时，都需要去修改各个B+树索引。

一个表上的索引越多，占用的空间也就越多，增删改时的性能也就越关。

7.2 B+树索引适用的条件

CREATE TABLE person_info(
	id INT NOT NULL auto_increment,
	name VARCHAR(100) NOT NULL,
	birthday DATE NOT NULL,
	phone_number CHAR(11) NOT NULL,
	country varchar(100) NOT NULL,
	PRIMARY KEY (id),
	KEY idx_name_birthday_phone_number (name, birthday, phone_number)
);

二级索引示意图

7.2.1 全值匹配

SELECT * FROM person_info WHERE name = 'Ashburn' AND birthday = '1990-09-27' AND phone_number = '15123983239';

联合索引中的三个列都可能被用到。

调整name，birthday，phone_number三列的顺序不会影响使用索引，查询优化器会搞定。

7.2.2 匹配左边的列

可以使用索引的写法：

SELECT * FROM person_info WHERE name = 'Ashburn';

SELECT * FROM person_info WHERE name = 'Ashburn' AND birthday = '1990-09-27';

不可以使用索引的写法：

SELECT * FROM person_info WHERE birthday = '1990-09-27';

如果我们想使用联合索引中尽可能多的列，搜索条件中的各个列必须是联合索引中从最左边连续的列。

7.2.3 匹配列前缀

因为字符串排序时，是按一个字符一个字符比较的，所以一个排好序的字符串列，他们的前缀也是排好序的。所以以下SQL可以使用索引

SELECT * FROM person_info WHERE name LIKE 'As%';

但是这样

SELECT * FROM person_info WHERE name LIKE '%As%';

就不能使用索引。因为字符串中间有"As"的字符串并没有排好序。

7.2.4 匹配范围值

SELECT * FROM person_info WHERE name > 'Asa' AND name < 'Barlow';

定位到name="Asa"的记录
定位到name="Barlow"的记录
由于所有记录都是链表连起来的，取出他们中间的记录的主键值
到聚簇索引中回表查询

SELECT * FROM person_info WHERE name > 'Asa' AND name < 'Barlow' AND birthday > '1980-01-01';

只可以使用name列的索引。因为只有name值相同时才能用birthday的值进行排序，而这个查询中通过name进行范围查找中的记录可能并不是按照birthday列进行排序的，所以无法使用birthday列的索引。

7.2.5 精确匹配某一列并范围匹配另外一列

SELECT * FROM person_info WHERE name = 'Ashburn' AND birthday > '1980-01-01' AND birthday< '2000-12-31' AND phone_number > '15100000000';

可以使用name列和birthday列的索引。

SELECT * FROM person_info WHERE name = 'Ashburn' AND birthday = '1980-01-01' AND AND phone_number > '15100000000';

可以使用整个联合索引。

7.2.6 用于排序

filesort：文件排序，在MySQL中，当查询的结果集太大不能在内存中进行排序时，暂时借助磁盘的空间来存放中间结果，排序操作完成后再把排好序的结果集返回到客户端。

SELECT * FROM person_info ORDER BY name, birthday, phone_number LIMIT 10;

可以直接使用idx_name_birthday_phone_number 索引。

7.2.6.1 使用联合索引进行排序注意事项

ORDER BY 的子句顺序要与索引顺序保持一致（这里可没有查询优化器进行优化哦）。

同样遵循最左匹配。

SELECT * FROM person_info WHERE name = 'A' ORDER BY birthday, phone_number LIMIT 10;

也可以使用索引进行排序。

7.2.6.2 不可以使用索引进行排序的几种情况

ASC、DESC 混用。B+树中都是升序排序的，降序查询时，无法使用索引。
WHERE 子句中出现非排序使用到的索引列。
排序列包含非同一个索引的列。
排序列使用了复杂的表达式。

7.2.7 用于分组

SELECT name, birthday, phone_number, COUNT(*) FROM person_info GROUP BY name, birthday, phone_number

分组条件与索引相同，可以直接使用B+树索引进行分组。

7.3 回表的代价

SELECT * FROM person_info WHERE name > 'Asa' AND name < 'Barlow';

查询分两步：

从索引idx_name_birthday_phone_number 对应的B+树中取出name在Asa~Barlow之间的用户记录。
根据上一步查询出来的主键到聚簇索引中找到完整的用户记录，即回表查询。

该查询有两个特点：

会使用两个B+树索引，一个二级索引，一个聚簇索引
访问二级索引使用顺序I/O，速度较快；访问聚簇索引使用随机I/O，速度较慢。

**需要回表的记录越多，使用二级索引的效率也就越低。**特殊情况下，查询器宁愿使用全表扫描也不使用二级索引。

PS：因为Asa~Barlow之间的用户记录在磁盘中的存储是相连的，集中在一个或几个数据页中，称为顺序I/O。而通过这些用户记录的ID去定位数据，因为ID可能并不相连，所以他们指向的数据可能分布在更多的数据页，因此要访问的数据页也就更多，称为随机I/O。

7.3.1 覆盖索引

索引列包含所有查询列时可以避免回表，这样的索引称为覆盖索引。

7.4 如何挑选索引

7.4.1 只为用于搜索、排序或分组的列创建索引

只为出现在WHERE子句中的列、连接子句中的连接列，或者出现在ORDER BY或GROUP BY子句中的列创建索引。

7.4.2 考虑列的基数

即选择性。

7.4.3 索引列的类型尽量小

数据类型越小，在查询时进行的比较操作越快（CPU级别）
数据类型越小，索引占用的存储空间就越少，在一个数据页内记录就越多，I/O次数就越少。

7.4.4 索引字符串值的前缀

只对字符串的前几个字符建立索引

CREATE TABLE person_info(
    name VARCHAR(100) NOT NULL,
    birthday DATE NOT NULL,
    phone_number CHAR(11) NOT NULL,
    country varchar(100) NOT NULL,
    KEY idx_name_birthday_phone_number (name(10), birthday, phone_number)
);

name(10)就表示在建立的B+树索引中只保留记录的前10个字符的编码，这种只索引字符串值的前缀的策略是我们非常鼓励的，尤其是在字符串类型能存储的字符比较多的时候。

7.4.5 让索引列在比较表达式中单独出现

如果索引列在比较表达式中不是以单独形式出现，而是以某个表达式，或者函数调用形式出现的话，是用不到索引的。

7.4.6 主键插入顺序

无顺的主键插入可能会导致页面分裂和记录移位，影响性能。所以尽量保持主键依次递增，让主键具有AUTO_INCREMENT，让存储引擎自己为表生成主键，而不是我们手动插入。

7.4.7 冗余和重复索引

CREATE TABLE person_info(
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    birthday DATE NOT NULL,
    phone_number CHAR(11) NOT NULL,
    country varchar(100) NOT NULL,
    PRIMARY KEY (id),
    KEY idx_name_birthday_phone_number (name(10), birthday, phone_number),
    KEY idx_name (name(10))
);

idx_name重复了。

7.5 总结

B+树索引在空间和时间上都有代价，使用时要有敬畏之心
B+树索引适用以下情况
- 全值匹配
- 匹配左边的列
- 匹配范围值
- 精确匹配某一列并范围匹配另外一列
- 排序
- 分组
在使用索引时，需要注意
- 只为用于索引、排序或分组的列创建索引
- 为列的基数大的列（选择性强）的列创建索引
- 索引列的类型尽量小
- 可以只对字符串值的前缀建立索引
- 只有索引列在比较表达式中单独出现才有效
- 为了避免页面分裂和记录移位，使用主键自增
- 避免重复和冗余索引
- 尽量使用覆盖索引，避免回表