Q&A
日時の日までの絞り込みなので可能といえば可能だと思うのですがbetweenに変な制御があるのかなと調べています
先日仕事中にbetweenはパーティションがきかないことがあったり、その人の過去の経験でmysqlの不具合でパーティションが削除されたことがあると言っていたのですが本当でしょうか？そういったことがあったらしのでin句を使えと言われたのですが、BETWEENはオプティマイザの指定された範囲のインデックスのノードを1回の操作で比較できるので優秀な句だと思って利用しています。
DBのバージョンは5.6です
回答5件
0
BETWEENはパーティションの刈り込みに有効です。
MySQL 5.6 リファレンスマニュアル 19.2.1 RANGE パーティショニング
テーブルのパーティショニングに使用されるカラムに直接依存するクエリーを頻繁に実行する。たとえば、EXPLAIN PARTITIONS SELECT COUNT(*) FROM employees WHERE separated BETWEEN '2000-01-01' AND '2000-12-31' GROUP BY store_id; などのクエリーを実行する場合、MySQL はパーティション p2 のみをスキャンする必要であることをすばやく判断できます (残りのパーティションに WHERE 句を満たすレコードが含まれるはずがないため)。これがどのように実現されるかについての詳細は、セクション19.4「パーティションプルーニング」を参照してください。
投稿2017/08/01 01:05
総合スコア1149
0
ベストアンサー
その人の過去の経験でmysqlの不具合でパーティションが削除されたことがあると言っていたのですが本当でしょうか？
⇒MySQLの生みの親から警告があるくらい、当時結構騒がれていたので、
私も頭の片隅くらいに印象が残ってましたが、あまり知られてなかったんですね。
MySQL4系においても、ちょっと複雑だったり特殊なSQLを書くだけで、すぐバグが出てたので、
割とバグがあるのが当たり前の時代でしたが、、、。
CREATE TABLE文等により、パーティションを作るのは簡単ですが、
パーティションをまたぐ登録や参照や変更、削除等を行うと、
それなりの複雑な処理や制御が必要です（mysqlの中身の話）。
当時のリリースで（今でも）バグが出てもしょうがないと思います。
※パーティションがなければ、1:nだったテーブルと物理ファイルの関係に紐づく様々な情報や操作が、
パーティションを使う事で、1:n*pになるので、紐づく様々な情報や操作の取扱いでmysqlの中身は
ちょっと考えただけで大変なのが分かります。
ちなみに、パーティション機能と同じような事を
パーティション機能を使わずテーブル設計で行えるので、
大容量のデータを取扱う場合だったとしても、
パーティション機能を使わずに性能に問題ない作りにする事は可能です。
（代替案があるので、mysqlバグの可能性があるパーティションは
使わないという選択は決して変な話ではありません。）
BETWEENはオプティマイザの指定された範囲のインデックスのノードを1回の操作で比較できるので優秀な句だと思って利用しています。
⇒パーティション機能にバグがなく、
BETWEENで指定する範囲がパーティションの条件と一致する場合に限り、
少ない回数の操作になり、優秀である可能性は高いですが、
その条件下だったとしても、
パーティションなし×In句等より優秀か？については設計やデータ等次第です。
betweenはパーティションがきかないことがあったり
そういったことがあったらしのでin句を使え
⇒パーティションとBetweenを同時に使って、当時、バグでもあったんでしょうね（知りませんが）。
mysqlの中身としても、In句よりもBetween句の方が処理の中身は複雑なので、
Between句×パーティションでバグがあり、
In句×パーティションでバグがなかったとしてもそこまで不思議ではないです。
なので、Between句よりIn句を使おうというのは、当時のバグの名残りかもしれませんが、
それを抜きにしても、性能面でBetween句×パーティションよりIn句の方が良い場合も多々あります。
ただ、設計やSQLにもよるので、気になるのであれば、現場での設計において、
大容量なデータを用意し、Between句とIn句でmysqlの中身がどんな事をしているか考えながら、
実測されるのが良いと思います。
※yambejp様の回答の引用・補足ですが、
「inは便利な処理ですがロジック的にインデックスが効きにくく」
⇒具体例としては、OR句 × IN句の場合、IN句で指定する件数が多すぎる場合等、INDEXの効きが悪い時があります。
※Between句を利用するテーブルが
DELETE ・INSERTを繰り返すようなテーブルの場合、
In句の方が優秀で、Between句だと性能劣化する事もあります。
（データ依存で発生したりしなかったりする厄介なケースもあります）
まとめ
何を使うかはケースバイケースです。
どれ（EXISTS句等を含めて）を使っても動くのであれば、どれでも良いです。
より最適な方法をパッと選んでSQLを書く事ができるのは熟練技であり、
考えながら実測した事がある経験者だけです。
投稿2017/08/01 16:17
編集2017/08/02 02:56
総合スコア760
0
mysqlの不具合でパーティションが削除されたことがあると言っていたのですが本当でしょうか？
過去にデータ削除する際に、BETWEEN指定で誤って広範囲を削除してしまったので、
「in句で限定して処理した方が良いよ」というアドバイスではないでしょうか。
質問者様が保守作業に従事されておられるなら、in句で指定するのは面倒ですが誤ってデータを消すことが無いので確実です。
そうでないとbetweenに対して、in句が引き合いに出される意味が掴めないので。
投稿2017/08/01 01:34
編集2017/08/01 01:41
総合スコア25195
0
betweenはパーティションがきかない（中略）in句を使え
うーん、そもそもBETWEENとINは代替が効くようなものではないと思うのですが…
投稿2017/08/01 01:02
総合スコア145184
0
BETWEENを代替するとしてたらA以上B以下をandでとることですよね？
オプティマイズされるので効率は変わらないと思いますが
論理的には有効な考え方でrangeで検索するには最適だと思います。
ただしよく使うrangeはシンボリックな値を入れておいたほうが
当然高速化できます。（たとえば、月次や年次の集計など）
逆にinは便利な処理ですがロジック的にインデックスが効きにくく
スピードの点であまりお勧めされることは少ないと認識しています。
以前はinでやることはexistsで置き換えることが多かったと記憶しています
投稿2017/08/01 01:23
総合スコア114835
あなたの回答
tips
プレビュー
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。
2017/08/01 01:26
2017/08/01 01:36
2017/08/01 01:53 編集