当前位置：首页 > news >正文

SQL性能优化实战：从慢查询到秒开（详细代码注释）

news 2026/4/26 4:12:05

前言

你写的SQL跑了30秒，老板催你，客户等着。

然后你把索引加上，1秒搞定。

这不是玄学，是有方法论的。

本文覆盖SQL性能优化最核心的5个方向：

✅ 读懂EXPLAIN执行计划

✅ 索引的正确姿势（和常见误区）

✅ 查询改写技巧

✅ 大表优化策略

✅ 千万级数据实战案例

💡 文末有关注船长Talk公众号的方式，每周更新数据分析实战教程，不定期分享面试真题。

一、读懂EXPLAIN：优化的起点

优化SQL的第一步：先看执行计划，不要盲目加索引。

1.1 基本用法

-- 在任意SELECT前加EXPLAIN EXPLAIN SELECT * FROM orders WHERE user_id = 1001; -- 更详细的信息（MySQL 8.0+） EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 1001;

1.2 关键字段解读

EXPLAIN输出的关键列：

-- EXPLAIN输出示例 +----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+-------------+ | 1 | SIMPLE | orders | NULL | ALL | NULL | NULL | NULL | NULL | 998234 | 10.00 | Using where | +----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+-------------+

最重要的字段：type（访问类型）

type值从最优到最差排列： const → 最优：通过主键/唯一索引查1条记录 eq_ref → 很优：join时每个组合最多1条 ref → 好：非唯一索引等值查询 range → 一般：索引范围扫描（BETWEEN/>/< ） index → 一般：全索引扫描 ALL → 最差：全表扫描（优化目标：避免ALL）

-- 实战：看到ALL就要警惕 EXPLAIN SELECT * FROM orders WHERE create_time > '2026-01-01'; -- type: ALL → 说明create_time没有索引，需要添加 -- 加索引后 ALTER TABLE orders ADD INDEX idx_create_time (create_time); -- 再次EXPLAIN EXPLAIN SELECT * FROM orders WHERE create_time > '2026-01-01'; -- type: range → 从全表扫描变为范围索引扫描，速度提升10x~100x

1.3 rows字段：最直观的性能指标

-- rows表示MySQL预估要扫描的行数 -- 1000万行数据的表，rows=1000000说明没用上索引 -- 慢查询示例 EXPLAIN SELECT * FROM orders WHERE user_id = 1001 AND status = 'paid'; -- rows: 998234 → 扫了100万行，说明user_id或status没有索引 -- 加联合索引 ALTER TABLE orders ADD INDEX idx_user_status (user_id, status); -- 再次EXPLAIN EXPLAIN SELECT * FROM orders WHERE user_id = 1001 AND status = 'paid'; -- rows: 12 → 只扫12行，性能提升约8万倍

二、索引设计：加对才有效

2.1 索引基本原则

-- ✅ 应该加索引的情况 -- 1. WHERE条件字段 SELECT * FROM orders WHERE user_id = ?; -- → ALTER TABLE orders ADD INDEX idx_user_id (user_id); -- 2. JOIN关联字段 SELECT o.*, u.name FROM orders o JOIN users u ON o.user_id = u.id; -- → users.id通常是主键已有索引 -- → orders.user_id需要加索引 -- 3. ORDER BY字段（避免filesort） SELECT * FROM orders ORDER BY create_time DESC LIMIT 10; -- → ALTER TABLE orders ADD INDEX idx_create_time (create_time); -- 4. GROUP BY字段 SELECT user_id, COUNT(*) FROM orders GROUP BY user_id; -- → ALTER TABLE orders ADD INDEX idx_user_id (user_id);

2.2 联合索引：最左前缀原则

-- 建立联合索引（顺序很重要） ALTER TABLE orders ADD INDEX idx_user_status_time (user_id, status, create_time); -- ✅ 能用上索引的查询（满足最左前缀） SELECT * FROM orders WHERE user_id = 1001; -- 用上了idx_user_status_time的第一列 SELECT * FROM orders WHERE user_id = 1001 AND status = 'paid'; -- 用上了前两列 SELECT * FROM orders WHERE user_id = 1001 AND status = 'paid' AND create_time > '2026-01-01'; -- 用上了全部三列 -- ❌ 用不上索引的查询（跳过了第一列） SELECT * FROM orders WHERE status = 'paid'; -- 跳过user_id，无法使用联合索引 SELECT * FROM orders WHERE create_time > '2026-01-01'; -- 跳过user_id和status，无法使用联合索引 -- 🔑 口诀：联合索引使用时，不能跳过中间的列

2.3 索引失效：这些写法会让索引白加

-- ❌ 索引失效场景（务必避免） -- 场景1：对索引列做函数操作 SELECT * FROM orders WHERE YEAR(create_time) = 2026; -- 解决方案： SELECT * FROM orders WHERE create_time BETWEEN '2026-01-01' AND '2026-12-31'; -- 场景2：对索引列做运算 SELECT * FROM orders WHERE user_id + 1 = 1002; -- 解决方案： SELECT * FROM orders WHERE user_id = 1001; -- 场景3：LIKE前缀通配符 SELECT * FROM users WHERE name LIKE '%张%'; -- 解决方案（如需模糊查询建议用全文索引或ES）： SELECT * FROM users WHERE name LIKE '张%'; -- 只有后缀通配符能用索引 -- 场景4：隐式类型转换 -- phone字段是varchar，但传了数字 SELECT * FROM users WHERE phone = 13800138000; -- 解决方案： SELECT * FROM users WHERE phone = '13800138000'; -- 加引号 -- 场景5：OR条件（某个字段无索引） SELECT * FROM orders WHERE user_id = 1001 OR amount > 1000; -- user_id有索引，amount没有 → 整个条件走全表 -- 解决方案：给amount也加索引，或改用UNION SELECT * FROM orders WHERE user_id = 1001 UNION SELECT * FROM orders WHERE amount > 1000;

三、查询改写：换个写法，快10倍

3.1 用EXISTS替代IN（大数据量时）

-- 问题场景：查找有订单的用户 -- 方法1：IN（子查询返回大量数据时很慢） SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE status = 'paid'); -- 方法2：EXISTS（通常更快） SELECT * FROM users u WHERE EXISTS ( SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.status = 'paid' ); -- 规律： -- 外表小、内表大 → 用EXISTS -- 外表大、内表小 → 用IN -- 都大 → 用JOIN

3.2 避免SELECT *，只取需要的列

-- ❌ 低效写法 SELECT * FROM orders WHERE user_id = 1001; -- ✅ 高效写法（只取需要的列） SELECT id, amount, status, create_time FROM orders WHERE user_id = 1001; -- 原因： -- 1. 减少数据传输量 -- 2. 可能触发"覆盖索引"（只读索引，不回表） -- 覆盖索引示例 -- idx_user_id_amount索引包含(user_id, amount) -- 查询只要这两列，就不需要回表读数据页 SELECT user_id, amount FROM orders WHERE user_id = 1001; -- EXPLAIN Extra字段会显示：Using index → 触发覆盖索引

3.3 深度分页优化

-- 问题：LIMIT偏移量很大时极慢 -- ❌ 低效（扫了10001行，只返回10行） SELECT * FROM orders ORDER BY id LIMIT 100000, 10; -- ✅ 方法1：游标分页（已知上一页最后一条ID） SELECT * FROM orders WHERE id > 99999 -- 上一页最后一条id ORDER BY id LIMIT 10; -- ✅ 方法2：子查询定位ID（先定位，再取数据） SELECT * FROM orders WHERE id >= ( SELECT id FROM orders ORDER BY id LIMIT 100000, 1 ) ORDER BY id LIMIT 10; -- 性能对比（1000万数据）： -- LIMIT 100000,10 → 约2.3秒 -- 游标分页 → 约0.01秒（提升200倍）

3.4 用UNION ALL替代UNION（无需去重时）

-- ❌ UNION会排序去重，多一步操作 SELECT user_id FROM orders WHERE status = 'paid' UNION SELECT user_id FROM returns WHERE status = 'approved'; -- ✅ 确定无重复数据时用UNION ALL SELECT user_id FROM orders WHERE status = 'paid' UNION ALL SELECT user_id FROM returns WHERE status = 'approved'; -- UNION ALL比UNION快约30%（无需排序去重）

四、大表优化策略

4.1 分页查询 + 覆盖索引实战

-- 场景：订单表5000万条，按用户查最近订单 -- 建索引：(user_id, create_time DESC) ALTER TABLE orders ADD INDEX idx_user_time (user_id, create_time); -- 查询 SELECT id, amount, status, create_time FROM orders WHERE user_id = 1001 ORDER BY create_time DESC LIMIT 20; -- EXPLAIN: type=ref, rows≈15, Extra=Using index → 完美

4.2 批量插入替代逐条插入

-- ❌ 低效：逐条插入（每次都有事务开销） INSERT INTO order_items (order_id, product_id, qty) VALUES (1, 101, 2); INSERT INTO order_items (order_id, product_id, qty) VALUES (1, 102, 1); INSERT INTO order_items (order_id, product_id, qty) VALUES (1, 103, 3); -- ✅ 高效：批量插入（一次事务） INSERT INTO order_items (order_id, product_id, qty) VALUES (1, 101, 2), (1, 102, 1), (1, 103, 3); -- Python批量插入示例 import pymysql conn = pymysql.connect(host='localhost', user='root', password='pwd', db='mydb') cursor = conn.cursor() data = [ (1, 101, 2), (1, 102, 1), (1, 103, 3), # 更多数据... ] # executemany比多次execute快3-10倍 cursor.executemany( "INSERT INTO order_items (order_id, product_id, qty) VALUES (%s, %s, %s)", data ) conn.commit() cursor.close() conn.close()

4.3 千万级数据统计优化

-- 场景：订单表1000万条，每天统计GMV -- ❌ 直接聚合（全表扫描） SELECT DATE(create_time) as date, SUM(amount) as gmv FROM orders WHERE create_time >= '2026-01-01' GROUP BY DATE(create_time); -- 耗时：约45秒 -- ✅ 优化方案1：预聚合表 -- 每天凌晨跑一次，把结果存到汇总表 CREATE TABLE daily_gmv ( stat_date DATE PRIMARY KEY, gmv DECIMAL(15,2), order_count INT, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); -- 每日更新脚本 INSERT INTO daily_gmv (stat_date, gmv, order_count) SELECT DATE(create_time), SUM(amount), COUNT(*) FROM orders WHERE create_time >= CURDATE() - INTERVAL 1 DAY AND create_time < CURDATE() GROUP BY DATE(create_time) ON DUPLICATE KEY UPDATE gmv = VALUES(gmv), order_count = VALUES(order_count), updated_at = NOW(); -- 查询直接走汇总表 SELECT stat_date, gmv FROM daily_gmv WHERE stat_date >= '2026-01-01' ORDER BY stat_date; -- 耗时：0.001秒（提升45000倍）

五、综合实战：慢查询从18秒优化到0.03秒

场景描述

一个电商平台，订单表orders约800万条，用户行为表user_events约5000万条。

有一个核心报表查询：找出近30天内下单金额超过1000元且最近有活跃行为的用户。

-- 原始查询（耗时18秒） SELECT DISTINCT u.id, u.name, u.email FROM users u WHERE u.id IN ( SELECT user_id FROM orders WHERE create_time >= DATE_SUB(NOW(), INTERVAL 30 DAY) AND amount > 1000 ) AND u.id IN ( SELECT user_id FROM user_events WHERE event_time >= DATE_SUB(NOW(), INTERVAL 7 DAY) );

-- 优化步骤 -- Step1: EXPLAIN分析 EXPLAIN SELECT DISTINCT u.id, u.name, u.email ...; -- 发现： -- orders.create_time无索引（type: ALL） -- orders.amount无索引（type: ALL） -- user_events.event_time无索引（type: ALL） -- Step2: 加索引 ALTER TABLE orders ADD INDEX idx_time_amount (create_time, amount); ALTER TABLE user_events ADD INDEX idx_user_time (user_id, event_time); -- Step3: 改写查询（IN→JOIN+CTE） WITH active_orders AS ( SELECT DISTINCT user_id FROM orders WHERE create_time >= DATE_SUB(NOW(), INTERVAL 30 DAY) AND amount > 1000 ), recent_active AS ( SELECT DISTINCT user_id FROM user_events WHERE event_time >= DATE_SUB(NOW(), INTERVAL 7 DAY) ) SELECT u.id, u.name, u.email FROM users u INNER JOIN active_orders ao ON u.id = ao.user_id INNER JOIN recent_active ra ON u.id = ra.user_id; -- 优化结果：18秒 → 0.03秒（提升600倍）

优化总结

优化路径： 1. EXPLAIN看执行计划 → 找type=ALL的问题 2. 加索引（WHERE/JOIN/ORDER BY字段）→ type从ALL变range/ref 3. 改写查询（IN→JOIN，SELECT*→具体字段） 4. 大表用预聚合/游标分页 5. 验证：再次EXPLAIN，对比rows数量 耗时对比： 原始查询：18秒 加索引后：2.1秒 改写查询：0.03秒 总提升：600倍