【黄啊码】MySQL入门—15、技术老大：写的SQL性能这么差，还好意思说自己五年开发经验？

​

大家好！我是黄啊码，MySQL的入门篇已经讲到第14个课程了，今天我们继续讲讲大白篇系列——数据库服务器优化流程

数据库服务器的优化步骤

当我们遇到数据库调优问题的时候，该如何思考呢？我把思考的流程整理成了下面这张图。

整个流程划分成了观察（Show status）和行动（Action）两个部分。字母 S 的部分代表观察（会使用相应的分析工具），字母 A 代表的部分是行动（对应分析可以采取的行动）。

​编辑

我们可以通过观察了解数据库整体的运行状态，通过性能分析工具可以让我们了解执行慢的 SQL 都有哪些，查看具体的 SQL 执行计划，甚至是 SQL 执行中的每一步的成本代价，这样才能定位问题所在，找到了问题，再采取相应的行动。

以上图片就是数据库调优的流程思路。如果我们发现执行 SQL 时存在不规则延迟或卡顿的时候，就可以采用分析工具帮我们定位有问题的 SQL，这三种分析工具你可以理解是 SQL 调优的三个步骤：慢查询、EXPLAIN 和 SHOW PROFILING。

使用慢查询定位执行慢的 SQL

好慢询可以帮我们找到执行慢的 SQL，在使用前，我们需要先看下慢查询是否已经开启，使用下面这条命令即可：

<code style="margin-left:0">show variables like '%slow_query_log';</code>

​编辑

我们能看到 slow_query_log=OFF，也就是说慢查询日志此时是关上的。我们可以把慢查询日志打开，注意设置变量值的时候需要使用 global，否则会报错：

<code style="margin-left:0">set global slow_query_log='ON';</code>

然后我们再来查看下慢查询日志是否开启，以及慢查询日志文件的位置：

​编辑

你能看到这时慢查询分析已经开启，同时文件保存在 DESKTOP-4BK02RP-slow 文件中。

接下来我们来看下慢查询的时间阈值设置，使用如下命令：

<code style="margin-left:0">show variables like '%long_query_time%';</code>

这里如果我们想把时间缩短，比如设置为 3 秒，可以这样设置：

<code style="margin-left:0">set global long_query_time = 3;</code>

​编辑

我们可以使用 MySQL 自带的 mysqldumpslow 工具统计慢查询日志（这个工具是个 Perl 脚本，你需要先安装好 Perl）。

mysqldumpslow 命令的具体参数如下：

• -s：采用 order 排序的方式，排序方式可以有以下几种。分别是 c（访问次数）、t（查询时间）、l（锁定时间）、r（返回记录）、ac（平均查询次数）、al（平均锁定时间）、ar（平均返回记录数）和 at（平均查询时间）。其中 at 为默认排序方式。
• -t：返回前 N 条数据 。
• -g：后面可以是正则表达式，对大小写不敏感。

比如我们想要按照查询时间排序，查看前两条 SQL 语句，这样写即可：

<code style="margin-left:0">perl mysqldumpslow.pl -s t -t 2 "C:\ProgramData\<a href="https://www.mhzhuji.com/kwck/57"  class="lar_link lar_link_outgoing" data-linkid="219" data-postid="787"  title="数据库MySQL"  rel="nofollow" target="_blank" >MySQL</a>\<a href="https://www.mhzhuji.com/kwck/57"  class="lar_link lar_link_outgoing" data-linkid="219" data-postid="787"  title="数据库MySQL"  rel="nofollow" target="_blank" >MySQL</a> Server 8.0\Data\DESKTOP-4BK02RP-slow.log"</code>

​编辑

你能看到开启了慢查询日志，并设置了相应的慢查询时间阈值之后，只要大于这个阈值的 SQL 语句都会保存在慢查询日志中，然后我们就可以通过 mysqldumpslow 工具提取想要查找的 SQL 语句了。

如何使用 EXPLAIN 查看执行计划

定位了查询慢的 SQL 之后，我们就可以使用 EXPLAIN 工具做针对性的分析，比如我们想要了解 product_comment 和 user 表进行联查的时候所采用的的执行计划，可以使用下面这条语句：

<code style="margin-left:0">EXPLAIN SELECT comment_id, product_id, comment_text, product_comment.user_id, user_name FROM product_comment JOIN user on product_comment.user_id = user.user_id </code>

​编辑

EXPLAIN 可以帮助我们了解数据表的读取顺序、SELECT 子句的类型、数据表的访问类型、可使用的索引、实际使用的索引、使用的索引长度、上一个表的连接匹配条件、被优化器查询的行的数量以及额外的信息（比如是否使用了外部排序，是否使用了临时表等）等。

SQL 执行的顺序是根据 id 从大到小执行的，也就是 id 越大越先执行，当 id 相同时，从上到下执行。

数据表的访问类型所对应的 type 列是我们比较关注的信息。type 可能有以下几种情况：

​编辑

在这些情况里，all 是最坏的情况，因为采用了全表扫描的方式。index 和 all 差不多，只不过 index 对索引表进行全扫描，这样做的好处是不再需要对数据进行排序，但是开销依然很大。如果我们在 extra 列中看到 Using index，说明采用了索引覆盖，也就是索引可以覆盖所需的 SELECT 字段，就不需要进行回表，这样就减少了数据查找的开销。

比如我们对 product_comment 数据表进行查询，设计了联合索引 composite_index (user_id, comment_text)，然后对数据表中的 comment_id、comment_text、user_id 这三个字段进行查询，最后用 EXPLAIN 看下执行计划：

<code style="margin-left:0">EXPLAIN SELECT comment_id, comment_text, user_id FROM product_comment </code>

​编辑

你能看到这里同时使用到了两个索引，分别是主键和 user_id，采用的数据表访问类型是 index_merge，通过 union 的方式对两个索引检索的数据进行合并。

ref 类型表示采用了非唯一索引，或者是唯一索引的非唯一性前缀。比如我们想要对 user_id=500000 的评论进行查询，使用 EXPLAIN 查看执行计划：

<code style="margin-left:0">EXPLAIN SELECT comment_id, comment_text, user_id FROM product_comment WHERE user_id = 500000 </code>

这里 user_id 为普通索引（因为 user_id 在商品评论表中可能是重复的），因此采用的访问类型是 ref，同时在 ref 列中显示 const，表示连接匹配条件是常量，用于索引列的查找。

eq_ref 类型是使用主键或唯一索引时产生的访问方式，通常使用在多表联查中。假设我们对 product_comment 表和 usre 表进行联查，关联条件是两张表的 user_id 相等，使用 EXPLAIN 进行执行计划查看：

<code style="margin-left:0">EXPLAIN SELECT * FROM product_comment JOIN user WHERE product_comment.user_id = user.user_id </code>

const 类型表示我们使用了主键或者唯一索引（所有的部分）与常量值进行比较，比如我们想要查看 comment_id=500000，查看执行计划：

<code style="margin-left:0">EXPLAIN SELECT comment_id, comment_text, user_id FROM product_comment WHERE comment_id = 500000 </code>

需要说明的是 const 类型和 eq_ref 都使用了主键或唯一索引，不过这两个类型有所区别，const 是与常量进行比较，查询效率会更快，而 eq_ref 通常用于多表联查中。

system 类型一般用于 MyISAM 或 Memory 表，属于 const 类型的特例，当表只有一行时连接类型为 system（我在 GitHub 上上传了 test_myisam 数据表，该数据表只有一行记录，

<code style="margin-left:0">EXPLAIN SELECT * FROM test_myisam</code>

你能看到除了 all 类型外，其他类型都可以使用到索引，但是不同的连接方式的效率也会有所不同，效率从低到高依次为 all < index < range < index_merge < ref < eq_ref < const/system。我们在查看执行计划的时候，通常希望执行计划至少可以使用到 range 级别以上的连接方式，如果只使用到了 all 或者 index 连接方式，我们可以从 SQL 语句和索引设计的角度上进行改进。

使用 SHOW PROFILE 查看 SQL 的具体执行成本

SHOW PROFILE 相比 EXPLAIN 能看到更进一步的执行解析，包括 SQL 都做了什么、所花费的时间等。默认情况下，profiling 是关闭的，我们可以在会话级别开启这个功能。

<code style="margin-left:0">show variables like 'profiling';</code>

通过设置 profiling='ON’来开启 show profile：

<code style="margin-left:0">set profiling = 'ON';</code>

我们可以看下当前会话都有哪些 profiles，使用下面这条命令：

<code style="margin-left:0">show profiles;</code>

你能看到当前会话一共有 2 个查询，如果我们想要查看上一个查询的开销，可以使用：

<code style="margin-left:0">show profile;</code>

我们也可以查看指定的 Query ID 的开销，比如 show profile for query 2 查询结果是一样的。在 SHOW PROFILE 中我们可以查看不同部分的开销，比如 cpu、block.io 等：

通过上面的结果，我们可以弄清楚每一步骤的耗时，以及在不同部分，比如 CPU、block.io 的执行时间，这样我们就可以判断出来 SQL 到底慢在哪里。

不过 SHOW PROFILE 命令将被弃用，我们可以从 information_schema 中的 profiling 数据表进行查看。

好了，今天的课程学到这里，有问题的留个言，别忘了一键三连，下次我们还会再见！

我是黄啊码，码字的码，退。。。退。。。退。。。朝！

​

未经允许不得转载：木盒主机 » 【黄啊码】MySQL入门—15、技术老大：写的SQL性能这么差，还好意思说自己五年开发经验？