掌握MySQL和MariaDB中的索引：最佳化之路

MySQL 和 MariaDB 都無縫利用平衡樹（B 樹）索引的高效性來最佳化資料操作。這種共享索引機制可確保快速的資料檢索，提升查詢效能，並最大限度地減少磁碟輸入/輸出 (I/O)，從而帶來更靈敏、更高效的資料庫體驗。

本文將深入探討索引，指導您建立索引，並分享在 MySQL 和 MariaDB 資料庫中更有效地使用索引的技巧。

什麼是索引？

當您在 MySQL 資料庫中查詢特定資訊時，查詢會搜尋資料庫表中的每一行，直到找到正確的行。這可能需要很長時間，尤其是在資料庫規模龐大的情況下。

資料庫管理員使用索引來加快資料檢索過程並最佳化查詢效率。索引構建了一種資料結構，透過系統地組織資料來最大限度地減少必須搜尋的資料量，從而更快、更有效地執行查詢。

假設您想在以下 Customer 表中查詢名字為 Ava 的客戶：

MySQL Workbench 的客戶表預覽

在 first_name 列中新增 B 樹索引可以建立一個結構，以便更高效地搜尋所需資訊。該結構類似於一棵樹，根節點位於頂部，向下分支到底部的葉節點。

它類似於一棵組織良好的樹，其中每一層都根據資料的排序順序引導搜尋。

下圖顯示了 B 樹索引的搜尋路徑：

B 樹客戶表

Ava 列在第一位，William 列在最後，按字母升序排列——B 樹就是按照字母升序排列姓名的。B 樹系統將列表的中間值指定為根節點。由於 Michael 位於字母列表的中間，因此它就是根節點。然後，樹向外分支，Michael 的左右兩側都有值。

隨著您沿著樹的層級向下移動，每個節點都會提供更多鍵（直接連結到原始資料行），以引導搜尋按字母順序排列的姓名。然後，您會在葉節點找到每個客戶名字的資料。

搜尋從比較 Ava 和根節點 Michael 開始。在確定 Ava 在字母順序上排在 Michael 之前後，搜尋向左移動。它向下移動到左側子節點（ Emily ），然後再次向左移動到 Daniel，再向左移動到 Ava，最後到達包含 Ava 資訊​​的葉節點。

B 樹就像一個簡化的導航系統，可以有效地引導搜尋到特定位置，而無需檢查資料集中的每個名稱。這就像在一本精心排列的目錄中導航，只需跟隨精心佈置的路標，就能直接到達目的地。

索引型別

索引有多種型別，可用於各種用途。下面我們將討論這些不同的型別。

1. 單級索引

單級索引（或稱平面索引）將索引鍵對映到表資料。索引中的每個鍵都對應一個錶行。

customer_id 列是 Customer 表中的主鍵，用作單級索引。該鍵標識每個客戶，並關聯他們在表中的資訊。

customer_id 鍵與單個客戶詳細資訊之間的關係非常簡單。單級索引在表包含少量行或列且不同值較少的情況下非常有效。例如，status 或 category 等列就是很好的選擇。

對於基於單個列定位特定行的簡單查詢，請使用單級索引。對於較小的資料集，其實現簡單、直接且高效。

2. 多級索引

與用於有組織的資料檢索的單級索引不同，多級索引採用分層結構。它們具有多層引導。頂級索引將搜尋定向到較低階別的索引，依此類推，直到儲存資料的葉級。這種結構減少了搜尋過程中所需的比較次數。

考慮一個包含 address 和 customer_id 列的多級索引。

第一層組織地址。第二層（在每個地址內）進一步組織客戶 ID。

這種組織方式非常適合需要有序搜尋層次結構的更大規模資料集。對於像 last_name 這樣具有中等基數（特定列中資料值的唯一性）的列，它也非常有用。

3. 聚集索引

MySQL 中的聚集索引決定了索引的邏輯順序以及資料在表中的順序。如果將聚集索引應用於 Customer 表中的 customer_id 列，則行將根據該列的值進行排序。這意味著表中資料的順序反映了聚集索引的順序，從而透過減少磁碟 I/O 來提高特定模式的資料檢索效能。

當資料檢索模式與客戶 ID 的順序一致時，此策略非常有效。它也適用於具有高基數的列，例如 customer_id。

雖然聚集索引在特定模式下的資料檢索效能方面具有優勢，但需要注意其潛在的缺點。基於聚集索引對行進行排序會影響插入和更新操作的效能，尤其是在插入或更新模式與聚集索引的順序不一致的情況下。這是因為新資料的插入或更新必須以保持排序順序的方式進行，這會導致額外的開銷。

4. 非聚集索引

非聚集索引賦予資料庫結構更大的靈活性。假設您在 email 列上使用非聚集索引。與聚集索引不同，它不會更改表中條目的順序。

相反，它會構建一個新的結構，將鍵（在本例中為電子郵件地址）對映到資料行。當您在資料庫中查詢特定的電子郵件地址時，非聚集索引會將搜尋直接引導到相關行，而無需依賴表的順序。

非聚集索引的靈活性是其主要優勢。它們能夠高效地搜尋多列，而無需對儲存的資料進行排序。該系統使非聚集索引用途廣泛，因為它們可以處理不遵循表主順序的查詢。

當資料檢索模式與字母順序不同，並且對於基數中等到高的列（例如電子郵件）時，非聚集索引非常有用。

如何建立索引

我們已經從總體上回顧了索引的概念，現在讓我們回顧一些使用 MySQL Workbench 建立索引的實際示例。

先決條件

要繼續學習，您需要：

• 一個 MySQL 資料庫（相容 MariaDB）
• 一些 SQL 和 MySQL 經驗
• MySQL Workbench

如何建立客戶表

1. 啟動 MySQL Workbench 並連線到您的 MySQL 伺服器。
2. 執行以下 SQL 查詢來建立 Customer 表：

   CREATE TABLE Customer (
   customer_id INT PRIMARY KEY,
   first_name VARCHAR(50),
   last_name VARCHAR(50),
   email VARCHAR(100),
   phone_number VARCHAR(15),
   address VARCHAR(255)
   );

3. 插入以下資料：

   -- Adding Data to the Customer Table
   INSERT INTO Customer (customer_id, first_name, last_name, email, phone_number, address)
   VALUES
   (1, 'John', 'Doe', 'john.doe@email.com', '123-456-7890', '123 Main St'),
   (2, 'Jane', 'Smith', 'jane.smith@email.com', '987-654-3210', '456 Oak Ave'),
   (3, 'Robert', 'Johnson', 'robert.johnson@email.com', '111-222-3333', '789 Pine Rd'),
   (4, 'Emily', 'Williams', 'emily.williams@email.com', '555-666-7777', '101 Cedar Ln'),
   (5, 'Michael', 'Brown', 'michael.brown@email.com', '444-555-8888', '202 Elm St'),
   (6, 'Sophia', 'Davis', 'sophia.davis@email.com', '999-888-7777', '303 Maple Ave'),
   (7, 'Daniel', 'Miller', 'daniel.miller@email.com', '777-888-9999', '404 Birch Rd'),
   (8, 'Olivia', 'Jones', 'olivia.jones@email.com', '333-222-1111', '505 Pine St'),
   (9, 'William', 'Wilson', 'william.wilson@email.com', '111-333-5555', '606 Oak Ln'),
   (10, 'Ava', 'Moore', 'ava.moore@email.com', '888-777-6666', '707 Cedar Ave');

單級索引

在 MySQL 和 MariaDB 中，最佳化查詢效能的一種策略是使用單級索引。

要向 Customer 表新增單級索引，請使用 CREATE INDEX 語句：

-- Creating a Single-Level Index on "customer_id"
CREATE INDEX idx_customer_id ON Customer(customer_id);

執行成功後，資料庫將返回以下程式碼來確認索引建立：

0 row(s) affected Records: 0  Duplicates: 0 Warnings: 0

現在，資料庫可以以最佳方式處理基於 customer_id 列值過濾資料的查詢，從而大幅提升效率。

多級索引

MySQL 和 MariaDB 提供多級索引，超越了單列索引。這些索引跨越多個級別或列，將多個列的值組合到一個索引中，從而提高查詢執行效率。

使用以下程式碼在 MySQL 或 MariaDB 中建立多級索引，重點關注 address 和 customer_id 列：

-- Creating a Multi-Level Index based on "address" and "customer_id"
CREATE INDEX idx_address_customer_id ON Customer(address, customer_id);

策略性地使用多級索引可以顯著提升查詢效能，尤其是在處理多列資料時。

聚集索引

除了單級索引和多級索引之外，MySQL 和 MariaDB 還使用聚集索引。這是一種動態工具，它透過將資料行與索引指標的順序對齊來增強資料庫效能。

例如，將聚集索引應用於 Customer 表中的 customer_id 列可以對齊客戶 ID 的順序。

-- Creating a Clustered Index on "customer_id"
CREATE CLUSTERED INDEX idx_customer_id_clustered ON Customer(customer_id);

由於最佳化了資料順序，此策略顯著提升了特定模式的資料檢索效能，同時減少了磁碟 I/O。

非聚集索引

非聚集索引可以根據列最佳化查詢，而無需強制資料按特定順序排列。在 MySQL 和 MariaDB 中，無需指定索引是否為非聚集索引。

表架構本身就隱含了這一點。只有主鍵或第一個非空唯一鍵可以是聚集索引。表的其他索引均隱式為非聚集索引。以下是非聚集索引的示例：

-- Creating a Non-clustered Index on "email"
CREATE INDEX idx_email_non_clustered ON Customer(email);

非聚集索引允許高效地搜尋多列，從而構建更通用、響應速度更快的資料庫。

最佳實踐和要點

當處理值範圍較小的列（例如狀態或類別）時，請選擇單級索引。當處理值範圍較大的列（例如電子郵件）時，請使用多級索引和非聚集索引。

在聚集索引和非聚集索引之間進行選擇時，您首選的資料檢索模式至關重要。對於聚集索引，請選擇基數較高的列（例如客戶 ID）。對於非聚集索引，請選擇基數中等到較高的列（例如電子郵件）。

如何最佳化索引

為了提升索引的效能，您可以使用一些實用的策略，例如覆蓋索引和刪除冗餘索引。

1. 覆蓋索引

覆蓋索引透過建立覆蓋所有必要資料的索引來提高查詢效能。覆蓋索引是指索引包含完成查詢所需的所有列，避免了訪問資料行的需要。

-- Create a Covering Index on "first_name" and "last_name"
CREATE INDEX idx_covering_name ON Customer(first_name, last_name);

2. 刪除冗餘索引

刪除冗餘索引，但務必謹慎，因為刪除索引可能會影響某些查詢效能。

-- Remove an Unnecessary Index
DROP INDEX idx_unnecessary_index ON Customer;

定期檢查並刪除冗餘索引，以確保資料庫結構精簡高效。

3. 避免過度索引

避免過度索引等常見陷阱。雖然索引可以提升查詢效能，但建立過多索引可能會降低效能。務必保持平衡，避免過度索引，因為過度索引可能會導致儲存需求增加和效能下降。

4. 分析查詢模式

在建立索引之前忽略查詢模式分析也是一個常見陷阱。瞭解經常執行的查詢，並重點關注 WHERE 子句或 JOIN 條件中使用的索引列，對於實現最佳效能至關重要。

小結

本文探討了 MySQL 和 MariaDB 的索引，重點介紹了 B 樹機制的效率。文章涵蓋了索引基礎知識和各種索引型別（單級、多級、聚集和非聚集）。