一、索引的定義與核心作用

1. 定義

索引是 MySQL 中用于優(yōu)化查詢效率的數(shù)據(jù)結(jié)構(gòu)，類比書籍的“目錄”，無需遍歷全表即可快速定位數(shù)據(jù)位置。

2. 核心作用

提升查詢速度：避免全表掃描（Full Table Scan），減少磁盤 I/O 和 CPU 資源消耗；

優(yōu)化排序/分組：支持 GROUP BY ORDER BY 操作快速完成（無需額外排序）；

加速表連接：優(yōu)化 JOIN 操作中關(guān)聯(lián)字段的匹配效率。

二、索引的工作邏輯

當(dāng)執(zhí)行 SELECT 查詢時(shí)，MySQL 執(zhí)行流程如下：

1. 解析查詢語句，識(shí)別查詢的表和字段；

2. 檢查該字段是否存在索引：

   若有索引：遍歷索引表（數(shù)據(jù)結(jié)構(gòu)如 B+ 樹），快速定位到數(shù)據(jù)記錄行的物理地址，直接讀取數(shù)據(jù)；

   若無索引：執(zhí)行全表掃描，逐行遍歷表中所有數(shù)據(jù)，匹配目標(biāo)結(jié)果（效率低、資源消耗高）。

三、索引的副作用（不可忽視）

索引并非“越多越好”，存在以下代價(jià)：

1. 占用額外磁盤空間：索引表獨(dú)立于數(shù)據(jù)表格，會(huì)消耗磁盤存儲(chǔ)（如大表的組合索引可能占用大量空間）；

2. 增加更新開銷：修改（INSERT/UPDATE/DELETE）含索引的表時(shí)，需同步更新對(duì)應(yīng)的索引表（如修改主鍵值時(shí)，主鍵索引需重新排序）；

3. 不合理索引降低效率：如在低選擇性字段創(chuàng)建索引，可能導(dǎo)致 MySQL 放棄使用索引，直接全表掃描。

四、索引的創(chuàng)建依據(jù)（適合/不適合場(chǎng)景）

1. 適合創(chuàng)建索引的場(chǎng)景

主鍵和外鍵：默認(rèn)創(chuàng)建主鍵索引，外鍵常用于表連接，必須建索引；

大數(shù)據(jù)量表：記錄數(shù)超過 300 行的表，全表掃描代價(jià)過高；

高頻查詢字段：WHERE 子句中頻繁出現(xiàn)的字段（如用戶查詢、篩選條件）；

連接/排序/分組字段：JOIN 關(guān)聯(lián)字段、GROUP BY/ORDER BY 操作的字段；

高選擇性字段：字段不同值占比高（如身份證號(hào)、手機(jī)號(hào)，選擇性接近 1），索引過濾效果好；

小字段：優(yōu)先在 INT、VARCHAR(短) 等小字段建索引（長(zhǎng)文本、BLOB 等大字段索引開銷極高）。

2. 不適合創(chuàng)建索引的場(chǎng)景

低選擇性字段：如性別（男/女）、布爾值（0/1），索引過濾效果差，不如全表掃描；

更新頻繁的字段：如訂單狀態(tài)（高頻更新），索引同步開銷會(huì)抵消查詢收益；

小表（<300 行）：全表掃描速度快，索引帶來的開銷大于收益；

高頻全表查詢字段：若查詢常使用 SELECT * 且無過濾條件，索引無效。

五、MySQL 常見索引類型及特點(diǎn)

補(bǔ)充：組合索引“最左前綴原則”

示例：創(chuàng)建組合索引 idx_a_b_c (a, b, c)，僅支持以下查詢場(chǎng)景命中索引：

• WHERE a=?（左前綴匹配）

• WHERE a=? AND b=?（連續(xù)前綴匹配）

• WHERE a=? AND b=? AND c=?（全匹配）

不命中場(chǎng)景：WHERE b=? WHERE a=? AND c=?（跳過中間字段）。

六、索引與慢查詢的關(guān)聯(lián)

1. 慢查詢與索引的關(guān)系

慢查詢定義：默認(rèn)情況下，執(zhí)行時(shí)間超過 2 秒的 SELECT 語句（可通過 long_query_time 配置修改）；

核心原因之一：查詢未使用索引（全表掃描），導(dǎo)致耗時(shí)過長(zhǎng)；

配置日志記錄：在 /etc/my.cnf 中開啟以下配置，記錄未使用索引的慢查詢：

[mysqld]
slow_query_log = 1  # 開啟慢查詢?nèi)罩?
slow_query_log_file = /var/lib/mysql/mysql-slow.log  # 日志存儲(chǔ)路徑
long_query_time = 2  # 慢查詢閾值（單位：秒）
log_queries_not_using_indexes = 1  # 記錄未使用索引的 SQL

配置后需重啟 MySQL 服務(wù)生效。

2. 驗(yàn)證索引有效性：EXPLAIN工具

作用：分析 SQL 執(zhí)行計(jì)劃，查看是否使用索引、索引類型、掃描行數(shù)等；

用法：在查詢語句前加 EXPLAIN，示例：

EXPLAIN SELECT * FROM user WHERE id=1;

關(guān)鍵字段解讀：

• type：索引使用類型（ALL 表示全表掃描，ref/range/eq_ref 表示使用索引）；

• key：實(shí)際使用的索引名稱（NULL 表示未使用索引）；

• rows：預(yù)估掃描的行數(shù)（值越小，效率越高）。

七、索引底層與優(yōu)化技巧

1. 索引底層數(shù)據(jù)結(jié)構(gòu)（MySQL 默認(rèn)）

采用 B+ 樹 結(jié)構(gòu)，優(yōu)勢(shì)：

葉子節(jié)點(diǎn)有序排列，支持范圍查詢（如 BETWEEN IN）；

葉子節(jié)點(diǎn)存儲(chǔ)數(shù)據(jù)地址（非聚簇索引）或數(shù)據(jù)本身（聚簇索引），查詢效率穩(wěn)定；

樹高度低（百萬級(jí)數(shù)據(jù)僅需 3-4 層），磁盤 I/O 次數(shù)少。

2. 聚簇索引 vs 非聚簇索引

聚簇索引：索引與數(shù)據(jù)存儲(chǔ)在一起（如主鍵索引），查詢無需“回表”，效率最高；

非聚簇索引：索引與數(shù)據(jù)分離，葉子節(jié)點(diǎn)存儲(chǔ)主鍵值，查詢需通過主鍵值二次查找數(shù)據(jù)（回表），效率略低。

3. 索引失效的常見場(chǎng)景（避坑?。?/h3>

索引字段使用函數(shù)/運(yùn)算：WHERE SUBSTR(name,1,2)='張'（無法命中 name 索引）；

模糊查詢以 % 開頭：WHERE name LIKE '%三'（全模糊 %三% 也失效）；

OR 條件中部分字段無索引：WHERE id=1 OR age=20（若 age 無索引，整個(gè)查詢可能全表掃描）；

隱式類型轉(zhuǎn)換：WHERE phone='123456'（phone 為 INT 類型，字符串與數(shù)字轉(zhuǎn)換導(dǎo)致索引失效）；

聯(lián)合查詢中違背最左前綴原則（見組合索引部分）。

4. 索引優(yōu)化技巧

覆蓋索引：查詢字段僅包含索引列（如 SELECT id, name FROM user WHERE id=1，id 為主鍵索引），避免回表；

定期維護(hù)索引：刪除冗余索引（如已存在 idx_a_b，無需再建 idx_a）、優(yōu)化碎片索引（OPTIMIZE TABLE 表名）；

避免過度索引：一張表索引數(shù)量建議不超過 5 個(gè)，過多索引會(huì)導(dǎo)致更新變慢。

八、核心小結(jié)

1. 索引核心價(jià)值：加速查詢，減少全表掃描開銷；

2. 創(chuàng)建原則：“高頻查詢、高選擇性、小字段”優(yōu)先，避開“更新頻繁、低選擇性”字段；

3. 驗(yàn)證工具：用 EXPLAIN 檢測(cè)索引是否生效，結(jié)合慢查詢?nèi)罩緝?yōu)化索引；

4. 平衡取舍：索引并非越多越好，需權(quán)衡查詢效率與更新開銷。

到此這篇關(guān)于MySQL基礎(chǔ)教程之索引定義與作用的文章就介紹到這了,更多相關(guān)MySQL索引定義與作用內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論