関数インデックスを活用する実践的なユースケース

関数インデックスがMySQLでも8.0から使えるようになりました。
JSONやカラムに関数を適用するとき、計算時などがユースケースとして有名ですが
実際のワークロードに合わないことが多い気がします。
そこで他の有益そうな事例を紹介します
ユースケースはPostgreSQLも同じなので参考に


# 比較演算子にインデックスをうまく効かせる

比較の場合、複合インデックスが途中で停止してしまうのはご存じのとおりです
つまり複合インデックス`(a,b,c)`と`where a = 1 AND b > 1 AND c = 1`が組み合わさると
aとbまでしかインデックスが使われないということ
range scanとなったら走査が終わってしまうイメージ。


ここは関数インデックスを使い

```sql
CREATE INDEX idx_comp on TBL (a, (b>1), c);
```

とすれば、aからcまで複合インデックスをフル活用して絞り込みできます
`b>1`というルックアップ専用の判別フラグカラムを作っているイメージです

ただし、MySQLの既知不具合によりクエリを以下のように書き換える必要があります
```sql
WHERE a = 1 
AND ((b > 1) = TRUE) -- 今のところ比較しないとインデックスが効かない謎
AND c = 1
```

https://bugs.mysql.com/bug.php?id=99775

(上記の例だけなら`(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までしかインデックスが使われないということ


ここで登場するのが関数。数学の「関数」という単語を思い出してください
```sql
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`でくっつければ両方とも効かせられるのですが、もう少し複雑な場合は難しい。)

そこで以下のように関数を使ってロジックを変換します。

```sql
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になる可能性があります。

```sql
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制約にするなりデータ投入時に加工するなりすべきですが...

```sql
CREATE INDEX idx_f on TBL ((flag IS NOT TRUE ));
```
とインデックスを付与した状態で
`(flag IS NOT TRUE) = TRUE`と若干書き換えばOK

# テキストマッチング

`WHERE url = 'https://www.example.com'`といった長くて代わり映えのないテキストの比較は計算コストが重くインデックスも高価なので、
```sql
WHERE url_crc=CRC32('https://www.example.com/') -- ハッシュ化
AND url = 'https://www.example.com'
```
にするというのは実践ハイパフォーマンスMySQLにも書いてあるテクニックです。

https://www.oreilly.co.jp/books/9784873116389/

~~(第4版日本語版は...)~~

ただ、`url_crc`というカラムを事前準備しておく必要があるのが難点でした
このタイプの絞り込みは関数インデックスが得意とするところです

```sql
CREATE INDEX idx_crc on TBL (CRC32(url));
```

といった絞り込み専用インデックスを用意しましょう。
カラムALTERなしは本番に影響少なく便利ですね。

uuidをvarcharに、ましてや主キーにしてしまっている人はいないと思いますが、
もしいたとしても関数である程度カバーできます。

文字列の評価は高価なのでここでのチューニングが必要と判明したら関数インデックスで数値などに変換してみましょう。


# IN / BETWEENを変換する
INやBETWEENもrange scanになります。

そこで関数インデックスで変換します

```sql
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`は前方一致しかインデックスが効きません。

中間や後方一致でインデックスを単純に効かせるなら
```sql
CREATE INDEX idx_l1 on TBL ((str LIKE '%STR'));
```
として評価自体をインデックスにしてしまうことです。
が、当然STRが定まっていないと無意味です。

文字数が定まっているなら

```sql
-- 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しかありませんでしたが

```sql
CREATE INDEX idx_comp on TBL (a, (b>1), c);
```

とすれば、
aからcまでインデックスを効かせられます


`WHERE a = 1 AND b > 1 ORDER BY c, b`
なら
```sql
CREATE INDEX idx_comp on TBL (a, (b>1), c, b);
```

ORDER BY後に関数を設定しているときも関数インデックスが狙えます
`ORDER BY a + b`など


# その他

- `DATE`や`YEAR`を使った日付絞り込み
- 価格計算
- 大文字・小文字
- FLOORによる桁切り捨て

rand()といった非決定関数は関数インデックスに使えません
この結果ランダムソートなどは難しいです


# 似た機能のイメージ表

右に行くほど実カラムに近い
|  | (関数)<br>インデックス | 生成列<br>(仮想列) | 生成列<br>(stored)物理 | 普通の<br>カラム |
|:-:|:-:|:-:|:-:|:-:|
| 値取得  | ✕  | ◯  | ◯  | ◯  |
| 計算負荷  | 更新時(DB)  | 取得時(DB)  | 更新時(DB)   | アプリ側 |
| アプリ修正  | 無  | 少  | 少  | 大  |


方式ごとに負荷がかかるタイミングと場所が異なるのでワークロードを評価検討しておきましょう

関数インデックスは、where句でデータの絞り込みはしたいが、
値は普段不要なときに便利です。
通常のインデックス同様、更新負荷が増加します。

事前計算結果をインデックスだけに格納しておくイメージ

手っ取り早い目先の問題を解決するだけの方法もよいのですが、
いつかは理想の姿に置き換えないとサービスの永続に影響が出ます。
データ負債はいつまでも残ります

# 最後に

関数によって論理を変換すればインデックスの幅が広がります。