MySQLのvarchar型とパフォーマンスチューニング

　今やRDBMSとしてはOracleに次いでポピュラーなMySQLですが、varchar型を操作する際に注意すべき点がいくつかあります。

クエリ

LIKE検索

　よく使うLIKE検索ですが、完全一致、前方一致、部分一致、後方一致検索のうち、インデックスが適切に効くのは完全一致と前方一致のみです。

-- インデックスが効かないクエリの例
SELECT `name` FROM `user` WHERE `name` LIKE '%一郎';  -- 後方一致
SELECT `name` FROM `user` WHERE `name` LIKE '%一%';  -- 部分一致


-- インデックスが効くクエリの例
SELECT `name` FROM `user` WHERE `name` LIKE '鈴木%';  -- 前方一致
SELECT `name` FROM `user` WHERE `name` LIKE '鈴木 一郎';  -- 完全一致

インデックスとは？

　ところで、RDBが有する機能のひとつであるインデックスについて、ごくごく簡単に解説してみます。

　インデックスとは、その名が表すとおり、DB上のデータに対する「索引」です。

　たとえば、膨大な情報量のある国語辞典などでは、項目を五十音順に目次として書き出していくことで、目的のページを探し出すことを容易にしています。

　もし、無秩序にデータが格納されていると、500ページ以上もある国語辞典を1ページ目から探していかなければなりません。これには膨大な労力がかかります。

　RDBも同じことです。1件に対する検索速度は人間が知覚できないほどに高速ですが、10万件、100万件とレコードが増えるにつれて、その件数分、リクエストされた値と比較する処理が必要になります。

　これを解決するのがインデックスというわけです。

なぜ部分一致、後方一致ではインデックスが効かないのか？

　単純なことですが、「部分一致、後方一致用のインデックスが作成されていないから」です。後方一致用のインデックスは、作ろうと思えば前方一致用インデックス生成のアルゴリズムを反転させる形で容易に作成できます。

　現実世界の辞書でも同じように、目次から辿れるのは五十音順のものしかありません。もちろん、後方一致用に目次を作ることもできますが、そもそもの話、需要がないのです。

でも、一郎さん（後方一致）に一致する結果がほしい

　これは、データベース設計の問題です。こういったケースが想定される場合は、データを適切な粒度に区切って格納するように工夫します。

　たとえば、「名前」でひとくくりにするのではなく、「姓」「名」として格納すれば、効率よく検索が可能ですね。

　あるいは、それらの要素を逆順にしたインデックス用のカラムを作成するのもよいでしょう。こうすることによって、前方一致検索と同様の速度をもったクエリが完成します。

郎一　木鈴
郎二　木鈴
郎二　藤佐

　名前が「二郎」で終わるレコードを検索するクエリ

SELECT `name` FROM `user` WHERE `name` LIKE '郎二%';

　しかし、このような場合では、「藤二郎」などもヒットしていしまいます。あくまで「名前だけ」を検索したい場合は、先に述べたように、カラムを分けてしまうのがよい方法です。

　また、こういった少し捻った実装は、プログラム上は動いたとしても、メンテナンス性の面で好ましくありません。やむを得ない場合以外は、セオリーに沿った設計をもってシンプルに実装を行いましょう。

部分一致でもインデックスを効かせたい！

　MySQLには、全文検索エンジンが組み込まれていますから、全文検索インデックスを作成すれば、部分一致であっても高速なスキャンが可能です。詳しい説明は省略いたしますが、「そういうこともできるんだな」程度に頭の片隅にインプットしておいてください。

ALTER TABLE product ADD FULLTEXT( description );  -- 全文検索インデックスの作成
SELECT * FROM product WHERE MATCH( description ) AGAINST('キレイに撮れる');

　たとえば、商品説明などを検索対象に入れるときに有用でしょう。

カラム定義

　非常に重要なのが、「文字コード」です。以下のクエリを見てみてください。

SELECT * FROM `users` LEFT JOIN `order` ON `users`.`userId` = `order`.`userId`;

　一見、何の問題もないクエリです。usersとorderのuserIdカラムに適切にインデックスが貼られていれば、パフォーマンス上もそれほどの問題はありません。

　しかし、テーブル定義上の問題があると、ほどなくして原因の分からない悪夢のような性能劣化に遭遇することになります。

文字コードが違うと、インデックスが効かない

　先ほど述べた、一見何の変哲もないJOINクエリでも、極端な性能劣化を招くことがあります。それが、テーブルおよびカラム同士の文字コードが異なる場合です。

　MySQLはお節介なデータベースとして有名です。ユーザーが多少誤ったクエリを投げていても、そう易々とエラーを返してはくれません。無理やりにでもレコードを引っ張ってこようとします。（日付型の検索などは有名な話でしょう）

　今回も、文字コードが異なることへのエラーはなく、淡々とすべてのレコードに対して文字コード変換をかけ、検索してくれます。正確に言えばクエリ実行時にwarnメッセージは出ていますが、エラーとなることはなく、正常な検索結果を返してくれます。

　ともなれば、延々ハマる要因となりえます。他に何も原因が無いのに、なぜかフルテーブルスキャンになったり、極端に性能が劣化した場合は、これを疑ってみましょう。

サイズ

　JOINでの結合カラムのサイズが違うだけでも、インデックスが効かなくなることがあります。普通に設計していればまず遭遇しないことですが、たとえば255と256などの閾値の誤解によって誤ったカラムが作成されることがあるかもしれません。

　これは、原因不明な性能劣化の調査に対する、有力な指標になるかもしれません。

まとめ

　以上、varchar型に関するパフォーマンスチューニング、性能劣化調査に関してのハマりどころをいくつか考えてみました。もちろん、これ以外にもいろいろあるかもしれません。explain句で実行計画を参照しながら、原因を探っていきましょう。