先ず次点の内容で言えば、
hirohiroさんのあげてらっしゃるミック先生のサイトや著書**「達人に学ぶ SQL徹底指南書」(WEBサイトの情報を再構成したものとはなりますが)、
またデータベース界の第一人者の一人ジョー・セルコ先生の「プログラマのためのSQL」**(第4版はミック先生日本語監訳)は参考となるかもしれません。
次にこれはMySQL公式とも関連してる話です。
DBMSが提供してくれている実行計画(実行プランとも言う)が信用できないと言われるとそこで試合終了となってしまうのですが、
結局はインデックスが使われてるのか、そうでないのかの判断の根拠は、
これ以外にないかなと思います。
ちなみに少し癖はあるみたいですが、
MySQLでももちろん実行計画を確認する術を提供しています。
EXPLAIN構文
MySQLのEXPLAINを徹底解説!!
※2番目のサイト様は個人ブログとなります
###インデックスに対する私見
ここからは質問者さんがあげる「個人的な意見」となり、
根拠としては薄いので読み飛ばしても結構です。
昔に関して言えばDBMSごとのオプティマイザ(経路最適化)の機能は貧相なものだったので、
どのベンダのDBMSもインデックスが効かない書き方ってものをやると、
いとも簡単にフルスキャン地獄でのパフォーマンス劣化が酷いものだったんだと思われます。
だからこそ、
- 左辺式で計算や関数を利用するとインデックスが効かない
- LIKE式では前後曖昧検索をするとインデックスが効かない
と口を酸っぱく強調されているのかなと。
(今も実行計画みると効かないDBMSも多いかも)
でも現在ではDBMSごとのオプティマイザによる経路最適化も賢くなってきているので、
もしかするとベンダによっては左辺式の計算をしてもインデックスが使われることもあるのかもしれません。
ですがインデックスの定義内容について考えて欲しいのですが、
インデックスはあくまで指定したカラムの現在格納した値に対して張られるものです。
なのでそもそも論を言うと、
計算、または関数を噛ませて加工した後の値というのは元々の値と違うのだからインデックスなんて効く方がおかしい話ではないでしょうか?
(確かに単純な四則計算とかだとど行も同じ計算ルールを適用してるのだから、それくらい融通利かせろやとも思いますが^^;)
将来的にはこの辺はオプティマイザが賢くなることにより、
インデックス選択がより柔軟となるかもしれませんが、
個人的にはその辺りが便利になり過ぎることによる、インデックスの本質が隠蔽されることは怖い気はします。
書き方を気にしなくなる面では非常に便利ですが、
それにより本質の理解が浅い開発者が増えると、
問題が発生しいざパフォーマンスチューニングが必要となった時に火を吹きそうで^^;
###ユーザ定義関数機能(関数インデックス)
左辺式に計算・関数を指定してインデックスが利かなくなる事態があるからこそ、DBMSによってはユーザ定義関数(関数インデックス)という機能を設けています。
この機能は簡単に言うと、
各種関数などで加工を行った結果に対してのインデックスとなり、
そのためここで定義した内容をそのまま左辺式に書いた場合はインデックスが適用されるようになります。
MySQLもVer5.7.6より同等の機能が実装されたようです(Generated Columns)。
###追記1
Generated Columnsは無しでしたのね、すみません^^;
後、集計関数のMAXとMINは例外的インデックスが効く理由はご存知とは思いますが、
インデックス自体がソートされた状態で登録されてるためです。
(そのためORDER BYに指定する項目に対してインデックスを張りパフォーマンス改善を図る手段を取られることあり。MAX、MINに限ってはソートが終わってるものを使えば極値取るだけだからインデックス使う方が効率的)
###追記2
少し書籍を調べてみましたが、
ことMySQLに限定するなら以下の書籍が根拠足り得るものになるかもしれません。
Effective MySQL Optimizing SQL Statements (Oracle Press)
ただし邦訳版がないことと、僕自身が当該書籍を持っていませんので、
演算・関数適用時にインデックスが効かないという点をカバーしているかの判断できませんが・・・。
(この書籍の4章に当たるサンプルコードに、
UPPER関数を利用した例が書かれてるので、
あくまでもしかしたら言及があるかもという推測レベルです^^;
###追記3
2005年刊行ということで旧い書籍とはなりますが、
Pro MySQL (The Expert's Voice in Open Source)の第2章「INDEX CONCEPT」のP66、
**「Query Structuring To Ensure Use Of Index」**の節にて、
インデックス列に関数を適用した例とその書き換えのサンプルが記載されています。
著者のjay pipes氏もMySQL界ではある程度名の知れた方とのことですが、
これを根拠とできるかは質問者様次第となると思います。
当方もMySQLのマニュアルをざっと見ましたが、
マニュアルにてオプティマイザについては、
注記
MySQL オプティマイザへの取り組みは継続中であるため、MySQL が実行する最適化のすべてをここで説明しているわけではありません。
との文面を残している以上は、
マニュアル内で最適化パターンを網羅してないのは間違いないので、
記載のない部分で100%の根拠を得るのは難しいと思われます。
実行計画を調べて毎回インデックスが効かないことを確認
他のDBMSに漏れずインデックス適用する仕組みは同様。
(というか他のDBMSで効かないけどうちでは効くよとかあったら普通にプッシュしそうな気がする)
そもそも関数インデックスという仕組みがなぜ必要なのか
これらを総合して100%に近づけるくらいしかできそうにないですね・・・
(公式に問い合わせて回答を得れるなら別なのですが)
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。
2016/09/02 04:59