MySQL数据表分区实践

354 次查看 0 条评论
本文可能超过1年没有更新,今后内容也许不会被维护或者支持,部分内容可能具有时效性,涉及技术细节或者软件使用方面,本文不保证相应的技术更新和实践可操作性。

MySQL支持的分区类型一共有四种:RANGELISTHASHKEY。其中,RANGE 又可分为原生RANGERANGE COLUMNS ; LIST 分为原生 LISTLIST COLUMNS ; HASH 分为原生 HASHLINEAR HASHKEY 包含原生 KEY 和 LINEAR HASH`。

创建测试的数据库表:

mysql -uroot -p
mysql> create database test;
mysql> use test;
mysql> create table `history` (
    `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `a` float(3,2) NOT NULL COMMENT '随机数值',
    `unix_time` int(11) NOT NULL COMMENT '时间戳',
    PRIMARY KEY (`id`,`unix_time`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='测试表';

这里添加了 idunix_time 的复合索引,如果只添加 id 主键的话后续无法使用 unix_time 字段分区。

创建一个存储过程用于构造测试数据:

drop procedure if exists insert_data;
create procedure insert_data(in max int,in time datetime)
begin
    declare id int;
    declare a float;
    set id = 1;
    -- set time = unix_timestamp('2018-01-15 09:45:16');
    while id <= max do
        select truncate(rand(),2) into @a;
        insert into history(a,unix_time) VALUES (@a,unix_timestamp(time));
        set id = id + 1;
    end while;
end

模拟测试数据。执行存储过程,插入数据测试数据:

call insert_data(5000000,'2018-09-16 11:13:23');
call insert_data(5000000,'2018-09-17 13:26:45');
call insert_data(5000000,'2018-09-18 14:37:41');
call insert_data(5000000,'2018-09-19 16:56:14');
call insert_data(5000000,'2018-09-20 17:53:01');
call insert_data(5000000,'2018-09-21 18:23:34');
call insert_data(5000000,'2018-09-22 19:43:54');

特构造了3500万的测试数据,放在 history 表中。其中 unix_time 包含7天的数据,从 2018-09-162018-09-22 (时间戳)。具体信息如下:

select min(id),max(id) from history;
select count(*) from history;

30270-ogih978pydf.png

select from_unixtime(unix_time) as time,count(*) as count from history group by unix_time;

83392-eyu98ljim46.png

数据构建完毕,先来做几个查询记录下所消耗时间。后面分区后再次执行相同查询对比查询消耗时间情况。

查询语句分区前耗时
select * from history where unix_time = 1537616634 limit 5;2.48秒
select * from history where id = 56841;0.34秒
select * from history where a = 0.43;3.06秒
select count(*) from history;0.00秒
select from_unixtime(unix_time) as time,count(*) as count from history group by unix_time;5.31秒
create table history_temp select * from history where unix_time = 1537162005;8.33秒
insert into history_temp select * from history where unix_time = 1537347374;4.96秒

测试的维度。测试的维度主要从两个方面进行:

1、分区剪裁

针对特定的查询,是否能进行分区剪裁(即只查询相关的分区,而不是所有分区)

2、查询时间

鉴于该批测试数据是静止的(即没有并发进行的insert、update、delete操作),数据量也不太大,从这个维度来考量貌似意义也不是很大。

因此,重点测试第一个维度。

分区。基于 RANGE 的分区方案。对3500万数据按天分区:

alter table `history` partition by range ( unix_time ) (
    partition p20180916 values less than (unix_timestamp("2018-09-17 00:00:00")),
    partition p20180917 values less than (unix_timestamp("2018-09-18 00:00:00")),
    partition p20180918 values less than (unix_timestamp("2018-09-19 00:00:00")),
    partition p20180919 values less than (unix_timestamp("2018-09-20 00:00:00")),
    partition p20180920 values less than (unix_timestamp("2018-09-21 00:00:00")),
    partition p20180921 values less than (unix_timestamp("2018-09-22 00:00:00")),
    partition p20180922 values less than (unix_timestamp("2018-09-23 00:00:00"))
);

84698-pepwbx31d3.png

查看分区情况:

select partition_name part,partition_expression expr,partition_description descr,table_rows from information_schema.partitions where table_schema = schema() and table_name='history';

59188-uzxn13zcte.png

分区后文件系统情况:

18269-31lgwlohkoe.png

维护增加新分区:

alter table `history` add partition (
    partition p20180923 values less than (unix_timestamp('2018-09-24 00:00:00'))
);

66744-ukhik3yj8wh.png

删除过期无用分区:

alter table `history` drop partition p20180916;

执行一些 sql 语句,查看下其执行情况:

select * from history where unix_time = 1537162005 limit 10;
explain partitions select * from history where unix_time = 1537162005 limit 10;

54633-7p2cnrrebvf.png

只查询了 p20180917 三个分区,由此来看确实实现了分区裁剪。好了,再来执行下没有分区之前的那些查询对比下消耗时间。

查询语句分区前耗时分区后耗时
select * from history where unix_time = 1537616634 limit 5;2.48秒0.01秒
select * from history where id = 56841;0.34秒0.06秒
select * from history where a = 0.43;3.06秒3.70秒
select count(*) from history;0.00秒0.00秒
select from_unixtime(unix_time) as time,count(*) as count from history group by unix_time;5.31秒13.94秒
create table history_temp select * from history where unix_time = 1537162005;8.33秒2.22秒
insert into history_temp select * from history where unix_time = 1537347374;4.96秒2.15秒

参考资料:

  1. https://www.cnblogs.com/ivictor/p/5032793.html
  2. https://blog.csdn.net/aofavx/article/details/50462687
  3. https://www.cnblogs.com/phpshen/p/6198375.html
MySQL 分区

暂无评论,快来抢沙发。