当前位置： 首页 > article >正文

mysql的索引在什么情况下会失效？

article 2024/12/4 15:20:27

文章目录

      • 前言
      • 1. 创建测试表
      • 2. 使用 `EXPLAIN` 分析查询
        • 2.1 正确使用索引的示例
        • 2.2 错误使用索引的示例
        • 2.3 使用 `OR` 导致索引失效
        • 2.4 使用 `LIKE` 导致索引失效
        • 2.5 联合索引的列顺序不正确
        • 2.6 `NULL` 值与索引的关系
        • 2.7 使用 `DISTINCT` 导致索引失效
        • 2.8 `JOIN` 查询中的索引失效
        • 2.9 使用 `NOT LIKE` 进行匹配时索引失效
        • 2.10 使用 `BETWEEN` 和日期范围查询时索引失效
        • 2.11 更新操作影响索引
      • 3. 总结

前言

在 MySQL 中，索引是优化查询性能的关键工具，它可以大幅提高查询速度。然而，在某些特定情况下，索引可能会失效，从而导致查询性能的显著下降。本文将通过一个简单的表和一些常见的查询示例，演示 MySQL 索引失效的常见场景，并使用 EXPLAIN 关键字分析查询执行计划，帮助您深入理解何时索引失效。

1. 创建测试表

首先，我们创建一个测试表，包含常见的字段类型，并为部分字段添加索引。

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    email VARCHAR(100),
    created_at DATETIME,
    INDEX idx_name_age (name, age),
    INDEX idx_created_at (created_at)
);
  • id：主键，自动生成索引。
  • name、age：我们为这两个字段创建了一个复合索引 idx_name_age。
  • created_at：创建了索引。
  • email：没有索引。

接下来，我们插入一些数据：

INSERT INTO users (name, age, email, created_at) VALUES
('Alice', 25, 'alice@example.com', '2024-01-01'),
('Bob', 30, 'bob@example.com', '2024-01-02'),
('Charlie', 35, 'charlie@example.com', '2024-01-03');

2. 使用 EXPLAIN 分析查询

EXPLAIN 是一个非常有用的 SQL 语句，用于分析查询的执行计划，它可以告诉我们 MySQL 是否使用了索引，使用了哪个索引，以及查询的其他细节。

2.1 正确使用索引的示例

首先，我们来看一个能够充分利用索引的查询：

EXPLAIN SELECT * FROM users WHERE name = 'Alice' AND age = 25;

输出结果（简化示例）可能如下：

| id | select_type | table | type  | possible_keys     | key            | key_len | ref  | rows | Extra       |
|----|-------------|-------|-------|-------------------|----------------|---------|------|------|-------------|
| 1  | SIMPLE      | users | range | idx_name_age      | idx_name_age   | 10      | NULL | 1    | Using where |
  • possible_keys：列出了可能的索引。
  • key：实际使用的索引。
  • type：查询的类型，这里是 range，表示 MySQL 使用了索引范围扫描。
  • rows：MySQL 估计扫描的行数。

在这个查询中，idx_name_age 索引被正确地使用，因为查询中同时使用了 name 和 age 字段，符合复合索引的顺序。

2.2 错误使用索引的示例

接下来，我们来看一个索引失效的例子，查询中使用了函数，导致 MySQL 无法使用索引：

EXPLAIN SELECT * FROM users WHERE YEAR(created_at) = 2024;

输出结果（简化示例）可能如下：

| id | select_type | table | type   | possible_keys | key  | key_len | ref  | rows | Extra         |
|----|-------------|-------|--------|---------------|------|---------|------|------|---------------|
| 1  | SIMPLE      | users | ALL    | NULL          | NULL | NULL    | NULL | 3    | Using where   |
  • type：这里显示为 ALL，意味着 MySQL 进行了全表扫描。
  • key：没有使用索引。
  • possible_keys：没有可用的索引。
  • Extra：Using where，表示 MySQL 在执行查询时进行了过滤，但没有使用任何索引。

问题分析：在这个查询中，我们使用了 YEAR(created_at) 函数，这使得索引失效。MySQL 无法利用 created_at 列上的索引，因为它必须首先计算 YEAR(created_at) 才能过滤数据。

解决方法：可以避免在查询条件中使用函数，或者考虑创建一个生成列，将 YEAR(created_at) 的结果存储在一个新的列中，并为该列创建索引。

2.3 使用 OR 导致索引失效

接下来，我们看看一个 OR 条件导致索引失效的例子：

EXPLAIN SELECT * FROM users WHERE name = 'Alice' OR age = 30;

输出结果（简化示例）可能如下：

| id | select_type | table | type  | possible_keys     | key  | key_len | ref  | rows | Extra         |
|----|-------------|-------|-------|-------------------|------|---------|------|------|---------------|
| 1  | SIMPLE      | users | ALL   | idx_name_age      | NULL | NULL    | NULL | 3    | Using where   |
  • type：ALL，MySQL 执行了全表扫描。
  • key：没有使用索引。

问题分析：当查询使用 OR 时，如果不同的条件可以使用不同的索引，MySQL 可能会放弃使用索引，转而执行全表扫描。在这个例子中，name = 'Alice' 可以使用 idx_name_age 索引，但由于 OR 条件包含 age = 30，它无法有效地结合两个索引，因此 MySQL 选择了全表扫描。

解决方法：可以将 OR 查询拆分为两个独立的查询，或者使用 UNION 来避免索引失效。

2.4 使用 LIKE 导致索引失效

我们来看一个使用 LIKE 导致索引失效的例子：

EXPLAIN SELECT * FROM users WHERE name LIKE '%Alice%';

输出结果（简化示例）可能如下：

| id | select_type | table | type  | possible_keys     | key  | key_len | ref  | rows | Extra         |
|----|-------------|-------|-------|-------------------|------|---------|------|------|---------------|
| 1  | SIMPLE      | users | ALL   | idx_name_age      | NULL | NULL    | NULL | 3    | Using where   |
  • type：ALL，全表扫描。
  • key：没有使用索引。

问题分析：当 LIKE 查询以 % 开头时，MySQL 无法利用索引。因为 % 通配符导致 MySQL 必须从字符串的开头到结尾进行匹配，这无法通过索引加速。

解决方法：避免在 LIKE 查询中以 % 开头进行匹配。如果需要支持模糊查询，可以考虑使用全文索引（FULLTEXT）。

2.5 联合索引的列顺序不正确

如果查询的条件列顺序与联合索引的定义顺序不一致，可能会导致索引失效。

EXPLAIN SELECT * FROM users WHERE age = 30 AND name = 'Bob';

输出结果（简化示例）可能如下：

| id | select_type | table | type  | possible_keys     | key            | key_len | ref  | rows | Extra       |
|----|-------------|-------|-------|-------------------|----------------|---------|------|------|-------------|
| 1  | SIMPLE      | users | range | idx_name_age      | idx_name_age   | 10      | NULL | 1    | Using where |

问题分析：在这个查询中，虽然 age 和 name 都存在索引，但由于查询条件顺序不符合 idx_name_age 联合索引的定义顺序，MySQL 可能无法完全利用索引，从而导致查询效率下降。

解决方法：尽量确保查询条件的列顺序与联合索引的定义顺序一致，或者创建合适的索引来优化查询。

2.6 NULL 值与索引的关系

在 MySQL 中，索引不能有效地处理包含 NULL 值的列。特别是在涉及到 IS NULL 或 IS NOT NULL 查询时，索引可能会失效。

示例：

EXPLAIN SELECT * FROM users WHERE email IS NULL;

输出结果（简化示例）可能如下：

| id | select_type | table | type  | possible_keys     | key  | key_len | ref  | rows | Extra         |
|----|-------------|-------|-------|-------------------|------|---------|------|------|---------------|
| 1  | SIMPLE      | users | ALL   | NULL              | NULL | NULL    | NULL | 3    | Using where   |

问题分析：在此查询中，email 列上的索引没有被使用，因为 MySQL 无法有效地在包含 NULL 值的列上使用索引。

解决方法：通常，建议避免将 NULL 值用于查询条件。如果必须使用 NULL，可以考虑调整表结构，使用 DEFAULT 值代替 NULL，或者为含有 NULL 值的列单独创建索引。

2.7 使用 DISTINCT 导致索引失效

有时候，使用 DISTINCT 关键字会导致索引失效。尤其是当 DISTINCT 被应用到多个列时，MySQL 可能会选择进行全表扫描而非使用索引。

示例：

EXPLAIN SELECT DISTINCT name FROM users;

输出结果（简化示例）可能如下：

| id | select_type | table | type   | possible_keys     | key  | key_len | ref  | rows | Extra         |
|----|-------------|-------|--------|-------------------|------|---------|------|------|---------------|
| 1  | SIMPLE      | users | ALL    | idx_name_age      | NULL | NULL    | NULL | 3    | Using distinct|

问题分析：尽管我们在 name 列上有索引，但由于查询中使用了 DISTINCT，MySQL 可能会放弃使用索引，转而执行全表扫描。这是因为 DISTINCT 需要比较多列的唯一性，MySQL 选择了全表扫描以便更好地处理重复的数据。

解决方法：尽量避免使用 DISTINCT，特别是在包含多个列时。如果必须使用 DISTINCT，可以考虑通过其他查询逻辑优化查询，或者使用 GROUP BY 替代。

2.8 JOIN 查询中的索引失效

在多表连接（JOIN）的查询中，索引也可能因为连接条件的不同导致失效。例如，如果 JOIN 操作没有使用连接字段上的索引，或者连接字段的数据类型不匹配，可能会导致索引失效。

示例：

EXPLAIN SELECT u.id, o.order_id FROM users u JOIN orders o ON u.id = o.user_id WHERE u.name = 'Alice';

输出结果（简化示例）可能如下：

| id | select_type | table | type  | possible_keys     | key  | key_len | ref  | rows | Extra         |
|----|-------------|-------|-------|-------------------|------|---------|------|------|---------------|
| 1  | SIMPLE      | u     | ref   | PRIMARY           | PRIMARY | 4     | const| 1    | Using where   |
| 1  | SIMPLE      | o     | ALL   | NULL              | NULL | NULL    | NULL | 3    | Using where   |

问题分析：在这个查询中，users 表的 id 字段使用了索引，而 orders 表的 user_id 字段没有索引。因此，orders 表上的连接字段没有有效利用索引，导致 MySQL 选择全表扫描。

解决方法：为了优化查询，可以确保连接字段（如 orders.user_id）上也有索引，或者根据查询的实际需求调整表的设计和索引。

2.9 使用 NOT LIKE 进行匹配时索引失效

虽然 LIKE 在匹配时以 % 开头会导致索引失效，但使用 NOT LIKE 时也可能会导致索引失效，特别是在大型表的情况下。

示例：

EXPLAIN SELECT * FROM users WHERE name NOT LIKE 'A%';

输出结果（简化示例）可能如下：

| id | select_type | table | type  | possible_keys     | key  | key_len | ref  | rows | Extra         |
|----|-------------|-------|-------|-------------------|------|---------|------|------|---------------|
| 1  | SIMPLE      | users | ALL   | idx_name_age      | NULL | NULL    | NULL | 3    | Using where   |

问题分析：当使用 NOT LIKE 进行字符串匹配时，MySQL 可能会放弃使用索引。由于 NOT LIKE 的匹配规则复杂，MySQL 不容易通过索引来优化此查询。

解决方法：避免使用 NOT LIKE 查询，如果必须使用，可以考虑将查询条件反转或者通过其他方式优化查询。

2.10 使用 BETWEEN 和日期范围查询时索引失效

对于日期范围查询，尤其是使用 BETWEEN 时，如果查询的日期格式或时间单位不一致，索引可能会失效。

示例：

EXPLAIN SELECT * FROM users WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31';

输出结果（简化示例）可能如下：

| id | select_type | table | type  | possible_keys     | key  | key_len | ref  | rows | Extra         |
|----|-------------|-------|-------|-------------------|------|---------|------|------|---------------|
| 1  | SIMPLE      | users | ALL   | idx_name_age      | NULL | NULL    | NULL | 3    | Using where   |

问题分析：尽管 created_at 列上有索引，但是如果查询日期格式不正确，或时间部分被省略，可能导致索引失效。MySQL 无法识别日期范围并优化查询，导致全表扫描。

解决方法：确保日期格式和时间单位一致，避免使用模糊的日期范围。也可以考虑使用合适的日期类型或者时间戳字段来存储时间。

2.11 更新操作影响索引

当执行 UPDATE 操作时，尤其是修改了索引列的值，MySQL 可能会失去原有的索引效果，导致更新操作执行效率低下。

示例：

EXPLAIN UPDATE users SET name = 'Alice' WHERE age = 30;

输出结果（简化示例）可能如下：

| id | select_type | table | type  | possible_keys     | key  | key_len | ref  | rows | Extra         |
|----|-------------|-------|-------|-------------------|------|---------|------|------|---------------|
| 1  | SIMPLE      | users | ALL   | idx_name_age      | NULL | NULL    | NULL | 3    | Using where   |

问题分析：当 UPDATE 操作涉及到索引列时，MySQL 会重新计算索引，并且可能因为更新数据而导致索引失效或重新构建。

解决方法：优化 UPDATE 查询，避免频繁修改索引列，或者尽量将修改操作集中到不影响索引的列上。

3. 总结

通过上述示例，我们演示了 MySQL 中索引失效的几种常见情况，并使用 EXPLAIN 分析了每种情况的执行计划。索引失效可能发生在以下场景：

  • 使用函数、运算符或表达式操作索引列。
  • 使用 OR 连接多个条件。
  • 使用 LIKE 查询时以 % 开头。
  • 联合索引的列顺序不正确。
  • 数据类型不匹配。

为避免索引失效，我们应尽量避免这些情况，或通过重构查询和设计适当的索引策略来提升查询性能。


http://www.kler.cn/a/421435.html

相关文章：

  • spring boot mapper测试类优化
  • Meta-Llama-3-8B-Instruct 模型的混合精度训练显存需求：AdamW优化器（中英双语）
  • KAN-Transfomer——基于新型神经网络KAN的时间序列预测
  • 前端：localstorage, session
  • Ollama是什么
  • 【Linux】应用层协议—HTTP
  • Idea 2024.3 突然出现点击run 运行没有反应，且没有任何提示。
  • 【数据事务】.NET开源 ORM 框架 SqlSugar 系列
  • openEuler 22.03 使用cephadm安装部署ceph集群
  • Go快速入门
  • JAVA设计模式，动态代理模式
  • 【Java基础面试题011】什么是Java中的自动装箱和拆箱？
  • 基于Java Springboot高校社团微信小程序
  • Nginx负载均衡综合实验
  • 【开源】A059-基于SpringBoot的社区养老服务系统的设计与实现
  • flutter Owner和Binding学习
  • 计费结算系统的架构设计思路
  • SpringBoot+MyBatis整合ClickHouse实践
  • Robot Screw Theory (Product of Exponentials)机器人螺旋理论（指数积）
  • 鸿蒙Next学习-webview原生与JS交互通信
  • 你听说过MIPS吗？它和ARM有何区别？
  • 2023年第十四届蓝桥杯Scratch国赛真题—推箱子
  • 如何解决 java.rmi.AlreadyBoundException: 已绑定异常问题？亲测有效的解决方法！
  • [C++设计模式] 为什么需要设计模式？
  • C++入门一
  • P3916 图的遍历(Tarjan缩点和反向建边）