Mysql Select语句之子句与范围优化

Java有货 2020-07-03

215

点击上方「Java有货」关注我们

技术交流群添加方式

添加小编微信:372787553，备注:进群

带您进入Java技术交流群

优化SELECT语句

优化查询的主要考虑因素是：

为了使慢速SELECT ... WHERE
查询更快，首先要检查的是是否可以添加索引。在WHERE
子句中使用的列上设置索引，以加快评估，过滤和最终检索结果的速度。为避免浪费磁盘空间，请构建一小组索引，以加快应用程序中使用的许多相关查询的速度。
对于使用连接和外键之类的功能引用不同表的查询，索引尤其重要。您可以使用该EXPLAIN
语句来确定用于的索引 SELECT
隔离和调整查询中花费过多时间的任何部分，例如函数调用。根据查询的结构方式，可以对结果集中的每一行调用一次函数，甚至可以对表中的每一行调用一次函数，从而极大地提高了效率。
最小化查询中全表扫描的次数，特别是对于大表。
通过ANALYZE TABLE
定期使用该语句来使表统计信息保持最新，使得优化器具有构造有效执行计划所需的信息。
了解特定于每个表的存储引擎的调整技术，索引技术和配置参数。InnoDB
与 MyISAM
有两套准则的实现和维持查询高性能。
您可以InnoDB
使用优化InnoDB只读事务”中的技术优化表的单查询事务。
避免以难以理解的方式转换查询，尤其是在优化程序自动执行某些相同转换的情况下。
如果使用基本准则之一不能轻松解决性能问题，请通过阅读EXPLAIN
计划并调整索引，WHERE
子句，连接子句等来调查特定查询的内部详细信息。
调整MySQL用于缓存的内存区域的大小和属性。通过有效地使用 InnoDB
缓冲池， MyISAM
键高速缓存和MySQL查询高速缓存，重复查询的运行速度更快，因为第二次及以后都从内存中检索了结果。
即使对于使用缓存区域快速运行的查询，您仍可能会进一步优化，以使它们需要更少的缓存，从而使您的应用程序更具可伸缩性。可伸缩性意味着您的应用程序可以处理更多的并发用户，更大的请求等，而不会导致性能大幅下降。
处理锁定问题，其中其他会话同时访问表可能会影响查询速度。

WHERE子句优化

这些示例使用 SELECT
语句，但是相同的优化适用WHERE
于DELETE
和 UPDATE
语句中的子句。

您可能会想重写查询以使算术运算更快，同时又牺牲了可读性。由于MySQL自动进行类似的优化，因此您通常可以避免这项工作，而将查询保留为更易于理解和维护的形式。MySQL执行的一些优化如下：

删除不必要的括号：

   ((a AND b) AND c OR (((a AND b) AND (c AND d))))
  
-> (a AND b AND c) OR (a AND b AND c AND d)
复制

恒定折叠：

   (a<b AND b=c) AND a=5
  
-> b>5 AND b=c AND a=5
复制

恒定条件消除：

   (b>=5 AND b=5) OR (b=6 AND 5=5) OR (b=7 AND 5=6)
  
-> b=5 OR b=6
复制

索引使用的常量表达式仅计算一次。
COUNT(*)
上没有一个单一的表WHERE
是从该表信息直接检索MyISAM
和MEMORY
表。NOT NULL
当仅与一个表一起使用时，对于任何表达式也可以执行此操作。
早期检测无效的常量表达式。MySQL快速检测到某些 SELECT
语句是不可能的，并且不返回任何行。
HAVING WHERE
如果您不使用GROUP BY
或汇总功能（COUNT()
， MIN()
等），则与合并。
对于连接中的每个表，WHERE
构造一个更简单 WHERE
的表以获得表的快速评估，并尽快跳过行。
在查询中的任何其他表之前，首先读取所有常量表。常量表可以是以下任意一个：
以下所有表均用作常量表：
```
SELECT * FROM t WHERE primary_key=1;
SELECT * FROM t1,t2
  WHERE t1.primary_key=1 AND t2.primary_key=t1.id;
复制
```

空表或具有一行的表。
与a 或索引WHERE
上的子句一起使用的表，其中所有索引部分都与常量表达式进行比较，并定义为。PRIMARY KEY UNIQUE NOT NULL

通过尝试所有可能的方法，找到用于联接表的最佳联接组合。如果ORDER BY
and GROUP BY
子句中的所有列都来自同一表，则在连接时优先使用该表。
如果有一个ORDER BY
子句和另一个GROUP BY
子句，或者如果 ORDER BY
或GROUP BY
包含联接队列中第一个表以外的表中的列，则会创建一个临时表。
如果使用SQL_SMALL_RESULT
修饰符，MySQL将使用内存中的临时表。
查询每个表索引，并使用最佳索引，除非优化程序认为使用表扫描更有效。一次使用扫描是基于最佳索引是否跨越了表的30％以上，但是固定百分比不再决定使用索引还是扫描。现在，优化器更加复杂，其估计基于其他因素，例如表大小，行数和I / O大小。
在某些情况下，MySQL甚至可以在不查询数据文件的情况下从索引中读取行。如果索引中使用的所有列都是数字，则仅索引树用于解析查询。
在输出每一行之前，HAVING
将跳过不匹配该子句的那些行。

快速查询的一些示例：

SELECT COUNT(*) FROM tbl_name;

SELECT MIN(key_part1),MAX(key_part1) FROM tbl_name;

SELECT MAX(key_part2) FROM tbl_name
  WHERE key_part1=constant;

SELECT ... FROM tbl_name
  ORDER BY key_part1,key_part2,... LIMIT 10;

SELECT ... FROM tbl_name
  ORDER BY key_part1 DESC, key_part2 DESC, ... LIMIT 10;
复制

假设索引列是数字，MySQL仅使用索引树来解析以下查询：

SELECT key_part1,key_part2 FROM tbl_name WHERE key_part1=val;

SELECT COUNT(*) FROM tbl_name
  WHERE key_part1=val1 AND key_part2=val2;

SELECT key_part2 FROM tbl_name GROUP BY key_part1;
复制

以下查询使用索引来按排序顺序检索行，而无需单独的排序遍历：

SELECT ... FROM tbl_name
  ORDER BY key_part1,key_part2,... ;

SELECT ... FROM tbl_name
  ORDER BY key_part1 DESC, key_part2 DESC, ... ;
复制

范围优化

range
访问方法使用单个索引来检索包含一个或若干个索引值的时间间隔内表行的子集。它可以用于单部分或多部分索引。以下各节描述了优化器使用范围访问的条件。

单部分索引的范围访问方法

对于单部分索引，索引值间隔可以方便地由条款中的相应条件 WHERE
表示，称为范围条件，而不是“ 间隔”。”

单部分索引的范围条件的定义如下：

对于这两种BTREE
和 HASH
索引，使用时具有恒定值的关键部分的比较是一个范围条件 =
， <=>
， IN()
，IS NULL
，或IS NOT NULL
运营商。
另外，对于BTREE
索引，当使用具有恒定值的关键部分的比较是一个范围条件 >
， <
， >=
， <=
， BETWEEN
， !=
，或 <>
运营商，或者LIKE
比较，如果参数 LIKE
是一个常数字符串不与通配符开始。
对于所有索引类型，多个范围条件组合OR
或 AND
形成一个范围条件。

前面的描述中的“ 恒定值 ”表示以下之一：

查询字符串中的常量
来自同一联接的const
或[system
表的列
不相关子查询的结果
任何完全由上述类型的子表达式组成的表达式

以下是该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
如下：

从原始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'
因为它们不能用于范围扫描。删除它们的正确方法是将它们替换为TRUE
，这样在进行范围扫描时我们不会丢失任何匹配的行。用TRUE
产量代替它们：
```
(key1 < 'abc' AND (key1 LIKE 'abcde%' OR TRUE)) OR
(key1 < 'bar' AND TRUE) OR
(key1 < 'uux' AND key1 > 'z')
复制
```

崩溃条件始终为true或false：

用常量替换这些条件将产生：

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

去除不必要的TRUE
和 FALSE
常数的产率：

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

(key1 LIKE 'abcde%' OR TRUE)
永远是真的
(key1 < 'uux' AND key1 > 'z')
永远是假的

将重叠的间隔合并为一个会产生用于范围扫描的最终条件：

(key1 < 'bar')
复制

一般而言（如前面的示例所示），范围扫描所使用的条件比该WHERE
子句的限制要少。MySQL执行附加检查以过滤出满足范围条件但不包括full WHERE
子句的行。

范围条件提取算法可以处理任意深度的嵌套 AND
/ OR
构造，并且其输出不取决于条件在WHERE
子句中出现的顺序。

MySQL不支持range
为空间索引的访问方法合并多个范围。要解决此限制，可以将a UNION
与相同的SELECT
语句一起使用，只是将每个空间谓词放在不同的中 SELECT
。

多部分索引的范围访问方法

多部分索引的范围条件是单部分索引的范围条件的扩展。多部分索引上的范围条件将索引行限制在一个或几个键元组间隔内。使用从索引开始的顺序，在一组键元组上定义键元组间隔。

例如，考虑定义为的多部分索引，并按键顺序列出以下一组键元组：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个元组，并且可以由范围访问方法使用。

相反，该条件 *
key_part3* = 'abc'
未定义单个间隔，并且不能被范围访问方法使用。

以下描述更详细地说明了范围条件如何作用于多部分索引。

对于HASH
索引，可以使用包含相同值的每个间隔。这意味着只能针对以下形式的条件生成间隔：
```
    key_part1 cmp const1
AND key_part2 cmp const2
AND ...
AND key_partN cmp constN;
复制
```
这里const1
， const2
...是常数，cmp
是一个 =
， <=>
或者IS NULL
比较运营商，以及条件覆盖所有指数部分。（也就是说，N
有条件，N
-part索引的每个部分都有一个条件。）例如，以下是三部分HASH
索引的范围条件：
```
key_part1 = 1 AND key_part2 IS NULL AND key_part3 = 'foo'
复制
```
对于一个BTREE索引，以一定间隔可能是可用于条件组合 AND，其中每个状态具有恒定值使用一个关键部分进行比较 =， <=>， IS NULL， >， <， >=， <=， !=， <>， BETWEEN，或（其中 LIKE 'pattern''pattern' 不以通配符开头）。只要可以确定包含所有与条件匹配的行的单个键元组，就可以使用一个间隔（如果使用<> 或，!= 则使用两个间隔）。
只要比较运算符为，或=，优化器就会尝试使用其他关键部分来确定间隔。如果操作是，，，，，，，或者，优化器使用它，但认为没有更多的关键部分。对于以下表达式，优化器使用第一个比较中的值。它也使用 <=>IS NULL><>=<=!=<>BETWEENLIKE=>= 根据第二次比较，但不考虑其他关键部分，并且不将第三次比较用于区间构造：
```
  key_part1 = 'foo' AND key_part2 >= 10 AND key_part3 > 10
复制
```
单个间隔为：
```
('foo',10,-inf) < (key_part1,key_part2,key_part3) < ('foo',+inf,+inf)
复制
```
创建的间隔可能包含比初始条件更多的行。例如，前面的时间间隔包含('foo', 11, 0)
不满足原始条件的值。
如果将覆盖间隔中包含的行集合的条件与组合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)
复制
```
在此示例中，第一行的间隔使用一个关键部分作为左边界，使用两个关键部分作为右边界。第二行的间隔仅使用一个关键部分。输出中的key_len
列EXPLAIN
表示所使用的密钥前缀的最大长度。
在某些情况下，key_len
可能表明已使用了关键部件，但这可能不是您期望的。假设 key_part1
和 key_part2
可以是 NULL
。然后，该 key_len
列显示以下条件的两个关键零件长度：
```
key_part1 >= 1 AND key_part2 < 2
复制
```
但是，实际上，条件已转换为：
```
key_part1 >= 1 AND key_part2 IS NOT NULL
复制
```

多值比较的等距范围优化

考虑以下表达式，其中 col_name
是索引列：

col_name IN(val1, ..., valN)
col_name = val1 OR ... OR col_name = valN
复制

如果col_name
等于多个值中的任何一个，则每个表达式为true 。这些比较是相等范围比较（其中“ range ”是单个值）。优化器估算相等范围比较的读取合格行的成本，如下所示：

如果在上有唯一索引 col_name
，则每个范围的行估计为1，因为最多一行可以具有给定值。
否则，任何索引 col_name
都不是唯一的，优化器可以使用对索引或索引统计的深入估算来估计每个范围的行数。

使用索引潜水时，优化器在范围的每个末端进行潜水，并将范围中的行数用作估计值。例如，表达式 *
col_name* IN (10, 20, 30)
具有三个相等范围，并且优化器对每个范围进行两次下潜以生成行估计。每对潜水都会得出具有给定值的行数的估计值。

索引潜水可提供准确的行估计，但是随着表达式中比较值数量的增加，优化器将花费更长的时间来生成行估计。使用索引统计数据的准确性不及使用索引潜水的准确性，但允许对大型值列表进行更快的行估计。

使用 eq_range_index_dive_limit
系统变量，您可以配置优化程序从一种行估计策略切换到另一种行估计策略的值的数量。要允许使用索引潜水进行最多N
等于范围的比较，请设置 eq_range_index_dive_limit
为N
+1。要禁用统计信息，并且始终使用索引潜水而不管 N
，将其设置 eq_range_index_dive_limit
为0。

要更新表索引统计信息以获得最佳估计值，请使用 ANALYZE TABLE
。

即使在本应使用索引潜水的条件下，对于满足所有这些条件的查询也将跳过它们：

存在单索引FORCE INDEX
索引提示。这样的想法是，如果强制使用索引，那么执行潜入索引的额外开销将无济于事。
索引不是唯一索引，不是 FULLTEXT
索引。
没有子查询。
没有DISTINCT
，GROUP BY
或ORDER BY
子句存在。

这些跳水条件仅适用于单表查询。对于多表查询（联接），不会跳过索引潜水。

行构造函数表达式的范围优化

优化器可以将范围扫描访问方法应用于以下形式的查询：

SELECT ... FROM t1 WHERE ( col_1, col_2 ) IN (( 'a', 'b' ), ( 'c', 'd' ));
复制

以前，要使用范围扫描，必须将查询编写为：

SELECT ... FROM t1 WHERE ( col_1 = 'a' AND col_2 = 'b' )
OR ( col_1 = 'c' AND col_2 = 'd' );
复制

为了使优化器使用范围扫描，查询必须满足以下条件：

仅使用IN()谓词，不使用NOT IN()。
在IN()谓词的左侧，行构造器仅包含列引用。
在IN()谓词的右侧，行构造器仅包含运行时常量，这些常量是在执行期间绑定到常量的文字或本地列引用。
在IN()谓词的右侧，有多个行构造函数。

限制内存使用以进行范围优化

要控制范围优化器可用的内存，请使用 range_optimizer_max_mem_size 系统变量：

值0表示“ 无限制”。”
值大于0时，优化器将在考虑范围访问方法时跟踪消耗的内存。如果将要超过指定的限制，则将放弃范围访问方法，而应考虑其他方法，包括全表扫描。这可能不是最佳选择。如果发生这种情况，则会发生以下警告（ N当前 range_optimizer_max_mem_size 值为）：
```
Warning    3170    Memory capacity of N bytes for
                   'range_optimizer_max_mem_size' exceeded. Range
                   optimization was not done for this query.
复制
```
对于UPDATE和 DELETE语句，如果优化器退回到全表扫描并且sql_safe_updates启用了系统变量，则会发生错误而不是警告，因为实际上，没有键用于确定要修改的行

对于超出可用范围优化内存的单个查询，并且对于该查询，优化器将退回至次优计划，增加 range_optimizer_max_mem_size 值可以提高性能。

若要估计处理范围表达式所需的内存量，请使用以下准则：

对于诸如以下的简单查询，其中有一个用于范围访问方法的候选关键字，与组合OR
使用的每个谓词大约使用230个字节：
```
SELECT COUNT(*) FROM t
WHERE a=1 OR a=2 OR a=3 OR .. . a=N;
复制
```
类似地，对于以下查询，每个谓词组合AND
使用大约125个字节：
```
SELECT COUNT(*) FROM t
WHERE a=1 AND b=1 AND c=1 ... N;
复制
```
对于带有IN()
谓词的查询：
```
SELECT COUNT(*) FROM t
WHERE a IN (1,2, ..., M) AND b IN (1,2, ..., N);
复制
```
IN()列表中的每个文字值都与组合为谓词OR。如果有两个IN() 列表，则与组合的谓词 OR数量是每个列表中文字值数量的乘积。因此，OR在前一种情况下组合的谓词数为 M× N。