一、查看MySQL是否支持分区
1、MySQL5.6以及之前版本
show variables like '%partition%';
2、MySQL5.7
show plugins;
二、分区表的分类与限制
1、分区表分类
RANGE分区:基于属于一个给定连续区间的列值,把多行分配给分区。
LIST分区:类似于按RANGE分区,区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。
HASH分区:基于用户定义的表达式的返回值来进行选择的分区,该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL 中有效的、产生非负整数值的任何表达式。
KEY分区:类似于按HASH分区,区别在于KEY分区只支持计算一列或多列,且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含整数值。
复合分区:在MySQL 5.6版本中,只支持RANGE和LIST的子分区,且子分区的类型只能为HASH和KEY。
2、分区表限制
1)分区键必须包含在表的所有主键、唯一键中。
2)MYSQL只能在使用分区函数的列本身进行比较时才能过滤分区,而不能根据表达式的值去过滤分区,即使这个表达式就是分区函数也不行。
3)最大分区数: 不使用NDB存储引擎的给定表的最大可能分区数为8192(包括子分区)。如果当分区数很大,但是未达到8192时提示 Got error … from storage engine: Out of resources when opening file,可以通过增加open_files_limit系统变量的值来解决问题,当然同时打开文件的数量也可能由操作系统限制。
4)不支持查询缓存: 分区表不支持查询缓存,对于涉及分区表的查询,它自动禁用。 查询缓存无法启用此类查询。
5)分区的innodb表不支持外键。
6)服务器SQL_mode影响分区表的同步复制。 主机和从机上的不同SQL_mode可能会导致sql语句; 这可能导致分区之间的数据分配给定主从位置不同,甚至可能导致插入主机上成功的分区表在从库上失败。 为了获得最佳效果,您应该始终在主机和从机上使用相同的服务器SQL模式。
7)ALTER TABLE … ORDER BY: 对分区表运行的ALTER TABLE … ORDER BY列语句只会导致每个分区中的行排序。
8)全文索引。 分区表不支持全文索引,即使是使用InnoDB或MyISAM存储引擎的分区表。
9)分区表无法使用外键约束。
10)Spatial columns: 具有空间数据类型(如POINT或GEOMETRY)的列不能在分区表中使用。
11)临时表: 临时表不能分区。
12)subpartition问题: subpartition必须使用HASH或KEY分区。 只有RANGE和LIST分区可能被分区; HASH和KEY分区不能被子分区。
13)分区表不支持mysqlcheck,myisamchk和myisampack。
三、创建分区表
1、range分区
行数据基于一个给定的连续区间的列值放入分区。
CREATE TABLE `test_11` (
`id` int(11) NOT NULL,
`t` date NOT NULL,
PRIMARY KEY (`id`,`t`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE (to_days(t))
(PARTITION p20170801 VALUES LESS THAN (736907) ENGINE = InnoDB,
PARTITION p20170901 VALUES LESS THAN (736938) ENGINE = InnoDB,
PARTITION pmax VALUES LESS THAN maxvalue ENGINE = InnoDB);123456789
然后插入4条数据:
insert into test_11 values (1,"20170722"),(2,"20170822"),(3,"20170823"),(4,"20170824");1
然后查看information下partitions对分区别信息的统计:
select PARTITION_NAME as "分区",TABLE_ROWS as "行数" from information_schema.partitions where table_schema="mysql_test" and table_name="test_11";
+-----------+--------+
| 分区 | 行数 |
+-----------+--------+
| p20170801 | 1 |
| p20170901 | 3 |
+-----------+--------+
2 rows in set (0.00 sec)12345678
可以看出分区p20170801插入1行数据,p20170901插入的3行数据。
可以是用year、to_days、unix_timestamp等函数对相应的时间字段进行转换,然后分区。
2、list分区
和range分区一样,只是list分区面向的是离散的值
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)123456789
与RANGE分区的情况不同