YAPC::Asia 2015に行ったらMySQLの気持ちが少しだけ理解できた

created: 2015/08/23 23:13

初めてのYAPC。
1日目は寝坊したので2日目から行った。
色々聴いたんだけど個人的に1番良かったのは"ISUCONの勝ち方"。

ISUCONの勝ち方 - YAPC::Asia Tokyo 2015

ハイスコアを出すためのサーバ構成の勘所とかの解説。
ISUCONは参加したことなかったから、どういう雰囲気でやってるかの紹介とかもあって良かった。

あと、ISUCONは"すごい人たちが何かすごい感じでチューニングする"っていうぼんやりとした認識だったんだけど、
トークを聴いてると"丁寧にボトルネックを調べてしっかり直す"印象で、自分みたいな普通の人間にもやれそうな感じがしてくる、やる気高まる良いトークでした。


で、ここからはMySQLの気持ちを理解する話。

トークの後半にSQLのチューニングの関連で「InnoDBはクラスタインデックスだから云々」「カバリングインデックスを利用してIOが云々」という話があって、あんまりちゃんと理解出来てなかったので家に帰ってから動かして調べた。

InnoDBのインデックスの仕組みについては以下のページがすごくわかりやすい。
漢(オトコ)のコンピュータ道: 知って得するInnoDBセカンダリインデックス活用術!
というか知りたかったのはこれが全てで、あとは納得するために実行時間を計測しただけ、みたいになってしまった。

今回調べたのは以下

  1. セカンダリインデックスは木の葉に主キーの値しか持っていないので、データを取得する際に主キーの値で再度検索が必要になる。主キーでの検索とセカンダリインデックスでの検索でどれくらい差が出るか
  2. セカンダリインデックスの木の葉でもインデックスを張った列の値は含まれるので、SELECTの際はインデックスが張られた列の値のみを取得することで主キーでの検索を省略できる(カバリングインデックス)。どれくらい速いのか

手元のMySQLのバージョンは

mysql> select version();
+-------------------------+
| version()               |
+-------------------------+
| 5.6.25-0ubuntu0.15.04.1 |
+-------------------------+
1 row in set (0.00 sec)

データを生成したり時間を測ったりするために以下のコードを書いた。
bench.rb

主キー vs セカンダリインデックス

以下の2テーブルを作る。

CREATE TABLE `data` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `data2` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  KEY `idx_id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

前者はid列を主キーとして、後者はid列をセカンダリインデックスとして指定した。後者のテーブルでは主キーを指定していないけれど、InnoDBではPRIMARY KEY若しくはNOT NULLなUNIQUEが指定されない場合、隠しクラスタインデックスなるものが自動で生成されるらしい。
MySQL :: MySQL 5.6 リファレンスマニュアル :: 14.2.13.2 クラスタインデックスとセカンダリインデックス
なので後者のテーブルでも"セカンダリインデックスで主キーの値を検索して主キーの値で再度検索"という挙動は変わらないはず。

データは先ほどのbench.rbで1000万件のデータを出力してLOAD DATA INFILEでテーブルdata, data2に流しこむ。
速度は、指定回数だけ

SELECT * FROM data WHER id = 1234

のようなクエリを発行してかかった時間を計測する。bench.rbを使うと1万回のSELECTなら以下のような感じになる。

bonono@vaiopro13:~/Desktop/idx$ ruby bench.rb select_id_bench --count=10000 --table=data
Result: 0.909373292

テーブルdata, data2に対してSELECTの実行回数を変えつつ実行した結果は以下(単位は秒)

SELECT回数 data(主キー) data2(セカンダリインデックス)
1000 0.0895 0.1062
10000 0.8868 1.0975
100000 8.9197 10.8839

確かにセカンダリインデックスでの検索の方が1割程度遅い。
手元の環境だと10万回で1秒程度の差が出た。

カバリングインデックス

次は所謂カバリングインデックスを試す。
以下の2テーブルを作成。

CREATE TABLE `data3` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `data4` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

両者ともname列にはセカンダリインデックスを設定し、前者のテーブルのみid列を主キーとして設定する。
データは先ほど通り1000万件で, 以下のようなクエリを指定回数だけ発行して速度を計測する。

SELECT id FROM data3 WHERE name = 'foobar1234';

"名前で検索してidを調べる"みたいなクエリ。SELECTの結果としてid列だけを返しているのがポイント。
こういうクエリを発行した場合、テーブルdata3はセカンダリインデックスの木の葉にname列の値に加え主キーであるidの値を暗黙的に保持しているため、主キーでの検索を行うこと無く結果(id列の値)を返すことができる、というのがカバリングインデックスという手法らしい(セカンダリインデックス自身にid列を含めるのもアリっぽい。でも出来ることならインデックスは小さく抑えたいよね)
テーブルdata4には主キーが無いため(多分隠しクラスタインデックスとやらへの参照を持ってるんじゃないかな)、同クエリでもidの値を取得するため再度検索を行う必要があるので、テーブルdata3よりも低速に動作するはず。
というわけで速度確認。

SELECT回数 data3(主キー有) data4(主キー無)
1000 0.0928 0.1003
10000 0.9365 1.1474
100000 9.6414 13.3038

予想通りテーブルdata3へのクエリの方が速い(テーブルdata4への10万回がやけに遅い気がするけど)
ちなみにSELECTで取得する列をidからnameに変更した場合は両テーブルとも同程度の速度でクエリを捌ける(どちらもセカンダリインデックスの木の葉にname列の値を持っているため、主キーによる検索が不要)

# 雑に確認(上のベンチマークは--fetch-columnオプションに"id"を与えて計測してた
bonono@vaiopro13:~/Desktop/idx$ ruby bench.rb select_name_bench --count=100000 --table=data3 --fetch-column=name
Result: 9.422176969
bonono@vaiopro13:~/Desktop/idx$ ruby bench.rb select_name_bench --count=100000 --table=data4 --fetch-column=name
Result: 9.538485882

MySQLのことばっかりになったけど、YAPC良かったです。これが最後なのがちょっと寂しい。