PG雑記: 5月 2013

クエリキャッシュ

MySQL4.0.1から利用可能。
my.cnfに以下設定を記述する。

query_cache_type=1

値は、以下の通り。 0|クエリキャッシュを行わない 1|SELECT SQL_NO_CACHE ... クエリ以外をキャッシュする 2|SELECT SQL_CACHE ... クエリのみキャッシュするクエリキャッシュはスレッド内から設定する事も出来る。

SET GLOBAL QUERY_CACHE_TYPE=1;
SET SESSION QUERY_CACHE_TYPE=1;

MySQLは、クエリのハッシュをキーにしてキャッシュを管理するため、クエリの細かい違いに左右される。
以下の２つのクエリは異なるクエリと判断される。

SELECT * FROM table;
select * from table;

また、MySQLは先頭と末尾の余計な空白も取り除かない事に注意する必要がある。
MySQL4.0ではクエリキャッシュの対象を、先頭３文字が大小文字区別なしでSELに等しいかで区別する。これは、先頭がコメントで始まるクエリを全てキャッシュしない事につながる。

構文解析と分析と最適化

MySQLはキャッシュに無いクエリに対して、クエリ構文を解析して構成要素に分解する。

クエリ種類の判定(SELECT,INSERT,UPDATE,DELETE,SET,GRANT,...)
どのテーブルが関係するのか、エイリアスを使用するのか
WHERE句はどうなっているか
ヒントや修飾子を利用しているか

MySQLはクエリを基本的な要素に分解した後に、何を実行すべきかを判断する。ここからクエリオプティマイザが動き始める。クエリオプティマイザは、あらゆる情報を利用して、最も効率的なクエリの実行方法を見つけ出す。MySQLのボトルネックはほぼディスクI/Oであり、この負担を軽くすべく調査すべきレコード数を限定するために動く。
適切な判断を下すために、MySQLは以下の調査を行う。

行を迅速に見つけるために、適切なインデックス候補が存在するか
どのインデックスが最適であるか、複数テーブルの場合それぞれについて
テーブルを結合する場合にどのテーブルがどのテーブルに依存するのか
テーブルにとってどの結合順序が適切か

EXPLAINによる調査

MySQLのSELECTクエリについて動作を調査するために、EXPLAINを利用する。
以下構成のテーブルに着いて、適当なレコードを1000000行挿入してある。

mysql@localhost> describe headline;
+------------+------------------+------+-----+-------------------+----------------+
| Field      | Type             | Null | Key | Default           | Extra          |
+------------+------------------+------+-----+-------------------+----------------+
| id         | int(10) unsigned | NO   | PRI |                   | auto_increment |
| headline   | varchar(255)     | NO   |     |                   |                |
| url        | varchar(255)     | NO   | UNI |                   |                |
| time       | int(10) unsigned | NO   |     | 0                 |                |
| expiretime | int(10) unsigned | NO   |     | 0                 |                |
| date       | varchar(6)       | NO   |     |                   |                |
| summary    | text             | YES  |     |                   |                |
| modtime    | timestamp        | YES  |     | CURRENT_TIMESTAMP |                |
+------------+------------------+------+-----+-------------------+----------------+
8 rows in set (0.45 sec)

プライマリキーを元にSELECTした結果。

mysql@localhost> select headline,url from headline where id=434 \G
*************************** 1. row ***************************
headline: headline::434
     url: http://www.example.com/news/434.html
1 row in set (0.08 sec)

上記クエリについてEXPLAINを実行。

mysql@localhost> explain select headline,url from headline where id=434 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: headline
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 1
        Extra: 
1 row in set (0.43 sec)

MySQLは一致行数が１である事を知っている(primary keyは重複しない)ため、非常に高速に結果を得る事が出来る。

id

複数のテーブルを利用している場合、それぞれについて結果が返ってくる。idはその通し番号。

select_type

このテーブルがどの様な役割を果たすか。SIMPLE,PRIMARY,UNION,DEPENDENT UNION,SUBSELECT,DERIVED

table

MySQLがレコードを読み出すテーブル

type

どのような型の結合を使用するか。system,eq_ref,ref,range,index,ALL

possible_keys

テーブルから行を見つけ出すために使用出来るインデックスのリスト

key_len

キーのサイズ(バイト数)

ref

キーを照合する時に使用するカラムまたは値

rows

調査する必要があると推定された行数。

Extra

追加情報
範囲選択を実行してみる。

mysql@localhost> select url from headline where id between 10 and 13;
mysql@localhost> select url from headline where id between 1033 and 1043;
+---------------------------------------+
| url                                   |
+---------------------------------------+
| http://www.example.com/news/1033.html |
| http://www.example.com/news/1034.html |
| http://www.example.com/news/1035.html |
| http://www.example.com/news/1036.html |
| http://www.example.com/news/1037.html |
| http://www.example.com/news/1038.html |
| http://www.example.com/news/1039.html |
| http://www.example.com/news/1040.html |
| http://www.example.com/news/1041.html |
| http://www.example.com/news/1042.html |
| http://www.example.com/news/1043.html |
+---------------------------------------+
11 rows in set (0.47 sec)

mysql@localhost> explain select url from headline where id between 1033 and 1043 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: headline
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 12
        Extra: Using where
1 row in set (0.00 sec)

範囲選択を実行したため、typeがrangeになっている。また、refもNULLとなっている。rowsは推測値であるため、実施の結果に一致しない場合もある。ExtraにUsing whereが追加されているが、これはWHERE句を利用して結果が絞り込まれた事を意味する。
インデックスがないカラムに基づいてレコードを取り出してみる。

mysql@localhost> select count(*) from headline where expiretime >= 4040;
+----------+
| count(*) |
+----------+
| 999615   |
+----------+
1 row in set (11.41 sec)

mysql@localhost> explain select count(*) from headline where expiretime >= 4040 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: headline
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1000000
        Extra: Using where
1 row in set (0.00 sec)

keyがNULLになり、possible_keysもNULLになっている。これは、キーを利用しない事を意味する。この種のクエリ実行が多い場合、インデックスを追加し、EXPLAINを実行してインデックスが利用される事を確認すればよい。

mysql@localhost> ALTER TABLE headline ADD INDEX (expiretime);
Query OK, 1000000 rows affected (4 min 51.75 sec)
Records: 1000000  Duplicates: 0  Warnings: 0

mysql@localhost> select count(*) from headline where expiretime >= 4040;
+----------+
| count(*) |
+----------+
| 999615   |
+----------+
1 row in set (2.24 sec)

mysql@localhost> explain select count(*) from headline where expiretime >= 4040 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: headline
         type: range
possible_keys: expiretime
          key: expiretime
      key_len: 4
          ref: NULL
         rows: 999683
        Extra: Using where; Using index
1 row in set (0.00 sec)

実行時間が短縮され、インデックスが利用されている事が確認出来る。また、ExtraにUsing indexとあるのは、インデックスの走査のみでデータが取得出来た事を意味する。
ORとINの違いについてみて見る。

mysql@localhost> select url from headline where id in (300,10000,8999);
+----------------------------------------+
| url                                    |
+----------------------------------------+
| http://www.example.com/news/300.html   |
| http://www.example.com/news/8999.html  |
| http://www.example.com/news/10000.html |
+----------------------------------------+
3 rows in set (0.39 sec)

mysql@localhost> explain select url from headline where id in (300,10000,8999) \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: headline
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 3
        Extra: Using where
1 row in set (0.00 sec)

mysql@localhost> select url from headline where id=300 OR id=10000 OR id=8999;
+----------------------------------------+
| url                                    |
+----------------------------------------+
| http://www.example.com/news/300.html   |
| http://www.example.com/news/8999.html  |
| http://www.example.com/news/10000.html |
+----------------------------------------+
3 rows in set (0.05 sec)

mysql@localhost> explain select url from headline where id=300 OR id=10000 OR id=8999 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: headline
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 3
        Extra: Using where
1 row in set (0.00 sec)

どちらも高速に実行出来ているし、EXPLAINの結果も変わらない。WHERE句で指定するidの候補数が増えた場合は、INを利用した方がクエリが短くなり構文解析時のオーバーヘッドが少なくなる。場合によってはINを利用する事が性能改善につながる。
サブクエリを利用した場合にどうなるかを見てみる。

mysql@localhost> select url from headline where id in (select max(id) from headline);
+------------------------------------------+
| url                                      |
+------------------------------------------+
| http://www.example.com/news/1000000.html |
+------------------------------------------+
1 row in set (4.15 sec)

mysql@localhost> explain select url from headline where id in (select max(id) from headline) \G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: headline
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1000000
        Extra: Using where
*************************** 2. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: NULL
         type: NULL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra: Select tables optimized away
2 rows in set (0.00 sec)

インデックスを全く使用していないし、rowsを見ると全ての行を探そうとしている事が分かる。
INでなく=を利用したらどうなるか見てみる。

mysql@localhost> select url from headline where id = (select max(id) from headline);
+------------------------------------------+
| url                                      |
+------------------------------------------+
| http://www.example.com/news/1000000.html |
+------------------------------------------+
1 row in set (0.23 sec)

mysql@localhost> explain select url from headline where id = (select max(id) from headline) \G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: headline
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 1
        Extra: 
*************************** 2. row ***************************
           id: 2
  select_type: SUBQUERY
        table: NULL
         type: NULL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra: Select tables optimized away
2 rows in set (0.00 sec)

プライマリキーを利用して1行だけ返すようになった。

結合

テーブル結合の際、MySQLは最も効率的であろう順序を推測する。このとき、各テーブルの相互関係を理解する必要がある。

select customer.name, order.date_placed, region.name
from customer, order, region
where order.customer_id = customer.id
and customer.region_id = region.id
and customer.name = 'John Doe';

上のクエリを実行した場合、MySQLはorderテーブルより先にcustomerテーブルを読む必要がある。つまり、customerテーブルのレコードを最初に読まなくてなならない。
MySQLにとって、最適な結合順序を見つけ出す事は苦手な技術の1つ。オプティマイザは単純に総当たり的に答えを見つける。場合によっては、結合順序の決定に時間がかかりすぎる事もある。

オプティマイザの機能と特徴

クエリをテストする場合、必ず現実的なデータを利用すべき。MySQLの問題に共通する原因は、あクエリオプティマイザがテストデータをどう扱うかにある。
一般に、MySQLはインデックスの利用によって『効率が向上する』と十分に確信出来る場合のみインデックスを利用する。これはテストを偽陰性(false negative)に導く事がある。

不十分な分散

データが多くても十分に分散していなければ、MySQLはインデックスを無視する事がある。例えば、全体の50%のレコードを読む必要がある場合、MySQLはテーブル全体をスキャンする方が高速だと判断する。この分岐点はおよそ30%。
このような挙動の理由は、ディスクシークの性能にある。MySQLは、インデックスを利用した場合にインデックスのソート順でディスクアクセスを行う。つまり、現実のディスクをランダムにアクセスする可能性が高い。この問題に対応するために、一定以上の割合のレコード読み出しが必要になった場合、より高速にデータを読み出せるであろう方法に切り替える。
もしも、テーブルのサイズを非常に小さいままで維持出来るとしたら、インデックスを使わない方がいい。
また、テストを行うのであれば、データサイズと分散度の両方について、現実的なデータを利用すべき出会う。

インデックスベースのソート

MySQLはソートに非常に時間がかかる。純粋に並べ替え作業にかかる時間は、高速なCPUを利用するしか対処方法は無い。問題は、ソートキーにインデックスを利用出来ない場合にある。MySQLではテーブル1つにつき、1つのインデックスしか利用出来ない。この場合、複数カラムからのインデックスを利用すればよい。
複数インデックスを利用する場合、インデックス指定の並びに意味がある。複数インデックスは、最も左に配置されたカラムのインデックスとして利用される。

あり得ないクエリ

MySQLは、すべてのクエリのWHERE句に対して、基本的な論理解析を実行し、無意味だと判断すれば0個のレコードを返す。これによって、無意味な実行時間が省略される。

LIKE句とフルテキストインデックス

LIKE句よりもフルテキストインデックスを利用した方が高速であるが、フルテキストインデックスを利用できる場合、MySQLはどれだけのレコードを読み取るかに関係なくフルテキストインデックスを利用しようとする点に注意する必要がある。

遅いクエリの特定

遅いクエリの発見は、その原因や対応に比べれば簡単。最も簡単な方法は、MySQLにクエリを処理させスロークエリログを出力させればよい。スロークエリログには多くの情報が入っているが、重要な遅い『理由』が書かれていない。
スロークエリログに記録されているクエリであっても、純粋にそのクエリが遅いとは断定出来ない。たまたまそのクエリが処理された時に、CPUやディスクなどの負荷が高かったのかもしれない。以下、その外部要因の一例。

テーブルロックの解除を待機していた
メモリ上にキャッシュされていなかった
バックアップなどが動いており、ディスクI/Oが大幅に遅くなっていた
何百もの関係ないクエリを処理していたためCPUパワーが不足していた

これらの理由はまだ続く。そのため、1度スロークエリログに現れたとしても、それだけで判断する事は難しい。MySQLに付属するmysqldumpslowを利用してスロークエリログを要約し、遅いクエリがどれだけの頻度で実行されているかを調べる事が出来る。また、mytopを利用してリアルタイムで遅いクエリを監視する事も出来る。

MySQLに与えるヒント

多くのリレーショナルデータベースサーバでは、何らかのかたちでヒントの概念を実装している。ヒントとは、基礎となるSQLエンジンとクエリオプティマイザに追加情報を提供するための簡単な構文。

SELECT SQL_CACHE * FROM mytable ...
SELECT /*! SQL_CACHE */ * FROM mytable ...

結合順序

MySQLは通常、クエリで指定したテーブルの順序を無視する。そのかわり、それぞれのテーブルを調べて、どのような順序でテーブルを読むべきか判断する。検証は、EXPLAINで行う事が出来る。
クエリの順序を維持したい場合は、STRAIGHT_JOINヒントを利用すればよい。

SELECT * FROM table1 STRAIGHT_JOIN table2 WHERE ...

インデックスの使用

利用したいインデックスのリストを指示してそれ以外を無視させたい場合、USE INDEXを追加すればよい。逆に、無視させたい場合は、IGNORE INDEXを利用する。特定のインデックスを強制させたい場合は、FORCE INDEXを追加する。

SELECT * FROM mytable USE INDEX (mod_time, name) ...
SELECT * FROM mytable IGNORE INDEX (priority) ...
SELECT * FROM mytable FORCE INDEX (priority) ...

結果のサイズ

多数の行を扱う場合など、SQL_BUFFER_RESULTヒントを利用すると、MySQLは結果をバッファに格納するため、ロックを素早く解除出来る。
SQL_BIG_RESULTヒントは、多数の行を返す事をMySQLに伝える。これによって、MySQLはディスクベースの一時テーブルを利用する事に着いて積極的な判断を下すようになる。

クエリキャッシュ

SQL_QUERY_CACHEヒントを利用すれば、そのクエリをキャッシュするし、SQL_NO_CACHEヒントを利用すれば、キャッシュしない。

クエリの特殊なトリック

1つのクエリを2つに分解

MySQLが期待に反してクエリを最適化しない場合がある。例えば、WHERE句とORDER句で利用するインデックスが異なる場合、ファイルソートが必要になる事がある。これを2つのクエリに分解し、期待するインデックスを利用出来るようにするという回避策がある。
以下構文でサーバ上に一時変数を定義する事が出来る。

SELECT @sid := id FROM symbols WHERE symbol = 'ibm';

2つのクエリを実行することによるオーバーヘッドとソートによるオーバーヘッドとでは、一般にクエリ処理にかかる負荷の方が小さい。

ORの代わりにUNIONを使用

WHERE句で複数のカラムを指定した場合、場合によっては適切なインデックスが利用されずに完全なテーブルスキャンを実行する事がある。さらに、ORを利用した場合、MySQLは簡単に次の候補を選ぶ事が出来ない(MySQL5.0で修正される予定)。
この場合、UNIONを利用してクエリを書き換える事が出来る。

SELECT * FROM headline
WHERE expiretime <= 400 OR id <= 60000
ORDER BY expiretime ASC LIMIT 10;

(SELECT * FROM headline WHERE expiretime <= 400 ORDER BY expiretime ASC LIMIT 10)
UNION
(SELECT * FROM headline WHERE id <= 60000 ORDER BY expiretime ASC LIMIT 10)
ORDER BY expiretime ASC LIMIT 10 \G

PG雑記

2013年5月29日水曜日

Mysql チューニング術

クエリキャッシュ

構文解析と分析と最適化

EXPLAINによる調査

結合

オプティマイザの機能と特徴

不十分な分散

インデックスベースのソート

あり得ないクエリ

LIKE句とフルテキストインデックス

遅いクエリの特定

MySQLに与えるヒント

結合順序

インデックスの使用

結果のサイズ

クエリキャッシュ

クエリの特殊なトリック

1つのクエリを2つに分解

ORの代わりにUNIONを使用