索引这玩意儿算是数据库里最容易被“背出来”又最容易被“问倒”的知识点。面试前能背得动聚簇索引、二级索引、最左前缀、覆盖索引可真到线上遇到一条慢SQL很多人却说不出它到底为什么没走索引。今天我把索引的底子拆开讲用最直白的方式说清楚“索引到底怎么工作的”不背八股文只讲能用在工单和调优现场的逻辑。先给热搜词做个体检你搜“索引”时出来的不一定是同一个东西。m3u8索引是视频分片列表Windows索引器是系统文件的检索目录obsidian的多级索引是笔记关联结构Elasticsearch里的索引是一堆文档的集合这些和本文要讲的数据库索引根本不是一回事。数据库索引是一个数据结构目的只有一个从大量数据里快速定位到想要的那一行。这篇文章适合被数据库面试折磨的开发者也适合写SQL总慢的运维和数据分析师我会从本质讲到实操最后再分享几个踩坑经验。1. 索引是什么把“翻书找内容”变成“翻目录”1.1 索引的本质就是一张目录表的数据在磁盘上是一行一行存的没有索引时查询只能老老实实从头扫到尾这叫全表扫描。如果表里只有几百行扫就扫了毫秒级完成可到了千万行级别全表扫描可能要读几千个数据页慢SQL就这么来了。索引的本质就是给表建一张“目录”。书的目录记录“章节名 - 页码”索引记录“索引键值 - 记录位置”。你查数据时数据库先翻目录定位到对应位置再直接取数据不用每一页都翻。这听起来很简单但很多讲索引的文章偏偏把它说得像天书非要从红黑树讲起其实没必要。这里有个坑需要提前说清楚在InnoDB引擎里二级索引的叶子节点存的并不是记录的物理地址而是主键值。也就是说通过二级索引找到的只是一个“书签”最后还要拿着这个书签去主键索引里再查一次才能拿到完整行数据这就是后面要说的“回表”。很多人一开始理解索引时被这个绕晕我建议先把索引简单理解为“键值到位置的映射”回表的细节放到后面讲聚簇索引时再说思路会顺很多。1.2 索引为什么快二分查找和B树层高目录之所以快是因为不需要顺序翻页而是按某个规则快速定位。最朴素的做法是有序数组加二分查找——每次比较都能排除一半数据查找100万条记录只需要大约20次比较。但数据库面临的问题是数据会不断增删改维护一个始终有序的数组代价太高插入一条数据可能要让后面所有数据都挪位置。所以数据库用了B树。B树是一种多路平衡查找树每个节点可以存储多个键值节点大小通常和一个磁盘页对齐这样一次磁盘IO就能读入整个节点。为什么B树层高那么低可以做个粗略估算假设一个16KB的数据页能存储约1000个键值对两层B树能覆盖约100万条记录三层能覆盖约10亿条记录。也就是说查询十亿级别的数据走索引只需要3次左右的磁盘IO而全表扫描可能需要读上百万个页。这就是索引快的最根本原因。很多人面试被问“为什么用B树不用二叉树”答案其实不在树本身而在磁盘IO。二叉树层高太高查询一个节点可能要跳很多次磁盘B树把树压扁了一次读页能带走更多信息IO次数自然少。记住这个逻辑比死记“B树矮胖”要有用得多。1.3 别忘了代价索引不是白来的索引提升查询速度是靠牺牲写入性能换来的。每建一个索引数据库在写入数据时就要额外维护一棵B树插入要找到位置删除要处理节点变化更新可能涉及索引键值调整。索引越多写入链路越长。我遇到过一张表被加了七八个索引业务方说“每个查询都要快”结果批量导入数据时速度掉了好几倍。这不是数据库不行而是写入时每行都要往每棵索引树里插一遍。所以在生产环境建索引一定要克制先观察慢SQL再针对性建不能拍脑袋给每列都来一个索引。关于“建几个合适”这个问题没有标准答案后面第6章我会细说。2. 索引的存储形态B树、哈希与多级索引2.1 B树大多数数据库的默认答案InnoDB的索引默认就是B树。B树的特点是叶子节点存储数据非叶子节点只存索引键所有叶子节点通过链表串联起来。这个“叶子节点用链表串起来”的设计就是热搜词里“多级索引链表”的出处也是B树比B树更适合数据库的关键原因之一。叶子节点串联解决的是范围查询。比如查“2024年1月到3月”的订单走索引找到1月第一条记录后就可以顺着叶子节点的链表一路往后扫到3月不需要回到树根重新搜索。如果没有这个链表范围查询就得反复在树里跳效率会大打折扣。B树的另一个特点是节点分裂和平衡。当节点满时会分裂成两个删除数据导致节点太空时可能发生合并。这些操作都是为了保持树的平衡确保查询路径长度稳定。你不需要手写B树但理解这点有助于明白为什么频繁增删改的表索引会产生碎片查询性能会慢慢下降。碎片问题在后面的实操章节我会讲怎么处理。2.2 哈希索引等值查询的“特快专列”哈希索引走的是另一条路对索引键做哈希计算得到哈希值后直接定位到数据位置。哈希索引做等值查询极快时间复杂度是O(1)比B树的O(log n)还快。但它有个致命缺点完全无法支持范围查询和排序。你想查“大于某个值”的数据哈希索引只能说“我做不到”。MySQL的MEMORY引擎默认索引类型就是哈希索引适合临时表、缓存表这类场景。InnoDB内部也有自适应哈希索引AHI但它是数据库自动管理的根据查询模式自动为热点页面建哈希索引不需要也不能手动创建。很多文章讲“InnoDB只有B树”严格来说不完全准确但手动建索引时你确实只能建B树。实际工作中哈希索引的存在感不强但面试官爱问。回答时抓住两个核心等值查询快、范围查询废就够了。2.3 主键索引和二级索引回表到底回什么InnoDB的表本身就是按主键索引组织的这种索引叫聚簇索引。聚簇索引的叶子节点直接存整行数据所以通过主键查询一次就能拿到完整记录效率最高。二级索引非聚簇索引的叶子节点存的是主键值。比如你在user_id字段上建了索引查询时先走二级索引找到匹配的主键值再拿这个主键去聚簇索引里查整行数据这个过程就是“回表”。回表意味着多一次磁盘IO所以查询慢的时候如果能避免回表就能快很多。怎么避免回表用覆盖索引。如果查询需要的列都包含在二级索引的叶子节点里那就不需要回表了。比如索引是(user_id, name)查询SELECT name FROM table WHERE user_id 1name已经在索引里直接返回即可省掉一次回表。记住这个思想后面讲联合索引时还会用到。2.4 顺带说一句ES、MongoDB与MySQL的索引不是一回事既然热搜词里出现了Elasticsearch和MongoDB我多说两句概念区分。Elasticsearch里的“索引”其实是文档集合类似关系型数据库里的“库”或“表”它的分词、倒排索引是另一套体系。MongoDB的索引底层是B树不是B树范围查询逻辑和MySQL有一定差异。Windows索引器、m3u8索引就离得更远了它们是文件检索和视频分片协议的概念。搞清楚这些至少能避免在技术讨论时把不同领域的“索引”混为一谈。3. 联合索引最左前缀是怎么推导出来的3.1 联合索引的排列规则联合索引就是把多个字段放进同一棵B树里常见写法是CREATE INDEX idx_user_status ON users(user_id, status)。很多人在这个点上死记“最左前缀”但没想过为什么。联合索引的排序规则是先按第一个字段排序第一个字段相同再按第二个字段排序依此类推。比如(user_id, status)这个联合索引先按user_id排同一个user_id下再按status排。相当于建立了一个按“user_id status”复合排序的目录。这个规则意味着一个联合索引能覆盖多种查询场景。索引(a, b, c)实际上能支持几种查询单独查a、查a和b、查a和b和c。为什么因为索引先按a排好了所以只用a来查询时可以快速定位再加上b还是可以利用索引的有序性继续缩小范围。但单独查b或者单独查c就完全没有顺序可用索引自然发挥不了作用。3.2 最左前缀原则的本质最左前缀的本质就是“索引的有序性是按列从左到右建立的”。用字典类比很好理解字典先按拼音首字母排首字母相同再看第二个字母。你能快速找到所有“a”开头的词也能找到“ab”开头的词但如果你翻开字典想直接找“所有第二个字母是b的词”就会发现根本无从下手因为第二个字母只有在首字母确定后才有序。同理联合索引(a, b, c)中跳过a直接查b ?数据库只能把整棵树的节点全扫一遍才能得到结果这就是索引失效。范围查询也会中断后续列的使用比如WHERE a 100 AND b 5a的范围条件用上了索引但b无法继续走索引精确匹配因为a锁定的是一个范围b在这个范围内不一定有序。理解了这个逻辑就不需要背“最左前缀原则”这几个字了。面试时你把字典类比说一遍再解释为什么跳列会失效面试官基本就认可你懂原理了。3.3 覆盖索引和索引下推联合索引除了支持多列查询还有一个隐藏优势更容易实现覆盖索引。如果一个联合索引包含了查询需要的所有列数据库直接从索引里取数完全不用回表。对于高频查询优先设计这样的索引收益非常明显。还有一个多列索引的优化叫索引下推ICPIndex Condition Pushdown。MySQL 5.6以后默认开启。它做的优化是在联合索引匹配时如果索引用到第二列以后的条件尽量在存储引擎层先用索引里的数据过滤一遍减少回表次数。举个例子索引(a, b)查询WHERE a x AND b LIKE abc%。没有ICP时先通过a定位到一批记录全部回表再在服务层过滤b有ICP时在索引层就把不满足b LIKE的记录过滤掉只对剩下的记录回表。理解了这个原理你就知道为什么联合索引设计得好查询性能能有质的提升。4. 索引失效场景别背结论要看原因4.1 五类典型失效场景网上流传的“索引失效场景”很多说法不够准确有些甚至互相矛盾因为数据库优化器在不同版本、不同数据分布下会有不同行为。我筛选出五个比较经典、基本不会出错的场景并解释它们为什么失效对索引列做函数运算或表达式计算。比如WHERE YEAR(create_time) 2024create_time的索引会被YEAR函数破坏索引树里找不到“2024”这个排序后的键值。应该写成范围条件WHERE create_time 2024-01-01 AND create_time 2025-01-01。隐式类型转换。下面单独细说。LIKE以通配符开头。WHERE name LIKE %张索引是按完整列值建立顺序的前缀未知就没办法快速定位。OR条件中包含非索引列。MySQL很可能选择全表扫描因为它要把OR两边都查出来再合并如果有一边没有索引全表扫反而更简单。违反联合索引最左前缀。前面已经解释过原因。你发现没有这些情况的共同点都是“破坏了索引的有序性”。记住这一点遇到新场景时自己就能判断不用背清单。4.2 隐式类型转换最容易踩的坑隐式类型转换是我在实际工单里遇到最多的问题。比如user_id字段是varchar类型业务代码里传了一个数字SQL写成WHERE user_id 10086。MySQL比较时会把字符串列转成数字这意味着列上发生了一次隐式转换索引直接失效。但如果反过来写成WHERE user_id 10086字符串和字符串比较索引就能正常走。排查方法很简单查看表结构的字段类型再对比SQL里的传参类型。最坑的是应用框架自动绑定参数时经常把值转成非字符串类型导致线上SQL偶然走不上索引。我的习惯是所有关联字段和WHERE条件字段尽量保证数据类型完全一致包括字符集和排序规则也要一致否则join时也可能出现索引失效。4.3 用EXPLAIN判断索引是否真的走了判断索引走没走别靠猜直接看EXPLAIN输出。EXPLAIN SELECT ... 会展示MySQL优化器选择的执行计划关键字段有type、key、rows、Extra。type字段从好到差大致是system const eq_ref ref range index ALL。看到ALL就是全表扫描index是扫描了整个索引树range是范围扫描ref是等值匹配。一个典型的例子EXPLAIN SELECT * FROM orders WHERE user_id 1024;如果输出里type是ref、key显示用了idx_user_idrows很小说明索引生效。如果type是ALL、key是NULL那就得回头检查条件列、类型、函数等。特别注意不同MySQL版本EXPLAIN输出字段略有差异新版还增加了formattree选项但核心看type、key、rows这几个就够定位大多数问题。慢查询日志也是排查利器。开启慢查询日志后把超过阈值的SQL记录下来逐条分析能快速找到集群里拖后腿的查询。5. 实操指南给一张订单表把索引建明白5.1 建索引前先做的事建索引前先收集这个表的所有高频SQL整理出WHERE条件、JOIN字段、ORDER BY字段、GROUP BY字段。然后考虑几点字段区分度要高比如“性别”这种只有两个值的字段就别建索引了字段长度尽量短过长可以用前缀索引更新频繁的字段要慎重因为每次更新都要动索引树。建索引基本语法-- 普通索引 CREATE INDEX idx_user_id ON orders(user_id); -- 联合索引 CREATE INDEX idx_user_status ON orders(user_id, status); -- 唯一索引 CREATE UNIQUE INDEX uk_order_no ON orders(order_no); -- 删除索引 DROP INDEX idx_user_id ON orders;联合索引的字段顺序也有讲究。一般把等值查询的字段放前面范围查询的字段放后面因为范围条件后面的列很难继续用上索引。但这个规律不是绝对的还要结合业务特点调整。5.2 用一条慢SQL演示完整调优过程假设线上订单表orders有500万行数据高频查询是“查某个用户最近一个月的订单”SQL如下SELECT order_id, amount, status FROM orders WHERE user_id 1024 AND created_at 2024-01-01 AND created_at 2024-02-01 ORDER BY created_at DESC;未加索引时EXPLAIN输出typeALLrows接近500万查询耗时接近2秒。这时先分析条件user_id是等值created_at是范围所以考虑建联合索引(user_id, created_at)。执行ALTER TABLE orders ADD INDEX idx_user_created (user_id, created_at);再次EXPLAINtype变成rangerows降到几百行查询耗时降到几十毫秒。然后检查SELECT列的覆盖情况order_id、amount、status都不在索引里所以需要回表。如果这个查询量极大可以进一步把查询列全部包含进去改成覆盖索引(user_id, created_at, order_id, amount, status)这样Extra里显示Using index就不用回表了。不过覆盖索引的代价是索引更大、写入更慢需要根据业务量权衡。5.3 索引维护重复索引、碎片与审计带来的争用索引不是建完就不管了。生产环境里最常见的索引问题是重复索引和闲置索引。比如先建了idx_user_id(user_id)后来又建了idx_user_status(user_id, status)那么idx_user_id大概率是冗余的因为联合索引已经能覆盖user_id单独查询。可以通过information_schema.statistics查出来SELECT table_name, index_name, GROUP_CONCAT(column_name) FROM information_schema.statistics WHERE table_schema your_db GROUP BY table_name, index_name;再人工判断有没有冗余。删除冗余索引能减少写入开销还能释放磁盘空间。频繁增删改的表索引页会产生碎片。碎片多了索引树变得稀疏查询IO次数上升。处理方法是重建索引常用命令是ALTER TABLE ... ENGINE InnoDB或者用OPTIMIZE TABLE。注意这些操作在大型表上可能锁表要选低峰期执行。热搜词里还有个很有意思的“数据库开启审计引起索引争用”。我实际遇到过类似情况某系统为了合规开启了数据库审计功能审计日志写入量激增同时审计功能会扫描大量数据页做记录导致缓冲池中索引页频繁被换出换入表现为索引命中率下降、CPU和IO升高。排查后确认不是索引本身的问题而是审计策略太重。解决方式是更换为独立审计通道或者把审计日志输出到外部系统避免和业务查询抢资源。6. 避坑实录与常见问题速查6.1 为什么有时建了索引却不生效加索引后查询没变快是最常见也最让人崩溃的情况。除了前面讲的函数、类型转换、最左前缀原因外还有几种可能一是数据量太小时优化器觉得走索引还要额外访问索引页不如直接全表扫描快于是主动放弃索引。这种情况下TYPEALL但rows很小性能也能接受不用纠结。二是统计信息过期。优化器做决策依赖表的统计信息如果统计信息陈旧可能做出错误选择。可以执行ANALYZE TABLE 表名刷新统计信息。三是字符集不一致。两张表join时如果字段字符集不同MySQL为了比较可能需要转换导致索引失效。建表时统一用utf8mb4能减少这类问题。四是字段允许NULL且查询用了IS NULL、IS NOT NULL。不同数据库和版本对NULL的索引处理差异很大在MySQL里IS NULL通常还能走索引但IS NOT NULL可能就不走。这个建议在实际环境中用EXPLAIN验证不要凭经验拍板。6.2 索引数量怎么把握索引不是越多越好也不是越少越好。每多一个索引写入时多一份维护成本磁盘空间也多占用一份。对于写多读少的业务如日志采集、订单状态频繁更新索引要精简能覆盖核心查询就行。对于读多写少的业务如报表查询、内容展示可以适当多建几个复合索引来覆盖各种维度。我个人的经验值是常规业务表索引控制在5到6个以内包含联合索引。超过这个数就要开始质疑每个索引的必要性了。特别忌讳的是给每个字段单独建索引因为MySQL在一个查询里通常只能利用一个索引单列索引多了既浪费空间又拖慢写入。6.3 大表加索引的注意事项给千万级的大表加索引最怕的就是长时间锁表、业务抖动。InnoDB支持在线DDLMySQL 5.6以后ALTER TABLE ADD INDEX通常不会锁全表但会占用额外空间和IO。实际操作中我在大表上加索引前会做几件事确认磁盘剩余空间充足选择业务低峰期操作先在测试环境用同样数据量验证执行时间。如果表实在太大可以考虑使用在线表结构变更工具比如pt-online-schema-change。它通过临时表复制数据的方式完成变更对在线业务影响更小。但工具不是万能的也可能引入主从延迟必须提前评估。加完索引后还要持续观察一段时间关注写入性能是否下降、索引是否真的被查询用到、有没有出现新的慢SQL。如果某个索引上线一周都没有被EXPLAIN使用过基本可以判断是多余索引该删就删。我在实际运维中最大的体会是把索引理解成书的目录之后很多问题都能自然推导出来根本不用背。真正决定索引好坏的是对业务查询模式的理解而不是对数据结构的死记。以后遇到慢SQL先别急着加索引先EXPLAIN看一眼type和rows再结合字段类型和查询条件判断大概率能直接定位问题。还是那句话索引不是越多越好合适才是最好。