関数インデックスがMySQLでも8.0から使えるようになりました。
JSONやカラムに関数を適用するとき、計算時などがユースケースとして有名ですが
実際のワークロードに合わないことが多い気がします。
そこで他の有益そうな事例を紹介します
ユースケースはPostgreSQLも同じなので参考に
比較演算子にインデックスをうまく効かせる
比較の場合、複合インデックスが途中で停止してしまうのはご存じのとおりです
つまり複合インデックス(a,b,c)とwhere a = 1 AND b > 1 AND c = 1が組み合わさると
aとbまでしかインデックスが使われないということ
range scanとなったら走査が終わってしまうイメージ。
ここは関数インデックスを使い
CREATE INDEX idx_comp on TBL (a, (b>1), c);
とすれば、aからcまで複合インデックスをフル活用して絞り込みできます
b>1というルックアップ専用の判別フラグカラムを作っているイメージです
ただし、MySQLの既知不具合によりクエリを以下のように書き換える必要があります
WHERE a = 1
AND ((b > 1) = TRUE) -- 今のところ比較しないとインデックスが効かない謎
AND c = 1
(上記の例だけなら(a,c,b)にインデックスを付与すればOKでしたが)
WHERE a > 1 AND b > 1 AND c > 1のような複数比較という今までは絶望的な条件でも
CREATE INDEX idx_comp on TBL ((a>1), (b>1),(c>1))
とインデックス(と若干のクエリ修正)だけで解決できます
欠点
見ての通りインデックスに定数が入り込みます
そもそも値が固定されていないと対応できません。
ロジックが入り込むため、ロジック改修時にインデックスも修正が必要になります
今のところMySQLバグのためクエリに若干の修正が入ることも難点です
否定にインデックスをうまく効かせる
考え方や動きは比較とほぼ同じです
複合インデックスa,b,cとwhere a = 1 AND b<>1 AND c = 1が組み合わさると
aとbまでしかインデックスが使われないということ
ここで登場するのが関数。数学の「関数」という単語を思い出してください
CREATE INDEX idx_comp on TBL (a, (ABS(b-1)>0), c);
と絶対値を使うことで否定をフラグに変換できます。
WHERE a = 1 AND ((ABS(b-1)>0)=TRUE) AND C = 1
もちろん否定と比較の複合も可能です。
＊(ABS(b-1)をインデックス化して>0と比較してもOK。これなら=TRUEは不要)
欠点
比較の欠点に加えて、確実にクエリ書き換えが発生する点と、若干わかりにくい点です。
OR文にインデックスを効かせる
OR文もインデックスが効きづらいことで有名です
WHERE price > 100 OR stock < 50と来たらpriceかstockのどちらかしか絞り込めない
というのが基本的な考え方です。
(これぐらい単純なら分割した条件をUNIONでくっつければ両方とも効かせられるのですが、もう少し複雑な場合は難しい。)
そこで以下のように関数を使ってロジックを変換します。
CREATE INDEX idx_greatest_price_stock ON products (
(GREATEST(price - 100, 50 - stock))
);
そして条件をWHERE GREATEST(price - 100, 50 - stock) > 0;に書き換えればOK
一部のOR文は関数を使い関数インデックスに書き換えることができます。
column IS NOT NULL
これもrange scanになる可能性があります。
CREATE INDEX idx_f on TBL ((column IS NOT NULL ));
とインデックスを付与した状態で
(column IS NOT NULL) = TRUEと若干書き換えればrange scanではなくなります
事前nullチェックですね
flag IS NOT TRUE
false / true / nullという3値の場合。
そもそもNOT NULL制約にするなりデータ投入時に加工するなりすべきですが...
CREATE INDEX idx_f on TBL ((flag IS NOT TRUE ));
とインデックスを付与した状態で
(flag IS NOT TRUE) = TRUEと若干書き換えばOK
テキストマッチング
WHERE url = 'https://www.example.com'といった長くて代わり映えのないテキストの比較は計算コストが重くインデックスも高価なので、
WHERE url_crc=CRC32('https://www.example.com/') -- ハッシュ化
AND url = 'https://www.example.com'
にするというのは実践ハイパフォーマンスMySQLにも書いてあるテクニックです。
(第4版日本語版は...)
ただ、url_crcというカラムを事前準備しておく必要があるのが難点でした
このタイプの絞り込みは関数インデックスが得意とするところです
CREATE INDEX idx_crc on TBL (CRC32(url));
といった絞り込み専用インデックスを用意しましょう。
カラムALTERなしは本番に影響少なく便利ですね。
uuidをvarcharに、ましてや主キーにしてしまっている人はいないと思いますが、
もしいたとしても関数である程度カバーできます。
文字列の評価は高価なのでここでのチューニングが必要と判明したら関数インデックスで数値などに変換してみましょう。
IN / BETWEENを変換する
INやBETWEENもrange scanになります。
そこで関数インデックスで変換します
CREATE INDEX idx_in on TBL ((ids IN (0001, 0002)));
CREATE INDEX idx_bet on TBL ((ids between 0001 AND 0002));
決め打ち前提なので実用性は少なそうですが...
range scan候補をとにかく変換する
定数が入ることに目を瞑れば、関数インデックスを使い無理やり変換可能です。
LIKE 'STR%'でもなんでもOK
LIKEで中間や後方一致でもインデックスを効かせる案
LIKEは前方一致しかインデックスが効きません。
中間や後方一致でインデックスを単純に効かせるなら
CREATE INDEX idx_l1 on TBL ((str LIKE '%STR'));
として評価自体をインデックスにしてしまうことです。
が、当然STRが定まっていないと無意味です。
文字数が定まっているなら
-- 5文字めから
CREATE INDEX idx_l2 on TBL (SUBSTRING(str, 5));
といった文字列関数で加工しておけば検索の応用が効きます。
メールアドレスのドメインも@で分割しておけば絞り込みできます。
ただドメインは生成列のほうが融通ききそうです。
ORDER BY
今までの議論同様、WHERE句がrangeで終わってしまってもORDER BYを強引に対応できることがあります
WHERE a = 1 AND b > 1 ORDER BY cについて
今まではcでの並べ替えは基本file sortしかありませんでしたが
CREATE INDEX idx_comp on TBL (a, (b>1), c);
とすれば、
aからcまでインデックスを効かせられます
WHERE a = 1 AND b > 1 ORDER BY c, b
なら
CREATE INDEX idx_comp on TBL (a, (b>1), c, b);
ORDER BY後に関数を設定しているときも関数インデックスが狙えます
ORDER BY a + bなど
その他
-
DATEやYEARを使った日付絞り込み - 価格計算
- 大文字・小文字
- FLOORによる桁切り捨て
rand()といった非決定関数は関数インデックスに使えません
この結果ランダムソートなどは難しいです
似た機能のイメージ表
右に行くほど実カラムに近い
| (関数) インデックス |
生成列 (仮想列) |
生成列 (stored)物理 |
普通の カラム |
|
|---|---|---|---|---|
| 値取得 | ✕ | ◯ | ◯ | ◯ |
| 計算負荷 | 更新時(DB) | 取得時(DB) | 更新時(DB) | アプリ側 |
| アプリ修正 | 無 | 少 | 少 | 大 |
方式ごとに負荷がかかるタイミングと場所が異なるのでワークロードを評価検討しておきましょう
関数インデックスは、where句でデータの絞り込みはしたいが、
値は普段不要なときに便利です。
通常のインデックス同様、更新負荷が増加します。
事前計算結果をインデックスだけに格納しておくイメージ
手っ取り早い目先の問題を解決するだけの方法もよいのですが、
いつかは理想の姿に置き換えないとサービスの永続に影響が出ます。
データ負債はいつまでも残ります
最後に
関数によって論理を変換すればインデックスの幅が広がります。