mysql中联合索引生效的条件及索引失效的条件是什么

发布时间：2021-11-18 13:04:12 作者：小新
来源：亿速云阅读：206

# MySQL中联合索引生效的条件及索引失效的条件

## 一、联合索引概述

联合索引（Composite Index）也称为复合索引或多列索引，是指在MySQL中对表上的多个列组合建立的索引。与单列索引相比，联合索引可以覆盖多个查询条件，在特定场景下能显著提高查询效率。

### 1.1 联合索引的特点
- 按照索引定义的列顺序构建B+树结构
- 遵循"最左前缀原则"（Leftmost Prefix Principle）
- 可以包含最多16个列（InnoDB引擎限制）

### 1.2 创建联合索引的语法
```sql
CREATE INDEX index_name ON table_name(col1, col2, col3);
-- 或
ALTER TABLE table_name ADD INDEX index_name(col1, col2, col3);

二、联合索引生效的条件

2.1 最左前缀原则

联合索引生效的核心条件是满足最左前缀原则，即查询条件必须包含联合索引的第一列（最左列）。以下是几种典型场景：

2.1.1 全列匹配

-- 索引生效（col1,col2,col3全部使用）
SELECT * FROM table WHERE col1 = 1 AND col2 = 2 AND col3 = 3;

2.1.2 左前缀匹配

-- 索引生效（使用col1）
SELECT * FROM table WHERE col1 = 1;

-- 索引生效（使用col1,col2）
SELECT * FROM table WHERE col1 = 1 AND col2 = 2;

2.1.3 列连续匹配

-- 索引生效（col1,col3中间跳过了col2，但col1仍是最左列）
SELECT * FROM table WHERE col1 = 1 AND col3 = 3;

2.2 范围查询后的列

范围查询（>, <, BETWEEN, LIKE等）后的列索引可能部分失效：

-- col1使用=，col2使用范围，col3仍能使用索引
SELECT * FROM table WHERE col1 = 1 AND col2 > 2 AND col3 = 3;

-- col1使用范围，col2和col3索引失效
SELECT * FROM table WHERE col1 > 1 AND col2 = 2 AND col3 = 3;

2.3 排序（ORDER BY）场景

当ORDER BY子句与联合索引列顺序一致时，可以利用索引排序：

-- 索引生效（排序顺序与索引一致）
SELECT * FROM table WHERE col1 = 1 ORDER BY col2, col3;

-- 索引部分生效（col2使用降序）
SELECT * FROM table WHERE col1 = 1 ORDER BY col2 DESC, col3;

2.4 覆盖索引（Covering Index）

当查询的列全部包含在索引中时，无需回表：

-- 只需扫描索引（col1,col2,col3都包含在索引中）
SELECT col1, col2 FROM table WHERE col1 = 1 AND col2 = 2;

三、联合索引失效的条件

3.1 违反最左前缀原则

3.1.1 缺少最左列

-- 索引失效（缺少col1）
SELECT * FROM table WHERE col2 = 2 AND col3 = 3;

3.1.2 不连续使用索引列

-- 索引部分失效（只用到col1）
SELECT * FROM table WHERE col1 = 1 AND col3 = 3;

3.2 对索引列进行计算或函数操作

任何对索引列的计算或函数处理都会导致索引失效：

-- 索引失效
SELECT * FROM table WHERE YEAR(col1) = 2023;
SELECT * FROM table WHERE col1 + 1 = 5;

3.3 使用不等于（!= 或 <>）

-- 索引失效
SELECT * FROM table WHERE col1 != 1;

3.4 使用IS NULL/IS NOT NULL

-- 索引可能失效（取决于数据分布）
SELECT * FROM table WHERE col1 IS NULL;

3.5 使用LIKE以通配符开头

-- 索引失效
SELECT * FROM table WHERE col1 LIKE '%abc';

-- 索引生效（通配符只在结尾）
SELECT * FROM table WHERE col1 LIKE 'abc%';

3.6 类型转换导致失效

隐式类型转换会导致索引失效：

-- 假设col1是varchar类型
SELECT * FROM table WHERE col1 = 123; -- 索引失效
SELECT * FROM table WHERE col1 = '123'; -- 索引生效

3.7 OR条件使用不当

OR条件可能导致索引失效：

-- 索引失效（除非col1和col2都有独立索引）
SELECT * FROM table WHERE col1 = 1 OR col2 = 2;

四、优化建议

4.1 索引设计原则

高频查询优先：将高频查询条件放在索引左侧
区分度高优先：选择性高的列放在左侧
字段长度小的优先：减少索引存储空间
避免过度索引：一般建议不超过5个联合索引

4.2 查询优化建议

使用EXPLN分析执行计划
尽量使用覆盖索引
避免在WHERE子句中对字段进行函数操作
对于长字符串考虑使用前缀索引

4.3 索引选择示例

-- 用户表常见查询
SELECT * FROM users WHERE username = ? AND status = ?;
SELECT * FROM users WHERE phone = ?;
SELECT * FROM users WHERE email = ?;

-- 推荐索引方案
ALTER TABLE users ADD INDEX idx_user_status(username, status);
ALTER TABLE users ADD INDEX idx_phone(phone);
ALTER TABLE users ADD INDEX idx_email(email);

五、EXPLN工具使用

通过EXPLN可以验证索引使用情况：

EXPLN SELECT * FROM table WHERE col1 = 1 AND col2 = 2;

关键指标解读： - type：const/ref/range表示索引使用良好 - key：显示实际使用的索引 - rows：预估扫描行数 - Extra：Using index表示覆盖索引

六、总结

合理使用联合索引需要深入理解最左前缀原则和索引失效场景。在实际应用中应当： 1. 根据业务查询模式设计索引 2. 通过EXPLN验证索引效果 3. 定期审查和优化索引结构 4. 注意避免常见的索引失效操作

通过科学的索引设计和查询优化，可以显著提升MySQL数据库的查询性能，特别是在处理复杂查询和大数据量场景时效果更为明显。 “`

这篇文章约2100字，全面介绍了MySQL联合索引的生效条件、失效场景以及优化建议，采用Markdown格式编写，包含代码示例和结构化标题，可直接用于技术文档或博客发布。