MySQL5.7性能优化(五)Single-Part索引的范围访

范围优化

  范围访问方法使用单个索引检索包含在一个或多个索引值区间内的表行子集。它可以用于Single-Part索引或Multiple-Part索引。以下部分描述优化器使用范围访问的条件：

• Single-Part索引的范围访问方法
• Multiple-Part索引的范围访问方法
• 多值比较的等式范围优化
• 行构造函数表达式的范围优化
• 用于范围优化的有限内存使用
    我将通过五篇文章来分别介绍这几种范围优化的方法。本文将以介绍Single-Part索引的范围访问方法为主。

Single-Part索引的范围访问方法

  对于单部分索引，索引值区间可以方便地用WHERE子句中的对应条件表示，表示为范围条件，而不是“区间”。
  Single-Part索引的范围条件定义如下:

• 对于BTREE索引和HASH索引，在()中使用=、<=>时的条件为空，或为非空操作符的时候键部分与常量值的比较都是一个范围条件。
• 此外，对于BTREE索引，当使用>、<、>=、<=、BETWEEN、!=、<>操作符或LIKE的参数内容是一个常量字符串，且不以通配符开头时，关键部分与常量值的比较是一个范围条件。
• 对于所有索引类型，由多个范围条件通过OR或AND组成一个范围条件。

  在前面的描述中，“常量值”是指以下内容之一：

• 一个常量形式的查询字符串。
• 来自同一连接的常量列或系统表的列
• 不相关子查询的结果
• 完全由前面类型的子表达式组成的任何表达式

  以下是在WHERE条件中包含范围条件的几个查询语句的示例：

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

  在优化器常量的传递阶段，可以将一些非常量值转换为常量。
  MySQL试图为每个可能的索引从WHERE子句中提取范围条件。在提取过程中，删除无法构造范围条件的条件，合并产生重叠范围的条件，删除产生空范围的条件。
  考虑下面的语句，其中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');

  关键值key1的提取过程如下:

• 1、以原有WHERE语句开头：

(key1 < 'abc' AND (key1 LIKE 'abcde%' OR key1 LIKE '%b')) OR
(key1 < 'bar' AND nonkey = 4) OR
(key1 < 'uux' AND key1 > 'z')

• 2、删除nonkey = 4和key1 LIKE '%b'，因为它们不能用于范围扫描。删除它们的正确方法是用TRUE替换它们，这样在进行范围扫描时就不会遗漏任何匹配的行。用TRUE代替以后得出以下SQL:

(key1 < 'abc' AND (key1 LIKE 'abcde%' OR TRUE)) OR
(key1 < 'bar' AND TRUE) OR
(key1 < 'uux' AND key1 > 'z')

• 3、 去除恒定真或恒定假的条件:
  • (key1 LIKE 'abcde%' OR TRUE)为恒真的条件
  • (key1 < 'uux' AND key1 > 'z')为恒假的条件
    用常数替换这些条件得到:

 (key1 < 'abc' AND TRUE) OR (key1 < 'bar' AND TRUE) OR (FALSE)

去掉不必要的真、假常数得到:

(key1 < 'abc') OR (key1 < 'bar')

• 4、将重叠区间合并为一个区间，得到用于范围扫描的最终条件:

(key1 < 'bar')

  一般来说(如上例所示)，用于范围扫描的条件和WHERE子句拥有更少的限制。MySQL执行额外的检查，过滤出满足范围条件但不满足完整WHERE子句的行。
  范围条件提取算法可以处理任意深度的嵌套AND/OR构造，其输出不依赖于WHERE子句中条件出现的顺序。
  MySQL不支持为空间索引的范围访问方法合并多个范围。为了解决这个限制，您可以使用UNOIN来关联具有相同的SELECT语句，来代替将每个空间谓词放在不同的SELECT中。

---

回到综述页