首页 › 时空过客vicyuppie › mysql select语句常用 (mysql语句优化原则)

mysql select语句常用 (mysql语句优化原则)

🏷️ 时空过客vicyuppie ✍️ 我不说，等我想好了再说吧。 📅 2026-03-17T01:24:22+00:00

优化SELECT语句是优化工作的重点，因为它是数据库输出数据的基本操作。SELECT语句的调优技术也适用于CREATE TABLE...AS SELECT, INSERT INTO...SELECT, DELETE语句中的SELECT和WHERE子句。

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

  • 为了加快慢查询，首先检查是是否可以对WHERE子句中使用的列建立索引。索引对于不同表的联合查询尤其重要，可以使用EXPLAIN语句来确定索引的使用情况。
  • 不应将查询的某一部分独立于主体去做调优，比如函数调用，这会导致浪费更多时间。应该基于查询语句的结构去判断，该函数是被调用一次得到了结果集中的所有行，还是低效地查询每行时都被调用一次。
  • 在查询中尽量减少全表扫描的次数，尤其是对于大表。
  • 通过定期使用ANALYZE TABLE语句来更新最新的表统计信息，以便于优化器构建高效执行计划。
  • 了解特定于每个表的存储引擎的优化技术、索引技术和配置参数。InnoDB和MyISAM都有一套在查询中实现和保持高性能的指导原则。
  • 如果有些性能问题通过以上基本准则依然无法解决，可以通过查阅EXPLAIN计划来调整索引、WHERE从句、join从句等。
  • 调整MySQL用于缓存的内存大小和属性。通过有效利用InnoDB缓冲池、MyISAM键缓存和MySQL查询缓存，重复查询的运行速度会更快，因为结果是从内存中检索的。
  • 即使使用高速缓存后使查询更快，但仍然可以考虑是否能通过进一步优化查询从而更高效的使用高速缓存，即可以在更小的缓存空间内完成相同的查询，这样应用程序便更具可伸缩性。可伸缩性即在性能不大幅下降的情况下，应用程序可以处理更多的并发用户、更大的请求等等。
  • 处理锁定问题。在竞争资源时，查询某一资源时，其他会话可能也在同时访问该资源，此时的查询速度会受到影响。

WHERE 子句优化

本部分的优化策略也适用于DELETE和UPDATE语句中的WHERE子句。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;
  • 执行早期预检测无效的常量表达式。MySQL会快速检测到那些不合乎逻辑并且不返回任何行记录的SELECT语句。
  • 如果没使用GROUP BY或聚合函数（COUNT()、MIN()等），则HAVING 会与 WHERE 合并。
  • 对于连接中的每个表， mysql会构造一个尽可能简单的WHERE条件来获得该表的快速WHERE评估，并尽可能跳过一些无关行记录。
  • 在查询中常量表先于任何其他表被首先读取。常量表是以下任何一种：
  • 空表或只有一行记录的表。
  • WHERE中使用了PRIMARY KEY或者UNIQUE索引的表，其中所有索引成员被定义为NOT NULL并且都与常量表达式进行了比较。
  • 以下所有表都用作常量表：
  • SELECT * FROM t WHERE primary_key=1; SELECT * FROM t1,t2 WHERE t1.primary_key=1 AND t2.primary_key=t1.id;
  • 尽可能找到表的最佳连接组合。如果ORDER BY和GROUP BY子句中的所有列都来自同一个表，则在连接时首选该表。
  • 如果ORDER BY子句和GROUP BY子句不相同（列表不同），或者如果ORDER BY或者GROUP BY 的列表中的列来自多张表，而不是连接队列中第一个表，则会创建一个临时表。
  • 如果使用了SQL_SMALL_RESULT修饰符，则MySQL会使用in-memory临时表。
  • 查询每个表索引并使用最佳索引，除非优化器认为使用表扫描更有效。老版本的mysql曾经根据最佳索引是否跨越超过30%的表来决定是否使用表扫描，但新版本的mysql中固定百分比不再是使用索引或表扫描的决定因素，现在的优化器更加复杂，它的估计基于多重因素，例如表大小、行数和 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, ... ;

范围优化

范围访问方法使用单个索引来检索包含在一个或多个索引值区间内的表行子集。它可用于单部分或多部分索引。

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

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

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

  • 对于BTREE索引和HASH索引，关键部分与常量值的比较时，范围条件中使用了=, <=>, IN(), IS NULL 或 IS NOT NULL运算符。
  • 此外，对于BTREE索引，关键部分与常量值的比较时，范围条件中使用了>, <, >=, <=, BETWEEN...AND, !=或<> 运算符或者LIKE比较（不以通配符开头的常量字符串）。
  • 对于所有索引类型，使用OR或AND将多个范围条件结合形成一个范围条件。

上述描述中的 “常量值”是指以下之一：

  • 来自查询字符串的常量
  • 来自同一连接的const或system表的列（explain中的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的提取过程如下：

  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. 删除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. 折叠始终为真或假的条件：(key1 LIKE 'abcde%' OR TRUE)永远是真的(key1 < 'uux' AND key1 > 'z')总是假的
  2. 再次替换这些条件后如下：
  3. (key1 < 'abc' AND TRUE) OR (key1 < 'bar' AND TRUE) OR (FALSE)
  4. 删除不必要TRUE和FALSE常量：
  5. (key1 < 'abc') OR (key1 < 'bar')
  6. 处理完重叠的范围区域后，最后产生一个用于范围扫描的最终条件：
  7. (key1 < 'bar')

通常（如前面的示例所示），用于范围扫描的条件没有WHERE子句那么严格。MySQL 执行额外的检查以过滤掉满足范围条件但不满足完整WHERE子句的行。

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

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

多部分索引的范围条件是单部分索引的范围条件的扩展。多部分索引上的范围条件将索引行限制在一个或多个键元组区间内。键元组区间是基于键元组集合，使用索引中的排序规则。

例如，定义为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'(其中LIKE 'pattern' 不以通配符%开头）。只要可以确定包含所有匹配条件的行的单个键元组，就可以使用间隔（如果 使用<> 或，!= 则使用两个间隔）。

只要比较运算符是 、 或 ，优化器就会尝试使用其他关键部分来 =确定 <=>区间IS NULL。如果运算符是 >, <, >=, <=, !=, <>, BETWEEN, or LIKE，优化器使用它但不再考虑关键部分。对于以下表达式，优化器使用 =第一次比较。它还使用 >= 来自第二个比较，但不考虑其他关键部分，并且不使用第三个比较进行区间构造：

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 = valNcol_name如果等于多个值中的任何一个， 则每个表达式都为真 。这些比较是相等范围比较（其中“范围”是单个值）。优化器估计读取符合条件的行以进行相等范围比较的成本如下：

如果 上存在唯一索引 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, orORDER BY子句。

对于EXPLAIN FOR CONNECTION，如果跳过索引潜水，输出会发生如下变化：

对于传统输出，rows和 filtered值为 NULL。

对于 JSON 输出， rows_examined_per_scan并 rows_produced_per_join没有出现， skip_index_dive_due_to_forceis true和 cost 计算不准确。

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

在执行跳过索引潜水的查询后， INFORMATION_SCHEMA.OPTIMIZER_TRACE 表中的相应行包含 的 index_dives_for_range_access值 skipped_due_to_force_index。

跳过扫描范围访问方法考虑以下场景：

CREATE TABLE t1 (f1 INT NOT NULL, f2 INT NOT NULL, PRIMARY KEY(f1, f2));INSERT INTO t1 VALUES(1,1), (1,2), (1,3), (1,4), (1,5),(2,1), (2,2), (2,3), (2,4), (2,5);INSERT INTO t1 SELECT f1, f2 + 5 FROM t1;INSERT INTO t1 SELECT f1, f2 + 10 FROM t1;INSERT INTO t1 SELECT f1, f2 + 20 FROM t1;INSERT INTO t1 SELECT f1, f2 + 40 FROM t1;ANALYZE TABLE t1;

EXPLAIN SELECT f1, f2 FROM t1 WHERE f2 > 40;要执行此查询，MySQL 可以选择索引扫描来获取所有行（索引包括要选择的所有列），然后应用子句中 的f2 > 40 条件来生成最终结果集。WHERE

范围扫描比全索引扫描更有效，但不能在这种情况下使用，因为在 f1第一个索引列上没有条件。f1但是，从 MySQL 8.0.13 开始，优化器可以使用类似于 Loose Index Scan 的称为 Skip Scan 的方法执行多个范围扫描，针对 的每个值进行一次（请参阅第 8.2.1.17 节，“GROUP BY 优化”）：

在第一个索引部分 f1（索引前缀）的不同值之间跳过。

f2 > 40对剩余索引部分 的条件的每个不同前缀值执行子范围扫描。

对于前面显示的数据集，算法操作如下：

f1 = 1获取第一个关键部分 ( ) 的第一个不同值。

根据第一个和第二个关键部分 ( f1 = 1 AND f2 > 40) 构建范围。

执行范围扫描。

获取第一个关键部分 ( f1 = 2) 的下一个不同值。

根据第一个和第二个关键部分 ( f1 = 2 AND f2 > 40) 构建范围。

执行范围扫描。

使用此策略会减少访问的行数，因为 MySQL 会跳过不符合每个构造范围的行。此跳过扫描访问方法适用于以下条件：

表 T 至少有一个复合索引，其关键部分为 ([A_1, ..., A_ k,] B_1, ..., B_ m, C [, D_1, ..., D_ n])。关键部分 A 和 D 可以为空，但 B 和 C 必须为非空。

该查询仅引用一张表。

该查询不使用GROUP BYor DISTINCT。

该查询仅引用索引中的列。

A_1, ..., A_ 上的k谓词必须是相等谓词并且它们必须是常量。这包括 IN()操作员。

查询必须是连词查询；也就是一个 AND条件OR ： (cond1(key_part1) OR cond2(key_part1)) AND (cond1(key_part2) OR ...) AND ...

C 上必须有一个范围条件。

D 列的条件是允许的。D 上的条件必须与 C 上的范围条件相结合。

Skip Scan 的使用在EXPLAIN 输出中指示如下：

Using index for skip scan列 Extra中表示使用松散索引Skip Scan访问方法。

如果索引可用于跳过扫描，则该索引应在possible_keys 列中可见。

跳过扫描的使用在优化器跟踪输出中由 "skip scan"以下形式的元素指示：

"skip_scan_range": {"type": "skip_scan","index": index_used_for_skip_scan,"key_parts_used_for_access": [key_parts_used_for_access],"range": [range]}您可能还会看到一个 "best_skip_scan_summary"元素。如果选择跳过扫描作为最佳范围访问变量， "chosen_range_access_summary"则写入 a。如果选择跳过扫描作为整体最佳访问方法， "best_access_path"则存在一个元素。

跳过扫描的使用取决于 系统变量的skip_scan标志 值。optimizer_switch请参阅第 8.9.2 节，“可切换的优化”。默认情况下，此标志为on. 要禁用它，请设置skip_scan为 off。

除了使用 optimizer_switch系统变量来控制优化器在会话范围内使用 Skip Scan 之外，MySQL 还支持优化器提示以在每个语句的基础上影​响优化器。请参阅 第 8.9.3 节，“优化器提示”。

行构造函数表达式的范围优化优化器能够将范围扫描访问方法应用于这种形式的查询：

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()，有不止一个行构造函数。

有关优化器和行构造函数的更多信息，请参阅 第 8.2.1.22 节，“行构造函数表达式优化”

限制用于范围优化的内存使用要控制范围优化器可用的内存，请使用 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. Rangeoptimization was not done for this query.对于UPDATE和 DELETE语句，如果优化器回退到全表扫描并且 sql_safe_updates启用了系统变量，则会发生错误而不是警告，因为实际上没有使用任何键来确定要修改哪些行。有关详细信息，请参阅 使用安全更新模式 (--safe-updates)。

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

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

对于像下面这样的简单查询，其中有一个范围访问方法的候选键，每个谓词组合OR 使用大约 230 个字节：

SELECT COUNT(*) FROM tWHERE a=1 OR a=2 OR a=3 OR .. . a=N;类似地，对于如下查询，每个谓词组合AND 使用大约 125 个字节：

SELECT COUNT(*) FROM tWHERE a=1 AND b=1 AND c=1 ... N;对于带有IN() 谓词的查询：

SELECT COUNT(*) FROM tWHERE a IN (1,2, ..., M) AND b IN (1,2, ..., N);列表中的每个文字值都 IN()算作一个与 . 组合的谓词OR。如果有两个IN() 列表，则结合的谓词 OR数量是每个列表中文字值数量的乘积。OR因此，在前一种情况下 结合的谓词数量 为M× N。