Mysql 范围查询优化

时间：2023-03-10 05:09:34
Mysql 范围查询优化

Range查询：用单独的Index的一个或多个index值来检索表的子集行数据，当然包含多个index。

1:一个index （单一部分）的range access 方法：（eg : 指的这种key (column1 )）

单独的index，index值间隔可以方便的由对应的where子句的条件表示，所有我们称值为range条件而不是间隔；

单独index的range条件的定义：

1.1:对于btree和hash索引，index和一个常量值通过 =, <=>,in(),is null,或者 IS not null操作符做比较；

1.2：另外，对于Btree索引，index和一个常量值通过 <,>,<=,>=,between，!=,或者<>操作符做比较；

1.3： 对于所有类型的index,多范围条件通过 or and关键字组合形式；

'常量值'在之前的描述中意味着：

2.1： 查询字符串的常量形式；

2.2： const 或者system表的一列（也只有一列）的自连接（join）；

2.3： 不相关子查询的结果；

2.4: 有上面类型子表达式完全组成的任意表达式；

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子句中的任何可能的index提取range condition,在提取过程中，不能构造的范围条件被舍去，产生重叠的条件被组合，产生空范围的条件被舍去。

如下：

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为index, nonkey非index;

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：舍去nokey = 4和key1 like ‘%d’,因为他们不能用作范围scan,正确的舍去方法是用true代替她们，

(key1 < 'abc' AND (key1 LIKE 'abcde%' OR TRUE)) OR

(key1 < 'bar' AND TRUE) OR

(key1 < 'uux' AND key1 > 'z')

3:  以下条件总是true or false;

   1:(key1 LIKE 'abcde%' OR TRUE) is always true

   2:(key1 < 'uux' AND key1 > 'z') is always false

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

4： 去掉不必要的true 和false条件：

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

5：组合那些重叠的区间成一个：

(key1 < 'bar')

范围条件的提取算法可以处理内嵌的and /or任意深度的构造，他的数去不依赖与他们出现在where子句中的顺序；

2：多part index的范围查询：

index的多个部分的范围条件是上面的扩展，被一个或几个key元组来限制条件：

eg: 一个联合索引定义：key1(key_part1,key_part2,key_part3),key元组以key order显示如下：

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)

这区间包括第四，第五，第六元组。

相对，条件 key_part3 = 'abc'不能定义一个单独的区间并且不能被区间scan方法使用（最左前缀index）;

3:多值的区间优化：

以下col_name 是index 列：

col_name IN(val1, ..., valN)

col_name = val1 OR ... OR col_name = valN

如果col_name 等于这些值中的任意一个返回true,

1:如果col_name 为unique index,则范围的行评估为1，因为最多一个值只能对应一行；

2：否则，优化器估计范围行数得使用index潜入和index的统计信息；

index潜入，优化器植入一个潜入在每个range的结尾并且用该范围的行数来估计，eg:col_name IN (10, 20, 30) ,优化器植入两个潜入在每个区间中来估计行数；index潜入提供精确的行估计，但是当表达式中的比较值越多，每个潜入对需要话费更多时间。用index统计信息缺乏精确但是较快。

30727 rows in set (5.37 sec)

mysql> explain select * from employees where first_name like 'm%' ;

+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+

| id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows   | Extra       |

+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+

|  1 | SIMPLE      | employees | ALL  | idx_fn_ln     | NULL | NULL    | NULL | 299290 | Using where |

+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+

1 row in set (0.00 sec)

mysql> explain select * from employees where first_name like 'mart%' ;

+----+-------------+-----------+-------+---------------+-----------+---------+------+------+-----------------------+

| id | select_type | table     | type  | possible_keys | key       | key_len | ref  | rows | Extra                 |

+----+-------------+-----------+-------+---------------+-----------+---------+------+------+-----------------------+

|  1 | SIMPLE      | employees | range | idx_fn_ln     | idx_fn_ln | 16      | NULL | 1635 | Using index condition |

+----+-------------+-----------+-------+---------------+-----------+---------+------+------+-----------------------+

1 row in set (0.00 sec)

第一个条件查找从执行计划上看走的扫表（all），where条件得到的结果为所以数据行的1/10;第二条增加like条件的匹配精度，走的ICP range查找，得到的数据行大约是1/30，mysql

会根据查找的数据范围（多少）决定走index range查找还是直接扫表（all）

mysql> desc select * from employees where first_name like 'ma%' and last_name like 'he%';

+----+-------------+-----------+-------+---------------+-----------+---------+------+-------+-----------------------+

| id | select_type | table     | type  | possible_keys | key       | key_len | ref  | rows  | Extra                 |

+----+-------------+-----------+-------+---------------+-----------+---------+------+-------+-----------------------+

|  1 | SIMPLE      | employees | range | idx_fn_ln     | idx_fn_ln | 34      | NULL | 38174 | Using index condition |

+----+-------------+-----------+-------+---------------+-----------+---------+------+-------+-----------------------+

1 row in set (0.04 sec)
mysql> desc select * from employees where first_name like 'ma%' or last_name like 'he%';

+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+

| id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows   | Extra       |

+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+

|  1 | SIMPLE      | employees | ALL  | idx_fn_ln     | NULL | NULL    | NULL | 299290 | Using where |

+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+

1 row in set (0.02 sec)
mysql> desc select * from employees where first_name in('mart','mori','moon');

+----+-------------+-----------+-------+---------------+-----------+---------+------+------+-----------------------+

| id | select_type | table     | type  | possible_keys | key       | key_len | ref  | rows | Extra                 |

+----+-------------+-----------+-------+---------------+-----------+---------+------+------+-----------------------+

|  1 | SIMPLE      | employees | range | idx_fn_ln     | idx_fn_ln | 16      | NULL |  710 | Using index condition |

+----+-------------+-----------+-------+---------------+-----------+---------+------+------+-----------------------+

1 row in set (0.02 sec)
mysql> explain select * from employees where emp_no = 11;

+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+

| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                               |

+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+

|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Impossible WHERE noticed after reading const tables |

+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+

1 row in set (0.01 sec)

Mysql发现where条件不可能成立 ，返回null

相关文章