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:
如果考虑不转换列值，可采取key进行分区。
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;
这样分区，不用转换列值。
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的分区，可使用时间函数进行分区。
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().
可以使用时间日期函数（返回整数或null）来进行分区优化，如TO_DAYS(), YEAR(), and TO_SECONDS(), WEEKDAY(), DAYOFYEAR(), orMONTH()
PS:分区表分区名，大小写不敏感。
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分区——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:支持的数据类型如下
（整数）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
备注：是按照数组进行比较插入的，官方文档参考位置21.2.3.1RANGE COLUMNS partitioning
以下SQL都是能正确创建的，来自官档。
案例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:
当你设计range columns分区表，通过比较期望的数组来测试连续分区定义。
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)
);
普通表转为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分区不支持非int的分区，list columns、rangecolumns分区支持条件见第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中的表达式转换为integer（实际上，不允许在columns中使用除了列名之外的表达式）。
就是表达式不允许使用。
案例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——含复合列：
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是创建失败的，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.
即分区键必须是integer type, 或返回integer type的表达式。
案例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——返回整数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表达式返回的值，应该是非常量非随机的整数，即数值上不同且确定性的。表达式的复杂程度，可能造成批量插入的性能问题。
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分区的hash函数是由mysql服务器提供，涉及到不同的算法。
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
如果没有主键但有唯一键，唯一键（必须非空）被作为分区键。
案列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.
不像其他分区类型一样，KEY分区的分区键不限于整数或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.
分区使用range或list，子分区使用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
)
);
注意：
每个分区有相同数量的子分区；
如果显式指定分区和子分区，需全部指定完整；
子分区名字必须唯一；
案列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直接插入最小的分区内；
List Partition只有显式定义nullvalue，才能插入空值，如partition part_1 values in (null)；
Hash & Key Partition中，Null被当做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中，分区表的所有分区必须拥有相同数量的子分区；一旦分区表创建，不能改变子分区。
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里有数据，即分区表有数据，也可重定义表结构。但是在oracle里，是不允许这样修改的。
5.
6.
7.
8.
9.
删除分区：
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.
如果想删除数据同时保留表结构，truncate table xxx。
If you intend to change the partitioning ofa table without losing data, use ALTER TABLE ...
REORGANIZE PARTITION instead.
如果想改变表分区不丢失数据，使用alter table t reorganize partition xxx;
添加分区：
Range添加分区：alter table members add partition (partition p3values less than (2000));
这个语法和oracle不太一样，oracle的语法如下：
alter table members add partition p3 valuesless than (2000);
List添加分区：ALTER TABLE tt ADD PARTITION (PARTITION p2 VALUESIN (7, 14, 21));
同时添加多个分区：
ALTER TABLE employees ADD PARTITION (
PARTITION p5 VALUES LESS THAN (2010),
PARTITION p6 VALUES LESS THAN MAXVALUE
);
分区合并与拆分：
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来改变分区类型，也不能用于改变分区表达式或列。为了达到上述目的且不删除数据和重构表，可以使用ALTER TABLE ... PARTITION BY ....
这里包括linear hash和linear key。
减少分区：
alter table clientscoalesce partition 4;
增加分区：
alter table clients addpartition partitions 2;
增减都是指增减的分区个数。
分区数据：
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)
交换分区数据，不需确认。
with validation, 这个不常用。
子分区数据：
ALTER TABLE es EXCHANGEPARTITION p3sp0 WITH TABLE es2;
p3sp0是子分区，含子分区的表不支持分区的交换
mysql> ALTER TABLE es EXCHANGE PARTITIONp3 WITH TABLE es2;
ERROR 1704 (HY000): Subpartitioned table,use subpartition instead of partition
还需保持需交换的分区和普通表的存储引擎相同。
分区表的维护。
分区的重构：
alter table t rebuildpartition p0, p1;
作用分区重构，整理分区碎片，删除分区数据，重新插入数据。
分区的优化：
ALTER TABLE t1 OPTIMIZEPARTITION p0, p1;
当删除大量数据或修改分区中变长字段（如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.
分区的分析：
ALTER TABLE t1 ANALYZEPARTITION p3;
This reads and stores the key distributionsfor partitions.
读取分区的分布和存储。
分区的修复：
ALTER TABLE t1 REPAIRPARTITION p0,p1;
修复崩溃的分区。
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).
分区的检查：
ALTER TABLE trb3 CHECKPARTITION p1;
作用和check TABLE with nonpartitioned tables一样。
会检查分区的数据和索引是否正常，用ALTER TABLE ... REPAIR PARTITION修复。
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不支持分区表。
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版本之前，不支持子分区的操作（ANALYZE, CHECK, OPTIMIZE, REBUILD, REPAIR, and TRUNCATE）
获取分区信息。
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根据条件，能准确定位数据在哪个（或哪些）分区，达到性能提升的目的。亲，可以理解为mysql服务器自己的查询优化。
注意：
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.
当使用MyISAM分区表时，所有分区都将被打开，无论分区是否被检查。
MySQL can apply partition pruning toSELECT, DELETE, and UPDATE statements. INSERT statements
currently cannot be pruned.
Mysql优化器能将分区裁剪应用于SELECT, DELETE, and UPDATE语句中，但是insert语句不支持（version5.7.17）
补充：
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;
显式分区选择：
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.
当表以[LINEAR] HASH or [LINEAR] KEY分区时并且分区名字未被指定，MySQL自动命名分区p0, p1,p2, ..., pN-1。对于没有显式定义的子分区，MySQL自动命名子分区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); ——仅是这里换成了子分区名字
案例2——在join中使用显式分区
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——多个分区插入
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.
对应多行记录同时插入以InnoDB为引擎的分区表，如果某条记录和显式指定的分区名不匹配，则整个语句都将失败，不会写入任何行。
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
分区表达式中禁用的结构：
Stored procedures,stored functions, UDFs, or plugins.
Declared variables or user variables.
算术和逻辑运算符：
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：
仅说最关键的一点：SQL_MODE请在数据库建立之初就指定并且不要发生变化。
性能考虑：
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.
当创建含大数量分区的分区表时，你可能会遇到Got error ... from storage engine: Out of resources when openingfile这类报错，可增加open_files_limit系统参数值来解决这个问题。然而这个参数依赖于操作系统，并不是在所有的操作系统上都是可行和建议的。在某些情况下，创建含大数量分区的分区表因为其他考虑并不是推荐的，因此用更多的分区数并不能导致更好的结果。
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.
查询缓存对分区表是不支持的。
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分区表不能含有外键关联；没有含有外键关联的InnoDB表可以被分区的。
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.
没有含有外键关联分区表的InnoDB表；没有InnoDB分区表含有外键关联列。
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要求子分区列被显式指定，不像PARTITION BY KEY分区时，使用默认主键可以省略。
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;
上述两语句都可以创建成功。
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可以使用在子分区上。
mysqlcheck,myisamchk, and myisampack are not supported with partitioned tables.
mysqlcheck,myisamchk, and myisampack不支持分区表。
10.
11.
12.
这部分请参考中文PDF文件，搜索key“MySQL*分区键”
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分区表不能有外键关联，也不能有列被外键关联。带有外键关联和被外键关联的InnoDB表不能分区。
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分区表中，不用使用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语句只锁需要被读取的分区，同时使用用SELECT…PARTITION.
An UPDATE prunes locks only for tables onwhich no partitioning columns are updated.
只要分区列没有被更新的分区，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只会锁涉及到的分区，如果含有AUTO_INCREMENT列，则整个表所有分区被锁。
INSERT ... ON DUPLICATE KEY UPDATE ispruned as long as no partitioning column is updated.
只要分区列没有被更新的分区，此语句就不会锁。
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只锁需要读取的源表分区，锁住整个目标表。
Locks imposed by LOAD DATA statements onpartitioned tables cannot be pruned.
触发器和分区
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.只锁交换表和交换分区。
ALTER TABLE ... TRUNCATE PARTITION pruneslocks; only the partitions to be emptied are locked.
只锁truncate部分分区。
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.
免责声明：本站发布的内容（图片、视频和文字）以原创、转载和分享为主，文章观点不代表本网站立场，如果涉及侵权请联系站长邮箱：is@yisu.com进行举报，并提供相关证据，一经查实，将立刻删除涉嫌侵权内容。
