mysql分区表 缓存_Mysql 分区表-分区操作

一、查看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分区的情况不同