MySQL パーティショニングで高速化

こんにちは。エンジニアの中山です。

最近、ふるさと納税を初体験しました。
手間がかかりそうという先入観を持っていたのですが、実際にはネットで申し込めて、決済はクレジットカードで、美味しい特産品がすぐに届きました。
こんなに簡単なら、もっと早くから知っていればよかったのにと、知識の大切さを再認識しました。

マネトク！にはそんなお得な情報がたくさんあります。

さて、本題。

サービスが成長してデータベースが大規模化・高使用頻度化してくると、やはり遅いクエリが気になってきます。
対策として、インデックスの工夫、クエリのチューニング、キャッシュ設定、高速なハードウェアへの置き換え等の施策を行うことで、改善をすることができます。
しかし、さらに成長が進むと、それらの手段だけでは足りず、次の手が欲しくなってきます。
高速化する手段の1つとして、パーティショニングが手軽で効果的なので試してみました。

パーティショニングとは

条件によってレコードを振り分け、1つのテーブルを分割する機能です。
データファイルは複数に分かれるのですが、データベースのユーザーから見ると１つのテーブルのままなので、SELECT/UPDATE/INSERT/DELETEのクエリは変えず、そのまま使うことができます。

【参考】
MySQL :: MySQL 5.6 リファレンスマニュアル :: 19 パーティション化

計測用データの準備

パーティショニングの効果を試すために、次のような計測用データを用意しました。

３テーブル。データは同一パーティション無し(テーブル名 samples)4パーティション(テーブル名 samples_4_partitions)64パーティション(テーブル名 samples_64_partitions)各テーブル約1677万レコード(2の24乗)カラム id : オートインクリメントのIDfoo_id : 1～1000の範囲の乱数content : 乱数から生成した32文字の文字列

テーブル・データ作成のクエリ

CREATE TABLE `samples` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `foo_id` int(11) DEFAULT NULL,
  `content` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_foo_id` (`foo_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `samples_4_partitions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `foo_id` int(11) NOT NULL,
  `content` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`id`,`foo_id`),
  KEY `index_foo_id` (`foo_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY HASH (foo_id) PARTITIONS 4;

CREATE TABLE `samples_64_partitions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `foo_id` int(11) NOT NULL,
  `content` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`id`,`foo_id`),
  KEY `index_foo_id` (`foo_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY HASH (foo_id) PARTITIONS 64;

INSERT INTO samples () VALUES ();
INSERT INTO samples (id) SELECT 0 FROM samples;
INSERT INTO samples (id) SELECT 0 FROM samples;
INSERT INTO samples (id) SELECT 0 FROM samples;
INSERT INTO samples (id) SELECT 0 FROM samples;
INSERT INTO samples (id) SELECT 0 FROM samples;
INSERT INTO samples (id) SELECT 0 FROM samples;
INSERT INTO samples (id) SELECT 0 FROM samples;
INSERT INTO samples (id) SELECT 0 FROM samples;
INSERT INTO samples (id) SELECT 0 FROM samples;
INSERT INTO samples (id) SELECT 0 FROM samples;
INSERT INTO samples (id) SELECT 0 FROM samples;
INSERT INTO samples (id) SELECT 0 FROM samples;
INSERT INTO samples (id) SELECT 0 FROM samples;
INSERT INTO samples (id) SELECT 0 FROM samples;
INSERT INTO samples (id) SELECT 0 FROM samples;
INSERT INTO samples (id) SELECT 0 FROM samples;
INSERT INTO samples (id) SELECT 0 FROM samples;
INSERT INTO samples (id) SELECT 0 FROM samples;
INSERT INTO samples (id) SELECT 0 FROM samples;
INSERT INTO samples (id) SELECT 0 FROM samples;
INSERT INTO samples (id) SELECT 0 FROM samples;
INSERT INTO samples (id) SELECT 0 FROM samples;
INSERT INTO samples (id) SELECT 0 FROM samples;
INSERT INTO samples (id) SELECT 0 FROM samples;

UPDATE samples SET foo_id = CEIL(RAND()*1000), content = MD5(RAND());
INSERT INTO samples_4_partitions SELECT * FROM samples;
INSERT INTO samples_64_partitions SELECT * FROM samples;

空のテーブルを作り、1行INSERTし、レコード数を倍増させるINSERTを23回実行することにより、約1677万レコードに増加させたあと、内容をUPDATEしています。それをパーティション数の違う各テーブルにコピーしました。

【参考】
MySQLで簡単にランダムなテストデータを作成する方法 – Qiita

計測結果

1パーティションのみアクセスする例

> SELECT SQL_NO_CACHE count(*) FROM samples where foo_id = 500 and content < '8';
+----------+
| count(*) |
+----------+
|     8271 |
+----------+
1 row in set (0.39 sec)

> SELECT SQL_NO_CACHE count(*) FROM samples_4_partitions  where foo_id = 500 and content < '8';
+----------+
| count(*) |
+----------+
|     8271 |
+----------+
1 row in set (0.28 sec)

> SELECT SQL_NO_CACHE count(*) FROM samples_64_partitions where foo_id = 500 and content < '8';
+----------+
| count(*) |
+----------+
|     8271 |
+----------+
1 row in set (0.05 sec)

パーティション数が多く、細かく分割するほどほど速くなりました。

パーティション無しでも、foo_idのインデックスが効いているので、アクセスするレコード数は変わらないように思えますが、実際は速くなりました。参照の局所性が高まって、ストレージへのアクセス等が効率的になることが要因なのではと考えています。

多数のパーティションにアクセスする例

> SELECT SQL_NO_CACHE count(*) FROM samples where foo_id between 501 and 800;
+----------+
| count(*) |
+----------+
|  5033677 |
+----------+
1 row in set (0.90 sec)

> SELECT SQL_NO_CACHE count(*) FROM samples_4_partitions  where foo_id between 501 and 800;
+----------+
| count(*) |
+----------+
|  5033677 |
+----------+
1 row in set (0.82 sec)

> SELECT SQL_NO_CACHE count(*) FROM samples_64_partitions where foo_id between 501 and 800;
+----------+
| count(*) |
+----------+
|  5033677 |
+----------+
1 row in set (0.85 sec)

あまり差が無い結果になりました。
パーティショニングの効果を発揮させるには、アクセス対象が特定のパーティションのみで済むようにすることが大事です。