MySQL データベース SQL ステートメントの最適化

#SQL の問題を判断する

SQL に問題があるかどうかを判断するときは、次のことができます。

システム レベルの症状
• 深刻な CPU 消費量
  • 深刻な IO 待機
  • ページの応答時間が長すぎます
  • アプリケーションログ タイムアウトなどのエラーが発生した場合は、
  • sar
  コマンドと
  top
コマンドを使用できます。現在のシステムステータスを表示します。

Prometheus、Grafana

、その他の監視ツールを通じてシステムのステータスを観察することもできます。

#SQL ステートメントの表現

長すぎます

実行時間が長すぎます

完全なテーブルからデータを取得しますscan
• 実行計画の行とコストが非常に大きい
  • 長い SQL は理解しやすいです。SQL が長すぎると、可読性が低下します。確実に貧弱になり、問題が発生し、その頻度は確実に高くなります。 SQL の問題をさらに特定するには、以下に示すように、実行プランから始める必要があります:
実行プランは、このクエリがテーブル全体のスキャンを通過したことを示しています

Type=ALL

、行が非常に大きい (9950400) ため、基本的には「風味豊かな」 SQL であると判断できます。

問題 SQL の取得

データベースが異なれば、それを取得する方法も異なります。以下は、現在の主流データベース向けのスロー クエリ SQL 取得ツールです。

MySQL スロークエリログ

テストツールloadrunner

Perconaのptqueryおよびその他のツール
• • Oracle
  • AWR レポート
  • テスト ツールのロードランナーなど
関連する内部ビュー (v$sql、v$session_wait など)
• GRID CONTROL 監視ツール
  • Dameng データベース
  • AWR レポート
  • テスト ツール ロードランナーなど
Dameng パフォーマンス監視ツール (dem)
• v$sql、v$session_wait などの関連する内部ビュー。
  • SQL 作成スキル
  一般的なスキルがいくつかあります。 SQL 記述の場合:

• インデックスの合理的な使用

インデックスが少ないとクエリが遅くなり、インデックスが多すぎると多くのスペースが占有されるため、インデックスを動的に維持する必要があります。追加、削除、変更を実行するとき、パフォーマンスに影響を与える

高い選択率 (重複値が少ない) そして、B ツリー インデックスを確立する必要がある場所によって頻繁に参照される、一般的な結合列にインデックスを付ける必要がある、複雑なドキュメント タイプのクエリフルテキスト インデックスを使用するとより効率的です。インデックスの確立では、クエリと DML のパフォーマンスのバランスをとる必要があります。複合インデックスを作成するときは、次の点に注意する必要があります。非先頭列クエリの場合は、

• UNION の代わりに UNION ALL を使用してください

UNION ALL は UNION よりも実行効率が高くなります。UNION は実行時に重複排除する必要があります。UNION はソートする必要があります。

• select * メソッドの記述を避ける

SQL を実行するとき、オプティマイザは * を特定の列に変換する必要があります。各クエリはテーブルに返される必要があり、上書きすることはできません。

• JOIN フィールドのインデックスを作成することをお勧めします

一般に、JOIN フィールドには事前にインデックスが付けられます

• 複雑な SQL を避けるステートメント

読みやすさを向上、クエリが遅くなる可能性を回避、複数の短いクエリに変換してビジネスエンドで処理可能

#• 1=1 の記述を避ける

• データ列が複数回スキャンされる原因となる

RAND() と同様の記述による rand() による順序付けを避ける

SQL の最適化 実行計画

SQL の最適化を完了する前に必ず実行計画を読んでください。実行計画には、効率が低い箇所と最適化が必要な箇所が示されます。 MYSQL を例として、実行計画がどのようなものかを見てみましょう。 (各データベースの実行計画は異なるため、自分で理解する必要があります)

#フィールド説明##idselect_type table#partitions#一致するパーティション情報 (パーティション化されていないテーブルの場合、値は NULL)##type結合操作の種類possible_keys使用される可能性のあるインデックスkeyインデックス実際にオプティマイザによって使用されます (最も重要な結合タイプは最良のものから最悪のものまで、consteq_reg#ref は、この行の操作対象オブジェクトの参照オブジェクトを表します。参照オブジェクトは NULL# ではありません。 ##rowsfilteredextraファイルソートの使用一時的な使用# 単語を使用するときは注意してください ##、SQL ステートメントを最適化する必要がある可能性が非常に高いです テーブル構造

それぞれが独立して実行されます。 identifier はオブジェクトを操作する順序を識別します。id の値が大きいほど最初に実行されます。同じ場合、実行順序は上から下です。
クエリ内 各 select 句のタイプ
操作対象のオブジェクトの名前。通常はテーブル名ですが、他の形式
、	、ref、range## です) #、index および ALL。 ALL が表示される場合は、現在の SQL に「悪臭」があることを意味します。 key_len によって選択されたインデックス キーの長さ。オプティマイザの場合、単位は次のとおりです。バイト
クエリ実行によってスキャンされたタプルの数 (innodb の場合、この値は推定値です)
条件テーブルのデータがフィルタリングされる タプル数の割合
この列が表示される場合の実行計画の重要な補足情報	,
	次に、実践的な最適化のケースでは、SQL 最適化プロセスと最適化手法を説明します。 #最適化ケース

CREATE TABLE `a` ( `id` int(11) NOT NULLAUTO_INCREMENT, `seller_id` bigint(20) DEFAULT NULL, `seller_name` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, `gmt_create` varchar(30) DEFAULT NULL, PRIMARY KEY (`id`) ); CREATE TABLE `b` ( `id` int(11) NOT NULLAUTO_INCREMENT, `seller_name` varchar(100) DEFAULT NULL, `user_id` varchar(50) DEFAULT NULL, `user_name` varchar(100) DEFAULT NULL, `sales` bigint(20) DEFAULT NULL, `gmt_create` varchar(30) DEFAULT NULL, PRIMARY KEY (`id`) ); CREATE TABLE `c` ( `id` int(11) NOT NULLAUTO_INCREMENT, `user_id` varchar(50) DEFAULT NULL, `order_id` varchar(100) DEFAULT NULL, `state` bigint(20) DEFAULT NULL, `gmt_create` varchar(30) DEFAULT NULL, PRIMARY KEY (`id`) );

3 つのテーブルの関連付け, 現在時刻から 10 時間前後の現在のユーザーの注文ステータスをクエリし、注文作成時刻に従って昇順に並べ替えます。具体的な SQL は次のとおりです。

• select a.seller_id,          a.seller_name,          b.user_name,          c.state   from a,        b,        c   where a.seller_name = b.seller_name     and b.user_id = c.user_id     and c.user_id = 17     and a.gmt_create       BETWEEN DATE_ADD(NOW(), INTERVAL – 600 MINUTE)       AND DATE_ADD(NOW(), INTERVAL 600 MINUTE)   order by a.gmt_create

データ量の表示
##元の実行時間

• 元の実行計画

• 初期の最適化アイデア

• Where 条件フィールドのタイプSQL の はテーブル構造と一致している必要があります。

  user_id

  は varchar(50) 型です。SQL で使用される実際の int 型は暗黙的な変換があり、インデックスは追加されません。テーブル b とテーブル c の user_id

  フィールドを int 型に変更します。

• #テーブル b とテーブル c の間にリレーションシップがあるため、テーブル b とテーブル c にインデックスを作成します。

  user_id

• #リレーションシップがあるため、テーブルは b テーブルに関連付けられており、a テーブルと b テーブルの seller_name フィールドにインデックスが付けられています。

• 複合インデックスを使用します。一時テーブルとソートを排除するため

SQL の初期最適化

• alter table b modify `user_id` int(10) DEFAULT NULL;   alter table c modify `user_id` int(10) DEFAULT NULL;   alter table c add index `idx_user_id`(`user_id`);   alter table b add index `idx_user_id_sell_name`(`user_id`,`seller_name`);   alter table a add index `idx_sellname_gmt_sellid`(`gmt_create`,`seller_name`,`seller_id`);

• 最適化後の実行時間を表示

• 
  

  最適化された実行プランの表示

• 警告情報の表示

• 最適化の継続

#

alter table a modify "gmt_create" datetime DEFAULT NULL

• #実行時間の表示

  

• 
  

  実行計画の表示
#最適化の概要

実行計画の説明を表示します。

• アラーム メッセージがある場合は、警告メッセージを確認してください。
  テーブル構造を表示します。 SQLに関わるインデックス情報やインデックス情報など

実行計画に基づいて最適化ポイントを考える

最適化ポイントに応じてテーブル構造の変更、インデックス追加、SQLリライトなどを実行

最適化された実行時間と実行計画を表示します

最適化の効果が明らかでない場合は、4 番目のステップを繰り返します

1. 推奨される「
mysql ビデオ チュートリアル
2. 」 「

以上がMySQL データベース SQL ステートメントの最適化の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。