表结构

优化 SQL 语句

  • 查询优化器优化 SQL, 生成执行计划
  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.     KEY idx_key1 (key1),
  12.     UNIQUE KEY idx_key2 (key2),
  13.     KEY idx_key3 (key3),
  14.     KEY idx_key_part(key_part1, key_part2, key_part3)
  15. ) Engine=InnoDB CHARSET=utf8;

我们为这个 single_table 表建立了1个聚簇索引和4个二级索引，分别是：

  • 为 id 列建立的聚簇索引。
  • 为 key1 列建立的 idx_key1 二级索引。
  • 为 key2 列建立的 idx_key2 二级索引，而且该索引是唯一二级索引。
  • 为 key3 列建立的 idx_key3 二级索引。
  • 为 key_part1 、 key_part2 、 key_part3 列建立的 idx_key_part 二级索引，这也是一个联合索引。

访问方法（access method）的概念

我们平时所写的那些查询语句本质上只是一种声明式的语法，只是告诉MySQL我们要获取的数据符合哪些规则，至于MySQL背地里是怎么把查询结果搞出来的那是MySQL自己的事儿。

设计MySQL的大叔把查询的执行方式大致分为下边两种:

  • 使用全表扫描进行查询

这种执行方式很好理解，就是把表的每一行记录都扫一遍嘛，把符合搜索条件的记录加入到结果集就完了。不管是啥查询都可以使用这种方式执行，当然，这种也是最笨的执行方式。

  • 使用索引进行查询

如果查询语句中的搜索条件可以使用到某个索引，那直接使用索引来执行查询可能会加快查询执行的时间。

类似算法中的剪枝.

使用索引来执行查询的方式五花八门，又可以细分为许多种类：

  • 针对主键或唯一二级索引的等值查询
  • 针对普通二级索引的等值查询
  • 针对索引列的范围查询
  • 直接扫描整个索引

同一个查询语句可以使用多种不同的访问方法来执行.

const (聚簇索引上的等值查询)

SELECT * FROM single_table WHERE id = 1438;

MySQL会直接利用主键值在聚簇索引中定位对应的用户记录:

image.png

B+树本来就是一个矮矮的大胖子，所以这样根据主键值定位一条记录的速度贼快。类似的，我们根据唯一二级索引列来定位一条记录的速度也是贼快的，比如下边这个查询：

SELECT * FROM single_table WHERE key2 = 3841;

这个查询的执行过程的示意图就是这样：

  • 查询两次, 每次都使用索引

image.png

const，意思是常数级别的，代价是可以忽略不计的。不过这种const访问方法只能在主键列或者唯一二级索引列和一个常数进行等值比较时才有效，如果主键或者唯一二级索引是由多个列构成的话，索引中的每一个列都需要与常数进行等值比较，这个const访问方法才有效

对于唯一二级索引来说，查询该列为NULL值的情况比较特殊:

SELECT * FROM single_table WHERE key2 IS NULL;

因为唯一二级索引列并不限制 NULL 值的数量，所以上述语句可能访问到多条记录，也就是说 上边这个语句不可以使用const访问方法来执行

ref (普通二级索引上的等值查询)

普通的二级索引列 (不是唯一的索引) 与常数进行等值比较:

SELECT * FROM single_table WHERE key1 = 'abc';

由于普通二级索引并不限制索引列值的唯一性，所以可能找到多条对应的记录，也就是说使用二级索引来执行查询的代价取决于等值匹配到的二级索引记录条数。如果匹配的记录较少，则回表的代价还是比较低的.

设计MySQL的大叔就把这种搜索条件为 (普通) 二级索引列与常数等值比较，采用二级索引来执行查询的访问方法称为：ref.

image.png

需要注意下边两种情况：

  • 二级索引列值为NULL的情况

不论是普通的二级索引，还是唯一二级索引，它们的索引列对包含NULL值的数量并不限制，所以我们采用key IS NULL这种形式的搜索条件最多只能使用 **ref** 的访问方法，而不是const的访问方法。

  • 对于某个包含多个索引列的二级索引来说，只要是最左边的连续索引列是与常数的等值比较就可能采用ref的访问方法
SELECT * FROM single_table WHERE key_part1 = 'god like';
SELECT * FROM single_table WHERE key_part1 = 'god like' AND key_part2 = 'legendary';
SELECT * FROM single_table WHERE key_part1 = 'god like' AND key_part2 = 'legendary' AND key_part3 = 'penta kill';

但是如果最左边的连续索引列并不全部是等值比较的话，它的访问方法就不能称为 **ref** 了:

SELECT * FROM single_table WHERE key_part1 = 'god like' AND key_part2 > 'legendary';

ref_or_null (+NULL 查询)

有时候我们不仅想找出某个二级索引列的值等于某个常数的记录，还想把该列的值为NULL的记录也找出来，就像下边这个查询：

SELECT * FROM single_table WHERE key1 = 'abc' OR key1 IS NULL;

当使用二级索引而不是全表扫描的方式执行该查询时，这种类型的查询使用的访问方法就称为 ref_or_null ，这个ref_or_null访问方法的执行过程如下：

image.png

range (某种索引上的范围查询)

SELECT * FROM single_table WHERE key2 IN (1438, 6328) OR (key2 >= 38 AND key2 <= 79);

image.png

  • 范围1：key2 = 1438
  • 范围2：key2 = 6328
  • 范围3：key2 ∈ [38, 79]，注意这里是闭区间

可以把那种索引列等值匹配的情况称之为单点区间，上边所说的范围1和范围2都可以被称为单点区间，像范围3这种的我们可以称为连续范围区间

index (二级联合索引上的全扫描)

SELECT key_part1, key_part2, key_part3 FROM single_table WHERE key_part2 = 'abc';

由于key_part2并不是联合索引idx_key_part最左索引列，所以我们无法使用ref或者range访问方法来执行这个语句

这个查询符合下边这两个条件:

  • 它的查询列表只有3个列：key_part1, key_part2, key_part3，而索引 idx_key_part 又包含这三个列。
  • 搜索条件中只有key_part2列。这个列也包含在索引 idx_key_part 中。

也就是说我们可以直接通过遍历 idx_key_part 索引的叶子节点的记录来比较 key_part2 = 'abc' 这个条件是否成立，把匹配成功的二级索引记录的 key_part1, key_part2, key_part3 列的值直接加到结果集中就行了。由于二级索引记录比聚簇索记录小的多，而且这个过程也不用进行回表操作，所以直接遍历二级索引比直接遍历聚簇索引的成本要小很多，设计MySQL的大叔就把这种采用遍历二级索引记录的执行方式称之为：index。

all (全表扫描)

对于InnoDB表来说也就是直接扫描聚簇索引，设计MySQL的大叔把这种使用全表扫描执行查询的方式称之为：all。

注意事项

重温 二级索引 + 回表

SELECT * FROM single_table WHERE key1 = 'abc' AND key2 > 1000;
  1. 优化器会根据统计数据, 选择一个匹配行数较少的索引
  2. 在选择好的二级索引上, 根据条件进行筛选
  3. 回表, 再根据其他条件进行筛选

明确range访问方法使用的范围区间

所有搜索条件都可以使用某个索引的情况

SELECT * FROM single_table WHERE key2 > 100 AND key2 > 200;

image.png

SELECT * FROM single_table WHERE key2 > 100 OR key2 > 200;

image.png

有的搜索条件无法使用索引的情况

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

请注意，这个查询语句中能利用的索引只有idx_key2一个，而idx_key2这个二级索引的记录中又不包含common_field这个字段，所以在使用二级索引idx_key2定位记录的阶段用不到common_field = ‘abc’这个条件，这个条件是在回表获取了完整的用户记录后才使用的，而范围区间是为了到索引中取记录中提出的概念，所以在确定范围区间的时候不需要考虑common_field = ‘abc’这个条件，我们在为某个索引确定范围区间的时候只需要把用不到相关索引的搜索条件替换为TRUE就好了。

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

如果我们强制使用idx_key2执行查询的话，对应的范围区间就是(-∞, +∞)，也就是需要将全部二级索引的记录进行回表，这个代价肯定比直接全表扫描都大了。也就是说一个使用到索引的搜索条件和没有使用该索引的搜索条件使用OR连接起来后是无法使用该索引的。

复杂搜索条件下找出范围匹配的区间

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')) ;

假设我们使用idx_key1执行查询:

  • 把那些用不到该索引的搜索条件暂时移除掉，移除方法也简单，直接把它们替换为TRUE就好了
(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')
  • 替换掉永远为TRUE或FALSE的条件:
(key1 > 'xyz') OR (key1 > 'zzz')
  • 继续化简区间
key1 > xyz

也就是说：上边那个有一坨搜索条件的查询语句如果使用 idx_key1 索引执行查询的话，需要把满足key1 > xyz的二级索引记录都取出来，然后拿着这些记录的id再进行回表，得到完整的用户记录之后再使用其他的搜索条件进行过滤。

假设我们使用idx_key2执行查询:

  • 把那些用不到该索引的搜索条件暂时使用TRUE条件替换掉
(TRUE AND key2 = 748 ) OR
(TRUE AND TRUE) OR
(TRUE AND TRUE AND (key2 < 8000 OR TRUE))
  • 简化
TRUE

这个结果也就意味着如果我们要使用idx_key2索引执行查询语句的话，需要扫描idx_key2二级索引的所有记录，然后再回表，这不是得不偿失么，所以这种情况下不会使用idx_key2索引的

索引合并

在一个查询中使用到多个二级索引: index merge

具体的索引合并算法有下边三种.

Intersection合并

等值查询.

Intersection翻译过来的意思是交集。这里是说某个查询可以使用多个二级索引，将从多个二级索引中查询到的结果取交集，比方说下边这个查询：

SELECT * FROM single_table WHERE key1 = 'a' AND key3 = 'b';

过程:

  1. 从idx_key1二级索引对应的B+树中取出key1 = ‘a’的相关记录。
  2. 从idx_key3二级索引对应的B+树中取出key3 = ‘b’的相关记录。
  3. 二级索引的记录都是由索引列 + 主键构成的，所以我们可以计算出这两个结果集中id值的交集。
  4. 按照上一步生成的id值列表进行回表操作，也就是从聚簇索引中把指定id值的完整用户记录取出来，返回给用户。

MySQL在某些特定的情况下才可能会使用到Intersection索引合并：

  • 二级索引列是等值匹配的情况，对于联合索引来说，在联合索引中的每个列都必须等值匹配，不能出现只匹配部分列的情况
SELECT * FROM single_table WHERE key1 = 'a' AND key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c';

不能进行Intersection索引合并：

SELECT * FROM single_table WHERE key1 > 'a' AND key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c';

SELECT * FROM single_table WHERE key1 = 'a' AND key_part1 = 'a';
  • 主键列可以是范围匹配
SELECT * FROM single_table WHERE id > 100 AND key1 = 'a';

之所以在二级索引列都是等值匹配的情况下才可能使用Intersection索引合并，是因为只有在这种情况下根据二级索引查询出的结果集是按照主键值排序的。从各个二级索引中查询的到的结果集本身就是已经按照主键排好序的，那么求交集的过程就很easy

索引合并也可以有聚簇索引参加.

SELECT * FROM single_table WHERE key1 = 'a' AND id > 100;

优化器只有在单独根据搜索条件从某个二级索引中获取的记录数太多，导致回表开销太大，而通过Intersection索引合并后需要回表的记录数大大减少时才会使用Intersection索引合并。

Union合并

等值查询.

我们在写查询语句时经常想把既符合某个搜索条件的记录取出来，也把符合另外的某个搜索条件的记录取出来，我们说这些不同的搜索条件之间是OR关系。有时候OR关系的不同搜索条件会使用到不同的索引，比方说这样：

SELECT * FROM single_table WHERE key1 = 'a' OR key3 = 'b'

Intersection是交集的意思，这适用于使用不同索引的搜索条件之间使用AND连接起来的情况；Union是并集的意思，适用于使用不同索引的搜索条件之间使用OR连接起来的情况。与Intersection索引合并类似，MySQL在某些特定的情况下才可能会使用到Union索引合并：

  • 二级索引列是等值匹配的情况，对于联合索引来说，在联合索引中的每个列都必须等值匹配，不能出现只出现匹配部分列的情况
SELECT * FROM single_table WHERE key1 = 'a' OR ( key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c');

而下边这两个查询就不能进行Union索引合并：

SELECT * FROM single_table WHERE key1 > 'a' OR (key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c');

SELECT * FROM single_table WHERE key1 = 'a' OR key_part1 = 'a';
  • 主键列可以是范围匹配
  • 使用Intersection索引合并的搜索条件
SELECT * FROM single_table WHERE key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c' OR (key1 = 'a' AND key3 = 'b');

过程:

  1. 先按照搜索条件key1 = ‘a’ AND key3 = ‘b’从索引idx_key1和idx_key3中使用Intersection索引合并的方式得到一个主键集合。
  2. 再按照搜索条件key_part1 = ‘a’ AND key_part2 = ‘b’ AND key_part3 = ‘c’从联合索引idx_key_part中得到另一个主键集合。
  3. 采用Union索引合并的方式把上述两个主键集合取并集，然后进行回表操作，将结果返回给用户。

优化器只有在单独根据搜索条件从某个二级索引中获取的记录数比较少，通过Union索引合并后进行访问的代价比全表扫描更小时才会使用Union索引合并。

Sort-Union合并

Union索引合并的使用条件太苛刻，必须保证各个二级索引列在进行等值匹配的条件下才可能被用到，比方说下边这个查询就无法使用到Union索引合并：

SELECT * FROM single_table WHERE key1 < 'a' OR key3 > 'z'

这是因为根据key1 < ‘a’从idx_key1索引中获取的二级索引记录的主键值不是排好序的，根据key3 > ‘z’从idx_key3索引中获取的二级索引记录的主键值也不是排好序的，但是key1 < ‘a’和key3 > ‘z’这两个条件又特别让我们动心，所以我们可以这样：

  1. 先根据key1 < ‘a’条件从idx_key1二级索引中获取记录，并按照记录的主键值进行排序
  2. 再根据key3 > ‘z’条件从idx_key3二级索引中获取记录，并按照记录的主键值进行排序
  3. 因为上述的两个二级索引主键值都是排好序的，剩下的操作和Union索引合并方式就一样了。

我们把上述这种先按照二级索引记录的主键值进行排序，之后按照Union索引合并方式执行的方式称之为Sort-Union索引合并，很显然，这种Sort-Union索引合并比单纯的Union索引合并多了一步对二级索引记录的主键值排序的过程。

索引合并注意事项

联合索引替代Intersection索引合并

SELECT * FROM single_table WHERE key1 = 'a' AND key3 = 'b';

要是把这两个列搞一个联合索引，那直接使用这个联合索引就把事情搞定了，何必用啥索引合并呢

思维导图

单表访问方法.xmind

索引合并.xmind