こんにちは。 エキサイト株式会社の三浦です。
以前こちらのブログで、「MySQLにおいて、Indexだけではパフォーマンス改善に限界があり、一定以上複雑なSQLやデータ構造に対してはIndex以外の手段を講じる必要がある」ことを説明しました。 今回は、その方法の1つであるMaterialized ViewをMySQLで実現する方法を説明します。
Materialized Viewとは
Materialized Viewとは、SQL実行パフォーマンスの改善等のために作るテーブルのことです。 通常のデータ構造でのIndexだけではパフォーマンス等が改善できないときに使用します。
例を挙げてみます。
以下のような article （記事）テーブルと article_tag （記事タグ）テーブルがあるとします。
article
| article_code | story | category | publish_date_time |
|---|---|---|---|
| article1 | テスト | lifestyle | 2022-01-01 00:00:00 |
| article2 | テストテスト | child | 2022-01-01 01:00:00 |
| article3 | テストテストテスト | beauty | 2022-01-01 02:00:00 |
| ... | ... | ... | ... |
article_tag
| article_code | tag |
|---|---|
| article1 | 芸能 |
| article1 | ニュース |
| article2 | 子育て |
| article2 | 育児 |
| article2 | 夫婦 |
| article3 | ニュース |
| ... | ... |
これについて、「指定のカテゴリに属しており、かつ指定のタグを持つ記事の一覧を、記事の公開日順に並び替えて取得する」ことを考えてみます。 SQLとしては以下になるでしょう。
SELECT article.*, article_tag.tag FROM article INNER JOIN article_tag ON article_tag.article_code = article.article_code WHERE article.category = "***" AND article_tag.tag = "***" ORDER BY article.publish_date_time;
この場合、どんなIndexが適しているでしょうか？
article テーブルに関しては、 category で絞り込んで publish_date_time でソートしているため category, publish_date_time のIndexが良さそうに見えます。
一方で article_tag テーブルに関しては、 tag で絞り込んでいるので tag 単体のIndexか、あるいは article_code で article テーブルと接続しているので tag, article_code の複合Indexでしょうか？
実はこちら、完璧にハマるIndexは存在しません。
全体の件数が少なかったり、検索したいタグがほとんどの記事に含まれているなどであれば検索速度は早いですが、全体の件数が多かったり、マイナーなタグを検索する場合は速度が大きく落ちてしまいます。
こういった場合にMaterialized Viewを使用します。
元のテーブルのデータを使って、以下のようなテーブルを作ります。
article_tag_materialized_view
| article_code | category | tag | publish_date_time |
|---|---|---|---|
| article1 | lifestyle | 芸能 | 2022-01-01 00:00:00 |
| article1 | lifestyle | ニュース | 2022-01-01 00:00:00 |
| article2 | child | 子育て | 2022-01-01 01:00:00 |
| article2 | child | 育児 | 2022-01-01 01:00:00 |
| article2 | child | 夫婦 | 2022-01-01 01:00:00 |
| article3 | beauty | ニュース | 2022-01-01 02:00:00 |
| ... | ... | ... | ... |
これを使用する場合、SQLは以下のようになります。
SELECT article.*, article_tag_materialized_view.tag FROM article_tag_materialized_view INNER JOIN article ON article.article_code = article_tag_materialized_view.article_code WHERE article_tag_materialized_view.category = "***" AND article_tag_materialized_view.tag = "***" ORDER BY article_tag_materialized_view.publish_date_time;
このSQLであれば、つけるべきIndexは簡単に推測できます。
article_tag_materialized_view については tag, category, publish_date_time のIndexが、 article については article_code のIndexがあれば高パフォーマンスとなるでしょう。
このように、Indexだけではパフォーマンスが改善できないデータ取得処理でも、Materialized Viewを用いることで改善することができます。
MySQLとMaterialized View
では、MySQLでMaterialized Viewを作るにはどうすれば良いでしょうか？
残念ながら、MySQLではデフォルトでMaterialized Viewを作る機能は存在しません。
データが増減することがないテーブルであれば最初にテーブルを作ってデータを入れてしまえば終わりですが、多くのテーブルはデータの増減や更新があるでしょう。 そこで、何らかの方法でエンジニアが手を加えて、データの変更があったときにも自動で対応されるようにMaterialized Viewを生成する必要があります。
方法としては、いくつか考えられます。
Triggerを使用する
MySQLにはTriggerという機能があります。
これは、指定テーブルに Insert / Update / Delete が走ったときに、自動的にこちらが指定したSQLを実行させることができる機能です。
例えば先程のMaterialized Viewであれば、 article や article_tag テーブルに Insert / Update / Delete が走ったら、関連するデータをMaterialized Viewに Insert / Update / Delete するようなSQLをTriggerとして設定しておけば、自動的にMaterialized Viewのデータが正しいものになります。
この方法には以下のメリット・デメリットがあります。
メリット
- データ元テーブルのデータ変更時に自動的にSQLが実行されるため、変更が即時Materialized Viewに適用される
- Triggerも含めてトランザクション単位となるので、トランザクションを使用してる場合、TriggerのSQLが正しければ必ずMaterialized Viewとデータ元テーブルのデータが一致する
デメリット
- データ元テーブルの
Insert / Update / Deleteと一体となって実行されるため、Triggerが無いときと比べてそれらの処理に時間がかかるようになる - アプリケーションコード側に処理を書くのに比べ、テーブル側に処理を書く場合はその存在が見過ごされやすく、何かあったときにバグの原因になりやすい
バッチを使用する
Materialized Viewを更新するバッチを作成し、cron等で定期実行する方法でもMaterialized Viewを更新できます。
この方法には以下のメリット・デメリットがあります。
メリット
- アプリケーションコードを使用するため、柔軟な処理を書くことができ、Materialized View変更に対するコストを最小限にすることができる
- 最低限の変更のみ実行するようにする、複数件の変更を1SQLで実行するなど
デメリット
- cronで定期実行する場合、反映が即時ではなくcronのタイミング次第となってしまう
- Materialized Viewへの処理が何らかの理由で失敗してしまった場合、Materialized Viewのデータとデータ元テーブルのデータが乖離してしまう可能性がある
他にもあるかもしれませんが、ざっくり考えられるのはこのあたりでしょう。
個人的には、データ元テーブルに対する変更処理の実行速度の低下が問題にならないのであれば、データ整合性の観点から基本的にはTriggerを使い、そこがどうしても問題になったり、何かしらの理由でTriggerが十分に使えない場合はバッチを使うのが良いと考えています。
なおTriggerを使う場合は、Trigger自体を作成するSQLをバージョン管理するなどして、その存在が忘れられてしまわないよう対策を講じましょう。
最後に
Materialized Viewは非常に便利な概念です。 ですが、安易にたくさん作ってしまうと管理が大変になってしまうというデメリットも抱えています。
可能な限りデータ構造自体を正したりIndexを使ってパフォーマンスを改善するようにし、どうしようもない場合だけMaterialized Viewを使うようにしましょう。
