B+树索引使用的心得

一、B+树

1.1 B+树的特点

InnoDB存储引擎使用的是B+树索引，学完B+树索引后，需要明白B+树具有以下特点：

• 每个索引都对应一棵B+ 树。 B+ 树分为好多层，最下边一层是叶子节点，最上面一层是根节点，其余的是内节点。所有用户记录都存储在B+ 树的叶子节点，所有目录项记录都存储在根节点与内节点。
• InnoDB 存储引擎会自动为主键建立聚簇索引（如果没有主键或不为NULL且被UNIQUE修饰的键，会自动添加主键），聚簇索引的叶子节点包含完整的用户记录。
• 可以对感兴趣的列建立二级索引， 二级索引的叶子节点包含的用户记录由索引列 + 主键组成，所以如果想通过二级索引来查找完整的用户记录的话，需要通过回表操作，也就是在通过二级索引找到主键值之后再到聚簇索引中查找完整的用户记录。
• B+ 树中每层节点都是按照索引列值从小到大的顺序排序而组成了双向链表，而且每个页内的记录（不论是用户记录还是目录项记录）都是按照索引列的值从小到大的顺序而形成了一个单链表。如果是联合索引的话，则页面和记录先按照联合索引最左边的列排序，如果该列值相同，再按照联合索引后边的列排序。
• 通过索引查找记录是从B+ 树的根节点开始，一层一层向下搜索。由于每个页面都按照索引列的值建立了Page Directory （页目录），所以在这些页面中的查找非常快。

1.2 索引的代价

B+树索引虽然可以加快查询的速度，但是也会有一定的代价。比如：

• 空间上的代价

每建立一个索引都要为它建立一棵B+树，一棵B+树的每一个节点都是一个数据页，一个页占用16KB 的存储空间，一棵很大的B+ 树由许多数据页组成，所以会占据很大存储空间的。

• 时间上的代价
  • 每次对表中的数据进行增、删、改操作时，为了维护节点和记录的排序，会造成页分裂、页面回收等操作，导致性能变差。
  • 执行查询语句前，会生成一个执行计划，索引太多会导致成本分析时间过长。

二、B+树索引的应用

首先创建一个表：

CREATE TABLE sing_table(
	id INT NOT NULL AUTO_INCREAMENT,
    key1 VARCHAR(100),
    key2 INT,
    key3 VARCHAR(100),
    key_part1 VARCHAR(100),
    key_part2 VARCHAR(100),
    key_part3 VARCHAR(100),
    common_field VARCHAR(100),
    PRIMARY KEY(id),
    KEY idx_key1 (key1),
    UNIQUE KEY idx_key2 (key2),
    KEY idx_key3 (key3),
    -- 联合索引
    KEY idx_key_part (key_part1,key_part2,key_part3)
)Engine=InnoDB CHARSET=utf8;

为id列创建聚簇索引，为key1，key2与key3列创建二级索引，为

2.1扫描区间与边界条件

2.1.1概念

如果执行下面的查询语句

SELECT * FROM single_table WHERE id >= 2 AND id <= 100;

把待扫描记录的id值所在区间（[2,100]）称为扫描区间，形成这个扫描区间的搜索条件（id>=2 AND id<=100）称为这个区间的边界条件。全局扫描的空间为(-00,+00)。

再比如下面的查询语句

SELECT * FROM single_table WHERE key2 IN (1438，6328) and (key2 >= 38 AND id <= 79);

key2 IN (1438，6328)对应的边界条件为[1438，1438]与[6328,6328]，这种称为单点扫描区间。(key2 >= 38 AND id <= 79)对应边界条件为[38,79]称为范围扫描区间。

2.1.2产生扫描区间的条件

对于B+树索引来说，查询条件需要索引项和常数使用=、<、>、<=、>=、IN、NOT IN、IS NULL、IS NOT NULL、BETWEEN、!= 或者 LIKE操作符连接起来。

需要注意：

• IN操作符的语义和OR连接起来的语音相同。比如：key2 IN (1438，6328) 等价于 key2 = 1438 OR key2 = 6328。
• 比较字符串的大小，是依次比较每个字符的大小。
• 条件1 AND 条件2：两个条件都为真的时候，才为true，相当于交集。
• 条件1 OR 条件2：两个条件有一个为真就为true，相当于并集。

2.1.3联合索引执行查询时对应的扫描区间

• 查询一：

SELECT * FROM single_table WHERE key_part1 = 'a';
SELECT * FROM single_table WHERE key_part1 < 'a';

由于二级索引记录是先按照key_part1列的值排序的，所以符合key_part1 = 'a'条件的所有记录的肯定是相邻的。此时，对于前一条查询语句，对应的扫描区间为[‘a’,‘a’]，边界条件key_part1 = 'a'；后一条查询语句，对应的扫描区间为(-00,‘a’)，边界条件key_part1 < 'a'

• 查询二：

-- 联合索引 KEY idx_key_part (key_part1,key_part2,key_part3)
SELECT * FROM single_table WHERE key_part1 = 'a' AND key_part2 = 'b';
SELECT * FROM single_table WHERE key_part1 = 'a' AND key_part3 = 'c';

二级索引记录是先按照key_part1列的值进行排序的，然后按照key_part2列排序，在前面的基础上，最后是key_part3排序。

对于前一条查询，如果使用idx_key_part索引执行查询，可以形成扫描区间[(‘a’,‘b’),(‘a’,‘b’)]，边界条件为key_part1 = 'a' AND key_part2 = 'b'；

而对于后一条查询，对于符合key_part1 = 'a' 条件的二级索引记录来说，并不是直接按照key_part3列排序的，所以 key_part3 = 'c'不能算作边界条件。这条语句对应的扫描区间为[‘a’,‘a’]，边界条件为key_part1 = 'a'。

这里有一个重要的概念

索引条件下推：虽然key_part3 = 'c'不能作为形成扫描区间的边界条件，但它包含在联合索引idx_key_part里。因此，每当从idx_key_part索引的扫描区间[‘a’,‘a’]中获取到一条二级索引记录时，可以先判断这条二级索引记录是否符合key_part3 = 'c'的条件，符合才进行回表。这就是索引下推。

• 查询三：

SELECT * FROM single_table WHERE key_part1 < 'b' AND key_part2 = 'a';
SELECT * FROM single_table WHERE key_part1 <= 'b' AND key_part2 = 'a';

对于前一条查询语句，虽然二级索引是先按照key_part1列的值排序的，但对于符合key_part1 < 'b'条件的二级索引记录来说，并不是直接按照key_part2列排序，因为不知道从哪一条记录开始。所以不能根据搜索条件key_part2 = 'a'，来进一步减少需要扫描的记录数量，这里不能索引下推。形成的扫描区间[-00,‘b’]，形成该扫描区间的边界条件是key_part1 < 'b'。

而对于后一条查询，搜索条件为key_part1 <= 'b'，注意，这里有一个=号。虽然对于符合key_part1 <= 'b'条件的二级索引记录来说，并不是直接按照key_part2列排序的，但对于符合key_part1 = 'b'条件的二级索引记录来说，是按照key_part2列排序的。这里可以进行索引下推，减少需要扫描的二级索引记录范围。形成的扫描区间[(-00,-00),(‘b’,‘a’)]，边界条件为key_part1 <= 'b' AND key_part2 = 'a'。

通过前面联合索引使用的三个查询示例，可以看出：

• 索引下推：指利用不连续的精准查询条件，进行过滤不满足条件的记录，减少回表次数。
• 最左前缀原则：创建联合索引(key_part1,key_part2,key_part3)，就相当于创建了(key_part1,key_part2,key_part3)、(key_part1,key_part2)、(key_part1)三个索引。所谓，最左优先，以最左边的为起点任何连续的索引都能匹配上，MySQL会一直向右匹配直到遇到范围查询（>,<,between,like）就停止匹配。如查询二中，第一条查询，条件二key_part2 = 'b'是精准查询，且与条件一是连续的，所以可以使用索引下推。第二条语句条件一跟条件二不是连续的，因此条件二无法下推。查询三中，第一条查询语句是范围查询，后面的条件二就失效了，索引不能下推。

2.2索引用于排序

2.2.1使用联合索引进行排序时的注意事项

• 在使用联合索引时，ORDER BY 子句后面的列的顺序也必须按照索引列的顺序给出；
• 当联合索引的索引列左边连续的列为常量时，也可以使用联合索引对右边的列进行排序。比如：

SELECT * FROM single_table WHERE key_part1 <= 'a' AND key_part2 = 'b' ORDER BY key_part3 LIMIT 10;

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

• ASC、DESC 混用

• 排序列包含非同一个索引的列

SELECT * FROM single_table ORDER BY key1,key2 LIMIT 10;

key1与key2是不同索引的列，这种情况只会按照key1列的值进行排序。

• 排序列是某个联合索引的索引项，但是这些排序列在联合索引中并不连续

SELECT * FROM single_table ORDER BY key_part1,key_part3 LIMIT 10;

key_part1和key_part3在联合索引里并不连续，中间还有一个key_part2。key_part1值相同的记录并不是按照key_part3排序的。

• 用来形成扫描区间的索引列与排序列不同

SELECT * FROM single_table WHERE key1 = 'a' ORDER BY key2 LIMIT 10;

• 排序列不是以单独列名的形式出现在 ORDER BY 子句中

SELECT * FROM single_table ORDER BY UPPER(key1) LIMIT 10;

key1列被函数修饰，所以不能使用key1列进行查询。

2.2.3索引用于分组

有时候为了方便统计信息表中的一些信息，会把表中的记录按照某些列进行分组。如果没有聚簇索引，就得建立一个用于统计的临时表，在扫描聚簇索引记录的时候将统计的中间结果填入这个临时表。当扫描记录后，再把临时表的结果作为结果集发送给客户端。但如果有了聚簇索引，刚好分组顺序也很索引一致，就不用再创建临时表了。

2.3回表的代价

二级索引的叶子节点装的是索引列与主键值，因此要查询用户的数据还需要进行回表操作。对于使用InnoDB的表来说，索引中的数据页都是存放在磁盘中的，等到需要的时候在加载到内存中使用。通过二级索引进行记录的查询，由于二级索引记录对应的主键值是毫无顺序的，所以每读取一条索引记录都需要回表，如果数据页不在内存当中，就需要从磁盘读取，这是随机IO，非常的耗时。

选择全表扫描或是二级索引+回表的方式，是由查询优化器决定的。查询优化器会事先对表中的记录计算一些统计数据，然后再利用这些统计数据根据查询的条件来计算一下需要回表的记录数，需要回表的记录数越多，就越倾向于使用全表扫描，反之倾向于使用二级索引 + 回表的方式。

一般情况下，限制（LIMIT）查询获取较少的记录数会让优化器更倾向于选择使用二级索引 + 回表的方式进行查询，因为回表的记录越少，性能提升就越高。

2.4更好的创建和使用索引

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

只为出现在WHERE子句中的列、连接子句中的列或者出现在ORDER BY或者GROUP BY子句中的列创建索引。仅在查询列表中的列，没必要建立索引。比如

SELECT common_field,key_part3 FROM single_table WHERE key1 = 'a';

只需要对key1列创建索引就好了，不需要对 common_field与key_part3建立索引。

• 考虑索引列中不重复值的个数

如果重复值的个数太多，通过二级索引+回表的方式查找记录就需要执行很多次回表。

• 索引列的类型尽量小

数据类型越小，索引占用的存储空间就越少，在一个数据页里就可以存放更多的记录，磁盘IO带来的性能损耗也越小，读写效率也就会越高。

• 为列前缀建立索引

字符串是由若干个字符组成的。在二级索引的记录中只保留字符串的前几个字符就好。

• 覆盖索引

为了彻底告别回表操作带来的性能损耗，建议最好在查询列表中只包含索引列。比如

SELECT key1,id FROM single_table WHERE key1 > 'a' AND key1 < 'c';

这种索引中已经包含所有需要读取的列的查询方式称为覆盖索引。

• 让索引列以列名的形式单独出现在查询条件中

SELECT * FROM single_table WHERE key2*2 < 4;
SELECT * FROM single_table WHERE key2 < 2;

第一个查询语句，不是以单独列名出现的，这种不能使用key2的索引，只能全表扫描；

第二个查询语句可以使用全表扫描。

• 新插入记录时主键大小对效率的影响

当数据页已经满了的时候，再插入一条记录就会导致页分裂，为了避免这种性能损耗，最好让插入记录的主键值依次递增，即设置为AUTO_INCREMENT。

• 冗余和重复索引

比如，当建立了联合索引时候，就不需要在对联合索引的最左前缀建立索引了。