SQL クエリを最適化するにはどうすればよいですか? (詳しい説明)

なぜ最適化する必要があるのか​​

システムのスループットのボトルネックは、データベースのアクセス速度に現れることがよくあります。つまり、アプリケーションが実行されるにつれて、データベース内のデータがますます増え、処理時間もそれに応じて遅くなります。さらに、データはディスクに保存されるため、読み取りおよび書き込みの速度は比較できません。

#最適化の方法

1. データベースを設計するとき: データベースのテーブルとフィールド、ストレージ エンジンの設計

2. Makeインデックスやステートメント記述の最適化など、MySQL 自体が提供する機能をうまく利用する

3. MySQL クラスター、サブデータベースとサブテーブル、読み取りと書き込みの分離

インターネットにはSQL ステートメントの最適化には多くの経験があるため、この記事ではこれらを脇に置き、DAO レイヤーとデータベース設計の最適化を試み、2 つの簡単な例を挙げます

例 1:ERPクエリの最適化

現状分析:

1. 関連するインデックスの欠如

2. Mysql 自体のパフォーマンスは限られており、複数のテーブルの関連付けはサポートされていません。現在のパフォーマンスは、多くのテーブルに関連付けられているリスト クエリに主に焦点を当てています

対策:

1 必要なインデックスを追加します: Explain を通じて実行レコードを表示し、指示に従ってインデックスを追加します。実行計画;

2 最初にビジネス データのメイン テーブルの主キーを数え、より小さい結果セットを取得してから、結果セット関連付けクエリを使用します;
1) 最初にクエリを実行し、その主キーを表示します。メイン テーブルと条件に基づくビジネス データ
2) 主キーをクエリ条件として使用し、他の関連テーブルを関連付けて、必要なビジネス フィールドをクエリします。
3) メイン テーブルをクエリするとき、クエリ条件の場合、他のテーブルと関連付ける必要がある場合は、この条件が設定されている場合にのみテーブルの関連付けを設定する必要があります。

例如 有如下表 TT_A   TT_B    TT_C  TT_D

假设未优化前的SQL是这样的

SELECT
    A.ID,
    ....
    B.NAME,
    .....
    C.AGE,
    ....
    D.SEX
    .....

FROM  TT_A A
LEFT JOIN TT_B B ON A.ID  = B.ITEM_ID
LEFT JOIN TT_C C ON B.ID = C.ITEM_ID
LEFT JOIN TT_D D ON C.ID = D.ITEM_ID
WHERE 1=1AND A.XX = ?AND A.VV = ?.....

那么优化后的SQL是

第一步

SELECT
    A.ID

FROM  TT_A A
WHERE 1=1AND A.XX = ?AND A.VV = ?第二步

SELECT
    A.ID,
    ....
    B.NAME,
    .....
    C.AGE,
    ....
    D.SEX
    .....
FROM  ( SELECT A.ID,..... FROM  TT_A  WHERE ID IN (1,2,3..)  ) A
LEFT JOIN TT_B B ON A.ID  = B.ITEM_ID
LEFT JOIN TT_C C ON B.ID = C.ITEM_ID
LEFT JOIN TT_D D ON C.ID = D.ITEM_ID
WHERE 1=1AND A.XX = ?AND A.VV = ?

概要:

この種の最適化は次のとおりです。リスト クエリの条件は一般にメイン テーブルにリンクされているため、これを使用してキー フィールド インデックスを確立し、同時にクエリ条件の制限によってメイン テーブルのデータ量を大幅に削減するため、リスト クエリに適しています。この方法では、他のテーブルを相関付けるときにはるかに高速になります。

例 2: 記事検索の最適化

Tieba の記事検索関数を作成するとします。最も単純かつ直接的な方法は、ストレージ構造として、リレーショナル データベースを使用して、記事を格納するためのリレーショナル データベース テーブル TT_ARTICLES を作成することです。

次に、現在の検索キーワードがが "target" の場合、文字列マッチングを使用して、CONTENT 列に対してマッチング クエリを実行できます。

select * from ARTICLES where CONTENT like '% 目标 %';

これにより、検索機能が簡単に実装されます。ただし、このアプローチには明らかな問題があります。つまり、文字列の一致に % を使用するのは非常に非効率であるため、そのようなクエリはテーブル全体を走査する必要があります (フル テーブル スキャン)。記事数が数、数十の場合は問題ありませんが、記事数が数十万、数百万となる場合、この方法はまったく実現できません。言うまでもなく、単一のリレーショナル データベース テーブルではこのような大規模なデータを収容できません。たとえ収容できたとしても、再度スキャンする必要があります。ここでの時間コストは想像を絶します

そこで、次のように導入する必要があります。 「反転」「インデックス」技術。上で説明したシナリオでは、この概念を 2 つの部分に分割して説明します: 上記の ARTICLES テーブルはまだ存在しますが、キーワード テーブル KEYWORDS を追加する必要があり、KEYWORD 列にインデックスを付ける必要があります。このキーワードはすぐに見つかります:

もちろん、KEYWORDS テーブルと ARTICLES テーブル、KEYWORD_ID と ARTICLE_ID を結合として結合するためのリレーションシップ テーブルも必要です。 主キー

#ご存知のとおり、これは実際には多対多の関係です。つまり、同じキーワードが複数の記事に出現する可能性があり、1 つの記事に複数の異なるキーワードが含まれる可能性があります。このようにして、まずインデックス付きキーワードに基づいて KEYWARDS テーブルから対応する KEYWORD_ID を見つけ、次に上記の関連付けテーブルに基づいて ARTICLE_ID を見つけ、それを使用して ARTICLES テーブル内の対応する記事を見つけることができます。

要約:

これは 3 回の検索のように見えますが、そのたびにインデックスが使用されるため、全テーブル スキャンが不要になります。 , 速度も遅くなく、SQLで実装するとこの処理を全てSQL文に落とし込むことができます。データ量が少ない場合は上記の方法で十分です。これにより、テーブル全体のスキャンと文字列 % 一致クエリによって引き起こされるパフォーマンスの問題が解決されます。

概要:

技術面接中に、実践的な例を挙げたり、開発プロセスの問題点や利点について直接話したりできる場合、面接は次のようになります。たくさん追加すると、答えはより論理的になるはずです。混乱しやすくなるため、あちこちに移動しないでください。たとえば、SQL を最適化する方法について尋ねられた場合、インデックスの追加について直接答えることはできません。次のように答えることができます:

こんにちは、インタビュアーさん、まず第一に、私たちのプロジェクト DB データ量がボトルネックに遭遇しました。その結果、リスト クエリが非常に遅くなり、ユーザーのエクスペリエンスが低下します。この問題を解決するには、次のような多くの方法があります。基本的なデータベーステーブルの設計、基本的な SQL の最適化、MYSQL クラスタリング、読み書きの分離、サブデータベースとサブテーブル、アーキテクチャへのキャッシュ層の追加など。それらの長所と短所...これらを組み合わせて、次のように組み合わせます。私たちのプロジェクトの特徴 最後に、テクノロジーを選択するときに選択します。

あなたがこれほど整然と理路整然と質問に答え、質問以外にも多くの知識ポイントについて話せば、面接官はあなたがただコードを書けるだけでなく、あなたは明確なロジックを持っています。あなたはテクノロジの選択について独自の理解と考え方を持っています。

この記事は SQL チュートリアル 列からのものです。ぜひ学習してください。

以上がSQL クエリを最適化するにはどうすればよいですか? (詳しい説明)の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。