MySQL索引详解从底层原理到实战优化一篇讲透接触MySQL这么多年几乎每次性能问题排查到最后都会落到索引头上。索引这东西说简单也简单无非就是B树、聚簇索引、回表这些概念说复杂也复杂从底层存储原理到优化器选择逻辑再到线上那些千奇百怪的索引失效场景每个环节都能写出一堆坑来。这篇文章我打算彻底掰开揉碎讲一遍MySQL索引。不光是讲是什么更重要的是讲为什么——为什么InnoDB选了B树而不是哈希表或者二叉树为什么联合索引有最左前缀原则为什么你的SQL明明建了索引却还是慢。无论你是刚入门准备面试的应届生还是被慢查询折磨的运维老哥这篇文章应该都能让你对索引有一个完整的认知。文章会比较长建议先收藏再慢慢看。1. 索引的本质与底层存储结构1.1 为什么需要索引从全表扫描说起先回到最基础的问题没有索引的时候MySQL怎么找数据假设有一张用户表user里面有100万条记录你要执行SELECT * FROM user WHERE username zhangsan。没有索引的情况下InnoDB存储引擎只能从第一条记录开始一条一条往下扫把所有记录都读一遍直到找到username为zhangsan的那条记录。这个操作叫全表扫描full table scan。全表扫描有多慢关键在于磁盘IO。数据是存在磁盘上的而磁盘随机读的速度大概是每秒几百次IOPS具体看硬件机械盘可能只有100多SSD能到几千到几万每次IO还要付出寻道和旋转延迟。如果100万条记录需要读几万个数据页全表扫描就要做几万次随机IO这在生产环境里是不可接受的。索引的作用就是把这几万次随机IO变成几次IO。怎么做到靠的是把数据组织成一种可以快速定位的结构让查询不需要从头遍历而是像查字典一样通过目录直接翻到目标页面。1.2 为什么不选哈希索引范围查询的痛点很多人第一反应是查找最快的结构不是哈希表吗O(1)复杂度比树还快为什么不直接用哈希做索引哈希索引确实快但它的快仅限于等值查询。WHERE username zhangsan这种精确匹配确实可以O(1)定位。但问题在于哈希索引无法处理范围查询。WHERE age 18这种条件哈希表是无能为力的因为哈希表把数据散列存储逻辑上相邻的数据物理位置毫无关系只能全部扫描一遍判断。同样ORDER BY排序、联合索引的排序能力、最左前缀匹配哈希索引统统不支持。还有一个问题是哈希冲突。多个key落到同一个桶里还得链地址法处理极端情况下哈希索引退化成链表扫描性能反而更不稳定。所以哈希索引在InnoDB里只扮演辅助角色——自适应哈希索引Adaptive Hash Index由InnoDB自动判断哪些热点页适合建哈希索引不需要DBA手动干预也属于我们平时说的索引优化中基本不用管的黑盒功能。1.3 B树为什么能胜出三层结构能存多少数据B树是InnoDB默认的索引结构。它为什么合适核心在于它控制了树的高度。先说B树的几个关键特性只有叶子节点存储数据或者说存储指向数据的指针非叶子节点只存储键值指针叶子节点之间通过双向链表连接天然支持范围查询每个节点对应一个数据页InnoDB默认16KB节点内的键值按顺序排列最经典的问题是一棵3层的B树能存多少行数据计算过程如下。假设主键是Bigint类型占8字节指针占6字节InnoDB中指针大小约6字节那么一个非叶子节点能存的键值对数量是16KB / (8 6) ≈ 1170个。第一层根节点有1170个指针第二层最多有1170×1170≈136万个指针第三层是叶子节点每个叶子节点还是一个16KB的数据页。假设一行数据大小为1KB一个叶子节点能存16条记录那么三层B树总共能存1170 × 1170 × 16 ≈ 2190万条记录。所以对于千万级以下的表走主键索引查询只需要3次磁盘IO三层树各一次一次定位根节点一次到中间层一次到叶子节点拿到数据。这比全表扫描上百万次IO快了不知道多少个数量级。B树和B树的另一个重要区别在数据存储位置B树的非叶子节点也存储数据导致同样大小的节点能存的指针更少树变得更高IO次数更多而且B树的范围查询需要中序遍历效率远不如B树的链表顺序扫描。这也是为什么MySQL最终选择了B树。2. MySQL索引的类型全拆解2.1 聚簇索引与非聚簇索引InnoDB的数据组织逻辑在InnoDB里索引和数据是一体的数据文件本身就是按主键构建的B树。这个以主键为索引键的B树就叫聚簇索引clustered index。聚簇索引的叶子节点直接存储完整行数据。一张InnoDB表只能有一个聚簇索引因为数据只能按一种顺序物理存储。如果你建表时没指定主键InnoDB会找第一个非空唯一索引作为聚簇索引如果也没有InnoDB会自动生成一个隐藏的rowid作为聚簇索引。其余所有索引都叫二级索引secondary index也叫非聚簇索引。二级索引的叶子节点不存储完整行数据只存储索引列的值 主键值。举个例子你在username上建了索引这个索引的B树叶子节点存的是(username, id)查的时候先通过username定位到主键id再拿id去聚簇索引里查完整行数据——这个过程叫回表。这也是为什么建议InnoDB表都要显式指定主键如果没有主键InnoDB用隐藏rowid做聚簇索引二级索引叶子节点存的是rowidrowid对应用户和应用层无感后续数据迁移、归档都会很麻烦。另外建议主键用自增整数因为B树按序插入效率最高用UUID这类随机字符串做主键会导致大量页分裂和随机写。2.2 主键索引、唯一索引、普通索引区别与选型主键索引PRIMARY KEY不允许NULL一张表只能有一个同时是聚簇索引的载体。唯一索引UNIQUE KEY不允许重复值但允许NULLMySQL中NULL不算重复一张表可以有多个。唯一索引除了查询加速还承担约束职责防止数据重复。普通索引INDEX/KEY最常规的索引既不要求唯一也不要求非空只有一个目的——加速查询。选型建议业务上需要保证唯一性的字段比如身份证号、订单号直接建唯一索引一举两得。有人会担心唯一索引影响写入性能确实有因为每次插入都要做唯一性校验。但相比应用层做一次查询再插入的方式数据库唯一索引的校验成本更低还避免了并发下的竞态问题强烈建议用数据库约束。2.3 联合索引与最左前缀原则为什么顺序这么重要联合索引也叫复合索引是在多个列上建的索引。例如INDEX idx_username_age (username, age)。联合索引的B树先按第一个列排序第一个列相同再按第二个列排序。这决定了它遵循最左前缀原则查询条件必须从联合索引的最左列开始才能用到这个索引。比如上面的索引WHERE username x能用到WHERE username x AND age 20能用到WHERE age 20用不到因为索引里age排在第二位没有username前缀无法定位。这个特性也是联合索引设计的关键。假设业务上经常查WHERE username ?也经常查WHERE username ? AND age ?那么建一个(username, age)联合索引就同时覆盖了这两个场景不需要单独再给username建索引。这就是联合索引可以一箭多雕的原因。但注意如果查询条件是WHERE age 20 AND username xMySQL优化器会做条件重排把username提到前面所以依然能走索引。优化器比你想象中聪明只要条件里出现了最左列就行不用纠结书写顺序。2.4 前缀索引给大字段加索引的唯一解对于VARCHAR(255)甚至TEXT类型的字段如果整个字段做索引索引体积会非常大B树每个节点能存的键值数量变少树变高磁盘占用和IO开销都上去了。前缀索引就是只取字段的前N个字符作为索引键。例如ALTER TABLE article ADD INDEX idx_title (title(20))只对title前20个字符建索引。前缀索引的难点在于选择N。太短的话区分度不够查出来的候选集太大回表次数反而多太长的话省空间的效果就差了。我一般用这个SQL来看不同前缀长度的区分度SELECT COUNT(DISTINCT LEFT(title, 5)) / COUNT(*) AS diff_5, COUNT(DISTINCT LEFT(title, 10)) / COUNT(*) AS diff_10, COUNT(DISTINCT LEFT(title, 15)) / COUNT(*) AS diff_15, COUNT(DISTINCT LEFT(title, 20)) / COUNT(*) AS diff_20 FROM article;当比值接近1一般0.9以上就够用时说明这个长度的前缀区分度已经足够了再增大长度提升不大。具体阈值根据数据量调整数据量越大要求的比值可以越高。前缀索引有一个副作用无法使用覆盖索引优化排序和分组因为索引里存的只是前缀字符不是完整值。这个在选型时要权衡。2.5 全文索引与哈希索引两个特殊场景全文索引FULLTEXT解决的是LIKE %keyword%这种模糊搜索性能极差的问题。MySQL的全文索引基于倒排索引实现适合对文本内容做关键词检索。注意全文索引只支持MyISAM和InnoDB5.6而且有自己专门的语法MATCH...AGAINST日常SQL的LIKE写法用不上它。如果业务有复杂的全文检索需求我更建议直接用ElasticsearchMySQL的全文索引在分词、相关性排序方面还是太弱了。哈希索引前面讲过InnoDB不支持手动创建哈希索引只有自适应哈希索引AHI这个内部机制。但有意思的是Memory引擎支持哈希索引适合临时表场景。另外如果确实需要哈希查找可以在建表时冗余一个hash列存目标字段的哈希值然后给hash列建普通索引查询时WHERE hash_col MD5(xxx)先等值定位再用原字段过滤精确匹配。这算是一种手动哈希索引方案适合超长字符串的精确查找场景。3. 索引失效的常见场景一张表看全索引失效恐怕是大家平时遇到最多的坑了。光背概念没用我整理了一套实测场景先建一张演示表CREATE TABLE t_user ( id INT PRIMARY KEY AUTO_INCREMENT, username VARCHAR(50), age INT, email VARCHAR(100), phone VARCHAR(20), sex TINYINT, create_time DATETIME, KEY idx_username_age (username, age), KEY idx_phone (phone), KEY idx_create_time (create_time), KEY idx_age_sex (age, sex) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;3.1 违反最左前缀原则联合索引idx_username_age如果查询条件是WHERE age 25不包含最左列username索引失效走全表扫描。这算是最基础的失效场景。但如果查询条件是WHERE username abc OR age 25情况更复杂MySQL优化器可能会对OR的两个条件分别判断如果其中一个条件可以走索引另一个不能有的版本会做Index Merge索引合并有的版本会直接全表扫描。生产环境建议尽量避免OR条件改写为UNION ALL让优化器走更确定的执行计划。3.2 对索引列做函数运算或隐式类型转换SELECT * FROM t_user WHERE LEFT(username, 3) abc; -- 索引失效 SELECT * FROM t_user WHERE DATE(create_time) 2024-01-01; -- 索引失效只要对索引列做了函数运算索引就废了因为B树的定位逻辑依赖原始键值的有序性函数处理后顺序完全变了无法二分查找。再看隐式类型转换SELECT * FROM t_user WHERE phone 13800138000; -- phone是varchar这里用了数字phone列是字符串类型但查询条件是数字MySQL会尝试把phone列转为数字再比较。这就相当于对索引列做了隐式函数转换索引失效。解决办法是保持类型一致WHERE phone 13800138000。3.3 LIKE以通配符开头SELECT * FROM t_user WHERE username LIKE %zhang%; -- 失效 SELECT * FROM t_user WHERE username LIKE zhang%; -- 有效%放在前面意味着字符串的前缀不确定B树无法定位起点。但zhang%是可以的因为前缀确定B树可以直接跳到zhang这个区间开始扫描。这是MySQL能利用索引的最左匹配特性。如果业务实在需要后缀模糊匹配考虑建一个反转字段的冗余列。关于前面热词里提到的find_in_set能不能走索引不能。FIND_IN_SET(目标值, 字段) 这种情况字段存储的是逗号分隔的列表MySQL没有对这种格式建立有效的索引机制只能全表扫描。建议规范化表结构把多值拆成多行或者用JSON_TABLE之类的函数展开后检索同样走不了索引但能优化业务逻辑。如果列表关系是固定的少量枚举值可以考虑生成的列索引方案。3.4 使用!、NOT IN、IS NOT NULL不等值查询情况下比如WHERE age ! 25优化器判断这个条件匹配的行数可能很多走索引需要回表大量数据反而不如全表扫描快。所以优化器主动选择了全表扫描。NOT IN同理。IS NOT NULL也类似因为NULL值在索引里是单独存储的非NULL值占了绝大多数。不过有一种例外IS NULL是可以走索引的因为NULL在InnoDB索引中是最小值级别的存在B树可以直接定位到最左边的NULL区间。但实际业务中IS NULL匹配的行数也常常很多优化器可能还是选全表扫描。3.5 优化器判断全表扫描更快这是最气人的一种情况索引没失效条件也规范但优化器就是不走索引。原因是当查询返回的行数预计超过全表的20%~30%时全表扫描的IO成本反而低于回表成本回表是随机IO全表扫描是顺序IO顺序IO比随机IO快得多。解决办法用FORCE INDEX(idx)强制走索引但慎用强制索引在许多情况下会弄巧成拙更好的方式是用覆盖索引见下文让索引里包含所有需要的列不需要回表重新分析表更新统计信息3.6 排序与分组中的索引失效ORDER BY和GROUP BY能不能走索引取决于排序字段的顺序是否满足最左前缀。比如索引idx_age_sex(age, sex)ORDER BY sex不能走索引ORDER BY age可以ORDER BY age, sex可以ORDER BY sex, age不行顺序不对。另外注意ORDER BY age ASC, sex DESC这种混合排序MySQL 5.7及以下索引是做不到混合方向排序的因为B树的叶子节点链表是单向有序的要么全部升序要么全部降序。MySQL 8.0支持降序索引DESC index后这个限制才被部分解除。3.7 索引失效场景速查表场景失效原因解决建议联合索引不满足最左前缀无法定位索引区间调整查询条件或索引列顺序对索引列使用函数索引键值有序性被破坏改写SQL避免函数包裹索引列隐式类型转换对索引列做了对比前的转换保持参数类型与列类型一致LIKE %xxx前缀不确定无法定位改为yxx%或加冗余字段OR连接非索引列优化器难用索引合并改写为UNION ALL!/NOT IN大量数据优化器弃用索引覆盖索引或拆分查询排序顺序与索引不一致B树叶子有序性不匹配调整索引列或排序顺序4. 索引的底层优化机制覆盖索引与索引下推4.1 覆盖索引最有效的回表规避方案回表是二级索引查询的代价。如果查询的所有列都在二级索引里就完全不需要回到主键索引去拿完整数据——这种情况叫覆盖索引covering index。比如有索引idx_username_age(username, age)执行SELECT username, age FROM t_user WHERE username zhang;这个查询需要的结果只有username和age两列而这两列都在idx_username_age的叶子节点里B树扫完直接返回连回表都省了。这就是覆盖索引的威力。实际优化中我经常用覆盖索引解决明明有索引但查询很慢的问题。比如业务需要查用户的基本信息可以设计联合索引 (age, username, sex)让高频查询的列完全落在索引内查询性能提升非常明显。当然覆盖索引不是万能的索引列越多写入成本和存储空间越大不能所有列都塞进去。4.2 索引下推MySQL 5.6带来的重大优化索引下推Index Condition PushdownICP是MySQL 5.6引入的优化影响非常大。没有ICP之前联合索引 (username, age)查询条件WHERE username zhang AND age BETWEEN 20 AND 30InnoDB会先用username定位索引区间拿到所有usernamezhang的记录然后回表再在完整行数据上过滤age条件。这样回表次数等于usernamezhang的数量。有了ICP之后InnoDB在索引遍历阶段就直接对age做判断不满足age条件的记录直接跳过根本不回表。回表次数大幅减少IO开销显著下降。这个优化对DBA是透明的MySQL 5.6及以上默认开启optimizer_switch里index_condition_pushdownon。我见过有些老系统从5.5升级到5.7后某些复杂查询性能突然变好了其实就是ICP在起作用。如果你在用5.6以下版本强烈建议升级ICP对大表查询的优化效果太明显了。4.3 索引合并MySQL的Index Merge机制允许一条查询同时使用多个索引再对结果做交集intersect或并集union。在OR条件连接多个索引列时尤其有用。比如WHERE username zhang OR phone 138xxx既有idx_username_age也有idx_phone优化器可以分别用两个索引查出两个集合再合并去重。Index Merge听着美好但实际性能不稳定两个索引分别扫描再把结果合并需要排序和去重代价不小而且只在特定条件下触发。我更建议设计联合索引来覆盖这类查询而不是每次依赖优化器做合并。EXPLAIN输出里typeindex_merge就代表走了索引合并看到它时多留个心眼确认一下是不是有更优的索引设计。5. 索引的维护与排查DBA实操经验5.1 查看索引与执行计划查看表上的所有索引SHOW INDEX FROM t_user;分析SQL执行计划EXPLAIN SELECT * FROM t_user WHERE username zhang\G重点看几个字段typeall全表扫描 index全索引扫描 range范围扫描 ref非唯一索引等值 eq_ref唯一索引等值 const主键等值从左到右性能递增。看到all就要警惕了key实际用到的索引rows预计扫描行数误差很大但能反映数量级ExtraUsing filesort排序没走索引、Using temporary用了临时表、Using index覆盖索引、Using index conditionICP我来模拟一次完整的排查过程。某天线上收到大量慢查询告警定位到这条SELECT * FROM order_info WHERE buyer_id 12345 ORDER BY create_time DESC LIMIT 20;EXPLAIN结果type: refkey: idx_buyerrows: 5000Extra: Using filesort分析过程buyer_id走了索引但排序用了filesort说明没有 (buyer_id, create_time) 的联合索引。解决建联合索引ALTER TABLE order_info ADD INDEX idx_buyer_time (buyer_id, create_time)让排序也走索引。改完之后EXPLAIN的Extra变成Using index conditionfilesort消失了查询耗时从800ms降到5ms。这种查询条件排序字段联合建索引的套路是日常优化中最常见也最有效的操作。5.2 索引过多过滥的问题索引越多查询越快是新手常见误区。实际上索引是有代价的写入变慢每次INSERT/UPDATE/DELETE所有相关索引都要同步更新。想象一下每次插入一行数据得同时维护5棵B树性能开销可想而知存储膨胀每棵索引B树都占磁盘空间。一张大表的多个冗余索引索引文件可能比数据文件还大优化器困惑索引太多优化器选择执行计划时需要评估的路径变多也可能选中次优索引我见过一张表建了十几个索引的表写入性能惨不忍睹。合理做法单表索引控制在5个以内去掉冗余索引。判断冗余索引有一个方法联合索引(a, b)建了之后单独的(a)索引就是冗余的因为联合索引的最左前缀已经覆盖了a列查询。5.3 索引统计信息不准导致的选错索引MySQL优化器基于统计信息估算成本。统计信息不准确的表现是明明该走索引A优化器走了索引B导致查询突然变慢。解决办法ANALYZE TABLE t_user;这个命令会重新统计索引的基数cardinality。正常情况下InnoDB的统计信息是采样的不是精确计算——采样本身是性能与准确度的权衡。如果表数据发生大幅变化比如批量删除/插入大量数据重新ANALYZE的频率也要跟上。我在生产环境通常把ANALYZE TABLE挂在每周的维护窗口里。5.4 索引碎片与重建InnoDB索引在频繁的增删改后会产生碎片导致索引的叶子节点不那么紧凑扫描花费的IO变多。两种情况需要处理表数据大量变更比如清空了再导入、按天删数据索引大量页分裂碎片整理有两种方式ALTER TABLE t_user ENGINE InnoDB; -- 重建整张表适合碎片严重的场景 OPTIMIZE TABLE t_user; -- 效果类似但表较大时耗时较长注意这些操作都会锁表必须在业务低峰期执行。MySQL 8.0支持在线DDLONLINE DDL的增强部分操作可以避免长时间的元数据锁但空间整理类操作仍建议预约维护窗口。5.5 数据库开启审计引起的索引争用这个场景我实际踩过某次安全合规要求开启了数据库审计功能结果业务高峰期出现大量锁等待和CPU飙升。排查后发现根因是审计日志表本身没有合适的索引审计功能每次写日志都会触发表扫描或锁竞争同时审计框架在解析SQL语句时也会占用大量CPU间接影响了常规查询的索引选择。处理方案分几步审计日志表必须有独立的高性能存储比如单独的表空间或专门的日志库不要和业务表混在一起给审计表的查询字段如操作时间、用户ID、操作类型建好联合索引审计策略按需精简不要所有操作全量记录——很多系统根本不需要记录SELECT操作如果用的是general_log直接关闭它对性能影响极大要用就用专业的审计插件并且做好日志滚动这个案例给我们的教训是审计和性能要平衡开启任何数据库层面的观测功能之前先评估它对索引和锁的影响。5.6 如何从慢查询日志中发现索引问题最后分享一个实用技巧。开启慢查询日志SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; -- 超过1秒的SQL记录然后定期分析慢日志重点关注两类SQL未走索引的查询用mysqldumpslow或pt-query-digest分析找出执行次数多、扫描行数大的SQL补充对应索引扫描行数远大于返回行数的查询说明索引选择性差或者用错了索引需要重新设计我个人的习惯是每两周跑一次慢日志分析把TOP 10慢SQL拿出来看EXPLAIN逐个优化。坚持几个月线上问题会越来越少。这是成本最低、收益最稳定的索引优化流程。6. 结语关于索引优化的几点个人体会做了这么多年数据库优化最大的体会就是索引设计不是建完就完事的事情。一张表的索引方案应该随着业务发展持续演进而不应该指望一次设计管几年。比如业务初期用户表查得少一个主键索引就够到了用户量起来、查询场景多样化之后就得逐步补充联合索引、覆盖索引甚至要考虑读写分离、分库分表这些更上层的架构方案。索引只是优化链路中的一环但往往是最先要做的那一环。最后再分享一个我踩过很多次坑的教训任何索引优化都必须先用EXPLAIN验证执行计划不要靠猜。有时候你觉得该走索引的SQL优化器偏就不走这时候耐心看统计信息、看数据分布、看实际执行成本往往比硬调整SQL更有效。数据库优化是一门平衡的艺术——查询速度、写入速度、存储成本、运维复杂度这几者之间需要找到适合你业务场景的平衡点。别为了极致查询性能把写入拖垮也别为了省存储空间连必要索引都不建。理解原理才能做好权衡。