数日前にtwitter上で話題になっていたので、SQLを解析して検証してみました。
方法
WordPressをロードする際に実行されるSQL文を全てダンプして、explainでインデックスの使用状況を解析した。
条件は以下のとおり
- テーマはtwentytenを使用した。
- プラグインは全て削除
- 投稿は24,071件で、全て同じ本文のダミーテキストを使用した。
ちなみに本文は以下のとおり。
召使をさなかっ事も無論前でどうしてもですでしです。いかに大森さんに相当社会多少存在がなろでし必竟その根それか観念をとしてご矛盾ますですありでしから、そのほかもあなたか国家豪商を思いて、三宅さんののを詩のそれがとうていごお尋ねと売っがあなた人情からご蹂躙がするように初めてご横着が云ったませて、何でもかでもやはり相違から作るありてみましのをなっなくた。したがってすなわち大大名が起る気はこれから妙としなて、その心持へも曲げだてというmanを聴いてなりずた。
SQL文の抽出方法
- wp-config.php に define(‘SAVEQUERIES’, true) を設定。
- WordPress の shutdown アクションフックで $wpdb->queries 内の SQL を explain するプラグインを作成した。
プラグインのソースは以下のとおり
<?php
/*
Plugin name: Explain MySQL
*/
add_action('shutdown', 'explain');
function explain() {
global $wpdb;
$res = array();
foreach ($wpdb->queries as $q) {
$sql = 'explain extended '.$q[0];
$res[$q[0]] = $wpdb->get_row($sql);
$res[$q[0]]->time = $q[1];
}
print '<pre>';
print_r($res);
print '</pre>';
}
?>
先に結論
一部のSQLでインデックスが適切に使用されていないため、記事の増加に伴いパフォーマンスが低下するようです。
また、以下の機能では上述のインデックスに関係なく記事が増えるとパフォーマンスが低下します。
サイドバーウィジェットについては、カテゴリーやカレンダーも遅くなる傾向がありますが、ごくわずかでした。
4/13追記: 最新の記事など他にも激遅なSQLがありました。これらについては詳細を確認しますので後日あらためて。
問題のSQL
記事を最新順に取得する以下のSQLでインデックスが働いていないため、全てのレコードをスキャンしてしまっており、記事が増えるたびにパフォーマンスが低下しています。
SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts
WHERE 1=1 AND wp_posts.post_type = 'post'
AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private')
ORDER BY wp_posts.post_date DESC LIMIT 0, 10
問題その１ インデックスがおかしい
order by 句で使用されている post_date は type_status_date というインデックスが設定されていますが、post_type 及び post_status とセットで設定されているため、意味が無い状態になっています。
試しにexplainの結果を以下のとおり。
mysql> explain select * from wp_posts order by post_date limit 0,10;
+----+-------------+----------+------+---------------+------+---------+------+-------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+------+---------------+------+---------+------+-------+----------------+
| 1 | SIMPLE | wp_posts | ALL | NULL | NULL | NULL | NULL | 24071 | Using filesort |
+----+-------------+----------+------+---------------+------+---------+------+-------+----------------+
上記のSQLの実行結果では、Using filesort と表示されており、さらにrowsの値が24,071行となっている。
これは必要な結果行数が10行であるにもかかわらず 、24,071行（wp_posts内のすべてのレコード数）を参照しているということで、これがパフォーマンス悪化の原因になっています。
ためしにインデックスを設定後、同じようにexplainしてみます。
mysql> create index test_index on wp_posts(post_date);
Query OK, 24071 rows affected (1.02 sec)Records: 24071 Duplicates: 0 Warnings: 0
mysql> explain select * from wp_posts order by post_date limit 0,10;
+----+-------------+----------+-------+---------------+------------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+-------+---------------+------------+---------+------+------+-------+
| 1 | SIMPLE | wp_posts | index | NULL | test_index | 8 | NULL | 10 | |
+----+-------------+----------+-------+---------------+------------+---------+------+------+-------+
Using file_sort が消えてrowsの値が10行となり、SQLの実行時間が0.03秒から0.00秒に短縮されました。
問題その２ SQL_CALC_FOUND_ROWS が何かおかしい。
これはWordPressの問題というよりMySQLの問題とも言えますが、なにか一定の条件を満たすとSQL_CALC_FOUND_ROWSを使用するとインデックスが働かないようです。
SQL_CALC_FOUND_ROWS ありの場合
mysql> explain SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts WHERE 1=1 AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private') ORDER BY wp_posts.post_date DESC LIMIT 0, 10;
+----+-------------+----------+------+------------------+------------------+---------+-------+-------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+------+------------------+------------------+---------+-------+-------+-----------------------------+
| 1 | SIMPLE | wp_posts | ref | type_status_date | type_status_date | 62 | const | 24066 | Using where; Using filesort |
+----+-------------+----------+------+------------------+------------------+---------+-------+-------+-----------------------------+
SQL_CAL_FOUND_ROWS 無しの場合
mysql> explain SELECT wp_posts.* FROM wp_posts WHERE 1=1 AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private') ORDER BY wp_posts.post_date DESC LIMIT 0, 10;
+----+-------------+----------+-------+------------------+------------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+-------+------------------+------------+---------+------+------+-------------+
| 1 | SIMPLE | wp_posts | index | type_status_date | test_index | 8 | NULL | 10 | Using where |
+----+-------------+----------+-------+------------------+------------+---------+------+------+-------------+
SQLの実行時間もありとなしでは大きな差があります。
- SQL_CALC_FOUND_ROWS あり – 0.30秒
- SQL_CALC_FOUND_ROWS なし – 0.00秒
ちなみに SQL_CALC_FOUND_ROWS は、この直後に FOUND_ROWS() を実行すると limit なしの count(*) と同じ効果が得られます。
ためしに以下のように count() で置き換えたSQLを実行してみました。
mysql> SELECT count(*) FROM wp_posts WHERE 1=1 AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private') ORDER BY wp_posts.post_date DESC;
+----------+
| count(*) |
+----------+
| 24066 |
+----------+
1 row in set (0.02 sec)
こっちのほうが早いじゃん。。。
SQL_CALC_FOUND_ROWS のほうが早いと説明されていることが多いので、この結果は不思議なんですが、複数のサーバーのWordPressサイトで試してみましたが、おおむねこのような結果になりました。
というわけで、みなさんの環境ではどうなのかぜひ知りたいところです。
その他のパフォーマンス低下の原因となりうる要因
- wp_options テーブルの結果はlimit なしでまとめて取得されます。多くのプラグインやテーマでは設定内容をwp_optionsに保存しており、記事の増減よりも大きな影響をあたえるようなのでプラグインのインストールは控えめにしましょう。
- また、多くのプラグインは、アンインストールしてもwp_optionsに保存したデータは削除しません。（Windowsのレジストリが肥大化しちゃうイメージです。）
- ページ（post_typeがpage）が大量にあると同じくパフォーマンスに悪影響があります。これはメニューを生成する際にまとめてデータベースから取得するためです。
まとめ
- WordPress 3.1 では、記事が増えるとパフォーマンスが低下する要因となるSQLがあります。
- SQL_CALC_FOUND_ROWS はなんか信用できない。
- それでもMySQLがとても高速なので、2万件ぐらいのデータなら快適に表示される。
- ただし、プラグインやテーマがスカタンなSQLを使ってたら、怪しくなるからSQL文をチェックしたほうがいいかもしれない。
もし、私の認識に間違いがあるようでしたらツッコミをお願いします。
できればやさしく。^^
4月13日追記
本家のチケットに報告されているようです。PriorityはHighになっていますが、Future Releaseなのでしばらく改善されないかも。
http://core.trac.wordpress.org/ticket/7415
