令人头大的慢查询分析

本是古典何须时尚 2023-03-13 13:28 31阅读 0赞

### 文章目录 ###

*   *  令人头大的慢查询分析
     *   *  前言
         *  explain命名使用方法
         *  索引失效情况
         *  慢查询分析
         *  参考文章

## 令人头大的慢查询分析 ##

### 前言 ###

**建表语句**

create table student(
      `id`  BIGINT(20)  NOT NULL AUTO_INCREMENT,
      `name` VARCHAR(50) NOT NULL,
      `age`  INT(11)    NOT NULL,
      `height` INT(11) NOT NULL,
      PRIMARY KEY (`id`),
      KEY `name_index` (`name`),
      KEY `multi_index` (`age`,`height`)  
    );

### explain命名使用方法 ###

> explain命令，主要可以对`select`语句进行分析，并输出`select`执行的详细信息，以供开发人员针对性优化。`explian`的使用十分简单，在select语句前面加上`explain`关键字即可，如下图,其中`order_id`是主键

![format_png][]

**输出的各个属性含义**

<table> 
 <thead> 
  <tr> 
   <th>id</th> 
   <th>select 查询的标识符,是唯一的</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>select_type</td> 
   <td>select查询类型</td> 
  </tr> 
  <tr> 
   <td>table</td> 
   <td>查询指定的表</td> 
  </tr> 
  <tr> 
   <td>partitions</td> 
   <td>匹配的分区</td> 
  </tr> 
  <tr> 
   <td>type</td> 
   <td>访问类型，ALL指的是全表扫描</td> 
  </tr> 
  <tr> 
   <td>possible_keys</td> 
   <td>查询中可能使用的索引</td> 
  </tr> 
  <tr> 
   <td>key</td> 
   <td>查询中确切使用到的索引</td> 
  </tr> 
  <tr> 
   <td>ref</td> 
   <td>哪个字段或常数与 key 一起被使用</td> 
  </tr> 
  <tr> 
   <td>rows</td> 
   <td>显示此查询一共扫描的行数,是一个估计值.</td> 
  </tr> 
  <tr> 
   <td>filtered</td> 
   <td>查询条件所过滤的数据的百分比</td> 
  </tr> 
  <tr> 
   <td>extra</td> 
   <td>额外信息</td> 
  </tr> 
 </tbody> 
</table>

### 索引失效情况 ###

*  **最佳左前缀法则**
    
    如果索引是复合索引，要遵循最佳左前缀法则，查询从索引的最前列开始，并且不跳过索引中的列
    
    下面是违反最佳左前缀法则的情况，针对复合索引(age, height), 直接根据height查询，则复合索引失效
    
    ![format_png 1][]
 *  **在索引上做操作(计算、函数、类型转换)，均会导致索引失效**
    
    如下图，对主键id，进行 id+1操作，会使索引失效
    
    ![format_png 2][]
 *  **索引字段上使用(is null, not null)判断时，会使索引失效**
    
    ![format_png 3][]
 *  **索引字段使用like以通配符%开头时，会导致索引失效**
    
    ![format_png 4][]
 *  **索引字段中使用or**
    
    ![format_png 5][]

### 慢查询分析 ###

> msql通过指定long\_query\_time参数，设置慢查询的时间阈值，一旦查询语句的执行时间超过此参数，其就会被系统记录到慢查询日志中。通过对慢查询日志进行分析，可以对相应的慢查询语句或者数据库进行优化，从而提升数据库性能。下面详细讲述几种造成慢查询的原因和优化手段。

*  **上文提到的索引失效**
 *  **limit分页语句偏移量过大**
    
    limit语句的查询时间与偏移量成正比，所以一旦偏移量过大会造成查询速度巨慢
    
    **使用子查询优化**
    
    原查询语句
    
    `select * from student limit 200000, 20;`
    
    优化后查询语句(id是主键索引)
    
    `select * from student where id >=(select id from student limit 200000, 1) limit 20;`
 *  **数据库在刷脏页**
    
    数据库的每一次修改(插入、删除、更新)操作，都会记录在`redo log`日志，`redo log`日志一般不会直接刷新到磁盘中，而是保存在缓冲区(`os buffer`),等操作系统空闲的时候，刷新进磁盘。但是缓冲区容量是有限的，如果数据库更新过于频繁，导致缓冲区满了,就不会等系统空闲，而是直接暂停其他操作，将缓冲区刷新进磁盘中。所以此时执行查询语句就会特别慢。
 *  **锁冲突**
    
    数据库一般支持表锁，或者行锁。拿表锁举例，如果一个sql语句，获取表的写锁后，在执行更新操作，之后的查询(`select`)语句,需要等待表写锁释放，才能获取读锁。
    
    所以一旦查询语句执行涉及到的锁，被其他sql语句占有，无法获取锁，需要等待其释放锁，所以会造成查询慢。

### 参考文章 ###

[腾讯面试：一条SQL语句执行得很慢的原因有哪些？—不看后悔系列][SQL]

[format_png]: /images/20230312/729e70ab803448a3ac9d45ec0d37b2ed.png
[format_png 1]: /images/20230312/b2241e93e08a4be695d951df0de2887b.png
[format_png 2]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9naXRlZS5jb20venljeG5hbndhbmcvSW1hZ2UvcmF3L21hc3Rlci9vcHRpMy5QTkc?x-oss-process=image/format,png
[format_png 3]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9naXRlZS5jb20venljeG5hbndhbmcvSW1hZ2UvcmF3L21hc3Rlci9vcHRpNy5QTkc?x-oss-process=image/format,png
[format_png 4]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9naXRlZS5jb20venljeG5hbndhbmcvSW1hZ2UvcmF3L21hc3Rlci9vcHRpNS5QTkc?x-oss-process=image/format,png
[format_png 5]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9naXRlZS5jb20venljeG5hbndhbmcvSW1hZ2UvcmF3L21hc3Rlci9vcHRpNi5QTkc?x-oss-process=image/format,png
[SQL]: https://cloud.tencent.com/developer/article/1431932