這是一篇關(guān)于 MySQL 數(shù)據(jù)庫索引的全面介紹，從基本概念到高級(jí)特性，希望能幫助你深入理解。

一、索引是什么？

想象一下一本書的目錄。如果沒有目錄，你想找到某一章節(jié)的內(nèi)容，只能從第一頁開始一頁一頁地翻找。而有了目錄，你可以通過章節(jié)標(biāo)題快速定位到對(duì)應(yīng)的頁碼。

在 MySQL 中，索引就是一種幫助數(shù)據(jù)庫高效獲取數(shù)據(jù)的“目錄”。它是在存儲(chǔ)引擎層實(shí)現(xiàn)的，而不是服務(wù)器層，因此不同的存儲(chǔ)引擎（如 InnoDB、MyISAM）的索引工作方式有所不同（我們主要討論最常用的 InnoDB）。

沒有索引：數(shù)據(jù)庫需要進(jìn)行全表掃描，從第一行開始，逐行讀取直到找到所有符合條件的行。對(duì)于海量數(shù)據(jù)，這非常緩慢。
有索引：數(shù)據(jù)庫可以通過索引直接定位到數(shù)據(jù)的大概位置，然后只需檢查很少的數(shù)據(jù)行即可得到結(jié)果，效率極高。

二、索引的優(yōu)缺點(diǎn)

優(yōu)點(diǎn)

• 大大加快數(shù)據(jù)的查詢速度：這是最主要的原因，尤其是對(duì) WHERE、ORDER BY 和 GROUP BY 子句。
• 通過創(chuàng)建唯一性索引，可以保證數(shù)據(jù)庫表中每一行數(shù)據(jù)的唯一性。
• ?加速表與表之間的連接：在進(jìn)行表連接查詢時(shí)，對(duì)有外鍵或連接鍵的列建立索引可以顯著提高性能。

缺點(diǎn)

• 創(chuàng)建和維護(hù)索引需要耗費(fèi)時(shí)間：對(duì)表進(jìn)行增、刪、改操作（INSERT、UPDATE、DELETE）時(shí)，索引也需要?jiǎng)討B(tài)維護(hù)，這會(huì)降低數(shù)據(jù)寫入的速度。
• 索引需要占用物理空間：除了數(shù)據(jù)表占據(jù)數(shù)據(jù)空間之外，每一個(gè)索引還要占一定的物理空間。
• 需要權(quán)衡：過多的索引或不恰當(dāng)?shù)乃饕粌H不能提高性能，反而會(huì)成為系統(tǒng)的負(fù)擔(dān)。

三、索引的常見類型

1. 按數(shù)據(jù)結(jié)構(gòu)劃分

• B+Tree 索引：MySQL 最常用、默認(rèn)的索引類型。InnoDB 和 MyISAM 都支持。
• 特點(diǎn)：
  • 數(shù)據(jù)都存儲(chǔ)在葉子節(jié)點(diǎn)，非葉子節(jié)點(diǎn)只存儲(chǔ)鍵值，因此查詢效率穩(wěn)定。
  • 葉子節(jié)點(diǎn)之間通過指針相連，形成了有序鏈表，非常適合范圍查詢（如 BETWEEN、>、<）和排序。
  • 適用于：全鍵值、鍵值范圍、鍵值前綴（最左前綴）查詢。
• Hash 索引：
  • 特點(diǎn)：通過對(duì)索引鍵計(jì)算一個(gè)哈希碼來定位數(shù)據(jù)，所以等值查詢（=）的效率極高，時(shí)間復(fù)雜度接近 O(1)。
• 局限性：
• 無法用于范圍查詢。
• 不支持排序。
  • 不支持部分索引鍵匹配（因?yàn)楣Ｖ凳腔谡麄€(gè)索引鍵計(jì)算的）。
• 注意：InnoDB 引擎有一個(gè)“自適應(yīng)哈希索引”功能，當(dāng)某些索引值被非常頻繁地訪問時(shí)，它會(huì)在內(nèi)存中基于 B-Tree 索引的鍵再創(chuàng)建一個(gè)哈希索引，以加速查詢。這是引擎自動(dòng)完成的，用戶無法手動(dòng)創(chuàng)建哈希索引（Memory 存儲(chǔ)引擎支持）。
• R-Tree (空間索引)：
  • 主要用于地理空間數(shù)據(jù)類型，如 GEOMETRY、POINT 等。日常業(yè)務(wù)中很少使用。
• Full-Text (全文索引)：
  • 用于快速查找文本中的關(guān)鍵字，類似于搜索引擎。它有自己的語法，使用 MATCH ... AGAINST 子句。 性能差強(qiáng)人意，一般也很少使用。

2. 按物理存儲(chǔ)劃分（InnoDB 聚簇索引特性）

這是 InnoDB 引擎的一個(gè)核心概念。

• 聚簇索引
• 定義：表數(shù)據(jù)本身其實(shí)就是聚簇索引。索引的葉子節(jié)點(diǎn)直接存儲(chǔ)了完整的數(shù)據(jù)行。
• 每個(gè) InnoDB 表有且只有一個(gè)聚簇索引。
  • 它是如何被選擇的？
  • 如果你定義了主鍵（PRIMARY KEY），那么主鍵就是聚簇索引。
  • 如果沒有主鍵，則選擇第一個(gè)不允許為 NULL 的唯一索引（UNIQUE KEY）作為聚簇索引。
  • 如果兩者都沒有，InnoDB 會(huì)隱式地創(chuàng)建一個(gè)隱藏的 ROWID 字段作為聚簇索引。
• 優(yōu)點(diǎn)：因?yàn)閿?shù)據(jù)行就存放在葉子節(jié)點(diǎn)，所以通過聚簇索引訪問數(shù)據(jù)非常快。
• 缺點(diǎn)：插入速度嚴(yán)重依賴于主鍵的順序。亂序插入可能導(dǎo)致頁分裂，影響性能。
• 非聚簇索引（也叫二級(jí)索引或輔助索引）
• 定義：索引的葉子節(jié)點(diǎn)存儲(chǔ)的不是完整的數(shù)據(jù)行，而是該行對(duì)應(yīng)的主鍵值。
  • 當(dāng)通過非聚簇索引查詢時(shí)，數(shù)據(jù)庫需要先找到對(duì)應(yīng)的主鍵，然后再用這個(gè)主鍵回到聚簇索引中查找完整的行數(shù)據(jù)。這個(gè)過程被稱為回表。
  • 因此，基于非聚簇索引的查詢通常比基于主鍵的查詢要慢，尤其是需要回表很多行時(shí)。

3. 按字段特性劃分

普通索引：最基本的索引，沒有任何限制。

CREATE INDEX idx_name ON table_name (column_name);

唯一索引：索引列的值必須是唯一的，但允許有空值。

CREATE UNIQUE INDEX idx_email ON users (email);

主鍵索引：一種特殊的唯一索引，不允許有空值。每個(gè)表只能有一個(gè)主鍵索引。

組合索引（復(fù)合索引）：在多個(gè)列上建立的索引。

CREATE INDEX idx_name_age ON employees (last_name, first_name, age);

最左前綴原則：這是組合索引最重要的特性。查詢時(shí)，索引只能從最左邊的列開始匹配。上面的 idx_name_age 索引對(duì)以下查詢有效：

WHERE last_name = ‘Smith’

WHERE last_name = ‘Smith’ AND first_name = ‘John’

WHERE last_name = ‘Smith’ AND first_name = ‘John’ AND age = 30

WHERE last_name = ‘Smith’ AND age = 30 (只使用了 last_name，因?yàn)?nbsp;first_name 斷了)

但對(duì)以下查詢無效：

WHERE first_name = ‘John’

WHERE age = 30

四、索引的使用策略與優(yōu)化

• 哪些情況需要?jiǎng)?chuàng)建索引？
  • 主鍵自動(dòng)建立唯一索引。
  • 頻繁作為查詢條件（WHERE）的字段。
  • 與其他表進(jìn)行關(guān)聯(lián)的字段（外鍵）。
  • 頻繁需要排序（ORDER BY）和分組（GROUP BY）的字段。
  • 查詢中需要統(tǒng)計(jì)或分組的字段。
• 哪些情況不適合創(chuàng)建索引？
  • 表記錄太少（例如配置表，可能只有幾十行）。
  • 頻繁進(jìn)行增、刪、改的表（需要權(quán)衡讀寫比例）。
  • 數(shù)據(jù)重復(fù)且分布均勻的字段（如“性別”字段，只有‘男‘/’女‘，建立索引意義不大）。
  • 很少或從不參與查詢的字段。
• 索引優(yōu)化技巧
• 前綴索引：對(duì)于很長(zhǎng)的字符列（如 VARCHAR(255)），可以只對(duì)列的前 N 個(gè)字符建立索引，以節(jié)省空間。

• CREATE INDEX idx_url_prefix ON websites (url(20));

• 覆蓋索引：如果一個(gè)索引包含了查詢所需要的所有字段，我們就稱之為“覆蓋索引”。這時(shí)，查詢可以直接從索引中獲取數(shù)據(jù)，而無需回表，極大地提升了性能。
• 例如，有一個(gè)索引 (a, b, c)，查詢 SELECT a, b FROM table WHERE a = 1 AND b = 2 就是一個(gè)覆蓋索引查詢。
• 索引下推：這是 MySQL 5.6 引入的重要優(yōu)化。在沒有 ICP 之前，存儲(chǔ)引擎會(huì)通過索引檢索到數(shù)據(jù)，然后將完整的數(shù)據(jù)行返回給服務(wù)器層，再由服務(wù)器層根據(jù) WHERE 條件過濾。有了 ICP 之后，如果 WHERE 條件中的某些列也存在于索引中，存儲(chǔ)引擎會(huì)在索引層面就完成一部分過濾，從而減少回表的次數(shù)。

索引使用總結(jié)：

正確理解和使用索引是數(shù)據(jù)庫性能優(yōu)化的基石。它需要在查詢速度和寫入開銷之間做出權(quán)衡，并通過不斷的分析和測(cè)試來找到最佳實(shí)踐。

五、查看和分析索引使用情況

1. 查看執(zhí)行計(jì)劃

使用 EXPLAIN 命令可以查看 SQL 語句的執(zhí)行計(jì)劃，這是優(yōu)化索引最強(qiáng)大的工具。

EXPLAIN SELECT * FROM employees WHERE last_name = ‘Smith' AND age > 30;

重點(diǎn)關(guān)注以下幾個(gè)字段：

• type：訪問類型
  •         從好到壞依次是 const、eq_ref、ref、range、index、ALL。至少要做到 range 級(jí)別，最好能達(dá)到 ref。
• key：實(shí)際使用的索引
• rows：預(yù)估需要掃描的行數(shù)
• Extra：額外信息

        如果出現(xiàn) Using filesort（文件排序）或 Using temporary（使用臨時(shí)表），通常意味著需要優(yōu)化索引。如果出現(xiàn) Using index，則說明使用了覆蓋索引，性能很好。

2. 查看估算成本

使用 EXPLAIN FORMAT=JSON 可以查看優(yōu)化器估算的精確成本值。

EXPLAIN FORMAT=JSON SELECT * FROM employees WHERE last_name = 'Smith' AND department_id = 5;

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "10.45"  // ← 這是整個(gè)查詢的預(yù)估總成本！
    },
    "table": {
      "table_name": "employees",
      "access_type": "ref",  // 訪問類型
      "possible_keys": ["idx_last_name", "idx_department"],
      "key": "idx_last_name",
      "used_key_parts": ["last_name"],
      "key_length": "62",
      "ref": ["const"],
      "rows_examined_per_scan": 45,  // 預(yù)估掃描行數(shù)
      "rows_produced_per_join": 5,   // 預(yù)估產(chǎn)出行數(shù)
      "filtered": "11.11",           // 過濾效率百分比
      "cost_info": {
        "read_cost": "9.95",         // 讀取成本
        "eval_cost": "0.50",         // 計(jì)算評(píng)估成本
        "prefix_cost": "10.45",      // 當(dāng)前表的總成本
        "data_read_per_join": "1K"   // 讀取數(shù)據(jù)量
      },
      "used_columns": [...]
    }
  }
}

關(guān)鍵成本指標(biāo)：

• query_cost：最重要的指標(biāo)，表示優(yōu)化器預(yù)估的查詢總成本。這個(gè)值越小越好。
• read_cost：從存儲(chǔ)引擎讀取數(shù)據(jù)的成本。
• eval_cost：在服務(wù)器層處理、計(jì)算和比較數(shù)據(jù)的成本。
• rows_examined_per_scan：預(yù)估需要掃描的行數(shù)。
• filtered：WHERE 條件過濾后，剩余行數(shù)的百分比。

3. 查看實(shí)際執(zhí)行

使用 MySQL 8.0 的 EXPLAIN ANALYZE，它不僅顯示優(yōu)化器的預(yù)估，還實(shí)際執(zhí)行查詢并返回實(shí)際執(zhí)行的統(tǒng)計(jì)信息！

EXPLAIN ANALYZE SELECT * FROM employees WHERE last_name = 'Smith' AND department_id = 5;

輸出示例：

-> Filter: (employees.department_id = 5)  (cost=10.45 rows=5) (actual time=0.125..0.256 rows=3 loops=1)
    -> Index lookup on employees using idx_last_name (last_name='Smith')  (cost=10.45 rows=45) (actual time=0.120..0.248 rows=45 loops=1)

示例解讀：

• cost=10.45 rows=5：優(yōu)化器預(yù)估的成本和行數(shù)
• actual time=0.125..0.256 rows=3：實(shí)際執(zhí)行的結(jié)果
• 0.125..0.256：第一行花費(fèi)0.125ms，所有行花費(fèi)0.256ms
• rows=3：實(shí)際返回3行

這是最可靠的性能分析工具，因?yàn)樗鼘?duì)比了優(yōu)化器的預(yù)估和實(shí)際執(zhí)行情況。

六、訪問類型詳解

EXPLAIN 命令輸出中的 type 列是非常重要的，它告訴我們 MySQL 是如何在表中查找行的。以下是從最優(yōu)到最差排序的各個(gè)訪問類型的詳細(xì)解釋。

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

在實(shí)際優(yōu)化中，我們最常關(guān)注的是 const, eq_ref, ref, range, index, ALL。

1.system

含義：這是 const 類型的一個(gè)特例。表示表中只有一行數(shù)據(jù)（等于系統(tǒng)表）。這是性能最好的情況。

觸發(fā)場(chǎng)景：通常是查詢系統(tǒng)表，或者使用 MyISAM 存儲(chǔ)引擎且只有一條記錄的表。

示例：

-- 假設(shè)只有一條記錄的系統(tǒng)配置表
EXPLAIN SELECT * FROM system_config WHERE id = 1;

2.const

• 含義：通過主鍵（PRIMARY KEY） 或唯一索引（UNIQUE INDEX） 進(jìn)行等值查詢時(shí)，最多只返回一條記錄。
• 說明：因?yàn)樗饕俏ㄒ坏?，所以查詢速度極快，性能最佳。MySQL 將查詢轉(zhuǎn)換為一個(gè)常量來優(yōu)化。
• 示例：

EXPLAIN SELECT * FROM users WHERE id = 10; -- id 是主鍵
EXPLAIN SELECT * FROM users WHERE email = 'admin@example.com'; -- email 有唯一索引

3.eq_ref

含義：在表連接時(shí)使用，通常出現(xiàn)在被驅(qū)動(dòng)表的連接條件上。它使用主鍵或唯一索引作為關(guān)聯(lián)條件，對(duì)于驅(qū)動(dòng)表的每一行，在被驅(qū)動(dòng)表中只能找到唯一的一行與之對(duì)應(yīng)。

說明：這是除了 system 和 const 之外最好的連接類型。

示例：

EXPLAIN SELECT *
FROM orders
JOIN customers ON orders.customer_id = customers.id; -- customers.id 是主鍵

在這個(gè)例子中，對(duì)于 orders 表中的每一個(gè) customer_id，到 customers 表中通過主鍵 id 查找，每次查找都只返回一條記錄。

4.ref

• 含義：使用非唯一性索引進(jìn)行等值查詢，或者連接查詢使用了非唯一索引的列。
• 說明：這是一種非常常見的、性能良好的訪問類型。它可能返回0行、1行或多行記錄。
• 示例：

-- 假設(shè) last_name 字段有一個(gè)普通索引
EXPLAIN SELECT * FROM employees WHERE last_name = 'Smith';
-- 表連接中使用非唯一索引
EXPLAIN SELECT *
FROM orders o
JOIN products p ON o.product_sku = p.sku; -- p.sku 是一個(gè)普通索引（非唯一）

5.ref_or_null

• 含義：類似于 ref，但 MySQL 會(huì)額外搜索包含 NULL 值的行。這種類型通常發(fā)生在對(duì)具有索引的列進(jìn)行等值比較并包括 IS NULL 條件時(shí)。
• 示例：

EXPLAIN SELECT * FROM users WHERE phone_number = '123456' OR phone_number IS NULL;
-- 假設(shè) phone_number 上有索引

6.range

• 含義：使用索引來檢索給定范圍的行。通常出現(xiàn)在 IN、BETWEEN、>、<、>=、<= 等操作中。
• 說明：這時(shí) key_len 列會(huì)顯示索引中使用的字節(jié)數(shù)，表示只使用了索引的一部分。
• 示例：

EXPLAIN SELECT * FROM employees WHERE age BETWEEN 25 AND 35; -- age 有索引
EXPLAIN SELECT * FROM users WHERE id IN (1, 5, 10); -- id 是主鍵
EXPLAIN SELECT * FROM products WHERE price > 50; -- price 有索引

7.index

• 含義：全索引掃描。它遍歷整個(gè)索引樹來查找數(shù)據(jù)，與全表掃描 ALL 類似，但因?yàn)樗粧呙杷饕ㄍǔ１葦?shù)據(jù)文件?。员?nbsp;ALL 快。
• 觸發(fā)場(chǎng)景：
  • 查詢的列都包含在某個(gè)索引中（即覆蓋索引），但需要掃描整個(gè)索引。
  • 使用 ORDER BY 主鍵的查詢。
• 示例：

-- 假設(shè) (status, created_at) 有一個(gè)復(fù)合索引
EXPLAIN SELECT status, created_at FROM articles; -- 覆蓋索引，但無 WHERE 條件
EXPLAIN SELECT id FROM users ORDER BY id; -- 按主鍵排序

8.ALL

• 含義：全表掃描。MySQL 會(huì)讀取表中的每一行來找到匹配的行。
• 說明：這是性能最差的訪問類型，對(duì)于大表來說是災(zāi)難性的。優(yōu)化目標(biāo)就是通過創(chuàng)建合適的索引來避免出現(xiàn) ALL。
• 觸發(fā)場(chǎng)景：沒有索引可用于查詢。
• 示例：

EXPLAIN SELECT * FROM products WHERE name = 'Laptop'; -- name 列上沒有索引

9. 其他較少見的類型

• index_merge：表示查詢使用了索引合并優(yōu)化。MySQL 會(huì)使用多個(gè)索引，然后將各自的結(jié)果進(jìn)行合并（取交集、并集等）。
• unique_subquery / index_subquery：這兩種類型都與 IN 子查詢相關(guān)，unique_subquery 用于唯一索引的子查詢，效率更高。

訪問類型總結(jié)：

核心建議：
在優(yōu)化時(shí)，我們的目標(biāo)是讓 type 至少達(dá)到 range 級(jí)別，最好能達(dá)到 ref。一旦看到 ALL，就應(yīng)該立即檢查是否可以為相關(guān)列創(chuàng)建有效的索引。

七、實(shí)際應(yīng)用：成本對(duì)比分析

讓我們通過一個(gè)實(shí)際例子來展示如何用成本分析來比較不同索引的效果：

-- 情況1：無合適索引
EXPLAIN FORMAT=JSON SELECT * FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31' AND customer_id = 100;
-- 結(jié)果：query_cost = "1250.25", type = "ALL"
-- 添加索引后
ALTER TABLE orders ADD INDEX idx_customer_date (customer_id, order_date);
-- 情況2：使用新索引
EXPLAIN FORMAT=JSON SELECT * FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31' AND customer_id = 100;
-- 結(jié)果：query_cost = "8.75", type = "range"

結(jié)論：通過成本對(duì)比，我們可以量化索引帶來的性能提升：從 1250.25 降到 8.75，性能提升了 140多倍！

成本評(píng)估總結(jié)：

• 日常快速檢查：使用 EXPLAIN 看 type 和 rows
• 深度優(yōu)化分析：使用 EXPLAIN FORMAT=JSON 查看詳細(xì)的 query_cost
• 最準(zhǔn)確驗(yàn)證：在 MySQL 8.0+ 中使用 EXPLAIN ANALYZE 對(duì)比預(yù)估和實(shí)際性能
• 長(zhǎng)期監(jiān)控：使用 Performance Schema 監(jiān)控查詢的歷史性能
• 優(yōu)化目標(biāo)：通過創(chuàng)建合適的索引，觀察 query_cost 值的下降來驗(yàn)證優(yōu)化效果

記住：成本值本身沒有絕對(duì)的好壞標(biāo)準(zhǔn)，重要的是通過對(duì)比不同查詢方案或索引設(shè)計(jì)的成本值，來選擇最優(yōu)的執(zhí)行計(jì)劃。通常成本值降低 10倍以上就說明優(yōu)化非常有效。

到此這篇關(guān)于MySQL數(shù)據(jù)庫索引全面介紹的文章就介紹到這了,更多相關(guān)MySQL數(shù)據(jù)庫索引內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論