Q&A
その中身は動的じゃないかと思うんですが、テンポラリテーブルですか？
前提・発生している事象
a,b,cの3カラムがある、500万行ほどのtableがあります。
a-bに対して複合PRIMARY KEYを設定しています。a,b,cは全てvarcharです。
下記のようなSQLを実行し、explainを確認します。
select * from tbl where (a,b) in (('*', '*')) select * from tbl where (a,b) in (('*', '*'),('*', '*'),...)
in句の中が1つの場合、explainのtypeは「const」となりました。
2つ〜350こ程度までの場合、typeは「range」となりました。
351以上の場合、typeは「all」となりました。
force index (PRIMARY)を付けてみましたが、状況は変わりませんでした。
質問
・なぜin句の中が多くなるとフルスキャンになってしまうのでしょうか
・上記SQLを高速化するにはどうしたら良いですか（in句の中身は2000個程度までを想定しております）
以上、よろしくお願い致します。
補足情報（FW/ツールのバージョンなど）
MySQL 5.7.14
###追記(3/27 23:24)
みなさまご回答ありがとうございます。
一度テンポラリテーブルを作成して、joinすることで0.003sec程度で結果が返ってきました。
しかしながらPKのみで、join または inすることは、両者ともあまり変わらないような気もしますが、内部的に処理が大きく異なるのでしょうか。
なぜin句だとフルスキャンになってしまうのか、引き続き回答お待ちしております。
回答5件
0
裏を取ってないから違ってたらすいませんね。
MySQLに限った事じゃないんですけど、RDBMSはSQLクエリを実行する前に実行計画を立ててオプティマイザが最適化を行います。
今回の場合は、MySQLのオプティマイザが『どうもインデックス使うよりもフルスキャンした方が速そうだ』と判断したんじゃないかと思います。
そういったケースは割と多くあります。
実際の実行時間ってどのぐらいだったでしょう？ レコード数に比してそれほど遅くないならそういう事だと思います。
で、MySQLの場合、使用するINDEXを指定できたりして
使用するインデックスを明示する – MySQL インデックスヒントで最適化
こんな感じで、インデックスが利用できるかもしれません。
何分、最近あんまりDBいじってないので、記憶違いだったらすいません。
投稿2018/03/27 12:06
退会済みユーザー
総合スコア0
0
in句の中身は2000個程度
ここまで増えるならテーブルを１つつくってjoinしてやればよいのでは？
投稿2018/03/27 12:17
総合スコア114843
逆に2000個のデータが動的である方が運用しにくいと思いますが
もし動的で処理するならテンポラリが妥当ですね
回答ありがとうございます。
tmpテーブル作成→joinで0.003secで結果が返ってくるようになりました。
> なぜin句だとフルスキャンになってしまう
inに関してはパフォーマンスが落ちるのは有名な話で
それでも最近はオプティマイザがある程度インテリジェントに
判断してくれるようになったみたいですけどね
やはりinを羅列しすぎるとオプティマイザの限界があるのでは
ないでしょうか？（ロジックはよくわかりません）
0
すいません、かなり推測と勘が入っています。
なぜin句の中が多くなるとフルスキャンになってしまうのでしょうか
2つ〜350こ程度までの場合、typeは「range」となりました。
ここから少々おかしくて、全て件数で「const」になっているのが期待値だと思います。
(そもそも複数件の期待値に対してconstになるのか未調査)
ちなみにinではなくORで繋げても同じでしょうか？
rangeは範囲検索になるので、in句で入力されたデータのバラつきによって、indexを検索する範囲が大きく異なります。
351以上の場合にはindexのバラつきが規定値以上になったため、allになったのだと思います。
luckerさんもおっしゃっているように本の索引のようなもので、
検索する対象が、例えばA～Eが対象なのかA～Sが対象なのかでrangeかallか変わるのだと思います。
rangeからallになった(type:indexがない)理由ですが、
インデックスを利用すると、インデックスが見つかった後に実テーブルにアクセスに行きます。
インデックスのテーブルと実テーブルの2回アクセスが必要。
今回だとcカラム取得のために必要な処理です。
そこで実テーブルにアクセスしに行くくらいなら、最初からallで実テーブル検索のほうが早いと判断されたのだと思います。
おそらくselectからcカラムを抜くとtypeがindexになるかと。
参考までに
join または inすることは、両者ともあまり変わらないような気もしますが・・・
joinのexplainは取りましたか？おそらくtypeが「eq_ref」になっていると思います。
普通のRDBですと、joinはhash join、inはnested loop joinになるはずで、
処理の時間はjoinのほうが圧倒的に早いです。
ただMySQLはnested loop joinだけという記事を目にするのですが、
typeに「eq_ref」が定義していあるということは、
MySQLの内部で別の処理が走っているのかもしれません。
投稿2018/03/28 04:58
総合スコア1400
後学の為簡単に回答できるのであれば、ご教示下さい。
私の回答のリンクにある「なぜMySQLのサブクエリは遅いのか。」に書かれてる内容から、「in()で固定値を設定している場合でも、内部的にはサブクエリーとしている」と解釈し、内部的にやってる事にヒント文を記述しても利用されないと思ったのですが、そんな事はなくヒント文は有効なのでしょうか？
＞MySQLは内部的にINを直接処理することができないので、EXISTSに変換することでSQL的には相関のないサブクエリも相関サブクエリになってしまうのである。
とあるので、解釈は間違っていないと思います。
ただ今回のご提示のヒント句はちょっと別かと思っています。
ヒント句に対して、SQLがどう実行されるかが具体的に分からないため、
あくまで憶測でしかないのですが、ざっと以下の2ケースが考えられました。
１．ヒント句は、オプティマイザが下した結果をダイレクトに上書きする。
２．ヒント句は、オプティマイザが評価するインデックスを指定する。
１はオプティマイザを無視してヒント句のインデックスを利用する
（そもそもそんなことが出来るか分かりませんが。）
２はオプティマイザのインプットとして使うインデックスを指定している（or指定しない）
以下の観点から、２になるのだと思います。
今回のケースで333secさんが実証してくれているように、
件数が少ない場合はオプティマイザはインデックスを正しく有効に評価してくれています。
件数が多くなった場合でも、オプティマイザは同じようにインデックスを評価はずです。
ただ最終的にインデックスを利用しないとオプティマイザが判断しているのだと思います。
要するにヒント句でインデックスを指定する前から、
オプティマイザは、同じインデックスを最初から評価していたため、ヒント句を書いても結果が変わらなかった。
のだと思いました。
ボクの個人的な考えですので参考程度に。
直接的な回答になってなくてすいません。
コメントありがとうございます。
この質問中ではヒント構文を使用した問い合わせが行われた実績は無いんですよ。
オプティマイザが内部的に生成した後に、ヒント文を含めた実行計画を立てるなら有効だし、
内部的に作るものだからヒント構文は使用されないということなら無効という事かと考えています。
試せば良いんですけど、環境作るところから始めないと駄目なので、横着してしまいました。
では、ヒント句を別として考えた場合、
オプティマイザが内部生成したサブクエリーにインデックを使っているかどうかという観点であれば、おそらく有効だと判断できますよね。type:rangeで検索しているわけですから。
そう考えるもヒント句のインデックスも有効なんじゃないかなぁと思うのですか。。
ただ、内部生成したクエリーとヒント句のインデックスが一致しているか確認は出来ませんし、
性能に関する検証は、データのばらつきなど統計情報に左右されるので、
ローカルで環境整えても、正しい回答は難しいと思います。
私もインデックスは適用されそうとは思っていますが、内部的に云々・・という情報をみてしまうと、このような質問のケースで、提供していい情報なのかを戸惑ってしまいます。
検証については同意です。
事象が発生している質問者さんの環境で試して貰うのが一番ではあります。。
ご回答どうもありがとうございます。お返事遅くなりました。
頂いた回答を元に再度実験してみます。後ほど結果を記入します。
0
通りすがりの者です。
mysql5.7のrangeで使えるメモリ使用量の設定値を超えてたりしませんか。このメモリサイズより大きくなるとfullscanになってしまいます。ためしにこの値を大きくして試してみると問題の切り分けができそうです。
投稿2019/05/08 08:03
総合スコア10
0
SQL
1select a,b,count(*) from tbl group by a,b
上記SQLを実行すると、出力される件数がパターン数で、count(*)はそれぞれのパターンの件数です。
in()での条件によって抽出される件数が多くなればなるほど、
オプチマイザーが、インデックスを使ったコストをかけない方が高速だと判断する閾値に到達し、
フルスキャンを行うことになります。
※(a,b)でプライマリーでしたね。上記は無駄なので無視して下さい。
500万行に対して、フルスキャンとなった場合の抽出件数は何件程度なのでしょう？
（これも書いてますね）
インデックス検索になった方が必ず高速ということであれば、インデックスヒントを使用してみて下さい。
(in に関するMySQLの振る舞いの問題ということなので、ヒントを与えても無駄だと思うので)
500万件に対して2,000件を抽出するということでいいんでしょうか？
それから、SQL自体は省略した内容じゃないのでしょうか？
フルスキャンする原因はin()ではない別な記述のような感じですけど。
追記
なぜin句だとフルスキャンになってしまうのか
以下が参考になるかと。
なぜMySQLのサブクエリは遅いのか。
MySQLは内部的にINを直接処理することができないので、EXISTSに変換することでSQL的には相関のないサブクエリも相関サブクエリになってしまうのである。
きちんと気をつけて使えばサブクエリも高速に実行される。
もちろんJOINに書き換えた方が速いのは言うまでもないが
in でも 相関サブクエリーでインデックスが適切なら遅くないってことなので、
一時テーブルで試されてはどうですか。
投稿2018/03/27 12:13
編集2018/03/28 03:51総合スコア25195
あなたの回答
tips
プレビュー
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。
退会済みユーザー
2018/03/27 12:14 編集
2018/03/27 14:37