1. 项目概述一切从一条慢SQL开始先说一个我上周刚处理完的真实事故。某业务线的订单列表接口原本P99延迟在50ms左右某个周二下午突然飙到2秒以上超时率接近5%用户侧反馈“订单页打不开”。我第一时间查慢查询日志抓出一条典型的全表扫描SQLSELECT * FROM order_info WHERE user_id 12345 AND status 1 ORDER BY create_time DESC LIMIT 20;下单表大概4800万行这条SQL之前一直没出过问题但数据量涨到一定规模后没有合适索引MySQL只能一行一行扫全表再把命中的记录做一次文件排序。EXPLAIN结果也印证了type是ALLrows估算4800万Extra里带着Using filesort。后来我加了一个组合索引把SQL耗时从1.8秒压到了35ms接口P99回到60ms以内。从用户体感上讲这个系统确实“快”了一个数量级还不止。这就是标题想聊的事数据库优化不是玄学核心就两件事第一会定位慢查询第二会用索引。这篇文章适合所有写SQL的后端开发、正在救火的DBA以及想系统搞懂“索引为什么能让查询快10倍”的初中级工程师。1.1 事故现场接口P99从50ms涨到2秒这次事故有两个关键背景第一是表数据量增长很快去年还是800万行今年已经4800万行第二是业务高峰期并发上来了多条类似的订单查询同时打到库上全表扫描的串行成本被放大成灾难。定位过程不算复杂。我先看监控面板确认瓶颈在数据库而不是应用服务器。然后打开慢查询日志用mysqldumpslow把近一小时的慢SQL按耗时排了个序结果top1就是上面这条订单列表查询。它占了慢查询总量的62%平均每次1.84秒。再跑一次EXPLAIN基本就能确认根因没有索引走的是ALL全表扫描。加上ORDER BY create_time DESCMySQL还需要把满足条件的行捞出来做排序这就是Using filesort的来源。这里有个非常容易踩的坑很多人一接到“接口变慢”的反馈第一反应是去改代码、加缓存、上消息队列。但绝大多数这类问题的第一现场都在数据库。先用慢查询日志把SQL抓出来剩下的优化才有方向。1.2 优化不是单点操作而是一套闭环流程我自己做慢查询优化从来不是“找到一条慢SQL加个索引完事”。那样只能解决眼前这一条下个月数据再涨还会冒出新的慢SQL。我习惯把优化当成一个闭环监控和日志先保证慢查询日志开着能随时抓到问题SQL。定位对慢SQL做EXPLAIN看访问路径、扫描行数、排序方式。分析判断是缺少索引、SQL写法有问题还是表结构设计不合理。优化建索引、改写SQL、拆分查询或者调整表结构。验证上线后对比执行计划、响应时间、系统负载确认没有副作用。回归把这类SQL沉淀成规则防止后面新增的查询再犯同样的错。在这次事故里我前面四步其实只花了半天后面的验证和回归花了一天。真正需要耐心的是确认“加这个索引不会让写入变慢太多”以及“其他查询会不会因为优化器选择变化受到牵连”。1.3 为什么说“快10倍”不是夸张很多人对数据库性能提升的预期是“从300ms优化到250ms”那确实谈不上10倍。但如果你把一个4800万行的全表扫描改成B树索引查找扫描行数从几千万降到几千这个数量级的差距在数据库里是实打实存在的。我用一组对比数据来说明。优化前这条SQL扫描约4800万行耗时1.84秒优化后它通过组合索引直接定位到user_id12345且status1的区间只需要扫描1350行耗时35ms。从耗时看提升了50倍以上从扫描量看下降了约3.5万倍。当然接口的最终延迟还要加上网络和业务逻辑开销所以我说“快10倍”是保守估计实际体感就是“秒开”和“等半天”的区别。2. 慢查询日志怎么把拖垮系统的SQL挖出来先别急着加索引。任何优化都从定位问题开始。我处理慢查询的第一件事就是确保慢查询日志开着。很多团队连这个都没开出问题的时候两眼一抹黑只能靠猜。2.1 五个参数打开慢查询日志MySQL的慢查询日志默认是关闭的上线后立刻打开这是最便宜的性能观测手段。动态开启方式很简单mysql SHOW VARIABLES LIKE slow_query%; mysql SET GLOBAL slow_query_log ON; mysql SET GLOBAL long_query_time 1;但注意SET GLOBAL只是临时生效重启MySQL后配置会丢。要让配置持久化需要写到my.cnf[mysqld] slow_query_log 1 slow_query_log_file /var/log/mysql/mysql-slow.log long_query_time 1 log_queries_not_using_indexes 1 log_throttle_queries_not_using_indexes 10这里有几个参数值得单独解释参数作用我的建议slow_query_log总开关生产库长期开启对性能影响很小long_query_time执行时间超过该秒数才记录默认10秒太宽松线上设1秒slow_query_log_file日志文件路径放到独立磁盘避免和binlog竞争log_queries_not_using_indexes记录所有未走索引的查询配合限流使用别裸开log_throttle_queries_not_using_indexes每分钟最多记录多少条未走索引的SQL防止日志爆炸我踩过一个坑以前图省事把long_query_time设成0结果每分钟刷几十万条正常查询进日志把磁盘写满了。生产环境一般设1秒就够了核心库如果对体验特别敏感可以设0.5秒但一定要监控日志增长速度。2.2 日志分析手工定位还是工具聚合慢查询日志打开后文件会越来越大靠肉眼一条条看是不现实的。我最常用的两个工具一个是MySQL自带的mysqldumpslow一个是Percona Toolkit里的pt-query-digest。mysqldumpslow适合快速看top Nmysqldumpslow -s at -t 10 /var/log/mysql/mysql-slow.log-s at表示按照平均查询时间排序-t 10表示只显示前10条。它会自动把数字参数替换成N把不同user_id的同类SQL聚合到一起这点非常实用。比如这次的订单查询无论user_id是10086还是12345都会被聚合成一条“SELECT * FROM order_info WHERE user_idN AND statusN ...”方便我找到规律。pt-query-digest更强大输出报告会按“总耗时”“平均耗时”“出现次数”多个维度排序还能算出每条SQL在全体慢查询里的占比。我一般先跑这个把报告拉到本地用文本编辑器看重点关注执行次数多、平均耗时高的组合。如果一个SQL既频繁又慢那就是头号嫌疑人。手工方式也有用武之地比如你想快速确认某个时间窗口有没有特定SQL直接grep:grep order_info /var/log/mysql/mysql-slow.log | head -50不过这种方式的效率低最终还是建议把工具链建立起来。2.3 EXPLAIN给慢SQL做一次全面体检抓到慢SQL后下一步是用EXPLAIN看执行计划。不要跳过这一步直接加索引那样你根本不知道索引加在哪个列上。EXPLAIN SELECT * FROM order_info WHERE user_id 12345 AND status 1 ORDER BY create_time DESC LIMIT 20\G我重点看这几个字段字段含义异常信号type访问类型出现ALL基本就是全表扫描key实际使用的索引是NULL说明没走索引rows预估扫描行数几千万行比几百行要警惕Extra额外信息出现Using filesort和Using temporary要警惕type字段从好到坏大致是const eq_ref ref range index ALL。如果看到ALL基本等于在告诉你要么没索引要么索引没用上。rows是优化器估算的扫描行数不是精确值但数量级很有参考价值。Extra里出现Using filesort意味着MySQL需要额外排序出现Using temporary意味着用了临时表这两种都是性能杀手。我这次查到的结果是typeALL、keyNULL、rows48000000、ExtraUsing where; Using filesort看到这里我已经知道必须靠索引把扫描范围缩小到user_id这一个“扇区”内。3. 索引为什么能让查询快10倍B树原理速讲如果你只记住了“建索引能变快”那是不够的因为你很快会遇到“为什么这个索引没生效”“为什么加了索引反而变慢”这类问题。理解背后的数据结构才能做出正确判断。3.1 B树数据库把全表扫描变成3次磁盘IO的奥秘索引在MySQL InnoDB里的实现是B树。你可以把B树想象成一本带多层目录的书最上层的目录页告诉你第1-100章在第几页第二层目录告诉你第1-10章在第几页最后一层叶子节点才真正指向正文。B树的特点是非叶子节点只存键值不存数据所以每个节点能放很多“目录项”叶子节点存数据并且用链表串在一起适合范围查询。因为非叶子节点很“窄”一棵3到4层的B树就能容纳几千万行数据。查一条记录走3到4次磁盘IO就能命中而全表扫描要把几千万行数据块全部读出来这个差距就是“快10倍”的根本来源。这里面还有一层逻辑索引是为了减少磁盘IO不是为了让CPU算得更快。一个4800万行的表全表扫描要读的磁盘页可能成千上万而B树查找只需要读几个页。只要理解了“数据库的瓶颈在磁盘IO”你就能明白为什么索引的价值这么大。3.2 聚簇索引与非聚簇索引别忽略存储引擎的影响讲到索引必然牵扯到InnoDB和MyISAM的差异。现在的生产库基本都是InnoDB但面试里“主键索引和唯一索引的区别”“存储引擎的影响”这类问题本质都和“聚簇”这个概念有关。InnoDB是聚簇索引组织表表里的每一行数据就存放在主键索引的B树叶子节点上。也就是说你用主键查询一次索引查找就能拿到整行数据不需要再回表。MyISAM则是典型的非聚簇索引索引文件和数据文件分离主键索引的叶子节点只存数据行的物理地址查询主键还得再按地址读一次数据文件。这件事带来一个实战建议每张InnoDB表都要有主键最好还是自增或趋势递增的主键。不然InnoDB会自己找一列唯一字段当聚簇索引找不到就生成一个隐藏主键。用UUID当主键则容易造成大量随机的页分裂写入性能会明显受损。3.3 主键索引和唯一索引的区别这个问题我在面试别人时经常问看起来简单其实不少人答不清楚。对比项主键索引唯一索引本质作用唯一标识一条记录兼作聚簇索引保证某个字段值唯一每个表数量最多1个可以有多个是否允许NULL不允许允许但NULL可重复数据存储叶子节点就是整行数据叶子节点存主键值查询路径主键查找直接拿到行先定位到主键再回表查数据唯一索引的高频误区是“唯一索引能保证NULL不重复”。实际上InnoDB不把NULL当作相等一张表里可以插入多条phoneNULL的记录哪怕你在phone上建了唯一索引。如果业务上要求“空值只能出现一次”唯一索引满足不了需要在应用层做额外控制。3.4 覆盖索引让查询连回表都省了二级索引普通索引、组合索引的叶子节点存的是主键值不是整行数据。当你通过二级索引查到记录后还要拿主键再去聚簇索引里回表才能读到完整数据。回表次数一多性能也会下降。覆盖索引就是让查询所需的列全部包含在索引中这样连回表都省了。比如这次订单优化我后来把业务语句改成只查必要字段SELECT user_id, status, create_time FROM order_info WHERE user_id 12345 AND status 1 ORDER BY create_time DESC LIMIT 20;当组合索引是(user_id, status, create_time)时这三个字段都在索引里EXPLAIN的Extra会变成Using index意思是MySQL直接从索引树里取到了全部返回数据不再回表。对一些高频小查询覆盖索引的效果比单纯加一个索引更明显。4. 组合索引实战where条件里有a和b到底怎么建字段顺序这是很多同事最头疼的部分单列索引好理解但WHERE条件有两个甚至三个列组合索引该怎么建顺序错了索引就会白建。4.1 最左前缀原则别把组合索引理解成“多个独立索引”组合索引(a, b, c)的内部结构相当于先按a排序a相同再按b排序b相同再按c排序。你可以把它想象成一本先按“姓氏”排、再按“名字首字母”排、再按“名字长度”排的通讯录。这意味着索引能高效命中的查询模式是WHERE a ?可以用到a这一列WHERE a ? AND b ?可以用到a和b两列WHERE a ? AND b ? AND c ?可以完整用到三列但如果直接跳过a比如WHERE b ?或者WHERE c ?索引就帮不上忙。这就是最左前缀原则。很多新人以为组合索引(a,b,c)等价于给a、b、c各建了一个单列索引实际不等于。4.2 字段顺序怎么排从选择性、等值、范围到排序回到这次订单表的例子查询条件是user_id 12345 AND status 1排序是ORDER BY create_time DESC。设计组合索引字段顺序时我一般按这个顺序来思考区分度高的列优先。user_id的取值数量远大于status的取值数量所以user_id放在最左边能最快缩小范围。等值条件放前面范围条件放后面。两个条件都是等值那区分度高的优先。排序字段尽量排进索引。create_time虽然不走过滤条件但它参与排序把它放进索引的末尾有机会消除filesort。最终方案(user_id, status, create_time)。ALTER TABLE order_info ADD INDEX idx_user_status_createtime (user_id, status, create_time);这个索引有三重价值。第一查询定位到user_id12345的区间第二在区间内继续用status1过滤第三区间内记录已经按create_time有序ORDER BY create_time DESC可以反向扫描直接取20条省掉filesort。对比一下错误示范如果建的是(status, user_id, create_time)status在前区分度低优化器可能还是能走索引但扫描范围会大很多如果建的是(user_id, create_time, status)那status过滤就要在查完user_id区间后再逐行判断效率略低。这些细微差别在千万级数据上会放大成明显延迟。4.3 排序与范围查询ORDER BY怎么才能不高价很多人没意识到ORDER BY同样可以走索引。如果查询已经通过索引确定了user_id12345、status1这个区间而组合索引恰好包含create_time在末尾那这个区间内的记录天然就是按create_time升序排列的MySQL直接反向扫一遍就能拿到DESC结果。但如果你建的索引只到(user_id, status)没有create_timeMySQL会把找到的记录单独排一次序。数据量小无所谓几百万行时就会出现Using filesort排序消耗的CPU和临时空间都不少。这里还有个更隐蔽的场景WHERE a 1 AND b 100 ORDER BY c。如果索引是(a, b, c)b是范围条件b之后的c列无法继续用于排序MySQL还是可能filesort。因为b100跨越了多个b值在这些b值下的c不保证全局有序。所以遇到范围条件时要特别小心它后面的索引列基本就“废”了。4.4 降序索引与“双向索引”反向排序不再当冤大头MySQL 8.0之前索引列只能按升序存储你在建索引时写DESC其实也会被忽略。这就导致ORDER BY create_time DESC要么反向扫描性能稍差要么干脆filesort。MySQL 8.0引入了降序索引允许在创建索引时指定降序ALTER TABLE order_info ADD INDEX idx_user_status_createtime (user_id, status, create_time DESC);如果业务查询大量是ORDER BY create_time DESC这个降序索引就能更自然地从左向右顺序扫描减少反向扫描的开销。社区里偶尔会听到“双向索引”的说法其实就是在说B树叶子节点通过双向链表连接正序反序都能高效访问配合降序索引就能把两个方向的排序都优化到位。如果你还在用MySQL 5.7不要指望索引能同时优化升序和降序混合排序必要时可以把业务排序字段提前到组合索引靠前的位置。5. 索引失效排查建了索引却不走多半是踏进了这六个坑这是让我踩过最多坑的部分。明明EXPLAIN显示possible_keys里能看到索引但key是NULL或者rows还是几百万。下面这六个场景是生产环境里最常见的索引失效原因建议收藏当速查表。5.1 六个容易让索引失效的写法场景一在索引列上做函数运算WHERE DATE(create_time) 2024-01-01只要对索引列套了函数优化器就无法再利用B树的有序性因为函数会把原始列值变换掉。改写方式是范围查询WHERE create_time 2024-01-01 00:00:00 AND create_time 2024-01-02 00:00:00场景二隐式类型转换表字段是VARCHAR查询却传了数字WHERE phone 13800000000MySQL会把索引列做隐式转换导致索引失效。改成字符串写法就好WHERE phone 13800000000判断核心就一句话是索引列本身被转换还是查询值被转换。只有索引列被转换才失效。场景三LIKE以通配符开头WHERE name LIKE %abc以%开头的模糊查询B树不知道从哪开始匹配只能全表扫。但abc%这种前缀匹配是可以走索引的。业务上真要搜中间字符串一般建议走全文索引或搜索引擎。场景四OR连接多个条件WHERE user_id 12345 OR status 1OR会让优化器需要同时判断两个条件如果其中一列没有索引就很可能放弃索引全表扫描。即使两列都有索引MySQL的Index Merge机制也不是什么时候都高效。更稳的方式是拆成两个查询用UNION ALL或者尽量让其中一个条件过滤性足够强。场景五违反最左前缀原则组合索引(a, b, c)却直接WHERE b 1 AND c 2跳过a索引大概率用不上。SQL要能走上这个组合索引最先出现条件里必须包含a。场景六范围查询后面的列继续当过滤条件WHERE user_id 12345 AND create_time 2024-01-01 AND status 1如果索引是(user_id, create_time, status)create_time是范围条件它后面的status无法继续利用索引只能做回表后的过滤。正确做法往往是把等值条件status放在create_time前面比如(user_id, status, create_time)。5.2 判断索引是否生效EXPLAIN和optimizer trace遇到“索引建了但没生效”先用EXPLAIN确认现状重点看type、key、rows三个字段。如果type不是ALL了key有值了rows明显下降了说明索引生效。如果还是ALL就把上面六个场景逐个对照SQL。有时候优化器不太听话明明有索引它就是不用。这种情况我会开optimizer trace看看优化器到底怎么评估成本SET optimizer_trace enabledon; -- 执行一遍目标SQL SELECT * FROM order_info WHERE user_id 12345 AND status 1; SELECT * FROM information_schema.OPTIMIZER_TRACE\G SET optimizer_trace enabledoff;trace里能看到优化器比较全表扫描和索引扫描成本的具体数值。说实话大部分时候是统计信息过期了让优化器低估了索引的效果。这时候执行ANALYZE TABLE通常能解决问题。5.3 关于视图加索引和索引表空间的几个疑问后台经常收到这几个关联问题我一起说清楚。第一个是“Oracle视图能加索引吗”。普通视图本质是一段SQL虚拟出来的结果集合没有物理存储所以没法直接给它建索引。你需要在视图查询涉及到的基表列上建索引。如果查询很复杂、聚合很重、还希望有缓存效果Oracle里可以考虑物化视图物化视图有实体数据就可以建索引了。MySQL原生没有物化视图业务上基本都是用汇总表自己实现。第二个是“索引表空间”相关。InnoDB里索引和数据存储在同一个.ibd表空间文件里。innodb_file_per_tableON的情况下每张表独立一个表空间这样便于恢复和空间回收。如果关闭了这个参数所有表和索引都堆在共享表空间ibdata1里出问题很难清理。我强烈建议保持ON。第三个是“索引不是越多越好”。每多一个索引B树就要多维护一份插入、更新、删除时都要同步变更写入性能会跟着下降。有些表我见过建了七八个单列索引其实完全可以用两三个组合索引覆盖掉。删除冗余索引之前至少观察一段时间的慢查询日志确认没有查询依赖它。6. 常见问题与排查技巧实录最后把这几年积累的排查经验整理成速查表遇到同类的报错和故障可以直接照着排查。6.1 常见问题速查表问题现象可能原因处理建议索引建了但EXPLAIN显示keyNULL索引列函数运算、隐式转换、LIKE前置%等逐条对照5.1的六个场景改写SQL组合索引只命中了一部分跳过了最左前列或范围条件后的列被放弃调整查询条件顺序或索引列顺序加上索引后写入变慢索引数量太多写放大严重评估业务写读比例删除冗余索引同一条SQL时快时慢统计信息过期或数据分布波动ANALYZE TABLE刷新统计信息慢查询日志日志文件增长过快long_query_time过短或未限流调大阈值打开log_throttle参数走索引后回表次数太多查询的列不在索引中改成覆盖索引或减少SELECT返回列大量查询走全表扫描反而更快查询要返回的行占比太高不要强行加索引考虑查询重写或缓存这张表最下面那一行尤其值得注意。优化器选择全表扫描有时候是对的比如你要查询的行占了全表的30%用二级索引回表可能比顺序扫描还慢因为回表是随机IO。不要为了“看起来走了索引”而牺牲实际性能。6.2 我在实际调优中养成的几个习惯第一个习惯永远先量后优。上grep、慢查询日志、EXPLAIN、性能监控任何一步缺了优化都可能变成拍脑袋。第二个习惯组合索引的设计要“以SQL为中心”不是“以为表为中心”。很多人问我这张表该建什么索引我说不知道你得先告诉我这张表上最频繁、最关键的查询长什么样。同一个用户表有的场景是登录查询有的场景是列表分页需要的索引完全不同。第三个习惯上线后一定要做前后对比。我这次优化后把EXPLAIN的type、rows、Extra输出和优化前放在同一个文档里明显看到type从ALL变成refrows从4800万降到1350Using filesort消失。做压力测试时接口P99从2s以上稳定在60ms以内。没有这些对比数据你很难知道一次优化到底有没有真正解决瓶颈。第四个习惯持续把慢查询优化做成例行机制。我会定期扫一遍慢查询日志把新出现的慢SQL归档然后在测试库复现、优化、记录。这套流程跑熟了以后很多问题在用户感知到之前就已经处理掉了。最后分享一个小技巧。我办公桌上一直放着个文档每次给慢SQL做EXPLAIN都会把type、rows、Extra这三个字段抄下来优化完成后再抄一遍新的。坚持几个月后你对“这条SQL应该配什么样的索引”会形成一种很自然的直觉。慢查询和索引优化说到底就是反复练习判断“数据库读取路径”的过程练得多了速度提升其实是水到渠成的事。