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

一、索引是什么？

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

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

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

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

優(yōu)點

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

缺點

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

三、索引的常見類型

1. 按數(shù)據(jù)結構劃分

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

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

這是 InnoDB 引擎的一個核心概念。

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

3. 按字段特性劃分

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

CREATE INDEX idx_name ON table_name (column_name);

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

CREATE UNIQUE INDEX idx_email ON users (email);

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

組合索引（復合索引）：在多個列上建立的索引。

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

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

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，因為 first_name 斷了)

但對以下查詢無效：

WHERE first_name = ‘John’

WHERE age = 30

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

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

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

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

索引使用總結：

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

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

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

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

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

重點關注以下幾個字段：

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

        如果出現(xiàn) Using filesort（文件排序）或 Using temporary（使用臨時表），通常意味著需要優(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"  // ← 這是整個查詢的預估總成本！
    },
    "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,  // 預估掃描行數(shù)
      "rows_produced_per_join": 5,   // 預估產(chǎn)出行數(shù)
      "filtered": "11.11",           // 過濾效率百分比
      "cost_info": {
        "read_cost": "9.95",         // 讀取成本
        "eval_cost": "0.50",         // 計算評估成本
        "prefix_cost": "10.45",      // 當前表的總成本
        "data_read_per_join": "1K"   // 讀取數(shù)據(jù)量
      },
      "used_columns": [...]
    }
  }
}

關鍵成本指標：

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

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

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

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)化器預估的成本和行數(shù)
• actual time=0.125..0.256 rows=3：實際執(zhí)行的結果
• 0.125..0.256：第一行花費0.125ms，所有行花費0.256ms
• rows=3：實際返回3行

這是最可靠的性能分析工具，因為它對比了優(yōu)化器的預估和實際執(zhí)行情況。

六、訪問類型詳解

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

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

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

1.system

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

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

示例：

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

2.const

• 含義：通過主鍵（PRIMARY KEY） 或唯一索引（UNIQUE INDEX） 進行等值查詢時，最多只返回一條記錄。
• 說明：因為索引是唯一的，所以查詢速度極快，性能最佳。MySQL 將查詢轉換為一個常量來優(yōu)化。
• 示例：

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

3.eq_ref

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

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

示例：

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

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

4.ref

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

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

5.ref_or_null

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

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

6.range

• 含義：使用索引來檢索給定范圍的行。通常出現(xiàn)在 IN、BETWEEN、>、<、>=、<= 等操作中。
• 說明：這時 key_len 列會顯示索引中使用的字節(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

• 含義：全索引掃描。它遍歷整個索引樹來查找數(shù)據(jù)，與全表掃描 ALL 類似，但因為它只掃描索引（通常比數(shù)據(jù)文件?。员?nbsp;ALL 快。
• 觸發(fā)場景：
  • 查詢的列都包含在某個索引中（即覆蓋索引），但需要掃描整個索引。
  • 使用 ORDER BY 主鍵的查詢。
• 示例：

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

8.ALL

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

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

9. 其他較少見的類型

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

訪問類型總結：

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

七、實際應用：成本對比分析

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

-- 情況1：無合適索引
EXPLAIN FORMAT=JSON SELECT * FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31' AND customer_id = 100;
-- 結果：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;
-- 結果：query_cost = "8.75", type = "range"

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

成本評估總結：

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

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

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

最新評論