MySQL优化系列17-MySQL SQL语句优化

末蓝、 2022-08-30 15:48 319阅读 0赞

备注:测试数据库版本为MySQL 8.0

### 文章目录 ###

*  一.优化select语句
 *   *  1.1 where子句优化
     *  1.2 IS NULL 优化
     *  1.3 ORDER BY优化
     *  1.4 group by 优化
     *   *  1.4.1 宽松的索引扫描
         *  1.4.2 紧索引扫描
     *  1.5 distinct优化
     *  1.6 Limit优化
     *  1.7 避免全表扫描
 *  二.优化DML语句
 *   *  2.1 优化insert语句
     *  2.2 优化update语句
     *  2.3 优化delete语句
 *  参考:

# 一.优化select语句 #

## 1.1 where子句优化 ##

1.  剔除重复的条件

((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)

1.  常数合并

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

1.  常数条件删除

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

## 1.2 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。

## 1.3 ORDER BY优化 ##

1.  使用索引来满足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;

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

为了获得用于filesort操作的内存，从MySQL 8.0.12开始，优化器根据需要递增分配内存缓冲区，直到sort\_buffer\_size系统变量指定的大小，而不是像MySQL 8.0.12之前那样预先分配固定数量的sort\_buffer\_size字节。这允许用户将sort\_buffer\_size设置为更大的值，以加快更大的排序，而不必担心小排序使用过多内存。(对于Windows上的多个并发排序来说，这个好处可能不会出现，因为Windows具有弱多线程malloc。)

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

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

1.  影响order by优化  
    对于没有使用filesort的低速ORDER BY查询，请尝试将max\_length\_for\_sort\_data系统变量降低到适合触发filesort的值。(将该变量的值设置得过高的症状是磁盘活动过多而CPU活动过少。)这种技术只适用于MySQL 8.0.20之前。从8.0.20开始，max\_length\_for\_sort\_data已被弃用，因为优化器的更改使其过时且无效。

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

增加sort\_buffer\_size变量值。理想情况下，该值应该足够大，以使整个结果集适合排序缓冲区(以避免写入磁盘和合并传递)。

要考虑到排序缓冲区中存储的列值的大小受max\_sort\_length系统变量值的影响。例如，如果元组存储长字符串列的值，你增加max\_sort\_length的值，排序缓冲区元组的大小也会增加，可能需要你增加sort\_buffer\_size。

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

增加read\_rnd\_buffer\_size变量值，以便一次读取更多行。

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

1.  ORDER BY 可用的执行计划信息

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

Peak\_memory\_used表示排序过程中任意时刻使用的最大内存。这个值相当于系统变量sort\_buffer\_size的值，但不一定比它大。在MySQL 8.0.12之前，输出显示的是sort\_buffer\_size，表示sort\_buffer\_size的值。(在MySQL 8.0.12之前，优化器总是为排序缓冲区分配sort\_buffer\_size字节。从8.0.12开始，优化器增量地分配排序缓冲区内存，从少量开始，必要时增加更多，直到sort\_buffer\_size字节。)

## 1.4 group by 优化 ##

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

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

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

### 1.4.1 宽松的索引扫描 ###

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

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

### 1.4.2 紧索引扫描 ###

紧索引扫描可以是完整索引扫描，也可以是范围索引扫描，这取决于查询条件。

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

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

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

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

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

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

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

## 1.5 distinct优化 ##

在许多情况下，DISTINCT结合ORDER BY需要一个临时表。

因为DISTINCT可能使用GROUP BY，所以了解MySQL如何使用ORDER BY或HAVING子句来处理不属于所选列的列。

在大多数情况下，一个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查询的优化可能性的更多细节。

当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;

## 1.6 Limit优化 ##

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

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

1.  如果使用LIMIT只选择几行，MySQL在通常倾向于执行全表扫描的情况下，会在某些情况下使用索引。
2.  如果将LIMIT row\_count和ORDER BY结合，MySQL在找到排序结果的第一个row\_count行时就会停止排序，而不是对整个结果进行排序。如果排序是通过使用索引来完成的，那么这是非常快的。如果必须执行文件排序，那么将选择不带LIMIT子句的所有匹配查询的行，并在找到第一个row\_count之前对它们中的大多数或所有行进行排序。在找到初始行之后，MySQL不会对结果集的任何剩余部分进行排序。  
    这种行为的一种表现是，带和不带LIMIT的ORDER BY查询可能以不同的顺序返回行。
3.  如果你把LIMIT row\_count和DISTINCT结合起来，MySQL一旦发现row\_count唯一的行就会停止。
4.  在某些情况下，可以通过按顺序读取索引(或对索引进行排序)来解析GROUP BY，然后计算摘要，直到索引值发生变化。在这种情况下，LIMIT row\_count不计算任何不必要的GROUP BY值。
5.  一旦MySQL向客户端发送了所需的行数，它就会中止查询，除非您使用SQL\_CALC\_FOUND\_ROWS。在这种情况下，可以使用SELECT FOUND\_ROWS()检索行数。
6.  LIMIT 0快速返回一个空集。这对于检查查询的有效性很有用。它还可以用于在使用MySQL API的应用程序中获取结果列的类型，该API使结果集元数据可用。在mysql客户端程序中，你可以使用——column-type-info选项来显示结果列类型。
7.  如果服务器使用临时表来解析查询，它将使用LIMIT row\_count子句来计算需要多少空间。
8.  如果没有为ORDER BY使用索引，但也有LIMIT子句，那么优化器可能能够避免使用合并文件，并使用内存中的文件ort操作对内存中的行进行排序。

## 1.7 避免全表扫描 ##

当MySQL使用全表扫描来解析查询时，EXPLAIN的输出在类型列中显示ALL。这种情况通常发生在以下情况下:

1.  表很小，执行表扫描比查找键要快得多。对于少于10行且行长度较短的表，这很常见。
2.  索引列的ON或WHERE子句中没有可用的限制。
3.  您正在将索引列与常量值进行比较，而MySQL已经计算出(基于索引树)常量覆盖了表的很大一部分，并且表扫描会更快。
4.  您正在通过另一列使用具有低基数的键(许多行匹配键值)。在这种情况下，MySQL假设通过使用键可能需要很多键查找，并且表扫描会更快。

对于小型表，表扫描通常是合适的，性能影响可以忽略不计。对于大型表，尝试以下技术以避免优化器错误地选择表扫描:

1.  使用ANALYZE TABLE tbl\_name更新被扫描表的键分布。
2.  对扫描的表使用FORCE INDEX，告诉MySQL表扫描比使用给定的索引昂贵:

SELECT * FROM t1, t2 FORCE INDEX (index_for_column)
      WHERE t1.col_name=t2.col_name;

使用–max-seek -for-key=1000选项启动mysqld，或者使用SET max\_seeks\_for\_key=1000来告诉优化器假设没有键扫描导致超过1000个键寻找。

# 二.优化DML语句 #

## 2.1 优化insert语句 ##

为了优化插入速度，将许多小操作组合成一个大操作。理想情况下，创建一个连接，一次发送多个新行数据，并将所有索引更新和一致性检查延迟到最后。

插入一行所需的时间由下列因素决定，其中数字表示近似比例:

1.  连接:(3)
2.  向服务器发送查询:(2)
3.  解析查询:(2)
4.  插入行:(1 ×行大小)
5.  插入索引:(1 ×索引数)
6.  关闭:(1)

这并不考虑打开表的初始开销，对于每个并发运行的查询，初始开销都要执行一次。

假设索引是B-tree，表的大小会使索引的插入速度慢log N。

您可以使用以下方法加快插入速度:

1.  如果您在同一时间插入来自同一客户端的多行，那么使用INSERT语句和多个VALUES列表来一次插入多行。这比使用单独的单行INSERT语句要快得多(在某些情况下快很多倍)。如果要向非空表添加数据，可以调优bulk\_insert\_buffer\_size变量，使数据插入更快。
2.  当从文本文件加载表时，使用LOAD DATA。这通常比使用INSERT语句快20倍。
3.  利用列具有默认值这一事实。只有当要插入的值与默认值不同时才显式插入值。这减少了MySQL必须做的解析，并提高了插入速度。

## 2.2 优化update语句 ##

更新语句与SELECT查询一样进行了优化，但增加了写入的开销。写的速度取决于要更新的数据量和要更新的索引数量。未更改的索引不会更新。

获得快速更新的另一种方法是延迟更新，然后在稍后连续进行许多更新。同时执行多个更新比一次只执行一个更新要快得多。

对于使用动态行格式的MyISAM表，将一行更新为更长的总长度可能会拆分该行。如果您经常这样做，那么偶尔使用OPTIMIZE TABLE是非常重要的。

## 2.3 优化delete语句 ##

在MyISAM表中删除单个行所需的时间与索引的数量刚好成正比。要更快地删除行，可以通过增加key\_buffer\_size系统变量来增加键缓存的大小。

要删除MyISAM表中的所有行，TRUNCATE table tbl\_name比delete from tbl\_name更快。截断操作不是事务安全的;在活动事务或活动表锁的过程中尝试使用一个锁时，会发生错误。

# 参考: #

1.  https://dev.mysql.com/doc/refman/8.0/en/statement-optimization.html