mysql 时间过滤类型索引 mysql时间区间查询索引

这一章主要讲索引的一些应用规则。

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;

第一种不会走索引的

• 新插入的值最好在已有区间外
  比如插入到最左或最右端，这样可以避免内部大规模的分裂，最多影响一下边上
• 冗余和重复索引
  联合索引或者主键索引已经覆盖的列，通常没必要再建立单独的索引了