MySQLで、パーティショニングを使った時に、どのパーティションにどれくらいのデータが入っているかを
確認するには、information_schemaにあるpartitionsテーブルを見ればよいみたいです。
※確認は、MySQL 5.7で行っています
こんなテーブルです。
mysql> DESC information_schema.partitions; +-------------------------------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------------------------+---------------------+------+-----+---------+-------+ | TABLE_CATALOG | varchar(512) | NO | | | | | TABLE_SCHEMA | varchar(64) | NO | | | | | TABLE_NAME | varchar(64) | NO | | | | | PARTITION_NAME | varchar(64) | YES | | NULL | | | SUBPARTITION_NAME | varchar(64) | YES | | NULL | | | PARTITION_ORDINAL_POSITION | bigint(21) unsigned | YES | | NULL | | | SUBPARTITION_ORDINAL_POSITION | bigint(21) unsigned | YES | | NULL | | | PARTITION_METHOD | varchar(18) | YES | | NULL | | | SUBPARTITION_METHOD | varchar(12) | YES | | NULL | | | PARTITION_EXPRESSION | longtext | YES | | NULL | | | SUBPARTITION_EXPRESSION | longtext | YES | | NULL | | | PARTITION_DESCRIPTION | longtext | YES | | NULL | | | TABLE_ROWS | bigint(21) unsigned | NO | | 0 | | | AVG_ROW_LENGTH | bigint(21) unsigned | NO | | 0 | | | DATA_LENGTH | bigint(21) unsigned | NO | | 0 | | | MAX_DATA_LENGTH | bigint(21) unsigned | YES | | NULL | | | INDEX_LENGTH | bigint(21) unsigned | NO | | 0 | | | DATA_FREE | bigint(21) unsigned | NO | | 0 | | | CREATE_TIME | datetime | YES | | NULL | | | UPDATE_TIME | datetime | YES | | NULL | | | CHECK_TIME | datetime | YES | | NULL | | | CHECKSUM | bigint(21) unsigned | YES | | NULL | | | PARTITION_COMMENT | varchar(80) | NO | | | | | NODEGROUP | varchar(12) | NO | | | | | TABLESPACE_NAME | varchar(64) | YES | | NULL | | +-------------------------------+---------------------+------+-----+---------+-------+ 25 rows in set (0.00 sec)
例えば、KEYパーティショニング、パーティション数8で構成したテーブルの状態を見てみると、こんな感じ。
※データは適当に入れています
mysql> SELECT table_schema, table_name, partition_name, partition_method, table_rows FROM information_schema.partitions WHERE table_name = 'person'; +--------------+------------+----------------+------------------+------------+ | table_schema | table_name | partition_name | partition_method | table_rows | +--------------+------------+----------------+------------------+------------+ | practice | person | p0 | KEY | 740 | | practice | person | p1 | KEY | 2768 | | practice | person | p2 | KEY | 740 | | practice | person | p3 | KEY | 1260 | | practice | person | p4 | KEY | 740 | | practice | person | p5 | KEY | 1752 | | practice | person | p6 | KEY | 740 | | practice | person | p7 | KEY | 1260 | +--------------+------------+----------------+------------------+------------+ 8 rows in set (0.00 sec)
参照する時は、スキーマ名やテーブル名で絞り込むことになるでしょう。
また、単にパーティションを確認するなら、EXPLAINを使うのもありみたいです。
※こちらは、パーティション数が16に…
mysql> EXPLAIN SELECT * FROM person; +----+-------------+--------+-------------------------------------------------------+------+---------------+------+---------+------+-------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+-------------------------------------------------------+------+---------------+------+---------+------+-------+----------+-------+ | 1 | SIMPLE | person | p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12,p13,p14,p15 | ALL | NULL | NULL | NULL | NULL | 10000 | 100.00 | NULL | +----+-------------+--------+-------------------------------------------------------+------+---------------+------+---------+------+-------+----------+-------+ 1 row in set, 1 warning (0.00 sec)
