MySQL之SQL优化篇（二）：SQL性能分析与诊断

MySQL之SQL优化篇（二）：SQL性能分析与诊断

  在优化之前，学会分析SQL性能十分重要，只有知道影响性能点，才能针对性地去进行优化。

上一节：MySQL之SQL优化篇（一）：概述

SQL性能分析的目的

  在编写SQL之初，最重要的是完成功能开发，在当时的系统环境和业务环境下，我们“自以为SQL已经最优”（不然也不会写上去）。如果不是因为业务上出现长时间等待或者超时，一般是想不起来要进行SQL优化的。当需要进行SQL优化时，掌握SQL性能分析与诊断的方法就显得尤为重要。只有找出问题所在，才能解决问题。
  本文将从数据库层面和应用层面，分别罗列一些分析SQL性能的方法。

数据库层面

数据库连接工具

  数据库连接工具都会显示SQL的执行时间（如图所示，为MySQL Workbench显示查询用时），可以通过此方法对比同一SQL不同实现的用时。

  开源免费数据库连接工具推荐:

工具名称	获取方式
MySQL Workbench	Windows MySQL Installer自带安装方式
DBeaver	DBeaver官网：https://dbeaver.io/

EXPLAIN命令

  EXPLAIN命令是查看查询优化器如何决定执行查询的主要方法，在SQL优化时该命令非常有用。虽然SQL并不是完全按照该输出的计划去执行，但它输出的结果依然很有参考性。
  下图是一段SQL查询执行EXPLAIN命令后的输出结果（MySQL 8.0.27）：

  关键的type、rows、filtered、Extra列的解释如下：

列名	解释
type	可选值（从左到右性能越来越好）：ALL、index、range、index_subquery、unique_subquery、index_merge、ref_or_null、fulltext、ref、eq_ref、const、system，重点关注ALL全表扫描、index索引树扫描、range范围扫描，很可能存在优化空间
rows	rows为扫描的总行数
filtered	该列是需要的数据行数占扫描总行数的比例，最高值100，越大越好
Extra	EXPLAIN 输出的 Extra 列包含有关 MySQL 如何解析查询的附加信息，下面的列表解释了可以出现在此列中的部分值：Using index（使用覆盖索引，不需要回表查询）、Using where（存储引擎过滤后，在MySQL服务器层再进行二次过滤）、Using temporary（使用临时表）、Using filesort（表示会对结果使用一个外部索引排序，而不是按索引的顺序简化排序操作）、…

  更多有关EXPLAIN输出列的解释请参考官方文档：MySQL EXPLAIN命令官方文档

SHOW STATUS命令

  SHOW STATUS命令返回了一些计数器，展示服务器的状态信息，此语句不需要任何privilege，仅仅是能连接到服务器。例子如下图所示（由于先运行了flush status清空计数器，所以都为0）：

  下面是执行查询语句后的结果：

  SHOW STATUS既有服务器级别的全局计数器，也有会话级别的。SHOW STATUS 接受可选的 GLOBAL 或 SESSION 变量范围修饰符：（1）使用GLOBAL 修饰符，该语句显示全局状态值。 全局状态变量可能表示服务器本身某些方面的状态（例如，Aborted_connects），或与 MySQL 的所有连接的聚合状态（例如，Bytes_received 和 Bytes_sent）。 如果变量没有全局值，则显示会话值。（2）使用 SESSION 修饰符，该语句显示当前连接的状态变量值。 如果变量没有会话值，则显示全局值。 LOCAL是会话的同义词。（3）如果不存在修饰符，则默认为 SESSION。参考官网描述
  下图是执行完flush status命令后show global status的结果（发现有些参数并没有被清空，注意，没有flush global status命令）：

  SHOW STATUS与EXPLAIN最大的不同在于，SHOW STATUS展示的是当前真实的数据，而EXPLAIN是估计的结果。
  更多有关SHOW STATUS输出参数的解释请参考官方文档：MySQL SHOW STATUS命令官方文档

SHOW PROCESSLIST命令

  通过不停获取SHOW PROCESSLIST的输出，来发现是否有大量线程处于不正常状态（可以绘制折线图，观察是否有“尖刺”）
  下图是执行此命令的示例，其中state变量每次执行的结果都可能不同，它代表了线程当前的状态，每个状态代表SQL语句执行到MySQL服务器“流水线”（建立连接、优化查询、发送数据等）的不同位置，如果有大量线程卡在某个位置，那么就要分析产生此问题的具体原因。

  从 MySQL 8.0.22 开始，SHOW PROCESSLIST 的替代实现基于 Performance Schema processlist 表可用，与默认的 SHOW PROCESSLIST 实现不同，它不需要互斥体并且具有更好的性能特征。参考官方文档
  更多关于Performance Schema processlist的描述和字段信息参考Performance Schema processlist
  下图是使用Performance Schema processlist的结果：

应用层面

有时候仅仅优化SQL并不能解决问题，有可能是后端逻辑问题，导致不必要的查询，需要结合后端逻辑进行优化

Druid数据库连接池监控

  Druid是为监控而生的数据库连接池，可以监控SQL执行情况，列出问题SQL，除此之外，还拥有数据源、SQL防火墙、Web应用、URL监控、Session监控、Spring监控等一系列监控功能，更多信息参考：Druid 常见问题

下一节：MySQL之SQL优化篇（三）：浅谈MySQL存储引擎