mysql 联合索引失效

基础概念

MySQL中的联合索引（Composite Index）是指在一个索引中包含两个或多个列。联合索引可以提高多列查询的效率，因为它允许数据库引擎通过索引快速定位到满足多个条件的记录。

优势

提高查询效率：对于多列查询条件，联合索引可以减少磁盘I/O操作，提高查询速度。
减少索引数量：通过一个联合索引可以覆盖多个查询条件，减少数据库中索引的数量，从而节省存储空间并提高写操作的性能。

类型

联合索引可以是升序或降序排列，具体取决于创建索引时指定的顺序。

应用场景

联合索引适用于以下场景：

多列查询条件：当查询条件涉及多个列时，使用联合索引可以提高查询效率。
范围查询：对于包含范围查询的SQL语句，联合索引可以显著提高性能。

联合索引失效的原因及解决方法

1. 查询条件顺序不匹配

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

解决方法：确保查询条件的顺序与联合索引的列顺序一致。

示例：

-- 创建联合索引
CREATE INDEX idx_name_age ON users(name, age);

-- 查询条件顺序不匹配
SELECT * FROM users WHERE age = 25 AND name = 'Alice'; -- 索引可能失效

-- 正确的查询条件顺序
SELECT * FROM users WHERE name = 'Alice' AND age = 25; -- 索引有效

2. 使用了不等于（<> 或 !=）操作符

原因：使用不等于操作符会导致MySQL无法使用联合索引。

解决方法：尽量避免使用不等于操作符，改用其他方式实现相同的功能。

示例：

-- 创建联合索引
CREATE INDEX idx_name_age ON users(name, age);

-- 使用不等于操作符
SELECT * FROM users WHERE name != 'Alice' AND age = 25; -- 索引失效

-- 改用其他方式
SELECT * FROM users WHERE name = 'Bob' AND age = 25; -- 索引有效

3. 使用了函数或表达式

原因：在查询条件中使用函数或表达式会导致MySQL无法使用联合索引。

解决方法：避免在查询条件中使用函数或表达式。

示例：

-- 创建联合索引
CREATE INDEX idx_name_age ON users(name, age);

-- 使用函数
SELECT * FROM users WHERE UPPER(name) = 'ALICE' AND age = 25; -- 索引失效

-- 避免使用函数
SELECT * FROM users WHERE name = 'Alice' AND age = 25; -- 索引有效

4. 数据类型不匹配

原因：查询条件中的数据类型与索引列的数据类型不匹配，可能导致索引失效。

解决方法：确保查询条件中的数据类型与索引列的数据类型一致。

示例：

-- 创建联合索引
CREATE INDEX idx_name_age ON users(name VARCHAR(50), age INT);

-- 数据类型不匹配
SELECT * FROM users WHERE name = 123 AND age = 25; -- 索引失效

-- 数据类型匹配
SELECT * FROM users WHERE name = '123' AND age = 25; -- 索引有效