MySQL索引最佳實踐與效能調優

索引如同書籍目錄，能快速定位所需資訊。MySQL 的索引機制根據 B-tree 結構，能有效加速資料查詢。然而，不恰當的索引設計反而可能降低效能。選擇正確的索引型別和欄位順序至關重要，尤其在多欄位索引中，欄位順序會直接影響查詢效率。理解索引選擇性和基數的概念，有助於選擇最佳的索引策略。此外，InnoDB 儲存引擎的叢集索引特性也需要特別關注，它將資料儲存在索引葉節點中，能有效提升相關資料的檢索速度。

索引基礎

要了解 MySQL 中的索引如何運作，最簡單的方法是將其與書籍的索引進行比較。當你想在書中找到特定主題的討論時，你會檢視索引，它會告訴你該主題出現的頁碼。在 MySQL 中，儲存引擎以類別似的方式使用索引。它在索引的資料結構中搜尋值，當找到匹配項時，它可以找到包含該匹配項的行。假設你執行以下查詢：

SELECT first_name FROM sakila.actor WHERE actor_id = 5;

由於 actor_id 列上有索引，MySQL 將使用該索引來查詢 actor_id 為 5 的行。換句話說，它在索引中的值上執行查詢，並傳回包含指定值的任何行。

索引的結構

索引包含表中一列或多列的值。如果你對多列建立索引，列的順序非常重要，因為 MySQL 只能有效地搜尋索引的最左字首。在兩列上建立索引與建立兩個單獨的單列索引不同。

使用 ORM 是否需要關心索引？

簡短的答案是：是的，即使你依賴 ORM 工具，你仍然需要了解索引。ORM 工具可以生成邏輯上和語法上正確的查詢，但在大多數情況下，除非你只使用最基本的查詢型別（如主鍵查詢），否則它們很難生成對索引友好的查詢。

索引型別

有多種型別的索引，每種索引都針對不同的目的設計得很好。索引是在儲存引擎層實作的，而不是在伺服器層。因此，它們並沒有被標準化：在每個引擎中，索引的工作方式都略有不同，並非所有引擎都支援所有型別的索引。即使多個引擎支援相同的索引型別，它們在底層實作方式也可能不同。假設你使用 InnoDB 作為所有表的引擎，我們將專門討論 InnoDB 中的索引實作。

B-tree 索引

當人們談論索引而沒有提到型別時，他們可能指的是 B-tree 索引，它通常使用 B-tree 資料結構來儲存資料。大多數 MySQL 的儲存引擎都支援這種索引型別。

B-tree 索引加快了資料存取速度，因為儲存引擎不必掃描整個表來找到所需的資料。相反，它從根節點開始（圖中未顯示）。根節點中的插槽包含指向子節點的指標，儲存引擎跟隨這些指標。它透過檢視節點頁面中的值來找到正確的指標，這些值定義了子節點中值的上下限。最終，儲存引擎要麼確定所需的值不存在，要麼成功到達葉頁。

B-tree 索引範例

假設你有以下表格：

CREATE TABLE People (
  last_name varchar(50) not null,
  first_name varchar(50) not null,
  dob date not null,
  key(last_name, first_name, dob)
);

該索引將包含表中每一行的 last_name、first_name 和 dob 列的值。圖 7-2 說明瞭索引如何排列它所儲存的資料。

此圖示展示了一個 B-tree 索引的基本結構，其中根節點指向多個子節點，子節點再指向葉節點，最終葉節點指向實際的資料。

#### 內容解密：

B-tree 索引是一種高效的資料結構，能夠加速查詢速度。它透過將資料儲存在有序的樹狀結構中，使得查詢可以快速定位到所需的資料。B-tree 索引支援全鍵值、鍵範圍和鍵字首的查詢，並且對於範圍查詢非常有效。

自適應雜湊索引

InnoDB 儲存引擎具有一個特殊的特性，稱為自適應雜湊索引。當 InnoDB 發現某些索引值被非常頻繁地存取時，它會在記憶體中為這些值建立一個雜湊索引，根據 B-tree 索引。這使得其 B-tree 索引具有一些雜湊索引的特性，例如非常快速的雜湊查詢。這個過程是完全自動的，你無法控制或組態它，但你可以完全停用自適應雜湊索引。

可使用 B-tree 索引的查詢型別

B-tree 索引適用於按全鍵值、鍵範圍或鍵字首進行查詢。它們只在查詢使用索引的最左字首時才有用。例如，前面顯示的索引對於以下型別的查詢非常有用：

• 匹配完整值：例如，這個索引可以幫助你找到一個名為 Cuba Allen 且出生於 1960-01-01 的人。
• 範圍查詢：由於 B-tree 索引按照順序儲存值，因此它們對於範圍查詢非常有效，例如查詢姓氏以特定字母開頭的所有人。

#### 內容解密：

B-tree 索引對於多種查詢型別都非常有用，包括全鍵值查詢、鍵範圍查詢和鍵字首查詢。這使得它們成為大多數資料函式庫應用程式中非常有價值的工具。透過瞭解如何有效地使用 B-tree 索引，你可以顯著提高資料函式庫查詢的效能。

索引的高效能策略

在資料函式倉管理中，索引的正確建立和使用對於查詢效能至關重要。本章節將探討如何有效地選擇和使用索引，以實作高效能的資料檢索。

字首索引與索引選擇性

索引字首是指對欄位值的一部分進行索引，而不是整個值。這種做法可以節省空間並提高效能，但也會降低索引的選擇性。索引選擇性是指索引中不同值的數量（基數）與表中總行數的比率，範圍從1/#T到1。選擇性高的索引非常有用，因為它允許MySQL在尋找匹配項時過濾掉更多的行。

內容解密：

• 索引選擇性的計算方式是：不同索引值的數量 / 表中的總行數。
• 一個具有高選擇性的索引對於查詢效能有顯著的提升。
• 當索引BLOB、TEXT或非常長的VARCHAR欄位時，必須定義字首索引，因為MySQL不允許對這些欄位的完整長度進行索引。

如何確定最佳字首長度

要找到最佳的字首長度，需要找出最頻繁出現的值，並與最頻繁出現的字首列表進行比較。這樣可以確保字首的基數接近完整欄位的基數，從而提供良好的查詢效能。

範例程式碼：

SELECT 
    COUNT(DISTINCT LEFT(column_name, prefix_length)) AS prefix_cardinality,
    COUNT(DISTINCT column_name) AS full_cardinality
FROM 
    table_name;

內容解密：

• 使用COUNT(DISTINCT LEFT(column_name, prefix_length))來計算字首的基數。
• 將字首基數與完整欄位的基數進行比較，以評估字首的選擇性。
• 透過調整prefix_length的值，找到一個既能節省空間又能保持良好選擇性的字首長度。

索引策略的最佳實踐

1. 根據查詢模式建立索引：瞭解應用程式的查詢模式，根據WHERE、JOIN和ORDER BY子句中使用的欄位建立合適的索引。
2. 使用覆寫索引：覆寫索引包含了查詢所需的所有欄位，可以避免存取表資料，直接從索引中取得結果，提高查詢效率。
3. 避免冗餘索引：檢查現有的索引，避免建立冗餘或重複的索引，以減少維護成本和儲存空間。

@startuml
skinparam backgroundColor #FEFEFE
skinparam componentStyle rectangle

title MySQL索引最佳實踐與效能調優

package "資料庫架構" {
    package "應用層" {
        component [連線池] as pool
        component [ORM 框架] as orm
    }

    package "資料庫引擎" {
        component [查詢解析器] as parser
        component [優化器] as optimizer
        component [執行引擎] as executor
    }

    package "儲存層" {
        database [主資料庫] as master
        database [讀取副本] as replica
        database [快取層] as cache
    }
}

pool --> orm : 管理連線
orm --> parser : SQL 查詢
parser --> optimizer : 解析樹
optimizer --> executor : 執行計畫
executor --> master : 寫入操作
executor --> replica : 讀取操作
cache --> executor : 快取命中

master --> replica : 資料同步

note right of cache
  Redis/Memcached
  減少資料庫負載
end note

@enduml

此圖示說明瞭資料函式庫在接收到查詢請求後，根據是否存在合適的索引來決定是使用索引查詢還是進行全表掃描。

內容解密：

• 圖中展示了查詢請求的處理流程。
• 當存在合適的索引時，資料函式庫可以使用索引來加速查詢。
• 當不存在合適的索引時，資料函式庫將進行全表掃描，這通常是一個耗時的過程。

字首索引的建立與評估

在處理大型資料集時，最佳化索引策略是提升查詢效能的關鍵。本文將探討如何建立字首索引，以及評估其選擇性的最佳實踐。

準備範例資料

首先，我們從 sakila.city 表中建立一個示例資料表 sakila.city_demo，並插入足夠的資料以供測試。

CREATE TABLE sakila.city_demo(city VARCHAR(50) NOT NULL);
INSERT INTO sakila.city_demo(city) SELECT city FROM sakila.city;
-- 重複執行以下陳述式五次，以增加資料量
INSERT INTO sakila.city_demo(city) SELECT city FROM sakila.city_demo;
-- 隨機化城市名稱，以模擬真實資料分佈
UPDATE sakila.city_demo SET city = (SELECT city FROM sakila.city ORDER BY RAND() LIMIT 1);

內容解密：

1. 建立 sakila.city_demo 表：該表用於儲存城市名稱，欄位 city 的型別為 VARCHAR(50)，且不可為空。
2. 初始資料插入：從 sakila.city 表中將城市名稱插入到 sakila.city_demo 中。
3. 資料量擴增：透過重複插入自身資料的方式，將資料量擴大，以便後續測試。
4. 隨機化城市名稱：使用 RAND() 函式隨機更新 city 欄位，以模擬真實世界中不均勻的資料分佈。

分析資料分佈

接下來，我們分析 city_demo 表中的資料分佈，找出最常出現的城市名稱。

SELECT COUNT(*) AS c, city 
FROM sakila.city_demo 
GROUP BY city 
ORDER BY c DESC 
LIMIT 10;

內容解密：

1. 分組統計：按城市名稱進行分組，並統計每組的數量。
2. 排序與限制輸出：按計數結果降序排列，並限制輸出前 10 筆資料。

結果顯示，各城市名稱的出現次數大約在 45 至 65 之間。

字首索引的選擇性評估

我們進一步分析城市名稱字首的出現頻率，從三個字元的字首開始。

SELECT COUNT(*) AS c, LEFT(city, 3) AS pref 
FROM sakila.city_demo 
GROUP BY pref 
ORDER BY c DESC 
LIMIT 10;

內容解密：

1. 提取字首：使用 LEFT(city, 3) 提取城市名稱的前三個字元作為字首。
2. 分組與統計：按字首進行分組，並統計每組的數量。
3. 排序與輸出：按計數結果降序排列，並輸出前 10 筆資料。

結果顯示，字首的出現頻率遠高於完整城市名稱的出現頻率。

選擇適當的字首長度

我們嘗試不同的字首長度，以找到一個合適的值，使其選擇性接近完整欄位的選擇性。

SELECT COUNT(DISTINCT LEFT(city, 3))/COUNT(*) AS sel3,
       COUNT(DISTINCT LEFT(city, 4))/COUNT(*) AS sel4,
       COUNT(DISTINCT LEFT(city, 5))/COUNT(*) AS sel5,
       COUNT(DISTINCT LEFT(city, 6))/COUNT(*) AS sel6,
       COUNT(DISTINCT LEFT(city, 7))/COUNT(*) AS sel7
FROM sakila.city_demo;

內容解密：

1. 計算不同字首長度的選擇性：分別計算字首長度為 3 至 7 的選擇性。
2. 比較選擇性：透過比較不同字首長度的選擇性，找出最合適的字首長度。

結果顯示，當字首長度為 7 時，其選擇性已非常接近完整欄位的選擇性（約 0.031）。

建立字首索引

根據上述分析，我們決定在 city 欄位上建立一個長度為 7 的字首索引。

ALTER TABLE sakila.city_demo ADD KEY (city(7));

內容解密：

1. 建立字首索引：使用 ALTER TABLE 陳述式在 city 欄位上新增一個字首索引，索引長度為 7。

多欄位索引的注意事項

除了字首索引外，多欄位索引也是最佳化查詢的重要手段。然而，常見的錯誤包括為每個欄位單獨建立索引，或是錯誤地排序索引欄位。正確的多欄位索引策略能夠顯著提升查詢效能。

高效能索引策略

在資料函式庫最佳化過程中，索引的設計與使用是提升查詢效能的關鍵因素之一。不當的索引策略不僅無法改善查詢效能，反而可能導致效能下降。

錯誤的索引設計

常見的錯誤索引設計包括為每個欄位單獨建立索引，如下所示：

CREATE TABLE t (
    c1 INT,
    c2 INT,
    c3 INT,
    KEY(c1),
    KEY(c2),
    KEY(c3)
);

這種做法往往源於一些模糊但聽起來很有權威的建議，例如「在WHERE子句中出現的欄位上建立索引」。然而，這種建議是錯誤的，因為它最多隻能產生一星級索引（one-star index），而真正的最佳索引可能會比這快上幾個數量級。

內容解密：

1. 這種單獨為每個欄位建立索引的做法在大多數查詢中並不能有效提升效能。
2. MySQL 可以透過索引合併（index merge）策略來有限地利用多個索引，但這通常不是最佳選擇。
3. 索引合併可能涉及多個索引的掃描、緩衝、排序和合併操作，這些操作可能會消耗大量的 CPU 和記憶體資源。

索引合併策略

MySQL 的索引合併策略允許查詢在單一表格上對多個索引進行有限的使用，以定位所需的資料列。以下是一個使用索引合併的查詢範例：

EXPLAIN SELECT film_id, actor_id FROM sakila.film_actor
WHERE actor_id = 1 OR film_id = 1\G

輸出結果顯示 MySQL 使用了索引合併（index_merge）策略：

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: film_actor
   partitions: NULL
         type: index_merge
possible_keys: PRIMARY,idx_fk_film_id
          key: PRIMARY,idx_fk_film_id
      key_len: 2,2
          ref: NULL
         rows: 29
     filtered: 100.00
        Extra: Using union(PRIMARY,idx_fk_film_id); Using where

內容解密：

1. 索引合併有三種變體：union（用於 OR 條件）、intersection（用於 AND 條件）以及兩者的組合。
2. 當伺服器對索引進行交叉（通常是 AND 條件）時，通常意味著需要一個包含所有相關欄位的單一索引，而不是多個需要合併的索引。
3. 當伺服器對索引進行聯合（通常是 OR 條件）時，演算法的緩衝、排序和合併操作可能會消耗大量的 CPU 和記憶體資源。

選擇適當的欄位順序

在多欄位索引中，欄位的順序至關重要。正確的順序取決於將使用索引的查詢，並且必須考慮如何選擇索引順序，以便以有利於查詢的方式對資料列進行排序和分組。

多欄位 B-tree 索引的排序規則

多欄位 B-tree 索引首先按照最左邊的欄位排序，然後是下一個欄位，依此類別推。因此，索引可以按照正向或反向順序掃描，以滿足與欄位順序完全匹配的 ORDER BY、GROUP BY 和 DISTINCT 子句。

選擇欄位順序的經驗法則

一個常見的經驗法則是將選擇性最高的欄位放在索引的最前面。然而，這種建議的有用性取決於具體情況。如果查詢中沒有排序或分組操作，那麼將選擇性最高的欄位放在最前面可能是合理的。

範例分析

考慮以下查詢：

SELECT * FROM payment WHERE staff_id = 2 AND customer_id = 584;

應該建立 (staff_id, customer_id) 還是 (customer_id, staff_id) 的索引？可以執行一些快速查詢來檢查表格中值的分佈，並確定哪個欄位具有更高的選擇性。

SELECT SUM(staff_id = 2), SUM(customer_id = 584) FROM payment\G

根據結果，customer_id 的選擇性更高，因此應該將其放在索引的最前面。

內容解密：

1. 需要根據具體查詢和資料分佈來決定欄位的順序。
2. 可以透過檢查特定常數值的選擇性來確定最佳的欄位順序。
3. 使用 pt-query-digest 等工具提供的範例查詢，可以有效地找出最有幫助的索引。

高效能索引策略探討

在資料函式庫最佳化過程中，索引的設計與使用對於查詢效能的影響至關重要。本文將探討索引的選擇性、基數以及叢集索引對查詢效能的影響。

索引順序的重要性

在建立索引時，欄位的順序對於查詢效能有著直接的影響。根據前文所述，當查詢條件中涉及多個欄位時，應將選擇性較高的欄位置於索引的前方，以提高查詢效率。例如，在 payment 表中，若 customer_id 的選擇性較高，則應將其置於 staff_id 之前：

ALTER TABLE payment ADD KEY(customer_id, staff_id);

內容解密：

1. 選擇性高的欄位優先：將 customer_id 置於索引首位，是因為其具有較高的選擇性，能夠更有效地縮小查詢範圍。
2. 多欄位索引的優勢：透過建立包含多個欄位的索引，可以提高複雜查詢的效能。
3. 避免單一索引的侷限：單一欄位的索引可能無法滿足複雜查詢的需求，因此需要建立多欄位索引。

特殊值對查詢效能的影響

在某些情況下，特殊值的存在可能會對查詢效能產生負面影響。例如，將未登入的使用者標記為「guest」，或是在系統中存在一個與所有使用者皆為「好友」的特殊管理帳號。這些特殊值可能會導致查詢結果集過大，從而影響效能。

案例分析：

某產品論壇的查詢案例中，發現特定查詢執行緩慢：

SELECT COUNT(DISTINCT threadId) AS COUNT_VALUE
FROM Message
WHERE (groupId = 10137) AND (userId = 1288826) AND (anonymous = 0)
ORDER BY priority DESC, modifiedDate DESC

執行 EXPLAIN 後發現，MySQL 使用了 (groupId, userId) 索引，但仍需掃描大量資料列。進一步分析資料後發現，該群組及使用者擁有的資料量遠超預期，導致查詢效能低下。

內容解密：

1. 資料分佈不均的影響：特殊值或異常資料可能會導致查詢效能下降。
2. 索引選擇的侷限：即使選擇了合適的索引，若資料分佈不均，仍可能無法達到預期的效能。
3. 應用層的最佳化：在某些情況下，調整應用程式的邏輯（如針對特殊值進行特殊處理）可能是更有效的解決方案。

叢集索引的原理與優勢

叢集索引是一種特殊的資料儲存方式，將資料列儲存在索引的葉節點中。InnoDB 儲存引擎預設使用主鍵作為叢集索引，這意味著具有相鄰主鍵值的資料列會被儲存在相近的位置。

圖示說明：

此圖示展示了叢集索引的資料佈局，葉節點包含完整的資料列，而節點頁僅包含索引欄位。

內容解密：

1. 叢集索引的優勢：能夠將相關資料儲存在一起，提高檢索效率。
2. InnoDB 的叢集索引實作：預設使用主鍵作為叢集索引，若無主鍵則會選擇唯一的非空索引，或是隱式建立主鍵。
3. 隱式主鍵的潛在問題：若 InnoDB 隱式建立主鍵，可能會導致互斥鎖競爭加劇。