Java开发 - 如何进行慢sql优化

ゝ一世哀愁。 2023-09-30 18:13 10阅读 0赞

**目录**

前言

什么是慢sql

关于sql优化的杂谈

如何进行慢sql的优化

第一步，查询哪些sql语句属于慢sql

第二步，我们来看看有哪些sql可以用来操作慢sql

查看慢sql日志是否打开

开启慢sql日志功能

查询慢sql日志设置的超时时间

修改慢sql设置的超时时间

查看日志文件

注意事项

如何对sql进行优化

数据库优化

服务器宕机的解决办法

索引

结语

--------------------

## 前言 ##

上一篇中，我们已经详细的了解了数据库的基本数据结构，虽有遗漏，不过也会在近几篇中通过相关的知识点慢慢补齐。

上一篇，我们最后讲了数据库的隔离级别，那么mysql默认隔离级别为可重复读，是否会产生幻读?在表格中，我们已经给出了答案，不妨再来看看这张表：

<table> 
 <tbody> 
  <tr> 
   <td>隔离等级</td> 
   <td>产生脏读</td> 
   <td>产生不可重复读</td> 
   <td>产生幻读</td> 
  </tr> 
  <tr> 
   <td>读未提交</td> 
   <td>true</td> 
   <td>true</td> 
   <td>true</td> 
  </tr> 
  <tr> 
   <td>读已提交</td> 
   <td>false</td> 
   <td>true</td> 
   <td>true</td> 
  </tr> 
  <tr> 
   <td>可重复读</td> 
   <td><span style="color:#fe2c24;">false</span></td> 
   <td>false</td> 
   <td>true</td> 
  </tr> 
  <tr> 
   <td>可串行化</td> 
   <td>false</td> 
   <td>false</td> 
   <td>false</td> 
  </tr> 
 </tbody> 
</table>

解决幻读的方式是MVCC，关于MVCC，在上一篇中，我们通过大量的篇幅和表格进行了说明，理解了也就不那么难了，很像我们开发中写个if...else判断的感觉。

下面，我们就来说说怎么进行慢sql优化。

## 什么是慢sql ##

通俗的讲，慢sql就是我们写的sql语句执行的时间太长，导致前台等待的时间太久，影响了用户体验。任何产品都是以服务他人为前提，体验差了自然是要去查找原因并作出优化的。

执行时间超过10s，默认视为慢sql，会将该sql记录到日志文件中。

## 关于sql优化的杂谈 ##

在我们的日常应用中，大多数接口在1s内算是合理的，个别接口3s内勉强能接受，当然，我们后期还会配合redis，es，session来做一些数据的缓存，以此来提高查询的效率。同时表的创建也是一个需要花时间推敲的东西，多表查询大家都知道，想象一下，若是五表连查，六表连查，是不是很要命，不说效率，光是要看懂都需要花些时间。所以关于连表，我们一般建议最多三表连查为最佳。另外还有主键的设置，虽然可以提高查询的效率，但也会因为增删改而影响效率，是一把双刃剑。

## 如何进行慢sql的优化 ##

### 第一步，查询哪些sql语句属于慢sql ###

有个东西叫慢sql日志功能，在mysql中，会有一个日志文件用于记录执行时间超过指定时间的sql，这个日志文件就叫sql日志。此功能在mysql中默认不开启，如果需要使用，需要手动开启。

### 第二步，我们来看看有哪些sql可以用来操作慢sql ###

#### 查看慢sql日志是否打开 ####

show variables like 'slow_query_log';

#### 开启慢sql日志功能 ####

//执行时间超过10s时，默认视为慢sql，会将该sql记录到日志文件中。
    set global slow_query_log=1

#### 查询慢sql日志设置的超时时间 ####

show global variables like 'long_query_time'

#### 修改慢sql设置的超时时间 ####

set global long_query_time=20

#### 查看日志文件 ####

show variables  like 'slow_query_log_file'

#### 注意事项 ####

*  对慢sql进行设置后，需要重启数据库服务器，注意，不是重启数据库，博主一般会在任务管理器里面对数据库进行重启操作，仅仅重启数据库可能不会重启数据库的服务器；
 *  慢sql的日志文件存储在数据库安装目录下的data文件夹下。

### 如何对sql进行优化 ###

*  查询sql中是否使用了\*，改为具体需要的字段；
 *  查询sql是否使用了嵌套，连查的效率要高于嵌套，如果可以的话，将sql改为连查；
 *  查询sql条件部分是否使用了索引，需要的话，可添加索引；
 *  查询sql条件部分是否使用了索引，索引是否失效。

关于索引失效的场景，我将在下一篇博客中详细说明索引的种类，数据结构。

以上只是主要的大方向，有些人还会对order by，like，group by，limit，or，in做优化，都是一个方向，全都做到了，那基本就做到极致了。

但有时候，我们不能单单因为sql是慢sql就去优化sql，这是不正确的，虽然慢sql需要优化，但并不是所有的慢sql都需要优化，我们要看慢sql产生的原因，像上面说到的一些情况，如果优化了，效率并没有提高太多，那该怎么办？这就是我接下来要说的东西：对数据库进行优化。

### 数据库优化 ###

数据库的优化有哪些？

我们一般会想到读写分离，读写分离了必然就要做主从复制，那集群也就产生了，如果用了微服务，那分库分表也势在必行，负载均衡也要跟上了，真是个大工程啊。

我们来做个整体的解释，假设我有主服务器master一台，从服务器salve三台，为什么要区分主从呢？因为功能不一样。我们的数据库访问分为读和写，读操作必定要远大于写操作。

假设我们做了主从，却只用master服务器进行读写，有两个问题：

第一，有没有可能写操作对主服务器中的表进行加锁？那是肯定的；

第二，其他三台从服务器作为小弟除了进行主从复制的数据同步外，就可以开开心心喝茶吗？那肯定不是，我master作为大哥也太累了，既要开店又要迎客，有三个小弟还不能使唤。

所以三台从服务器就要负责读的工作了，此时三台从服务器形成了一个集群，那么大并发条件下，负载均衡就要登场了，因为不能把工作全部给其中某个小弟做，所以我们要相对公平的分配给三个小弟，让他们都心理平衡。

关于负载均衡的策略，看选择什么组件，什么策略，比如轮询，权重等等，但今天它不在我们所要了解的知识体系内，我们以后再去说明。

### 服务器宕机的解决办法 ###

数据库优化说了集群，那么总也要防止数据库所在服务器宕机，我们通常使用哨兵模式解决这个问题。

哨兵系统中会有多个哨兵存在，因为要避免哨兵错判的情况出现。每个哨兵都会通过心跳机制和所有的服务器保持联系，定时发出pin命令，服务器接收到之后会做出响应。若果某个哨兵没有接收到其中一台服务器的响应，主观认为这台服务器宕机，但是为了客观的公平，哨兵会向其他哨兵发出询问，若是半数以上的哨兵接受不到这台服务器的响应，则认为这台服务器宕机，如果是master宕机，哨兵系统会在从服务器中选出一台作为新的master服务器，将原来的master服务器从集群中移除，并通知所有的salve，有点昭告天下的味道了。这时候，其他的从salve自然是要服从于心的君主，和新的master建立联系。

### 索引 ###

索引有很多好处，但也有缺点，前面优化sql时有提到过，本篇内容还是有点多的，大家看完消化下，索引将放在下一篇中重点说明，加个索引很简单，但里面却隐藏的你我不知道的大秘密，小不点儿也可以有大能耐。加的好，事半功倍；加的不好，作茧自缚。

**更详细的MySQL性能优化请移步：**[Java开发 - 不知道算不算详细的MySQL多维度性能优化剖析][Java_ - _MySQL]

## 结语 ##

到这里，你应该了解了慢sql的基础优化方式，实际工作中，要贴合真实的场景去做优化，切勿盲目优化。还要格外注意表的创建，好的表结构可以减少相当多的工作量。

[Java_ - _MySQL]: https://codingfire.blog.csdn.net/article/details/130635843?spm=1001.2014.3001.5502