SQL优化 - Range查询优化

https://dev.mysql.com/doc/refman/8.4/en/range-optimization.html

查询计划中 type 为 range 时，表示 MySQL 会通过索引进行范围扫描。

单列索引

对于单列索引，以下情况会使用到 range 优化：

1. BTREE和HASH索引，使用=、<=>、IN()、IS NULL或者IS NOT NULL
2. BTREE索引，使用>、<, >=、<=、BETWEEN、!=或者<>，以及LIKE 'xxx%'
3. BTREE和HASH索引，使用 OR 或者 AND 拼接上面这些条件

SELECT * FROM t1
  WHERE key_col > 1
  AND key_col < 10;

SELECT * FROM t1
  WHERE key_col = 1
  OR key_col IN (15,18,20);

SELECT * FROM t1
  WHERE key_col LIKE 'ab%'
  OR key_col BETWEEN 'bar' AND 'foo';

对常量值的理解

1. 查询的条件值为常量，比如SELECT * FROM t1 WHERE key_col = 'xxx'
2. JOIN操作中，比较的值来源于 const 或者 system 访问类型的表，例如：

-- t2 表只有一行，为 system 类型
SELECT * FROM t1
	LEFT JOIN t2 ON t1.key_col = t2.id 

-- t2 表不只有一行，但是根据条件确定唯一一行，也就是t3只有1行为const类型
SELECT * FROM t1
	LEFT JOIN (SELECT * FROM t2 WHERE id = 1) t3 
	ON t1.key_col = t3.id 

3. 非相关子查询的结果，例如

SELECT * FROM t1
  WHERE key_col IN (SELECT col FROM t2 WHERE t2.id = 2);

4. 以上3种情况的表达式的组合

提取 range 条件

优化器会有一个常量传播（constant propagation）的阶段，对于一些存在非常量值的条件，MySQL优化器可能会提取出全部条件中满足 range 的条件进行查询。整个过程如下所示：对于下面这条查询 sql

SELECT * FROM t1 WHERE
  (key1 < 'abc' AND (key1 LIKE 'abcde%' OR key1 LIKE '%b')) OR
  (key1 < 'bar' AND nonkey = 4) OR