日向夏特殊応援部隊

俺様向けメモ

Partitioning と information_schema.PARTITIONS のメモ

MySQL

例によって information_schema に partitioning されたテーブルの情報がある訳ですが、ちょっとメモ。ちなみに SQL としてはこんな感じです。

SELECT PARTITION_NAME, SUBPARTITION_NAME, PARTITION_ORDINAL_POSITION, SUBPARTITION_ORDINAL_POSITION, PARTITION_METHOD, SUBPARTITION_METHOD, PARTITION_EXPRESSION, SUBPARTITION_EXPRESSION, PARTITION_DESCRIPTION, PARTITION_COMMENT, NODEGROUP, TABLESPACE_NAME FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA = 'test_partition' AND TABLE_NAME = 'k1'

で抽出はこんな感じ。

perl -MDBI=neat -e 'my $dbh = DBI->connect("dbi:mysql:dbname=test_partition", "root", ""); my @columns = qw(PARTITION_NAME SUBPARTITION_
NAME PARTITION_ORDINAL_POSITION SUBPARTITION_ORDINAL_POSITION PARTITION_METHOD SUBPARTITION_METHOD PARTITION_EXPRESSION SUBPARTITION_EXPRESSION PARTITION_DE
SCRIPTION PARTITION_COMMENT NODEGROUP TABLESPACE_NAME); my @short_names = qw(name s_name ord_pos s_ord_pos method s_method exp s_exp desc comment nodegroup
tablespace_name); my $rs = $dbh->selectall_arrayref("SELECT " . join(",", @columns) . " FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA = ? AND TABLE_
NAME = ?", undef, @ARGV); sub print_row { print "|| " . join(" || ", @_) . " ||\n" } print_row(@short_names); for (@$rs) { print_row(map { defined $_ ? $_ :
 neat $_ } @$_) }' test_partition k1

Key

15.2.4. KEY パーティショニング からテーブル定義を拝借。

CREATE TABLE `k1` (
  `id` int(11) NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
/*!50100 PARTITION BY KEY ()
PARTITIONS 2 */
name s_name ord_pos s_ord_pos method s_method exp s_exp desc comment nodegroup tablespace_name
p0 undef 1 undef KEY undef undef undef default undef
p1 undef 2 undef KEY undef undef undef default undef

HASH

15.2.3. HASH パーティショニング より

CREATE TABLE `employees` (
  `id` int(11) NOT NULL,
  `fname` varchar(30) DEFAULT NULL,
  `lname` varchar(30) DEFAULT NULL,
  `hired` date NOT NULL DEFAULT '1970-01-01',
  `separated` date NOT NULL DEFAULT '9999-12-31',
  `job_code` int(11) DEFAULT NULL,
  `store_id` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
/*!50100 PARTITION BY HASH ( YEAR(hired))
PARTITIONS 4 */
name s_name ord_pos s_ord_pos method s_method exp s_exp desc comment nodegroup tablespace_name
p0 undef 1 undef HASH undef YEAR(hired) undef undef default undef
p1 undef 2 undef HASH undef YEAR(hired) undef undef default undef
p2 undef 3 undef HASH undef YEAR(hired) undef undef default undef
p3 undef 4 undef HASH undef YEAR(hired) undef undef default undef

LIST

15.2.2. LIST パーティショニング

CREATE TABLE `employees2` (
  `id` int(11) NOT NULL,
  `fname` varchar(30) DEFAULT NULL,
  `lname` varchar(30) DEFAULT NULL,
  `hired` date NOT NULL DEFAULT '1970-01-01',
  `separated` date NOT NULL DEFAULT '9999-12-31',
  `job_code` int(11) DEFAULT NULL,
  `store_id` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
/*!50100 PARTITION BY LIST (store_id)
(PARTITION pNorth VALUES IN (3,5,6,9,17) ENGINE = MyISAM,
 PARTITION pEast VALUES IN (1,2,10,11,19,20) ENGINE = MyISAM,
 PARTITION pWest VALUES IN (4,12,13,14,18) ENGINE = MyISAM,
 PARTITION pCentral VALUES IN (7,8,15,16) ENGINE = MyISAM) */
name s_name ord_pos s_ord_pos method s_method exp s_exp desc comment nodegroup tablespace_name
pNorth undef 1 undef LIST undef store_id undef 3,5,6,9,17 default undef
pEast undef 2 undef LIST undef store_id undef 1,2,10,11,19,20 default undef
pWest undef 3 undef LIST undef store_id undef 4,12,13,14,18 default undef
pCentral undef 4 undef LIST undef store_id undef 7,8,15,16 default undef

RANGE

15.2.1. RANGE パーティショニング

CREATE TABLE `employees3` (
  `id` int(11) NOT NULL,
  `fname` varchar(30) DEFAULT NULL,
  `lname` varchar(30) DEFAULT NULL,
  `hired` date NOT NULL DEFAULT '1970-01-01',
  `separated` date NOT NULL DEFAULT '9999-12-31',
  `job_code` int(11) NOT NULL,
  `store_id` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (store_id)
(PARTITION p0 VALUES LESS THAN (6) ENGINE = MyISAM,
 PARTITION p1 VALUES LESS THAN (11) ENGINE = MyISAM,
 PARTITION p2 VALUES LESS THAN (16) ENGINE = MyISAM,
 PARTITION p3 VALUES LESS THAN (21) ENGINE = MyISAM) */
name s_name ord_pos s_ord_pos method s_method exp s_exp desc comment nodegroup tablespace_name
p0 undef 1 undef RANGE undef store_id undef 6 default undef
p1 undef 2 undef RANGE undef store_id undef 11 default undef
p2 undef 3 undef RANGE undef store_id undef 16 default undef
p3 undef 4 undef RANGE undef store_id undef 21 default undef

Sub Partitioning

15.2.5. サブパーティショニング

CREATE TABLE `ts` (
  `id` int(11) DEFAULT NULL,
  `purchased` date DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=sjis
/*!50100 PARTITION BY RANGE ( YEAR(purchased))
SUBPARTITION BY HASH ( TO_DAYS(purchased))
(PARTITION p0 VALUES LESS THAN (1990)
 (SUBPARTITION s0 ENGINE = MyISAM,
  SUBPARTITION s1 ENGINE = MyISAM),
 PARTITION p1 VALUES LESS THAN (2000)
 (SUBPARTITION s2 ENGINE = MyISAM,
  SUBPARTITION s3 ENGINE = MyISAM),
 PARTITION p2 VALUES LESS THAN MAXVALUE
 (SUBPARTITION s4 ENGINE = MyISAM,
  SUBPARTITION s5 ENGINE = MyISAM)) */
name s_name ord_pos s_ord_pos method s_method exp s_exp desc comment nodegroup tablespace_name
p0 s0 1 1 RANGE HASH YEAR(purchased) TO_DAYS(purchased) 1990 default undef
p0 s1 1 2 RANGE HASH YEAR(purchased) TO_DAYS(purchased) 1990 default undef
p1 s2 2 1 RANGE HASH YEAR(purchased) TO_DAYS(purchased) 2000 default undef
p1 s3 2 2 RANGE HASH YEAR(purchased) TO_DAYS(purchased) 2000 default undef
p2 s4 3 1 RANGE HASH YEAR(purchased) TO_DAYS(purchased) MAXVALUE default undef
p2 s5 3 2 RANGE HASH YEAR(purchased) TO_DAYS(purchased) MAXVALUE default undef