Mysql 语句优化方案—官方原版

一、概述

查询以SELECT语句的形式执行数据库中的所有查找操作。调整这些语句是当务之急，无论是实现动态网页的秒级响应时间，还是缩短几个小时来生成巨大的隔夜报告。

除了SELECT语句之外，查询的调优技术也适用于CREATE TABLE。。。作为SELECT，插入到。。。DELETE语句中的SELECT和WHERE子句。这些语句有额外的性能考虑，因为它们将写操作和面向读的查询操作结合在一起。

NDB集群支持连接下推优化，从而将符合条件的连接整体发送到NDB集群数据节点，在这些节点之间可以分布并并行执行。有关此优化的更多信息，请参阅NDB下推连接的条件。

优化查询的主要注意事项包括：

• 要进行慢速SELECT。。。WHERE查询速度更快，首先要检查的是是否可以添加索引。在WHERE子句中使用的列上设置索引，以加快结果的计算、筛选和最终检索。为了避免浪费磁盘空间，请构造一组索引，以加快应用程序中使用的许多相关查询。
• 索引对于使用联接和外键等特性引用不同表的查询尤其重要。您可以使用EXPLAIN语句来确定SELECT使用了哪些索引。
• 隔离并调整查询的任何部分，例如函数调用，这会花费大量时间。根据查询的结构，可以为结果集中的每一行调用一次函数，甚至为表中的每一行都调用一次，从而大大提高了效率。
• 尽量减少查询中的全表扫描次数，尤其是对于大表。
• 通过定期使用ANALYZETABLE语句使表统计信息保持最新，这样优化器就可以获得构建高效执行计划所需的信息。
• 了解每个表的特定于存储引擎的调整技术、索引技术和配置参数。InnoDB和MyISAM都有一套在查询中启用和保持高性能的准则。
• 避免以难以理解的方式转换查询，尤其是当优化器自动执行某些相同的转换时。
• 如果性能问题无法通过基本准则之一轻松解决，请通过阅读EXPLAIN计划并调整索引、WHERE子句、联接子句等来调查特定查询的内部细节
• 调整MySQL用于缓存的内存区域的大小和财产。通过高效使用InnoDB缓冲池、MyISAM密钥缓存和MySQL查询缓存，重复查询运行速度更快，因为第二次和后续时间都会从内存中检索结果。
• 即使对于使用缓存内存区域快速运行的查询，您也可以进一步优化，使其需要更少的缓存内存，从而使应用程序更具可扩展性。可扩展性意味着您的应用程序可以同时处理更多的用户、更大的请求等，而不会出现性能的大幅下降。
• 处理锁定问题，其中查询的速度可能会受到同时访问表的其他会话的影响。

二、WHERE 子句优化

本节讨论可用于处理WHERE子句的优化。示例使用SELECT语句，但相同的优化适用于DELETE和UPDATE语句中的WHERE子句。

您可能会尝试重写查询以使算术运算更快，同时牺牲可读性。由于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           

在 MySQL 8.0.14 及更高版本中，这发生在 准备而不是在优化阶段， 这有助于简化连接。

• 仅计算索引使用的常量表达式 一次。
• 从 MySQL 8.0.16 开始，列的比较 检查并折叠具有常量值的数值类型 或因无效或愤怒值而被删除：

# CREATE TABLE t (c TINYINT UNSIGNED NOT NULL);
  SELECT * FROM t WHERE c ≪ 256;
-≫ SELECT * FROM t WHERE 1;           

• 没有WHERE的单个表上的COUNT（*）直接从MyISAM和MEMORY表的表信息中检索。当只与一个表一起使用时，这也适用于任何NOT NULL表达式。
• 早期检测到无效的常量表达式。MySQL很快检测到某些SELECT语句是不可能的，并且不返回任何行。
• 如果不使用GROUP BY或聚合函数（COUNT（）、MIN（）等），HAVING将与WHERE合并。
• 对于联接中的每个表，构造一个更简单的WHERE，以获得表的快速WHERE求值，并尽快跳过行。
• 在查询中的任何其他表之前，首先读取所有常量表。常量表是以下任意一种：
• 空表或只有一行的表。
• 与PRIMARY KEY或UNIQUE索引上的WHERE子句一起使用的表，其中所有索引部分都与常量表达式进行比较，并定义为NOT NULL。

以下所有表格均用作常量表格：

SELECT * FROM t WHERE primary_key=1;
SELECT * FROM t1,t2
  WHERE t1.primary_key=1 AND t2.primary_key=t1.id;           

• 通过尝试所有可能性，可以找到连接表的最佳连接组合。如果ORDERBY和GROUPBY子句中的所有列都来自同一个表，则在连接时首先选择该表。
• 如果存在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 MAX(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, ... ;           

三、范围优化

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

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

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

单个零件索引的范围条件定义如下：

• 对于BTREE和HASH索引，当使用=、<=>、IN（）、is NULL或is NOT NULL运算符时，将键部分与常量值进行比较是一个范围条件。
• 此外，对于BTREE索引，当使用>、<、>=、<=、BETWEEN、！=、，如果LIKE的参数是一个不以通配符开头的常量字符串，则可以使用<>运算符或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.1.从原始WHERE子句开始：

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

1.2.删除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')           

1.3. 始终为真或假的折叠条件：

• (key1 LIKE 'abcde%' OR TRUE)是 永远正确
• (key1 < 'uux' AND key1 > 'z')总是假的

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

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

删除不必要的TRUE和FALSE常量会产生：

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

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

(key1 < 'bar')           

通常（如前一个示例所示），用于范围扫描的条件比WHERE子句的限制性小。MySQL执行额外的检查，以筛选出满足范围条件但不满足完整WHERE子句的行。

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

MySQL不支持为空间索引的范围访问方法合并多个范围。为了解决这个限制，您可以使用UNION和相同的SELECT语句，除了将每个空间谓词放在不同的SELECT中。

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

多部分索引上的范围条件是单个部分索引的范围条件的扩展。多部分索引上的范围条件限制索引行位于一个或多个键元组间隔内。使用索引中的排序，在一组键元组上定义键元组间隔。

例如，考虑定义为key1（key_part1、key_part2、key_part 3）的多部分索引，以及按键顺序列出的以下一组键元组：

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是=、<=>或ISNULL比较运算符之一，条件涵盖所有索引部分。（也就是说，有N个条件，N部分索引的每个部分有一个条件。）例如，以下是三部分HASH索引的范围条件：

key_part1 = 1 AND key_part2 IS NULL AND key_part3 = 'foo'           

有关被视为常量的定义，请参阅单个部分索引的范围访问方法。

• 对于BTREE索引，间隔可能适用于与AND组合的条件，其中每个条件使用=、<=>、IS NULL、>、<、>=、<=、！=、<>将关键部分与常量值进行比较，BETWEEN或LIKE'pattern'（其中'pattern''不以通配符开头）。只要可以确定包含所有符合条件的行的单关键字元组，就可以使用间隔（如果使用<>或！=，则使用两个间隔）。

只要比较运算符为=、<=>或ISNULL，优化器就会尝试使用其他关键部分来确定间隔。如果运算符是>，<，>=，<=，！=，<>，BETWEEN或LIKE，优化器使用它，但不考虑更多关键部分。对于以下表达式，优化器在第一次比较中使用=。它还使用第二次比较中的>=，但不考虑其他关键部分，也不使用第三次比较进行区间施工：

key_part1 = 'foo' AND key_part2 >= 10 AND key_part3 > 10           

单个间隔为：

('foo',10,-inf) < (key_part1,key_part2,key_part3) < ('foo',+inf,+inf)           

创建的间隔可能包含比初始条件更多的行。例如，前面的间隔包括值（'o'，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)           

在本例中，第一行的间隔使用一个键部分作为左边界，两个键部分用于右边界。第二行的间隔仅使用一个关键部分。EXPLAIN输出中的key_len列表示使用的密钥前缀的最大长度。

在某些情况下，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           

3、多值比较的相等范围优化

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

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

如果 col_name 等于以下任何一项，则每个表达式为 true。 几个值。这些比较是相等范围 比较（其中“范围”是单个 值）。优化程序估计读取成本 相等范围比较的限定行如下所示：

• 如果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。

在MySQL 8.0之前，除了使用eq_range_index_dive_limit系统变量之外，无法跳过使用索引俯冲来估计索引的有用性。在MySQL 8.0中，满足以下所有条件的查询可以跳过索引：

• 该查询针对单个表，而不是多个表上的联接。
• 存在单个索引FORCE index索引提示。其想法是，如果强制使用索引，那么对索引执行深入操作的额外开销将不会带来任何好处。
• 该索引不是唯一的，并且不是FULLTEXT索引。
• 不存在子查询。
• 不存在DISTINCT、GROUP BY或ORDER BY子句。

对于EXPLAIN For CONNECTION，如果跳过索引跳转，则输出更改如下：

• 对于传统输出，行和过滤值为NULL。
• 对于JSON输出，rows_inspected_per_scan和rows_produced_per_join不出现，skip_index_dive_due_to_force为true，成本计算不准确。

如果没有FOR CONNECTION，当跳过索引跳转时，EXPLAIN输出不会改变。

执行跳过索引俯冲的查询后，信息模式OPTIMIZER_TRACE表中的相应行包含skipped_due_to_force_index的index_dives_for_range_access值。

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

优化器能够应用范围扫描访问方法 对于此形式的查询：

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

要使优化程序使用范围扫描，查询必须满足 这些条件：

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

四、排序方式优化

本节描述MySQL何时可以使用索引来满足ORDERBY子句，当无法使用索引时使用的文件排序操作，以及优化器提供的有关ORDERBY的执行计划信息。

1、使用索引满足ORDER BY

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

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

假设（key_part1，key_part2）上有一个索引，下面的查询可以使用该索引来解析ORDERBY部分。优化器是否真的这样做取决于如果索引中没有的列也必须被读取，那么读取索引是否比表扫描更有效。

• 在此查询中，索引（key_part1，key_part2）使优化器能够避免排序：

SELECT * FROM t1
  ORDER BY key_part1, key_part2;           

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

如果t1是一个InnoDB表，则表主键隐式地是索引的一部分，索引可以用于解析此查询的ORDERBY：

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

• 在该查询中，key_part1是常量，因此通过索引访问的所有行都是key_part2顺序，如果WHERE子句的选择性足以使索引范围扫描比表扫描便宜，则（key_part 1，key_part 2）上的索引可以避免排序：

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;           

ORDER BY中的两列可以按相同方向排序（两个ASC或两个DESC），也可以按相反方向排序（一个ASC，一个DESC。使用索引的一个条件是索引必须具有相同的同质性，但不需要具有相同的实际方向。

如果查询混合了ASC和DESC，如果索引也使用相应的混合升序和降序列，优化器可以对列使用索引：

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

如果key_part1是递减的，而key_part2是递增的，优化器可以在（key_part3，key_part4）上使用索引。如果key_part1为升序，而key_part2为降序，则它还可以对这些列使用索引（使用反向扫描）。

• 在接下来的两个查询中，将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;           

• 在下一个查询中，ORDERBY没有命名key_part1，但所有选定的行都有一个常量key_part2值，因此索引仍然可以使用：

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

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

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

SELECT * FROM t1 ORDER BY key1, key2;           

• 查询对索引的非连续部分使用ORDER BY：

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

• 用于获取行的索引与ORDER BY：

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

• 查询将ORDERBY与包含索引列名以外的其他术语的表达式一起使用：

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

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

列别名的使用可能会影响排序索引的可用性。假设列t1.a被索引。在该语句中，选择列表中的列的名称是a。它引用t1.a，ORDERBY中的引用也是如此，因此可以使用t1.a上的索引：

SELECT a FROM t1 ORDER BY a;           

在此语句中，选择列表中列的名称也是，但它是别名。它引用ABS（a），ORDER BY中对a的引用也是如此，因此不能使用t1.a上的索引：

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

在下面的语句中，ORDERBY引用的名称不是选择列表中列的名称。但是t1中有一列名为a，因此ORDERBY引用t1.a，可以使用t1.a上的索引。（当然，生成的排序顺序可能与ABS（a）的排序顺序完全不同。）

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

以前（MySQL 5.7及更低版本），GROUP BY在某些条件下隐式排序。在MySQL 8.0中，这种情况不再发生，因此在末尾指定ORDER BY NULL以禁止隐式排序（如前所述）不再是必要的。但是，查询结果可能与以前的MySQL版本不同。要生成给定的排序顺序，请提供ORDERBY子句。

2、使用文件排序满足ORDER BY

如果索引不能用于满足ORDERBY子句，MySQL将执行文件排序操作，读取表行并对其进行排序。文件排序是查询执行中的一个额外的排序阶段。

为了获得文件排序操作的内存，从MySQL 8.0.12开始，优化器根据需要递增分配内存缓冲区，直到sort_buffer_size系统变量指示的大小，而不是像MySQL 8.0.12之前那样预先分配固定数量的sort_buffer.size字节。这允许用户将sort_buffer_size设置为更大的值，以加快更大的排序，而不必担心小排序会占用过多的内存。（对于Windows上的多线程malloc较弱的多并发排序，这一好处可能不会出现。）

如果结果集太大，无法容纳在内存中，则文件排序操作会根据需要使用临时磁盘文件。某些类型的查询特别适合完全在内存中进行文件排序操作。例如，优化器可以使用filesort在内存中高效地处理ORDERBY操作，而不使用临时文件，用于以下形式的查询（和子查询）：

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;           

五、条件过滤

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

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

只有在以下情况下，条件才有助于过滤估计：

• 它指的是当前表格。
• 它取决于连接序列中的一个或多个常量值。
• 访问方法尚未将其考虑在内。

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

前缀行计数（估计将从联接中的当前表传递到下一个表的行数）是行和过滤值的乘积。也就是说，前缀行计数是估计的行计数，减去估计的过滤效果。例如，如果行数为1000，过滤后的行数为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行。
• 部门表有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'           

如果没有条件过滤， 会生成类似 这：

+----+------------+--------+------------------+---------+---------+------+----------+
| 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，名称索引上的访问方法选择与“John”名称匹配的8行。没有进行任何过滤（过滤为100%），因此所有行都是下一个表的前缀行：前缀行计数为行×过滤＝8×100%＝8。

通过条件过滤，优化器还考虑了WHERE子句中的条件，而访问方法没有考虑这些条件。在这种情况下，优化器使用启发式方法来估计employee.hire_date上的BETWEEN条件的过滤效果为16.31%。因此，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   |
+----+------------+--------+------------------+---------+---------+------+----------+           

大家好，我是Doker品牌的Sinbad,欢迎点赞和评论,您的鼓励是我们持续更新的动力！更多资料请前往Doker 多克