MySQLのINサブクエリによってインデックスが使用できなくなる問題を解決する

MySQLのINサブクエリによってインデックスが使用できなくなる問題を解決する

今日は、MySQL IN サブクエリの最適化に関するケーススタディを見ました。

最初は少し懐疑的でした (SQL Server ではこれは不可能ですが、後で簡単なテストを実行します)。

その後、彼の言うとおりにテーブルを作ってテストして検証したところ、MySQLのINサブクエリがうまく実行されておらず、実際にインデックスが使用できない状況になっていることがわかりました(INサブクエリが使用できないため、シナリオはMySQLで、終了バージョンは5.7.18でした)

MySQL テスト環境

テストテーブルは以下のとおりです

テーブル test_table2 を作成する
(
  id int auto_increment 主キー、
  支払いID int、
  pay_time 日時、
  その他の列 varchar(100)
)

テスト データを挿入するためのストアド プロシージャを作成します。テスト データの特徴は、pay_id が繰り返し可能であることです。ここでは、ストアド プロシージャで、300 万のデータ項目のループ挿入中に、100 のデータ項目ごとに繰り返される pay_id が挿入されます。時間フィールドは、特定の範囲内でランダムです。

CREATE DEFINER=`root`@`%` PROCEDURE `test_insert`(IN `loopcount` INT)
  言語 SQL
  決定論的ではない
  SQLを含む
  SQL セキュリティ定義者
  コメント ''
始める
  cnt int を宣言します。
  cnt = 0 に設定します。
  while cnt< loopcount 実行
    test_table2 (pay_id、pay_time、other_col) に値 (cnt、date_add(now()、interval floor(300*rand()) day)、uuid() を挿入します。
    (cnt mod 100 = 0)の場合
      test_table2 (pay_id、pay_time、other_col) に値 (cnt、date_add(now()、interval floor(300*rand()) day)、uuid() を挿入します。
    終了の場合;
    cnt = cnt + 1 に設定します。  
  終了しながら;
終わり

test_insert(3000000) を実行して、303000行のデータを挿入します。

サブクエリを書く2つの方法

クエリは大まかに言うと、特定の期間内にビジネス ID が 1 より大きいデータを照会することを意味するため、記述方法は 2 つあります。

最初の書き方は次のとおりです。IN サブクエリには、一定期間内にビジネス統計が 1 より大きいビジネス ID が含まれます。外側のレイヤーは、IN サブクエリの結果に従ってクエリを実行します。ビジネス ID の pay_id 列にはインデックスがあり、ロジックは比較的単純です。この書き方は、データ量が多く、インデックスが必要ない場合には確かに非効率的です。

test_table2から*を選択、強制インデックス(idx_pay_id)
pay_idが(
  test_table2からpay_idを選択 
  ここで、pay_time>="2016-06-01 00:00:00" 
    かつ、支払い時間<="2017-07-03 12:59:59" 
  pay_idでグループ化 
  count(pay_id) > 1 である
);

実行結果: 2.23秒

2 番目の書き方は、サブクエリと結合することです。この書き方は、上記の IN サブクエリの書き方と同じです。次のテストでは、確かに効率が大幅に向上することがわかりました。

test_table2 tpp1からtpp1.*を選択します。 
(
   pay_idを選択 
   test_table2から 
   ここで、pay_time>="2016-07-01 00:00:00" 
   かつ、支払い時間<="2017-07-03 12:59:59" 
   pay_idでグループ化 
   count(pay_id) > 1 である
) tpp2 
ここで、tpp1.pay_id=tpp2.pay_id

実行結果: 0.48秒

サブクエリの実行プランでは、外部クエリがフルテーブルスキャンであり、pay_id のインデックスが使用されていないことがわかります。

結合セルフチェックの実行プランでは、外側のレイヤー (tpp1 エイリアスのクエリ) は pay_id のインデックスを使用します。

その後、最初のクエリメソッドに強制インデックスを使用したいと思いました。エラーは発生しませんでしたが、役に立たないことがわかりました。

サブクエリが直接値の場合、インデックスを通常どおり使用できます。

MySQL の IN サブクエリのサポートは実際にはあまり良くないことがわかります。

さらに、一時テーブルを使用するケースを追加します。これは、多くの結合クエリよりも効率的ですが、IN サブクエリを直接使用するよりも効率的です。この場合、インデックスも使用できますが、この単純なケースでは、一時テーブルを使用する必要はありません。

以下は、SQL Server 2014 での同様のケースのテストです。テスト テーブルの構造と数はまったく同じです。この場合、2 つの書き込み方法は SQL Server でまったく同じであると考えられます (実行プラン + 効率)。この点では、SQL Server は MySQL よりもはるかに優れています。

以下は、sqlserver のテスト環境スクリプトです。

テーブル test_table2 を作成する
(
  id int アイデンティティ(1,1) 主キー、
  支払いID int、
  pay_time 日時、
  その他の列 varchar(100)
)
翻訳を始める
@i int = 0 を宣言する
@i<300000の場合
始める
  test_table2に値(@i、getdate()-rand()* 300、newid())を挿入します。
  もし(@i%1000=0)
  始める
    test_table2に値(@i、getdate()-rand()* 300、newid())を挿入します。
  終わり
  @i = @i + 1 と設定する
終わり
専念
行く
test_table2(pay_id) にインデックス idx_pay_id を作成します。
test_table2(pay_time) にインデックス idx_time を作成します。
行く
test_table2から*を選択 
pay_idが(
          test_table2からpay_idを選択 
          支払時間>='2017-01-21 00:00:00' 
          かつ、支払い時間<='2017-07-03 12:59:59' 
          pay_idでグループ化 
          count(pay_id) > 1 である
        );
test_table2 tpp1からtpp1.*を選択します。 
(
   pay_idを選択 
   test_table2から 
   ここで、pay_time>='2017-01-21 00:00:00'
   かつ、支払い時間<='2017-07-30 12:59:59' 
   pay_idでグループ化 
   カウント(pay_id) > 1
) tpp2 
ここで、tpp1.pay_id=tpp2.pay_id

要約: MySQLデータでは、バージョン5.7.18以降、INサブクエリは依然として注意して使用する必要があります。

以下もご興味があるかもしれません:
  • MySQL のサブクエリの例
  • MySQL のサブクエリ操作の詳細な説明
  • MySQL サブクエリ (ネストされたクエリ)、結合テーブル、複合クエリの詳細な説明
  • MySQL IN ステートメントにおける低速クエリの効率を最適化する手法の例
  • MySQL の最適化: サブクエリの代わりに結合を使用する
  • LIMIT を使用した MySQL サブクエリ IN アプリケーションの例
  • MYSQL サブクエリとネストされたクエリの最適化例の分析
  • MySQLは複数テーブル関連統計(サブクエリ統計)の例を実装します
  • MySQL ノート: サブクエリの使用法の紹介
  • MySQLサブクエリでorder byが効かない問題の解決方法

<<:  WeChatアプレット+mqtt、esp8266温度と湿度の読み取り実装方法

>>:  nginxリバースプロキシのyum設定の詳細な手順

推薦する

HTMLおよびJSPページがキャッシュされ、Webサーバーから再取得されるのを防ぎます。

ユーザーがログアウトした後、ブラウザの戻るボタンがクリックされると、Web アプリケーションは保護さ...

Vue3 でサードパーティのコンポーネントライブラリをオンデマンドでロードする方法

序文Element Plus を例に、コンポーネントとスタイルのオンデマンド読み込みを構成します。環...

Windows 10 Home EditionにDockerをインストールする方法を教えます

Redisの本やSpring Cloud Alibabaの本を執筆した際に、一部の分散コンポーネント...

MySQL トリガー: 複数のトリガー操作の作成例の分析

この記事では、例を使用して、MySQL で複数のトリガー操作を作成する方法について説明します。ご参考...

MySQL テーブル作成外部キー エラーの解決方法

データベーステーブルA: テーブル task_desc_tab を作成します ( id INT(11...

共有サイドバーを実装するためのネイティブJS

この記事では、ネイティブ JS で実装された共有サイドバーを紹介します。効果は次のとおりです。 以下...

Vue3はCSSの無限シームレススクロール効果を実装します

この記事では、CSS無限シームレススクロール効果を実現するためのvue3の具体的なコードを参考までに...

ポップアップはすべて不正なものではありません。ウェブサイトのポップアップをデザインするためのヒント

ポップアップニュースは国内のインターネットサービスでは一般的であり、リアルタイムプッシュ方式はウェブ...

Webデザインチュートリアル(8):Webページの階層と空間デザイン

<br />前回の記事:Webデザイン講座(7):Webページ制作の効率化1:必要な小言...

Linux Autofs 自動マウント サービスのインストールと展開のチュートリアル

目次1. autofs サービスの紹介2. Autofsのインストールと展開3. Autofs効果の...

SQLで同じフィールドの異なる値のデータ統計を実行する

適用シナリオ: シールのさまざまな状態に応じて、さまざまな状態のシールの数をカウントする必要がありま...

解決策 - BASH: /HOME/JAVA/JDK1.8.0_221/BIN/JAVA: 権限が不十分です

1) jdkファイルが保存されているフォルダパスを入力します私はここにいますusr/local/jd...

Ansibleを使用してTomcatをバッチでデプロイする方法

1.1 ディレクトリ構造の構築この操作は、nginx+mysql+tomcat+dbのディレクトリ構...

SpringBoot を MySQL に接続してデータを取得し、バックエンド インターフェースに書き込む方法

目次1. 新しいプロジェクトを作成する2. 依存関係を追加する3. SpringコンテナにDrive...