MySQL における or、in、union、インデックス最適化の詳細な分析

この記事は、「1 分でインデックス作成スキルを学ぶ」という宿題から生まれました。

注文ビジネス テーブルの構造が次のとおりであると仮定します。

順序(oid、日付、uid、ステータス、金額、時間、…)

で：

• oid、注文ID、主キー
• 日付、注文日には共通のインデックスがあり、管理バックエンドは日付でクエリを実行することが多い
• uid、ユーザーID、共通のインデックスを持ち、ユーザーは自分の注文を照会します
• ステータス、注文ステータスには共通のインデックスがあり、管理バックエンドはステータスに応じてクエリを実行することが多い
• 金額/時間、注文金額/時間、照会フィールド、インデックスなし
• …

注文には 0 が発注済み、1 が支払い済み、2 が完了の 3 つの状態があるとします。

ビジネス要件: 未完了の注文をクエリする場合、どの SQL の方が高速ですか?

• ステータスが 2 である順序から * を選択
• ステータス=0 またはステータス=1 の順序から * を選択します
• ステータスが (0,1) である順序から * を選択
• ステータス=0 の順序から * を選択
  すべて結合
  ステータスが 1 である順序から * を選択

結論: ソリューション 1 は最も遅く、ソリューション 2、3、4 はすべてインデックスにヒットできます。

しかし...

1: union allは確実にインデックスにヒットする

ステータス=0 の順序から * を選択

すべて結合

ステータスが 1 である順序から * を選択

例:

MySQLに何をするかを直接指示すると、MySQLはCPUを最も少なく消費します

プログラマーはこのようなSQLを書くことはあまりありません（union all）

2: シンプルインでインデックスにヒットできる

ステータスが (0,1) である順序から * を選択

例:

MySQLに考えさせると、クエリの最適化はunion allよりも多くのCPUを消費しますが、無視できるほどです。

プログラマーはSQLを次のように書くことが多い。この例では、次のように書くのが推奨される。

3: または、MySQLの新しいバージョンはインデックスにヒットすることができます

ステータス=0 またはステータス=1 の順序から * を選択します

例:

MySQL に考えさせてください。クエリの最適化は IN よりも多くの CPU を消費します。MySQL に負担をかけないでください。

すべての or がインデックスにヒットするわけではないので、プログラマが or を頻繁に使用することは推奨されません。

MySQLの古いバージョンの場合は、クエリと分析を行うことをお勧めします。

4. !=の場合、否定クエリは確実にインデックスにヒットしません

ステータスが 2 である順序から * を選択

例:

フルテーブルスキャンは、すべてのソリューションの中で最も効率が悪く、最も遅い

否定的な検索は禁止されています

V. その他のオプション

ステータスが 2 未満の順序か​​ら * を選択

この特定の例では、確かに高速ですが、

この例では、3 つの状態のみが示されています。実際の業務では、これら 3 つの状態よりも多くの状態があり、状態の「値」は半順序関係を満たすだけです。他の状態をチェックしたい場合はどうすればよいでしょうか。SQL は列挙の値に依存すべきではなく、解決策は普遍的ではありません。

この SQL は読みにくく、理解しにくく、保守性も低いため、強く推奨されません。

6. 宿題

このようなクエリはインデックスにヒットできますか?

uid in (の順序で*を選択

   ステータス=0の注文からUIDを選択

）

select * from order where status in (0, 1) order by date desc

ステータスが 0 または日付が CURDATE() である順序から * を選択します

注: これは単なる例です。業務に対応する SQL の合理性についてはあまりこだわらないでください。

要約する

以上がこの記事の全内容です。この記事の内容が皆様の勉強や仕事に何らかの参考学習価値をもたらすことを願います。123WORDPRESS.COM をご愛顧いただき、誠にありがとうございます。