MySQL编程优化篇（一） SQL优化（ⅰ）优化SQL的一般步骤

雨点打透心脏的1/2处 2023-06-23 10:55 60阅读 0赞

**目录**

优化SQL的一般步骤

通过show status命令了解各种SQL的执行频率

定位执行效率较低的SQL语句

show profile的使用

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

# 优化SQL的一般步骤 #

## 通过show status命令了解各种SQL的执行频率 ##

mysql> show status like 'Com_%';

通常比较关心以下几个统计参数：

<table> 
 <thead> 
  <tr> 
   <th>参数</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>Com_ select</td> 
   <td>执行SELECT操作的次数, 一次查询只累加1</td> 
  </tr> 
  <tr> 
   <td>Com_ insert</td> 
   <td>执行INSERT操作的次数，对于批量插入的INSERT操作，只累加一次</td> 
  </tr> 
  <tr> 
   <td>Com_ update</td> 
   <td>执行UPDATE操作的次数</td> 
  </tr> 
  <tr> 
   <td>Com_ delete</td> 
   <td>执行DELETE操作的次数</td> 
  </tr> 
  <tr> 
   <td>Com_commit/Com_rollback</td> 
   <td>事务提交和回滚的情况</td> 
  </tr> 
  <tr> 
   <td>......</td> 
   <td>.......</td> 
  </tr> 
 </tbody> 
</table>

## 定位执行效率较低的SQL语句 ##

1.  通过慢查询日志
    
    在查询结束以后才记录，所以在应用反映执行效率出现问题的时候查询慢查询日志并不能定位问题
2.  通过`show processlist`
    
    查看当前MySQL在进行的线程，包括线程的状态、是否锁表等，可以实时地查看 SQL 的执行情况，同时对一些锁表操作进行优化。

## show profile的使用 ##

-- 查看当前MySQL是否支持profile
    mysql> select @@have_profiling;
    +------------------+
    | @@have_profiling |
    +------------------+
    | YES              |
    +------------------+
    1 row in set, 1 warning (0.00 sec)
    
    -- 默认是关闭的，可以通过set语句在session级别开启
    mysql> select @@profiling;
    +-------------+
    | @@profiling |
    +-------------+
    |           0 |
    +-------------+
    1 row in set, 1 warning (0.00 sec)
    
    -- 开启profile
    mysql> set profiling = 1;
    Query OK, 0 rows affected, 1 warning (0.00 sec)
    
    -- 再次查询，已经开启
    mysql> select @@profiling;
    +-------------+
    | @@profiling |
    +-------------+
    |           1 |
    +-------------+
    1 row in set, 1 warning (0.00 sec)

执行show profiles，查看执行sql的query id

mysql> show profiles;
    +----------+------------+--------------------+
    | Query_ID | Duration   | Query              |
    +----------+------------+--------------------+
    |        1 | 0.00021675 | select @@profiling |
    |        2 | 0.00007650 | show tables        |
    |        3 | 0.02193350 | show databases     |
    +----------+------------+--------------------+
    3 rows in set, 1 warning (0.00 sec)

通过 show profile for query 语句能够看到执行过程中线程的每个状态和消耗的时间

mysql> show profile for query 3;
    +----------------------+----------+
    | Status               | Duration |
    +----------------------+----------+
    | starting             | 0.000069 |
    | checking permissions | 0.000015 |
    | Opening tables       | 0.000056 |
    | init                 | 0.000015 |
    | System lock          | 0.000008 |
    | optimizing           | 0.000008 |
    | statistics           | 0.000016 |
    | preparing            | 0.000016 |
    | executing            | 0.021611 |
    | Sending data         | 0.000036 |
    | end                  | 0.000008 |
    | query end            | 0.000006 |
    | closing tables       | 0.000005 |
    | removing tmp table   | 0.000009 |
    | closing tables       | 0.000009 |
    | freeing items        | 0.000025 |
    | cleaning up          | 0.000021 |
    +----------------------+----------+
    17 rows in set, 1 warning (0.00 sec)

**注意：**

> Sending data状态表示MySQL线程**开始访问数据行并把结果返回给客户端**，而不仅仅是返回结果给客户端。由于在Sending data状态下，MySQL线程往往需要做大量的磁盘读取操作，所以经常是整个查询中耗时最长的状态。

另外，还可以通过 **tarce** 进一步了解优化器选择A执行计划而不选择B执行计划，帮助我们更好地理解优化器的行为。