1. 聯(lián)合索引

MySQL 聯(lián)合索引（也稱復合索引）是在表的多個列上共同創(chuàng)建的一個索引，能極大的優(yōu)化多條件查詢的性能

它并非多個單列索引的簡單疊加，而是一個將多列值組合在一起，并按照特定順序進行排序和存儲的 B+ 樹結(jié)構(gòu)

假設(shè)創(chuàng)建了一個聯(lián)合索引 (A, B, C)，聯(lián)合索引設(shè)計速查表：

2. 最左側(cè)原則

最左側(cè)原則：在使用聯(lián)合索引時，查詢條件必須從索引的最左邊一列開始匹配，并且匹配過程不能跳過中間的列

這是理解和使用聯(lián)合索引的基石，聯(lián)合索引（a,b,c）的底層 B+ 樹是按照（a,b,c）的順序進行排序的，所以必須遵循該原則

接下來，我們通過一個案例來理解最左側(cè)原則，運行以下命令創(chuàng)建一個測試表：

CREATE TABLE `users` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主鍵',
  `name` varchar(30) DEFAULT NULL COMMENT '姓名',
  `age` tinyint(4) DEFAULT NULL COMMENT '年齡',
  `gender` char(1) DEFAULT NULL COMMENT '性別',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用戶表';
INSERT INTO `users` (`name`, `age`, `gender`) VALUES ('liang', 18, '男');
INSERT INTO `users` (`name`, `age`, `gender`) VALUES ('zhang', 20, '女');
INSERT INTO `users` (`name`, `age`, `gender`) VALUES ('chen', 25, '男');
INSERT INTO `users` (`name`, `age`, `gender`) VALUES ('sun', 30, '女');

運行以下命令分析 SQL 語句，可以發(fā)現(xiàn)是全表掃描數(shù)據(jù)

explain select * from users where name = 'liang' and age = 18 and gender = '男';

我們準備使用聯(lián)合索引來優(yōu)化這個 SQL，創(chuàng)建聯(lián)合索引語法格式：

• index_name：索引名稱，為索引指定的名稱
• table_name：指定表名，說明是給哪張表創(chuàng)建索引

create index index_name on table_name (column1, column2, ...);

所以，我們可以運行以下命令創(chuàng)建聯(lián)合索引：

-- 相當于構(gòu)建了一個按照 name > age > gender 順序排序的數(shù)據(jù)結(jié)構(gòu)
create index idx_name_age_gender on users (name, age, gender);

聯(lián)合索引創(chuàng)建之后，那么它在什么時候有效呢 ？需要認真思考一下這個問題

有效查詢：

• where name = 1（匹配最左列，有效）
• where name = 1 and age = 2（匹配前兩列，有效）
• where name = 1 and age = 2 and gender = 3（完全匹配，有效）

無效或部分失效查詢：

• where age = 2（跳過最左列 name，完全失效）
• where name = 1 and gender = 2（跳過中間列 age，僅 name 生效，gender 失效）

-- 聯(lián)合索引有效
explain select * from users where name = 'liang';
explain select * from users where name = 'liang' and age = 18;
explain select * from users where name = 'liang' and age = 18 and gender = '男';
-- 聯(lián)合索引無效
explain select * from users where age = 18;
-- 僅 name 生效，gender 失效
explain select * from users where name = 'liang' and gender = '男';

底層原理：為什么要遵守這個原則 ？理解這個原則的關(guān)鍵在于理解 B+ 樹的存儲結(jié)構(gòu)

聯(lián)合索引在底層并不簡單的三個字段并列，而是層級排序的。（可以把它想象為一本電話簿或字典）

• 先 a 排序：就像電話簿先按 “姓氏” 排序
• a 相同，再按 b 排序：姓氏相同的人，再按名字排序
• a 和 b 都相同，再按 c 排序

為什么跳過 a 就不行 ？

• 因為 b 列的數(shù)據(jù)在全表中并不是有序的，它只在 a 相同的小組內(nèi)是有序的
• 如果直接查 b，數(shù)據(jù)庫就像在一本亂序的書中找字，只能全表掃描，此時聯(lián)合索引根本就不會生效

為什么跳過 b 查 c 就不行 ？

• 同理，c 只有在 a 和 b 都確定的情況下才是有序的
• 如果只給了 a 和 c，數(shù)據(jù)庫可以使用 a 快速定位到一大塊區(qū)域，但在這塊區(qū)域里，c 是亂序的

3. 范圍查詢打斷

前面我們說的都是等值查詢，但如果是范圍查詢和模糊查詢呢 ？這兩個場景容易踩坑，也是面試和實戰(zhàn)中的高頻考點

范圍查詢打斷（范圍查詢后面的列索引失效）：

• 在聯(lián)合索引中，一旦某一列使用了范圍查詢（>、<、between）,該列右側(cè)的所有列都無法再利用索引進行快速查找

為什么會這樣 ？（底層原理）

這是因為聯(lián)合索引的 B+ 樹是按照從左到右的順序構(gòu)建的：

• a 列：全局有序
• b 列：只有在 a 相同的情況下，才是有序的
• c 列：只有在 a 和 b 都相同的情況下，才是有序的

當你使用 a = 1 and b > 10 時：

數(shù)據(jù)庫找到了 a = 1 的數(shù)據(jù)塊，然后在這個塊里找 b > 10 的數(shù)據(jù)。因為 b 是范圍查找，它匹配到了多個 b 的值

在這些不同的 b 值下，c 列的數(shù)據(jù)是雜亂無章的（因為 c 只有在 b 固定時才有序），既然 c 是亂序的，索引樹就無法利用二分查找來定位 c，只能遍歷掃描

實戰(zhàn)案例演示，假設(shè)聯(lián)合索引為 idx(a, b, c)：

注：通常我們將等值查詢的列放在前面，范圍查詢的列放在最后，這樣能最大化利用索引

4. 模糊查詢（like）

核心規(guī)則：模糊查詢是否走索引，完全取決于通配符 % 的位置

假設(shè)索引為 idx(name) 或聯(lián)合索引的最左列：

聯(lián)合索引中的模糊查詢行為遵循 “最左前綴原則” 和 “范圍打斷原則” 的混合邏輯

場景 A：前綴匹配（視為等值）

• where a = 1 and b like '梁%' and c = 3
• 結(jié)果：a，b，c 全部生效
• 原因：like '梁%' 在索引中會被視為一個確定的范圍起點，它不會打斷后續(xù)列的有序性（類似于等值查詢）

場景 B：后綴/包含匹配（視為全表掃描）

• where a = 1 and b like '%梁' and c = 3
• 結(jié)果：只有 a 生效，b 和 c 失效
• 原因：b 的查詢無法利用索引，相當于在 a = 1 的結(jié)果集中做全表掃描

場景 C：前綴匹配作為范圍（打斷后續(xù)）

• 雖然 like '梁%' 能走索引，但在某些嚴格定義下，它被視作一種范圍
• 不過，在 MySQL 的聯(lián)合索引中，like '梁%' 不會打斷后續(xù)列（只要它是前綴匹配），MySQL 優(yōu)化器做了處理
• 特例：如果是 like 'ab%c' （中間有通配符），則視為范圍/失效，后續(xù)列無法使用索引

為了方便記憶，可以參考這張表：

5. 實際項目示例

假設(shè)正在開發(fā)一個電商后臺，有一張訂單表 orders，數(shù)據(jù)量很大（百萬級）

CREATE TABLE `orders` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主鍵ID',
  `order_no` varchar(64) NOT NULL COMMENT '訂單編號',
  `user_id` bigint(20) NOT NULL COMMENT '用戶ID',
  `merchant_id` bigint(20) NOT NULL COMMENT '商家ID',
  `status` varchar(20) NOT NULL COMMENT '訂單狀態(tài)：UNPAID, PAID, SHIPPED, FINISHED',
  `amount` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '訂單金額',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創(chuàng)建時間',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_order_no` (`order_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='電商訂單表';

創(chuàng)建一個存儲過程，用于生成測試數(shù)據(jù)

DELIMITER $$
CREATE PROCEDURE batch_insert_orders()
BEGIN
  DECLARE i INT DEFAULT 1;
  DECLARE v_user_id BIGINT;
  DECLARE v_merchant_id BIGINT;
  DECLARE v_status VARCHAR(20);
  -- 開啟事務(wù)，提高插入速度
  START TRANSACTION;
  WHILE i <= 10000 DO
    -- 隨機生成用戶ID (800-999)
    SET v_user_id = FLOOR(800 + RAND() * 200);
    -- 隨機生成商家ID (1001-1005)
    SET v_merchant_id = FLOOR(1001 + RAND() * 5);
    -- 隨機狀態(tài)
    SET v_status = ELT(FLOOR(1 + RAND() * 4), 'UNPAID', 'PAID', 'SHIPPED', 'FINISHED');
    INSERT INTO `orders` (`order_no`, `user_id`, `merchant_id`, `status`, `amount`, `create_time`)
    VALUES (
      CONCAT('ORD_', DATE_FORMAT(NOW(), '%Y%m%d'), '_', LPAD(i, 6, '0')),
      v_user_id,
      v_merchant_id,
      v_status,
      ROUND(RAND() * 1000, 2),
      DATE_ADD(NOW(), INTERVAL -FLOOR(RAND() * 30) DAY) -- 隨機過去30天內(nèi)的時間
    );
    SET i = i + 1;
  END WHILE;
  COMMIT;
END$$
DELIMITER ;
-- 調(diào)用存儲過程執(zhí)行插入
CALL batch_insert_orders();

場景一：后臺訂單列表查詢（最典型）

業(yè)務(wù)需求：運營人員經(jīng)常在后臺查詢 某個商家 在 特定日期范圍內(nèi) 的 待發(fā)貨 訂單

-- 當前我們還沒有加索引，現(xiàn)在查詢是全表掃描
SELECT * FROM orders
	WHERE merchant_id = 1001
	AND status = 'UNPAID'
	AND create_time > '2023-10-01';

索引設(shè)計策略：我們需要建立聯(lián)合索引 (merchant_id, status, create_time)

• merchant_id（等值查詢）：這是最左列，用來先鎖定是哪個商家的數(shù)據(jù)，過濾性最強
• status（等值查詢）：在商家的數(shù)據(jù)里，再篩選出特定狀態(tài)的訂單
• create_time（范圍查詢）：最后處理時間范圍。根據(jù)“最左前綴原則”和“范圍截斷規(guī)則”，必須放在聯(lián)合索引的最后面

-- 創(chuàng)建聯(lián)合索引之后，再執(zhí)行上面命令分析 SQL 語句可以看到聯(lián)合索引已被使用
create index idx_merchant_status_time on orders (merchant_id, status, create_time);

場景二：用戶訂單列表查詢（排序優(yōu)化）

業(yè)務(wù)需求：C 端用戶查看 “我的訂單”，通常按時間倒序排列

SELECT * FROM orders WHERE user_id = 888 ORDER BY create_time DESC LIMIT 20;

索引設(shè)計策略：建立聯(lián)合索引 (user_id, create_time)

• 如果不把 create_time 加到索引里，會先查出該用戶的所有訂單，然后在內(nèi)存中進行排序（Filesort），這在數(shù)據(jù)量比較大時非常慢
• 建立了聯(lián)合索引后，索引樹本身就是按照 user_id 分組，組內(nèi)按照 create_time 排序的。數(shù)據(jù)庫可以直接利用索引的有序性，從后往前讀取20條數(shù)據(jù)，效率極高

create index idx_user_time on orders (user_id, create_time);

場景三：覆蓋索引（無需回表，極致性能）

業(yè)務(wù)需求：在訂單列表頁，只需要展示 “訂單號” 和 “當前狀態(tài)”，不需要展示收獲地址等大字段的詳情

SELECT order_no, status FROM orders WHERE user_id = 888 AND create_time > '2023-01-01';

索引設(shè)計策略：建立聯(lián)合索引 (user_id, create_time, order_no, status)

• 這個索引包含了 where 條件用到的列，也包含了 select 查詢的列
• MySQL 引擎發(fā)現(xiàn)索引樹上已經(jīng)有所需的所有數(shù)據(jù)，完全不需要回表（不需要去查主鍵索引拿數(shù)據(jù)），直接在索引樹上遍歷返回結(jié)果。這是查詢速度的天花板

create index idx_uid_time_no_status on orders (user_id, create_time, order_no, status);

6. 覆蓋索引的理解

在場景三中使用了覆蓋索引，你可能不太理解什么是覆蓋索引，我們來研究一下這個問題

首先，需要先了解一下數(shù)據(jù)庫中的原理，在 MySQL 的 InnoDB 引擎里：

• 主鍵索引（聚簇索引）：它的葉子節(jié)點存儲了完整的行數(shù)據(jù)
• 普通索引（二級索引）：它的葉子節(jié)點只存儲了索引列的值 + 主鍵的值

執(zhí)行查詢時，如果使用二級索引，通常會發(fā)生兩件事：

• 查二級索引：先在二級索引樹上找到符合條件的記錄，拿到主鍵 ID
• 回表：拿著這個主鍵 ID，再去聚簇索引（主鍵索引）樹上查找完整的行數(shù)據(jù)

“回表” 是一次額外的、昂貴的 I/O 操作，而覆蓋索引的精髓就在于：

• SQL 語句中 SELECT 的所有字段，恰好都包含在使用的這個二級索引中，無需回表拿著主鍵 ID 查詢完整行數(shù)據(jù)

結(jié)合 orders 表來理解，回到場景三，建立的索引是：(user_id, create_time, order_no, status)

SELECT order_no, status FROM orders WHERE user_id = 888 AND create_time > '2023-01-01';

為什么這個索引是覆蓋索引 ？先來拆解一下

• where 條件字段：user_id 和 create_time。這兩個字段是索引的最左兩列，數(shù)據(jù)庫會利用它們快速定位數(shù)據(jù)
• select 查詢字段：order_no 和 status。這兩個字段也包含我們建立的聯(lián)合索引中

執(zhí)行過程（使用覆蓋索引）：

• 數(shù)據(jù)庫引擎在索引樹上找到 user_id = 888 AND create_time > '2023-01-01' 的所有節(jié)點
• 它發(fā)現(xiàn)這些索引節(jié)點上已經(jīng)直接存好了 order_no 和 status 的值
• 任務(wù)完成，直接把 order_no 和 status 返回。整個過程沒有去查主鍵索引，也就沒有發(fā)生回表

對比一下，如果不用覆蓋索引會怎么樣 ？

假設(shè)我們的索引只是 (user_id, create_time)，那么執(zhí)行過程需要回表：

• 數(shù)據(jù)庫引擎在索引樹上找到 user_id = 888 AND create_time > '2023-01-01' 的所有節(jié)點
• 但是這個索引樹上只有 user_id、create_time 和 id，它沒有 order_no 和 status
• 于是，數(shù)據(jù)庫必須拿著找到的 id，回到主鍵索引樹上去查找完整的行數(shù)據(jù)，才能拿到 order_no 和 status
• 這個 “回到主鍵索引樹查找” 的過程，就是 “回表”

總結(jié)：覆蓋索引不是一種特殊的索引類型，而是一種高效的查詢狀態(tài)

• 核心思想：你需要的，索引里全都有
• 最大好處：避免回表，極大的減少了磁盤的 I/O，讓查詢速度飛快
• 如何判斷：當你用 explain 分析 SQL 時，如果 extra 列顯示為 Using index，那就說明用上了覆蓋索引

到此這篇關(guān)于MySQL 聯(lián)合索引的文章就介紹到這了,更多相關(guān)mysql 聯(lián)合索引內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論