共用方式為


使用 EXPLAIN 分析適用於 MySQL 的 Azure 資料庫 - 彈性伺服器中的查詢效能

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

重要

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

EXPLAIN 是一種便利工具,可協助您最佳化查詢。 您可以使用 EXPLAIN 陳述式,取得 SQL 陳述式如何執行的相關資訊。 下列顯示執行 EXPLAIN 陳述式的範例輸出。

mysql> EXPLAIN SELECT * FROM tb1 WHERE id=100\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb1
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 995789
     filtered: 10.00
        Extra: Using where

在此範例中,key 的值是 NULL,這表示適用於 MySQL 的 Azure 資料庫彈性伺服器找不到針對查詢最佳化的索引。 因此,其會執行完整資料表掃描。 讓我們在 ID 資料行上新增索引,然後再次執行 EXPLAIN 陳述式來最佳化此查詢。

mysql> ALTER TABLE tb1 ADD KEY (id);
mysql> EXPLAIN SELECT * FROM tb1 WHERE id=100\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb1
   partitions: NULL
         type: ref
possible_keys: id
          key: id
      key_len: 4
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL

現在,輸出顯示適用於 MySQL 的 Azure 資料庫彈性伺服器會使用索引將列數限定為 1,因而大幅縮短了搜尋時間。

涵蓋索引

涵蓋索引包含查詢的所有資料行,這會減少從資料表中進行值擷取。 下列 GROUP BY 陳述式和相關輸出說明這點。

mysql> EXPLAIN SELECT MAX(c1), c2 FROM tb1 WHERE c2 LIKE '%100' GROUP BY c1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb1
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 995789
     filtered: 11.11
        Extra: Using where; Using temporary; Using filesort

輸出顯示適用於 MySQL 的 Azure 資料庫彈性伺服器不會使用任何索引,因為沒有適當的索引可用。 輸出也顯示 Using temporary; Using filesort,這指出適用於 MySQL 的 Azure 資料庫彈性伺服器建立了暫存資料表以滿足 GROUP BY 子句。

只在資料行 c2 建立索引並不會產生差異,適用於 MySQL 的 Azure 資料庫彈性伺服器仍然需要建立暫存資料表:

mysql> ALTER TABLE tb1 ADD KEY (c2);
mysql> EXPLAIN SELECT MAX(c1), c2 FROM tb1 WHERE c2 LIKE '%100' GROUP BY c1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb1
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 995789
     filtered: 11.11
        Extra: Using where; Using temporary; Using filesort

在此情況下,您可以同時在 c1c2 上建立涵蓋索引,方法為將 c2 的值直接加入索引中,這會消除進一步的資料查閱。

mysql> ALTER TABLE tb1 ADD KEY covered(c1,c2);
mysql> EXPLAIN SELECT MAX(c1), c2 FROM tb1 WHERE c2 LIKE '%100' GROUP BY c1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb1
   partitions: NULL
         type: index
possible_keys: covered
          key: covered
      key_len: 108
          ref: NULL
         rows: 995789
     filtered: 11.11
        Extra: Using where; Using index

如上述 EXPLAIN 的輸出所示,適用於 MySQL 的 Azure 資料庫彈性伺服器現在會使用涵蓋性索引,並避免必須建立暫存資料表。

結合的索引

結合的索引包含多個資料行的值,可被視為依串連的索引資料行值排序的資料列陣列。 此方法適用於 GROUP BY 陳述式。

mysql> EXPLAIN SELECT c1, c2 from tb1 WHERE c2 LIKE '%100' ORDER BY c1 DESC LIMIT 10\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb1
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 995789
     filtered: 11.11
        Extra: Using where; Using filesort

適用於 MySQL 的 Azure 資料庫彈性伺服器會執行速度緩慢的檔案排序作業,尤其是在必須排序許多資料列時。 若要最佳化此查詢,請在這兩個要排序的資料行上建立結合的索引。

mysql> ALTER TABLE tb1 ADD KEY my_sort2 (c1, c2);
mysql> EXPLAIN SELECT c1, c2 from tb1 WHERE c2 LIKE '%100' ORDER BY c1 DESC LIMIT 10\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb1
   partitions: NULL
         type: index
possible_keys: NULL
          key: my_sort2
      key_len: 108
          ref: NULL
         rows: 10
     filtered: 11.11
        Extra: Using where; Using index

EXPLAIN 陳述式的輸出現在顯示,適用於 MySQL 的 Azure 資料庫彈性伺服器會使用結合的索引,以避免額外的排序,因為索引已排序。

推論

您可以搭配不同類型的索引使用 EXPLAIN 來大幅提升效能。 在資料表上具有索引不一定表示適用於 MySQL 的 Azure 資料庫彈性伺服器可以將其用於您的查詢。 請一律使用 EXPLAIN 驗證您的假設,並使用索引將查詢最佳化。

下一步

  • 若想知道是否有人可解答您最重要的問題,或是要張貼或回答問題,請造訪 Stack Overflow