Mysql数据库表分区深入详解

0、mysql数据库分区的由来？

1）传统不分区数据库痛点

mysql数据库中的数据是以文件的形势存在磁盘上的，默认放在/mysql/data下面（可以通过my.cnf中的datadir来查看），
一张表主要对应着三个文件，一个是frm存放表结构的，一个是myd存放表数据的，一个是myi存表索引的。

[root@laoyang test]# ls -al总用量 1811444drwx------ 2 mysql mysql 4096 10月 17 15:12 .drwxr-xr-x 4 mysql mysql 4096 10月 17 14:37 ..-rw-rw---- 1 mysql mysql 8962 10月 10 17:45 bz_info.frm-rw-rw---- 1 mysql mysql 347727032 10月 17 15:16 bz_info.MYD-rw-rw---- 1 mysql mysql 56341504 10月 17 15:16 bz_info.MYI-rw-rw---- 1 mysql mysql 8962 10月 10 17:44 dz_info.frm-rw-rw---- 1 mysql mysql 418645764 10月 17 15:15 dz_info.MYD-rw-rw---- 1 mysql mysql 81381376 10月 17 15:15 dz_info.MYI

2）数据库分区处理

如果一张表的数据量太大的话，那么myd,myi就会变的很大，查找数据就会变的很慢，这个时候我们可以利用mysql的分区功能，在物理上将这一张表对应的三个文件，分割成许多个小块，这样呢，我们查找一条数据时，就不用全部查找了，只要知道这条数据在哪一块，然后在那一块找就行了。如果表的数据太大，可能一个磁盘放不下，这个时候，我们可以把数据分配到不同的磁盘里面去。

表分区是Mysql被Oracle收购后推出的一个新特性。

一、表分区通俗解释

通俗地讲表分区是将一大表，根据条件分割成若干个小表。mysql5.1开始支持数据表分区了。
如：某用户表的记录超过了600万条，那么就可以根据入库日期将表分区，也可以根据所在地将表分区。当然也可根据其他的条件分区。

二、为什么要对表进行分区？

为了改善大型表以及具有各种访问模式的表的可伸缩性，可管理性和提高数据库效率。

2.1 表分区要解决的问题：

当表非常大，或者表中有大量的历史记录，而“热数据”却位于表的末尾。如日志系统、新闻。。此时就可以考虑分区表。【注：此处也可以使用分表，但是会增加业务的复杂性。】

2.2 表分区有如下优点：

1）与单个磁盘或文件系统分区相比，可以存储更多的数据。
2）对于那些已经失去保存意义的数据，通常可以通过删除与那些数据有关的分区，很容易地删除那些数据。
相反地，在某些情况下，添加新数据的过程又可以通过为那些新数据专门增加一个新的分区，来很方便地实现。
同样的，你可以很快的通过删除分区来移除旧数据。你还可以优化、检查、修复个别分区。
3）一些查询可以得到极大的优化。 可以把一些归类的数据放在一个分区中，可以减少服务器检查数据的数量加快查询。
这主要是借助于满足一个给定WHERE语句的数据可以只保存在一个或多个分区内，这样在查找时就不用查找其他剩余的分区。
PS：因为分区可以在创建了分区表后进行修改，所以在第一次配置分区方案时还不曾这么做时，可以重新组织数据，来提高那些常用查询的效率。
4）涉及到例如SUM()和COUNT()这样聚合函数的查询，可以很容易地进行并行处理。
这种查询的一个简单例子如
“SELECT salesperson_id, COUNT (orders) as order_total FROM sales GROUP BY salesperson_id；”。
通过“并行”，这意味着该查询可以在每个分区上同时进行，最终结果只需通过总计所有分区得到的结果。
5）通过跨多个磁盘来分散数据查询，来获得更大的查询吞吐量。

三、mysql分区类型

根据所使用的不同分区规则可以分成几大分区类型。

3.1 RANGE 分区：

基于属于一个给定连续区间的列值，把多行分配给分区。
举例：

create table foo_range (id int not null auto_increment,created DATETIME,primary key (id, created)) engine = innodb partition by range (TO_DAYS(created))(PARTITION foo_1 VALUES LESS THAN (TO_DAYS('2016-10-18')),PARTITION foo_2 VALUES LESS THAN (TO_DAYS('2017-01-01')));//新增一个分区ALTER TABLE foo_range ADD PARTITION(PARTITION foo_3 VALUES LESS THAN (TO_DAYS('2017-10-18')));//插入数据insert into `foo_range` (`id`, `created`) values (1, '2016-10-17'),(2, '2016-10-20'),(3, '2016-1-25');//查询explain partitions select * from foo_range where created = '2016-10-20';//查询结果：mysql> explain partitions select * from foo_range where created = '2016-10-20';+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+--------------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+--------------------------+| 1 | SIMPLE | foo_range | foo_2 | index | NULL | PRIMARY | 12 | NULL | 2 | Using where; Using index |+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+--------------------------+

3.2 LIST 分区：

类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。

create table foo_list(empno varchar(20) not null ,empname varchar(20),deptno int,birthdate date not null,salary int)partition by list(deptno)(partition p1 values in (10),partition p2 values in (20),partition p3 values in (30));

以上显示，以部门号为分区依据，每个部门一个分区。

3.3 HASH分区：

基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL中有效的、产生非负整数值的任何表达式。
HASH分区主要用来确保数据在预先确定数目的分区中平均分布。在RANGE和LIST分区中，必须明确指定一个给定的列值或列值集合应该保存在哪个分区中。
在HASH分区中，MySQL 自动完成这些工作，你所要做的只是基于将要被哈希的列值指定一个列值或表达式，以及指定被分区的表将要被分割成的分区数量。

create table foo_hash(empno varchar(20) not null ,empname varchar(20),deptno int,birthdate date not null,salary int)partition by hash(year(birthdate))partitions 4;

以上创建了4个分区。

3.4 KEY分区：

类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含整数值。

create table foo_key(empno varchar(20) not null ,empname varchar(20),deptno int,birthdate date not null,salary int)partition by key(birthdate)partitions 4;

3.5 复合分区：

基于RANGE/LIST 类型的分区表中每个分区的再次分割。子分区可以是 HASH/KEY 等类型。

四、常见分区操作

修改已有表举例：

ALTER TABLE bj_infoPARTITION BY RANGE(id) PARTITIONS 14(PARTITION part_00yntai VALUES LESS THAN (610001),PARTITION part_01shxia VALUES LESS THAN (1220001),PARTITION part_02zhfu VALUES LESS THAN (1830001),PARTITION part_03fuhan VALUES LESS THAN (2440001),PARTITION part_04mping VALUES LESS THAN (3660001),PARTITION part_06chngdao VALUES LESS THAN (4270001),PARTITION part_07lonkou VALUES LESS THAN (4880001),PARTITION part_08layang VALUES LESS THAN (5490001),PARTITION part_09laihou VALUES LESS THAN (6100001),PARTITION part_10peglai VALUES LESS THAN (6710001),PARTITION part_11zhoyuan VALUES LESS THAN (7320001),PARTITION part_12qixa VALUES LESS THAN (7930001),PARTITION part_13haiyng VALUES LESS THAN (8540000),PARTITION part_05laisan VALUES LESS THAN MAXVALUE);

五、获取分区表信息的方法

5.1 show create table 表名

可以查看创建分区表的create语句
举例：

mysql> show create table foo_list;+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table |+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+| foo_list | CREATE TABLE `foo_list` (  `empno` varchar(20) NOT NULL,  `empname` varchar(20) DEFAULT NULL,  `deptno` int(11) DEFAULT NULL,  `birthdate` date NOT NULL,  `salary` int(11) DEFAULT NULL) ENGINE=MyISAM DEFAULT CHARSET=utf8/*!50100 PARTITION BY LIST (deptno)(PARTITION p1 VALUES IN (10) ENGINE = MyISAM, PARTITION p2 VALUES IN (20) ENGINE = MyISAM, PARTITION p3 VALUES IN (30) ENGINE = MyISAM) */ |+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.01 sec)

5. 2 show table status

可以查看表是不是分区表
举例：
SHOW TABLE STATUS LIKE ‘foo_range’;
结果如红色部分所示：

5.3 查看information_schema.partitions表

如下命令可以查看表具有哪几个分区、分区的方法、分区中数据的记录数等信息

mysql> select  -> partition_name part,  -> partition_expression expr,  -> partition_description descr,  -> table_rows  -> from information_schema.partitions where  -> table_schema = schema()  -> and table_name='foo_range';+-------+------------------+--------+------------+| part | expr | descr | table_rows |+-------+------------------+--------+------------+| foo_1 | TO_DAYS(created) | 736620 | 2 || foo_2 | TO_DAYS(created) | 736695 | 1 || foo_3 | TO_DAYS(created) | 736985 | 0 |+-------+------------------+--------+------------+3 rows in set (0.00 sec)

5.4 explain partitions select语句

通过此语句来显示扫描哪些分区，及他们是如何使用的.
举例如下：

mysql> explain partitions select * from foo_range;+----+-------------+-----------+-------------------+-------+---------------+---------+---------+------+------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-----------+-------------------+-------+---------------+---------+---------+------+------+-------------+| 1 | SIMPLE | foo_range | foo_1,foo_2,foo_3 | index | NULL | PRIMARY | 12 | NULL | 4 | Using index |+----+-------------+-----------+-------------------+-------+---------------+---------+---------+------+------+-------------+1 row in set (0.00 sec)

六、性能对比（分区表和非分区表）

步骤一：创建两张表: part_tab(分区表),no_part_tab(普通表)

CREATE TABLE part_tab(c1 int default NULL, c2 varchar(30) default NULL, c3 date not null) PARTITION BY RANGE(year(c3))(PARTITION p0 VALUES LESS THAN (1995),PARTITION p1 VALUES LESS THAN (1996) ,PARTITION p2 VALUES LESS THAN (1997) ,PARTITION p3 VALUES LESS THAN (1998) ,PARTITION p4 VALUES LESS THAN (1999) ,PARTITION p5 VALUES LESS THAN (2000) ,PARTITION p6 VALUES LESS THAN (2001) ,PARTITION p7 VALUES LESS THAN (2002) ,PARTITION p8 VALUES LESS THAN (2003) ,PARTITION p9 VALUES LESS THAN (2004) ,PARTITION p10 VALUES LESS THAN (2010),PARTITION p11 VALUES LESS THAN (MAXVALUE) );CREATE TABLE no_part_tab(c1 int default NULL, c2 varchar(30) default NULL, c3 date not null);

步骤二：创建存储过程。

CREATE PROCEDURE load_part_tab()  begin  declare v int default 0;  while v < 8000000  do  insert into part_tab  values (v,'testingpartitions',adddate('1995-01-01',(rand(v)*36520)mod 3652));  set v = v + 1;  end while;end;//调用存储过程，插入数据call load_part_tab();//从 part_tab 导入数据到 no_part_tabinsert into no_part_tab select * from part_tab;

步骤三：执行查询速度比对

select count(*) from part_tab where c3 > date '1995-01-01' and c3 < date '1995-12-31';

耗时：0.407s

select count(*) from no_part_tab where c3 > date '1995-01-01'and c3 < date '1995-12-31';

耗时：3.716s：3.716/0.407=9.13倍。

扫描次数对比：

mysql> explain select count(*) from part_tab where c3 > date '1995-01-01'and c3 < date '1995-12-31';+----+-------------+----------+------+---------------+------+---------+------+--------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+----------+------+---------------+------+---------+------+--------+-------------+| 1 | SIMPLE | part_tab | ALL | NULL | NULL | NULL | NULL | 798458 | Using where |+----+-------------+----------+------+---------------+------+---------+------+--------+-------------+1 row in set (0.00 sec)mysql> explain select count(*) from no_part_tab where c3 > date '1995-01-01'and c3 < date '1995-12-31';+----+-------------+-------------+------+---------------+------+---------+------+---------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------------+------+---------------+------+---------+------+---------+-------------+| 1 | SIMPLE | no_part_tab | ALL | NULL | NULL | NULL | NULL | 8000000 | Using where |+----+-------------+-------------+------+---------------+------+---------+------+---------+-------------+1 row in set (0.00 sec)

如上：普通表扫描了 8000000次， 分区表扫描了798458次。
分区表扫描比例是普通表的：798458/ 8000000 = 9.98%。

七、分区适用场景

7.1常见使用场景

1）当数据量很大(过T)时，肯定不能把数据再如到内存中，这样查询一个或一定范围的item是很耗时。另外一般这情况下，历史数据或不常访问的数据占很大部分，最新或热点数据占的比例不是很大。这时可以根据有些条件进行表分区。

2）分区表的更易管理，比如删除过去某一时间的历史数据，直接执行truncate，或者狠点drop整个分区，这比detele删除效率更高

3）当数据量很大，或者将来很大的，但单块磁盘的容量不够，或者想提升IO效率的时候，可以把没分区中的子分区挂载到不同的磁盘上。

4）使用分区表可避免某些特殊的瓶颈，例如Innodb的单个索引的互斥访问..

5）单个分区表的备份很恢复会更有效率，在某些场景下

总结：可伸缩性，可管理性，提高数据库查询效率。

7.2 业务场景举例

项目中需要动态新建、删除分区。如新闻表，按照时间维度中的月份对其分区，为了防止新闻表过大，只保留最近6个月的分区，同时预建后面3个月的分区，这个删除、预建分区的过程就是分区表的动态管理。

参考：
http://blog.51yip.com/mysql/1029.html
http://blog.51yip.com/mysql/949.html
http://blog.51yip.com/mysql/1013.html
http://blog.csdn.net/feihong247/article/details/7885199
http://www.wiquan.com/article/669

分区坑：
http://www.simlinux.com/archives/133.html

创建路径：
http://dev.mysql.com/doc/refman/5.7/en/create-table.html

5.6版本才支持：
http://www.linuxidc.com/Linux/2014-01/95725.htm
https://dev.mysql.com/doc/refman/5.6/en/tablespace-placing.html

2016年10月21日 20:52 思于家中床前

作者：铭毅天下
转载请标明出处，原文地址：
http://blog.csdn.net/laoyang360/article/details/52886987
如果感觉本文对您有帮助，请点击‘顶’支持一下，您的支持是我坚持写作最大的动力，谢谢！