MySQL数据库，分区测试

发表于 | 更新于 | 分类于 | 评论数：

一、表分区通俗解释

通俗地讲表分区是将一大表，根据条件分割成若干个小表。mysql5.1开始支持数据表分区了。
如：某用户表的记录超过了600万条，那么就可以根据入库日期将表分区，也可以根据所在地将表分区。当然也可根据其他的条件分区。

二、为什么要对表进行分区？

为了改善大型表以及具有各种访问模式的表的可伸缩性，可管理性和提高数据库效率。

2.1 表分区要解决的问题：
当表非常大，或者表中有大量的历史记录，而“热数据”却位于表的末尾。如日志系统、新闻。。此时就可以考虑分区表。【注：此处也可以使用分表，但是会增加业务的复杂性。】

2.2 表分区有如下优点：
1）与单个磁盘或文件系统分区相比，可以存储更多的数据。
2）对于那些已经失去保存意义的数据，通常可以通过删除与那些数据有关的分区，很容易地删除那些数据。
相反地，在某些情况下，添加新数据的过程又可以通过为那些新数据专门增加一个新的分区，来很方便地实现。
同样的，你可以很快的通过删除分区来移除旧数据。你还可以优化、检查、修复个别分区。
3）一些查询可以得到极大的优化。 可以把一些归类的数据放在一个分区中，可以减少服务器检查数据的数量加快查询。
这主要是借助于满足一个给定WHERE语句的数据可以只保存在一个或多个分区内，这样在查找时就不用查找其他剩余的分区。
PS：因为分区可以在创建了分区表后进行修改，所以在第一次配置分区方案时还不曾这么做时，可以重新组织数据，来提高那些常用查询的效率。
4）涉及到例如SUM()和COUNT()这样聚合函数的查询，可以很容易地进行并行处理。
这种查询的一个简单例子如
“SELECT salesperson_id, COUNT (orders) as order_total FROM sales GROUP BY salesperson_id；”。
通过“并行”，这意味着该查询可以在每个分区上同时进行，最终结果只需通过总计所有分区得到的结果。
5）通过跨多个磁盘来分散数据查询，来获得更大的查询吞吐量。

原先的表结构

1
2
3
4
5
6
7
8
9
10
drop table if EXISTS partition_demo;
create table partition_demo(
    id BIGINT auto_increment,
    batch_no BIGINT,
    `name` VARCHAR(50),
    ticker VARCHAR(50),
    price decimal(18,8),
    primary key join_primary_key (id, batch_no),
    index batch_index (batch_no, `name`)
)

修改表结构

1
2
3
4
5
alter table exchange_transaction_data_bibox modify column id bigint;
alter table exchange_transaction_data_bibox drop PRIMARY key;
alter table exchange_transaction_data_bibox add PRIMARY key(id, batch_no);
alter table exchange_transaction_data_bibox modify column id bigint auto_increment;
show create table exchange_transaction_data_bibox;

添加分区

1
2
3
4
ALTER TABLE exchange_transaction_data_bibox PARTITION BY RANGE(batch_no)(
  partition p20190624 values less than(201906240000),
  PARTITION p20190924 values less than(201909240000)
);

创建存储过程用于：删除90天前的分区，同时创建两天后的分区

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
drop PROCEDURE if EXISTS UPDATE_EXCHANGE_TABLE_PARTITION;
DELIMITER $$
CREATE PROCEDURE UPDATE_EXCHANGE_TABLE_PARTITION(in table_name VARCHAR(50))
BEGIN
 #1. 两天之后的long
 declare BATCH_NO BIGINT;
 declare BATCH_NO_NITY_BEFORE BIGINT;
 declare partition_name VARCHAR(20);
 declare partition_name90 VARCHAR(20);
	
 SET BATCH_NO = CAST(DATE_FORMAT(DATE_ADD(now(), INTERVAL 2 DAY), '%Y%m%d') as signed);
 SET BATCH_NO_NITY_BEFORE = CAST(DATE_FORMAT(DATE_SUB(now(), INTERVAL 91 DAY), '%Y%m%d') as signed);
 set partition_name = CONCAT('p', BATCH_NO);
 set partition_name90 = CONCAT('p', BATCH_NO_NITY_BEFORE);
	
 SET BATCH_NO = BATCH_NO * 10000;
 set @drop_part = CONCAT('alter table ', table_name, ' drop partition ', partition_name90);
 set @create_part = CONCAT('alter table ', table_name, ' add PARTITION (partition ', partition_name ,' values less than(', BATCH_NO ,'))');
	
 SELECT @drop_part,@create_part;
	
 PREPARE stmt FROM @create_part;
  EXECUTE stmt;
	
 PREPARE stmt FROM @drop_part;
  EXECUTE stmt;
END $$
DELIMITER ;

创建定时任务，执行存储过程

CREATE EVENT INSERT_PARTITION_EVENT ON SCHEDULE 
EVERY 1 DAY STARTS '2019-09-24 00:01:00'
DO
    CALL UPDATE_EXCHANGE_TABLE_PARTITION('exchange_transaction_data_bibox');

参考

https://blog.csdn.net/laoyang360/article/details/52886987
https://blog.csdn.net/qq_22783587/article/details/101158018