- インデックスをつけるだけでカバー出来る場合が多い。
- パーショニングを使わずに、単にテーブルを分けてしまえばいい。
- テーブルが巨大にならないとあまり効果を実感できない。
- 使い方を間違えると性能が落ちてしまう。
そんなわけで、今日と明日でパーティショニングが役に立つシーンを2つ紹介しようと思う。今日は一つ目、インデックスをつけたいカラムのカーディナリティが低い場合だ。カーディナリティとは日本語に訳すと濃度とか訳されるが、要は値の種類（分散具合）のことである。例えば、YesかNoの2つの値しかとらないカラムは非常にカーディナリティが低く、インデックスをつけるととても効率が悪い。インデックスを使って目的の行を見付けようとしても、インデックススキャンが起きるだけなので、オプティマイザはしばしばそのようなスキャンを回避して、テーブルスキャンを選択してしまう。（その方がインデックスと行の間の行き来がなくなるので、高速になるからだ。）ただし、どちらか一方の値を持つ行だけが圧倒的に少ないというような場合、少ない方の値を指定すればオプティマイザはインデックスを使用する。例えばYesの行が10万行あって、Noの行が100行のとき、条件でNoを指定すればオプティマイザはインデックスを利用するだろう。しかし、YesとNoの行が半々ぐらいの場合には、インデックスを利用することは殆ど無い。
このような場合、パーティショニングが役に立つ。そのカラムの値をWHERE句内で指定してクエリを実行すれば、検索は片方のパーティションだけを見て行えば良いのでクエリの効率は格段に向上する。利用出来るインデックスがなくてスキャンが発生したとしても、片方のパーティションをスキャンするだけでいいので、検索のコストは半分である。カラムの値が2つしかないというのは最も極端な例であるが、カラムがとる値が数個しかないというのはよくあることだろう。おなじみのMySQL公式サンプルデータベースであるworldデータベースから例を挙げると、CountryテーブルのContinentカラムなどが良い例ではないだろうか。Countryテーブルは次のように定義されており、Continentカラムは7種類の値しかとらない。
mysql> SHOW CREATE TABLE Country\G
*************************** 1. row ***************************
Table: Country
Create Table: CREATE TABLE `Country` (
`Code` char(3) NOT NULL DEFAULT '',
`Name` char(52) NOT NULL DEFAULT '',
`Continent` enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') NOT NULL DEFAULT 'Asia',
`Region` char(26) NOT NULL DEFAULT '',
`SurfaceArea` float(10,2) NOT NULL DEFAULT '0.00',
`IndepYear` smallint(6) DEFAULT NULL,
`Population` int(11) NOT NULL DEFAULT '0',
`LifeExpectancy` float(3,1) DEFAULT NULL,
`GNP` float(10,2) DEFAULT NULL,
`GNPOld` float(10,2) DEFAULT NULL,
`LocalName` char(45) NOT NULL DEFAULT '',
`GovernmentForm` char(45) NOT NULL DEFAULT '',
`HeadOfState` char(60) DEFAULT NULL,
`Capital` int(11) DEFAULT NULL,
`Code2` char(2) NOT NULL DEFAULT '',
PRIMARY KEY (`Code`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
Continentカラムでパーティショニングを行うわけであるが、ここで一つ問題がある。パーティショニングは整数値に基づいて行う必要があるので、ENUM型をそのまま用いることが出来ない。なので、次のように整数型のカラムContinentIdを追加して、Continentの値に応じて1〜7までの整数値を格納することにする。
mysql> ALTER TABLE Country ADD ContinentId TINYINT UNSIGNED NOT NULL AFTER Continent; Query OK, 239 rows affected (0.40 sec) Records: 239 Duplicates: 0 Warnings: 0 mysql> UPDATE Country SET ContinentId=1 WHERE Continent='Asia'; Query OK, 51 rows affected (0.40 sec) Rows matched: 51 Changed: 51 Warnings: 0 ...
ContinentIdカラムがあると元のContinentカラムは必要なくなってしまうわけであるが、カラムを残しておくか、DROPしてしまうか、別のテーブルContinentを作成してそちらで名前とIDの紐付けを行うかはアプリの設計次第である。
ContinentIdカラムに基づいてパーティショニングを行うには、ContinentIdカラムをPRIMARY KEYに含める必要がある。
mysql> ALTER TABLE Country DROP PRIMARY KEY, ADD PRIMARY KEY (Code, ContinentId); Query OK, 239 rows affected (0.40 sec) Records: 239 Duplicates: 0 Warnings: 0
さて、いよいよパーティショニングを行うわけであるが、4種類あるパーティショニングの方式のうちどれを用いれば良いだろうか？（パーティショニングの種類については、この記事などを参考にして欲しい。）それぞれの値を明確に指定できるLISTパーティショニングを選んでしまいそうになるが、オトコ的にはここはHASHパーティショニングを推したい。なぜならば、その方が高速だし記述が簡単だから。1〜7のいずれか・・・という場合のように、連続した値に基づいてパーティショニングする場合には、HASHパーティショニングがお勧めである。
mysql> ALTER TABLE Country PARTITION BY HASH (ContinentId) PARTITIONS 7; Query OK, 239 rows affected (0.67 sec) Records: 239 Duplicates: 0 Warnings: 0
パーティションの定義が出来たら、さっそくパーティショニングが有効かどうかを確かめてみよう。オプティマイザがどのパーティションをクエリの対象にするかということを調べるには、EXPLAIN PARTITIONSコマンドを使えば良い。
mysql> EXPLAIN PARTITIONS SELECT * FROM Country WHERE ContinentId=1 AND Population > 1000000; +----+-------------+---------+------------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+------------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | Country | p1 | ALL | NULL | NULL | NULL | NULL | 239 | Using where | +----+-------------+---------+------------+------+---------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec)
ごらんの通り、ContinentIdを指定すれば一つのパーティションだけからクエリが行われていることが分かる。ContinentIdを複数指定した場合でも、該当のパーティションだけがクエリの対象になることが分かる。
mysql> EXPLAIN PARTITIONS SELECT * FROM Country WHERE ContinentId IN (1,2,3) AND Population > 1000000; +----+-------------+---------+------------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+------------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | Country | p1,p2,p3 | ALL | NULL | NULL | NULL | NULL | 239 | Using where | +----+-------------+---------+------------+------+---------------+------+---------+------+------+-------------+ 1 row in set (0.03 sec)
このようにオプティマイザがパーティションを絞り込むことをPartition Pruningと呼ぶので覚えておこう。
ちなみに、定義できるパーティションの数は最大で1024個までである。が、あまりに多くパーティショニングを定義し過ぎると、Pruningが効かなかった場合にオーバーヘッドが大きくなるので注意が必要である。（1024個のパーティションを定義していると、1024個のパーティションに対して問い合わせが発生してしまう!!）
ただし、Countryテーブルは行数がとても少ないので、パーティショニングによってどれだけ性能が向上するか、またはオーバーヘッドにより低下するかということを実感するのは難しいだろう。
パーティショニングはJOINを行うときも有効である。各テーブルで検索条件を指定しなければならないような場合には、パーティショニングを組み合わせると非常に有効である。例えば次のようにCountryテーブルとCityテーブルをJOINして、それぞれのテーブルで追加のWHERE句を指定するような場合である。このようなクエリはパーティショニングを用いないと、とても遅くなってしまう傾向にある。
mysql> EXPLAIN PARTITIONS SELECT * FROM City LEFT JOIN Country ON City.Name = Country.Name WHERE ContinentInt=1 AND City.Population > 1000000; +----+-------------+---------+------------+------+---------------+------+---------+------+------+--------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+------------+------+---------------+------+---------+------+------+--------------------------------+ | 1 | SIMPLE | Country | p1 | ALL | NULL | NULL | NULL | NULL | 239 | Using where | | 1 | SIMPLE | City | NULL | ALL | NULL | NULL | NULL | NULL | 4079 | Using where; Using join buffer | +----+-------------+---------+------------+------+---------------+------+---------+------+------+--------------------------------+ 2 rows in set (0.00 sec)
このクエリは、CountryテーブルおよびCityテーブルにおいて、結合するカラム以外の条件をWHERE句で指定している。Countryテーブルはパーティションp1だけをスキャンするだけでいいが、Cityテーブルは全件スキャンが発生している。
そこで、次のようにPopulationに従ってパーティションを定義する。ここでは、RANGEパーティショニングを利用している。
mysql> ALTER TABLE City DROP PRIMARY KEY, ADD PRIMARY KEY (ID, Population); Query OK, 4079 rows affected (0.43 sec) Records: 4079 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE City PARTITION BY RANGE (Population) ( -> PARTITION p1 VALUES LESS THAN (1000), -> PARTITION p2 VALUES LESS THAN (10000), -> PARTITION p3 VALUES LESS THAN (100000), -> PARTITION p4 VALUES LESS THAN (1000000), -> PARTITION p5 VALUES LESS THAN (10000000), -> PARTITION p6 VALUES LESS THAN MAXVALUE); Query OK, 4079 rows affected (0.46 sec) Records: 4079 Duplicates: 0 Warnings: 0
すると、それぞれのテーブルにおいてPruningが効いているのが分かるだろう。
mysql> EXPLAIN PARTITIONS SELECT * FROM City LEFT JOIN Country ON City.Name = Country.Name WHERE ContinentInt=1 AND City.Population > 1000000; +----+-------------+---------+------------+------+---------------+------+---------+------+------+--------------------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+---------+------------+------+---------------+------+---------+------+------+--------------------------------+| 1 | SIMPLE | Country | p1 | ALL | NULL | NULL | NULL | NULL | 239 | Using where | | 1 | SIMPLE | City | p5,p6 | ALL | NULL | NULL | NULL | NULL | 4079 | Using where; Using join buffer | +----+-------------+---------+------------+------+---------------+------+---------+------+------+--------------------------------+ 2 rows in set (0.00 sec)
題材として用いているテーブルが小さいので実行時間ではあまり差は出ないが、実際にテーブルから取得した行数を見ることで効果を確認することができる。まず、パーティショニングがある場合。
mysql> FLUSH STATUS; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM City LEFT JOIN Country ON City.Name = Country.Name WHERE ContinentInt=1 AND City.Population > 1000000; ...略... 1 row in set (0.02 sec) mysql> SHOW STATUS LIKE 'Handler%'; +----------------------------+-------+ | Variable_name | Value | +----------------------------+-------+ | Handler_commit | 0 | | Handler_delete | 0 | | Handler_discover | 0 | | Handler_prepare | 0 | | Handler_read_first | 0 | | Handler_read_key | 0 | | Handler_read_next | 0 | | Handler_read_prev | 0 | | Handler_read_rnd | 0 | | Handler_read_rnd_next | 292 | <------ | Handler_rollback | 0 | | Handler_savepoint | 0 | | Handler_savepoint_rollback | 0 | | Handler_update | 0 | | Handler_write | 0 | +----------------------------+-------+ 15 rows in set (0.05 sec)
次に、パーティショニングを解除した場合。パーティショニングの解除は、ALTER TABLE ... REMOVE PARTITIONINGで行う。
mysql> ALTER TABLE City REMOVE PARTITIONING; Query OK, 4079 rows affected (0.41 sec) Records: 4079 Duplicates: 0 Warnings: 0 mysql> FLUSH STATUS; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM City LEFT JOIN Country ON City.Name = Country.Name WHERE ContinentInt=1 AND City.Population > 1000000; ...略... 1 row in set (0.02 sec) mysql> SHOW STATUS LIKE 'Handler%'; +----------------------------+-------+ | Variable_name | Value | +----------------------------+-------+ | Handler_commit | 0 | | Handler_delete | 0 | | Handler_discover | 0 | | Handler_prepare | 0 | | Handler_read_first | 0 | | Handler_read_key | 0 | | Handler_read_next | 0 | | Handler_read_prev | 0 | | Handler_read_rnd | 0 | | Handler_read_rnd_next | 4132 | <------ | Handler_rollback | 0 | | Handler_savepoint | 0 | | Handler_savepoint_rollback | 0 | | Handler_update | 0 | | Handler_write | 0 | +----------------------------+-------+ 15 rows in set (0.00 sec)
実際にテーブルへアクセスする回数（矢印のところ）が激減しているのが分かるだろう。巨大なテーブルを扱う場合や、クエリが多重で走る場合などには差は歴然となる。
そんなわけで、カーディナリティが低いカラムを検索条件に指定しなければいけない場合には、パーティショニングをうまく活用することでクエリの高速化に挑戦してみて欲しい。
0 コメント:
コメントを投稿