ホームページ >データベース >mysql チュートリアル >例を使用して SQL を最適化する方法を説明します

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

醉折花枝作酒筹転載: 2021-08-04 09:26:361635ブラウズ

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

SQL の問題を判断する

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

システムレベルの現象
- CPU 消費量が深刻です
- IO 待機が深刻です
- ページの応答時間が長すぎますlong
- タイムアウトやその他のエラーがアプリケーションログに表示される

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

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

- #フルテーブルスキャンからデータを取得しています
- #実行プランの行とコストが非常に大きくなっています

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

例を使用して SQL を最適化する方法を説明します問題 SQL の取得

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

MySQL

スロークエリログ
- テストツールloadrunner
- Perconaのptqueryおよびその他のツール
AWR レポート
- テストツールロードランナーなど
- v$、$session_wait などの関連内部ビュー
- GRID CONTROL 監視ツール
- ## Dameng データベース
#テストツールロードランナーなど
- # Dameng パフォーマンス監視ツール (開発者)
- v$、$session_wait などの関連内部ビュー
- 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 を最適化する方法を説明します

元の実行時間

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

元の実行計画

例を使用して SQL を最適化する方法を説明します ##初期の最適化アイデア

#複合インデックスを使用して一時テーブルを削除し、並べ替えます
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`);

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

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

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