mysql技术分享--表分区实现

分区表

分区概念

• 分区功能并不是在存储引擎层完成的，因此不止有InnoDB存储引擎支持分区，常见的存储引擎MyISAM，NDB等都支持。但是也并不是所有存储引擎都支持，比如CSV，FEDERATED，MERGE等就不支持，在使用分区功能之前应该先了解选择的存储引擎是否支持分区操作。
• MySQL是在5.1版本之后添加了对于分区的支持。功能是将表或者索引分解成更小的多个，更可管理的部分。就访问数据库的应用而言，逻辑上还是一个表或者一个索引，但是物理上这个表或者索引可能由数十个物理分区组成，每个分区都是独立的对象，可以独立出来，也可以作为一个更大的对象的一部分进行处理。
• MySQL数据库支持的分区类型是水平分区（指同一个表中不同行的记录分配到不同的物理文件），不支持垂直分区（指将同一个表中不同的列分配到不同的物理文件）
• MySQL数据库的分区是局部分区索引，一个分区中即存放了数据又存放了索引。
• 如下命令查看是否开启分区功能：

show variables like '%partition%'

show plugins;

分区功能

• 我们分区的作用一般是为了让数据库变得更快，但是启用分区后，并不一定会变快。通过经验来看分区对某些SQL语句的性能会带来提高，但是分区主要用于高可用，更利于数据库的管理。
• 在OLTP应用中，对于分区的使用应该更小心。因为OLTP类型的数据库对性能要求特别高，也行，分表分区能够对某一批查询效率提升，但是可能对其他某些查询是致命的，导致他需要遍历N个库的全量数据，大大增加了IO次数。
• 先了解当前MySQL数据库支持的以下几种类型的分区：
• RANGE分区：行数据基于属于一个给定连续区间的值放入分区。MySQL数据库5.5开始支持RANGE COLUMNS的分区
• LIST分区：和RANGE分区类似，只是LIST分区面向的是离散的值。同样5.5开始支持LIST COLUMNS的分区
• HASH分区：根据用户自定义的表达式的返回值来进行分区，返回值不能为负数。
• KEY分区：更具MySQL数据库提供的哈希函数进行分区

错误案例

• 注意，不论任何类型分区，如果表中存在主键或者唯一索引时候，分区必须是唯一索引的一个组成部分，因此如下案例会报错：

create table t1(
coll1 int not null,
coll2 date not null,
coll3 int not null,
coll4 int not null,
UNIQUE key(coll1, coll2)
)
PARTITION by Hash(coll3)
PARTITIONS 4;

[Err] 1503 - A PRIMARY KEY must include all columns in the table's partitioning function

• 将PARTITION by Hash(coll3) 修改成PARTITION by Hash(coll1)或者coll2 就能正确创建

唯一索引情况

• 唯一索引可以允许是NULL值的，并且分区列只要是唯一索引的一个组成部分，不需要整个唯一索引列都是分区列

create table t1(
coll1 int null,
coll2 date null,
coll3 int null,
coll4 int null,
UNIQUE key(coll1, coll2,coll3, coll4)
)
PARTITION by Hash(coll3)
PARTITIONS 4;

没有主键或唯一索引情况

• 当建表时没有指定主键，唯一索引，可以指定任何一个列为分区列，如下两种情况都可以正确创建案例：

create table t1(
coll1 int not null,
coll2 date not null,
coll3 int not null,
coll4 int not null
)
PARTITION by Hash(coll3)
PARTITIONS 4;

create table t1(
coll1 int not null,
coll2 date not null,
coll3 int not null,
coll4 int not null,
key (coll4)
)
PARTITION by Hash(coll3)
PARTITIONS 4;

RANGE分区

• RANGE分区是常用的一种分区类型。如下案例，我们create table语句创建了一个id列的区间分区表。当id小于10 时候，数据插入P0分区。当大于等于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)
);
insert into t values(9),(10),(15)

• 插入数据后，因为t根据id进行分区，因此数据是根据id列的值的范围存放在不同的物理文件中，可以通过查information_schema 架构在的 PARTITIONS 表来查看每个分区的具体信息，在数据库中有一个 information_schema数据库
  

select * from information_schema.PARTITIONS where TABLE_SCHEMA=DATABASE() and TABLE_NAME = 't';

• TABLE_ROWS列说明了每个分区记录中记录的数量，由于之前项表中插入了9,10,15三条记录，因此可以看到，当前分区P0有一条记录，分区p1 有2条记录，PARTITION_METHOD 标识分区的类型，这里显示的是RANGE
• 对于表t，因为我们定义了分区，因此对于插入的值都严格遵循分区的定义，当插入不属于分区的数据时候，会抛出异常：

[SQL]
insert INTO t select 30;
[Err] 1526 - Table has no partition for value 30

• 应该添加新的范围，使得数据库分区能够兼容所有的数据清空，如下：

alter table t add PARTITION(PARTITION p2 values less than MAXVALUE);
insert INTO t select 30;

RANGE日期分区

• RANGE分区主要用于日期列的分区，如对于销售类的表，可以根据年份来分区存储销售数据，如下案例：

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 values(100, '2008-01-01'),
(100, '2008-02-01'),
(200, '2008-01-02'),
(100, '2009-03-01'),
(200, '2010-03-01')

select * from information_schema.PARTITIONS where TABLE_SCHEMA=DATABASE() and TABLE_NAME = 'sales';

• 如上创建好，可以对sales这张表按时间处理账本，如果我们需要删除18年的数据，就不需要去delete from sales where date >=‘2008-01-01’ and date < ‘2009-01-01’ ,我们可以直接删掉p2008分区即可，如下操作

alter table sales drop PARTITION p2008;

• 分区操作还有另外一个好处，可以加快某些查询的操作，如果我们只需要查询19年的销售额，如下：

EXPLAIN partitions select *  from sales where date >='2009-01-01' and date < '2009-12-31' 

’

• 如上通过explain partitions 命令，上述语句sql优化器只需要搜索p2009分区，不用去全表扫描数据，大大提高执行速度，但是如上语句则不同：

EXPLAIN partitions select *  from sales where date >='2009-01-01' and date < '2020-01-01' 

• 条件修改成了< 2020-01-01，这个时候优化器选择p2009,p2010两个分区，这是不希望看到的。因此对于启用分区应该更具分区的特点编写sql语句

• 如果我们需要更细分，按月来分区，如下分区方式第查询来说是无效的，如下：

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)
);
insert into sales values(100, '2008-01-01'),
(100, '2008-02-01'),
(200, '2008-01-02'),
(100, '2009-03-01'),
(200, '2010-03-01')

EXPLAIN partitions select *  from sales where date >='2010-01-01' and date < '2010-01-31' 

• 如上查询语句扫描的全分区，但是之前的逻辑应该只扫p2020001分区，这个问题原因在于RANGE分区的查询，优化器只能对YEAR(),TO_DAYS(),TO_SECONDS(),UNIX_TIMESTAMP()这类函数进行优化选择，因此对于上述要求，需要将分区函数改为TO_DAY，如下:

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' 

• 此时查询只对p201001 分区进行扫描

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

insert into t values(1,1),
(1,2),
(1,3),
(1,4)

select * from information_schema.PARTITIONS where TABLE_SCHEMA=DATABASE() and TABLE_NAME = 't';

• 如上，按奇数偶数分区，如果插入的值不值分区的定义，MySQL会抛出异常：

[SQL]insert into t values(1,100)

[Err] 1526 - Table has no partition for value 100

• 而且List有一个特殊地方，在insert插入多行时候，遇到未分区定义的值时候，MyISAM和InnoDB存储引擎的处理完全不同，MyISAM会将之前的行都insert，之后的不成功，InnoDB存储引擎会将他看成是一个事务，因此都会失败，

[SQL]insert into t values(1,1),
(1,2),
(1,3),
(1,4),
(1,100)

[Err] 1526 - Table has no partition for value 100

• 如上是InnoDB存储引擎，返回的是没有对应的分区值，并且数据全失败，

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,1),
(1,2),
(1,3),
(1,4),
(1,100)
select * from information_schema.PARTITIONS where TABLE_SCHEMA=DATABASE() and TABLE_NAME = 't';

• 可以看到，插入的1,10，记录是没有成功的，之前的2，3,4都是已经成功insert。

HASH分区

• HASH分区目的是将数据均由的分布的预定的各个分区中，保证个分区的数据大致一样。在RANGE和LIST分区中，必须明确知道一个列值或者列值的集合。而在HASH分区中MySQL自动完成了这些工作，你需要做的是基于要被hash的列值指定一个列值或者表达式，以及指定备份区的表要被分割的分区数量，如下案例

create table t_hash(
a int,
b datetime
)ENGINE=INNODB
PARTITION by hash(YEAR(b))
PARTITIONS 4;

• 如上hash规则，如果b列的值2010-04-01，那么这个记录在t_hash中，保存的确认值如下

MOD(YEAR('2010-04-01'), 4)= MOD(2010,4) = 2

• 如上计算，应该放到2分区中，

insert into t_hash values (1, '2010-04-01')

select * from information_schema.PARTITIONS where TABLE_SCHEMA=DATABASE() and TABLE_NAME = 't_hash';

• 如上数据落在了P2分区
• MySQL数据库还支持一种LINEAR HASH的分区，更负责的分区算法，

create table t_hash(
a int,
b datetime
)ENGINE=INNODB
PARTITION by LINEAR HASH (YEAR(b))
PARTITIONS 4;

• MySQL数据库用一下方式判断分区
  • 取大于分区数量4 的下一个2 的幂值V, V = POWRE(2, CEILING(LOG(2,num))) = 4
  • 所在分区N= YEAR(‘2010-04-01’) & (V-1) = 2
• 得到的分区还是2，但是HASH算法与之前的完全不同，
• LINEAR HASH分区的有点在于，增加，删除，合并和拆分分区变得更加快速，这有利于处理含有大量数据的表，缺点在于与使用HASH分区得到的数据分布相比，各个分区数据的分布不均匀。

KEY分区

上一篇：mysql技术分享-- 视图是什么