杭州房产网官方网站,共享办公都有哪些公司,科技公司手机网站,河南做网站公司mysql检验分区性能的操作
创建两个结构相同但是一个有分区另外一个没有分区的表 如上图我们给part_tab5创建的分区为1024个#xff0c;因为mysql中允许最多有1024个分区#xff1b;之前我测试的是创建8个分区#xff0c;然后插入500万条数据#xff0c;然后按照id查询因为mysql中允许最多有1024个分区之前我测试的是创建8个分区然后插入500万条数据然后按照id查询非索引part_tab5表的查询速率大约是part_tab6表的查询速率的9倍注意这里测试的时候不要按照主键查询因为主键是索引按照索引查询的时候即便没有分区也是非常快的。
现在我很好奇如果我创建了1024个分区然后插入5000万条数据带分区的查询速率会比不带分区的快多少呢
使用存储过程往带有分区的表插入5000万条数据
如下图 可以使用select命令随时监控插入的条数如下图
拷贝一份数据到没有加分区的表中
part_tab6表的结构字段和part_tab5一样只不过它没有加分区等到part_tab5表中的5000万条数据插入完成之后拷贝一份到part_tab6表中代码如下图
对比条件查询速率
如果是select 星去查询全部的话那么有分区的查询的会更慢一些但是如果是按条件查询的话比如按照id查询它的查询时间会更快如下图 注意这里千万不要对比按照c1字段条件查询的速率因为c1是索引内部使用了B树它的查询速率是非常快的你是对比不出来的。而id是一个普通字段采用了分区和不采用分区对比就非常明显了。
hash分区的时候如果只对其中的某一列进行分区的话那么其它列速率仍然低
比如我现在有一张表它的字段有column1,column2,column3我现在只对column3列进行了hash分区其它两列没有进行hash分区那么按条件查询的时候如果是按照column3条件进行查询查询速率是比较快的因为它使用了分区但是如果按照其它的两列条件查询的话查询速率是很慢的因为其它两列没有进行hash分区。
创建太多分区应该也不太好先创建100个分区。想要用hash分区给多个列分区格式是不是hash(列名1 列名2)先假设是这样。那么我们创建表的时候就不要有主键了因为如果有主键的话我们的hash分区的字段必须是主键包含的字段也就是主键里面必须有所有hash分区的字段也就是如果这里的主键不包含列名1和列名2那么hash(列名1 列名2)这样写就是错误的。那如果主键就包含一个字段而我们想要hash分区的列有好多个就没办法使用hash分区了。因此我不太确定这样正不正确先自己测试一下。
后来我又问了大侠大侠说没必要每个列名都给他分区分区一个就行了比如说我们给时间列名分区每次查询的时候都可以带上时间它就会找到对应的区了。但是如果是hash分区这就要求主键必须有时间这肯定是不合理的。所以我们不能够用hash分区对时间进行分区。要想一下RANGE分区可不可以
看一下hash分区的时候写hash(列名1 列名2)的效果如下图 最终的效果是如果hash分区的时候是按照id和c1分区的那么筛选的时候只有同时有这两个条件的时候才能找到具体是哪个分区才能提高效率如下图 但是如果where筛选条件不是同时包含c1和id那么查询效率就很慢了如下图 只要筛选条件包含建立分区的所有字段不管后面还有没有其它的筛选条件那么分区都有效如下图 所以这里能不能按照时间进行hash分区然后每次条件筛选的时候都加上时间范围呢这个我要建立一张表测试一下不用建立了不行因为不可能把时间当做一张表的主键。
后面我又使用RANGE分区的时候发现分区字段也必须是在主键里面的字段所以所有的分区类型都要求如果有主键那么分区的字段一定是主键里面的某个字段。 因此使用分区的时候一般会把那个字段加上一个联合主键。如下图 所以现在把id和test_time时间设置成了联合主键那么用hash分区也是可以的但是hash分区的时候不允许类型是datetime类型我们需要把它变成时间戳bigint类型然后进行hash分区测试一下使用hash分区(test_time)分成100个区500w条数据和使用上面的RANGE分区分成12个区500w条数据看一下后面那个查询效率高
现在我猜测是使用hash分区分成100个表的查询效率比较高。现在正在使用存储过程生成500w条数据生成完之后对比一下。
先不管上面的看下下面的
使用的是RANGE分区按照日期中的月份分为12个区日期是1月份的行放到 month1分区可以理解成一个子表日期是2月份的行放到month2分区可以理解成一个子表。。。日期是12月份的行放到month12分区可以理解成一个子表。
创建分区表part_tab19和未分区表part_tab20
如下图 代码如下
CREATE TABLE part_tab19 (id int,c1 int,test_time datetime,PRIMARY KEY(id, test_time))engineINNODB PARTITION BY RANGE (month(test_time)) (PARTITION month1 values less than(2),PARTITION month2 values less than(3),PARTITION month3 values less than(4),PARTITION month4 values less than(5),PARTITION month5 values less than(6),PARTITION month6 values less than(7),PARTITION month7 values less than(8),PARTITION month8 values less than(9),PARTITION month9 values less than(10),PARTITION month10 values less than(11),PARTITION month11 values less than(12),PARTITION month12 values less than(13));CREATE TABLE part_tab20 (id int,c1 int,test_time datetime,PRIMARY KEY(id, test_time))engineINNODB;使用存储过程往分区表里面插入500w条数据
如下图 存储过程代码如下
delimiter $$create procedure ss()BEGINdeclare i int;set i 1;while i 5000000doinsert into part_tab19(id, c1, test_time) VALUES(i, i, date_add(2023-10-28, INTERVAL i%12 month)) ;set i i 1 ;end while ;END $$把分区表中的数据拷贝到未分区表中
如下图 代码如下 insert into part_tab20 select * from part_tab19;看下分区表part_tab19和未分区表part_tab20里面的数据条数是否一致如下图 发现现在这俩表的数据是完全一致的这两个表的字段数据都是一样的唯一的区别就是一个表有分区而另外一个表没有分区。
接下来看一下分区是怎么提升查询速率的
首先在未分区的表part_tab20中我们查询一下c1162这条数据看看需要多少时间如下图 从上图可以发现接近1.3s 接下来加上时间查询条件看看需要多久如下图 可以发现加上时间范围筛选条件之后没有什么变化甚至查询时间更长了为什么呢因为我们的part_tab20没有按照时间中的月份进行分区
接下来看一下分区的表part_tab19中我们查询一下c1162这条数据加上时间筛选的查询时间如下图 可以发现只需要0.3秒
通过分区表的查询时间0.3和未分区表的查询时间1.3对比可得分区之后速率快了接近4倍
分区之后表的查询速率为什么会快呢
比如我们上面的例子把表分为了12个区每个月份为一个区其实也就相当于是按照日期的月份分为了十二张子表当我们按照月份条件进行查询的时候不会去查询总表的数据而是会去查询子表中的数据因为子表中的数据相对较少因此查询速率就会提高。
