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

2022-11-10 15:17:00

mysql自我优化

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

1、GROUP BY 优化

满足一个GROUP BY 子句的最通用的方法是扫描整个表并创建一个新的临时表，其中每个组的所有行都是连续的，然后使用这个临时表来发现组并应用聚合函数（如果有）。在某些情况下，MySQL 能够做得比这更好，并通过使用索引访问避免创建临时表。

为GROUP BY使用索引的最重要的先决条件是，所有GROUP BY列引用来自同一索引的属性，并且索引按顺序存储键(例如，对于BTREE索引是这样的，而对于HASH索引则不是)。是否可以用索引访问代替临时表的使用还取决于在查询中使用索引的哪些部分、为这些部分指定的条件以及所选的聚合函数

有两种方法可以GROUP BY 通过索引访问来执行查询，详细内容将在下面的部分中介绍。第一种方法将分组操作与所有范围谓词（如果有）一起应用。第二种方法首先执行范围扫描，然后对生成的元组进行分组。

在MySQL中，GROUP BY用于排序，因此服务器也可以对分组应用ORDER BY优化。但是，不建议依赖隐式或显式GROUP BY排序。参见“ORDER BY Optimization”。

1.松散索引扫描

处理GROUP BY最有效的方法是使用索引直接检索分组列。使用这种访问方法，MySQL使用一些索引类型的属性，键是有序的(例如，BTREE)。此属性允许在索引中使用查找组，而不必考虑索引中满足所有WHERE条件的所有键。这种访问方法只考虑索引中键的一部分，因此称为松散索引扫描。当没有WHERE子句时，松散索引扫描读取与组数量一样多的键，组的数量可能比所有键的数量要少得多。如果WHERE子句包含范围谓词(请参阅第8.8.1节“用EXPLAIN优化查询”中对范围连接类型的讨论)，那么松散索引扫描将查找满足范围条件的每个组的第一个键，并再次读取尽可能小的键数。这在下列情况下是可能的:

查询只针对一个表。
GROUP BY只命名构成索引最左边前缀的列，而不命名其他列。(如果查询有DISTINCT子句，而不是groupby，则所有不同的属性都指向构成索引最左边前缀的列。)例如，如果表t1在(c1,c2,c3)上有索引，那么如果查询具有groupby c1,c2，则适用松散索引扫描。如果查询具有GROUP BY c2, c3(列不是最左边的前缀)或GROUP BY c1, c2, c4 (c4不在索引中)，则不适用。
选择列表中使用的唯一聚合函数（如果有）是MIN()and MAX()，并且它们都引用同一列。该列必须在索引中，并且必须紧跟在 GROUP BY.
除了查询中引用的GROUP BY之外，索引的其他部分必须是常量(也就是说，它们必须在带有常量的等式中引用)，MIN()或MAX()函数的参数除外
对于索引中的列，必须索引完整的列值，而不仅仅是一个前缀。例如，对于c1 VARCHAR(20)， INDEX (c1(10))，索引只使用c1值的前缀，不能用于松散索引扫描。

如果松散索引扫描适用于查询，则 EXPLAIN输出显示 Using index for group-by在 Extra列中。

假设idx(c1,c2,c3)table 上有一个索引 t1(c1,c2,c3,c4)。松散索引扫描访问方法可用于以下查询：

SELECT c1, c2 FROM t1 GROUP BY c1, c2;
SELECT DISTINCT c1, c2 FROM t1;
SELECT c1, MIN(c2) FROM t1 GROUP BY c1;
SELECT c1, c2 FROM t1 WHERE c1 < const GROUP BY c1, c2;
SELECT MAX(c3), MIN(c3), c1, c2 FROM t1 WHERE c2 > const GROUP BY c1, c2;
SELECT c2 FROM t1 WHERE c1 < const GROUP BY c1, c2;
SELECT c1, c2 FROM t1 WHERE c3 = const GROUP BY c1, c2;

以下查询无法通过这种快速选择方法执行，原因如下:

除了MIN()或MAX()之外，还有其他聚合函数:

SELECT c1, SUM(c2) FROM t1 GROUP BY c1;

GROUP BY子句中的列不构成最左边的索引前缀:

SELECT c1, c2 FROM t1 GROUP BY c2, c3;

该查询引用了GROUP BY部分之后的键的一部分，并且与常量不相等:

SELECT c1, c3 FROM t1 GROUP BY c1, c2;

如果查询包含WHERE c3 = const，则可以使用松散索引扫描。

除了已经支持的MIN()和MAX()引用之外，松散索引扫描访问方法可以应用于选择列表中的其他形式的聚合函数引用:

支持AVG(DISTINCT)、SUM(DISTINCT)和COUNT(DISTINCT)。AVG(DISTINCT)和SUM(DISTINCT)只有一个参数。COUNT(DISTINCT)可以有多个列参数。
查询中必须没有GROUP BY或DISTINCT子句。
前面描述的松散索引扫描限制仍然适用。

假设表t1(c1,c2,c3,c4)上有一个索引idx(c1,c2,c3,c4)。Loose Index Scan访问方法可以用于以下查询:

SELECT COUNT(DISTINCT c1), SUM(DISTINCT c1) FROM t1;

SELECT COUNT(DISTINCT c1, c2), COUNT(DISTINCT c2, c1) FROM t1;

2.紧密索引扫描

紧密索引扫描可以是完整索引扫描或范围索引扫描，具体取决于查询条件。

当不满足松散索引扫描的条件时，仍然可以避免为GROUP BY查询创建临时表。如果WHERE子句中有范围条件，该方法只读取满足这些条件的键。否则，它执行索引扫描。由于此方法读取WHERE子句定义的每个范围内的所有键，或者如果没有范围条件则扫描整个索引，因此称为紧密索引扫描。使用紧密索引扫描，只有在找到所有满足范围条件的键后才执行分组操作。

要使此方法工作，对于引用GROUP BY键的部分之前或之间的部分的查询中的所有列，有一个常量相等条件就足够了。相等条件中的常量将填入搜索键中的任何“空白”，这样就可以形成索引的完整前缀。这些索引前缀可以用于索引查找。如果GROUP BY结果需要排序，并且有可能形成索引前缀的搜索键，MySQL也避免了额外的排序操作，因为在有序索引中使用前缀搜索已经按顺序检索了所有键。

假设idx(c1,c2,c3)table 上有一个索引 t1(c1,c2,c3,c4)。以下查询不适用于前面描述的松散索引扫描访问方法，但仍适用于紧密索引扫描访问方法。

GROUP BY中有一个缺口，但它被条件c2 = 'a’所覆盖:

SELECT c1, c2, c3 FROM t1 WHERE c2 = 'a' GROUP BY c1, c3;

GROUP BY不以键的第一部分开始，但有一个条件为该部分提供一个常量

SELECT c1, c2, c3 FROM t1 WHERE c1 = 'a' GROUP BY c2, c3;

2、DISTINCT 优化

因为DISTINCT可以使用GROUP BY，了解 MySQL 如何处理不属于所选列的部分ORDER BY或HAVING子句中的列。请参阅“MySQL 处理 GROUP BY”。

在大多数情况下，DISTINCT可以将子句视为的特例GROUP BY。例如，以下两个查询是等效的：

SELECT DISTINCT c1, c2, c3 FROM t1
WHERE c1 > const;

SELECT c1, c2, c3 FROM t1
WHERE c1 > const GROUP BY c1, c2, c3;

由于这种等效性，适用于GROUP BY查询的优化也可以应用于带有DISTINCT子句的查询。因此，有关DISTINCT查询优化可能性的更多详细信息，请参阅上面的“GROUP BY 优化”。

当LIMIT row_count和DISTINCT组合时，MySQL一旦发现row_count唯一的行就会停止。

如果没有使用查询中命名的所有表中的列，MySQL一旦找到第一个匹配项就停止扫描任何未使用的表。在下面的例子中，假设t1在t2之前使用(你可以用EXPLAIN检查)，MySQL在t2中找到第一行时停止从t2中读取(对于t1中的任何特定行):

SELECT DISTINCT t1.a FROM t1, t2 where t1.a=t2.a;

2、LIMIT 查询优化

如果只需要结果集中指定的行数，则在查询中使用LIMIT子句，而不是获取整个结果集并扔掉额外的数据。

MySQL有时会优化一个有LIMIT row_count子句和没有HAVING子句的查询:

如果使用LIMIT只选择几行，MySQL在通常倾向于执行全表扫描的情况下，会在某些情况下使用索引。
如果将LIMIT row_count和ORDER BY结合，MySQL在找到排序结果的第一个row_count行时就会停止排序，而不是对整个结果进行排序。如果排序是通过使用索引来完成的，那么这是非常快的。如果必须执行filesort，那么将选择不带LIMIT子句的所有匹配查询的行，并在找到第一个row_count之前对它们中的大多数或所有行进行排序。在找到初始行之后，MySQL不会对结果集的任何剩余部分进行排序。

这种行为的一种表现是，带和不带LIMIT的ORDER BY查询可能以不同的顺序返回行，如本节稍后所述。
如果你把LIMIT row_count和DISTINCT结合起来，MySQL一旦发现row_count唯一的行就会停止。
在某些情况下，可以通过按顺序读取索引(或对索引进行排序)来解析GROUP BY，然后计算摘要，直到索引值发生变化。在这种情况下，LIMIT row_count不计算任何不必要的GROUP BY值。
一旦MySQL向客户端发送了所需的行数，它就会中止查询，除非您使用SQL_CALC_FOUND_ROWS。在这种情况下，可以使用SELECT FOUND_ROWS()检索行数。见“信息功能”。
LIMIT 0快速返回一个空集。这对于检查查询的有效性很有用。它还可以用于在使用MySQL API的应用程序中获取结果列的类型，该API使结果集元数据可用。在mysql客户端程序中，你可以使用--column-type-info选项来显示结果列类型。
如果服务器使用临时表来解析查询，它将使用LIMIT row_count子句来计算需要多少空间。
如果没有为ORDER BY使用索引，但也有LIMIT子句，那么优化器可能能够避免使用合并文件，并使用内存中的filesort操作对内存中的行进行排序。

如果多个行在ORDER BY列中有相同的值，服务器可以*地以任何顺序返回这些行，根据总体执行计划可能会以不同的方式执行。换句话说，这些行的排序顺序对于非有序列是不确定的。

影响执行计划的一个因素是LIMIT，因此带和不带LIMIT的ORDER BY查询可能以不同的顺序返回行。考虑这个查询，它是按category排序的，但对于id和rating列不确定:

mysql> SELECT * FROM ratings ORDER BY category;
+----+----------+--------+
| id | category | rating |
+----+----------+--------+
|  1 |        1 |    4.5 |
|  5 |        1 |    3.2 |
|  3 |        2 |    3.7 |
|  4 |        2 |    3.5 |
|  6 |        2 |    3.5 |
|  2 |        3 |    5.0 |
|  7 |        3 |    2.7 |
+----+----------+--------+

包含LIMIT可能会影响每个category值中的行顺序。例如，这是一个有效的查询结果:

mysql> SELECT * FROM ratings ORDER BY category LIMIT 5;
+----+----------+--------+
| id | category | rating |
+----+----------+--------+
|  1 |        1 |    4.5 |
|  5 |        1 |    3.2 |
|  4 |        2 |    3.5 |
|  3 |        2 |    3.7 |
|  6 |        2 |    3.5 |
+----+----------+--------+

在每种情况下，行按ORDER by列排序，这是SQL标准所需的全部内容。

如果确保使用和不使用LIMIT时相同的行顺序很重要，那么在order BY子句中包含额外的列以使顺序具有确定性。例如，如果id值是唯一的，你可以让一个给定category值的行按id顺序排列，如下所示:

mysql> SELECT * FROM ratings ORDER BY category, id;
+----+----------+--------+
| id | category | rating |
+----+----------+--------+
|  1 |        1 |    4.5 |
|  5 |        1 |    3.2 |
|  3 |        2 |    3.7 |
|  4 |        2 |    3.5 |
|  6 |        2 |    3.5 |
|  2 |        3 |    5.0 |
|  7 |        3 |    2.7 |
+----+----------+--------+

mysql> SELECT * FROM ratings ORDER BY category, id LIMIT 5;
+----+----------+--------+
| id | category | rating |
+----+----------+--------+
|  1 |        1 |    4.5 |
|  5 |        1 |    3.2 |
|  3 |        2 |    3.7 |
|  4 |        2 |    3.5 |
|  6 |        2 |    3.5 |
+----+----------+--------+

对于带有ORDER BY或GROUP BY和LIMIT子句的查询，优化器会在默认情况下尝试选择有序索引，这样做将加快查询执行。在MySQL 5.7.33之前，没有办法覆盖这种行为，即使在使用一些其他优化可能更快的情况下。从MySQL 5.7.33开始，可以通过将optimizer_switch系统变量的prefer_ordering_index标记设置为off来关闭这种优化。

首先，我们创建并填充一个表t，如下所示:

# Create and populate a table t:

mysql> CREATE TABLE t (
    ->     id1 BIGINT NOT NULL,
    ->     id2 BIGINT NOT NULL,
    ->     c1 VARCHAR(50) NOT NULL,
    ->     c2 VARCHAR(50) NOT NULL,
    ->  PRIMARY KEY (id1),
    ->  INDEX i (id2, c1)
    -> );

# [Insert some rows into table t - not shown]

验证prefer_ordering_index标志是否启用:

mysql> SELECT @@optimizer_switch LIKE '%prefer_ordering_index=on%';
+------------------------------------------------------+
| @@optimizer_switch LIKE '%prefer_ordering_index=on%' |
+------------------------------------------------------+
|                                                    1 |
+------------------------------------------------------+

由于下面的查询有一个LIMIT子句，如果可能的话，我们希望它使用有序索引。在本例中，正如我们从EXPLAIN输出中看到的，它使用了表的主键。

mysql> EXPLAIN SELECT c2 FROM t
    ->     WHERE id2 > 3
    ->     ORDER BY id1 ASC LIMIT 2\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t
   partitions: NULL
         type: index
possible_keys: i
          key: PRIMARY
      key_len: 8
          ref: NULL
         rows: 2
     filtered: 70.00
        Extra: Using where

现在我们禁用prefer_ordering_index标志，并重新运行相同的查询;这一次它使用索引i(其中包括WHERE子句中使用的id2列)和一个filesort:

mysql> SET optimizer_switch = "prefer_ordering_index=off";

mysql> EXPLAIN SELECT c2 FROM t
    ->     WHERE id2 > 3
    ->     ORDER BY id1 ASC LIMIT 2\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t
   partitions: NULL
         type: range
possible_keys: i
          key: i
      key_len: 8
          ref: NULL
         rows: 14
     filtered: 100.00
        Extra: Using index condition; Using filesort

请参考“可切换优化”。

码农公寓