【稀に役に立つシリーズ】MySQL の生成カラムと文字列関数で値を分割保存して Indexing

【稀に役に立つシリーズ】始めました
万人にはウケないけど「ちょうどこれやりたかったねん！」という需要が満たせればいいなと思っています

どういうケースで役に立つ？

例えば， Slack の投稿の URL は以下のような構造になっています．

https://{work_space}.slack.com/archives/{channel_id}/p{timestamp}

この URL を DB に保存するという要件があるとします．
そして， 投稿したチャンネルで絞り込んだり，投稿された時間を範囲で絞り込んだりしたいよね，という話になるとします．（要は ある程度構造化された文字列があって，いくつかのパーツを絞り込みの条件にしたい みたいな話だと思っていただけば分かりやすいと思います）

そうすると，

• channel_id と timestamp は別でカラム持ちたいよね
• それぞれのカラムにインデックス貼りたいよね

みたいな話になっていくと思います．
「ちゃんとカラムとして用意して，アプリケーション側で分解して保存する」という方法が割りと最初に思いつく方法だと思いますし，多くの場合それが最適かと思います．
でも今回は，そこをあえてできるだけ MySQL だけで完結できるように工夫してみたいと思います．

何を使う？

今回は， Generated Column （生成カラム） と文字列関数 （SUBSTRING_INDEX()） を使ってみたいと思います．

Generated Column

生成カラムは，簡単にいうと

• 他の列から計算される値を保持する
• データが変更されるたびに自動的に再計算されて更新される
• 普通のカラムと同様に扱うことができる（参照できる，インデックスが貼れる など）

といったものです．

生成カラムは，例えば次のように定義できます

CREATE TABLE table1 (
    column1 INT,
    column2 INT,
    column_sum INT AS (column1 + column2),
);

この場合， column_sum というカラムが生成カラムに当たります． column1 + column2 という式の結果を自動的に計算して保存してくれるというわけです．

自動的に計算されるため，データの挿入時に column_sum に格納する値を使用者が与える必要はなく，以下のようなクエリを書くだけで済みます．

INSERT INTO table1 (column1, column2) VALUES (2, 3);

そうすると，以下のようなレコードが作られます．

column1	column2	column_sum
2	3	5

文字列関数

文字列関数は，その名の通り文字列に対して作用する関数です．例えば

• CONCAT() ・・・ 結合
• MATCH() ・・・ 全文検索
• TRIM() ・・・ 空白削除
• REGEXP() ・・・ 正規表現
• SUBSTRING() ・・・ 部分文字列の取得

などなど，様々な関数が用意されています．

今回， URL から部分文字列の切り出しを正規表現でやりたかったのですが，MySQL が提供する正規表現系の機能があまり高機能ではなかったため SUBSTRING_INDEX() を使って実現したいと思います．

CHECK 制約

CHECK 制約は，テーブルに 挿入 / 更新 されるデータの値を検証するための条件を定義する機能です．
これを使うことで，テーブルに格納されるデータの整合性を担保できるようになります．

今回は， SUBSTRING_INDEX() を使って確実にチャンネル ID とタイムスタンプを切り出せることを担保するために， URL カラムに対して CHECK 制約をかけようと思います．

CHECK 制約構文には，制約条件として ブール式（true/false を返す式） を与えて上げる必要がありますが，今回はこの部分に正規表現を利用したいと思います．

REGEXP_LIKE() を使うことで 文字列が正規表現と一致するかどうか を検証することができるため，今回はこれを使うことにします．

いざ実践

SUBSTRING_INDEX() を使ってカラムの生成ルールを定義した， CREATE TABLE 文は以下のようになりました．

CREATE TABLE slack_urls (
  id INT AUTO_INCREMENT PRIMARY KEY, 
  full_url TEXT, 
  CHECK (
    REGEXP_LIKE(
      full_url, '^https:\/\/xxxx.slack.com\/archives\/[A-Z0-9]+\/p[0-9]{16}$'
    )
  ), 
  channel_id VARCHAR(20) AS (
    SUBSTRING_INDEX(
      SUBSTRING_INDEX(full_url, '/archives/', -1), 
      '/', 
      1
    )
  ), 
  ts VARCHAR(16) AS (
    SUBSTRING_INDEX(
      SUBSTRING_INDEX(full_url, '/archives/', -1), 
      '/p', 
      -1
    )
  )
);

CHECK 制約部分

full_url TEXT, 
CHECK (
    REGEXP_LIKE(
      full_url, '^https:\/\/xxxx.slack.com\/archives\/[A-Z0-9]+\/p[0-9]{16}$'
    )
), 

full_url という TEXT 型のカラムに対して， Slack のメッセージリンクの形式にマッチするような正規表現を制約として与えました．今回 Slack のワークスペースは xxxx ということにしています．

SUBSTRING_INDEX() 部分

channel_id VARCHAR(20) AS (
    SUBSTRING_INDEX(
        SUBSTRING_INDEX(full_url, '/archives/', -1), 
        '/', 
        1
    )
), 
ts VARCHAR(16) AS (
    SUBSTRING_INDEX(
        SUBSTRING_INDEX(full_url, '/archives/', -1), 
        '/p', 
        -1
    )
)

Slack の URL が

https://{work_space}.slack.com/archives/{channel_id}/p{timestamp}

という形式になっているため，一旦 /archives/ 以降を取得します．
そうすると {channel_id}/p{timestamp} の部分だけが切り出されますので， / や /p で更に分割して前半と後半をそれぞれ channel_id や ts としてあげることで，正しく切り出すことができます．

INSERT について

今回のテーブルにレオードを追加する場合，たとえば以下のようなクエリを実行することになります．

insert into slack_urls (full_url) values ('https://xxxx.slack.com/archives/C26E33588C6/p1686818914362529');

生成カラムの良いところは， full_url だけ書けばあとは DB がルールに従って勝手にカラムの値を生成してくれる点にあると思います．
もちろん，ロジックが分散してしまうことのデメリットもありますが，ある程度の分解ロジックを DB 側に持たせられるのは，アプリケーションのコードがシンプルになって良いのかなと思います．

パフォーマンス検証

今回の工夫でどれくらいパフォーマンスが改善されるかを検証してみました．
今回は，「特定のチャンネルに投稿された url をすべて取得する」というシーンを想定しました．

準備

一応ある程度リアルなデータ量で検証したかったため，以下のようなランダムデータを 5 万件ほど用意しました．

比較するクエリ

今回比較するクエリは以下の通り

【A】 full_url に対して LIKE で部分一致

SELECT * FROM slack_urls WHERE full_url LIKE '%C4F33B9533F%';

【B】 channel_id に対して完全一致(インデックスあり)

CREATE INDEX slack_urls_channel_id_index ON slack_urls(channel_id);
SELECT * FROM slack_urls WHERE channel_id = 'C4F33B9533F';

【C】 channel_id に対して完全一致（インデックスなし）

SELECT * FROM slack_urls WHERE channel_id = 'C4F33B9533F';

検証結果

パターン	実行時間	実行計画
A	90.5 ms	-> Filter: (slack_urls.full_url like '%C4F33B9533F%') -> Table scan on slack_urls
B	2.8 ms	-> Index lookup on slack_urls using slack_urls_channel_id_index (channel_id='C4F33B9533F')
C	66.8 ms	-> Filter: (slack_urls.channel_id = 'C4F33B9533F') -> Table scan on slack_urls

わーい！早くなったぞ〜！（当たり前）

実は ．．．

MySQL のオプティマイザって結構賢いんですよね．
生成カラムに対してインデックスを張った時，カラムの生成ルールをそのまま検索条件にしたときにも同様にインデックスが効きます．

どういうことかというと，今回

channel_id VARCHAR(20) AS (
    SUBSTRING_INDEX(
        SUBSTRING_INDEX(full_url, '/archives/', -1), 
        '/', 
        1
    )
)

という生成ルールを定義しているため，

CREATE INDEX slack_urls_channel_id_index ON slack_urls(channel_id);

というインデックッスは

SELECT * FROM slack_urls WHERE (SUBSTRING_INDEX(SUBSTRING_INDEX(full_url, '/archives/',  -1), '/', 1)) = 'C4F33B9533F';

というクエリに対しても，同じように効きます．賢い！

また， MySQL 8.0.13 からは式の値のインデックスをサポートしているので，実は生成カラムを使わなくてもチャンネル ID 部分に対してインデックスを貼ることもできます．

したがって検索だけで言えば生成カラムは必要ないんですが，今回のソリューションではレコードを取得してきたあとチャンネル ID やタイムスタンプへのアクセスが容易になるという点で，手段として選択するメリットはあるかなと思います．

まとめ

SQL なんかで無理やりやらずにアプリケーション側で分割して普通に挿入してください．