Herman's Notes

Appearance

索引的代价 ​

索引在空间和时间上都会有代价

  • 空间上的代价：这是显而易见的，每创建一个索引，都需要为它创建一个B+树，每个B+树的节点都是一个数据页
  • 时间上的代价：每对表中的数据做增删改操作时，都需要修改各个B+树索引，影响操作效率；每次查找通常来说只能使用到一个索引，如果建太多索引，那么在查询的时候分析过程会耗时太多

所以我们需要创建又少又号的索引。

扫描区间和边界条件 ​

在使用某个索引执行查询的时候，关键的问题就是通过查询条件找出合适的扫描区间，然后再到对应的B+树中扫描索引列值在这些区间的记录。对于适合的区间来说， 只需要通过B+树定位到区间最左边的第一个值，就可以沿着链表向右查找出所有的记录满足条件的记录。

  • IN操作和若干个等值操作操作符之间用 OR 连接，都会产生多个单点区间
  • !=a 操作符产生的区间是 (-∞,'a') 和 ('a', +∞)
  • like 操作符只能匹配 完整字符串 或者 匹配字符串前缀

如何从复杂的搜索条件中找出区间，比如：

sql
select * from single_table where
(key1 > 'xyz' and key2 = 748) or 
(key1 < 'abc' and key1 > '1mn') or 
(key1 like '%suf' and key1 > 'zzz' and (key2 < 8000 or common_field = 'abc'))
  1. 假如使用 key1 作为索引查询，直接简化查询条件，把使用key2的条件替换成true
sql
(key1 > 'xyz' and true) or 
(key1 < 'abc' and key1 > '1mn') or 
(key1 like '%suf' and key1 > 'zzz' and true)

继续简化，把不能识别出区间的条件去掉，由于 (key1 < 'abc' and key1 > '1mn') 结果为false也可以去掉

sql
(key1 > 'xyz') or (key1 > 'zzz')

由于使用的是 or 连接，所以最终取最大区间也就是 key1 > 'xyz', 这就是使用key1作为查询索引的扫描范围；

  1. 假如使用 key2 作为索引查询，直接简化查询条件，把使用key1的条件替换成true
sql
(true and key2 = 748) or true or (true and (key2 < 8000 or true))

继续简化后

sql
key2 = 748 or true

最终使用key2作为索引扫描的区间是 (-∞, +∞)

所以最后Mysql会选择使用key1作为所以查询。

key1 like '%suf' 这个条件虽然不能减少扫描的结果，但可以直接在key1的索引中判断出来结果，过滤出不满足条件的记录，减少回表操作，这种优化方式叫做索引下推

索引用于排序 ​

  • 使用联合索引排序时，如果左边的查询条件是常量，可以使用右边的字段进行排序，比如：
sql
select * from single_table where where key1 = 'a' and key2='b' order by key3 limit 10;

不可以使用索引排序的几种情况：

  • 在同一排序中对不同的字段使用不同的排序方式，ASC、DESC混用；在使用联合索引排序的时候，要求各个字段的排序是一致的。
  • 排序列中包含了非同一个索引中的列,
sql
select * from single_table order key1, key2 limit 10;

这里key1,key2是两个不同的索引，同时参与排序的话将不会使用到索引

  • 排序的列是联合索引中的列，但是字段不连续，这时候不能使用到索引
sql
select * from single_table order by key1, key3 limit 10;
  • 用来形成扫描区间的列与排序的列不在同一索引
sql
select * from single_table where key1='1' order by key2 limit 10;
  • 排序的列必须以单独的列名的形式出现，不能够条件函数，或者做逻辑计算
sql
select * from single_table order by upper(key1) limit 10;

使用索引分组 ​

分组与排序差不多，分组的列的顺序必须与索引的顺序一致；也可以只使用索引左边连续的列进行分组。

回表 ​

需要执行回表的操作记录越多，使用二级索引进行查询的性能也就越低，导致某些查询操作宁愿使用全表扫描也不使用二级索引，比如需要查询索引90%的记录然后进行回表操作，那还不如直接使用全表扫描； 一般情况下我们可以给查询语句添加上limit来限制查询的数量，这样有可能会让mysql使用二级索引+回表的操作来查询。

需要对查询结果进行排序，如果使用二级索引查询需要执行的回表记录太多，也不会使用到索引

sql
select * from single_table order by key1;

更好的创建索引 ​

  • 只为搜索，排序，分组的列创建索引
  • 考虑索引列中不重复值的个数占全部记录条数的比例，如果比例太低，使用二级索引+回表的方式，就有可能执行太多次的回表操作
  • 索引列的类型尽量要小；索引列越小，单页存放的记录越多，磁盘io带来的性能损耗越小
  • 为列前缀建立索引，如果某个字符串列很长，可以给这个列建立前缀索引
sql
alter table single_tale add index idx_key1(key1(10))
  • 覆盖索引：对于查询语句返回的字段较少的情况，可以把返回的字段添加到索引里面，这样就可以告别回表操作

原文链接: http://herman7z.site