性能分析-MySQL慢查询

在MySQL数据库运维中，慢查询是导致数据库性能瓶颈的核心原因之一，尤其在高并发、大数据量业务场景下，一条未优化的慢查询可能导致数据库卡顿、连接堆积，甚至影响整个业务系统的可用性。通过监控SQL语句的执行效率、分析索引使用情况，可精准定位慢查询引发的性能问题，进而优化SQL与索引，提升数据库响应速度。

一、慢查询的配置与抓取

慢查询指执行时间超过预设阈值的SQL语句，MySQL默认不会主动记录慢查询，需手动配置开启慢查询日志，将符合条件的SQL语句记录到指定日志文件中，便于后续分析定位问题根源。慢查询的核心判断标准是「执行时间」，但需注意：执行时间不仅包含SQL本身的查询时间，还包括磁盘I/O、网络传输等耗时，配置时需结合业务场景合理设定阈值。

1. 慢查询配置方法

适配MySQL 5.7+，兼容8.0版本，不同版本配置差异极小，核心配置要点、实操命令和注意事项如下：

• 开启慢查询日志：通过修改my.cnf（Linux系统，路径通常为/etc/my.cnf、/etc/mysql/my.cnf）/my.ini（Windows系统，路径通常在MySQL安装目录下）配置文件，添加「slow_query_log = 1」（1表示开启，0表示关闭），开启慢查询日志记录。 补充：临时开启（无需重启MySQL，重启后失效）：set global slow_query_log = 1;
• 设置慢查询阈值：配置long_query_time参数（默认10秒），指定“执行时间超过该值”的SQL为慢查询，例如「long_query_time = 1」表示执行时间超过1秒的SQL会被记录。 补充：阈值支持小数（如0.5表示500毫秒），需结合业务响应要求设定（如高频接口建议设为0.1-0.5秒，后台报表查询可设为1-3秒）；临时修改阈值：set global long_query_time = 1;
• 指定日志存储路径：配置slow_query_log_file参数，明确慢查询日志的存储位置，便于后续查看和分析，例如「slow_query_log_file = /var/log/mysql/slow.log」（Linux）、「slow_query_log_file = D:/mysql/logs/slow.log」（Windows）。 注意：日志存储路径需确保MySQL服务有读写权限，否则日志无法生成；若未指定路径，默认存储在MySQL的数据目录下（Linux默认路径：/var/lib/mysql，日志名称格式为{hostname}_slow-query.log，其中{hostname}为服务器主机名）。
• 重启MySQL生效：配置修改后，需重启MySQL服务，使慢查询配置永久生效。 补充：Linux重启命令：systemctl restart mysqld；Windows重启命令：在服务中找到“MySQL”，右键选择“重启”；临时配置（set global命令）无需重启，立即生效，但仅在当前MySQL服务运行期间有效，重启后恢复默认配置。
• 可选配置（优化日志实用性）： - log_queries_not_using_indexes = 1：记录未使用索引的SQL语句（即使未达到慢查询阈值），便于排查索引失效问题； - log_output = FILE,TABLE：指定慢查询日志的输出格式（文件+数据表），可通过查询mysql.slow_log表快速查看慢查询，无需打开日志文件。

2. 慢查询抓取核心

除了配置日志抓取（适合长期监控），还可通过实时命令查看当前慢查询情况，快速定位正在执行的异常SQL，避免慢查询长时间占用数据库资源，两种方式结合使用，覆盖“历史慢查询分析”和“实时慢查询监控”场景。

• 查看慢查询配置状态：show variables like '%slow_query%'; 输出解读：slow_query_log的值为ON表示开启，OFF表示关闭；slow_query_log_file显示日志存储路径；log_queries_not_using_indexes显示是否记录未使用索引的SQL。
• 查看慢查询阈值：show variables like 'long_query_time'; 注意：修改阈值后，新提交的SQL会按新阈值判断，历史已记录的慢查询不受影响。
• 查看慢查询数量：show global status like 'Slow_queries'; 解读：统计累计慢查询条数，可用于判断数据库慢查询整体情况，若条数持续增长，需及时排查。
• 实时查看正在执行的慢查询：show processlist; 输出解读：查看当前MySQL所有连接和执行的SQL，重点关注「Time」字段（表示SQL执行时间，单位：秒），超过long_query_time阈值的即为正在执行的慢查询；State字段显示SQL执行状态（如Sending data表示正在读取数据，Locked表示被锁定）；Info字段显示完整的SQL语句，可直接复制分析；若发现慢查询，可通过kill 连接ID（Id字段的值）终止SQL执行，缓解数据库压力。
• 慢查询日志解读技巧：日志中会记录SQL执行时间、执行用户、执行主机、SQL语句等信息，重点关注「Query_time」（SQL实际执行时间）、「Rows_examined」（扫描的行数）、「Rows_sent」（返回的行数），若Rows_examined远大于Rows_sent，说明SQL扫描了大量无用数据，大概率是索引失效或SQL写法问题。

3. 慢查询日志分析工具

MySQL自带慢查询分析工具mysqldumpslow，可对慢查询日志进行针对性分析，核心功能是统计SQL语句的各类执行信息，无需额外安装，使用前需确保服务器已安装perl环境（用于解析日志）。

（1）前置准备：安装perl环境（Linux系统）

通过以下命令安装perl（若已安装可跳过）：

yum install -y perl

（2）mysqldumpslow核心参数与命令

该工具默认位于/usr/bin目录下，可直接在终端执行命令，核心常用参数如下：

• -s：指定排序规则（order），核心可选值分为两类： - 基础排序：c（按查询次数排序）、t（按执行时间排序）、l（按等待锁时间排序）、r（按返回记录数排序）； - 累计排序：ac（按查询次数累计排序）、at（按执行时间累计排序）、al（按等待锁时间累计排序）、ar（按返回记录数累计排序）；
• -a：按指定排序规则倒序排列（即从大到小排序，优先显示数值最大的条目）；
• -t：指定返回的条目数量（top N），即显示排序后前N条慢查询SQL；
• -g：指定正则匹配模式，用于筛选包含特定内容的慢查询SQL，匹配时大小写不敏感。

（3）常用命令示例（直接复制可用）

• 查看访问次数最多的20条慢查询SQL： mysqldumpslow -s c -t 20 /var/log/mysql/slow.log
• 查看返回记录集最多的20条慢查询SQL： mysqldumpslow -s r -t 20 /var/log/mysql/slow.log
• 查看按执行时间排序的前10条，且包含左连接（left join）的慢查询SQL： mysqldumpslow -t 10 -s t -g "left join" /var/log/mysql/slow.log
• 查看累计耗时最长的50条慢查询SQL的执行信息（常用）： mysqldumpslow -s at -t 50 /var/log/mysql/slow.log

（4）日志分析结果解读（示例）

以mysqldumpslow工具输出的一条典型结果为例，详细解读各字段含义：

Count: 32 Time=0.26s (8s) Lock=0.00s (0s) Rows=10.0 (320), wos_20120719[wos_20120719]@2host

• Count: 32：该条SQL总共执行了32次；
• Time=0.26s (8s)：平均每次执行该SQL耗时0.26秒，32次执行累计总耗时为32（次）×0.26（秒）=8秒；
• Lock=0.00s (0s)：该SQL所有执行次数的累计等待锁时间为0秒（单次等待锁时间也为0秒）；
• Rows=10.0 (320)：平均每次执行该SQL，会影响（扫描/返回）数据库表中的10行记录，32次执行累计影响10（行）×32（次）=320行记录；
• wos_20120719[wos_20120719]@2host：执行该SQL的数据库用户为wos_20120719，执行主机为2host。

二、EXPLAIN执行计划分析（定位慢查询原因）

抓取到慢查询后，核心是分析慢查询的执行过程，找到性能瓶颈（如全表扫描、索引失效、连接方式不合理等）。在SQL语句前添加EXPLAIN关键字，MySQL会返回该SQL的执行计划（不实际执行SQL），通过分析执行计划的各个字段，可精准判断SQL的执行效率、索引使用情况，进而定位优化方向。

1. 基本使用方法

语法格式：EXPLAIN + 待分析的SQL语句（支持SELECT、DELETE、UPDATE、INSERT语句，但主要用于分析SELECT查询语句）

示例：

EXPLAIN SELECT * FROM teacher WHERE teacher_id = 1;
-- 复杂查询示例（多表连接+条件筛选）
EXPLAIN SELECT t.teacher_id, t.teacher_name, c.class_name 
FROM teacher t 
LEFT JOIN class c ON t.teacher_id = c.teacher_id 
WHERE t.subject = '数学';

执行后会返回多个字段（通常为12个字段），核心关注Type、Possible_keys、Key、Rows四个字段，即可快速判断SQL执行效率和索引使用情况；若需更全面分析，可结合其他字段（如Extra、Id、Table等）。

2. 核心字段详解

（1）Type字段（核心，判断索引使用级别）

Type字段表示MySQL在表中找到满足条件的行的方式，即“访问类型”，值的性能从优到劣排序如下，重点关注是否出现All（全表扫描，性能最差），若Type为All且数据量较大，需优先优化。

• Const：表中只有一个匹配行，通常用于主键（primary key）或唯一索引（unique key）的等值查询（如WHERE id = 1），性能最优。 示例：EXPLAIN SELECT * FROM teacher WHERE teacher_id = 1; （teacher_id为主键），Type会显示为Const。
• Eq_ref：唯一性索引扫描，索引的所有部分都被联接查询使用，且索引是unique或primary key，通常出现在多表连接中，被连接表的索引字段作为连接条件，性能仅次于Const。 示例：多表连接中，EXPLAIN SELECT * FROM teacher t JOIN class c ON t.teacher_id = c.teacher_id（t.teacher_id为主键，c.teacher_id为唯一索引），Type会显示为Eq_ref。
• Ref：非唯一性索引扫描，或只使用了联合索引的最左前缀，返回匹配某个单独值的所有行（如WHERE name = '张三'，name为普通索引），性能中等，适合非唯一值的筛选查询。
• Range：索引范围扫描，在索引列上进行给定范围内的检索（如between、in(1,100)、>、<、>=、<=、like '张%'等条件），仅扫描索引范围内的数据，性能优于全表扫描。 注意：like '张%'会使用索引，like '%张'、like '%张%'不会使用索引（索引失效）。
• Index：遍历整个索引树（索引全扫描），未利用索引筛选条件，仅通过索引获取数据（如SELECT id FROM teacher，id为索引），性能较差（略优于全表扫描），通常是因为SQL只查询索引字段，无需访问数据表。
• All：全表扫描，遍历整个数据表的每一行，查找匹配条件的行，性能最差，慢查询中最常见的问题之一。当数据量较大（如10万+条）时，全表扫描会占用大量CPU和磁盘I/O资源，导致SQL执行缓慢，需优先优化（如添加索引、优化WHERE条件）。

（2）Possible_keys字段

表示MySQL优化器可能会使用哪个索引来找到表中的行，即“候选索引”，该字段仅显示可能使用的索引（可能有多个），不代表实际执行时会使用。若Possible_keys为NULL，说明没有可用索引，MySQL会直接触发全表扫描（Type为All），需检查是否为查询字段创建了索引。

补充：Possible_keys有值但实际未使用（Key为NULL），可能是因为索引筛选效果不佳（如索引列值重复率过高，如性别字段，只有男/女两个值，使用索引不如全表扫描高效），或SQL条件中使用了函数操作索引列（如WHERE DATE(create_time) = '2024-01-01'），导致索引失效。

（3）Key字段

表示MySQL实际执行SQL时使用的索引，是优化SQL的核心参考字段。若Key为NULL，说明未使用任何索引（触发全表扫描）；若Key有值，说明使用了对应的索引，需确认该索引是否为最优索引。

注意事项：

• Possible_keys有值但Key为NULL：通常是因为索引筛选效果不佳，MySQL优化器判定全表扫描比使用索引更高效，可通过修改SQL条件、优化索引（如更换索引字段）解决。
• Key字段显示的索引的长度（Key_len）：Key_len越小，说明索引使用越充分，查询效率越高，可通过Key_len判断联合索引的使用情况（如联合索引(A,B,C)，若Key_len仅对应A字段的长度，说明只使用了联合索引的最左前缀A字段）。

（4）Rows字段

表示MySQL根据索引选择情况，估算的“查找数据所需读取的行数”，是判断索引优化效果的重要参考，数值越小，执行效率越高。该字段是估算值（非实际读取行数），但误差较小，可用于对比优化前后的效果（如优化前Rows为10000，优化后为100，说明索引优化效果显著）。

补充：若Rows数值远大于实际返回的行数（Rows_sent），说明SQL扫描了大量无用数据，大概率是索引失效或WHERE条件筛选性较差，需优化索引或SQL条件。

（5）Extra字段（补充重点，提升分析准确性）

Extra字段表示MySQL执行SQL时的额外信息，包含大量关键优化线索，常见值及解读如下（重点关注异常值）：

• Using index：使用了覆盖索引（查询的字段全部包含在索引中，无需访问数据表），性能优秀，无需优化。
• Using where：使用了WHERE条件筛选数据，但未使用索引（Type为All），需添加索引优化。
• Using index condition：使用了索引筛选条件（ICP优化），性能较好，无需额外优化。
• Using filesort：MySQL无法利用索引进行排序，需手动对结果集进行排序，性能较差，需优化索引（如添加排序字段的索引）。
• Using temporary：MySQL为了执行SQL，创建了临时表存储中间结果（如GROUP BY、DISTINCT未使用索引），性能较差，需优化索引或SQL写法。
• Using join buffer：多表连接时，使用了连接缓冲区，说明连接条件未使用索引，需添加连接字段的索引。

三、联合索引与最左前缀规则

实际业务中，单一字段索引往往无法满足复杂查询需求（如多字段组合查询：WHERE A = 1 AND B = 2），因此会使用联合索引（一个索引同时作用于多个字段）。联合索引的查询效率高于多个单一字段索引，但使用需严格遵循“最左前缀规则”，否则会导致索引失效，触发全表扫描，引发慢查询，这是MySQL索引优化中最常见的坑点之一。

1. 联合索引定义

联合索引（复合索引）是指基于多个字段创建的索引，本质是将多个字段的值组合成一个索引键，存储在索引树中，用于优化多字段组合查询的效率。联合索引的创建需遵循“高频查询字段优先、筛选性强的字段优先”原则，最大化利用索引的筛选效果。

示例：在User表的A字段（用户ID）、B字段（用户名）、C字段（手机号）上创建联合索引，其中A字段查询频率最高、筛选性最强（每个用户ID唯一），B字段次之，C字段最低：

-- 创建联合索引（字段顺序：A > B > C，结合查询频率和筛选性）
CREATE INDEX my_index ON User(A, B, C);
-- 查看表中所有索引
SHOW INDEX FROM User;

补充：联合索引的存储顺序是按创建时的字段顺序排列（A→B→C），查询时会按该顺序匹配，因此字段顺序直接影响索引的使用效果，不可随意调整。

2. 最左前缀规则

联合索引检索数据时，会从索引的最左边字段开始匹配（与SQL语句中WHERE条件的字段顺序无关），匹配到最左前缀字段后，再依次匹配后续字段；如果最左边的字段无法匹配（即WHERE条件中未包含最左前缀字段），MySQL会放弃使用该联合索引，直接触发全表扫描。

核心口诀：最左前缀必匹配，中间字段不跳过，字段顺序无影响（指SQL中WHERE条件的字段顺序，不影响索引匹配，只要包含最左前缀字段即可）。

以联合索引my_index(A, B, C)为例，以下是不同SQL是否使用索引的判断示例，清晰体现最左前缀规则，同时补充实操避坑点：

SQL语句	是否使用my_index索引	原因说明（含避坑点）
SELECT * FROM User WHERE A = 1	是	匹配联合索引最左前缀A字段，可正常使用索引（仅利用A字段部分索引，效率略低于匹配全部字段）。
SELECT * FROM User WHERE B = 2	否	未匹配最左前缀A字段，索引失效，触发全表扫描（常见坑点：误以为只要包含索引字段就会使用索引）。
SELECT * FROM User WHERE C = 3	否	未匹配最左前缀A字段，索引失效，触发全表扫描（与上一条同理，跳过最左前缀字段，索引必失效）。
SELECT * FROM User WHERE A = 1 AND B = 2	是	匹配最左前缀A字段，且继续匹配后续B字段，使用索引的A、B部分，筛选效果优于仅匹配A字段。
SELECT * FROM User WHERE A = 1 AND C = 3	是	匹配最左前缀A字段，可使用索引（仅利用A字段部分索引）；C字段未使用索引（跳过B字段，不影响A字段的索引匹配）。
SELECT * FROM User WHERE B = 2 AND C = 3	否	未匹配最左前缀A字段，即使包含B、C两个索引字段，索引仍失效，触发全表扫描。
SELECT * FROM User WHERE B = 2 AND A = 1	是	虽SQL中B字段在A字段前，但包含最左前缀A字段，索引正常使用（体现“字段顺序无影响”）。
SELECT * FROM User WHERE A = 1 AND B > 2 AND C = 3	是	匹配最左前缀A字段，B字段使用范围查询（>），C字段无法使用索引（范围查询后，后续字段索引失效）。

3. 联合索引使用注意事项

• 联合索引的字段顺序需结合业务查询场景，将查询频率最高、筛选性最强的字段放在最左边（最左前缀字段），最大化利用索引。例如：若业务中高频查询是“WHERE A = 1 AND B = 2”，低频查询是“WHERE A = 1”，则联合索引顺序设为(A, B, C)；若高频查询是“WHERE A = 1 AND C = 3”，则可调整顺序为(A, C, B)。
• 避免“跳过最左前缀字段”查询，否则会导致索引失效，反而降低查询效率。例如：联合索引(A, B, C)，避免写“WHERE B = 2 AND C = 3”这类无A字段的查询。
• 若业务中存在“B = 2 AND C = 3”这类无A字段的高频查询，可单独为B、C字段创建新的联合索引（B, C），避免全表扫描；但需注意：索引越多，插入、更新、删除操作的效率越低，需平衡查询和写操作的性能。
• 联合索引中，若某个字段使用范围查询（如>、<、between、in），则该字段后续的索引字段会失效（如上述示例中，A = 1 AND B > 2 AND C = 3，C字段无法使用索引），可将范围查询字段放在联合索引的最右侧，减少索引失效的影响。
• 避免过度创建联合索引（如为每一个字段组合都创建联合索引），会增加数据库的存储压力，同时降低写操作（INSERT、UPDATE、DELETE）的效率，建议只创建高频查询场景所需的联合索引。

四、慢查询优化核心总结

慢查询优化的核心逻辑是：先定位慢查询，再分析执行计划，最后优化SQL和索引，形成“定位-分析-优化-验证”的闭环，以下是可直接落地的核心步骤，补充优化后的验证方法：

1. 开启慢查询日志，抓取执行时间过长的SQL，明确优化目标；同时通过show processlist实时监控正在执行的慢查询，及时终止异常SQL，缓解数据库压力。 验证：优化后，慢查询数量（show global status like 'Slow_queries'）不再持续增长，实时无长时间执行的SQL。
2. 使用EXPLAIN分析慢查询执行计划，重点关注Type（避免All全表扫描）、Key（确保使用索引）、Rows（减少读取行数）、Extra（避免Using filesort、Using temporary），定位性能瓶颈。 验证：优化后，Type字段提升（如从All变为Ref、Range），Rows数值大幅减少，Extra字段无异常值。
3. 合理设计索引，复杂查询使用联合索引，严格遵循最左前缀规则，避免索引失效；同时删除无用索引（如未使用的单一字段索引、重复索引），平衡查询和写操作性能。 验证：优化后，SQL执行时间明显缩短（低于预设的long_query_time阈值），索引使用率提升。
4. 优化SQL语句，避免全表扫描、避免过度使用SELECT *（只查询需要的字段，尽量使用覆盖索引）、优化WHERE条件（避免函数操作索引列、避免NULL值判断、避免like '%张'这类模糊查询）、优化多表连接（确保连接字段有索引，避免交叉连接）。 示例：优化前（索引失效）：SELECT * FROM User WHERE DATE(create_time) = '2024-01-01'； 优化后（使用索引）：SELECT * FROM User WHERE create_time BETWEEN '2024-01-01 00:00:00' AND '2024-01-01 23:59:59'。
5. 优化后验证：执行优化后的SQL，使用EXPLAIN查看执行计划，确认索引正常使用、Type提升、Rows减少；同时查看SQL执行时间（通过EXPLAIN ANALYZE，MySQL 8.0+支持，可查看实际执行时间），确保满足业务响应要求。

五、常见慢查询优化案例

结合实际业务中最常出现的慢查询场景，补充详细的问题分析、优化方案、优化前后对比（执行计划/执行时间），每个案例均附带实操SQL，便于直接参考复用，覆盖全表扫描、索引失效、排序分组低效、多表连接、大数据量分页等核心场景。

案例1：全表扫描（Type=All）—— 无索引或索引未使用

场景：User表（100万条数据），查询“手机号为138xxxx8888的用户信息”，执行缓慢，执行时间约8秒，超过慢查询阈值（1秒）。

问题SQL：SELECT * FROM User WHERE phone = '138xxxx8888';

问题分析：执行EXPLAIN分析，Type=All（全表扫描），Possible_keys=NULL（无可用索引），Rows≈1000000（需扫描全表所有数据），Extra=Using where（仅用WHERE筛选，无索引支撑），导致扫描行数过多，耗时过长。

优化方案：为查询条件字段（phone）创建单一字段索引，利用索引快速定位匹配数据，避免全表扫描。

优化SQL：CREATE INDEX idx_user_phone ON User(phone); -- 创建索引

优化前后对比：

• 优化前：执行时间≈8秒，Type=All，Rows≈1000000，无索引；
• 优化后：执行时间≈0.01秒，Type=Ref，Rows≈1（仅扫描1行匹配数据），Key=idx_user_phone，Extra=NULL。

避坑点：若phone字段重复率极高（如大量用户共用同一手机号，实际业务中极少出现），MySQL优化器可能判定全表扫描比索引更高效，此时可通过FORCE INDEX强制使用索引（不推荐长期使用，优先优化字段筛选性）。

案例2：索引失效（Possible_keys有值，Key=NULL）—— 函数操作索引列

场景：Order表（50万条数据），查询“2024年1月1日创建的订单列表”，该表已为create_time字段创建索引（idx_order_create_time），但执行仍缓慢，执行时间约6秒。

问题SQL：SELECT order_id, order_no, total_amount FROM Order WHERE DATE(create_time) = '2024-01-01';

问题分析：执行EXPLAIN分析，Possible_keys=idx_order_create_time（有候选索引），但Key=NULL（未使用索引），Type=All（全表扫描），原因是WHERE条件中对索引列create_time使用了DATE()函数，导致索引失效（MySQL无法对函数处理后的索引列进行匹配）。

优化方案：避免对索引列使用函数操作，将函数逻辑转换为对查询值的处理，让MySQL能够正常使用索引进行范围匹配。

优化SQL：SELECT order_id, order_no, total_amount FROM Order WHERE create_time BETWEEN '2024-01-01 00:00:00' AND '2024-01-01 23:59:59';

优化前后对比：

• 优化前：执行时间≈6秒，Type=All，Key=NULL，Rows≈500000；
• 优化后：执行时间≈0.05秒，Type=Range，Key=idx_order_create_time，Rows≈1200（仅扫描当天订单数据）。

延伸：除了DATE()函数，以下操作也会导致索引失效——索引列使用算术运算（如price+10 > 100）、使用IS NULL/IS NOT NULL（索引不存储NULL值）、使用NOT IN（优先用IN或 EXISTS替代）。

案例3：Using filesort/Using temporary —— 排序、分组未使用索引

场景：Goods表（80万条数据），查询“分类为‘电子产品’的商品，按价格降序排列，取前20条”，执行时间约7秒，EXPLAIN显示Extra=Using where; Using filesort。

问题SQL：SELECT goods_id, goods_name, price FROM Goods WHERE category = '电子产品' ORDER BY price DESC LIMIT 20;

问题分析：category字段有索引（idx_goods_category），但排序字段price无索引，MySQL无法利用索引进行排序，需手动对查询结果集进行文件排序（Using filesort），文件排序耗时极高；若同时存在GROUP BY操作，还会创建临时表（Using temporary），进一步降低效率。

优化方案：创建联合索引，包含筛选字段（category）和排序字段（price），利用联合索引的有序性，避免文件排序（联合索引遵循最左前缀规则，筛选字段在前，排序字段在后）。

优化SQL：CREATE INDEX idx_goods_category_price ON Goods(category, price DESC); -- 排序字段按查询需求指定升序/降序

优化前后对比：

• 优化前：执行时间≈7秒，Type=Ref，Extra=Using where; Using filesort，Rows≈15000；
• 优化后：执行时间≈0.02秒，Type=Ref，Extra=Using index（覆盖索引，无需访问数据表），无filesort和temporary。

注意：联合索引中排序字段的顺序（ASC/DESC）需与SQL中的ORDER BY一致，否则仍会触发文件排序；若SQL中同时有GROUP BY和ORDER BY，建议将两个操作的字段都包含在联合索引中。

案例4：多表连接慢 —— 连接字段无索引、连接方式不合理

场景：Order表（50万条）、User表（100万条），查询“用户ID为1001的所有订单，包含用户名和订单信息”，执行时间约9秒，EXPLAIN显示Extra=Using join buffer (hash join)。

问题SQL：SELECT o.order_id, o.order_no, u.user_name FROM Order o LEFT JOIN User u ON o.user_id = u.id WHERE o.user_id = 1001;

问题分析：多表连接的核心是“连接字段有索引”，此处User表的id字段为主键（有索引），但Order表的user_id字段无索引，导致MySQL无法通过索引匹配连接数据，只能使用连接缓冲区（join buffer）进行哈希连接，效率极低；同时LEFT JOIN会扫描左表全部数据（Order表），进一步增加耗时。

优化方案：1. 为Order表的连接字段user_id创建索引；2. 若业务允许，将LEFT JOIN改为INNER JOIN（仅返回匹配数据，避免扫描左表全部数据）。

优化SQL：

• 1. CREATE INDEX idx_order_user_id ON Order(user_id); -- 为连接字段创建索引
• 2. SELECT o.order_id, o.order_no, u.user_name FROM Order o INNER JOIN User u ON o.user_id = u.id WHERE o.user_id = 1001;

优化前后对比：

• 优化前：执行时间≈9秒，Type=All（Order表），Extra=Using join buffer (hash join)，Rows≈500000；
• 优化后：执行时间≈0.03秒，Type=Ref（两张表均使用索引），Extra=NULL，Rows≈20（仅扫描用户1001的订单）。

案例5：大数据量分页慢 —— LIMIT offset过大

场景：Article表（200万条数据），分页查询第1000页数据（每页20条），执行时间约10秒，SQL为：SELECT article_id, title, content FROM Article LIMIT 19980, 20;

问题分析：LIMIT 19980, 20的逻辑是“扫描前19999条数据，丢弃前19980条，返回最后20条”，offset越大，扫描的无用数据越多，耗时越长；若同时使用ORDER BY，还会触发文件排序，耗时翻倍。

优化方案：利用索引有序性，通过“主键/索引字段过滤”替代offset，减少扫描的无用数据（核心：让MySQL直接定位到分页起始位置，无需扫描前面的所有数据）。

优化SQL：

• -- 方案1：主键自增，适合主键连续的场景
• SELECT article_id, title, content FROM Article WHERE article_id > 19980 LIMIT 20;
• -- 方案2：非主键索引，适合主键不连续的场景（需为排序字段创建索引）
• SELECT a.article_id, a.title, a.content FROM Article a INNER JOIN (SELECT article_id FROM Article ORDER BY create_time DESC LIMIT 19980, 20) b ON a.article_id = b.article_id;

优化前后对比：

• 优化前：执行时间≈10秒，Type=All，Extra=Using filesort（若有ORDER BY），Rows≈2000000；
• 优化后：执行时间≈0.08秒，Type=Range，Key=主键/索引，Rows≈20000（仅扫描分页所需数据）。

案例6：联合索引失效 —— 违反最左前缀规则

场景：User表（100万条数据），已创建联合索引my_index(A, B, C)，查询“B=2 AND C=3的用户”，执行时间约8秒，EXPLAIN显示Type=All，Key=NULL。

问题SQL：SELECT * FROM User WHERE B = 2 AND C = 3;

问题分析：联合索引my_index(A, B, C)遵循最左前缀规则，查询条件中未包含最左前缀字段A，导致联合索引失效，触发全表扫描；即使包含B、C两个索引字段，也无法使用该联合索引。

优化方案：1. 若该查询为高频查询，单独为B、C字段创建新的联合索引（idx_user_b_c）；2. 若业务允许，在查询条件中添加A字段的筛选（如A IS NOT NULL，需确保A字段无大量NULL值）。

优化SQL：CREATE INDEX idx_user_b_c ON User(B, C);

优化前后对比：

• 优化前：执行时间≈8秒，Type=All，Key=NULL，Rows≈1000000；
• 优化后：执行时间≈0.06秒，Type=Ref，Key=idx_user_b_c，Rows≈800（仅扫描B=2且C=3的数据）。

避坑点：联合索引的最左前缀字段不可跳过，若高频查询场景中存在“跳过最左前缀”的情况，需单独创建对应联合索引，避免过度依赖原有索引。

六、优化补充总结

所有慢查询优化的核心都是“减少扫描行数、利用索引提升匹配效率”，优化后需通过EXPLAIN验证执行计划，通过执行时间确认优化效果，同时平衡查询性能和写操作（INSERT/UPDATE/DELETE）性能，避免索引过多导致写操作变慢。

核心优化要点提炼：

• 全表扫描（Type=All）→ 优化方案：为WHERE条件中的字段添加索引（单一字段索引或联合索引）；
• 索引失效（Possible_keys有值，Key=NULL）→ 优化方案：避免函数操作索引列、调整SQL条件，确保遵循联合索引最左前缀规则；
• Using filesort/Using temporary → 优化方案：为排序、分组字段添加索引，避免无索引的GROUP BY、DISTINCT操作；
• 多表连接慢 → 优化方案：为连接字段添加索引，避免交叉连接，优先使用INNER JOIN（比LEFT JOIN效率高，若业务允许）。