一篇文章讲透mysql数据库下的分区表

• 正确理解数据库分区表

• 分区表的特征及分区类型

• 分区表的应用场景

什么是分区表

分区表通过对分区列的判断，把分区列不同的记录，放到不同的分区中。 查询时可以通过查询表来访问各个分区中的数据，也可以通过在查询时直接指定分区的方法来进行查询。

 

首先来看下正常的表的存放方式

输入cd /var/lib/mysql/test_database/，再ls,查看数据库下的表(customer_login_log)文件信息：

 

customer_login_log.frm #保存了每个表的元数据，包括表结构以及相应的定义,无论什么数据引擎都有此文件

customer_login_log.ibd #innodb的存放数据文件和索引的地方

customer_login_log--表名

 

换引擎测试

 

分区表在：customer_login_log.idb上会有多个

• 在mysql v5.1版本后就开始支持分区表功能

show variables like '%partition%';

• mysql v5.6(含)版本下使用这个命令,看系统是否支持分区

show plugins;

        

    

我们为什么要使用分区表

• 经常遇到上千万甚至上亿的记录表数据

• 查询困难，而且历史数据其实是不太关心的

• 要进行归档了，那么如何归档？

1. insert select:一年前的数据通过where<20190101 00:00:00查出来insert进arch,bak

2.dump方式：mysqldump是可以加where条件

原表的历史数据要删除，方式： 

1.delete方式：delete table where time< and time>,删除不要的

innodb数据是有b+树组织的，如果delete where只会标记该数据被删除，不会真正删除，将来

插入数据的时候空间可复用

mysql> optimize table my_table_name;

2.把要的数据导出，创建新表，不要的数据备份，旧表drop

• 如果我们有这样一种文件组织形式：2017、2018数据单独都放一个文件，这个时候就可以按照文件进行

删除了，并且可以指定数据文件查询范围，就会提升查询效率

分区表都有哪些分区类型

• HASH分区：按照hash算法分区

• List分区:   

• RANGE分区(重点):

• KEY分区:

HASH分区

• 根据MOD(分区键)的值把数据存储到表的不同分区中

• 基本可以平均分布于各个区

• HASH分区的键必须为整形，可以通过函数转换为int类型

create table test2(name varchar(5),age int(3)) ENGINE=InnoDb 

DEFAULT CHARSET=utf8 PARTITION BY HASH(age) PARTITIONS 4;  # 分成四个文件  age可以转换为函数方式

 

explain partitions select * from test2;#查看分区是否创建成功

 

select table_name,partition_name,partition_description,table_rows from information_schema.PARTITIONS where table_name='test2'; #每个分区存放多少数据 

 

select * from test2 partition(p1) where age=3#明确指定查询哪个分区

 

LIST分区

• 按照分区键取值的列表进行分区

• 各分区的列表值不能重复

• 每一行数据必须能找到对应的分区列表，否则插入失败

 

create table test3(name varchar(5),age int(3)) ENGINE=InnoDb

DEFAULT CHARSET=utf8 PARTITION BY LIST(age) (PARTITION p0 VALUES in(1,3,5,7,9),PARTITION p1 VALUES in(2,4,6,8));  #分成两个文件 

 

RANGE范围分区

• 根据分区键值保存到不同表中

• 多个分区要连续，不能重叠

• 要完全封口则需要使用MAXVALUE

create table test5(name varchar(5),age int(3),login_time dateTime) ENGINE=InnoDb

DEFAULT CHARSET=utf8 PARTITION BY RANGE(age) (PARTITION p0 VALUES LESS THAN (30),PARTITION p1 VALUES LESS THAN (50)

,PARTITION p2 VALUES LESS THAN MAXVALUE);  #分成两个文件,用数字划分   

 

create table test6(name varchar(5),age int(3),login_time dateTime) ENGINE=InnoDb

DEFAULT CHARSET=utf8 PARTITION BY RANGE(YEAR(login_time)) (PARTITION p0 VALUES LESS THAN (2018),PARTITION p1 VALUES LESS THAN (2019)

,PARTITION p2 VALUES LESS THAN (2020)

,PARTITION p3 VALUES LESS THAN MAXVALUE);  #分成两个文件    也可以用 时间,dateTime YEAR(login_time)

 

日志数据进行备份的时候 

在mysql v5.7版本后，加入了一个分区交换的概念

• 表结构要相同

• 归档的表不能是分区表

• 归档表不能有外键约束

• ARCHIVE

create table test5_2016(name varchar(5),age int(3),login_time dateTime) ENGINE=InnoDb

DEFAULT CHARSET=utf8 #归档表

 

ALTER TABLE test5 exchange PARITITON p0 WITH TABLE test5_2016; #将数据备份到归档表中

ALTER TABLE test5_2016 ENGINE=ARCHIVE; #归档，文件会变成极小

 

删除分区文件

ALTER TABLE test5 DROP PARITITON p0; #删除分区文件

 

增加分区文件

ALTER TABLE test5 ADD PARITITON(PARTITION p4 VALUES LESS THAN (2020); #增加分区文件

 

 

问题1：如果一个表不是分区表同时已经有数据，能不能变成分区表？可以，如下：

 

不是分区表变成分区表

ALTER TABLE test5 PARITITON BY RANGE(YEAR(login_time))(PARTITION p4 VALUES LESS THAN (2020)); #增加分区文件，重排数据，但数据量大的情况下I/O消耗很大，这种数据量大的表，要按照修改表结构的方式来进行分区表操作

• 建立新结构相同的分区表

• 数据导入

• 表名切换

 

问题2：既然把表分开了，那我们分区表最大有多少个：1024 

 

分区表的限制和注意事项

• 如果表里有主键必须包含分区表的分区键

• 很多时候，使用分区表就不要使用主键了 ，建立主键后可能会影响性能

• 不要建立过多的分区

• 分区表不支持外键

• 分区规则必须要提前设立好，否则修改很麻烦

• 分区表设定时间单位建议以年为单位

 

日志表的特征

• 记录非常的多，非常的大

• 明显的时间区间特征

• 查询频次低，就近时间点查询频次高

• 需要定期归档转储 

在这样的表结构下分区表就是我们的最佳实践。

 

作业：测试分区表的特性，上百万数据并发测试

详细笔记可见我的笔记 ：https://app.yinxiang.com/shard/s66/nl/16944865/cc8f3c15-fb96-4351-9ae6-52efe7750977