MySQL(九)：分区，java程序员面试题解大全

INSERT INTO t4 SELECT 9;

INSERT INTO t4 SELECT 10;

INSERT INTO t4 SELECT 15;

INSERT INTO t4 SELECT 16;

//查看分区情况

SELECT partition_name,table_rows FROM information_schema.PARTITIONS WHERE table_schema = DATABASE() AND table_name = “t4”;

那如果我们插入超过分区范围外的id呢

INSERT INTO t4 SELECT 25;

它会报错

针对这个问题，我们可以给分区添加一个MAX VALUE值得分区，MAX VALUE可以理解为正无穷，因此所有大于等于20并且小于MAX VALUE的值都可以放入p2分区，如下所示

ALTER TABLE t4 ADD PARTITION(PARTITION p2 VALUES LESS THAN maxvalue);

//下面SQL就可以执行成功了

INSERT INTO t4 SELECT 30;

RANGE分区主要用于日期列的分区，例如，对于销售类的类，可以根据年份来区存放销售记录，比如下面对sale表进行分区

CREATE TABLE sales(

money INT UNSIGNED NOT NULL,

cur DATETIME

)ENGINE=INNODB

PARTITION BY RANGE (YEAR(cur))(

PARTITION p2008 VALUES LESS THAN (2009),

PARTITION p2009 VALUES LESS THAN (2010),

PARTITION p2010 VALUES LESS THAN (2011)

);

//再进行插入数据

INSERT INTO sales SELECT 100,“2008-01-01”;

INSERT INTO sales SELECT 100,“2008-02-01”;

INSERT INTO sales SELECT 200,“2008-01-02”;

INSERT INTO sales SELECT 100,“2008-03-01”;

INSERT INTO sales SELECT 200,“2008-03-01”;

这样创建的好处之一就是便于对sales表的管理，如果要删除2008年的数据，我们一般会执行下面这条SQL

DELETE FROM sales WHERE cur >= “2008-01-01” and cur < “2009-01-01”;

使用了分区之后，我们只需删除2008年数据所在的分区即可，执行的SQL如下

ALTER TABLE sales DROP PARTITION p2008;

通过EXPLAIN PARTITION命令我们可以发现，上述语句中，SQL优化器只需要检索p2008这个分区，而不会去搜索所有的分区——称为Partition Pruning(分区修剪)，故查询的速度得到了大幅度的提升。但需要注意的是，如果执行下列语句，结果是一样的，但是优化器的选择会出现不同

EXPLAIN SELECT * FROM sales WHERE cur >= “2008-01-01” AND cur < “2009-01-01”;

可以看到partitions列只有p2009这个分区，证明了只查找了p2009这个分区

EXPLAIN SELECT * FROM sales WHERE cur >= “2009-01-01” AND cur < “2010-01-02”;

可以看到现在就查询了两个分区。

LIST分区

LIST分区和RANGE分区非常相似，只是分区列的值是离散的，而非连续的，例如

CREATE TABLE haha(

a INT,

b INT

)ENGINE=INNODB

PARTITION BY LIST(b)(

PARTITION p0 VALUES IN (1,3,5,7,9),

PARTITION p1 VALUES IN (0,2,4,6,8)

);

两句SQL的不同之处，仅仅在于RANGE分区使用的是 LESS THAN，而LIST分区使用的是IN，因为每个区的值是离散的，因此只能定义值。例如向表中插入下面的一些数据

INSERT INTO haha SELECT 1,1;

INSERT INTO haha SELECT 1,2;

INSERT INTO haha SELECT 1,3;

INSERT INTO haha SELECT 1,4;

下面我们来看一下表里面的情况

SELECT table_name,partition_name,table_rows FROM information_schema.PARTITIONS WHERE table_name = “haha” AND table_schema = DATABASE();

同理，如果插入的值不在分区上，同样会抛出异常

INSERT INTO haha SELECT 1,11;

注意

在执行Insert操作插入多个行数据的过程中如果遇到分区未定义的值，MyISAM和InnoDB存储引擎的处理会完全不同，MyISAM会将之前的行数据都插入，但之后的数据不会被插入，而InnoDB则会将整个插入过程视为一个业务，最终的结果是会进行回滚到正常状态。

HASH分区

HASH分区的目的是将数据均匀地分布到预先定义的各个分区中，保证各分区的数据数量大致是一样的。在 Range和LIST分区中，必须明确指定一个给定的列值或列值范围应该保存在哪个分区中；而在HASH分区中，MySQL会自动完成这些工作(不需要自己去规划分区，就是不需要像LIST和RANGE一样，还要去设置范围和名字)，用户所要做的只是基于将要被散列的列值指定一个列值或表达式，以及指定被分区的表将要被分割成的分区数量。

要使用HASH分区来分隔一个表，要在CREATE TABLE语句上添加一个PARTITION BY HASH（expr）子句，其中"expr"就是表达式，要返回一个整形的表达式，expr可以仅仅是字段类型为MySQL整形的列名，此外，用户很可能需要在后面再添加一个PARTITIONS num子句，其中num是一个非负的整数，它表示表将要被分割成分区的数量，如果没有这个子句的话，那么默认为1。

CREATE TABLE t_hash(

a INT,

b DATETIME

)ENGINE=INNODB

PARTITION BY HASH(YEAR(b))

PARTITIONS 4; //分成4个区

它的散列算法使用的是下面的算法

M O D ( Y E A R ( 日 期 ) , 分 区 数 量 ) MOD(YEAR(日期),分区数量) MOD(YEAR(日期),分区数量)，即取日期的年份然后余上分区数量

分区是按照YEAR函数进行的，而这个值本身是离散的，可能会不太均匀，但如果对连续的值进行HASH分区，比如自增长的主键，则可以较好地将数据进行平均分布。

MySQL数据库还支持一种称为LINEAR HASH的分区，它使用一个更加复杂的算法来确定新行插入到已经分区的表中的位置，它的语法和HASH分区的语法相似，只是将关键字HASH改为LINEAR HASH。下面创建一个LINEAR HASH的分区表t_linear_hash

CREATE TABLE t_linear_hash(

a INT,

b DATETIME

)ENGINE=INNODB

PARTITION BY LINEAR HASH(YEAR(b))

PARTITIONS 4;

它的算法就比较复杂一点

• 首先取大于或等于分区数量的下一个2的幂值 V = POWER(2,CEILING(LOG(2,num))) = 4 ,num为分区数量

• 所在分区 N = YEAR(‘日期’) & (V-1)

CEILING函数是对参数进行向上取整，LOG（a,b）是进行求 l o g a = b log_a = b loga​=b，其实就是通过向上取整，来找到比分区数量大或者相等的下一个2的幂值（ 2 l o g 2 4 = 4 2^{log_24} = 4 2log2​4=4，所以POWER的幂为LOG时，其实就是等于num，只不过这里使用向上取整然他变大）

现在来谈谈&运算，这个是关于二进制的运算，比如a&b，其实就是将a和b都化成二进制，然后进行与运算，相同位并且都为1才为1，其余都为0，那这里为什么要跟V-1进行与运算呢？因为跟V-1进行与运算的话，那么得到的最大值顶多就是V-1，不可能大于V-1（分区从0开始算起）。

LINEAR HASH分区的优点在于增加、删除、合并和拆分分区将会变得更加快捷，这有利于处理含有大量数据的表，但LINEAR HASH分区的缺点在于与使用HASH分区得到的数据分布相比，各个分区间数据的分布可能不太均匀，也就是散列程度会比HASH分区低（因为幂值V是固定的，所以V-1也是固定的，所以只要后面位数V-1化成二进制的位数对应相同，那么就会在同一个分区）

KEY分区

KEY分区和HASH分区相似，不同之处在于HASH分区通过用户定义的函数进行分区（其实只是可以自己规定，比如使用YEAR），KEY分区使用MySQL数据库提供的函数进行分区，NDB Cluster引擎使用MD5函数来分区，对于其他存储引擎，MySQL数据库使用其内部的散列函数来分区，这些函数基于与PASSWORD()一样的运算法则。

当在KEY中使用关键字Linear时，效果跟HASH一样，不是通过余数方法得到分区值，而是通过求幂，然后进行与运算得到。

COLUMNS分区

在前面介绍的4种分区，都有一个共同点，就是分区的条件必须是一个整形，如果不是整形，那么就需要通过MySQL内置函数来将它化成整形，比如YEAR()、TO_DAYS()、MONTH()等函数。不过从MySQL5.5开始支持COLUMNS分区，可以将它当成是RANGE分区和LIST分区的一种进化。COLUMNS分区可以使用非整形来进行分区，分区根据类型直接比较得到，不需要转化为整形，此外RANGE COLUMNS分区可以对多个列的值进行分区。

COLUMNS分区支持以下数据类型：

• 所有的整数类型，如INT、SMALLINT、TINYINT和BIGINT。对于FLOAT和DECIMAL(浮点数类型)则不予支持。

• 日期类型，如DATE和DATETIME，对其余的日期类型不予支持

• 字符串类型，如CHAR、VARCHAR、BINARY和VARBINARY。对BLOB和TEXT类型不予支持（即对超大的VARCHAR和超大的BINARY不支持）

对于日期类型的分期，不再需要使用YEAR()和TO_DAYES()等函数来将它化成整形，可以直接使用COLUMNS。

举个栗子（这里使用RANGE COLUMNS）

CREATE TABLE t_columns_range(

a INT,

b DATETIME

)ENGINE=INNODB

PARTITION BY RANGE COLUMNS(b)(

PARTITION p0 VALUES LESS THAN (“2009-01-01”),

PARTITION p1 VALUES LESS THAN (“2010-01-01”)

);

同样地，可以使用字符串进行分区

举个栗子（这里使用LIST COLUMNS）

CREATE TABLE customer(

first_name VARCHAR(25),

last_name VARCHAR(25),

street_1 VARCHAR(25),

street_2 VARCHAR(25),

city VARCHAR(15),

renewal DATE

)ENGINE=INNODB

PARTITION BY LIST COLUMNS(city)(

PARTITION pRegion_1 VALUES IN(“shanghai”,“foshan”),

PARTITION pRegion_2 VALUES IN(“guangzhou”,“beijing”)

) ;

对于RANGE COLUMNS分区，可以使用多个列进行分区

CREATE TABLE rcx(

a INT,

b INT,

c CHAR(3),

d INT

)ENGINE=INNODB

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

PARTITION p0 VALUES LESS THAN(5,10,‘a’),

PARTITION p1 VALUES LESS THAN(10,20,‘b’),

PARTITION p2 VALUES LESS THAN(maxvalue,maxvalue,maxvalue)

);

MySQL5.5开始支持COLUMNS分区，对于之前的RANGE和LIST，最好都使用RANGE COLUMNS和LIST COLUMNS来进行代替。

子分区

子分区其实是在分区的基础上再进行分区，有时也称这种分区为复合分区，MySQL数据库允许在RANGE和LIST的分区上再进行HASH或KEY的子分区。

举个栗子

CREATE TABLE ts(

a INT,

b DATE

)ENGINE=INNODB

PARTITION BY RANGE(YEAR(b))

SUBPARTITION BY HASH(TO_DAYS(b))

SUBPARTITIONS 2 (

PARTITION p0 VALUES LESS THAN (1990),

PARTITION p1 VALUES LESS THAN (2000),

PARTITION p2 VALUES LESS THAN maxvalue

);

子分区使用的是SUBPARTITIONS关键字，这里是首先对YEAR（b）进行RANGE分区，分成了三个（1990，2000，maxvalue），然后子分区使用HASH进行分区，又分成了2分，所以总的来说，有3*2=6个分区。

我们也可以为每个子分区来定义它的名字。

CREATE TABLE ts2(

a INT,

b DATE

)ENGINE=INNODB

PARTITION BY RANGE(YEAR(b))

SUBPARTITION BY HASH(TO_DAYS(b))(

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来明确定义任何子分区，那么就必须定义所以的子分区，也就是说不可以在一个分区上详细定义了子分区，然后另一个分区不详细定义。

//下面的SQL是错的

CREATE TABLE ts(

a int,

b date

)ENGINE=INNODB

PARTITION BY RANGE(YEAR(b))

SUBPARTITION BY HASH(TO_DAYS(b))(

PARTITION P0 VALUES LESS THAN(2019)(

SUBPARTITION s0,

SUBPARTITION s1

),

PARTITION p1 VALUES LESS THAN(2020)(

SUBPARTITION S2,

SUBPARTITION S3

),

PARTITION p2 VALUES LESS THAN maxvalue

);

1. 每个SUBPARTITION子句必须包括子分区的一个名字

2. 子分区的名字必须是唯一的，不可以出现重复

子分区可以用于特别大的表，在多个磁盘间分别分配数据和索引，假设有3个磁盘，为/disk0、/disk1、/disk2等

那么可以使用下面SQL进行分配嘛？

CREATE TABLE ts(

a INT,

b DATE

)ENGINE=INNODB

PARTITION BY RANGE(YEAR(b))

SUBPARTITION BY HASH(TO_DAYS(b))(

PARTITION p0 VALUES LESS THAN(2000)(

SUBPARTITION s0,

DATE DIRECTORY ‘/disk0/data’,

INDEX DIRECTORY ‘/disk0/idx’

),

PARTITION p1 VALUES LESS THAN(2001)(

SUBPARTITION s1,

DATE DIRECTORY ‘/disk1/data’,

INDEX DIRECTORY ‘/disk1/idx’

),

PARTITION p2 VALUES LESS THAN maxvalue(

SUBPARTITION s2,

DATE DIRECTORY ‘/disk2/data’,

INDEX DIRECTORY ‘/disk2/idx’

)

)

这句SQL是没用的，由于InnoDB存储引擎使用表空间自动地进行数据和索引的管理，会忽略DATA DIRECTORY和INDEX DIRECTOR语法，因此上诉分区表的数据和索引文件分开放置对InnoDB存储引擎表是无效的，并不会按照写的代码去进行写入

分区中的NULL值

MySQL数据库允许对NULL值进行分区，但是处理方法可能与其他数据库完全不同，MySQL数据库的分区总是把NULL值视为比任何一个非NULL值小，对于不同得分区类型，MySQL数据库对NULL值处理也是各不相同的。

对于分区中的NULL值，如果向分区列插入了NULL值，那么MySQL会将它分到最左边的分区，也就是LESS THAN最小的那个分区

首先先建立一张表

CREATE TABLE dododi(

a INT,

b INT

)ENGINE=INNODB

PARTITION BY RANGE(b)(

PARTITION p0 VALUES LESS THAN(10),

PARTITION p1 VALUES LESS THAN(20),

PARTITION p2 VALUES LESS THAN maxvalue

);

//插入一些数据

INSERT INTO dododi SELECT 1,1;

INSERT INTO dododi SELECT 1,NULL;

SELECT * FROM dododi;

//查看分区情况

SELECT table_name,partition_name,table_rows FROM information_schema.PARTITIONS WHERE table_schema = DATABASE() AND table_name = “dododi”;

可以看到两条数据都被分正在p0区上，也就证明了在RANGE分区下，NULL值会被放在最左分区中。因此，在删除p0分区时，要记得NULL的数据也会被删除。

在LIST分区中使用NULL时，因为LIST是离散型的，所以要在分区中规定NULL，否则插入时会报错，举个栗子

//创建这个表

CREATE TABLE list_no_null(

a INT,

b INT

)ENGINE=INNODB

PARTITION BY LIST(b)(

PARTITION p0 VALUES IN(1,2,3,4),

PARTITION p1 VALUES IN(5,6,7,8)

);

//进行插入

INSERT INTO list_no_null SELECT 1,NULL;

报错如下

//现在给表加上我们的NULL分区

ALTER TABLE list_no_null ADD PARTITION(PARTITION p3 VALUES IN(9,10,NULL));

//再进行插入

INSERT INTO list_no_null SELECT 1,NULL;

//查询分区情况

SELECT table_name,partition_name,table_rows FROM information_schema.PARTITIONS WHERE table_schema = DATABASE() AND table_name = “list_no_null”;

可以看到插入进了p3分区

HASH和KEY分区对于NULL的处理方式与RANGE与LIST分区都不一样，任何分区函数都会将含有NULL值得记录记为0，即NULL == 0

//创建表

CREATE TABLE hash_null(