MySQL优化

本文最后更新于：3 年前

引言

随着业务复杂度和数据量的不断上升，数据库性能往往成为系统瓶颈之一。如何让常见查询在海量数据下依然保持高效？MySQL 提供了多种机制与工具帮助我们识别和调优低效的 SQL，包括慢查询日志、SHOW PROFILE、Performance Schema 以及 EXPLAIN 等。结合良好的索引设计和 SQL 写法，才能可以显著改善查询响应时间，合理利用硬件资源。本文将围绕 MySQL 中常见的性能分析方法与查询优化技巧展开，从日志配置到语法策略，从索引失效到查询执行计划，为数据库优化提供一份系统性的指导。

慢查询日志

作用

  • 记录慢查询：MySQL可以通过慢查询日志记录执行时间超过设定阈值的 SQL 语句，帮助定位性能瓶颈。
  • 诊断工具：慢查询日志提供的详细信息（如执行时间、扫描行数等）可以发现未使用索引或设计不合理的查询，为后续调优提供依据。

常用配置参数

  • slow_query_log：用于开启或关闭慢查询日志。设置为 ON 表示启用。
  • slow_query_log_file：指定慢查询日志的存储文件路径。可以根据实际情况选择合适的位置，确保 MySQL 有写入权限。
  • long_query_time：定义记录为慢查询的阈值（单位为秒）。例如设置为 1 表示记录执行时间超过 1 秒的查询。可以根据系统情况和业务要求调整这个值。
  • log_queries_not_using_indexes：当设置为 ON 时，未使用索引的查询也会被记录，帮助发现没有走索引的查询语句，通常对于优化非常有帮助。
  • log_output：指定日志的输出方式，可以设置为 FILE（默认）、TABLE 或者 NONE。如果设置为 TABLE，慢查询日志会写入系统表 mysql.slow_log，便于通过 SQL 语句查询和分析。

配置示例

配置文件（my.cnf 或 my.ini）

在 MySQL 的配置文件中添加或修改相关参数，示例如下：

1
2
3
4
5
6
7
8
9
10
11
[mysqld]
# 开启慢查询日志
slow_query_log = ON
# 指定慢查询日志文件路径（根据实际环境调整路径）
slow_query_log_file = /var/log/mysql/slow_query.log
# 设置慢查询的时间阈值（单位秒）
long_query_time = 1
# 记录未使用索引的查询
log_queries_not_using_indexes = ON
# 设置日志输出方式，可选FILE或TABLE
log_output = FILE

修改配置文件后需要重启MySQL服务以使配置生效。

动态设置

除了配置文件外，也可以在 MySQL 客户端中通过SET GLOBAL命令动态设置参数：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 开启慢查询日志
SET GLOBAL slow_query_log = 'ON';

-- 设置日志文件路径（注意：部分参数可能需要在配置文件中设置，具体取决于MySQL版本）
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow_query.log';

-- 设置慢查询时间阈值为1秒
SET GLOBAL long_query_time = 1;

-- 开启记录未使用索引的查询
SET GLOBAL log_queries_not_using_indexes = 'ON';

-- 指定日志输出方式为FILE
SET GLOBAL log_output = 'FILE';

这种方式适用于临时调整，但重启后可能失效，因此建议将关键参数写入配置文件中。

记录示例

在开启慢查询日志后，当某条 SQL 语句执行时间超过设置的阈值（例如1秒）时，就会被记录到慢查询日志中。

示例：

1
2
3
4
5
# Time: 2022-07-02T23:42:43.123456Z
# User@Host: dbuser[dbuser] @ localhost [127.0.0.1]  Id: 8
# Query_time: 1.234567  Lock_time: 0.000123  Rows_sent: 50  Rows_examined: 10000
SET timestamp=1656776563;
SELECT name, email FROM users WHERE status = 'active' ORDER BY created_at DESC LIMIT 100;

解析：

  • 时间戳信息：

    # Time: 2022-07-02T23:42:43.123456Z 表示该查询的开始执行时间（UTC 时间）。

  • 连接信息：

    # User@Host: dbuser[dbuser] @ localhost [127.0.0.1] Id: 8 记录了执行该查询的数据库用户、主机以及连接 ID。

  • 执行详情：

    • # Query_time: 1.234567 表示该查询耗时 1.234567 秒。
    • Lock_time: 0.000123 显示锁定时间。
    • Rows_sent: 50 表示返回的行数。
    • Rows_examined: 10000 表示扫描的行数，通常行数越多说明可能存在优化空间。

  • SET timestamp：

    表示实际执行 SQL 语句时的 Unix 时间戳，可以用来确认日志中的时间。

  • 实际查询语句：

    最后一行显示了完整的 SQL 语句，方便后续重现和调试。

注意事项

  • 权限与磁盘空间：确保 MySQL 用户对日志文件所在目录具有写权限，并定期监控日志文件的大小，避免因日志过大而影响磁盘空间。

  • 日志轮转（Log Rotation）：可配置日志轮转策略，定期归档和清理慢查询日志文件，避免日志文件无限制增长。

  • 日志分析工具：配合 Percona Toolkit 中的 pt-query-digest 等工具，可以对慢查询日志进行统计和分析，找到最耗时的查询和潜在的性能瓶颈。

SHOW PROFILE

基本概念

SHOW PROFILE 是 MySQL 提供的一种查询分析工具，用于展示单条 SQL 语句执行过程中的各个阶段及其耗时。通过查看这些阶段信息，可以发现查询在执行过程中哪些部分比较耗时，从而辅助性能调优。

开启 Profiling

在使用 SHOW PROFILE 前，需要在当前会话中启用 profiling 功能。可以通过以下命令启用：

1
SET profiling = 1;

此命令会在当前会话中启用查询分析功能，后续执行的 SQL 语句都会被记录详细的执行阶段信息。

查看 Profiling 信息

执行 SQL 查询

启用 profiling 后，执行需要分析的查询。

示例：

1
SELECT id, name FROM tt_student WHERE class_id = 1;

查看已记录的查询

使用 SHOW PROFILES 命令可以查看当前会话中所有执行过的查询及其对应的查询 ID 和执行时间。

示例：

1
SHOW PROFILES;

得到的结果为：

1
2
3
4
5
+----------+------------+----------------------------------------------------+
| Query_ID | Duration   | Query                                              |
+----------+------------+----------------------------------------------------+
|        1 | 0.00036925 | SELECT id, name FROM tt_student WHERE class_id = 1 |
+----------+------------+----------------------------------------------------+

这里的 Query_ID 是查询标识，后续可以通过该 ID 查看详细的执行阶段信息。

查看单条查询的详细执行阶段

使用 SHOW PROFILE FOR QUERY [query_id] 命令可以查看指定查询的详细信息。

示例：

1
SHOW PROFILE FOR QUERY 1;

得到的结果为：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000065 |
| Executing hook on transaction  | 0.000002 |
| starting                       | 0.000005 |
| checking permissions           | 0.000003 |
| Opening tables                 | 0.000117 |
| init                           | 0.000003 |
| System lock                    | 0.000005 |
| optimizing                     | 0.000006 |
| statistics                     | 0.000038 |
| preparing                      | 0.000009 |
| executing                      | 0.000025 |
| end                            | 0.000001 |
| query end                      | 0.000002 |
| waiting for handler commit     | 0.000006 |
| closing tables                 | 0.000005 |
| freeing items                  | 0.000043 |
| logging slow query             | 0.000030 |
| cleaning up                    | 0.000006 |
+--------------------------------+----------+

解析说明：

  • Status：描述查询执行过程中的各个阶段（例如 starting、opening tables、executing、sending data 等）。
  • Duration：显示每个阶段的耗时，单位为秒，通过这些数据可以定位哪个环节消耗的时间较多。

应用场景

  • 性能调优：通过详细的阶段耗时数据，可以找出执行时间较长的环节。例如，如果 sending data 阶段耗时显著，可能需要检查是否存在数据传输或排序等瓶颈。
  • 调试复杂查询：对于结构复杂或执行过程不明确的查询，SHOW PROFILE 能够提供直观的分解信息，帮助理解 SQL 执行过程。
  • 对比优化效果：在进行 SQL 优化前后，通过多次执行并对比 SHOW PROFILE 的输出，可以直观感受到性能改进情况。

注意事项

  • 性能开销：启用 profiling 功能会对查询性能产生一定开销，建议在开发或测试环境中使用，生产环境中尽量只在必要时启用。
  • 会话级别：profiling 信息仅在当前会话内有效，断开连接后相关数据将丢失。若需要长期保存查询性能信息，可结合慢查询日志或 Performance Schema。

Performance Schema

概述

Performance Schema 是 MySQL 提供的内置性能监控与诊断框架。它基于一系列的 producer-consumer 模式来捕获数据库内部的各种事件（语句事件、阶段事件、等待事件等），并将这些事件信息存储在 Performance Schema 下的若干表中（如 events_statements_summary_by_digest、events_stages_history、events_waits_summary_global_by_event_name 等），以便我们进行查询分析和系统调优。

相较于早期的 SHOW PROFILE 功能，Performance Schema 提供的数据更加全面、更细粒度，可用于多种场景下的性能分析，包括定位慢 SQL、监控高并发场景下的资源消耗、查看锁等待、分析表扫描量等。。

启用

  1. 检查是否已启用

    在 MySQL 5.6+ 版本中，Performance Schema 默认是启用的。可以通过以下方式查看：

    1
    SHOW VARIABLES LIKE 'performance_schema';

    如果结果为 ON，则表示已启用；若为 OFF，可在配置文件或启动参数中设置 performance_schema=ON 并重启 MySQL 生效。

  2. 启用生产者（Instruments）

    Performance Schema 下的 setup_instruments 表决定了哪些事件会被记录。可以查看如下内容：

    1
    2
    3
    4
    5
    SELECT NAME, ENABLED, TIMED
    FROM performance_schema.setup_instruments
    WHERE NAME LIKE 'stage/%' 
       OR NAME LIKE 'statement/%'
       OR NAME LIKE 'wait/%';
    • ENABLED = 'YES' 表示对该事件类型已开启采集；
    • TIMED = 'YES' 表示会记录耗时信息。

    如需启用更多，可以执行：

    1
    2
    3
    UPDATE performance_schema.setup_instruments
    SET ENABLED = 'YES', TIMED = 'YES'
    WHERE NAME LIKE 'stage/%';

  3. 启用消费者（Consumers）

    采集到的数据是否写入相应的表，还取决于 setup_consumers 表的设置。

    1
    2
    SELECT NAME, ENABLED
    FROM performance_schema.setup_consumers;

    常用的消费者包括：

    • events_statements_history

    • events_statements_history_long

    • events_stages_history

    • events_stages_history_long

    • events_statements_summary_by_digest (用来做 SQL 摘要统计) 若想启用 events_stages_history 存储阶段数据：

      1
      2
      3
      UPDATE performance_schema.setup_consumers
      SET ENABLED = 'YES'
      WHERE NAME = 'events_stages_history';

      同理，也可以启用或关闭其他消费者。

    注意：

    • 通过 UPDATE 的方式只对当前运行的 MySQL 实例生效，重启后可能会回到默认值。若需永久生效，需在 my.cnf（或 mysqld.cnf）里进行合适的配置或使用 MySQL 8.0+ 的持久化系统变量机制。
    • 在同一会话中进行配置、执行 SQL、查看数据，效果更明确。

场景示例

执行次数及时长最多的 SQL

最简单的是查看 events_statements_summary_by_digest 表。它会将类似的 SQL（相似结构但绑定值不同）聚合为一个 digest，统计执行次数、总耗时、平均耗时、最大耗时等信息。

示例：

1
2
3
4
5
6
7
8
SELECT 
    DIGEST_TEXT,
    COUNT_STAR AS exec_count,
    SUM_TIMER_WAIT/1000000000000 AS total_time_sec,
    AVG_TIMER_WAIT/1000000000000 AS avg_time_sec
FROM performance_schema.events_statements_summary_by_digest
ORDER BY exec_count DESC
LIMIT 10;

这条 SQL 能快速找到执行次数最多的前 10 条 SQL 摘要。

  • DIGEST_TEXT：一条 SQL 摘要化后的模板，如 SELECT * FROM employees WHERE employee_id = ?。
  • COUNT_STAR：统计 SQL 被执行的次数。
  • SUM_TIMER_WAIT 和 AVG_TIMER_WAIT：代表总执行时间和平均执行时间，单位通常是皮秒（可除以 10^12 转化为秒）。

扫描记录最多的 SQL

Performance Schema 的摘要表也会统计 “扫描的行数（Rows_examined）” 等信息，可以帮助定位 “谁” 在频繁地做全表扫描或高代价扫描。

示例：

1
2
3
4
5
6
7
8
SELECT 
    DIGEST_TEXT,
    SUM_ROWS_EXAMINED AS total_rows_examined,
    COUNT_STAR AS exec_count,
    ROUND(SUM_ROWS_EXAMINED / COUNT_STAR) AS avg_rows_examined
FROM performance_schema.events_statements_summary_by_digest
ORDER BY total_rows_examined DESC
LIMIT 10;
  • SUM_ROWS_EXAMINED：此 digest 下所有 SQL 语句执行时累计检查的行数。
  • avg_rows_examined：平均每次扫描的行数，如果某条 SQL 平均扫描行数特别高，则通常有潜在的索引或写法问题，需要优化。

查看单条 SQL 各阶段运行时间

当需要更细粒度地了解 SQL 执行中的具体耗时环节（例如：opening tables、executing、sending data 等），可以使用阶段（stage）事件。步骤如下：

  1. 启用生产者 stage instrument：

    1
    2
    3
    UPDATE performance_schema.setup_instruments
    SET ENABLED = 'YES', TIMED = 'YES'
    WHERE NAME LIKE 'stage/%';

  2. 启用对应消费者 setup_consumers：

    1
    2
    3
    UPDATE performance_schema.setup_consumers
    SET ENABLED = 'YES'
    WHERE NAME = 'events_stages_history';

  3. 在当前会话中执行一个查询（尽量稍微复杂些，让它有足够阶段可记录）。

  4. 然后查看 events_stages_history 表：

    1
    2
    3
    4
    5
    6
    SELECT EVENT_ID,
           EVENT_NAME,
           TIMER_WAIT/1000000000000 AS duration_sec
    FROM performance_schema.events_stages_history
    ORDER BY EVENT_ID DESC
    LIMIT 10;

    或者查看 events_stages_history_long 表 以获取更久远的数据。开发者将看到每一阶段名称（例如 stage/sql/starting、stage/sql/executing、stage/sql/sending data 等）以及对应的耗时，从而判断在哪个阶段花费了最多时间，针对性进行优化。

其他数据指标

Performance Schema 涵盖了广泛的性能信息，以下是常见的一些指标及其对应的表或视图，你可在需要时参考官方文档或进一步查询：

  • 临时表使用情况：可关注 events_statements_summary_by_digest 中的 SUM_CREATED_TMP_DISK_TABLES、SUM_CREATED_TMP_TABLES 等字段，或在 events_statements_history[_long] 表中查看单次执行的具体信息。
  • 索引使用情况：可以通过 “扫描行数” 与 “返回行数” 的对比来做粗略判断，也可结合慢查询日志和 EXPLAIN 工具来评估索引是否生效。
  • 锁使用情况：在 performance_schema.events_waits_history、events_waits_summary_global_by_event_name 等表中查看锁等待事件（wait/lock）。
  • IO 次数、IO 等待情况：查询 events_waits_summary_global_by_event_name 中 wait/io/file 或 wait/io/socket 类型的事件。也可通过 events_waits_history / events_waits_history_long 研究具体会话下的 IO 等待。

注意事项

  • 性能开销：开启过多的 Instruments 和 Consumers，尤其在高并发环境下，可能带来较大性能开销。应根据实际需求 “按需启用”，避免给生产环境造成显著负担。
  • 会话与持久化：通过 UPDATE performance_schema.setup_instruments 或 setup_consumers 的方式通常只对当前实例生效，重启后会恢复为默认。若要永久生效，需在配置文件中写明或使用 MySQL 8.0+ 的 SET PERSIST 语句。
  • 版本差异：不同 MySQL 版本对 Performance Schema 的默认配置和可用字段略有差异。遇到无法查询字段或表的情况，应查阅对应版本的官方文档进行确认。
  • 安全与权限：Performance Schema 某些表仅有 SELECT 权限对 DBA 或特定用户开放，需确保当前用户具备访问 performance_schema 数据的权限。

Explain

作用

  • 展示执行计划：EXPLAIN 命令用于显示给定 SQL 语句（包括 SELECT、DELETE、INSERT、REPLACE、UPDATE 等）生成的执行计划。它揭示了查询语句中涉及的各个表的访问顺序、使用的索引以及访问数据的方式。
  • 辅助查询优化：通过分析 EXPLAIN 的输出，可以判断查询是否存在全表扫描、索引选择不合理或者不必要的排序、临时表等问题，从而针对性地调整 SQL 语句、创建或修改索引以及改进表结构，以提高查询性能。

用法

在 SQL 语句前加上 EXPLAIN，即可查看执行计划。

示例：

1
EXPLAIN [FORMAT=JSON] SELECT * FROM employees WHERE id = 100;

其中 FORMAT=JSON 为 MySQL 5.6 引入的可选项，可以适用 JSON 格式输出执行计划。

详细说明

EXPLAIN 命令返回的结果集通常包含多个列，不同版本可能略有不同，但主要列及其含义如下：

  • id：表示查询中每个 SELECT 子句的标识符。id 值越大，表示该子查询或联合查询的优先级越高，通常也能反映执行顺序。

  • select_type：描述查询中各个 SELECT 的类型，如：

    • SIMPLE：简单查询，不包含子查询或 UNION。
    • PRIMARY：主查询，即复杂查询中的最外层 SELECT。
    • SUBQUERY：在 SELECT 或 WHERE 语句中嵌套的子查询。
    • DERIVED：派生表（Derived Table），即 FROM 子查询，MySQL 会将其视为临时表。
    • UNION：UNION 查询中的第二个及后续 SELECT 语句。
    • UNION RESULT：UNION 查询的结果集，存放在一个临时表中。
    • DEPENDENT SUBQUERY：依赖外部查询的子查询，必须逐行执行（类似于 correlated subquery 相关子查询）。
    • DEPENDENT UNION：UNION 查询中的第二个及后续 SELECT 语句，且依赖于外部查询。
    • UNCACHEABLE SUBQUERY：不能被缓存的子查询，每次执行都要重新评估。
    • UNCACHEABLE UNION：UNION 语句中的一个分支，该分支不能被缓存。

  • table：显示当前行所引用的表的名称，表示查询中正在访问哪个表。

  • partitions：如果表使用了分区，则此列会显示参与查询的分区信息。

  • type：代表访问类型，也是衡量查询性能的关键指标。从最好到最差的类型依次为：

    • system、const：极其高效，只需要访问一行或几行数据。
    • eq_ref、ref：较好，可以利用索引精确定位。
    • range：通过范围条件访问索引中的连续数据。
    • index：全索引扫描，比全表扫描略好。
    • ALL：全表扫描，效率较低，应尽量避免。

  • possible_keys：列出查询中可能用到的所有索引，供优化器参考。

  • key：实际上被 MySQL 选择使用的索引。如果为 NULL，说明没有使用索引。

  • key_len：表示 MySQL 实际使用的索引长度。这个值可以帮助判断索引是否被充分利用。

  • ref：显示哪个列或常量与 key 索引一起被使用，用来指示索引的匹配情况。

  • rows：估计需要扫描的行数，行数越多，通常说明查询的代价越高。

  • filtered：表示经过表过滤条件后，实际返回的行所占的百分比。

  • Extra：提供额外的执行信息，比如：

    • Using where：查询中使用了 WHERE 过滤条件。
    • Using index：查询仅通过索引、无需访问表数据就能获取数据，称为 “覆盖索引”。
    • Using index condition：只利用索引条件进行部分筛选，但仍需访问表数据。
    • Using temporary：查询中使用了临时表（通常出现在 GROUP BY 和 ORDER BY 查询中）。
    • Using filesort：需要额外的排序操作，而不是利用索引进行排序（通常出现在 ORDER BY 语句中）。
    • **Using join buffer (Block Nested Loop)**：连接时使用了 join buffer，通常发生在 JOIN 查询未使用索引的情况下。
    • Using MRR：MySQL 使用了 Multi-Range Read，优化范围查询时的一种方式。
    • Using index for group-by：GROUP BY 操作直接使用索引，无需额外排序或临时表。
    • Using where with pushed condition：WHERE 条件被 “推送” 到索引扫描阶段，提高查询效率（主要适用于 InnoDB）。
    • Start temporary; End temporary：表示查询涉及临时表，数据可能在临时表中被重新排序或处理。
    • No matching rows in const table：查询的 const 类型表没有匹配的行，通常意味着索引查找失败。

注意事项

  • 关注访问类型（type）：访问类型是衡量查询效率的重要指标，尽量避免出现全表扫描这种类型。如果发现查询使用了全表扫描，应考虑是否能通过增加索引或修改查询条件来改进。
  • 索引使用情况：比较 possible_keys 与 key 列，看看是否 MySQL 在可选的索引中选用了最优的索引。如果 key 为 NULL 而 possible_keys 不为空，可能说明查询条件未能充分利用索引。
  • 额外信息（Extra）：注意 Extra 列中出现的提示信息，如 Using temporary 或 Using filesort。这通常表示查询中存在需要额外优化的部分，如排序或分组操作，这时可以考虑改写查询或调整索引。
  • 扫描行数（rows）：如果扫描的行数较多，查询可能会变慢。可以尝试进一步缩小查询范围或优化查询条件，以减少扫描的行数。

示例

查看以下 SQL 的执行计划

1
EXPLAIN SELECT id, name FROM tt_student WHERE class_id = 1;

执行计划如下：

1
2
3
4
5
+----+-------------+------------+------------+------+---------------+--------------+---------+-------+------+----------+-------+
| id | select_type | table      | partitions | type | possible_keys | key          | key_len | ref   | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+--------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | tt_student | NULL       | ref  | idx_class_id  | idx_class_id | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+------------+------------+------+---------------+--------------+---------+-------+------+----------+-------+

通过 EXPLAIN 输出，可以检查：

  • 查询是否利用了 class_id 的索引；
  • 访问类型是否为 range 或 ref，从而判断索引是否有效；
  • rows 列显示的扫描行数，判断查询过滤效果；
  • Extra 列是否有额外信息，比如是否需要临时表或排序。

根据这些信息，你可以决定是否需要重新建立索引，或者调整查询语句来提高性能。

优化方向

关注 EXPLAIN 结果可以让 SQL 优化有一些方向，如：

访问方式

关注 type 列，如果出现 ALL（全表扫描）或 index（全索引扫描），通常意味着索引未被合理利用，应优化查询。

type 值 可能问题 解决方案
ALL (全表扫描) 查询未使用索引，性能最差 创建合适的索引、调整 WHERE 条件
index (全索引扫描) 索引被扫描，但未高效过滤数据 考虑是否可以改用 WHERE 限制数据范围
range (范围查询) 使用索引进行范围查找，但扫描行数仍然较多 确保查询字段上有合适的索引，考虑索引选择性
ref 索引被使用，但匹配的数据较多 尽量提高索引的唯一性（比如 唯一索引）
eq_ref 索引精准查找，性能很好 无需优化
const/system 仅查询单行数据，性能最佳 无需优化

索引使用

关注 possible_keys 和 key。

possible_keys 和 key 可能问题 解决方案
possible_keys 为空 没有可用索引 创建合适的索引
key 为 NULL 没有索引被使用 调整 WHERE 条件或创建索引
possible_keys 多于 key 有更优索引但未被使用 使用 FORCE INDEX 或优化查询逻辑
key_len 过小 使用了复合索引，但未充分利用 确保索引的最左前缀原则

扫描行数

关注 rows，这个参数代表 MySQL 预计要扫描的行数，值越大，查询成本越高。

关注点：

  • 确保索引被使用，减少 rows 数量。
  • rows 过大可能意味着索引选择不合理，需要调整索引或查询逻辑。
  • 对大表查询时，考虑 LIMIT 限制查询范围。

额外执行信息

关注 Extra 列展示的额外执行信息。

Extra 取值 可能问题 解决方案
Using filesort 额外的排序操作，未使用索引排序 添加合适的索引 (ORDER BY 字段建立索引)
Using temporary 查询使用了临时表，性能较差 优化 GROUP BY、ORDER BY，尽量使用索引
Using where 使用了 WHERE 过滤条件，但索引可能未充分利用 确保 WHERE 条件字段有索引
Using index 覆盖索引查询，无需回表 推荐，尽量让查询满足索引覆盖
Using join buffer JOIN 查询未使用索引，导致 join buffer 过多使用 给 JOIN 的连接字段创建索引

索引失效及应对

对于数据量较大的表，利用好索引往往能让查询效率变得非常高，但 SQL 如果不规范往往会导致索引失效，如以下场景：

对索引列使用函数或表达式

场景说明

当在 WHERE 子句中对索引列应用函数（如 YEAR()、DATE()、UPPER() 等）或做算术运算时，MySQL 无法直接利用该列的原始索引。

示例

1
SELECT * FROM orders WHERE YEAR(order_date) = 2021;

应对措施

  • 尽量将常量转换为范围值，避免在索引列上进行函数调用。

  • 修改查询，如将上例改写为：

    1
    2
    SELECT * FROM orders 
    WHERE order_date BETWEEN '2021-01-01' AND '2021-12-31';

  • 或者在设计时考虑增加一个存储计算结果的辅助列，并对其建立索引。

注意：MySQL 8.0 的优化器在某些函数场景也可能部分利用索引的 MRR（Multi-Range Read）或 ICP（Index Condition Pushdown），但大多数情况下仍建议避免对索引列进行函数处理。

数据类型不匹配或隐式转换

场景说明

如果查询中的常量数据类型与索引列的数据类型不一致，或者表关联字段数据类型不一致，MySQL 可能会进行隐式转换，这往往会使索引失效。

示例

1
2
# users表中id字段为数值类型，非字符串类型
SELECT * FROM users WHERE id = '100';  -- 字符串 '100' 可能引发隐式转换

应对措施

  • 在编写 SQL 语句时，确保传入的参数与列的数据类型一致，避免不必要的类型转换。
  • 检查应用层传入的参数，保持数据类型的匹配。

使用 LIKE 时使用前导通配符

场景说明

当使用 LIKE 模糊查询且模式中以 % 或 _ 开头时，MySQL 无法利用索引进行匹配。

示例

1
SELECT * FROM products WHERE name LIKE '%phone';

应对措施

  • 如果业务允许，尽量避免前导通配符；改为后缀通配符，如 LIKE 'phone%'。
  • 当必须使用前导通配符时，可以考虑使用全文索引（FULLTEXT）或其他搜索引擎来替代常规索引。

OR 条件中部分字段缺少索引

场景说明

在使用 OR 条件连接多个字段时，如果其中某个字段没有索引，整个查询可能会无法利用索引。

示例

1
2
SELECT * FROM customers 
WHERE first_name = 'John' OR last_name = 'Doe';

假设只有 first_name 建有索引，而 last_name 没有索引，查询整体可能不会走索引。

应对措施

  • 为所有涉及的字段建立适当的索引。
  • 或者将 OR 查询拆分成两个独立查询，再用 UNION 组合结果，这样可以确保每个子查询都能走索引。

多列复合索引的最左前缀原则失效

场景说明

对于复合索引，只有在 WHERE 子句中包含索引最左侧连续列时，索引才会生效。如果跳过了最左侧的列，后面的索引将无法被利用。

示例

假设有复合索引 (a, b, c)，但查询条件仅涉及 b：

1
SELECT * FROM table WHERE b = 'value';

此时就会导致整个查询没有用上符合索引。

应对措施

  • 设计复合索引时，应根据查询条件的实际使用顺序，合理规划最左前缀。
  • 根据常用查询调整索引顺序，或者针对单列查询建立独立索引。

不等于条件和范围查询的特殊情况

场景说明

对于不等于 (!= 或 <>) 条件或范围查询，索引可能无法达到理想的过滤效果，尤其是在数据分布不均的情况下。

示例

1
SELECT * FROM employees WHERE salary <> 5000;

应对措施

  • 尽量避免在索引列上使用不等于条件，或者改为其他能利用索引的查询条件。
  • 对于范围查询，要确保索引列选择性较高，以便缩小扫描范围。

SQL 语法参数优化

除了利用好索引外，SQL 语法中的参数同样需要引起重视：

SELECT语句

  • 字段选择：避免 SELECT *，明确列名，减少数据传输量。
  • DISTINCT 与 GROUP BY：对结果集进行去重或分组时，注意计算成本，必要时可以通过业务逻辑在应用层实现。

JOIN 操作

  • 选择合适的JOIN类型：内连接（INNER JOIN）通常性能较好，尽量避免使用笛卡尔积。
  • 索引支持：JOIN 操作涉及的字段应有索引支持，确保连接条件能够高效匹配。
  • 顺序优化：调整 JOIN 顺序，使得大表与小表连接时优先过滤数据。

条件优化

  • 避免在 WHERE 子句中对索引字段使用函数或进行运算，尽量直接使用常量进行比较。

  • 使用范围查询时，合理利用索引前缀。

GROUP BY 与 ORDER BY

  • 对于排序和分组字段应建立合适的索引。

  • 避免在大数据集上进行不必要的排序操作，可考虑在应用层处理部分逻辑。

LIMIT 与分页查询

  • 索引分页：对于大数据量分页，使用 “上次记录的最后值” 作为条件进行下一页查询，避免大量数据扫描。
  • 合理分页大小：根据业务场景设置合理的分页大小，平衡用户体验与系统性能。

子查询与预编译

  • 子查询优化：尽量将子查询转换为 JOIN 或临时表，降低嵌套层级。
  • 预编译与参数绑定：使用预编译语句（Prepared Statement）不仅可以提高执行效率，还能防止 SQL 注入。

小表驱动大表

含义

这句话通常出现在数据库的多表 JOIN 查询 优化中，尤其是对于嵌套循环连接（Nested Loop Join） 的场景。其含义为在多表连接中，应该让较小的数据集（“小表”）作为驱动表（外表/驱动器），而较大的数据集（“大表”）作为被驱动表（内表）。这种优化策略有助于提高查询效率。

原理

  • 驱动表更小，内存缓存更高效：
    • 小表更容易被完全加载到内存中作为驱动表，从而减少磁盘 I/O。
    • 驱动表每次只需要从内存中取数据，而大表可以按块分批扫描，提高查询效率。
  • 被驱动表通常有索引辅助：如果被驱动表的连接列上存在索引，查询效率更高，因为每次匹配时能直接利用索引快速定位目标数据。
  • 网络开销考虑：分布式数据库中，小表驱动大表可以减少跨节点传输的数据量。例如，先将小表的结果集传输到另一个节点与大表匹配，网络开销更小。

场景

JOIN

1
2
3
4
SELECT *
FROM a
JOIN b
ON a.key = b.key;
  • 驱动表：a，JOIN 的处理会先扫描此表。
  • 被驱动表：b，通过 a.key 扫描此表。

优化注意：数据量较小的表放在位置 a 作为驱动表，减少外层扫描次数，提高效率。

EXISTS

1
2
3
4
5
6
7
SELECT *
FROM a
WHERE EXISTS (
    SELECT 1
    FROM b
    WHERE b.key = a.key
);
  • 驱动表：a，EXISTS 的执行是对此表的每一行数据一次检查是否满足子查询的条件，因此外层表始终都是驱动表。
  • 被驱动表：b，外层的行作为条件执行此表的查询。

优化注意：数据量较小的表放在位置 a 作为驱动表，位置 b 上的表不需要全部扫描，只要有一行匹配即返回 TRUE。

IN

情况1：子查询在 IN 中。

1
2
3
4
5
6
SELECT *
FROM a
WHERE l.key IN (
    SELECT b.key
    FROM b
);
  • 驱动表：a，IN 查询时 MySQL 优化器会对此表逐行扫描，每行数据都与子查询的结果进行匹配，因此外层表是驱动表。
  • 被驱动表：b，MySQL 会先执行子查询，生成一个中间结果集，然后与外层表的行进行比对。

优化注意：如果子查询返回的结果集比较大，可能导致性能问题，这种情况下应考虑将子查询改为 JOIN 或使用索引优化。

情况2：多值 IN 的写法。

1
2
3
SELECT *
FROM a
WHERE a.key IN (1, 2, 3);

这种写法中，a 是驱动表，IN 的常量集合被 MySQL 优化为索引查找。

注意事项

  1. 不绝对适用：“小表驱动大表” 是通用原则，但具体是否适用需结合表的大小、索引情况和查询语句的实际执行计划来判断。
  2. 联合索引优先：被驱动表如果有合适的联合索引，则查询效率可能更高，此时，即使大表作为驱动表也可能具有一定优势。
  3. 分布式系统：在分布式数据库中，需综合考虑网络传输成本和各节点的计算负载。

特殊情况

在某些情况下，MySQL 的查询优化器可能主动选择全表扫描而不是使用索引，这是一个经过计算和权衡后的决策，并且实际上查询效率可能更高。以下是可能导致这种情况的原因，以及引发的思考：

全表扫描更高效场景

表中的数据量较少

当表的数据量很少时（如只有几十行或更少），全表扫描一次性读取所有数据的开销可能比使用索引导航到数据位置的开销更低。

索引的代价： 索引查找需要进行多次 B+ 树节点访问（通常是磁盘 I/O），即使在内存中也存在一定的代价。对于小表来说，这种代价可能高于全表扫描。

回表代价更高

  1. 索引选择性较低：索引选择性是指索引列中不重复值的比例（公式：选择性 = 不同值的数量 / 总行数）。当索引选择性较低时，大量行具有相同的值，使用索引可能会返回大量数据行，这会导致查询性能不如全表扫描。

    示例：

    1
    SELECT * FROM table_name WHERE status = 1;

    如果 status 字段的值几乎都是 1（如 99% 的行满足条件），使用索引会导致大量回表操作，而全表扫描一次性读取所有数据可能更高效。

  2. 索引无法覆盖查询：如果查询的字段不在索引中，MySQL 需要通过索引定位到主键值，然后再回表查询数据，这会产生额外的 I/O 开销，而全表扫描在这种情况下可能更高效。

    示例：

    1
    SELECT id, name FROM table_name WHERE age > 20;

    如果只有 age 建立了索引，但 id 和 name 没有包含在索引中，查询需要频繁回表，效率可能低于直接全表扫描。

查询涉及大量数据行

如果查询需要返回的行数占总行数的大部分，使用索引将不再具备显著优势，优化器可能会选择全表扫描。

示例：

1
SELECT * FROM table_name WHERE age > 5;

如果表中 90% 的记录都符合条件，MySQL 会倾向于全表扫描而不是通过索引逐一定位数据行。

索引统计信息不准确

MySQL 优化器根据表的统计信息（如行数、索引选择性等）评估索引的性能。如果统计信息过期或不准确，可能导致优化器选择全表扫描，此时可以手动更新统计信息。

示例：

1
ANALYZE TABLE table_name;

引发思考

  • 索引并非越多越好：每个索引都有维护成本（写入、更新、删除时需更新索引结构），因此需要合理设计，不合理的索引可能导致查询计划复杂化甚至性能下降。

  • 索引设计需结合数据特性：索引适用于高选择性字段，避免对低选择性字段（如性别、状态等）单独建立索引。对于频繁查询的字段，尽可能设计覆盖索引，避免回表操作。

  • 定期更新优化器：优化器是基于统计信息来选择执行计划的，需要理解数据分布、索引选择性、返回行数与扫描范围的关系，同时定期更新表的统计信息以帮助优化器做出更准确的选择。

  • 要关注实际查询执行计划：使用 EXPLAIN 检查查询的执行计划，分析是否选择了合理的索引路径，如果发现优化器选择全表扫描，而强制索引更快，可能需要调整索引设计或优化 SQL。

  • 性能测试与实践的重要性：理论分析并不总能覆盖所有情况，应通过实际查询测试确认最佳索引策略，在高并发和大数据量场景下，实时测试尤为重要。

其他注意事项

编写规范化SQL

  • 清晰明了： 保持 SQL 结构清晰，避免嵌套过深和复杂联接。
  • 注释与文档： 对复杂查询加以注释，便于日后维护和调优。

索引管理

  • 定期评审： 根据查询情况和数据量变化定期审查索引使用情况，删除冗余和低效索引。
  • 监控工具： 使用慢查询日志和监控工具定期评估索引命中率和性能。

数据库连接与事务管理

  • 连接池： 使用数据库连接池技术，避免频繁创建连接带来的开销。
  • 事务控制： 合理划分事务范围，避免长事务锁定资源，同时确保数据一致性。

代码与数据库解耦

  • 业务逻辑分离： 将复杂数据处理逻辑尽可能放在业务层，实现数据查询与业务逻辑的分离。
  • 缓存策略： 对热点数据使用缓存策略，降低数据库直接压力。

定期监控与调优

  • 性能监控： 建立完善的性能监控体系，及时发现异常查询。
  • 版本升级： 关注MySQL版本更新，利用新版本中引入的性能改进和新特性。

总结

在高并发、大数据量的应用场景中，SQL 优化对系统的整体性能至关重要。通过慢查询日志和 SHOW PROFILE，迅速定位个别耗时严重的查询，配合 Performance Schema 进一步挖掘性能瓶颈，掌握执行阶段的每个细节。而 EXPLAIN 则提供了直观的执行计划视图，帮助评估索引使用、扫描行数以及是否产生临时表或文件排序。在实际落地时，仍针对不同类型的 SQL，需要针对性地编写或改写查询。综合运用本文介绍的技巧与工具，在开发和运维阶段持续关注慢查询和性能指标，既能大幅缩短关键 SQL 的响应时间，也能为业务提供更稳定的系统支撑。

MySQL
MySQL MySQL优化

本博客所有文章除特别声明外，均采用 CC BY-SA 4.0 协议 ，转载请注明出处！