MySQLパーティショニングでパフォーマンスアップ！

logomysql

mysqlには、レンジパーティションってものがありまして、うーなんでしょ？ある規則にしがったデータをおのおののデータファイルに振り分けてくれる機能です。

・データ領域が分割されるため、大量のデータを処理することによる性能上のボトルネックの発生を抑えられる・MyISAMなど、テーブルサイズに上限がある場合でもそれ以上のデータを格納することが可能になる

といった点です、少ないデータですとこれといった利点はないかと思いますが、数百万規模データですと、このデータ分割が、大きな効果を呼ぶ。。かもしれないですし、そうでないかもしれません（汗ただ、はっきりといえるのが、ある規則に従ったデータを、SELECTする際にそのSELECTで必要の無いデータまで、mysqlがシークする必要がないっていったところでしょうか？ただし、データをまたぐ検索が発生する場合は、パフォーマンスは非パーティションテーブルと比較して結構落ちると思います。合うか会わないかは設計しだいです。

[現状のテーブル状態を確認する]

[sql] | squad_tables | CREATE TABLE squad_tables ( id bigint(20) unsigned NOT NULL AUTO_INCREMENT, detail1 varchar(26) DEFAULT NULL, detail2 enum('cate','com','blog') NOT NULL DEFAULT 'cate', PRIMARY KEY (id) ) ENGINE=InnoDB AUTO_INCREMENT=271124 DEFAULT CHARSET=utf8 |

mysql> select count() from squad_tables; +----------+ | count() | +----------+ | 271123 | +----------+ 1 row in set (0.00 sec)

[/sql]

1.まずは、既存のテーブルを、パーティション表に変換できるか検証

[sql]

mysql> alter table squad_tables -> PARTITION BY RANGE (id) ( -> PARTITION p000000 VALUES LESS THAN (100000), -> PARTITION p100000 VALUES LESS THAN (200000), -> PARTITION p200000 VALUES LESS THAN (300000), -> PARTITION pover VALUES LESS THAN MAXVALUE -> ); Query OK, 271123 rows affected (4.97 sec) Records: 271123 Duplicates: 0 Warnings: 0

mysql> select count() from squad_tables; +----------+ | count() | +----------+ | 271123 | +----------+ 1 row in set (0.11 sec)

★正常終了したことを確認

[/sql]

2. 上記のパーティションのデータ分布状況を調べる

[sql]

select TABLE_SCHEMA,TABLE_NAME,PARTITION_NAME,PARTITION_ORDINAL_POSITION,TABLE_ROWS from INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME='squad_tables';

mysql> select count() from squad_tables; +----------+ | count() | +----------+ | 271123 | +----------+ 1 row in set (0.00 sec)

[/sql]

★たぶん、分布されていると思う

3.上記にて作成したテーブルに、新規パーティションを追加する

[sql]

ALTER TABLE squad_tables ADD PARTITION ( PARTITION p300000 VALUES LESS THAN (400000) );

[/sql]

×ERROR 1481 (HY000): MAXVALUE can only be used in last partition definition RANGE パーティショニングされているテーブルにおいて、ADD PARTITION は既存パーティションの「後」にしかパーティションの追加ができない（「前」や「間」はNGです）。そのためMAXVALUEが既に存在する場合はMAXVALUE より後の値は存在し得ないため、新規パーティションが作成できないということになります。

4. 上記3に対する代替案 上記の場合は、新規でパーティションを作るより、データを再配置（REORGANIZE）する方法で実現することができます。

[sql]

mysql> ALTER TABLE squad_tables REORGANIZE PARTITION pover INTO ( -> PARTITION p300000 VALUES LESS THAN (400000), -> PARTITION pover VALUES LESS THAN MAXVALUE -> ); Query OK, 0 rows affected (0.10 sec) Records: 0 Duplicates: 0 Warnings: 0

mysql> select count() from squad_tables; +----------+ | count() | +----------+ | 271123 | +----------+ 1 row in set (0.11 sec)

[/sql]

・・ちょっと気になることが [sql]

[/sql]

気になるところは、p000000とp100000のデータ配置が変化しております。いろいろ調べていくうちにわかったのですが、REORGANIZE PARTITIONを行うと、データの再配置を非同期で行うようです。

・再度、分布状態を確認する

[sql] +--------------+-----------------+----------------+----------------------------+------------+ | TABLE_SCHEMA | TABLE_NAME | PARTITION_NAME | PARTITION_ORDINAL_POSITION | TABLE_ROWS | +--------------+-----------------+----------------+----------------------------+------------+ | say | squad_tables | p000000 | 1 | 99622 | | say | squad_tables | p100000 | 2 | 100211 | | say | squad_tables | p200000 | 3 | 71523 | | say | squad_tables | p300000 | 4 | 0 | | say | squad_tables | pover | 5 | 0 | +--------------+-----------------+----------------+----------------------------+------------+ 5 rows in set (0.00 sec)

[/sql]

以上の結果から、パーティションを作成する際は、

▼MAXVALUEはあえて指定しないある程度、バッファを持たせた形でレンジを振っておく

等の設計を行っておいたほうが良いかと思います。

6.既存のパーティションをさらに細分化する 作成したパーティションをさらに細分化したい場合、たとえば、squad_tablesの例ですと100000レンジで、パーティションを分けておりますが、さらにこれを、10000単位で分割しなおしたいという場合は、REORGANIZE PARTITIONを利用して、すべて再配置することが可能になります。ただし上記5同様再配置に時間がかかります。

[sql]

ALTER TABLE squad_tables REORGANIZE PARTITION p000000, p100000, p200000, p300000,pover INTO ( PARTITION p00000 VALUES LESS THAN (10000), PARTITION p10000 VALUES LESS THAN (20000), PARTITION p20000 VALUES LESS THAN (30000), PARTITION p30000 VALUES LESS THAN (40000), PARTITION p40000 VALUES LESS THAN (50000), PARTITION p50000 VALUES LESS THAN (60000), PARTITION p60000 VALUES LESS THAN (70000), PARTITION p70000 VALUES LESS THAN (80000), PARTITION p80000 VALUES LESS THAN (90000), PARTITION p90000 VALUES LESS THAN (100000), PARTITION pover VALUES LESS THAN MAXVALUE );

Query OK, 271123 rows affected (5.00 sec) Records: 271123 Duplicates: 0 Warnings: 0

mysql> select count(*) from squad_tables;

+----------+ | count(*) | +----------+ | 271123 | +----------+ 1 row in set (0.10 sec)

[/sql]

★細分化されました [sql]

+--------------+-----------------+----------------+----------------------------+------------+ | TABLE_SCHEMA | TABLE_NAME | PARTITION_NAME | PARTITION_ORDINAL_POSITION | TABLE_ROWS | +--------------+-----------------+----------------+----------------------------+------------+ | say | squad_tables | p00000 | 1 | 9975 | | say | squad_tables | p10000 | 2 | 9851 | | say | squad_tables | p20000 | 3 | 10421 | | say | squad_tables | p30000 | 4 | 10236 | | say | squad_tables | p40000 | 5 | 10222 | | say | squad_tables | p50000 | 6 | 9761 | | say | squad_tables | p60000 | 7 | 9992 | | say | squad_tables | p70000 | 8 | 10222 | | say | squad_tables | p80000 | 9 | 10166 | | say | squad_tables | p90000 | 10 | 9984 | | say | squad_tables | pover | 11 | 173134 | +--------------+-----------------+----------------+----------------------------+------------+ 11 rows in set (0.00 sec)

[/sql]

7.おまけ ○パーティションの一部を消したい場合

[sql]

！！！！この作業を行うと、パーティション内に格納されていたデータはすべてなくなります！！！ ALTER TABLE squad_tables DROP PARTITION p00000; ALTER TABLE squad_tables DROP PARTITION p10000; ALTER TABLE squad_tables DROP PARTITION p20000; ALTER TABLE squad_tables DROP PARTITION p30000;

・データ件数が減っているのがわかるかと思います mysql> select count() from squad_tables; +----------+ | count() | +----------+ | 231124 | +----------+ 1 row in set (0.08 sec)

p00000、p10000、p20000、p30000 がなくなっております。

+--------------+-----------------+----------------+----------------------------+------------+ | TABLE_SCHEMA | TABLE_NAME | PARTITION_NAME | PARTITION_ORDINAL_POSITION | TABLE_ROWS | +--------------+-----------------+----------------+----------------------------+------------+ | say | squad_tables | p40000 | 1 | 9819 | | say | squad_tables | p50000 | 2 | 9779 | | say | squad_tables | p60000 | 3 | 10513 | | say | squad_tables | p70000 | 4 | 10082 | | say | squad_tables | p80000 | 5 | 10215 | | say | squad_tables | p90000 | 6 | 9968 | | say | squad_tables | pover | 7 | 170286 | +--------------+-----------------+----------------+----------------------------+------------+ 7 rows in set (0.00 sec)

squad_tables | CREATE TABLE squad_tables ( id bigint(20) unsigned NOT NULL AUTO_INCREMENT, detail1 varchar(26) DEFAULT NULL, detail2 enum('cate','com','blog') NOT NULL DEFAULT 'cate', PRIMARY KEY (id) ) ENGINE=InnoDB AUTO_INCREMENT=271124 DEFAULT CHARSET=utf8 /!50100 PARTITION BY RANGE (id) (PARTITION p40000 VALUES LESS THAN (50000) ENGINE = InnoDB, PARTITION p50000 VALUES LESS THAN (60000) ENGINE = InnoDB, PARTITION p60000 VALUES LESS THAN (70000) ENGINE = InnoDB, PARTITION p70000 VALUES LESS THAN (80000) ENGINE = InnoDB, PARTITION p80000 VALUES LESS THAN (90000) ENGINE = InnoDB, PARTITION p90000 VALUES LESS THAN (100000) ENGINE = InnoDB, PARTITION pover VALUES LESS THAN MAXVALUE ENGINE = InnoDB) / |

[/sql]

○そもそもパーティションをやめて非パーティションのテーブルに戻したい場合

[sql]

mysql> ALTER TABLE squad_tables REMOVE PARTITIONING; Query OK, 271123 rows affected (5.02 sec) Records: 271123 Duplicates: 0 Warnings: 0

| squad_tables | CREATE TABLE squad_tables ( id bigint(20) unsigned NOT NULL AUTO_INCREMENT, disp_id varchar(26) DEFAULT NULL, type enum('cate','com','blog') NOT NULL DEFAULT 'cate', env enum('PC','MB') NOT NULL DEFAULT 'PC', category_id int(10) unsigned NOT NULL DEFAULT '0', user_id bigint(20) unsigned NOT NULL DEFAULT '0', word varchar(200) DEFAULT NULL, url varchar(255) DEFAULT NULL, ext enum('no','jpg','gif','png','exist') DEFAULT 'no', res int(10) unsigned NOT NULL DEFAULT '0', iine int(10) unsigned NOT NULL DEFAULT '0', community_id bigint(20) unsigned DEFAULT NULL, status enum('draft','disp') NOT NULL DEFAULT 'draft', res_modified datetime DEFAULT NULL, sage_flg enum('off','on') NOT NULL DEFAULT 'off', disp_date datetime DEFAULT NULL, delete_flag tinyint(4) NOT NULL DEFAULT '0', created datetime DEFAULT NULL, updated datetime DEFAULT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB AUTO_INCREMENT=271124 DEFAULT CHARSET=utf8 |