前言
Mysql是目前比较主流的一款关系型数据库,详细介绍点击.
本次所介绍的内容是基于mysql数据库三百万数据的基础上进行查询优化。需要对数据库命令操作和数据库索引以及数据库分区有一定的了解。
开发环境
MySQL5.7.30下载链接
操作系统win10
优化过程
表结构如下:
备注:本次优化过程将student表复制三份,方便测试用,分别为:
1、student
2、student_test
3、student_test_copy
本次优化过程中默认使用mysql innodb引擎,点击了解相关引擎。
student_test_copy表count查询及sql执行计划,从下图可以看出count查询耗时24秒(项目经理发现前端调用分页查询接口耗时24秒不知作何感想-_-!!)
从sql执行计划中可以看出student_test_copy表未进行分区处理,接下来对表student_test_copy进行分区操作。分区详解
创建好的分区表如下,此时我们可以通过执行SQL语句(insert into student select * from student_test_copy;)来进行数据导入,此过程需要一定的时间,依据数据量大小和硬件配置有关。
数据导入完成后,我们对分区后的表进行查询操作,可以发现出现了负优化的情况!!!从原来的24秒变成了1分33秒!!!什么情况(老板os:卷铺盖走人)
出现负优化的情况是出乎我们意料之外,于是乎赶紧查阅相关资料,先看下数据库数据文件(可以通过show global variables like “%datadir%” ;指令查看数据文件路径),分区表(student)比未分区表(student_test_copy)多了一个.ibd文件。
.frm文件:存储数据表的框架结构
.ibd文件:单表表空间文件,每个表使用一个表空间文件(file per table),存放用户数据库表数据和索引
细心的小伙伴应该发现了.ibd文件名#号后面跟的就是分区表的分区名。
此时表的两个分区在同一个磁盘的同一个目录下面,而且SQL执行计划也是说明走了两个分区,并没有问题。这时候问题就有点大了-_-!!!
然后又去查阅相关资料,终于有人提到是不是因为分区在同一个磁盘,因为磁盘的IO情况导致的,仔细想想好像确实有这么个回事,话不多说,赶紧动手操作。
新建 student_test表,同样是两个分区,并指定分区路径,表建好后同样用(insert into student_testselect * from student_test_copy;)SQL插入数据,表结构如下。
再次进行查询,结果如下,3秒内查询出结果。相比前两次24秒和1分33秒已经有很大的提升了。
可以看出数据库分区对数据查询效率优化还是有很大提升的,然后我们再来分析一下分区后的数据文件:
1、可以看出在数据文件中,表(student_test)对应的ibd文件没有了
2、多了两个isl文件,我们打开isl文件可以看到里面对应的是ibd文件路径,这也就是我们创建分区表的时候指定的分区路径。
结语
至此,本篇文章所讲述的内容已经告一段落了,数据库优化还有很长的路要走,表分区只是其中很小的一部分。初次创作,欢迎各路大神指点,若有错误之处请及时指出,本人将及时纠正。