MySQL 索引失效实战解析：10 个高频场景 + 避坑技巧

在MySQL数据库开发中，索引是提升查询性能的核心手段——合理的索引能让百万级数据查询从秒级缩短到毫秒级，而索引失效则会让索引形同虚设，甚至拖慢整个系统。很多开发者都有过这样的经历：明明给字段建了索引，查询却依然全表扫描，SQL执行超时、数据库CPU飙升，上线后深夜排查性能问题，耗时费力却找不到根源。

其实，索引失效并非偶然，而是开发者在写SQL、设计索引时，忽略了MySQL索引的使用规则，导致MySQL优化器放弃使用索引。本文聚焦实战落地，避开晦涩的底层原理，拆解10个开发中最高频的索引失效场景，每个场景都配套可复制SQL、原因分析和解决方案，再结合企业级真实案例，教你快速排查、精准避坑，让索引真正发挥作用。

一、索引失效的实战痛点与核心价值

1.1 索引失效有多致命？

在高并发、大数据量的业务场景中，索引失效的影响远比我们想象的更严重，这也是后端开发和DBA最常踩的坑之一，主要集中在3个方面：

高频问题1：查询性能暴跌。明明给字段建了索引，查询却触发全表扫描，百万级数据查询耗时从10ms飙升到10s，直接导致接口超时，影响用户体验。比如电商平台的商品列表查询，一旦索引失效，用户点击后加载半天，大概率会直接退出。

高频问题2：拖慢整个系统。索引失效会导致MySQL频繁进行全表扫描，CPU、IO使用率飙升，进而拖慢整个数据库实例，甚至引发系统卡顿、雪崩。曾遇到过一个场景：某平台批量查询用户数据时，因索引失效导致数据库CPU使用率达到100%，所有依赖数据库的接口全部瘫痪，排查后发现只是一条简单的SQL忽略了隐式类型转换。

高频问题3：排查成本极高。开发时未注意索引失效细节，上线后出现性能问题，排查时需要逐行分析SQL、查看执行计划、核对索引设计，往往耗费数小时甚至通宵，不仅影响开发效率，还可能造成业务损失。

更值得警惕的是一个常见认知误区：“建了索引就一定能提升性能”。很多开发者盲目给字段建索引，忽略了索引失效场景，不仅无法提升性能，反而会增加插入、更新、删除的开销——因为每次写操作都需要维护索引，过多的无效索引会拖慢写操作性能。

1.2 精准避坑，让索引真正发挥作用

本文面向后端开发、DBA、测试工程师，全程聚焦“实战落地”，不堆砌B+树、哈希索引等底层原理，重点解决“哪些场景会导致索引失效、怎么排查、怎么避坑”这3个核心问题。

读完本文，你能收获3个核心能力：一是掌握10个高频索引失效场景，开发时主动规避；二是学会用3个实用工具快速排查索引失效问题，找到问题根源；三是掌握可直接套用的优化技巧，无论是写SQL还是设计索引，都能避开陷阱，让索引发挥最大价值。

本文最大的特色的是“实战性”——每个失效场景都配套“可复制SQL+原因分析+解决方案+避坑提醒”，结合真实业务场景，不用死记硬背，看完就能直接套用在项目开发和问题排查中，少走90%的弯路。

1.3 索引失效的本质是“MySQL优化器放弃使用索引”

一句话讲透索引失效的本质：MySQL索引的核心作用是通过快速定位数据，减少扫描范围，从而提升查询性能。但当MySQL优化器判断“使用索引比全表扫描更耗时”，或者查询语句、表结构不符合索引使用规则时，就会放弃使用索引，这就是“索引失效”。

所以，索引优化的关键不是“建更多索引”，而是“规避失效场景、引导优化器正确使用索引”——这也是本文的核心逻辑，后续所有内容都围绕这个逻辑展开。

二、基础铺垫：小白也能看懂的MySQL索引核心认知

在拆解索引失效场景前，先铺垫几个核心认知，小白也能快速理解，避免后续内容晦涩难懂。重点掌握“什么是索引、索引的作用、如何判断索引失效”这3个关键点即可。

2.1 什么是MySQL索引？（极简版）

MySQL索引本质上是一种“数据结构”，类似书籍的目录——书籍的目录能帮我们快速找到目标章节，不用逐页翻阅；MySQL索引能帮MySQL快速定位数据位置，不用扫描整张表，核心目的是提升查询性能，降低数据库IO压力。

需要注意的是，索引不是“越多越好”，也不是“建了就有用”，关键在于“合理设计、正确使用”，否则反而会拖慢数据库性能。

2.2 索引的核心作用与常见类型（实战高频）

索引的核心作用有3个：一是提升查询速度，减少全表扫描；二是降低数据库IO和CPU消耗，减轻服务器压力；三是支撑高并发场景，让大量查询请求能平稳执行。

实战中最常用的4种索引类型，不用记太多，重点掌握前4种即可：

1. 主键索引（primary key）：默认自动创建，唯一且非空，用于唯一标识表中每条数据，查询速度最快，比如用户表的id字段。

2. 唯一索引（unique）：索引字段的值唯一，允许为空（与主键索引的区别），比如用户表的phone字段，确保手机号不重复。

3. 普通索引（index）：最常用的索引类型，无唯一约束，可重复，用于普通查询场景，比如商品表的name字段。

4. 组合索引：将多个字段组合成一个索引，比如订单表的（user_id, order_time）组合索引，用于多条件联合查询，后续会重点讲解其使用规则。

全文索引（fulltext）主要用于文本搜索，比如文章内容检索，实战中使用较少，且容易被Elasticsearch替代，本文不重点讲解。

2.3 索引失效的核心判断方法（快速定位）

开发中遇到查询变慢，如何快速判断是不是索引失效？教你3个简单实用的方法，直接套用即可：

方法1：使用EXPLAIN分析SQL执行计划（最核心、最常用）。在SQL语句前加上EXPLAIN，执行后查看3个关键字段：

• type字段：表示查询类型，若显示“ALL”，说明触发了全表扫描，大概率是索引失效；

• key字段：表示实际使用的索引，若显示“NULL”，说明没有使用任何索引，索引失效；

• rows字段：表示扫描的行数，行数越多，查询效率越低，若行数接近表中总数据量，说明全表扫描，索引失效。

方法2：对比查询耗时。建索引前后，分别执行同一条SQL，查看执行耗时；若建索引后耗时无明显下降（比如从10s降到8s），大概率是索引失效。

方法3：查看慢查询日志。开启MySQL慢查询日志，会记录执行时间超过指定阈值（比如1s）的SQL，定位到耗时较长的SQL后，再用EXPLAIN分析，判断是否存在索引失效。

2.4 索引不是越多越好，失效比不建更糟

很多开发者容易陷入两个误区，一定要避开：

误区1：建越多索引越好。过多的索引会增加插入、更新、删除的开销——每次执行写操作时，MySQL不仅要修改表数据，还要维护所有相关索引（比如插入一条数据，需要更新该数据涉及的所有索引的B+树结构），反而会拖慢写操作性能。

误区2：只要建了索引，查询就会变快。忽略索引失效场景，即使建了索引，MySQL也会放弃使用，依然执行全表扫描，不仅无法提升性能，还会增加索引维护成本，相当于“做无用功”。

核心原则：按需建索引，聚焦高频查询场景；建完索引后，务必用EXPLAIN验证是否生效，避免建立无效索引。比如用户表的phone字段经常用于查询，就建唯一索引；而性别字段很少单独查询，就不用单独建索引。

三、10个高频索引失效场景（附案例+解决方案）

本节是本文的核心内容，覆盖开发中最常遇到的10个索引失效场景，每个场景都按照“失效案例（可复制SQL）→ 原因分析 → 解决方案 → 避坑提醒”的逻辑拆解，所有SQL都可直接复制到MySQL中测试，确保实战落地。

3.1 场景1：索引字段使用函数操作（高频Top1）

这是最常见的索引失效场景，没有之一，很多开发者在写SQL时会不经意间踩坑。

失效案例（可复制SQL）：假设用户表（user）的create_time字段建了普通索引，查询“2023年注册的用户”，用函数操作索引字段后，索引失效：

-- 1. 建索引
CREATE INDEX idx_user_create_time ON user(create_time);
-- 2. 失效查询：对索引字段使用YEAR()函数，索引失效，触发全表扫描
SELECT * FROM user WHERE YEAR(create_time) = 2023;
-- 补充：其他常见函数操作（SUBSTR、DATE_FORMAT等）也会导致失效
SELECT * FROM user WHERE SUBSTR(phone, 1, 3) = '138'; -- phone字段有索引，使用SUBSTR函数后失效
SELECT * FROM user WHERE DATE_FORMAT(create_time, '%Y-%m-%d') = '2023-01-01'; -- 索引失效

原因分析：MySQL索引的B+树结构是基于索引字段的原始值构建的，当我们对索引字段使用函数时，MySQL优化器无法识别函数操作后的字段值，无法利用索引快速定位数据，只能对表中每一行都应用该函数，再与条件比较，最终触发全表扫描，导致索引失效。简单说：索引认“原始值”，不认“函数处理后的值”。

解决方案：核心是“避免在索引字段上使用函数”，将函数操作转移到查询值上；若业务必须使用函数，可基于函数结果建立生成列索引。

-- 优化方案1：将函数操作转移到查询值上（推荐，无需修改索引）
SELECT * FROM user WHERE create_time >= '2023-01-01 00:00:00' AND create_time < '2024-01-01 00:00:00';
-- 优化方案2：若必须使用函数，建立生成列索引（MySQL 5.7+支持）
ALTER TABLE user ADD COLUMN create_year INT GENERATED ALWAYS AS (YEAR(create_time)) STORED;
CREATE INDEX idx_user_create_year ON user(create_year);
-- 优化后查询，索引生效
SELECT * FROM user WHERE create_year = 2023;

避坑提醒：常见的函数（SUBSTR、DATE_FORMAT、YEAR、COUNT、SUM、AVG），只要作用在索引字段上，都会导致索引失效；即使是简单的函数操作，也不能掉以轻心，比如TRIM()、UPPER()等。

3.2 场景2：索引字段使用隐式类型转换（高频Top2）

隐式类型转换是仅次于函数操作的高频失效场景，大多是因为开发者忽略了“索引字段类型与查询条件类型一致”，导致MySQL自动进行类型转换，进而索引失效。

失效案例（可复制SQL）：用户表（user）的phone字段是varchar类型（手机号），建了普通索引，但查询时用数字作为条件，未加引号，导致索引失效：

-- 1. 建索引（phone字段为varchar类型）
CREATE INDEX idx_user_phone ON user(phone);
-- 2. 失效查询：查询条件为数字，与索引字段类型（varchar）不匹配，触发隐式转换
SELECT * FROM user WHERE phone = 13800138000; -- 索引失效，全表扫描
-- 补充：其他隐式转换场景
SELECT * FROM user WHERE create_time = '2023-01-01'; -- create_time是datetime类型，查询值是字符串，可能触发隐式转换

原因分析：当查询条件的类型与索引字段的类型不匹配时，MySQL会自动进行隐式类型转换（比如将varchar类型的phone字段转换为int类型，与查询条件的数字匹配）。这种转换会改变索引字段的原始值，导致MySQL优化器无法匹配索引，只能触发全表扫描，索引失效。

解决方案：核心是“确保查询条件的类型与索引字段类型完全一致”，避免MySQL自动进行隐式转换，具体可根据字段类型调整查询条件：

-- 优化方案：查询条件加引号，与varchar类型一致，索引生效
SELECT * FROM user WHERE phone = '13800138000';
-- 补充：datetime类型字段的正确查询方式
SELECT * FROM user WHERE create_time = STR_TO_DATE('2023-01-01', '%Y-%m-%d'); -- 类型一致，索引生效

避坑提醒：常见的隐式转换场景有3种，务必注意：① varchar类型字段用数字查询；② int类型字段用字符串查询；③ datetime类型字段用纯字符串查询。开发时要严格匹配字段类型，不要依赖MySQL的隐式转换。

3.3 场景3：索引字段使用模糊查询（%开头）

模糊查询（like）在开发中非常常用，但如果使用不当，很容易导致索引失效，尤其是“%开头”的模糊查询。

失效案例（可复制SQL）：商品表（product）的name字段建了普通索引，查询“包含‘手机’的商品”，用“%开头”的模糊查询，索引失效：

-- 1. 建索引
CREATE INDEX idx_product_name ON product(name);
-- 2. 失效查询：%放在开头，索引失效，全表扫描
SELECT * FROM product WHERE name LIKE '%手机';
SELECT * FROM product WHERE name LIKE '%手机%'; -- 中间包含%，同样失效
-- 有效查询：%放在结尾，索引生效
SELECT * FROM product WHERE name LIKE '手机%'; -- 索引生效，范围扫描

原因分析：MySQL索引的B+树是按照索引字段的原始值排序的，类似字典的排序规则，只能从“开头”开始匹配。当模糊查询的通配符%放在开头时，MySQL无法确定索引的起始位置，无法利用索引进行范围扫描，只能逐行扫描表中所有数据，判断是否符合条件，导致索引失效；而%放在结尾时，MySQL能从索引的起始位置开始匹配，正常使用索引。

解决方案：根据业务场景选择合适的查询方式，优先避免%开头的模糊查询；若必须使用，可采用全文索引或Elasticsearch替代。

-- 优化方案1：尽量将%放在结尾（推荐，无需修改索引）
SELECT * FROM product WHERE name LIKE '手机%'; -- 匹配“手机XX”类商品，索引生效
-- 优化方案2：必须%开头，使用全文索引（MySQL 5.6+支持）
ALTER TABLE product ADD FULLTEXT INDEX ft_product_name(name);
-- 全文索引查询，替代%开头的模糊查询
SELECT * FROM product WHERE MATCH(name) AGAINST('手机' IN BOOLEAN MODE);
-- 优化方案3：大数据量场景，用Elasticsearch替代（推荐，性能更优）

避坑提醒：记住一个核心规则：like查询中，只有“%放在结尾”（如‘手机%’）能使用索引，“%放在开头”（如‘%手机’）和“中间包含%”（如‘%手机%’）都会导致索引失效；另外，全文索引只适用于文本搜索，且不支持精确匹配，需根据业务场景选择。

3.4 场景4：组合索引不遵循“最左前缀原则”（高频Top3）

组合索引是实战中最常用的索引类型之一，但其使用有严格的“最左前缀原则”，一旦违反，就会导致索引失效，很多开发者容易忽略这一点。

失效案例（可复制SQL）：订单表（order）建立了组合索引（user_id, order_time），查询时跳过最左侧的user_id字段，直接查询order_time，导致索引失效：

-- 1. 建组合索引（顺序：user_id, order_time）
CREATE INDEX idx_order_userid_ordertime ON `order`(user_id, order_time);
-- 2. 失效查询：跳过最左侧的user_id，直接查询order_time，索引失效
SELECT * FROM `order` WHERE order_time > '2023-01-01';
-- 补充：其他失效场景（跳过左侧字段、打乱顺序）
SELECT * FROM `order` WHERE order_time > '2023-01-01' AND user_id = 1001; -- 顺序打乱，不影响（MySQL会自动调整顺序）
SELECT * FROM `order` WHERE order_time > '2023-01-01' AND status = 1; -- 跳过user_id，索引失效

原因分析：组合索引的生效遵循“最左前缀原则”，简单说就是“必须从最左侧的字段开始匹配，不能跳过左侧字段”。组合索引（a,b,c）的生效逻辑是：能匹配a、a+b、a+b+c，但不能匹配b、c、b+c——因为组合索引的B+树是按照“a→b→c”的顺序构建的，跳过a字段，MySQL无法定位索引的起始位置，只能放弃使用索引，触发全表扫描。

解决方案：查询时必须包含组合索引的最左侧字段；若需频繁跳过左侧字段查询，考虑单独为该字段建立普通索引，避免索引失效。

-- 优化方案1：查询时包含最左侧的user_id字段，索引生效
SELECT * FROM `order` WHERE user_id = 1001 AND order_time > '2023-01-01';
-- 优化方案2：若需频繁单独查询order_time，单独建普通索引
CREATE INDEX idx_order_ordertime ON `order`(order_time);
-- 优化后查询，索引生效
SELECT * FROM `order` WHERE order_time > '2023-01-01';

避坑提醒：组合索引的字段顺序很重要，需将“查询频率最高、选择性最高”的字段放在最左侧——选择性越高，索引能过滤的数据越多，查询性能越好。比如订单表中，user_id的查询频率比order_time高，所以将user_id放在组合索引的左侧。

3.5 场景5：使用or连接查询，其中一个字段无索引

or连接查询在开发中很常用，但如果or连接的多个条件中，有一个字段无索引，就会导致整个查询的索引失效，这个坑很容易被忽略。

失效案例（可复制SQL）：用户表（user）的phone字段有索引，但name字段无索引，用or连接两个条件，导致整个查询索引失效：

-- 1. 建索引（phone字段有索引，name字段无索引）
CREATE INDEX idx_user_phone ON user(phone);
-- 2. 失效查询：or连接，name字段无索引，导致整个查询索引失效
SELECT * FROM user WHERE phone = '13800138000' OR name = '张三';
-- 补充：若两个字段都有索引，or连接会生效（MySQL 5.6+支持索引合并）
CREATE INDEX idx_user_name ON user(name);
SELECT * FROM user WHERE phone = '13800138000' OR name = '张三'; -- 索引生效

原因分析：MySQL优化器在处理or连接查询时，会判断“是否所有条件都能使用索引”。如果有一个条件的字段无索引，MySQL会认为“使用索引的成本高于全表扫描”——因为无索引的字段需要全表扫描，此时即使其他字段有索引，MySQL也会放弃所有索引，直接进行全表扫描，导致索引失效。

解决方案：有两种思路，一是为or连接的所有字段都建立索引；二是若无法建索引，用union替代or，避免索引失效。

-- 优化方案1：为name字段建索引，所有条件都有索引，or连接生效
CREATE INDEX idx_user_name ON user(name);
SELECT * FROM user WHERE phone = '13800138000' OR name = '张三'; -- 索引生效
-- 优化方案2：无法建索引，用union替代or（需结合业务场景，确保无重复数据）
SELECT * FROM user WHERE phone = '13800138000'
UNION
SELECT * FROM user WHERE name = '张三';

避坑提醒：即使只有一个字段无索引，or连接也会导致所有索引失效，这是很多开发者容易踩的坑；另外，union和or的区别是：union会去重，or不会去重，使用时需根据业务场景选择，避免出现数据不一致。

3.6 场景6：使用not in、not exists查询（部分场景失效）

not in、not exists是开发中常用的否定查询方式，但这类查询并非一定会导致索引失效，而是“视数据量而定”，大数据量场景下容易失效。

失效案例（可复制SQL）：订单表（order）的status字段建了普通索引，用not in查询“非已完成”的订单，当数据量较大时，索引失效：

-- 1. 建索引
CREATE INDEX idx_order_status ON `order`(status);
-- 2. 失效查询：大数据量下，not in导致索引失效，全表扫描
SELECT * FROM `order` WHERE status NOT IN ('completed', 'shipped');
-- 补充：not exists的失效场景
SELECT * FROM `order` o WHERE NOT EXISTS (SELECT 1 FROM user u WHERE u.id = o.user_id); -- 大数据量下失效

原因分析：not in、not exists属于否定查询，其查询结果集的大小决定了索引是否生效。当查询结果集占表中总数据量较大时（如超过30%），MySQL优化器会判断“使用索引的成本高于全表扫描”——因为需要扫描大部分索引数据，不如直接全表扫描高效，因此会放弃索引；而小数据量场景下，查询结果集较小，MySQL会正常使用索引。

解决方案：小数据量场景下，可正常使用not in、not exists；大数据量场景下，建议用left join … on … is null替代，避免索引失效。

-- 优化方案：大数据量下，用left join替代not in，索引生效
SELECT o.* FROM `order` o
LEFT JOIN user u ON o.user_id = u.id
WHERE u.id IS NULL;
-- 补充：status字段的not in优化
SELECT * FROM `order` WHERE status IN ('pending', 'processing', 'cancelled'); -- 用肯定查询替代否定查询，索引生效

避坑提醒：not in、not exists并非一定失效，核心看查询结果集占比，建议用EXPLAIN验证；另外，not in查询中，若子查询包含NULL值，会导致查询结果不准确，需格外注意。

3.7 场景7：索引字段使用!=、<>、is not null查询

使用!=、<>（不等于）、is not null这类否定运算符查询索引字段，也容易导致索引失效，本质和not in类似，都是因为查询结果集较大。

失效案例（可复制SQL）：用户表（user）的status字段建了普通索引，用!=、is not null查询，导致索引失效：

-- 1. 建索引
CREATE INDEX idx_user_status ON user(status);
-- 2. 失效查询：使用!=、is not null，索引失效，全表扫描
SELECT * FROM user WHERE status != 1;
SELECT * FROM user WHERE status <> 1;
SELECT * FROM user WHERE status IS NOT NULL;
-- 补充：is null不会失效
SELECT * FROM user WHERE status IS NULL; -- 索引生效，正常查询

原因分析：!=、<>、is not null都属于“范围查询”，且这类查询的结果集通常较大（比如查询“非活跃用户”，可能占总用户数的80%）。MySQL优化器判断“全表扫描比使用索引更高效”，因此会放弃索引，触发全表扫描；而is null查询的结果集通常较小（比如 NULL值较少），因此不会导致索引失效——MySQL支持NULL值索引，能正常定位NULL值数据。

解决方案：尽量避免使用这类否定运算符；若必须使用，可调整查询逻辑，用肯定查询替代，或基于业务场景拆分查询。

-- 优化方案1：用肯定查询替代否定查询，索引生效
SELECT * FROM user WHERE status IN (0, 2, 3); -- 替代status != 1
-- 优化方案2：必须使用is not null，拆分查询（适用于大数据量）
SELECT * FROM user WHERE status = 0
UNION
SELECT * FROM user WHERE status = 2
UNION
SELECT * FROM user WHERE status = 3;

避坑提醒：重点区分两个点：① is null不会导致索引失效，is not null会失效；② !=和<>效果一致，都会导致索引失效，开发时尽量避免使用，优先用in替代。

3.8 场景8：查询条件包含“范围查询”，后续字段索引失效

组合索引中，若某个字段使用范围查询（>、<、>=、<=、between…and），会导致该字段后续的所有字段索引失效，这是组合索引使用中的一个关键陷阱。

失效案例（可复制SQL）：订单表（order）建立组合索引（user_id, order_time, amount），查询时user_id用范围查询，导致order_time、amount字段索引失效：

-- 1. 建组合索引（顺序：user_id, order_time, amount）
CREATE INDEX idx_order_userid_ordertime_amount ON `order`(user_id, order_time, amount);
-- 2. 失效查询：user_id用范围查询，后续的order_time、amount索引失效
SELECT * FROM `order` WHERE user_id > 1001 AND order_time > '2023-01-01' AND amount > 100;
-- 补充：范围查询字段后续的字段，即使加了条件，也无法使用索引
SELECT * FROM `order` WHERE user_id > 1001 AND order_time > '2023-01-01'; -- order_time索引失效

原因分析：组合索引的B+树是按照“左→右”的顺序构建的，当某个字段使用范围查询时，MySQL能利用该字段左侧的索引定位数据，但无法确定该字段右侧的索引顺序——比如user_id > 1001，MySQL能找到所有user_id大于1001的数据，但这些数据的order_time、amount字段是无序的，无法利用后续的索引，因此后续字段的索引会失效。简单说：范围查询会“阻断”组合索引后续字段的生效。

解决方案：将范围查询字段放在组合索引的最右侧，避免阻断后续字段；若需频繁对某个字段进行范围查询，考虑单独为该字段建立普通索引。

-- 优化方案1：调整组合索引顺序，将范围查询字段（user_id）放在最右侧
DROP INDEX idx_order_userid_ordertime_amount ON `order`;
CREATE INDEX idx_order_ordertime_amount_userid ON `order`(order_time, amount, user_id);
-- 优化后查询，order_time、amount索引生效，user_id范围查询不影响
SELECT * FROM `order` WHERE user_id > 1001 AND order_time > '2023-01-01' AND amount > 100;
-- 优化方案2：单独为user_id建立普通索引，应对范围查询
CREATE INDEX idx_order_userid ON `order`(user_id);
SELECT * FROM `order` WHERE user_id > 1001 AND order_time > '2023-01-01' AND amount > 100;

避坑提醒：组合索引中，范围查询字段的位置决定了后续字段是否生效，务必将范围查询字段放在最右侧；常见的范围查询运算符（>、<、>=、<=、between…and）都会触发这个问题，开发时需格外注意。

3.9 场景9：索引字段被覆盖查询，却使用select *

覆盖索引是提升查询性能的重要技巧，但很多开发者习惯用select *，导致无法使用覆盖索引，甚至触发索引失效，白白浪费索引的价值。

失效案例（可复制SQL）：用户表（user）建立组合索引（name, phone），查询时用select *，导致无法使用覆盖索引，甚至索引失效：

-- 1. 建组合索引（name, phone），覆盖name和phone字段
CREATE INDEX idx_user_name_phone ON user(name, phone);
-- 2. 失效查询：使用select *，无法使用覆盖索引，可能触发全表扫描
SELECT * FROM user WHERE name = '张三';
-- 有效查询：只查询索引覆盖的字段，使用覆盖索引，无需回表
SELECT name, phone FROM user WHERE name = '张三'; -- 索引生效，性能最优

原因分析：覆盖索引的核心是“查询的所有字段，都包含在索引中”，此时MySQL无需回表查询（无需访问数据表，直接从索引中获取数据），查询性能极高。而select * 会查询表中所有字段，若索引未覆盖所有字段（比如上述案例中，user表还有id、create_time等字段），MySQL会进行“回表查询”——先通过索引找到数据的主键，再通过主键查询表中所有字段。若回表成本过高（比如查询数据量较大），MySQL会放弃使用索引，触发全表扫描。

解决方案：核心是“避免使用select *”，只查询需要的字段，让查询能使用覆盖索引；若业务需要查询较多字段，可调整索引，将需要查询的字段加入组合索引，构建覆盖索引。

-- 优化方案1：避免select *，只查询需要的字段，使用覆盖索引
SELECT name, phone FROM user WHERE name = '张三'; -- 索引生效，无需回表
-- 优化方案2：若需查询更多字段，调整索引，构建覆盖索引
-- 需查询name、phone、create_time，将create_time加入组合索引
DROP INDEX idx_user_name_phone ON user;
CREATE INDEX idx_user_name_phone_createtime ON user(name, phone, create_time);
-- 优化后查询，使用覆盖索引，无需回表
SELECT name, phone, create_time FROM user WHERE name = '张三';

避坑提醒：select * 不仅会导致无法使用覆盖索引，还会查询多余字段，增加IO消耗，开发时务必养成“按需查询字段”的习惯；覆盖索引能大幅提升查询性能，尤其是大数据量场景，建议优先使用。

3.10 场景10：数据量过小/索引选择性差，优化器放弃索引

这种场景容易被忽略：即使建了索引，若表中数据量过小，或索引字段的选择性极差，MySQL优化器也会放弃使用索引，触发全表扫描。

失效案例（可复制SQL）：用户表（user）只有50条数据，或性别（gender）字段建了索引（选择性极差），查询时索引失效：

-- 场景1：数据量过小（50条数据），建索引也失效
CREATE INDEX idx_user_name ON user(name);
SELECT * FROM user WHERE name = '张三'; -- 索引失效，全表扫描
-- 场景2：索引选择性差（gender字段只有男、女两个值）
CREATE INDEX idx_user_gender ON user(gender);
SELECT * FROM user WHERE gender = '男'; -- 索引失效，全表扫描

原因分析：有两个核心原因：① 数据量过小时，全表扫描的耗时比使用索引更短——使用索引需要先定位索引位置，再查询数据，而全表扫描直接遍历所有数据，对于几十条、几百条数据，全表扫描更快，MySQL优化器会选择放弃索引；② 索引选择性差，指索引字段的唯一值比例极低（如gender字段，只有2个唯一值），索引无法有效缩小查询范围，使用索引的成本高于全表扫描，因此优化器放弃索引。

解决方案：小表无需建索引（数据量小于1000条，可不用建索引）；索引选择性差的字段（如性别、状态、类型等），不建议单独建索引，可作为组合索引的辅助字段，提升索引选择性。

-- 优化方案1：小表无需建索引，直接全表扫描，性能更优
DROP INDEX idx_user_name ON user;
SELECT * FROM user WHERE name = '张三'; -- 全表扫描，耗时更短
-- 优化方案2：选择性差的字段，作为组合索引的辅助字段
-- 性别（gender）+ 姓名（name）组合索引，提升索引选择性
CREATE INDEX idx_user_gender_name ON user(gender, name);
-- 优化后查询，索引生效（组合索引选择性更高，能有效缩小查询范围）
SELECT * FROM user WHERE gender = '男' AND name = '张三';

避坑提醒：建索引前，需先评估两个因素：一是数据量（小表无需建索引）；二是索引选择性（选择性低于30%的字段，不建议单独建索引）。索引选择性的计算公式：索引选择性 = 唯一值数量 / 总数据量，选择性越接近1，索引性能越好。

四、索引失效排查与解决流程（实战落地）

开发中遇到查询变慢、索引失效，不用慌，掌握“排查3步走”，就能快速定位问题、解决问题，流程可直接套用，适合所有开发场景。

4.1 索引失效排查3步走（直接套用）

第一步：定位问题SQL。通过两个途径定位：① 查看数据库慢查询日志，找到执行时间超过阈值（如1s）的SQL，这些SQL大概率存在索引失效；② 结合业务监控，找到接口超时的接口，定位到接口对应的查询SQL（比如用户查询接口超时，找到该接口的用户查询SQL）。重点关注“执行时间长、扫描行数多”的SQL。

第二步：分析执行计划。在问题SQL前加上EXPLAIN，执行后查看3个关键字段：type（是否为ALL，全表扫描）、key（是否为NULL，未使用索引）、rows（扫描行数，是否接近总数据量）。若type=ALL、key=NULL、rows接近总数据量，说明确定是索引失效。

第三步：定位失效场景。将问题SQL与前面10个高频失效场景逐一对比，找到具体的失效原因——比如SQL中对索引字段使用了函数，就是场景1；用了%开头的模糊查询，就是场景3；组合索引未遵循最左前缀原则，就是场景4，定位后针对性优化即可。

4.2 索引失效解决方案（通用版）

无论哪种失效场景，都可以从以下4个维度优化，通用且可落地：

方案1：优化SQL语句。核心是规避失效场景：避免在索引字段上使用函数、避免隐式类型转换、避免%开头的模糊查询、组合索引遵循最左前缀原则、避免select * 等，让MySQL优化器能正常使用索引。

方案2：优化索引设计。根据业务场景调整索引：删除无效索引（如小表的索引、选择性差的单列索引）、调整组合索引字段顺序（范围查询字段放右侧、高频查询字段放左侧）、建立覆盖索引（按需包含查询字段）、补充缺失索引（or连接的字段、高频查询字段）。

方案3：调整查询逻辑。用更高效的查询方式替代低效方式：用union替代or、用left join … on … is null替代not in、用肯定查询替代否定查询（!=、is not null），减少索引失效的概率。

方案4：调整数据库配置。根据业务场景，调整MySQL优化器参数，比如用force index强制使用指定索引（谨慎使用，仅适用于确认索引有效，但优化器未使用的场景）；调整innodb_buffer_pool_size，提升索引缓存效率，减少IO消耗。

4.3 常用排查工具（3个实用工具）

开发和排查索引失效时，3个工具足够用，无需复杂工具，重点掌握用法：

工具1：EXPLAIN（最核心工具）。用法：在SQL前加上EXPLAIN，执行后查看执行计划，重点关注type、key、rows字段，快速判断索引是否失效，定位失效原因。比如EXPLAIN SELECT * FROM user WHERE phone = 13800138000; 若key=NULL、type=ALL，说明索引失效。

工具2：慢查询日志。用法：开启慢查询日志（配置slow_query_log=1，long_query_time=1），会记录执行时间超过1s的SQL，定位到耗时较长的问题SQL，再用EXPLAIN分析。适合线上环境排查索引失效问题。

工具3：show index from 表名（查看索引信息）。用法：执行show index from user; 可查看用户表的所有索引，包括索引名称、索引字段、索引类型等，确认索引是否存在、是否合理，比如查看组合索引的字段顺序是否正确。

五、真实业务案例（企业级，避坑关键）

结合两个企业级真实业务场景，完整还原索引失效的“踩坑→排查→解决”全流程，让你学会在实际项目中应用前面的技巧，避开常见陷阱。

5.1 案例1：用户查询接口超时——隐式类型转换导致索引失效

案例背景：某互联网平台的“用户查询接口”，根据手机号查询用户信息，用户表（user）的phone字段是varchar类型，建了唯一索引，但接口上线后频繁超时，高峰期甚至无法访问，排查后发现查询耗时超过10s。

踩坑点：开发人员写SQL时，忽略了phone字段的类型，查询条件用数字（未加引号），导致隐式类型转换，索引失效，触发全表扫描——用户表有100万条数据，全表扫描耗时极长，导致接口超时。

关键SQL（失效）：

-- phone是varchar类型，查询条件用数字，隐式类型转换，索引失效
SELECT * FROM user WHERE phone = 13800138000;

排查过程：① 定位问题SQL：通过慢查询日志，找到该用户查询SQL，执行时间12s；② 分析执行计划：EXPLAIN执行该SQL，发现type=ALL（全表扫描）、key=NULL（未使用索引），确认索引失效；③ 定位失效场景：对比10个高频场景，发现是场景2（隐式类型转换）——phone字段是varchar类型，查询条件是数字，导致索引失效。

解决方案：查询条件加引号，确保与phone字段（varchar类型）一致，避免隐式类型转换，索引正常生效。

-- 优化后SQL，索引生效
SELECT * FROM user WHERE phone = '13800138000';

优化效果：接口耗时从12s缩短至10ms，查询性能提升1200倍，高峰期接口正常响应，无超时问题。

避坑总结：开发时严格匹配字段类型，varchar字段查询必须加引号，int字段查询不用加引号，不要依赖MySQL的隐式转换；尤其是手机号、身份证号等varchar类型字段，查询时务必加引号，这是最容易踩的坑。

5.2 案例2：订单列表查询卡顿——组合索引未遵循最左前缀原则

案例背景：某电商平台的“订单列表接口”，查询用户的订单列表，订单表（order）有50万条数据，建立了组合索引（user_id, order_time），但用户反馈订单列表加载卡顿，排查后发现查询耗时5s，触发全表扫描。

踩坑点：开发人员写查询SQL时，跳过了组合索引最左侧的user_id字段，直接按order_time查询，违反最左前缀原则，导致索引失效，触发全表扫描——订单表数据量大，全表扫描耗时极长，导致列表卡顿。

关键SQL（失效）：

-- 组合索引（user_id, order_time），跳过user_id，直接查询order_time，索引失效
SELECT * FROM `order` WHERE order_time > '2023-01-01';

排查过程：① 定位问题SQL：通过业务监控，找到订单列表接口对应的SQL，执行时间5s；② 分析执行计划：EXPLAIN执行该SQL，type=ALL、key=NULL，确认索引失效；③ 定位失效场景：对比10个高频场景，发现是场景4（组合索引未遵循最左前缀原则）——跳过了最左侧的user_id字段，导致索引失效。

解决方案：调整查询逻辑，增加user_id查询条件（订单列表是用户个人的列表，必然会传入user_id），遵循最左前缀原则，索引正常生效；同时优化SQL，避免select *，使用覆盖索引，进一步提升性能。

-- 优化后SQL，包含user_id，遵循最左前缀原则，索引生效
SELECT id, order_no, order_time, amount FROM `order` WHERE user_id = 1001 AND order_time > '2023-01-01';

优化效果：订单列表查询耗时从5s缩短至50ms，卡顿问题完全解决，用户体验大幅提升；同时，使用覆盖索引，无需回表查询，进一步降低了数据库IO压力。

避坑总结：组合索引必须遵循最左前缀原则，查询时务必包含最左侧字段；开发时要结合业务场景，确保查询条件包含组合索引的左侧字段，避免跳过左侧字段导致索引失效；同时，避免select *，充分利用覆盖索引提升性能。

六、索引失效避坑清单（必记，少走弯路）

整理10个高频避坑点，打印出来贴在桌面，开发时随时查看，能避免90%的索引失效问题，少走弯路：

1. 避免在索引字段上使用函数（如SUBSTR、DATE_FORMAT、YEAR），防止索引失效；

2. 确保查询条件类型与索引字段类型一致，varchar字段查询加引号，避免隐式类型转换；

3. 模糊查询尽量将%放在结尾，避免%开头（如‘%手机’），必要时用全文索引替代；

4. 组合索引必须遵循最左前缀原则，不跳过左侧字段，范围查询字段放在最右侧；

5. or连接查询时，确保所有关联字段都有索引，否则会导致全量索引失效；

6. 大数据量场景避免使用not in、not exists，可用left join … on … is null替代；

7. 避免使用!=、<>、is not null查询索引字段，优先用肯定查询（in）替代；

8. 组合索引中，范围查询会阻断后续字段生效，字段顺序需合理设计；

9. 避免select *，按需查询字段，充分利用覆盖索引，减少回表查询；

10. 小表（数据量<1000条）无需建索引，索引选择性差的字段不建议单独建索引。

七、总结：掌握这3点，彻底规避索引失效

MySQL索引的优化，从来不是“建了就好”，而是“建得合理、用得正确”。