MySQL チューニングのど本命！索引チューニング

MySQLのチューニングで一番効果があるものは、パラメータのチューニングでもなく、はたまた、マシンのスペックでもなく、

索引のチューニング

です。私自身、サーバをスケールアウトしてみたり、パラメータをいじってみたりしてみましたが大した効果はないんですよね。やはり、目で見て効果が高いのは、やはり索引を適切に設定することでした。

f:id:hit10231023:20180309104332j:plain

今回、MySQLのオプティマイザがぁ。。とか、オンメモリーにするためにはーとかの話は抜きで、何を勘所にして、どのように索引チューニングしていくかというのを中心に書いていきます

MySQL即効クエリチューニング ThinkIT Books

作者: yoku0825
出版社/メーカー: インプレス
発売日: 2016/11/29
メディア: Kindle版
この商品を含むブログ (2件) を見る

索引チューニングが必要？確認作業について

ロードアレベージを確認する

インデクシングがうまくいっていない時の予兆として、サーバのロードアベレージが上昇するケースが頻繁にあります。サービスによりけりですが、ロードアベレージが、5以上上がっている時は、まず索引が適切に設定されていないことを疑って良いです。

$ uptime
 09:43:13 up 139 days,  6:52,  1 user,  load average: 12.03, 8.99, 1.86

スローログを確認する

基本中の基本ですが、スロークエリーが頻繁に発生していないか確認しましょう

hit.hateblo.jp

プロセスを確認する

いやいや、実はスローログよりこちらのほうが重要かもしれません。スローログは、SQLがAcceptされてから、レスポンスを返すまでの時間で出力されます。一番たちが悪いのは、そもそも結果すら返していないクエリーがプロセスに滞留しているのです。それを確認するには、以下のコマンドを発行します。（意外にこれをやらない人が多い）

mysql>
SHOW FULL PROCESSLIST \G

上記コマンドを実行すると以下のような結果が出てきます

*************************** 1076. row ***************************
     Id: 3356655
   User: mysqluser
   Host: ip-10-0-12-101.ec2.internal:38168
     db: test_schema
Command: Sleep
   Time: 0
  State:
   Info: NULL
*************************** 1077. row ***************************
     Id: 3356657
   User: mysqluser
   Host: ip-10-0-12-101.ec2.internal:38172
     db: test_schema
Command: Query
   Time: 20
  State: Sending data
   Info: select timegrid from table1 where UserId=? and kubun in ('ABC','XYZ') and createdAt between now() - interval 10 day and now() - interval 12 day

ここで着目すべきは、StateとTimeです。Timeが、2秒以上かかるクエリは、まずおかしいとみて良いでしょう。稀にバッチ処理とかでいたしかたないものもありますが、それでも、負荷は負荷です。なんらかの対応が必要になります。上記を例にすると、Timeが20(秒）かかっているこのクエリーが怪しいです。索引のチューニングが必要そうですね

いざチューニング！。。お題目を披露

問題のクエリは特定できた。じゃあ、チューニングを始めましょう！今回、music_testと、music_detailを利用したJOINクエリーで、パフォーマンスがよろしくないことを想定しております。

テーブル定義

今回のお題目の定義です。

mysql>
desc music_test ;
+-----------+--------------+------+-----+-------------------+-----------------------------+
| Field     | Type         | Null | Key | Default           | Extra                       |
+-----------+--------------+------+-----+-------------------+-----------------------------+
| Id        | varchar(32)  | NO   |     | NULL              |                             |
| CreatedAt | datetime     | NO   |     | CURRENT_TIMESTAMP |                             |
| UpdatedAt | datetime     | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| Title     | varchar(512) | YES  |     | NULL              |                             |
| Artist    | varchar(512) | YES  |     | NULL              |                             |
+-----------+--------------+------+-----+-------------------+-----------------------------+

mysql>
desc music_detail ;
+---------+---------------+------+-----+---------+-------+
| Field   | Type          | Null | Key | Default | Extra |
+---------+---------------+------+-----+---------+-------+
| Id      | varchar(32)   | NO   |     | NULL    |       |
| MusicId | varchar(32)   | NO   |     | NULL    |       |
| Url     | varchar(2048) | YES  |     | NULL    |       |
+---------+---------------+------+-----+---------+-------+

ちょっとおかしな名前ですが（笑）

music_testと、music_detailは、１対多の関係にあり。Urlが異なっても、歌手、曲は同じって構成になっています。

クエリー

以下のクエリを発行したところ、とんでもなくパフォーマンスが悪い・・・

  SELECT
    mt.Title,
    mt.Artist
  FROM
    music_test mt
  INNER JOIN
    music_detail md
  ON
    mt.Id = md.MusicId
  WHERE
    mt.Title LIKE 'You%'
  ;

1000 rows in set (1 min 59.76 sec)

約２分ですね。とんでもないクエリーだ（汗）

余談ですが、このクエリーの流した際のプロセスリストがこちらです

*************************** 1066. row ***************************
     Id: 3379250
   User: myuser
   Host: localhost
     db: test
Command: Query
   Time: 48
  State: Sending data
   Info: SELECT     mt.Title,     mt.Artist   FROM     music_test mt   INNER JOIN     music_detail md   ON     mt.Id = md.MusicId   WHERE     mt.Title LIKE 'You%' LIMIT 1000

結果すら帰ってこない場合、以下のようにプロセスを強制終了することも可能です。（もちろん解決策ではありません）

mysql>
KILL 3379250 ;

クエリーを解析

じゃあ、何がそんなに効率が悪いのってところで、登場するのが、EXPLAINです。こちらでSQLの実行計画に関する情報を取得します。ざっくりいうと、このクエリーで結果を返すために、どのインデクスを使って・・とかそういう情報が出力されます

さっそく流して見ました

EXPLAINを実行

  EXPLAIN 
  SELECT
    mt.Title,
    mt.Artist
  FROM
    music_test mt
  INNER JOIN
    music_detail md
  ON
    mt.Id = md.MusicId
  WHERE
    mt.Title LIKE 'You%'
LIMIT 1000
  \G

*実行結果

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: mt
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 992648
     filtered: 11.11
        Extra: Using where
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: md
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 995226
     filtered: 10.00
        Extra: Using where; Using join buffer (Block Nested Loop)
2 rows in set, 1 warning (0.00 sec)

ここで着目すべきは、typeと、keyおよび、rowsの箇所です。上記の例ですとパーフェクトに最悪の結果といって良いでしょう

type: ALLは、全件走査（テーブルスキャン）

key: NULLは、採用された索引なし

rows: 992648は、論理読み取り件数

です。。チューニングのしがいがありそうです。

いざ、チューニング！！

では、それぞれのテーブルの細かな定義を確認してみましょう。

mysql>
SHOW CREATE TABLE music_test ;
----
CREATE TABLE `music_test` (
  `Id` varchar(32) CHARACTER SET utf8mb4 NOT NULL,
  `CreatedAt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `UpdatedAt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `Title` varchar(512) CHARACTER SET utf8mb4 DEFAULT NULL,
  `Artist` varchar(512) CHARACTER SET utf8mb4 DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

mysql>
SHOW CREATE TABLE music_detail ;

CREATE TABLE `music_detail` (
  `Id` varchar(32) CHARACTER SET utf8mb4 NOT NULL,
  `MusicId` varchar(32) CHARACTER SET utf8mb4 NOT NULL,
  `Url` varchar(2048) CHARACTER SET utf8mb4 DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

ここまで、あからさまな例は滅多にないとは思いますが（笑）索引も何も設定されていませんよね。これでは遅いはずです。

では、もう一度、SQLをおさらいして、索引の設定をしてみましょう！

  SELECT
    mt.Title,
    mt.Artist
  FROM
    music_test mt
  INNER JOIN
    music_detail md
  ON
    mt.Id = md.MusicId
  WHERE
    mt.Title LIKE 'You%'
  \G

索引を貼るべき箇所の勘所は、

JOINの部分と WHEREの部分です、一つずつ索引を貼ってEXPLAINしてみます。

まずは、JOIN句

CREATE UNIQUE INDEX ix01 on music_test(Id) ;
CREATE INDEX ix01 on music_detail(MusicId) ;

EXPLAINの結果

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: mt
   partitions: NULL
         type: ALL
possible_keys: ix01
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1000531
     filtered: 11.11
        Extra: Using where
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: md
   partitions: NULL
         type: ref
possible_keys: ix01
          key: ix01
      key_len: 130
          ref: v4_t.mt.Id
         rows: 1
     filtered: 100.00
        Extra: Using index
2 rows in set, 1 warning (0.00 sec)

応答速度

15614 rows in set (0.52 sec)

さきほどとは雲泥の差になりました。でも、music_test側の、keyは採用されていないようです。そして、論理読み取りが、rows: 1000531なので、けして良い数字ではないですね。

というわけで、次はWHERE句の部分に索引を設定してみましょう。

CREATE INDEX ix02 on music_test(Title) ;

EXPLAINの結果

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: mt
   partitions: NULL
         type: range
possible_keys: ix01,ix02
          key: ix02
      key_len: 2051
          ref: NULL
         rows: 28692
     filtered: 100.00
        Extra: Using index condition
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: md
   partitions: NULL
         type: ref
possible_keys: ix01
          key: ix01
      key_len: 130
          ref: v4_t.mt.Id
         rows: 1
     filtered: 100.00
        Extra: Using index

いかがでしょうか？music_testで貼った、Titleのインデクスを採用してくれましたね。そして、論理読み取り件数も、rows: 28692 となっており。理想に近い形になったと思います。

そして応答速度！！

15614 rows in set (0.18 sec)

うん、すばらしく改善。。。

結果、1 min 59.76 sec --> 0.18 sec となりました。一般的に応答速度は、0.5以下が理想であり、それを越えると、全体的にパフォーマンスが低下する傾向にありますので、ここでチューニングがクリアですね！（自論）

今回少し極端な例で説明しましたが、索引のチューニングだけでも、これだけ効果があることを理解していただくために書きました。

でも、実際こんな簡単な対策ってあまりないんですよね。まして、DBと、プログラムが分業っていう企業もあまりないでしょうし。

止められないサービスをする上で、このインデクスチューニングは最強の技なので、自分のスタイルを作って解決できれば、もう神レベルになれますよ！！

・・余談ですが、EXPLAIN実行時に

2 rows in set, 1 warning (0.00 sec)

で、ワーニングが出ていることに気づきました？これ実は結構大事で、このワーニングを中身を確認しておいたほうが良いです。

show warnings \G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select `v4_t`.`mt`.`Title` AS `Title`,`v4_t`.`mt`.`Artist` AS `Artist` from `v4_t`.`music_test` `mt` join `v4_t`.`music_detail` `md` where ((`v4_t`.`md`.`MusicId` = `v4_t`.`mt`.`Id`) and (`v4_t`.`mt`.`Title` like 'You%')) limit 1000

MySQLが変換した、SQLを確認することができます。例えば、索引チューニングしても全然ヒットしてないとか、パフォーマンスが思うようにでない場合は、show warnings コマンドで、SQLをどう解釈しているのか確認できます。私がこれで助かったのは、JOINしているキーの文字セットがそれぞれ異なるために、文字セット変換の関数が入っていたなんてオチもありましたので。。。

ちなみに、MySQLで今回書きましたが、他のデータベースでも手法は違えど、索引チューニングの基礎は、ほぼ同じと言って良いです

それでは、良いMySQLライフを。。

Database JUNKY

MySQL,MariaDBを中心としたブログです

MySQL チューニングのど本命！索引チューニング

索引のチューニング

索引チューニングが必要？確認作業について

ロードアレベージを確認する

スローログを確認する

プロセスを確認する

いざチューニング！。。お題目を披露

テーブル定義

今回のお題目の定義です。

クエリー

クエリーを解析

いざ、チューニング！！

まずは、JOIN句

EXPLAINの結果

応答速度

EXPLAINの結果