mysql表分区实际开发运用 表分区 mysql

 

一、分区介绍

分区是根据一定的规则，数据库把一个表分解成多个更小的、更容易管理的部分。就访问数据库应用而言，逻辑上就只有一个表或者一个索引，但实际上这个表可能有N个物理分区对象组成，每个分区都是一个独立的对象，可以独立处理，可以作为表的一部分进行处理。分区对应用来说是完全透明的，不影响应用的业务逻辑。分区有利于管理非常大的表，它采用分而治之的逻辑，分区引入了分区键的概念，分区键用于根据某个区间值(或者范围值)、特定值列表或者hash函数值执行数据的聚集，让数据根据规则分布在不同的分区中，让一个大对象分割为一些小对象。

二、分区类型

range 分区：基于一个给定的连续区间范围(区间要求连续并且不能重叠)，把数据分配到不同的分区
list     分区：类似于range分区，区别在于list分区是居于枚举出的值列表分区，range是基于给定的连续区间范围分区
hash  分区：基于给定的分区个数，把数据分配到不同的分区
key    分区：类似于hash分区

注意：无论哪种分区，要么你分区表上没有主键/唯一键，要么分区表的主键/唯一键都必须包含分区键，也就是说不能使用主键/唯一键字段之外的其它字段分区。
如果你想在分区表使用时间字段来作为分区键，你应该把ID和时间字段设为分区表的组合主键。

三、分区的优点

1.和单个磁盘或者文件系统分区相比，可以存储更多数据
2.优化查询。在where子句中包含分区条件时，可以只扫描必要的一个或者多个分区来提高查询效率；同时在涉及sum()和count()这类聚合函数的查询时，可以容易的在每个分区上并行处理，最终只需要汇总所有分区得到的结果
3.对于已经过期或者不需要保存的数据，可以通过删除与这些数据有关的分区来快速删除数据
4.跨多个磁盘来分散数据查询，以获得更大的查询吞吐量

四、查看数据库文件

通过show variables like ‘%datadir%’;命令查看mysql的data存放目录，进入所在的数据库目录。不同的引擎数据库文件格式不同。

mysql> show variables like '%datadir%';
+---------------+---------------------------------------------+
| Variable_name | Value                                       |
+---------------+---------------------------------------------+
| datadir       | C:\ProgramData\MySQL\MySQL Server 5.7\Data\ |
+---------------+---------------------------------------------+

innodb: 只有设置成独立表空间才能做成功表分区 
.frm : 表结构
.ibd : 数据 + 索引

五、分区详解和应用

MySQL有五种分区类型 range、list、hash、key、子分区，其中最常用的是range和list分区

首先需要确定你使用的数据库有没有开启分区功能

-- 查看mysql版本
select version( )    ，   mysql5.1开始支持数据表分区

-- 查看分区插件是否激活 partition active
show plugins; 

(1) range分区
给定一个连续区间的范围值进行分区，某个字段的值满足这个范围就会被分配到该分区。适用于字段的值是连续的区间的字段，如 日期范围, 连续的数字。

-- 语法

create table <table> (
    // 字段
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1
partition by range (分区字段) (
  partition <分区名称> values less than (Value),
  partition <分区名称> values less than (Value),
  ...
  partition <分区名称> values less than maxvalue
);

-- 字段介绍
range：表示按范围分区 
分区字段：表示要按照哪个字段进行分区，可以是一个字段名，也可以是对某个字段进行表达式运算如year(create_time)，使用range最终的值必须是数字 
分区名称: 要保证不同，也可以采用 p0、p1、p2 这样的分区名称， 
less than : 表示小于 
Value : 表示要小于某个具体的值，如 less than (10) 那么分区字段的值小于10的都会被分到这个分区 
maxvalue: 表示一个最大的值

注意：range 对应的分区键值必须是数字值，可以使用range columns(分区字段) 对非int型做分区，如字符串，对于日期类型的可以使用year()、to_days()、to_seconds()等函数。

-- 创建方式
分区可以在创建表的时候进行分区，也可以在创建表之后进行分区

alter table <table> partition by RANGE(id) (
    PARTITION p0 VALUES LESS THAN (1000000),
    PARTITION p1 VALUES LESS THAN (2000000),
    PARTITION p2 VALUES LESS THAN (3000000),
    PARTITION p3 VALUES LESS THAN (4000000),
    PARTITION p4 VALUES LESS THAN MAXVALUE 
);

CREATE TABLE `tbl_user_part` (
   `id` int(11) NOT NULL ,
   `username` varchar(255) DEFAULT NULL,
   `email` varchar(20)     DEFAULT NULL,
   `age` tinyint(4)        DEFAULT NULL,
   `type` int(11)          DEFAULT NULL,
   `create_time` datetime  DEFAULT CURRENT_TIMESTAMP
   -- PRIMARY KEY (`id`,`age`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
PARTITION BY RANGE (age) (
    PARTITION p0 VALUES LESS THAN (20),
    PARTITION p1 VALUES LESS THAN (40),
    PARTITION p2 VALUES LESS THAN (60),
    PARTITION p3 VALUES LESS THAN (80),
    PARTITION p4 VALUES LESS THAN MAXVALUE
);

-- 常见错误
在创建分区的时候经常会遇到这个错误：A PRIMARY KEY must include all columns in the table’s partitioning function。意思是说分区的字段必须是要包含在主键当中。
 可以使用PRIMARY KEY (id,xxx)来将多个字段作为主键。在做分区表时，选择分区的依据字段时要谨慎，需要仔细斟酌这个字段拿来做为分区依据是否合适，这个字段加入到主键中做为复合主键是否适合。
 
(2) list  分区-- 语法

create table <table> (
    // 字段
) ENGINE=数据库引擎  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1

partition by LIST (分区字段或者基于该字段的返回的整数值的表达式) (
  partition <分区名称> values IN (Value1，Value2, Value3),
  ...
  partition <分区名称> values IN (Value4, Value5),
);

(3) 关于 hash 和 key 分区这里不做介绍.........

六、管理分区

mysql提供了添加、删除、重定义、合并、拆分分区的命令，这些操作都可以通过alter table 命令来实现

-- 删除list或者range分区(同时删除分区对应的数据)
alter table <table> drop partition <分区名称>;
注意： 删除分区的同时会删除数据

-- 取消分区，不删除数据
alter table <table> remove partitioning;

-- range添加新分区
alter table <table> add partition(partition p4 values less than MAXVALUE);
注意：RANGE 的分区方式在加分区的时候，只能从最大值后面加，而最大值前面不可以添加

-- list添加新分区
alter table <table> add partition(partition p4 values in (25,26,28));