1. 何为分区?
分区概念图如下,分区类型包括
- 列分区:包括范围分区(如create_time,大于1998可分为一个区,小于它又 可分为一个区)和列表分区(如age in (31,32,33),当插入数据,age=31时为a区,age=32时为b区,age=33时为c区)
- 哈希分区:通过取模运算进行分区
- key分区::ey进行进行分区,也使用了取模运算
- 子分区:即在分区的基础上还能继续进行分区
2. 进行分区测试
这里为了突出对比,分为两张表都为100w数据量,一张表分区一张没有,最后进行查询通过执行时间对比其效率高低。初始表为user100w,测试数据中,根据创建时间,以年为单位已经明确分为了五个范围。
接下来以18,19,20,21,22进行表分区,在分区前记录下Mysql中存储文件的格式,这里为Innodb执行引擎,所以此时与表相关的只会有frm(保持表结构),和ibd(保持数据和索引B+树)两个文件存在。
开始创建分区表
CREATE TABLE user_part100w (
id INT NOT NULL AUTO_INCREMENT,
first_name VARCHAR(10) NOT NULL,
last_name VARCHAR(10) NOT NULL,
sex VARCHAR(5) NOT NULL,
score INT NOT NULL,
copy_id INT NOT NULL,
create_time TIMESTAMP NOT NULL,
KEY `id` (`id`)
)
PARTITION BY RANGE ( UNIX_TIMESTAMP(create_time) ) (
PARTITION p0 VALUES LESS THAN (UNIX_TIMESTAMP ('2019-01-01')),
PARTITION p1 VALUES LESS THAN (UNIX_TIMESTAMP ('2020-01-01')),
PARTITION p2 VALUES LESS THAN (UNIX_TIMESTAMP ('2021-01-01')),
PARTITION p3 VALUES LESS THAN (UNIX_TIMESTAMP ('2022-01-01')),
PARTITION p4 VALUES LESS THAN MAXVALUE
);
关于分区表创建时,分区键的选择需要说明下,如果使用了主键或者索引,在分区键里必须包含主键和索引,博主查询到一篇当表含主键时进行分区的解决方案。
当插入的创建时间小于(不包括)2019-01-01时存p0中。
当插入的创建时间小于(不包括)2020-01-01时存p1中。
当插入的创建时间小于(不包括)2021-01-01时存p2中。
当插入的创建时间小于(不包括)2022-01-01时存p3中。
当插入的创建时间小于(不包括)TIMESTAMP类型的最大值(timestamp类型是4个字节,最大值是2的31次方减1,也就是2147483647,转换成北京时间就是2038-01-19 11:14:07)时存p4。
创建完成之后,可以看到存储文件变为如下图所示了:
有po~p4五个分区,可以通过查询 Information_schema的partitions表来查看分区表的情况。
接下来开始通过存储过程插入100w条数据,插入的创建时间仍然分为2018…,2019…,2020…,2021…,2022…五个时间,这个过程本文中省去,数据插入完成后通过查询 Information_schema的partitions表验证是否插入成功。
这里插入完成之后,分区表成功存储了相应数据,不过通过Information_schema的partitions表查出来的表行数有些误差,我估摸着是个估计值,若小伙伴有思路可告知,谢谢~
数据准备完成之后,可以开始效率测试了!user100w表是未分区的,user_part100w表是进行了分区的。
测试结构如下所示:
可以发现,未分区的表查询效率明显要比分区表查询效率要??为了进一步探究详情,接下来查看Query_ID = 1(select未分区表)和Query_ID=2(select未分区表)的SQL语句的执行状态。
通过对比可以发现,当select分区表的执行流程要比普通表多出一大截,这里想到了分区的一个概念,当访问分区表时,MySQL会锁底层表,这个也是消耗效率的一部分,当它的执行流程变复杂了,消耗的时间自然也多了,此时反而还没有使用未分区表的查询效率高。但使用分区还有个好处就是便于维护,对数据进行了"分而治之",切割开来,便于处理大数据。
3.后言
结论:当数据量为100w时,还是别吃饱了撑着进行分区。。