mysql 联合索引生效的条件及索引失效的条件

MySQL 联合索引，是指在表中建立多个字段的索引，以便在查询时能够提高查询效率。但是在实际使用中，我们也会遇到联合索引失效的情况，因此需要了解联合索引生效的条件及失效的条件。

联合索引生效的条件

1. 联合索引的顺序要与查询条件一致。例如，如果联合索引包含 A、B 两个字段，而查询语句中先按 B 来筛选，那么索引将无效，需要创建一个包含 B、A 的联合索引。

2. 查询条件包含联合索引的第一个字段。例如，如果索引是 A、B、C，则查询语句中必须包含 A 条件，才能利用索引。

3. 查询条件包含联合索引的一部分。如果索引是 A、B、C，则查询语句中包含 A、B 或 A、B、C 此类条件，才能利用索引。

以下是联合索引生效的 SQL 示例：

CREATE TABLE users (
  id int(11) NOT NULL AUTO_INCREMENT,
  name varchar(255) NOT NULL,
  age int(11) NOT NULL,
  gender varchar(255) NOT NULL,
  PRIMARY KEY (id),
  INDEX idx_name_age_gender (name, age, gender)
);

-- 查询语句可以使用联合索引
SELECT * FROM users WHERE name='Tom' AND age=20;

-- 查询语句可以使用联合索引
SELECT * FROM users WHERE name='Tom';

-- 查询语句可以使用联合索引
SELECT * FROM users WHERE name='Tom' AND age=20 AND gender='male';

索引失效的条件

1. 查询条件中涉及索引列的函数或运算。例如，查询语句中包含 WHERE YEAR(create_time) = 2021，因为 YEAR 函数会把 create_time 列的值先计算出来，再跟 2021 进行比较，所以无法使用索引。

2. 查询条件中涉及到索引列的范围查询，如 <、<=、>、>=、BETWEEN、IN、NOT IN 等。例如，查询语句中的 WHERE age > 18，因为 age 的值是范围值，所以索引失效。

3. 查询条件中存在 OR 连接的条件。例如，查询语句中的 WHERE name='Tom' OR age=20 OR gender='male'，因为 OR 连接会把多个条件拆分成多个子查询，所以无法使用索引。

以下是索引失效的 SQL 示例：

-- name 字段上使用了函数 YEAR ，索引失效
SELECT * FROM users WHERE YEAR(`create_time`) = 2021;

-- age 栏位超出了范围，索引失效
SELECT * FROM users WHERE age > 18;

-- 使用了 OR 连接，索引失效
SELECT * FROM users WHERE name='Tom' OR age=20 OR gender='male';

需要注意的是，索引在某些情况下可能会失效，造成性能问题，但是通过优化查询语句或是重新设计索引，我们可以有效地解决这些问题，并保证索引的有效性。因此，在实际使用中，需要结合自身实际情况来对索引进行优化和调整。