[MySQL Reference Manual] 20 分区

阅读量：5077 次

发布时间：2019-06-12

本文共 21379 字，大约阅读时间需要 71 分钟。

20 分区

可以通过show plugins查看是否支持分区。

mysql> SHOW PLUGINS;

+------------+----------+----------------+---------+---------+

+------------+----------+----------------+---------+---------+

| partition | ACTIVE | STORAGE ENGINE | NULL | GPL |

+------------+----------+----------------+---------+---------+

11 rows in set (0.00 sec)

也可以检查information_schema.plugins表检查是否支持。

如果partition的status不是active，或者没有记录。那么就不支持分区。

如果编译的时候已经支持了分区，就不需要去启动。如果想要不支持分区，可以使用参数—skip-partition选项。不启动分区后，不能看到已经分区的表，也不能删除他们。

20.1 MySQL的分区概述

SQL标准一般不会涉及到关于数据存放物理方面。SQL语言本身尽量从数据结构，schema，表，行，列中独立出来。但是很多高级的数据库管理系统都涉及了某些数据存放的物理位置，文件系统，硬件等。在MySQL，InnoDB存储引擎，支持表空间已经很久，MySQL服务可以把不同的数据库存放到不同的物理目录中。

分区者更进了一步，把一个表根据规则分布到文件系统。实际上表的不同分区以独立表的方式被保存在不同的位置上。用户选择的分区规则，在MySQL中可以是一个模块，一个range或者一个值的列表，或者内部hash函数，或者线性hash函数。这个方法根据用户指定的分区类型决定，参数为用户提供表达式的值。这个表达式可以是一个列的值，可以是一个或者多个列的值，也可以是列值的集合，根据分区类型决定。

比如RANGE,LIST,[LINEAR]HASH分区，把分区列传递到分区函数，然后返回一个整型表示该行应该存放的分区号。函数必须是非常量或者非随机。也不能包含查询但是可以使用SQL表达式只要表达式返回要不是NULL要不是整型数据。

对于[LINEAR] KEY,RANGE COLUMNS,LIST COLUMNS分区，分区表达式由一个或者多个列组成。

对于[LINEAR] KEY表达式，分区函数由MySQL提供。

这个就是水平分区，对于垂直分区目前不支持。大多数存储引擎是支持分区的，MySQL分区引擎是独立的一层，可以和其他引擎进行交互。在MySQL 5.7一个表的所有分区必须使用相同的引擎。以下引擎不支持分区：MERGE，CSV，FEDERATED存储引擎。

要指定分区存储引擎，和非分区表一样，指定engine参数：

CREATE TABLE ti (id INT, amount DECIMAL(7,2), tr_date DATE)

ENGINE=INNODB

PARTITION BY HASH( MONTH(tr_date) )

PARTITIONS 6;

每个分区都可以指定存储引擎，但是在MySQL 5.7中没有效果。

每个分区的数据和索引可以适应data directory 和index directory选项分配独立的目录。DATA DIRECTOY和INDEX DIRECTORY对于windows的myisam存储引擎不支持。Innodb所有平台都支持。

分区表达式中的涉及到的列必须是唯一索引的一部分，包括主键。也就是说以下表不能创建分区：

CREATE TABLE tnp (

id INT NOT NULL AUTO_INCREMENT,

ref BIGINT NOT NULL,

name VARCHAR(255),

PRIMARY KEY pk (id),

UNIQUE KEY uk (name)

);

因为pk，uk没有相同的列，没有列可以用来作为分区列。要么pk上加上name，id加到uk，也可以直接删掉uk。

分区表的一些好处：

1.分区表可以把一个表的数据分散到不同的文件系统或者磁盘中。

2.分区表的数据比较容易删除，可以直接删除一个分区。

3.如果where子句可以满足分区列，那么查询性能会得到提升。

其他的好处：

1.聚合函数，如果在分区表上可以并发。

2.数据分散在多个磁盘上，加大查询的吞吐量。

20.2 分区类型

20.2.1 RANGE分区

安装RANGE分区表的，行会根据RANGE的划分存放到分区中。RANGE是连续的但是没有重叠，使用VALEUS LESS THAN定义。对于store_id进行分区：

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从1到5都保存在p0分区中，6到10保存在p1中类推。在这里如果插入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 (11),

PARTITION p2 VALUES LESS THAN (16),

PARTITION p3 VALUES LESS THAN MAXVALUE

);

MAXVALUE表示最大值，比int型最大值还要大。所以大于16的都会被放在p3分区中。分区列也可以使用表达式：

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

);

那么在1991年之前离职的员工都会保存在p0依次类推。如果分区列时个时间戳字段，可以如下分区：

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)

);

RANGE在以下场景下非常有用：

1.想要删除老的数据，刚好在p0上，那么可以直接alter table employee drop partition p0删除分区，达到删除数据的目的

2.想要使用包含时间或者日期的列，或者其他连续的升序列。

3.想要频繁的根据分区列进行查询。可以快速的定位到某个分区。

有个RANGE分区的变种RANGE COLUMNS，可以多个列一起决定一个分区。

20.2.2 LIST分区

很多情况下range分区和list分区很像。LIST和RANGE的区别是LIST是一组值，RANGE是一组连续的区间。

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)

);

LIST分区删除数据也会很快比如要删除pWest分区数据，用阶段分区比delete快。和RANGE不同没有MAXVALUE。所有的值都要在分区里面，不然就报错。

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.11 sec)

mysql> INSERT INTO h2 VALUES (3, 5);

ERROR 1525 (HY000): Table has no partition for value 3

报错的时候，如果表示innodb表就会全部回滚。如果是非事务表，错误之前都插入了，之后的都没插入。

也可以通过IGNORE关键字，对错误行进行忽略，这样错误行就会自动被忽略，正常行就可以被全部插入。

mysql> TRUNCATE h2;

Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM h2;

Empty set (0.00 sec)

mysql> INSERT IGNORE INTO h2 VALUES (2, 5), (6, 10), (7, 5), (3, 1), (1, 9);

Query OK, 3 rows affected (0.00 sec)

Records: 5 Duplicates: 2 Warnings: 0

mysql> SELECT * FROM h2;

+------+------+

| c1 | c2 |

+------+------+

| 7 | 5 |

| 1 | 9 |

| 2 | 5 |

+------+------+

3 rows in set (0.00 sec)

20.2.3 COLUMNS分区

COLUMNS分区是RANGE和LIST的变种。COLUMNS分区可以可以使用多个列作为分解键。2个列都可以用来分配分区。另外RANGE COLUMNS和LIST COLUMNS分区支持费insert定义range或者list项。允许的字段类型如下：

1.所有int类型

2.date和datetime类型

3.字符串类型，char，varchar，binary和varbinary。TEXT和BLOB列不支持分区。

20.2.3.1 RANGE COLUMNS分区

RANGE列分区和RANGE分区很像。就是启用了多列range。另外你可以使用其他数据类型，不一定只用int类型。

RANGE COLUMNS和RANGE明显的区别有以下几点：

1.RANGE COLUMNS不能用表达式，只能是列名

2.RANGE COLUMNS可以使用一个或者多个列。

3.RANGE COLUMNS分区列可以不是int类型。

mysql> CREATE TABLE rcx (

-> a INT,

-> b INT,

-> c CHAR(3),

-> d INT

-> )

-> PARTITION BY RANGE COLUMNS(a,d,c) (

-> PARTITION p0 VALUES LESS THAN (5,10,'ggg'),

-> PARTITION p1 VALUES LESS THAN (10,20,'mmmm'),

-> PARTITION p2 VALUES LESS THAN (15,30,'sss'),

-> PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE,MAXVALUE)

-> );

Query OK, 0 rows affected (0.15 sec)

如创建了一个rcx表，有4个列，a,b,c进行分区。那么如果有一样要插入，根据a,b,c依次对比，来决定放入哪个分区。

20.2.3.2 LIST COLUMNS分区

MySQL 5.7支持LIST COLUMNS分区。是LIST分区的变种可以使用非int类型作为分区列，而且可以使用多个列。

20.2.4 Hash分区

使用HASH分区可以保证数据均匀的分布在各分区上面。使用RANGE，LIST分区需要显示给定值进行分区。使用hash分区，MySQL会帮你处理。

使用hash分区，使用create table子句PARTITION BY HASH(expr)，表达式返回int类型。然后需要指定分区个数，如PARTITIONS 4。

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;

如果不包含PARTITIONS 那么默认分区个数是1个。

20.2.4.1 LINEAR HASH分区

MySQL也支持线性hash分区，和传统分区不一样线性hash利用线性2的指数算法，来代替老的hash函数。

算法大致如下：

num表示分区个数

1.V=POWER(2,CEILING(LOG(2,NUM)))，V为num的2的最小幂次的值。

2.N=expr&(V-1)计算分区号

3.如果N>=num,N=N&(ceil(V/2)-1)，否则就用N编号的分区。

如表如下：

CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATE)

PARTITION BY LINEAR HASH( YEAR(col3) )

PARTITIONS 6;

插入了col3为‘20030414’的列：

V = POWER(2, CEILING( LOG(2,6) )) = 8

N = YEAR('2003-04-14') & (8 - 1)

= 2003 & 7

= 3

(3 >= 6 is FALSE: record stored in partition #3)

如果N>=num那么就需要额外计算，比如：

V = 8

N = YEAR('1998-10-19') & (8-1)

= 1998 & 7

= 6

(6 >= 6 is TRUE: additional step required)

N = 6 & CEILING(8 / 2)

= 6 & 3

= 2

(2 >= 6 is FALSE: record stored in partition #2)

使用线性hash的好处是增加，删除，合并分立分区会比较快，特别是对于T级别的表来说。坏处是不能像传统hash分区一样的数据均匀。

20.2.5 Key分区

Key分区和hash分区有点类似，但是和HASH分区有很多不同：

1.KEY不单单是hash

2.如果表有主键，key被用于分区的必须是表主键的一部分。不需要指定分区列。如果没有primary key但是有唯一建那么使用唯一建作为分区列。如果唯一建没有定义为 not null那么就会报错。

CREATE TABLE k1 (

id INT NOT NULL PRIMARY KEY,

name VARCHAR(20)

)

PARTITION BY KEY()

PARTITIONS 2;

20.2.6 子分区

子分区创建如图：

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

);

ts表有3个RANGE分区，然后每个range分区有2个hash子分区。表一共有6个分区。

MySQL 5.7可以对RANGE LIST分区的表进行子分区，子分区要不是hash要不是key。

也可以使用通过SUBPARTITION子句显示的指定选项，比如：

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

)

);

一些注意点：

1.每个分区的子分区个数要一样。

2.如果使用subpartition显示的定义分区，就必须定义所有的分区，否则就会失败。

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),

PARTITION p2 VALUES LESS THAN MAXVALUE (

SUBPARTITION s2,

SUBPARTITION s3

)

);

3.每个子分区必须包含子分区的分区名。

4.分配的子分区名必须在表内是唯一的。

20.2.7 MySQL分区如何处理NULL

在MySQL分区中，null会被认为小于任何非null的值和order by 一样。

RANGE分区处理NULL

RANGE分区中，NULL值会被放入最小的分区中。

LIST分区处理NULL

list分区中，如果分区list没有null值，插入就会报错。需要在分区list中指定null存放的位置。

Hash和Key分区NULL处理

hash和Key分区对NULL的处理方法和上面的不同，HASH和KEY会把null当成0来处理。

20.3 分区管理

20.3.1 RANGE和LIST分区管理

首先删除分区，删除一个分区可以直接用alter table … drop partition来实现。但是删除分区权限需要有表的drop权限。

如果要修改分区，但是不想丢失数据可以使用alter table …reorganize partition语句。

删除分区之后表结构变换，可以使用show create table来查看表结构变化。

如果使用range分区，要增加一个高端的分区可以使用alter table …add partition语句，比如：

CREATE TABLE members (

id INT,

fname VARCHAR(25),

lname VARCHAR(25),

dob DATE

)

PARTITION BY RANGE( YEAR(dob) ) (

PARTITION p0 VALUES LESS THAN (1970),

PARTITION p1 VALUES LESS THAN (1980),

PARTITION p2 VALUES LESS THAN (1990)

);

那么要增加一个2000的分区可以使用如下语句：

ALTER TABLE members ADD PARTITION (PARTITION p3 VALUES LESS THAN (2000)

但是如果要加一个小端的分区比如1960，那么就不能在使用这个语句，要重新整理分区才行，语句如下：

ALTER TABLE members

REORGANIZE PARTITION p0 INTO (

PARTITION n0 VALUES LESS THAN (1960),

PARTITION n1 VALUES LESS THAN (1970)

);

P0分区会被分为n0,n12个分区。

对于list分区可以直接通过alter table…add partition添加分区。但是如果list值里面已经包含在老的分区中就会报错。如果需要拆分已有的分区也需要使用alter table … reorganize partition语句。

语法如下：

ALTER TABLE tbl_name

REORGANIZE PARTITION partition_list

INTO (partition_definitions);

使用alter table… reorganize partition的时候有以下几个注意点：

1.Partition子句用来定义性分区的和create table的规则是一样的。

2.partition_Definitions中的分区定义要能够覆盖partition_list的分区。

3.对于range分区，partition_list必须是连续的不能跳过中间的一个分区。

4.修改表的分区类型不能通过alter table…reorganize partition，也不能修改分区的表达式或者列。只能通过语句修改：

ALTER TABLE members

PARTITION BY HASH( YEAR(dob) )

PARTITIONS 8;

20.3.2 管理HASH和KEY分区

被hash或者key分区的分区表不能删除分区。但是可以通过alter table…coalesce partition语句合并分区。如：

CREATE TABLE clients (

id INT,

fname VARCHAR(30),

lname VARCHAR(30),

signed DATE

)

PARTITION BY HASH( MONTH(signed) )

PARTITIONS 12;

表有12个分区，那么可以通过以下语句把分区收缩到4个。

mysql> ALTER TABLE clients COALESCE PARTITION 4;

Query OK, 0 rows affected (0.02 sec)

Alter table…coalesce partition可以使用在hash，key，linear hash或者linear key。

如果Alter table…coalesce partition指定了大于现在分区的个数，就会报错。

如果要增加分区可以使用alter table…add partition实现：

ALTER TABLE clients ADD PARTITION PARTITIONS 6;

20.3.3移动表的分区和子分区

在MySQL 5.7，可以使用alter table pt exchange partition p with table nt 来移动分区。pt表示一个分区表，p是分区或者子分区。移动到非分区表nt。nt的限制条件：

1.nt不是分区表

2.nt不是临时表

3.pt和nt 2个表的表结构一直。

4.nt没有外键约束，也没有其他表引用了nt

5.nt中没有行。如果加了without validation那么就不会验证这个条件。

需要alter，insert，create，drop的权限。

Alter table…exchange partiton注意点：

1.这个语句执行的时候不会调用任何触发器。

2.auto_increment会被重置

3.ignore关键字在这个语句的时候不会起作用。

如：

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");

mysql> CREATE TABLE e2 LIKE e;

Query OK, 0 rows affected (1.34 sec)

mysql> ALTER TABLE e2 REMOVE PARTITIONING;

Query OK, 0 rows affected (0.90 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> SELECT PARTITION_NAME, TABLE_ROWS

-> FROM INFORMATION_SCHEMA.PARTITIONS

-> WHERE TABLE_NAME = 'e';

+----------------+------------+

| PARTITION_NAME | TABLE_ROWS |

+----------------+------------+

| p0 | 1 |

| p1 | 0 |

| p2 | 0 |

| p3 | 3 |

+----------------+------------+

4 rows in set (0.00 sec)

mysql> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2;

Query OK, 0 rows affected (0.28 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.00 sec)

mysql> SELECT * FROM e2;

+----+-------+-------+

| id | fname | lname |

+----+-------+-------+

| 16 | Frank | White |

+----+-------+-------+

1 row in set (0.00 sec)

20.3.4 管理分区

1.重建分区。就好像删除表内所有数据，然后重新插入，减少碎片

ALTER TABLE t1 REBUILD PARTITION p0, p1;

2.优化分区。如果删除了大量数据或者修改了大量数据，使用语句回收不适用空间，减少碎片，innodb不支持对单个分区进行优化，使用rebuild和analyze代替。

ALTER TABLE t1 OPTIMIZE PARTITION p0, p1;

3.分析分区，读取key在分区的分布

ALTER TABLE t1 ANALYZE PARTITION p3;

4.修复分区数据。当分区有重复键的时候修复会报错。在MySQL 5.7.2之后可以使用alter ignore table，因为重复键问题导致不能应用的都会被删除。

ALTER TABLE t1 REPAIR PARTITION p0,p1;

5.检查分区。和检查表一样检查分区是否有问题

ALTER TABLE trb3 CHECK PARTITION p1;

这个命令会告诉你分区在表上是否正常，如果有错误运行alter table…repair进行修复。Check partition在用有重复键的时候会检查失败。同上用alter ignore table进行处理。

20.3.5 获取分区的信息

获取分区的方法有以下几个：

1.使用show create table

2.使用show table status

3.查询information_schema.partitions表

4.使用explain parititons select查看那些分区被select。

CREATE TABLE trb1 (id INT, name VARCHAR(50), purchased DATE)

PARTITION BY RANGE(id)

(

PARTITION p0 VALUES LESS THAN (3),

PARTITION p1 VALUES LESS THAN (7),

PARTITION p2 VALUES LESS THAN (9),

PARTITION p3 VALUES LESS THAN (11)

);

mysql> EXPLAIN PARTITIONS SELECT * FROM trb1\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: trb1

partitions: p0,p1,p2,p3

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 10

Extra: Using filesort

这种情况下所有的4个分区都被使用了，如下语句就只是用了2个分区：

mysql> EXPLAIN PARTITIONS SELECT * FROM trb1 WHERE id < 5\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: trb1

partitions: p0,p1

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 10

Extra: Using where

使用explain partitions要注意几个问题：

1.不能 explain partitions和extentded 不能一起使用会有语法错误。

2.如果explain partitions用来检查非分区表的查询，不会产生错误就是partitions变成null。

20.4 分区裁剪(Pruning)

分区裁剪其实很简单，就是查询的时候不扫描不符合条件的分区。比如有一个分区表定义如下：

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

);

当使用查询语句：

SELECT fname, lname, region_code, dob

FROM t1

WHERE region_code > 125 AND region_code < 130;

很明显如果这个条件p0和p3不会返回数据，所以我们需要在p1和p2上查找这样可以花很少的时间查找到匹配的数据。这种去掉不要用的分区就是裁剪。如果一个sql认为可以被裁剪，那么比在非分区表上要快。

那么如果where条件可以分解为，下面条件之一，就可以被裁剪：

1.partition_column = constant

2.partition_column IN (constant1, constant2, ...,constantN)

首先评估给定的值，决定哪个分区包含了这个值，然后扫描这个分区。很多情况下等号可以被一下符号代替，<, >, <=, >=, 和 <>。一些语句使用了between and也可以使用分区裁剪。

第二种情况，优化器评估表达式中的每个值，创建一个符合条件的分区列表，然后扫描分区分区中的列表。

分区裁剪可以使用在select，delete，update语句，但是不能使用在insert语句。

RANGE COLUMNS和LIST COLUMNS分区上使用分区裁剪。

这种优化不管是分区的表达式是由等号组成，或者是一个range但是可以被分解为等号，或者表达式表示一个增加或者减少的关系。

裁剪也可以在分区表达式为使用了year或者to_days 函数的 date或者datetime类型。假设有个表t2如下：

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 dob BETWEEN '1991-02-15' AND '1997-04-25'; DELETE FROM t2 WHERE dob >= '1984-06-21' AND dob <= '1999-06-21'

对于最后一个语句，优化器如下行为：

1.查找范围的最小所在的分区。那么在分区d3中。

2.查找范围的最大所在的分区。那么在分区d5中。

3.那么扫描2个分区范围内的所有分区。

对于如果分区时LIST分区的，表达式是递增或者递减的，比如t3:

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 BETWEEN 1 AND 3,

那么优化器会去查找1,2,3所在的分区，r0,r1会跳过r2,r3分区。

对于表分区是hash或者 linear 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;

对于简单的range也会使用分区裁剪，会把range当成in语句，比如：

SELECT * FROM t4 WHERE region_code > 2 AND region_code < 6; SELECT * FROM t4 WHERE region_code BETWEEN 3 AND 5;

2个语句都会被认为region_code in (3,4,5)。当表是hash或者Linear Key分区，裁剪只能是int类型，一下语句不会发生分区裁剪：

SELECT * FROM t4 WHERE dob >= '2001-04-14' AND dob <= '2005-10-15';

MySQL 5.7.1，分区裁剪对于自动分区的都不能使用。

20.5 分区选择

MySQL 5.7支持显示的选择分区或者子分区。分区选择和分区裁剪有点类似但是有点不同：

1.分区选择需要显示指定，不是自动完成的。

2.分区选择可以支持所有的DML语句。

语法如下：

PARTITION (partition_names)

      partition_names:

          partition_name, ...

这个选项紧跟表后面，分区名使用逗号分隔。如果指定的分区名找不到会报错。当partition已经使用，那么只会在这个分区查找。

20.6 分区的限制和缺点

禁止的结构，一下结构不能用在分区表达式上：

1.存储过程，存储方法，用户自定义函数，插件。

2.定义的变量和用户变量

算术操作和逻辑操作，使用+，-，*在分区表达式是允许的，但是结果不能为null，并且是一个int类型。（Linear key分区除外）。DIV操作是允许的但是不能用/。逻辑操作可以使用|，&,^,<<,>>，~ 操作是不允许的。

HANDEL语句，之前分区表不支持HANDEL语句，MySQL 5.7.1之后允许使用。

Server SQL Mode，在分区表创建后，用户定义分区不在维护sql mode。

性能问题，一些分区操作对性能有影响的如下：

· 文件系统操作，分区和重新分区操作依赖于文件系统操作。也就是说影响速度的有，文件系统类型，特点，磁盘速度，交换区空间，系统文件处理性能，和MySQL选项和变量对文件的处理。特别是保证large_files_support和open_files_limit的设置对于myisam的分区表，增加myisam_max_sort_file_size可能会提高性能。分区和重新分区操作如果是innodb表应该要启动innodb_file_per_table。

· MyISAM和分区文件描述符的使用，对于分区的MyISAM表，MySQL为每个分区使用2个文件描述符，对于分区表来说消耗的描述符大于普通表的描述符。

· 表锁，在执行分区操作的时候表上有写锁。那么读取这个表相对来说是不影响的，但是insert和update会等到知道分区操作完成。

· 存储引擎，分区操作，查询和更新操作通常在myisam中比innodb中要快。

· 索引，分区裁剪，一般使用索引就可以加快查询速度，特别是可以完美的利用分区裁剪的查询。

· LOAD Data性能，在MySQL 5.7 Load data使用buffer来提高导入性能。要注意每个分区使用130KB的buffer。

分区最大个数，如果不适用NDB，那么分区最大个数是8192个包括子分区。

Query Cache不支持，查询缓存不被支持。

每个分区的Key cache，在MySQL 5.7，key cache支持MyISAM表。

Innodb分区表不支持外键，更严格的是：

1.innodb分区表不能使用外建，包含了外键的innodb不能定义分区

2.innodb表不能引用分区表的外键，innodb分区表不能被引用。

Alter table order by，这个语句只会在各个分区内排序。

使用replace修改primary key，可能会导致语句被彻底修改。

全文索引，分区表不支持全文索引或者扫描。

空间列，空间数据类型不能被分区表支持。

临时表，临时表不能是分区表。

Log表，log表不能使用分区。

数据类型和分区key，分区关键字要不是int，要不是表达式返回int。enum类型也不能使用。有2个列外：

1. [Linear] Key分区支持任何非text，blob分区字段类型。

2.当使用range columns或者list columns分区可以使用date，datetime作为分区列。

子查询，分区key不能是子查询

子分区，子分区必须使用hash或者key分区。只有range和list分区可以有子分区。

DATA DIRECTORY和INDEX DIRECTORY,data directory和index directory如果是分区表有以下限制：

1.表级别的选项会被忽略

2.在windows 的时候，不能为myisam表指定某个分区或者子分区的该选项。

修复和重建分区表，分区表支持check table，optimize table，analyze table和repair table。

FLUSH TABLES的FOR EXPORT，对于FLUSH TABLES的FOR EXPORT不被分区表支持。

20.6.1 分区键，主键，唯一键

所有的使用在分区表达式的列必须是唯一键的一部分。如果没有唯一键，包括没有主键，那么就不会有这个限制。

20.6.2 各个存储引擎分区表限制

MERGE存储引擎,用户定义分区表和MERGE存储引擎不兼容。

FEDERATED存储引擎，FEDERATED表不支持分区表

CSV存储引擎，不支持分区表

InnoDB存储引擎，InnoDB外键和MySQL分区不兼容。分区innodb表不能有外键。Innodb引用了外键的不能是分区表。

NDB存储引擎,NDB支持KEY分区

20.6.3 分区表对函数的限制

只有一下函数可以被分区表支持：

	(see )

	(see )	(see )



	(with columns)

20.6.4 分区和锁

对于MyISAM存储引擎，对于MySQL 5.6.5之前的，会锁整个表知道操作完成，在MySQL 5.7 分区锁会裁剪不需要的锁，比如读取或者更新分区表，只有受影响的表才会被锁。如果使用了innodb分区表，使用行级别锁，因此不会像myisam一样有锁的分区裁剪。

DML语句

Select语句只会锁定需要读取的分区

Update锁裁剪只会对更新非分区列有用。

Replace和insert锁只会锁要修改的或者插入的分区。如果auto_increment值被每个列生成那么所有分区都会锁。

Insert…on duplicate key update只会在非分区列上才会裁剪。

Insert…select只会锁定被读取的表，目标表的所有的分区都会被锁。

Load data锁不能被裁剪。

对于before insert，before update触发器都不可以使用分区裁剪，因为触发器可能会修改值。

DDL语句

Create view不会有锁

Alter table…exchange partition，锁裁剪，只会锁定被切换分区的锁。

Alter table…truncate partition，锁裁剪，只会锁定被截断的分区。

另外alter table都会锁元数据

其他语句

Lock tables不能裁剪分区。

Call stored_procedure(expr)支持锁裁剪，但是expr不行

Do和set语句不支持锁裁剪

posted on

2016-05-24 17:04 阅读(

...) 评论(

...)

转载于:https://www.cnblogs.com/Amaranthus/p/5524083.html

你可能感兴趣的文章

jquery实现限制textarea输入字数

查看>>

thinkphp5 csv格式导入导出（多数据处理）

查看>>

PHP上传RAR压缩包并解压目录

查看>>

Codeforces 719B Anatoly and Cockroaches

jquery datagrid 后台获取datatable处理成正确的json字符串

poj1981 Circle and Points 单位圆覆盖问题

查看>>

POP的Stroke动画

查看>>

SQL语句在查询分析器中可以执行，代码中不能执行

查看>>