MySQL进阶：6.定位SQL性能问题

╰半夏微凉° 2023-02-23 12:30 17阅读 0赞

【专栏目录】  
[MySQL进阶：1.MySQL基础环境搭建][MySQL_1.MySQL]  
[MySQL进阶：2.索引][MySQL_2.]  
[MySQL进阶：3.视图][MySQL_3.]  
[MySQL进阶：4.为什么不推荐使用存储过程和触发器][MySQL_4.]  
[MySQL进阶：5.存储引擎][MySQL_5.]  
[MySQL进阶：6.定位SQL性能问题][MySQL_6._SQL]  
[MySQL进阶：7.SQL优化][MySQL_7.SQL]

# 1.SQL性能问题 #

`在应用开发过程中，初期数据量往往比较小，SQL语句的性能体现并不明显。举个例子：假设数据量为100时，查询性能是0.01s，数据量增长到1000时，查询性能是0.05s。0.01和0.05这种差值从直观感受上来说体现不出来太大变化，还是会觉得挺快的。随着数据量的增加，SQL语句的性能问题就会逐渐显露出来，比如当数据量为10w时，查询性能为5s，这就能明显感觉到性能非常低。如果开发过程中遇到这样的问题，即SQL性能问题，就需要我们进行SQL优化。`

# 2.SQL优化步骤 #

1.  定位SQL性能瓶颈位置
2.  分析SQL
3.  优化SQL

## 2.1如何定位 ##

### 2.1.1查看SQL执行频率 ###

`下面语句可以查看所有连接MySQL的客户端对该数据库的操作分类以及频率，用于直观判断当前数据库被哪种操作执行的频率比较高，比如高频查询，高频插入、高频提交等等。`

mysql> show global status;

### 2.1.2定位低效SQL ###

一般通过下面两种方式定位：

1.  慢查询日志：可以通过开启慢查询日志，设置一个long\_query\_time，该日志会记录执行时间超过long\_query\_time的SQL详细信息；
2.  show processlist：由于慢查询日志只能在SQL执行完之后才记录，所以当应用正在执行时的定位低性能问题就不能用慢查询日志的方式了，可以使用show processlist查看当前MySQL正在运行的线程，包括线程状态、是否锁表等，可以实时查看SQL的执行情况，同时对一些锁表操作进行优化；

<table> 
 <thead> 
  <tr> 
   <th>列项</th> 
   <th>含义</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>Id</td> 
   <td>代表当前连接的id</td> 
  </tr> 
  <tr> 
   <td>User</td> 
   <td>代表使用哪个用户建立的连接</td> 
  </tr> 
  <tr> 
   <td>Host</td> 
   <td>代表连接当前数据库的主机</td> 
  </tr> 
  <tr> 
   <td>db</td> 
   <td>代表当前连接正在使用哪个数据库</td> 
  </tr> 
  <tr> 
   <td>Command</td> 
   <td>代表当前连接的状态（正在做什么操作），一般取值为sleep、query、connect</td> 
  </tr> 
  <tr> 
   <td>Time</td> 
   <td>代表当前连接的状态持续的时间</td> 
  </tr> 
  <tr> 
   <td>State</td> 
   <td>代表当前连接的SQL语句的状态</td> 
  </tr> 
  <tr> 
   <td>Info</td> 
   <td>代表当前正在执行的SQL语句</td> 
  </tr> 
 </tbody> 
</table>

## 2.2定位之后进行分析 ##

### 2.2.1explain分析 ###

`使用explain分析指导SQL语句`

<table> 
 <thead> 
  <tr> 
   <th>列项</th> 
   <th>含义</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>id</td> 
   <td>id相同时（单表查询或多表查询），加载表的顺序是从上到下；id不同时（嵌套子查询），加载表的顺序是数字越大优先级越高；id有相同也有不同时（多表查询&amp;&amp;嵌套子查询）,id相同的为一组，同一组的加载顺序是从上到下，不同组的加载顺序是数字越大优先级越高</td> 
  </tr> 
  <tr> 
   <td>select_type</td> 
   <td>代表查询的类型，一般有以下几种，查询效率从上到下依次递减：1.SIMPLE：简单的查询，不包含子查询和UNION；2.PRIMARY：出现在有子查询的情况，和SUBQUERY同时出现，PRIMARY代表最外层查询，即id数字最低的SQL；3.SUBQUERY：出现在有子查询的情况，和PRIMARY同时出现，SUBQUERY代表子查询，id数字越大优先级越高；4.DERIVED：在FROM列表中包含的子查询；UNION：两个Select取交集，若第二个Select出现在UNION之后，该Select就会被标记为UNION；5.从UNION表取得结果后的Select；</td> 
  </tr> 
  <tr> 
   <td>table</td> 
   <td>代表当前行是关于哪个表的</td> 
  </tr> 
  <tr> 
   <td>type</td> 
   <td>代表访问的数据的方式，一般有以下几种：1.NULL：代表不访问任何表、索引，直接返回结果；2.system：代表当前访问的表只有一行记录（等于系统表），这是const类型的特例，一般不会出现；3.const：表示通过索引一次就找到了；eq_ref：类似ref，区别在于使用的是唯一索引，使用主键的关联查询，关联查询出的记录只有一条。常见于主键或唯一索引扫描；ref：非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，返回所有匹配某个单独值的所有行（多个）；range：只检索给定返回的行，使用一个索引来选择行。比如where之后出现between、&lt;、&gt;、in等操作；index：index和ALL的区别为idenx只遍历索引树所有节点，ALL遍历数据文件，所以index比ALL快，但它的效率也不高；ALL：遍历全表以找到匹配的行</td> 
  </tr> 
  <tr> 
   <td>possible_keys</td> 
   <td>代表可能用到的索引</td> 
  </tr> 
  <tr> 
   <td>key</td> 
   <td>代表实际用到的索引</td> 
  </tr> 
  <tr> 
   <td>key_len</td> 
   <td>代表索引长度</td> 
  </tr> 
  <tr> 
   <td>rows</td> 
   <td>代表扫描的行数</td> 
  </tr> 
  <tr> 
   <td>Extra</td> 
   <td>一些额外信息，一般常见下面几种：1.using filesort：代表当前使用到了文件排序而不是内部索引排序，效率低；2.using temporary：代表使用率临时表保存中间结果，常见于order by和group by，效率低；3.代表当前使用了索引，效率高；一般出现1，2的时候就需要考虑优化了。</td> 
  </tr> 
 </tbody> 
</table>

### 2.2.2show profile分析 ###

`show profile可以分析SQL执行的总耗时，以及各个阶段的耗时情况，可以帮助定位低性能的阶段`

*  1.查看当前MySQL是否支持profile

mysql> select @@have_profiling;
    +------------------+
    | @@have_profiling |
    +------------------+
    | YES              |
    +------------------+
    # YES则表示支持

*  2.查看profiling是否开启（默认是关闭的），如果关闭则开启

mysql> select @@profiling;
    +-------------+
    | @@profiling |
    +-------------+
    |           0 |
    +-------------+
    # 0则表示关闭
    
    mysql> set profiling=1;
    Query OK, 0 rows affected, 1 warning (0.00 sec)
    # 开启成功

*  3.接下来进行几个SQL操作，用于后面使用show profiles分析

# SSO是我的数据库，请替换成你的
    mysql> use SSO;
    mysql> select * from UserEO;
    mysql> select count(*) from UserEO;
    mysql> show profiles;
    +----------+------------+-----------------------------+
    | Query_ID | Duration   | Query                       |
    +----------+------------+-----------------------------+
    |        1 | 0.00016450 | SELECT DATABASE()           |
    |        2 | 0.00022850 | show databases              |
    |        3 | 0.00011550 | show tables                 |
    |        4 | 0.00030575 | select * from UserEO        |
    |        5 | 0.00026600 | select count(*) from UserEO |
    |        6 | 0.00006325 | show profile；              |
    +----------+------------+-----------------------------+
    # 可以看到刚才所有SQL的耗时时间

*  4.进一步分析，对一个SQL的每个阶段进行分析，使用show profile for query \[Query\_ID\]

# 这里分析Query_ID为5的SQL，即select count(*) from UserEO的各个阶段
    mysql> show profile for query 5;
    +----------------------+----------+
    | Status               | Duration |
    +----------------------+----------+
    | starting             | 0.000074 |
    | checking permissions | 0.000007 |
    | Opening tables       | 0.000018 |
    | init                 | 0.000021 |
    | System lock          | 0.000008 |
    | optimizing           | 0.000014 |
    | statistics           | 0.000028 |
    | preparing            | 0.000013 |
    | executing            | 0.000003 |
    | Sending data         | 0.000042 |
    | end                  | 0.000004 |
    | query end            | 0.000008 |
    | closing tables       | 0.000007 |
    | freeing items        | 0.000010 |
    | cleaning up          | 0.000011 |
    +----------------------+----------+
    # 可以看到每个阶段对应的耗时时间
    # ☆☆☆这里需要重点提一下Sending data这个阶段，Sending data不仅仅是指将数据返回给客户端，而是指从MySQL线程开始访问数据到返回给客户端的阶段。
    # 由于该阶段涉及到磁盘读取操作，所以经常是整个查询中耗时最长的阶段。

[MySQL_1.MySQL]: https://blog.csdn.net/w_y_x_y/article/details/107048525
[MySQL_2.]: https://blog.csdn.net/w_y_x_y/article/details/107048532
[MySQL_3.]: https://blog.csdn.net/w_y_x_y/article/details/107188936
[MySQL_4.]: https://blog.csdn.net/w_y_x_y/article/details/107188957
[MySQL_5.]: https://blog.csdn.net/w_y_x_y/article/details/107188967
[MySQL_6._SQL]: https://blog.csdn.net/w_y_x_y/article/details/107188979
[MySQL_7.SQL]: https://blog.csdn.net/w_y_x_y/article/details/107214956