mysql8.0 分区表查询性能调查

mysql8.0 分区表查询性能调查

测试环境

  • vmware CentOS Linux release 7.8.2003 (Core) 64位
  • 2处理器，2G
  • mysq l8.0.23

创建表

14个分区，每个分区400W数据,共5600W数据。

CREATE TABLE bdm_range_datetime(
	`id` BIGINT NOT NULL AUTO_INCREMENT,
    hiredate DATETIME,
    user_name varhcar(64),
   PRIMARY KEY (`id`,hiredate) 
)
PARTITION BY RANGE (TO_DAYS(hiredate) ) (
    PARTITION p1 VALUES LESS THAN ( TO_DAYS('20200101') ),
    PARTITION p2 VALUES LESS THAN ( TO_DAYS('20200201') ),
    PARTITION p3 VALUES LESS THAN ( TO_DAYS('20200301') ),
    PARTITION p4 VALUES LESS THAN ( TO_DAYS('20200401') ),
    PARTITION p5 VALUES LESS THAN ( TO_DAYS('20200501') ),
    PARTITION p6 VALUES LESS THAN ( TO_DAYS('20200601') ),
    PARTITION p7 VALUES LESS THAN ( TO_DAYS('20200701') ),
    PARTITION p8 VALUES LESS THAN ( TO_DAYS('20200801') ),
    PARTITION p9 VALUES LESS THAN ( TO_DAYS('20200901') ),
    PARTITION p10 VALUES LESS THAN ( TO_DAYS('20201001') ),
	PARTITION p11 VALUES LESS THAN ( TO_DAYS('20201101') ),
	PARTITION p12 VALUES LESS THAN ( TO_DAYS('20201201') ),
	PARTITION p13 VALUES LESS THAN ( TO_DAYS('20210101') ),
	PARTITION p14 VALUES LESS THAN ( TO_DAYS('20210201') )
);

数据初始化

CREATE DEFINER=`root`@`%` PROCEDURE `test_insert`(IN `hiredate` INT)
	LANGUAGE SQL
	NOT DETERMINISTIC
	CONTAINS SQL
	SQL SECURITY DEFINER
	COMMENT ''
BEGIN
DECLARE y INT DEFAULT 1;
WHILE y<40000
DO
INSERT INTO bdm_range_datetime(`hiredate`,`user_name`) VALUES 
(hiredate,LAST_INSERT_ID()+1),(hiredate,LAST_INSERT_ID()+1),(hiredate,LAST_INSERT_ID()+1),(hiredate,LAST_INSERT_ID()+1),(hiredate,LAST_INSERT_ID()+1),(hiredate,LAST_INSERT_ID()+1),(hiredate,LAST_INSERT_ID()+1),(hiredate,LAST_INSERT_ID()+1),(hiredate,LAST_INSERT_ID()+1),(hiredate,LAST_INSERT_ID()+1),
(hiredate,LAST_INSERT_ID()+1),(hiredate,LAST_INSERT_ID()+1),(hiredate,LAST_INSERT_ID()+1),(hiredate,LAST_INSERT_ID()+1),(hiredate,LAST_INSERT_ID()+1),(hiredate,LAST_INSERT_ID()+1),(hiredate,LAST_INSERT_ID()+1),(hiredate,LAST_INSERT_ID()+1),(hiredate,LAST_INSERT_ID()+1),(hiredate,LAST_INSERT_ID()+1),
(hiredate,LAST_INSERT_ID()+1),(hiredate,LAST_INSERT_ID()+1),(hiredate,LAST_INSERT_ID()+1),(hiredate,LAST_INSERT_ID()+1),(hiredate,LAST_INSERT_ID()+1),(hiredate,LAST_INSERT_ID()+1),(hiredate,LAST_INSERT_ID()+1),(hiredate,LAST_INSERT_ID()+1),(hiredate,LAST_INSERT_ID()+1),(hiredate,LAST_INSERT_ID()+1),
(hiredate,LAST_INSERT_ID()+1),(hiredate,LAST_INSERT_ID()+1),(hiredate,LAST_INSERT_ID()+1),(hiredate,LAST_INSERT_ID()+1),(hiredate,LAST_INSERT_ID()+1),(hiredate,LAST_INSERT_ID()+1),(hiredate,LAST_INSERT_ID()+1),(hiredate,LAST_INSERT_ID()+1),(hiredate,LAST_INSERT_ID()+1),(hiredate,LAST_INSERT_ID()+1),
(hiredate,LAST_INSERT_ID()+1),(hiredate,LAST_INSERT_ID()+1),(hiredate,LAST_INSERT_ID()+1),(hiredate,LAST_INSERT_ID()+1),(hiredate,LAST_INSERT_ID()+1),(hiredate,LAST_INSERT_ID()+1),(hiredate,LAST_INSERT_ID()+1),(hiredate,LAST_INSERT_ID()+1),(hiredate,LAST_INSERT_ID()+1),(hiredate,LAST_INSERT_ID()+1),
(hiredate,LAST_INSERT_ID()+1),(hiredate,LAST_INSERT_ID()+1),(hiredate,LAST_INSERT_ID()+1),(hiredate,LAST_INSERT_ID()+1),(hiredate,LAST_INSERT_ID()+1),(hiredate,LAST_INSERT_ID()+1),(hiredate,LAST_INSERT_ID()+1),(hiredate,LAST_INSERT_ID()+1),(hiredate,LAST_INSERT_ID()+1),(hiredate,LAST_INSERT_ID()+1),
(hiredate,LAST_INSERT_ID()+1),(hiredate,LAST_INSERT_ID()+1),(hiredate,LAST_INSERT_ID()+1),(hiredate,LAST_INSERT_ID()+1),(hiredate,LAST_INSERT_ID()+1),(hiredate,LAST_INSERT_ID()+1),(hiredate,LAST_INSERT_ID()+1),(hiredate,LAST_INSERT_ID()+1),(hiredate,LAST_INSERT_ID()+1),(hiredate,LAST_INSERT_ID()+1),
(hiredate,LAST_INSERT_ID()+1),(hiredate,LAST_INSERT_ID()+1),(hiredate,LAST_INSERT_ID()+1),(hiredate,LAST_INSERT_ID()+1),(hiredate,LAST_INSERT_ID()+1),(hiredate,LAST_INSERT_ID()+1),(hiredate,LAST_INSERT_ID()+1),(hiredate,LAST_INSERT_ID()+1),(hiredate,LAST_INSERT_ID()+1),(hiredate,LAST_INSERT_ID()+1),
(hiredate,LAST_INSERT_ID()+1),(hiredate,LAST_INSERT_ID()+1),(hiredate,LAST_INSERT_ID()+1),(hiredate,LAST_INSERT_ID()+1),(hiredate,LAST_INSERT_ID()+1),(hiredate,LAST_INSERT_ID()+1),(hiredate,LAST_INSERT_ID()+1),(hiredate,LAST_INSERT_ID()+1),(hiredate,LAST_INSERT_ID()+1),(hiredate,LAST_INSERT_ID()+1),
(hiredate,LAST_INSERT_ID()+1),(hiredate,LAST_INSERT_ID()+1),(hiredate,LAST_INSERT_ID()+1),(hiredate,LAST_INSERT_ID()+1),(hiredate,LAST_INSERT_ID()+1),(hiredate,LAST_INSERT_ID()+1),(hiredate,LAST_INSERT_ID()+1),(hiredate,LAST_INSERT_ID()+1),(hiredate,LAST_INSERT_ID()+1),(hiredate,LAST_INSERT_ID()+1);
SET y=y+1;
END WHILE ;
commit;
END

调用：

call test_insert(20191231);
call test_insert(20200101);
call test_insert(20200201);
call test_insert(20200301);
call test_insert(20200401);
call test_insert(20200501);
call test_insert(20200601);
call test_insert(20200701);
call test_insert(20200801);
call test_insert(20200901);
call test_insert(20201001);
call test_insert(20201101);
call test_insert(20201201);
call test_insert(20210101);

执行情况

PRIMARY KEY (`id`,hiredate) 组合主键

1个分区执行时间- PRIMARY KEY (`id`,hiredate) 组合主键

一个分区查询
select count(*) from bdm_range_datetime where hiredate='2019-12-31' ;
/* Affected rows: 0  已找到记录: 1  警告: 0  持续时间 1 query: 0.734 sec. */

索引全分区查询
select * from bdm_range_datetime1 where id=300
/* Affected rows: 0  已找到记录: 1  警告: 0  持续时间 1 query: 0.015 sec. */

索引id查询
select * from bdm_range_datetime1 where id=300 and hiredate='2019-12-31'
/* Affected rows: 0  已找到记录: 1  警告: 0  持续时间 1 query: 0.000 sec. */

非索引列查询
select count(*) from bdm_range_datetime1 where user_name='3999' and hiredate='2019-12-31';
/* Affected rows: 0  已找到记录: 1  警告: 0  持续时间 1 query: 0.969 sec. */

跨两分区非索引列查询
select count(*) from bdm_range_datetime1 
where user_name='39998902' and (hiredate='2019-12-31' or hiredate='2020-01-01')
;
/* Affected rows: 0  已找到记录: 1  警告: 0  持续时间 1 query: 1.859 sec. */


1个分区执行时间-hiredate索引
select count(*) from bdm_range_datetime where hiredate='2019-12-31';
/* Affected rows: 0  已找到记录: 1  警告: 0  持续时间 1 query: 1.453 sec. */
id无索引 hiredate索引： 给分片键创建了索引，速度会更慢。
select count(*) from bdm_range_datetime where hiredate='2019-12-31' and id=3;
/* Affected rows: 0  已找到记录: 1  警告: 0  持续时间 1 query: 37.703 sec. */


id无索引 hiredate没有索引
select count(*) from bdm_range_datetime where hiredate='2019-12-31' and id=3;
/* Affected rows: 0  已找到记录: 1  警告: 0  持续时间 1 query: 3.000 sec. */

1个分区执行时间-hiredate没有索引
select count(*) from bdm_range_datetime where hiredate='2019-12-31';
/* Affected rows: 0  已找到记录: 1  警告: 0  持续时间 1 query: 2.968 sec. */
2个分区执行时间
select count(*) from bdm_range_datetime where hiredate='2019-12-31' or hiredate='2020-01-02' ;
/* Affected rows: 0  已找到记录: 1  警告: 0  持续时间 1 query: 1.516 sec. */
跨分区数量 执行时间（S）
1 0.7
2 1.5
3 2.7
4 3.8
5 5.0
6 6.4
7 7.8
8 9.5
9 11.3
10 13.2
11 15
12 17.3
13 19
14 21.6

统计结论增加一个分区不多1倍的执行时间。

14 分片键查询

带分片键
select * from bdm_range_datetime where hiredate='2021-01-04' and user_name='56003838'
/* Affected rows: 0 已找到记录: 1 警告: 0 持续时间 1 query: 1.218 sec. */

跨7个分区
select * from bdm_range_datetime where (hiredate<'2021-01-08' and hiredate>'2020-08-04') and user_name='56003838'；
/* Affected rows: 0 已找到记录: 1 警告: 0 持续时间 1 query: 9.578 sec. */

不待分片键
select * from bdm_range_datetime where user_name='56003838';
/* Affected rows: 0 已找到记录: 1 警告: 0 持续时间 1 query: 26.500 sec. */

差26倍。

总结

根据官网对分片键的限制解释，经过测试，确实是这样：

  • 如果一个表有主键了，分片键必须与主键组合主键。或删除表的主键，单独创建分片键进行分区。
  • 为分区键创建索引，执行效率会更慢。
  • 不管多少个分区，跨一个分区就会多一倍的执行时间
  • 条件包括分区键和非索引列 400W一个分区，执行时间为1秒，多一个分区就多1秒。
  • 条件包括分区键和索引列 400W一个分区，执行时间为0秒，多少分区没有影响。
posted on 2021-04-20 10:24  wolf12  阅读(1061)  评论(0)  编辑  收藏  举报

Powered by: 博客园 Copyright © 2024 wolf12
Powered by .NET 8.0 on Kubernetes