列の連結


この説では、複合 インデックスに影響する扱いにくい処理について取り上げます。

最初は、日付型・時刻型に 関する例ですが、逆順で考えてみましょう。次のMySQLのクエリは、 日付と時刻の両方の列に範囲条件を適用するために、それらを連結して います。

SELECT ...
  FROM ...
 WHERE ADDTIME(date_column, time_column)
     > DATE_ADD(now(), INTERVAL -1 DAY)

このクエリは、過去24時間の全てのレコードを選択します。 (DATE_COLUMN, TIME_COLUMN)に作られた インデックスがあっても、正しく使うことはできません。検索は、インデックスの 作られた列に対して行われるのではなく、関数が生成したデータに対して 行われてしまうからです。

このウェブサイトにぴったりのカップは僕たちのショップにあります。
#見た目もいい感じだし、ここでの僕の仕事を支えてくれています

この問題を避けるために、日付と時間の両方を含んだデータ型(例、 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を 使うはっきりしたフィルタです。この手法は完璧とは言い難いですが、 多くの場合では十分なものでしょう。

Tweet this tip

ヒント

範囲条件で複数の列を連結する場合は、 代表的な列に対する冗長な条件を付け加えましょう。

PostgreSQLでは、 行値文法を 使うのが望ましいでしょう。

この方法を、日付と時刻のデータを文字列型の列に保存する時にも 使用可能ですが、日付と時刻を単語順に並べた時に、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_IDLAST_NAMEにそれぞれ インデックスがあると考えた場合、どちらがこのクエリには効果的でしょうか?

検索語のどこにワイルドカードがあるか分からない状況では、まともな 答えを出すことは難しいでしょう。オプティマイザにとっても、「推測する」他 ありません。もし先頭にワイルドカードが常にあると事前に 知っているならば、LAST_NAMEにあるインデックスを オプティマイザが無視してしまうように、LIKEの条件を あえて抽象化してしまうことができます。

SELECT last_name, first_name, employee_id
  FROM employees
 WHERE subsidiary_id = ?
   AND last_name || '' LIKE ?

LAST_NAMEに空文字列を加えてしまう方法もありますが、 それは最後の手段にしておきましょう。どうしても必要な場合だけ、ならその 方法を使うこともできます。

Photo of Markus Winand
Markus Winand氏は、開発者がSQLパフォーマンスを改善するお手伝いをしています。 彼は、SQL Performance Explainedの 著者でもあり、出張トレーニングhttp://winand.at/での リモート講義も 行っています。