一般のプログラマーが習得しなければならない SQL 最適化スキル

仕事でも面接でも、基本的に SQL 最適化スキルを習得する必要があります。たとえば、 Explain を使用して SQL の実行計画を表示し、SQL を最適化します。実行計画に従っています。

Explain の使用と関連フィールドの分析に関しては、現在ではプログラマーにとって基本的に標準となっています。

いいえ、よく読んでください。

1. MySQL 実行プランとは何ですか?

実行プランをより深く理解するには、まず MySQL の基本構造を簡単に理解する必要があります。 MySQL とクエリの基本原則を学びます。

MySQL 自体の機能アーキテクチャは、アプリケーション層、論理層、物理層の 3 つの部分に分かれており、MySQL に限らず、ほとんどのデータベース製品はこのアーキテクチャに基づいて分割されています。

• アプリケーション層は主に、クライアントとの対話、リンクの確立、リンク状態の記憶、データの返し、リクエストへの応答を担当します。この層はクライアントを処理します。
• ロジック層は主に、クエリ処理、トランザクション管理、およびクエリを例に挙げたその他のデータベース機能の処理を担当します。

最初にクエリ SQL を受信すると、データベースはそれを処理するためのスレッドをすぐに割り当てます。最初のステップでは、クエリ プロセッサが SQL クエリを最適化し、最適化後に実行プランを生成します。その後、計画実行者に引き渡されて実行されます。

プラン実行者は、データを操作するために下位のトランザクションマネージャーとストレージマネージャーにアクセスする必要があり、それぞれの分業が異なります。最後に、物理層のファイルを呼び出してクエリ構造情報を取得し、最終結果はアプリケーション層に応答されます。

• 物理層。実際の物理ディスクに保存されるファイルには、主にペニー データ ファイルとログ ファイルが含まれます。

上記の説明からわかるように、実行プランの生成は SQL を実行するために不可欠なステップです 実行プランを見ることで、SQL のパフォーマンスを直感的に把握することができます 実行プラン さまざまなクエリタイプとレベルが提供されており、これらを確認してパフォーマンス分析の基礎とします。

2. 実行計画の分析方法

MySQL には、SQL の実行計画を視覚的に表示するための Explain キーワードが用意されています。

explain は、MySQL がインデックスを使用して選択ステートメントを処理し、テーブルを結合する方法を示しています。これは、より適切なインデックスを選択し、より最適化されたクエリ ステートメントを作成するのに役立ちます。

以下では、次のように Explain を使用してクエリを作成します:

mysql> explain select * from payment;
+----+-------------+---------+------------+------+---------------+------+---------+------+-------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+-------+----------+-------+
|  1 | SIMPLE      | payment | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 16086 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------+------+---------+------+-------+----------+-------+
1 row in set, 1 warning (0.01 sec)

クエリ構造には 12 列があります。実行計画を理解するには、各列の意味を理解することが重要です。表の形式で説明します。

DEPENDENT UNION:UNION中的第二個或後面的SELECT語句,取決於外面的查詢UNION RESULTDEPENDENT SUBQUERYDERIVED:導出表格的SELECT(FROM子句的子查詢)table輸出的行所引用的表partitions指出MySQL能使用哪個索引在該表中找到行顯示MySQL實際決定使用的鍵(索引)。如果沒有選擇索引,鍵是NULL。 顯示MySQL決定使用的鍵長度。如果鍵是NULL,則長度為NULL。在不損失精確性的情況下，長度越短越好#顯示使用哪個列或常數與key一起從表中選擇行。 顯示MySQL認為它執行查詢時必須檢查的行數。多行之間的資料相乘可以估算要處理的行數。 顯示了透過條件過濾出的行數的百分比估計值。 該欄位包含MySQL解決查詢的詳細資料

列名	說明
##id
	##SELECT識別符，這是SELECT的查詢序號。 select_typeSELECT類型,可以為以下任何一種:SIMPLE:簡單SELECT(不使用UNION或子查詢)PRIMARY:最外面的SELECTUNION:UNION中的第二個或後面的SELECT語句
:UNION 的結果SUBQUERY:子查詢中的第一個SELECT	:子查詢中的第一個SELECT,取決於外面的查詢
	如果查詢是基於分區表的話，顯示查詢將訪問的分區。 type 連接類型。下面給出各種聯結類型,按照從最佳類型到最壞類型進行排序:system :表僅有一行(=系統表)。這是const聯接類型的一個特例。 const :表最多有一個符合行,它將在查詢開始時被讀取。因為只有一行,在這行的列值可被最佳化器剩餘部分認為是常數。 const表很快,因為它們只讀取一次!eq_ref :對於每個來自於前面的表的行組合,從該表中讀取一行。這可能是最好的聯接類型,除了const類型。 ref :對於每個來自於前面的表的行組合,所有有匹配索引值的行將從這張表中讀取。 ######ref_or_null###:這個聯結類型如同ref,但是加入了MySQL可以特別搜尋包含NULL值的行。 ######index_merge###:此聯結類型表示使用了索引合併最佳化方法。 ######unique_subquery###:該類型取代了下面形式的IN子查詢的ref: value IN (SELECT primary_key FROM single_table WHERE some_expr) unique_subquery是一個索引函數,可以完全替換子查詢,效率更高。 ###index_subquery:該聯結類型類似unique_subquery。可以替換IN子查詢,但只適合下列形式的子查詢中的非唯一索引: value IN (SELECT key_column FROM single_table WHERE some_expr) #range:只檢索給定範圍的行,使用一個索引來選擇行(建議，最差也要這個等級)。 index:該聯結類型與ALL相同,除了只有索引樹被掃描。這通常比ALL快,因為索引檔通常比資料檔小。 ALL:對於每個來自於先前的表的行組合,進行完整的表掃描，說明查詢就需要優化了。一般來說，得確保查詢至少達到range級別，最好能達到ref。 以上system最好，依序遞減，ALL最差
##possible_keys
key
key_len
ref
rows
filtered
Extra	Distinct:MySQL發現第1個匹配行後,停止為目前的行組合搜尋更多的行。 Select tables optimized away MySQL根本沒有遍歷表或索引就回傳資料了，表示已經最佳化到不能再最佳化了 Not exists:MySQL能夠對查詢進行LEFT JOIN最佳化,發現1個符合LEFT JOIN標準的行後,不再為前面的行組合在該表內檢查更多的行。 range checked for each record (index map: #):MySQL沒有發現好的可以使用的索引,但發現如果來自前面的表的列值已知,可能部分索引可以使用。 Using filesort:MySQL需要額外的一次傳遞,以找出如何按排序順序檢索行，說明查詢就需要優化了。 Using index:從只使用索引樹中的資訊而不需要進一步搜尋讀取實際的行來檢索表中的列資訊。 Using temporary:為了解決查詢,MySQL需要建立一個暫存表來容納結果，說明查詢就需要最佳化了。 Using where:WHERE 子句用於限制哪一個行符合下一個資料表或傳送到客戶。 Using sort_union(...), Using union(...), Using intersect(...):這些函式說明如何為index_merge聯結型別合併索引掃描。 Using index for group-by:類似於存取表的Using index方式,Using index for group-by表示MySQL發現了一個索引,可以用來查詢GROUP BY或DISTINCT查詢的所有列,而不要額外搜尋硬碟存取實際的表。

根據上述表格，可以在執行計劃分析上提供很好的幫助。

注意：如果是為了應付面試，最好是能背下來，不能全背下來的情況，也要能說上個123，然後就說，記不得這麼多，可以翻閱相關文件來對照著優化SQL。

以上が一般のプログラマーが習得しなければならない SQL 最適化スキルの詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。