在MySQL中，使用前綴索引（Prefix Index）是一種優(yōu)化查詢性能的策略，特別是在處理長字符串字段時。通過僅對字段的一部分創(chuàng)建索引，可以減少索引的大小，從而加快查詢速度并節(jié)省磁盤空間。

MySQL 前綴索引（依據(jù)字段前 N 個字符創(chuàng)建索引）

一、概念

前綴索引：對字符串類型字段，不取完整字段，只截取前若干個字符建立索引。適用場景：CHAR、VARCHAR、TEXT 等長文本字段，減少索引體積、提升索引效率。

二、基本語法

-- 格式：CREATE INDEX 索引名 ON 表名(字段(截取長度));
CREATE INDEX 索引名 ON 表名(字段名(N));

三、實戰(zhàn)案例

1. 準備測試表

CREATE TABLE student (
    sid INT PRIMARY KEY AUTO_INCREMENT,
    sname VARCHAR(50),
    address VARCHAR(200)  -- 地址字段較長
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

2. 對姓名字段前 3 個字符建前綴索引

-- 截取 sname 前3位字符創(chuàng)建索引
CREATE INDEX idx_name_pre3 ON student(sname(3));

3. 對長地址字段前 10 個字符建前綴索引

CREATE INDEX idx_addr_pre10 ON student(address(10));

4. 查看索引

SHOW INDEX FROM student;

結果中會顯示索引長度為設定的截取位數(shù)。

四、使用規(guī)則 & 生效場景

1. 索引能命中的情況

前綴匹配查詢（右模糊），和索引截取規(guī)則一致：

-- 正常走前綴索引：從開頭匹配
SELECT * FROM student WHERE sname LIKE '張%';
SELECT * FROM student WHERE address LIKE '北京市%';

2. 索引失效場景

1. 左模糊 / 兩端模糊

SELECT * FROM student WHERE sname LIKE '%三';    -- 失效
SELECT * FROM student WHERE sname LIKE '%李%';  -- 失效

1. 查詢需要用到字段完整內容，無法僅靠前綴區(qū)分數(shù)據(jù)

五、如何選擇合適的截取長度（考點）

目標：區(qū)分度盡可能高，接近完整字段的查詢效果。

1. 計算區(qū)分度公式

區(qū)分度 = 不同前綴值數(shù)量 / 總數(shù)據(jù)行數(shù)區(qū)分度越接近 1，效果越好。

2. 實操計算示例

-- 1. 統(tǒng)計整列不重復值數(shù)量
SELECT COUNT(DISTINCT sname) FROM student;
-- 2. 依次測試前1、2、3...位字符的不重復數(shù)量
SELECT COUNT(DISTINCT LEFT(sname,1)) FROM student;
SELECT COUNT(DISTINCT LEFT(sname,2)) FROM student;
SELECT COUNT(DISTINCT LEFT(sname,3)) FROM student;

選取區(qū)分度趨于穩(wěn)定的最小長度，節(jié)約空間又保證效率。

六、前綴索引優(yōu)缺點

優(yōu)點

1. 大幅減小索引文件體積，降低磁盤占用
2. 索引加載更快，內存消耗更少
3. 針對長字符串，查詢速度明顯優(yōu)于全字段索引

缺點

無法使用覆蓋索引因為索引只存前綴字符，無法拿到完整字段值，查詢一定會回表。

排序、分組無法使用該前綴索引

-- 無法走前綴索引排序
SELECT * FROM student ORDER BY sname;

左模糊查詢直接失效

七、修改、刪除前綴索引

-- 刪除前綴索引
DROP INDEX idx_name_pre3 ON student;
-- 用 ALTER 方式創(chuàng)建前綴索引
ALTER TABLE student ADD INDEX idx_addr_pre5(address(5));

八、考試簡答背誦版

1. 前綴索引定義對字符串字段截取前 N 個字符建立的索引，常用于長文本字段，縮減索引大小。
2. 使用特點僅對 ** 右模糊（前綴匹配）** 查詢生效，左模糊、排序、覆蓋索引均不支持。
3. 長度選擇原則通過計算前綴字符的區(qū)分度，選擇區(qū)分度足夠高的最小截取長度。

九、補充限制

1. 數(shù)值型、日期型字段不能創(chuàng)建前綴索引；
2. InnoDB 中，前綴長度有上限，超長截取會被限制；
3. 聯(lián)合索引也可對其中字符串字段設置前綴：

-- 聯(lián)合索引 + 前綴
CREATE INDEX idx_class_name ON student(classid, sname(3));

給出例子，如何依據(jù)前綴創(chuàng)建b樹

結合 MySQL 前綴索引 + B + 樹（InnoDB 默認索引結構），用實例講清：截取字段前綴后，數(shù)據(jù)如何存入 B + 樹、查詢過程、生效 / 失效場景，附可視化結構。

一、前置環(huán)境與數(shù)據(jù)

1. 測試表 & 數(shù)據(jù)

CREATE TABLE student (
    sid INT PRIMARY KEY AUTO_INCREMENT,
    sname VARCHAR(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 插入測試數(shù)據(jù)
INSERT INTO student(sname) 
VALUES 
('張三'),('張三豐'),('張小花'),('李四'),('李磊'),('王五');

2. 創(chuàng)建前綴索引（核心）

截取 sname 前 2 個字符 建立前綴索引：

-- 對姓名字段前2位字符創(chuàng)建前綴索引
CREATE INDEX idx_sname_pre2 ON student(sname(2));

說明：InnoDB 所有索引底層都是 B + 樹，該前綴索引同樣基于 B + 樹 存儲。

二、前綴規(guī)則梳理

截取規(guī)則：只取字符串開頭前 2 個字符

表格

按字符編碼升序排序后，索引排序序列：張三(1)、張三(2)、張小(3)、李四(4)、李磊(5)、王五(6)

三、前綴索引在 B + 樹 中的存儲結構

InnoDB 二級索引通用格式：葉子節(jié)點 = 截取的前綴字符 + 主鍵值非葉子節(jié)點 = 前綴分界字符（僅用于引路，不存完整數(shù)據(jù)）

1. B + 樹整體結構（簡化版）

                【根節(jié)點（非葉子）】
             張三        李四
          ↙        ↘
【葉子組1】                【葉子組2】
(張三,1) (張三,2) (張小,3)  (李四,4) (李磊,5) (王五,6)

結構說明

1. 非葉子節(jié)點（上層）只存儲前綴分界值（張三、李四），用來快速劃分區(qū)間、指引查找，不存儲主鍵。
2. 葉子節(jié)點（最底層）有序存放 (前綴字符, 主鍵sid)，是索引真實數(shù)據(jù)；所有葉子節(jié)點通過雙向鏈表串聯(lián)，保持有序。
3. 整棵樹完全遵循 B + 樹 特性：只有葉子存數(shù)據(jù)、層級少、磁盤 IO 低。

2. 葉子節(jié)點實際存儲內容

("張三",1) 、("張三",2) 、("張小",3) 、("李四",4) 、("李磊",5) 、("王五",6)

四、查詢案例：前綴索引 + B + 樹 執(zhí)行流程

案例 1：前綴匹配（索引生效，走 B + 樹）

執(zhí)行右模糊查詢（開頭匹配前綴，最常用場景）

SELECT * FROM student WHERE sname LIKE '張%';

完整執(zhí)行步驟

1. 解析條件：匹配以 “張” 開頭的姓名，優(yōu)先使用 idx_sname_pre2 前綴索引；
2. 訪問 B + 樹根節(jié)點，對比分界值，定位到左側葉子區(qū)間；
3. 在葉子節(jié)點中遍歷所有前綴為 張三、張小 的記錄，拿到對應主鍵：1、2、3；
4. 拿著主鍵去 ** 聚簇索引（主鍵 B + 樹）** 回表，查詢整行數(shù)據(jù)；
5. 返回最終結果。

優(yōu)勢：沒有全表掃描，依靠 B + 樹二分定位，查詢效率高。

案例 2：左模糊（前綴索引失效，全表掃描）

SELECT * FROM student WHERE sname LIKE '%三';

原因

前綴索引只按字符串開頭字符構建 B + 樹，無法匹配尾部字符；數(shù)據(jù)庫無法使用該索引，放棄 B + 樹檢索，直接全表掃描。

案例 3：完整等值查詢（依然可走前綴 B + 樹）

SELECT * FROM student WHERE sname = '張三';

流程：通過前綴 張三 在 B + 樹找到對應主鍵，回表校驗完整姓名，正常使用索引。

五、關鍵特性 & 考點（考試必背）

1. 前綴 B + 樹 和 普通完整字段 B + 樹 區(qū)別

1. 存儲內容不同
   • 普通索引：葉子存 完整字段值 + 主鍵
   • 前綴索引：葉子存 字段前 N 個字符 + 主鍵
2. 索引體積前綴索引字符更少，B + 樹整體更小，占用磁盤、內存更低。
3. 功能限制
   • 前綴索引 無法實現(xiàn)覆蓋索引（索引內只有前綴，沒有完整字段）；
   • 無法利用該索引做 ORDER BY sname 排序（排序依賴完整字符）。

2. 為什么長字符串推薦前綴 B + 樹？

長文本（地址、備注、長昵稱）如果建完整索引，B + 樹節(jié)點存儲的數(shù)據(jù)量大，樹層級變高、IO 變多；截取合理前綴后，B + 樹更緊湊、層級更低，查詢更快。

3. 截取長度選擇（結合 B + 樹理解）

截取長度太短 → 前綴重復多 → B + 樹葉子節(jié)點大量重復值 → 區(qū)分度低，索引效果差；截取長度太長 → 失去 “縮減索引體積” 的意義。

原則：選擇區(qū)分度足夠高的最小前綴長度構建 B + 樹。

六、考試簡答題標準作答

對字符串字段截取前 N 個字符創(chuàng)建前綴索引，底層依舊采用B + 樹結構存儲：

1. 將截取后的前綴字符按編碼排序，與對應主鍵共同存入 B + 樹葉子節(jié)點；
2. 上層非葉子節(jié)點僅存儲前綴分界值，用于檢索引路；
3. 僅 ** 前綴匹配（右模糊）** 查詢可利用該 B + 樹快速定位數(shù)據(jù)，左模糊、排序、覆蓋索引均無法使用該索引。

七、補充 SQL 驗證

查看索引，確認是前綴索引：

SHOW INDEX FROM student;

輸出中會標記索引長度為 2，代表基于前 2 位字符構建 B + 樹索引。

刪除前綴索引：

DROP INDEX idx_sname_pre2 ON student;

在這個例子中，如何通過姓名列查找到張三豐這個人，給出查找樹到數(shù)據(jù)的過程

結合上文中sname 前 2 位前綴索引（idx_sname_pre2） + B + 樹結構，一步步拆解查找 張三豐 的完整流程，包含樹檢索、回表全過程。

前置回顧

• 表與索引表 student，主鍵 sid，對 sname 前 2 個字符建前綴索引。數(shù)據(jù)清單：| sid | sname | 截取前 2 位前綴 ||-----|--------|--------------|| 1 | 張三 | 張三 || 2 | 張三豐 | 張三 || 3 | 張小花 | 張小 || 4 | 李四 | 李四 || 5 | 李磊 | 李磊 || 6 | 王五 | 王五 |
• 前綴索引 B + 樹 簡化結構

                根節(jié)點(非葉子)
             張三        李四
          ↙        ↘
葉子節(jié)點組1          葉子節(jié)點組2
(張三,1) (張三,2) (張小,3)  (李四,4) (李磊,5) (王五,6)

葉子節(jié)點存儲格式：(前綴字符，主鍵 sid)

1. 執(zhí)行 SQL

SELECT * FROM student WHERE sname = '張三豐';

一、完整查找步驟（從 B + 樹到最終數(shù)據(jù)）

步驟 1：解析查詢條件，確定使用前綴索引

查詢目標完整姓名 張三豐，優(yōu)先使用 sname(2) 前綴索引。截取目標字符串前 2 位：張三豐 → 前綴 = 張三。

步驟 2：訪問 B + 樹根節(jié)點，二分匹配區(qū)間

1. 拿前綴 張三 和根節(jié)點的分界關鍵字對比；
2. 匹配到左區(qū)間，進入左側葉子節(jié)點組。

步驟 3：遍歷當前葉子節(jié)點，匹配前綴

在葉子組 1 中依次讀取索引項：

1. (張三,1)：前綴匹配，取出主鍵 sid=1；
2. (張三,2)：前綴匹配，取出主鍵 sid=2；
3. (張小,3)：前綴不匹配，停止當前分支檢索。

此時得到候選主鍵集合：[1, 2]

步驟 4：根據(jù)主鍵回表（查詢聚簇索引）

前綴索引只存前綴 + 主鍵，沒有完整姓名，必須回表：

1. 拿著 sid=1、sid=2 去主鍵聚簇索引（另一棵 B + 樹）查找整行數(shù)據(jù)；
2. 取出兩條記錄的完整 sname：
   • sid=1 → 張三
   • sid=2 → 張三豐

步驟 5：過濾校驗，返回最終結果

用完整字段和查詢條件 '張三豐' 精準比對：

• sid=1：張三 → 不匹配，舍棄
• sid=2：張三豐 → 完全匹配，保留

步驟 6：輸出該行所有字段數(shù)據(jù)

最終返回：sid=2，sname=張三豐 整行記錄。

二、流程總結（考試簡答版）

1. 截取查詢值前 2 位字符作為檢索關鍵字，進入前綴索引 B + 樹；
2. 從根節(jié)點逐層向下二分查找，定位到對應葉子節(jié)點；
3. 取出所有前綴匹配的記錄及對應主鍵；
4. 通過主鍵到聚簇索引回表，獲取完整姓名字段；
5. 用完整姓名做精準匹配，過濾出目標數(shù)據(jù)并返回。

三、補充考點

1. 為什么不能只靠前綴直接判定結果？前綴相同不代表完整姓名相同，必須回表校驗完整字段。
2. 該場景能否使用覆蓋索引？不能。前綴索引僅存儲部分字符，無法覆蓋查詢所需完整字段。

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

最新評論