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

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 をご愛顧いただき、ありがとうございます。

以下もご興味があるかもしれません:
  • MySQL で SQL クエリを最適化するための 30 の一般的な方法について簡単に説明します。
  • SQL 文の最適化: IN を EXISTS に、NOT IN を NOT EXISTS に置き換えます。
  • SQL 文の最適化方法の例 30 個 (推奨)
  • SQL文を最適化する方法についての簡単な説明
  • SQL 文の最適化方法の 30 の例
  • SQL ステートメントを最適化する方法 (完全版)
  • SQL文の実行計画を分析してSQLを最適化する
  • 一般的な SQL 文の最適化手法の概要 [クラシック]
  • SQL 文を最適化してデータベースのパフォーマンスを向上させる
  • SQL ステートメントのパフォーマンス最適化 (続き)

<<:  nginx を使用して同じドメイン名で複数の Vue プロジェクトをデプロイし、リバース プロキシを使用する方法

>>:  CocosCreator ScrollView 最適化シリーズ: フレーム読み込み

推薦する

JavaScript インスタンス オブジェクトでプロトタイプ メソッドをオーバーライドする方法の詳細

目次JavaScriptでは、通常、次のコードのようにクラスを簡単に定義できます。 var サンプル...

Windows で Mysql を起動したときに 1067 が表示される場合の解決策

数日前に仕事を始めて、Mysql をインストールしたところ、開くことができました。今日、会社に行った...

Linux コマンドラインでパケットをキャプチャするために tcpdump を使用するいくつかの機能

tcpdump は、ネットワークの問題のトラブルシューティングに効果的に役立つ、柔軟で強力なパケット...

MySQL がタイムスタンプを使用するときにタイムゾーンの問題を無視できるのはなぜですか?

私はいつも、なぜMySQLデータベースのtimestampタイムゾーンの問題を無視できるのか疑問に思...

IE6 ウェブページ作成リファレンス IE6 デフォルトスタイル

これは実際には IE の公式ドキュメントではありません。他の人が実践を通じて開発した IE6 のデフ...

ESXI の仮想マシンにワークステーションをインストールするときに発生するネットワーク障害の解決策

問題の説明ESXI で Windows にワークステーションをインストールした後、内部の仮想マシンは...

MySQL 5.7 のスロークエリログの時間がシステム時間より 8 時間遅れている理由の詳細な説明

遅いクエリをチェックすると、時間が正しくなく、システム時間とちょうど 8 時間異なっていることがわか...

bashの初期化メカニズムの詳細な説明

Bash 初期化ファイル対話型ログインシェル次の場合にはログイン シェルを取得できます。ローカル端末...

Linux での MongoDB のインストールと設定のチュートリアル

MongoDBインストールYumを使用してインストールすることを選択する1. repoファイルを作成...

MySQL無料インストールバージョンの設定チュートリアル

この記事では、参考までにMySQLの無料インストール構成チュートリアルを紹介します。具体的な内容は次...

Linuxでプロセスが占有するポート番号を表示する

Linux システム管理者にとって、サービスがポートに正しくバインドされているか、またはポートをリッ...

VMWare 仮想マシン 15.X LAN ネットワーク構成チュートリアル図

最近、分散型およびビッグデータ技術について学ぶために、いくつかの仮想マシンに取り組んでいます。まず、...

Vueプロジェクトでパラメータジャンプ機能を実装する

ページの説明:​ メインページ: 名前 —> shishengzuotanhuichaxun ...

Vueはテーマ切り替えのための複数のアイデアを実装します

目次テーマを動的に変更する最初の方法: 動的コンポーネント2番目の方法はルーティング分離です要約する...

Windows Server 2016 リモート デスクトップ サービスの構成とライセンスのアクティブ化 (2 ユーザー)

Server 2016 のリモート デスクトップ接続のデフォルト数は 2 ユーザーです。2 人以上...