做数据库优化这些年我越来越觉得 MySQL 8.0 的索引调优本质上不是背几条“加索引、避免 select *”的口诀就能解决的而是先学会看清执行计划理解底层索引结构最后再决定到底加不加索引、怎么加。三百万行数据的订单表一个简单的条件查询要跑三秒多第一次接手这个系统时我还以为是机器配置不行。把慢查询日志捞出来一看SQL 并不复杂就一个 WHERE 条件加一个 ORDER BY问题完全出在索引设计上。这是很多业务系统都会踩的坑也是我想认真聊聊 MySQL 8.0 索引调优的原因。这篇文章不聊虚的全部都是线上排查中总结出来的实战经验适合刚接触数据库调优的开发者也适合正在被慢查询折腾的运维和 DBA。1. 一次慢查询故障把索引问题聊透1.1 故障现场三百万行的订单查询三秒多先还原一下当时的场景。某个电商项目的订单表orders存量数据三百万行左右每天新增大概两万行。业务方反馈后台订单查询页面很慢一个简单的列表接口响应时间经常超过三秒。当时抓出来的核心 SQL 大概是这样的SELECT order_id, user_id, status, amount, create_time FROM orders WHERE user_id 12345 AND status PAID ORDER BY create_time DESC LIMIT 20;单看 SQL 本身这几乎是教科书级别的常规查询。但执行计划一出来问题就暴露了参数优化前typeALLkeyNULLrows3200000filtered1.2ExtraUsing where; Using filesorttype 是 ALL意思是全表扫描MySQL 把三百多万行数据全部读了一遍再去过滤 user_id 和 status最后还得做文件排序。这种情况无论机器多好都扛不住。其实解决办法不复杂但要理解为什么这么快就能定位到问题需要对 MySQL 8.0 的索引机制有清晰认识。这个案例让我意识到日常调优最多的问题不是 SQL 写得多烂而是索引根本没建对。很多团队建索引的时候只考虑“哪个字段经常作为查询条件就先放哪个”完全不考虑字段区分度、排序需求、覆盖索引这些因素等数据量上来之后才发现查询性能断崖式下跌。1.2 MySQL 8.0 里索引定位已经变了先说一个很多老 DBA 容易忽略的点MySQL 8.0 相比 5.7索引方面的能力有了明显升级如果还在按 5.7 的老思路去优化等于拿着旧地图找新大陆。8.0 引入了几个关键特性降序索引5.7 里虽然能定义INDEX (col1 DESC)但优化器并不会真正使用反向扫描排序优化效果有限8.0 真正支持降序遍历对ORDER BY ... DESC的场景非常友好。不可见索引加索引之前可以先用INVISIBLE隐藏测试完再决定是否保留避免上线后才发现索引影响写入性能。函数索引8.0.13 之后支持INDEX ((LOWER(col)))这种表达式索引对函数查询场景有奇效。直方图对非索引列的统计信息有了更精细的刻画优化器选执行计划的依据更靠谱。这些特性意味着调优思路要从“建完索引看效果”变成“按场景设计索引”。比如说页面要做大量按时间倒序查询就可以直接用降序索引比如某个字段经常会用UPPER()或LOWER()做条件匹配可以建函数索引而不是改写 SQL。这些我会在后面的实战案例中详细展开。2. B 树索引的核心原理与匹配规则2.1 为什么 B 树能这么快很多人看到“B 树”三个字就觉得是理论知识考完试就忘了。但在实际调优中不理解 B 树根本没法解释为什么某个联合索引会失效也没法判断新加的索引能不能被用到。B 树是一棵多路平衡查找树它的核心特点是所有数据都存在叶子节点并且叶子节点之间用指针串联成一个有序链表。这就带来两个直接收益。第一查找复杂度稳定在O(log N)三百万行数据只需要二十多次磁盘 I/O 的级别远低于全表扫描。第二因为叶子节点有序且相连范围查询和排序操作天然高效不再需要额外排序。可以这样类比B 树索引就像一本工具书最后附的术语索引。你想查“索引优化”这个词不会从第一页翻到最后一页而是直接翻到索引页找到“索引优化P234”然后跳到那一页。数据库里的 B 树就是在做同样的事情只不过它的数据量大了几个数量级。但要注意B 树索引不是银弹。如果查询条件无法匹配索引前缀优化器宁可全表扫描也不走索引。这就是为什么LIKE %abc、OR条件乱搭、隐式类型转换这些场景索引会“失效”。2.2 聚簇索引、二级索引与回表InnoDB 存储引擎里表本身就是按主键组织的 B 树结构这棵树的叶子节点存的就是整行数据称为聚簇索引。除了聚簇索引之外的其他索引都叫二级索引二级索引的叶子节点存的是索引列的值加上主键值。这里就引出一个关键概念——回表。假设表上有普通索引idx_user_id(user_id)执行SELECT * FROM orders WHERE user_id 12345时MySQL 会先到二级索引idx_user_id找到主键值再拿着主键去聚簇索引里查整行数据。这就是回表相当于查两次 B 树。如果查询的列恰好都被索引覆盖了就不需要回表这叫覆盖索引。比如SELECT order_id, user_id FROM orders WHERE user_id 12345如果idx_user_id是(user_id, order_id)的联合索引那直接扫描索引就把数据拿全了性能自然高很多。所以调优时我一直提倡一个理念能用覆盖索引解决的问题绝不靠回表硬扛。特别是报表查询、列表分页这种高频场景覆盖索引带来的提升往往比加普通索引高一倍还多。2.3 联合索引的最左前缀不只是靠前联合索引的顺序设计是索引调优里最容易被搞砸的一件事。很多人的理解停留在“查询条件里必须包含最左边的字段”这话没错但不够。以联合索引(a, b, c)为例以下查询能用到索引WHERE a 1 WHERE a 1 AND b 2 WHERE a 1 AND b 2 AND c 3 WHERE a 1 AND c 3 -- 注意a 可以用索引c 用不了以下查询用不了索引WHERE b 2 WHERE c 3 WHERE b 2 AND c 3原因是 B 树联合索引的排序规则是先按 a 排再按 b、c 排所以无法跳过 a 直接从 b 开始。但这里有一个进阶细节联合索引除了服务WHERE条件还能服务ORDER BY。例如(user_id, status, create_time)这个联合索引既能匹配WHERE user_id ? AND status ?也能让ORDER BY create_time DESC直接按索引顺序读取避免 filesort。设计联合索引时把等值条件的字段放前面把排序字段放后面这几乎是最实用的联合索引设计套路。另一个容易忽略的点是字段顺序对索引空间和查询效率的影响。把区分度高的字段放前面比如 user_id 比 status 区分度高那么索引树在过滤时能尽早剪枝减少扫描范围。经验法则先放等值条件字段再放范围条件字段最后放排序列。3. 索引优化前的三件套慢日志、EXPLAIN、optimizer_trace3.1 慢查询日志怎么配置和采集调优的第一步永远是找到“慢在哪”。MySQL 8.0 的慢查询日志需要进行合理配置才能做到既不影响性能又能精准捕获问题 SQL。推荐比较实用的配置slow_query_log ON slow_query_log_file /var/log/mysql/slow.log long_query_time 1 log_queries_not_using_indexes ON min_examined_row_limit 1000long_query_time设成 1 秒抓取超过 1 秒的查询。线上环境先按 1 秒起步后续如果慢日志太多再调到 2 秒。log_queries_not_using_indexes配合min_examined_row_limit使用可以抓出那些“没走索引但扫描行数超过 1000 行”的 SQL。这里有个坑只开log_queries_not_using_indexes而不设行数下限会把大量小表扫描日志也记录下来日志膨胀得很快。拿到慢日志之后推荐用mysqldumpslow工具做聚合按执行次数和总耗时排序快速找出最耗时的 TOP N 语句。很多团队用 pt-query-digest但临时排查的话mysqldumpslow完全够用。3.2 EXPLAIN 关键列精读EXPLAIN SELECT ...是索引调优最常用的工具。很多人知道看type和key但这两列之外的信息同样关键。我习惯按下面这个顺序读执行计划列名含义重点关注type访问类型出现 ALL 基本是警钟range、ref、eq_ref、const 都算健康key实际选中的索引NULL 意味着索引没用上key_len索引使用的字节数判断联合索引到底用了几个字段rows预估扫描行数数据量越大越要警惕filtered存储引擎过滤后的比例值太低说明索引选择性差Extra附加信息出现 Using filesort、Using temporary 要小心重点讲两个容易忽略的细节。第一个是key_len。联合索引(user_id, status, create_time)中如果key_len只显示 user_id 的长度说明 SQL 最终只用了联合索引的第一个字段。这时候需要回过头检查是否漏了等值条件还是字段类型不匹配导致索引无法继续匹配。第二个是Extra里的Using index condition这是 MySQL 5.6 引入的索引下推特性8.0 继续沿用。它表示存储引擎在扫描索引时就直接过滤了一部分条件减少了回表次数。看到这个词不需要紧张它反而是优化器在努力工作的体现。3.3 用 optimizer_trace 看决策过程EXPLAIN 能看到结果但看不到优化器做决策的过程。有一次排查一条奇怪 SQLEXPLAIN 显示索引 A 明明更合适优化器偏偏选了索引 B查了很久都查不出原因。后来用 MySQL 8.0 的optimizer_trace看到了完整决策链才发现是统计信息不准导致优化器对行数估算偏差太大。使用方式很简单SET optimizer_trace enabledon; SELECT * FROM orders WHERE user_id 12345 AND status PAID ORDER BY create_time DESC LIMIT 20; SELECT * FROM information_schema.OPTIMIZER_TRACE;输出里有一个considered_execution_plans字段里面会列出优化器把哪些索引作为候选比较了哪些因素最后为什么选了某个计划。这对理解“明明是 range 为什么没用索引”这类问题非常有用。如果用户用的是 MySQL 8.0还可以配合explain analyzeMySQL 8.0.18 后提供查看实际执行成本和耗时对比优化器估算值与实际值的差异定位统计信息的偏差。4. 五类常见慢查询的索引优化实战4.1 案例一简单 WHERE 没走索引还是开头的orders表查询。原始 SQL:SELECT order_id, user_id, status, amount, create_time FROM orders WHERE user_id 12345 AND status PAID ORDER BY create_time DESC LIMIT 20;这个查询有三个诉求按user_id精确过滤按status精确过滤按create_time倒序取最新 20 条。最初表上只有一个主键order_id查询走全表扫描。考虑到三个条件都是常见组合最合理的索引设计就是联合索引ALTER TABLE orders ADD INDEX idx_user_status_time (user_id, status, create_time DESC);这里用了 MySQL 8.0 的降序索引。如果表是 5.7create_time DESC这个索引定义写上去实际效果跟 ASC 差不多优化器还是得做 filesort。但在 8.0 中create_time DESC是真正的降序 B 树顺序配合 LIMIT 20直接扫索引头 20 条就返回了。优化之后的执行计划参数优化前优化后typeALLrefkeyNULLidx_user_status_timerows32000003filtered1.2100ExtraUsing where; Using filesortNULL查询时间从三秒多降到个位数毫秒级别。三百万行扫描变成三行扫描这就是索引设计对不对的差距。4.2 案例二ORDER BY 触发文件排序再来看一个典型的filesort问题。业务方有一个报表需求需要按用户维度查最近半年的订单按创建时间倒序取前 100 条。SQL 形如SELECT user_id, amount, create_time FROM orders WHERE create_time DATE_SUB(NOW(), INTERVAL 6 MONTH) AND user_id IN (1001, 1002, 1003, 1004) ORDER BY create_time DESC LIMIT 100;这个 SQL 的问题在于如果只在create_time上建了单列索引WHERE过滤和ORDER BY排序正好冲突。走create_time索引能过滤时间范围但排序字段已经在索引顺序里了走user_id相关索引能快速匹配用户列表但create_time的顺序就被打乱需要额外排序。我的处理思路是拆条件。先确认user_id IN后面这个列表通常多大。如果结果集已经很小那直接在(user_id, create_time DESC)联合索引上走range再排序的成本不高。如果IN列表很大可以考虑把 IN 拆成多个等值查询然后用 UNION 合并但这样改造幅度比较大不建议一上来就做。实际操作中我对orders表加了(user_id, create_time DESC)联合索引并调整 SQL 为只查必要字段最终执行计划中 Extra 列从Using filesort变成了Using index condition排序需求天然满足响应时间从 800ms 下降到 80ms 左右。这里要提醒一句ORDER BY方向必须和索引方向一致。索引是create_time ASC你查ORDER BY create_time DESC在 8.0 之前可能还是得排序在 8.0 中如果索引定义反了也会影响效果。选降序索引时要看清业务里到底哪种排序更常见。4.3 案例三函数操作与隐式类型转换函数操作让索引失效这是比较常见的问题。比如用户表里有手机号字段mobile定义为varchar(20)但业务里经常用WHERE mobile 13800138000这种数字形式查询。因为字段类型是字符型查询条件是数字型MySQL 会做隐式类型转换等于把mobile套了一个 CAST 函数索引自然失效。这类问题的排查办法是先看表结构和慢日志里的实际 SQLSHOW CREATE TABLE users;如果字段确实是varchar而 SQL 里写成数字最直接的修复方案是改写 SQLWHERE mobile 13800138000但业务代码多挨个改不现实。MySQL 8.0 提供了函数索引可以直接在数字转换后的表达式上建索引ALTER TABLE users ADD INDEX idx_mobile_cast ((CAST(mobile AS UNSIGNED)));这样WHERE mobile 13800138000仍会触发隐式转换但只要转换函数和索引表达式一致优化器也能匹配上索引。这是 8.0 带来的实打实的便利同样的思路也适用于LOWER(email)、DATE(create_time)这类函数查询。不过函数索引不适合滥用。每次写入都要计算函数结果增加 CPU 开销索引空间也会变大。还是优先建议从代码层修正类型函数索引作为兜底方案。4.4 案例四深分页 LIMIT 太慢分页接口越往后翻越慢这是几乎每个业务系统都会碰到的问题。核心 SQL 长这样SELECT order_id, user_id, amount, create_time FROM orders ORDER BY create_time DESC LIMIT 200000, 20;表面看有create_time索引执行计划也不是全表扫描但为什么慢因为 InnoDB 必须先把前 200000 行全部扫描出来再抛弃前面 199980 行最后只返回 20 行。这个过程中的行读取和回表操作是相当大的开销。优化思路有两种。第一种是延迟关联延迟 join。先只查主键再回表拿完整数据SELECT o.order_id, o.user_id, o.amount, o.create_time FROM orders o INNER JOIN ( SELECT order_id FROM orders ORDER BY create_time DESC LIMIT 200000, 20 ) t ON o.order_id t.order_id ORDER BY o.create_time DESC;子查询只查order_id这一列被create_time索引覆盖不需要回表扫描 200000 行的成本大幅降低。外层再把 20 个主键回表总共就 20 次点查性能提升非常明显。第二种方案的思路是记录上一页位置的游标分页WHERE create_time 2024-06-01 12:00:00 ORDER BY create_time DESC LIMIT 20;这种方式适合用户持续下拉翻页、不需要随机跳页的场景。我实际对比下来同样的数据量传统深分页 1.8 秒游标分页 35 毫秒差距接近两个数量级。改造成本也不高只需要把前端页码参数换成游标值就行。4.5 案例五OR 条件乱用联合索引还有一个容易让索引失效的场景是OR条件。假设表上有联合索引(user_id, status)SQL 写成SELECT * FROM orders WHERE user_id 12345 OR status REFUND;这个查询很难用好联合索引。因为OR表示两个条件都可能成立优化器要对两边分别评估。如果一个走索引另一个全表扫描优化器可能直接选择全表扫描因为合并结果集并不划算。实际测过之后我发现比较靠谱的改写方式是用 UNIONSELECT * FROM orders WHERE user_id 12345 UNION SELECT * FROM orders WHERE status REFUND;UNION 两边各自用自己的最优索引结果合并再去重。不过要注意两边结果集如果很大UNION 的去重成本也不低需要结合实际数据分布判断。如果两个 OR 条件被拆开后大多是小结果集合起来性价比更高如果两边都是大范围数据可能全表扫描反而更稳。除了改写 SQL也可以考虑在status单列上再加一个索引让优化器多一个选择。但索引不是越多越好需要权衡写入开销这一点后面我会专门展开。5. 调优后的验证与性能对照5.1 优化前后执行计划对比每次做索引调整我都会把优化前后的执行计划放在一起对比至少确认四点type 是否从 ALL 变成 range 或 refkey 是否真正落到新索引上rows 是否明显减少Extra 是否消除了 filesort 和 temporary。以订单查询那个案例为例完整对比是这样的维度优化前优化后typeALLrefkeyNULLidx_user_status_timerows32000003filtered1.2100ExtraUsing where; Using filesortNULL耗时约 3.2s约 8ms确保key_len符合预期也很重要。如果联合索引(user_id, status, create_time)的key_len只匹配到 user_id说明 SQL 只用到索引的第一列后续字段没有接入匹配流程。检查方式很简单把表结构调整和 SQL 条件一列一列对照看类型是否一致、是否有函数包裹。5.2 压测结果与收益数据调整完索引不能只看执行计划还要做压测验证。我一般用 sysbench 或者直接用生产环境的只读流量做灰度对比。继续以订单查询为例。我在测试环境用 500 万行数据模拟了三种场景无索引平均耗时 3.5 秒CPU 占用接近 90%。单列索引(user_id)平均耗时 180 毫秒但 ORDER BY 仍需 filesort耗时在 150-200ms 波动。联合索引(user_id, status, create_time DESC)平均耗时 8 毫秒CPU 占用下降 70%。从这个对比可以明显看出索引设计不单影响单条 SQL 的耗时还会影响整个数据库的 CPU 消耗。三百万行数据全表扫描会把数据库的缓冲池全部污染掉其他热数据的查询也会被拖慢。这也是为什么慢查询优化要趁早数据量大了再回头改索引风险大收益小。6. 索引调优的常见坑与长期机制6.1 加了索引却没生效的五个原因在实际排查中最常见的困惑是“我明明加了索引为什么 EXPLAIN 还是不用”我总结下来大概有五个原因查询条件对索引列做了函数运算或表达式运算比如WHERE DATE(create_time) 2024-06-01。隐式类型转换比如 varchar 列和数字比较。LIKE以通配符开头LIKE %keyword没法走索引。优化器觉得走索引比全表扫描更慢这种情况常见于小表或者索引区分度极低的列。统计信息过期优化器基于错误的 rows 估算做了错误选择。针对第 5 点MySQL 8.0 可以通过ANALYZE TABLE手动更新统计信息也可以直接EXPLAIN ANALYZE对比实际执行和估算差异。如果统计信息频繁不准建议检查是否开启了自动统计信息更新并合理设置innodb_stats_auto_recalc相关参数。6.2 索引不是越多越好很多团队有一个误区觉得查询慢就加索引结果一张表上建了十几个索引写入性能严重下降磁盘空间也暴涨。这里要理解索引的成本。每个二级索引都是一棵独立的 B 树每次 INSERT、UPDATE、DELETE 都要同步维护所有索引树。索引越多写放大越严重。特别是高并发写入的订单表、日志表索引数量直接决定写入吞吐上限。我的经验判断标准是单表常规索引控制在 4-6 个以内每个联合索引的字段数控制在 3-4 个左右超过这个量就需要警惕。同时定期用performance_schema或者sys.schema_unused_indexes找出那些“建了却从未被使用”的索引尽早删除。MySQL 8.0 的不可见索引在这里特别好用。不确定某个索引是否有用直接ALTER TABLE table ALTER INDEX idx_name INVISIBLE把它藏起来跑一段时间的业务如果没有告警和慢查询再动手删除。这比直接删索引安全太多。6.3 日常巡检与自动化建议索引调优应该是一个持续优化的过程而不是一次性的救援。我的习惯是建立一套常规巡检机制数据量上来之后每半个月跑一次开启慢查询日志设置合理阈值定期分析 TOP SQL。每周对比一次常驻 SQL 的执行计划发现执行计划漂移就及时处理。用sys.schema_unused_indexes和sys.schema_redundant_indexes检查冗余索引。比如已经有(a, b)联合索引又建了(a)单列索引后者大概率就是冗余的。结合业务数据增长情况定期评估索引的区分度和选择率。在工具层面如果需要长期做性能监控可以考虑接入 Prometheus mysqld_exporter或者使用开源的数据库巡检工具。但工具只是辅助最重要的还是对业务查询模式的理解。索引设计没有一劳永逸的银弹只有对着真实 SQL、真实数据分布去不断验证、调整才能让 MySQL 8.0 跑出应有的性能。最后再分享一个我实际工作中的小习惯每次做完索引调整一定要把优化前后的执行计划、耗时数据、影响到的业务接口都记录到文档里。不要嫌麻烦几个月后线上出现诡异问题这份记录往往能帮你快速定位是“哪次变更导致了性能波动”。数据库调优最怕的就是凭感觉改改完不留痕出了问题无从下手。 
长河君 · 2026/10/10 13:00:35 · 阅读 1.2k MySQL 8.0索引调优实战：从执行计划到慢查询优化
做数据库优化这些年我越来越觉得 MySQL 8.0 的索引调优本质上不是背几条“加索引、避免 select *”的口诀就能解决的而是先学会看清执行计划理解底层索引结构最后再决定到底加不加索引、怎么加。三百万行数据的订单表一个简单的条件查询要跑三秒多第一次接手这个系统时我还以为是机器配置不行。把慢查询日志捞出来一看SQL 并不复杂就一个 WHERE 条件加一个 ORDER BY问题完全出在索引设计上。这是很多业务系统都会踩的坑也是我想认真聊聊 MySQL 8.0 索引调优的原因。这篇文章不聊虚的全部都是线上排查中总结出来的实战经验适合刚接触数据库调优的开发者也适合正在被慢查询折腾的运维和 DBA。1. 一次慢查询故障把索引问题聊透1.1 故障现场三百万行的订单查询三秒多先还原一下当时的场景。某个电商项目的订单表orders存量数据三百万行左右每天新增大概两万行。业务方反馈后台订单查询页面很慢一个简单的列表接口响应时间经常超过三秒。当时抓出来的核心 SQL 大概是这样的SELECT order_id, user_id, status, amount, create_time FROM orders WHERE user_id 12345 AND status PAID ORDER BY create_time DESC LIMIT 20;单看 SQL 本身这几乎是教科书级别的常规查询。但执行计划一出来问题就暴露了参数优化前typeALLkeyNULLrows3200000filtered1.2ExtraUsing where; Using filesorttype 是 ALL意思是全表扫描MySQL 把三百多万行数据全部读了一遍再去过滤 user_id 和 status最后还得做文件排序。这种情况无论机器多好都扛不住。其实解决办法不复杂但要理解为什么这么快就能定位到问题需要对 MySQL 8.0 的索引机制有清晰认识。这个案例让我意识到日常调优最多的问题不是 SQL 写得多烂而是索引根本没建对。很多团队建索引的时候只考虑“哪个字段经常作为查询条件就先放哪个”完全不考虑字段区分度、排序需求、覆盖索引这些因素等数据量上来之后才发现查询性能断崖式下跌。1.2 MySQL 8.0 里索引定位已经变了先说一个很多老 DBA 容易忽略的点MySQL 8.0 相比 5.7索引方面的能力有了明显升级如果还在按 5.7 的老思路去优化等于拿着旧地图找新大陆。8.0 引入了几个关键特性降序索引5.7 里虽然能定义INDEX (col1 DESC)但优化器并不会真正使用反向扫描排序优化效果有限8.0 真正支持降序遍历对ORDER BY ... DESC的场景非常友好。不可见索引加索引之前可以先用INVISIBLE隐藏测试完再决定是否保留避免上线后才发现索引影响写入性能。函数索引8.0.13 之后支持INDEX ((LOWER(col)))这种表达式索引对函数查询场景有奇效。直方图对非索引列的统计信息有了更精细的刻画优化器选执行计划的依据更靠谱。这些特性意味着调优思路要从“建完索引看效果”变成“按场景设计索引”。比如说页面要做大量按时间倒序查询就可以直接用降序索引比如某个字段经常会用UPPER()或LOWER()做条件匹配可以建函数索引而不是改写 SQL。这些我会在后面的实战案例中详细展开。2. B 树索引的核心原理与匹配规则2.1 为什么 B 树能这么快很多人看到“B 树”三个字就觉得是理论知识考完试就忘了。但在实际调优中不理解 B 树根本没法解释为什么某个联合索引会失效也没法判断新加的索引能不能被用到。B 树是一棵多路平衡查找树它的核心特点是所有数据都存在叶子节点并且叶子节点之间用指针串联成一个有序链表。这就带来两个直接收益。第一查找复杂度稳定在O(log N)三百万行数据只需要二十多次磁盘 I/O 的级别远低于全表扫描。第二因为叶子节点有序且相连范围查询和排序操作天然高效不再需要额外排序。可以这样类比B 树索引就像一本工具书最后附的术语索引。你想查“索引优化”这个词不会从第一页翻到最后一页而是直接翻到索引页找到“索引优化P234”然后跳到那一页。数据库里的 B 树就是在做同样的事情只不过它的数据量大了几个数量级。但要注意B 树索引不是银弹。如果查询条件无法匹配索引前缀优化器宁可全表扫描也不走索引。这就是为什么LIKE %abc、OR条件乱搭、隐式类型转换这些场景索引会“失效”。2.2 聚簇索引、二级索引与回表InnoDB 存储引擎里表本身就是按主键组织的 B 树结构这棵树的叶子节点存的就是整行数据称为聚簇索引。除了聚簇索引之外的其他索引都叫二级索引二级索引的叶子节点存的是索引列的值加上主键值。这里就引出一个关键概念——回表。假设表上有普通索引idx_user_id(user_id)执行SELECT * FROM orders WHERE user_id 12345时MySQL 会先到二级索引idx_user_id找到主键值再拿着主键去聚簇索引里查整行数据。这就是回表相当于查两次 B 树。如果查询的列恰好都被索引覆盖了就不需要回表这叫覆盖索引。比如SELECT order_id, user_id FROM orders WHERE user_id 12345如果idx_user_id是(user_id, order_id)的联合索引那直接扫描索引就把数据拿全了性能自然高很多。所以调优时我一直提倡一个理念能用覆盖索引解决的问题绝不靠回表硬扛。特别是报表查询、列表分页这种高频场景覆盖索引带来的提升往往比加普通索引高一倍还多。2.3 联合索引的最左前缀不只是靠前联合索引的顺序设计是索引调优里最容易被搞砸的一件事。很多人的理解停留在“查询条件里必须包含最左边的字段”这话没错但不够。以联合索引(a, b, c)为例以下查询能用到索引WHERE a 1 WHERE a 1 AND b 2 WHERE a 1 AND b 2 AND c 3 WHERE a 1 AND c 3 -- 注意a 可以用索引c 用不了以下查询用不了索引WHERE b 2 WHERE c 3 WHERE b 2 AND c 3原因是 B 树联合索引的排序规则是先按 a 排再按 b、c 排所以无法跳过 a 直接从 b 开始。但这里有一个进阶细节联合索引除了服务WHERE条件还能服务ORDER BY。例如(user_id, status, create_time)这个联合索引既能匹配WHERE user_id ? AND status ?也能让ORDER BY create_time DESC直接按索引顺序读取避免 filesort。设计联合索引时把等值条件的字段放前面把排序字段放后面这几乎是最实用的联合索引设计套路。另一个容易忽略的点是字段顺序对索引空间和查询效率的影响。把区分度高的字段放前面比如 user_id 比 status 区分度高那么索引树在过滤时能尽早剪枝减少扫描范围。经验法则先放等值条件字段再放范围条件字段最后放排序列。3. 索引优化前的三件套慢日志、EXPLAIN、optimizer_trace3.1 慢查询日志怎么配置和采集调优的第一步永远是找到“慢在哪”。MySQL 8.0 的慢查询日志需要进行合理配置才能做到既不影响性能又能精准捕获问题 SQL。推荐比较实用的配置slow_query_log ON slow_query_log_file /var/log/mysql/slow.log long_query_time 1 log_queries_not_using_indexes ON min_examined_row_limit 1000long_query_time设成 1 秒抓取超过 1 秒的查询。线上环境先按 1 秒起步后续如果慢日志太多再调到 2 秒。log_queries_not_using_indexes配合min_examined_row_limit使用可以抓出那些“没走索引但扫描行数超过 1000 行”的 SQL。这里有个坑只开log_queries_not_using_indexes而不设行数下限会把大量小表扫描日志也记录下来日志膨胀得很快。拿到慢日志之后推荐用mysqldumpslow工具做聚合按执行次数和总耗时排序快速找出最耗时的 TOP N 语句。很多团队用 pt-query-digest但临时排查的话mysqldumpslow完全够用。3.2 EXPLAIN 关键列精读EXPLAIN SELECT ...是索引调优最常用的工具。很多人知道看type和key但这两列之外的信息同样关键。我习惯按下面这个顺序读执行计划列名含义重点关注type访问类型出现 ALL 基本是警钟range、ref、eq_ref、const 都算健康key实际选中的索引NULL 意味着索引没用上key_len索引使用的字节数判断联合索引到底用了几个字段rows预估扫描行数数据量越大越要警惕filtered存储引擎过滤后的比例值太低说明索引选择性差Extra附加信息出现 Using filesort、Using temporary 要小心重点讲两个容易忽略的细节。第一个是key_len。联合索引(user_id, status, create_time)中如果key_len只显示 user_id 的长度说明 SQL 最终只用了联合索引的第一个字段。这时候需要回过头检查是否漏了等值条件还是字段类型不匹配导致索引无法继续匹配。第二个是Extra里的Using index condition这是 MySQL 5.6 引入的索引下推特性8.0 继续沿用。它表示存储引擎在扫描索引时就直接过滤了一部分条件减少了回表次数。看到这个词不需要紧张它反而是优化器在努力工作的体现。3.3 用 optimizer_trace 看决策过程EXPLAIN 能看到结果但看不到优化器做决策的过程。有一次排查一条奇怪 SQLEXPLAIN 显示索引 A 明明更合适优化器偏偏选了索引 B查了很久都查不出原因。后来用 MySQL 8.0 的optimizer_trace看到了完整决策链才发现是统计信息不准导致优化器对行数估算偏差太大。使用方式很简单SET optimizer_trace enabledon; SELECT * FROM orders WHERE user_id 12345 AND status PAID ORDER BY create_time DESC LIMIT 20; SELECT * FROM information_schema.OPTIMIZER_TRACE;输出里有一个considered_execution_plans字段里面会列出优化器把哪些索引作为候选比较了哪些因素最后为什么选了某个计划。这对理解“明明是 range 为什么没用索引”这类问题非常有用。如果用户用的是 MySQL 8.0还可以配合explain analyzeMySQL 8.0.18 后提供查看实际执行成本和耗时对比优化器估算值与实际值的差异定位统计信息的偏差。4. 五类常见慢查询的索引优化实战4.1 案例一简单 WHERE 没走索引还是开头的orders表查询。原始 SQL:SELECT order_id, user_id, status, amount, create_time FROM orders WHERE user_id 12345 AND status PAID ORDER BY create_time DESC LIMIT 20;这个查询有三个诉求按user_id精确过滤按status精确过滤按create_time倒序取最新 20 条。最初表上只有一个主键order_id查询走全表扫描。考虑到三个条件都是常见组合最合理的索引设计就是联合索引ALTER TABLE orders ADD INDEX idx_user_status_time (user_id, status, create_time DESC);这里用了 MySQL 8.0 的降序索引。如果表是 5.7create_time DESC这个索引定义写上去实际效果跟 ASC 差不多优化器还是得做 filesort。但在 8.0 中create_time DESC是真正的降序 B 树顺序配合 LIMIT 20直接扫索引头 20 条就返回了。优化之后的执行计划参数优化前优化后typeALLrefkeyNULLidx_user_status_timerows32000003filtered1.2100ExtraUsing where; Using filesortNULL查询时间从三秒多降到个位数毫秒级别。三百万行扫描变成三行扫描这就是索引设计对不对的差距。4.2 案例二ORDER BY 触发文件排序再来看一个典型的filesort问题。业务方有一个报表需求需要按用户维度查最近半年的订单按创建时间倒序取前 100 条。SQL 形如SELECT user_id, amount, create_time FROM orders WHERE create_time DATE_SUB(NOW(), INTERVAL 6 MONTH) AND user_id IN (1001, 1002, 1003, 1004) ORDER BY create_time DESC LIMIT 100;这个 SQL 的问题在于如果只在create_time上建了单列索引WHERE过滤和ORDER BY排序正好冲突。走create_time索引能过滤时间范围但排序字段已经在索引顺序里了走user_id相关索引能快速匹配用户列表但create_time的顺序就被打乱需要额外排序。我的处理思路是拆条件。先确认user_id IN后面这个列表通常多大。如果结果集已经很小那直接在(user_id, create_time DESC)联合索引上走range再排序的成本不高。如果IN列表很大可以考虑把 IN 拆成多个等值查询然后用 UNION 合并但这样改造幅度比较大不建议一上来就做。实际操作中我对orders表加了(user_id, create_time DESC)联合索引并调整 SQL 为只查必要字段最终执行计划中 Extra 列从Using filesort变成了Using index condition排序需求天然满足响应时间从 800ms 下降到 80ms 左右。这里要提醒一句ORDER BY方向必须和索引方向一致。索引是create_time ASC你查ORDER BY create_time DESC在 8.0 之前可能还是得排序在 8.0 中如果索引定义反了也会影响效果。选降序索引时要看清业务里到底哪种排序更常见。4.3 案例三函数操作与隐式类型转换函数操作让索引失效这是比较常见的问题。比如用户表里有手机号字段mobile定义为varchar(20)但业务里经常用WHERE mobile 13800138000这种数字形式查询。因为字段类型是字符型查询条件是数字型MySQL 会做隐式类型转换等于把mobile套了一个 CAST 函数索引自然失效。这类问题的排查办法是先看表结构和慢日志里的实际 SQLSHOW CREATE TABLE users;如果字段确实是varchar而 SQL 里写成数字最直接的修复方案是改写 SQLWHERE mobile 13800138000但业务代码多挨个改不现实。MySQL 8.0 提供了函数索引可以直接在数字转换后的表达式上建索引ALTER TABLE users ADD INDEX idx_mobile_cast ((CAST(mobile AS UNSIGNED)));这样WHERE mobile 13800138000仍会触发隐式转换但只要转换函数和索引表达式一致优化器也能匹配上索引。这是 8.0 带来的实打实的便利同样的思路也适用于LOWER(email)、DATE(create_time)这类函数查询。不过函数索引不适合滥用。每次写入都要计算函数结果增加 CPU 开销索引空间也会变大。还是优先建议从代码层修正类型函数索引作为兜底方案。4.4 案例四深分页 LIMIT 太慢分页接口越往后翻越慢这是几乎每个业务系统都会碰到的问题。核心 SQL 长这样SELECT order_id, user_id, amount, create_time FROM orders ORDER BY create_time DESC LIMIT 200000, 20;表面看有create_time索引执行计划也不是全表扫描但为什么慢因为 InnoDB 必须先把前 200000 行全部扫描出来再抛弃前面 199980 行最后只返回 20 行。这个过程中的行读取和回表操作是相当大的开销。优化思路有两种。第一种是延迟关联延迟 join。先只查主键再回表拿完整数据SELECT o.order_id, o.user_id, o.amount, o.create_time FROM orders o INNER JOIN ( SELECT order_id FROM orders ORDER BY create_time DESC LIMIT 200000, 20 ) t ON o.order_id t.order_id ORDER BY o.create_time DESC;子查询只查order_id这一列被create_time索引覆盖不需要回表扫描 200000 行的成本大幅降低。外层再把 20 个主键回表总共就 20 次点查性能提升非常明显。第二种方案的思路是记录上一页位置的游标分页WHERE create_time 2024-06-01 12:00:00 ORDER BY create_time DESC LIMIT 20;这种方式适合用户持续下拉翻页、不需要随机跳页的场景。我实际对比下来同样的数据量传统深分页 1.8 秒游标分页 35 毫秒差距接近两个数量级。改造成本也不高只需要把前端页码参数换成游标值就行。4.5 案例五OR 条件乱用联合索引还有一个容易让索引失效的场景是OR条件。假设表上有联合索引(user_id, status)SQL 写成SELECT * FROM orders WHERE user_id 12345 OR status REFUND;这个查询很难用好联合索引。因为OR表示两个条件都可能成立优化器要对两边分别评估。如果一个走索引另一个全表扫描优化器可能直接选择全表扫描因为合并结果集并不划算。实际测过之后我发现比较靠谱的改写方式是用 UNIONSELECT * FROM orders WHERE user_id 12345 UNION SELECT * FROM orders WHERE status REFUND;UNION 两边各自用自己的最优索引结果合并再去重。不过要注意两边结果集如果很大UNION 的去重成本也不低需要结合实际数据分布判断。如果两个 OR 条件被拆开后大多是小结果集合起来性价比更高如果两边都是大范围数据可能全表扫描反而更稳。除了改写 SQL也可以考虑在status单列上再加一个索引让优化器多一个选择。但索引不是越多越好需要权衡写入开销这一点后面我会专门展开。5. 调优后的验证与性能对照5.1 优化前后执行计划对比每次做索引调整我都会把优化前后的执行计划放在一起对比至少确认四点type 是否从 ALL 变成 range 或 refkey 是否真正落到新索引上rows 是否明显减少Extra 是否消除了 filesort 和 temporary。以订单查询那个案例为例完整对比是这样的维度优化前优化后typeALLrefkeyNULLidx_user_status_timerows32000003filtered1.2100ExtraUsing where; Using filesortNULL耗时约 3.2s约 8ms确保key_len符合预期也很重要。如果联合索引(user_id, status, create_time)的key_len只匹配到 user_id说明 SQL 只用到索引的第一列后续字段没有接入匹配流程。检查方式很简单把表结构调整和 SQL 条件一列一列对照看类型是否一致、是否有函数包裹。5.2 压测结果与收益数据调整完索引不能只看执行计划还要做压测验证。我一般用 sysbench 或者直接用生产环境的只读流量做灰度对比。继续以订单查询为例。我在测试环境用 500 万行数据模拟了三种场景无索引平均耗时 3.5 秒CPU 占用接近 90%。单列索引(user_id)平均耗时 180 毫秒但 ORDER BY 仍需 filesort耗时在 150-200ms 波动。联合索引(user_id, status, create_time DESC)平均耗时 8 毫秒CPU 占用下降 70%。从这个对比可以明显看出索引设计不单影响单条 SQL 的耗时还会影响整个数据库的 CPU 消耗。三百万行数据全表扫描会把数据库的缓冲池全部污染掉其他热数据的查询也会被拖慢。这也是为什么慢查询优化要趁早数据量大了再回头改索引风险大收益小。6. 索引调优的常见坑与长期机制6.1 加了索引却没生效的五个原因在实际排查中最常见的困惑是“我明明加了索引为什么 EXPLAIN 还是不用”我总结下来大概有五个原因查询条件对索引列做了函数运算或表达式运算比如WHERE DATE(create_time) 2024-06-01。隐式类型转换比如 varchar 列和数字比较。LIKE以通配符开头LIKE %keyword没法走索引。优化器觉得走索引比全表扫描更慢这种情况常见于小表或者索引区分度极低的列。统计信息过期优化器基于错误的 rows 估算做了错误选择。针对第 5 点MySQL 8.0 可以通过ANALYZE TABLE手动更新统计信息也可以直接EXPLAIN ANALYZE对比实际执行和估算差异。如果统计信息频繁不准建议检查是否开启了自动统计信息更新并合理设置innodb_stats_auto_recalc相关参数。6.2 索引不是越多越好很多团队有一个误区觉得查询慢就加索引结果一张表上建了十几个索引写入性能严重下降磁盘空间也暴涨。这里要理解索引的成本。每个二级索引都是一棵独立的 B 树每次 INSERT、UPDATE、DELETE 都要同步维护所有索引树。索引越多写放大越严重。特别是高并发写入的订单表、日志表索引数量直接决定写入吞吐上限。我的经验判断标准是单表常规索引控制在 4-6 个以内每个联合索引的字段数控制在 3-4 个左右超过这个量就需要警惕。同时定期用performance_schema或者sys.schema_unused_indexes找出那些“建了却从未被使用”的索引尽早删除。MySQL 8.0 的不可见索引在这里特别好用。不确定某个索引是否有用直接ALTER TABLE table ALTER INDEX idx_name INVISIBLE把它藏起来跑一段时间的业务如果没有告警和慢查询再动手删除。这比直接删索引安全太多。6.3 日常巡检与自动化建议索引调优应该是一个持续优化的过程而不是一次性的救援。我的习惯是建立一套常规巡检机制数据量上来之后每半个月跑一次开启慢查询日志设置合理阈值定期分析 TOP SQL。每周对比一次常驻 SQL 的执行计划发现执行计划漂移就及时处理。用sys.schema_unused_indexes和sys.schema_redundant_indexes检查冗余索引。比如已经有(a, b)联合索引又建了(a)单列索引后者大概率就是冗余的。结合业务数据增长情况定期评估索引的区分度和选择率。在工具层面如果需要长期做性能监控可以考虑接入 Prometheus mysqld_exporter或者使用开源的数据库巡检工具。但工具只是辅助最重要的还是对业务查询模式的理解。索引设计没有一劳永逸的银弹只有对着真实 SQL、真实数据分布去不断验证、调整才能让 MySQL 8.0 跑出应有的性能。最后再分享一个我实际工作中的小习惯每次做完索引调整一定要把优化前后的执行计划、耗时数据、影响到的业务接口都记录到文档里。不要嫌麻烦几个月后线上出现诡异问题这份记录往往能帮你快速定位是“哪次变更导致了性能波动”。数据库调优最怕的就是凭感觉改改完不留痕出了问题无从下手。