WPを高速化するPlugin【Beyond Wpdb】

WordPressでmetaに対して複数の条件で検索を行うとシステムが重くなりやすいです。

その理由として、WordPressのテーブル構造に関係があります。

通常、システムを開発する時は1件のデータに対し、1行レコードが増えるようなテーブル構造で考えるのが基本です。(もちろん全てのデータに対してこの考えが正しいわけではありません。)
データに対し、項目を増やしたい場合は横に項目が増えていきます。

ID	column1	column2	column3	column4
1	1	2	3	4

ID	column1	column2	column3	column4	column5
1	1	2	3	4	5

対してWordPressのテーブル構造は最初から決まっており、項目を増やすと
metaテーブルに対して縦にレコードを増やすことで項目が増えます。

ID	post_id	meta_key	meta_value
1	1	column1	1
2	1	column2	2
3	1	column3	3
4	1	column4	4

ID	post_id	meta_key	meta_value
1	1	column1	1
2	1	column2	2
3	1	column3	3
4	1	column4	4
5	1	column5	5

このWordPressのデータベース構造のメリット・デメリットとして以下があります。

メリット

• テーブル定義が決まっているので、テーブル設計の時間を短縮できる
• 項目追加、削除が発生してもテーブル定義を変更する必要がない

デメリット

• 項目が増えるほどデータ件数が増える
• 1項目1レコード発生するため、検索する際に1項目毎に結合する必要がある

そのため、WordPressでシステムを開発することでテーブル設計に関する工程をスキップすることで素早い開発、対応が可能となっています。
しかし、運用を続けていく中で、データが増えるとmetaテーブルにレコード数が極端に増え、検索では重くなりやすいです。

今回開発したPlugin【Beyond Wpdb】ではこのデメリットを解決します。

Beyond Wpdbの紹介

このPluginはインストールしただけでは何も起きません。

設定ページに遷移し、検索を高速化させたい種類を有効化に変更し、更新します。
高速化できる対象はmetaテーブルが存在する「post」「user」「comment」です。

有効化にするだけで検索が高速化されます。
特にレコード件数が多い項目、頻繁に検索に使用する項目はバーチャルカラムを設定します。

これで指定した項目に対しての検索は更に高速化されます。

留意点

このPluginはデータベースの高度な機能を利用しているため、使用制限があります。
また、設定時にデータベースに負荷がかかる場合があります。
以下留意点をご確認いただき、可能であれば開発環境でお試しいただいてから本番環境に適用することをオススメします。

• 大量のデータが存在している場合、独自メタテーブルを有効化した際にデータベースに負荷がかかるクエリが実行されます。
• バーチャルカラムの追加時にはcolumnとindexの追加が独自テーブルに対して行われるため、レコード数が多い場合にはデータベースに負荷がかかります。
• データベースの機能を使用したPluginのため、MySQL5.7以上 又は MariaDB10.2以上のバージョンである必要があります。
• データベースにテーブルを作成する かつ TRIGGER機能を使用するため、それらの機能が実行可能な権限を持つデータベースユーザーでWordPressを使用している必要があります。
• 検索は早くなりますが、データ変更時にデータベース側で処理が増えるため遅くなる可能性があります。

仕組み

Database

このPluginはMySQL、MariaDBの以下機能を利用しています。

• JSON型
• 生成カラム(generated column)
• TRIGGER
• GROUP_CONCA

設定ページにて、postに対して機能を有効化すると
post_idとjson型のカラムが定義されたテーブル「[prefix]_postmeta_beyond」が作成されます。

既存のデータについては1つのSQLでpostsテーブル内のIDと、IDに紐つくmeta情報をpostmetaテーブルから収集し、データをGROUP_CONCAで繋ぎjson型として作成されたテーブルに全レコード登録します。

新規のデータについてはposts、postmetaテーブルに登録したTRIGGER機能により
データ変更がある度に[prefix]_postmeta_beyondテーブルを更新します。

これでデータに対して項目が増えた際に、metaテーブルの様に縦に広がらず、横に広がるテーブルが作成できました。

以下の条件で検索の速度を検証してみました。

• 100post
• それぞれのpostに対し50項目のmetaを登録
• 全meta項目に対しEqual検索

結果は以下のようになりました。

• WordPressが発行するSQL:4.72秒
• 同条件でmeta条件のみ[prefix]_postmeta_beyondテーブルを参照する場合:0.04秒

となりました。

ここまで早くなった理由としては、複数のmetaに対して検索を行うと
WordPressのテーブル構造では1つのmetaに対し、1度JOINを発生させないといけないためです。
速度検証の例では50metaに対し検索を行っているので、50のJOINが発生しています。

[prefix]_postmeta_beyondを使用した検索を行うと、1行に全metaの情報が入っているため
50metaに対して検索をかけても、JOINは1度しか発生しません。
JOINの回数を大幅に削減できたことにより、高速化されています。

通常のテーブル構成では検索の高速化手段として、頻繁に検索する項目やデータ件数が多い項目に対しindex貼ることが有効です。
WordPressのテーブル構造では、meta_valueにindexが貼られていないため
検索対象のデータが増えてきた場合、速度低下が考えられます。

この問題を解決するために、設定画面からバーチャルカラムの設定を行うと
テーブルに新規にカラムが生成され、indexが貼られます。

このカラムに対して検索をかけることで、indexを使用した検索となり、更に高速化された検索が可能となります。

検索の速度を以下条件で実施してみました。

• 1万post
• それぞれのpostに対し同一のmeta項目を1件ずつ別meta_valueで登録
• 任意のmeta_valueでEqual検索

結果は以下のようになりました。

• WordPressが発行するSQL:0.13秒
• 同条件でmeta条件のみ[prefix]_postmeta_beyondテーブルを参照する場合(バーチャルカラム):0.02秒

検索の変換

新規テーブルの作成で高速化するためのデータ構造は完成しました。
ただ、WordPress本体や他Pluginが発行するSQLはmetaテーブルを参照した検索を行うので、これだけでは高速化になっていません。

Beyond WpdbはWP Queryやget_posts、get_users、get_comments関数などでmetaテーブルに検索をかけるSQL文が生成された時、検索クエリの向き先を独自テーブルに変換することで
複数の検索条件が存在しても、結合を独自テーブルに対し1回のみ行うことでで完了することができます。

また、バーチャルカラムが設定されている項目に対して検索が発生した場合はバーチャルカラムを参照し、indexを使用した検索になります。

検索だけでなく、並び順の指定にmetaの項目を使用した場合も独自テーブルのデータを参照します。

ただし、変換には以下の条件があります。

• metaの検索条件指定にmeta_keyとmeta_value両方の値が指定されている
• meta_compare_keyが指定されている場合、=、EXISTS以外が使用されていない
• suppress_filters がfalseである(WP_Query、get_postsのみ)

変換できない場合は元のSQLのまま実行されます。

まとめ

このPluginは当初、save_postフックに引っ掛けて全metaを収集し、PHPでJSONに変換することでデータ更新を行っていました。
しかし、この方法では1つのmetaが更新されたら必ずsave_postフックを通す必要があり
毎回metaを全取得するのも効率が悪いなと感じており、データベース側で良い感じにできないかなと思い開発をしました。

また、その時は変換も無く、WordPressや他Pluginが発行するSQLは高速化されないということもあり
今回Pluginを開発する際に、変換を行うことで全体の高速化にもチャレンジしてみました。

WordPressのテーブル構造による弱点を少しでもカバーできたら嬉しいです。

githubにリポジトリとしてコード公開しているので、気になる点があればプルリク等お待ちしています！