mysql中聯合索引生效的條件及索引失效的條件是什么

# 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 索引設計原則

1. 高頻查詢優先：將高頻查詢條件放在索引左側
2. 區分度高優先：選擇性高的列放在左側
3. 字段長度小的優先：減少索引存儲空間
4. 避免過度索引：一般建議不超過5個聯合索引

4.2 查詢優化建議

1. 使用EXPLN分析執行計劃
2. 盡量使用覆蓋索引
3. 避免在WHERE子句中對字段進行函數操作
4. 對于長字符串考慮使用前綴索引

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格式編寫，包含代碼示例和結構化標題，可直接用于技術文檔或博客發布。