mysql自我优化-优化select语句（四）

最新推荐文章于 2024-06-27 23:18:27 发布

追风少年201

最新推荐文章于 2024-06-27 23:18:27 发布

阅读量282

点赞数

分类专栏：数据库mysql 文章标签： mysql

原文链接：https://dev.mysql.com/doc/refman/5.7/en/order-by-optimization.html

版权

数据库mysql 专栏收录该内容

11 篇文章 1 订阅

订阅专栏

mysql自我优化

首先这里要简要说明，该文章翻译自 mysql手册，并经过我的整合，所以文字内容较多，但是实际含金量比较高， 建议大家仔细阅读， 必有收获。

1、条件过滤

在连接处理中，前缀行是从连接中的一个表传递到下一个表的那些行。通常，优化器会尝试在连接顺序的早期放置具有低前缀计数的表，以防止行组合数快速增加。如果优化器可以使用有关从一个表中选择并传递到下一个表的行的条件信息，它可以更准确地计算行估计并选择最佳执行计划。

如果没有条件过滤，表的前缀行计数基于WHERE子句根据优化器选择的任何访问方法选择的估计行数。条件过滤使优化器可以使用WHERE访问方法未考虑的子句中的其他相关条件，从而改进其前缀行计数估计。例如，即使可能有一种基于索引的访问方法可用于从连接中的当前表中选择行，但也可能有其他条件用于连接中的表。WHERE 可以过滤（进一步限制）传递到下一个表的合格行的估计值的子句。

只有在以下情况下，条件才会对过滤估计起作用：

它指的是当前表。
它取决于连接序列中早期表中的一个或多个常量值。
访问方法尚未考虑到它。

在EXPLAIN输出中， rows列表示所选访问方法的行估计值，filtered 列反映条件过滤的效果。 filtered值以百分比表示。最大值为 100，这意味着没有发生行过滤。从 100 开始减小的值表示过滤量增加。

前缀行计数（估计从连接中的当前表传递到下一个表的行数）是rows和 filtered值的乘积。也就是说，前缀行数是估计的行数，减去估计的过滤效果。例如，如果rows是 1000 并且filtered是 20%，条件过滤会将估计的行数 1000 减少到前缀行数 1000 × 20% = 1000 × .2 = 200。

考虑以下查询：

SELECT *
  FROM employee JOIN department ON employee.dept_no = department.dept_no
  WHERE employee.first_name = 'John'
  AND employee.hire_date BETWEEN '2018-01-01' AND '2018-06-01';

假设数据集具有以下特征:

员工表有1024行。
department表有12行。
两个表在dept_no上都有一个索引。
employee表在first_name上有一个索引。
employee.first_name上有8行满足这个条件:

employee.first_name = 'John'

在employee.hire_date中有150行满足这个条件:

employee.hire_date BETWEEN '2018-01-01' AND '2018-06-01'

1行同时满足两个条件:

employee.first_name = 'John'
AND employee.hire_date BETWEEN '2018-01-01' AND '2018-06-01'

如果没有条件过滤，EXPLAIN会产生如下输出:

+----+------------+--------+------------------+---------+---------+------+----------+
| id | table      | type   | possible_keys    | key     | ref     | rows | filtered |
+----+------------+--------+------------------+---------+---------+------+----------+
| 1  | employee   | ref    | name,h_date,dept | name    | const   | 8    | 100.00   |
| 1  | department | eq_ref | PRIMARY          | PRIMARY | dept_no | 1    | 100.00   |
+----+------------+--------+------------------+---------+---------+------+----------+

对于employee，name索引上的访问方法选取与名称匹配的 8 行’John’。没有进行过滤（filtered是 100%），所以所有行都是下一个表的前缀行：前缀行数是 rows× filtered= 8 × 100% = 8。

通过条件过滤，优化器会额外考虑WHERE 访问方法未考虑的子句中的条件。在这种情况下，优化器使用启发式方法来估计 16.31%,BETWEEN 条件的过滤效果employee.hire_date。结果，EXPLAIN产生如下输出：

+----+------------+--------+------------------+---------+---------+------+----------+
| id | table      | type   | possible_keys    | key     | ref     | rows | filtered |
+----+------------+--------+------------------+---------+---------+------+----------+
| 1  | employee   | ref    | name,h_date,dept | name    | const   | 8    | 16.31    |
| 1  | department | eq_ref | PRIMARY          | PRIMARY | dept_no | 1    | 100.00   |
+----+------------+--------+------------------+---------+---------+------+----------+

现在前缀行数为rows× filtered= 8 × 16.31% = 1.3，更能反映实际数据集。

通常，优化器不会计算最后一个连接表的条件过滤效果（前缀行计数减少），因为没有下一个表要传递行。发生异常 EXPLAIN：为了提供更多信息，对所有连接的表计算过滤效果，包括最后一个。

要控制优化器是否考虑其他过滤条件，请使用optimizer_switch系统变量的condition_fanout_filter标志(见“可切换优化”)。该标志在默认情况下是on的，但可以禁用以抑制条件过滤(例如，如果发现某个特定查询在不启用它的情况下会产生更好的性能)。

如果优化器高估了条件过滤的效果，则性能可能比不使用条件过滤时更差。在这种情况下，这些技术可能会有所帮助：

如果某列未编入索引，请将其编入索引，以便优化器了解有关列值分布的一些信息，并可以改进其行估计值。
更改连接顺序。完成此操作的方法包括连接顺序优化器提示（参见“优化器提示”）， STRAIGHT_JOIN紧跟在 SELECT, 和 STRAIGHT_JOIN连接运算符之后。
禁用会话的条件过滤：
SET optimizer_switch = 'condition_fanout_filter=off';

2、IS NULL 优化

MySQL可以对col_name IS NULL执行相同的优化，它可以使用col_name = constant_value。例如，MySQL可以使用索引和范围以IS NULL搜索NULL。

例子:

SELECT * FROM tbl_name WHERE key_col IS NULL;

SELECT * FROM tbl_name WHERE key_col <=> NULL;

SELECT * FROM tbl_name
  WHERE key_col=const1 OR key_col=const2 OR key_col IS NULL;

如果WHERE子句包含声明为NOT NULL的列的col_name IS NULL条件，则该表达式将被优化掉。如果列无论如何都可能产生NULL(例如，如果它来自LEFT JOIN右侧的表)，则不会发生这种优化。

MySQL还可以优化col_name = expr OR col_name IS NULL的组合，这种形式在已解析的子查询中很常见。当使用此优化时，EXPLAIN显示ref_or_null。

这种优化可以处理任何键部分的一个IS NULL。

一些优化查询的例子，假设在表t2的列a和列b上有一个索引:

SELECT * FROM t1 WHERE t1.a=expr OR t1.a IS NULL;

SELECT * FROM t1, t2 WHERE t1.a=t2.a OR t2.a IS NULL;

SELECT * FROM t1, t2
  WHERE (t1.a=t2.a OR t2.a IS NULL) AND t2.b=t1.b;

SELECT * FROM t1, t2
  WHERE t1.a=t2.a AND (t2.b=t1.b OR t2.b IS NULL);

SELECT * FROM t1, t2
  WHERE (t1.a=t2.a AND t2.a IS NULL AND ...)
  OR (t1.a=t2.a AND t2.a IS NULL AND ...);

ref_or_null的工作原理是:首先对引用键进行读取，然后对具有NULL键值的行进行单独搜索。

优化只能处理一个IS NULL级别。在下面的查询中，MySQL只对表达式(t1.a=t2.a AND t2.a IS NULL)并且不能在b上使用关键部分:

SELECT * FROM t1, t2
  WHERE (t1.a=t2.a AND t2.a IS NULL)
  OR (t1.b=t2.b AND t2.b IS NULL);

2、ORDER BY 优化

本节描述MySQL什么时候可以使用索引来满足ORDER BY子句，当索引不能被使用时使用的filesort操作，以及从优化器中获得的关于ORDER BY的执行计划信息。

有和没有LIMIT的ORDER BY可以以不同的顺序返回行，如“LIMIT查询优化”所讨论的。

1.使用索引满足 ORDER BY

在某些情况下，MySQL 可能会使用索引来满足 ORDER BY子句并避免执行filesort 操作所涉及的额外排序。

即使ORDER BY索引与索引不完全匹配，也可以使用索引，只要索引的所有未使用部分和所有额外 ORDER BY列都是WHERE子句中的常量。如果索引不包含查询访问的所有列，则仅当索引访问比其他访问方法便宜时才使用索引。

1.索引可能起作用的情况

假设在(key_part1, key_part2)上有一个索引，下面的查询可能使用这个索引来解析ORDER BY部分。如果必须读取索引中没有的列，那么优化器是否真的这样做取决于读取索引是否比表扫描更有效。

在这个查询中，(key_part1, key_part2)上的索引使优化器能够避免排序:

SELECT * FROM t1
  ORDER BY key_part1, key_part2;

但是，查询使用SELECT *，它可能比key_part1和key_part2选择更多的列。在这种情况下，扫描整个索引并查找表行以找到索引中没有的列，可能比扫描表并对结果排序代价更高。如果是这样，优化器就不太可能使用索引。如果SELECT *只选择索引列，则使用索引并避免排序。

如果t1是一个InnoDB表，表的主键是隐式索引的一部分，索引可以用来解析这个查询的ORDER BY:

SELECT pk, key_part1, key_part2 FROM t1
  ORDER BY key_part1, key_part2;

在这个查询中，key_part1是常量，所以通过索引访问的所有行都是按key_part2顺序的，如果WHERE子句的选择性足够使索引范围扫描比表扫描便宜，(key_part1, key_part2)上的索引会避免排序:

SELECT * FROM t1
  WHERE key_part1 = constant
  ORDER BY key_part2;

在接下来的两个查询中，是否使用索引与之前没有DESC显示的相同查询类似：

SELECT * FROM t1
  ORDER BY key_part1 DESC, key_part2 DESC;

SELECT * FROM t1
  WHERE key_part1 = constant
  ORDER BY key_part2 DESC;

在接下来的两个查询中，将key_part1与一个常量进行比较。如果WHERE子句的选择性足够使索引范围扫描比表扫描便宜，则使用索引:

SELECT * FROM t1
  WHERE key_part1 > constant
  ORDER BY key_part1 ASC;

SELECT * FROM t1
  WHERE key_part1 < constant
  ORDER BY key_part1 DESC;

在下一个查询中，ORDER BY没有命名key_part1，但是所有被选中的行都有一个固定的key_part1值，所以索引仍然可以使用:

SELECT * FROM t1
  WHERE key_part1 = constant1 AND key_part2 > constant2
  ORDER BY key_part2;

2.索引在order by中不起作用的情况

在某些情况下，MySQL无法使用索引来解析ORDER BY，尽管它仍然可以使用索引来查找与WHERE子句匹配的行。例子：

查询在不同的索引上使用ORDER BY:

SELECT * FROM t1 ORDER BY key1, key2;

该查询用于ORDER BY索引的非连续部分：

SELECT * FROM t1 WHERE key2=constant ORDER BY key1_part1, key1_part3;

查询混合了ASC和 DESC：

SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC;

用于获取行的索引与ORDER BY中使用的索引不同:

SELECT * FROM t1 WHERE key2=constant ORDER BY key1;

该查询使用ORDER BY和一个表达式，其中包含除索引列名以外的其他术语:

SELECT * FROM t1 ORDER BY ABS(key);
SELECT * FROM t1 ORDER BY -key;

查询连接了许多表，ORDER BY中的列并不都来自用于检索行的第一个非常量表。(这是EXPLAIN输出中第一个没有const连接类型的表。)
查询有不同的ORDER BY和 GROUP BY表达式。
只有在ORDER BY子句中命名的列的前缀上有一个索引。在这种情况下，索引不能用于完全解析排序顺序。例如，如果只索引CHAR(20)列的前10个字节，则索引不能区分超过第10个字节的值，因此需要一个文件ort。
索引不按顺序存储行。例如，对于MEMORY表中的HASH索引就是如此。

用于排序的索引的可用性可能会受到列别名的使用的影响。假设该列 t1.a已编入索引。在此语句中，选择列表中列的名称是 a。它指的是t1.a，如同在在 ORDER BY中的a，所以 t1.a上的索引可用：

SELECT a FROM t1 ORDER BY a;

在此语句中，选择列表中的列的名称也是a，但它是别名。它指的是ABS(a)，在ORDER BY中对a的引用也是如此，所以t1.a上的索引不能使用:

SELECT ABS(a) AS a FROM t1 ORDER BY a;

在下面的语句中，ORDER BY引用的名称不是选择列表中的列的名称。但是t1中有一列名为a，因此ORDER BY指向t1.A,t1.a上的索引可以使用。(当然，得到的排序顺序可能与ABS(a)的顺序完全不同。)

SELECT ABS(a) AS b FROM t1 ORDER BY a;

默认情况下，MySQL排序GROUP By col1, col2，…如果您还包含ORDER BY col1, col2，…在查询。如果包含一个包含相同列列表的显式ORDER BY子句，MySQL会在没有任何速度损失的情况下优化它，尽管仍然会进行排序。

如果查询包含GROUP BY，但希望避免对结果进行排序的开销，可以通过指定ORDER BY NULL来抑制排序。例如:

INSERT INTO foo
SELECT a, COUNT(*) FROM bar GROUP BY a ORDER BY NULL;

优化器仍然可以选择使用排序来实现分组操作。ORDER BY NULL 抑制结果的排序，而不是通过分组操作完成的预先排序以确定结果。

GROUP BY默认情况下隐式排序（即，在没有列ASC或列的DESC指示符的情况下GROUP BY）。但是，不推荐依赖隐式 GROUP
BY排序（即在没有ASC或 DESC指示符的情况下进行排序）或显式排序GROUP BY（即，对列使用显式ASC或DESC指示符GROUP
BY）。要生成给定的排序顺序，请提供一个 ORDER BY子句。

2.使用文件排序(filesort)来满足 ORDER BY

如果索引不能满足ORDER BY子句，MySQL将执行一个filesort操作，读取表行并对它们进行排序,filesort在查询执行中构成了一个extra的排序阶段。

为了获得用于filesort操作的内存，优化器会预先分配固定数量的sort_buffer_size字节。单个会话可以根据需要更改此变量的会话值，以避免过度使用内存，或者根据需要分配更多内存。

如果结果集太大，内存无法容纳，那么filesort操作将根据需要使用临时磁盘文件。有些类型的查询特别适合于完全在内存中的filesort操作。例如，在没有临时文件的情况下，优化器可以使用filesort在内存中有效地处理以下形式的查询(和子查询)中的ORDER BY操作:

SELECT ... FROM single_table ... ORDER BY non_index_column [DESC] LIMIT [M,]N;

这样的查询在web应用程序中很常见，这些应用程序只显示较大结果集中的几行。例子:

SELECT col1, ... FROM t1 ... ORDER BY name LIMIT 10;
SELECT col1, ... FROM t1 ... ORDER BY RAND() LIMIT 15;

3.影响 ORDER BY 优化

对于没有使用filesort的低速ORDER BY查询，请尝试将max_length_for_sort_data系统变量降低到适合触发filesort的值。(将该变量的值设置得过高的症状是磁盘活动过多而CPU活动过少。)

为了提高ORDER BY的速度，请检查是否可以让MySQL使用索引而不是使用额外的的排序阶段。如果这是不可能的，试试下面的策略:

增加sort_buffer_size变量值。理想情况下，该值应该足够大，以使整个结果集适合排序缓存区(以避免写入磁盘和合并传递)，但该值至少必须足够容纳15个元组。(最多15个临时磁盘文件被合并，内存中每个文件必须至少有一个元组。)

要考虑到排序缓存区中存储的列值的大小受max_sort_length系统变量值的影响。例如，如果元组存储长字符串列的值，你增加max_sort_length的值，排序缓存区元组的大小也会增加，可能需要你增加sort_buffer_size。对于作为字符串表达式的结果计算的列值(比如调用字符串值函数的列值)，filesort算法不能告诉表达式值的最大长度，因此它必须为每个元组分配max_sort_length字节。

要监视合并传递(合并临时文件)的数量，请检查Sort_merge_passes状态变量。

增加read_rnd_buffer_size变量值，以便一次读取更多行。
将系统变量tmpdir更改为指向具有大量空闲空间的专用文件系统。变量值可以列出几种循环方式使用的路径;您可以使用此特性将负载分散到多个目录。在Unix上用冒号(:)分隔路径，在Windows上用分号(;)分隔路径。路径应该命名位于不同物理磁盘上的文件系统中的目录，而不是同一磁盘上的不同分区。

4.ORDER BY 执行计划信息可用

使用EXPLAIN(参考“用EXPLAIN优化查询”)，你可以检查MySQL是否可以使用索引来解析ORDER BY子句:

如果EXPLAIN输出的Extra列不包含Using filesort，则使用索引，不执行filesort。
如果EXPLAIN输出的Extra列包含Using filesort，则不使用索引，并执行filesort。

此外，如果执行filesort，优化器跟踪输出包括一个filesort_summary块。例如:

"filesort_summary": {
  "rows": 100,
  "examined_rows": 100,
  "number_of_tmp_files": 0,
  "sort_buffer_size": 25192,
  "sort_mode": "<sort_key, packed_additional_fields>"
}