例を使用して SQL を最適化する方法を説明します

最近ではハードウェアのコストが下がっていますが、ハードウェアをアップグレードしてシステムのパフォーマンスを向上させることも一般的な最適化方法です。リアルタイム性の高いシステムではやはりSQL面からの最適化が必要であり、今回はSQLの最適化方法を例に基づいて紹介します。

SQL の問題を判断する

SQL に問題があるかどうかを判断する場合、次の 2 つの現象によって判断できます。

• システム レベルの現象

  • CPU 消費量が深刻です

  • IO 待機が深刻です

  • ページの応答時間が長すぎますlong

  • タイムアウトやその他のエラーがアプリケーション ログに表示される

sar コマンドと top コマンドを使用できます。現在のシステムステータスを表示します。 Prometheus や Grafana などの監視ツールを通じてシステムのステータスを観察することもできます。

##SQL ステートメントの外観

• 長さ
  実行時間が長すぎます

  • #フル テーブル スキャンからデータを取得しています

  • #実行プランの行とコストが非常に大きくなっています

  長い SQL は理解しやすい SQL が長すぎると可読性が悪く、問題の発生頻度も確実に高くなります。 SQL の問題をさらに特定するには、以下に示すように、実行プランから始める必要があります。

実行プランは、このクエリがフル テーブル スキャン Type= を使用していることを示しています。 ALL、行が非常に大きい (9950400) 基本的には「風味豊かな」SQL であると判断できます。

問題 SQL の取得

データベースごとに取得方法が異なります。以下は、現在主流のデータベース用のスロー クエリ SQL 取得ツールです。

MySQL

• スロークエリログ

  • テストツールloadrunner

  • Perconaのptqueryおよびその他のツール

  Oracle

• AWR レポート

  • テスト ツール ロードランナーなど

  • v$、$session_wait などの関連内部ビュー

  • GRID CONTROL 監視ツール

  • ## Dameng データベース
#AWR レポート

• #テスト ツール ロードランナーなど

  • # Dameng パフォーマンス監視ツール (開発者)

  • 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 を例として、実行計画がどのようなものかを見てみましょう。 (各データベースの実行計画は異なるため、自分で理解する必要があります)

#フィールド説明idそれぞれは独立して実行されます。操作識別子はオブジェクトを操作する順序を識別します。idの値が大きいほど最初に実行されます。同じ場合、実行順序は上から下です。 select_typeクエリ内 各 select 句のタイプtable操作対象のオブジェクトの名前。通常はテーブル名ですが、他の形式ですpartitions一致するパーティション情報 (パーティション化されていないテーブルの値は NULL)type結合操作の種類possible_keys使用される可能性のあるインデックスkeyオプティマイザによって実際に使用されるインデックス (最も重要な列) 最適な結合タイプから最悪の結合タイプまで、const、eq_reg、ref、range、index、および ALL です。 ALL が表示される場合は、現在の SQL に「悪臭」があることを意味します。key_lenオプティマイザによって選択されたインデックス キーの長さ (バイト単位)ref はこの行の操作オブジェクトの参照オブジェクトを示します。参照オブジェクトは NULLrowsQuery 実行によってスキャンされたタプルの数 (innodb の場合、この値は推定値です)filtered条件式のタプル数のパーセンテージフィルタリングされたテーブルextra実行計画の重要な補足情報。この列に「ファイルソートを使用」、「一時を使用」という言葉が表示されている場合は注意してください。 SQL ステートメントを最適化する必要があります

ではありません

次に、実際の最適化ケースを使用して 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`)
);

現在時刻から10時間前後の現在のユーザーの注文状況をクエリし、昇順に並べ替える3つのテーブルが関連付けられています。具体的な 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；

View データ量

元の実行時間

元の実行計画

##初期の最適化アイデア

SQL の where 条件フィールドの型テーブルの user_id は、SQL で使用される実際の int 型である varchar(50) 型であり、暗黙的な変換が行われ、インデックスは追加されません。テーブル b とテーブル c の user_id フィールドを int 型に変更します。
テーブル b とテーブル c の間に関連付けがあるため、テーブル b とテーブル c の user_id にインデックスを作成します。
テーブル a とテーブル b の間の関連付けです。テーブル a と b の sell_name フィールドにインデックスを作成します。

3. #複合インデックスを使用して一時テーブルを削除し、並べ替えます

4. 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`);

最適化後の実行時間を表示
#最適化後の実行計画を表示




#警告情報の表示




最適化を続行します。テーブルの変更、「gmt_create」の日時の変更 DEFAULT NULL;

実行時間の表示





実行計画の表示



#概要




#実行計画の説明を表示




##アラーム メッセージがある場合は、アラーム メッセージに警告が表示されていることを確認します。

1. #SQL に関連するテーブル構造とインデックス情報を表示する

2. 実行計画に従って考えられる最適化ポイントを考える

3. テーブルを実行する可能な最適化ポイントに応じて、構造の変更、インデックスの追加、SQL の書き換え、その他の操作

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

5. #最適化の効果は明ら​​かではありません。4 番目のステップを繰り返します。

6. 関連推奨: 「

   mysql チュートリアル

   」

以上が例を使用して SQL を最適化する方法を説明しますの詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。