第5回はSQLチューニング基礎として、チューニング対象とすべきSQLの見つけ方と利用可能なツールについて解説します。これまでの連載でご紹介したMySQL WorkbenchのパフォーマンスレポートやMySQL Enterprise Monitorのクエリアナライザ以外にもチューニング時に利用する機能やツールがあります。

スロークエリログ

MySQLサーバで実行されたSQL文の中からチューニング対象とすべきものを見つけるシンプルな方法が、スロークエリログです。スロークエリログは設定された閾値（long_query_time）を超えたSQL文を記録するログです。long_query_timeは秒単位での指定ですが、精度はミリ秒単位となっています（250ミリ秒を指定する場合は「long_query_time=0.25」と設定します⁠）⁠。

スロークエリログはデフォルトではOFFになっています。設定にslow_query_logの項目を追加すると有効になります。slow_query_log_fileでログファイル名を指定しますが、ファイル名を指定しない場合のファイル名は「ホスト名-slow.log」となります。

スロークエリログで記録する管理系SQL文の制御

データベース管理者による実行が中心となると想定されている下記のSQL文に関してはデフォルトで、仮に閾値の時間を超えてもスロークエリログに記録されません。

以下のSQL文もスロークエリログに記録するには、log_slow_admin_statementsをONに設定します。

• ALTER TABLE
• ANALYZE TABLE
• CHECK TABLE
• CREATE INDEX
• DROP INDEX
• OPTIMIZE TABLE
• REPAIR TABLE

インデックスを使用していないSQL文の記録

log_queries_not_using_indexesをONに設定すると、long_query_timeを超えていないSQL文でも、インデックスを使用せずフルテーブルスキャンをしたものがログに記録されます。フルテーブルスキャンは多くのディスクIOを発生させサーバ全体のSQLのレイテンシを増加させている要因になり得ます。

ただし、レコード件数が小さなテーブルなどの場合は、インデックスを走査してから実際のレコードを取得するよりも、始めからテーブルスキャンをして直接レコードを取得したほうが効率が良いこともあります。そのようなSQL文が多数実行されることが想定される場合は、min_examined_row_limitにてフルテーブルスキャンしたSQL文が最低何行アクセスしたらログに記録するかを設定します。

フルテーブルスキャンするSQL文が多くなることが想定されている場合には、フルテーブルスキャンしたSQL文を1分間にいくつまで記録するかをlog_throttle_queries_not_using_indexesにて設定可能です。なお、min_examined_row_limitとlog_throttle_queries_not_using_indexesはMySQL 5.6から利用可能となった設定です。

スロークエリログの集計

スロークエリログに出力されたSQL文の集計にはmysqldumpslowコマンドが利用できます。

$ mysqldumpslow svr01-slow.log
Count: 4999  Time=0.00s (4s)  Lock=0.00s (1s)  Rows=127.8 (638717), root[root]@localhost
  SELECT intcol1,charcol1 FROM t1　WHERE intcol1 = N
（省略）

mysqldumpslowの主なオプションは以下のとおりです。

スロークエリログの出力フォーマット

スロークエリログはデフォルトではログファイルに出力されます。log_outputをTABLEに設定すると、mysqlデータベースのslow_logテーブルに出力されます。カンマ区切りで「FILE,TABLE」と設定すると、slow_logテーブルとログファイルの両方に出力されます。なお、log_outputは一般ログ（General Log）とスロークエリログの両方に影響しますので注意してください。

slow_logテーブルはCSVストレージエンジンを利用しているため、CSV形式のデータファイルをコピーして各種のツールで集計も可能です。テーブルに出力している場合のmysqldumpslowに類似した集計は下記のSQL文で可能です。

mysql> SELECT COUNT(*) AS QCount,
            AVG(query_time) AS Avg_QTime,
            SUM(query_time) AS Total_QTime,
            AVG(lock_time) AS Avg_LTime,
            SUM(lock_time) AS Total_LTime
            AVG(rows_sent) AS Avg_RSent,
            SUM(rows_sent) AS Total_RSent,
            user_host, sql_text
    FROM mysql.slow_log
    GROUP BY user_host, sql_text
    ORDER BY Avg_QTime DESC";

またmysqlクライアントプログラムのオプションと組み合わせると、mysqldumpslowに似た出力を得ることも可能です（下記コマンドは1行です。ユーザおよびパスワードは適宜指定して下さい⁠）⁠。

$ mysql -uroot mysql -p -B -e "SELECT CONCAT('Count: ', COUNT(*), '
Time=', AVG(query_time), ' (', SUM(query_time), ')', '  Lock=',
AVG(lock_time), ' (', SUM(lock_time), ')', '  Rows=', AVG(rows_sent), '
(', SUM(rows_sent), ') ', user_host, '  ', sql_text) AS mysqldumpslow
FROM slow_log GROUP BY user_host, sql_text ORDER BY AVG(query_time) DESC"

mysqldumpslow
Count: 4999  Time=0 (4)  Lock=0 (1)  Rows=1.0000 (1) root[root] @ localhost
[]  SELECT intcol1,charcol1 FROM t1　WHERE intcol1 = N
（省略）

実行中のSQL文の稼働確認

スロークエリログは実行が完了した時点で設定した条件に合ったもののみが記録されます。どれだけ時間がかかっていても実行中のSQL文は記録されません。現在実行中のSQL文の状況を知るにはSHOW PROCESSLIST文を使用します。SQL文が1,000文字を超える長さがあり得る場合は、SHOW FULL PROCESSLISTでSQL文全体が表示されます。

Command列がQueryである行がSQL文を実行中のクライアントの情報です。Info列の内容の実行時間がTime列に表示されます。Command列がSleepでTime列の値が大きいものは、接続だけして特に処理はしていないクライアントになります。

mysql> SHOW FULL PROCESSLIST\G
*************************** 1. row ***************************
     Id: 1004
   User: root
   Host: localhost
     db: NULL
Command: Query
   Time: 0
  State: init
   Info: SHOW FULL PROCESSLIST
*************************** 2. row ***************************
     Id: 3225
   User: root
   Host: localhost
     db: mysqlslap
Command: Query
   Time: 0
  State: Writing to net
   Info: SELECT intcol1,charcol1 FROM t1
（省略）

SQL文の処理時間が想定外に長すぎる場合などは、SUPER権限を持ったユーザがId列に表示されたスレッドIDを指定してKILLすることが可能です。

SYSスキーマのPROCESSLISTテーブルからでも同様の情報が得られ、かつSHOW PROCESSLIST文よりも実行効率が良いので、負荷の高いサーバではこのテーブルの利用を検討してください。

実行計画の確認

MySQLサーバのオプティマイザが、どのインデックスを利用するかやJOINの順序をどのように決めるかなどの実行計画を確認できるのがEXPLAIN文です。

mysql> EXPLAIN
    -> SELECT a.Name, a.District FROM City AS a
    ->   INNER JOIN Country ON a.CountryCode = Country.Code
    ->   WHERE Country.Code = 'JPN' AND a.Population >=
    ->     (SELECT MAX(City.Population) FROM City
    ->     INNER JOIN Country ON City.CountryCode = Country.Code
    ->     WHERE Country.Name = 'Malaysia');
+----+-------------+---------+-------+---------------+-------------+---------+-------------------+------+------------------------------------+
| id | select_type | table   | type  | possible_keys | key         | key_len | ref               | rows | Extra                              |
+----+-------------+---------+-------+---------------+-------------+---------+-------------------+------+------------------------------------+
|  1 | PRIMARY     | Country | const | PRIMARY       | PRIMARY     | 3       | const             |    1 | Using index                        |
|  1 | PRIMARY     | a       | ref   | CountryCode   | CountryCode | 3       | const             |  249 | Using index condition; Using where |
|  2 | SUBQUERY    | Country | ALL   | PRIMARY       | NULL        | NULL    | NULL              |  239 | Using where                        |
|  2 | SUBQUERY    | City    | ref   | CountryCode   | CountryCode | 3       | test.Country.Code |    9 | NULL                               |
+----+-------------+---------+-------+---------------+-------------+---------+-------------------+------+------------------------------------+
4 rows in set (0.00 sec)

上記の例では様々な情報が得られます。

1. サブクエリ内でCountryテーブルのフルテーブルスキャンでデータを絞り込み（3行目のALL）
2. CityテーブルのCountryCode列のインデックスを使ってCountryテーブルとでJOIN（4行目のref）
3. その値とCityテーブルのPopulation列と比較（2行目のUsing where）
4. Countryテーブルの主キーと定数“⁠JPN⁠”で絞り込み（1行目のconstおよびPRIMARY）

処理の順序やテーブルスキャンなどの課題の有無をより視覚的にわかりやすくするために、MySQL 5.6とMySQL Workbenchの組み合わせでVisual EXPLANという方法が利用可能なりました。MySQL 5.6ではEXPLAIN文の出力形式を従来のテーブル型から、JSONフォーマットに切り替えることできるオプションFORMAT=JSONが加わりました。MySQL WorkbenchはこのJSONフォーマットの出力を読み込んで表示します。

上記の例のようにテーブルスキャンが行われているテーブルは、テーブル名が赤く表示されています。インデックスは使われているものの条件による絞り込みが行われていないフルインデックススキャンが行われているテーブルも赤で表示されます。部分的なインデックススキャンなどはオレンジで、一般的には処理コストが高くなく問題となりにくいインデックスを使用したJOINなどを行っているテーブルは緑、単一行のテーブルや主キーと定数による比較など処理コストが低い処理を行っているテーブルは青で表示されます。

各テーブル名の右上には、統計情報を元にしたオプティマイザが推計したアクセス行数が表示されます。MySQL 5.7からはさらに各処理コストの推計値が併せて表示されるため、より負荷の高い処理を簡単に見つけることが可能となります。

スキーマ設計時の考慮点

スキーマを設計するときの考慮点は、他のデータベースでも共通点が数多くあります。正規化を行うことでデータの矛盾がなくなるという重要な点が挙げられますが、重複するデータが減るということもポイントです。データの正しさを保証すると言う点では正規化はOLTPでは必須ですが、結果的に更新が多いアプリケーションで性能面のメリットがあります。逆にデータの取得時にはJOINが必要になることが増え、オーバーヘッドとなります。分析や集計がメインとなる場合には、非正規化を行ってあらかじめJOINを削減することも有効です。正規化されたことによるメリットを最大限活かす場合には、正規化された元のテーブルは残しつつ、非正規化されたテーブルを別途用意して元のテーブルに作成したトリガ等で更新することもあります。この場合、ストレージの容量が余計に必要となる、ほか管理対象のテーブルが増えてしまう点には注意が必要です。

MySQLではこのような用途のテーブルにMEMORYストレージエンジンを利用することも考えられます。MEMORYストレージエンジンのテーブル上のデータはMySQLサーバ停止時に空になってしまうため、起動時にMySQLサーバの--init-fileオプションで指定してテキストファイルにて元のテーブルのデータをロードするようにしておきます。

利用するデータに適した種類でかつデータ全体が格納できるデータ型の内、最小のデータサイズとなるデータ型を選択します。例えば格納される値が全て整数の場合はINT型が候補となりますが、値が0以上255以下であることがわかっている場合にはTINYINT UNSIGNED型を使用することで、1レコードあたりのサイズをINT型の4バイトからTINYINT UNSIGNED型の1バイトに削減可能です。また、MySQLではデータ型の異なる列のJOINが可能となっていますが、内部的には型の自動変換を行うためオーバーヘッドとなるほか、アプリケーションの意味的に誤りにつながるため避けることが無難です。

インデックスの設計と考慮点

レコードを一意に識別する主キーやレコードの重複を防ぐためのユニークキーの機能的な意味とともに、データの検索を高速化するためにもインデックスは重要です。ただし、インデックスはデータの更新時に合わせて変更される必要があるため、過剰なインデックスの作成は更新処理にとって問題となります。そのため必要最低限のインデックスを設定する必要があります。

地名や製品名などの長い文字列にインデックスをつける場合、最初の何文字かをインデックスとして設定することで、ユニークにはならないもののおおむねインデックスにてデータの絞り込みが可能なこともあります。この場合には文字列全体のインデックスの大きさと、インデックス上でのデータ重複を実際のテーブルから取得するコストの大きさとの兼ね合いを考慮します。

複合インデックス

MySQLでも複数の列を1つのインデックスに含めることができます。この場合はインデックス内での先の列から利用されます。「⁠INDEX (colA, colB)」のように例えばcolA列とcolB 列をインデックスに含めた場合、WHERE句の条件が「WHERE colA = 123」や「WHERE colA = 100 AND colB = 200」などの場合はこのインデックスが使用されますが、「⁠WHERE colB = 300」のように先頭の列が含まれない場合はこのインデックスは使用されません。すでにこのインデックスが存在している場合、colA列のみが含まれるINDEX (colA)は有効に利用されず、更新時のオーバーヘッドになるだけなので作成する必要がありません。MySQL Utilitiesのmysqlindexcheckコマンドではこのような重複したインデックスを抽出してくれます。

mysql> EXPLAIN SELECT * FROM tbl1 WHERE colA = 1 AND colB=10\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tbl1
         type: ref
possible_keys: colA
          key: colA
      key_len: 10
          ref: const,const
         rows: 1
        Extra: NULL
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM tbl1 WHERE colB=10\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tbl1
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 7
        Extra: Using where
1 row in set (0.00 sec)

上記の例では、1つ目のSELECT文の実行計画では「type: ref」かつ「ref: const,const」となっていて複数の列が含まれたインデックスを使用した検索であることがわかりますが、2つ目のSELECT文の実行計画では「type: ALL」となっていてフルテーブルスキャンになっています。

Covering Index

検索の条件や対象列が全てインデックスに含まれる場合は、インデックスの走査だけでテーブルにはアクセスしないため高い応答性能が期待できます。例えば上記の「INDEX (colA, colB)」が存在する場合、「⁠SELECT colB FROM Tbl WHERE colA = 123」はインデックスのみのアクセスで済みます。このような場合はCovering Indexと呼ぶこともあります。ただし、Covering Indexを狙うためにインデックスを作りすぎて更新時のオーバーヘッドを増大させてしまうのは本末転倒です。

mysql> EXPLAIN SELECT colB FROM tbl1 WHERE colA = 1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tbl1
         type: ref
possible_keys: colA
          key: colA
      key_len: 5
          ref: const
         rows: 2
        Extra: Using index
1 row in set (0.01 sec)

上記の例では、ExtraにUsing indexが表示されているのがCovering Indexでデータが取得できることを表しています。

ヒント句

オプティマイザが想定したインデックスを選択しなかった場合や、強制的にインデックスを使わないでテーブルスキャンさせる場合には、MySQLでもヒント句が利用できます。

USEの場合にはレコード件数によってはフルテーブルスキャンが選択されることもありますが、FORCEの場合には基本的にフルテーブルスキャンは選択されません。ヒント句は行の取得以外にもJOIN、ORDER BYおよびGROUP BYの処理それぞれに対して指定が可能です。

次回は

次回はMySQLのパラメタチューニングの基本をご紹介いたします。