Mysql - 分区表使用

一、查看版本是否支持分区？

v5.7: show plugins;
小于v5.6: show variables like ‘%partition%’;

二、分区表的分类与限制

2.1 分区表分类
RANGE分区基于属于一个给定联系区间的列值，把多行分配给分区；
LIST分区类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择；
HASH分区基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL中有效的、产生非负整数值的任何表达式；
KEY分区类似于HASH分区，区别在于key分区只支极端一列或多列，且Mysql服务器提供其自身的HASH函数，必须有一列或多列包含整数值；
复合分区在Mysql 5.6版本中，只支持RANGE和LIST子分区。且子分区的类型只能为HASH和KEY
2.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。

三、分区表

3.1 创建分区表

1. range分区：针对范围进行分区，入0~10、11~20 ... 等
2. list分区，离散的值，如（1，3，5）、（2，4，6）... 等；
3. hash分区：根据用户自定义的返回值进行分区，返回值不能为负数；
4. key分区：根据mysql数据库提供的离散函数进行分区，通过主键进行分区后，不能删除主键；
5. 复合分区：5.5以后引入的分区功能，不支持表达式作为分区键，支持的类型（整数、日期类型、字符类型）；

3.2 修改表，不同表转换为分区表

    命令：alter table table_name PARTITION BY key(col_name) partitions 8;

3.3 分区表操作

3.3.1 创建分区表

命令：
CREATE TABLE t1 (
    id INT,
    year_col INT
)
PARTITION BY RANGE (year_col) (
    PARTITION p0 VALUES LESS THAN (1991),
    PARTITION p1 VALUES LESS THAN (1995),
    PARTITION p2 VALUES LESS THAN (1999)
);

3.3.2 新增分区

命令：alter table table_name ADD PARTITION (PARTITION P3 VALUES LESS THAN(2002));

3.3.3 删除分区

命令： alter table table_name DROP PARTITION P0;

3.3.4 截取分区

命令：
ALTER TABLE t1 TRUNCATE PARTITION p0;
ALTER TABLE t1 TRUNCATE PARTITION p1, p3;

3.3.4 合并分区

命令：
CREATE TABLE t2 (
    name VARCHAR (30),
    started DATE
)
PARTITION BY HASH( YEAR(started) )
PARTITIONS 6;

ALTER TABLE t2 COALESCE PARTITION 2;

3.3.5 获取分区的相关信息

命令：
show create table e/G
show table status/G
查看 INFORMATION_SCHEMA.PARTITIONS表
通过 EXPLAIN PARTITIONS SELECT 语句查看对于具体的SELECT语句,会访问哪个分区。

3.3.6 分区表数据删除

MySQL- hash分区表数据回滚：
命令： alter table table_name truncate partition p${id}
注意： id为分区序号；

四、MySQL5.7 改进

1. 性能忧患：每个分区使用130KB缓冲区来实现这一点；

五、参考文献

mysql分区操作：https://www.cnblogs.com/xibuhaohao/p/10154281.html#_label0_0