【MySQL进阶-07】深入理解mysql性能优化以及解决慢查询问题

客官°小女子只卖身不卖艺 2024-03-27 11:41 62阅读 0赞

MySql系列整体栏目

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

<table> 
 <thead> 
  <tr> 
   <th align="left">内容</th> 
   <th>链接地址</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left"><font>【一】深入理解mysql索引本质</font></td> 
   <td><a href="https://blog.csdn.net/zhenghuishengq/article/details/121027025" rel="nofollow">https://blog.csdn.net/zhenghuishengq/article/details/121027025</a></td> 
  </tr> 
  <tr> 
   <td align="left"><font>【二】深入理解mysql索引优化以及explain关键字</font></td> 
   <td><a href="https://blog.csdn.net/zhenghuishengq/article/details/124552080" rel="nofollow">https://blog.csdn.net/zhenghuishengq/article/details/124552080</a></td> 
  </tr> 
  <tr> 
   <td align="left"><font>【三】深入理解mysql的索引分类，覆盖索引(失效)，回表，MRR</font></td> 
   <td><a href="https://blog.csdn.net/zhenghuishengq/article/details/128273593" rel="nofollow">https://blog.csdn.net/zhenghuishengq/article/details/128273593</a></td> 
  </tr> 
  <tr> 
   <td align="left"><font>【四】深入理解mysql事务本质</font></td> 
   <td><a href="https://blog.csdn.net/zhenghuishengq/article/details/127753772" rel="nofollow">https://blog.csdn.net/zhenghuishengq/article/details/127753772</a></td> 
  </tr> 
  <tr> 
   <td align="left"><font>【五】深入理解mvcc机制</font></td> 
   <td><a href="https://blog.csdn.net/zhenghuishengq/article/details/127889365" rel="nofollow">https://blog.csdn.net/zhenghuishengq/article/details/127889365</a></td> 
  </tr> 
  <tr> 
   <td align="left"><font>【六】深入理解mysql的内核查询成本计算</font></td> 
   <td><a href="https://blog.csdn.net/zhenghuishengq/article/details/128820477" rel="nofollow">https://blog.csdn.net/zhenghuishengq/article/details/128820477</a></td> 
  </tr> 
  <tr> 
   <td align="left"><font>【七】深入理解mysql性能优化以及解决慢查询问题</font></td> 
   <td><a href="https://blog.csdn.net/zhenghuishengq/article/details/128854433" rel="nofollow">https://blog.csdn.net/zhenghuishengq/article/details/128854433</a></td> 
  </tr> 
  <tr> 
   <td align="left"><font>【八】深入理解innodb和buffer pool底层结构和原理</font></td> 
   <td><a href="https://blog.csdn.net/zhenghuishengq/article/details/128993871" rel="nofollow">https://blog.csdn.net/zhenghuishengq/article/details/128993871</a></td> 
  </tr> 
  <tr> 
   <td align="left"><font>【九】深入理解mysql执行的底层机制</font></td> 
   <td><a href="https://blog.csdn.net/zhenghuishengq/article/details/128100377" rel="nofollow">https://blog.csdn.net/zhenghuishengq/article/details/128100377</a></td> 
  </tr> 
  <tr> 
   <td align="left"><font>【十】深入理解mysql集群的高可用机制</font></td> 
   <td><a href="https://blog.csdn.net/zhenghuishengq/article/details/126239652" rel="nofollow">https://blog.csdn.net/zhenghuishengq/article/details/126239652</a></td> 
  </tr> 
  <tr> 
   <td align="left"><font>【彩蛋篇】深入理解顺序io和随机io</font></td> 
   <td><a href="https://blog.csdn.net/zhenghuishengq/article/details/129080088" rel="nofollow">https://blog.csdn.net/zhenghuishengq/article/details/129080088</a></td> 
  </tr> 
 </tbody> 
</table>

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

#### 深入理解mysql的性能优化 ####

*  一，mysql的性能优化
 *   *  1，mysql调优的基本思路
     *  2，慢查询优化
     *   *  2.1，慢查询日志的基本使用
         *  2.2，对慢查询进行优化
         *   *  2.2.1，在业务层中-是否请求了不需要的数据
             *  2.2.2，在执行层中-是否存在扫描额外的记录
         *  2.3，慢查询的重构
         *   *  2.3.1，将一个复杂查询拆分成多个简单查询
             *  2.3.2，切分查询
             *  2.3.3，分解关联查询
     *  3，通过mysql的执行流程考虑性能优化
     *   *  3.1，查询速度慢的原因
         *  3.2，show profile使用(了解)
     *  4，mysql查询优化规则
     *   *  4.1，条件简化
         *  4.2，外连接消除
         *  4.3，子查询优化
         *   *  4.3.1，子查询优化规则

## 一，mysql的性能优化 ##

### 1，mysql调优的基本思路 ###

在mysql性能优化中，存在一个调优的金字塔，如下图所示。

![在这里插入图片描述][c8697683b06a464fb0b4a8068504660d.png]

主要有硬件和OS调优，MySql调优和架构调优这三种，并且越往上他的成本就越来越高，越往下他的效果就越来越好。如磁盘调优可以将机械硬盘换成SSD硬盘；架构调优可以进行分库分表，读写分离，并且可以在最初的系统设计时，可以根据业务来选择使用一些中间件，如redis，es，hbase等；MySql调优就是对一些sql语句，如添加索引，所有等进行调优。

### 2，慢查询优化 ###

#### 2.1，慢查询日志的基本使用 ####

慢查询日志，指的就是mysql记录所有执行超过 **long\_query\_time** 参数设定的时间阈值的SQL语句的日志。默认情况下，慢查询日志是关闭的，因此如果要使用上这个慢查询日志，那么就需要开启这个慢查询日志的功能。

show VARIABLES like 'slow_query_log';

通过上面这条命令来查询当前mysql的慢查询日志功能是否开启，如果没有开启，那么就通过下面这条命令来开启

//开启慢查询日志功能
    set GLOBAL slow_query_log = 1;

接下来可以查看这个慢查询日志的这个时间阈值，该值默认是10s，这个值也可以进行一个修改。当查询的语句的时间超过10s时，那么就会将该语句记录到日志中。

show VARIABLES like '%long_query_time%';

![在这里插入图片描述][8a218c5b39f14ce8911186a912bf474c.png]

对于这个慢查询产生的日志，一般是存储在这个table和file中，最好是存储在这个file文件中，因为放表的话会影响mysql的执行效率，并且这个日志文件一般是存储在这个data的目录下

show VARIABLES like 'log_output';

#### 2.2，对慢查询进行优化 ####

在开启这个慢查询日志之后，在超过这个10s的阈值时，这条sql就会添加到这个慢查询的日志里面，那么就可以通过这些日志文件查看哪些sql语句的执行效率比较低，然后再分析这些sql的效率低下的原因。在查看文件中的日志时，可以借助这个慢查询的辅助工具**mysqldumpslow**来对这些日志进行分析，并且可以通过这个辅助工具进行一个快速定位。

mysqldumpslow -s r -t 10 slow-mysql.log

在使用这个工具时，其伴有以下几个参数：

-s order (c,t,l,r,at,al,ar)
        c：总次数
        t：总时间
        l：锁的时间
        r：获取结果的行数
        at：平均数
        -s：排序
        -t：NUM，仅显示前n条数据
        -g：筛选数据

在分析这个慢sql时，主要通过以下的几个方法和思路来进行sql的优化：

##### 2.2.1，在业务层中-是否请求了不需要的数据 #####

1，**是否查询了不需要的记录**，如只需要获取最早创建的时间的数据，但是确实直接获取了全部数据，在内存中获取最小的时间，这样将其他查询出来的数据全部不要，这样就会让整个sql相对来说比较慢

2，**总是取出全部的列**，如果时只需要查询少量的字段，那么就杜绝这个select \* 的使用，并且这样也可以防止覆盖索引的失效问题。但是需要在数据缓存的时候，还是需要写select \* 的

3，**重复查询相同的数据**，如在一个for循环中，每循环一次都是使用一个sql查询，这样就可以将这个查询放在这个循环的外面。或者直接加入到缓存中，后面直接去缓存中获取数据

##### 2.2.2，在执行层中-是否存在扫描额外的记录 #####

1，**响应时间**，通过具体的响应时间来判断是否存在额外扫描，如是否出现大量数据的网络传输，加行锁的情况

2，**扫描的行数和返回的行数**，这个如典型的limit，会先将全部数据查出来，然后取10条，将其他的数据全部弃掉

3，**扫描的行数和返回的类型**，如通过这个explain来具体分析，通过这个type来确认这个索引的类型等，这个主要是通过这个索引，因为索引可以让 MySQL以最高效、扫描行数最少的方式找到需要的记录。

在SQL语句中常见的WHERE条件，一般有三种方式，其从好到坏依次如下：

1，在索引中直接使用where条件过滤掉不匹配的数据，直接在这个引擎层完成。

2，使用覆盖索引扫描返回记录，即在Extra字段出现这个**Using index**时，直接从索引中过滤掉不需要的记录并返回命中的结果，直接在server层将数据返回。

3，从数据表中返回数据，在Extra这个字段出现这个**Using Where**时，直接在mysql的server层完成，但是需要从表中将数据读取中，然后在对这些数据进行过滤。

#### 2.3，慢查询的重构 ####

在发现了慢查询时，那么就需要对这个慢查询进行一个重构，其重构的思路主要有如下几种

##### 2.3.1，将一个复杂查询拆分成多个简单查询 #####

如存在一写条件判定，如下这种，并且存在多个这种条件判断，如果全部挤在一个sql语句中，当数据量很大时，这些业务判断逻辑如果强行加在这个mysql身上，那么就会很大的程度上影响这个sql的查询效率。

if ... then ... else if ... then ... else

这样就可以去拆分这个复杂的查询，第一个if后面做一次查询，第二个做一次查询。如查询三个年龄段的用户，如果强行在一个sql中将数据全部查出来，那么效率肯定会很低，甚至可能因为范围查询出现这个联合索失效的问题，那么就可以将这个复杂的查询拆分成三个sql语句来查询了。当然这里最好时根据具体的业务需求来决定是复杂查询还是简单查询。

##### 2.3.2，切分查询 #####

在数据量大的情况下，如果想一次性将所有数据全部查出，然后在对查出的数据做一个修改，那么效率肯定会特别的慢，甚至可能会引发很多锁表，耗尽系统资源的问题。那么就需要利用这个切分的概念，将大数据拆分成小数据，每次查询只完成一部分数据，从而解决慢查询的问题。如开启一个定时任务，每一分钟完成1000条数据。

##### 2.3.3，分解关联查询 #####

在使用这个连接查询时，可以通过这个分解这个连接查询，来提高这个查询效率。如在拆分之后，可以直接走缓存来获取结果。分解之后的的代码也可以实现复用，更容易的做到高性能和高扩展。

### 3，通过mysql的执行流程考虑性能优化 ###

#### 3.1，查询速度慢的原因 ####

其基本流程就是**建立连接，语法分析器分析，优化器优化，执行器执行，最后将结果集返回给客户端**。

查询需要在不同的地方花费时间，包括网络，CPU计算，生成统计信息和执行计划、锁等待（互斥等待）等操作，尤其是向底层存储引擎检索数据的调用操作,这些调用需要在内存操作,CPU操作和内存不足时导致的IO操作上消耗时间。

如在查询出10w条数据的时候，要将结果集返回给客户端，那么其网络传输就需要部分时间；在加载数据驱动时，也需要一部分时间去加载这个驱动Driver，也需要部分时间；用户认证，语义分析，索引优化等，都需要时间。

除了这些情况之外，还要判断线程的数量，如果线程数太多，导致进程的上下文切换，也会对查询速度有一定的影响。

#### 3.2，show profile使用(了解) ####

在使用这个慢查询工具依旧不能分析出这个具体的结果的时候，那么就可以通过这个 show profile 来分析这个线程到底时间花在哪里。

show profiles;

然后找到具体的mysql的位置，如在1这个位置，然后直接定位到1这个位置

show profile for query 1; 或者
    show profile for all query 1;

其结果如下，其具体的值也可以从mysql的官方文档中有详细的描述。

![在这里插入图片描述][5449477cd6a44280bfc7ea1aaf05844b.png]

### 4，mysql查询优化规则 ###

#### 4.1，条件简化 ####

如存在一些 1=1时，mysql会自动将这些简化忽视；还有如一些 a = 5 and b > a这种条件，会简化成 a = 5 and b > 5等等。

#### 4.2，外连接消除 ####

由于内连接和外连接最大的区别就是这个sql补齐的问题，外连接会自动补null值，内连接只查出符合条件的sql。

select * from e1 inner join e2 on e1.name = e2.name
    select * from e1 left  join e2 on e1.name = e2.name

在使用这个内连接的时候，mysql内部会对这几张表做一个成本计算，会去**判断一下使用哪张表作为这个驱动表的这个成本低一些，然后再根据具体的成本比较再决定选择谁作为驱动表**。而使用这个外连接时，mysql默认认为from后面的表就是驱动表，join后面的表就是被驱动表，**其驱动表和被驱动表的位置是固定的**，那么这个mysql也不会对这个外连接做一个成本计算的优化。

因此在使用这个外连接时，可以考虑增加一个where条件，过滤掉这些被驱动表数据为null的值，因此可以将上面的left join连接查询的sql语句改写成如下：

select * from e1 left  join e2 on e1.name = e2.name where e2 is not null

这样被驱动表数据为空的数据就直接被过滤掉了，mysql就会默认的将这个值优化成内连接，这样mysql就可以根据内部的成本计算，来选择哪张表作为这个驱动表，哪张表作为这个驱动表了，这样就可以实现这个外连接消除。

#### 4.3，子查询优化 ####

子查询的基本语法如下

select (select name from teacher limit 1);
    select name,age from (select name,age + 1 from teacher) as t;
    select * from student where age in (select age-20 as age from teacher where age < 50);

在优化这个子查询的过程中，需要先判断这条sql语句的外部查询和子查询的两张表是否相关

//不相关子查询
    select * from student where age in (select age-20 as age from teacher);
    //相关子查询
    select * from student where age in (select age-20 as age from teacher where student.class_id = teacher.class_id);

不相关就是直接先查内部的子查询，后面将参数代入到整个外部查询中；相关就是先查外部的sql语句，每查出一条数据，就代入到子查询中进行匹配，看是否满足其内部的规则。

##### 4.3.1，子查询优化规则 #####

再来分析这个不相关的子查询，其sql如下:

select * from student where age in (select age-20 from teacher where class_id = 1);

这里需要考虑的问题就是，在这个子查询中，可能会查询出几千甚至几万条数据，这样作为参数给前面的sql语句，首先就是效率比较慢，其实很有可能会让这个服务器内存放不下；并且在使用这个in时，mysql很有可能无法使用到索引，从而走全表扫描。

因此mysql主要的内部的优化方式就是**将这个子查询作为一个临时表，然后进行一个联表查询来获取数据**。并且可以给这个临时表建索引，去重等操作，或者将这些数据加入到memory的内存中，从而提高查询效率。这种表被称为**物化表**

而在这个物化表的基础之上，就是在建立临时表的基础上，将这个子查询再优化成这个连接查询，并且优化成内连接的连接查询，这样就在之前的物化表的基础之上提升了效率，这种优化方式被称为**半连接**，即**semi-join**

再分析一个表相关的子查询，其sql如下：

select * from student where age in (select age-20 as age from teacher where student.class_id = teacher.class_id);

这张表上是有索引的，但是不一定会使用到索引，mysql内部就会通过改变这个in关键字，将这个in改写成exists这个关键字，从而让这个字段走索引。不管子查询是相关的还是不相关的，都尝试可以使用这个exists替换in，从而解决这个in索引失效的问题。当然直接在外部直接使用这个exists关键字，这样就可以省去mysql内部转换的时间，从而增加查询效率。

[c8697683b06a464fb0b4a8068504660d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/c725059712764784b66c45f75de94497.png
[8a218c5b39f14ce8911186a912bf474c.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/d574f123fac24bef94205ebce550f65f.png
[5449477cd6a44280bfc7ea1aaf05844b.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/dcb90ffeffca4486a77c5e552616a569.png