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

前言你写的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~100x1.3 rows字段最直观的性能指标-- rows表示MySQL预估要扫描的行数 -- 1000万行数据的表rows1000000说明没用上索引 -- 慢查询示例 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; -- 场景3LIKE前缀通配符 SELECT * FROM users WHERE name LIKE %张%; -- 解决方案如需模糊查询建议用全文索引或ES SELECT * FROM users WHERE name LIKE 张%; -- 只有后缀通配符能用索引 -- 场景4隐式类型转换 -- phone字段是varchar但传了数字 SELECT * FROM users WHERE phone [phone]; -- 解决方案 SELECT * FROM users WHERE phone [phone]; -- 加引号 -- 场景5OR条件某个字段无索引 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大数据量时-- 问题场景查找有订单的用户 -- 方法1IN子查询返回大量数据时很慢 SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE status paid); -- 方法2EXISTS通常更快 SELECT * FROM users u WHERE EXISTS ( SELECT 1 FROM orders o WHERE o.user_id u.id AND o.status paid ); -- 规律 -- 外表小、内表大 → 用EXISTS -- 外表大、内表小 → 用IN -- 都大 → 用JOIN3.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: typeref, rows≈15, ExtraUsing 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(hostlocalhost, userroot, passwordpwd, dbmydb) 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→JOINCTE 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看执行计划 → 找typeALL的问题 2. 加索引WHERE/JOIN/ORDER BY字段→ type从ALL变range/ref 3. 改写查询IN→JOINSELECT*→具体字段 4. 大表用预聚合/游标分页 5. 验证再次EXPLAIN对比rows数量 耗时对比 原始查询18秒 加索引后2.1秒 改写查询0.03秒 总提升600倍六、核心知识速查表EXPLAIN type值速查const → 主键/唯一索引单行查询 | eq_ref → JOIN唯一匹配 | ref → 普通索引等值查询 | range → 索引范围扫描 | index → 全索引扫描 | ALL → 全表扫描⚠️需优化索引设计原则① 区分度高的列优先加索引 ② 联合索引遵循最左前缀 ③ 不对索引列做函数操作 ④ 不用LIKE %前缀通配符常用优化技巧① SELECT * → 具体列名 ② LIMIT大偏移 → 游标分页 ③ UNION → UNION ALL ④ 大量IN → JOIN ⑤ 每日聚合 → 预计算汇总表⭐ 更多SQL实战教程关注「船长Talk」公众号 每周更新SQL/Python/数据分析干货职场真相投资洞察 微信搜索「船长Talk」或扫描文末二维码关注系列文章推荐 SQL零基础入门10个语句解决80%查询问题 SQL窗口函数完整指南5大高频场景 SQL多表查询JOIN的7种用法详解