アプリケーション負荷を抑えるテーブルレイアウト設計案

ある名簿があって、その人名の検索をしたい

こんな要望があったとします。
その名簿のレイアウトをこんなふうに設計してみました。
入力するデータは「氏名漢字」「氏名かな」「氏名アルファベット」の3つとします。

CREATE TABLE TBL_HUMANLIST (
　ID VARCHAR(10) NOT NULL PRIMARY KEY,
　NAME_KANJI VARCHAR(50) NOT NULL,
　NAME_KANA VARCHAR(50) NOT NULL,
　NAME_ALPHABET VARCHAR(100) NOT NULL
)

ココで、どういうふうに検索をしたいのかをもっと具体的に突き詰めていくと、こんな要望が上がっていたことがわかったとします。

1) 検索対象は「氏名漢字」「氏名かな」「氏名アルファベット」とする
2) 「氏名かな」にはひらがな、カタカナどちらが入ってもいいとする、ただし全角
3) 検索する際には、「漢字」「ひらがな」「カタカナ」「アルファベット」のいずれを入れてもいい
4) 「氏名かな」にひらがなデータしかなくとも、「カタカナ」で検索してちゃんとヒットする、逆も然り
5) 「氏名アルファベット」は大文字小文字混在しているとする、ただし半角
6) 「氏名アルファベット」の大文字小文字の区別関係なく、アルファベット検索できるようにする
7) 漢字は旧字体・新字体の区別なく検索する…ということまではしなくてよい
8) 検索条件として入力されたものの部分一致したものを全件を表示する

イヤ、お客様というのはワガママですから、コレぐらいの要望は朝飯前に仰ってきますよね。(^^;)

ココで、先のテーブルレイアウトのまま、検索したとします。
すると、こんなクエリを書くことになります。(Transact-SQL方式で記述しています)

SELECT T.*
FROM TBL_HUMANLIST T
WHERE
　T.NAME_KANJI LIKE '%' + @検索キーワード + '%'
OR カナ変換関数(T.NAME_KANA) LIKE '%' + カナ変換関数(@検索キーワード) + '%'
OR UPPERCASE(T.NAME_ALPHABET) LIKE '%' + UPPERCASE(@検索キーワード) + '%'

このクエリではこんな現象が起きています。

A) ORを使うことになるので遅い
B) 比較対照のカラムに変換関数を利用しているので遅い

遅いということは、システムだけでなく、利用するユーザにも負担を与えるということです。
コレを回避するための一つの方法として

テーブルレイアウト設計を変更する

という手段をとることがあります。
ココで負担を減らすためのターゲットになるのは、上記のAとBです。

A→ORを使わなくてよいテーブルレイアウトにする、もしくはクエリを工夫する
B→変換関数を使わなくてよいテーブルレイアウトにする

まずAの改良から。

クエリの工夫
ORを利用するよりUNIONを利用するほうがなんぼか負荷を軽減できる可能性があります。
よって、先のクエリはこんなふうに書き換えることが出来ます。

SELECT T.*
FROM TBL_HUMANLIST T
WHERE
　T.NAME_KANJI LIKE '%' + @検索キーワード + '%'
UNION
SELECT T.*
FROM TBL_HUMANLIST T
WHERE
　カナ変換関数(T.NAME_KANA) LIKE '%' + カナ変換関数(@検索キーワード) + '%'
UNION
SELECT T.*
FROM TBL_HUMANLIST T
WHERE
　UPPERCASE(T.NAME_ALPHABET) LIKE '%' + UPPERCASE(@検索キーワード) + '%'

結果は同じはずです。
なお、本当に負荷がかかっていないかどうか、ベンチマークテストなどを行って確認してくださいね。

テーブルレイアウト設計の工夫
DBは基本的に文字変換があまり得意ではありません。
また、関数はじめ関数は極力利用しない方向にもって行く、使わなければいけない場面だけ使うよう勤めるのがよいと思います。
関数だけでなく、式も同様です。極力しなくていい計算はやめておくのが吉だと思います。

というわけで、関数を使わないでよいテーブルレイアウト設計をするための一つとして、今回は「検索用のカラム」を作るという方法があると思います。
「検索用のテーブル」でもよいかもしれません。
ココでは「検索用のテーブルを作成してみます。

CREATE TABLE TBL_HUMANLIST_FORSEARCH (
　ID VARCHAR(10) NOT NULL PRIMARY KEY,
　NAME_KANJI VARCHAR(50) NOT NULL,
　NAME_KANA VARCHAR(50) NOT NULL,
　NAME_ALPHABET VARCHAR(100) NOT NULL
)

このテーブルを作成したところで、左記のTBL_HUMANLISTテーブルに、このTBL_HUMANLIST_FORSEARCHテーブルに対して、INSERT/UPDATE/DELETEのタイミングでトリガを仕込みます。

INSERT時
INSERT INTO TBL_HUMANLIST_FORSEARCH
SELECT
ID,
NAME_KANJI,
カナ変換関数(NAME_KANA),
UPPERCASE(NAME_ALPHABET)
FROM TBL_HUMANLIST

UPDATE時
UPDATE TBL_HUMANLIST_FORSEARCH T
SET
T.NAME_KANJI = T0.NAME_KANJI,
T.NAME_KANA = カナ変換関数(T0.NAME_KANA),
T.NAME_ALPHABET = UPPERCASE(T0.NAME_ALPHABET)
FROM TBL_HUMANLIST T0
WHERE T.ID = T0.ID

DELETE時
DELETE FROM TBL_HUMANLIST_FORSEARCH
WHERE ID = TBL_HUMANLIST.ID

このトリガをTBL_HUMANLISTに仕込むことによって、TBL_HUMANLISTに対してデータが更新・削除されるたびに、TBL_HUMANLIST_FORSEARCHという検索用テーブルに検索用情報がたまっていくというわけです。
もちろん、トリガが信用できないDBも中にはありますので(ってほとんどそうかも(爆))、トリガを使わずにプログラムなどで上記を実装してやるほうが確実だと思います。

このテーブルを利用すると、こんなクエリを書くことが出来ます。

SELECT T.*
FROM TBL_HUMANLIST T
WHERE
　EXISTS(SELECT 'X' FROM TBL_HUMANLIST_FORSEARCH T0
WHERE T0.ID = T.ID AND
　T0.NAME_KANJI LIKE '%' + @検索キーワード + '%')
UNION
SELECT T.*
FROM TBL_HUMANLIST T
WHERE
　EXISTS(SELECT 'X' FROM TBL_HUMANLIST_FORSEARCH T0
WHERE T0.ID = T.ID AND
　T0.NAME_KANA LIKE '%' + カナ変換関数(@検索キーワード) + '%')
UNION
SELECT T.*
FROM TBL_HUMANLIST T
WHERE
　EXISTS(SELECT 'X' FROM TBL_HUMANLIST_FORSEARCH T0
WHERE T0.ID = T.ID AND
　T0.NAME_ALPHABET LIKE '%' + UPPERCASE(@検索キーワード) + '%')

コレでだいぶ検索が軽くなったと思います。
更新・削除より、検索のほうがはるかに利用頻度が高いシステムなのであれば、更新・削除に時間がかかったとしても、検索をするときになるべく軽くなるよう、工夫してやるのがよいと思われます。

さらにもう一工夫
お気づきの方もおられるでしょうが、同じ検索キーワードを複数のカラムに対して検索をかけています。
ということは、複数のカラムを一つにまとめてやることで、もう少しクエリが軽くなるのでは？と考えてみましょう。

まず、3つのカラムを一つにまとめる方法としてクエリ…をあげてみます。一応。

SELECT T.*
FROM TBL_HUMANLIST T
WHERE
　EXISTS(SELECT 'X' FROM TBL_HUMANLIST_FORSEARCH T0
WHERE T0.ID = T.ID AND
　T0.NAME_KANJI + T0.NAME_KANA + T0. NAME_ALPHABET
LIKE '%' + @検索キーワード + '%')

最後の行、長いですね。(^^;)
先にも書いたようにクエリ中で式を書くのはDBとしてはあまり得意でない分野を頑張れ！といわれているようなものですので、やめておいたほうがいいです。
ココではテーブルレイアウトの工夫をする方向でやってみたいとます。

CREATE TABLE TBL_HUMANLIST_FORSEARCH (
　ID VARCHAR(10) NOT NULL PRIMARY KEY,
　NAME_KANJI VARCHAR(50) NOT NULL,
　NAME_KANA VARCHAR(50) NOT NULL,
　NAME_ALPHABET VARCHAR(100) NOT NULL,
　NAME_SEARCH VARCHAR(300) NOT NULL,
)

NAME_SEARCHというカラムを追加してみました。
ここにNAME_KANJI、NAKE_KANA、NAME_ALPHABETの3カラムを合体させたデータを突っ込めばいいのです。(その方法は略(^^;))

そして、上記のテーブルレイアウトを反映させたクエリはこんな感じ。

SELECT T.*
FROM TBL_HUMANLIST T
WHERE
　EXISTS(SELECT 'X' FROM TBL_HUMANLIST_FORSEARCH T0
WHERE T0.ID = T.ID AND
　T0.NAME_SEARCH LIKE '%' + @検索キーワード + '%')

だいぶとすっきりしました。

最後に
今まで、検索のためのクエリの負荷を軽くする方向ばかりに注目してやってきました。
ところが、この方法、HDDのほうに負荷をかけているのです。
DBのデータのせいで、テーブルの要領も確実に容量が大きくなり、結果、HDDの空き容量が圧迫されます。

そのあたりも考慮に入れた上で、システムの設計をあーでもないこーでもないと試行錯誤してバランスをとっていただければ、と思います。

以上、Doblogスタッフ様もそういうことやって欲しいな…という布石を込めた記事でした。
表示部分、工夫できませんか？多分、変換プログラム・複雑なクエリ多用して表示していると思いますので、この辺、参考にならないかな…という希望的観測。sigh
コレを実行するにはカラムサイズの制限とかあると思うので難しいかとは思いますが…考える余地は…ないことはないですよね？