SQL文の最適化の一般的な手順の詳細な説明

序文

この記事では主に、SQL ステートメントの最適化の一般的な手順について説明します。これは、参考と学習のために共有されています。さっそく、詳細な紹介を見てみましょう。

1. show statusコマンドを使用して、さまざまなSQL文の実行頻度を把握する

mysql クライアントが正常に接続された後、 show [session|global] statusコマンドでサーバーのステータス情報を取得できます。また、オペレーティングシステムでmysqladmin extend-statusコマンドを使用してこれらのメッセージを取得することもできます。

show statusコマンドは、セッション (デフォルト) またはグローバルオプションを使用して追加できます。

セッション（現在の接続）
グローバル（前回の起動以降のデータ）

# Com_xxx は、各 xxx ステートメントが実行される回数を示します。
mysql> 'Com_%' のようなステータスを表示します。

私たちは通常、次のような統計パラメータを重視します。

Com_select: 選択操作が実行された回数。クエリごとに 1 回のみが累積されます。
Com_insert: 挿入操作が実行された回数。バッチ挿入操作の場合は、1 回のみ累積されます。
Com_update: 実行された更新操作の数。
Com_delete: 削除操作が実行された回数。

上記のパラメータは、すべてのストレージエンジンのテーブル操作に対して累積されます。以下のパラメータは InnoDB 専用であり、蓄積アルゴリズムは若干異なります。

Innodb_rows_read: 選択クエリによって返される行数。
Innodb_rows_inserted: 挿入操作によって挿入された行数。
Innodb_rows_updated: 更新操作によって更新された行数。
Innodb_rows_deleted: 削除操作によって削除された行数。

上記のパラメータにより、現在のデータベースアプリケーションが挿入更新を主としているのか、クエリ操作を主としているのか、また、さまざまな種類の SQL のおおよその実行比率を簡単に把握できます。更新操作の回数は、コミットまたはロールバックに関係なく累積される実行回数の回数です。

トランザクションアプリケーションの場合、 Com_commitとCom_rollback使用して、トランザクションのコミットとロールバックの状況を把握できます。ロールバック操作が非常に頻繁に行われるデータベースの場合、アプリケーションの書き込みに問題がある可能性があります。

さらに、次のパラメータは、ユーザーがデータベースの基本的な状況を理解するのに役立ちます。

接続: MySQL サーバーへの接続試行回数。
稼働時間: サーバーの稼働時間。
Slow_queries: 遅いクエリの数。

2. 実行効率の低いSQL文を定義する

1. 実行効率が低い SQL ステートメントを見つけるには、スロークエリログを使用します。-- --log-slow-queries[=file_name]オプションを指定して起動すると、mysqld は実行時間が long_query_time 秒を超えるすべての SQL ステートメントを含むログファイルを書き込みます。

2. スロークエリログは、クエリが完了した後にのみ記録されます。そのため、アプリケーションが実行効率の問題を反映している場合、スロークエリログでは問題を特定できません。show processlistコマンドを使用すると、スレッドの状態、テーブルがロックされているかどうかなど、現在のMySQLスレッドを表示できます。SQLの実行状況をリアルタイムで表示し、一部のテーブルロック操作を最適化できます。

3. 非効率的なSQLの実行計画をExplainで分析する

テストデータベースアドレス: https://downloads.mysql.com/docs/sakila-db.zip (ローカルダウンロード)

特定の電子メールで映画のコピーをレンタルするために支払われた合計金額をカウントするには、顧客テーブル customer と支払いテーブル payment を関連付け、amount フィールドに対して合計演算を実行する必要があります。対応する実行プランは次のとおりです。

mysql> explain select sum(amount) from customer a , payment b where a.customer_id= b.customer_id and a.email='[email protected]'\G 

************************** 1. 行 ****************************
  id: 1
 選択タイプ: シンプル
 テーブル:
 パーティション: NULL
  タイプ: すべて
可能なキー: プライマリ
  キー: NULL
 キー長さ: NULL
  参照: NULL
  行数: 599
 フィルター: 10.00
 追加: where の使用
************************** 2. 行 ****************************
  id: 1
 選択タイプ: シンプル
 表: b
 パーティション: NULL
  タイプ: ref
可能なキー: idx_fk_customer_id
  キー: idx_fk_customer_id
 キーの長さ: 2
  参照: sakila.a.customer_id
  行数: 26
 フィルター: 100.00
 追加: NULL
セットに 2 行、警告 1 件 (0.00 秒)

select_type: 選択タイプを示します。一般的な値は次のとおりです:
シンプル: シンプルなテーブルで、テーブル結合やサブクエリは使用されません
primary: メインクエリ、つまり外側のクエリ
union: ユニオン内の2番目以降のクエリステートメント
サブクエリ: サブクエリの最初の選択
テーブル: 結果セットを出力するテーブル
type: MySQL がテーブル内の必要な行を見つける方法、つまりアクセスタイプを示します。一般的なタイプは、最悪から最善の順に、all、index、range、ref、eq_ref、const、system、null です。

1. type=ALL 、フルテーブルスキャン、mysql はテーブル全体を走査して一致する行を検索します。

mysql> explain select * from film where ratings > 9 \G

************************** 1. 行 ****************************
  id: 1
 選択タイプ: シンプル
 表: フィルム
 パーティション: NULL
 タイプ: すべて
可能なキー: NULL
  キー: NULL
 キー長さ: NULL
  参照: NULL
 行数: 1000
 フィルター: 33.33
 追加: where の使用
セットに 1 行、警告 1 回 (0.01 秒)

2. type=index 、インデックスフルスキャン、MySQLはインデックス全体を走査して一致する行を検索します

mysql> フィルム\G のタイトル選択フォームの説明

************************** 1. 行 ****************************
  id: 1
 選択タイプ: シンプル
 表: フィルム
 パーティション: NULL
 タイプ: インデックス
可能なキー: NULL
  キー: idx_title
 キーの長さ: 767
  参照: NULL
 行数: 1000
 フィルター: 100.00
 追加: インデックスの使用
セットに 1 行、警告 1 件 (0.00 秒)

3. type=range 、インデックス範囲スキャン。<、<=、>、>=、between などの演算でよく使用されます。

mysql> explain select * from payment where customer_id >= 300 and customer_id <= 350 \G 

************************** 1. 行 ****************************
  id: 1
 選択タイプ: シンプル
 表: 支払い
 パーティション: NULL
 タイプ: 範囲
可能なキー: idx_fk_customer_id
  キー: idx_fk_customer_id
 キーの長さ: 2
  参照: NULL
 行数: 1350
 フィルター: 100.00
 追加: インデックス条件の使用
セットに1行、警告1回（0.07秒）

4. type=ref 、一意でないインデックススキャンまたは一意のインデックスのプレフィックススキャンを使用して、単一の値に一致する行を返します。次に例を示します。

mysql> 顧客ID = 350 \G の支払いから選択 * を説明します 
************************** 1. 行 ****************************
  id: 1
 選択タイプ: シンプル
 表: 支払い
 パーティション: NULL
 タイプ: ref
可能なキー: idx_fk_customer_id
  キー: idx_fk_customer_id
 キーの長さ: 2
  参照: 定数
 行数: 23
 フィルター: 100.00
 追加: NULL
セットに 1 行、警告 1 回 (0.01 秒)

インデックスidx_fk_customer_idは一意でないインデックスです。クエリ条件は等価値クエリ条件customer_id = 350であるため、スキャンインデックスのタイプは ref です。 Ref は結合操作でもよく使用されます。

mysql> explain select b.*, a.* from payment a,customer b where a.customer_id = b.customer_id \G

************************** 1. 行 ****************************
  id: 1
 選択タイプ: シンプル
 表: b
 パーティション: NULL
 タイプ: すべて
可能なキー: プライマリ
  キー: NULL
 キー長さ: NULL
  参照: NULL
 行数: 599
 フィルター: 100.00
 追加: NULL
************************** 2. 行 ****************************
  id: 1
 選択タイプ: シンプル
 テーブル:
 パーティション: NULL
 タイプ: ref
可能なキー: idx_fk_customer_id
  キー: idx_fk_customer_id
 キーの長さ: 2
  参照: sakila.b.customer_id
 行数: 26
 フィルター: 100.00
 追加: NULL
セットに 2 行、警告 1 件 (0.00 秒)

5. type=eq_refは ref と似ていますが、使用されるインデックスが一意のインデックスであるという点が異なります。インデックスキー値ごとに、テーブル内の 1 つのレコードのみが一致する必要があります。簡単に言えば、 primary keyまたはunique index 、複数のテーブルを結合する際の結合条件として使用されます。

mysql> explain select * from film a , film_text b where a.film_id = b.film_id \G

************************** 1. 行 ****************************
  id: 1
 選択タイプ: シンプル
 表: b
 パーティション: NULL
 タイプ: すべて
可能なキー: プライマリ
  キー: NULL
 キー長さ: NULL
  参照: NULL
 行数: 1000
 フィルター: 100.00
 追加: NULL
************************** 2. 行 ****************************
  id: 1
 選択タイプ: シンプル
 テーブル:
 パーティション: NULL
 タイプ: eq_ref
可能なキー: プライマリ
  キー: PRIMARY
 キーの長さ: 2
  参照: sakila.b.film_id
 行数: 1
 フィルター: 100.00
 追加: where の使用
セットに 2 行、警告 1 回 (0.03 秒)

6. type=const/system場合、単一のテーブルに一致する行は最大で 1 つであり、クエリは非常に高速であるため、この一致する行の他の列の値は、現在のクエリでオプティマイザによって定数として扱われ、たとえば、 primary keyキーまたはunique indexに基づいてクエリを実行できます。

mysql> テーブル test_const を作成します (
 -> テストID int,
 -> テストコンテキストvarchar(10)、
 -> 主キー (`test_id`)、
 -> );
 
test_const に値(1,'hello')を挿入します。

select * from ( select * from test_const where test_id=1 ) a \G の説明
************************** 1. 行 ****************************
  id: 1
 選択タイプ: シンプル
 テーブル: test_const
 パーティション: NULL
 型: 定数
可能なキー: プライマリ
  キー: PRIMARY
 キーの長さ: 4
  参照: 定数
 行数: 1
 フィルター: 100.00
 追加: NULL
 セットに 1 行、警告 1 件 (0.00 秒)

7. type=null場合、mysql はテーブルやインデックスにアクセスせずに結果を直接取得できます。

mysql> 説明 1 から 1 をデュアルで選択する where 1 \G
************************** 1. 行 ****************************
  id: 1
 選択タイプ: シンプル
 テーブル: NULL
 パーティション: NULL
 タイプ: NULL
可能なキー: NULL
  キー: NULL
 キー長さ: NULL
  参照: NULL
 行: NULL
 フィルター: NULL
 追加: テーブルは使用されません
セットに 1 行、警告 1 件 (0.00 秒)

タイプ type には、 ref_or_null (ref に似ていますが、条件に null のクエリが含まれている点が異なります)、index_merge (インデックスマージの最適化)、unique_subquery (in の後に主キーフィールドをクエリするサブクエリが続く)、index_subquery (unique_subquery に似ていますが、in の後に非一意のインデックスフィールドをクエリするサブクエリが続く) などの他の値もあります。

possible_keys : クエリ中に使用できる可能性のあるインデックスを示します。
キー: 実際に使用されるインデックスを示します
key-len: 使用されるインデックスフィールドの長さ。
行数: スキャン行数
追加: 実行の説明と記述。他の列に表示するには適していないが、実行計画にとって非常に重要な追加情報が含まれます。

警告を表示するコマンド

explain を実行した後、 show warningsを実行して、SQL が実際に実行される前にオプティマイザが書き換える SQL を確認します。

MySQL [sakila]> explain select sum(amount) from customer a , payment b where 1=1 and a.customer_id = b.customer_id and email = '[email protected]'\G
************************** 1. 行 ****************************
  id: 1
 選択タイプ: シンプル
 テーブル:
 パーティション: NULL
  タイプ: すべて
可能なキー: プライマリ
  キー: NULL
 キー長さ: NULL
  参照: NULL
  行数: 599
 フィルター: 10.00
 追加: where の使用
************************** 2. 行 ****************************
  id: 1
 選択タイプ: シンプル
 表: b
 パーティション: NULL
  タイプ: ref
可能なキー: idx_fk_customer_id
  キー: idx_fk_customer_id
 キーの長さ: 2
  参照: sakila.a.customer_id
  行数: 26
 フィルター: 100.00
 追加: NULL
セットに 2 行、警告 1 件 (0.00 秒)

MySQL [sakila]> 警告を表示します。
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| レベル | コード | メッセージ |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 注 | 1003 | /* select#1 */ select sum(`sakila`.`b`.`amount`) AS `sum(amount)` from `sakila`.`customer` `a` join `sakila`.`payment` `b` where ((`sakila`.`b`.`customer_id` = `sakila`.`a`.`customer_id`) and (`sakila`.`a`.`email` = '[email protected]')) |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
セット内の 1 行 (0.00 秒)

警告メッセージ欄から、1=1 が常に成立する条件をオプティマイザが自動的に削除していることがわかります。つまり、SQL を書き換える際に、常に成立する条件をオプティマイザが自動的に削除していることになります。

explain コマンドはパーティションもサポートしています。

MySQL [sakila]> テーブル `customer_part` を作成します (
 -> `customer_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
 -> `store_id` tinyint(3) 符号なし NOT NULL,
 -> `first_name` varchar(45) NOT NULL,
 -> `last_name` varchar(45) NOT NULL,
 -> `email` varchar(50) デフォルト NULL,
 -> `address_id` smallint(5) 符号なし NOT NULL,
 -> `active` tinyint(1) NOT NULL デフォルト '1',
 -> `create_date` 日時 NOT NULL、
 -> `last_update` タイムスタンプ NULL デフォルト CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP、
 -> 主キー (`customer_id`)
 -> 
 -> ) ハッシュによるパーティション (customer_id) パーティション 8;
クエリは正常、影響を受けた行は 0 行 (0.06 秒)

MySQL [sakila]> customer_part に挿入し、customer から * を選択します。
クエリは正常、599 行が影響を受けました (0.06 秒)
レコード: 599 重複: 0 警告: 0

MySQL [sakila]> explain select * from customer_part where customer_id=130\G
************************** 1. 行 ****************************
  id: 1
 選択タイプ: シンプル
 テーブル: customer_part
 パーティション: p2
  型: 定数
可能なキー: プライマリ
  キー: PRIMARY
 キーの長さ: 2
  参照: 定数
  行数: 1
 フィルター: 100.00
 追加: NULL
セット内の行は 1 行、警告は 1 件 (0.00 秒)

sql によってアクセスされるパーティションは p2 であることがわかります。

4. performance_schema を通じて SQL パフォーマンスを分析する

MySQLの古いバージョンでは、プロファイルを使用してSQLパフォーマンスを分析できます。私はバージョン5.7.18を使用していますが、これではプロファイルを使用できなくなりました。
performance_schema は SQL を分析します。

5. トレースを使用して、オプティマイザーが実行プランを選択する方法を分析します。

MySQL 5.6 では、SQL ステートメントのトレース機能が提供されており、オプティマイザが実行プラン B ではなく実行プラン A を選択する理由や、オプティマイザの動作をより深く理解するのに役立ちます。

使用方法: まずトレースを開き、形式を json に設定し、トレースが使用できる最大メモリサイズを設定して、デフォルトのメモリが小さすぎるために解析プロセス中に不完全な表示が発生するのを回避します。

MySQL [sakila]> optimizer_trace="enabled=on",end_markers_in_json=on; を設定します。
クエリは正常、影響を受けた行は 0 行 (0.00 秒)

MySQL [sakila]> optimizer_trace_max_mem_size を 1000000 に設定します。
クエリは正常、影響を受けた行は 0 行 (0.00 秒)

次に、トレースする SQL ステートメントを実行します。たとえば、レンタル日 rental_date 2005-05-25 4:00:00 ～ 5:00:00 の間にレンタルされた、在庫番号 inventory_id 4466 の映画コピーのレコードをレンタルテーブル rental から検索するには、次のようにします。

mysql> 1=1 かつ rental_date >= '2005-05-25 04:00:00' かつ rental_date <= '2005-05-25 05:00:00' かつ inventory_id=4466 の場合、 rental から rental_id を選択します。
+-----------+
| レンタルID |
+-----------+
| 39 |
+-----------+
セット内の1行（0.06秒）

MySQL [sakila]> information_schema.optimizer_trace\G から * を選択します
************************** 1. 行 ****************************
    クエリ: infomation_schema.optimizer_trace から * を選択します
    トレース: {
 「ステップ」: [
 ] /* 手順 */
}
最大メモリサイズを超えるバイト数: 0
  権限不足: 0
セット内の 1 行 (0.00 秒)

6. 問題を特定し、適切な最適化策を講じる

上記の手順を実行すると、問題の原因を基本的に確認できます。このとき、状況に応じて適切な対策を講じることで、実行効率を最適化し、向上させることができます。

要約する

上記はこの記事の全内容です。この記事の内容が皆さんの勉強や仕事に少しでもお役に立てれば幸いです。ご質問があれば、メッセージを残してコミュニケーションしてください。123WORDPRESS.COM をご愛顧いただき、ありがとうございます。

以下もご興味があるかもしれません: