天堂中文字幕,欧美中日韩网站

MySQL表分區(qū)介紹

??表分區(qū)是將?個(gè)表的數(shù)據(jù)按照?定的規(guī)則?平劃分為不同的邏輯塊，并分別進(jìn)?物理存儲(chǔ)，這個(gè)規(guī)則就叫做分區(qū)函數(shù)，可以有不同的分區(qū)規(guī)則。5.7可以通過(guò)show plugins語(yǔ)句查看當(dāng)前MySQL是否?持表分區(qū)功能。

 +----------------------------+----------+--------------------+---------+---------+
 | Name                       | Status   | Type               | Library | License |
 +----------------------------+----------+--------------------+---------+---------+
 | BLACKHOLE                  | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
 | partition                  | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
 | FEDERATED                  | DISABLED | STORAGE ENGINE     | NULL    | GPL     |
 | ARCHIVE                    | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
 | ngram                      | ACTIVE   | FTPARSER           | NULL    | GPL     |

MySQL8.0移除了show plugins?對(duì)partition的顯示，但社區(qū)版本的表分區(qū)功能是默認(rèn)開(kāi)啟的

mysql> CREATE TABLE employees ( id INT NOT NULL,
    ->  fname VARCHAR(30), lname VARCHAR(30),
    ->  hired DATE NOT NULL DEFAULT '1970-01-01',
    ->  separated DATE NOT NULL DEFAULT '9999-12-31',
    ->  job_code INT NOT NULL,
    ->  store_id INT NOT NULL )
    ->  PARTITION BY RANGE (store_id)
    ->  ( PARTITION p0 VALUES LESS THAN (6), PARTITION p1 VALUES LESS THAN (11),
    ->  PARTITION p2 VALUES LESS THAN (16), PARTITION p3 VALUES LESS THAN (21) );
Query OK, 0 rows affected (0.19 sec)

mysql> insert into employees values(1,'a','a',now(),now(),1,1),(2,'b','b',now(),now(),1,6);
Query OK, 2 rows affected, 4 warnings (0.07 sec)
Records: 2  Duplicates: 0  Warnings: 4

mysql> select * from employees;
+----+-------+-------+------------+------------+----------+----------+
| id | fname | lname | hired      | separated  | job_code | store_id |
+----+-------+-------+------------+------------+----------+----------+
|  1 | a     | a     | 2020-12-28 | 2020-12-28 |        1 |        1 |
|  2 | b     | b     | 2020-12-28 | 2020-12-28 |        1 |        6 |
+----+-------+-------+------------+------------+----------+----------+
2 rows in set (0.00 sec)

mysql> select * from employees partition (p0);
+----+-------+-------+------------+------------+----------+----------+
| id | fname | lname | hired      | separated  | job_code | store_id |
+----+-------+-------+------------+------------+----------+----------+
|  1 | a     | a     | 2020-12-28 | 2020-12-28 |        1 |        1 |
+----+-------+-------+------------+------------+----------+----------+
1 row in set (0.00 sec)

mysql> select * from employees partition (p1);
+----+-------+-------+------------+------------+----------+----------+
| id | fname | lname | hired      | separated  | job_code | store_id |
+----+-------+-------+------------+------------+----------+----------+
|  2 | b     | b     | 2020-12-28 | 2020-12-28 |        1 |        6 |
+----+-------+-------+------------+------------+----------+----------+
1 row in set (0.00 sec)

??但當(dāng)表中含有主鍵或唯?鍵時(shí)，則每個(gè)被?作分區(qū)函數(shù)的字段必須是表中唯?鍵和主鍵的全部或?部分，否則就?法創(chuàng)建分區(qū)表。?如下?的表由于唯?鍵和主鍵沒(méi)有相同的字段，所以?法創(chuàng)建表分區(qū)

mysql> CREATE TABLE tnp (
    ->  id INT NOT NULL AUTO_INCREMENT,
    ->  ref BIGINT NOT NULL,
    ->  name VARCHAR(255),
    ->  PRIMARY KEY pk (id),
    ->  UNIQUE KEY uk (ref) )
    ->  PARTITION BY RANGE (id)
    ->  ( PARTITION p0 VALUES LESS THAN (6), PARTITION p1 VALUES LESS THAN(11));
ERROR 1503 (HY000): A UNIQUE INDEX must include all columns in the table's partitioning function
mysql>  CREATE TABLE tnp (
    ->  id INT NOT NULL AUTO_INCREMENT,
    ->  ref BIGINT NOT NULL,
    ->  name VARCHAR(255),
    ->  PRIMARY KEY pk (id),
    ->  UNIQUE KEY uk (ref) )
    ->  PARTITION BY RANGE (ref)
    ->  ( PARTITION p0 VALUES LESS THAN (6), PARTITION p1 VALUES LESS THAN(11));
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function

上述例?中刪除唯?鍵，確保主鍵中的字段包含分區(qū)函數(shù)中的所有字段，創(chuàng)建成功

mysql>  CREATE TABLE tnp (
    ->  id INT NOT NULL AUTO_INCREMENT,
    ->  ref BIGINT NOT NULL,
    ->  name VARCHAR(255),
    ->  PRIMARY KEY pk (id))
    ->  PARTITION BY RANGE (id)
    ->  ( PARTITION p0 VALUES LESS THAN (6), PARTITION p1 VALUES LESS THAN (11));
Query OK, 0 rows affected (0.17 sec)

或者將主鍵擴(kuò)展為包含ref字段

mysql> CREATE TABLE tnp (id INT NOT NULL ,
    -> ref BIGINT NOT NULL,
    -> name VARCHAR(255),
    -> PRIMARY KEY pk (id,ref),
    -> UNIQUE KEY uk (ref) )
    -> PARTITION BY RANGE (ref)
    -> ( PARTITION p0 VALUES LESS THAN (6), PARTITION p1 VALUES LESS THAN (11));
Query OK, 0 rows affected (0.21 sec)

表分區(qū)的主要優(yōu)勢(shì)在于：
??可以允許在?個(gè)表?存儲(chǔ)更多的數(shù)據(jù)，突破磁盤(pán)限制或者?件系統(tǒng)限制
??對(duì)于從表?將過(guò)期或歷史的數(shù)據(jù)移除在表分區(qū)很容易實(shí)現(xiàn)，只要將對(duì)應(yīng)的分區(qū)移除即可
??對(duì)某些查詢(xún)和修改語(yǔ)句來(lái)說(shuō)，可以?動(dòng)將數(shù)據(jù)范圍縮?到?個(gè)或?個(gè)表分區(qū)上，優(yōu)化語(yǔ)句執(zhí)?效率。?且可以通過(guò)顯示指定表分區(qū)來(lái)執(zhí)?語(yǔ)句，?如SELECT * FROM t PARTITION (p0,p1) WHERE c < 5

[root@vmware1 data]# cd test
[root@vmware1 test]# ls
 db.opt employees#P#p0.ibd employees#P#p2.ibd tnp.frm employees.frm employees#P#p1.ibd employees#P#p3.ibd tnp#P#p0.ibd

表分區(qū)類(lèi)型分為：

RANGE表分區(qū)：范圍表分區(qū)，按照?定的范圍值來(lái)確定每個(gè)分區(qū)包含的數(shù)據(jù)
LIST表分區(qū)：列表表分區(qū)，按照?個(gè)?個(gè)確定的值來(lái)確定每個(gè)分區(qū)包含的數(shù)據(jù)
HASH表分區(qū)：哈希表分區(qū)，按照?個(gè)?定義的函數(shù)返回值來(lái)確定每個(gè)分區(qū)包含的數(shù)據(jù)
KEY表分區(qū) ：key表分區(qū)，與哈希表分區(qū)類(lèi)似，只是?MySQL??的HASH函數(shù)來(lái)確定每個(gè)分區(qū)包含的數(shù)據(jù)

$\color{red}{RANGE表分區(qū)：}$
范圍表分區(qū)，按照?定的范圍值來(lái)確定每個(gè)分區(qū)包含的數(shù)據(jù)，分區(qū)函數(shù)使?的字段必須只能是整數(shù)類(lèi)型，分區(qū)的定義范圍必須是連續(xù)的，且不能有重疊部分，通過(guò)使?VALUES LESS THAN來(lái)定義分區(qū)范圍，表分區(qū)的范圍定義是從?到?定義的

?如：

CREATE TABLE employees ( id INT NOT NULL,
 fname VARCHAR(30), lname VARCHAR(30),
 hired DATE NOT NULL DEFAULT '1970-01-01',
 separated DATE NOT NULL DEFAULT '9999-12-31',
 job_code INT NOT NULL,
 store_id INT NOT NULL )
 PARTITION BY RANGE (store_id)
 ( PARTITION p0 VALUES LESS THAN (6), PARTITION p1 VALUES LESS THAN (11),
 PARTITION p2 VALUES LESS THAN (16), PARTITION p3 VALUES LESS THAN (21) );

??Store_id<6的數(shù)據(jù)被放在p0分區(qū)?，6<=store_id<10之間的數(shù)據(jù)被放在p1分區(qū)?，以此類(lèi)推，當(dāng)新插?的數(shù)據(jù)為(72, ‘Mitchell’, ‘Wilson’, ‘1998-06-25’, NULL, 13) 時(shí)，則新數(shù)據(jù)被插?到p2分區(qū)?，但當(dāng)插?的數(shù)據(jù)的store_id為21時(shí)，由于沒(méi)有分區(qū)去容納此數(shù)據(jù)，所以會(huì)報(bào)錯(cuò)，我們需要修改?下表的定義
報(bào)錯(cuò)：

mysql> insert into employees values(4,'d','d',now(),now(),1,21);
 ERROR 1526 (HY000): Table has no partition for value 21

修改表的定義：

 CREATE TABLE employees ( id INT NOT NULL,
 fname VARCHAR(30), lname VARCHAR(30),
 hired DATE NOT NULL DEFAULT '1970-01-01',
 separated DATE NOT NULL DEFAULT '9999-12-31',
 job_code INT NOT NULL,
 store_id INT NOT NULL )
 PARTITION BY RANGE (store_id)
 ( PARTITION p0 VALUES LESS THAN (6),PARTITION p1 VALUES LESS THAN (16),
 PARTITION p2 VALUES LESS THAN (11), PARTITION p3 VALUES LESS THAN (21));
 ERROR 1493 (HY000): VALUES LESS THAN value must be strictly increasing for each partition ##表分區(qū)的范圍定義不符合從?到?

 CREATE TABLE employees2( id INT NOT NULL,
 fname VARCHAR(30), lname VARCHAR(30),
 hired DATE NOT NULL DEFAULT '1970-01-01',
 separated DATE NOT NULL DEFAULT '9999-12-31',
 job_code INT NOT NULL,
 store_id INT NOT NULL )
 PARTITION BY RANGE (name)
 ( PARTITION p0 VALUES LESS THAN ('a'), PARTITION p1 VALUES LESS THAN ('b'),
 PARTITION p2 VALUES LESS THAN ('c'), PARTITION p3 VALUES LESS THAN ('d'));
 ERROR 1697 (HY000): VALUES value for partition 'p0' must have typeINT ##表分區(qū)的范圍定義不符合整形

CREATE TABLE employees ( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30),
 hired DATE NOT NULL DEFAULT '1970-01-01', separated DATE NOT NULL DEFAULT '9999-12-31',
 job_code INT NOT NULL, store_id INT NOT NULL )
 PARTITION BY RANGE (store_id)
 ( PARTITION p0 VALUES LESS THAN (6),
 PARTITION p1 VALUES LESS THAN (11),
 PARTITION p2 VALUES LESS THAN (16),
 PARTITION p3 VALUES LESS THAN MAXVALUE
 );

??MAXVALUE關(guān)鍵詞的作?是表示可能的最?值，所以任何store_id>=16的數(shù)據(jù)都會(huì)被寫(xiě)?到p3分區(qū)?。分區(qū)函數(shù)中也可以使?表達(dá)式，?如：

CREATE TABLE employees ( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30),
 hired DATE NOT NULL DEFAULT '1970-01-01', separated DATE NOT NULL DEFAULT '9999-12-31',
 job_code INT, store_id INT )
 PARTITION BY RANGE ( YEAR(separated) )
 ( PARTITION p0 VALUES LESS THAN (1991),
 PARTITION p1 VALUES LESS THAN (1996),
 PARTITION p2 VALUES LESS THAN (2001),
 PARTITION p3 VALUES LESS THAN MAXVALUE );

??對(duì)timestamp字段類(lèi)型可以使?的表達(dá)式?前僅有unix_timestamp，其他的表達(dá)式都不允許

mysql> CREATE TABLE quarterly_report_status ( 
 report_id INT NOT NULL, 
 report_status VARCHAR(20) NOT NULL, 
 report_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP)
 PARTITION BY RANGE ( UNIX_TIMESTAMP(report_updated) )
 ( PARTITION p0 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-01-01 00:00:00') ),
 PARTITION p1 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-04-01 00:00:00') ),
 PARTITION p2 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-07-01 00:00:00') ),
 PARTITION p3 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-10-01 00:00:00') ),
 PARTITION p4 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-01-01 00:00:00') ),
 PARTITION p5 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-04-01 00:00:00') ),
 PARTITION p6 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-07-01 00:00:00') ),
 PARTITION p7 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-10-01 00:00:00') ),
 PARTITION p8 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-01-01 00:00:00') ),
 PARTITION p9 VALUES LESS THAN (MAXVALUE) ); 
Query OK, 0 rows affected (0.28 sec)

mysql> create table temp(tstamp timestamp) partition by range(year(tstamp))(partition p0 values less than(2017));
ERROR 1486 (HY000): Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
mysql> create table temp(tstamp datetime) partition by range(year(tstamp))(partition p0 values less than(2017));
Query OK, 0 rows affected (0.07 sec)

$\color{red}{LIST表分區(qū)：}$
列表表分區(qū)，按照?個(gè)?個(gè)確定的值來(lái)確定每個(gè)分區(qū)包含的數(shù)據(jù)，通過(guò)PARTITION BY LIST(expr)分區(qū)函數(shù)表達(dá)式必須返回整數(shù)，取值范圍通過(guò)VALUES IN (value_list)定義

CREATE TABLE employees (
 id INT NOT NULL,
 fname VARCHAR(30),
 lname VARCHAR(30),
 hired DATE NOT NULL DEFAULT '1970-01-01',
 separated DATE NOT NULL DEFAULT '9999-12-31',
 job_code INT, store_id INT )
 PARTITION BY LIST(store_id)
 ( PARTITION pNorth VALUES IN (3,5,6,9,17),
 PARTITION pEast VALUES IN (1,2,10,11,19,20),
 PARTITION pWest VALUES IN (4,12,13,14,18),
 PARTITION pCentral VALUES IN (7,8,15,16) ); 
Query OK, 0 rows affected (0.20 sec)

mysql> drop table employees;
Query OK, 0 rows affected (0.18 sec)

CREATE TABLE employees (
 id INT NOT NULL,
 fname VARCHAR(30),
 lname VARCHAR(30),
 hired DATE NOT NULL DEFAULT '1970-01-01',
 separated DATE NOT NULL DEFAULT '9999-12-31',
 job_code INT, store_name varchar(20) )
 PARTITION BY LIST(store_name)
 ( PARTITION pNorth VALUES IN ('a','b'),
 PARTITION pEast VALUES IN ('c','d'));
ERROR 1697 (HY000): VALUES value for partition 'pNorth' must have type INT

對(duì)List表分區(qū)來(lái)說(shuō)，沒(méi)有MAXVALUE特殊值，所有的可能取值都需要再VALUES IN中包含，如果有未定義的取值則會(huì)報(bào)錯(cuò)

mysql> CREATE TABLE h2 (c1 INT, c2 INT )
 PARTITION BY LIST(c1)
 (PARTITION p0 VALUES IN (1, 4, 7),
 PARTITION p1 VALUES IN (2, 5, 8)); 
Query OK, 0 rows affected (0.16 sec)

mysql> INSERT INTO h2 VALUES (3, 5);
ERROR 1526 (HY000): Table has no partition for value 3

同樣，當(dāng)有主鍵或者唯?鍵存在的情況下，分區(qū)函數(shù)字段需要包含在主鍵或唯?鍵中

mysql> CREATE TABLE employees (
 id INT NOT NULL primary key,
 fname VARCHAR(30),
 lname VARCHAR(30),
 hired DATE NOT NULL DEFAULT '1970-01-01',
 separated DATE NOT NULL DEFAULT '9999-12-31',
 job_code INT,
 store_id int )
 PARTITION BY LIST(store_id)
 ( PARTITION pNorth VALUES IN (1,3),
 PARTITION pEast VALUES IN (2,4)); 
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function

對(duì)range和list表分區(qū)來(lái)說(shuō)，分區(qū)函數(shù)可以包含多個(gè)字段，分區(qū)多字段函數(shù)(column partition)所涉及的字段類(lèi)型可以包括：

TINYINT, SMALLINT, MEDIUMINT, INT (INTEGER), and BIGINT.
DATE and DATETIME.
CHAR, VARCHAR, BINARY, and VARBINARY.
其他的字段類(lèi)型都不?持

范圍多字段分區(qū)函數(shù)與普通的范圍分區(qū)函數(shù)的區(qū)別在于：
a) 字段類(lèi)型多樣化
b) 范圍多字段分區(qū)函數(shù)不?持表達(dá)式，只能?字段名
c) 范圍多字段分區(qū)函數(shù)?持?個(gè)或多個(gè)字段

每個(gè)column_list?的字段和value_list?的數(shù)值必須??對(duì)應(yīng)，數(shù)據(jù)類(lèi)型也要?致
對(duì)范圍多字段分區(qū)來(lái)說(shuō)，有時(shí)??數(shù)據(jù)的分區(qū)列表的第?個(gè)元素等于VALUES LESS THAN的值列表的第?個(gè)元素是會(huì)被插?到相應(yīng)的分區(qū)

mysql> CREATE TABLE rcx (a INT, b INT,
 c CHAR(3), d INT)
 PARTITION BY RANGE COLUMNS(a,b,c)
 (PARTITION p0 VALUES LESS THAN (5,10,'ggg'),
 PARTITION p1 VALUES LESS THAN (10,20,'mmm'),
 PARTITION p2 VALUES LESS THAN (15,30,'sss'),
 PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE,MAXVALUE));
Query OK, 0 rows affected (0.13 sec)

mysql> insert into rcx values(4,5,'abc',1),(5,9,'abc',1),(4,11,'ggg',1),(5,11,'abc',1),(6,2, 'abc',1);
Query OK, 5 rows affected (0.07 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from rcx partition (p0);
+------+------+------+------+
| a    | b    | c    | d    |
+------+------+------+------+
|    4 |    5 | abc  |    1 |
|    5 |    9 | abc  |    1 |
|    4 |   11 | ggg  |    1 |
+------+------+------+------+
3 rows in set (0.00 sec)

mysql> select * from rcx partition (p1);
+------+------+------+------+
| a    | b    | c    | d    |
+------+------+------+------+
|    5 |   11 | abc  |    1 |
|    6 |    2 | abc  |    1 |
+------+------+------+------+
2 rows in set (0.00 sec)

再?如創(chuàng)建如下的表分區(qū)：

mysql> CREATE TABLE rc1 ( a INT, b INT )
 PARTITION BY RANGE COLUMNS(a, b)
 ( PARTITION p0 VALUES LESS THAN (5, 12),
 PARTITION p3 VALUES LESS THAN (MAXVALUE, MAXVALUE) );
Query OK, 0 rows affected (0.18 sec)

mysql> INSERT INTO rc1 VALUES (5,10), (5,11), (5,12);
Query OK, 3 rows affected (0.09 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT PARTITION_NAME,TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'rc1';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0             |          2 |
| p3             |          0 |
+----------------+------------+
2 rows in set (0.00 sec)

mysql> select * from rc1 partition (p0);
+------+------+
| a    | b    |
+------+------+
|    5 |   10 |
|    5 |   11 |
+------+------+
2 rows in set (0.00 sec)

mysql> select * from rc1 partition (p3);
+------+------+
| a    | b    |
+------+------+
|    5 |   12 |
+------+------+
1 row in set (0.00 sec)

## 此時(shí)，可以看出系統(tǒng)表的table_rows不是精確的，也不是實(shí)時(shí)統(tǒng)計(jì)的。需要要分析一下分區(qū)表
mysql> alter table rc1 analyze partition p3;
+----------+---------+----------+----------+
| Table    | Op      | Msg_type | Msg_text |
+----------+---------+----------+----------+
| test.rc1 | analyze | status   | OK       |
+----------+---------+----------+----------+
1 row in set (0.06 sec)

mysql> SELECT PARTITION_NAME,TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'rc1';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0             |          2 |
| p3             |          1 |
+----------------+------------+
2 rows in set (0.00 sec)

對(duì)多列對(duì)?來(lái)說(shuō)：

mysql> SELECT (5,10) < (5,12), (5,11) < (5,12), (5,12) < (5,12);
+-----------------+-----------------+-----------------+
| (5,10) < (5,12) | (5,11) < (5,12) | (5,12) < (5,12) |
+-----------------+-----------------+-----------------+
|               1 |               1 |               0 |
+-----------------+-----------------+-----------------+
1 row in set (0.00 sec)

當(dāng)然只要保證取值范圍是增?的，表分區(qū)就能創(chuàng)建成功，?如：

CREATE TABLE rc4 ( a INT, b INT, c INT )
 PARTITION BY RANGE COLUMNS(a,b,c)
 ( PARTITION p0 VALUES LESS THAN (0,25,50),
 PARTITION p1 VALUES LESS THAN (10,20,100),
 PARTITION p2 VALUES LESS THAN (10,30,50),
 PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE,MAXVALUE) ); 
Query OK, 0 rows affected (0.14 sec)

但如果取值范圍不是增?的，就會(huì)返回錯(cuò)誤：

CREATE TABLE rcf (a INT, b INT, c INT)
 PARTITION BY RANGE COLUMNS(a,b,c) (
 PARTITION p0 VALUES LESS THAN (0,25,50),
 PARTITION p1 VALUES LESS THAN (20,20,100),
 PARTITION p2 VALUES LESS THAN (10,30,50),
 PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE,MAXVALUE));
ERROR 1493 (HY000): VALUES LESS THAN value must be strictly increasing for each partition

對(duì)其他數(shù)據(jù)類(lèi)型的?持：

mysql> CREATE TABLE employees_by_lname ( id INT NOT NULL, fname VARCHAR(30),
 lname VARCHAR(30), hired DATE NOT NULL DEFAULT '1970-01-01',
 separated DATE NOT NULL DEFAULT '9999-12-31',
 job_code INT NOT NULL, store_id INT NOT NULL )
 PARTITION BY RANGE COLUMNS (lname)
 ( PARTITION p0 VALUES LESS THAN ('g'),
 PARTITION p1 VALUES LESS THAN ('m'),
 PARTITION p2 VALUES LESS THAN ('t'),
 PARTITION p3 VALUES LESS THAN (MAXVALUE) );
Query OK, 0 rows affected (0.16 sec)

mysql> drop table employees_by_lname;
Query OK, 0 rows affected (0.13 sec)

mysql> CREATE TABLE employees_by_lname ( id INT NOT NULL, fname VARCHAR(30),
 lname VARCHAR(30), hired DATE NOT NULL DEFAULT '1970-01-01',
 separated DATE NOT NULL DEFAULT '9999-12-31',
 job_code INT NOT NULL, store_id INT NOT NULL )
 PARTITION BY RANGE COLUMNS (hired)
 ( PARTITION p0 VALUES LESS THAN ('1970-01-01'),
 PARTITION p1 VALUES LESS THAN ('1980-01-01'),
 PARTITION p2 VALUES LESS THAN ('1990-01-01'),
 PARTITION p3 VALUES LESS THAN ('2000-01-01'),
 PARTITION p4 VALUES LESS THAN ('2010-01-01'),
 PARTITION p5 VALUES LESS THAN (MAXVALUE) );
Query OK, 0 rows affected (0.28 sec)

list列表多字段表分區(qū)，例如：你有一個(gè)在12個(gè)城市客戶(hù)的業(yè)務(wù), 為了銷(xiāo)售和市場(chǎng)的目的, 你的組織每3個(gè)城市劃分為一個(gè)區(qū)域針對(duì)LIST COLUMNS分區(qū), 你可以基于城市的名稱(chēng)創(chuàng)建一個(gè)客戶(hù)數(shù)據(jù)表并聲明4個(gè)分區(qū)當(dāng)你的客戶(hù)在對(duì)應(yīng)的這個(gè)區(qū)域:

CREATE TABLE customers_1
 ( first_name VARCHAR(25),
 last_name VARCHAR(25),
 street_1 VARCHAR(30),
 street_2 VARCHAR(30),
 city VARCHAR(15),
 renewal DATE )
 PARTITION BY LIST COLUMNS(city)
 ( PARTITION pRegion_1 VALUES IN('Oskarshamn', 'H?gsby', 'M?nster?s'),
 PARTITION pRegion_2 VALUES IN('Vimmerby', 'Hultsfred', 'V?stervik'),
 PARTITION pRegion_3 VALUES IN('N?ssj?', 'Eksj?', 'Vetlanda'),
 PARTITION pRegion_4 VALUES IN('Uppvidinge', 'Alvesta', 'V?xjo') );

使用日期分區(qū)

CREATE TABLE customers_2 (
    first_name VARCHAR(25),
    last_name VARCHAR(25),
    street_1 VARCHAR(30),
    street_2 VARCHAR(30),
    city VARCHAR(15),
    renewal DATE
)
PARTITION BY LIST COLUMNS(renewal) (
    PARTITION pWeek_1 VALUES IN('2010-02-01', '2010-02-02', '2010-02-03',
        '2010-02-04', '2010-02-05', '2010-02-06', '2010-02-07'),
    PARTITION pWeek_2 VALUES IN('2010-02-08', '2010-02-09', '2010-02-10',
        '2010-02-11', '2010-02-12', '2010-02-13', '2010-02-14'),
    PARTITION pWeek_3 VALUES IN('2010-02-15', '2010-02-16', '2010-02-17',
        '2010-02-18', '2010-02-19', '2010-02-20', '2010-02-21'),
    PARTITION pWeek_4 VALUES IN('2010-02-22', '2010-02-23', '2010-02-24',
        '2010-02-25', '2010-02-26', '2010-02-27', '2010-02-28')
);

但是這種情況在日期增長(zhǎng)到非常大的時(shí)候是很復(fù)雜的, 所以這種還是使用RANGE 分區(qū)方式比較好

$\color{red}{哈希表分區(qū):}$
??按照?個(gè)?定義的函數(shù)返回值來(lái)確定每個(gè)分區(qū)包含的數(shù)據(jù)，這個(gè)?定義函數(shù)也可以?xún)H僅是?個(gè)字段名字
??通過(guò)PARTITION BY HASH (expr)?句來(lái)表達(dá)哈希表分區(qū)，其中的expr表達(dá)式必須返回?個(gè)整數(shù)，基于分區(qū)個(gè)數(shù)的取模（%）運(yùn)算。根據(jù)余數(shù)插?到指定的分區(qū)
??對(duì)哈希表分區(qū)來(lái)說(shuō)只需要定義分區(qū)的個(gè)數(shù)，其他的事情由內(nèi)部完成

mysql> CREATE TABLE employees (
 id INT NOT NULL,
 fname VARCHAR(30),
 lname VARCHAR(30),
 hired DATE NOT NULL DEFAULT '1970-01-01',
 separated DATE NOT NULL DEFAULT '9999-12-31',
 job_code INT,
 store_id INT )
 PARTITION BY HASH(store_id)
 PARTITIONS 4; 
Query OK, 0 rows affected (0.15 sec)

mysql> insert into employees values(1,'a','a',now(),now(),1,1);
Query OK, 1 row affected, 2 warnings (0.07 sec)

mysql> insert into employees values(1,'a','a',now(),now(),1,2);
Query OK, 1 row affected, 2 warnings (0.02 sec)

mysql> insert into employees values(1,'a','a',now(),now(),1,3);
Query OK, 1 row affected, 2 warnings (0.05 sec)

mysql> insert into employees values(1,'a','a',now(),now(),1,4);
Query OK, 1 row affected, 2 warnings (0.04 sec)

mysql> insert into employees values(1,'a','a',now(),now(),1,5);
Query OK, 1 row affected, 2 warnings (0.03 sec)

mysql> select * from employees partition(p0);
+----+-------+-------+------------+------------+----------+----------+
| id | fname | lname | hired      | separated  | job_code | store_id |
+----+-------+-------+------------+------------+----------+----------+
|  1 | a     | a     | 2020-12-29 | 2020-12-29 |        1 |        4 |
+----+-------+-------+------------+------------+----------+----------+
1 row in set (0.00 sec)

mysql> select * from employees partition(p1);
+----+-------+-------+------------+------------+----------+----------+
| id | fname | lname | hired      | separated  | job_code | store_id |
+----+-------+-------+------------+------------+----------+----------+
|  1 | a     | a     | 2020-12-29 | 2020-12-29 |        1 |        1 |
|  1 | a     | a     | 2020-12-29 | 2020-12-29 |        1 |        5 |
+----+-------+-------+------------+------------+----------+----------+
2 rows in set (0.00 sec)

mysql> select * from employees partition(p2);
+----+-------+-------+------------+------------+----------+----------+
| id | fname | lname | hired      | separated  | job_code | store_id |
+----+-------+-------+------------+------------+----------+----------+
|  1 | a     | a     | 2020-12-29 | 2020-12-29 |        1 |        2 |
+----+-------+-------+------------+------------+----------+----------+
1 row in set (0.00 sec)

mysql> select * from employees partition(p3);
+----+-------+-------+------------+------------+----------+----------+
| id | fname | lname | hired      | separated  | job_code | store_id |
+----+-------+-------+------------+------------+----------+----------+
|  1 | a     | a     | 2020-12-29 | 2020-12-29 |        1 |        3 |
+----+-------+-------+------------+------------+----------+----------+
1 row in set (0.00 sec)

如果沒(méi)有寫(xiě)明PARTITIONS字段，則默認(rèn)為1，表達(dá)式可以是整數(shù)類(lèi)型字段，也可以是?個(gè)函數(shù)，?如

CREATE TABLE employees (
 id INT NOT NULL,
 fname VARCHAR(30),
 lname VARCHAR(30),
 hired DATE NOT NULL DEFAULT '1970-01-01',
 separated DATE NOT NULL DEFAULT '9999-12-31',
 job_code INT, store_id INT )
 PARTITION BY HASH( YEAR(hired) )
 PARTITIONS 4;

?如：CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATE) PARTITION BY HASH( YEAR(col3) ) PARTITIONS 4;
如果插??條數(shù)據(jù)對(duì)應(yīng)的col3為‘2005-09-15’時(shí)，則插?數(shù)據(jù)的分區(qū)計(jì)算?法為：

MOD(YEAR('2005-09-01'),4)
 = MOD(2005,4)
 = 1

$\color{red}{key表分區(qū)：}$
??與哈希表分區(qū)類(lèi)似，只不過(guò)哈希表分區(qū)依賴(lài)于?定義的函數(shù)，?key表分區(qū)的哈希算法是依賴(lài)MySQL本身，CREATE TABLE ... PARTITION BY KEY ()創(chuàng)建key表分區(qū)，括號(hào)??可以包含0個(gè)或者多個(gè)字段，所引?的字段必須是主鍵或者主鍵的?部分，如果括號(hào)??沒(méi)有字段，則代表使?主鍵

CREATE TABLE k1 ( id INT NOT NULL PRIMARY KEY, name VARCHAR(20) )
 PARTITION BY KEY()
 PARTITIONS 2;

如果表中沒(méi)有主鍵但有唯?鍵，則使?唯?鍵，但唯?鍵字段必須定義為not null，否則報(bào)錯(cuò)

mysql> CREATE TABLE k1 ( id INT, name VARCHAR(20), UNIQUE KEY (id) ) PARTITION BY KEY() PARTITIONS 2;
ERROR 1488 (HY000): Field in list of fields for partition function not found in table
mysql> CREATE TABLE k1 ( id INT NOT NULL, name VARCHAR(20), UNIQUE KEY (id) ) PARTITION BY KEY() PARTITIONS 2;
Query OK, 0 rows affected (0.09 sec)

所引?的字段未必必須是整數(shù)類(lèi)型，其他的類(lèi)型也可以使?，?如：

mysql> CREATE TABLE tm1 ( s1 CHAR(32) PRIMARY KEY )
 PARTITION BY KEY(s1)
 PARTITIONS 10;
Query OK, 0 rows affected (0.27 sec)

?表分區(qū)，是在表分區(qū)的基礎(chǔ)上再創(chuàng)建表分區(qū)的概念，每個(gè)表分區(qū)下的?表分區(qū)個(gè)數(shù)必須?致，?如：

mysql> CREATE TABLE ts (id INT, purchased DATE)
 PARTITION BY RANGE( YEAR(purchased) )
 SUBPARTITION BY HASH( TO_DAYS(purchased) )
 SUBPARTITIONS 2
 ( PARTITION p0 VALUES LESS THAN (1990),
 PARTITION p1 VALUES LESS THAN (2000),
 PARTITION p2 VALUES LESS THAN MAXVALUE );
Query OK, 0 rows affected (0.26 sec)

ts表?yè)碛腥齻€(gè)范圍分區(qū)，同時(shí)每個(gè)分區(qū)都各?有兩個(gè)?分區(qū)，所以總共有6個(gè)分區(qū)

?表分區(qū)必須是范圍/列表分區(qū)+哈希/key?表分區(qū)的組合
?表分區(qū)也可以顯示的指定?表分區(qū)的名字，?如：

mysql> CREATE TABLE ts (id INT, purchased DATE)
 PARTITION BY RANGE( YEAR(purchased) )
 SUBPARTITION BY HASH( TO_DAYS(purchased) )
 ( PARTITION p0 VALUES LESS THAN (1990)
 ( SUBPARTITION s0, SUBPARTITION s1 ),
 PARTITION p1 VALUES LESS THAN (2000)
 ( SUBPARTITION s2, SUBPARTITION s3 ),
 PARTITION p2 VALUES LESS THAN MAXVALUE
 ( SUBPARTITION s4, SUBPARTITION s5 ) );
Query OK, 0 rows affected (0.22 sec)

MySQL表分區(qū)對(duì)Null值處理

不同的表分區(qū)對(duì)NULL值的處理?式不同
對(duì)范圍表分區(qū)來(lái)說(shuō)，如果插?的是NULL值，則將數(shù)據(jù)放到最?的分區(qū)表?

mysql> CREATE TABLE t1 ( c1 INT, c2 VARCHAR(20) )
 PARTITION BY RANGE(c1)
 ( PARTITION p0 VALUES LESS THAN (0),
 PARTITION p1 VALUES LESS THAN (10),
 PARTITION p2 VALUES LESS THAN MAXVALUE );
Query OK, 0 rows affected (0.20 sec)

mysql> INSERT INTO t1 VALUES (NULL, 'mothra');
Query OK, 1 row affected (0.02 sec)

mysql> select * from t1 partition(p0);
+------+--------+
| c1   | c2     |
+------+--------+
| NULL | mothra |
+------+--------+
1 row in set (0.00 sec)

對(duì)list表分區(qū)來(lái)說(shuō)，?持NULL值的唯?情況就是某個(gè)分區(qū)的允許值中包含NULL

mysql> CREATE TABLE ts1 (c1 INT, c2 VARCHAR(20))
 PARTITION BY LIST(c1)
 (PARTITION p0 VALUES IN (0, 3, 6),
 PARTITION p1 VALUES IN (1, 4, 7),
 PARTITION p2 VALUES IN (2, 5, 8));
Query OK, 0 rows affected (0.17 sec)

mysql> INSERT INTO ts1 VALUES (9, 'mothra');
ERROR 1526 (HY000): Table has no partition for value 9
mysql> INSERT INTO ts1 VALUES (NULL, 'mothra');
ERROR 1526 (HY000): Table has no partition for value NULL

mysql> CREATE TABLE ts2 (c1 INT, c2 VARCHAR(20))
 PARTITION BY LIST(c1)
 (PARTITION p0 VALUES IN (0, 3, 6),
 PARTITION p1 VALUES IN (1, 4, 7),
 PARTITION p2 VALUES IN (2, 5, 8),
 PARTITION p3 VALUES IN (NULL));
Query OK, 0 rows affected (0.19 sec)

mysql> CREATE TABLE ts3 (c1 INT, c2 VARCHAR(20))
 PARTITION BY LIST(c1)
 (PARTITION p0 VALUES IN (0, 3, 6),
 PARTITION p1 VALUES IN (1, 4, 7, NULL),
 PARTITION p2 VALUES IN (2, 5, 8));
Query OK, 0 rows affected (0.14 sec)

mysql> INSERT INTO ts2 VALUES (NULL, 'mothra');
Query OK, 1 row affected (0.03 sec)

mysql> INSERT INTO ts3 VALUES (NULL, 'mothra');
Query OK, 1 row affected (0.02 sec)

對(duì)哈希表分區(qū)和Key表分區(qū)來(lái)說(shuō)，NULL值會(huì)被當(dāng)成0值對(duì)待

mysql> CREATE TABLE th ( c1 INT, c2 VARCHAR(20) )
 PARTITION BY HASH(c1)
 PARTITIONS 2;
Query OK, 0 rows affected (0.13 sec)

mysql> INSERT INTO th VALUES (NULL, 'mothra'), (0, 'gigan');
Query OK, 2 rows affected (0.05 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from th partition(p0);
+------+--------+
| c1   | c2     |
+------+--------+
| NULL | mothra |
|    0 | gigan  |
+------+--------+
2 rows in set (0.00 sec)

MySQL表分區(qū)管理

通過(guò)alter table命令可以執(zhí)?增加，刪除，重新定義，合并或者拆分表分區(qū)的管理動(dòng)作
對(duì)范圍表分區(qū)和列表表分區(qū)來(lái)說(shuō)，刪除?個(gè)表分區(qū)命令如下：

mysql> CREATE TABLE tr
 (id INT,
 name VARCHAR(50),
 purchased DATE)
 PARTITION BY RANGE( YEAR(purchased) )
 ( PARTITION p0 VALUES LESS THAN (1990),
 PARTITION p1 VALUES LESS THAN (1995),
 PARTITION p2 VALUES LESS THAN (2000),
 PARTITION p3 VALUES LESS THAN (2005),
 PARTITION p4 VALUES LESS THAN (2010),
 PARTITION p5 VALUES LESS THAN (2015) ); 
Query OK, 0 rows affected (0.26 sec)

mysql> insert into tr values(1, 'abc','1999-12-21');
Query OK, 1 row affected (0.09 sec)

mysql> select * from tr partition(p2);
+------+------+------------+
| id   | name | purchased  |
+------+------+------------+
|    1 | abc  | 1999-12-21 |
+------+------+------------+
1 row in set (0.00 sec)

mysql> ALTER TABLE tr DROP PARTITION p2;
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from tr partition(p2);
ERROR 1735 (HY000): Unknown partition 'p2' in table 'tr'

刪除表分區(qū)的動(dòng)作不光會(huì)把分區(qū)刪掉，也會(huì)把表分區(qū)?原來(lái)的數(shù)據(jù)給刪除掉

mysql> show create table tr;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tr    | CREATE TABLE `tr` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `purchased` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
/*!50100 PARTITION BY RANGE (year(`purchased`))
(PARTITION p0 VALUES LESS THAN (1990) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (1995) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (2005) ENGINE = InnoDB,
 PARTITION p4 VALUES LESS THAN (2010) ENGINE = InnoDB,
 PARTITION p5 VALUES LESS THAN (2015) ENGINE = InnoDB) */ |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM tr WHERE purchased BETWEEN '1995-01-01' AND '1999-12-31';                                                                                                                                                                                                 
Empty set (0.00 sec)

在原分區(qū)上增加?個(gè)表分區(qū)可以通過(guò)alter table … add partition語(yǔ)句來(lái)完成

mysql> CREATE TABLE members (
 id INT, fname VARCHAR(25),
 lname VARCHAR(25),
 dob DATE ) 
 PARTITION BY RANGE( YEAR(dob) )
 ( PARTITION p0 VALUES LESS THAN (1980),
 PARTITION p1 VALUES LESS THAN (1990),
 PARTITION p2 VALUES LESS THAN (2000) );
Query OK, 0 rows affected (0.14 sec)

mysql> ALTER TABLE members ADD PARTITION (PARTITION p3 VALUES LESS THAN (2010));
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into members values(1,'a','b','1978-01-01');
Query OK, 1 row affected (0.03 sec)

但對(duì)范圍表分區(qū)來(lái)說(shuō)，增加的表分區(qū)必須在尾部增加，在頭部或者在中間增加都會(huì)失?。?/em>

mysql> ALTER TABLE members ADD PARTITION ( PARTITION n VALUES LESS THAN (1970)); ERROR 1493 (HY000): VALUES LESS THAN value must be strictly increasing for each partition

為解決這個(gè)問(wèn)題，可以使?REORGANIZE命令：

mysql> show create table members; +---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | members | CREATE TABLE `members` ( `id` int(11) DEFAULT NULL, `fname` varchar(25) COLLATE utf8mb4_general_ci DEFAULT NULL, `lname` varchar(25) COLLATE utf8mb4_general_ci DEFAULT NULL, `dob` date DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci /*!50100 PARTITION BY RANGE (year(`dob`)) (PARTITION p0 VALUES LESS THAN (1980) ENGINE = InnoDB, PARTITION p1 VALUES LESS THAN (1990) ENGINE = InnoDB, PARTITION p2 VALUES LESS THAN (2000) ENGINE = InnoDB, PARTITION p3 VALUES LESS THAN (2010) ENGINE = InnoDB) */ | +---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> ALTER TABLE members REORGANIZE PARTITION p0 INTO ( PARTITION n0 VALUES LESS THAN (1970), PARTITION n1 VALUES LESS THAN (1980) ); Query OK, 0 rows affected (0.17 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table members; +---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | members | CREATE TABLE `members` ( `id` int(11) DEFAULT NULL, `fname` varchar(25) COLLATE utf8mb4_general_ci DEFAULT NULL, `lname` varchar(25) COLLATE utf8mb4_general_ci DEFAULT NULL, `dob` date DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci /*!50100 PARTITION BY RANGE (year(`dob`)) (PARTITION n0 VALUES LESS THAN (1970) ENGINE = InnoDB, PARTITION n1 VALUES LESS THAN (1980) ENGINE = InnoDB, PARTITION p1 VALUES LESS THAN (1990) ENGINE = InnoDB, PARTITION p2 VALUES LESS THAN (2000) ENGINE = InnoDB, PARTITION p3 VALUES LESS THAN (2010) ENGINE = InnoDB) */ | +---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> select * from members; +------+-------+-------+------------+ | id | fname | lname | dob | +------+-------+-------+------------+ | 1 | a | b | 1978-01-01 | +------+-------+-------+------------+ 1 row in set (0.00 sec) mysql> select * from members partition(n1); +------+-------+-------+------------+ | id | fname | lname | dob | +------+-------+-------+------------+ | 1 | a | b | 1978-01-01 | +------+-------+-------+------------+ 1 row in set (0.00 sec)

對(duì)列表表分區(qū)來(lái)說(shuō)，只要新增加的分區(qū)對(duì)應(yīng)的值在之前的表分區(qū)中沒(méi)有出現(xiàn)過(guò)，就可以通過(guò)alter table… add partition來(lái)增加

mysql> CREATE TABLE tt ( id INT, data INT ) PARTITION BY LIST(data) ( PARTITION p0 VALUES IN (5, 10, 15), PARTITION p1 VALUES IN (6, 12, 18) ); Query OK, 0 rows affected (0.17 sec) mysql> ALTER TABLE tt ADD PARTITION (PARTITION p2 VALUES IN (7, 14, 21)); Query OK, 0 rows affected (0.19 sec) Records: 0 Duplicates: 0 Warnings: 0

當(dāng)然，也可以通過(guò)REORGANIZE命令將之前的多個(gè)分區(qū)合并成?個(gè)或?個(gè)分區(qū)，但要保持分區(qū)值?致：

mysql> ALTER TABLE members REORGANIZE PARTITION n0,n1 INTO ( PARTITION p0 VALUES LESS THAN (1970) ); ERROR 1520 (HY000): Reorganize of range partitions cannot change total ranges except for last partition where it can extend the range mysql> ALTER TABLE members REORGANIZE PARTITION n0,n1 INTO ( PARTITION p0 VALUES LESS THAN (1985) ); ERROR 1520 (HY000): Reorganize of range partitions cannot change total ranges except for last partition where it can extend the range mysql> ALTER TABLE members REORGANIZE PARTITION n0,n1 INTO ( PARTITION p0 VALUES LESS THAN (1980) ); Query OK, 0 rows affected (0.19 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table members; +---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | members | CREATE TABLE `members` ( `id` int(11) DEFAULT NULL, `fname` varchar(25) COLLATE utf8mb4_general_ci DEFAULT NULL, `lname` varchar(25) COLLATE utf8mb4_general_ci DEFAULT NULL, `dob` date DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci /*!50100 PARTITION BY RANGE (year(`dob`)) (PARTITION p0 VALUES LESS THAN (1980) ENGINE = InnoDB, PARTITION p1 VALUES LESS THAN (1990) ENGINE = InnoDB, PARTITION p2 VALUES LESS THAN (2000) ENGINE = InnoDB, PARTITION p3 VALUES LESS THAN (2010) ENGINE = InnoDB) */ | +---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)

更復(fù)雜的?如將多個(gè)分區(qū)重組成多個(gè)分區(qū)：

mysql> ALTER TABLE members REORGANIZE PARTITION p0,p1,p2,p3 INTO ( PARTITION m0 VALUES LESS -> THAN (1980), PARTITION m1 VALUES LESS THAN (2010)); Query OK, 0 rows affected (0.18 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table members; +---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | members | CREATE TABLE `members` ( `id` int(11) DEFAULT NULL, `fname` varchar(25) COLLATE utf8mb4_general_ci DEFAULT NULL, `lname` varchar(25) COLLATE utf8mb4_general_ci DEFAULT NULL, `dob` date DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci /*!50100 PARTITION BY RANGE (year(`dob`)) (PARTITION m0 VALUES LESS THAN (1980) ENGINE = InnoDB, PARTITION m1 VALUES LESS THAN (2010) ENGINE = InnoDB) */ | +---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.01 sec)

對(duì)列表分區(qū)來(lái)說(shuō)，重新組織的分區(qū)必須是相鄰的分區(qū)

mysql> show create table tt; +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | tt | CREATE TABLE `tt` ( `id` int(11) DEFAULT NULL, `data` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci /*!50100 PARTITION BY LIST (`data`) (PARTITION p0 VALUES IN (5,10,15) ENGINE = InnoDB, PARTITION p1 VALUES IN (6,12,18) ENGINE = InnoDB, PARTITION p2 VALUES IN (7,14,21) ENGINE = InnoDB) */ | +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> ALTER TABLE tt ADD PARTITION (PARTITION np VALUES IN (4, 8)); Query OK, 0 rows affected (0.15 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE tt REORGANIZE PARTITION p1,np INTO ( PARTITION p1 VALUES IN (6, 18), PARTITION np VALUES in (4, 8, 12) ); ERROR 1519 (HY000): When reorganizing a set of partitions they must be in consecutive order mysql> show create table tt; +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | tt | CREATE TABLE `tt` ( `id` int(11) DEFAULT NULL, `data` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci /*!50100 PARTITION BY LIST (`data`) (PARTITION p0 VALUES IN (5,10,15) ENGINE = InnoDB, PARTITION p1 VALUES IN (6,12,18) ENGINE = InnoDB, PARTITION p2 VALUES IN (7,14,21) ENGINE = InnoDB, PARTITION np VALUES IN (4,8) ENGINE = InnoDB) */ | +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> insert into tt values(1,10),(2,5); Query OK, 2 rows affected (0.04 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from tt; +------+------+ | id | data | +------+------+ | 1 | 10 | | 2 | 5 | +------+------+ 2 rows in set (0.00 sec)

如果表?已有的數(shù)據(jù)在新重組的分區(qū)中沒(méi)有指定的值，則數(shù)據(jù)會(huì)丟失

mysql> ALTER TABLE tt REORGANIZE PARTITION p0,p1 INTO ( PARTITION p0 VALUES IN (6, 18), PARTITION p1 -> VALUES in (5,15)); Query OK, 0 rows affected (0.19 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> select * from tt; +------+------+ | id | data | +------+------+ | 2 | 5 | +------+------+ 1 row in set (0.00 sec)

??對(duì)哈希表分區(qū)和KEY表分區(qū)的管理?段與范圍和列表表分區(qū)完全不同，?如不能刪除表分區(qū)，但可以通過(guò)ALTER TABLE ... COALESCE PARTITION語(yǔ)句合并表分區(qū)，其partition后?的數(shù)字代表縮減的個(gè)數(shù)，?不是縮減到的個(gè)數(shù)

mysql> CREATE TABLE clients ( id INT, fname VARCHAR(30), lname VARCHAR(30), signed DATE ) PARTITION BY HASH( MONTH(signed) ) PARTITIONS 12; Query OK, 0 rows affected (0.41 sec) mysql> insert into clients values(1,'a','a','2017-01-01'),(2,'a','a','2017-02-01'),(3,'a','a','2017-03-01'),(4,'a','a','2017-04-01'); Query OK, 4 rows affected (0.03 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE clients COALESCE PARTITION 4; Query OK, 0 rows affected (0.68 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> analyze table clients; +--------------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +--------------+---------+----------+----------+ | test.clients | analyze | status | OK | +--------------+---------+----------+----------+ 1 row in set (0.17 sec) mysql> select partition_name,table_rows from information_schema.partitions where table_name='clients'; +----------------+------------+ | PARTITION_NAME | TABLE_ROWS | +----------------+------------+ | p0 | 0 | | p1 | 1 | | p2 | 1 | | p3 | 1 | | p4 | 1 | | p5 | 0 | | p6 | 0 | | p7 | 0 | +----------------+------------+ 8 rows in set (0.00 sec)

對(duì)于哈希表分區(qū)和key表分區(qū)，如果是增加表分區(qū)，則可以使?add partition語(yǔ)句

mysql> ALTER TABLE clients ADD PARTITION PARTITIONS 6; Query OK, 0 rows affected (0.76 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> analyze table clients; +--------------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +--------------+---------+----------+----------+ | test.clients | analyze | status | OK | +--------------+---------+----------+----------+ 1 row in set (0.13 sec) mysql> select partition_name,table_rows from information_schema.partitions where table_name='clients'; +----------------+------------+ | PARTITION_NAME | TABLE_ROWS | +----------------+------------+ | p0 | 0 | | p1 | 1 | | p10 | 0 | | p11 | 0 | | p12 | 0 | | p13 | 0 | | p2 | 1 | | p3 | 1 | | p4 | 1 | | p5 | 0 | | p6 | 0 | | p7 | 0 | | p8 | 0 | | p9 | 0 | +----------------+------------+ 14 rows in set (0.01 sec)

??對(duì)分區(qū)表可以通過(guò)ALTER TABLE pt EXCHANGE PARTITION p WITH TABLE nt命令將?個(gè)分區(qū)或者是?分區(qū)的數(shù)據(jù)與普通的表的數(shù)據(jù)相互交換，其本身的表結(jié)構(gòu)不會(huì)變化
??交換的分區(qū)表和?標(biāo)表必須結(jié)構(gòu)完全相同，包括字段，類(lèi)型，索引，存儲(chǔ)引擎必須完全樣

mysql> CREATE TABLE e ( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30) ) PARTITION BY RANGE (id) ( PARTITION p0 VALUES LESS THAN (50), PARTITION p1 VALUES LESS THAN (100), PARTITION p2 VALUES LESS THAN (150), PARTITION p3 VALUES LESS THAN (MAXVALUE) ); Query OK, 0 rows affected (0.18 sec) mysql> CREATE TABLE e2 LIKE e; Query OK, 0 rows affected (0.17 sec) mysql> insert into e(id,fname) values(10,'a'),(20,'b'),(170,'c'),(180,'d'),(190,'e'); Query OK, 5 rows affected (0.05 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE e2 REMOVE PARTITIONING; Query OK, 0 rows affected (0.22 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2; Query OK, 0 rows affected (0.13 sec) mysql> SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'e'; +----------------+------------+ | PARTITION_NAME | TABLE_ROWS | +----------------+------------+ | p0 | 0 | | p1 | 0 | | p2 | 0 | | p3 | 3 | +----------------+------------+ 4 rows in set (0.01 sec) mysql> select * from e2; +----+-------+-------+ | id | fname | lname | +----+-------+-------+ | 10 | a | NULL | | 20 | b | NULL | +----+-------+-------+ 2 rows in set (0.00 sec) mysql> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2;##再次執(zhí)?后數(shù)據(jù)交換回來(lái) Query OK, 0 rows affected (0.08 sec) mysql> select * from e; +-----+-------+-------+ | id | fname | lname | +-----+-------+-------+ | 10 | a | NULL | | 20 | b | NULL | | 170 | c | NULL | | 180 | d | NULL | | 190 | e | NULL | +-----+-------+-------+ 5 rows in set (0.01 sec) mysql> select * from e2; Empty set (0.00 sec) mysql> create table e3(id int, fname varchar(30),lname2 varchar(30)); Query OK, 0 rows affected (0.14 sec) mysql> alter table e exchange partition p3 with table e3; ERROR 1736 (HY000): Tables have different definitions mysql> drop table e3; Query OK, 0 rows affected (0.07 sec) mysql> create table e3(id int not null, fname varchar(30),lname varchar(32)); Query OK, 0 rows affected (0.10 sec) mysql> alter table e exchange partition p3 with table e3; ERROR 1736 (HY000): Tables have different definitions

??執(zhí)?exchange命令時(shí)，?標(biāo)表?不?定是空數(shù)據(jù)，如果有數(shù)據(jù)需要保證??的數(shù)據(jù)符合表分區(qū)的條件，否則只能?WITHOUT VALIDATION來(lái)跳過(guò)驗(yàn)證環(huán)節(jié)

mysql> INSERT INTO e2 VALUES (51, "Ellen", "McDonald"); Query OK, 1 row affected (0.07 sec) mysql> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLEe2; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TABLEe2' at line 1 mysql> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2 WITHOUT VALIDATION; Query OK, 0 rows affected (0.08 sec)

對(duì)?表分區(qū)也可以執(zhí)?exchange命令：

mysql> CREATE TABLE es ( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30) ) PARTITION BY RANGE (id) SUBPARTITION BY KEY (lname) SUBPARTITIONS 2 ( PARTITION p0 VALUES LESS THAN (50), PARTITION p1 VALUES LESS THAN (100), PARTITION p2 VALUES LESS THAN (150), PARTITION p3 VALUES LESS THAN (MAXVALUE) ); Query OK, 0 rows affected (0.34 sec) mysql> CREATE TABLE es2 LIKE es; Query OK, 0 rows affected (0.27 sec) mysql> ALTER TABLE es2 REMOVE PARTITIONING; Query OK, 0 rows affected (0.40 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE es EXCHANGE PARTITION p3sp0 WITH TABLE es2; Query OK, 0 rows affected (0.14 sec)

當(dāng)表是有?表分區(qū)時(shí)，只能exchange?個(gè)?表分區(qū)，?不能交換整個(gè)分區(qū)

mysql> SELECT PARTITION_NAME, SUBPARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'es'; +----------------+-------------------+------------+ | PARTITION_NAME | SUBPARTITION_NAME | TABLE_ROWS | +----------------+-------------------+------------+ | p0 | p0sp0 | 0 | | p0 | p0sp1 | 0 | | p1 | p1sp0 | 0 | | p1 | p1sp1 | 0 | | p2 | p2sp0 | 0 | | p2 | p2sp1 | 0 | | p3 | p3sp0 | 0 | | p3 | p3sp1 | 0 | +----------------+-------------------+------------+ 8 rows in set (0.00 sec) mysql> ALTER TABLE es EXCHANGE PARTITION p3 WITH TABLE es2; ERROR 1734 (HY000): Subpartitioned table, use subpartition instead of partition

當(dāng)需要去除分區(qū)碎?是，可以執(zhí)?rebuild命令，相當(dāng)于刪除數(shù)據(jù)之后重新插?

mysql> ALTER TABLE t1 REBUILD PARTITION p0, p1; Query OK, 0 rows affected (0.23 sec) Records: 0 Duplicates: 0 Warnings: 0

也可以執(zhí)?OPTIMIZE命令回收分區(qū)中未使?的空間和重新獲取統(tǒng)計(jì)資料

mysql> ALTER TABLE t1 OPTIMIZE PARTITION p0, p1; +---------+----------+----------+---------------------------------------------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +---------+----------+----------+---------------------------------------------------------------------------------------------+ | test.t1 | optimize | note | Table does not support optimize on partitions. All partitions will be rebuilt and analyzed. | | test.t1 | optimize | status | OK | +---------+----------+----------+---------------------------------------------------------------------------------------------+ 2 rows in set (0.35 sec)

Analyzing partitions命令重新收集分區(qū)統(tǒng)計(jì)資料

mysql> show create table t1; +-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | t1 | CREATE TABLE `t1` ( `c1` int(11) DEFAULT NULL, `c2` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci /*!50100 PARTITION BY RANGE (`c1`) (PARTITION p0 VALUES LESS THAN (0) ENGINE = InnoDB, PARTITION p1 VALUES LESS THAN (10) ENGINE = InnoDB, PARTITION p2 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */ | +-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> ALTER TABLE t1 ANALYZE PARTITION p3; +---------+---------+----------+----------------------------------------+ | Table | Op | Msg_type | Msg_text | +---------+---------+----------+----------------------------------------+ | test.t1 | analyze | Error | Error in list of partitions to test.t1 | | test.t1 | analyze | status | Operation failed | +---------+---------+----------+----------------------------------------+ 2 rows in set (0.01 sec) mysql> ALTER TABLE t1 ANALYZE PARTITION p2; +---------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +---------+---------+----------+----------+ | test.t1 | analyze | status | OK | +---------+---------+----------+----------+ 1 row in set (0.02 sec)

Repairing partitions命令修復(fù)異常的分區(qū)

mysql> ALTER TABLE t1 REPAIR PARTITION p0,p1; +---------+--------+----------+----------+ | Table | Op | Msg_type | Msg_text | +---------+--------+----------+----------+ | test.t1 | repair | status | OK | +---------+--------+----------+----------+ 1 row in set (0.01 sec)

Checking partitions命令檢查分區(qū)中數(shù)據(jù)或者索引數(shù)據(jù)是否損壞

mysql> ALTER TABLE t1 CHECK PARTITION p1; +---------+-------+----------+----------+ | Table | Op | Msg_type | Msg_text | +---------+-------+----------+----------+ | test.t1 | check | status | OK | +---------+-------+----------+----------+ 1 row in set (0.00 sec)

ALTER TABLE ... TRUNCATE PARTITION命令?來(lái)刪除分區(qū)中的所有數(shù)據(jù)

mysql> select * from e partition(p0); +----+-------+----------+ | id | fname | lname | +----+-------+----------+ | 51 | Ellen | McDonald | +----+-------+----------+ 1 row in set (0.00 sec) mysql> alter table e truncate partition p0; Query OK, 0 rows affected (0.16 sec) mysql> select * from e partition(p0); Empty set (0.00 sec)

獲取表的分區(qū)信息有如下?種：

通過(guò)show create table命令：

mysql> show create table e; +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | e | CREATE TABLE `e` ( `id` int(11) NOT NULL, `fname` varchar(30) COLLATE utf8mb4_general_ci DEFAULT NULL, `lname` varchar(30) COLLATE utf8mb4_general_ci DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci /*!50100 PARTITION BY RANGE (`id`) (PARTITION p0 VALUES LESS THAN (50) ENGINE = InnoDB, PARTITION p1 VALUES LESS THAN (100) ENGINE = InnoDB, PARTITION p2 VALUES LESS THAN (150) ENGINE = InnoDB, PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */ | +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)

通過(guò)show table status命令來(lái)查看表是否是分區(qū)表，Create_options項(xiàng)

mysql> show table status like 'e'; +------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+ | e | InnoDB | 10 | Dynamic | 3 | 21845 | 65536 | 0 | 0 | 0 | NULL | 2020-12-30 20:05:04 | 2020-12-30 20:05:14 | NULL | utf8mb4_general_ci | NULL | partitioned | | +------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+ 1 row in set (0.01 sec)

通過(guò)information_schema.partitions系統(tǒng)表來(lái)查看分區(qū)表的具體信息

mysql> select * from information_schema.partitions where table_name='e'; +---------------+--------------+------------+----------------+-------------------+----------------------------+-------------------------------+------------------+---------------------+----------------------+-------------------------+-----------------------+------------+----------------+-------------+-----------------+--------------+-----------+---------------------+---------------------+------------+----------+-------------------+-----------+-----------------+ | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | PARTITION_NAME | SUBPARTITION_NAME | PARTITION_ORDINAL_POSITION | SUBPARTITION_ORDINAL_POSITION | PARTITION_METHOD | SUBPARTITION_METHOD | PARTITION_EXPRESSION | SUBPARTITION_EXPRESSION | PARTITION_DESCRIPTION | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | MAX_DATA_LENGTH | INDEX_LENGTH | DATA_FREE | CREATE_TIME | UPDATE_TIME | CHECK_TIME | CHECKSUM | PARTITION_COMMENT | NODEGROUP | TABLESPACE_NAME | +---------------+--------------+------------+----------------+-------------------+----------------------------+-------------------------------+------------------+---------------------+----------------------+-------------------------+-----------------------+------------+----------------+-------------+-----------------+--------------+-----------+---------------------+---------------------+------------+----------+-------------------+-----------+-----------------+ | def | test | e | p0 | NULL | 1 | NULL | RANGE | NULL | `id` | NULL | 50 | 0 | 0 | 16384 | 0 | 0 | 0 | 2020-12-30 20:05:04 | NULL | NULL | NULL | | default | NULL | | def | test | e | p1 | NULL | 2 | NULL | RANGE | NULL | `id` | NULL | 100 | 0 | 0 | 16384 | 0 | 0 | 0 | 2020-12-30 20:05:04 | NULL | NULL | NULL | | default | NULL | | def | test | e | p2 | NULL | 3 | NULL | RANGE | NULL | `id` | NULL | 150 | 0 | 0 | 16384 | 0 | 0 | 0 | 2020-12-30 20:05:04 | NULL | NULL | NULL | | default | NULL | | def | test | e | p3 | NULL | 4 | NULL | RANGE | NULL | `id` | NULL | MAXVALUE | 3 | 5461 | 16384 | 0 | 0 | 0 | 2020-12-30 20:05:04 | 2020-12-30 20:05:14 | NULL | NULL | | default | NULL | +---------------+--------------+------------+----------------+-------------------+----------------------------+-------------------------------+------------------+---------------------+----------------------+-------------------------+-----------------------+------------+----------------+-------------+-----------------+--------------+-----------+---------------------+---------------------+------------+----------+-------------------+-----------+-----------------+ 4 rows in set (0.00 sec)

MySQL表分區(qū)修剪

表分區(qū)修剪是MySQL優(yōu)化的?種，其核?就是只掃描需要的分區(qū)
?如：

mysql> CREATE TABLE t1 ( fname VARCHAR(50) NOT NULL, lname VARCHAR(50) NOT NULL, region_code TINYINT UNSIGNED NOT NULL, dob DATE NOT NULL ) PARTITION BY RANGE( region_code ) ( PARTITION p0 VALUES LESS THAN (64), PARTITION p1 VALUES LESS THAN (128), PARTITION p2 VALUES LESS THAN (192), PARTITION p3 VALUES LESS THAN MAXVALUE ); Query OK, 0 rows affected (0.14 sec) mysql> explain SELECT fname, lname, region_code, dob FROM t1 WHERE region_code > 125 AND region_code < 130; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | t1 | p1,p2 | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.01 sec)

此查詢(xún)只需要掃描P1和P2兩個(gè)分區(qū)就能得到結(jié)果，從?獲得額外的性能提升
不光是select語(yǔ)句可以被使?表分區(qū)修剪，update和delete語(yǔ)句也可以使?

MySQL表分區(qū)修剪

表分區(qū)選擇和表分區(qū)修剪類(lèi)似，只不過(guò)修剪是?動(dòng)實(shí)現(xiàn)的，?表分區(qū)選擇是現(xiàn)實(shí)的指定分區(qū)范圍
表分區(qū)選擇不僅?持select語(yǔ)句，也?持update，insert，delete等語(yǔ)句

CREATE TABLE employees ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, fname VARCHAR(25) NOT NULL, lname VARCHAR(25) NOT NULL, store_id INT NOT NULL, department_id INT NOT NULL ) PARTITION BY RANGE(id) ( PARTITION p0 VALUES LESS THAN (5), PARTITION p1 VALUES LESS THAN (10), PARTITION p2 VALUES LESS THAN (15), PARTITION p3 VALUES LESS THAN MAXVALUE ); mysql> SELECT * FROM employees PARTITION (p1); # 從分區(qū)p1下查找 +----+-------+--------+----------+---------------+ | id | fname | lname | store_id | department_id | +----+-------+--------+----------+---------------+ | 5 | Mary | Jones | 1 | 1 | | 6 | Linda | Black | 2 | 3 | | 7 | Ed | Jones | 2 | 1 | | 8 | June | Wilson | 3 | 1 | | 9 | Andy | Smith | 1 | 3 | +----+-------+--------+----------+---------------+ mysql> SELECT * FROM employees PARTITION (p0, p2) WHERE lname LIKE 'S%'; # 從分區(qū)p0、p2下查找 +----+-------+-------+----------+---------------+ | id | fname | lname | store_id | department_id | +----+-------+-------+----------+---------------+ | 4 | Jim | Smith | 2 | 4 | | 11 | Jill | Stone | 1 | 4 | +----+-------+-------+----------+---------------+ mysql> SELECT e.id AS 'Employee ID', CONCAT(e.fname, ' ', e.lname) AS Name, s.city AS City, d.name AS department FROM employees AS e JOIN stores PARTITION (p1) AS s ON e.store_id=s.id JOIN departments PARTITION (p0) AS d ON e.department_id=d.id ORDER BY e.lname; +-------------+---------------+-----------+------------+ | Employee ID | Name | City | department | +-------------+---------------+-----------+------------+ | 14 | Fred Goldberg | Bellingen | Delivery | | 5 | Mary Jones | Nambucca | Sales | | 17 | Mark Morgan | Bellingen | Delivery | | 9 | Andy Smith | Nambucca | Delivery | | 8 | June Wilson | Bellingen | Sales | +-------------+---------------+-----------+------------+ mysql> DELETE FROM employees PARTITION (p0, p1) WHERE fname LIKE 'j%'; Query OK, 2 rows affected (0.09 sec) mysql> UPDATE employees PARTITION (p0) SET store_id = 2 WHERE fname = 'Jill'; Query OK, 0 rows affected (0.00 sec) Rows matched: 0 Changed: 0 Warnings: 0 mysql> INSERT INTO employees PARTITION (p2) VALUES (20, 'Jan', 'Jones', 1, 3); ERROR 1729 (HY000): Found a row not matching the given partition set mysql> INSERT INTO employees PARTITION (p3) VALUES (20, 'Jan', 'Jones', 1, 3); Query OK, 1 row affected (0.07 sec)

MySQL表分區(qū)函數(shù)

表分區(qū)函數(shù)可以使?的函數(shù)包含以下這些：

色偷偷精品伊人,欧洲久久精品,欧美综合婷婷骚逼,国产AV主播,国产最新探花在线,九色在线视频一区,伊人大交九欧美,1769亚洲,黄色成人av

十、MySQL表分區(qū)

十、MySQL表分區(qū)

MySQL表分區(qū)介紹

MySQL表分區(qū)對(duì)Null值處理

MySQL表分區(qū)管理

MySQL表分區(qū)修剪

MySQL表分區(qū)修剪

MySQL表分區(qū)函數(shù)

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容

色偷偷精品伊人,欧洲久久精品,欧美综合婷婷骚逼,国产AV主播,国产最新探花在线,九色在线视频一区,伊人大交九 欧美,1769亚洲,黄色成人av

十、MySQL表分區(qū)

MySQL表分區(qū)介紹

MySQL表分區(qū)對(duì)Null值處理

MySQL表分區(qū)管理

MySQL表分區(qū)修剪

MySQL表分區(qū)修剪

MySQL表分區(qū)函數(shù)

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容

色偷偷精品伊人,欧洲久久精品,欧美综合婷婷骚逼,国产AV主播,国产最新探花在线,九色在线视频一区,伊人大交九欧美,1769亚洲,黄色成人av

十、MySQL表分區(qū)