通過實例學習MySQL分區(qū)表原理及常用操作
更新時間：2019年05月21日 15:53:38 作者：yikayi
我們試著想一想, 在生產(chǎn)環(huán)境中什么最重要？ 我感覺在生產(chǎn)環(huán)境中應該沒有什么比數(shù)據(jù)跟更為重要. 那么我們該如何保證數(shù)據(jù)不丟失、或者丟失后可以快速恢復呢？只要看完這篇大家應該就能對MySQL中數(shù)據(jù)備份有一定了解
1、分區(qū)表含義
分區(qū)表定義指根據(jù)可以設置為任意大小的規(guī)則，跨文件系統(tǒng)分配單個表的多個部分。實際上，表的不同部分在不同的位置被存儲為單獨的表。用戶所選擇的、實現(xiàn)數(shù)據(jù)分割的規(guī)則被稱為分區(qū)函數(shù)，這在MySQL中它可以是模數(shù)，或者是簡單的匹配一個連續(xù)的數(shù)值區(qū)間或數(shù)值列表，或者是一個內(nèi)部HASH函數(shù)，或一個線性HASH函數(shù)。
分表與分區(qū)的區(qū)別在于：分區(qū)從邏輯上來講只有一張表，而分表則是將一張表分解成多張表
2、分區(qū)表優(yōu)點
1)分區(qū)表更容易維護。對于那些已經(jīng)失去保存意義的數(shù)據(jù)，通?？梢酝ㄟ^刪除與那些數(shù)據(jù)有關的分區(qū)，很容易地刪除那些數(shù)據(jù)。相反地，在某些情況下，添加新數(shù)據(jù)的過程又可以通過為那些新數(shù)據(jù)專門增加一個新的分區(qū)，來很方便地實現(xiàn)。
2)一些查詢可以得到極大的優(yōu)化，這主要是借助于滿足一個給定WHERE語句的數(shù)據(jù)可以只保存在一個或多個分區(qū)內(nèi)，這樣在查找時就不用查找其他剩余的分區(qū)。因為分區(qū)可以在創(chuàng)建了分區(qū)表后進行修改，所以在第一次配置分區(qū)方案時還不曾這么做時，可以重新組織數(shù)據(jù)，來提高那些常用查詢的效率。
3)優(yōu)化查詢。涉及到例如SUM()和COUNT(),可以在多個分區(qū)上并行處理，最終結(jié)果只需通過總計所有分區(qū)得到的結(jié)果。
4)通過跨多個磁盤來分散數(shù)據(jù)查詢，來獲得更大的查詢吞吐量。
3、分區(qū)表限制
1)一個表最多只能有1024個分區(qū)；
2) MySQL5.1中，分區(qū)表達式必須是整數(shù)，或者返回整數(shù)的表達式。在MySQL5.5中提供了非整數(shù)表達式分區(qū)的支持；
3)如果分區(qū)字段中有主鍵或者唯一索引的列，那么多有主鍵列和唯一索引列都必須包含進來。即：分區(qū)字段要么不包含主鍵或者索引列，要么包含全部主鍵和索引列；
4)分區(qū)表中無法使用外鍵約束；
5)MySQL的分區(qū)適用于一個表的所有數(shù)據(jù)和索引，不能只對表數(shù)據(jù)分區(qū)而不對索引分區(qū)，也不能只對索引分區(qū)而不對表分區(qū)，也不能只對表的一部分數(shù)據(jù)分區(qū)。
6)分區(qū)鍵必須是INT類型，或者通過表達式返回INT類型，可以為NULL。唯一的例外是當分區(qū)類型為KEY分區(qū)的時候，可以使用其他類型的列作為分區(qū)鍵(BLOB or TEXT 列除外)
7)如果表中有主鍵和唯一索引，按主鍵字段進行分區(qū)時，唯一索引列應該包含分區(qū)鍵。
8)目前mysql不支持空間類型和臨時表類型進行分區(qū)。不支持全文索引。
9)對象限制(分區(qū)表達式不能出現(xiàn)Stored functions, stored procedures, UDFs, orplugins,Declared variables or user variables.)
10)運算限制(支持加減乘等運算出現(xiàn)在分區(qū)表達式，但是運算后的結(jié)果必須是一個INT或者NULL。支持DIV,不支持/,|, &, ^, <<, >>, and ~ 不允許出現(xiàn)在分區(qū)表達式中)
11)sql_mode限制(官方強烈建議你在創(chuàng)建分區(qū)表后，永遠別改變mysql的sql_mode。因為在不同的模式下，某些函數(shù)或者運算返回的結(jié)果可能會不一樣)
12)不支持query_cache和INSERT DELAYED
13)分區(qū)鍵不能是一個子查詢(即使是子查詢返回的是int值或者null.)
14)子分區(qū)限制(只有RANG和LIST分區(qū)能進行子分區(qū)。HASH和KEY分區(qū)不能進行子分區(qū)并且子分區(qū)必須是HASH 或 KEY類型)
4、分區(qū)類型
1)水平分區(qū)(根據(jù)列屬性按行分)
如:一個包含十年發(fā)票記錄的表可以被分區(qū)為十個不同的分區(qū)，每個分區(qū)包含的是其中一年的記錄。
水平分區(qū)的幾種模式：
* Range（范圍）：這種模式允許DBA將數(shù)據(jù)劃分不同范圍。
如:可以將一個表通過年份劃分成三個分區(qū)，80年代（1980's）的數(shù)據(jù)，90年代（1990's）的數(shù)據(jù)以及任何在2000年（包括2000年）后的數(shù)據(jù)。
* Hash（哈希）：這中模式允許DBA通過對表的一個或多個列的Hash Key進行計算，最后通過這個Hash碼不同數(shù)值對應的數(shù)據(jù)區(qū)域進行分區(qū)。
如：可以建立一個對表主鍵進行分區(qū)的表。
* Key（鍵值）：上面Hash模式的一種延伸，這里的Hash Key是MySQL系統(tǒng)產(chǎn)生的。
* List（預定義列表）：這種模式允許系統(tǒng)通過DBA定義的列表的值所對應的行數(shù)據(jù)進行分割。例如：DBA建立了一個橫跨三個分區(qū)的表，分別根據(jù)2004年2005年和2006年值所對應的數(shù)據(jù)。
* Columns分區(qū)是對range，list分區(qū)的補充，彌補了后兩者只支持整型數(shù)分區(qū)（或者通過轉(zhuǎn)換為整型數(shù)），使得支持數(shù)據(jù)類型增加很多（所有整數(shù)類型，日期時間類型，字符類型），還支持多列分區(qū)。
注:在多列分區(qū)表上插入數(shù)據(jù)時，采用元組的比較，即多列排序，先根據(jù)field1排序，再根據(jù)field2排序，根據(jù)排序結(jié)果來來分區(qū)存儲數(shù)據(jù)。
* Composite（復合模式）：以上模式的組合使用。
如：在初始化已經(jīng)進行了Range范圍分區(qū)的表上，可以對其中一個分區(qū)再進行hash哈希分區(qū)。
垂直分區(qū)（按列分）：
如：一個包含了大text和BLOB列的表，這些text和BLOB列又不經(jīng)常被訪問，可以把這些不經(jīng)常使用的text和BLOB劃分到另一個分區(qū)，在保證它們數(shù)據(jù)相關性的同時還能提高訪問速度。
注意：子分區(qū)（關鍵字subparttition）：使用RANGE或LIST分區(qū)可以再次分割形成子分區(qū)，子分區(qū)可以是HASH分區(qū)或者KEY分區(qū)。建議在多磁盤上使用。
查看是否有支持Partition分區(qū)表
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 參數(shù)，新的版本已移除該參數(shù)。
mysql> SHOW VARIABLES LIKE '%partition%';
5、實戰(zhàn)常用分區(qū)表幾種模式
1)使用RANGE分區(qū)模式
####創(chuàng)建測試表t1，并插入接近400萬行數(shù)據(jù)，再沒有分區(qū)的情況下，對查詢某一條件耗時
mysql> CREATE TABLE `t1` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '表主鍵', `pid` int(10) unsigned NOT NULL COMMENT '產(chǎn)品ID', `price` decimal(15,2) NOT NULL COMMENT '單價', `num` int(11) NOT NULL COMMENT '購買數(shù)量', `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'); /**********************************主從復制大量數(shù)據(jù)******************************/ 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) #沒有分區(qū)表情況耗時5.62s
如果是針對已有的表進行表分區(qū)，可以使用ALTER TABLE來進行更改表為分區(qū)表，這個操作會創(chuàng)建一個分區(qū)表，然后自動進行數(shù)據(jù)copy然后刪除原表。
注: 這種會使服務器資源消耗比較大(400多萬數(shù)據(jù)要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`; #查看分區(qū)情況 +----+-------------+-------+-------------+------+---------------+------+---------+------+---------+----------+-------+ | 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)
同樣用上面的查詢測試結(jié)果
mysql> SELECT * FROM `t1` WHERE `uid`=89757 AND `atime`< CURRENT_TIMESTAMP(); 1048576 rows in set (4.46 sec) #與上面沒有分區(qū)查詢執(zhí)行的時間相比少了接近1s mysql> EXPLAIN PARTITIONS SELECT * FROM `t1` WHERE `uid`=89757 AND `atime`< CURRENT_TIMESTAMP(); #查看查詢使用的分區(qū)情況 +----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+ | 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)同時也要注意，進行表分區(qū)以后，mysql存放的數(shù)據(jù)文件夾中該表的存放文件也被拆分為多個
-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實際生產(chǎn)環(huán)境中，大多是采用另外一種方式:新建一個和原來表一樣的分區(qū)表，然后把數(shù)據(jù)從原表導出，接著導入新表，最后建立普通索引。
mysql> CREATE TABLE `t2` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '表主鍵',
`pid` int(10) unsigned NOT NULL COMMENT '產(chǎn)品ID',
`price` decimal(15,2) NOT NULL COMMENT '單價',
`num` int(11) NOT NULL COMMENT '購買數(shù)量',
`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，而分區(qū)字段是atime, 這里主鍵要修改為 id,stsdate 聯(lián)合主鍵，分區(qū)表要求分區(qū)字段要是主鍵或者是主鍵的一部分!!!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) *******************************************插入數(shù)據(jù)************************************************* 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或采用導出數(shù)據(jù)再導入數(shù)據(jù)，可再添加索引
mysqldump -u dbname -p --no-create-info dbname t2 > t2.sq
修改表名，導入數(shù)據(jù)，測試下ok，刪除原來的表。
2)使用LIST分區(qū)模式(如果原表存在主鍵強烈創(chuàng)建新表時，把原主鍵和要分區(qū)字段作為聯(lián)合主鍵一并創(chuàng)建)
mysql> CREATE TABLE `tb01` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '表主鍵', `pid` int(10) unsigned NOT NULL COMMENT '產(chǎn)品ID', `price` decimal(15,2) NOT NULL COMMENT '單價', `num` int(11) NOT NULL COMMENT '購買數(shù)量', `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`) ); *****************************插入測試數(shù)據(jù)****************************************************** 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數(shù)據(jù)文件中生成，以下文件 -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分區(qū)
創(chuàng)建多列分區(qū)表tb02，這里兩列都不是聯(lián)合主鍵
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); #手工插入測試數(shù)據(jù) 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分區(qū)
HASH主要是為了讓數(shù)據(jù)在設定個數(shù)的分區(qū)中盡可能分布平均，執(zhí)行哈希分區(qū)時，mysql會對分區(qū)鍵執(zhí)行哈希函數(shù)，以確定數(shù)據(jù)放在哪個分區(qū)中。HASH分區(qū)分為常規(guī)HASH分區(qū)和線性HASH分區(qū)，前者使用取模算法，后者使用線性2的冪的運算規(guī)則。
CREATE TABLE `tb03` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '表主鍵', `pid` int(10) unsigned NOT NULL COMMENT '產(chǎn)品ID', `price` decimal(15,2) NOT NULL COMMENT '單價', `num` int(11) NOT NULL COMMENT '購買數(shù)量', `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行數(shù)據(jù)： 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分區(qū)雖然盡可能讓數(shù)據(jù)平均地分布在每個分區(qū)上，提高了查詢效率，但增加了分區(qū)管理的代價，比如以前有5個分區(qū)，現(xiàn)在要加上一個分區(qū)，算法有mod(expr,5)變成(expr,6)，原5個分區(qū)的數(shù)據(jù)大部分要重新計算重新分區(qū)。雖然使用線性HASH分區(qū)會降低分區(qū)管理的代價，但是數(shù)據(jù)卻沒有常規(guī)HASH分布得那么均勻。
5)KEY分區(qū)
KEY分區(qū)類似與HASH分區(qū)，但是不能自定義表達式，不過支持分區(qū)鍵的類型很多，除Text,Blob等文本類型。
CREATE TABLE `tb04` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '表主鍵', `pid` int(10) unsigned NOT NULL COMMENT '產(chǎn)品ID', `price` decimal(15,2) NOT NULL COMMENT '單價', `num` int(11) NOT NULL COMMENT '購買數(shù)量', `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行數(shù)據(jù)： 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()); #用執(zhí)行任務查看記錄落在分區(qū)情況 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)分區(qū)表管理
建議在生產(chǎn)環(huán)境中盡量不要修改分區(qū)，alter會讀出存在舊表中的數(shù)據(jù)，再存入新定義的表中，過程IO將很大，而且全表都會鎖住。
*1*刪除分區(qū):示例以上面tb01表
--未刪除p05分區(qū)查詢數(shù)據(jù)，主要驗證當刪除分區(qū)數(shù)據(jù)是否被刪除
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分區(qū),如：一次性刪除多個分區(qū)，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; #結(jié)果數(shù)據(jù)也被刪除，慎重操作 +----------+ | count(1) | +----------+ | 0 | +----------+ 1 row in set (0.01 sec）注意：刪除分區(qū)會刪除數(shù)據(jù)，謹慎操作；不可以刪除hash或者key分區(qū)。
*2*增加分區(qū)
注：新分區(qū)的值不能包含任意一個現(xiàn)有分區(qū)中值列表中的值，否則報錯;新增分區(qū)會重新整理數(shù)據(jù)，原有數(shù)據(jù)不會丟失。有MAXVALUE值后，直接不能直接加分區(qū)，如示例以上面的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分區(qū)添加 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*分解分區(qū)
注：Reorganize partition關鍵字可以對表的部分分區(qū)或全部分區(qū)進行修改，并且不會丟失數(shù)據(jù)。分解前后分區(qū)的整體范圍應該一致。
示例：
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) ****插入一條測試數(shù)據(jù) 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) ); ----不會丟失數(shù)據(jù) mysql> explain partitions select * from tb05 where salary=80; #查看已經(jīng)落在新的分區(qū)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*合并分區(qū)
注:把2個分區(qū)合并為一個。
示例：把上面的tb05表中分解的p01和p02合并至p1上
mysql> alter table tb05 reorganize partition p01,p02 into(partition p1 values less than (1000)); --不會丟失數(shù)據(jù) 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分區(qū)表:
RANGE和LIST分區(qū)在重新定義時，只能重新定義相鄰的分區(qū)，不可以跳過分區(qū)，并且重新定義的分區(qū)區(qū)間必須和原分區(qū)區(qū)間一致，也不可以改變分區(qū)的類型。
示例:
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; #不會丟失數(shù)據(jù) 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*刪除表的所有分區(qū):
示例:刪除tb03表所有分區(qū)
mysql> Alter table tb03 remove partitioning; #不會丟失數(shù)據(jù) 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*整理分區(qū)碎片
注:如果從分區(qū)中刪除了大量的行，或者對一個帶有可變長度的行（也就是說，有VARCHAR，BLOB，或TEXT類型的列）作了許多修改，可以使用“ALTER TABLE ... OPTIMIZE PARTITION”來收回沒有使用的空間，并整理分區(qū)數(shù)據(jù)文件的碎片。
ALTER TABLE tb03 optimize partition p1,p2;
*8*分析分區(qū)：
讀取并保存分區(qū)的鍵分布。
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*檢查分區(qū):
可以使用幾乎與對非分區(qū)表使用CHECK TABLE 相同的方式檢查分區(qū)。這個命令可以告訴tb04表分區(qū)p1,p2中的數(shù)據(jù)或索引是否已經(jīng)被破壞。如果發(fā)生了這種情況，使用“ALTER TABLE ... REPAIR PARTITION”來修補該分區(qū)。
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、實際生產(chǎn)簡單應用
場景:之前有個沒有分區(qū)的大數(shù)據(jù)量表SmsSend(例表，大概2800萬行記錄)，統(tǒng)計過程非常的耗時，考慮用年分區(qū)，并且對歷史數(shù)據(jù)庫進行備份，把過去2014年的數(shù)據(jù)轉(zhuǎn)移至新的備份表smssendbak。如在線重定義比較耗時間，可采用exchange處理！
1)查看當前SmsSend表
mysql> SHOW CREATE TABLE SmsSend; #查看創(chuàng)建信息，未進行分區(qū) | 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 '短信內(nèi)容', `SmsCount` int(11) NOT NULL DEFAULT '1' COMMENT '條數(shù)', `Status` int(11) NOT NULL COMMENT '當前狀態(tài)(0,等待發(fā)送;1,發(fā)送成功;-1,發(fā)送失敗)', `SendChanelKeyName` varchar(20) DEFAULT NULL COMMENT '發(fā)送通道標識', `SendTime` datetime NOT NULL COMMENT '發(fā)送成功時間', `SendType` int(11) NOT NULL DEFAULT '1' COMMENT '短信發(fā)送類型(1,單發(fā);2,群發(fā))', `ReceiveTime` datetime DEFAULT NULL COMMENT '接收到回復報告的時間', `Priority` int(11) NOT NULL DEFAULT '0' COMMENT '優(yōu)先級', `UserAccount` varchar(50) DEFAULT NULL COMMENT '操作員', `ChainStoreGuid` char(36) DEFAULT NULL COMMENT '操作店面唯一標識', `RelationKey` longtext COMMENT '回復報告關聯(lián)標識', `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) #可得知大數(shù)據(jù)表下在線分區(qū)比較慢并且耗性能 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 #查看分區(qū)記錄數(shù) 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年的數(shù)據(jù)落在第一分區(qū) +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 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)快速創(chuàng)建一個smssendbak備份表與原SmsSend表結(jié)構(gòu)一致，并刪除備份表所有分區(qū)
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轉(zhuǎn)移分區(qū)數(shù)據(jù)至備份表，并查看原來表分區(qū)記錄以及新備份表
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表分區(qū)的記錄 +----------+ | count(1) | +----------+ | 0 | +----------+ 1 row in set (0.00 sec) mysql> SELECT COUNT(1) FROM smssendbak; #查看新smssendbak備份表轉(zhuǎn)移記錄 +----------+ | COUNT(1) | +----------+ | 10 | +----------+ 1 row in set (0.00 sec) *****************測試使用的表*********************************************************************** 創(chuàng)建一個基礎測試表: CREATE TABLE `tb` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '表主鍵', `pid` int(10) unsigned NOT NULL COMMENT '產(chǎn)品ID', `price` decimal(15,2) NOT NULL COMMENT '單價', `num` int(11) NOT NULL COMMENT '購買數(shù)量', `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 '軟刪除標識', ) ; 插入數(shù)據(jù)： 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'); ************************************插入大量的數(shù)據(jù)(建議百萬以上)************************************* 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; #自增起始
以上就是本文的全部內(nèi)容，希望對大家的學習有所幫助，也希望大家多多支持腳本之家。
相關文章
這篇文章主要介紹了如何通過配置文件my.ini修改mysql密碼問題，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教2022-12-12
SQLyog?是一個快速而簡潔的圖形化管理MYSQL數(shù)據(jù)庫的工具，它能夠在任何地點有效地管理你的數(shù)據(jù)庫，這篇文章主要介紹了SQLyog12.08安裝詳細教程,需要的朋友可以參考下2023-04-04
MySQL使用全庫備份數(shù)據(jù)恢復單表數(shù)據(jù)的方法
這篇文章主要給大家介紹了關于MySQL使用全庫備份數(shù)據(jù)恢復單表數(shù)據(jù)的相關資料，文中通過示例代碼介紹的非常詳細，對大家學習或者使用mysql具有一定的參考學習價值，需要的朋友們下面來一起看看吧2018-12-12
這篇文章主要為大家詳細介紹了MySql安裝與使用圖文教程，具有一定的參考價值，感興趣的小伙伴們可以參考一下2017-03-03
這篇文章主要介紹了mysql 協(xié)議的ping命令包及解析詳解及實例的相關資料,這里附有簡單實例代碼并附下載源碼，需要的朋友可以參考下2017-01-01
當用戶查看表格的大量數(shù)據(jù)是,由于數(shù)據(jù)量過于巨大會導致很難獲取到需要的數(shù)據(jù),在這時,就需要一個方法,一個可以通過用戶輸入獲取到用戶需要的數(shù)據(jù)并回填入表格，這就是條件查詢的作用2021-10-10
這篇文章主要給大家介紹了關于MySQL定時備份方案的相關資料，文中通過示例代碼以及圖文介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面來一起學習學習吧2020-07-07
這篇文章主要介紹了MySQL 導入 導出數(shù)據(jù)的方法，文中講解非常詳細，代碼幫助大家更好的理解和學習，感興趣的朋友可以了解下2020-07-07
在 MySQL 中導入大文件時,mysql source 導入大文件速度太慢,可能會遇到性能問題或內(nèi)存限制,以下是一些優(yōu)化導入大文件的建議,需要的朋友可以參考一下2025-02-02
深入mysql "ON DUPLICATE KEY UPDATE" 語法的分析
本篇文章是對mysql "ON DUPLICATE KEY UPDATE"語法進行了詳細的分析介紹，需要的朋友參考下2013-06-06