WordPress：MySQLの遅いクエリーを検出、最適化する

WordPress、インストール始めのころは速かったが、最近なんだか遅くなって感じることは多いと思います。過剰なプラグインのインストール、重い外部スクリプトの大量読み込み等など、原因は様々ありますが、データベース（MySQL）のクエリーがその一要因となっていることがあります。MySQLの遅いクエリーを検出、最適化することで、Wordpressのパフォーマンス向上を狙います。(2016/09/11作成)

hpgruesen / Pixabay

0.環境

Wordpress Ver.4.6.1

$ mysql --version
mysql  Ver 14.14 Distrib 5.7.15, for Linux (x86_64) using  EditLine wrapper

1.MySQLの SlowQueryLogを「mysqldumpslow」で分析する

$ sudo mysqldumpslow -s t mysql_slow.log > /tmp/analytics.log

代表的なコマンドオプション

-s al → 平均ロックタイムの長い順
-s ar → 平均行数の多い順
-s at → 平均実行時間の長い順
-s c → 総クエリ数の多い順
-s l → 総ロックタイムの長い順
-s r → 総行数の多い順
-s t → 総実行時間の長い順

分析結果（mysqldumpslow）：

Count: 312  Time=11.18s (3488s)  Lock=4.45s (1387s)  Rows=497.0 (155075), wordpress[wordpress]@localhost
  SELECT option_name, option_value FROM wp_options WHERE autoload = 'S'

2.スローと検出された「Query」を「EXPLAIN」確認する

mysql> EXPLAIN SELECT option_name, option_value FROM wp_options WHERE autoload = 'S' \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: wp_options
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 623
     filtered: 10.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

ERROR: 
No query specified

typeの解説 （MySQLリファレンスより）

system：
1 レコードのみで構成されるテーブル（= システムテーブル）。これは、const結合型の特殊なケースである。
const：
テーブルに、一致するレコードが最大で 1 つあり、クエリの開始時に読み取られる。レコードが 1 つしかないため、このレコードのカラムの値はオプティマイザによって定数と見なされる。constテーブルは、1 回しか読み取られないため、非常に高速である。
eq_ref：
前のテーブルのレコードの組み合わせのそれぞれに対して、このテーブルから 1 レコードずつ読み取られる。これは、systemとconst型以外で最適な結合型である。 結合でインデックスのすべての部分が使用され、このインデックスが UNIQUEまたは PRIMARY KEYである場合に使用される。
ref：
前のテーブルのレコードの組み合わせのそれぞれに対して、インデックス値にマッチするすべてのレコードがこのテーブルから読み取られる。refは、インデックスの左端の先頭部分のみが結合で使用される場合、またはインデックスが UNIQUEや PRIMARY KEYではない場合（すなわち、この結合において、インデックス値から1つのレコードをSELECTできない場合）に使用される。
この結合型は、使用されるインデックスと一致するレコードが数レコードしかない場合に適している。
ref_or_null：
refと同様だが、NULLを使用したレコードの補足検索も追加で実行される。 この結合型の最適化は主としてサブクエリを解決する場合に使用される。 下記の例では、MySQLはref_tableで ref_or_nullが使用される。
unique_subquery：
この型は、下記のフォームでINサブクエリの代わりに、refを使用します。
index_subquery：
この結合型はunique_subqueryに似ています。INサブクエリの代わりに使用されますが、下記のサブクエリのフォームでユニークではないインデックスで使用できます。
range：
インデックスを使用して、一定の範囲にあるレコードのみが取り出される。keyカラムに使用されるインデックスが示される。key_len_には使用される最長のインデックス部分が記載される。 この型ではrefカラムがNULLになる。
range は、インデックスを張っているカラムが =、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN、およびIN を使用して定数と比較される場合に使用される。
index：
これは、インデックスツリーのみがスキャンされる点を除いて ALLと同じである。一般にインデックスファイルはデータファイルより小さいため、通常は ALLより高速である。
MySQL は、クエリで 1 インデックスの構成部分であるカラムのみが使用される場合にのみ使用できます。
all：
前のテーブルのレコードの組み合わせのそれぞれに対して、フルテーブルスキャンが実行される。一般に、テーブルが constの指定がない第 1 テーブルの場合には適さず、その他の場合はすべて非常に不適である。
通常は、さらにインデックスを追加することで ALLを回避し、定数値または以前のテーブルのカラム値を基準にレコードを取り出すようにすることができる。

【ポイント】
「all」が選択されている部分が一番コストが高い、インデクスのチューニングが必要である。具体的には、インデクスを張ったほうが良いと思われる部分にインデクスを貼る。

3.インデックス設定

※テーブル「 wp_options 」の「autoload」にINDEXを貼る。

mysql> CREATE INDEX idx01_wp_options ON wp_options(autoload);
Query OK, 623 rows affected (0.41 sec)
Records: 623  Duplicates: 0  Warnings: 0

4.「EXPLAIN」結果（インデックス設定後）

mysql> EXPLAIN SELECT option_name, option_value FROM wp_options WHERE autoload = 'S' \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: wp_options
   partitions: NULL
         type: ref
possible_keys: idx01_wp_options
          key: idx01_wp_options
      key_len: 82
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.03 sec)

ERROR: 
No query specified

付録1.MySQLで INDEXが貼られているかを確認する

mysql> SHOW INDEX FROM wp_options \G;
*************************** 1. row ***************************
        Table: wp_options
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: option_id
    Collation: A
  Cardinality: 294
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 2. row ***************************
        Table: wp_options
   Non_unique: 0
     Key_name: option_name
 Seq_in_index: 1
  Column_name: option_name
    Collation: A
  Cardinality: 294
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 3. row ***************************
        Table: wp_options
   Non_unique: 1
     Key_name: idx01_wp_options
 Seq_in_index: 1
  Column_name: autoload
    Collation: A
  Cardinality: 2
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
3 rows in set (0.00 sec)

ERROR: 
No query specified

以上