MySQL8.0索引调优实战：从慢SQL到执行计划与联合索引设计

前两天同事跑过来说线上有个查询要三秒多问我是不是加个索引就行。我让他先EXPLAIN一下结果typeALLrows直接奔着四百万去。后来在(shop_id, status, created_at)上加了一个联合索引三秒变成二十毫秒。这种案例在MySQL8.0索引调优的时候几乎每周都能遇到。做SQL优化这么多年我最深的体会是慢SQL十有八九不是执行器慢而是优化器选错了执行计划。索引调优不是上来就加索引而是顺着SQL的真实执行路径去定位问题再决定加什么索引、怎么改SQL。这篇文章不会只讲概念我会把从发现问题、读懂执行计划到设计索引、处理深分页和JOIN优化这一整套思路拆开来说。不管你是在Linux上直接装的MySQL8.0还是用Docker跑起来的只要业务在这个版本上索引调优和SQL优化的逻辑都是一样的。后端开发、DBA、运维都能照着操作尤其适合被慢SQL折磨过、但又不知道从哪里下手的同学。1. 慢SQL是怎么发生的先定位瓶颈再动手很多人一遇到查询慢第一反应就是“加索引”。这个想法没错但是忽略了一个前提你得先搞清楚SQL慢在哪个环节。否则很容易出现加了个索引用不上或者明明走索引了还是慢半拍的情况。1.1 MySQL执行一条SQL的完整路径一条SELECT语句从客户端发到MySQL大概要经过四个阶段连接器负责权限校验和连接管理分析器做词法语法解析优化器负责生成执行计划最后才是执行器调存储引擎接口取数。慢SQL的问题大多数出在优化器这一层。优化器要根据表结构、索引、统计信息、数据分布来决定走哪个索引、怎么关联、是否排序一旦它的判断基于错误信息后续执行就会跟着遭殃。所以排查的时候我第一件事永远是看执行计划而不是盯着慢查询日志里的耗时数字发呆。1.2 全表扫描的代价到底有多大先算一笔账。假设有一张100万行的订单表每行平均200字节InnoDB默认页大小是16KB一个页大约能装80行。全表扫描要读大约12500个页也就是差不多200MB的数据。如果你是机械硬盘顺序读可能还好但遇到回表或者随机读场景性能会非常难看。即便在SSD上全表扫描也没有任何查询优化空间因为每一行都要过一遍。数据量再涨上去这条路根本走不通。所以索引最核心的价值就是把“我要翻遍整本书”变成“我直接翻到目录那一页”。1.3 索引不是越多越好这里得泼一盆冷水。每增加一个索引写入和更新时就要多维护一棵B树代价是实打实的。一个表搞七八个索引写入慢不说还容易让优化器在选择时犯迷糊。我见过一个项目某张表上建了三个包含相同首列的索引比如idx_a、idx_a_b、idx_a_b_c其实前两个在绝大多数场景下都是冗余的。删掉冗余索引后写入性能明显回升。记住一个原则索引是为查询服务的不是为“看着专业”服务的。上线前花几分钟检查一下冗余索引回报率很高。2. 索引选择的底层原理从B树到回表想做好索引调优光会建索引不行得理解InnoDB为什么这么设计。很多表面上的“玄学”问题追到底层之后其实都是数据结构问题。2.1 为什么InnoDB选择B树而不是哈希表哈希表做等值查询确实快O(1)复杂度但它对范围查询无能为力也没法排序。B树虽然也能做范围查询但它的每个节点既存数据又存索引树会变得矮胖但非叶子节点能存的索引条目就少了。B树把数据全部放在叶子节点非叶子节点只存索引键和指针一层能放更多条目树更矮I/O次数更少。更重要的是B树的叶子节点之间用链表串起来了天然支持范围查询和顺序扫描。像WHERE create_time BETWEEN 2024-01-01 AND 2024-01-31这种条件B树只要找到起点顺着链表往后读就行对磁盘顺序I/O非常友好。2.2 回表查询与覆盖索引的取舍InnoDB的二级索引叶子节点存的不是整行数据而是“索引列 主键值”。如果SQL要的字段在二级索引里找不到就得拿着主键去聚簇索引里查完整行这个动作叫回表。回表是随机I/O行数一多性能就崩。所以就有了覆盖索引的理念让索引覆盖SQL所需的全部字段查询就不需要回表了。最简单的例子-- 假设有索引 (name) SELECT id, name FROM user WHERE name 张三;如果索引是(name, id)那这个查询直接扫描二级索引就能拿到全部字段Extra里会显示Using index。这就是为什么我经常建议别用SELECT *按需取列既减少网络传输又有机会命中覆盖索引。2.3 联合索引的设计与最左前缀原则联合索引不是简单地把几个列拼在一起它的排序逻辑是先按第一列排序再按第二列排序。所以查询条件里如果不包含最左列索引通常走不了这就是最左前缀原则。举个例子索引(shop_id, status, created_at)WHERE shop_id 1能走索引WHERE shop_id 1 AND status 2能走索引WHERE shop_id 1 AND status 2 AND created_at 2024-01-01能走索引WHERE status 2 AND created_at 2024-01-01走不了因为跳过了shop_id设计联合索引时我的习惯是把等值条件放在前面范围条件放后面。比如status是枚举值等值场景多就放前面created_at一般用于范围筛选放后面。反过来会导致范围条件后面的列无法用于过滤等于白建。3. 用EXPLAIN读懂执行计划执行计划就是SQL在MySQL里“打算怎么跑”的路线图。看不懂执行计划索引调优就是盲人摸象。这一章我把最关键的几个字段讲清楚。3.1 执行计划核心字段说明用EXPLAIN SELECT ...会得到一张表。重点关注这几个字段type访问类型从好到差依次是system、const、eq_ref、ref、range、index、ALL。看到ALL基本就是全表扫描是首要优化对象。key实际用到的索引。如果为NULL说明没走索引。key_len用到的索引字节数可以算出哪些索引列真正参与过滤。rows优化器预估需要扫描的行数这个值越接近实际结果集越健康。Extra经常出现Using filesort、Using temporary、Using index等提示。前两个通常意味着性能隐患Using index则是好事。下面这张表是我常用的参考type含义是否理想system表只有一行极佳const主键或唯一索引等值查询极佳eq_ref被驱动表通过唯一索引关联极佳ref通过普通索引等值查询良好range索引范围扫描可接受index全索引扫描一般ALL全表扫描需要优化3.2 一条SQL从慢到快的执行计划对比之前调过一条统计SQL原来是这样SELECT order_id, amount FROM orders WHERE status 1 ORDER BY created_at DESC LIMIT 10;表里有200万行status字段分布很不均匀只有2%的行是status1。EXPLAIN显示typeALLrows是200万Extra里还有Using filesort意思是要先把200万行扫一遍再全部排序取10行慢是必然的。后来加了联合索引(status, created_at)执行计划变成typerefrows降到4万Using filesort也消失了。原因很简单索引先按status过滤再按created_at排好序MySQL扫描到第10条就能停了。这中间有个细节为什么不只建status单列索引因为单列索引虽然能过滤status但created_at的排序还是得交给filesort。联合索引让排序字段也在索引里直接省掉一次排序操作。执行计划里key_len的变化也能验证这一点。4. 实战四个典型慢SQL优化案例理论说完了来看几个真实场景。这些SQL模式在业务代码里出现频率极高每一种我都踩过坑。4.1 隐式转换和函数操作让索引白建用户表有个mobile字段类型是VARCHAR(20)上面建了索引。业务代码里经常这么查SELECT * FROM user WHERE mobile 13800138000;表面看没问题但mobile是字符串拿数字跟它比MySQL会自动把字符串转成数字导致索引列上发生隐式转换索引直接失效。EXPLAIN一看typeALL。改成WHERE mobile 13800138000之后立刻变成ref。这个坑特别隐蔽尤其是从接口层拿到的参数是数字类型时很容易中招。同理下面的SQL也是典型的“索引杀手”SELECT * FROM order WHERE DATE(created_at) 2024-06-01;DATE()函数套在索引列上索引就没法用了。优化思路不是建函数索引8.0支持函数索引但毕竟有额外成本而是改成范围查询SELECT * FROM order WHERE created_at 2024-06-01 00:00:00 AND created_at 2024-06-02 00:00:00;这样created_at上的普通索引就能正常走range扫描效率天差地别。4.2 深分页查询的延迟关联优化分页是个老话题但很多人只优化到“表里有索引”就停止了。一行代码SELECT * FROM orders ORDER BY created_at DESC LIMIT 1000000, 20;这条SQL哪怕created_at上有索引也会慢得离谱。因为MySQL必须从头扫到第1000020行然后再扔掉前100万行。扫描和回表的成本都花在了“不需要返回”的数据上。我用过最有效的方案是延迟关联先覆盖索引查出主键再回表拿完整行SELECT t.* FROM orders t INNER JOIN ( SELECT id FROM orders ORDER BY created_at DESC LIMIT 1000000, 20 ) tmp ON t.id tmp.id;子查询只查id配合(created_at, id)覆盖索引MySQL扫描100万行时不需要回表代价小很多。外层再按主键回表20行整体耗时会从秒级降到百毫秒级。如果业务上允许还可以用游标分页替代深分页SELECT * FROM orders WHERE created_at 上一次的最大值 ORDER BY created_at DESC LIMIT 20;这种方案把随机跳页的能力换成了“下一页”换来的是线性性能在高并发场景下非常推荐。4.3 JOIN关联查询的驱动表选择多表关联查询变慢常见原因是被关联字段没有索引或者驱动表选错了。MySQL一般会选小表作为驱动表用小表的结果集去匹配大表。看一条常见SQLSELECT u.name, o.amount FROM orders o JOIN users u ON o.user_id u.id WHERE o.status 1;如果orders.user_id没有索引优化器只能对orders全表扫描然后对每一行去users主键匹配效率要看驱动表大小。后来在orders.user_id上加了个普通索引执行计划从Using join buffer变成了ref查询时间下降了80%以上。这里有个很实用的套路JOIN的关联字段类型一定要一致否则跟隐式转换一样会让索引失效。user_id在A表是INT在B表是VARCHAR就算两边都有索引也未必能用上。另外join_buffer_size这类参数不是调得越大越好它只是辅助手段治标不治本。4.4 UPDATE和DELETE也可能慢在索引上很多人以为只有SELECT才需要优化索引其实大范围的UPDATE/DELETE同样会拖垮数据库。原因有两个一是定位需要更新的行时走了全表扫描二是更新范围太大锁了太多行还让undo log膨胀。我之前处理过一个清理任务DELETE FROM logs WHERE created_at 2024-01-01;这表有几千万行直接跑下去锁的范围巨大主从延迟飙到十几分钟。后来改成按主键分批处理DELETE FROM logs WHERE id IN ( SELECT id FROM logs WHERE created_at 2024-01-01 ORDER BY id LIMIT 1000 );循环执行每批只删1000行加上索引(created_at)快速定位数据整个清理过程对线上业务几乎没有影响。几条简单的启发大批量写操作永远要拆批拆批之后永远要走索引定位这两点同时满足性能就不会太差。4.5 窗口函数好用但别让它裸奔MySQL8.0带来了窗口函数写排名、累计求和方便了不少。但窗口函数如果涉及全表排序没有合适的索引支撑一样会慢。比如按用户算订单排名SELECT user_id, order_id, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn FROM orders;如果没有(user_id, created_at)联合索引这个查询必然要对全表做一次临时表和排序。加上联合索引后PARTITION BY和ORDER BY都能直接复用索引顺序Extra里不再出现Using temporary; Using filesort。这算是我在MySQL8.0上最常给业务方推荐的一个优化点。5. MySQL8.0的新特性在调优里的实际价值MySQL8.0不只是加了窗口函数和CTE索引层面也更新了几个非常有用的能力。用好它们能解决很多老版本里只能绕路的问题。5.1 降序索引真正支持反向排序的索引老版本的MySQL虽然能写INDEX (a DESC)但实际存储还是升序遇到混合排序就会走filesort。比如SELECT * FROM product ORDER BY price DESC, created_at ASC LIMIT 10;在MySQL8.0里可以建一个真正的降序索引ALTER TABLE product ADD INDEX idx_price_created (price DESC, created_at ASC);建完之后ORDER BY里的排序方式完全由索引提供Extra里的Using filesort消失。这个优化的典型场景就是商品列表、秒杀列表这类“价格从高到低、时间从旧到新”的排序需求。5.2 不可见索引安全验证索引价值在MySQL8.0里可以把索引设置为不可见优化器完全忽略它但索引本身还在维护ALTER TABLE orders ALTER INDEX idx_old INVISIBLE;我经常用它来验证“这个索引到底有没有被用到”。如果设为不可见之后线上一切正常查询性能没有波动说明这个索引就是冗余的可以彻底删除。反之则赶紧设回VISIBLE。这比直接删索引安全太多尤其是面对不敢轻举妄动的生产环境。需要注意主键索引和唯一约束依赖的索引不能被设为不可见否则会破坏数据约束。5.3 直方图优化器的数据分布盲区MySQL优化器默认假设数据分布是均匀的但现实往往是80%的数据集中在少数几个值上。字段status可能90%都是1优化器会高估WHERE status 2的返回行数从而选错索引。MySQL8.0支持给字段建直方图ANALYZE TABLE orders UPDATE HISTOGRAM ON status WITH 100 BUCKETS;直方图能告诉优化器这个字段的数据分布让它更准确地估算rows和成本。这跟索引是两回事索引改变的是访问路径直方图改变的是优化器的“判断依据”。两者配合使用效果最佳。5.4 EXPLAIN ANALYZE真实执行看每一行的代价MySQL8.0.18开始支持EXPLAIN ANALYZE它不只是展示预估计划而是真的把SQL跑一遍输出每步操作的实际耗时、扫描行数、返回行数。EXPLAIN ANALYZE SELECT * FROM orders WHERE status 1 ORDER BY created_at DESC LIMIT 10;输出里能看到actual time和actual rows跟优化器预估的对比一下能很快发现统计信息偏差。但要记住它会让SQL真实执行生产环境遇到大查询千万别直接跑。6. 常见问题与排查技巧实录最后把日常调优中反复踩过的坑整理成速查表照着排查就能解决大部分问题。6.1 索引失效高频场景速查场景原因解决方案WHERE name LIKE %关键词%前模糊查询无法使用索引改后模糊或改用全文索引WHERE DATE(create_time) ...函数作用在索引列上改成范围条件WHERE mobile 138...隐式类型转换参数类型与字段类型保持一致WHERE a 1 OR b 2多条件OR导致无法走索引拆分成两个查询UNION或改INWHERE status IS NOT NULLNULL值使索引判定复杂化设计时避免可空列联合索引跳过了首列违反最左前缀原则调整SQL或增加对应索引6.2 索引生效了但还是慢大概率是这几个原因第一种是选择性太低。比如gender字段只有男女两个值索引虽然被使用了但过滤后还有一半行要回表性能自然上不去。这种情况应该考虑复合索引或者干脆接受全表扫描。第二种是回表行数太多。明明走了索引但需要回表的数据量巨大I/O开销抵消了索引优势。最简单的解法是把要查的字段都放进索引做成覆盖索引。第三种是统计信息过期。有时候执行计划里rows跟实际差了好几个数量级这时候手动跑一下ANALYZE TABLE让优化器重新评估可能问题就解决了。第四种是行锁竞争和长事务。SQL本身很快但被其他未提交的事务堵住了这就不完全是索引问题了需要去performance_schema看锁等待事件。6.3 关于FORCE INDEX的最后手段网上经常可以看到FORCE INDEX的用法强制优化器走某个索引。我的建议是能不用就不用。因为FORCE INDEX是人工干预一旦数据分布或统计信息变化强制指定的索引可能变成最差的选择而且这种问题很难提前发现。如果确实用了记得加上注释说明原因并且在每次大版本升级或数据量翻倍时重新评估。长期来看把SQL本身和索引设计调整到优化器能自然选对才是最健康的做法。6.4 一套实用的慢SQL监控组合调优不能只看偶尔一条慢SQL得建立监控习惯SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1;把超过1秒的查询都记录到慢查询日志然后配合mysqldumpslow或者performance_schema里的events_statements_summary_by_digest做聚合统计。MySQL8.0里还有现成的sys.statement_analysis视图一条SQL就能看到所有慢查询的排名、平均耗时、扫描行数非常方便。我自己的习惯是每周固定看一眼Top 10慢SQL列表用EXPLAIN逐个过一遍把需要优化的记录到工单里。不要等业务方反馈某个接口慢了才去处理主动发现和被动救火的差别很大。最后分享一个小习惯。我每次定位一条慢SQL都会先问三个问题是否走了索引回表了多少行排序和临时表能不能省掉大部分性能问题只要把这三件事想明白答案基本就清晰了。索引调优做到后面拼的不是操作技巧而是对数据和查询路径的理解。如果你手里正有一条三秒钟的SQL别急着加索引先EXPLAIN看一眼多半能找到比加索引更精准的解法。