この説では、複合 インデックスに影響する扱いにくい処理について取り上げます。
最初は、日付型・時刻型に 関する例ですが、逆順で考えてみましょう。次のMySQLのクエリは、日付と時刻の両方の列に範囲条件を適用するために、それらを連結して います。
SELECT ...
FROM ...
WHERE ADDTIME(date_column, time_column)
> DATE_ADD(now(), INTERVAL -1 DAY)このクエリは、過去24時間の全てのレコードを選択します。(DATE_COLUMN, TIME_COLUMN)に作られたインデックスがあっても、正しく使うことはできません。検索は、インデックスの
作られた列に対して行われるのではなく、関数が生成したデータに対して行われてしまうからです。
協力してください
この記事が気に入ったら、私の書いた本「SQLパフォーマンス詳解」や私によるトレーニングもきっと気にいるはず。
この問題を避けるために、日付と時間の両方を含んだデータ型(例、
MySQLのDATETIME)を使うことができます。それによって、
列に対して関数の呼び出しを使わなくてよくなります。
SELECT ...
FROM ...
WHERE datetime_column
> DATE_ADD(now(), INTERVAL -1 DAY)しかし残念なことに、こういった問題に気付いた時には、既にテーブルを変更できない場合が往々にしてあります。
次の方法として、データベースが関数 インデックスをサポートしているなら、前に出てきた欠点を知った上で、 それを使う手があります。MySQLを使っている場合は、関数インデックスは選択肢にありません。
一部だけでもアクセス
述語になるように、DATE_COLUMN,TIME_COLUMNに
作られた複合インデックスを使うように、クエリを書きかえることもできます。
このためには、DATE_COLUMNに追加の条件を付ける必要があります。
WHERE ADDTIME(date_column, time_column)
> DATE_ADD(now(), INTERVAL -1 DAY)
AND date_column
>= DATE(DATE_ADD(now(), INTERVAL -1 DAY))追加した条件は冗長ですが、アクセス述語としてDATE_COLUMNを
使うはっきりしたフィルタです。この手法は完璧とは言い難いですが、多くの場合では十分なものでしょう。
この方法を、日付と時刻のデータを文字列型の列に保存する時にも使用可能ですが、日付と時刻を単語順に並べた時に、ISO
8601 (YYYY-MM-DD HH:MM:SS)で提示されているような、
時刻順にもなるフォーマットで書く必要があります。次のクエリは、Oracleの
TO_CHAR関数をこのために使う例です。
SELECT ...
FROM ...
WHERE date_string || time_string
> TO_CHAR(sysdate - 1, 'YYYY-MM-DD HH24:MI:SS')
AND date_string
>= TO_CHAR(sysdate - 1, 'YYYY-MM-DD')複数列に範囲条件を適用する時の問題には、「次ページの取得」という節でも直面することになります。 その時の問題を軽減するのにも、ここで挙げた代替策を使います。
逆のケース、つまりアクセス述語としては使えないようになってしまうけれども、条件を抽象化したい時があるかも知れません。このような
場合の問題については、LIKE条件に対するバインドパラメータの影響について取り上げた
部分で見てきました。次の例を考えてみましょう。
SELECT last_name, first_name, employee_id
FROM employees
WHERE subsidiary_id = ?
AND last_name LIKE ?SUBSIDIARY_IDとLAST_NAMEにそれぞれ
インデックスがあると考えた場合、どちらがこのクエリには効果的でしょうか？
検索語のどこにワイルドカードがあるか分からない状況では、まともな
答えを出すことは難しいでしょう。オプティマイザにとっても、「推測する」他
ありません。もし先頭にワイルドカードが常にあると事前に
知っているならば、LAST_NAMEにあるインデックスを
オプティマイザが無視してしまうように、LIKEの条件をあえて抽象化してしまうことができます。
SELECT last_name, first_name, employee_id
FROM employees
WHERE subsidiary_id = ?
AND last_name || '' LIKE ?LAST_NAMEに空文字列を加えてしまう方法もありますが、
それは最後の手段にしておきましょう。どうしても必要な場合だけ、ならその方法を使うこともできます。
