印刻数据库

外观

MariaDB 索引失效场景 ​

索引失效的概念 ​

索引失效是指查询优化器在执行查询时，没有使用预期的索引，而是选择了全表扫描或其他低效的执行方式。索引失效会导致查询性能下降，增加系统资源消耗。

了解索引失效的常见场景，对于优化查询性能至关重要。本文将详细介绍MariaDB中索引失效的各种场景，以及如何避免索引失效。

常见索引失效场景 ​

1. 不满足最左前缀原则 ​

场景描述：对于复合索引，查询条件中没有包含索引的最左列，导致索引无法被使用。

示例：

sql
-- 创建复合索引
CREATE INDEX idx_user_status_created ON users(status, created_at);

-- 有效：使用了最左列status
SELECT * FROM users WHERE status = 'active';

-- 有效：使用了最左列status和第二列created_at
SELECT * FROM users WHERE status = 'active' AND created_at > '2024-01-01';

-- 无效：没有使用最左列status，索引失效
SELECT * FROM users WHERE created_at > '2024-01-01';

避免方法：

  • 确保查询条件中包含复合索引的最左列
  • 根据查询需求调整索引列顺序
  • 为频繁使用的单列查询创建单独的索引

2. 使用了函数或表达式 ​

场景描述：在索引列上使用了函数或表达式，导致索引无法被使用。

示例：

sql
-- 创建索引
CREATE INDEX idx_user_created ON users(created_at);

-- 无效：在索引列上使用了函数
SELECT * FROM users WHERE YEAR(created_at) = 2024;

-- 无效：在索引列上使用了表达式
SELECT * FROM users WHERE created_at + INTERVAL 7 DAY > NOW();

-- 有效：将函数或表达式移到右侧
SELECT * FROM users WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';

避免方法：

  • 避免在索引列上使用函数或表达式
  • 将函数或表达式移到查询条件的右侧
  • 考虑创建基于函数的索引（MariaDB 10.0+支持）

3. 类型转换 ​

场景描述：查询条件中的值与索引列的数据类型不匹配，导致类型转换，索引无法被使用。

示例：

sql
-- 创建索引，id为INT类型
CREATE INDEX idx_user_id ON users(id);

-- 无效：字符串类型与INT类型不匹配，导致类型转换
SELECT * FROM users WHERE id = '123';

-- 有效：使用正确的数据类型
SELECT * FROM users WHERE id = 123;

避免方法：

  • 确保查询条件中的值与索引列的数据类型匹配
  • 避免隐式类型转换
  • 在应用程序中进行类型转换，而不是在数据库中

4. 使用了!=或<>操作符 ​

场景描述：使用了不等于操作符（!=或<>），导致索引无法被充分利用。

示例：

sql
-- 创建索引
CREATE INDEX idx_user_status ON users(status);

-- 索引可能失效：使用了!=操作符
SELECT * FROM users WHERE status != 'active';

-- 索引可能失效：使用了<>操作符
SELECT * FROM users WHERE status <> 'active';

避免方法：

  • 考虑使用其他查询方式，如范围查询
  • 对于低频查询，可以接受全表扫描
  • 对于高频查询，考虑调整业务逻辑

5. 使用了IS NULL或IS NOT NULL ​

场景描述：使用了IS NULL或IS NOT NULL操作符，导致索引无法被充分利用。

示例：

sql
-- 创建索引
CREATE INDEX idx_user_email ON users(email);

-- 索引可能失效：使用了IS NULL
SELECT * FROM users WHERE email IS NULL;

-- 索引可能失效：使用了IS NOT NULL
SELECT * FROM users WHERE email IS NOT NULL;

避免方法：

  • 考虑将NULL值转换为默认值
  • 对于频繁查询NULL值的列，考虑创建专门的索引
  • 调整业务逻辑，减少对NULL值的查询

6. 使用了LIKE操作符的前缀模糊匹配 ​

场景描述：使用了LIKE操作符的前缀模糊匹配（如%keyword），导致索引无法被使用。

示例：

sql
-- 创建索引
CREATE INDEX idx_user_name ON users(name);

-- 无效：前缀模糊匹配，索引失效
SELECT * FROM users WHERE name LIKE '%John';

-- 无效：前后都模糊匹配，索引失效
SELECT * FROM users WHERE name LIKE '%John%';

-- 有效：后缀模糊匹配，索引有效
SELECT * FROM users WHERE name LIKE 'John%';

避免方法：

  • 避免使用前缀模糊匹配
  • 考虑使用全文索引进行模糊搜索
  • 调整查询需求，使用后缀模糊匹配

7. 使用了OR操作符 ​

场景描述：使用了OR操作符，且OR两侧的列没有都创建索引，导致索引无法被使用。

示例：

sql
-- 创建索引
CREATE INDEX idx_user_id ON users(id);
CREATE INDEX idx_user_status ON users(status);

-- 有效：OR两侧的列都有索引
SELECT * FROM users WHERE id = 123 OR status = 'active';

-- 无效：OR两侧的列只有一侧有索引
CREATE INDEX idx_user_id ON users(id);
SELECT * FROM users WHERE id = 123 OR name = 'John';

避免方法：

  • 确保OR两侧的列都有索引
  • 考虑使用UNION替代OR
  • 调整查询逻辑，减少OR的使用

8. 数据分布不均匀 ​

场景描述：索引列的数据分布不均匀，导致优化器认为使用索引的成本高于全表扫描。

示例：

sql
-- 创建索引，status列只有两个值：active和inactive
-- 其中active占95%，inactive占5%
CREATE INDEX idx_user_status ON users(status);

-- 索引可能失效：查询active状态的用户，占比95%
SELECT * FROM users WHERE status = 'active';

-- 索引有效：查询inactive状态的用户，占比5%
SELECT * FROM users WHERE status = 'inactive';

避免方法：

  • 对于数据分布不均匀的列，避免单独创建索引
  • 考虑使用复合索引，提高索引的选择性
  • 强制使用索引（不推荐，因为优化器通常更智能）

9. 索引列参与了计算 ​

场景描述：索引列参与了计算，导致索引无法被使用。

示例：

sql
-- 创建索引
CREATE INDEX idx_user_score ON users(score);

-- 无效：索引列参与了计算
SELECT * FROM users WHERE score * 2 > 100;

-- 有效：将计算移到右侧
SELECT * FROM users WHERE score > 50;

避免方法：

  • 避免索引列参与计算
  • 将计算移到查询条件的右侧
  • 考虑创建生成列（Generated Columns）并为其创建索引

10. 优化器认为全表扫描更快 ​

场景描述：优化器根据统计信息判断，全表扫描比使用索引更快，导致索引失效。

示例：

sql
-- 创建索引
CREATE INDEX idx_user_created ON users(created_at);

-- 索引可能失效：查询结果占表行数的大部分
SELECT * FROM users WHERE created_at > '2020-01-01';

避免方法：

  • 定期更新统计信息
  • 确保表和索引的统计信息准确
  • 对于大表，考虑分区表
  • 调整查询逻辑，减少返回的行数

如何诊断索引失效 ​

1. 使用EXPLAIN命令 ​

方法：使用EXPLAIN命令查看查询执行计划，判断是否使用了预期的索引。

示例：

sql
EXPLAIN SELECT * FROM users WHERE status = 'active';

关键字段解释：

  • id：查询的唯一标识符
  • select_type：查询类型
  • table：查询的表
  • type：访问类型（ALL表示全表扫描，ref表示使用索引）
  • possible_keys：可能使用的索引
  • key：实际使用的索引
  • key_len：使用的索引长度
  • ref：索引的引用方式
  • rows：估计扫描的行数
  • Extra：额外信息

2. 使用EXPLAIN EXTENDED ​

方法：使用EXPLAIN EXTENDED命令查看更详细的执行计划。

示例：

sql
EXPLAIN EXTENDED SELECT * FROM users WHERE status = 'active';

额外功能：可以使用SHOW WARNINGS查看优化器的详细优化过程。

3. 使用Performance Schema ​

方法：使用Performance Schema监控索引访问情况。

示例：

sql
-- 启用Performance Schema
UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME LIKE '%index%';

-- 查看索引访问情况
SELECT * FROM performance_schema.table_io_waits_summary_by_index_usage WHERE object_name = 'users';

4. 分析慢查询日志 ​

方法：启用慢查询日志，分析慢查询的执行计划。

示例：

bash
# 启用慢查询日志
vi /etc/mysql/my.cnf
# 添加以下配置
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 1

# 重启MariaDB
systemctl restart mariadb

# 分析慢查询日志
mysqldumpslow -s t /var/log/mysql/mysql-slow.log
pt-query-digest /var/log/mysql/mysql-slow.log

避免索引失效的最佳实践 ​

1. 遵循最左前缀原则 ​

  • 对于复合索引，确保查询条件中包含索引的最左列
  • 根据查询频率和选择性合理安排索引列顺序
  • 为频繁使用的查询创建专门的复合索引

2. 避免在索引列上使用函数或表达式 ​

  • 避免在WHERE子句中对索引列使用函数
  • 将函数或表达式移到查询条件的右侧
  • 考虑创建基于函数的索引（MariaDB 10.0+支持）

3. 确保数据类型匹配 ​

  • 确保查询条件中的值与索引列的数据类型匹配
  • 避免隐式类型转换
  • 在应用程序中进行类型转换

4. 合理使用操作符 ​

  • 对于等于查询，使用=操作符
  • 对于范围查询，使用BETWEEN、>、<等操作符
  • 避免使用!=、<>、IS NULL、IS NOT NULL等操作符
  • 考虑使用UNION替代OR

5. 优化LIKE查询 ​

  • 避免使用前缀模糊匹配（%keyword）
  • 对于后缀模糊匹配（keyword%），可以使用索引
  • 对于复杂的模糊搜索，考虑使用全文索引

6. 定期更新统计信息 ​

  • 定期更新表和索引的统计信息
  • 确保优化器获得准确的统计信息
  • 使用ANALYZE TABLE命令更新统计信息

7. 合理设计索引 ​

  • 根据查询需求设计合适的索引
  • 避免过度索引
  • 定期清理未使用的索引
  • 考虑使用覆盖索引，减少回表操作

8. 监控索引使用情况 ​

  • 定期监控索引的使用情况
  • 识别未使用或低效的索引
  • 根据监控结果调整索引设计

常见问题（FAQ） ​

Q1: 为什么使用了索引，查询还是很慢？ ​

A: 可能的原因包括：

  • 索引失效，优化器选择了全表扫描
  • 索引设计不合理，无法覆盖查询需求
  • 查询返回的行数过多
  • 服务器资源不足（CPU、内存、I/O）
  • 锁等待或死锁

Q2: 如何强制使用索引？ ​

A: 可以使用FORCE INDEX或USE INDEX提示优化器使用特定的索引，但不推荐频繁使用，因为优化器通常更智能。

sql
-- 强制使用索引
SELECT * FROM users FORCE INDEX (idx_user_status) WHERE status = 'active';

-- 建议使用索引
SELECT * FROM users USE INDEX (idx_user_status) WHERE status = 'active';

Q3: 复合索引的列顺序应该如何确定？ ​

A: 复合索引的列顺序应该考虑以下因素：

  • 查询频率：最常用的查询列放在最左侧
  • 选择性：选择性高的列放在最左侧
  • 范围查询：将范围查询列放在最右侧
  • 数据类型：较小的数据类型放在前面（可选）

Q4: 如何处理数据分布不均匀的列？ ​

A: 对于数据分布不均匀的列，可以考虑以下方法：

  • 不单独为其创建索引，而是作为复合索引的一部分
  • 考虑使用分区表
  • 调整业务逻辑，减少对该列的查询
  • 对于低频查询，可以接受全表扫描

Q5: 为什么OR操作会导致索引失效？ ​

A: 当使用OR操作符时，如果OR两侧的列没有都创建索引，优化器无法使用索引，因为它需要扫描多个索引，成本可能高于全表扫描。确保OR两侧的列都有索引，或者考虑使用UNION替代OR。

Q6: 如何优化IS NULL查询？ ​

A: 优化IS NULL查询的方法包括：

  • 将NULL值转换为默认值
  • 为NULL值频繁查询的列创建专门的索引
  • 调整业务逻辑，减少对NULL值的查询
  • 考虑使用NOT EXISTS替代IS NULL

Q7: 如何监控索引的使用情况？ ​

A: 可以通过以下方式监控索引的使用情况：

  • 使用INFORMATION_SCHEMA.INDEX_STATISTICS查看索引使用情况
  • 使用Performance Schema监控索引访问
  • 分析慢查询日志，查看索引使用情况
  • 使用第三方工具，如pt-index-usage

Q8: 如何避免索引失效？ ​

A: 避免索引失效的方法包括：

  • 遵循最左前缀原则
  • 避免在索引列上使用函数或表达式
  • 确保数据类型匹配
  • 合理使用操作符
  • 优化LIKE查询
  • 定期更新统计信息
  • 合理设计索引
  • 监控索引使用情况

总结 ​

索引失效是导致查询性能下降的常见原因之一。了解索引失效的常见场景，对于优化查询性能至关重要。

DBA应该：

  • 熟悉各种索引失效场景
  • 定期使用EXPLAIN命令分析查询执行计划
  • 监控索引的使用情况
  • 根据查询需求合理设计索引
  • 定期更新统计信息
  • 与开发团队密切合作，优化查询语句

通过避免索引失效，可以充分发挥MariaDB的性能潜力，提高查询效率，减少系统资源消耗，为业务提供高效、稳定的数据库服务。