非正規化によるパフォーマンス改善、などと書くと、多くの真面目なプログラマーの方にフルボッコにされそうな酷いお話です。わたしも大嫌いです、こういう発想は。
ただ、今扱っているアプリケーションで、DB周りが非常に重くなってきてしまい、indexの検討など通常考えられることを色々試した挙句、最後にこの禁じ手？で一気に解決したので、メモだけしておきます。
もともとのテーブルは、
CREATE TABLE `TABLE_NAME` (
`PRIMARY_KEY` int(11) NOT NULL auto_increment,
`HOGE` varchar(255) NOT NULL default ”,
`FUGA` int(11) NOT NULL default ‘0’,
`BIG_COLUMN` longtext NOT NULL,
PRIMARY KEY (`WORD_NUM`),
)
みたいな感じで（実際は沢山カラムがあります）、まぁごく平凡なテーブルでした。参照も更新もされるし、数十件程度をまとめて取ってくる場合もあります。
問題はこのBIG_COLUMNというlongtextです。
最初のうちは良かったのですが、件数が増えるに従って、単純なSelectでも非常に時間がかかるようになりました。もちろん、必要な場合以外はこのカラムを選択対象に入れていないのですが、それでも重いです。
このカラムは主キーをキーに一行だけ取得または更新・挿入する場合のみに見られるものなので、そうした場合だけ選択対象に入れています。
このカラムと全然関係ない、例えば
SELECT HOGE,FUGA FROM TABLE_NAME WHERE PRIMARY_KEY=5
のようなクエリでもパフォーマンスが低下しました。
巨大なテクストが入っているので、仕方ないのかもしれません。
先述の通り、indexの再検討や複合indexなど、色々思いつくことをやってみたのですが、ふと考えて、テーブルを分割してみました。
CREATE TABLE `TABLE_NAME` (
`PRIMARY_KEY` int(11) NOT NULL auto_increment,
`HOGE` varchar(255) NOT NULL default ”,
`FUGA` int(11) NOT NULL default ‘0’,
PRIMARY KEY (`WORD_NUM`),
)
CREATE TABLE `TABLE_NAME2` (
`PRIMARY_KEY` int(11) NOT NULL auto_increment,
`BIG_COLUMN` longtext NOT NULL,
PRIMARY KEY (`WORD_NUM`),
)
みたいな感じです。もう一つのテーブルは、行数は最初のテーブルと全く同じ、主キーも同一で、主キーと巨大カラムの二つだけがあります。
選択する時は結合するのではなく、必要な場合にのみTABLE_NAME2に対して改めてクエリを発行します。基本的にTABLE_NAME2は一行単位でしか参照・更新することがありません。
すると当たり前ですが、パフォーマンスが劇的に向上しました。
アプリケーション側で配慮すべき要素が少し増えましたが、幸いそれほど入り組んだシステムでもないので、修正も最小限で済みました。
非常に泥臭いというか、恥ずかしいというか、できればやりたくないタイプの方法ですが、この場合、非正規化といっても同じカラムがあちこちにできてしまうという程のものではなく、単純にテーブルを縦に割ったようなものです。まぁ、みっともないですが、ギリギリ許される範囲かな・・とも思います。実際、この泥臭い方法がなければ切り抜けられなかったので・・。