你知道那种感觉吗数据库慢查询日志里躺着一条SQL跑了三秒半接口超时用户疯狂点刷新你疯狂翻代码最后发现罪魁祸首就是一条看起来人畜无害的Python ORM查询。我在过去几年里处理过不少类似的线上事故Python写的后端服务数据库从MySQL到PostgreSQL都碰过慢查询翻来覆去就那么几个套路索引没建对、ORM写法太天真、连接池被拖垮、或者干脆就是一条SQL设计上不合理。这篇东西不是教科书是我实打实的排查笔记。围绕Python生态下的数据库优化从怎么开慢查询日志、怎么看执行计划到索引设计的反直觉之处再到Python侧的连接管理、批量写入、缓存兜底最后配上几组实测数据。不管你是刚接手一个慢得离谱的项目还是想在写SQL的时候少给DBA添麻烦这文章都能让你少走几趟弯路。1. 慢查询到底慢在哪先分清是SQL的问题还是你代码的问题很多Python开发者一遇到接口慢就下意识地去SQL里找毛病结果优化半天没效果。我自己的经验是先确认慢在哪一层再动手改。1.1 慢查询日志是最诚实的告密者MySQL为例慢查询日志默认是关的。开它很简单但我建议你在自己的开发环境里开别在生产直接开日志量容易爆炸。最稳妥的方式是动态开启不用重启进程SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; SET GLOBAL slow_query_log_file /var/log/mysql/mysql-slow.log;long_query_time设成1秒是比较合理的起点。设太短比如0.1秒日志会被一堆正常查询刷屏设太长比如10秒那种“昨晚开始变慢”的慢性问题根本抓不到。日志打开之后每隔一段时间看一眼TOP N。我习惯用mysqldumpslow做汇总mysqldumpslow -s at -t 20 /var/log/mysql/mysql-slow.log这个命令按平均耗时排序把最耗时的20条查询列出来。看到的结果经常让人倒吸一口凉气——排在前面的SQL往往来自Python代码里隐蔽的循环查库而不是单条复杂SQL。我曾经接过一个项目慢查询日志里全是同一条SELECT平均执行0.8秒但每秒出现几十次。乍一看单条不慢但累积起来直接把数据库连接池吃光了。这就引出一个关键判断慢查询日志里看到的同一条SQL大量重复多半是代码层的问题不是SQL本身的问题。1.2 EXPLAIN不是用来装饰的定位到具体SQL之后别急着加索引。先把EXPLAIN跑一遍看看执行计划长什么样。EXPLAIN SELECT * FROM orders WHERE user_id 1024 AND status 1 ORDER BY created_at DESC LIMIT 20;最直观的几个字段type从上到下分别是const、ref、range、index、ALL。看到ALL基本就是全表扫描是慢查询的头号元凶。rows预估扫描的行数。这个数字和实际执行时间强相关不是绝对准但可以作为优化前后的对照。Extra看到Using filesort说明排序没有走索引看到Using temporary说明用了临时表都是可以去优化的信号。拿上面这条SQL来说如果user_id选择性很高但type还是ALL那大概率orders表上连user_id索引都没有。这不是Python代码能解决的必须回到数据库层面建索引。但这里有个反直觉的点有时候SQL的执行计划看着没问题数据库侧也确实不慢但接口就是慢。这时候问题已经不在SQL本身了而在Python代码怎么跟数据库打交道的。下一章我会细讲。2. 索引设计的反直觉真相不是建了就完事建错更坑慢查询优化最常动的就是索引。但我见过太多把索引当万能药的用法结果越优化越糟糕。索引设计有几个反直觉的地方值得单开一章说清楚。2.1 隐式类型转换让索引瞬间失效这是一个非常隐蔽的问题。比如users表里phone字段是varcharPython这边传参是个整数# 错误示范 row cursor.execute(SELECT * FROM users WHERE phone %s, (13800138000,)).fetchone()MySQL在比较的时候会把varchar字段隐式转换成数字直接导致phone上的索引失效每次查询变成全表扫描。我当时排查一个“用户登录偶尔超时”的问题找了一个小时才发现是这里。解决办法很简单参数类型对齐# 正确示范转成字符串 row cursor.execute(SELECT * FROM users WHERE phone %s, (13800138000,)).fetchone()养成习惯写SQL的时候确认一下Python传参的类型跟数据库字段类型一致。这个坑用EXPLAIN看不出来因为执行计划里显示的可能还是ref但实际扫描行数会异常地高。2.2 组合索引的顺序就是你的查询命门组合索引不是随便把几个列塞进去就行。它遵循最左前缀原则如果索引是(a, b, c)那它可以支撑a、a b、a b c这三种查询条件但不能直接支撑只有b或只有c的查询。这带来一个实际建议先把等值查询的列放最左范围查询大于、小于、BETWEEN放后面ORDER BY的列也要考虑进去。举个实际例子CREATE INDEX idx_orders_user_created ON orders(user_id, created_at);这条索引能同时支撑两类查询用户查自己的订单列表WHERE user_id ? ORDER BY created_at DESC排序直接走索引不会出现Using filesort。后台按时间范围捞订单WHERE created_at BETWEEN ? AND ?虽然用不上最左前缀但如果user_id同时作为等值条件created_at的范围过滤还是能用的。反过来如果你建了(created_at, user_id)那第二个查询“WHERE user_id ?”就用不上这个索引等于索引白建了。2.3 覆盖索引才是压榨查询速度的终极武器索引的Extra字段有时候会显示Using index意思是查询的列全部在索引树里不需要回表。这种叫做覆盖索引是查询效率的天花板。假设你的页面只需要展示订单编号和金额# 慢SELECT *回表拉取整行数据 SELECT * FROM orders WHERE user_id 1024; # 快只取索引中的列Using index SELECT order_no, amount FROM orders WHERE user_id 1024;如果这个查询频繁出现可以建一个(用户id, 订单编号, 金额)的组合索引。代价是写入多了一份索引数据换来的是查询几乎瞬时。我在实际项目里把一个频繁执行的统计SQL从200ms压到30ms以内靠的就是覆盖索引。但要强调的是不是所有SELECT都要搞成覆盖索引。只有那种高频、热点查询才值得这么做。低频的后台报表查询回表就回表没必要把索引堆得像字典那么厚。3. 从Python侧下手别让你的代码成为慢查询的帮凶SQL和索引都检查过了但接口还是慢这时候问题大概率在Python代码跟数据库的交互方式上。这一章聊的几个坑几乎每个Python后端项目都会碰到。3.1 N1查询ORM的温柔陷阱用Django ORM或SQLAlchemy的兄弟对这个都不陌生。一个典型的例子# 伪代码查询某个用户组下的所有用户再逐一查每个用户的订单 groups Group.objects.filter(name__icontains测试) for group in groups: users group.user_set.all() for user in users: orders user.order_set.all() ...这段代码看起来逻辑清晰实际上每访问一个user.order_set就产生一条数据库查询。假设有10个组每组20个用户那就是10200条查询数据库不慢才怪。标准解法是select_related和prefetch_relatedDjango里这样写groups Group.objects.filter(name__icontains测试).prefetch_related(user_set__order_set) for group in groups: for user in group.user_set.all(): orders user.order_set.all() ...查询数从几百条降到一条。SQLAlchemy对应的就是joinedload和selectinload。排查这类问题的时候我会在中间件里打印每条查询的SQL和耗时凡是出现“一个页面几十条查询且都是同类SELECT”的直接往N1方向查。3.2 循环里逐条INSERT是我见过最蠢的写法另一个高频事故是批量写入。比如导入Excel数据Python代码里写了个for循环逐条INSERT# 错误示范1000条数据 1000次网络往返 for row in rows: cursor.execute(INSERT INTO items (name, price) VALUES (%s, %s), (row[0], row[1]))每一条INSERT都是一次完整的数据库交互1000条数据就是1000次网络往返。就算每次只有1ms延迟算下来也要1秒多实际往往更慢。优化成批量插入# 正确示范一次交互写入多行 data [(row[0], row[1]) for row in rows] cursor.executemany(INSERT INTO items (name, price) VALUES (%s, %s), data)我用executemany做过测试10万条数据的导入时间从原来的15分钟压到了不到40秒。差距巨大。Django里的bulk_create也是同样的道理。能一次干完的事绝对不要循环里干。这个习惯不仅省数据库的时间还省CPU、省网络IO几乎零成本。3.3 连接池怎么配置才合理Python的数据库连接不像Java那样默认有应用服务器托管很多脚本或者轻量服务直接用pymysql连一次用一次。每次新建连接都要经过TCP握手、MySQL权限校验、建立会话这个开销在低并发时不明显一旦QPS上来就卡脖子。我的做法是给服务加上连接池。用dbutils的PooledDB或者SQLAlchemy自带连接池都行from dbutils.pooled_db import PooledDB import pymysql pool PooledDB( creatorpymysql, maxconnections20, mincached5, blockingTrue, host127.0.0.1, userroot, passwordpassword, databaseshop, )几个参数的含义要理解清楚mincached5启动时预热5个连接防止流量高峰期手忙脚乱地建连接。maxconnections20硬顶防止突发流量把后端数据库打挂。blockingTrue连接用完时让请求排队等待而不是直接报错。很多Python服务的数据库瓶颈根本不是SQL慢而是连接建不过来。把连接池一上接口延迟立刻降一个档次。这个优化我做过无数次属于性价比最高的改动。4. 优化效果实测从秒级到毫秒级的三个真实案例光讲理论不给数据都是耍流氓。这一章我复盘三个之前做过的真实优化案例每个都有前后对比。你在自己项目里遇到类似问题可以直接套用思路。4.1 案例一报表统计查询从十几秒到1秒内背景是订单系统的运营后台要按天统计各渠道的支付金额。原始SQL大概是这样的SELECT DATE(created_at), channel, SUM(amount) FROM orders WHERE created_at 2024-01-01 GROUP BY DATE(created_at), channel;orders表几百万行跑了13秒。EXPLAIN结果是全表扫描因为created_at上的单列索引没能帮上GROUP BY聚合的忙。这里的优化思路不是加索引而是预聚合。我建了一张日汇总表每天凌晨用定时任务跑一次聚合把结果存进去INSERT INTO daily_channel_stats (stat_date, channel, total_amount) SELECT DATE(created_at), channel, SUM(amount) FROM orders WHERE created_at 2024-01-01 GROUP BY DATE(created_at), channel ON DUPLICATE KEY UPDATE total_amount VALUES(total_amount);后台查询的时候直接从daily_channel_stats表读取查询时间变为几十毫秒。报表场景本来就不要求实时与其折磨大表不如让数据提前算好。4.2 案例二深分页导致的慢查询列表页分页Python里的习惯写法是LIMIT OFFSETSELECT * FROM articles ORDER BY id DESC LIMIT 10 OFFSET 100000;表数据多了之后OFFSET越大越慢。因为这个写法会让数据库扫描前100010行再扔掉前100000行。我第一次跑EXPLAIN的时候rows字段显示十万多行扫描心里咯噔一下。优化方法是延迟关联或者游标分页。延迟关联的意思是先只取主键再回表拿完整数据SELECT a.* FROM articles a INNER JOIN (SELECT id FROM articles ORDER BY id DESC LIMIT 10 OFFSET 100000) tmp ON a.id tmp.id;实际测试中同样的深分页查询从1.2秒降到不到100毫秒。如果前端可以接受更推荐游标分页也就是每次把上一页最后一条记录的id传过来SELECT * FROM articles WHERE id 100000 ORDER BY id DESC LIMIT 10;这种写法在任意深度下都是恒定速度真正做到了“飞一般的感觉”。4.3 案例三COUNT(*)统计卡住列表接口一个后台列表页每次打开都要显示总条数于是执行SELECT COUNT(*) FROM orders WHERE status 1。订单表有几百万行这个COUNT走了索引但还是需要遍历所有符合条件的索引项跑了1.8秒。优化的方式分两级如果业务允许近似值用EXPLAIN里的rows估算或者维护一个计数器表。如果必须精确把高频筛选条件的COUNT结果放到Redis里定时更新。我当时的做法是在订单写入时更新Redis里的计数读取时直接取缓存列表接口从1.8秒降到了200毫秒以下。当然这需要接受一定延迟适合后台低实时性场景。5. 数据库优化里那些“看着有用、实际更坑”的操作最后聊几个我踩过的坑。这些操作初看是优化实际反而把系统搞得更慢甚至引发故障。5.1 索引堆太多写入成了“慢查询”有人会把查询条件里的每一列都建索引觉得这样“万无一失”。结果插入一条数据要同时更新七八个索引树写入慢了10倍。数据库优化永远是读写权衡。高频写、低频查的库索引宁缺毋滥高频查、低频写的库索引可以适当多建。我见过最夸张的一个表索引数量快赶上字段数量后来删了一半索引写入立刻恢复。判断索引有没有用就看慢查询日志里还有没有对应的SQL在用。三个月都没被EXPLAIN走到的索引基本可以删了。5.2 Python里的隐式事务让人防不胜防用ORM的时候很多人以为单条UPDATE一定是一个独立事务。但有时代码里嵌套了transaction.atomic()或begin()之后忘了提交行锁一直握着不放。这时候其他连接更新同一行就会阻塞慢查询日志上全是“Waiting for table metadata lock”或“Lock wait timeout exceeded”。这类问题有个典型的排查方式用SHOW PROCESSLIST看一下有哪些连接处于Sleep状态但还开着事务或者查information_schema.innodb_trx表。我在排查一个“刚上线就卡死”的问题时就是发现Python服务有个定时任务跑完异常后没有commit锁了主表整整两个小时。5.3 用Python脚本朴素地“优化”慢SQL反而更糟我见过一种“优化”方式拿到了慢SQL看了一眼觉得不够快就在Python里加了个time.sleep(0.1)或异步重试机制想用“慢一点”来“稳一点”。这是完全错误的方向。慢查询不是靠限流解决的慢查询靠的是让查询本身变快。正确的思路永远是先定位根因。日志开了EXPLAIN跑了执行计划解读了再去动代码。如果实在没头绪把那条SQL丢到生产环境的备份库上跑一遍把真实耗时量出来再来判断是索引、是锁、还是连接池的问题。5.4 我个人的最后一条经验现在每次上线涉及数据库变更我都会在预发环境跑一遍慢查询日志的前二十条确认没有新冒出来的高耗时SQL。这个习惯帮我挡掉了好几次线上事故。另外在你的监控面板上给慢查询数量加个告警平时可能一直不响但一旦响了绝对是救命的信号。