1、分區表含義
分區表定義指根據可以設置為任意大小的規則,跨文件系統分配單個表的多個部分。實際上,表的不同部分在不同的位置被存儲為單獨的表。用戶所選擇的、實現數據分割的規則被稱為分區函數,這在MySQL中它可以是模數,或者是簡單的匹配一個連續的數值區間或數值列表,或者是一個內部HASH函數,或一個線性HASH函數。
分表與分區的區別在于:分區從邏輯上來講只有一張表,而分表則是將一張表分解成多張表
2、分區表優點
1)分區表更容易維護。對于那些已經失去保存意義的數據,通??梢酝ㄟ^刪除與那些數據有關的分區,很容易地刪除那些數據。相反地,在某些情況下,添加新數據的過程又可以通過為那些新數據專門增加一個新的分區,來很方便地實現。
2)一些查詢可以得到極大的優化,這主要是借助于滿足一個給定WHERE語句的數據可以只保存在一個或多個分區內,這樣在查找時就不用查找其他剩余的分區。因為分區可以在創建了分區表后進行修改,所以在第一次配置分區方案時還不曾這么做時,可以重新組織數據,來提高那些常用查詢的效率。
3)優化查詢。涉及到例如SUM()和COUNT(),可以在多個分區上并行處理,最終結果只需通過總計所有分區得到的結果。
4)通過跨多個磁盤來分散數據查詢,來獲得更大的查詢吞吐量。
3、分區表限制
1)一個表最多只能有1024個分區;
2) MySQL5.1中,分區表達式必須是整數,或者返回整數的表達式。在MySQL5.5中提供了非整數表達式分區的支持;
3)如果分區字段中有主鍵或者唯一索引的列,那么多有主鍵列和唯一索引列都必須包含進來。即:分區字段要么不包含主鍵或者索引列,要么包含全部主鍵和索引列;
4)分區表中無法使用外鍵約束;
5)MySQL的分區適用于一個表的所有數據和索引,不能只對表數據分區而不對索引分區,也不能只對索引分區而不對表分區,也不能只對表的一部分數據分區。
6)分區鍵必須是INT類型,或者通過表達式返回INT類型,可以為NULL。唯一的例外是當分區類型為KEY分區的時候,可以使用其他類型的列作為分區鍵(BLOB or TEXT 列除外)
7)如果表中有主鍵和唯一索引,按主鍵字段進行分區時,唯一索引列應該包含分區鍵。
8)目前mysql不支持空間類型和臨時表類型進行分區。不支持全文索引。
9)對象限制(分區表達式不能出現Stored functions, stored procedures, UDFs, orplugins,Declared variables or user variables.)
10)運算限制(支持加減乘等運算出現在分區表達式,但是運算后的結果必須是一個INT或者NULL。支持DIV,不支持/,|, , ^, , >>, and ~ 不允許出現在分區表達式中)
11)sql_mode限制(官方強烈建議你在創建分區表后,永遠別改變mysql的sql_mode。因為在不同的模式下,某些函數或者運算返回的結果可能會不一樣)
12)不支持query_cache和INSERT DELAYED
13)分區鍵不能是一個子查詢(即使是子查詢返回的是int值或者null.)
14)子分區限制(只有RANG和LIST分區能進行子分區。HASH和KEY分區不能進行子分區并且子分區必須是HASH 或 KEY類型)
4、分區類型
1)水平分區(根據列屬性按行分)
如:一個包含十年發票記錄的表可以被分區為十個不同的分區,每個分區包含的是其中一年的記錄。
水平分區的幾種模式:
* Range(范圍):這種模式允許DBA將數據劃分不同范圍。
如:可以將一個表通過年份劃分成三個分區,80年代(1980's)的數據,90年代(1990's)的數據以及任何在2000年(包括2000年)后的數據。
* Hash(哈希):這中模式允許DBA通過對表的一個或多個列的Hash Key進行計算,最后通過這個Hash碼不同數值對應的數據區域進行分區。
如:可以建立一個對表主鍵進行分區的表。
* Key(鍵值):上面Hash模式的一種延伸,這里的Hash Key是MySQL系統產生的。
* List(預定義列表):這種模式允許系統通過DBA定義的列表的值所對應的行數據進行分割。例如:DBA建立了一個橫跨三個分區的表,分別根據2004年2005年和2006年值所對應的數據。
* Columns分區是對range,list分區的補充,彌補了后兩者只支持整型數分區(或者通過轉換為整型數),使得支持數據類型增加很多(所有整數類型,日期時間類型,字符類型),還支持多列分區。
注:在多列分區表上插入數據時,采用元組的比較,即多列排序,先根據field1排序,再根據field2排序,根據排序結果來來分區存儲數據。
* Composite(復合模式):以上模式的組合使用。
如:在初始化已經進行了Range范圍分區的表上,可以對其中一個分區再進行hash哈希分區。
垂直分區(按列分):
如:一個包含了大text和BLOB列的表,這些text和BLOB列又不經常被訪問,可以把這些不經常使用的text和BLOB劃分到另一個分區,在保證它們數據相關性的同時還能提高訪問速度。
注意:子分區(關鍵字subparttition):使用RANGE或LIST分區可以再次分割形成子分區,子分區可以是HASH分區或者KEY分區。建議在多磁盤上使用。
查看是否有支持Partition分區表
mysql> SHOW PLUGINS ;
+----------------------------+----------+--------------------+---------+---------+
| Name | Status | Type | Library | License |
+----------------------------+----------+--------------------+---------+---------+
| partition | ACTIVE | STORAGE ENGINE | NULL | GPL |
+----------------------------+----------+--------------------+---------+---------+
或使用
mysql> SELECT PLUGIN_NAME as Name, PLUGIN_VERSION as Version, PLUGIN_STATUS as Status
-> FROM INFORMATION_SCHEMA.PLUGINS
-> WHERE PLUGIN_TYPE='STORAGE ENGINE';
注意:MySQL 5.6.1 之前的版本,可以下命令查看 have_partitioning 參數,新的版本已移除該參數。
mysql> SHOW VARIABLES LIKE '%partition%';
5、實戰常用分區表幾種模式
1)使用RANGE分區模式
####創建測試表t1,并插入接近400萬行數據,再沒有分區的情況下,對查詢某一條件耗時
mysql> CREATE TABLE `t1` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '表主鍵',
`pid` int(10) unsigned NOT NULL COMMENT '產品ID',
`price` decimal(15,2) NOT NULL COMMENT '單價',
`num` int(11) NOT NULL COMMENT '購買數量',
`uid` int(10) unsigned NOT NULL COMMENT '客戶ID',
`atime` datetime NOT NULL COMMENT '下單時間',
`utime` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '修改時間',
`isdel` tinyint(4) NOT NULL DEFAULT '0' COMMENT '軟刪除標識',
PRIMARY KEY (`id`,`atime`)
)
INSERT INTO t1(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89757,CURRENT_TIMESTAMP());
INSERT INTO t1(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89757,'2016-05-01 00:00:00');
INSERT INTO t1(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89757,'2017-05-01 00:00:00');
INSERT INTO t1(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89757,'2018-05-01 00:00:00');
INSERT INTO t1(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89756,'2015-05-01 00:00:00');
INSERT INTO t1(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89756,'2016-05-01 00:00:00');
INSERT INTO t1(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89756,'2017-05-01 00:00:00');
INSERT INTO t1(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89756,'2018-05-01 00:00:00');
/**********************************主從復制大量數據******************************/
mysql> INSERT INTO `t1`(`pid`,`price`,`num`,`uid`,`atime`) SELECT `pid`,`price`,`num`,`uid`,`atime` FROM `t1`;
mysql> SELECT * FROM `t1` WHERE `uid`=89757 AND `atime` CURRENT_TIMESTAMP();
1048576 rows in set (5.62 sec) #沒有分區表情況耗時5.62s
如果是針對已有的表進行表分區,可以使用ALTER TABLE來進行更改表為分區表,這個操作會創建一個分區表,然后自動進行數據copy然后刪除原表。
注: 這種會使服務器資源消耗比較大(400多萬數據要1分多鐘)
mysql> ALTER TABLE t1 PARTITION BY RANGE (YEAR(atime))
-> (
-> PARTITION p0 VALUES LESS THAN (2016),
-> PARTITION p1 VALUES LESS THAN (2017),
-> PARTITION p2 VALUES LESS THAN (2018),
-> PARTITION p3 VALUES LESS THAN MAXVALUE );
Query OK, 4194304 rows affected (1 min 8.32 sec)
mysql> EXPLAIN PARTITIONS SELECT * FROM `t1`; #查看分區情況
+----+-------------+-------+-------------+------+---------------+------+---------+------+---------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+-------------+------+---------------+------+---------+------+---------+----------+-------+
| 1 | SIMPLE | t1 | p0,p1,p2,p3 | ALL | NULL | NULL | NULL | NULL | 4180974 | 100.00 | NULL |
+----+-------------+-------+-------------+------+---------------+------+---------+------+---------+----------+-------+
1 row in set, 2 warnings (0.00 sec)
同樣用上面的查詢測試結果
mysql> SELECT * FROM `t1` WHERE `uid`=89757 AND `atime` CURRENT_TIMESTAMP();
1048576 rows in set (4.46 sec) #與上面沒有分區查詢執行的時間相比少了接近1s
mysql> EXPLAIN PARTITIONS SELECT * FROM `t1` WHERE `uid`=89757 AND `atime` CURRENT_TIMESTAMP(); #查看查詢使用的分區情況
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| 1 | SIMPLE | t1 | p0,p1,p2 | ALL | NULL | NULL | NULL | NULL | 3135804 | 3.33 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
1 row in set, 2 warnings (0.00 sec)
同時也要注意,進行表分區以后,mysql存放的數據文件夾中該表的存放文件也被拆分為多個
-rw-r----- 1 mysql mysql 8.7K 2月 14 14:49 t1.frm
-rw-r----- 1 mysql mysql 36M 2月 14 14:50 t1#P#p0.ibd
-rw-r----- 1 mysql mysql 64M 2月 14 14:50 t1#P#p1.ibd
-rw-r----- 1 mysql mysql 92M 2月 14 14:50 t1#P#p2.ibd
-rw-r----- 1 mysql mysql 64M 2月 14 14:50 t1#P#p3.ibd
實際生產環境中,大多是采用另外一種方式:新建一個和原來表一樣的分區表,然后把數據從原表導出,接著導入新表,最后建立普通索引。
mysql> CREATE TABLE `t2` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '表主鍵',
`pid` int(10) unsigned NOT NULL COMMENT '產品ID',
`price` decimal(15,2) NOT NULL COMMENT '單價',
`num` int(11) NOT NULL COMMENT '購買數量',
`uid` int(10) unsigned NOT NULL COMMENT '客戶ID',
`atime` datetime NOT NULL COMMENT '下單時間',
`utime` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '修改時間',
`isdel` tinyint(4) NOT NULL DEFAULT '0' COMMENT '軟刪除標識',
PRIMARY KEY (`id`,`atime`)
)
PARTITION BY RANGE COLUMNS(atime) (
PARTITION p0 VALUES LESS THAN ('2016-01-01'),
PARTITION p1 VALUES LESS THAN ('2016-02-01'),
PARTITION p2 VALUES LESS THAN ('2016-03-01'),
PARTITION p3 VALUES LESS THAN ('2016-04-01'),
PARTITION p4 VALUES LESS THAN ('2016-05-01'),
PARTITION p5 VALUES LESS THAN ('2016-06-01'),
PARTITION p6 VALUES LESS THAN ('2016-07-01'),
PARTITION p7 VALUES LESS THAN ('2016-08-01'),
PARTITION p8 VALUES LESS THAN ('2016-09-01'),
PARTITION p9 VALUES LESS THAN ('2016-10-01'),
PARTITION p10 VALUES LESS THAN ('2016-11-01'),
PARTITION p11 VALUES LESS THAN ('2016-12-01'),
PARTITION p12 VALUES LESS THAN ('2017-01-01'),
PARTITION p13 VALUES LESS THAN ('2017-02-01'),
PARTITION p14 VALUES LESS THAN ('2017-03-01'),
PARTITION p15 VALUES LESS THAN ('2017-04-01'),
PARTITION p16 VALUES LESS THAN ('2017-05-01'),
PARTITION p17 VALUES LESS THAN ('2017-06-01'),
PARTITION p18 VALUES LESS THAN ('2017-07-01'),
PARTITION p19 VALUES LESS THAN ('2017-08-01'),
PARTITION p20 VALUES LESS THAN ('2017-09-01'),
PARTITION p21 VALUES LESS THAN ('2017-10-01'),
PARTITION p22 VALUES LESS THAN ('2017-11-01'),
PARTITION p23 VALUES LESS THAN ('2017-12-01'),
PARTITION p24 VALUES LESS THAN ('2018-01-01'),
PARTITION p25 VALUES LESS THAN ('2018-02-01'),
PARTITION p26 VALUES LESS THAN ('2018-03-01'),
PARTITION p27 VALUES LESS THAN ('2018-04-01'),
PARTITION p28 VALUES LESS THAN ('2018-05-01'),
PARTITION p29 VALUES LESS THAN ('2018-06-01'),
PARTITION p30 VALUES LESS THAN ('2018-07-01'),
PARTITION p31 VALUES LESS THAN ('2018-08-01'),
PARTITION p32 VALUES LESS THAN ('2018-09-01'),
PARTITION p33 VALUES LESS THAN ('2018-10-01'),
PARTITION p34 VALUES LESS THAN ('2018-11-01'),
PARTITION p35 VALUES LESS THAN ('2018-12-01'),
PARTITION p36 VALUES LESS THAN MAXVALUE
);
注:表主鍵只有id,而分區字段是atime, 這里主鍵要修改為 id,stsdate 聯合主鍵,分區表要求分區字段要是主鍵或者是主鍵的一部分!!!
mysql> EXPLAIN PARTITIONS SELECT * FROM `t2`\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t2
partitions: p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12,p13,p14,p15,p16,p17,p18,p19,p20,p21,p22,p23,p24,p25,p26,p27,p28,p29,p30,p31,p32,p33,p34,p35,p36
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1
filtered: 100.00
Extra: NULL
1 row in set, 2 warnings (0.00 sec)
*******************************************插入數據*************************************************
INSERT INTO `t2`(`pid`,`price`,`num`,`uid`,`atime`) SELECT `pid`,`price`,`num`,`uid`,`atime` FROM `t1`;
Query OK, 4194304 rows affected (1 min 18.54 sec)
Records: 4194304 Duplicates: 0 Warnings: 0
或采用導出數據再導入數據,可再添加索引
mysqldump -u dbname -p --no-create-info dbname t2 > t2.sq
修改表名,導入數據,測試下ok,刪除原來的表。
2)使用LIST分區模式(如果原表存在主鍵強烈創建新表時,把原主鍵和要分區字段作為聯合主鍵一并創建)
mysql> CREATE TABLE `tb01` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '表主鍵',
`pid` int(10) unsigned NOT NULL COMMENT '產品ID',
`price` decimal(15,2) NOT NULL COMMENT '單價',
`num` int(11) NOT NULL COMMENT '購買數量',
`uid` int(10) unsigned NOT NULL COMMENT '客戶ID',
`atime` datetime NOT NULL COMMENT '下單時間',
`utime` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '修改時間',
`isdel` tinyint(4) NOT NULL DEFAULT '0' COMMENT '軟刪除標識',
PRIMARY KEY (`id`,`num`)
);
*****************************插入測試數據******************************************************
INSERT INTO `tb01`(`pid`,`price`,`num`,`uid`,`atime`) SELECT `pid`,`price`,`num`,`uid`,`atime` FROM `tb`;
Query OK, 3145728 rows affected (46.26 sec)
Records: 3145728 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE tb01 PARTITION BY LIST(num)
(
PARTITION pl01 VALUES IN (1,3),
PARTITION pl02 VALUES IN (2,4),
PARTITION pl03 VALUES IN (5,7),
PARTITION pl04 VALUES IN (6,8),
PARTITION pl05 VALUES IN (9,10)
);
Query OK, 3145728 rows affected (48.86 sec)
Records: 3145728 Duplicates: 0 Warnings: 0
存放mysql數據文件中生成,以下文件
-rw-r----- 1 mysql mysql 8.7K 2月 15 11:35 tb01.frm
-rw-r----- 1 mysql mysql 56M 2月 15 11:36 tb01#P#pl01.ibd
-rw-r----- 1 mysql mysql 32M 2月 15 11:36 tb01#P#pl02.ibd
-rw-r----- 1 mysql mysql 36M 2月 15 11:36 tb01#P#pl03.ibd
-rw-r----- 1 mysql mysql 36M 2月 15 11:36 tb01#P#pl04.ibd
-rw-r----- 1 mysql mysql 52M 2月 15 11:36 tb01#P#pl05.ibd
mysql> EXPLAIN PARTITIONS SELECT * FROM `tb01`;
+----+-------------+-------+--------------------------+------+---------------+------+---------+------+---------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+--------------------------+------+---------------+------+---------+------+---------+----------+-------+
| 1 | SIMPLE | tb01 | pl01,pl02,pl03,pl04,pl05 | ALL | NULL | NULL | NULL | NULL | 3136392 | 100.00 | NULL |
+----+-------------+-------+--------------------------+------+---------------+------+---------+------+---------+----------+-------+
1 row in set, 2 warnings (0.00 sec)
3)COLUMNS分區
創建多列分區表tb02,這里兩列都不是聯合主鍵
mysql> CREATE TABLE tb02(
-> a int not null,
-> b int not null
-> )
-> PARTITION BY RANGE COLUMNS(a,b)(
-> partition p0 values less than(0,10),
-> partition p1 values less than(10,20),
-> partition p2 values less than(10,30),
-> partition p3 values less than(maxvalue,maxvalue)
-> );
mysql> EXPLAIN PARTITIONS SELECT * FROM `tb02`; #查看
+----+-------------+-------+-------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+-------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | tb02 | p0,p1,p2,p3 | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL |
+----+-------------+-------+-------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 2 warnings (0.00 sec)
mysql> insert into tb02 values (11,13); #手工插入測試數據
Query OK, 1 row affected (0.01 sec)
mysql> select PARTITION_NAME,PARTITION_EXPRESSION,TABLE_ROWS from information_schema.partitions where table_schema=schema() and table_name='tb02';
+----------------+----------------------+------------+
| PARTITION_NAME | PARTITION_EXPRESSION | TABLE_ROWS |
+----------------+----------------------+------------+
| p0 | `a`,`b` | 0 |
| p1 | `a`,`b` | 0 |
| p2 | `a`,`b` | 0 |
| p3 | `a`,`b` | 1 |
+----------------+----------------------+------------+
4 rows in set (0.03 sec)
4)Hase分區
HASH主要是為了讓數據在設定個數的分區中盡可能分布平均,執行哈希分區時,mysql會對分區鍵執行哈希函數,以確定數據放在哪個分區中。HASH分區分為常規HASH分區和線性HASH分區,前者使用取模算法,后者使用線性2的冪的運算規則。
CREATE TABLE `tb03` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '表主鍵',
`pid` int(10) unsigned NOT NULL COMMENT '產品ID',
`price` decimal(15,2) NOT NULL COMMENT '單價',
`num` int(11) NOT NULL COMMENT '購買數量',
`uid` int(10) unsigned NOT NULL COMMENT '客戶ID',
`atime` datetime NOT NULL COMMENT '下單時間',
`utime` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '修改時間',
`isdel` tinyint(4) NOT NULL DEFAULT '0' COMMENT '軟刪除標識',
PRIMARY KEY (`id`)
)
PARTITION BY HASH(id) partitions 4;
插入2行數據:
INSERT INTO tb03(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89757,CURRENT_TIMESTAMP());
INSERT INTO tb03(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89757,CURRENT_TIMESTAMP());
mysql> explain partitions select * from tb03 where id=1;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | tb03 | p1 | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 2 warnings (0.00 sec)
mysql> explain partitions select * from tb03 where id=2;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | tb03 | p2 | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 2 warnings (0.00 sec)
注意:HASH分區雖然盡可能讓數據平均地分布在每個分區上,提高了查詢效率,但增加了分區管理的代價,比如以前有5個分區,現在要加上一個分區,算法有mod(expr,5)變成(expr,6),原5個分區的數據大部分要重新計算重新分區。雖然使用線性HASH分區會降低分區管理的代價,但是數據卻沒有常規HASH分布得那么均勻。
5)KEY分區
KEY分區類似與HASH分區,但是不能自定義表達式,不過支持分區鍵的類型很多,除Text,Blob等文本類型。
CREATE TABLE `tb04` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '表主鍵',
`pid` int(10) unsigned NOT NULL COMMENT '產品ID',
`price` decimal(15,2) NOT NULL COMMENT '單價',
`num` int(11) NOT NULL COMMENT '購買數量',
`uid` int(10) unsigned NOT NULL COMMENT '客戶ID',
`atime` datetime NOT NULL COMMENT '下單時間',
`utime` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '修改時間',
`isdel` tinyint(4) NOT NULL DEFAULT '0' COMMENT '軟刪除標識',
PRIMARY KEY (`id`)
)
PARTITION BY KEY(id) partitions 4;
插入2行數據:
INSERT INTO tb04(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89757,CURRENT_TIMESTAMP());
INSERT INTO tb04(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89757,CURRENT_TIMESTAMP());
#用執行任務查看記錄落在分區情況
mysql> explain partitions select * from tb04 where id=1;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | tb04 | p0 | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 2 warnings (0.00 sec)
mysql> explain partitions select * from tb04 where id=2;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | tb04 | p3 | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 2 warnings (0.00 sec)
6)分區表管理
建議在生產環境中盡量不要修改分區,alter會讀出存在舊表中的數據,再存入新定義的表中,過程IO將很大,而且全表都會鎖住。
*1*刪除分區:示例以上面tb01表
--未刪除p05分區查詢數據,主要驗證當刪除分區數據是否被刪除
mysql> select count(1) from tb01 where num=10;
+----------+
| count(1) |
+----------+
| 524288 |
+----------+
1 row in set (0.37 sec)
mysql> alter table tb01 drop partition pl05; #刪除pl05分區,如:一次性刪除多個分區,alter table tb01 drop partition pl04,pl05;
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select count(1) from tb01 where num=10; #結果數據也被刪除,慎重操作
+----------+
| count(1) |
+----------+
| 0 |
+----------+
1 row in set (0.01 sec)
注意:刪除分區會刪除數據,謹慎操作;不可以刪除hash或者key分區。
*2*增加分區
注:新分區的值不能包含任意一個現有分區中值列表中的值,否則報錯;新增分區會重新整理數據,原有數據不會丟失。有MAXVALUE值后,直接不能直接加分區,如示例以上面的t1表為例子。
mysql> ALTER TABLE t1 ADD PARTITION (PARTITION P4 VALUES LESS THAN (2018) ) ;
ERROR 1481 (HY000): MAXVALUE can only be used in last partition definition
示例:把tb01上面刪除的pl05分區添加
mysql> ALTER TABLE tb01 ADD PARTITION(PARTITION pl05 VALUES IN (9,10));
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
*3*分解分區
注:Reorganize partition關鍵字可以對表的部分分區或全部分區進行修改,并且不會丟失數據。分解前后分區的整體范圍應該一致。
示例:
mysql> create table tb05
-> (dep int,
-> birthdate date,
-> salary int
-> )
-> partition by range(salary)
-> (
-> partition p1 values less than (1000),
-> partition p2 values less than (2000),
-> partition p3 values less than maxvalue
-> );
Query OK, 0 rows affected (0.08 sec)
****插入一條測試數據
mysql> insert tb05 values(1,'2016-03-06',80);
Query OK, 1 row affected (0.01 sec)
mysql>alter table tb05 reorganize partition p1 into(
partition p01 values less than (100),
partition p02 values less than (1000)
); ----不會丟失數據
mysql> explain partitions select * from tb05 where salary=80; #查看已經落在新的分區p01上
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | tb05 | p01 | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 2 warnings (0.00 sec)
*4*合并分區
注:把2個分區合并為一個。
示例:把上面的tb05表中分解的p01和p02合并至p1上
mysql> alter table tb05 reorganize partition p01,p02 into(partition p1 values less than (1000)); --不會丟失數據
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> explain partitions select * from tb05 where salary=80;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | tb05 | p1 | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 2 warnings (0.00 sec)
*5*重新定義hash分區表:
RANGE和LIST分區在重新定義時,只能重新定義相鄰的分區,不可以跳過分區,并且重新定義的分區區間必須和原分區區間一致,也不可以改變分區的類型。
示例:
mysql> EXPLAIN PARTITIONS SELECT * FROM `tb03`;
+----+-------------+-------+-------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+-------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | tb03 | p0,p1,p2,p3 | ALL | NULL | NULL | NULL | NULL | 4 | 100.00 | NULL |
+----+-------------+-------+-------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 2 warnings (0.00 sec)
mysql> Alter table tb03 partition by hash(id)partitions 8; #不會丟失數據
Query OK, 4 rows affected (0.13 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> EXPLAIN PARTITIONS SELECT * FROM `tb03`;
+----+-------------+-------+-------------------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+-------------------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | tb03 | p0,p1,p2,p3,p4,p5,p6,p7 | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL |
+----+-------------+-------+-------------------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 2 warnings (0.02 sec)
*6*刪除表的所有分區:
示例:刪除tb03表所有分區
mysql> Alter table tb03 remove partitioning; #不會丟失數據
Query OK, 4 rows affected (0.07 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> EXPLAIN PARTITIONS SELECT * FROM `tb03`;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | tb03 | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 2 warnings (0.00 sec)
*7*整理分區碎片
注:如果從分區中刪除了大量的行,或者對一個帶有可變長度的行(也就是說,有VARCHAR,BLOB,或TEXT類型的列)作了許多修改,可以使用“ALTER TABLE ... OPTIMIZE PARTITION”來收回沒有使用的空間,并整理分區數據文件的碎片。
ALTER TABLE tb03 optimize partition p1,p2;
*8*分析分區:
讀取并保存分區的鍵分布。
mysql> ALTER TABLE tb04 CHECK partition p1,p2;
+--------------+-------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+--------------+-------+----------+----------+
| testsms.tb04 | check | status | OK |
+--------------+-------+----------+----------+
1 row in set (0.01 sec)
*9*檢查分區:
可以使用幾乎與對非分區表使用CHECK TABLE 相同的方式檢查分區。這個命令可以告訴tb04表分區p1,p2中的數據或索引是否已經被破壞。如果發生了這種情況,使用“ALTER TABLE ... REPAIR PARTITION”來修補該分區。
mysql> ALTER TABLE tb04 CHECK partition p1,p2;
+--------------+-------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+--------------+-------+----------+----------+
| testsms.tb04 | check | status | OK |
+--------------+-------+----------+----------+
1 row in set (0.01 sec)
6、實際生產簡單應用
場景:之前有個沒有分區的大數據量表SmsSend(例表,大概2800萬行記錄),統計過程非常的耗時,考慮用年分區,并且對歷史數據庫進行備份,把過去2014年的數據轉移至新的備份表smssendbak。如在線重定義比較耗時間,可采用exchange處理!
1)查看當前SmsSend表
mysql> SHOW CREATE TABLE SmsSend; #查看創建信息,未進行分區
| SmsSend | CREATE TABLE `SmsSend` (
`Guid` char(36) NOT NULL COMMENT '唯一標識',
`SID` varbinary(85) DEFAULT NULL COMMENT '商家唯一編號',
`Mobile` longtext NOT NULL COMMENT '接收手機號(以","分割)',
`SmsContent` varchar(500) NOT NULL COMMENT '短信內容',
`SmsCount` int(11) NOT NULL DEFAULT '1' COMMENT '條數',
`Status` int(11) NOT NULL COMMENT '當前狀態(0,等待發送;1,發送成功;-1,發送失敗)',
`SendChanelKeyName` varchar(20) DEFAULT NULL COMMENT '發送通道標識',
`SendTime` datetime NOT NULL COMMENT '發送成功時間',
`SendType` int(11) NOT NULL DEFAULT '1' COMMENT '短信發送類型(1,單發;2,群發)',
`ReceiveTime` datetime DEFAULT NULL COMMENT '接收到回復報告的時間',
`Priority` int(11) NOT NULL DEFAULT '0' COMMENT '優先級',
`UserAccount` varchar(50) DEFAULT NULL COMMENT '操作員',
`ChainStoreGuid` char(36) DEFAULT NULL COMMENT '操作店面唯一標識',
`RelationKey` longtext COMMENT '回復報告關聯標識',
`Meno` text COMMENT '備注',
`IsFree` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否免費'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
mysql> SELECT COUNT(*) FROM SmsSend; #行記錄
+----------+
| COUNT(*) |
+----------+
| 28259803 |
+----------+
1 row in set (1 min 52.60 sec)
#可得知大數據表下在線分區比較慢并且耗性能
mysql> ALTER TABLE SmsSend PARTITION BY RANGE (YEAR(SendTime))
-> (
-> PARTITION py01 VALUES LESS THAN (2015),
-> PARTITION py02 VALUES LESS THAN (2016),
-> PARTITION py03 VALUES LESS THAN (2017) );
Query OK, 28259803 rows affected (20 min 36.05 sec)
Records: 28259803 Duplicates: 0 Warnings: 0
#查看分區記錄數
mysql> select count(1) from SmsSend partition(py01);
+----------+
| count(1) |
+----------+
| 10 |
+----------+
1 row in set (0.00 sec)
mysql> explain partitions select * from SmsSend where SendTime '2015-01-01'; #2014年的數據落在第一分區
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | SmsSend | py01 | ALL | NULL | NULL | NULL | NULL | 10 | 33.33 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 2 warnings (0.00 sec)
mysql> select count(1) from SmsSend partition(py02);
+----------+
| count(1) |
+----------+
| 10 |
+----------+
1 row in set (0.00 sec)
2)快速創建一個smssendbak備份表與原SmsSend表結構一致,并刪除備份表所有分區
mysql> CREATE TABLE smssendbak LIKE SmsSend;
Query OK, 0 rows affected (0.14 sec)
mysql> ALTER TABLE smssendbak REMOVE PARTITIONING;
Query OK, 0 rows affected (0.19 sec)
Records: 0 Duplicates: 0 Warnings: 0
3)使用EXCHANGE PARTITION轉移分區數據至備份表,并查看原來表分區記錄以及新備份表
smssendbak記錄
mysql> ALTER TABLE SmsSend EXCHANGE PARTITION py01 WITH TABLE smssendbak;
Query OK, 0 rows affected (0.13 sec)
mysql> select count(1) from SmsSend partition(py01); #對比上面原SmsSend表分區的記錄
+----------+
| count(1) |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)
mysql> SELECT COUNT(1) FROM smssendbak; #查看新smssendbak備份表轉移記錄
+----------+
| COUNT(1) |
+----------+
| 10 |
+----------+
1 row in set (0.00 sec)
*****************測試使用的表***********************************************************************
創建一個基礎測試表:
CREATE TABLE `tb` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '表主鍵',
`pid` int(10) unsigned NOT NULL COMMENT '產品ID',
`price` decimal(15,2) NOT NULL COMMENT '單價',
`num` int(11) NOT NULL COMMENT '購買數量',
`uid` int(10) unsigned NOT NULL COMMENT '客戶ID',
`atime` datetime NOT NULL COMMENT '下單時間',
`utime` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '修改時間',
`isdel` tinyint(4) NOT NULL DEFAULT '0' COMMENT '軟刪除標識',
) ;
插入數據:
INSERT INTO tb(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89757,CURRENT_TIMESTAMP());
INSERT INTO tb(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89757,'2016-05-01 00:00:00');
INSERT INTO tb(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89757,'2017-05-01 00:00:00');
INSERT INTO tb(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89757,'2018-05-01 00:00:00');
INSERT INTO tb(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89756,'2015-05-01 00:00:00');
INSERT INTO tb(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89756,'2016-05-01 00:00:00');
INSERT INTO tb(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89756,'2017-05-01 00:00:00');
************************************插入大量的數據(建議百萬以上)*************************************
INSERT INTO `tb`(`pid`,`price`,`num`,`uid`,`atime`) SELECT `pid`,`price`,`num`,`uid`,`atime` FROM `tb`;
****注意,如果要刪除自增長的主鍵id(修改過程中,建議該庫改為只讀),如下操作:
Alter table tb change id id int(10); #先刪除自增長
Alter table tb drop primary key;#刪除主建
Alter table tb change id id int not null auto_increment; #如果想重新設置為自增字段
Alter table tb auto_increment=1; #自增起始
以上就是本文的全部內容,希望對大家的學習有所幫助,也希望大家多多支持腳本之家。
您可能感興趣的文章:- MySQL最佳實踐之分區表基本類型
- MySQL分區表的基本入門教程
- MySQL分區表的最佳實踐指南
- MySQL分區表的正確使用方法
- MySQL分區表的局限和限制詳解
- Mysql分區表的管理與維護
- MySQL優化之分區表
- Rails中使用MySQL分區表一個提升性能的方法
- mysql使用教程之分區表的使用方法(刪除分區表)
- 詳解MySQL分區表