|
这一章主要讲索引的一些应用规则。
7.1
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),
KEY idx_key1 (key1),
UNIQUE KEY uk_key2 (key2),
KEY idx_key3 (key3),
KEY idx_key_part(key_part1, key_part2, key_part3)
) Engine=InnoDB CHARSET=utf8;
这个表以后的章节也会大量使用。
7.2
索引虽然可以方便我们快速查找，但维护索引也是有代价的。
空间上，我们肯定需要单独的空间去维护索引。
时间上，当我们对记录进行修改操作时，索引也要跟着变化。
此外，执行查询前需要生成查询计划。每个查询计划中，通常只会用最多一个二级索引。
如果我们建立大量的索引，会使得分析查询计划开销的过程变慢。
7.3
扫描区间
select * from single_table where id >= 2 and id <= 100;
这个sql的扫描区间是[2, 100]，边界条件是 id >= 2 and id <= 100
select * from single_table where key2 in (1438, 6328) or (key2 >= 38 and key2 <= 79);
其中 1438 6328这两个点本身也算扫描区间，叫做单点扫描区间。边界条件就是 [1438, 1438]这种。
并不是所有搜索条件都可以成为边界条件。比如 where key1 < ‘a’。扫描区间是负无穷到’a’的开区间。
这种时候就没有什么边界条件一说了。
综上，在使用某个索引执行查询时，关键的问题就是通过搜索条件找出合适的扫描区间，然后再到对应的B+树中扫描索引列值在这些扫描区间的记录。要先找出所有可用的索引和扫描区间，然后综合选择。
所有搜索条件都可以生成合适的扫描区间
select * from single_table where key2 > 100 and key2 > 200;
select * from single_table where key2 > 100 or key2 > 200;
这两个sql中，搜索条件都是可以用来帮助减小搜索范围的。
有的搜索条件不能生成合适的扫描区间
select * from single_table where key2 > 100 and common_field = 'abc';
这个sql里，common_field字段不能帮助减少任何扫描范围，因为key2上的索引不涉及common_field
相当于一个[100, INF] 和 (-INF, INF) 的交集。
这种时候，我们可以直接把第二个搜索条件视为true。
从复杂的搜索条件中找出扫描区间
select * from single_table where
(key1 > 'xyz' and key2 = 748) or
(key1 < 'abc' and key1 > 'lmn') or
(key1 like '%suf' and key1 > 'zzz' and (key2 < 8000 or common_field = 'abc'));
这个sql可以说是很复杂了。但是我们也就可能使用key1的索引或key2的索引。
假设我们用key1的索引，则可以化简为
(key1 > 'xyz' and true) or (key1 < 'abc' and key1 > 'lmn') or (true and key1 > 'zzz' and (true or true))
(key1 > 'xyz') or (key1 < 'abc' and key1 > 'lmn') or (key1 > 'zzz')
(key1 > 'xyz') or (key1 > 'zzz')
key1 > 'xyz'
这样不断化简就可以了。
同理，对key2分析的话，过程如下
(true and key2 = 748) or (true and true) or (true and true and (key2 < 8000 or true))
true
使用联合索引执行查询时对应的扫描区间
select * from single_table where key_part1 = 'a';
我们可以定位到=a的第一条记录，然后往后一条条扫描。
select * from single_table where key_part1 = 'a' and key_part2 = 'b';
道理和上面的一样。定位到符合条件的第一条记录往后扫描即可。
select * from single_table where key_part1 = 'a' and key_part2 = 'b' and key_part3 = 'c';
同上
select * from single_table where key_part1 < 'a';
对于这个搜索条件，我们要找到第一个小于’a’的记录，然后向后扫描，直到某个记录不符合条件为止。
select * from single_table where key_part1 = 'a' and key_part2 > 'a' and key_part2 < 'd';
我们还是定位到第一条符合条件的记录，然后向后查询，直到不满足条件为之
select * from single_table where key_part2 = 'a';
不符合最左前缀，我们只能全都扫描了。
select * from single_table where key_part1 = 'a' and key_part3 = 'c';
这里也是不完全符合最左前缀，先定位到满足part1='a’的第一条记录，然后往后一条条查找，直到不符合part1 = 'a’结束
select * from single_table where key_part1 < 'b' and key_part2 = 'a';
从满足part1 < 'b’的第一条记录开始往后扫描。直到某条记录不满足part1 < ‘b’。
select * from single_table where key_part1 <= 'b' and key_part2 = 'a';
当扫描到一条part1 = 'b’且part2 != 'a’的记录时就可以停止。因为part123是有序的。
索引用于排序
在MySQL中，在内存/磁盘中进行排序的方式称为文件排序。但由于索引本身是有序的，因此在索引上进行查找的时候，我们有可能省略排序的步骤。比如在主键上搜素 或者联合索引是覆盖索引的情况。
注意，使用联合索引进行排序时，如果想让索引帮助我们省略排序的步骤，则order by中列的顺序要与索引中的一致。
不可以使用索引进行排序的几种情况
1 asc desc混用。这个在mysql 8.0中支持了，引入了descending index，之前都是不支持的。
2 排序列包含非同一个索引的列
select * from single_table order by key1, key2 limit 10;
这个例子里，就不能使用索引排序了。
3 排序列是某个联合索引的索引列，但这些排序列的顺序和联合索引中的不一样。
4 用来形成扫描区间的索引列和排序列不一样
select * from single_table where key1 = 'a' order by key2 limit 10;
用key1形成扫描区间，然后用key2排序。
5 排序列不是以单独列名的形式出现在order by子句中。
select * from single_table order by upper(key1) limit 10;
索引用于分组
select key_part1, key_part2, key_part3, count(*) from single_table group by key_part1, key_part2, key_part3;
如果没有idx_key_part索引，就得建立一个用于统计的临时表，在扫描聚簇索引的记录时将统计的中间结果填入这个临时表。
但现在有联合索引，所以不用建立临时表了。
7.4
正常的扫描全表虽然没有搜索加速，但是磁盘io是顺序的，相对较快。
可每次回表，因为主键值是没有规律的，因此回表时的磁盘io是随机的。
因此，在扫描大量的列时，回表带来的开销可能比顺序扫描全表更大。
查询优化器会做这方面的工作，看看到底是用二级索引+回表还是直接在聚簇索引上扫描。
7.5
这一节举出了一些用索引的小tips
- 只为用于搜索、排序、连接、分组的列创建索引
- 考虑索引列中不重复值的个数
这里解释一下，比如性别只有两个值，如果对性别建索引，则索引几乎不会帮我们加快查找，那二级索引+回表的查找方式会显得格外的亏。如果势比较高的情况下，二级索引+回表相对来说不会那么亏 - 索引列的类型尽量小
尤其是主键，小的话二级索引占用的空间就会笑，B+树消耗的页面也相对少 - 为列前缀建立索引
alter table single_table drop index idx_key1;
alter table single_table add index idx_key1 (key1(10));
比如varchar列太长，我们可以只对前10个字母建立索引。这样省空间，但是不能用来简化排序了
- 覆盖索引
指索引包含全部要查询的列。我们可以把常用的组合建个联合索引，减少回表开销 - 让索引以列名的形式在搜索条件中单独出现
select * from single_table where key2 * 2 < 4;
select * from single_table where key2 < 4/2;
第一种不会走索引的
- 新插入的值最好在已有区间外
比如插入到最左或最右端，这样可以避免内部大规模的分裂，最多影响一下边上 - 冗余和重复索引
联合索引或者主键索引已经覆盖的列，通常没必要再建立单独的索引了
|