MySQL Partitioning 分區

限制

Restrictions and Limitations on Partitioning
分區資料表就不支援 Foreign key

MySQL 分區可以設定更改原先資料儲存結果。

以 MYISAM 引擎為例，原本一個 A 資料表在硬碟會存在

A.frm
A.MYD
A.MYI

三個檔案，無論資料筆數多寡都放在同一檔案內，這時資料儲存限制 == 作業系統單一檔案大小限制。在目前主流檔案系統之下，單一檔案大小限制在 4G ~ 16TByte 之間。

資料表太大對資料庫不是好事，有可能只為了讀取 10Byte 的資料，卻將整個資料表都讀取一遍，如果可能還是需要控制資料表的實體儲存容量，達到增進效能以及方便管理的目的。

參考

CREATE TABLE part_tab ( 
    c1 int default NULL,
    c2 varchar(30) default NULL,
    c3 date default NULL
) engine=myisam
PARTITION BY RANGE (year(c3)) (
    PARTITION p0 VALUES LESS THAN (1995),
    PARTITION p1 VALUES LESS THAN (1996),
    PARTITION p2 VALUES LESS THAN (1997),
    PARTITION p3 VALUES LESS THAN (1998),
    PARTITION p4 VALUES LESS THAN (1999),
    PARTITION p5 VALUES LESS THAN (2000),
    PARTITION p6 VALUES LESS THAN (2001),
    PARTITION p7 VALUES LESS THAN (2002),
    PARTITION p8 VALUES LESS THAN (2003),
    PARTITION p9 VALUES LESS THAN (2004),
    PARTITION p10 VALUES LESS THAN (2010),
    PARTITION p11 VALUES LESS THAN MAXVALUE
);

CREATE TABLE part_tab (

c1 int default NULL,

c2 varchar(30) default NULL,

c3 date default NULL

) engine=myisam

PARTITION BY RANGE (year(c3)) (

PARTITION p0 VALUES LESS THAN (1995),

PARTITION p1 VALUES LESS THAN (1996),

PARTITION p2 VALUES LESS THAN (1997),

PARTITION p3 VALUES LESS THAN (1998),

PARTITION p4 VALUES LESS THAN (1999),

PARTITION p5 VALUES LESS THAN (2000),

PARTITION p6 VALUES LESS THAN (2001),

PARTITION p7 VALUES LESS THAN (2002),

PARTITION p8 VALUES LESS THAN (2003),

PARTITION p9 VALUES LESS THAN (2004),

PARTITION p10 VALUES LESS THAN (2010),

PARTITION p11 VALUES LESS THAN MAXVALUE

);

以時間作為分區依據

以一個有 AUTO_INCREMENT 及 CURRENT_TIMESTAMP 的資料表為例，

關鍵是「必須把區分時間的欄位包含到 Primary Key 內」

CREATE TABLE IF NOT EXISTS `NODE1p` (
  `id` int(255) NOT NULL AUTO_INCREMENT,
  `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `V0` float NOT NULL DEFAULT '0',
  `V1` float NOT NULL DEFAULT '0',
  `V2` float NOT NULL DEFAULT '0',
  `V3` float NOT NULL DEFAULT '0',
  `V4` float NOT NULL DEFAULT '0',
  `V5` float NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`, `time`)
) ENGINE=myisam DEFAULT CHARSET=utf8 AUTO_INCREMENT=1
PARTITION BY RANGE (UNIX_TIMESTAMP(`time`)) (
    PARTITION p2014 VALUES LESS THAN (UNIX_TIMESTAMP('2015-01-01 00:00:00')),
    PARTITION p2015 VALUES LESS THAN (UNIX_TIMESTAMP('2016-01-01 00:00:00')),
    PARTITION pNOW VALUES LESS THAN MAXVALUE
);

CREATE TABLE IF NOT EXISTS `NODE1p` (

`id` int(255) NOT NULL AUTO_INCREMENT,

`time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,

`V0` float NOT NULL DEFAULT '0',

`V1` float NOT NULL DEFAULT '0',

`V2` float NOT NULL DEFAULT '0',

`V3` float NOT NULL DEFAULT '0',

`V4` float NOT NULL DEFAULT '0',

`V5` float NOT NULL DEFAULT '0',

PRIMARY KEY (`id`, `time`)

) ENGINE=myisam DEFAULT CHARSET=utf8 AUTO_INCREMENT=1

PARTITION BY RANGE (UNIX_TIMESTAMP(`time`)) (

PARTITION p2014 VALUES LESS THAN (UNIX_TIMESTAMP('2015-01-01 00:00:00')),

PARTITION p2015 VALUES LESS THAN (UNIX_TIMESTAMP('2016-01-01 00:00:00')),

PARTITION pNOW VALUES LESS THAN MAXVALUE

);

INSERT 新的分區資料表 SELECT * FROM 原資料表;

1	INSERT 新的分區資料表 SELECT * FROM 原資料表;

一個將無分區資料表修改為具分區功能範例

建立一個具有分區功能資料表，必須和原資料表資料結構相同，準備做替換用
將原資料表資料複製到此分區資料表，使用的是 INSERT SELECT 語法
刪除原資料表
將分區資料表名稱更換為原資料表名稱

CREATE TABLE IF NOT EXISTS `NODE1p` (
  `id` int(255) NOT NULL AUTO_INCREMENT,
  `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `V0` float NOT NULL DEFAULT '0',
  `V1` float NOT NULL DEFAULT '0',
  `V2` float NOT NULL DEFAULT '0',
  `V3` float NOT NULL DEFAULT '0',
  `V4` float NOT NULL DEFAULT '0',
  `V5` float NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`, `time`)
) ENGINE=myisam DEFAULT CHARSET=utf8 AUTO_INCREMENT=1
PARTITION BY RANGE (UNIX_TIMESTAMP(`time`)) (
    PARTITION p2014 VALUES LESS THAN (UNIX_TIMESTAMP('2015-01-01 00:00:00')),
    PARTITION p2015 VALUES LESS THAN (UNIX_TIMESTAMP('2016-01-01 00:00:00')),
    PARTITION p2016 VALUES LESS THAN (UNIX_TIMESTAMP('2017-01-01 00:00:00')),
    PARTITION p2017 VALUES LESS THAN (UNIX_TIMESTAMP('2018-01-01 00:00:00')),
    PARTITION p2018 VALUES LESS THAN (UNIX_TIMESTAMP('2019-01-01 00:00:00')),
    PARTITION p2019 VALUES LESS THAN (UNIX_TIMESTAMP('2020-01-01 00:00:00')),
    PARTITION p2020 VALUES LESS THAN (UNIX_TIMESTAMP('2021-01-01 00:00:00')),
    PARTITION p2021 VALUES LESS THAN (UNIX_TIMESTAMP('2022-01-01 00:00:00')),
    PARTITION p2022 VALUES LESS THAN (UNIX_TIMESTAMP('2023-01-01 00:00:00')),
    PARTITION p2023 VALUES LESS THAN (UNIX_TIMESTAMP('2024-01-01 00:00:00')),
    PARTITION pNOW VALUES LESS THAN MAXVALUE
);

INSERT `NODE1p` SELECT * FROM `NODE1`;
DROP TABLE `NODE1`;
RENAME TABLE `NODE1p` TO `NODE1` ;

CREATE TABLE IF NOT EXISTS `NODE1p` (

`id` int(255) NOT NULL AUTO_INCREMENT,

`time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,

`V0` float NOT NULL DEFAULT '0',

`V1` float NOT NULL DEFAULT '0',

`V2` float NOT NULL DEFAULT '0',

`V3` float NOT NULL DEFAULT '0',

`V4` float NOT NULL DEFAULT '0',

`V5` float NOT NULL DEFAULT '0',

PRIMARY KEY (`id`, `time`)

) ENGINE=myisam DEFAULT CHARSET=utf8 AUTO_INCREMENT=1

PARTITION BY RANGE (UNIX_TIMESTAMP(`time`)) (

PARTITION p2014 VALUES LESS THAN (UNIX_TIMESTAMP('2015-01-01 00:00:00')),

PARTITION p2015 VALUES LESS THAN (UNIX_TIMESTAMP('2016-01-01 00:00:00')),

PARTITION p2016 VALUES LESS THAN (UNIX_TIMESTAMP('2017-01-01 00:00:00')),

PARTITION p2017 VALUES LESS THAN (UNIX_TIMESTAMP('2018-01-01 00:00:00')),

PARTITION p2018 VALUES LESS THAN (UNIX_TIMESTAMP('2019-01-01 00:00:00')),

PARTITION p2019 VALUES LESS THAN (UNIX_TIMESTAMP('2020-01-01 00:00:00')),

PARTITION p2020 VALUES LESS THAN (UNIX_TIMESTAMP('2021-01-01 00:00:00')),

PARTITION p2021 VALUES LESS THAN (UNIX_TIMESTAMP('2022-01-01 00:00:00')),

PARTITION p2022 VALUES LESS THAN (UNIX_TIMESTAMP('2023-01-01 00:00:00')),

PARTITION p2023 VALUES LESS THAN (UNIX_TIMESTAMP('2024-01-01 00:00:00')),

PARTITION pNOW VALUES LESS THAN MAXVALUE

);

INSERT `NODE1p` SELECT * FROM `NODE1`;

DROP TABLE `NODE1`;

RENAME TABLE `NODE1p` TO `NODE1` ;

顯示資料表分區資訊

使用的是顯示建立資料表資訊技巧

SHOW CREATE TABLE

1	SHOW CREATE TABLE

一個分區資料表建立後修改範例

CREATE TABLE `NODE2p` (
 `id` int(255) NOT NULL AUTO_INCREMENT,
 `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 `V0` float NOT NULL DEFAULT '0',
 `V1` float NOT NULL DEFAULT '0',
 `V2` float NOT NULL DEFAULT '0',
 `V3` float NOT NULL DEFAULT '0',
 `V4` float NOT NULL DEFAULT '0',
 `V5` float NOT NULL DEFAULT '0',
 PRIMARY KEY (`id`,`time`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (UNIX_TIMESTAMP(`time`))
(PARTITION p2014 VALUES LESS THAN (1388505600) ENGINE = MyISAM,
PARTITION p2015 VALUES LESS THAN (1420041600) ENGINE = MyISAM,
PARTITION pNOW VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */

CREATE TABLE `NODE2p` (

`id` int(255) NOT NULL AUTO_INCREMENT,

`time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,

`V0` float NOT NULL DEFAULT '0',

`V1` float NOT NULL DEFAULT '0',

`V2` float NOT NULL DEFAULT '0',

`V3` float NOT NULL DEFAULT '0',

`V4` float NOT NULL DEFAULT '0',

`V5` float NOT NULL DEFAULT '0',

PRIMARY KEY (`id`,`time`)

) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8

/*!50100 PARTITION BY RANGE (UNIX_TIMESTAMP(`time`))

(PARTITION p2014 VALUES LESS THAN (1388505600) ENGINE = MyISAM,

PARTITION p2015 VALUES LESS THAN (1420041600) ENGINE = MyISAM,

PARTITION pNOW VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */

ALTER TABLE NODE2p REORGANIZE PARTITION pNOW INTO (
    PARTITION p201601 VALUES LESS THAN (UNIX_TIMESTAMP('2016-02-01 00:00:00')),
    PARTITION p201602 VALUES LESS THAN (UNIX_TIMESTAMP('2016-03-01 00:00:00')),
    PARTITION pNOW VALUES LESS THAN MAXVALUE
);

ALTER TABLE NODE2p REORGANIZE PARTITION pNOW INTO (

PARTITION p201601 VALUES LESS THAN (UNIX_TIMESTAMP('2016-02-01 00:00:00')),

PARTITION p201602 VALUES LESS THAN (UNIX_TIMESTAMP('2016-03-01 00:00:00')),

PARTITION pNOW VALUES LESS THAN MAXVALUE

);

分區區分到年、月

MySQL table partition by month

CREATE TABLE IF NOT EXISTS `J` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主鍵',
  `Create_Time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '建立時間',
  `AuctionDate` date NOT NULL,
  `AuctionNumber` int(11) NOT NULL,
  PRIMARY KEY (`id`, `AuctionDate`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1
PARTITION BY RANGE((YEAR(`AuctionDate`) * 100) + MONTH(`AuctionDate`))(
    PARTITION p199501 VALUES LESS THAN (199501),
    PARTITION p199502 VALUES LESS THAN (199502)
);

CREATE TABLE IF NOT EXISTS `J` (

`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主鍵',

`Create_Time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '建立時間',

`AuctionDate` date NOT NULL,

`AuctionNumber` int(11) NOT NULL,

PRIMARY KEY (`id`, `AuctionDate`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1

PARTITION BY RANGE((YEAR(`AuctionDate`) * 100) + MONTH(`AuctionDate`))(

PARTITION p199501 VALUES LESS THAN (199501),

PARTITION p199502 VALUES LESS THAN (199502)

);

順便說一個情況，第一個設定是 199501 ，代表 1995年1月以前的資料存在此，而199502 則只存放 1995年 2 月的資料，因為沒有 MAXVALUE 所以超過的日期會新增失敗

InnoDB 如何分區區分檔案

vi /etc/my.cnf

[mysqld]
innodb_file_per_table=1

vi /etc/my.cnf

[mysqld]

innodb_file_per_table=1

重新啟動資料庫

使用 HASH

HASH Partitioning

CREATE TABLE IF NOT EXISTS `Data2` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主鍵',
  `Create_Time` datetime NOT NULL COMMENT '建立時間',
  `Device_id` int(11) NOT NULL,
  `Key` enum('Temperature1','Humidity1','Watt1','WalkStep1') NOT NULL,
  `Value` decimal(24,12) NOT NULL,
  `is_Del` enum('Y','N') CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT 'N' COMMENT '是否刪除',
  UNIQUE KEY `id` (`id`,`Create_Time`),
  KEY `Device_id` (`Device_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `Data2` (

`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主鍵',

`Create_Time` datetime NOT NULL COMMENT '建立時間',

`Device_id` int(11) NOT NULL,

`Key` enum('Temperature1','Humidity1','Watt1','WalkStep1') NOT NULL,

`Value` decimal(24,12) NOT NULL,

`is_Del` enum('Y','N') CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT 'N' COMMENT '是否刪除',

UNIQUE KEY `id` (`id`,`Create_Time`),

KEY `Device_id` (`Device_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

使用 Create_Time 轉天數後 hash

ALTER TABLE `Data2` PARTITION BY hash (TO_DAYS(Create_Time)) partitions 31;

1	ALTER TABLE `Data2` PARTITION BY hash (TO_DAYS(Create_Time)) partitions 31;

SELECT TO_DAYS( '0001-01-01' );
-> 366

1 2	SELECT TO_DAYS( '0001-01-01' ); -> 366

從插入多筆 2018/06/07 資料來觀察 HASH 儲存資料邏輯

SELECT TO_DAYS('2018-06-07');
-> 737217

SELECT MOD(737217,31);
-> 6

SELECT TO_DAYS('2018-06-07');

-> 737217

SELECT MOD(737217,31);

-> 6

分區刪除合併檔案變化

分區會將檔案根據條件分開，刪除分區檔案就會合併？

理論上是如此，實際測試亦是如此

查詢時是否有效套用到分區？

使用 EXPLAIN PARTITIONS 可以查詢取用分區檔案的狀態

EXPLAIN PARTITIONS SELECT * 
FROM  `Data2` 
WHERE Create_Time =  '2018-06-07'

EXPLAIN PARTITIONS SELECT *

FROM `Data2`

WHERE Create_Time = '2018-06-07'

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	Data2	p6	ALL	NULL	NULL	NULL	NULL	2738	Using where

如果查訊跨到多日就會查詢到多個或是全部的分區檔案，如此就失去分區作用

4,014 total views, 1 views today

限制

以時間作為分區依據

分區區分到年、月

InnoDB 如何分區區分檔案

使用 HASH

分區刪除合併檔案變化

查詢時是否有效套用到分區？

Related Post

發佈留言

MySQL Partitioning 分區

限制

以時間作為分區依據

分區區分到年、月

InnoDB 如何分區區分檔案

使用 HASH

分區刪除合併檔案變化

查詢時是否有效套用到分區？

Related Post

MySQL - 錯誤代碼及解決思路

MySQL 5.7 - 忘記 root 密碼

MySQL - 排序

發佈留言