数据库服务器的优化步骤

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

image.png

  • 慢查询可以帮我们定位执行慢的 SQL 语句。可以通过设置long_query_time参数定义“慢”的阈值，如果 SQL 执行时间超过了long_query_time，则会认为是慢查询。当收集上来这些慢查询之后，我们就可以通过分析工具对慢查询日志进行分析。
  • 调优服务器的参数，比如适当增加数据库缓冲池等。
  • 这三种分析工具你可以理解是 SQL 调优的三个步骤：慢查询、EXPLAIN 和 SHOW PROFILE。

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

  1. 看下慢查询是否已经开启:
  1. mysql > show variables like '%slow_query_log';

image.png

  1. 把慢查询日志打开:
  1. mysql > set global slow_query_log='ON';

image.png

  1. 看下慢查询的时间阈值设置
  1. mysql > show variables like '%long_query_time%';

image.png

  1. 把时间缩短，比如设置为 3 秒
  1. mysql > set global long_query_time = 3;

image.png

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

mysqldumpslow 命令的具体参数如下：

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

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

  1. perl mysqldumpslow.pl -s t -t 2 "C:\ProgramData\MySQL\MySQL Server 8.0\Data\DESKTOP-4BK02RP-slow.log"

image.png

如何使用 EXPLAIN 查看执行计划

  1. 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

image.png

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

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

image.png

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

range 表示采用了索引范围扫描. 尽量让 SQL 查询可以使用到 range 这一级别及以上的 type 访问方式

index_merge 说明查询同时使用了两个或以上的索引，最后取了交集或者并集。

ref 类型表示采用了非唯一索引，或者是唯一索引的非唯一性前缀。

eq_ref 类型是使用主键或唯一索引时产生的访问方式，通常使用在多表联查中。

const 类型表示我们使用了主键或者唯一索引（所有的部分）与常量值进行比较

system 类型一般用于 MyISAM 或 Memory 表，属于 const 类型的特例，当表只有一行时连接类型为 system

效率从低到高依次为 all < index < range < index_merge < ref < eq_ref < const/system

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

SHOW PROFILE 相比 EXPLAIN 能看到更进一步的执行解析，包括 SQL 都做了什么、所花费的时间等。默认情况下，profiling 是关闭的.

  1. 我们可以在会话级别开启这个功能:
  1. mysql > show variables like 'profiling';

image.png

  1. 通过设置profiling=’ON’来开启 show profile
  1. mysql > set profiling = 'ON';

image.png

  1. 看下当前会话都有哪些 profiles，使用下面这条命令
  1. mysql > show profiles;

image.png

  1. 查看上一个查询的开销
  1. mysql > show profile;

image.png

  1. 查看指定的 Query ID 的开销
  1. mysql > show profile for query 2;
  1. 查看不同维度的开销
  1. mysql > show profile cpu, block io for query 2;

image.png

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

总结

image.png