刷到这篇的你应该已经坚持到 Day 43 了先给自己鼓个掌。前面四十多天咱们写过爬虫、搭过 Web 服务、处理过各种数据但在数据量只有几千条的时候你很难感受到数据库性能的瓶颈。直到某一天你的接口在测试环境跑得飞快一上生产、数据量涨到几十万上百万同一个查询从几十毫秒变成好几秒——恭喜你遇到了几乎所有 Python 开发者都会撞上的那堵墙SQL 写得没问题但 MySQL 在“蛮力干活”。这一天的主题就一个MySQL 索引。索引是数据库性能优化里性价比最高、见效最快的一招也是新手最容易“听过但没吃透”的概念。很多人知道要加索引但不知道为什么加、加在哪个字段、联合索引顺序怎么定、为什么加了索引查询还是慢。这一篇我会从真实业务场景出发把索引的底层逻辑、EXPLAIN 执行计划的读法、联合索引与覆盖索引的实战设计以及 Python 代码侧怎么和索引配合一次讲清楚。适合正在写业务系统的 Python 开发者也适合准备面试前把索引这块彻底搞明白的朋友。1. 数据量一上来接口就变慢先学会给慢查询“听诊”1.1 一个典型到不能再典型的翻车现场假设你写了一个订单列表接口逻辑很简单按用户 ID 查订单筛掉已删除的按创建时间倒序返回最近二十条。刚开始数据量小一切正常。等线上跑了两三个月订单表到了几十万行这个接口开始变得不对劲——首次打开要两三秒翻页更是煎熬。这时候大部分人的第一反应是什么查 Python 代码逻辑、怀疑 ORM 性能差、觉得是服务器带宽不够、甚至直接上 Redis 缓存。我在实际项目里见过太多人把时间花在缓存和架构调整上结果发现最根本的问题是那条 SQL 根本没走索引MySQL 每次都在做全表扫描。缓存能挡住一部分重复请求但用户一多、查询条件一变数据库照样被打穿。先别急着堆组件把索引搞明白很多性能问题能直接解决一大半。真实世界里的教训是性能问题的排查顺序应该是先数据库再应用代码最后才轮到架构。而数据库层面第一个要查的就是 SQL 有没有充分利用索引。1.2 全表扫描到底有多贵MySQL 的“蛮力”成本没有索引的时候MySQL 是怎么执行一条WHERE user_id 1001的它会把整张表的数据从磁盘一页一页读出来逐行比对把所有满足条件的行挑出来。这种做法的专业术语叫全表扫描full table scan在 EXPLAIN 里表现为type ALL。你感受一下这个成本假设订单表有 100 万行每行大约 200 字节InnoDB 默认页大小 16KB那这 100 万行大约占用 12 万多个数据页。全表扫描意味着哪怕你只想查一个用户的 20 条订单MySQL 也得把 12 万个页从头到尾翻一遍。就算这些页大部分在内存缓冲池里CPU 逐行比对的开销也足够让接口卡顿。数据量翻十倍扫描成本就翻十倍这就是为什么“以前好好的现在不行了”。打个比方全表扫描就像你在一个没有按姓氏整理的书架上找一本叫《XXX》的书只能从第一层第一本开始一本一本看封面。索引的作用就是先给书架做一个带拼音标签的分类目录——你不用翻书先查目录定位到对应区域直接抽那几本就行。1.3 第一步不是加索引而是先定位“元凶 SQL”很多新手拿到慢接口第一件事就是凭感觉给字段加索引这其实是本末倒置。正确做法是先确认到底是哪条 SQL 慢慢到什么程度是不是根本没走索引MySQL 自带的慢查询日志就是干这个用的。在 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; SET GLOBAL log_queries_not_using_indexes ON;long_query_time 1表示执行时间超过 1 秒的 SQL 都会被记录下来log_queries_not_using_indexes则会把那些没走索引的查询也记下来——这两类基本就是你需要重点关注的对象。生产环境建议把阈值设成 0.5 秒甚至更低宁可多记一些也不能漏掉真正的元凶。在 Python 项目里你可以用日志系统把每条 SQL 的执行时间打印出来或者用 ORM 自带的 SQL 日志功能。比如用 SQLAlchemy 时设置echoTrue用 Django 时配置 logging 里的django.db.backendslogger都能实时看到 ORM 生成的 SQL 和耗时。记住一句话没有数据的优化都是瞎猜先定位再动手。2. 索引为什么能让查询“起飞”B树是怎么省时间的2.1 从“翻字典”说起B树的结构与查询路径索引能提速靠的不是玄学而是一种叫 B树 的数据结构。你可以把 B树理解成一本多级目录最上面一层是根节点存放少量“区间边界”中间层是路由节点用来缩小查找范围最底层是叶子节点按顺序存放真正的数据或者指向数据的指针。查询一条记录时MySQL 从根节点开始通过二分查找逐层往下走。树的高度通常只有三四层也就是说在百万级数据表里定位一条记录只需要经过三四次节点比较。对比全表扫描要读十几万个页这效率差距是数量级的。为什么 MySQL 选 B树而不是别的结构两个关键原因。第一B树的非叶子节点不存数据只存键值和指针所以一个节点能容纳大量分支树非常“矮”磁盘 IO 次数被压到最低。第二B树的叶子节点之间用链表串了起来天然支持范围查询比如BETWEEN、、ORDER BY而哈希索引虽然单点查询更快但一遇到范围查询就抓瞎了。日常业务 SQL 里范围查询是家常便饭所以 B树才是 InnoDB 的默认选择。2.2 聚簇索引与二级索引存储层面最关键的区别InnoDB 里有个概念很多人没搞懂聚簇索引。简单说InnoDB 的表数据本身就是按主键顺序组织存放的主键索引的叶子节点直接保存整行数据这个结构就叫聚簇索引。换句话说一张 InnoDB 表默认就有一个以主键为键的“天然索引”数据行物理上是按主键排序的。那你后来建的索引呢它们叫二级索引也叫辅助索引。二级索引的叶子节点存的不是整行数据而是对应的主键值。比如你在user_id上建了索引这个索引会把 user_id 排序好每个 user_id 后面跟着该行数据的主键 ID。这里就引出一个特别重要的推论通过二级索引查数据通常要查两次。第一次在二级索引的 B树里找到符合条件的主键值集合第二次拿着主键值去聚簇索引里取完整行数据。这第二次查找就是下面要说的“回表”。2.3 回表查询慢的隐形元凶回表是新手最容易忽略的性能杀手。你可能会奇怪我明明给user_id加了索引EXPLAIN 也显示走了索引为什么查询还是慢答案就在回表。假设订单表 100 万行某个用户的订单有 2 万条你执行SELECT * FROM orders WHERE user_id 1001。二级索引帮你快速定位到了这 2 万条记录的主键但为了拿到*的全部字段MySQL 得拿着这 2 万个主键一个个到聚簇索引里查完整行——每次回表都是一次随机 IO2 万次随机 IO 的成本非常可观。随机 IO 有多贵顺序读磁盘和随机读磁盘的耗时可以差几十倍因为顺序读可以利用磁盘预读和页缓存随机读每次都要重新寻道。所以优化的核心思路之一就是尽量少回表甚至不回表。怎么做两个方向一是让查询条件尽可能精确减少命中的行数二是在合适的场景用覆盖索引让二级索引直接“回答”你的查询后面会专门讲。3. 用 EXPLAIN 给 SQL 做“体检”执行计划字段逐个拆解3.1 先学会看 type查询质量的阶梯定位到慢 SQL 之后下一步就是用EXPLAIN看它的执行计划。执行计划就是 MySQL 告诉你“我打算怎么执行这条 SQL”相当于给 SQL 做 CT。用法极其简单在 SQL 前面加 EXPLAIN 关键字即可不需要真的执行EXPLAIN SELECT * FROM orders WHERE user_id 1001 ORDER BY created_at DESC LIMIT 20;输出结果里有一大堆字段新手要从type列看起。type表示表的访问方式从好到差大致是下面这个阶梯type 值含义评价system表只有一行极优几乎见不到const按主键或唯一索引等值查询极优最多返回一行eq_ref连接查询中被驱动表按唯一索引等值匹配优ref按普通索引等值匹配良业务常见range索引范围扫描尚可如 BETWEEN、IN、index全索引扫描差需要遍历整个索引树ALL全表扫描极差必须优化正常情况下你的核心查询至少要达到range级别最好是ref或const。看到ALL基本可以直接拍板这条 SQL 缺索引或者索引没被用上。index虽然有个“索引”字样但它是把整个二级索引遍历一遍和全表扫描是半斤八两的兄弟同样要警惕。3.2 key_len 与 rows联合索引到底用了几分力type告诉你走没走索引key_len则告诉你索引用了多少。这两个字段配合起来能看出联合索引是否被充分利用。举个例子你建了一个联合索引idx(user_id, status, created_at)执行EXPLAIN SELECT * FROM orders WHERE user_id 1001 AND status 1 ORDER BY created_at DESC LIMIT 20;如果key idx(user_id, status, created_at)但key_len只等于user_id字段的长度比如 INT 是 4 字节说明 MySQL 只用到了索引的第一个字段后面的status和created_at压根没参与索引定位。这种情况下查询可能仍然会慢尤其当 user_id 对应的数据量很大时。key_len的计算逻辑每个被使用的索引字段其字节长度会累加进来。INT 类型 4 字节BIGINT 8 字节VARCHAR 还要加上字符集字节数和长度前缀。比如user_id INT status TINYINT都用上了key_len 大约是4 1如果字段允许 NULL 还要加 1。你不用背得太细但要养成习惯看到 key_len 明显小于联合索引的完整长度就要怀疑索引没有被充分使用。rows字段是 MySQL 估计要扫描的行数同样很有参考价值。优化前扫描几十万行优化后扫描几十行效果一目了然。3.3 一个真实的优化案例从几百毫秒到几毫秒光讲理论不够我拆一个真实案例。某项目有个订单分页接口核心 SQL 长这样SELECT * FROM orders WHERE user_id 1001 AND status 1 ORDER BY created_at DESC LIMIT 20;订单表 200 万行最初执行计划是type ALLrows 2000000Extra里还有Using filesort表示排序没走索引MySQL 在内存里额外排了一遍。整条查询耗时约 500 毫秒接口响应轻松超过一秒。分析一下user_id和status是等值条件created_at是排序条件这三者非常适合放到同一个联合索引里。于是执行ALTER TABLE orders ADD INDEX idx_user_status_time (user_id, status, created_at);再看执行计划type refrows 约几十行Extra里的Using filesort消失了变成了Using index condition。查询耗时直接降到 5 毫秒以内。这里有个设计顺序的讲究等值条件字段放前面排序字段放最后。为什么因为 MySQL 在联合索引中只有当前面的字段都是等值匹配时后续字段才能继续用于索引定位如果是范围条件后面的字段就“断”了。把等值字段排在前才能让索引的每一层都发挥作用同时让ORDER BY created_at直接利用索引的有序性完成排序。4. 索引设计实战联合索引、最左前缀与覆盖索引4.1 最左前缀原则联合索引的顺序就是战斗力联合索引的核心规则叫最左前缀原则。意思是联合索引(a, b, c)实际能服务三种查询——只用a、用ab、用abc。MySQL 会从最左边的列开始匹配一直匹配到第一个范围条件为止如果查询条件里没有最左边的a那这个联合索引基本就废了。举个 Python 业务里的常见场景。你要做一个“管理后台订单筛选”功能查询条件可能是状态、下单时间范围、店铺 ID。三个条件都可能出现也可能只出现其中一两个。这时候怎么建索引一般来说区分度高、固定等值的字段放最左边。比如店铺 ID 是等值条件、区分度高放第一状态字段区分度低但也是等值条件放第二下单时间是范围条件放最后。于是索引设计成(shop_id, status, created_at)。这样无论用户是否筛状态shop_id都能先定位到一小撮数据然后用status缩小范围最后用created_at做排序或范围过滤。反过来如果你把created_at放最前面那么查询条件里一旦没有限定大范围的时间段索引就会退化。这就是为什么很多新手建了联合索引却没效果——不是索引没用是顺序搞反了。4.2 覆盖索引让二级索引自己“扛下所有”前面说的回表问题最优雅的解法就是覆盖索引。所谓覆盖索引是指查询涉及的所有列都包含在同一个二级索引里这样 MySQL 只扫索引树就能拿到全部需要的数据连主键都不用回Extra里会出现醒目的Using index。业务里最常见的覆盖索引场景就是 COUNT 和纯 ID 查询。比如统计某个用户的有效订单数SELECT COUNT(*) FROM orders WHERE user_id 1001 AND status 1;如果存在idx(user_id, status)这条 COUNT 就可以完全在二级索引里完成不需要碰聚簇索引速度极快。再比如你只需要SELECT id FROM orders WHERE shop_id 5只要shop_id上有索引就能覆盖。所以设计索引时有个进阶意识别只想着“WHERE 用到的字段”还要关注“SELECT 要取的字段”。如果一个高频查询只需要索引里的几个列就可以把查询列也纳入联合索引做成覆盖索引彻底消灭回表。但要注意索引列越多写入维护成本越高覆盖索引要用在真正的热点查询上不能无脑加。4.3 索引设计自查清单动手建索引前先过一遍我在项目里总结了一套建索引之前必过的自查清单分享出来区分度优先优先给区分度高的字段建索引。性别、状态这种只有几个取值的字段单独建索引价值很低因为查出来的行数太多索引帮助不大还得回表。等值条件放前范围条件放后联合索引里等值查询字段放前面BETWEEN、、等范围字段放后面否则后面的字段无法利用索引。兼顾 ORDER BY 与 GROUP BY频繁排序分组的字段能放进索引就放进去可以消除Using filesort和临时表。警惕冗余索引已有(a, b, c)时再建(a)或者(a, b)通常是冗余的白白增加写入成本。VARCHAR 可用前缀索引超长文本字段比如 URL、备注做索引时可以用INDEX idx_name (content(20))只索引前 20 个字符省空间又够用。外键字段必须有索引JOIN 的关联字段、经常出现在 WHERE 里的字段都是建立索引的优先对象。这套清单是我在多个业务系统里反复验证过的不能说覆盖全部场景但能避开 80% 的常见坑。5. 索引的代价与维护索引多了反而拖垮写入5.1 写入放大与存储膨胀每个索引都要“供养”前面讲了索引的各种好处但必须泼一盆冷水索引不是免费的。每建一个二级索引就等于在磁盘上多维护一棵 B树。每次 INSERT 一条数据除了写主键聚簇索引还要往每一棵二级索引树里插入对应记录每次 UPDATE 如果改了索引字段也要同步更新所有相关索引DELETE 同理。这就是所谓的“写入放大”。一个订单表如果有 5 个索引那一次插入就要写 6 棵树。如果你的系统是读多写少多几个索引无伤大雅但如果是日志采集、消息入库这类写多读少的场景索引过多会明显拖慢写入速度还可能造成磁盘空间膨胀。所以在设计阶段就要想清楚这张表的核心查询路径是什么有多少写并发不要抱着“索引反正能删”的心态一把梭上线之后再逐个排查冗余索引代价远比设计时多花十分钟要大。5.2 冗余索引与无效索引常见的不健康状态项目跑久了索引很容易失控。最常见的问题就是冗余索引。比如某同事先建了idx(user_id, status)后来另一个需求又建了idx(user_id)这两个索引功能高度重叠——后者能做的事前者全都能做前者还更全面。冗余索引浪费存储、拖慢写入却几乎不带来额外查询收益。怎么发现它们可以从information_schema.statistics里把表的所有索引导出来人工比对前缀列是否重复也可以借助performance_schema的索引使用统计看看哪些索引从来没被用过。下面这条 SQL 能查出某个表的全部索引结构SELECT TABLE_NAME, INDEX_NAME, SEQ_IN_INDEX, COLUMN_NAME FROM information_schema.STATISTICS WHERE TABLE_SCHEMA your_db AND TABLE_NAME orders ORDER BY INDEX_NAME, SEQ_IN_INDEX;如果发现有索引建立很久但SHOW INDEX里的Cardinality一直很低或者业务统计里从未被possible_keys引用就该考虑在低峰期把它删掉。删除索引本身不阻塞长时间业务在 MySQL 8.0 里是瞬间的元数据操作但删错了重新加回来要重建整棵树所以删之前务必确认没有查询依赖它。5.3 碎片整理与统计信息周期性的“大扫除”索引用久了还有另一个问题碎片。频繁的随机插入和删除会让 B树的叶子节点产生大量空洞页利用率下降范围扫描的效率也跟着变差。常见的处理方式有两种ANALYZE TABLE orders;重新统计索引的基数信息。这个很轻量建议在数据量大变动后比如导完一批历史数据执行一次帮助优化器做出更准确的索引选择。OPTIMIZE TABLE orders;重建表和索引消除碎片。这个操作比较重会锁表或者在在线 DDL 下消耗较多 IO务必要在业务低峰期执行。MySQL 8.0 对 OPTIMIZE 做了不少改进但在大表上仍然不建议频繁执行。我的习惯是像订单、日志这样持续增长的表每个月挑一个凌晨窗口做一次巡检看慢查询日志有没有新的慢 SQL、看索引使用统计有没有“僵尸索引”、数据变动大就先 ANALYZE 一次。索引维护不是一次性的工作而是长期健康管理的一部分。6. Python 代码侧如何与索引“相互成全”6.1 别急着骂 ORM先看看它生成了什么 SQL很多 Python 开发者在性能出问题时第一反应是“ORM 太慢”但说实话ORM 本身很少是瓶颈瓶颈往往是 ORM 生成的 SQL 没走索引或者触发了 N1 查询。N1 是个经典问题你先查了 100 个用户然后循环里又对每个用户发一条 SQL 查他的订单总共 101 条查询。数据库层每条 SQL 都不慢但网络往返和查询数量把性能拖垮了。解决办法是让 ORM 帮我们把多条查询合并起来。用 SQLAlchemy 的话查询时用selectinload、joinedload这类预加载方式一次性把关联数据查出来用 Django 就用select_related适合外键一对一和prefetch_related适合多对多和反向关联。关键在于你要能看见 ORM 到底生成了什么 SQL。# SQLAlchemy 开启 SQL 回显开发环境强烈建议 engine create_engine(mysqlpymysql://user:passlocalhost/db, echoTrue)# Django 配置 SQL 日志 LOGGING { loggers: { django.db.backends: { level: DEBUG, handlers: [console], } } }看到 ORM 生成的 SQL 之后把关键查询复制出来跑一遍EXPLAIN确定它走没走索引。很多时候你会发现问题不在 ORM 本身而在查询条件的写法让索引失效了。6.2 函数包裹与隐式类型转换索引失效的“自杀”操作索引建得再好如果 SQL 写法不当MySQL 也照样用不上。Python 后端写 SQL 时最常见的两个“自杀”操作一个是函数包裹一个是隐式类型转换。先看函数包裹。很多人习惯写成SELECT * FROM orders WHERE DATE(created_at) 2024-01-01;虽然created_at上有索引但 MySQL 无法对索引列应用函数后还使用索引因为索引树里存的原始值不是DATE()处理后的值。这条查询会退化成全表扫描。正确做法是写成范围查询SELECT * FROM orders WHERE created_at 2024-01-01 00:00:00 AND created_at 2024-01-02 00:00:00;再看隐式类型转换。假设表的phone字段是 VARCHARPython 端如果传了整数进去SELECT * FROM users WHERE phone 13800138000;MySQL 会把字段值隐式转成数字再比较导致索引失效。解决办法就一句话参数类型和字段类型保持一致。在 Python 代码里从请求拿到的参数往往都是字符串传给数据库时别自己乱转让数据库参数化查询去处理比如cursor.execute(SELECT * FROM users WHERE phone %s, (phone,))既安全又避免类型错乱。还有一类常见的失效情况是模糊查询。LIKE abc%可以走索引但LIKE %abc和LIKE %abc%因为前导通配符无法确定起点用不上索引。如果业务确实需要后模糊匹配考虑用全文索引或者搜索引擎方案而不是硬刚。6.3 批量写入与分页查询代码侧配合索引的细节最后聊两个 Python 代码里能直接落地的配合技巧。第一个是批量写入。很多新手用 ORM 往 MySQL 导数据是循环里一条条session.add()、一条条 commit。每条写操作都是一次完整的索引树更新加一次网络往返几万条数据导完要几分钟甚至更久。正确的姿势是用 ORM 的批量接口或原生 SQL 的批量 INSERTINSERT INTO orders (user_id, status, created_at) VALUES (1001, 1, 2024-01-01 00:00:00), (1002, 2, 2024-01-01 00:01:00), ...;一次写几百上千条索引树的更新在引擎内部完成效率高一个数量级。SQLAlchemy 的session.bulk_insert_mappings和 Django 的bulk_create就是干这个的。第二个是深分页问题。LIMIT 100000, 20这种写法很常见但它会把前 10 万条全部扫出来再丢掉即使走了索引也很慢。更聪明的做法是“游标分页”也就是记录上一页最后一条的 ID 或排序值下一页用WHERE created_at 上一页的值 ORDER BY created_at DESC LIMIT 20。这样每次查询只扫描目标区间的数据索引优势被完全发挥。-- 传统深分页扫 10 万行后丢弃 SELECT * FROM orders ORDER BY id LIMIT 100000, 20; -- 游标分页只扫需要的数据 SELECT * FROM orders WHERE id 100020 ORDER BY id DESC LIMIT 20;这两个技巧本质上都是“减少数据库做的无用功”和索引配合好后性能收益非常明显。写到这MySQL 索引这条线算是完整串起来了。从慢查询定位到 B树原理从 EXPLAIN 读法到联合索引设计再到 Python 代码侧的配合这一套流程我在不同项目里用了很多次几乎每次都能把核心接口的耗时从秒级拉到毫秒级。最后再送大家一句我的切身体会索引优化最忌“拍脑袋”先用 EXPLAIN 看清现状再结合业务查询频率去设计索引少而精永远好过多而杂。Day 43 的内容如果消化透了后面再遇到数据库性能问题你会比大多数人都冷静。 
长河君 · 2026/10/17 2:26:39 · 阅读 1.2k MySQL索引优化实战：从B+树原理到EXPLAIN，告别慢查询
刷到这篇的你应该已经坚持到 Day 43 了先给自己鼓个掌。前面四十多天咱们写过爬虫、搭过 Web 服务、处理过各种数据但在数据量只有几千条的时候你很难感受到数据库性能的瓶颈。直到某一天你的接口在测试环境跑得飞快一上生产、数据量涨到几十万上百万同一个查询从几十毫秒变成好几秒——恭喜你遇到了几乎所有 Python 开发者都会撞上的那堵墙SQL 写得没问题但 MySQL 在“蛮力干活”。这一天的主题就一个MySQL 索引。索引是数据库性能优化里性价比最高、见效最快的一招也是新手最容易“听过但没吃透”的概念。很多人知道要加索引但不知道为什么加、加在哪个字段、联合索引顺序怎么定、为什么加了索引查询还是慢。这一篇我会从真实业务场景出发把索引的底层逻辑、EXPLAIN 执行计划的读法、联合索引与覆盖索引的实战设计以及 Python 代码侧怎么和索引配合一次讲清楚。适合正在写业务系统的 Python 开发者也适合准备面试前把索引这块彻底搞明白的朋友。1. 数据量一上来接口就变慢先学会给慢查询“听诊”1.1 一个典型到不能再典型的翻车现场假设你写了一个订单列表接口逻辑很简单按用户 ID 查订单筛掉已删除的按创建时间倒序返回最近二十条。刚开始数据量小一切正常。等线上跑了两三个月订单表到了几十万行这个接口开始变得不对劲——首次打开要两三秒翻页更是煎熬。这时候大部分人的第一反应是什么查 Python 代码逻辑、怀疑 ORM 性能差、觉得是服务器带宽不够、甚至直接上 Redis 缓存。我在实际项目里见过太多人把时间花在缓存和架构调整上结果发现最根本的问题是那条 SQL 根本没走索引MySQL 每次都在做全表扫描。缓存能挡住一部分重复请求但用户一多、查询条件一变数据库照样被打穿。先别急着堆组件把索引搞明白很多性能问题能直接解决一大半。真实世界里的教训是性能问题的排查顺序应该是先数据库再应用代码最后才轮到架构。而数据库层面第一个要查的就是 SQL 有没有充分利用索引。1.2 全表扫描到底有多贵MySQL 的“蛮力”成本没有索引的时候MySQL 是怎么执行一条WHERE user_id 1001的它会把整张表的数据从磁盘一页一页读出来逐行比对把所有满足条件的行挑出来。这种做法的专业术语叫全表扫描full table scan在 EXPLAIN 里表现为type ALL。你感受一下这个成本假设订单表有 100 万行每行大约 200 字节InnoDB 默认页大小 16KB那这 100 万行大约占用 12 万多个数据页。全表扫描意味着哪怕你只想查一个用户的 20 条订单MySQL 也得把 12 万个页从头到尾翻一遍。就算这些页大部分在内存缓冲池里CPU 逐行比对的开销也足够让接口卡顿。数据量翻十倍扫描成本就翻十倍这就是为什么“以前好好的现在不行了”。打个比方全表扫描就像你在一个没有按姓氏整理的书架上找一本叫《XXX》的书只能从第一层第一本开始一本一本看封面。索引的作用就是先给书架做一个带拼音标签的分类目录——你不用翻书先查目录定位到对应区域直接抽那几本就行。1.3 第一步不是加索引而是先定位“元凶 SQL”很多新手拿到慢接口第一件事就是凭感觉给字段加索引这其实是本末倒置。正确做法是先确认到底是哪条 SQL 慢慢到什么程度是不是根本没走索引MySQL 自带的慢查询日志就是干这个用的。在 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; SET GLOBAL log_queries_not_using_indexes ON;long_query_time 1表示执行时间超过 1 秒的 SQL 都会被记录下来log_queries_not_using_indexes则会把那些没走索引的查询也记下来——这两类基本就是你需要重点关注的对象。生产环境建议把阈值设成 0.5 秒甚至更低宁可多记一些也不能漏掉真正的元凶。在 Python 项目里你可以用日志系统把每条 SQL 的执行时间打印出来或者用 ORM 自带的 SQL 日志功能。比如用 SQLAlchemy 时设置echoTrue用 Django 时配置 logging 里的django.db.backendslogger都能实时看到 ORM 生成的 SQL 和耗时。记住一句话没有数据的优化都是瞎猜先定位再动手。2. 索引为什么能让查询“起飞”B树是怎么省时间的2.1 从“翻字典”说起B树的结构与查询路径索引能提速靠的不是玄学而是一种叫 B树 的数据结构。你可以把 B树理解成一本多级目录最上面一层是根节点存放少量“区间边界”中间层是路由节点用来缩小查找范围最底层是叶子节点按顺序存放真正的数据或者指向数据的指针。查询一条记录时MySQL 从根节点开始通过二分查找逐层往下走。树的高度通常只有三四层也就是说在百万级数据表里定位一条记录只需要经过三四次节点比较。对比全表扫描要读十几万个页这效率差距是数量级的。为什么 MySQL 选 B树而不是别的结构两个关键原因。第一B树的非叶子节点不存数据只存键值和指针所以一个节点能容纳大量分支树非常“矮”磁盘 IO 次数被压到最低。第二B树的叶子节点之间用链表串了起来天然支持范围查询比如BETWEEN、、ORDER BY而哈希索引虽然单点查询更快但一遇到范围查询就抓瞎了。日常业务 SQL 里范围查询是家常便饭所以 B树才是 InnoDB 的默认选择。2.2 聚簇索引与二级索引存储层面最关键的区别InnoDB 里有个概念很多人没搞懂聚簇索引。简单说InnoDB 的表数据本身就是按主键顺序组织存放的主键索引的叶子节点直接保存整行数据这个结构就叫聚簇索引。换句话说一张 InnoDB 表默认就有一个以主键为键的“天然索引”数据行物理上是按主键排序的。那你后来建的索引呢它们叫二级索引也叫辅助索引。二级索引的叶子节点存的不是整行数据而是对应的主键值。比如你在user_id上建了索引这个索引会把 user_id 排序好每个 user_id 后面跟着该行数据的主键 ID。这里就引出一个特别重要的推论通过二级索引查数据通常要查两次。第一次在二级索引的 B树里找到符合条件的主键值集合第二次拿着主键值去聚簇索引里取完整行数据。这第二次查找就是下面要说的“回表”。2.3 回表查询慢的隐形元凶回表是新手最容易忽略的性能杀手。你可能会奇怪我明明给user_id加了索引EXPLAIN 也显示走了索引为什么查询还是慢答案就在回表。假设订单表 100 万行某个用户的订单有 2 万条你执行SELECT * FROM orders WHERE user_id 1001。二级索引帮你快速定位到了这 2 万条记录的主键但为了拿到*的全部字段MySQL 得拿着这 2 万个主键一个个到聚簇索引里查完整行——每次回表都是一次随机 IO2 万次随机 IO 的成本非常可观。随机 IO 有多贵顺序读磁盘和随机读磁盘的耗时可以差几十倍因为顺序读可以利用磁盘预读和页缓存随机读每次都要重新寻道。所以优化的核心思路之一就是尽量少回表甚至不回表。怎么做两个方向一是让查询条件尽可能精确减少命中的行数二是在合适的场景用覆盖索引让二级索引直接“回答”你的查询后面会专门讲。3. 用 EXPLAIN 给 SQL 做“体检”执行计划字段逐个拆解3.1 先学会看 type查询质量的阶梯定位到慢 SQL 之后下一步就是用EXPLAIN看它的执行计划。执行计划就是 MySQL 告诉你“我打算怎么执行这条 SQL”相当于给 SQL 做 CT。用法极其简单在 SQL 前面加 EXPLAIN 关键字即可不需要真的执行EXPLAIN SELECT * FROM orders WHERE user_id 1001 ORDER BY created_at DESC LIMIT 20;输出结果里有一大堆字段新手要从type列看起。type表示表的访问方式从好到差大致是下面这个阶梯type 值含义评价system表只有一行极优几乎见不到const按主键或唯一索引等值查询极优最多返回一行eq_ref连接查询中被驱动表按唯一索引等值匹配优ref按普通索引等值匹配良业务常见range索引范围扫描尚可如 BETWEEN、IN、index全索引扫描差需要遍历整个索引树ALL全表扫描极差必须优化正常情况下你的核心查询至少要达到range级别最好是ref或const。看到ALL基本可以直接拍板这条 SQL 缺索引或者索引没被用上。index虽然有个“索引”字样但它是把整个二级索引遍历一遍和全表扫描是半斤八两的兄弟同样要警惕。3.2 key_len 与 rows联合索引到底用了几分力type告诉你走没走索引key_len则告诉你索引用了多少。这两个字段配合起来能看出联合索引是否被充分利用。举个例子你建了一个联合索引idx(user_id, status, created_at)执行EXPLAIN SELECT * FROM orders WHERE user_id 1001 AND status 1 ORDER BY created_at DESC LIMIT 20;如果key idx(user_id, status, created_at)但key_len只等于user_id字段的长度比如 INT 是 4 字节说明 MySQL 只用到了索引的第一个字段后面的status和created_at压根没参与索引定位。这种情况下查询可能仍然会慢尤其当 user_id 对应的数据量很大时。key_len的计算逻辑每个被使用的索引字段其字节长度会累加进来。INT 类型 4 字节BIGINT 8 字节VARCHAR 还要加上字符集字节数和长度前缀。比如user_id INT status TINYINT都用上了key_len 大约是4 1如果字段允许 NULL 还要加 1。你不用背得太细但要养成习惯看到 key_len 明显小于联合索引的完整长度就要怀疑索引没有被充分使用。rows字段是 MySQL 估计要扫描的行数同样很有参考价值。优化前扫描几十万行优化后扫描几十行效果一目了然。3.3 一个真实的优化案例从几百毫秒到几毫秒光讲理论不够我拆一个真实案例。某项目有个订单分页接口核心 SQL 长这样SELECT * FROM orders WHERE user_id 1001 AND status 1 ORDER BY created_at DESC LIMIT 20;订单表 200 万行最初执行计划是type ALLrows 2000000Extra里还有Using filesort表示排序没走索引MySQL 在内存里额外排了一遍。整条查询耗时约 500 毫秒接口响应轻松超过一秒。分析一下user_id和status是等值条件created_at是排序条件这三者非常适合放到同一个联合索引里。于是执行ALTER TABLE orders ADD INDEX idx_user_status_time (user_id, status, created_at);再看执行计划type refrows 约几十行Extra里的Using filesort消失了变成了Using index condition。查询耗时直接降到 5 毫秒以内。这里有个设计顺序的讲究等值条件字段放前面排序字段放最后。为什么因为 MySQL 在联合索引中只有当前面的字段都是等值匹配时后续字段才能继续用于索引定位如果是范围条件后面的字段就“断”了。把等值字段排在前才能让索引的每一层都发挥作用同时让ORDER BY created_at直接利用索引的有序性完成排序。4. 索引设计实战联合索引、最左前缀与覆盖索引4.1 最左前缀原则联合索引的顺序就是战斗力联合索引的核心规则叫最左前缀原则。意思是联合索引(a, b, c)实际能服务三种查询——只用a、用ab、用abc。MySQL 会从最左边的列开始匹配一直匹配到第一个范围条件为止如果查询条件里没有最左边的a那这个联合索引基本就废了。举个 Python 业务里的常见场景。你要做一个“管理后台订单筛选”功能查询条件可能是状态、下单时间范围、店铺 ID。三个条件都可能出现也可能只出现其中一两个。这时候怎么建索引一般来说区分度高、固定等值的字段放最左边。比如店铺 ID 是等值条件、区分度高放第一状态字段区分度低但也是等值条件放第二下单时间是范围条件放最后。于是索引设计成(shop_id, status, created_at)。这样无论用户是否筛状态shop_id都能先定位到一小撮数据然后用status缩小范围最后用created_at做排序或范围过滤。反过来如果你把created_at放最前面那么查询条件里一旦没有限定大范围的时间段索引就会退化。这就是为什么很多新手建了联合索引却没效果——不是索引没用是顺序搞反了。4.2 覆盖索引让二级索引自己“扛下所有”前面说的回表问题最优雅的解法就是覆盖索引。所谓覆盖索引是指查询涉及的所有列都包含在同一个二级索引里这样 MySQL 只扫索引树就能拿到全部需要的数据连主键都不用回Extra里会出现醒目的Using index。业务里最常见的覆盖索引场景就是 COUNT 和纯 ID 查询。比如统计某个用户的有效订单数SELECT COUNT(*) FROM orders WHERE user_id 1001 AND status 1;如果存在idx(user_id, status)这条 COUNT 就可以完全在二级索引里完成不需要碰聚簇索引速度极快。再比如你只需要SELECT id FROM orders WHERE shop_id 5只要shop_id上有索引就能覆盖。所以设计索引时有个进阶意识别只想着“WHERE 用到的字段”还要关注“SELECT 要取的字段”。如果一个高频查询只需要索引里的几个列就可以把查询列也纳入联合索引做成覆盖索引彻底消灭回表。但要注意索引列越多写入维护成本越高覆盖索引要用在真正的热点查询上不能无脑加。4.3 索引设计自查清单动手建索引前先过一遍我在项目里总结了一套建索引之前必过的自查清单分享出来区分度优先优先给区分度高的字段建索引。性别、状态这种只有几个取值的字段单独建索引价值很低因为查出来的行数太多索引帮助不大还得回表。等值条件放前范围条件放后联合索引里等值查询字段放前面BETWEEN、、等范围字段放后面否则后面的字段无法利用索引。兼顾 ORDER BY 与 GROUP BY频繁排序分组的字段能放进索引就放进去可以消除Using filesort和临时表。警惕冗余索引已有(a, b, c)时再建(a)或者(a, b)通常是冗余的白白增加写入成本。VARCHAR 可用前缀索引超长文本字段比如 URL、备注做索引时可以用INDEX idx_name (content(20))只索引前 20 个字符省空间又够用。外键字段必须有索引JOIN 的关联字段、经常出现在 WHERE 里的字段都是建立索引的优先对象。这套清单是我在多个业务系统里反复验证过的不能说覆盖全部场景但能避开 80% 的常见坑。5. 索引的代价与维护索引多了反而拖垮写入5.1 写入放大与存储膨胀每个索引都要“供养”前面讲了索引的各种好处但必须泼一盆冷水索引不是免费的。每建一个二级索引就等于在磁盘上多维护一棵 B树。每次 INSERT 一条数据除了写主键聚簇索引还要往每一棵二级索引树里插入对应记录每次 UPDATE 如果改了索引字段也要同步更新所有相关索引DELETE 同理。这就是所谓的“写入放大”。一个订单表如果有 5 个索引那一次插入就要写 6 棵树。如果你的系统是读多写少多几个索引无伤大雅但如果是日志采集、消息入库这类写多读少的场景索引过多会明显拖慢写入速度还可能造成磁盘空间膨胀。所以在设计阶段就要想清楚这张表的核心查询路径是什么有多少写并发不要抱着“索引反正能删”的心态一把梭上线之后再逐个排查冗余索引代价远比设计时多花十分钟要大。5.2 冗余索引与无效索引常见的不健康状态项目跑久了索引很容易失控。最常见的问题就是冗余索引。比如某同事先建了idx(user_id, status)后来另一个需求又建了idx(user_id)这两个索引功能高度重叠——后者能做的事前者全都能做前者还更全面。冗余索引浪费存储、拖慢写入却几乎不带来额外查询收益。怎么发现它们可以从information_schema.statistics里把表的所有索引导出来人工比对前缀列是否重复也可以借助performance_schema的索引使用统计看看哪些索引从来没被用过。下面这条 SQL 能查出某个表的全部索引结构SELECT TABLE_NAME, INDEX_NAME, SEQ_IN_INDEX, COLUMN_NAME FROM information_schema.STATISTICS WHERE TABLE_SCHEMA your_db AND TABLE_NAME orders ORDER BY INDEX_NAME, SEQ_IN_INDEX;如果发现有索引建立很久但SHOW INDEX里的Cardinality一直很低或者业务统计里从未被possible_keys引用就该考虑在低峰期把它删掉。删除索引本身不阻塞长时间业务在 MySQL 8.0 里是瞬间的元数据操作但删错了重新加回来要重建整棵树所以删之前务必确认没有查询依赖它。5.3 碎片整理与统计信息周期性的“大扫除”索引用久了还有另一个问题碎片。频繁的随机插入和删除会让 B树的叶子节点产生大量空洞页利用率下降范围扫描的效率也跟着变差。常见的处理方式有两种ANALYZE TABLE orders;重新统计索引的基数信息。这个很轻量建议在数据量大变动后比如导完一批历史数据执行一次帮助优化器做出更准确的索引选择。OPTIMIZE TABLE orders;重建表和索引消除碎片。这个操作比较重会锁表或者在在线 DDL 下消耗较多 IO务必要在业务低峰期执行。MySQL 8.0 对 OPTIMIZE 做了不少改进但在大表上仍然不建议频繁执行。我的习惯是像订单、日志这样持续增长的表每个月挑一个凌晨窗口做一次巡检看慢查询日志有没有新的慢 SQL、看索引使用统计有没有“僵尸索引”、数据变动大就先 ANALYZE 一次。索引维护不是一次性的工作而是长期健康管理的一部分。6. Python 代码侧如何与索引“相互成全”6.1 别急着骂 ORM先看看它生成了什么 SQL很多 Python 开发者在性能出问题时第一反应是“ORM 太慢”但说实话ORM 本身很少是瓶颈瓶颈往往是 ORM 生成的 SQL 没走索引或者触发了 N1 查询。N1 是个经典问题你先查了 100 个用户然后循环里又对每个用户发一条 SQL 查他的订单总共 101 条查询。数据库层每条 SQL 都不慢但网络往返和查询数量把性能拖垮了。解决办法是让 ORM 帮我们把多条查询合并起来。用 SQLAlchemy 的话查询时用selectinload、joinedload这类预加载方式一次性把关联数据查出来用 Django 就用select_related适合外键一对一和prefetch_related适合多对多和反向关联。关键在于你要能看见 ORM 到底生成了什么 SQL。# SQLAlchemy 开启 SQL 回显开发环境强烈建议 engine create_engine(mysqlpymysql://user:passlocalhost/db, echoTrue)# Django 配置 SQL 日志 LOGGING { loggers: { django.db.backends: { level: DEBUG, handlers: [console], } } }看到 ORM 生成的 SQL 之后把关键查询复制出来跑一遍EXPLAIN确定它走没走索引。很多时候你会发现问题不在 ORM 本身而在查询条件的写法让索引失效了。6.2 函数包裹与隐式类型转换索引失效的“自杀”操作索引建得再好如果 SQL 写法不当MySQL 也照样用不上。Python 后端写 SQL 时最常见的两个“自杀”操作一个是函数包裹一个是隐式类型转换。先看函数包裹。很多人习惯写成SELECT * FROM orders WHERE DATE(created_at) 2024-01-01;虽然created_at上有索引但 MySQL 无法对索引列应用函数后还使用索引因为索引树里存的原始值不是DATE()处理后的值。这条查询会退化成全表扫描。正确做法是写成范围查询SELECT * FROM orders WHERE created_at 2024-01-01 00:00:00 AND created_at 2024-01-02 00:00:00;再看隐式类型转换。假设表的phone字段是 VARCHARPython 端如果传了整数进去SELECT * FROM users WHERE phone 13800138000;MySQL 会把字段值隐式转成数字再比较导致索引失效。解决办法就一句话参数类型和字段类型保持一致。在 Python 代码里从请求拿到的参数往往都是字符串传给数据库时别自己乱转让数据库参数化查询去处理比如cursor.execute(SELECT * FROM users WHERE phone %s, (phone,))既安全又避免类型错乱。还有一类常见的失效情况是模糊查询。LIKE abc%可以走索引但LIKE %abc和LIKE %abc%因为前导通配符无法确定起点用不上索引。如果业务确实需要后模糊匹配考虑用全文索引或者搜索引擎方案而不是硬刚。6.3 批量写入与分页查询代码侧配合索引的细节最后聊两个 Python 代码里能直接落地的配合技巧。第一个是批量写入。很多新手用 ORM 往 MySQL 导数据是循环里一条条session.add()、一条条 commit。每条写操作都是一次完整的索引树更新加一次网络往返几万条数据导完要几分钟甚至更久。正确的姿势是用 ORM 的批量接口或原生 SQL 的批量 INSERTINSERT INTO orders (user_id, status, created_at) VALUES (1001, 1, 2024-01-01 00:00:00), (1002, 2, 2024-01-01 00:01:00), ...;一次写几百上千条索引树的更新在引擎内部完成效率高一个数量级。SQLAlchemy 的session.bulk_insert_mappings和 Django 的bulk_create就是干这个的。第二个是深分页问题。LIMIT 100000, 20这种写法很常见但它会把前 10 万条全部扫出来再丢掉即使走了索引也很慢。更聪明的做法是“游标分页”也就是记录上一页最后一条的 ID 或排序值下一页用WHERE created_at 上一页的值 ORDER BY created_at DESC LIMIT 20。这样每次查询只扫描目标区间的数据索引优势被完全发挥。-- 传统深分页扫 10 万行后丢弃 SELECT * FROM orders ORDER BY id LIMIT 100000, 20; -- 游标分页只扫需要的数据 SELECT * FROM orders WHERE id 100020 ORDER BY id DESC LIMIT 20;这两个技巧本质上都是“减少数据库做的无用功”和索引配合好后性能收益非常明显。写到这MySQL 索引这条线算是完整串起来了。从慢查询定位到 B树原理从 EXPLAIN 读法到联合索引设计再到 Python 代码侧的配合这一套流程我在不同项目里用了很多次几乎每次都能把核心接口的耗时从秒级拉到毫秒级。最后再送大家一句我的切身体会索引优化最忌“拍脑袋”先用 EXPLAIN 看清现状再结合业务查询频率去设计索引少而精永远好过多而杂。Day 43 的内容如果消化透了后面再遇到数据库性能问题你会比大多数人都冷静。