在MySQL中，索引就像书的目录。用得好，秒查数据；用得不对，反而白建一场。我们以为“建了索引就万事大吉”，却不知一句函数、一个类型不匹配，甚至一个%开头的模糊查询，都可能让索引瞬间失效，导致全表扫描、性能暴跌。

一、索引列参与函数/运算（高危）：对索引列执行函数操作（如：DATE()、SUBSTRING()、CONCAT()）或算术运算（如：col+N、col*N）。

失效原理：MySQL索引基于列的原始值构建B+树，函数/运算会改变索引列的原始有序性，优化器无法利用索引快速匹配，直接触发全表扫描。

正反示例：

-- 错误：索引列被函数包裹（create_time为索引列）
SELECT * FROM users WHERE DATE(create_time) = '2024-01-01';

-- 错误：索引列参与算术运算（age为索引列）
SELECT * FROM users WHERE age + 1 = 20;

-- 正确：函数/运算移至等式右侧
SELECT * FROM users WHERE create_time >= '2024-01-01 00:00:00' 
  AND create_time <= '2024-01-01 23:59:59';
SELECT * FROM users WHERE age = 19;

避坑方法：

1. 所有函数/运算逻辑必须作用于查询值，而非索引列；
2. 复杂日期计算，我们可预计算存储（如：创建create_date字段存储日期，避免DATE(create_time)）；
3. 禁止对索引列使用CAST()、CONVERT()等类型转换函数。

校验方法：执行EXPLAIN，如果type=ALL，且key=NULL，判定为索引失效。

二、字符串字段未使用前缀索引导致隐式截断（高危）：对长字符串字段（如：VARCHAR(255)），建立前缀索引（如：name(20)），但是在查询条件中：

• 使用了超出前缀长度的精确匹配；
• 对字段使用函数（如：UPPER(name)）；
• 查询值长度远大于前缀长度，导致优化器认为前缀索引“不可靠”。

失效原理：前缀索引仅存储字段的前N个字符，B+树基于这N个字符排序。如果查询条件涉及完整字段值或函数处理，优化器无法保证前缀索引能唯一/准确匹配目标行，因此放弃使用索引。

正反示例：

-- name VARCHAR(255)，已建前缀索引idx_name(name(20))
-- 错误：查询值长度 > 20，优化器可能认为前缀不足以区分，放弃索引
SELECT * FROM users WHERE name = 'ThisIsAVeryLongUsernameThatExceedsTwentyCharacters';

-- 错误：对前缀索引列使用函数
SELECT * FROM users WHERE UPPER(name) = 'ALICE';

-- 正确：查询值长度 ≤ 前缀长度，且无函数
SELECT * FROM users WHERE name = 'AliceSmith1234567890'; -- ≤20字符

-- 更好做法：避免前缀索引，改用哈希列或全文索引
ALTER TABLE users ADD COLUMN name_hash CHAR(64) AS (SHA2(name, 256)) STORED;
CREATE INDEX idx_name_hash ON users(name_hash);
SELECT * FROM users WHERE name_hash = SHA2('LongUsername...', 256);

避坑方法：

1. 慎用前缀索引：仅在字段前缀区分度极高（如：URL前30字符几乎唯一）时使用；
2. 明确前缀长度：通过SELECT COUNT(DISTINCT LEFT(col, N)) / COUNT(*)测试前缀选择性，确保≥95%；
3. 替代方法：
• 对超长字段，我们使用生成列 + 哈希索引（如：SHA2）；
• 全文检索需求，我们使用FULLTEXT索引；
• 避免在前缀索引列上使用任何函数或表达式。

校验方法：

• 执行SHOW CREATE TABLE确认是否为前缀索引；
• EXPLAIN查看是否使用索引；如果key=NULL，且查询值较长，我们高度怀疑前缀索引失效；
• 使用INFORMATION_SCHEMA.STATISTICS检查索引是否为前缀类型。

三、隐式类型转换（高危）：索引列数据类型与查询值类型不匹配，触发MySQL自动类型转换（如：varchar列匹配数字、int列匹配字符串）。

失效原理：MySQL会对索引列执行CAST(col AS 目标类型)转换，本质等同于索引列参与函数操作，导致索引失效。

正反示例：

-- phone为varchar(20)类型，已建索引
-- 错误：数字匹配字符串，触发隐式转换
SELECT * FROM users WHERE phone = 13800138000;

-- user_id为int类型，已建索引
-- ⚠️ 注意：字符串匹配int不一定失效！
SELECT * FROM users WHERE user_id = '10086';

-- 正确：类型严格一致
SELECT * FROM users WHERE phone = '13800138000';
SELECT * FROM users WHERE user_id = 10086;

对于数字类型列匹配字符串数字（如：user_id INT = '10086'），MySQL会将字符串常量转为数字，索引仍有效；但是字符串列匹配数字（如：phone VARCHAR = 13800138000）会触发对列的CAST转换，索引失效。

转换规则：

• 当发生比较时，MySQL会将类型优先级低的一方，向优先级高的一方转换。
• 类型优先级（从高到低）： DATETIME/TIMESTAMP > DOUBLE > BIGINT > INTEGER > VARCHAR/CHAR。
• 因此：
• int_col = '123'：'123'（字符串，优先级低）会向int_col（整型，优先级高）转换，转换发生在常量值上，索引有效。
• varchar_col = 123：varchar_col（字符串，优先级低）会向整型常量转换，这相当于对列做了CAST(varchar_col AS UNSIGNED)，索引失效。

避坑方法：

1. 明确规范：字符串类型查询值必须加单引号，数字类型查询值禁止加引号
2. ORM框架需要配置类型映射校验（如：MyBatis的@Param类型校验）
3. 保持类型一致是唯一安全的选择，即使 int_col = '123' 在当前版本有效，也应避免依赖该行为

校验方法：执行EXPLAIN EXTENDED后，通过SHOW WARNINGS查看优化后的SQL，如果出现CAST(col AS ...)，判定为隐式转换。

四、负向查询（高危）：使用!=/<>、NOT LIKE、NOT BETWEEN、NOT IN等负向运算符。

失效原理：负向查询，会破坏B+树索引的有序性匹配逻辑，导致优化器无法快速定位目标数据，大概率触发全表扫描。

正反示例：

-- 不等于判断
-- 错误：负向查询，全表扫描
SELECT * FROM orders WHERE status != 1;

-- 正确：用范围查询替代（列无NULL值）
SELECT * FROM orders WHERE status < 1 OR status > 1;

-- NOT IN子查询
-- 错误：易失效且NULL值导致结果异常
SELECT * FROM t1 WHERE id NOT IN (SELECT id FROM t2);

-- 正确：LEFT JOIN + IS NULL替代
SELECT t1.* FROM t1
LEFT JOIN t2 ON t1.id = t2.id
WHERE t2.id IS NULL;

-- NOT LIKE
-- 错误：负向模糊查询，全表扫描
SELECT * FROM users WHERE name NOT LIKE '张%';

-- 正确：先正向过滤缩小范围
SELECT * FROM users 
WHERE create_time >= '2024-01-01'
  AND name NOT LIKE '张%';

负向查询本身不必然导致索引失效。如果结合高选择性索引列+范围条件，索引仍可生效。例如：SELECT * FROM orders WHERE id > 10000 AND status != 1（id为主键），先通过id>10000缩小范围，再过滤status。

例外情况：如果查询能够被覆盖索引（Covering Index）完全满足，即使使用负向查询，优化器也可能选择扫描索引而不是全表。因为扫描整个索引（Index Full Scan）通常比扫描整个表（Table Full Scan）要快。

-- 假设存在索引idx_status(status, id)
-- 以下查询虽然用了!=，但是EXPLAIN可能显示type=range, key=idx_status，且 Extra=Using where; Using index
SELECT id, status FROM orders WHERE status != 1;

但是，这通常是不得已的次优选择，改写为正向查询或结合范围条件仍是我们的首选。

避坑方法：

1. 严格禁止在大表（数据量>10万）中单独使用负向查询；
2. 所有负向查询必须改写为正向查询或范围查询；
3. 负向查询必须结合正向范围条件（如：时间、ID）缩小扫描范围。

校验方法：执行EXPLAIN，如果type=ALL，且key=NULL，判定为索引失效。

五、OR连接非索引列条件（高危）：OR运算符两侧的查询条件中，存在无索引的列。

失效原理：当OR连接的条件中，任一侧列无索引，MySQL通常无法使用index_merge优化。此时，优化器可能选择全表扫描，或仅使用有索引的一侧进行过滤（再回表验证另一条件），性能仍较差。为确保高效，应保证OR所有分支均命中索引，或改写为UNION ALL。

正反示例：

-- id已建索引，email无索引
-- 错误：OR一侧无索引，可能导致全表扫描或低效回表
SELECT * FROM users WHERE id = 100 OR email = 'test@example.com';

-- 正确：方法1：两侧列均建索引
ALTER TABLE users ADD INDEX idx_email(email);
SELECT * FROM users WHERE id = 100 OR email = 'test@example.com';

-- 正确：方法2：拆分为UNION ALL（我们推荐，避免冗余索引）
SELECT * FROM users WHERE id = 100
UNION ALL
SELECT * FROM users WHERE email = 'test@example.com';

避坑方法：

1. 禁止在OR两侧使用"索引列+非索引列"的组合；
2. 优先使用UNION ALL拆分查询；
3. 如果我们必须使用OR，需要保证两侧列均有索引且索引选择性良好。

六、相关子查询导致全表扫描（高危）：使用相关子查询（子查询依赖外层查询结果），或子查询嵌套层数≥2层。

失效原理：相关子查询的执行逻辑是"外层表每行数据触发一次子查询"，本质是嵌套循环（时间复杂度O(n²)）。失效的往往不是子查询内的索引，而是整个执行算法（Nested-Loop）导致外层全表扫描。子查询内部的u.id和u.vip_level上的索引可能依然有效，但是无法改变O(n²)的复杂度。

正反示例：

-- 错误：相关子查询（外层orders表每行触发一次子查询）
SELECT * FROM orders o 
WHERE EXISTS (
  SELECT 1 FROM users u 
  WHERE u.id = o.user_id 
    AND u.vip_level > 3
);

-- 正确：改写为JOIN（时间复杂度O(n)）
SELECT o.* FROM orders o
INNER JOIN users u ON o.user_id = u.id
WHERE u.vip_level > 3;

-- 非相关子查询改写
SELECT * FROM orders 
WHERE user_id IN (SELECT id FROM users WHERE vip_level > 3);
-- 改写为：
SELECT o.* FROM orders o
INNER JOIN (SELECT id FROM users WHERE vip_level > 3) u 
  ON o.user_id = u.id;

避坑方法：

1. 禁止使用相关子查询，所有子查询必须改写为JOIN；
2. 子查询嵌套层数禁止超过1层，复杂查询优先使用临时表+索引；
3. 禁止在WHERE子句中使用IN (SELECT ...)，优先用JOIN替代。

七、LIKE以通配符%开头（高危）：模糊查询使用LIKE '%keyword'（后缀匹配）或LIKE '%keyword%'（前后匹配）。

失效原理：B+树索引按"前缀有序"组织数据，%开头会导致索引无法定位起始匹配位置，只能全表扫描。

正反示例：

-- name为varchar(50)类型，已建索引
-- 错误：后缀匹配（%开头）
SELECT * FROM products WHERE name LIKE '%手机';

-- 错误：前后匹配（双%）
SELECT * FROM products WHERE name LIKE '%智能%';

-- 正确：前缀匹配（无前置%）
SELECT * FROM products WHERE name LIKE '华为%';

避坑方法：

1. 业务允许时优先使用前缀匹配（keyword%）；
2. 需要前后匹配时：短文本使用FULLTEXT全文索引，长文本接入Elasticsearch；
3. 禁止在大表（数据量>10万）中使用%keyword或%keyword%。

八、JOIN关联字段类型/字符集不匹配（高危）：多表JOIN时，关联字段类型不一致（如：INT vs BIGINT）、字符集/排序规则不同（如：utf8 vs utf8mb4）。

失效原理：触发隐式转换，等同于索引列加函数，导致索引失效。

正反示例：

-- 错误：t1.user_id INT，t2.user_id BIGINT，JOIN时触发转换
SELECT * FROM t1 JOIN t2 ON t1.user_id = t2.user_id;

-- 错误：字符集不一致（t1.name utf8，t2.name utf8mb4）
SELECT * FROM t1 JOIN t2 ON t1.name = t2.name;

-- 正确：关联字段类型与字符集严格一致
ALTER TABLE t2 MODIFY user_id INT;
ALTER TABLE t2 CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

避坑方法：

1. 所有JOIN关联字段必须保证数据类型、长度、字符集、排序规则完全一致；
2. 在数据库设计阶段统一字段规范，避免跨服务表结构不一致；
3. 使用DDL审核工具拦截类型不匹配的JOIN字段。

校验方法：

• 执行EXPLAIN FORMAT=JSON，查看是否有"implicit_cast"提示；
• 使用SHOW WARNINGS检查是否有隐式转换警告。

九、JSON列索引失效（高危）：对JSON类型索引列使用JSON_EXTRACT()、->、->>等函数（如：WHERE JSON_EXTRACT(info, '$.age') = 20）。

失效原理：函数操作破坏索引有序性，且JSON列无法直接建普通索引（除非使用生成列或MySQL 8.0+函数索引）。

正反示例：

-- info为JSON类型
-- 错误：直接对JSON列使用函数，索引失效
SELECT * FROM users WHERE JSON_EXTRACT(info, '$.age') = 20;
SELECT * FROM users WHERE info->>'$.name' = '张三';

-- 正确（MySQL 5.7+）：生成列+索引
ALTER TABLE users ADD COLUMN age INT AS (JSON_UNQUOTE(JSON_EXTRACT(info, '$.age'))) STORED;
CREATE INDEX idx_age ON users(age);
SELECT * FROM users WHERE age = 20;

-- 正确（MySQL 8.0+）：函数索引
CREATE INDEX idx_create_date ON users((DATE(create_time)));
CREATE INDEX idx_json_name ON users((JSON_UNQUOTE(JSON_EXTRACT(info, '$.name'))));

避坑方法：

1. 禁止直接对JSON字段使用函数查询；
2. MySQL 5.7：使用生成列 + 普通索引；
3. MySQL 8.0+：优先使用函数索引（不需要额外列）；
4. 高频查询字段，我们建议平铺为独立列，避免JSON嵌套查询。

校验方法：EXPLAIN中，如果key=NULL，且查询含JSON函数，基本判定失效。

十、IN后接超大列表（中危）：IN后的常量列表长度超过1000个值，或列表长度不固定（如：动态拼接）。

失效原理：MySQL对IN列表的优化阈值为1000个值，超过阈值后，优化器会判定"遍历索引的成本高于全表扫描"，直接放弃索引。

正反示例：

-- 错误：IN列表长度1001个值，索引失效
SELECT * FROM products WHERE id IN (1,2,3,...,1001);

-- 正确：拆分小批次查询（每批≤500个值）
SELECT * FROM products WHERE id IN (1,2,...,500)
UNION ALL
SELECT * FROM products WHERE id IN (501,...,1000)
UNION ALL
SELECT * FROM products WHERE id IN (1001);

-- 正确：临时表+JOIN（适用于列表长度>2000）
CREATE TEMPORARY TABLE tmp_ids (id INT PRIMARY KEY) ENGINE=InnoDB;
INSERT INTO tmp_ids VALUES (1),(2),...,(1001);
SELECT p.* FROM products p
INNER JOIN tmp_ids t ON p.id = t.id;

避坑方法：

1. IN列表长度严格限制≤500个值，超过则拆分或用临时表；
2. 动态列表优先使用临时表+JOIN，避免拼接超长IN语句；
3. 禁止IN列表长度>1000。

对于动态生成的长IN列表，一个更实战的做法是使用批处理或应用程序层拆分，避免在数据库层面拼接超长SQL。例如，在Java中可以用Lists.partition(idList, 500)进行拆分查询。

十一、联合索引违反最左前缀原则（中危）：联合索引的查询条件未包含最左前缀列，或跳过中间列。

失效原理：联合索引的B+树按"左列优先"排序，跳过最左列会导致索引无法定位，跳过中间列会导致后续列索引失效。

正反示例：

-- 已建联合索引idx_name_city_age(name, city, age)
-- 正确：包含最左前缀，全索引生效
SELECT * FROM users WHERE name = '张三';
SELECT * FROM users WHERE name = '张三' AND city = '北京';

-- 中危：包含最左前缀，跳过中间列（age列索引失效）
SELECT * FROM users WHERE name = '张三' AND age = 25;

-- 错误：未包含最左前缀，全索引失效
SELECT * FROM users WHERE city = '北京' AND age = 25;

避坑方法：

1. 联合索引设计遵循"高频列左置"原则；
2. 编写SQL时，必须包含联合索引的最左前缀列；
3. 禁止在联合索引中间列单独作为查询条件（需要单独建索引）。

校验方法：执行EXPLAIN，查看key_len字段判断索引使用情况。

十二、IS NULL/IS NOT NULL（中危）：对索引列使用IS NULL或IS NOT NULL判断。

失效原理：

• IS NULL：可正常使用索引（B+树中NULL值集中存储，可快速定位）；
• IS NOT NULL：本质是负向查询，如果非空值占比高（如：>70%），优化器会选择全表扫描。

正反示例：

-- name为VARCHAR(50) INDEX NULL
-- 正确：IS NULL 可走索引
SELECT * FROM users WHERE name IS NULL;

-- 中危：IS NOT NULL（非空值占比高时失效）
SELECT * FROM users WHERE name IS NOT NULL;

-- 正确：结合范围条件缩小扫描范围
SELECT * FROM users 
WHERE create_time >= '2024-01-01 00:00:00' 
  AND name IS NOT NULL;

-- 最佳做法：索引列设置NOT NULL约束
ALTER TABLE users MODIFY COLUMN name VARCHAR(50) NOT NULL DEFAULT '';

与负向查询类似，IS NOT NULL在覆盖索引场景下也可能使用索引扫描。更重要的是，在MySQL 8.0中，优化器对IS NOT NULL的处理更加智能。如果该列上有索引且NULL值占比较低，优化器可能会选择使用该索引进行范围扫描（range访问）。

-- MySQL 8.0, name列索引，且NULL值很少
EXPLAIN SELECT * FROM users WHERE name IS NOT NULL;
-- 可能结果: type=range, key=idx_name

避坑方法：

1. 所有索引列强制设置NOT NULL约束，用合理默认值替代NULL；
2. IS NULL本身不失效索引，我们可放心使用；
3. IS NOT NULL必须结合范围条件，或确保NULL值占比>30%；
4. 如果无法避免NULL，我们需要确保NULL值占比显著（如：>10%）。

十三、优化器选错索引（中危）：索引本身有效，但优化器因统计信息过期、成本计算错误，选择了低效索引或全表扫描。

失效原因：MySQL统计信息（如：innodb_stats_persistent）未更新，或查询条件的基数预估错误。

正反示例：

-- 表users有idx_name和idx_age，但统计信息陈旧
-- 错误：优化器选择全表扫描，尽管idx_name更优
SELECT * FROM users WHERE name = '张三';

-- 正确：更新统计信息
ANALYZE TABLE users;

-- 正确（MySQL 8.0+）：使用直方图提升选择性预估
ANALYZE TABLE users UPDATE HISTOGRAM ON name WITH 100 BUCKETS;

避坑方法：

1. 定期（每周/数据变更>10%时）执行ANALYZE TABLE；
2. MySQL 8.0+启用直方图：ANALYZE TABLE ... UPDATE HISTOGRAM；
3. 关键查询可临时使用FORCE INDEX，但是我们需要监控长期效果；
4. 避免在高峰时段，大量DML后立即执行复杂查询（统计信息未刷新）。

关于FORCE INDEX的使用警告：FORCE INDEX是给优化器的强制指令，会一直生效直到索引被删除或查询改变。如果数据分布后续发生变化（例如，name='张三'从1条变成了100万条），我们强制使用idx_name可能从最优变为最差。我们更推荐的做法是：先用FORCE INDEX验证该索引确实更优，然后通过ANALYZE TABLE更新统计信息，让优化器自己“学”会。或者在极端情况下，使用/*+ INDEX(...) */优化器提示（MySQL 5.7+），它的强制性弱于FORCE INDEX。注：此提示应仅作为诊断工具或数据分布极其稳定的情况下的临时方法，长期依赖可能导致性能回退。

数据分布与索引失效：这是一个常被我们忽略的深层原因。索引是否被使用，最终取决于优化器的成本估算。成本估算依赖于innodb_stats_persistent_sample_pages等参数收集的统计信息。一个非常偏斜的数据分布，例如，status字段99%的值都是1，查询WHERE status=1，即使有索引，优化器也会认为“反正几乎要读全表，不如直接全表扫描更快”。解决方法为：

1. 确保统计信息准确：定期ANALYZE TABLE，或在大量DML后手动执行。
2. MySQL 8.0+使用直方图（Histogram）来为等值查询条件提供更精准的数据分布信息。

   ANALYZE TABLE users UPDATE HISTOGRAM ON status WITH 100 BUCKETS;
   

我们强调一下：“索引有效”不等于“索引会被使用”，统计信息是桥梁。

校验方法：

• 使用EXPLAIN ANALYZE（MySQL 8.0+）对比预估行数与实际行数；
• 检查information_schema.OPTIMIZER_TRACE分析决策过程。

十四、ORDER BY/GROUP BY与索引不匹配（中危）：ORDER BY/GROUP BY的字段未包含在索引中，或与索引列顺序不一致。

失效原理：ORDER BY/GROUP BY需要依赖有序数据，如果字段不在索引中，会触发filesort或Using temporary。

正反示例：

-- 已建索引idx_city(city)，未建name索引
-- 错误：ORDER BY字段不在索引中，触发filesort
SELECT * FROM users WHERE city = '北京' ORDER BY name;

-- 正确：构建覆盖索引
ALTER TABLE users ADD INDEX idx_city_name(city, name);
SELECT city, name FROM users WHERE city = '北京' ORDER BY name;

-- 正确：ORDER BY字段与索引顺序一致
SELECT * FROM users WHERE city = '北京' ORDER BY age; -- idx_city_age索引

避坑方法：

1. 将ORDER BY/GROUP BY字段加入联合索引；
2. 联合索引列顺序需要与ORDER BY顺序一致；
3. 禁止ORDER BY/GROUP BY使用函数。

Extra: Using filesort中的“file”容易引起误解。它不一定发生在磁盘上。如果排序数据量小于sort_buffer_size，排序完全在内存中完成，效率尚可。只有当数据量过大时，才会使用磁盘临时文件。关键在于避免不可控的大数据量排序。

十五、DISTINCT未命中覆盖索引（中危）：DISTINCT去重的字段未包含在索引中，导致需要回表查询后再去重。

失效原理：DISTINCT需要基于完整数据去重，如果字段不在索引中，MySQL需要先全表扫描获取数据，再执行去重操作。

正反示例：

-- email无索引
-- 错误：全表扫描+去重
SELECT DISTINCT email FROM users;

-- 正确：构建覆盖索引
ALTER TABLE users ADD INDEX idx_email(email);
SELECT DISTINCT email FROM users;

-- 进阶：联合查询的DISTINCT覆盖索引
ALTER TABLE users ADD INDEX idx_city_gender(city, gender);
SELECT DISTINCT city, gender FROM users;

避坑方法：

1. 所有DISTINCT查询的目标字段必须构建索引；
2. 联合查询的DISTINCT需要构建包含所有去重字段的覆盖索引；
3. 禁止在DISTINCT中使用*。

十六、低选择性字段单独建索引（中危）：对低选择性字段（如：性别、状态、类型，唯一值占比<5%）单独建立索引。

失效原理：低选择性字段的过滤效果极差，优化器会判定"全表扫描比索引扫描更快"，直接放弃索引。

正反示例：

-- gender字段取值为'男'/'女'，唯一值占比50%
-- 错误：单独建索引，低效且易失效
ALTER TABLE users ADD INDEX idx_gender(gender);
SELECT * FROM users WHERE gender = '男'; -- 大概率全表扫描

-- 正确：作为联合索引后缀列
ALTER TABLE users ADD INDEX idx_city_gender(city, gender);
SELECT * FROM users WHERE city = '北京' AND gender = '男';

-- 正确：不建索引（数据量小时可接受）
SELECT * FROM users WHERE gender = '男';

避坑方法：

1. 单独建索引的字段，选择性必须>20%；
2. 选择性<10%的字段，禁止单独建索引，仅作为联合索引后缀；
3. 定期删除低选择性的低效索引。