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

  • 1
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值