MySQL学习(4)好好使用B+树索引
每个索引都是一颗B+树，对于聚簇索引，每一条完整记录都存储在B+树都叶子节点上；对于其他索引，叶子节点存储了索引列和主键。这么做都是为了提升查询速度，那么在实际使用中，是不是应该给所有列都添加索引呢，索引该如何使用呢？
先见一张表，随机添加一些数据：
CREATE TABLE single_table( id INT NOT NULL AUTO_INCREMENT, 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), INDEX idx_key1 (key1), UNIQUE INDEX uk_key2 (key2), INDEX idx_key3 (key3), INDEX idx_key_part(key_part1, key_part2, key_part3) ) ENGINE=InnoDB CHARSET=utf8;
为什么不给每个列都建立索引
-
每建一个索引，就会新建一颗B+树，占用很大存储空间。
-
增删改操作时，维护索引需要耗费资源和时间。
索引不是越多越好，要用到刀刃上，索引要又好又少才好。
B+树索引的应用场景
扫描区间和边界条件
如下有一条查询语句：
SELECT * FROM single_table WHERE id >=2 AND id <= 100;
这条语句通过聚簇索引去查找到id值符合范围的第一条记录开始，向右扫描记录，直到不符合条件的记录为止，这个扫描范围也就是[2, 100]，我们把这个区间称为扫描区间，把形成这个扫描区间的搜索条件称为边界条件。对于全表扫描来说，扫描区间就是(-∞, +∞)。
对于单点扫描区间，也是一样的，如下面一条语句：
SELECT * FROM single_table where id IN (1, 50, 1688) OR (id >=2 AND id <= 100);
这个搜索条件形成的扫描区间为：[1, 1]、[2, 100]、[1688, 1688]，其中[1, 1]和[1688, 1688]为单点扫描区间，[2, 100]为范围扫描区间，由于[50, 50]包含于[2, 100]，取并集所以省略。
使用AND连接符的两个表达式的范围取交集，使用OR连接符则取范围的并集。
如果搜索条件不是id，是key1列，刚好我们为key1列添加了二级索引idx_key1。有如下语句：
SELECT * FROM single_table where key1 IN ('a', 'c') OR (key1 >='h' AND key1 <= 'j');
这个搜索条件形成了['a', 'a']、['c', 'c']、['h', 'j']，根据这个扫描区间，从第一条记录开始扫描，直到不符合区间范围。需要注意的是，由于这是二级索引，又因为SELECT *（包括查询列需要展示索引列或主键以外的列），所以需要根据聚簇索引进行回表操作。
从上面这些搜索条件形成扫描区间来看，它的作用就是缩小扫描范围，减少不必要的时间浪费，这就是索引带来的好处。如果想使用某个索引来执行查询，但是又无法通过搜索条件形成合适的扫描区间来减少需要的扫描的记录数量时，就不考虑使用这个索引执行查询。
下面一条语句：
SELECT * FROM single_table WHERE key1 < 'b' AND key3 > 'x' AND common_field = 'abc';
-
如果使用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、>、<、>=、<=、BETWEEN、!=、<>或者LIKE连接，就可以产生扫描区间。
-
IN连接符和多个用OR连接的=是一个意思。
-
!=产生的扫描区间就是(-∞, 该值)和(该值, +∞)。
-
LIKE必须匹配前缀才可以形成扫描区间。LIKE 'a%' 区间为['a', 'b')。LIKE ‘%a’区间为(-∞, +∞)。
有的搜索条件不能生成合适的扫描空间
在使用某个索引执行查询时，有些搜索条件不能生成合适的扫描区间来减少扫描记录量，比如下面这个查询语句：
SELECT * FROM single_table WHERE key2 > 100 AND common_field = 'abc';
根据这个搜索条件很容易想到使用uk_key2索引执行查询，搜索条件key2>100可以形成扫描区间(100, +∞)。但是在uk_key2的二级索引中，记录并不包含common_field列，更不会按照common_field列进行排序，所以common_field=‘abc’这个搜索条件并没能有效的缩小扫描范围，最终的扫描区间是(100, +∞)。
如果上述查询语句中的AND改为OR，如下所示：
SELECT * FROM single_table WHERE key2 > 100 OR common_field = 'abc';
key>100的扫描区间为(100, +∞)，common_field=‘abc’的扫描区间为(-∞, +∞)，使用OR连接表示取并集，最终的扫描区间是(-∞, +∞)，这还不如使用聚簇索引全表扫描，还省得回表操作。
联合索引的情况
使用联合索引执行查询时，要遵循最左匹配原则，意思就当使用联合索引进行查询时，最左侧的索引列必须包含在查询条件中，因为在联合索引idx_key_part的B+树中，记录都是按照key_part1排序，然后按照key_part2排序，再按照key_part3排序，最后前三列都相同的情况下，按照id排序。
对于如下查询语句：
SELECT * FROM single_table WHERE key_part1 = 'a';
dx_key_part索引中的记录按照key_part1列的值排序的，搜索条件key_part1=‘a’生成[‘a’, ‘a’]的扫描区间，定位到第一条符合条件的记录后，向后扫描直到不符合条件为止。
对于如下查询语句：
SELECT * FROM single_table WHERE key_part1 = 'a' AND key_part3 = 'b';
与上一条语句相同的是搜索条件key_part1=‘a’生成[‘a’, ‘a’]的扫描区间，但是在key_part1=‘a’的第一条记录向后，并不是直接按照key_part3排序的，key_part3=‘b’的记录并不是相邻的，而是先按照key_part2排序，再按照key_part3排序，扫描区间为(-∞, +∞)，并不能有效减少扫描记录量。这次查询的扫描区间最终为[‘a’, ‘a’]，与key_part3列没啥关系。
对于如下查询语句：
SELECT * FROM single_table WHERE key_part1 = 'a' AND key_part2 = 'b';
这条语句的key_part2列就能使用到索引带来的好处的，
如下语句：
SELECT * FROM single_table WHERE key_part1 < 'b' AND key_part2 = 'a';
index_key_part索引记录按照key_part1列的值排序，key_part<‘b’的值是相邻的，从符合条件的第一条开始扫描，直到不符合条件为止，扫描区间为(-∞, ‘b’)。但是对于符合key_part<‘b’的记录，key_part2=‘a’的值却不是直接按照key_part2列排序的。
举个例子，很显然，当key_part1<’b’是，key_part并不是连续的。这个查询的扫描区间是由key_part1决定的(-∞, ‘b’)，与key_part2无关。
但是对于下面这条查询语句又有些许不同：
SELECT * FROM single_table WHERE key_part1 <= 'b' AND key_part2 = 'a';
key_part1<’b’时与上一条语句情况一致，但是当key_part1=’b’时，key_part2的记录是有序排列的，也就是可以缩小扫描范围，最终形成扫描区间((-∞, -∞), (’b’, ‘a’)]。
索引排序
在MySQL中，在内存或磁盘中进行排序的方式统称为文件排序。但是如果ORDER BY语句使用索引列，则可省去文件排序的步骤。比如下面这个语句：
SELECT * FROM single_table ORDER BY key_part1, key_part2, key_part3 LIMIT 10;
这个查询语句需要按照key_part1值排序，如果记录的key_part1相同，再按照key_part2值排序，如果key_part2值相同，再按照key_part3排序。如此的排序，难道不就是索引中记录存储的顺序么。不过要注意的是，二级索引的记录中只包含索引列和主键，所以每一条记录都需要执行回表操作，才能获取完整的记录，如果记录条数非常多，会影响性能。最好能按需使用LIMIT。
使用联合索引进行排序时的注意事项
ORDER BY后面的列的顺序必须按照索引列的顺序给出，否则无法使用B+树索引。
例如下列语句，则无法使用索引排序：
SELECT * FROM single_table ORDER BY key_part3, key_part2, key_part1 LIMIT 10;
当联合索引的索引列左边连续的列为常量时，也可以使用联合索引对右边的列进行排序，例如：
SELECT * FROM single_table WHERE key_part1 = 'a' AND key_part2 = 'b' ORDER BY key_part3 LIMIT 10;
不可以使用索引进行排序的几种情况
-
ASC、DESC混用
使用联合索引进行排序时，排序规则必须一致（MySQL 8.0以前），也就是要么全部ASC、要么全部DESC。
我们知道在索引页中记录通过next_record有序排列，那如何倒序查找记录呢？
在查找当前记录的上一条记录时，先通过next_record找到该组的最后一条记录，再通过这条记录的槽的上一个槽，找到该记录所在组的第一条记录，然后开始遍历直到next_record是本记录的那条记录，就是上一条记录。如下图所示：
如果key_part1使用ASC规则排序，key_part2使用DESC规则排序，且去LIMIT10。此时在key_part1从左取10条记录，然后从key_part1为最小值的最右侧开始向左找10条记录，我们也不知道key_part1最小值时有没有10条记录，如果没有，需要再取更大值的key_part1，然后重复，直到取的10条记录。。。
-
排序列包含非同一个索引的列
下列语句：
SELECT * FROM single_table ORDER BY key1, key2 LIMIT 10;
显然，不管是使用idx_key1索引还是idx_key2索引，排序列中都有不能被包含的列。
-
联合索引不连续的列
下列语句：
SELECT * FROM single_tabl ORDER BY key_part1, key_part3 LIMIT 10;
key_part1值相同的记录不是直接按照key_part3排序的。
-
用来形成扫描区间的索引列与排序列不同
下列语句：
SELECT * FROM single_table WHERE key1 = 'a' ORDER BY key2 LIMIT 10;
这条语句中，搜索条件key1='a'形成扫描区间['a', 'a']，在使用idx_key1执行查询时，无法使用uk_key2排序，idx_key1中无key2。
-
排序列不是单独列名
要想使用索引排序，必须保证索引列是以单独列名的形式出现，如下所示：
SELECT * FROM single_table ORDER BY UPPER(key1) LIMIT 10;
这样不能使用idx_key1。
索引分组
如下语句：
SELECT key_part1, key_part2, key_part3, COOUNT(*) FROM single_table GROUP BY key_part1, key_part2, key_part3;
按照在索引中存储的顺序直接分组，key_part1相同的记录分为一组，再将key_part1相同的每组记录中对key_part2值进行分组，最后对key_part2值相同的每组记录中对key_part3值进行分组。
与使用B+树索引进行排序差不多，分组列的顺序也需要与索引列的顺序一致；也可以只使用索引列中左边连续的列进行分组。
回表的代价
二级索引的记录中没有保留完整记录，所以使用二级索引进行查询其他列时，是需要进行回表的。需要执行回表操作的记录越多，使用二级索引进行查询的性能也就越低。MySQL查询优化器会事先针对表中的记录计算一些统计数据，然后再利用这些统计数据或者访问表中的少量记录来计算需要执行回表操作的记录数量。如果需要执行回表的记录过多，则更倾向全表扫描，反之则使用二级索引+回表的方式。一般情况下，可以给查询语句指定LIMIT子句来限制查询返回的记录数，回表的记录越少，性能提升越高。
创建和使用索引的原则
-
只为用于搜索、排序或分组的列创建索引
只为出现在WHERE子句中的列、连接子句中的连接列，以及出现在ORDER BY或GROUP BY子句中的列创建索引。如果只是出现在查询列表中的列，就没这个必要。
-
考虑索引列中不重复值的个数
列中不重复值的个数占据全部记录数的比例太低时，也就是有很多重复值，不建议创建索引，会造成大量回表操作。
-
索引列的类型尽量小
每创建一个索引都会新建一颗B+树，这些B+树会包含索引列和主键，所以索引列的类型越小，越节省空间。
如果索引列存储的数据为校场字符串，只将字符串的前几个字符存放在索引中，也可以节省空间。如下列为前10个字符建立索引。
ALTER TABLE single_table ADD INDEX idx_key1(key1(10));
然后执行如下查询语句：
SELECT * FROM single_table WHERE key1 = 'abcdefghijklmn';
在idx_key1二级索引记录中只保留字符串的前10个字符，所以只能定位到'abcdefghij'的记录，然后扫描所有的这些记录，判断符合'abcdefghijklmn'条件。正是因为如此，所以这个索引不能用来排序，只能通过全表扫描+文件排序的方式进行排序。
-
覆盖索引
查询列表中包含非索引列，会造成回表操作，浪费性能。如果业务需要查询索引列以外的列，还是以业务为准。在使用查询语句时，最好仅将业务需要的列放到查询列表中，不要随便用*。
-
让索引列以列名的形式在搜索条件中单独出现
在搜索条件中不要对列进行函数、运算等操作，MySQL并不会将结果带入查询，而是直接认为这个不符合索引，而执行全盘扫描。例如下列这些写法索引失效：
SELECT * FROM single_table WHERE key2 * 2 <4000 > OK > Time: 0.574s SELECT * FROM single_table WHERE key2 <4000 / 2 > OK > Time: 0.004s
-
新插入记录时主键大小影响系统效率
B+树索引没加入一条记录时，都会根据索引列进行排序，若插入记录的主键大小小于记录中最大值，会导致记录都会向后移动，如果页中记录存放满了，会移动到新的页面，也就是页分裂。最好让插入记录的主键值递增，通过添加AUTO_INCREMENT属性。
-
重复索引
建立索引就是建立B+树，都是存储在磁盘中实实在在占用空间的数据，增删改记录时，都要进行维护排序工作。删除掉多余和重复的索引可以节省空间，提高性能。
阅读参考《MySQL是怎样运行的》小孩子4919.