共用方式為

使用 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

在此情況下，您可以同時在 c1 和 c2 上建立涵蓋索引，方法為將 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。