一次mysql查询过慢的优化记录

在生产环境中有一个功能,加载数据超过了30秒,于是开始启动优化之路。优化过程大致为:

  1. 数据同步到本地进行测试
  2. 找出慢查询的语句
  3. 查询语句在本地与服务器上分别执行进行对比
  4. 分析慢查询的原因
  5. 提出优化解决方案

1 数据同步到本地进行测试

第一步做的是将数据同步到本地进行测试,毕竟在生产环境中进行测试还是不可取的。本次数据库数据量有12G左右,使用mysqldump

来进行数据备份,并通过ftp或者共享文件夹的方式下载到本地恢复。

备份数据命令:

#进入mysql安装目录bin目录下:
mysqldump -uroot -p db_name > D:/db_name.sql
#默认使用的是root账户,如果是其他账户,在-u后面直接跟账户名称。   db_name替换为实际数据库名称
#上述命令回车后,需要输入密码。

备份数据库相对来说需要花费较长的时间,10G大概在20分钟以内。同时需要注意,使用mysqldump命令时,可能会占用较多的资源

,影响生产业务,由于对业务影响不大,没有详细研究mysqldump指令的可选参数。总之,执行mysqldump命令,建议在应用访问量小的时候进行。

备份好的数据库拷贝到本地之后,通过mysql命令进行恢复。

mysql -uroot -p123 db_name < db_name.sql

2 找出慢查询的语句

这一步主要是对代码进行调试,找出查询慢的语句。像mybatis、hibernate都可以将sql语句打印出来,这个直接看执行情况即可。

3 查询语句在本地与服务器上分别执行进行对比

这一步主要是我在进行处理的过程中发现,在本地执行相同sql语句,执行时间相差10倍。本地只需要2-3秒,但是服务器上需要20秒左右。

在这一步我对本地和服务器磁盘进行了对比发现,本地数据存放在固态盘中,于是我将数据移动到机械硬盘中进行查询。结果没有太大变化。

4 分析慢查询的原因

4.1 分析sql语句

首先使用explain做常规的sql执行语句分析,结果如下:

EXPLAIN sql;
#sql为实际执行的慢查询语句

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-9OSUWIAL-1625212615944)(C:\Users\00\AppData\Roaming\Typora\typora-user-images\image-20210702105213120.png)]

可以看到,查询使用了两张表的关联查询,其中一张表没能走索引。理论上来说,此处就应该去优化sql语句了。但是因为在本地执行正常,同时,查询时通过hibernate进行实体查询,优化相对来说也比较困难,暂时没有去优化sql语句。

4.2 使用show profiles分析查询消耗情况

  • 执行查询语句

  • 执行show profiles

  • 根据第二部执行结果,查看执行语句的详细资源消耗和持续时间情况

 show profile cpu, block io for query 31;

可以看到,耗时的主要是Sending data这一步。

开始我以为,因为数据放在本地机械硬盘上对效率没有太大影响,又因为是Sending data耗时长,以为是网络传输的问题,于是到mysql服务器上执行相应语句,没有太大变化,基本排除网络传输的问题。

4.3 重心调整

其实通过4.1基本就已经清楚,执行慢的原因终究还是没有走索引。

于是进一步分析,将hibernate打印出来的sql语句进行分析,最终得到一个结论:

之前写这个查询的同事只考虑了把数据查询出来,没有考虑任何优化。通过分析sql语句发现,查询需要在60多万条数据中不走索引的情况下来按条件查询。

4.4 sql优化

于是我对sql进行了优化,先以索引字段作为条件进行过滤,得到小部分(1000条左右)数据,在这个基础上再进行条件过滤。简单来说就是讲索引字段的过滤条件放在最前面,让首先过滤的条件走索引查询,效率得到了大幅度提升。20秒降至1秒左右。

当然,sql语句会变得复杂一些,长一些,但是查询效率得到了提升。

  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值