by Hayato Matsuura.

列の連結


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

最初は、日付型・時刻型に 関する例ですが、逆順で考えてみましょう。次の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を 使うはっきりしたフィルタです。この手法は完璧とは言い難いですが、多くの場合では十分なものでしょう。

ヒント

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

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

著者について

Markus Winandの写真

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

彼の本

カバー『SQLパフォーマンス詳解』

核心をわかりやすく 解説。

Markusから購入します
(送料無料+PDF)

Amazonで購入
(印刷版のみ)

Do not use offset for pagination

Learn why

Visit my sibling!A lot changed since SQL-92!

Use The Index, Luke のカップは

ステッカー、コースター、本、コーヒーマグ。 学習に必要なものすべて。

今すぐ購入

Connect with Markus Winand

Markus Winand on LinkedInMarkus Winand on XINGMarkus Winand on Twitter
“Use The Index, Luke!” by Markus Winand is licensed under a Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 Unported License.
法律上の通知 | お問い合わせ | 無保証 | 商標 | プライバシーとGDPR | CC-BY-NC-ND 3.0 license