在前面的文章中就讲过了查询优化器,知道在查询优化器中会经历逻辑查询优化和物理查询优化。需要注意的是,查询优化器只能在已经确定的情况下(SQL 语句、索引设计、缓冲池大小、查询优化器参数等)决定最优的查询执行计划。
但实际上 SQL 执行起来可能还是很慢,那么到底从哪里定位 SQL 查询慢的问题呢?是索引设计的问题?服务器参数配置的问题?还是需要增加缓存的问题呢?今天我们就从性能分析来入手,定位导致 SQL 执行慢的原因。
今天的内容主要包括以下几个部分:
- 数据库服务器的优化分析的步骤是怎样的?中间有哪些需要注意的地方?
- 如何使用慢查询日志查找执行慢的 SQL 语句? 3.如何使用 EXPLAIN 查看 SQL 执行计划? 4.如何使用 SHOW PROFILING 分析 SQL 执行步骤中的每一步的执行时间?
一、数据库服务器的优化步骤
当我们遇到数据库调优问题的时候,该如何思考呢?我把思考的流程整理成了下面这张图。
整个流程划分成了观察(Show status)和行动(Action)两个部分。字母 S 的部分代表观察(会使用相应的分析工具),字母 A 代表的部分是行动(对应分析可以采取的行动)。
![ab30473a63bd1e3e98b3a82cf09180e8.png](https://i-blog.csdnimg.cn/blog_migrate/92596f5eaf30feefd3df79527a5b8f79.jpeg)
我们可以通过观察了解数据库整体的运行状态,通过性能分析工具可以让我们了解执行慢的SQL 都有哪些,查看具体的 SQL 执行计划,甚至是 SQL 执行中的每一步的成本代价,这样才能定位问题所在,找到了问题,再采取相应的行动。
我来详细解释一下这张图。
首先在 S1 部分,我们需要观察服务器的状态是否存在周期性的波动。如果存在周期性波动,有可能是周期性节点的原因,比如双十一、促销活动等。这样的话,我们可以通过 A1这一步骤解决,也就是加缓存,或者更改缓存失效策略。
如果缓存策略没有解决,或者不是周期性波动的原因,我们就需要进一步分析查询延迟和卡顿的原因。接下来进入 S2 这一步,我们需要开启慢查询。慢查询可以帮我们定位执行慢的SQL 语句。我们可以通过设置 long_query_time 参数定义“慢”的阈值,如果 SQL 执行时间超过了 long_query_time,则会认为是慢查询。当收集上来这些慢查询之后,我们就可以通过分析工具对慢查询日志进行分析。
在 S3 这一步骤中,我们就知道了执行慢的 SQL,这样就可以针对性地用 EXPLAIN 查看对应 SQL 语句的执行计划,或者使用 show profile 查看 SQL 中每一个步骤的时间成本。这样我们就可以了解 SQL 查询慢是因为执行时间长,还是等待时间长。
如果是 SQL 等待时间长,我们进入 A2 步骤。在这一步骤中,我们可以调优服务器的参数,比如适当增加数据库缓冲池等。如果是 SQL 执行时间长,就进入 A3 步骤,这一步中我们需要考虑是索引设计的问题?还是查询关联的数据表过多?还是因为数据表的字段设计问题导致了这一现象。然后在这些维度上进行对应的调整。
如果 A2 和 A3 都不能解决问题,我们需要考虑数据库自身的 SQL 查询性能是否已经达到了瓶颈,如果确认没有达到性能瓶颈,就需要重新检查,重复以上的步骤。如果已经达到了性能瓶颈,进入 A4 阶段,需要考虑增加服务器,采用读写分离的架构,或者考虑对数据库进行分库分表,比如垂直分库、垂直分表和水平分表等。
以上就是数据库调优的流程思路。如果我们发现执行 SQL 时存在不规则延迟或卡顿的时候,就可以采用分析工具帮我们定位有问题的 SQL,这三种分析工具你可以理解是 SQL 调优的三个步骤:慢查询、EXPLAIN 和 SHOW PROFILING。