MySQLをチューニング，そしてスケールアップ／スケールアウトへ

第5回　MySQLチューニング（4）　SQLチューニング基礎

MySQL, チューニング, SQL, スロークエリログ, MySQL Workbench, Visual EXPLAN, MySQL Utilities

この記事を読むのに必要な時間：およそ 6 分

実行中のSQL文の稼働確認

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

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

図4　SHOW FULL PROCESSLIST文の出力例

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文です。

図5　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フォーマットの出力を読み込んで表示します。

図6　MySQL WorkbenchのVisual EXPLAN

図6　MySQL WorkbenchのVisual EXPLAN

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

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

MySQL, チューニング, SQL, スロークエリログ, MySQL Workbench, Visual EXPLAN, MySQL Utilities

著者プロフィール

梶山隆輔

MySQL Sales Consulting Senior Manager。

日本オラクル（株）において，MySQLのお客様環境への導入支援や製品の技術解説を担当するセールスコンサルタントチームのアジア太平洋地域リーダー。多国籍なMySQL部門にて，オーストラリア，インド，台湾などに在籍するチームメンバーを束ね，アジア太平洋地域の25以上の国や地域でのMySQL普及やビジネスの拡大をミッションとする。