「MySQL は関数や演算が適用されたカラムのインデックスを使用できない」の根拠は？

一般に、ある事象が「可能である」ことを示すのは簡単ですが、
「不可能である」ことを示すのは難しいものです。

なぜなら、「可能である」ことを示すには実際に実行してみせるだけでよいのに対して、
「不可能である」ことを示すためには背理法を使うか、
始めからそのような機能・能力が備わっていないことを証明してみせる必要があるからです。

Teratailの回答で、

MySQLでは、SQLの式の中でカラムに何らかの関数や演算を適用すると、
そのカラムに張られたインデックスを使用できない

ことに言及する機会が何度かありました。
（例えば https://teratail.com/questions/43855）

これが（たぶん）事実であることは経験的に知っており、
ほうぼうのブログ記事などでもそのように書かれているのを目にすることができるのですが、
これについての信用できる根拠を見つけることができず、毎回忸怩たる思いをしております。

そこで、この（たぶん）事実に対する「信用できる根拠」をご存知の方がいましたら、ご教示願いたく思います。

ただし、「信用できる根拠」とは、以下のようなものに限らせていただきます。

MySQL のマニュアルに記載された情報
MySQL 開発チームのブログに記載された情報

次点として

MySQL の中の人が書いた記事
DB や MySQL の専門家だと一般に認知されている方が書いた記事

逆に、以下のような回答は無用に願います。

MySQL の InnoDB テーブルの B ツリー・インデックス以外についての話題

-> 話が散乱するのを避けるため、今回はこれに限定させていただきます。

「ジェネレーテッド・カラムを使えば可能」

-> 上と同じ理由で、これも無しに願います。

B ツリー・インデックスの原理的な解説

-> B ツリー・インデックスが不可能だからといって、
-> MySQL の B ツリー・インデックスも不可能とは限りません。
-> オプティマイザが十分に賢ければ、key_col - 1 = 0をkey_col = 1に変換してから
-> インデックスを適用することも理論的には可能だと考えるからです。

得体の知れない方（失礼w）が書いた記事

-> 「個人的な知り合いで、信用できる人だよ」とかも無しに願います。

また、これについての例外・反証も大歓迎です。

例えば、関数 MIN() と MAX() は、例外的にインデックスが使用可能です。
https://dev.mysql.com/doc/refman/5.6/ja/mysql-indexes.html

MySQL はこれらの操作にインデックスを使用します。

（中略）
特定のインデックス設定されたカラム key_col に対して、MIN() あるいは MAX() 値を見つけるため。

よろしくお願いします。

行動規範の内容に同意します

回答3件

解決してないようですので、
こちらの退会済ユーザさんのように
https://teratail.com/questions/29899

MySQLのメーリングリストで質問してはどうでしょう？
yoku0825さん奥野幹也さん木村明治さんのような
MySQLの専門家が回答してくれるかもしれません。

投稿2016/09/02 03:04

garasya

総合スコア16

KiyoshiMotoki

2016/09/02 04:59

garasya様回答ありがとうございます。さっそく、試してみます。

行動規範の内容に同意します

先ず次点の内容で言えば、
hirohiroさんのあげてらっしゃるミック先生のサイトや著書**「達人に学ぶ SQL徹底指南書」(WEBサイトの情報を再構成したものとはなりますが)、
またデータベース界の第一人者の一人ジョー・セルコ先生の「プログラマのためのSQL」**(第4版はミック先生日本語監訳)は参考となるかもしれません。

次にこれはMySQL公式とも関連してる話です。
DBMSが提供してくれている実行計画(実行プランとも言う)が信用できないと言われるとそこで試合終了となってしまうのですが、
結局はインデックスが使われてるのか、そうでないのかの判断の根拠は、

実行したSQLの「実行計画」を確認する

これ以外にないかなと思います。

ちなみに少し癖はあるみたいですが、
MySQLでももちろん実行計画を確認する術を提供しています。
EXPLAIN構文
 MySQLのEXPLAINを徹底解説!!
※2番目のサイト様は個人ブログとなります

###インデックスに対する私見
ここからは質問者さんがあげる「個人的な意見」となり、
根拠としては薄いので読み飛ばしても結構です。

昔に関して言えばDBMSごとのオプティマイザ(経路最適化)の機能は貧相なものだったので、
どのベンダのDBMSもインデックスが効かない書き方ってものをやると、
いとも簡単にフルスキャン地獄でのパフォーマンス劣化が酷いものだったんだと思われます。

だからこそ、

左辺式で計算や関数を利用するとインデックスが効かない
LIKE式では前後曖昧検索をするとインデックスが効かない

と口を酸っぱく強調されているのかなと。
(今も実行計画みると効かないDBMSも多いかも)

でも現在ではDBMSごとのオプティマイザによる経路最適化も賢くなってきているので、
もしかするとベンダによっては左辺式の計算をしてもインデックスが使われることもあるのかもしれません。

ですがインデックスの定義内容について考えて欲しいのですが、
インデックスはあくまで指定したカラムの現在格納した値に対して張られるものです。

なのでそもそも論を言うと、
計算、または関数を噛ませて加工した後の値というのは元々の値と違うのだからインデックスなんて効く方がおかしい話ではないでしょうか？
(確かに単純な四則計算とかだとど行も同じ計算ルールを適用してるのだから、それくらい融通利かせろやとも思いますが^^;)

将来的にはこの辺はオプティマイザが賢くなることにより、
インデックス選択がより柔軟となるかもしれませんが、
個人的にはその辺りが便利になり過ぎることによる、インデックスの本質が隠蔽されることは怖い気はします。

書き方を気にしなくなる面では非常に便利ですが、
それにより本質の理解が浅い開発者が増えると、
問題が発生しいざパフォーマンスチューニングが必要となった時に火を吹きそうで^^;

###ユーザ定義関数機能(関数インデックス)
左辺式に計算・関数を指定してインデックスが利かなくなる事態があるからこそ、DBMSによってはユーザ定義関数(関数インデックス)という機能を設けています。

この機能は簡単に言うと、
各種関数などで加工を行った結果に対してのインデックスとなり、
そのためここで定義した内容をそのまま左辺式に書いた場合はインデックスが適用されるようになります。
MySQLもVer5.7.6より同等の機能が実装されたようです(Generated Columns)。

###追記１
Generated Columnsは無しでしたのね、すみません^^;

後、集計関数のMAXとMINは例外的インデックスが効く理由はご存知とは思いますが、
インデックス自体がソートされた状態で登録されてるためです。
(そのためORDER BYに指定する項目に対してインデックスを張りパフォーマンス改善を図る手段を取られることあり。MAX、MINに限ってはソートが終わってるものを使えば極値取るだけだからインデックス使う方が効率的)

###追記２
少し書籍を調べてみましたが、
ことMySQLに限定するなら以下の書籍が根拠足り得るものになるかもしれません。
Effective MySQL Optimizing SQL Statements (Oracle Press)
ただし邦訳版がないことと、僕自身が当該書籍を持っていませんので、
演算・関数適用時にインデックスが効かないという点をカバーしているかの判断できませんが・・・。
(この書籍の4章に当たるサンプルコードに、
UPPER関数を利用した例が書かれてるので、
あくまでもしかしたら言及があるかもという推測レベルです^^;

###追記３
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で効かないけどうちでは効くよとかあったら普通にプッシュしそうな気がする)

DBMS仕組み

そもそも関数インデックスという仕組みがなぜ必要なのか

これらを総合して100%に近づけるくらいしかできそうにないですね・・・
(公式に問い合わせて回答を得れるなら別なのですが)

投稿2016/08/15 15:45

編集2016/08/18 22:56

Panzer_vor

総合スコア1636

KiyoshiMotoki

2016/08/15 16:57

KotoriMaturi様回答ありがとうございます。不勉強につき、紹介いただいた書籍は未読でしたので、さっそく確認してみたいと思います。

KiyoshiMotoki

2016/08/18 06:34

KotoriMaturi様紹介いただいた書籍を確認させていただきましたが、「MySQLでは、SQLの式の中でカラムに何らかの関数や演算を適用すると、　そのカラムに張られたインデックスを使用できない」ことの証左となるような記述は見受けられませんでした。『プログラマのためのSQL 第4版』には P.749 に「関数は呼び出されるたびに評価されるので、関数が使用された列にインデックスを使用することはできない。」という記述があり、『達人に学ぶSQL徹底指南書初版』には P.202 から P.205 にかけてインデックスが使用できないケースが列挙されていましたが、いずれもインデックスの一般論であり、「MySQLでも同様である」ことを示すものではありませんでした。また、『Effective MySQL Optimizing SQL Statements』については、経済的な理由により確認できておりません。

Panzer_vor

2016/08/18 23:03

> KiyoshiMotokiさん申し訳ありません。当方の回答の書き方がまずかったです。ご指摘の通り、回答であげた2冊はMySQLに限定したものでない一般論です。お手数をおかけしてしまい申し訳ありませんでした。また、旧い書籍となりますがMySQLの関数利用時のインデックスについて言及している書籍を1冊確認しましたので追記しておきます。 (根拠たるかはわかりかねますが・・・)

行動規範の内容に同意します

DB や MySQL の専門家だと一般に認知されている方が書いた記事

mysqlに限定した話ではない上に、古い記事ですが、これを書いてる方は「SQL実践入門──高速でわかりやすいクエリの書き方 (WEB+DB PRESS plus)」とかの書籍を書いているみたいなので、次点に含まれますかね？(単に使えませんと書かれているだけで、なぜ？どういう理由で？はありません。)

以下あまり意味の無い話になるかも知れませんが、
「MySQLでは」とすると未来のバージョンでは一部対応しちゃうかも知れません。

select * from table where no * 2 > 10;
こういったSQLは「*2」によってnoのindexが無意味化しないので使ってくれても良いように思います。ただ(/2)にして右辺に移すなど使う側の注意で回避可能で、これが不可能なケースをパット思いつきませんでした。
それに大抵は元の値の並びが役に立たなくなります。
select * from table where price * n > 1000; はnとpriceにindexがあっても...

で、やっぱり出来なくてもいいんじゃ？と思うのですが、SQLserverでは最初のSQLを実行するとindex scanになるそうです。(indexは使ってるけどあまり意味は無い？)

投稿2016/08/14 19:10

編集2016/08/14 19:18

hirohiro

総合スコア2068

KiyoshiMotoki

2016/08/15 04:23

hirohiro様ご回答ありがとうございます。 > 「MySQLでは」とすると未来のバージョンでは一部対応しちゃうかも知れません。については、むしろそれを期待したいところですね。「使用者の注意しだいで回避可能」とはいえ、便利なことに間違いはありませんので。

hirohiro

2016/08/15 08:28

そうですね。ただそうすると今度は左辺(カラム)側を弄っても良いケースと、indexが使えないケースを初心者が理解するのが難しくなるかも知れません。 n*2で問題無いと経験を得たユーザがダメなケースのコードを量産するかもしれませんし、難しいところですね。(大量のテストデータを準備してアナライズを確認するようなユーザはそもそもこのようなミスはしませんし、そうすると仮にダメな記述をしていてもそれに気がつくのはデータが増えて処理が重くなった時になります。)

行動規範の内容に同意します

あなたの回答