MySQL学习(4)好好使用B+树索引

葫芦岛德信成叉车

前言

每个索引都是一颗B+树，对于聚簇索引，每一条完整记录都存储在B+树都叶子节点上；对于其他索引，叶子节点存储了索引列和主键。这么做都是为了提升查询速度，那么在实际使用中，是不是应该给所有列都添加索引呢，索引该如何使用呢？
先见一张表，随机添加一些数据：

1. CREATE TABLE single_table(
2.   id INT NOT NULL AUTO_INCREMENT,
3.   key1 VARCHAR(100),
4.   key2 INT,
5.   key3 VARCHAR(100),
6.   key_part1 VARCHAR(100),
7.   key_part2 VARCHAR(100),
8.   key_part3 VARCHAR(100),
9.   common_field VARCHAR(100),
10.   PRIMARY KEY (id),
11.   INDEX idx_key1 (key1),
12.   UNIQUE INDEX uk_key2 (key2),
13.   INDEX idx_key3 (key3),
14.   INDEX idx_key_part(key_part1, key_part2, key_part3)
15. ) ENGINE=InnoDB CHARSET=utf8;

复制代码

 
 
 
为什么不给每个列都建立索引

• 每建一个索引，就会新建一颗B+树，占用很大存储空间。
  
• 增删改操作时，维护索引需要耗费资源和时间。
  

索引不是越多越好，要用到刀刃上，索引要又好又少才好。
 
B+树索引的应用场景

扫描区间和边界条件

如下有一条查询语句：

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

复制代码

 

• 如果使用idx_key1索引执行查询，根据key1 < 'b'形成(-∞, 'b')的扫描区间，但是对与key3 > 'x' AND common_field = 'abc'来说，无法使用索引B+树中的列，只能通过查询(-∞, 'b')的结果，根据主键回表后的记录进行逐条判断。
  
• 如果使用idx_key3索引执行查询，根据key3 > 'x'形成('x', +∞)的扫描区间，但是对于key1和common_field列，则无法使用。
  

这些搜索条件为在索引中不存在的列称为普通所搜条件，只能在得到的二级索引记录进行回表后得到完整的记录，才能去判断它们是否成立。

对于B+树来说，只要索引列和常数使用=、、IN、NOT IN、IS NULL、IS NOT NULL、>、=、 100 AND common_field = 'abc';[/code] 
根据这个搜索条件很容易想到使用uk_key2索引执行查询，搜索条件key2>100可以形成扫描区间(100, +∞)。但是在uk_key2的二级索引中，记录并不包含common_field列，更不会按照common_field列进行排序，所以common_field=‘abc’这个搜索条件并没能有效的缩小扫描范围，最终的扫描区间是(100, +∞)。
如果上述查询语句中的AND改为OR，如下所示：

1. SELECT * FROM single_table where id IN (1, 50, 1688) OR (id >=2 AND id <= 100);

复制代码

 
key>100的扫描区间为(100, +∞)，common_field=‘abc’的扫描区间为(-∞, +∞)，使用OR连接表示取并集，最终的扫描区间是(-∞, +∞)，这还不如使用聚簇索引全表扫描，还省得回表操作。
 
联合索引的情况
使用联合索引执行查询时，要遵循最左匹配原则，意思就当使用联合索引进行查询时，最左侧的索引列必须包含在查询条件中，因为在联合索引idx_key_part的B+树中，记录都是按照key_part1排序，然后按照key_part2排序，再按照key_part3排序，最后前三列都相同的情况下，按照id排序。
对于如下查询语句：

1. SELECT * FROM single_table where key1 IN ('a', 'c') OR (key1 >='h' AND key1 <= 'j');

复制代码

 
dx_key_part索引中的记录按照key_part1列的值排序的，搜索条件key_part1=‘a’生成[‘a’, ‘a’]的扫描区间，定位到第一条符合条件的记录后，向后扫描直到不符合条件为止。
对于如下查询语句：

1. SELECT * FROM single_table WHERE key1 < 'b' AND key3 > 'x' AND common_field = 'abc';

复制代码

 
与上一条语句相同的是搜索条件key_part1=‘a’生成[‘a’, ‘a’]的扫描区间，但是在key_part1=‘a’的第一条记录向后，并不是直接按照key_part3排序的，key_part3=‘b’的记录并不是相邻的，而是先按照key_part2排序，再按照key_part3排序，扫描区间为(-∞, +∞)，并不能有效减少扫描记录量。这次查询的扫描区间最终为[‘a’, ‘a’]，与key_part3列没啥关系。
对于如下查询语句：

1. SELECT * FROM single_table WHERE key2 > 100 AND common_field = 'abc';

复制代码

 
这条语句的key_part2列就能使用到索引带来的好处的，
如下语句：

1. SELECT * FROM single_table WHERE key2 > 100 OR common_field = 'abc';

复制代码

 

index_key_part索引记录按照key_part1列的值排序，key_part