分区功能并不是在存储引擎层完成的，因此不只有InnoDB存储引擎支持分区，常见的存储引擎MyISAM、NDB等都支持。但也并不是所有的存储引擎都支持，如CSV、FEDERATED、MERGE等就不支持。在使用分区功能前，应该了解所选择的存储引擎对于分区的支持。
MySQL数据库在5.1版本时添加了对于分区的支持，这个过程是将一个表或者索引物分解为多个更小、更可管理的部分。就访问数据库的应用而言，从逻辑上讲，只有一个表或者一个索引，但是在物理上这个表或者索引可能由数十个物理分区组成。每个分区都是独立的对象，可以独自处理，也可以作为一个更大对象的一部分进行处理。
MySQL数据库支持的分区类型为水平分区
水平分区，指同一表中不同行的记录分配到不同的物理文件中。
垂直分区，指将同一表中不同的列分配到不同的物理文件中。
此外，MySQL数据库的分区是局部分区索引，一个分区中既存放了数据又存放了索引。
可以通过以下命令来查看当前数据库是否启用了分区功能：
show variables like '%partition%';
show pluginsG
大多数DBA会有这样一个误区：只要启用了分区，数据库就会变得更快。这个结论是存在很多问题的。就我的经验来看，分区对于某些SQL语句性能可能会带来提高，但是分区主要用于高可用性，利于数据库的管理。在OLTP应用中，对于分区的使用应该非常小心。总之，如果你只是一味地使用分区，而不理解分区是如何工作的，也不清楚你的应用如何使用分区，那么分区极有可能只会对性能产生负面的影响。
当前MySQL数据库支持以下几种类型的分区：
- RANGE分区：行数据基于属于一个给定连续区间的列值放入分区。MySQL数据库5.5开始支持RANGE COLUMNS的分区。
- LIST分区：和RANGE分区类似，只是LIST分区面向的是离散的值。MySQL数据库5.5开始支持LIST COLUMNS的分区。
- HASH分区：根据用户自定义的表达式的返回值来进行分区，返回值不能为负数。
- KEY分区：根据MySQL数据库提供的哈希函数来进行分区。
不论创建何种类型的分区，如果表中存在主键或者是唯一索引时，分区列必须是唯一索引的一个组成部分，因此下面创建分区的SQL语句是会产生错误的：
CREATE TABLE t1(
col1 INT NOT NULL,
col2 DATE NOT NULL,
col3 INT NOT NULL,
col4 INT NOT NULL,
UNIQUE KEY(col,col2)
)
PARTITION BY HASH(col3)
PARTITIONS 4;
ERROR 1503（HY000）：A PRIMARY KEY must include all columns in the table's
partitioning function
唯一索引可以是允许NULL值的，并且分区列只要是唯一索引的一个组成部分，不需要整个唯一索引列都是分区列，如下代码所示。
CREATE TABLE t1(
col1 INT NULL,
col2 DATE NULL,
col3 INT NULL,
col4 INT NULL,
UNIQUE KEY(col1,col2,col3,col4)
)
PARTITION BY HASH（col3）
PARTITIONS 4；
当建表时没有指定主键，唯一索引时，可以指定任何一个列为分区列，因此下面2句创建分区的SQL语句都是可以正确运行的：
CREATE TABLE t1(
col1 INT NULL,
col2 DATE NULL,
col3 INT NULL,
col4 INT NULL
)engine=innodb
PARTITION BY HASH(col3)
PARTITIONS 4;
drop table t1;
mysql＞CREATE TABLE t1(
col1 INT NULL,
col2 DATE NULL,
col3 INT NULL,
col4 INT NULL,
key(col4)
)engine=innodb
PARTITION BY HASH（col3）
PARTITIONS 4;
RANGE分区
第一种类型是RANGE分区，也是最常用的一种分区类型。
下面的CREATE TABLE语句创建了一个id列的区间分区表。当id小于10时，数据插入p0分区。当id大于等于10小于20时，插入p1分区：
create table t(id int) engine=innodb
partition by range(id)(
partition p0 values less than(10),
partition p1 values less than(20));
查看表在磁盘上的物理文件，启用分区之后，表不再由一个ibd文件组成了，而是由建立分区时的各个分区ibd文件组成，如下所示的t#P#p0.ibd，t#P#p1.ibd：
system ls -lh /usr/local/mysql/data/test2/t*
-rw-rw----1 mysql mysql 8.4K 7月31 14：11/usr/local/mysql/data/test2/t.frm
-rw-rw----1 mysql mysql 28 7月31 14：11/usr/local/mysql/data/test2/t.par
-rw-rw----1 mysql mysql 96K 7月31 14：12/usr/local/mysql/data/test2/t#P#p0.ibd
-rw-rw----1 mysql mysql 96K 7月31 14：12/usr/local/mysql/data/test2/t#P#p1.ibd
接着插入如下数据：
insert into t select 9;
insert into t select 10;
insert into t select 15;
因为表t根据列id进行分区，因此数据是根据id列的值的范围存放在不同的物理文件中的.
可以通过查询information_schema架构下的PARTITIONS表来查看每个分区的具体信息：
select * from information_schema.PARTITIONS where table_schema=database() and table_name='t'G;
TABLE_ROWS列反映了每个分区中记录的数量。由于之前向表中插入了9、10、15三条记录，因此可以看到，当前分区p0中有1条记录、分区p1中有2条记录。PARTITION_METHOD表示分区的类型，这里显示的是RANGE。
对于表t，因为我们定义了分区，因此对于插入的值应该严格遵守分区的定义，当插入一个不在分区中定义的值时，MySQL数据库会抛出一个异常。
如下所示，我们向表t中插入30这个值：
insert into t select 30;
ERROR 1526（HY000）：Table has no partition for value 30
对于上述问题，我们可以对分区添加一个MAXVALUE值的分区。MAXVALUE可以理解为正无穷，因此所有大于等于20并且小于MAXVALUE的值放入p2分区：
alter table t add partition(partition p2 values less than maxvalue);
insert into t select 30;
RANGE分区主要用于日期列的分区，如对于销售类的表，可以根据年来分区存放销售记录，如以下所示的分区表sales：
create table sales(
money int unsigned not null,
date datetime
)engine=innodb
partition by range(YEAR(date))(
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,'2009-03-01';
insert into sales select 200,'2010-03-01';
这样创建的好处是，便于对sales这张表的管理。如果我们要删除2008年的数据，就不需要执行DELETE FROM sales WHERE date＞='2008-01-01'and date＜'2009-01-01'，而只需删除2008年数据所在的分区即可：
alter table sales drop partition p2008;
这样创建的另一个好处是，可以加快某些查询的操作。如果我们只需要查询2008年整年的销售额：
explain partitions select * from sales where date＞='2008-01-01' and date＜='2008-12-31'G;
通过EXPLAIN PARTITION命令我们可以发现，在上述语句中，SQL优化器只需要去搜索p2008这个分区，而不会去搜索所有的分区，因此大大提高了执行的速度。
需要注意的是，如果执行下列语句，结果是一样的，但是优化器的选择又会不同了：
explain partitions select * from sales where date＞='2008-01-01' and date＜'2009-01-01'G;
这次条件改为date＜'2009-01-01'而不是date＜='2008-12-31'时，优化器会选择搜索p2008和p2009两个分区，这是我们不希望看到的。因此对于启用分区，你应该根据分区的特性来编写最优的SQL语句。
对于sales这张分区表，我曾看到过另一种分区函数，设计者的原意是想可以按照每年每月来进行分区，如：
create table sales(
money int unsigned not null,
date datetime
)engine=innodb
partition by range(YEAR(date)*100+MONTH(date))(
partition p201001 values less than (201002),
partition p201002 values less than (201003),
partition p201003 values less than (201004)
);
但是在执行SQL语句时开发人员会发现，优化器不会根据分区进行选择，即使他们编写的SQL语句已经符合了分区的要求，如：
explain partitions select * from sales where date＞='2010-01-01' and date＜='2010-01-31'G；
***************************1.row*************************** id：1 select_type：SIMPLE table：sales partitions：p201001，p201002，p201003 type：ALL possible_keys：NULL key：NULL key_len：NULL ref：NULL rows：4 Extra：Using where 1 row in set（0.00 sec）
可以看到优化对分区p201001、p201002、p201003都进行了搜索。产生这个问题的主要原因是，对于RANGE分区的查询，优化器只能对YEAR()、TO_DAYS()、TO_SECONDS()、UNIX_TIMESTAMP()这类函数进行优化选择，因此对于上述的要求，需要将分区函数改为TO_DAYS，如：
create table sales(
money int unsigned not null,
date datetime
)engine=innodb
partition by range(to_days(date))(
partition p201001 values less than(to_days('2010-02-01')),
partition p201002 values less than(to_days('2010-03-01')),
partition p201003 values less than(to_days('2010-04-01'))
);
这时再进行相同类型的查询，优化器就可以对特定的分区进行查询了：
explain partitions select * from sales where date＞='2010-01-01' and date＜='2010-01-31'G;
LIST分区
LIST分区和RANGE分区非常相似，只是分区列的值是离散的，而非连续的。如：
create table t(
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)
);
不同于RANGE分区中定义的VALUES LESS THAN语句，LIST分区使用VALUES IN，所以每个分区的值是离散的，只能是定义的值。如我们往表中插入一些数据：
insert into t select 1,1;
insert into t select 1,2;
insert into t select 1,3;
insert into t select 1,4;
select table_name,partition_name,table_rows from information_schema.PARTITIONS where table_name='t' and table_schema=database();
如果插入的值不在分区的定义中，MySQL数据库同样会抛出异常：
insert into t select 1,10;
ERROR 1526（HY000）：Table has no partition for value 10
在用INSERT插入多个行数据的过程中遇到分区未定义的值时，MyISAM和InnoDB存储引擎的处理完全不同。MyISAM引擎会将之前的行数据都插入，但之后的数据不会被插入。而InnoDB存储引擎将其视为一个事务，因此没有任何数据插入。
先对MyISAM存储引擎进行演示，如：
create table t(
a int,
b int
)engine=myisam
partition by list(b)(
partition p0 values in (1,3,5,7,9),
partition p1 values in (0,2,4,6,8)
);
insert into t values(1,2),(2,4),(6,10),(5,3);
ERROR 1526（HY000）：Table has no partition for value 10
select * from t;
可以看到对于插入的（6，10）记录没有成功，但是之前的（1，2），（2，4）记录都已经插入成功了。
而同一张表，存储引擎换成InnoDB，则结果完全不同：
truncate table t;
alter table t engine=innodb;
insert into t values(1,2),(2,4),(6,10),(5,3);
ERROR 1526（HY000）：Table has no partition for value 10
select * from t;
Empty set（0.00 sec）
可以看到同样在插入(6,10)记录是报错，但是没有任何一条记录被插入表t中。因此在使用分区时，也需要对不同存储引擎支持的事务特性进行考虑。
HASH分区
HASH分区的目的是将数据均匀地分布到预先定义的各个分区中，保证各分区的数据数量大致都是一样的。在RANGE和LIST分区中，必须明确指定一个给定的列值或列值集合应该保存在哪个分区中；而在HASH分区中，MySQL自动完成这些工作，你所要做的只是基于将要被哈希的列值指定一个列值或表达式，以及指定被分区的表将要被分割成的分区数量。
要使用HASH分区来分割一个表，要在CREATE TABLE语句上添加一个“PARTITION BY HASH(expr)”子句，其中“expr”是一个返回一个整数的表达式。它可以仅仅是字段类型为MySQL整型的一列的名字。此外，你很可能需要在后面再添加一个“PARTITIONS num”子句，其中num是一个非负的整数，它表示表将要被分割成分区的数量。如果没有包括一个PARTITIONS子句，那么分区的数量将默认为1。
下面的例子创建了一个HASH分区的表t，按日期列b进行分区：
create table t_hash(
a int,
b datetime
)engine=innodb
partition by hash(YEAR(b))
partitions 4;
如果将一个列b为2010-04-01这个记录插入表t_hash中，那么保存该条记录的分区确定如下。
MOD(YEAR('2010-04-01'),4)
=MOD(2010,4)
=2
因此会放入分区2中，我们可以按如下方法来验证：
insert into t_hash select 1,'2010-04-01';
select table_name,partition_name,table_rows from information_schema. PARTITIONS where table_schema=database() and table_name='t_hash';
可以看到p2分区有1条记录。当然这个例子中并不能把数据均匀地分布到各个分区中，因为分区是按照YEAR函数，因此这个值本身可以视为是离散的。如果对于连续的值进行HASH分区，如自增长的主键，则可以很好地将数据进行平均分布。
MySQL数据库还支持一种称为LINEAR HASH的分区，它使用一个更加复杂的算法来确定新行插入已经分区的表中的位置。它的语法和HASH分区的语法相似，只是将关键字HASH改为LINEAR HASH。下面创建一个LINEAR HASH的分区表t_linear_hash，它和之前的表t_hash相似，只是分区类型不同：
create table t_linear_hash(
a int,
b datetime
)engine=innodb
partition by linear hash(year(b))
partition by 4;
同样插入‘2010-04-01’的记录，这次MySQL数据库根据以下的方法来进行分区的判断：
（1）取大于分区数量4的下一个2的幂值V，V=POWER(2,CEILING(LOG(2,num)))=4;
（2）所在分区N=YEAR('2010-04-01')&(V-1)=2。
虽然还是在分区2，但是计算的方法和之前的HASH分区完全不同。接着进行插入实际数据的验证：
insert into t_linear_hash select 1,'2010-04-01';
select table_name,partition_name,table_rows from information_schema.PARTITIONS where table_schema=database() and table_name='t_linear_hash';
LINEAR HASH分区的优点在于，增加、删除、合并和拆分分区将变得更加快捷，这有利于处理含有大量数据的表；它的缺点在于，与使用HASH分区得到的数据分布相比，各个分区间数据的分布可能不大均衡。
KEY分区
KEY分区和HASH分区相似；不同在于，HASH分区使用用户定义的函数进行分区，KEY分区使用MySQL数据库提供的函数进行分区。NDB Cluster引擎使用MD5函数来分区，对于其他存储引擎，MySQL数据库使用其内部的哈希函数，这些函数是基于与PASSWORD()一样的运算法则。如：
create table t_key(
a int,
b datetime
)engine=innodb
partition by key(b)
partitions 4;
在KEY分区中使用关键字LINEAR，和在HASH分区中具有同样的作用，分区的编号是通过2的幂（powers-of-two）算法得到的，而不是通过模数算法。
COLUMNS分区
RANGE、LIST、HASH和KEY这四种分区中，分区的条件必须是整型（interger），如果不是整型，那应该需要通过函数将其转化为整型，如YEAR()、TO_DAYS()、MONTH()等函数。MySQL数据库5.5版本开始支持COLUMNS分区，可视为RANGE分区和LIST分区的一种进化。COLUMNS分区可以直接使用非整型的数据进行分区，分区根据类型直接比较而得，不需要转化为整型。其次，RANGE COLUMNS分区可以对多个列的值进行分区。
COLUMNS分区支持以下的数据类型：
- 所有的整型类型，如INT、SMALLINT、TINYINT、BIGINT。FLOAT和DECIMAL则不予支持。
- 日期类型，如DATE和DATETIME。其余的日期类型不予支持。
- 字符串类型，如CHAR、VARCHAR、BINARY和VARBINARY。BLOB和TEXT类型不予支持。
对于日期类型的分区，我们不再需要YEAR()和TO_DAYS()函数了，而直接可以使用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')
);
同样，可以直接使用字符串的分区：
CREATE TABLE customers_1(
first_name VARCHAR(25),
last_name VARCHAR(25),
street_1 VARCHAR(30),
street_2 VARCHAR(30),
city VARCHAR(15),
renewal DATE
)
PARTITION BY LIST COLUMNS(city)(
PARTITION pRegion_1 VALUES IN ('Oskarshamn','Högsby','Mönster˚as'),
PARTITION pRegion_2 VALUES IN ('Vimmerby','Hultsfred','Västervik'),
PARTITION pRegion_3 VALUES IN ('Nössjö','Eksjö','Vetlanda'),
PARTITION pRegion_4 VALUES IN ('Uppvidinge','Alvesta','Växjo')
);
对于RANGE COLUMNS分区，可以使用多个列进行分区，如：
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)
);
MySQL数据库版本5.5.0开始支持COLUMNS分区，对于之前的RANGE和LIST分区，我们应该可以用RANGE COLUMNS和LIST COLUMNS分区进行很好的代替。
子分区
子分区（subpartitioning）是在分区的基础上再进行分区，有时也称这种分区为复合分区(composite partitioning)。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
);
system ls -lh /usr/local/mysql/data/test2/ts*
-rw-rw----1 mysql mysql 8.4K Aug 1 15：50/usr/local/mysql/data/test2/ts.frm
-rw-rw----1 mysql mysql 96 Aug 1 15：50/usr/local/mysql/data/test2/ts.par
-rw-rw----1 mysql mysql 96K Aug 1 15：50/usr/local/mysql/data/test2/ts#P#p0#SP#p0sp0.ibd
-rw-rw----1 mysql mysql 96K Aug 1 15：50/usr/local/mysql/data/test2/ts#P#p0#SP#p0sp1.ibd
-rw-rw----1 mysql mysql 96K Aug 1 15：50/usr/local/mysql/data/test2/ts#P#p1#SP#p1sp0.ibd
-rw-rw----1 mysql mysql 96K Aug 1 15：50/usr/local/mysql/data/test2/ts#P#p1#SP#p1sp1.ibd
-rw-rw----1 mysql mysql 96K Aug 1 15：50/usr/local/mysql/data/test2/ts#P#p2#SP#p2sp0.ibd
-rw-rw----1 mysql mysql 96K Aug 1 15：50/usr/local/mysql/data/test2/ts#P#p2#SP#p2sp1.ibd
表ts先根据b列进行了RANGE分区，然后又再进行了一次HASH分区，所以分区的数量应该为（3×2=）6个，这通过查看物理磁盘上的文件也可以得到证实。
我们也可以通过使用SUBPARTITION语法来显式指出各个子分区的名称，同样对上述的ts表：
CREATE TABLE ts(a INT,b DATE)
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
)
);
子分区的建立需要注意以下几个问题：
- 每个子分区的数量必须相同。
- 如果在一个分区表上的任何分区上使用SUBPARTITION来明确定义任何子分区，那么就必须定义所有的子分区。
因此下面的创建语句是错误的。
CREATE TABLE ts(a INT,b DATE)
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),
PARTITION p2 VALUES LESS THAN MAXVALUE(
SUBPARTITION s2,
SUBPARTITION s3
)
);
ERROR 1064（42000）：Wrong number of subpartitions defined，mismatch with previous setting near'
PARTITION p2 VALUES LESS THAN MAXVALUE(
SUBPARTITION s2,
SUBPARTITION s3
)
3.每个SUBPARTITION子句必须包括子分区的一个名称。
4.在每个分区内，子分区的名称必须是唯一的。
因此下面的创建语句是错误的。
CREATE TABLE ts(a INT,b DATE)
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 s0,
SUBPARTITION s1
),
PARTITION p2 VALUES LESS THAN MAXVALUE(
SUBPARTITION s0,
SUBPARTITION s1
)
);
ERROR 1517（HY000）：Duplicate partition name s0
子分区可以用于特别大的表，在多个磁盘间分别分配数据和索引。假设有6个磁盘，分别为/disk0、/disk1、/disk2等。现在考虑下面的例子：
CREATE TABLE ts(a INT,b DATE) ENGINE=MYISAM
PARTITION BY RANGE(YEAR(b))
SUBPARTITION BY HASH(TO_DAYS(b))(
PARTITION p0 VALUES LESS THAN(2000)(
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(2010)(
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'
)
);
但是InnoDB存储引擎会忽略DATA DIRECTORY和INDEX DIRECTORY语法，因此上述分区表的数据和索引文件分开放置对其是无效的：
分区中的NULL值
MySQL数据库允许对NULL值做分区，但是处理的方法和Oracle数据库完全不同。MYSQL数据库的分区总是把NULL值视为小于任何一个非NULL值，这和MySQL数据库中对于NULL的ORDER BY的排序是一样的。因此对于不同的分区类型，MySQL数据库对于NULL值的处理是不一样的。
对于RANGE分区，如果对于分区列插入了NULL值，则MySQL数据库会将该值放入最左边的分区（这和Oracle数据库完全不同，Oracle数据库会将NULL值放入MAXVALUE分区中）。例如：
create table t_range(
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
);
接着往表中插入（1，1）和（1，NULL）两条数据，并观察每个分区中记录的数量：
insert into t_range select 1,1;
insert into t_range select 1,NULL;
select * from t_rangeG;
select table_name,partition_name,table_rows from information_schema.PARTITIONS where table_schema=database() and table_name='t_range'G;
可以看到两条数据都放入了p0分区，也就是说明了RANGE分区下，NULL值会放入最左边的分区中。另外需要注意的是，如果删除p0这个分区，你删除的是小于10的记录，并且还有NULL值的记录，这点非常重要。
LIST分区下要使用NULL值，则必须显式地指出哪个分区中放入NULL值，否则会报错，如：
create table t_list(
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)
);
insert into t_list select 1,NULL;
ERROR 1526（HY000）：Table has no partition for value NULL
若p0分区允许NULL值，则插入不会报错：
create table t_list(
a int,
b int)engine=innodb
partition by list(b)(
partition p0 values in (1,3,5,7,9,NULL),
partition p1 values in (0,2,4,6,8)
);
insert into t_list select 1,NULL;
select table_name,partition_name,table_rows from information_schema.PARTITIONS where table_schema=database() and table_name='t_list';
HASH和KEY分区对于NULL的处理方式，和RANGE分区、LIST分区不一样。任何分区函数都会将含有NULL值的记录返回为0。如：
create table t_hash(
a int,
b int)engine=innodb
partition by hash(b)
partitions 4;
insert into t_hash select 1,0;
insert into t_hash select 1,NULL;
select table_name,partition_name,table_rows from information_schema.PARTITIONS where table_schema=database() and table_name='t_hash';
***************************1.row***************************
table_name：t_hash
partition_name：p0
table_rows：2
分区和性能
常听到开发人员说“对表做个分区，然后数据库的查询就会快了”。但是这是真的吗？实际中可能你根本感觉不到查询速度的提升，甚至是查询速度急剧的下降。因此，在合理使用分区之前，必须了解分区的使用环境。
数据库的应用分为两类：
- 一类是OLTP（在线事务处理），如博客、电子商务、网络游戏等；
- 一类是OLAP（在线分析处理），如数据仓库、数据集市。
在一个实际的应用环境中，可能既有OLTP的应用，也有OLAP的应用。如网络游戏中，玩家操作的游戏数据库应用就是OLTP的，但是游戏厂商可能需要对游戏产生的日志进行分析，通过分析得到的结果来更好地服务于游戏、预测玩家的行为等，而这却是OLAP的应用。
对于OLAP的应用，分区的确可以很好地提高查询的性能，因为OLAP应用的大多数查询需要频繁地扫描一张很大的表。假设有一张1亿行的表，其中有一个时间戳属性列。你的查询需要从这张表中获取一年的数据。如果按时间戳进行分区，则只需要扫描相应的分区即可。
对于OLTP的应用，分区应该非常小心。在这种应用下，不可能会获取一张大表中10%的数据，大部分都是通过索引返回几条记录即可。而根据B+树索引的原理可知，对于一张大表，一般的B+树需要2～3次的磁盘IO（到现在我都没看到过4层的B+树索引）。因此B+树可以很好地完成操作，不需要分区的帮助，并且设计不好的分区会带来严重的性能问题。
很多开发团队会认为含有1000万行的表是一张非常巨大的表，所以他们往往会选择采用分区，如对主键做10个HASH的分区，这样每个分区就只有100万行的数据了，因此查询应该变得更快了，如SELECT * FROM TABLE WHERE PK=@pk。但是有没有考虑过这样一个问题：100万行和1000万行的数据本身构成的B+树的层次都是一样的，可能都是2层？那么上述走主键分区的索引并不会带来性能的提高。是的，即使1000万行的B+树的高度是3，100万行的B+树的高度是2，那么上述走主键分区的索引可以避免1次IO，从而提高查询的效率。嗯，这没问题，但是这张表只有主键索引，而没有任何其他的列需要查询？如果还有类似如下的语句SQL：SELECT * FROM TABLE WHERE KEY=@key，这时对于KEY的查询需要扫描所有的10个分区，即使每个分区的查询开销为2次IO，则一共需要20次IO。而对于原来单表的设计，对于KEY的查询还是2～3次IO。
如下表Profile，根据主键ID进行了HASH分区，HASH分区的数量为10，表Profile有接近1000万行的数据：
CREATE TABLE 'Profile'(
'id' int(11) NOT NULL AUTO_INCREMENT,
'nickname' varchar(20) NOT NULL DEFAULT'',
'password' varchar(32) NOT NULL DEFAULT'',
'sex' char(1)NOT NULL DEFAULT'',
'rdate' date NOT NULL DEFAULT '0000-00-00',
PRIMARY KEY('id'),
KEY 'nickname' ('nickname')
）ENGINE=InnoDB
partition by hash(id)
partitions 10；
select count（nickname）from Profile；
count（1）：9999248
因为是根据HASH分区的，因此每个区分的记录数大致是相同的，即数据分布比较均匀：
select table_name，partition_name，table_rows from information_schema.PARTITIONS where table_schema=database() and table_name='Profile';
注意：即使是根据自增长主键进行的HASH分区，也不能保证分区数据的均匀。因为插入的自增长ID并非总是连续的，如果该主键值因为某种原因被回滚了，则该值将不会再次被自动使用。
如果进行主键的查询，可以发现分区的确是有意义的：
explain partitions select * from Profile where id=1G;
可以发现只寻找了p1分区。
但是对于表Profile中nickname列索引的查询，EXPLAIN PARTITIONS则会得到如下的结果：
explain partitions select * from Profile where nickname='david'G;
可以看到，MySQL数据库会搜索所有分区，因此查询速度会慢很多，比较上述的语句：
select * from Profile where nickname='david'G;
上述简单的索引查找语句竟然需要1.05秒，这显然是因为搜索所有分区的关系，实际的IO执行了20～30次，在未分区的同样结构和大小的表上执行上述SQL语句，只需要0.26秒。
因此对于使用InnoDB存储引擎作为OLTP应用的表，在使用分区时应该十分小心，设计时要确认数据的访问模式，否则在OLTP应用下分区可能不仅不会带来查询速度的提高，反而可能会使你的应用执行得更慢。