Table Partition很常在大表格上使用,
本文紀錄幾種常見的Partition方式與維護辦法.
一般使用partition時,不僅僅拆檔案,
還會有將各檔案存放不同裝置的情況.
所以也說明如何指定檔案位置.
事前檢查
伺服器是否支援Partition,
SHOW PLUGINS;
+-----------------------+----------+-----------------------+---------+---------+
| Name | Status | Type | Library | License |
+-----------------------+----------+-----------------------+---------+---------+
| partition | ACTIVE | STORAGE ENGINE | NULL | GPL |
+-----------------------+----------+-----------------------+---------+---------+
是否開啟innodb一表一檔,沒有的話,惠存到統一的ibdata內,這樣分表的目的大打折扣.
SHOW VARIABLES Like 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_file_per_table | ON |
+-----------------------+-------+
Partition 種類 -> 分表Key,必須存在所有的uniquer key和primary key內.
(沒有PK與UIX的表也可以做partition,只是在新增PK與UIX時,分表key必須在內)
Range
CREATE TABLE IF NOT EXISTS `rptable` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`op_dt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`op_type` int(10) unsigned NOT NULL,
`comment` varchar(50) NOT NULL,
PRIMARY KEY (`id`, `op_dt`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
partition by range (UNIX_TIMESTAMP(`op_dt`))
(
PARTITION start VALUES LESS THAN (0),
PARTITION from20170612 VALUES LESS THAN (UNIX_TIMESTAMP('2017-06-19 00:00:00')),
PARTITION from20170619 VALUES LESS THAN (UNIX_TIMESTAMP('2017-06-26 00:00:00')),
PARTITION from20170626 VALUES LESS THAN (UNIX_TIMESTAMP('2017-07-03 00:00:00')),
PARTITION future VALUES LESS THAN MAXVALUE
);
List
CREATE TABLE IF NOT EXISTS `lptable` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`op_dt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`op_type` int(10) unsigned NOT NULL,
`comment` varchar(50) NOT NULL,
PRIMARY KEY (`id`, `op_type`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
partition by list (`op_type`)
(
PARTITION OPS VALUES IN (0),
PARTITION OPI VALUES IN (1),
PARTITION OPU VALUES IN (2),
PARTITION OPD VALUES IN (3)
-- default 要10.2版以上才提供
-- ,PARTITION Other DEFAULT
);
Hash
CREATE TABLE IF NOT EXISTS `hptable` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`op_dt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`op_type` int(10) unsigned NOT NULL,
`comment` varchar(50) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
partition by HASH (`id`)
PARTITIONS 7;
Key -> 與HASH差別,在Key使用系統函式;Hash使用自定義
CREATE TABLE IF NOT EXISTS `kptable` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`op_dt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`op_type` int(10) unsigned NOT NULL,
`comment` varchar(50) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
partition by Key (`id`)
PARTITIONS 7;
Subpartition -> 僅可使用Key和Hash作為子分表的方法類型,參考 Create Table : Partition
使用key method作為sub key
CREATE TABLE IF NOT EXISTS `subptable` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`op_dt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`op_type` int(10) unsigned NOT NULL,
`comment` varchar(50) NOT NULL,
PRIMARY KEY (`id`, `op_dt`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
partition by range (UNIX_TIMESTAMP(`op_dt`))
subpartition by key (`id`)
subpartitions 2
(
PARTITION from20170612 VALUES LESS THAN (UNIX_TIMESTAMP('2017-06-19 00:00:00')),
PARTITION future VALUES LESS THAN MAXVALUE
);
或者使用hash method作為sub key
CREATE TABLE IF NOT EXISTS `subptable` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`op_dt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`op_type` int(10) unsigned NOT NULL,
`comment` varchar(50) NOT NULL,
PRIMARY KEY (`id`, `op_dt`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
partition by range (UNIX_TIMESTAMP(`op_dt`))
subpartition by hash (`id`)
subpartitions 2
(
PARTITION from20170612 VALUES LESS THAN (UNIX_TIMESTAMP('2017-06-19 00:00:00')),
PARTITION future VALUES LESS THAN MAXVALUE
);
指定檔案與路徑
在PARTITION p_name VALUES { IN | LESS THAN } (condition) DATA DIRECTORY = 'data_path' INDEX DIRECTORY = 'ix_path'
Partition 操作
新增 - 若表格最後一個partition為maxvalue,則不可加入新的partition
ALTER TABLE rptable ADD PARTITION (PARTITION from20170703 VALUES LESS THAN (UNIX_TIMESTAMP('2017-07-10 00:00:00')));
刪除
ALTER TABLE rptable DROP PARTITION from20170612;
拆分
ALTER TABLE rptable REORGANIZE PARTITION future
INTO (
PARTITION from20170703 VALUES LESS THAN (UNIX_TIMESTAMP('2017-07-10 00:00:00')),
PARTITION future VALUES LESS THAN MAXVALUE
)
合併 - 必須全部重構或者對最後的partitions整合,
ALTER TABLE rptable REORGANIZE PARTITION from20170619,from20170626 INTO (
PARTITION from20170626 VALUES LESS THAN (UNIX_TIMESTAMP('2017-07-03 00:00:00'))
);
出錯 => Error Code: 1520
Reorganize of range partitions cannot change total ranges except for last partition where it can extend the range
ALTER TABLE rptable REORGANIZE PARTITION from20170703,future INTO (
PARTITION future VALUES LESS THAN MAXVALUE
);
成功
新增SP與Event,自動化新增即將使用的Partiton. 以下為SP範例
DELIMITER $$
CREATE PROCEDURE `SP_auto_add_partition`()
BEGIN
DECLARE p_name VARCHAR(50);
SET p_name := CONCAT('from', REPLACE(DATE(DATE_ADD(NOW(), INTERVAL 7 DAY)),'-', ''));
SET @run_sql := CONCAT('ALTER TABLE `rptable` REORGANIZE PARTITION future INTO ( PARTITION ', p_name);
SET @run_sql := CONCAT(@run_sql, ' VALUES LESS THAN (UNIX_TIMESTAMP(''', DATE(DATE_ADD(NOW(), INTERVAL 14 DAY)), ' 00:00'')), ');
SET @run_sql := CONCAT(@run_sql, 'PARTITION future VALUES LESS THAN MAXVALUE )');
PREPARE stmt FROM @run_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;
Partition 維護
Analyze
Alter Table tbl_name Analyze Partition p_name;
Rebuild
Alter Table tbl_name Rebuild Partition p_name;
Optimize - 若engine為InnoDB,無法對單一partition執行,會對整個表執行analyze和rebuild.
Alter Table tbl_name Optimize Partition p_name;
Repair
Alter Table tbl_name Repair Partition p_name;
Check
Alter Table tbl_name Check Partition p_name;
正常情況下會進行Optimize或者是 Rebuild + Analyze的日常維護.
參考
MySQL 資料表分區(partition) (頗多語法在MariaDB 10.X 並不適用)
留言列表
