三十九、MySQL基础系列笔记优化篇之SQL优化步骤

作者: 温新

分类: 【MySql】

阅读: 1898

时间: 2021-04-06 15:35:39

随着数据量的不断增大，很多SQL语句逐渐暴露出性能问题，此时这些有问题的SQL语句将会成为整个系统的性能瓶颈，因此，对SQL的优化非常重要。面对系统突然变慢（sql层面），那么如何下手查找出问题所在？下文提供了SQL优化的几个步骤。

第一步：查看SQL执行频率

命令：show [session|global] status查看服务器状态信息。

<span style="box-sizing: border-box;padding-right: 0.1px">mysql> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">show</span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">status</span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">like</span> <span style="box-sizing: border-box;color: rgb(195, 232, 141)">'Com_%'</span>;</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">+-------------------------------------+-------+</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">| Variable_name                       | <span style="box-sizing: border-box;color: rgb(199, 146, 234)">Value</span> |</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">+-------------------------------------+-------+</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">| Com_admin_commands                  | <span style="box-sizing: border-box;color: rgb(255, 83, 112)">0</span>     |</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">.......</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">| Com_alter_resource_group            | <span style="box-sizing: border-box;color: rgb(255, 83, 112)">0</span>     |</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">| Com_alter_server                    | <span style="box-sizing: border-box;color: rgb(255, 83, 112)">0</span>     |</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">| Com_alter_table                     | <span style="box-sizing: border-box;color: rgb(255, 83, 112)">24</span>    |</span>

Com_xxx表示每个xxx语句执行的次数，通过关心这样几个统计参数：

  • Com_select 执行select查询操作的次数，一次查询只累加1；
  • Com_insert 执行insert插入操作的次数，对于批量插入，只累加一次；
  • Com_update 执行update更新操作的次数；
  • Com_delete 执行delete删除操作的次数。

这4个操作对于所有存储引擎有效，下面的参数只对InnoDB存储引擎有效：

  • InnoDB_rows_read select查询结果返回的行数；
  • InnoDB_rows_inserted 执行insert操作插入的行数；
  • InnoDB_rows_updated 执行update操作更新的行数；
  • InnoDB_rows_deleted 执行delete操作的次数。

通过上面的参数可以了解到当前数据库的应用是以插入更新为主还是以查询操作为主，以及各种类型的SQL大致的执行比例是多少。

对于事务型的应用，通过Com_commit和Com_rollback可以了解到事务提交和回滚的情况，对于回滚操作非常频繁的数据库，可能意味着应用编写存在问题。

查看服务器工作时间

show status like 'update';

查看服务器连接次数

show status like 'connections';

查看慢查询次数

show status like 'slow_queries';

第二步：定位需要优化的SQL语句

如何找出执行效率低的SQL？可以通过如下两种方式来定位查询效率低下的SQL：

  • 通过慢查询日志定位查询效率低的SQL语句；
  • 通过show processlist实时查看SQL执行的情况。

通过慢查询日志定位查询效率低的SQL语句

要使用慢查询分析SQL，就必须先开启慢查询。慢查询默认是关闭的。

下面的步骤可拆分出新的文章

步骤一：开启慢查询

set session slow_query_log = 1;

步骤二：设置慢查询阈值（单位s）

set session long_query_time = 1;

步骤三：查看慢查询日志的路径

show variables like 'datadir';

步骤四：查看慢查询日志文件名

show session variables like 'slow_query_log_file';

第三步：EXPLAIN分析SQL执行效率为什么低

对SQL的分析是优化SQL语句的重要手段，通过上面的介绍，已经知道有两种方法定位慢SQL，定位出慢SQL之后，使用EXPLAIN分析是重要的一步，它记录了查询的各种信息，如SQL是简单查询还是复杂查询，是否使用索引，是否全表扫描等重要分析数据。

案例：使用explain分析语句

<span style="box-sizing: border-box;padding-right: 0.1px">mysql> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">explain</span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">select</span> * <span style="box-sizing: border-box;color: rgb(199, 146, 234)">from</span> customer;</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">| id | select_type | <span style="box-sizing: border-box;color: rgb(199, 146, 234)">table</span>    | <span style="box-sizing: border-box;color: rgb(199, 146, 234)">partitions</span> | type | possible_keys | <span style="box-sizing: border-box;color: rgb(199, 146, 234)">key</span>  | key_len | ref  | rows | filtered | Extra |</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">|  <span style="box-sizing: border-box;color: rgb(255, 83, 112)">1</span> | SIMPLE      | customer | <span style="box-sizing: border-box;color: rgb(247, 140, 108)">NULL</span>       | <span style="box-sizing: border-box;color: rgb(199, 146, 234)">ALL</span>  | <span style="box-sizing: border-box;color: rgb(247, 140, 108)">NULL</span>          | <span style="box-sizing: border-box;color: rgb(247, 140, 108)">NULL</span> | <span style="box-sizing: border-box;color: rgb(247, 140, 108)">NULL</span>    | <span style="box-sizing: border-box;color: rgb(247, 140, 108)">NULL</span> |  <span style="box-sizing: border-box;color: rgb(255, 83, 112)">599</span> |   <span style="box-sizing: border-box;color: rgb(255, 83, 112)">100.00</span> | <span style="box-sizing: border-box;color: rgb(247, 140, 108)">NULL</span>  |</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+</span>

结果参数解释

id为SQL执行成功的标识，ID表示从小到大的执行。ID相同，执行顺序从上到下；ID不同，若是子查询，ID递增，值越大越先执行；ID相同不同，同时存在。

这些参数中，select_type、type、Extra以及key是非常重要的。这些参数中select_type、type、Extra又可以细分一些参数，如下：

select_type参数

type参数

Extra参数

第四步：通过show profile分析SQL

mysql提供了show profile来分析当前会话中sql语句执行时所消耗资源情况的工具，用于sql优化分析。

步骤一：开启show profile

<span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(103, 110, 149)"># 1、查看当前mysql是否支持show profile</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(103, 110, 149)"># 结果：yes支持，no不支持</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(199, 146, 234)">select</span> <span style="box-sizing: border-box;color: rgb(238, 255, 255)">@@have_profiling</span>;</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box"></span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(103, 110, 149)"># 2、查看是否开启</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(103, 110, 149)"># 方式一</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(199, 146, 234)">show</span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">variables</span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">like</span> <span style="box-sizing: border-box;color: rgb(195, 232, 141)">'profiling'</span>;</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(103, 110, 149)"># 方式二</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(199, 146, 234)">select</span> <span style="box-sizing: border-box;color: rgb(238, 255, 255)">@@have_profiling</span>;</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box"></span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(103, 110, 149)"># 3、若没有开启则开启</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(199, 146, 234)">set</span> profiling=<span style="box-sizing: border-box;color: rgb(255, 83, 112)">1</span>;</span>

步骤二：通过show profile分析

Ps：导入mysql提供的测试数据库（sakila-db）

<span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(103, 110, 149)"># 1、查询语句</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(199, 146, 234)">select</span> * <span style="box-sizing: border-box;color: rgb(199, 146, 234)">from</span> payment;</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(103, 110, 149)"># 2、通过show profiles查看结果</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(199, 146, 234)">show</span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">profiles</span>;</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(103, 110, 149)"># 查看结果如下 </span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">Query_ID Duration <span style="box-sizing: border-box;color: rgb(199, 146, 234)">Query</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(255, 83, 112)">1</span> <span style="box-sizing: border-box;color: rgb(255, 83, 112)">0.0006545</span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">SHOW</span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">STATUS</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(255, 83, 112)">2</span> <span style="box-sizing: border-box;color: rgb(255, 83, 112)">0.00041425</span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">SHOW</span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">STATUS</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(255, 83, 112)">3</span> <span style="box-sizing: border-box;color: rgb(255, 83, 112)">0.01084225</span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">select</span> * <span style="box-sizing: border-box;color: rgb(199, 146, 234)">from</span> payment</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(255, 83, 112)">4</span> <span style="box-sizing: border-box;color: rgb(255, 83, 112)">0.0007145</span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">SHOW</span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">STATUS</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(255, 83, 112)">5</span> <span style="box-sizing: border-box;color: rgb(255, 83, 112)">0.0002845</span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">SELECT</span> QUERY_ID, <span style="box-sizing: border-box;color: rgb(199, 146, 234)">SUM</span>(DURATION) <span style="box-sizing: border-box;color: rgb(199, 146, 234)">AS</span> SUM_DURATION <span style="box-sizing: border-box;color: rgb(199, 146, 234)">FROM</span> INFORMATION_SCHEMA<span style="box-sizing: border-box;color: rgb(238, 255, 255)">.PROFILING</span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">GROUP</span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">BY</span> QUERY_ID</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(255, 83, 112)">6</span> <span style="box-sizing: border-box;color: rgb(255, 83, 112)">0.000296</span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">SELECT</span> STATE <span style="box-sizing: border-box;color: rgb(199, 146, 234)">AS</span> <span style="box-sizing: border-box;color: rgb(238, 255, 255)">`Status`</span>, ROUND(<span style="box-sizing: border-box;color: rgb(199, 146, 234)">SUM</span>(DURATION),<span style="box-sizing: border-box;color: rgb(255, 83, 112)">7</span>) <span style="box-sizing: border-box;color: rgb(199, 146, 234)">AS</span> <span style="box-sizing: border-box;color: rgb(238, 255, 255)">`Duration`</span>, CONCAT(ROUND(<span style="box-sizing: border-box;color: rgb(199, 146, 234)">SUM</span>(DURATION)/0<span style="box-sizing: border-box;color: rgb(255, 83, 112)">.000416</span>*<span style="box-sizing: border-box;color: rgb(255, 83, 112)">100</span>,<span style="box-sizing: border-box;color: rgb(255, 83, 112)">3</span>), <span style="box-sizing: border-box;color: rgb(195, 232, 141)">''</span>) <span style="box-sizing: border-box;color: rgb(199, 146, 234)">AS</span> <span style="box-sizing: border-box;color: rgb(238, 255, 255)">`Percentage`</span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">FROM</span> INFORMATION_SCHEMA<span style="box-sizing: border-box;color: rgb(238, 255, 255)">.PROFILING</span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">WHERE</span> QUERY_ID=<span style="box-sizing: border-box;color: rgb(255, 83, 112)">2</span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">GROUP</span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">BY</span> SEQ, STATE <span style="box-sizing: border-box;color: rgb(199, 146, 234)">ORDER</span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">BY</span> SEQ</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(255, 83, 112)">7</span> <span style="box-sizing: border-box;color: rgb(255, 83, 112)">0.00009175</span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">SET</span> PROFILING = <span style="box-sizing: border-box;color: rgb(255, 83, 112)">1</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(255, 83, 112)">8</span> <span style="box-sizing: border-box;color: rgb(255, 83, 112)">0.0005115</span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">SHOW</span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">STATUS</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(255, 83, 112)">9</span> <span style="box-sizing: border-box;color: rgb(255, 83, 112)">0.00040425</span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">SHOW</span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">STATUS</span></span>

通过show profiles可以看到查询语句（Query）的Query_ID为3，所花费时间（Duration）为0.01084225

步骤三：show profile诊断语句

语法：show profile for query Query_ID

<span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(103, 110, 149)"># 根据Query_ID分析sql语句</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(199, 146, 234)">show</span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">profile</span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">for</span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">query</span> <span style="box-sizing: border-box;color: rgb(255, 83, 112)">3</span>;</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box"></span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(103, 110, 149)"># 分析结果</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(199, 146, 234)">Status</span>  Duration</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(199, 146, 234)">starting</span> <span style="box-sizing: border-box;color: rgb(255, 83, 112)">0.000056</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">checking permissions <span style="box-sizing: border-box;color: rgb(255, 83, 112)">0.000003</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">Opening <span style="box-sizing: border-box;color: rgb(199, 146, 234)">tables</span> <span style="box-sizing: border-box;color: rgb(255, 83, 112)">0.000224</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">init <span style="box-sizing: border-box;color: rgb(255, 83, 112)">0.000004</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(240, 113, 120)">System</span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">lock</span> <span style="box-sizing: border-box;color: rgb(255, 83, 112)">0.000006</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">optimizing <span style="box-sizing: border-box;color: rgb(255, 83, 112)">0.000002</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">statistics <span style="box-sizing: border-box;color: rgb(255, 83, 112)">0.000007</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">preparing <span style="box-sizing: border-box;color: rgb(255, 83, 112)">0.000006</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">executing <span style="box-sizing: border-box;color: rgb(255, 83, 112)">0.000001</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">Sending <span style="box-sizing: border-box;color: rgb(199, 146, 234)">data</span> <span style="box-sizing: border-box;color: rgb(255, 83, 112)">0.010488</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(199, 146, 234)">end</span> <span style="box-sizing: border-box;color: rgb(255, 83, 112)">0.000006</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(199, 146, 234)">query</span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">end</span> <span style="box-sizing: border-box;color: rgb(255, 83, 112)">0.000007</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">closing <span style="box-sizing: border-box;color: rgb(199, 146, 234)">tables</span> <span style="box-sizing: border-box;color: rgb(255, 83, 112)">0.000005</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">freeing items <span style="box-sizing: border-box;color: rgb(255, 83, 112)">0.000009</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">cleaning up <span style="box-sizing: border-box;color: rgb(255, 83, 112)">0.000008</span></span>

注意：Sending data表示MySQL线程开始访问数据行并把结果返回给客户端，而不是仅仅将结果返回给客户端。这个过程需要大量的磁盘读取操作，因此该操作也是查询中最耗时操作。

第五步：通过trace分析优化器如何选择执行计划

trace对sql进行跟踪，通过trace分析能够进一步了解为什么优化器选择A执行计划而不是选择B执行计划。

举个例子，有一个查询子查询语句，其sql书写顺序为1、2、3，MySQL在实际执行过程中可能是按照3、1、2的执行执行顺序来执行的，通过trace就能知道原因。

第一步：开启trace，并设置格式为JSON

SET OPTIMIZER_TRACE="enabled=on",END_MARKERS_IN_JSON=on;

可选步骤：设置最大使用内存（可选）

SET OPTIMIZER_TRACE_MAX_MEM_SIZE=1000000;

第二步：执行需要trace的sql语句

select city from city where city_id =1;

第三步：查看分析结果

SELECT * FROM information_schema.OPTIMIZER_TRACE\G;

注意：Navicat携带\G会报错

第六步：确定问题并进行优化

如：对表进行建立索引，优化语句查询等。

请登录后再评论