SQL慢查询优化

你的名字 2023-01-09 14:16 191阅读 0赞

### SQL数据库慢查询优化 ###

*  数据库优化
 *   *  目标
     *  指标
     *  流程
     *  数据库参数优化
     *   *  Oracle
         *  MySQL
 *  SQL优化
 *   *  定位慢查询SQL
     *  SQL语句优化
 *  总结

# 数据库优化 #

*  数据库优化要保证以下三点:
    
     *  **日志不能小**
     *  **缓存足够大**
     *  **连接足够用**
 *  数据库事务提交后需要将事务对数据的修改刷新到磁盘上,这样才能保证数据的持久性.这个刷新过程是一个随机写的过程,性能低.如果每次事务提交都需要对磁盘进行刷新,会极大影响数据库的性能
 *  可以在数据库架构设计中使用以下方式进行优化:
    
     *  先将事务写到日志文件**RedoLog,** 将随机写优化成顺序写
     *  添加一层缓存结构**Buffer,** 将每次写优化成批量写

## 目标 ##

*  根据不同的角色,数据库优化包含以下几个目标:
    
     *  **业务角度:** 减少客户端页面响应时间
     *  **数据库角度:** 减少数据库**SQL**响应时间
     *  **数据库服务器角度:**
        
         *  充分使用数据库服务器物理资源
         *  减少数据库服务器**CPU**的使用率
         *  减少数据库服务器**IO**使用率
         *  减少数据库服务器内存使用率

## 指标 ##

*  减少**SQL**的平均响应时间
 *  减少数据库服务器**CPU**的使用率
 *  减少数据库服务器的**IO**使用率

## 流程 ##

*  **了解优化问题:** 首先要尽可能了解优化的问题
 *  **收集系统信息:** 收集问题期间系统信息并做好存档
 *  **制定优化目标:** 根据当前系统问题表现制定优化目标并和客户沟通目标达成一致
 *  **分析性能问题:** 通过一系列工具分析系统问题
 *  制定优化方案
 *  实施优化方案

## 数据库参数优化 ##

*  数据库架构都包括以下几个方面,可以调整以下方面参数使得数据库达到最佳性能:
    
     *  数据缓存
     *  **SQL**解析区
     *  排序内存
     *  **REDO**以及**NUDO**
     *  锁 **, LATCH, MUTEX**
     *  监听及连接
     *  文件读写性能

### Oracle ###

<table> 
 <thead> 
  <tr> 
   <th>参数类别</th> 
   <th>参数名</th> 
   <th>参数值</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>数据缓存</td> 
   <td>SGA_TAGET<br>MEMORY_TARGET<br>DB_CACHE_SIZE</td> 
   <td>物理内存的70% - 80%</td> 
   <td>越大越好</td> 
  </tr> 
  <tr> 
   <td>SQL解析</td> 
   <td>SHARED_POOL_SIZE</td> 
   <td>4GB - 16GB</td> 
   <td>不宜设置过大6GB</td> 
  </tr> 
  <tr> 
   <td>监听连接</td> 
   <td>PROCESS<br>SESSIONS<br>OPEN_CURSORS</td> 
   <td>根据业务需求设置</td> 
   <td>一般为业务预估连接数的120%</td> 
  </tr> 
  <tr> 
   <td>其余参数</td> 
   <td>SESSION_CACHED_CURSORS</td> 
   <td>大于200</td> 
   <td>解析</td> 
  </tr> 
 </tbody> 
</table>

### MySQL ###

*  对于**MySQL**的**InnoDB**引擎有如下参数设置:

<table> 
 <thead> 
  <tr> 
   <th>参数类别</th> 
   <th>参数名</th> 
   <th>参数值</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>数据缓存</td> 
   <td>INNODB_BUFFER_POOL_SIZE</td> 
   <td>物理内存的50% - 80%</td> 
   <td>越大越好</td> 
  </tr> 
  <tr> 
   <td>日志记录</td> 
   <td>Innodb_log_buffer_size<br>sync_binlog</td> 
   <td>16MB - 32MB<br>1, 100, 0</td> 
   <td>根据运行状况调整<br>1时安全性最好</td> 
  </tr> 
  <tr> 
   <td>监听连接</td> 
   <td>max_connections</td> 
   <td>根据业务需求设置</td> 
   <td>可以预留一部分值</td> 
  </tr> 
  <tr> 
   <td>文件读写性能</td> 
   <td>innodb_flush_log_at_trx_commit</td> 
   <td>2</td> 
   <td>安全和性能的折中考虑</td> 
  </tr> 
  <tr> 
   <td>其余参数</td> 
   <td>wait_timeout<br>interactive_timeout</td> 
   <td>28800</td> 
   <td>避免应用连接中断</td> 
  </tr> 
 </tbody> 
</table>

# SQL优化 #

## 定位慢查询SQL ##

*  定位慢查询**SQL**可以通过两个表象进行判断
    
     *  **系统级表象:**
        
         *  **CPU**消耗严重
         *  **IO**等待严重
         *  页面响应时间过长
         *  项目日志出现超时等错误
            
             *  使用**sar**命令和**top**命令查看当前系统的状态
             *  也可以使用**Prometheus**和**Grafana**监控工具查看当前系统状态
     *  **SQL语句表象:**
        
         *  **SQL**语句冗长
         *  **SQL**语句执行时间过长
         *  **SQL**从全表扫描中获取数据
         *  执行计划中的**rows**和**cost**很大
 *  根据不同的数据库使用不同的方式获取问题**SQL**
    
     *  **MySQL:**
        
         *  慢查询日志
         *  测试工具**loadrunner**
         *  **ptquery**工具
     *  **Oracle:**
        
         *  **AWR**报告
         *  测试工具**loadrunner**
         *  相关内部视图**v$, $session\_wait**
         *  **GRID CONTROL**监控工具

## SQL语句优化 ##

*  **合理使用索引:**
    
     *  索引少了查询慢. 索引多了占用空间大,执行增删改语句需要动态维护索引,影响性能
     *  选择率高,重复值少并且**where**中频繁引用的字段需要建立**B**树索引
 *  **使用UNION ALL替代UNION**
 *  **禁止使用SELECT \* 的写法**
 *  **对join的字段建立索引**
 *  **避免复杂的SQL语句,可以转换成多个子查询,在业务层处理**
 *  **使用explain sql查看SQL的执行计划:**

<table> 
 <thead> 
  <tr> 
   <th>字段</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>id</td> 
   <td>每一个被执行的操作标识,标识对象被操作的顺序<br>ID的值较大的先被执行.如果相同,执行顺序从上向下</td> 
  </tr> 
  <tr> 
   <td>select_type</td> 
   <td>查询中每个select语句的类型</td> 
  </tr> 
  <tr> 
   <td>table</td> 
   <td>被操作的对象的名称,通常是表名.也会包含其余格式</td> 
  </tr> 
  <tr> 
   <td>partitions</td> 
   <td>匹配的分区的信息,对于非分区的表值为NULL</td> 
  </tr> 
  <tr> 
   <td>type</td> 
   <td>连接操作的类型</td> 
  </tr> 
  <tr> 
   <td>possible_keys</td> 
   <td>可能用到的索引</td> 
  </tr> 
  <tr> 
   <td>key</td> 
   <td>优化器实际使用的索引<br>最好到最差的索引依次为const, eq_reg, ref, range, index, ALL<br>对于ALL类型的索引一定要进行优化</td> 
  </tr> 
  <tr> 
   <td>key_len</td> 
   <td>优化器选定的索引键长度,以字节为单位</td> 
  </tr> 
  <tr> 
   <td>ref</td> 
   <td>表示本行对象被操作对象的参照对象, 对于无参照对象时为NULL</td> 
  </tr> 
  <tr> 
   <td>rows</td> 
   <td>执行查询所扫描的元组个数</td> 
  </tr> 
  <tr> 
   <td>filtered</td> 
   <td>条件表上数据被过滤的元组个数百分比</td> 
  </tr> 
  <tr> 
   <td>extra</td> 
   <td>计划执行的重要补充信息<br>当出现Using filesort, Using temporary时要对SQL语句进行优化</td> 
  </tr> 
 </tbody> 
</table>

# 总结 #

*  使用**explain**查看**SQL**语句的执行计划
 *  如果有告警信息,查看告警信息的**show warnings**
 *  查看**SQL**语句涉及的表结构和索引信息
 *  根据执行计划对**SQL**语句需要优化的地方进行优化
 *  根据需要优化的情况执行表结构的修改,索引的添加 **,SQL**语句的改写等操作
 *  再次使用**explain**查看优化后的执行时间和执行计划
 *  根据优化效果选择继续优化,还是优化成功