溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務(wù)條款》
您好，登錄后才能下訂單哦！
MysqlPartition topic study
When partitioning by KEY or LINEAR KEY, youcan use a DATE, TIME, or DATETIME column as the
partitioning column without performing any modification of the column value. Forexample, this table creation statement is perfectly valid in MySQL:
如果考慮不轉(zhuǎn)換列值，可采取key進(jìn)行分區(qū)。
CREATE TABLE members(
firstname VARCHAR(25)NOT NULL,
lastname VARCHAR(25)NOT NULL,
username VARCHAR(16)NOT NULL,
email VARCHAR(35),
joined DATE NOT NULL
)
PARTITION BYKEY(joined)
PARTITIONS 6;
這樣分區(qū)，不用轉(zhuǎn)換列值。
In MySQL 5.7, it is also possible to use aDATE or DATETIME column as the partitioning column using RANGE COLUMNS and LISTCOLUMNS partitioning. MySQL's other partitioning types, however, require apartitioning expression that yields an integer value or NULL. If you wish touse date-based partitioning by RANGE, LIST, HASH, or LINEAR HASH, you can simplyemploy a function that operates on a DATE, TIME, or DATETIME column and returnssuch a value, as shown here:
如果想使用基于日期RANGE, LIST, HASH, or LINEAR HASH的分區(qū)，可使用時間函數(shù)進(jìn)行分區(qū)。
CREATE TABLE members(
firstname VARCHAR(25)NOT NULL,
lastname VARCHAR(25)NOT NULL,
username VARCHAR(16)NOT NULL,
email VARCHAR(35),
joined DATE NOT NULL
)
PARTITION BY RANGE(YEAR(joined) ) (
PARTITION p0 VALUESLESS THAN (1960),
PARTITION p1 VALUESLESS THAN (1970),
PARTITION p2 VALUESLESS THAN (1980),
PARTITION p3 VALUESLESS THAN (1990),
PARTITION p4 VALUESLESS THAN MAXVALUE
);
MySQL partitioning is optimized for usewith the TO_DAYS(), YEAR(), and TO_SECONDS()
functions. However, you can use other dateand time functions that return an integer or NULL, such
as WEEKDAY(), DAYOFYEAR(), or MONTH().
可以使用時間日期函數(shù)（返回整數(shù)或null）來進(jìn)行分區(qū)優(yōu)化，如TO_DAYS(), YEAR(), and TO_SECONDS(), WEEKDAY(), DAYOFYEAR(), orMONTH()
PS:分區(qū)表分區(qū)名，大小寫不敏感。
mysql> CREATE TABLE t2 (val INT)
-> PARTITION BY LIST(val)(
-> PARTITION mypart VALUES IN(1,3,5),
-> PARTITION MyPart VALUES IN(2,4,6)
-> );
ERROR 1517 (HY000): Duplicate partitionname MyPart
案例1——maxvalue：
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
);
案例2——range date：
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
);
案例3——range timestamp：
CREATE TABLE quarterly_report_status (
report_id INT NOT NULL,
report_status VARCHAR(20) NOT NULL,
report_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMPON 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-0100: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)
);
案列4——range columns(date or datetime):
此部分案例可以參考文檔MySQL分區(qū)——Column Partition.docx
CREATE TABLE members (
firstname VARCHAR(25) NOT NULL,
lastname VARCHAR(25) NOT NULL,
username VARCHAR(16) NOT NULL,
email VARCHAR(35),
joined datetime NOTNULL
)
PARTITION BY RANGE COLUMNS(joined) (
PARTITION p0 VALUES LESS THAN ('1960-01-0100:00:00'),
PARTITION p1 VALUES LESS THAN ('1970-01-0100:00:00'),
PARTITION p2 VALUES LESS THAN ('1980-01-0100:00:00'),
PARTITION p3 VALUES LESS THAN ('1990-01-0100:00:00'),
PARTITION p4 VALUES LESS THAN MAXVALUE
);
CREATE TABLE members (
firstname VARCHAR(25) NOT NULL,
lastname VARCHAR(25) NOT NULL,
username VARCHAR(16) NOT NULL,
email VARCHAR(35),
joined date NOTNULL
)
PARTITION BY RANGE COLUMNS(joined) (
PARTITION p0 VALUES LESS THAN ('1960-01-0100:00:00'),
PARTITION p1 VALUES LESS THAN ('1970-01-0100:00:00'),
PARTITION p2 VALUES LESS THAN ('1980-01-0100:00:00'),
PARTITION p3 VALUES LESS THAN ('1990-01-0100:00:00'),
PARTITION p4 VALUES LESS THAN MAXVALUE
);
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)
);
The permitted data types are shown in the followinglist:支持的數(shù)據(jù)類型如下
（整數(shù)）All integer types: TINYINT, SMALLINT,MEDIUMINT, INT (INTEGER), and BIGINT. (This is the
same as with partitioning by RANGE andLIST.)
Other numeric data types (such as DECIMALor FLOAT) are not supported as partitioning columns.
（日期）DATE and DATETIME.
Columns using other data types relating todates or times are not supported as partitioning columns.
（字符串）The following string types: CHAR, VARCHAR,BINARY, and VARBINARY.
COLUMNS Partitioning
TEXT and BLOB columns are not supported aspartitioning columns.
1.
2.
3.
4.
1.
2.
3.
4.
實驗
create table rcx
(aint,
bint,
cchar(10),
dint)
partition by range columns(a, b, c)
(partition p0 values less than (5, 10, 'aaa'),
partition p1 values less than (10, 20, 'nnnn'),
partition pmax values less than (maxvalue, maxvalue, maxvalue));
insert into rcx values (5, 9, 'aaa', 1); -- insert into p0
insert into rcx values (5, 10, 'aaa', 1); -- insert into p1
insert into rcx values (5, 11, 'aaa', 1); -- insert into p1
insert into rcx values (4, 12, 'aaa', 1) -- insert into p0;
select (5, 9) < (5, 10), (5, 10) <(5, 10), (4, 12) < (5, 10) ;
# (5, 9) < (5, 10), (5, 10) < (5,10), (4, 12) < (5, 10)
1, 0, 1
備注：是按照數(shù)組進(jìn)行比較插入的，官方文檔參考位置21.2.3.1RANGE COLUMNS partitioning
以下SQL都是能正確創(chuàng)建的，來自官檔。
案例1：
CREATE TABLE rc2 (
a INT,
b INT
)
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)
);
案例2：
CREATE TABLE rc3 (
a INT,
b INT
)
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 (10,35),
PARTITION p4 VALUES LESS THAN (20,40),
PARTITION p5 VALUES LESS THAN(MAXVALUE,MAXVALUE)
);
案例3：
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)
);
When designing tablespartitioned by RANGE COLUMNS, you can always test successive partition
definitions by comparingthe desired tuples using the mysql client, like this:
當(dāng)你設(shè)計range columns分區(qū)表，通過比較期望的數(shù)組來測試連續(xù)分區(qū)定義。
mysql> SELECT (0,25,50) <(10,20,100), (10,20,100) < (10,30,50);
+-------------------------+--------------------------+
| (0,25,50) <(10,20,100) | (10,20,100) < (10,30,50) |
+-------------------------+--------------------------+
| 1 | 1 |
+-------------------------+--------------------------+
1 row in set (0.00 sec)
案例4：
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)
);
普通表轉(zhuǎn)為rangecolumns表
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
);
ALTER TABLE employees PARTITION BY RANGECOLUMNS (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)
);
ALTER TABLE employees PARTITION BY RANGECOLUMNS (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)
);
注意：list、range分區(qū)不支持非int的分區(qū)，list columns、rangecolumns分區(qū)支持條件見第4大點的首部。
4.1.
案例1:
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','Hgsby', 'Mnsters'),
PARTITION pRegion_2 VALUES IN('Vimmerby','Hultsfred', 'Vstervik'),
PARTITION pRegion_3 VALUES IN('Nssj','Eksj', 'Vetlanda'),
PARTITION pRegion_4 VALUES IN('Uppvidinge','Alvesta', 'Vxjo')
);
As with partitioning by RANGE COLUMNS, youdo not need to use expressions in the COLUMNS() clause to convert column valuesinto integers. (In fact, the use of expressions other than column names is not permittedwith COLUMNS().)
實際上和range columns一樣，你不需要將columns中的表達(dá)式轉(zhuǎn)換為integer（實際上，不允許在columns中使用除了列名之外的表達(dá)式）。
就是表達(dá)式不允許使用。
案例2：
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')
);
案例3——含復(fù)合列：
create table test_list_cols
(col1 char(4),
col2 char(4))
partition by list columns(col1, col2)
(partition part_1 values in ('a', 'b'),
partition part_2 values in ('c', 'd'));
這個SQL是創(chuàng)建失敗的，syntax錯誤。
create table test_list_cols
(col1 char(4),
col2 char(4))
partition by list columns(col1, col2)
(partition part_1 values in (('a', 'b')),
partition part_2 values in (('c', 'd')));
和單列list不一樣，需多個括號（tuple）。
To partition a table using HASHpartitioning, it is necessary to append to the CREATE TABLE statement aPARTITION BY HASH (expr) clause, where expr is anexpression that returns an integer. This can simply be the name of acolumn whose type is one of MySQL's integer types. In addition, you most likelywant to follow this with PARTITIONS num, where num is a positive integerrepresenting the number of partitions into which the table is to be divided.
即分區(qū)鍵必須是integer type, 或返回integer type的表達(dá)式。
案例1：
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;
案例2——返回整數(shù)expr：
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;
expr must return a nonconstant,nonrandom integer value (in other words, it should be varying
but deterministic), You should also keep in mind that this expression is evaluatedeach time a row is inserted or updated (or possibly deleted); this means thatvery complex expressions may give rise to performance issues, particularly whenperforming operations (such as batch inserts) that affect a great many rows atone time.
expression表達(dá)式返回的值，應(yīng)該是非常量非隨機(jī)的整數(shù)，即數(shù)值上不同且確定性的。表達(dá)式的復(fù)雜程度，可能造成批量插入的性能問題。
Partitioning by key is similar topartitioning by hash, except that where hash partitioning employs a user-definedexpression, the hashing function for key partitioningis supplied by the MySQL server.
MySQL Cluster uses MD5()for this purpose; for tables using other storage engines, the server
employs its own internal hashing functionwhich is based on the same algorithm as PASSWORD().
The syntax rules for CREATE TABLE ...PARTITION BY KEY are similar to those for creating a
table that is partitioned by hash. Themajor differences are listed here:
KEY is used rather than HASH.
KEY takes only a list of zero or morecolumn names. Any columns used as the partitioning key
must comprise part or all of the table'sprimary key, if the table has one. Where no column name is specified as thepartitioning key, the table's primary key is used, if there is one.
Key partition分區(qū)的hash函數(shù)是由mysql服務(wù)器提供，涉及到不同的算法。
Key后面可包含0或多個列，如果表中有主鍵，Key（）中沒指定列，則主鍵被使用為key。
案列1：
CREATE TABLE k1 (
id INT NOT NULL PRIMARY KEY,
name VARCHAR(20)
)
PARTITION BY KEY()
PARTITIONS 2;
If there is no primary key but there is aunique key, then the unique key is used for the partitioning
key
如果沒有主鍵但有唯一鍵，唯一鍵（必須非空）被作為分區(qū)鍵。
案列2：
CREATE TABLE k1 (
id INT NOT NULL,
name VARCHAR(20),
UNIQUE KEY (id)
)
PARTITION BY KEY()
PARTITIONS 2;
Unlike the case with other partitioningtypes, columns used for partitioning by KEY are not restricted to integer orNULL values.
不像其他分區(qū)類型一樣，KEY分區(qū)的分區(qū)鍵不限于整數(shù)或null。
案列3——CHAR：
CREATE TABLE tm1 (
s1 CHAR(32) PRIMARY KEY
)
PARTITION BY KEY(s1)
PARTITIONS 10;
案列4——linear key:
CREATE TABLE tk (
col1 INT NOT NULL,
col2 CHAR(5),
col3 DATE
)
PARTITION BY LINEARKEY (col1)
PARTITIONS 3;
案列1：
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
);
In MySQL 5.7, it is possible tosubpartition tables that are partitioned by RANGE or LIST. Subpartitions mayuse either HASH or KEY partitioning.
分區(qū)使用range或list，子分區(qū)使用hash或key。注意subpartition by key必須明確指定列。
案列2：
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
)
);
注意：
每個分區(qū)有相同數(shù)量的子分區(qū)；
如果顯式指定分區(qū)和子分區(qū)，需全部指定完整；
子分區(qū)名字必須唯一；
案列3——myiasm指定不同的存儲：
CREATE TABLE ts (id INT, purchased DATE)
ENGINE = MYISAM
PARTITION BY RANGE( YEAR(purchased) )
SUBPARTITION BY HASH( TO_DAYS(purchased) )(
PARTITION p0 VALUES LESS THAN (1990) (
SUBPARTITION s0
DATA DIRECTORY = '/disk0/data'
INDEX DIRECTORY = '/disk0/idx',
SUBPARTITION s1
DATA DIRECTORY = '/disk1/data'
INDEX DIRECTORY = '/disk1/idx'
),
PARTITION p1 VALUES LESS THAN (2000) (
SUBPARTITION s2
DATA DIRECTORY = '/disk2/data'
INDEX DIRECTORY = '/disk2/idx',
SUBPARTITION s3
DATA DIRECTORY = '/disk3/data'
INDEX DIRECTORY = '/disk3/idx'
),
PARTITION p2 VALUES LESS THAN MAXVALUE (
SUBPARTITION s4
DATA DIRECTORY = '/disk4/data'
INDEX DIRECTORY = '/disk4/idx',
SUBPARTITION s5
DATA DIRECTORY = '/disk5/data'
INDEX DIRECTORY = '/disk5/idx'
)
);
CREATE TABLE ts (id INT, purchased DATE)
ENGINE = MYISAM
PARTITION BY RANGE(YEAR(purchased))
SUBPARTITION BY HASH( TO_DAYS(purchased) )(
PARTITION p0 VALUES LESS THAN (1990) (
SUBPARTITION s0a
DATA DIRECTORY = '/disk0'
INDEX DIRECTORY = '/disk1',
SUBPARTITION s0b
DATA DIRECTORY = '/disk2'
INDEX DIRECTORY = '/disk3'
),
PARTITION p1 VALUES LESS THAN (2000) (
SUBPARTITION s1a
DATA DIRECTORY = '/disk4/data'
INDEX DIRECTORY = '/disk4/idx',
SUBPARTITION s1b
DATA DIRECTORY = '/disk5/data'
INDEX DIRECTORY = '/disk5/idx'
),
PARTITION p2 VALUES LESS THAN MAXVALUE (
SUBPARTITION s2a,
SUBPARTITION s2b
)
);
Range Partition直接插入最小的分區(qū)內(nèi)；
List Partition只有顯式定義nullvalue，才能插入空值，如partition part_1 values in (null)；
Hash & Key Partition中，Null被當(dāng)做0來處理。
In MySQL 5.7, all partitions of apartitioned table must have the same number of
subpartitions, and it is not possible tochange the subpartitioning once the table
has been created.
在mysql5.7中，分區(qū)表的所有分區(qū)必須擁有相同數(shù)量的子分區(qū)；一旦分區(qū)表創(chuàng)建，不能改變子分區(qū)。
CREATE TABLE trb3 (id INT, nameVARCHAR(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)
);
insert into trb3 values(1, 'test','1988-10-10'), (2, 'test', '1999-10-10'), (3, 'test', '2001-10-10');
ALTER TABLE trb3 PARTITION BY KEY(id)PARTITIONS 2;
這里可以成功修改的，即使trb3里有數(shù)據(jù)，即分區(qū)表有數(shù)據(jù)，也可重定義表結(jié)構(gòu)。但是在oracle里，是不允許這樣修改的。
5.
6.
7.
8.
9.
刪除分區(qū)：
alter table droppartition xxx;這個語法和oracle一致。
If you wish to drop all data from allpartitions while preserving the table definition and its partitioning scheme,use the TRUNCATE TABLE statement.
如果想刪除數(shù)據(jù)同時保留表結(jié)構(gòu)，truncate table xxx。
If you intend to change the partitioning ofa table without losing data, use ALTER TABLE ...
REORGANIZE PARTITION instead.
如果想改變表分區(qū)不丟失數(shù)據(jù)，使用alter table t reorganize partition xxx;
添加分區(qū)：
Range添加分區(qū)：alter table members add partition (partition p3values less than (2000));
這個語法和oracle不太一樣，oracle的語法如下：
alter table members add partition p3 valuesless than (2000);
List添加分區(qū)：ALTER TABLE tt ADD PARTITION (PARTITION p2 VALUESIN (7, 14, 21));
同時添加多個分區(qū)：
ALTER TABLE employees ADD PARTITION (
PARTITION p5 VALUES LESS THAN (2010),
PARTITION p6 VALUES LESS THAN MAXVALUE
);
分區(qū)合并與拆分：
Range：
alter table members reorganize partition p0into (
partition s0 values less than (1960),
partition s1 values less than (1970));
alter table members reorganize partitions0, s1 into (
partition p0 values less than (1970));
ALTER TABLE members REORGANIZE PARTITIONp0,p1,p2,p3 INTO (
PARTITION m0 VALUES LESS THAN (1980),
PARTITION m1 VALUES LESS THAN (2000)
);
List：
ALTER TABLE tt ADD PARTITION (PARTITION npVALUES IN (4, 8));
ALTER TABLE tt REORGANIZE PARTITION p1,npINTO (
PARTITION p1 VALUES IN (6, 18),
PARTITION np VALUES in (4, 8, 12)
);
注意事項：
You cannot use REORGANIZE PARTITION tochange the table's partitioning type; that is, you cannot
(for example) change RANGE partitions toHASH partitions or vice versa. You also cannot use this
command to change the partitioningexpression or column. To accomplish either of these tasks
without dropping and re-creating the table,you can use ALTER TABLE ... PARTITION BY ....
不能使用reorganize來改變分區(qū)類型，也不能用于改變分區(qū)表達(dá)式或列。為了達(dá)到上述目的且不刪除數(shù)據(jù)和重構(gòu)表，可以使用ALTER TABLE ... PARTITION BY ....
這里包括linear hash和linear key。
減少分區(qū)：
alter table clientscoalesce partition 4;
增加分區(qū)：
alter table clients addpartition partitions 2;
增減都是指增減的分區(qū)個數(shù)。
分區(qū)數(shù)據(jù)：
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)
);
INSERT INTO e VALUES
(1669, "Jim", "Smith"),
(337, "Mary", "Jones"),
(16, "Frank", "White"),
(2005, "Linda","Black");
create table e2 like e;
alter table e2 removepartitioning;
alter table e exchangepartition p0 with table e2;
e2是普通表。
mysql> ALTER TABLE e EXCHANGE PARTITIONp0 WITH TABLE e2;
ERROR 1707 (HY000): Found row that does notmatch the partition
The IGNORE keyword is accepted, but has noeffect when used with EXCHANGE PARTITION, as
shown here:
mysql> ALTER IGNORE TABLE e EXCHANGEPARTITION p0 WITH TABLE e2;
ERROR 1707 (HY000): Found row that does notmatch the partition
Only the WITHOUT VALIDATION option wouldpermit this operation to succeed:
mysql> ALTERTABLE e EXCHANGE PARTITION p0 WITH TABLE e2 WITHOUT VALIDATION;
Query OK, 0 rows affected (0.02 sec)
交換分區(qū)數(shù)據(jù)，不需確認(rèn)。
with validation, 這個不常用。
子分區(qū)數(shù)據(jù)：
ALTER TABLE es EXCHANGEPARTITION p3sp0 WITH TABLE es2;
p3sp0是子分區(qū)，含子分區(qū)的表不支持分區(qū)的交換
mysql> ALTER TABLE es EXCHANGE PARTITIONp3 WITH TABLE es2;
ERROR 1704 (HY000): Subpartitioned table,use subpartition instead of partition
還需保持需交換的分區(qū)和普通表的存儲引擎相同。
分區(qū)表的維護(hù)。
分區(qū)的重構(gòu)：
alter table t rebuildpartition p0, p1;
作用分區(qū)重構(gòu)，整理分區(qū)碎片，刪除分區(qū)數(shù)據(jù)，重新插入數(shù)據(jù)。
分區(qū)的優(yōu)化：
ALTER TABLE t1 OPTIMIZEPARTITION p0, p1;
當(dāng)刪除大量數(shù)據(jù)或修改分區(qū)中變長字段（如varchar,blob,text字段），回收未使用空間和重新整理碎片。
等效于check partition, analyze partition, repair patition
Some MySQL storage engines, including InnoDB, do not support per-partition optimization; in
these cases, ALTERTABLE ... OPTIMIZE PARTITION analyzes and rebuilds the entire table,
and causes an appropriate warning to beissued. (Bug #11751825, Bug #42822) Use ALTER
TABLE ... REBUILD PARTITION and ALTER TABLE... ANALYZE PARTITION instead, to
avoid this issue.
分區(qū)的分析：
ALTER TABLE t1 ANALYZEPARTITION p3;
This reads and stores the key distributionsfor partitions.
讀取分區(qū)的分布和存儲。
分區(qū)的修復(fù)：
ALTER TABLE t1 REPAIRPARTITION p0,p1;
修復(fù)崩潰的分區(qū)。
Normally, REPAIRPARTITION fails when the partition contains duplicate key errors. In MySQL
5.7.2 and later, you can use ALTER IGNORETABLE with this option, in which case all rows that
cannot be moved due to the presence ofduplicate keys are removed from the partition (Bug
#16900947).
分區(qū)的檢查：
ALTER TABLE trb3 CHECKPARTITION p1;
作用和check TABLE with nonpartitioned tables一樣。
會檢查分區(qū)的數(shù)據(jù)和索引是否正常，用ALTER TABLE ... REPAIR PARTITION修復(fù)。
Normally, CHECKPARTITION fails when the partition contains duplicate key errors. In MySQL 5.7.2
and later, you can use ALTER IGNORE TABLEwith this option, in which case the statement returns
the contents of each row in the partitionwhere a duplicate key violation is found. Note that only the values for thecolumns in the partitioning expression for the table are reported. (Bug#16900947)
注意
The use of mysqlcheckand myisamchk is not supported withpartitioned tables.
mysqlcheck和myisamchk不支持分區(qū)表。
In MySQL 5.7, you can also truncatepartitions using ALTER TABLE ... TRUNCATE PARTITION.
This statement can be used to delete allrows from one or more partitions in much the same way that TRUNCATE TABLEdeletes all rows from a table.
可以使用ALTER TABLE ... TRUNCATE PARTITION.
ALTER TABLE ... TRUNCATEPARTITION ALL truncates all partitions in the table.
Prior to MySQL 5.7.2, ANALYZE, CHECK,OPTIMIZE, REBUILD, REPAIR, and TRUNCATE operations
were not permitted on subpartitions (Bug#14028340, Bug #65184).
在5.7.2版本之前，不支持子分區(qū)的操作（ANALYZE, CHECK, OPTIMIZE, REBUILD, REPAIR, and TRUNCATE）
獲取分區(qū)信息。
Using the SHOW CREATE TABLEstatement to view the partitioning clauses used in creating a
partitioned table.
Using the SHOW TABLE STATUSstatement to determine whether a table is partitioned.
Querying the INFORMATION_SCHEMA.PARTITIONStable.
Using the statement EXPLAIN SELECTto see which partitions are used by a given SELECT.
mysql> show create table t \G;
mysql> show table status like 't' \G;
EXPLAIN SELECT * FROM t WHERE id < 5 \G
意思，就是mysql optimizer根據(jù)條件，能準(zhǔn)確定位數(shù)據(jù)在哪個（或哪些）分區(qū)，達(dá)到性能提升的目的。親，可以理解為mysql服務(wù)器自己的查詢優(yōu)化。
注意：
When pruning is performed on a partitionedMyISAM table, all partitions are opened, whether or not they are examined, dueto the design of the MyISAM storage engine.
當(dāng)使用MyISAM分區(qū)表時，所有分區(qū)都將被打開，無論分區(qū)是否被檢查。
MySQL can apply partition pruning toSELECT, DELETE, and UPDATE statements. INSERT statements
currently cannot be pruned.
Mysql優(yōu)化器能將分區(qū)裁剪應(yīng)用于SELECT, DELETE, and UPDATE語句中，但是insert語句不支持（version5.7.17）
補(bǔ)充：
Pruning can also be applied for tablespartitioned on a DATE or DATETIME column whenthe partitioning expression uses the YEAR() or TO_DAYS()function.In addition, in MySQL 5.7, pruning can beapplied for such tables when the partitioning expression uses the TO_SECONDS()function.
案例1——range：
CREATE TABLE t2 (
fname VARCHAR(50) NOT NULL,
lname VARCHAR(50) NOT NULL,
region_code TINYINT UNSIGNED NOT NULL,
dob DATE NOT NULL
)
PARTITION BY RANGE( YEAR(dob) ) (
PARTITION d0 VALUES LESS THAN (1970),
PARTITION d1 VALUES LESS THAN (1975),
PARTITION d2 VALUES LESS THAN (1980),
PARTITION d3 VALUES LESS THAN (1985),
PARTITION d4 VALUES LESS THAN (1990),
PARTITION d5 VALUES LESS THAN (2000),
PARTITION d6 VALUES LESS THAN (2005),
PARTITION d7 VALUES LESS THAN MAXVALUE
);
SELECT * FROM t2 WHERE dob = '1982-06-23';
UPDATE t2 SET region_code = 8 WHERE dobBETWEEN '1991-02-15' AND '1997-04-25';
DELETE FROM t2 WHERE dob >= '1984-06-21'AND dob <= '1999-06-21';
Invalid DATE and DATETIME values referencedin the WHERE condition of a statement against a partitioned table are treatedas NULL.
在where子句中無效的DATE、DATETIME將被視為NULL，不返回任何值。
案例2——list：
CREATE TABLE t3 (
fname VARCHAR(50) NOT NULL,
lname VARCHAR(50) NOT NULL,
region_code TINYINT UNSIGNED NOT NULL,
dob DATE NOT NULL
)
PARTITION BY LIST(region_code) (
PARTITION r0 VALUES IN (1, 3),
PARTITION r1 VALUES IN (2, 5, 8),
PARTITION r2 VALUES IN (4, 9),
PARTITION r3 VALUES IN (6, 7, 10)
);
查詢
SELECT * FROM t3 WHERE region_code BETWEEN1 AND 3
案例3——key：
CREATE TABLE t4 (
fname VARCHAR(50) NOT NULL,
lname VARCHAR(50) NOT NULL,
region_code TINYINT UNSIGNED NOT NULL,
dob DATE NOT NULL
)
PARTITION BY KEY(region_code)
PARTITIONS 8;
UPDATE t4 WHERE region_code = 7;
SELECT * FROM t4 WHERE region_code > 2AND region_code < 6;
SELECT * FROM t4 WHERE region_code BETWEEN3 AND 5;
顯式分區(qū)選擇：
SQL statements supporting explicit partition selection are listed here:
SELECT
DELETE
INSERT
REPLACE
UPDATE
LOAD DATA.
LOAD XML.
SELECT * FROM employeesPARTITION (p1, p2)，中間以逗號分隔，這種寫法在oracle里不支持的。
When a table is created using [LINEAR] HASHor [LINEAR] KEY partitioning and the names of the partitions are not specified,MySQL automatically names the partitions p0, p1, p2, ..., pN-1, where N is thenumber of partitions. For subpartitions not explicitly named, MySQL assignsautomatically to the subpartitions in each partition pX the names pXsp0, pXsp1,pXsp2, ..., pXspM-1, where M is the number of subpartitions.
當(dāng)表以[LINEAR] HASH or [LINEAR] KEY分區(qū)時并且分區(qū)名字未被指定，MySQL自動命名分區(qū)p0, p1,p2, ..., pN-1。對于沒有顯式定義的子分區(qū)，MySQL自動命名子分區(qū)pXsp0, pXsp1, pXsp2, ..., pXspM-1。
案例1：
mysql> CREATE TABLE employees_sub (
-> id INT NOT NULL AUTO_INCREMENT,
-> fname VARCHAR(25) NOT NULL,
-> lname VARCHAR(25) NOT NULL,
-> store_id INT NOT NULL,
-> department_id INT NOT NULL,
-> PRIMARY KEY pk (id, lname)
-> )
-> PARTITION BY RANGE(id)
-> SUBPARTITION BY KEY (lname)
-> SUBPARTITIONS 2 (
-> PARTITION p0 VALUES LESS THAN (5),
-> PARTITION p1 VALUES LESS THAN (10),
-> PARTITION p2 VALUES LESS THAN (15),
-> PARTITION p3 VALUES LESS THANMAXVALUE
-> );
Query OK, 0 rows affected (1.14 sec)
mysql> SELECT id, CONCAT(fname, ' ',lname) AS name
-> FROM employees_sub PARTITION (p2sp1); ——僅是這里換成了子分區(qū)名字
案例2——在join中使用顯式分區(qū)
mysql> SELECT
-> e.id AS 'Employee ID',CONCAT(e.fname, ' ', e.lname) AS Name,
-> s.city AS City, d.name AS department
Partition Selection
3436
-> FROM employees AS e
-> JOIN storesPARTITION (p1) AS s ON e.store_id=s.id
-> JOIN departmentsPARTITION (p0) AS d ON e.department_id=d.id
-> ORDER BY e.lname;
案例3——多個分區(qū)插入
For statements that write multiple rows toa partitioned table that uses the InnoDB storage engine:
If any row in the list following VALUEScannot be written to one of the partitions specified in the
partition_names list, the entire statementfails and no rows are written.
對應(yīng)多行記錄同時插入以InnoDB為引擎的分區(qū)表，如果某條記錄和顯式指定的分區(qū)名不匹配，則整個語句都將失敗，不會寫入任何行。
mysql> SHOW CREATE TABLE employees\G
*************************** 1. row***************************
Table: employees
Create Table: CREATE TABLE `employees` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`fname` varchar(25) NOT NULL,
`lname` varchar(25) NOT NULL,
`store_id` int(11) NOT NULL,
`department_id` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULTCHARSET=latin1
/*!50100 PARTITION BY RANGE (id)
(PARTITION p0 VALUES LESS THAN (5) ENGINE =InnoDB,
PARTITION p1 VALUES LESS THAN (10) ENGINE =InnoDB,
PARTITION p2 VALUES LESS THAN (15) ENGINE =InnoDB,
PARTITION p3 VALUES LESS THAN (20) ENGINE =InnoDB,
PARTITION p4 VALUES LESS THAN (25) ENGINE =InnoDB,
PARTITION p5 VALUES LESS THAN MAXVALUEENGINE = InnoDB) */
1 row in set (0.00 sec)
mysql> INSERT INTO employees PARTITION(p3, p4) VALUES
-> (24, 'Tim', 'Greene', 3, 1), (26,'Linda', 'Mills', 2, 1);
ERROR 1729 (HY000): Found a row not matching the given partition set
mysql> INSERT INTO employees PARTITION(p3, p4. p5) VALUES
-> (24, 'Tim', 'Greene', 3, 1), (26,'Linda', 'Mills', 2, 1);
Query OK, 2 rows affected (0.06 sec)
Records: 2 Duplicates: 0 Warnings: 0
分區(qū)表達(dá)式中禁用的結(jié)構(gòu)：
Stored procedures,stored functions, UDFs, or plugins.
Declared variables or user variables.
算術(shù)和邏輯運算符：
Use of thearithmetic operators +, -, and * is permitted in
partitioning expressions. However, the result must be aninteger value or NULL
Handler statements：
Previously, theHANDLER statement was not supported with partitioned
tables. This limitation is removed beginning with MySQL5.7.1.
Server SQL mode：
僅說最關(guān)鍵的一點：SQL_MODE請在數(shù)據(jù)庫建立之初就指定并且不要發(fā)生變化。
性能考慮：
File system operations.
Partitioning and repartitioning operations(such as ALTER TABLE with PARTITION BY ..., REORGANIZE PARTITION, or REMOVEPARTITIONING) depend on
file systemoperations for their implementation. This means thatthe speed of these operations is affected by such factors as file system type and characteristics, disk speed, swap space,file handling efficiency of the operating system, and MySQL serveroptions and variables that relate to file handling. In particular, you shouldmake sure that large_files_supportis enabled and that open_files_limit is setproperly. For partitioned tables using the MyISAM storage engine, increasing myisam_max_sort_file_sizemay improve performance; partitioning and repartitioning operations involvingInnoDB tables may be made more efficient by enablinginnodb_file_per_table.
Tablelocks. Theprocess executing a partitioning operation on a table takesa write lock on the table. Reads from such tables are relativelyunaffected; pending INSERT and UPDATE operations are performed as soon as thepartitioning operation has completed.
Storage engine. Partitioning operations,queries, and update operations generally tend to be fasterwith MyISAM tables than with InnoDB or NDB tables.
Indexes; partition pruning. As with nonpartitionedtables, proper use of indexes can speed up queries onpartitioned tables significantly. In addition, designing partitionedtables and queries on these tables to take advantage of partition pruning canimprove performance dramatically.
Performancewith LOAD DATA. In MySQL 5.7, LOAD DATA uses buffering to improve
performance. You should be aware that the buffer uses 130 KB memory per partition to achievethis.
Maximum number of partitions.
The maximum possible number of partitionsfor a given table not using the NDB storage engine is 8192. This numberincludes subpartitions.
when creating tables with a largenumber of partitions (but less than the maximum), you encounter an errormessage such as Got error ... from storage engine: Out of resources
when opening file, you may be able toaddress the issue by increasing the value of the
open_files_limit system variable.However, this is dependent on the operating system, and may not be possible oradvisable on all platforms; see Section B.5.2.18, “File Not Found and SimilarErrors”, for more information. In some cases, using large numbers (hundreds) ofpartitions may also not be advisable due to other concerns, so using morepartitions does not automatically lead to better results.
當(dāng)創(chuàng)建含大數(shù)量分區(qū)的分區(qū)表時，你可能會遇到Got error ... from storage engine: Out of resources when openingfile這類報錯，可增加open_files_limit系統(tǒng)參數(shù)值來解決這個問題。然而這個參數(shù)依賴于操作系統(tǒng)，并不是在所有的操作系統(tǒng)上都是可行和建議的。在某些情況下，創(chuàng)建含大數(shù)量分區(qū)的分區(qū)表因為其他考慮并不是推薦的，因此用更多的分區(qū)數(shù)并不能導(dǎo)致更好的結(jié)果。
Query cache not supported.
The query cache isnot supported for partitioned tables, and isautomatically disabled for queries involving partitioned tables. The querycache cannot be enabled for such queries.
查詢緩存對分區(qū)表是不支持的。
Per-partition key caches.
In MySQL 5.7, key caches are supported for partitionedMyISAM tables, using the CACHE INDEX and LOAD INDEX INTO CACHE statements.Key caches may be defined for one, several, or all partitions, and indexes forone, several, or all partitions may be preloaded into key caches.
Foreign keysnot supported for partitioned InnoDB tables.
Partitioned tables using the InnoDB storage engine do not supportforeign keys. More specifically, this
means that the following two statements are true:
1. No definition of an InnoDB table employing user-defined partitioningmay contain foreign key references; no InnoDB table whose definition containsforeign key references may be partitioned.
用戶定義的InnoDB分區(qū)表不能含有外鍵關(guān)聯(lián)；沒有含有外鍵關(guān)聯(lián)的InnoDB表可以被分區(qū)的。
2. No InnoDB table definition may contain a foreign key reference toa user-partitioned table; no InnoDB table with user-defined partitioning maycontain columns referenced by foreign keys.
沒有含有外鍵關(guān)聯(lián)分區(qū)表的InnoDB表；沒有InnoDB分區(qū)表含有外鍵關(guān)聯(lián)列。
ALTER TABLE ... ORDER BY.
An ALTER TABLE ...ORDER BY column statement run against a partitioned table causes ordering ofrows only within each partition.
FULLTEXT indexes.
Partitioned tables do not support FULLTEXT indexesor searches, even for partitioned tables employing theInnoDB or MyISAM storage engine.
Spatial columns.
Columns with spatialdata types such as POINT or GEOMETRY cannot be used inpartitioned tables.
Temporary tables.
Temporary tables cannot be partitioned. (Bug #17497)
Log tables.
It is not possible topartition the log tables; an ALTER TABLE ... PARTITION BY ... statement on sucha table fails with an error.
Issues with subpartitions.
Subpartitions must use HASHor KEY partitioning. Only RANGE andLIST partitions may be
subpartitioned; HASH and KEYpartitions cannot be subpartitioned.
SUBPARTITIONBY KEY requires that the subpartitioning column or columns be specifiedexplicitly,
unlikethe case with PARTITION BY KEY, where it can be omitted (in which case thetable's primary
keycolumn is used by default). Consider the table created by this statement:
SUBPARTITIONBY KEY要求子分區(qū)列被顯式指定，不像PARTITION BY KEY分區(qū)時，使用默認(rèn)主鍵可以省略。
CREATETABLE ts (
id INTNOT NULL AUTO_INCREMENT PRIMARY KEY,
nameVARCHAR(30)
)
PARTITIONBY KEY()
PARTITIONS4;
CREATETABLE ts (
id INTNOT NULL AUTO_INCREMENT PRIMARY KEY,
nameVARCHAR(30)
)
PARTITIONBY KEY(id)
PARTITIONS4;
上述兩語句都可以創(chuàng)建成功。
mysql>CREATE TABLE ts (
-> idINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
->name VARCHAR(30)
-> )
->PARTITION BY RANGE(id)
-> SUBPARTITION BY KEY()
->SUBPARTITIONS 4
-> (
->PARTITION p0 VALUES LESS THAN (100),
->PARTITION p1 VALUES LESS THAN (MAXVALUE)
-> );
ERROR1064 (42000): You have an error in your SQL syntax; check the manual that
correspondsto your MySQL server version for the right syntax to use near ')
mysql>CREATE TABLE ts (
-> idINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
->name VARCHAR(30)
-> )
->PARTITION BY RANGE(id)
-> SUBPARTITION BY KEY(id)
->SUBPARTITIONS 4
-> (
->PARTITION p0 VALUES LESS THAN (100),
->PARTITION p1 VALUES LESS THAN (MAXVALUE)
-> );
QueryOK, 0 rows affected (0.07 sec)
Inaddition, you can use ALTER TABLE ... REBUILD PARTITION to rebuild one or morepartitions
of apartitioned table; ALTER TABLE ... REORGANIZE PARTITION also causes partitionsto be
rebuilt.
Startingin MySQL 5.7.2, ANALYZE, CHECK, OPTIMIZE, REPAIR, and TRUNCATE operations are
supportedwith subpartitions.
從5.7.2開始，ANALYZE,CHECK, OPTIMIZE, REPAIR, and TRUNCATE可以使用在子分區(qū)上。
mysqlcheck,myisamchk, and myisampack are not supported with partitioned tables.
mysqlcheck,myisamchk, and myisampack不支持分區(qū)表。
10.
11.
12.
這部分請參考中文PDF文件，搜索key“MySQL*分區(qū)鍵”
InnoDB storage engine. InnoDB foreign keysand MySQL partitioning are not compatible.
Partitioned InnoDB tables cannot haveforeign key references, nor can they have columns referenced by foreign keys.InnoDB tables which have or which are referenced by foreign keys cannot be partitioned.
InnoDB分區(qū)表不能有外鍵關(guān)聯(lián)，也不能有列被外鍵關(guān)聯(lián)。帶有外鍵關(guān)聯(lián)和被外鍵關(guān)聯(lián)的InnoDB表不能分區(qū)。
InnoDB does not supportthe use of multiple disks for subpartitions. (This iscurrently supported only by MyISAM.)
In addition, ALTER TABLE... OPTIMIZE PARTITION does not work correctly with partitioned
tables that use theInnoDB storage engine. Use ALTER TABLE ... REBUILD PARTITION and
ALTER TABLE ... ANALYZEPARTITION, instead, for such tables.
在InnoDB分區(qū)表中，不用使用ALTER TABLE ... OPTIMIZE PARTITION，使用ALTER TABLE ... REBUILD PARTITION and ALTER TABLE... ANALYZE PARTITION。
Only the MySQL functions shown in thefollowing table are allowed in partitioning expressions.
In MySQL 5.7, partition pruning issupported for the TO_DAYS(), TO_SECONDS(), YEAR(), and
UNIX_TIMESTAMP() functions. See Section21.4, “Partition Pruning”, for more information.
CEILING() and FLOOR(). Each of these functions returns an integeronly if it is passed an argument
of an exact numeric type, such as one ofthe INT types or DECIMAL. This means, for example, that the
following CREATE TABLE statement fails withan error, as shown here:
mysql> CREATE TABLE t (c FLOAT)PARTITION BY LIST( FLOOR(c) )(
-> PARTITION p0 VALUES IN (1,3,5),
-> PARTITION p1 VALUES IN (2,4,6)
-> );
ERROR 1490 (HY000): The PARTITION functionreturns the wrong type
EXTRACT() function with WEEKspecifier. The value returned by theEXTRACT() function, when
used as EXTRACT(WEEK FROM col), depends onthe value of the default_week_format system
variable. For this reason, EXTRACT() is notpermitted as a partitioning function when it specifies the
unit as WEEK. (Bug #54483)
In MySQL 5.7, partition lock pruningeliminates unneeded locks in many cases,
and most statements reading from orupdating a partitioned MyISAM table cause only the effected
partitions to be locked.
Effectson DML statements
SELECT statements (including thosecontaining unions or joins) lock only those partitions that actually need to beread. This also applies to SELECT ... PARTITION.
Select語句只鎖需要被讀取的分區(qū)，同時使用用SELECT…PARTITION.
An UPDATE prunes locks only for tables onwhich no partitioning columns are updated.
只要分區(qū)列沒有被更新的分區(qū)，UPDATE語句就不會鎖。
REPLACE and INSERT lock only thosepartitions having rows to be inserted or replaced. However, if an AUTO_INCREMENTvalue is generated for any partitioning column then all partitions are locked.
REPLACE and INSERT只會鎖涉及到的分區(qū)，如果含有AUTO_INCREMENT列，則整個表所有分區(qū)被鎖。
INSERT ... ON DUPLICATE KEY UPDATE ispruned as long as no partitioning column is updated.
只要分區(qū)列沒有被更新的分區(qū)，此語句就不會鎖。
INSERT ... SELECT locks only thosepartitions in the source table that need to be read, although all
partitions in the target table are locked.
INSERT ... SELECT只鎖需要讀取的源表分區(qū)，鎖住整個目標(biāo)表。
Locks imposed by LOAD DATA statements onpartitioned tables cannot be pruned.
觸發(fā)器和分區(qū)
The presence of BEFORE INSERT or BEFOREUPDATE triggers using any partitioning column of a
partitioned table means that locks onINSERT and UPDATE statements updating this table cannot
be pruned, since the trigger can alter itsvalues: A BEFORE INSERT trigger on any of the table's
partitioning columns means that locks setby INSERT or REPLACE cannot be pruned, since the BEFORE INSERT trigger maychange a row's partitioning columns before the row is inserted, forcing the rowinto a different partition than it would be otherwise. A BEFORE UPDATE triggeron a partitioning column means that locks imposed by UPDATE or INSERT ... ONDUPLICATE KEY UPDATE cannot be pruned.
AffectedDDL statements
CREATE VIEW does not cause any locks.
ALTER TABLE ... EXCHANGE PARTITION pruneslocks; only the exchanged table and the exchanged partition are locked.只鎖交換表和交換分區(qū)。
ALTER TABLE ... TRUNCATE PARTITION pruneslocks; only the partitions to be emptied are locked.
只鎖truncate部分分區(qū)。
In addition, ALTERTABLE statements take metadata locks on the table level.
Otherstatements
LOCK TABLES cannot prune partition locks.
CALLstored_procedure(expr) supports lock pruning, butevaluating expr does not.
DO and SET statements do not supportpartitioning lock pruning.
免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點不代表本網(wǎng)站立場，如果涉及侵權(quán)請聯(lián)系站長郵箱：is@yisu.com進(jìn)行舉報，并提供相關(guān)證據(jù)，一經(jīng)查實，將立刻刪除涉嫌侵權(quán)內(nèi)容。
億速云公眾號
手機(jī)網(wǎng)站二維碼
