mysqlの負荷について

Question

DATE_FORMAT(TestDatabase.registdate, '%Y-%m') = '2016-07'

このような条件文を書いて上の人にレビューしていただいたところ、

日付型を文字型 にするのではなく、固定値側を 文字型を日付型 に
変換して比較する方が性能が出るはず。
DB項目を変換する場合は、DBのレコード数=変換数ですが、固定値を変換すれば1回で済む。

という感想が返ってきました。
'2016-07'を日付形式にした方が良いということだと思います。

ただ、registdateというDB項目には「2016-07-14 09:06:01.901」という形で格納されているので、やはりDATE_FORMAT必要なんじゃないでしょうか？

こう質問返した方がよいでしょうか。

ご意見お待ちしております。

Answer 1

私も

日付型を文字型 にするのではなく、固定値側を 文字型を日付型 に

変換して比較する方が

よい、という意見には同意ですが、

DB項目を変換する場合は、DBのレコード数=変換数ですが、固定値を変換すれば1回で済む。

については
「問題はそこではない」
という意見です。

DBサーバの能力やレコードの件数にもよりますが、システム全体の実行時間に比べると、その理由による性能の差など、微々たるものに過ぎないと考えるからです。

もっと重要な問題は、MySQLの場合、
「カラムに何らかの関数や演算を適用すると、そのカラムに張られたインデックスを使用できない」
ということです(※)。

どういうことかというと、例えTestDatabase.registdateにインデックスが張られていたとしても、

DATE_FORMAT(TestDatabase.registdate, '%Y-%m') = '2016-07'

という式ではそのインデックスを使用できない、ということです。

一方、

TestDatabase.registdate >= '2016-07-01' AND TestDatabase.registdate < '2016-08-01'

や

TestDatabase.registdate BETWEEN '2016-07-01 00:00:00' AND '2016-07-31 23:59:59.999'

といった式では、そのインデックスを使用できます。

現在、このカラムにインデックスが張られていないとしても、将来、インデックスが必要になる可能性を考慮して、今のうちから後者のような形式にしておくほうが無難だと、私は考えます。

---

※信用できる根拠が見つからず、ご提示できないのが恐縮ですが、例えば以下のような場合、MySQLはインデックスを使用できません。

# カラムに演算を適用している
SELECT * FROM tbl_name WHERE key_col + 1 = 0;

# カラムに（MIN、MAX以外の）関数を適用している
SELECT * FROM tbl_name WHERE DATE_FORMAT(key_col, '%Y-%m') = '2016-07'

# LIKE値がワイルドカード文字で始まっている
SELECT * FROM tbl_name WHERE key_col LIKE '%Patrick%';

3番目のSQLに関してのみ、根拠として以下を見つけることができました。
http://dev.mysql.com/doc/refman/5.6/ja/index-btree-hash.html

Comments

[退会済みユーザー]

ありがとうございました。

[退会済みユーザー]

ありがとうございました。

Answer 2

TestDatabase.registdate に索引が設定されていることを確認して、
MySQLのEXPLAINを徹底解説!!
実務に見合うデータを用意して、
統計情報を取得。
実行計画の確認。
処理時間を実測。
で最もパフォーマンスが良い方法を選択して下さい。

Comments

[退会済みユーザー]

ありがとうございました。

Answer 3

きっと、こういうSQLにすることを期待されているのではないかと。

SELECT * from TestDatabase 
where YEAR(TestDatabase.registdate) = '2016'
and MONTH(TestDatabase.registdate) = '7';

SELECT * from TestDatabase 
where DATE(TestDatabase.registdate) BETWEEN '2016-07-01' AND '2016-07-31';

実際に性能が変わるかは、検証してみないことには、判断つきませんが…

Comments

[退会済みユーザー]

ありがとうございました。