深入理解MySQL中的范围访问方法：单索引与多索引优化策略

深入理解MySQL中的范围访问方法：单索引与多索引优化策略

范围查询方法是数据库查询优化的一种重要手段，它通过利用索引来减少需要检查的数据行数，从而提高查询的执行效率。

范围查询使用单个索引来检索表中包含在一个或多个索引值区间内的子集。它可以用于单索引或多索引。本文会对优化器使用范围访问的条件进行详细介绍。

单索引

针对单索引查询，我们可以通过WHERE子句中的条件来定义索引值区间，这些条件被称为范围条件。范围条件可以是等于（=）、不等于（!= 或 <>）、大于（>）、小于（<）、大于等于（>=）、小于等于（<=）、BETWEEN、IN()、LIKE（当LIKE的参数是不以通配符开头的常量字符串时）、IS NULL或IS NOT NULL等比较操作。

对于BTREE索引，以上所有比较操作都能定义范围条件，而对于HASH索引，只有等于、IS NULL和IS NOT NULL操作能定义范围条件。

考虑以下查询语句，其中key1是一个索引列，而nonkey不是索引列：

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

提取过程首先移除不能用于范围扫描的nonkey = 4和key1 LIKE '%b'条件，因为它们不符合范围条件的定义。然后，通过合并和简化条件，最终确定用于范围扫描的条件。

上述过程的结果是(key1 < 'bar')，这是一个比原始WHERE子句更宽松的条件。MySQL将使用这个条件进行范围扫描，然后再执行额外的检查，以过滤出满足范围条件但不满足完整WHERE子句的行。

优化建议

• 使用BTREE索引：对于需要执行范围查询的列，使用BTREE索引通常比HASH索引更有优势，因为它支持更多类型的范围查询。
• 精确定义范围条件：通过精确定义WHERE子句中的范围条件，可以帮助MySQL优化器更有效地利用索引进行查询。
• 避免使用通配符开头的LIKE操作：当需要使用LIKE进行模糊匹配时，尽量避免参数以通配符开头，因为这会阻止优化器使用范围访问方法。

多索引

多索引的范围条件通过在WHERE子句中使用特定条件来限制索引行，使其位于一个或多个数据区间内。这些数据区间根据索引的排序来定义。

例如，设想一个定义为key1(key_part1, key_part2, key_part3)的多索引，以下是按键顺序列出的一组数据：

key_part1  key_part2  key_part3
  NULL       1          'abc'
  NULL       1          'xyz'
  NULL       2          'foo'
   1         1          'abc'
   1         1          'xyz'
   1         2          'abc'
   2         1          'aaa'

条件key_part1 = 1定义了以下区间：

(1,-inf,-inf) <= (key_part1,key_part2,key_part3) < (1,+inf,+inf)

该区间覆盖了上述数据集中的第4、5、6条数据，可以被范围访问方法使用。

对于HASH索引

每个包含相同值的区间都可以被使用。这意味着区间只能为以下形式的条件生成：

    key_part1 cmp const1 
AND key_part2 cmp const2 
AND ... 
AND key_partN cmp constN;

这里，const1、const2等是常数，cmp是=, <=>, 或IS NULL比较运算符，且条件覆盖了所有索引部分。

对于BTREE索引

条件与AND组合时，如果每个条件使用=, <=>, IS NULL, >, <, >=, <=, !=, <>, BETWEEN, 或LIKE 'pattern'（其中’pattern’不以通配符开头）将键部分与常量值进行比较，那么一个区间可能是可用的。

如果比较运算符是=, <=>, 或IS NULL，优化器尝试使用额外的键部分来确定区间。
如果运算符是>, <, >=, <=, !=, <>, BETWEEN, 或LIKE，优化器将使用它但不考虑更多键部分。

范围条件的优化实践

当使用OR组合覆盖区间内行集的条件时，它们形成了一个覆盖它们区间并集内行集的条件。如果条件与AND组合，则形成一个覆盖它们区间交集内行集的条件。

例如，对于一个两部分索引的条件：

(key_part1 = 1 AND key_part2 < 2) OR (key_part1 > 5)

区间为：

(1,-inf) < (key_part1,key_part2) < (1,2)
(5,-inf) < (key_part1,key_part2)

参考链接

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