SQL 优化之路：让你的查询秒杀竞争对手

背景

在数据驱动的时代，企业依赖数据库来管理核心业务数据。随着用户需求和数据量的增加，慢 SQL 查询逐渐成为性能瓶颈，影响用户体验和业务效率。这类查询不仅消耗系统资源，还可能导致应用响应延迟，进而影响客户满意度和企业竞争力。

SQL语句分析

接下来我们将从理解SQL查询的工作原理、EXPLAIN命令分析查询性能瓶颈、优化索引和查询语句、监控和定位慢SQL等以下几个方面来进行分析。

理解 SQL 查询的工作原理

SQL 查询的工作原理可以分为四个主要阶段：解析、优化、执行和结果返回：

1. 解析阶段：

• 在这个阶段，数据库引擎会检查 SQL 语句的语法是否正确，并将其转换为内部表示形式。解析器会验证 SQL 语句的结构，确保没有语法错误，并确认所引用的表和列存在。

1. 优化阶段：

• 优化器会分析查询计划，确定执行查询的最有效方式。这一过程包括选择使用哪个索引、是否需要进行全表扫描、以及如何最优地连接多个表。优化器会生成一个或多个执行计划，并选择成本最低的计划。

1. 执行阶段：

• 执行器根据优化后的查询计划执行 SQL 查询。这包括从磁盘读取数据、应用 WHERE 子句中的条件、执JOIN 操作等。执行器会逐步处理每个操作，并根据需要从数据库中检索数据。

1. 结果返回阶段：

• 最后，执行器将查询结果返回给客户端。这一阶段涉及将数据格式化为客户端可以理解的形式，并通过网络将结果返回给请求的用户。

对这部分不了解的可以看看这篇文章： 你真的了解SQL语句的执行过程？

EXPLAIN 命令分析查询性能瓶颈

EXPLAIN 命令是数据库管理员和 SQL 开发人员的重要工具，它帮助理解 MySQL 如何执行特定查询。通过 EXPLAIN 命令，可以查看查询的执行计划，包括：

• 如何连接表以及连接的顺序
• 是否使用了索引
• 每个表的读取行数
• 估计的成本和效率

通过分析这些信息，可以判断查询的性能瓶颈，并据此对查询或表结构进行优化。这有助于识别潜在的问题，如不必要的全表扫描或低效的索引使用。

优化索引和查询语句

优化索引和查询语句是提高 SQL 性能的关键步骤。具体方法包括：

1. 确定索引列：

• 找出需要优化的索引列，例如假设为 `id` 列。确保该列有合适的索引，以加快查询速度。

1. 简化查询：

• 如果查询中使用了不必要的函数或复杂的连接操作，考虑将这些操作转移到应用层处理，以减少数据库的负担。

1. 避免非 SARGable 表达式：

• 在 WHERE 子句中避免使用非 SARGable 的表达式（即不可利用索引的表达式），因为这会导致索引失效，进而降低查询性能。

什么是 SARGable 表达式？

SARGable 是一个术语，源自于“Search ARGument ABLE”的缩写，指的是能够利用索引进行高效搜索的表达式。在 SQL 查询中，SARGable 表达式可以在 WHERE 子句中高效地使用索引，从而加快查询的执行速度。简单来说，SARGable 表达式是指那些在 SQL 查询中可以有效利用索引的条件。以下是 SARGable 的一些特征：

直接比较：

• 使用简单的比较操作符，如 =, >, <, >=, <=, <> 等。
• 例如：WHERE column_name = 'value' 或 WHERE column_name > 10。

使用索引字段：

• 对索引字段进行直接比较。
• 例如：WHERE indexed_column BETWEEN 10 AND 20 是 SARGable 的，因为可以利用索引快速定位符合条件的记录。

逻辑运算：

• 使用逻辑运算符（如 AND、OR）进行组合的条件，但前提是条件中的每个部分都是 SARGable 的。
• 例如：WHERE column1 = 'value1' AND column2 > 10。

什么是非 SARGable 表达式？

与 SARGable 相对的是非 SARGable 表达式。这些表达式无法有效利用索引，导致全表扫描，通常会显著降低查询性能。以下是一些常见的非 SARGable 表达式示例：

函数操作：

WHERE YEAR(date_column) = 2023

计算机表达式：

WHERE indexed_column + 1 = 10

模糊匹配：

WHERE column_name LIKE '%value'

监控和定位慢 SQL

监控和定位慢 SQL 是优化过程中的重要步骤。可以通过配置慢查询日志来记录执行时间超过特定阈值的 SQL 语句。具体步骤包括：

• 设置慢查询日志：通过配置数据库的慢查询日志功能，记录所有执行时间超过设定阈值的 SQL 语句。例如，可以设置 `SLOW_LOG_TIME_THRESHOLD` 参数，定义时间阈值，超过该阈值的 SQL 语句将被记录。

• 分析慢查询日志：定期检查慢查询日志文件，找出执行时间长的 SQL 语句，并进行优化。

优化步骤

通过具体的实践案例展示如何优化慢 SQL 是非常有帮助的。以下是一个优化流程的示例：

1. 使用 EXPLAIN 命令：

• 对一个具体的慢 SQL 语句执行 EXPLAIN 命令，分析其执行计划，识别性能瓶颈。例如，可能发现某个表的全表扫描导致性能下降。

1. 优化查询或索引：

• 根据 EXPLAIN 分析结果，可能需要对查询语句进行优化，如简化 JOIN 操作或避免不必要的排序。
• 同时，检查相关字段的索引，考虑添加缺失的索引或修改现有索引以提高查询性能。

1. 重新运行 EXPLAIN：

• 在进行优化后，再次运行 EXPLAIN 命令以验证优化效果。观察执行计划的变化，确认性能瓶颈是否得到解决。

SQL优化&索引优化

当我们使用EXPLAIN 命令得出该语句是慢SQL后，我们可以从以下几个方面来进行优化。

select * 和 select column

性能

磁盘 I/O：

• SELECT *：需要读取表中的所有列，这可能导致更多的磁盘 I/O 操作，尤其是在表中包含大量列或大字段（如 BLOB 或 TEXT 类型）时。
• SELECT column：仅读取指定的列，减少了磁盘 I/O 操作，提高了查询效率。

详解：

• 这里指的I/O次数变多并不是查询时查询了某个字段导致的，而是本身的数据量和索引问题。从2个方面进行分析:

• • 如果查询未使用覆盖索引，数据库将必须先通过索引找到记录的地址，然后再访问表（即回表）以检索完整的行数据。这一过程会引入额外的 I/O 操作，因为每次回表都需要读取数据页，增加了访问时间和资源消耗。
  • 当表中的数据量非常庞大时，查询可能需要跨多个数据页进行检索。每个数据页的读取都需要 I/O 操作，因此数据量的增加会直接导致 I/O 次数的增加。

内存使用：

• SELECT *：使用 SELECT * 查询时，数据库会获取表中的所有列，这意味着在内存中需要处理和存储更多的数据。尤其是在表有很多列或列的数据类型较大（如文本、BLOB 等）时，内存使用量会显著增加。
• SELECT column：由于需要处理更多的数据，SELECT * 可能导致更高的 CPU 使用率和更长的查询响应时间，特别是在网络带宽有限的情况下，传输大量数据的成本也会增加。

网络带宽：

• SELECT *：当仅选择特定的列（如 SELECT column1, column2）时，数据库系统只需处理和存储被请求的列，从而减少内存的使用。这种方式不仅减少了内存占用，也提高了查询的效率。
• SELECT column：通过减少需要传输和处理的数据量，SELECT column 可以显著提高查询性能，降低响应时间，并减轻数据库服务器和客户端的负担。

查询优化

索引使用：

• SELECT *：如果查询中使用了索引，但索引不是覆盖索引（即索引中没有包含查询所需的所有列），则需要进行索引回表操作。这可能会导致性能下降。
• SELECT column：如果查询所需列都在索引中，则可以使用覆盖索引，提高查询性能。

查询计划：

• SELECT *：当使用 SELECT * 时，数据库优化器需要考虑表中的所有列。这可能导致生成更复杂的查询计划，因为优化器必须评估每一列的相关性、数据类型和索引情况，以确定如何最有效地检索数据。
• SELECT column：当查询中仅包含特定列（例如 SELECT column1, column2）时，优化器可以更轻松地确定最优查询路径。优化器能更快地评估所选列与索引的关系，从而生成更高效的执行计划。

函数操作

对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。

条件字段函数操作

使用常量

--- 走索引
SELECT * FROM events WHERE DATE(timestamp) = '2023-01-01';

详解：

• 当查询中使用常量时，优化器在编译阶段能够识别该常量的值并考虑使用索引，但如果这个值不符合索引的顺序，优化器可能仍然选择不使用索引。

使用参数

SELECT * FROM events WHERE DATE(timestamp) = '2023-01-01';

详解：

• 在参数化查询中，如WHERE DATE(date_column) = :date_param，由于优化器在编译阶段不知道参数的具体值，因此可能无法确定是否能有效使用索引来过滤结果。此时，优化器可能会选择一种更通用的查询计划，即不使用索引。

查询字段函数操作

-- 会使用索引
SELECT UPPER(name), age FROM users;

-- 会使用索引
SELECT name, age FROM users ORDER BY UPPER(name);

详解：

• 当函数仅在SELECT或ORDER BY子句中应用时，数据库会先检索数据，然后在检索出的数据上应用函数。因此，这类函数的应用不会影响索引的有效性，因为索引主要用于定位和排序数据，而不是改变数据的值。（参考SQL语句的执行顺序）。

隐式类型转换

• 当 tradeid 的字段类型是 varchar(32)，而输入的参数却是整型，所以需要做类型转换。在MySQL中字符串和数字做比较的话，是将字符串转换成数字。因此tradid字段使用到了函数，所以索引失效了。

select * from tradelog where tradeid=110717;

<!-- 转换后 -->
select * from tradelog where CAST(tradid AS signed int) = 110717;

隐式字符编码转换

• 当字段编码和参数编码不同时，MySQL会使用CONVERT()函数进行编码转换。

select * from trade_detail where tradeid=$L2.tradeid.value;

<!-- 转换后 -->
select * from trade_detail where CONVERT(traideid USING utf8mb4)=$L2.tradeid.value;

"字符串" 转换"整数"会索引失效？

不会，整数转换字符串时，MySQL会选择将"参数"进行转换且只转换一次，转换后的类型与索引列的类型一致，所以能够走索引。

SELECT * FROM trades WHERE trade_id = CAST('123' AS SIGNED); -- 字符串 123

MySQL隐式转换是如何选择"列"或"参数"的？

这里需要先谈一下字段的优先级。数据类型优先级的概念可以帮助我们理解MySQL在处理不同数据类型之间的隐式转换时的行为。虽然MySQL官方文档中并没有明确列出“数据类型优先级”的概念，但在实际操作中，MySQL确实会对不同类型的数据进行隐式转换以确保比较的一致性。

数据类型优先级和隐式类型转换

MySQL在处理查询时会尝试隐式地转换不同类型的数据以使其一致，以便进行比较。数据类型的优先级决定了哪种类型会被转换为另一种类型。以下是MySQL中的数据类型优先级从高到低的排序：

• 定点数和浮点数 (DECIMAL, FLOAT, DOUBLE)。
• 整数 (BIGINT, INT, SMALLINT, TINYINT, MEDIUMINT)。
• 日期和时间 (DATE, TIME, DATETIME, TIMESTAMP)。
• 字符串 (CHAR, VARCHAR, BLOB, TEXT)。

选择在 "列" 上进行转换

trade_id为 "字符串" 类型而参数是 "整数" 时，由于 "整数" 类型优先级高于 "字符串" 类型，所以在 "整数" 转换 "字符串"时，会选择将"列"值逐一进行转换，从而保持类的一致性。因此也会导致索引失效。

SELECT * FROM trades WHERE CAST(trade_id AS SIGNED) = 123;  -- 整数 123

选择在 "参数" 上进行转换

trade_id为 "整数" 类型而参数是 "字符串" 时，由于 "整数" 类型优先级高于 "字符串" 类型，所以在 "字符串" 转换 "整数"时，会选择将"参数"值进行转换，从而保持类的一致性。由于参数只转换一次，且转换后的值不会影响到索引的有序性，所以能够走索引。

SELECT * FROM trades WHERE trade_id = CAST('123' AS SIGNED); -- 字符串 123

函数是如何破坏索引有序性的？

当在查询中对索引字段应用函数时，函数改变了字段的原始值，从而破坏了索引的有序性。下面以类型转换的场景举例说明。

示例说明

假设tradeid字段是varchar(32)类型，并且我们有一个索引idx_tradeid是按照tradeid字段的值进行排序的。

表中的数据如下：

tradeid	other_column
'123'	data1
'234'	data2
'345'	data3
'456'	data4

索引idx_tradeid的顺序如下：

1. '123'
2. '234'
3. '345'
4. '456'

现在假设我们执行以下查询：

SELECT * FROM trades WHERE CAST(tradeid AS SIGNED) = 123;

这里，CAST(tradeid AS SIGNED)将tradeid字段的值从字符串转换为整数。尽管123是一个整数，但索引条目是按照字符串'123'进行排序的。因此，索引的有序性不再适用于查询条件，所以索引会失效。

union 和 union all

• 使用union时，数据库需要对结果集进行去重处理。这意味着数据库需要维护一个临时表来跟踪已出现过的行，并检查每一条新行是否已经存在于结果集中。这个过程消耗了大量的CPU资源和内存资源。
• 使用union all，数据库不会进行去重处理。每个查询的结果集直接合并到最终结果集中，没有额外的去重逻辑。因此，查询的速度通常会更快。

where 条件索引失效

IS NULL 和 IS NOT NULL

是否会索引失效取决于字段的值，如果字段中NULL值较多，使用IS NOT NULL查询时索引可能更有效；反之，如果NULL值较少，使用IS NULL查询时索引可能更有效。不过，这种分析主要适用于普通索引。

!= 、<> 操作符

不一定失效，具体是否索引失效取决于索引类型。

主键索引或唯一索引

• 由于这些索引中的值是唯一的，查询优化器仍会选择使用索引处理 "不等于" 条件，从而减少需要检查的记录数量。（这里通过explain查看会查询rows是总行数的一半，主要是因为 "简要估算" ）

示例说明

假设我们有一个表orders，总数据量为10万，其中包含字段order_id作为主键，并且order_id字段有主键索引：

SELECT * FROM orders WHERE order_id != 1;

执行流程

查找特定值的位置：

• 优化器使用主键索引找到order_id = 1的位置。由于主键索引是有序的，优化器可以通过二分查找快速定位到这个值。
• 如果order_id是从最小值开始排列的，那么order_id = 1将是第一个值。

排除特定值：

• 优化器跳过所有等于1的记录。由于主键索引是唯一的，这意味着只会跳过一个记录。
• 在10万条记录中，排除order_id = 1后，理论上还剩下99999条记录。

返回其余记录：

• 优化器返回除了order_id = 1之外的所有记录。
• 由于主键索引是有序的，优化器可以从特定值之前和之后的部分读取数据。

优化器估算

• 优化器通常假设数据是均匀分布的，并且排除一个特定值后，剩余的记录数大致为总记录数的一半。
• 在10万条记录的情况下，优化器估计为5万。

实际返回的行数

• 实际上，排除一个特定值后的记录数应该是100000 - 1 = 99999条。
• 优化器简化估算为5万，但实际返回的行数可能是99999条。

普通索引

• 如果字段包含许多相同的值，优化器可能选择全表扫描，因为检查所有记录的成本可能低于使用索引的成本。

MySQL 简要估算

查询优化器基于表的统计信息，并简化估算来预测返回的行数。

等值查询：

• 对于等值查询（如WHERE column = value），优化器通常根据索引键的唯一值数量来估算返回的行数。如果索引键是唯一的，那么返回的行数就是1。

范围查询：

• 对于范围查询（如WHERE column BETWEEN value1 AND value2），优化器通常假设数据是均匀分布的，并且会估算返回的行数。例如，如果表中有10万条记录，column是从1递增到10万，那么BETWEEN 100 AND 200的范围大约包含100条记录。

"不等于" 查询：

• 对于“不等于”查询（如WHERE column != value），优化器通常假设排除一个特定值后，剩余的记录数大致为总记录数的一半。这是因为排除一个特定值的影响相对较小，特别是在数据量较大的情况下。

IN、 NOT IN 操作符

• 单值情况：如果IN操作符后面只有一个值，那么索引可以正常工作。
• 多值情况：如果IN操作符后面有多个值，优化器可能会根据值的数量和分布情况决定是否使用索引。

示例说明

优化器并不知道IN列表中的值是否是有序的，因此对于每个值，优化器会分别在索引中查找匹配的记录。即使这些值分布在不同的页中，优化器也会逐一进行查找。

SELECT * FROM user WHERE k IN (1, 2, 3, 4, 5);

执行流程

查找k = 1：

• 优化器首先在根节点（root node）查找1。
• 根据键值1，优化器会向下移动到相应的子节点（child node）。
• 在子节点中，优化器会继续查找1，直到找到包含1的页（leaf node）。
• 假设找到记录{id: 1, k: 1}。

查找k = 2：

• 优化器再次在根节点查找2。
• 根据键值2，优化器会向下移动到相应的子节点。
• 在子节点中，优化器会继续查找2，直到找到包含2的页。
• 假设找到记录{id: 2, k: 2}。

查找k = 3：

• 优化器在根节点查找3。
• 根据键值3，优化器会向下移动到相应的子节点。
• 在子节点中，优化器会继续查找3，直到找到包含3的页。
• 假设找到记录{id: 3, k: 3}。

查找k = 4：

• 优化器在根节点查找4。
• 根据键值4，优化器会向下移动到相应的子节点。
• 在子节点中，优化器会继续查找4，直到找到包含4的页。
• 假设找到记录{id: 4, k: 4}。

查找k = 5：

• 优化器在根节点查找5。
• 根据键值5，优化器会向下移动到相应的子节点。
• 在子节点中，优化器会继续查找5，直到找到包含5的页。
• 假设找到记录{id: 5, k: 5}。

Like 查询

• 前缀为%：LIKE '%pattern'会导致索引失效，因为需要匹配所有可能的字符串前缀，索引无法有效定位记录，优化器会选择全表扫描。
• 后缀为%：LIKE 'pattern%'可以利用索引，因为模式的开头是固定的，索引可以通过固定的前缀快速定位到可能匹配的记录范围，从而缩小搜索范围。

OR 操作符

使用OR操作符是否会引发全表扫描，取决于索引的使用情况和数据量的大小。

前后都有索引

假设我们有两个字段order_id和customer_id，并且这两个字段都有索引。

• 如果customer_id = 1001的值在表中出现次数较少，并且customer_id有索引，那么查询优化器很可能会使用该索引来定位记录，避免全表扫描。
• 如果customer_id > 1001的条件涉及大量数据，优化器可能会认为全表扫描更高效，从而选择全表扫描。

//会走索引
SELECT * FROM orders WHERE order_id = 123 OR customer_id = 1001;
//可能会索引失效
SELECT * FROM orders WHERE order_id = 123 OR customer_id > 1001;

单边索引

• 当只有order_id字段有索引而customer_id字段没有索引时，会导致全表扫描，因为为了找出所有customer_id = 1001的记录，数据库必须检查每一行数据。

SELECT * FROM orders WHERE order_id = 123 OR customer_id = 1001;

小表驱动大表

使用“小表驱动大表”的策略可以有效地减少不必要的全表扫描，从而提高查询效率。

EXISTS 查询

适合外表较小，内表较大的情况。

1. 对于每个orders表中的行，检查order_items表中是否存在匹配项。
2. 如果存在匹配项，则将orders表中的行放入结果集中。
3. 由于orders表较小，order_items表较大，使用EXISTS可以提高效率。

SELECT * FROM orders
WHERE EXISTS (
    SELECT 1 FROM order_items
    WHERE order_items.order_id = orders.order_id
);

IN 查询

适合内表较小，外表较大的情况。

1. 查询order_items表中的所有order_id。
2. 遍历orders表中的每一行。
3. 检查orders表中的order_id是否在order_items表中存在。
4. 匹配成功则将orders表中的行放入结果集中。

SELECT * FROM orders
WHERE order_id IN (
    SELECT order_id FROM order_items
);

JOIN 查询

优化器会根据统计信息确定哪个表较小，哪个表较大。较小的表通常会被选作驱动表。

1. 进行联接操作时，如果orders表较小，应该先处理orders表。
2. 用orders表中的数据去匹配order_items表中的数据。

SELECT o.* FROM orders o JOIN order_items oi ON o.order_id = oi.order_id;

LEFT JOIN 或RIGHT JOIN

而在使用LEFT JOIN或RIGHT JOIN时，优化器通常会按照你指定的顺序执行，而不会根据表的大小来重新排列。

因此在编写查询时，如果可能，应将较小的表放在前面作为驱动表。

SELECT o.*, oi.*
FROM orders o
LEFT JOIN order_items oi ON o.order_id = oi.order_id;

连接查询代替子查询

数据量过大时，子查询会索引失效，并全表扫描，使用连接查询代替子查询可以更好地利用索引，减少不必要的全表扫描，从而提高查询效率。

---子查询
SELECT * FROM orders
WHERE order_id IN (SELECT order_id FROM order_items);

---连接查询
SELECT o.*
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id;

分页优化

通过使用起始ID代替OFFSET，减少了不必要的数据扫描，避免了跳过大量记录的过程，从而显著提高了查询效率。

----优化前
select id,name,age 
from user limit 1000000,20;

----优化后
select id,name,age 
from user where id > 1000000 limit 20;

跳过记录

使用LIMIT和OFFSET会导致数据库需要进行大量的I/O操作（读取和缓存数据）、CPU计算（排序和跳过记录）以及内存使用（缓存大量数据），从而导致性能下降。

1. 数据库需要从头开始逐条记录跳过前OFFSET数量的记录：

1. 1. 例如，跳过前1000000条记录。

1. 每跳过一条记录，数据库都需要检查是否已经达到了OFFSET的数量：

1. 1. 这意味着数据库需要逐条记录地进行计数操作。

1. 这意味着数据库需要访问并跳过大量的记录，即使这些记录最终不会出现在结果集中：

1. 1. 大量的记录被跳过，但这些记录不会出现在最终的结果集中。

1. 在跳过OFFSET数量的记录后，数据库开始获取接下来的目标记录：

1. 1. 例如，在跳过1000000条记录后，开始获取接下来的20条记录。

1. 这20条记录才是最终结果集的一部分：

1. 1. 这些记录是用户实际需要的数据。

分表分库

分表分库可以看下这两篇

分表策略，你真的分对了？-CSDN博客

MySQL 分表真的能提高查询效率？_mysql分表查询-CSDN博客