做数据库的人基本都绕不开索引。不管你是刚入行的后端开发还是已经写了几年SQL的业务工程师早晚会遇到一条慢SQL慢到接口超时、数据库CPU飙升最后DBA甩给你一句“这里没走到索引。”MySQL的索引就像一个加速器加对了千万级数据也像翻书一样快加错了或者压根没设计数据一多就直接把数据库拖垮。这篇内容我把索引从底层数据结构到日常设计、失效排查一次讲透适合那些想真正搞懂“为什么慢、怎么建索引、索引什么时候失效”的人。内容会比较长但保证每一条都能直接拿去看业务、建索引、调SQL。1. 索引到底在解决什么问题1.1 从一次全表扫描说起先看一条再普通不过的SQLSELECT * FROM user WHERE user_name 张三;假设user表里有1000万条数据且user_name上没有任何索引。MySQL会怎么执行它只能从第一行到最后一行挨个把user_name拿出来比对。这就是我常说的全表扫描在InnoDB存储引擎里实际表现为扫描聚簇索引的叶子节点链表一行都不放过。这里要说一个核心概念InnoDB的最小读写单位不是行而是数据页。一个页默认16KB里面可能装着几十行甚至更多数据。全表扫描意味着要把存储这张表数据的页一连串读进来页数越多磁盘IO次数就越多。机械硬盘的IO延迟本来就高哪怕换成了SSD大量随机读也会让整体查询时间随数据量线性上涨。数据量一千万时这种线性扫描基本就是噩梦一条查询跑几百毫秒甚至几秒都算正常。没有索引时MySQL面对一个等值查询只能靠“蛮力”。你体会一下从一万行里找一个名字和从一千万行里找一个名字耗时完全不可控。索引解决的问题本质上就是把这个“挨个看”的过程变成“按目录翻页”的过程。1.2 索引为什么能这么快有序结构加二分查找索引的思想其实特别朴素就像一本几百页的书没有目录你只能一页一页翻有了目录你就可以直接定位到章节。数据库索引就是为某一列或某几列的值建一份“有序目录”。为什么有序这么重要因为有序的数据可以做二分查找。想象一个场景让你在一堆按大小排好的数字里找77你会从中间开始比中间值大了就看右半部分小了就看左半部分每次都能砍掉一半的数据。对于1000万个值二分查找最多比较约log2(1000万)次大约是23次跟全表扫描1000万次完全不是一个量级。在MySQL里这个“有序目录”是用B树实现的。B树的每一层都可以看作一个筛子从根节点逐层往下一次就能排除大量无关数据。于是一次索引查找耗时基本可以由树的高度决定而InnoDB的B树高度通常只有2到4层。这意味着哪怕表里有几千万行走索引也就几次磁盘IO的事查询时间不会因为数据量增加而急剧劣化。1.3 索引不是免费的写变慢空间变多索引能提速查询但不是白送的。每建立一棵索引树就等于额外维护一份有序结构。你插入一行数据不仅要写主键索引还要同步写入每一个二级索引删除一行也要从所有索引里把它摘掉更新一个索引列本质上相当于删除旧值再插入新值。所以你会发现索引建得越多写入性能越差磁盘空间占用也越大。这就是空间换时间的典型Trade-off。很多新人容易犯的错就是把所有可能出现在WHERE里的列都单独建个索引结果查询没快多少写入反而越来越慢。我见过一张20个字段的表被建了30多个索引最后每次批量导入数据慢得像蜗牛。优化的第一步不是加索引而是想清楚“到底哪些查询值得建索引”。一个优秀的索引设计永远是结合业务查询来的不是按字段数量来的。2. MySQL索引的底层数据结构B树为什么是主角2.1 二叉树、B树、B树到底差在哪很多人背过“InnoDB用B树”但不知道为什么不用二叉树、不用B树。我从磁盘IO的角度拆开说。先说二叉查找树。它的问题在于极端情况下会退化成链表比如插入有序数据时树的形状会变得很高查询又变成了线性扫描而且每次都只能读两个分支中的一个磁盘IO效率很低。平衡二叉树能解决退化问题但它每个节点只能存一个键、两个孩子树高依然按数据量对数增长。数据多到千万级时树高可能达到二三十层每层一次随机IO谁也受不了。再看B树。B树的一个节点可以存多个键也能有多个孩子树高被压得很低。但B树有个特点每个节点既存索引键也存数据。这意味着如果一行数据很大一个节点能容纳的键数量就很有限。非叶子节点还要存储数据导致每一层能够携带的“搜索路径”变少树高就不容易压下来。B树是怎么改的B树的所有数据都存放在叶子节点非叶子节点只存索引键和指针。这样一个16KB的页能存上千个键树高被压得非常低。同时B树的叶子节点之间用双向链表串联非常适合范围查询和排序扫描。范围查询时只要找到起点顺着叶子节点链表往后走就行不需要反复从根节点重新搜索。我自己在给新人讲的时候会打一个比方B树像是每个楼层都放了一些商品你找东西每层都要看一眼B树则像商场每层只挂楼层索引所有商品都集中在底层并按顺序摆好。搜索引擎也好数据库也好都天然喜欢后者。2.2 一棵B树到底能存多少数据很多人对B树的“矮”没有直观感受我算一组经典数字。假设主键类型是BIGINT占用8字节页内的指针占用6字节那么非叶子节点每条索引项大约14字节。一个16KB的数据页可以容纳大约16 * 1024 / 14 ≈ 1170个索引项。也就是说第二层能分出1170个分支。假设一行数据大小是1KB一个叶子页可以存16行。那么三层B树大约可以存储1170 * 1170 * 16 ≈ 2190万行数据。也就是2200万行左右只需要三次IO定位到叶子页。如果行比较小比如一行只有几百字节三层树能存的行数会更大。这也是为什么千万级数据表在InnoDB里走主键查询通常也就两三毫秒的原因。如果索引键更小比如用INT做主键一层非叶子页能存的索引项更多树可以更矮。这里也顺便解释了为什么InnoDB强烈建议你用自增主键。自增主键顺序插入新数据总是加到B树最右侧页分裂较少。如果主键是随机字符串插入时索引页会被频繁分裂、移动产生大量碎片写性能会明显下降。2.3 聚簇索引和二级索引回表到底怎么发生InnoDB的数据存储和索引是绑在一起的这个设计叫聚簇索引。表里的主键索引树的叶子节点直接保存了整行数据。也就是说只要你按主键查询拿到叶子节点就等于拿到了这一行的所有列。那二级索引呢比如你在user_name上建了一个普通索引这就是二级索引。二级索引的叶子节点保存的是索引列的值加上主键值而不是整行数据。执行SELECT * FROM user WHERE user_name 张三;如果只靠user_name索引MySQL能在索引树上快速找到“张三”对应的主键id但发现SELECT *还需要user_name、age、mobile等其他字段。于是它拿着这个主键id再回到聚簇索引树里查一遍完整数据这个过程就叫回表。回表多了一次随机IO所以性能上比直接在索引里拿数据要差。那怎么避免回表有两种办法。第一种是只查询索引中包含的列比如只查user_name和id那么索引覆盖了需求不用回表这就是覆盖索引。第二种是建一个包含所有所需字段的联合索引让查询所需列都落在索引里。很多人只听说过覆盖索引这个词不知道它的意义。在核心高频查询里设计一个覆盖索引往往比盲目增加缓存更直接有效。3. 索引设计实战从EXPLAIN读懂执行计划开始3.1 EXPLAIN核心字段到底怎么看索引设计不能靠猜。拿到一条慢SQL第一步永远是执行EXPLAIN让MySQL告诉你它打算怎么查。EXPLAIN SELECT id, order_no FROM order_info WHERE user_id 10086 AND status 1;执行结果里有几个字段要重点看。type告诉你访问类型。常见的从好到差排序system、const、eq_ref、ref、range、index、ALL。看到ALL基本就是全表扫描通常意味着索引没建好或者没走成。看到index代表扫了整棵索引树一般也比ALL好不到哪去。ref和range是比较常见且健康的访问方式一个用于非唯一索引等值匹配一个用于范围匹配。key表示优化器最终选中的索引。possible_keys是可能用到的索引列表但真正用的是key这一列。有时候possible_keys里明明有索引优化器却没选中说明优化器认为用了反而更慢这种时候要认真分析原因。rows是优化器估算的需要扫描的行数。这个数字越小通常越好。有时候索引选择的区分度不够rows依然很大比如在性別这种只有两个值的列上建索引基本没什么意义。Extra信息量很大。出现Using filesort说明排序没有利用索引需要额外排序操作出现Using temporary说明用了临时表常用于GROUP BY或者去重出现Using index则说明正在使用覆盖索引这是很好的信号。我看EXPLAIN的习惯是先看type和rows再看key和Extra。一次EXPLAIN看不出问题就加一个FORCE INDEX对比或者拿真实数据量验证。3.2 联合索引与最左前缀原则业务里单列索引远远不够更多时候需要联合索引。比如你的查询条件是user_id和status同时存在那么建立一个(user_id, status)联合索引通常比分别建两个单列索引更高效。联合索引的底层逻辑是先按照第一个字段排序第一个字段相同再按第二个字段排序。所以它天然支持“从最左边开始连续匹配”。这就是最左前缀原则。CREATE INDEX idx_user_status ON order_info(user_id, status);下面这些查询都能用上这个索引WHERE user_id 10086 WHERE user_id 10086 AND status 1 WHERE user_id 10086 ORDER BY status但下面的查询用不上或者只能用一部分WHERE status 1 WHERE status 1 AND user_id 10086 -- 优化器可能自动调整但依赖顺序风险大如果查询条件里有范围条件比如WHERE user_id 10000 AND status 1那么联合索引(user_id, status)左列user_id走了范围status条件不一定能继续走索引。因为索引排序时在user_id范围后面的status并不是全局有序的。这就是很多人说的“范围查询会打断索引后续列”。实际设计联合索引时我建议把等值条件放在最左边把范围条件放在后面并把排序字段也尽量纳入索引避免Using filesort。这听起来简单但真正动手设计时还是要拿具体SQL一条条盘。3.3 一个订单查询的索引设计案例拿一个我处理过的典型场景说。某项目有张订单表核心查询是SELECT order_no, amount, create_time FROM order_info WHERE user_id 12345 AND status 1 ORDER BY create_time DESC LIMIT 20;我先看表结构和数据量order_id主键user_id、status、create_time都可能作为查询条件表里大约800万行。最粗暴的方案是给user_id、status、create_time各建一个单列索引但这样效果很差因为单靠user_id的等值扫描可能扫出几千行再逐个判断status、再排序性能不稳定。最终我建的索引是ALTER TABLE order_info ADD INDEX idx_user_status_time (user_id, status, create_time);为什么这样设计user_id等值过滤最左侧命中status等值过滤继续缩小范围create_time在索引里已经有序正好满足ORDER BY create_time DESC避免额外的文件排序。这个索引让查询从全表扫的几百毫秒降到了个位数毫秒。这里有一个细节如果还希望避免回表可以把查询需要的amount也放进索引比如ALTER TABLE order_info ADD INDEX idx_user_status_time_amount (user_id, status, create_time, amount);但索引不是越长越好。多加一个字段占用空间变大写入变慢。只有当这条SQL是高频热点时才值得用覆盖索引换一次回表。我一般在线上先确认查询频率再决定是否扩大索引列。4. 索引失效场景与排查技巧4.1 看起来走了索引实际却没走上索引建好了不代表每条SQL都会命中。最容易踩的坑就是对索引列做计算或函数操作。SELECT * FROM order_info WHERE DATE(create_time) 2024-05-01;如果在create_time上有索引这样写优化器很难直接使用索引因为索引中存储的是完整的日期时间值而DATE()函数改变了比较条件。更推荐写成范围条件SELECT * FROM order_info WHERE create_time 2024-05-01 00:00:00 AND create_time 2024-05-02 00:00:00;另一个常见问题是隐式类型转换。比如mobile字段是varchar类型却写成SELECT * FROM user WHERE mobile 13800138000;MySQL可能会把字符串转成数字再比较导致索引失效。解决方法是等号右侧也写成字符串。还有前导模糊查询SELECT * FROM user WHERE user_name LIKE %张%;因为索引按前缀排序第一个字符是%时优化器无法确定从哪里开始匹配。如果业务确实需要可以改用前缀索引或者全文检索方案。OR条件里只要有一个列没索引整条查询也可能放弃索引。比如SELECT * FROM order_info WHERE order_no A1001 OR status 2;即使order_no上有索引MySQL也可能需要把OR两侧结果合并若status无索引容易出现全表扫描。这种情况尽量用UNION拆开或者确保两边都有索引。4.2 用慢查询日志定位真正的凶手线上系统慢SQL往往不会主动跑到你面前。要主动发现问题第一步是开启慢查询日志。SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; SET GLOBAL log_queries_not_using_indexes ON;long_query_time设置为1秒意味着超过1秒的SQL会被记录。log_queries_not_using_indexes可以帮你找出那些没走索引的SQL。注意这些参数只是临时生效持久化配置需要写进配置文件。拿到慢SQL之后我会先EXPLAIN确认执行计划再根据实际数据重新设计索引。有时候一条SQL慢不是因为缺索引而是因为写法让索引用不上比如前面讲的函数、隐式转换、范围条件位置不对。所以排查慢SQL的最后一步一定要回到SQL写法上。4.3 常见失效原因速查表我把自己平时排查问题时最常遇到的场景整理成了一张表方便你直接对照。场景原因正确做法WHERE DATE(create_time)...对索引列使用函数改成范围条件WHERE id1100对索引列做运算左边保持原始列WHERE mobile13800000000隐式类型转换右侧写字符串LIKE %关键词前导模糊用前缀匹配或全文检索OR多条件部分列无索引确保所有列都有索引或UNION索引列区分度太低优化器认为全表扫更快换高区分度列建索引字符集或排序规则不同关联列无法使用索引统一字符集和排序规则WHERE a100 AND b1联合索引范围截断把范围列放在联合索引末尾这张表不需要死记你只要记住一个核心索引的本质是有序结构任何破坏有序性或者无法直接匹配有序结构的写法都会让索引失效。5. 索引维护与大表加索引的坑5.1 索引不是越多越好冗余更要命我在第1章说了索引有写入成本这里再聊一个常见现象同一个字段被多个索引重复覆盖。比如已经有了联合索引(user_id, status)又单独建了一个user_id索引。因为联合索引左边的user_id已经可以支持“where user_id?”的查询单独再建user_id索引就是冗余的。数据库写入时还要多维护一棵索引树纯粹浪费。查询系统表可以看到一张表的所有索引我一般会逐个检查SHOW INDEX FROM order_info;重点看每一棵索引是不是被其他联合索引完全包含。如果联合索引的左前缀已经覆盖了单列索引同时字段长度相近单列索引基本就是冗余可以删掉。当然有一种例外如果那个单列索引的查询频率极高而且联合索引体积很大、前缀列很多删除单列索引后查询可能需要走更宽的索引树。所以删除前最好结合真实EXPLAIN和统计数据判断不要机械地“眼见冗余就删”。5.2 大表加索引在线DDL与锁表风险给几百万甚至上千万行的表加索引最怕锁表。MySQL 5.6之前很多DDL会锁整张表业务直接不可用。从5.6开始支持Online DDL可以在很多场景下做到不阻塞读写。加普通二级索引时可以使用ALTER TABLE order_info ADD INDEX idx_user_status (user_id, status), ALGORITHMINPLACE, LOCKNONE;ALGORITHMINPLACE表示不复制整张表在原表上进行索引构建LOCKNONE表示允许并发DML。需要注意并不是所有DDL都支持INPLACE比如修改主键、改变列类型有时还是会走COPY算法复制整张表耗时和磁盘占用都会剧增。实际执行前我会先检查磁盘剩余空间因为Online DDL过程中可能额外占用临时空间。还会避免在业务高峰期操作并在从库上观察延迟。大表加索引再优化也可能要跑几十分钟甚至更久必须有心理预期。如果表已经很大直接ALTER依然有风险可以考虑用在线变更工具分批处理。这类工具的原理是创建一个新表、同步增量数据、在后台批量拷贝旧数据最后切换。整个过程中业务无感知。要注意的是无论用哪种方式都要在测试环境先跑一遍拿到执行时间和空间占用再上生产。5.3 索引用得少怎么发现和清理索引不光是加还要清理。一张表可能积累了很多历史索引但实际上从来没有SQL用过。MySQL有个比较方便的办法从performance_schema里查索引的统计信息。SELECT * FROM performance_schema.table_io_waits_summary_by_index_usage WHERE OBJECT_SCHEMA your_db AND OBJECT_NAME order_info;通过这个视图能看到每棵索引被读了多少次如果某个索引的COUNT_STAR长期为0或者极小基本可以判定是冗余索引。结合慢日志和业务上线记录确认安全后就可以删掉。清理索引的收益往往被忽视。索引少了写入会变快缓冲池也能腾出更多空间给真正热的数据。我一直建议每半年做一次索引治理算是数据库日常保养的一部分。6. 常见问题实录与独家经验6.1 字符集和排序规则不同关联查询索引失效有一个非常容易忽视的坑两张表关联字段的字符集不一样。比如order_info库是utf8mb4_general_ciuser_info库是utf8mb4_unicode_ci两张表通过user_id关联时MySQL为了实现比较可能要对其中一列做隐式转换导致无法直接使用索引。解决办法是统一字符集和排序规则。如果暂时不能改表结构可以在关联时显式指定转换SELECT * FROM order_info o JOIN user_info u ON o.user_id CONVERT(u.user_id USING utf8mb4) COLLATE utf8mb4_general_ci;但这也不是长久之计因为函数和转换又会影响索引使用。统一规范才是正解。所有业务库的字符集、排序规则在建库时就该定死。6.2 ORDER BY、GROUP BY、分页到底能不能走索引排序不走索引时EXPLAIN的Extra里会出现Using filesort。解决排序问题最直接的方式是让排序列在联合索引中正好处于从最左开始连续的位置。GROUP BY本质上也要排序如果分组字段能匹配联合索引也会避免临时表。遇到深分页问题就更有意思了SELECT * FROM order_info WHERE user_id 10086 ORDER BY create_time LIMIT 100000, 20;MySQL不是只查20行而是先扫描前100020行再把前面十万行丢弃。数据多了性能急剧下降。常用优化方案是延迟关联先利用覆盖索引拿到主键ID再回表取完整数据SELECT o.* FROM order_info o JOIN ( SELECT order_id FROM order_info WHERE user_id 10086 ORDER BY create_time LIMIT 100000, 20 ) t ON o.order_id t.order_id;这样前半段查询可以在索引上完成大幅减少回表行数。另一种方案是记住上一页最后一条的排序值用范围条件翻页也就是游标分页适合实时性要求不高的场景。6.3 我对索引的几条实战建议做索引设计这么多年我总结了几条最实用的原则。第一先定位热点SQL再设计索引而不是看表上有什么列就建什么索引。一张表的索引是给高频且慢的查询服务的。第二联合索引把等值条件放最左范围条件放右边尽量把ORDER BY字段放进去。这个顺序我每次设计都会默念一遍。第三区分度低的列不要单独建索引比如状态、性别。联合索引里要根据实际情况决定是否放入。第四能用覆盖索引就覆盖索引但不要无限加列否则写入成本会反噬业务。第五任何索引变更上线前先在测试环境用接近真实数据量的表做EXPLAIN和压测。索引不是玄学慢就是慢快就是快跑一把就知道。第六线上永远不要高峰期大表DDL。就算Online DDL支持并发也要留足冗余空间观察主从延迟。我记得有一次线上某张表几百万数据单条查询从几秒压到了几十毫秒原因只是把一条WHERE user_id? AND status? ORDER BY create_time的SQL从三个单列索引改成了一个联合索引。工具就在那里原理也就那么点真正拉开差距的是能不能理解和设计好索引背后的数据组织方式。你现在写出SQL前如果能习惯性想一下“这条查询怎么在索引树里走”那说明你真的入门了。