在MySQL的日常使用里索引是提升查询性能最直接、最关键的手段。很多同学对索引的印象停留在“建过就能快”“加个索引万事大吉”但实际上一旦遇到慢查询、索引失效、组合索引顺序不对往往会排查半天也找不到原因。这篇文章我准备把MySQL索引从数据结构到使用姿势、从建索引原则到失效场景彻底讲透结合我这些年实际操作中踩过的坑把这方面的经验一次性倒出来。无论你是刚接触MySQL的新手还是被面试官问过“主键索引和唯一索引的区别”的老手这篇文章都能给你一个清晰的答案。1. 索引到底干了什么本质与数据结构很多人最开始接触索引听到的解释就是“像书的目录一样”。这个类比没错但要真正用好索引必须理解底层的B树到底比目录强大在哪里。1.1 为什么查询慢的根源是“全表扫描”在没有索引的情况下MySQL执行一条SELECT * FROM user WHERE age 30只能把整张表的记录一条一条读出来对比每个age字段。这个过程叫全表扫描数据量小的时候感觉不到一旦表里有几万、几十万行每多一个零查询耗时就可能多好几个数量级。这就是索引存在的第一性原理减少需要扫描的数据量。索引本质上是一个额外维护的、经过排序的数据结构。它把字段的值按照某种规则组织起来让你能像查字典一样快速定位到目标记录而不是翻遍整本字典。1.2 为什么MySQL InnoDB选择了B树而不是二叉树、哈希表先说说为什么不用哈希。哈希索引通过散列函数直接把键值映射到桶单条等值查询WHERE id 100确实快到极致但一旦遇到范围查询WHERE id 100 AND id 200或者排序ORDER BY id哈希就无能为力了因为它只支持等值匹配无法维持任何顺序。再看二叉树。普通的二叉查找树在极端情况下会退化成链表查询复杂度从O(logN)变成O(N)。即便使用平衡二叉树AVL/红黑树每个节点只有两个分支树的高度会随着数据量增长而不可控。一台服务器上存储几百万行数据树深度可能到20多层以上每次访问一个节点就是一次磁盘I/O树越深I/O次数越多。B树的核心优势是矮胖每个节点能存放多个键值单个节点一次I/O就能读取大量数据。非叶子节点只存索引键不存数据所以同样大小的页可以容纳更多的键分支更多。叶子节点按顺序排列且通过链表连接天然支持范围查询和排序。所有数据都存在叶子节点每次查询的路径长度一致性能稳定。层高通常只有3到4层即使一张表有上千万行数据也只需要三到四次磁盘I/O就能定位到目标。用“房子的楼层索引图”来类比B树就像一本高度压缩的楼层指引册你先看楼层大分类再找具体房间最后顺着走廊直达。1.3 哈希索引与自适应哈希索引场景中的补充InnoDB默认使用B树但它也支持自适应哈希索引Adaptive Hash Index。它是在内存里针对热点页自动构建的哈希索引用于加速频繁等值查询不需要手动创建。需要注意它不是显式索引不能出现在SHOW INDEX中。提示MySQL的MEMORY存储引擎默认使用哈希索引适合临时表、快速查缓存场景但不适合范围查询。所以做索引选型之前先确认存储引擎和实际查询类型。2. 索引分类千万别把“主键索引”和“唯一索引”搞混索引可以从多个维度分类常规考点分成三大类数据结构、逻辑功能、物理存储。面试常问的也是这几类。2.1 按逻辑功能分类普通索引最基本的索引没有任何约束只是为了加快查询。唯一索引列值不能重复但允许NULL并且NULL可以有多个。主键索引也是一种唯一索引但它是特殊的一张表只能有一个主键索引且不允许NULL。全文索引在varchar/text等大文本字段上建立用于全文检索底层是倒排索引InnoDB从5.6才支持。组合索引由多个列组成一个索引遵循最左前缀原则。很多人会纠结“主键索引和唯一索引到底哪里不一样”。区别在于主键是表的物理组织方式InnoDB聚簇索引就是主键索引它决定了表数据的物理存储顺序。每张表有个且只有一个聚簇索引。创建了主键后主键索引是聚簇索引如果没有主键InnoDB会选择第一个非空唯一索引作为聚簇索引否则就隐式生成一个6字节的rowid主键。唯一索引是非聚簇索引二级索引每张表可以有多个它只维护索引列的顺序和唯一性不改变表的物理存储组织。从约束角度看主键列自动非空而唯一索引允许NULL在MySQL里NULL值在唯一索引中可能允许多个视隔离级别和具体实现而定但实际中多个NULL是允许的。这导致的一个实际区别是查询主键列直接通过聚簇索引定位行数据而通过二级唯一索引查询通常会多一次回表除非覆盖索引所以有时候用主键查询会比用唯一索引查询更快。2.2 按物理存储方式分类聚簇索引与非聚簇索引聚簇索引clustered index索引键顺序与表数据行的物理存储顺序一致。InnoDB的主键索引就是聚簇索引它的叶子节点直接存储整行数据。非聚簇索引secondary index一般叫二级索引它的叶子节点存储的是索引列的值主键值而不是整行数据。所以通过二级索引查找数据时先找到主键再根据主键回表查询完整行。如果表里没有定义主键InnoDB会找第一个非空唯一索引作为聚簇索引再找不到就隐式生成一个隐藏主键。所以尽量为每张表显式定义主键否则所有二级索引查询都要额外多一次间接寻址性能损耗不可忽略。2.3 覆盖索引、回表、索引下推这三个概念在调优中非常关键。回表使用二级索引查询先从二级索引的叶子节点拿到主键值再到聚簇索引中查找整行数据。这个二次查询过程称为回表。回表意味着多次I/O是性能杀手之一。覆盖索引如果查询所需的所有列都包含在索引里那么二级索引的叶子节点就已经包含了全部需要的数据无需再回表。这被称为“覆盖索引”优化效果非常明显。例如表CREATE TABLE user ( id INT PRIMARY KEY, age INT, name VARCHAR(50), address VARCHAR(100), KEY idx_age_name (age, name) );执行查询SELECT age, name FROM user WHERE age 20;此时age和name均在索引idx_age_name中直接从索引返回结果无需回表。但如果查询SELECT age, name, address FROM ...address不在索引中就需要回表。索引下推Index Condition PushdownICP在MySQL 5.6之后引入。以前带上条件查询时要先通过索引找到全部符合条件的记录再回表对其它列做过滤有了ICP在索引遍历过程中就会对索引包含的字段预先过滤减少回表次数。例如WHERE name LIKE 张% AND age 20如果索引是(name, age)MySQL会在索引层面对age 20进行判断不满足条件的直接跳过不回表。3. 创建索引的正确姿势语法、组合索引与最左前缀建索引不是“想到就加”更不是“越多越好”。下面把我实际建索引时常用的语法、规则和踩过的雷都整理出来。3.1 索引创建语法与简单示例-- 创建索引 CREATE INDEX idx_name ON table_name (col1, col2); -- 创建唯一索引 CREATE UNIQUE INDEX uk_name ON table_name (col1); -- 创建全文索引 CREATE FULLTEXT INDEX ft_content ON table_name (content); -- 建表时创建索引 CREATE TABLE t ( id INT NOT NULL AUTO_INCREMENT, name VARCHAR(50), age INT, PRIMARY KEY (id), KEY idx_age (age), UNIQUE KEY uk_name (name) ) ENGINEInnoDB; -- 查看索引 SHOW INDEX FROM table_name;此外ALTER TABLE也可以用来创建索引ALTER TABLE table_name ADD INDEX idx_name (col);不建议直接用ALTER TABLE在线上大表加索引因为会锁表。可以考虑使用在线DDL工具或者低峰期操作。3.2 组合索引与最左前缀原则组合索引是应对多条件查询的利器但它的顺序非常重要。例如建立组合索引(a, b, c)实际上相当于同时建了(a)、(a, b)、(a, b, c)三个索引。执行WHERE a 1 AND b 2 AND c 3时可以利用整个索引。 执行WHERE a 1 AND b 2时可以利用前两列。 执行WHERE b 2 AND c 3时因为跳过了最左列a索引大概率失效。这就是“最左前缀原则”查询条件必须从索引的最左列开始连续匹配才能最大化利用索引。但有一个常见误区WHERE a 1 AND c 3跳过b此时a可以用来定位c无法利用索引只能作为普通条件过滤。优化方式是调整索引顺序或者把高频查询列放在最前面。还有一个重要的查询条件WHERE a 1 AND b 100 AND c 3。因为b是范围条件c不能继续走索引所以设计组合索引时应该把范围条件列放在组合索引的最后把等值条件列放在前面。3.3 建立索引依据与实际经验我在建立索引时会综合考虑几个因素字段基数索引列的去重值数量要高。像性别字段只有“男”“女”基数极低加索引几乎没有意义。一般建议取选择性高的列如手机号、邮箱、身份证等。查询频率经常出现在WHERE、ORDER BY、GROUP BY、JOIN字段上的列优先考虑。数据区分度区分度 去重值数 / 总行数越接近1越好。避免冗余索引已有(a, b)索引时再去建(a)索引通常会冗余。MySQL会额外维护索引增加写入开销。控制索引数量一张表索引数量一般不建议超过5个多的索引会让INSERT/UPDATE/DELETE变得很慢因为每次数据变更都需要同步更新索引。索引是拿空间换时间也是拿写性能换读性能。3.4 我认为最实用的建索引策略优先用主键查询尽量使用WHERE id ...。小表不用过度优化几十上百行的表全表扫描往往比索引查找更高效。多表关联时尽量让关联字段的类型、长度、排序规则一致否则索引可能失效。对varchar字段使用前缀索引ALTER TABLE t ADD INDEX idx_code (code(10))只对前10个字符建索引可以大幅度压缩索引空间但需要测试选择性足够高。注意给大字段如TEXT、BLOB建索引时通常必须指定前缀长度否则会报错Specified key was too long。4. 索引失效的坑真实场景下的排查实录建立索引不代表一定被使用。我遇到过不少开发同事提交的SQL说是加了索引但查询依然很慢一排查就是触发了索引失效。下面把常见的索引失效场景列出来附上实际排查思路。4.1 常见的索引失效场景使用函数操作索引列WHERE DATE(create_time) 2024-01-01。因为对create_time列使用了函数索引无法用于匹配。建议改为范围查询WHERE create_time 2024-01-01 AND create_time 2024-01-02。隐式类型转换字段类型是varchar(20)查询条件却写了WHERE phone 13800000000数值比较。MySQL会尝试把字符串转为数值导致索引失效。解决办法是用字符串WHERE phone 13800000000。违背最左前缀原则查询条件里跳过组合索引的最左列索引失效。使用LIKE模糊查询且通配符在开头WHERE name LIKE %张%这种搜索目标不明确索引无法利用。但WHERE name LIKE 张%是可以走索引前缀匹配的。使用OR连接不含索引列WHERE id 100 OR age 20如果age没有索引MySQL无法用id索引快速查询后合并只能放弃索引全表扫描。优化方式是改为UNION或者保证OR两边都有索引。使用IS NOT NULL或 等有些场景下WHERE col IS NOT NULL可能无法走索引尤其当大部分行都满足条件时。相比IS NULLIS NOT NULL普遍选择性低。NOT IN、、!MySQL优化器有时会认为负向查询无法利用索引。通常可以通过EXISTS、NOT EXISTS或反连接改写。统计信息不准确表数据量变化大但很久没做ANALYZE TABLE优化器误判放弃索引。这时候执行ANALYZE TABLE t;重新统计即可。另外就是优化器本身的选择就算索引可用如果表绝大部分行都满足条件优化器可能选择全表扫描会更高效。这不算失效是优化器认为全表扫描成本更低。4.2 用 EXPLAIN 定位索引问题我排查慢查询时第一件事就是执行EXPLAIN。EXPLAIN SELECT * FROM user WHERE age 30 AND name LIKE 张%;重点关注几列type从All到index到range到ref到eq_ref到const/system扫描效率递增。如果出现All说明全表扫描。key实际使用的索引名。rows预估扫描行数。Extra如果出现Using filesort说明文件排序需要关注排序字段是否有索引。出现Using temporary通常是临时表也要优化。排查经验遇到Using filesort先考虑把排序字段加入索引遇到Using join buffer检查小表驱动大表、连接字段的索引。这些都可以通过调整SQL或加索引解决。4.3 案例where条件a and b应该怎么建索引热搜词里有“mysql where条件a and b应该怎么建索引”这其实是一个很典型的组合索引问题。假设表CREATE TABLE orders ( id INT PRIMARY KEY, user_id INT, status TINYINT, create_time DATETIME );常见的查询是SELECT * FROM orders WHERE user_id ? AND status ? ORDER BY create_time DESC;首先确定哪个条件更高频、区分度更高。user_id区分度高应该放最左侧。组合索引设计为(user_id, status, create_time)这样既能同时过滤user_id和status又因为create_time在最后可以避免ORDER BY引发的filesort。如果几乎总是只按user_id查询但很少用到status那么(user_id, create_time)可能更合理。所以建索引之前要统计查询模式的频率而不是无脑加字段。拿“厨房做饭”来类比组合索引顺序像备菜顺序。你总是先准备主菜user_id再调味status最后摆盘create_time。如果有一天需要直接摆盘却发现前面的菜还没备步骤就乱了索引也会失效。4.4 组合索引字段顺序的计算与本质给组合索引选择字段顺序本质是“最大化过滤”和“支撑排序”。如果查询条件是WHERE a 1 AND b 2 AND c 3把三个字段全放进索引顺序对等的效率其实不大。但如果有排序、范围就要认真考虑WHERE a 1 ORDER BY b顺序(a, b)是合理的。WHERE a 1 ORDER BY b如果a是范围b无法保证有序通常需要filesort。尽力让ORDER BY字段包含在组合索引里且和WHERE中的等值列连续。5. 索引的存储机制与调优实践光会建索引还不够索引坏了、胀了、没用上都是运维碰到的真实问题。这一部分讲存储机制与调优工具。5.1 InnoDB索引的物理存储InnoDB数据由页Page组成默认页大小16KB。索引也是一种页。每个索引页从磁盘加载时至少要一次I/O所以降低树的层数、减少访问页数优化逻辑就顺理成章。聚簇索引的叶子页直接存行记录所以主键如果太长会让每个二级索引占用的叶子节点变大因为二级索引记录里保存主键值。这也是建议使用自增主键而非UUID作为主键的一个重要原因。UUID不适合主键不仅因为长更是因为随机性会造成聚簇索引插入时频繁页分裂产生很多碎片。5.2 索引碎片优化当数据频繁插入、删除、更新时索引页可能产生碎片导致扫描索引时访问大量无效页查询变慢。优化方式对单表执行OPTIMIZE TABLE t;会重建表并整理索引。对分区表或大表谨慎使用OPTIMIZE TABLE可能在锁定状态下运行很久建议借助在线DDL或低峰期操作。另一个常见问题索引被删除又重新建立后即使量级相同可能大小相差很大这种碎片问题容易被忽视。定期巡检information_schema.TABLES中的DATA_FREE值能看出是否有大量空闲空间。5.3 索引大小估算索引不是免费午餐。以InnoDB组合索引为例每个二级索引页中大约可以存放几百到上千个索引项。估算方式索引条目大小 索引列长度 主键长度 页槽开销。假设主键为BIGINT8字节索引列为INT4字节一个条目约20字节。一个16KB页大约能存800个条目。100万行数据二级索引大约需要1250个页即约20MB空间。实际情况因为页填充率、碎片问题会更多。所以索引空间上的开销要看但相比查询性能提升往往是值得的。不过一张表给10个字段都建索引空间冗余和写放大就可能失控。5.4 慢查询日志与performance_schema线上排查索引问题时先开启慢查询日志SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1;然后在慢日志里抓取超过阈值的SQL逐一EXPLAIN。performance_schema中events_statements_summary_by_digest可以做语句聚合分析找出哪些SQL占用了大量资源。这个比单纯看慢日志更全面能暴露短小但高频的查询。6. 面试高频题主键、唯一索引、索引失效、SQL优化React项目的面试题不一样但MySQL这块几乎是必考区。这里结合热搜词里的高频提问给大家整理一套可以直接讲的逻辑。6.1 主键索引和唯一索引到底有哪些区别主键是聚簇索引唯一索引是非聚簇索引二级索引。每张表只允许一个主键但可以有多个唯一索引。主键列不允许NULL唯一索引列允许NULL。主键直接影响表的物理存储顺序唯一索引不改变表存储顺序。查询完整行时通常主键查询比唯一索引查询少一次回表。主键适合作为二级索引的锚点二级索引叶子节点存的就是主键值。6.2 哪些场景会导致索引失效我把以上场景总结成一张速查表序号场景示例后果1函数操作WHERE YEAR(create_time)2024无法走索引2隐式类型转换varchar字段100转换导致失效3LIKE前导通配符LIKE %abc失效4跳过最左列索引(a,b)只用b失效5OR的一侧无索引id1 OR age20全表扫描6负向查询、!、NOT IN大概率失效7索引列参与运算id15失效8优化器估算全表更快小表或大部分行满足放弃索引注意第8条不算真正的失效而是优化器的成本决策。遇到这种情况可以尝试给表重新收集统计信息或者强制索引FORCE INDEX但慎用。6.3 SQL优化答疑从索引角度优化SQL的思路1. 先看慢日志找出真正慢的SQL。 2. EXPLAIN分析type, key, rows, Extra。 3. 如果typeALL看能否加索引。 4. 如果ExtraUsing filesort看ORDER BY字段是否覆盖索引。 5. 如果ExtraUsing temporary检查GROUP BY、DISTINCT、子查询等是否可改写。 6. 避免SELECT *尽量只查必要列为覆盖索引创造条件。 7. 分页优化offset大时改用WHERE id 上页最大id的延迟关联方式。 8. 多表JOIN时尽量用小表驱动大表。举个例子分页查询常见问题SELECT * FROM user ORDER BY id LIMIT 100000, 20;这个查询要扫描10万行后丢弃再返回20行即使有索引也很慢。优化后的写法SELECT * FROM user WHERE id 100000 ORDER BY id LIMIT 20;但这要求id连续无空洞或者用上一页最大id作为条件。真实环境里可能不连续更通用的优化是SELECT u.* FROM user u INNER JOIN ( SELECT id FROM user ORDER BY id LIMIT 100000, 20 ) tmp ON u.id tmp.id;子查询里只扫描索引不取全行所以效率高很多。7. 建索引前必须想清楚的5个问题建索引不是把字段随便放到KEY后面而是要回答清楚以下问题1. 表的数据量到底多大低于几百行的小表全表扫描通常快于索引。索引本身带来的维护开销和存储开销可能得不偿失。2. 这个查询频率高吗低频率的报表统计、后台一次性任务即使慢一点也不会影响用户体验没必要为此建索引。核心线上高频查询才是优先对象。3. 查询列的选择性够高吗性别、状态、是否删除之类的低基数列单独建索引效果很差。如果非要用考虑组合索引或前缀索引。4. 索引会不会和现有索引冗余如果已经有一个(user_id, status)的组合索引再建一个单独的(user_id)索引就是冗余。MySQL会额外写一份索引还会在优化时造成困惑。5. 更新频繁的列适合建索引吗如果某个字段经常被修改而查询很少不建议建索引。每改一次值索引就要重新调整位置写入成本很高。我曾经接手过一个订单表里面对remark字段建了全文索引结果每次插入订单都非常慢最后直接把全文索引去掉业务写入恢复到了毫秒级别。所以索引的“度”真的很重要。8. 聊聊索引实践中的血泪经验最后说几个我自己在真实环境中反复验证过的小经验不写点这些总觉得不完整。第一MySQL 5.7和8.0的索引行为有一些差异。MySQL 8.0支持隐藏索引用ALTER TABLE t ALTER INDEX idx INVISIBLE;可以临时让优化器忽略索引测试其必要性而无需真正删除这对线上验证很有帮助。8.0还对某些查询有了更多的内部优化比如哈希连接但索引的基本原理没有变。第二如果遇到ORDER BY排序慢一定要先确认索引顺序。当时我们有一张登录日志表查询最近一个月登录记录并倒序排列加了(user_id, login_time)索引后还是出现Using filesort。后来发现是因为查询里WHERE user_id IN (...)不是等值条件login_time无法按索引顺序返回排序结果改成OR改写或临时删掉IN才可以解决。排序场景下索引字段要求等值条件先行这个细节很多人忽略。第三组合索引不是越靠前越好。很多人一上来就把id放最左侧实际上如果id是主键而且查询都是通过id定位那单独的组合索引往往没发挥价值。要结合真实的业务查询去设计索引别背“最左前缀”的死书。第四定期通过SHOW INDEX FROM table;查看Cardinality如果值远低于预期说明索引列的选择性不佳。在此基础上重建或者调整索引。第五线上大表加索引要注意锁问题。MySQL 8.0支持在线DDL但也不能完全避免性能抖动建议在低峰期操作并先验证语法。用真实案例来沉淀价值。有一次线上一个order表单表3000万行有个查询经常超时。我们EXPLAIN后发现typeALL扫描行数超过1500万简直灾难。排查后发现这个表的组合索引是(user_id)但是查询条件是WHERE shop_id ? AND status ?完全没有用到任何索引。后来把索引改成(shop_id, status, create_time)同样的SQL扫描行数从1500万变成1万多查询时间从3秒降到几十毫秒。这就是索引优化的真实力量。分享这些不是为了让你背结论而是希望你在遇到类似问题时能有个清晰的排查路径。索引是一个需要结合业务模式、表结构、查询特征去灵活设计的东西没有万能的银弹但理解了底层原理你自然知道该从哪里切入。如果这篇文章能帮你把MySQL索引的来龙去脉搞明白那么你在实际开发、面试、排查慢SQL时都会更有底气。后面遇到具体问题也欢迎随时交流我会把你关心的场景拿出来一起拆一拆。