適用於 MySQL 的 Azure 資料庫 - 彈性伺服器疑難排解的最佳做法

發行項
08/15/2024

適用於：適用於 MySQL 的 Azure 資料庫 - 單一伺服器適用於 MySQL 的 Azure 資料庫 - 彈性伺服器

重要

適用於 MySQL 的 Azure 資料庫單一伺服器位於淘汰路徑上。強烈建議您升級至適用於 MySQL 的 Azure 資料庫彈性伺服器。如需移轉至適用於 MySQL 的 Azure 資料庫 - 彈性伺服器的詳細資訊，請參閱適用於 MySQL 的 Azure 資料庫單一伺服器會發生什麼事？

使用下方各節的內容來讓您的適用於 MySQL 的 Azure 資料庫彈性伺服器資料庫順利執行，並將這項資訊當作指導原則，以確保結構描述以最佳方式設計，並為您的應用程式提供最佳效能。

請參閱索引的數目

在忙碌的資料庫環境中，您可能會觀察到高 I/O 使用量，而這可能是資料存取模式不佳的指標。未使用的索引可能會因為消耗磁碟空間和快取而對效能產生負面影響，並讓寫入作業變慢 (INSERT/DELETE/UPDATE)。未使用的索引非必要地消耗更多的儲存空間，並增加其備份大小。

在移除任何索引之前，請務必收集足夠資訊，確認已不再使用。驗證可協助您避免不慎移除會影響到每季或每年執行查詢的重要索引。此外，請務必考慮是否使用索引來強制執行唯一性或排序。

注意

請記得定期檢閱索引，並根據資料表資料的任何修改，來執行任何必要的更新。

SELECT object_schema, object_name, index_name FROM performance_schema.table_io_waits_summary_by_index_usage WHERE index_name IS NOT NULL AND count_star = 0 ORDER BY object_schema, object_name;

(或)

use information_schema; select tables.table_name, statistics.index_name, statistics.cardinality, tables.table_rows from tables join statistics on (statistics.table_name = tables.table_name and statistics.table_schema = '<YOUR DATABASE NAME HERE>' and ((tables.table_rows / statistics.cardinality) > 1000));

列出伺服器上最繁忙的索引

下列查詢的輸出提供資料庫伺服器上所有資料表和架構中最常使用之索引的相關資訊。這項資訊有助於識別針對每個索引的寫入與讀取比率，以及讀取的延遲號碼，以及個別寫入作業，這表示基礎表和相依查詢需要進一步調整。

SELECT 
object_schema AS table_schema, 
object_name AS table_name, 
index_name, count_star AS all_accesses, 
count_read, 
count_write, 
Concat(Truncate(count_read / count_star * 100, 0), ':', 
Truncate(count_write / count_star * 100, 0)) AS read_write_ratio, 
 count_fetch AS rows_selected , 
 count_insert AS rows_inserted, 
 count_update AS rows_updated, 
 count_delete AS rows_deleted, 
 Concat(Round(sum_timer_wait / 1000000000000, 2), ' s') AS total_latency , 
 Concat(Round(sum_timer_fetch / 1000000000000, 2), ' s') AS select_latency, 
 Concat(Round(sum_timer_insert / 1000000000000, 2), ' s') AS insert_latency, 
Concat(Round(sum_timer_update / 1000000000000, 2), ' s') AS update_latency, 
 Concat(Round(sum_timer_delete / 1000000000000, 2), ' s') AS  delete_latency 
FROM performance_schema.table_io_waits_summary_by_index_usage 
WHERE index_name IS NOT NULL AND count_star > 0 
ORDER BY sum_timer_wait DESC

請檢閱主索引鍵的設計

適用於 MySQL 的 Azure 資料庫彈性伺服器對所有非暫存資料表會使用 InnoDB 儲存引擎。使用 InnoDB 時，資料會在使用 B 型樹狀結構時儲存於叢集索引內。資料表會根據主索引鍵值來實際組織，這表示資料列會以主索引鍵的排序儲來儲存。

InnoDB 資料表中，每個次要索引鍵都涵蓋資料儲存於主索引鍵值的指標。換句話說，次要索引輸入目涵蓋進入所指向的主索引鍵值複本。因此，主索引鍵選擇對資料表中，其儲存體負荷量有著直接影響。

若金鑰衍生自實際資料 (例如使用者名稱、電子郵件、SSN 等)，則稱之為 自然索引鍵。若索引鍵是人工而非衍生自資料 (例如，自動遞增的整數)，則稱之為綜合索引鍵或 Surrogate 索引鍵。

通常建議避免使用自然主索引鍵。這些索引鍵通常非常寬，而且涵蓋來自一或多個資料行的長值。如此一來，當主要金鑰值複製至每個次要金鑰輸入時，則會產生嚴重的儲存體額外負荷。此外，自然索引鍵通常不會遵循預先決定的排序，這會大幅降低插入或更新資料列時的效能，進而引發頁面片段。為避免這些問題，請使用單調增加 Surrogate 索引鍵，而不是自然索引鍵。自動遞增 (大) 整數資料行為單調遞增 Surrogate 索引鍵的良好範例。若您需要特定資料行組合，則必須是唯一的，請將這些資料行宣告為唯一的次要索引鍵。

在建置應用程式的初始階段，您可能不會事先想著資料表開始接近 20 億個資料列所需的時間。因此，您可以選擇使用帶正負號的 4 位元組整數做為識別碼 (主索引鍵) 資料行的資料類型。請務必檢查所有資料表主索引鍵，並切換為使用 8 位元組整數 (BIGINT) 資料行，以容納大量或成長的可能性。

注意

如需資料類型及其最大值的詳細資訊，在「MySQL 參考手冊」中，請參閱資料類型。

請使用涵蓋索引

上一節說明 MySQL 中的索引如何組織為 B 型樹狀結構，以及在叢集索引中，該分葉節點包括基礎資料表的資料分頁。次要索引的 B 型樹狀結構與叢集索引相同，您亦可以在資料表或檢視上，使用叢集索引或堆積來定義。每個次要索引資料列皆包括非叢集索引碼的值與資料列的定位器。此定位器指向含有鍵值之叢集索引或堆積中的資料列。因此，涉及次要索引的任何查閱都必須從根節點開始瀏覽至正確之分葉節點，以取得主索引鍵值。接著，系統會在主索引鍵的索引上執行隨機 IO 讀取 (再次從根節點瀏覽至正確的分葉節點)，以取得資料列。

為避免在主索引鍵的索引上讀取此額外的隨機 IO 來取得資料列，請使用涵蓋索引，其中包括查詢必要的所有欄位。一般而言，使用此方法有助於 I/O 繫結並快取的工作負載。因此，最佳做法是使用涵蓋索引，因其符合記憶體，而且比掃描所有資料列更具效率。

例如，您用以嘗試尋找在 2000 年 1 月 1 日之後加入該公司的全部員工資料表。

mysql> show create table employee\G
*************************** 1. row ***************************
       Table: employee
Create Table: CREATE TABLE `employee` (
  `empid` int(11) NOT NULL AUTO_INCREMENT,
  `fname` varchar(10) DEFAULT NULL,
  `lname` varchar(10) DEFAULT NULL,
  `joindate` datetime DEFAULT NULL,
  `department` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`empid`)
  ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)`

`mysql> select empid, fname, lname from employee where joindate > '2000-01-01';

若您在此查詢上執行 EXPLAIN 計畫，則會發現目前並未使用任何索引，而是單獨使用 where 子句來篩選員工記錄。

mysql> EXPLAIN select empid, fname, lname from employee where joindate > '2000-01-01'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: employee
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3
     filtered: 33.33
        Extra: Using where
1 row in set, 1 warning (0.01 sec)

不過，若要新增涵蓋 where 子句中資料行的索引以及投影資料行，則您會看到索引用於尋找資料行的速度更快且更具效率。

mysql> CREATE INDEX cvg_idx_ex ON employee (joindate, empid, fname, lname);

現在，若您在相同的查詢上執行 EXPLAIN 計畫，「Using Index」值會出現在 [額外] 欄位，這就表示 InnoDB 會使用我們稍早建立的索引來執行查詢，並將其確認為涵蓋索引。

mysql> EXPLAIN select empid, fname, lname from employee where joindate > '2000-01-01'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: employee
   partitions: NULL
         type: range
possible_keys: cvg_idx_ex
          key: cvg_idx_ex
      key_len: 6
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using where; Using index
1 row in set, 1 warning (0.01 sec)

注意

請務必選擇涵蓋索引中資料行的正確排序，以正確提供查詢。一般規則是先選擇資料行來篩選 (WHERE 子句)，然後排序/分組 (ORDER BY 和 GROUP BY)，最後則是資料投影 (SELECT)。

在先前的範例中，我們發現查詢的涵蓋索引可提供更具效率的記錄擷取路徑，並將高度並行資料庫環境中的效能加以優化。

下一步

若想知道是否有可解答您最重要問題對等的答案，或是想張貼或回答問題，請造訪 Stack Overflow。

共用方式為

適用於 MySQL 的 Azure 資料庫 - 彈性伺服器疑難排解的最佳做法

請參閱索引的數目

列出伺服器上最繁忙的索引

請檢閱主索引鍵的設計

請使用涵蓋索引

下一步

意見反應

其他資源