MySQL慢查询的坑

<p>一条慢查询会造成什么后果？年轻时，我一直觉得不就是返回数据会慢一些么，用户体验变差？其实远远不止，我经历过几次线上事故，有一次就是由一条SQL慢查询导致的。</p>
<p>记得那是一条查询SQL，数据量万级时还保持在0.2秒内，随着某一段时间数据猛增，耗时一度达到了2-3秒！没有命中索引，导致全表扫描。explain 中extra显示：Using where; Using temporary; Using filesort，被迫使用了临时表排序，由于是高频查询，并发一起来很快就把DB线程池打满了，导致大量查询请求堆积，DB服务器cpu长时间100%+，大量请求timeout。。最终系统崩溃。老板登场~</p>
<p>对了，那次是十月二日晚上8点半，我在老家枣庄，和哥儿几个正坐在大排档吹着牛B！你猜，我将面临什么尴尬局面？</p>
<p>可见，团队如果对慢查询不引起足够的重视，风险是很大的。经过那次事故我们老板就说了：谁的代码再出现类似事故，开发和部门领导一起走人，吓得一大堆领导心发慌，赶紧招了两位DBA同事🙂🙂🙂。</p>
<p>慢查询，顾名思义，执行很慢的查询。有多慢？超过 long_query_time 参数设定的时间阈值（默认10s），就被认为是慢的，是需要优化的。慢查询被记录在慢查询日志里。</p>
<p>慢查询日志默认是不开启的，如果你需要优化SQL语句，就可以开启这个功能，它可以让你很容易地知道哪些语句是需要优化的（想想一个SQL要10s就可怕）。</p>
<p>墨菲定律：会出错的事情就一定会出错。</p>
<p>这是太真实的事情之一了。为了防患于未然，一起来看看慢查询该怎么处理。本文很干，记得接杯水，没时间看的先收藏哦！<br>
</p>
<h2>一、慢查询配置<br>
</h2>
<h3>1-1、开启慢查询<br>
</h3>
<p>MySQL支持通过</p>
<ul>
<li>1、输入命令开启慢查询（临时），在MySQL服务重启后会自动关闭；</li>
<li>2、配置my.cnf（windows是my.ini）系统文件开启，修改配置文件是持久化开启慢查询的方式。</li>
</ul>
<p><strong>方式一：通过命令开启慢查询</strong><br>
</p>
<p>步骤1、查询 slow_query_log 查看是否已开启慢查询日志：</p>
<div class="blockcode">
<pre class="brush:sql;">
show variables like '%slow_query_log%';</pre>
</div>
<div class="blockcode">
<pre class="brush:sql;">
mysql> show variables like '%slow_query_log%';
+---------------------+-----------------------------------+
| Variable_name    | Value                            |
+---------------------+-----------------------------------+
| slow_query_log    | OFF                            |
| slow_query_log_file | /var/lib/mysql/localhost-slow.log |
+---------------------+-----------------------------------+
2 rows in set (0.01 sec)</pre>
</div>
<p>步骤2、开启慢查询命令：</p>
<div class="blockcode">
<pre class="brush:sql;">
set global slow_query_log='ON'; </pre>
</div>
<p>步骤3、指定记录慢查询日志SQL执行时间得阈值（long_query_time 单位：秒，默认10秒）</p>
<p>如下我设置成了1秒，执行时间超过1秒的SQL将记录到慢查询日志中</p>
<div class="blockcode">
<pre class="brush:sql;">
set global long_query_time=1; </pre>
</div>
<p>步骤4、查询 “慢查询日志文件存放位置”</p>
<div class="blockcode">
<pre class="brush:sql;">
show variables like '%slow_query_log_file%';</pre>
</div>
<div class="blockcode">
<pre class="brush:sql;">
mysql> show variables like '%slow_query_log_file%';
+---------------------+-----------------------------------+
| Variable_name    | Value                            |
+---------------------+-----------------------------------+
| slow_query_log_file | /var/lib/mysql/localhost-slow.log |
+---------------------+-----------------------------------+
1 row in set (0.01 sec)</pre>
</div>
<p>slow_query_log_file 指定慢查询日志的存储路径及文件（默认和数据文件放一起）</p>
<p>步骤5、核对慢查询开启状态</p>
<p>需要退出当前MySQL终端，重新登录即可刷新；</p>
<p>配置了慢查询后，它会记录以下符合条件的SQL：</p>
<ul>
<li>查询语句</li>
<li>数据修改语句</li>
<li>已经回滚的SQL</li>
</ul>
<p><strong>方式二：通过配置my.cnf（windows是my.ini）系统文件开启</strong><br>
</p>
<p>（版本：MySQL5.5及以上）</p>
<p>在my.cnf文件的[mysqld]下增加如下配置开启慢查询，如下图</p>
<div class="blockcode">
<pre class="brush:plain;">
# 开启慢查询功能
slow_query_log=ON
# 指定记录慢查询日志SQL执行时间得阈值
long_query_time=1
# 选填，默认数据文件路径
# slow_query_log_file=/var/lib/mysql/localhost-slow.log

</pre>
</div>
<p style="text-align: center"><img alt="" src="https://beijingoptbbs.oss-cn-hangzhou.aliyuncs.com/jb/2426819-665e27f09fd58fe510569b4ddbdeef88"></p>
<p>重启数据库后即持久化开启慢查询，查询验证如下：</p>
<div class="blockcode">
<pre class="brush:sql;">
mysql> show variables like '%_query_%';
+------------------------