在生产环境中有一个功能，加载数据超过了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语句会变得复杂一些，长一些，但是查询效率得到了提升。