今日は、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サブクエリは依然として注意して使用する必要があります。 以下もご興味があるかもしれません:
|
<<: WeChatアプレット+mqtt、esp8266温度と湿度の読み取り実装方法
rpmインストールパッケージを使用してmysqlをオフラインでインストールします。参考までに準備:公...
目次Dockerを起動するDockerを停止するPython 呼び出しスクリプト最近、日々のテストで...
MySQL には 3 つの日付型があります。日付(年-月-日)テーブル test(hiredate ...
CI/CD の概要CIワークフロー設計Gitコードバージョン管理システムはコマンドラインでのみ管理で...
参考までに、HTMLフォームの使い方を9つの簡単な例で分析します。具体的な内容は次のとおりです。 1...
序文実際、クエリ中に結果に番号が付けられるこのような状況に遭遇したことは一度もありません。同僚が転職...
最近、Zoom ビデオ会議をテストし、100 人が同時に会議に参加することをシミュレートする必要があ...
この質問をされたとき、私は無知で頭が真っ白になりました。もちろん、正しく答えられませんでした。私はず...
目次序文1. MySQL メインストレージエンジン: 2. さまざまなストレージエンジンがテーブルを...
目次1. はじめに2. 設定手順1. はじめに1. NAT モード (VMnet8) は、仮想マシン...
スレッドが同時に実行される場合、スレッドがリソースを競合してデータの曖昧さが生じるのを防ぐために、重...
目次1. ソフトウェアとシステムイメージ2. 仮想マシンを作成する3. CentOS8をインストール...
TranslateThis URL: http://translateth.is Google 翻訳...
この記事は、参考のためにMySQL 8.0.16のインストールグラフィックチュートリアルを記録してい...
Docker パッケージは、デフォルトの CentOS-Extras リポジトリにすでに含まれていま...