数据库慢查询优化完全指南:从一次"数据量一大接口就卡死、加了索引却没用"看懂慢查询治理

2021 年我做一个电商后台,有一个订单列表接口。第一版我做得很省事:写一句 SQL 把订单查出来,按时间倒序、分页返回。开发期和测试环境我测了测——真不错:翻页飞快,几十毫秒就返回。我心里很踏实:"查数据库嘛,写条 SQL 查出来就行了,真慢了,给条件列加个索引不就快了。"可等这个系统真正上线、订单表的数据从几万行涨到几百万行,一串问题冒了出来。第一种最先把我打懵:数据量一大,这个列表接口直接卡死——一次查询要几十秒,接口频频超时。第二种最反直觉:我明明给查询条件的那个列加了索引,可查询还是慢——EXPLAIN 一看,type 那一列写着 ALL,全表扫描,我加的索引压根没被用上。第三种:翻页翻到很后面,LIMIT 1000000, 20,越翻越慢——前几页飞快,翻到第几万页要等十几秒。第四种最隐蔽:有个接口在一个循环里查数据库,一次请求打出去几百条几乎一模一样的 SQL,每一条单看都不慢,可几百条加起来,接口就慢得离谱。我盯着这一连串问题想了很久才彻底想明白,第一版错在一个根本的认知上:我以为"查询慢,就给条件列加个索引,加了就快了"。这句话把"慢查询治理"整个简化成了"加索引"这一个动作。可它不是。慢查询治理是一套有顺序的工程:先用慢查询日志找到真正慢的那几条 SQL,再用 EXPLAIN 看清它到底是怎么执行的,然后才谈得上对症下药——慢的原因可能是没索引,也可能是索引加了却没生效(被函数包裹、类型不匹配、不满足最左前缀),还可能根本不是索引的事(深分页、回表、N+1、大事务)。真正做好慢查询治理,核心不是"加索引",而是理解"先定位、再分析、最后才优化"、读懂 EXPLAIN、知道索引为什么会失效、避开深分页和 N+1 这些真正的性能杀手。这篇文章就把数据库慢查询优化梳理一遍:为什么"加索引就完事"是错的、慢查询日志怎么开和怎么读、EXPLAIN 怎么看、索引为什么加了却不生效、深分页和回表怎么治,以及 N+1、SELECT *、大事务这些把数据库拖慢又最容易被忽略的坑。

问题背景

先把那串问题的现象和我的误判讲清楚,后面所有的设计都是冲着纠正这个误判去的。

现象:一句"写出来就能跑"的查询 SQL,在数据量涨上去之后冒出一串问题:数据量一大,列表接口直接卡死、频频超时;给条件列加了索引,查询却还是全表扫描,索引没被用上;翻页越翻越慢,深分页要等十几秒;一个接口在循环里查库,一次请求打出几百条 SQL。

我当时的错误认知:"查询慢,就给条件列加个索引,加了就快了。"

真相:"加索引"只是慢查询治理的一种手段,而且经常加了不生效。慢查询治理是一条有先后顺序的链路:第一步是定位——打开慢查询日志,让数据库自己把超过阈值的慢 SQL 记下来,你才知道到底是哪几条 SQL 慢,而不是凭感觉猜。第二步是分析——对这几条慢 SQL 执行 EXPLAIN,看清数据库实际是怎么执行它的:走没走索引、扫了多少行、要不要回表。第三步才是优化——而优化手段远不止加索引:索引可能因为被函数包裹、隐式类型转换、不满足最左前缀而失效,这时要改的是 SQL 写法;查询慢也可能根本不是索引问题,而是深分页、回表、N+1、SELECT \* 在作怪。不先定位、不看 EXPLAIN 就闷头加索引,十有八九加的是一个用不上的索引——白白占着写入性能和磁盘,慢查询却一点没好。

要把数据库慢查询优化做对,需要几块认知:

• 为什么"加索引就完事"是错的——治理要先定位、再分析、最后才优化;
• 慢查询日志——让数据库自己告诉你哪几条 SQL 真的慢;
• EXPLAIN——看清一条 SQL 到底是怎么执行的;
• 索引失效——加了索引却没生效的几种典型场景;
• 深分页、回表、N+1、SELECT \* 这些工程坑怎么处理。

一、为什么"查询慢就加个索引"是错的

先把这件最根本的事钉死:"加索引"不是一个能闭着眼睛做的动作。索引不是越多越好——每加一个索引,写入就多一份维护成本、磁盘就多占一块;更要命的是,你加的索引,数据库未必会用。一条 SQL 走不走某个索引,是数据库的优化器根据 SQL 的写法、列的类型、数据的分布,自己算出来的决定。你把索引建好了,只是给了优化器一个"选项",至于它选不选,取决于你这条 SQL 有没有写成"让索引能被选中"的样子。不看 EXPLAIN 就加索引,等于蒙着眼睛往黑箱里扔东西——你以为修好了,其实优化器根本没理你那个新索引。

下面这段 SQL,就是我那个"上线就卡死"的第一版订单查询:

-- 反面教材:给 created_at 加了索引,查询却用函数包裹了它
CREATE INDEX idx_created_at ON orders (created_at);

-- 查"某天的订单"—— 条件列被 DATE() 函数包裹了
SELECT * FROM orders
WHERE DATE(created_at) = '2021-06-01'
ORDER BY created_at DESC
LIMIT 20;
-- 破绽一:DATE(created_at) 让 idx_created_at 彻底失效,退化成全表扫描。
-- 破绽二:SELECT * 把每行所有列都捞出来,数据量大时 IO 翻倍。
-- 破绽三:翻到很后面 LIMIT 1000000, 20 会越来越慢。

这段 SQL 在开发期和测试环境表现不错,因为那时订单表才几万行,全表扫描扫几万行也就几十毫秒,慢不到哪去。它的问题不在 SQL 本身,而在一个被忽略的前提:它默认"我建了 idx_created_at 索引,这条查 created_at 的 SQL 就会走它"。可DATE(created_at) 这个函数,让索引彻底用不上了。于是那串问题就有了解释:数据量一大就卡死,是因为索引失效后退化成了全表扫描,扫描行数随数据量线性暴涨;加了索引却没用,是因为函数包裹住了索引列,优化器没法走索引;翻页越翻越慢,是因为深分页要先扫描并丢弃前面成百万行。问题的根子清楚了:做好慢查询治理的工程量,全在"承认优化要先定位、再分析、不能上来就加索引"之后——你不先看清它慢在哪,加的索引就只是个摆设。先从治理的第一步——把慢 SQL 找出来——说起。

二、慢查询日志:先找到真正慢的那条 SQL

治理慢查询,第一步永远不是优化,而是定位。一个稍有规模的系统,SQL 成百上千条,你凭感觉猜"大概是那条慢"是极不可靠的。正确的做法,是让数据库自己把慢 SQL 记下来——这就是慢查询日志。打开它,只需在配置里设几个开关:

# my.cnf —— 开启慢查询日志,这是定位慢 SQL 的第一步
[mysqld]
slow_query_log      = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time     = 1                  # 超过 1 秒的查询,记进慢日志
log_queries_not_using_indexes = 1        # 没用上索引的查询也一并记下

这里 long_query_time 是慢的阈值,log_queries_not_using_indexes 尤其值得开——它会把所有没走索引的查询都揪出来,哪怕它这次还不到 1 秒。日志攒起来之后,不能一行行去翻——要用工具按"总耗时"聚合排序,把结构相同、只是参数不同的 SQL 归成一类:

# mysqldumpslow:按"总耗时"把慢查询日志聚合排序
# -s t 按总时间排序,-t 10 只看最该治理的 10 类
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

# 输出会把结构相同、只是参数不同的 SQL 归成一类:
#   Count: 8217  Time=4.51s (37051s)  ...
#   SELECT * FROM orders WHERE DATE(created_at) = 'S'
# —— Count 高(调用频繁) + Time 长(单次慢)的那几类,优先治理

这里的认知要点是:慢查询治理是一件要"抓主要矛盾"的事。系统里慢的 SQL 可能有几十条,但符合"二八定律"——往往是少数几条,贡献了绝大部分的慢。慢查询日志 + 聚合排序,就是帮你把这少数几条精确地拎出来。你要优先治理的,不是"单次最慢"的那条,而是"调用次数 × 单次耗时"这个总账最高的那几条——一条 0.5 秒但每秒被调几百次的 SQL,比一条 5 秒但一天才跑一次的,危害大得多。慢 SQL 找到了,下一步是看清它到底慢在哪——这要靠 EXPLAIN。

三、读懂 EXPLAIN:看清查询到底怎么执行

找到慢 SQL 之后,千万别急着优化。你得先知道它慢在哪一步——而 EXPLAIN 就是数据库递给你的那张执行计划单。在慢 SQL 前面加上 EXPLAIN,数据库不会真去执行它,而是告诉你它"打算"怎么执行:

-- 在慢 SQL 前加 EXPLAIN,看 MySQL 到底打算怎么执行它
EXPLAIN SELECT * FROM orders
WHERE DATE(created_at) = '2021-06-01';

-- 重点看这几列:
--   type   = ALL      —— 全表扫描,最差;要的是 ref / range
--   key    = NULL     —— 没用上任何索引
--   rows   = 3821004  —— 预计要扫描三百多万行
--   Extra  = Using where —— 在 server 层逐行过滤

这四列里,最该先看的是 type 和 key。type 是访问类型,它从好到坏大致是:const(主键等值,最快)、ref(走非唯一索引等值)、range(走索引的范围扫描)、index(扫整个索引)、ALL(全表扫描,最差)。key 是实际用上的索引,NULL 就代表一个索引都没用上。rows 是预计扫描行数,这个数字越接近表总行数,就越危险。下面这张图,把从"接口变慢"到"优化验证"的完整治理流程串起来:

这里的认知要点是:EXPLAIN 是慢查询治理里唯一不能跳过的一步。它把"这条 SQL 为什么慢"从一道猜谜题,变成了一张明明白白的事实清单——type=ALL 就是没走索引,key=NULL 就是索引没被选中,rows 三百万就是要扫三百万行。优化前不看 EXPLAIN,你做的所有改动都是赌博;优化后不再看一次 EXPLAIN,你都不知道自己的改动到底有没有生效。它既是诊断书,也是验收单。EXPLAIN 最常告诉你的一件事就是"key=NULL,索引没用上"——可你明明加了索引。这就要说到索引失效。

四、索引失效:为什么"加了索引却没用"

"我加了索引,EXPLAIN 里 key 却还是 NULL"——这是慢查询治理中最高频、也最让人困惑的场景。原因是:索引能不能被用上,对 SQL 的写法极其敏感。下面是三种最常见的索引失效场景,以及对应的正确写法:

-- 索引失效的三种最常见场景

-- 场景一:用函数 / 计算包裹索引列 —— 索引失效
SELECT * FROM orders WHERE DATE(created_at) = '2021-06-01';
-- 正确:把范围算好,让索引列"裸露"在条件里
SELECT * FROM orders
WHERE created_at >= '2021-06-01 00:00:00'
  AND created_at <  '2021-06-02 00:00:00';

-- 场景二:隐式类型转换 —— user_id 是 varchar,却用数字去比
SELECT * FROM orders WHERE user_id = 123456;     -- 索引失效
SELECT * FROM orders WHERE user_id = '123456';   -- 正确

-- 场景三:联合索引不满足最左前缀
-- 索引是 (user_id, status),却只用 status 查 —— 用不上
SELECT * FROM orders WHERE status = 'paid';

这三种之外还有几个常见的:LIKE '%xxx' 以通配符开头、用 OR 连接的条件里有一边没索引、对索引列做 隐式或显式的运算。理解了失效场景,反过来就知道该怎么正确地建和用索引了——尤其是联合索引和覆盖索引:

-- 联合索引:把最常一起出现的查询条件,按"区分度高的在前"建成一个
CREATE INDEX idx_user_status_time
    ON orders (user_id, status, created_at);

-- 这个索引能高效服务下面这些查询(都满足最左前缀):
--   WHERE user_id = ?
--   WHERE user_id = ? AND status = ?
--   WHERE user_id = ? AND status = ? AND created_at >= ?

-- 覆盖索引:查询要的列,全都在索引里 —— 连回表都省了
SELECT user_id, status, created_at FROM orders
WHERE user_id = 88 AND status = 'paid';
-- EXPLAIN 的 Extra 会显示 Using index —— 全程不碰主键表

这里的认知要点是:索引不是"建上就生效"的,它是一份你和优化器之间的"潜在约定"——而这份约定,会被一个函数、一次类型不匹配、一个跳过的前导列轻易撕毁。所以加索引从来不是终点,加完之后用 EXPLAIN 确认它真的被选中了,才是。联合索引尤其要记住"最左前缀":它像一本按"省、市、区"三级排好序的通讯录,你可以只查到省、或查到省+市,但你没法跳过省、直接查市。索引的事说清楚了,但还有两类慢——它们跟索引关系不大,却最常被忽略。

五、深分页与回表:两个最常见的性能杀手

开头第三个问题——"翻页越翻越慢"——根子不在索引,在深分页。LIMIT 1000000, 20 的真实含义是:数据库要先扫描出前 1000020 行,再把前面 1000000 行统统丢弃,只为拿最后 20 行。翻得越后,被扫描又被丢弃的行就越多。治它有两种思路:

-- 深分页:LIMIT 1000000, 20 会先扫描并丢弃前一百万行
SELECT * FROM orders ORDER BY id LIMIT 1000000, 20;   -- 越翻越慢

-- 优化一:延迟关联 —— 先在索引上翻页拿到 20 个 id,再回表取整行
SELECT o.* FROM orders o
JOIN (
    SELECT id FROM orders ORDER BY id LIMIT 1000000, 20
) AS page ON o.id = page.id;

-- 优化二:游标分页 —— 记住上一页最后一个 id,直接跳过
-- 彻底告别 OFFSET,每页耗时恒定,与翻到多后面无关
SELECT * FROM orders
WHERE id > 1000000          -- 上一页最后一行的 id
ORDER BY id LIMIT 20;

第二个杀手是回表。在 InnoDB 里,除主键外的索引(二级索引)上,只存了索引列的值和主键。当你用二级索引查到一行、却还要取索引里没有的列时,数据库得拿着主键再去主键表里捞一次完整的行——这个动作就叫回表。SELECT * 几乎必然触发回表,而只取索引覆盖得到的列,就能省掉它:

-- SELECT *:把大字段(如 detail_json、长备注)也一并捞出,且必然回表
SELECT * FROM orders WHERE user_id = 88;

-- 只取真正要用的列 —— 传输和内存都更省
SELECT id, status, amount, created_at
FROM orders WHERE user_id = 88;

-- 更进一步:让"要的列"正好落在某个索引里,构成覆盖索引,
-- 查询连主键表都不必回 —— 这是 SELECT 指定列最大的隐藏收益

这里的认知要点是:深分页和回表,是两个"加索引也救不了"的慢——它们慢在"做了大量无用功"。深分页的无用功,是扫描了又丢弃成百万行;回表的无用功,是为了几个字段反复往主键表跑。治深分页,核心是用"游标"取代"偏移量",让翻页不再依赖"跳过前面多少行";治回表,核心是让查询的列尽量被索引覆盖。这两件事的共同精神是:不要让数据库为你不需要的数据付出代价。主链路的优化讲完了,最后是几个真正上规模后才会撞见的工程坑。

六、工程坑:N+1、大事务与持续监控

五块设计之外,还有几个工程坑,不处理就会让数据库要么被拖慢、要么被拖垮、要么你根本不知道它快不快。坑 1:杜绝 N+1 查询。开头第四个问题——"一次请求打出几百条 SQL"——就是经典的 N+1:先查出一个列表(1 次),再在循环里逐个去查每一项的关联数据(N 次)。每条 SQL 单看都不慢,但N 次网络往返加起来,慢得惊人。对策是把 N 次查询合并成一次 IN 批量查询:

# 反面教材:N+1 查询 —— 一次列表请求打出几百条 SQL
def list_orders_bad(user_ids):
    orders = []
    for uid in user_ids:                  # 100 个用户
        # 循环里每轮查一次库 —— 100 次网络往返
        rows = db.query(
            "SELECT id, amount FROM orders WHERE user_id = %s", uid)
        orders.extend(rows)
    return orders


# 正确写法:用一条 IN 查询,把 N 次往返压成 1 次
def list_orders_good(user_ids):
    rows = db.query(
        "SELECT id, amount FROM orders WHERE user_id IN %s",
        (tuple(user_ids),))
    return rows

坑 2:别用大事务、长事务。一个事务里塞进太多操作、或中间夹着耗时的外部调用,会让事务长时间持有行锁,既阻塞别的查询,又给 MVCC 的 undo log 造成压力。事务要短小、只包住真正需要原子性的那几步。坑 3:索引不是越多越好。每个索引都要在每次写入(INSERT / UPDATE / DELETE)时同步维护——索引建得太多,读是快了,写会变慢、磁盘也膨胀。要定期清理那些 EXPLAIN 证明从没被选中的"僵尸索引"。坑 4:慢查询要持续监控,不能治一次就不管。除了翻日志,更实时的办法是查 performance_schema,它会按 SQL 结构归一化地统计每类查询的耗时:

import pymysql

# 用 performance_schema 找出"平均耗时最长"的 SQL 模式
# 它按 SQL 结构归一化统计,比翻日志文件更实时
DIGEST_SQL = """
SELECT digest_text,
       count_star                    AS calls,
       round(avg_timer_wait/1e9, 2)  AS avg_ms,
       round(sum_timer_wait/1e12, 2) AS total_s
FROM performance_schema.events_statements_summary_by_digest
ORDER BY avg_timer_wait DESC
LIMIT 10
"""


def top_slow_queries(conn):
    """揪出平均最慢的 10 类查询 —— 治理要盯住高频又慢的那几条。"""
    with conn.cursor() as cur:
        cur.execute(DIGEST_SQL)
        for digest, calls, avg_ms, total_s in cur.fetchall():
            # avg_ms 高、calls 又大的,就是最该优先优化的
            print(f"avg={avg_ms}ms  calls={calls}  {digest[:60]}")

坑 5:优化要用真实数据量验证。一条 SQL 快不快,和数据量强相关——在几千行的测试库里,全表扫描和走索引都是几毫秒,根本看不出差别。一定要在数据量接近生产的环境里,用 EXPLAIN 和真实计时去验证优化效果,别让"测试环境很快"再一次骗了你。

关键概念速查

避坑清单

1. 查询慢先开慢查询日志定位,别凭感觉猜是哪条 SQL。
2. 优化前必须 EXPLAIN,看清 type、key、rows 再动手。
3. 别用函数或计算包裹索引列,会让索引彻底失效。
4. 注意隐式类型转换,字符串列要用字符串值去比较。
5. 联合索引要满足最左前缀,按区分度高的列在前来建。
6. 能用覆盖索引就用,让查询的列全落在索引里,免去回表。
7. 别滥用 SELECT *,只取真正要用的列,减少回表与传输。
8. 深分页用游标分页或延迟关联,别用大 OFFSET 的 LIMIT。
9. 杜绝循环里查库的 N+1,合并成一条 IN 批量查询。
10. 持续监控慢查询,盯住高频又慢的 SQL 优先治理。

总结

回头看那串"数据量一大就卡死、加了索引却没用、翻页越翻越慢、一次请求几百条 SQL"的问题,以及我后来在慢查询上接连踩的坑,最该记住的不是某一个优化技巧,而是我动手前那个想当然的判断——"查询慢,就给条件列加个索引,加了就快了"。这句话错在它把"慢查询治理"这件需要诊断的事,简化成了"加索引"这一个动作。我以为慢就是缺索引,补上索引就好。可我忽略了一件事:"慢"只是一个症状,而症状背后的病因有很多种。可能是没索引,可能是索引被一个函数废掉了,可能是深分页在做无用功,可能是 N+1 在反复往返——不先诊断就开药,你开的索引这味药,治不好深分页的病,反而白白增加了写入的负担。

所以做好慢查询优化,真正的工程量不在"敲一句 CREATE INDEX"那一行上。那一行,谁都会写。真正的工程量,在于你要承认"优化是先定位、再分析、最后才动手"的一条链路,并老老实实地一步步走完它:你不知道哪条 SQL 慢,就打开慢查询日志,让数据库自己把慢 SQL 告诉你;你不知道它为什么慢,就用 EXPLAIN 看清它走没走索引、扫了多少行;你的索引明明建了却没生效,就去排查是不是函数包裹、类型转换、最左前缀;翻页越来越慢,你就用游标分页取代大 OFFSET;一次请求打出几百条 SQL,你就把 N+1 合并成一条 IN 查询。这篇文章的几节,其实就是顺着这条链路展开的:先想清楚"加索引就完事"为什么错,再讲慢查询日志怎么定位、EXPLAIN 怎么分析、索引为什么失效、深分页和回表怎么治,最后是 N+1、大事务、持续监控这几个把数据库性能守扎实的工程细节。

你会发现,慢查询治理,和现实里"在一座大图书馆里找书"完全相通。这座图书馆有几百万册藏书,书库就是你的数据表。一个不会用图书馆的人会怎么找书?他不查目录,直接钻进书库,一排排书架挨着翻(这就是全表扫描);就算图书馆有目录卡片,他要找的书名却被记成了"作者名 + 书名"的混合格式,跟卡片的排序规则对不上,卡片等于白做(这就是索引被函数包裹而失效);他要看的书在书库最里面那排,每次都得抱着书号跑回前台核对一遍(这就是回表);他要借一百本书,却一本一本地单独去办手续,来回跑了一百趟(这就是 N+1)。而一个会用图书馆的人怎么做?他先查目录卡片,按索书号径直走到那一格(这就是走索引);常翻的工具书,他选那种卡片上就印全了关键信息、不必再进书库的(这就是覆盖索引);要借一百本,他一次性列好清单,统一办理(这就是 IN 批量查询)。同样一座图书馆、同样几百万册书,可前者找一本书要耗上半天,后者几分钟就能精准取到——差别不在书多书少,只在那一套"先查目录、再定位、按需批量取"的章法。

最后想说,数据库查询写没写好,差距永远不会在"开发期一测、几十毫秒就返回"时暴露——开发和测试环境里数据量小,全表扫描扫几万行也很快,索引走不走、SQL 写得糙不糙,根本看不出区别,你会觉得"写条 SQL 查出来"已经是全部。它只在真实的、数据量持续增长、并发持续上升的线上环境里才显形。那时候它会用最伤体验的方式给你结账:做不好,你的接口会随着数据量增长一天比一天慢,直到某天彻底卡死、频频超时,你加的索引还不一定生效;而做对了,你的查询会稳稳地走在索引上:数据量再大,扫描的行数也始终被索引框在很小的范围,翻页翻到多后面都耗时恒定,接口的响应时间不随数据量起伏。所以别等"用户抱怨页面转圈、监控告警库被打满"找上门,在你写下那条 SQL 的时候就该想清楚:它会在几百万行的表上跑,而不是几千行的测试库——它走索引了吗、索引会不会被我的写法废掉、它要不要回表、它会不会在循环里被打成几百条,这一道道工序,我是不是都替它想过了?这些问题有了答案,你写下的才不只是一句"能查出结果"的 SQL,而是一条数据量再涨也扛得住、经得起真实流量考验的可靠查询。

—— 别看了 · 2026