在 MySQL 中，索引 是通過特定的數據結構來加速查詢操作。MySQL 支持多種類型的索引，其中 B+ 樹索引 是最常見的一種。

1. B+ 樹索引（B+ Tree Index）

B+ 樹的特點：

1. 所有數據存儲在葉子節點：

   • 所有的數據都存儲在 B+ 樹的葉子節點中，非葉子節點僅存儲索引。

2. 葉子節點鏈表：

   • B+ 樹的葉子節點通過鏈表連接，使得在進行範圍查詢時，可以快速遍歷所有匹配的數據。

3. 多路平衡：

   • B+ 樹是多路查找樹，每個節點可以有多個子節點，保持樹的高度較低，從而提高查詢效率。

4. 高度平衡：

   • B+ 樹保持高度平衡，所有葉子節點都位於樹的同一層級，確保查找操作的時間複雜度為 O(log n)。

B+ 樹的工作原理：

• 查找操作：查找時，通過從根節點開始，按照鍵值的大小比較，逐層向下查找，最終到達葉子節點。在葉子節點中，可以通過鏈表進行範圍查詢。
• 插入和刪除操作：插入和刪除操作會保持樹的平衡，可能會引起節點的分裂或合併，保證 B+ 樹始終是平衡的，從而保證查找的效率。

B+ 樹和 B 樹的區別：

• 數據存儲位置：在 B 樹中，數據存儲在非葉子節點和葉子節點，而 B+ 樹的所有數據都存儲在葉子節點。
• 葉子節點鏈接：B+ 樹的葉子節點通過鏈表連接，支持範圍查詢，而 B 樹沒有這種結構。
• 查詢性能：B+ 樹的查詢性能通常優於 B 樹，尤其是在範圍查詢時，B+ 樹具有更高的效率。

2. 適合加索引的情況

加索引可以顯著提高查詢效率，特別是在處理大數據量的表和複雜查詢時。以下是幾種適合加索引的情況：

1. 查詢條件中使用 JOIN 操作：

   • 如果兩個表通過某個列進行連接查詢，這個列應該加上索引。通過索引可以加速連接操作，避免全表掃描。

   SELECT * FROM orders o JOIN customers c ON o.customer_id = c.customer_id;

   在這種情況下，orders.customer_id 和 customers.customer_id 應該加索引。

2. 需要進行分組操作（GROUP BY）：

   • 當查詢需要進行分組時（如 COUNT()、SUM() 等聚合函數），加上索引可以加速分組操作，尤其是在分組列上加索引。

   SELECT department, COUNT(*) FROM employees GROUP BY department;

3. 涉及到 ORDER BY 的查詢：

   • 如果查詢結果需要排序，在排序的列上加索引可以提高排序效率，避免進行全表掃描和排序。

   SELECT * FROM employees ORDER BY salary DESC;

4. 對於具有唯一性的字段（如用户名、郵箱等）：

   • 唯一性字段上加索引可以加速查詢，並避免重複值，保證數據一致性。

   CREATE UNIQUE INDEX idx_email ON users (email);

5. 用於防止全表掃描的查詢：

   • 對於經常在 WHERE 子句中查詢的字段，尤其是範圍較小的字段，應該考慮加索引來避免全表掃描。

   SELECT * FROM users WHERE age = 25;

6. 使用 DISTINCT 查詢：

   • 如果查詢包含 DISTINCT 操作，索引可以幫助快速排除重複記錄，提高查詢效率。

   SELECT DISTINCT department FROM employees;

7. 外鍵列：

   • 外鍵列的索引有助於提高查詢性能，並在更新或刪除父表記錄時加速約束檢查。

8. 經常用作過濾條件的列：

   • 如果某列經常作為過濾條件出現，並且該列的數據基數較高（即非唯一性且具有較好的選擇性），應該考慮加索引。

3. 什麼時候索引會失效？

雖然索引能夠提高查詢效率，但在某些情況下，索引會失效。以下是幾種常見的情況：

1. 使用 OR 操作符：

   • 如果查詢條件中使用了 OR 操作符，MySQL 可能會選擇其中無法使用索引的條件，導致索引失效。

   SELECT * FROM users WHERE age = 25 OR name = 'Alice';

2. 使用 LIKE 進行模糊查詢（尤其是以 % 開頭）：

   • 對於 LIKE '%text%' 這種查詢模式，索引會失效，因為 MySQL 無法通過索引定位模糊匹配的位置。

   SELECT * FROM users WHERE name LIKE '%Alice%';

3. 使用不等號 <> 或 !=：

   • 使用不等於操作符通常會導致索引失效，因為 MySQL 不能直接通過索引來確定不等的條件。

   SELECT * FROM users WHERE age <> 30;

4. 數據類型不匹配：

   • 當查詢條件中的數據類型與列的數據類型不一致時，可能導致索引失效，尤其是在比較字符串與數字時。

   SELECT * FROM users WHERE age = '30';  -- age 是 INT 類型

5. 函數操作：

   • 如果在查詢條件中使用了函數，MySQL 將無法使用索引，因為函數會影響列的值，使得索引無法有效地參與查詢。

   SELECT * FROM users WHERE YEAR(birth_date) = 1990;

6. NULL 值查詢：

   • 查詢含有 NULL 值的字段時，索引可能失效，尤其是在查詢條件中使用 IS NULL 或 IS NOT NULL 時。

   SELECT * FROM users WHERE birth_date IS NULL;

7. 部分列的聯合索引：

   • 在使用多列索引時，如果查詢條件沒有包含索引的最左邊部分，可能導致索引失效。

   CREATE INDEX idx_name_age ON users (name, age);
   SELECT * FROM users WHERE age = 30;  -- 沒有涉及 name 列，索引失效

總結

• B+ 樹索引：適用於大多數查詢，包括精確查找、範圍查找和排序操作。它是 MySQL 默認的索引類型，支持高效的查詢。

• 適合加索引的情況：

  • 常用的查詢條件列，特別是用於 WHERE、JOIN、ORDER BY、GROUP BY 等的列。
  • 大數據量表、需要排序或唯一性要求的場景。
  • 經常進行連接查詢、分組操作、排序和去重操作的列。

• 索引失效的情況：

  • 使用 OR、LIKE（以 % 開頭）、函數操作、不等於操作等。