MySQL索引策略最佳化與維護實踐

在資料函式庫效能調校中，索引策略扮演著至關重要的角色。設計良好的索引能大幅提升查詢速度，而錯誤的索引策略則可能導致效能瓶頸。本文將探討如何制定有效的索引策略，包含識別並移除冗餘或未使用的索引，以及執行定期的表維護工作，例如檢查和修復表損壞、更新統計資訊以及減少碎片化。同時，文章也將介紹如何利用工具例如 pt-duplicate-key-checker 和 MySQL 8.0 的不可見索引功能來輔助索引管理，並透過 performance_schema 和 sys schema 識別未使用的索引。此外，文章也將探討索引的型別、選擇性與基數，以及如何根據查詢需求選擇合適的索引，並透過 SHOW INDEX 命令檢查索引資訊。最後，文章將探討如何編寫高效的 SQL 查詢，避免檢索不必要的資料，並透過 EXPLAIN 命令分析查詢計劃，找出效能瓶頸並進行最佳化。

高效能索引策略與維護

在資料函式倉管理中，索引的正確使用對於查詢效能至關重要。適當的索引可以大幅提升查詢速度，但不當的索引則可能導致效能下降甚至資料損壞。本章將探討如何最佳化索引策略、識別並移除冗餘索引，以及進行必要的表維護。

瞭解索引的重要性

索引是一種資料結構，能夠幫助資料函式庫快速定位到特定的資料行。適當的索引可以加速查詢、減少磁碟 I/O 操作，從而提升整體系統效能。然而，過多的索引可能會對寫入操作（如 INSERT、UPDATE 和 DELETE）造成負擔，因為每次資料變更都需要同步更新相關索引。

識別和移除冗餘索引

冗餘索引是指那些對查詢沒有實際幫助，或者可以被其他索引覆寫的索引。這些索引不僅佔用額外的儲存空間，還會拖慢寫入操作的效能。以下是一些識別和移除冗餘索引的方法：

使用 pt-duplicate-key-checker 工具：這是 Percona Toolkit 中的一個工具，可以分析表的結構並建議重複或冗餘的索引。
MySQL 8.0 的不可見索引功能：在刪除索引之前，可以先將索引設定為不可見，以觀察系統的反應。如果系統運作正常，則可以安全地刪除該索引。

未使用的索引

除了冗餘索引外，還可能存在一些完全未被使用的索引。這些索引同樣佔用空間且無益於查詢效能。可以使用 performance_schema 和 sys 來識別未使用的索引。例如，查詢 sys.schema_unused_indexes 檢視可以列出所有未被使用的索引。

mysql> SELECT * FROM sys.schema_unused_indexes;
+
---
-
---
-
---
-
---
+
---
-
---
-
---
-
---
+
---
-
---
-
---
-
---
-
---
-
---
-
---
--+
| object_schema | object_name   | index_name                  |
+
---
-
---
-
---
-
---
+
---
-
---
-
---
-
---
+
---
-
---
-
---
-
---
-
---
-
---
-
---
--+
| sakila        | actor         | idx_actor_last_name        |
| sakila        | address       | idx_fk_city_id             |
| sakila        | address       | idx_location               |
| sakila        | payment       | fk_payment_rental          |
+
---
-
---
-
---
-
---
+
---
-
---
-
---
-
---
+
---
-
---
-
---
-
---
-
---
-
---
-
---
--+

索引和表的維護

除了最佳化索引外，定期的表維護也是必要的。主要目標包括：

檢查並修復表損壞：使用 CHECK TABLE 命令檢查表的完整性，如果發現損壞，可以使用 REPAIR TABLE 或進行「無操作」的 ALTER TABLE 操作來修復。
更新索引統計資訊：執行 ANALYZE TABLE 命令來更新表的統計資訊，確保最佳化器能夠產生最佳的查詢計劃。
減少碎片化：定期檢查和最佳化表的物理儲存結構，以減少碎片化提升效能。

修復表損壞

如果表發生損壞，可能是由於硬體問題、MySQL 或作業系統的內部錯誤。InnoDB 的設計使其對損壞具有很強的還原能力，但仍有可能發生。因此，一旦發現損壞，應立即調查原因並修復。

更新索引統計資訊

統計資訊對於最佳化器的決策至關重要。過時或不準確的統計資訊可能導致查詢計劃不佳。使用 SHOW INDEX FROM 命令可以檢查索引的基數等資訊。

mysql> SHOW INDEX FROM sakila.actor\G
*************************** 1. row ***************************
        Table: actor
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: actor_id
    Collation: A
  Cardinality: 200
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
*************************** 2. row ***************************
        Table: actor
   Non_unique: 1
     Key_name: idx_actor_last_name
 Seq_in_index: 1
  Column_name: last_name
    Collation: A
  Cardinality: 200
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:

索引的高效能最佳化與實踐

在 MySQL 資料函式庫的效能最佳化中，索引扮演著至關重要的角色。正確地使用索引可以大幅提升查詢效能，而錯誤的索引策略則可能導致效能下降。本章將探討索引的工作原理、型別以及如何選擇合適的索引來最佳化查詢。

索引的基本概念與重要性

索引是一種資料結構，能夠加速資料的檢索速度。在 MySQL 中，最常見的索引型別是 B-tree 索引。B-tree 索引能夠有效地支援範圍查詢、排序以及等值查詢。

B-tree 索引的工作原理

B-tree 索引是一種多層級的索引結構，能夠保持資料的有序性。其工作原理如下：

索引結構：B-tree 索引由多個節點組成，每個節點包含多個鍵值和指向子節點的指標。
查詢過程：當執行查詢時，MySQL 會從根節點開始，逐層向下搜尋，直到找到符合條件的葉子節點。
葉子節點：葉子節點包含了實際的資料指標或資料本身，能夠快速定位到所需的資料。

索引的選擇性與基數

索引的選擇性是指索引中不同值的數量與總記錄數的比例。選擇性越高，表示索引越能夠有效地過濾資料。基數（Cardinality）是指索引中不同值的數量，可以透過 SHOW INDEX 或 INFORMATION_SCHEMA.STATISTICS 表來檢視。

SHOW INDEX FROM table_name;

內容解密：

SHOW INDEX 命令用於顯示表的索引資訊。
Cardinality 欄位表示索引的基數，基數越高，表示索引的選擇性越好。

索引的維護與最佳化

索引統計資訊：InnoDB 會根據抽樣頁面來估計索引的統計資訊。innodb_stats_sample_pages 變數控制抽樣頁面的數量。
```
SET GLOBAL innodb_stats_sample_pages = 16;
```
內容解密：
- 增加 innodb_stats_sample_pages 的值可以提高索引統計資訊的準確性。
- 過高的值可能會增加計算統計資訊的時間。
停用不必要的統計更新：在某些情況下，停用 innodb_stats_on_metadata 可以避免不必要的統計更新，從而提高效能。
```
SET GLOBAL innodb_stats_on_metadata = 0;
```
內容解密：
- 停用 innodb_stats_on_metadata 可以減少對大表的查詢造成的負擔。
- 這在大型資料函式庫或 I/O 較慢的環境中尤其重要。

索引與資料碎片整理

B-tree 索引可能會因為資料的插入、刪除而變得碎片化，影響查詢效能。可以使用以下方法進行碎片整理：

OPTIMIZE TABLE：
```
OPTIMIZE TABLE table_name;
```
內容解密：
- OPTIMIZE TABLE 可以重新組織表的資料和索引，減少碎片化。
- 對於不支援 OPTIMIZE TABLE 的儲存引擎，可以使用 ALTER TABLE 重建表。
ALTER TABLE：
```
ALTER TABLE table_name ENGINE = InnoDB;
```
內容解密：
- 將表的儲存引擎重新設定為原來的引擎，可以重建表和索引，減少碎片化。

查詢效能最佳化

在前面的章節中，我們討論了結構最佳化和索引，這些對於高效能是必要的。但僅僅如此是不夠的——您還需要設計良好的查詢。如果您的查詢寫得不好，即使有最佳的結構設計和索引，也無法達到良好的效能。

查詢最佳化、索引最佳化和結構最佳化是相輔相成的。隨著您在MySQL中編寫查詢的經驗越來越豐富，您將學會如何設計表格和索引以支援高效查詢。同樣，您所學到的最佳結構設計也會影響您編寫的查詢型別。這個過程需要時間，因此我們鼓勵您在學習過程中回顧這三個章節。

為什麼查詢會很慢？

在嘗試編寫快速查詢之前，請記住這一切都與回應時間有關。查詢是一項任務，但它由子任務組成，而這些子任務會消耗時間。要最佳化查詢，您必須透過消除子任務、減少子任務的執行次數或使子任務執行得更快來最佳化其子任務。

一般來說，您可以透過從客戶端到伺服器的查詢序列圖來理解查詢的生命週期，查詢在伺服器上被解析、規劃和執行，然後再傳回給客戶端。執行是查詢生命週期中最重要的階段之一，它涉及大量對儲存引擎的呼叫以檢索行，以及檢索後的操作，如分組和排序。

在完成所有這些任務的過程中，查詢在網路、CPU和諸如統計、規劃、鎖定（互斥等待）等操作上花費時間，尤其是對儲存引擎的呼叫以檢索行。這些呼叫在記憶體操作、CPU操作中消耗時間，如果資料不在記憶體中，還會涉及I/O操作。根據儲存引擎的不同，可能還會涉及大量的上下文切換和/或系統呼叫。

在每種情況下，都可能因為操作被不必要地執行、執行次數過多或執行速度太慢而消耗過多的時間。最佳化的目標是透過消除或減少操作或使其執行得更快來避免這種情況。

慢速查詢基礎：最佳化資料存取

查詢效能不佳的最基本原因是它處理的資料太多。有些查詢只是需要篩選大量資料，這是無法避免的。但大多數不良查詢可以透過修改來存取更少的資料。我們發現分析效能不佳的查詢通常分為兩個步驟：

找出您的應用程式是否檢索了比需要的更多的資料。這通常意味著存取了太多的行，但也可能是存取了太多的列。
找出MySQL伺服器是否分析了比需要的更多的行。

您是否向資料函式庫請求了不需要的資料？

有些查詢請求了比需要的更多的資料，然後丟棄其中一些。這對MySQL伺服器造成了額外的負擔，增加了網路開銷，並在應用程式伺服器上消耗了記憶體和CPU資源。

以下是一些常見的錯誤：

檢索比需要的更多的行：一個常見的錯誤是假設MySQL按需提供結果，而不是計算並傳回完整的結果集。應用程式設計者經常使用諸如發出傳回許多行的SELECT陳述式，然後取得前N行並關閉結果集的技術。他們認為MySQL將提供這N行然後停止執行查詢，但MySQL實際上生成了完整的結果集。客戶端函式庫然後取得所有資料並丟棄大部分資料。最好的解決方案是在查詢中新增LIMIT子句。
從多表連線中檢索所有列：如果您想要檢索出現在電影《Academy Dinosaur》中的所有演員，不要這樣寫查詢：

SELECT * FROM sakila.actor
INNER JOIN sakila.film_actor USING(actor_id)
INNER JOIN sakila.film USING(film_id)
WHERE sakila.film.title = 'Academy Dinosaur';

這樣會傳回所有三個表格的所有列。相反，應這樣寫查詢：

SELECT sakila.actor.* FROM sakila.actor...;

檢索所有列

當您看到SELECT *時，應該始終保持懷疑。您真的需要所有列嗎？可能不需要。檢索所有列可能會阻止最佳化，如覆寫索引，並為伺服器增加I/O、記憶體和CPU開銷。

重複檢索相同的資料

如果您不小心，很容易編寫出重複從資料函式庫伺服器檢索相同資料的應用程式碼，執行相同的查詢來取得它。例如，如果您想要找出使用者的個人資料影像URL以顯示在旁邊，您可能會重複執行相同的查詢來取得它。

最佳化查詢的基本原則

最佳化查詢需要了解查詢的生命週期，並思考時間消耗在哪裡。透過消除或減少操作，或使其執行得更快，可以實作最佳化。

程式碼範例：避免檢索不必要的資料

-- 不好的例子：檢索比需要的更多的列
SELECT * FROM users WHERE id = 1;

-- 好的例子：只檢索需要的列
SELECT name, email FROM users WHERE id = 1;

內容解密：

在第一個例子中，使用SELECT *會檢索users表格中的所有列，這可能包括您不需要的列。
在第二個例子中，我們明確指定了需要的列（name和email），這樣可以減少資料傳輸量並提高查詢效率。
這種做法還可以幫助避免因表格結構變更而導致的潛在問題，例如新增或刪除列。

透過遵循這些原則和最佳實踐，您可以顯著提高MySQL查詢的效能。

最佳化查詢效能：資料存取的關鍵

在最佳化查詢效能時，首先要確保查詢只檢索所需的資料。接著，我們需要找出檢查過多資料的查詢。在 MySQL 中，衡量查詢成本最簡單的指標包括：回應時間、檢查的行數和傳回的行數。

回應時間的複雜性

回應時間並非完全可靠，因為它由服務時間和佇列時間組成。服務時間是指伺服器處理查詢所需的時間，而佇列時間則是伺服器等待某些事件的時間，例如 I/O 操作或鎖定等待。

如何評估回應時間

評估查詢的回應時間時，應考慮是否合理。可以使用 Tapio Lahdenmaki 和 Mike Leach 提出的 QUBE（快速上限估計）技術來計算查詢回應時間的上界估計。

檢查的行數與傳回的行數

檢查的行數與傳回的行數的比例可以反映查詢的效率。理想情況下，這兩個數字應該相同，但實際上很少如此。例如，在進行聯接操作時，伺服器需要存取多行才能生成結果集中的每一行。

存取型別與檢查的行數

MySQL 使用多種存取方法來查詢和傳回行。存取方法的選擇會影響檢查的行數。在 EXPLAIN 的輸出中，type 列顯示了存取方法。從全表掃描到索引掃描、範圍掃描、唯一索引查詢和常數查詢，存取方法的效率依次提高。

索引的重要性

新增適當的索引通常是解決存取方法問題的最佳方法。索引使 MySQL 能夠使用更高效的存取方法來查詢行，從而減少檢查的資料量。

示例：Sakila 樣本資料函式庫中的查詢最佳化

SELECT * FROM sakila.film_actor WHERE film_id = 1;

使用 EXPLAIN 分析查詢計劃，可以看到 MySQL 使用 ref 存取方法在 idx_fk_film_id 索引上執行查詢。

mysql> EXPLAIN SELECT * FROM sakila.film_actor WHERE film_id = 1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: film_actor
   partitions: NULL
         type: ref
possible_keys: idx_fk_film_id
          key: idx_fk_film_id
      key_len: 2
          ref: const
         rows: 10
     filtered: 100.00
        Extra: NULL

刪除索引後，存取方法變為全表掃描，MySQL 估計需要檢查 5,462 行。

mysql> ALTER TABLE sakila.film_actor DROP FOREIGN KEY fk_film_actor_film;
mysql> ALTER TABLE sakila.film_actor DROP KEY idx_fk_film_id;
mysql> EXPLAIN SELECT * FROM sakila.film_actor WHERE film_id = 1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: film_actor
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 5462
     filtered: 10.00
        Extra: Using where

WHERE 子句的應用

MySQL 可以透過三種方式應用 WHERE 子句，從最佳到最差依次是：將條件應用於索引查詢操作、在儲存引擎層丟棄不符合的行、使用 WHERE 子句在伺服器層丟棄行。

內容解密：

此段落說明瞭 MySQL 如何最佳化查詢效能，包括檢查過多資料的問題、回應時間的複雜性、檢查的行數與傳回的行數的比例、存取型別的選擇以及索引的重要性。同時，透過示例展示瞭如何使用 EXPLAIN 分析查詢計劃以及刪除索引對查詢效能的影響。最後，討論了 WHERE 子句的應用方式及其對查詢效能的影響。

玄貓 BlackCat

技術愛好者，專注於分享程式開發、雲端技術與 AI 應用的心得體會。