MySQL索引失效原因及解决方案

最新推荐文章于 2025-02-15 17:29:26 发布

原创最新推荐文章于 2025-02-15 17:29:26 发布 · 588 阅读

4 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #数据库

Database 专栏收录该内容

98 篇文章

订阅专栏

1. 字符串字段未使用前缀索引

• 原因：字符串字段没有使用索引的前缀。
• 解决方案：在创建索引时使用字符串字段的前缀。
• 错误示例： SELECT * FROM table WHERE name LIKE '%keyword%';
• 正确示例： SELECT * FROM table WHERE name LIKE 'keyword%';

2. OR操作符使用多列索引

• 原因：使用OR操作符连接多列查询条件，导致索引失效。
• 解决方案：将多列查询条件拆分为单列查询，分别使用索引。
• 错误示例： SELECT * FROM table WHERE col1 = 'value1' OR col2 = 'value2';
• 正确示例： SELECT * FROM table WHERE col1 = 'value1'; 或 SELECT * FROM table WHERE col2 = 'value2';

3. 索引列上使用了函数

• 原因：在索引列上使用函数，导致无法使用索引。
• 解决方案：避免在索引列上使用函数，或者使用函数表达式索引。
• 错误示例： SELECT * FROM table WHERE YEAR(date_column) = 2021;
• 正确示例： SELECT * FROM table WHERE date_column >= '2021-01-01' AND date_column < '2022-01-01';

4. 隐式数据类型转换

• 原因：查询条件中的数据类型与索引字段的数据类型不一致，导致隐式数据类型转换，从而失效索引。
• 解决方案：确保查询条件的数据类型与索引字段的数据类型一致。
• 错误示例： SELECT * FROM table WHERE id = '1'; （id字段为整型，查询条件为字符串）
• 正确示例： SELECT * FROM table WHERE id = 1;

5. 范围查询使用不等于操作符

• 原因：范围查询使用不等于操作符（!= 或 <>），导致无法使用索引。
• 解决方案：尽量避免使用不等于操作符进行范围查询。
• 错误示例： SELECT * FROM table WHERE age != 30;
• 正确示例： SELECT * FROM table WHERE age > 30 OR age < 30;

6. 复合索引字段顺序不正确

• 原因：查询条件中的字段顺序与索引字段顺序不一致，导致无法使用索引。
• 解决方案：调整查询条件中字段的顺序，与索引字段顺序一致。
• 错误示例： SELECT * FROM table WHERE name = 'John' AND age = 30; （索引：(age, name)）
• 正确示例： SELECT * FROM table WHERE age = 30 AND name = 'John';

7. 索引列上使用了 NOT 操作符

• 原因：在索引列上使用了NOT操作符，导致无法使用索引。
• 解决方案：避免在索引列上使用NOT操作符，可以使用其他方式实现相同的逻辑。
• 错误示例： SELECT * FROM table WHERE NOT id = 1;
• 正确示例： SELECT * FROM table WHERE id <> 1;

8. 索引列上使用了 IS NULL 或 IS NOT NULL

• 原因：在索引列上使用了IS NULL或IS NOT NULL操作符，导致无法使用索引。
• 解决方案：避免在索引列上使用IS NULL或IS NOT NULL操作符，可以使用其他方式实现相同的逻辑。
• 错误示例： SELECT * FROM table WHERE id IS NULL;
• 正确示例： SELECT * FROM table WHERE id = NULL; 或 SELECT * FROM table WHERE id <=> NULL;

9. 索引列上使用了运算符

• 原因：在索引列上使用了运算符，导致无法使用索引。
• 解决方案：避免在索引列上使用运算符，可以使用其他方式实现相同的逻辑。
• 错误示例： SELECT * FROM table WHERE id + 1 = 2;
• 正确示例： SELECT * FROM table WHERE id = 1;