mysql update 慢_《深入精通Mysql（六）》系列之如何通过慢查询日志进行SQL分析和优化...

深入精通Mysql系列其他文章推荐：

《深入精通Mysql（一）》系列之Mysql整体架构和sql执行过程

《深入精通Mysql（二）》深入底层剖析Mysql索引（面试必问）

《深入精通Mysql（三）》深入底层剖析Mysql各种锁机制（面试必问）

《深入精通Mysql（四）》MySQL 事务机制，中高级开发面试必问！

《深入精通Mysql（五）》实战：Mysql实现主从复制

《深入精通Mysql（六）》系列之如何通过慢查询日志进行SQL分析和优化

《深入精通Mysql（七）》系列之如何通过EXPLAIN 执行计划分析SQL语句的性能瓶颈

《深入精通Mysql（八）》系列之十年架构师从架构层面进行Mysql性能优化

---

从本系列第一篇《深入精通Mysql（一）》系列之Mysql整体架构和sql执行过程我们就可以知道一条sql语句的执行过程会经过优化器进行优化。

优化器就是对我们的 SQL 语句进行分析，生成执行计划。

问题：在我们做项目的时候，有时会收到 DBA 的邮件，里面列出了我们项目上几个耗时比较长的查询语句，让我们去优化，这些语句是从哪里来的呢？

我们的服务层每天执行了这么多 SQL 语句，它怎么知道哪些 SQL 语句比较慢呢？

第一步，我们要把 SQL 执行情况记录下来。

1.1 慢查询日志 slow query log

1.1.1 打开慢日志开关

因为开启慢查询日志是有代价的（跟 bin log、optimizer-trace 一样），所以它默认是关闭的：

show variables like 'slow_query%';

除了这个开关，还有一个参数，控制执行超过多长时间的 SQL 才记录到慢日志，默认是 10 秒。

show variables like '%slow_query%';

可以直接动态修改参数（重启后失效）。

set @@global.slow_query_log=1; 
set @@global.long_query_time=3; 
​show variables like '%long_query%';-- 1 开启， 0 关闭， 重启后失效
-- mysql 默认的慢查询时间是 10 秒， 另开一个窗口后才会查到最新值
show variables like '%slow_query%';

或者修改配置文件 my.cnf。

以下配置定义了慢查询日志的开关、慢查询的时间、日志文件的存放路径。

slow_query_log = ON
long_query_time=2
slow_query_log_file =/var/lib/mysql/localhost-slow.log

模拟慢查询：

select sleep(10);

查询 user_innodb 表的 500 万数据（检查是不是没有索引）。

SELECT * FROM `user_innodb` where phone = '136';

4.1.2 慢日志分析

1、 日志内容

show global status like 'slow_queries'; -- 查看有多少慢查询
show variables like '%slow_query%'; -- 获取慢日志目录

--日志路径
cat /var/lib/mysql/ localhost-slow.log

日志内容

有了慢查询日志，怎么去分析统计呢？
比如 SQL 语句的出现的慢查询次数最多，平均每次执行了多久？

2、 mysqldumpslow

https://dev.mysql.com/doc/refman/5.7/en/mysqldumpslow.html

MySQL 提供了 mysqldumpslow 的工具，在 MySQL 的 bin 目录下。

mysqldumpslow --help

例如：查询用时最多的 20 条慢 SQL：

mysqldumpslow -s t -t 20 -g 'select' /var/lib/mysql/localhost-slow.log

Count 代表这个 SQL 执行了多少次；

Time 代表执行的时间，括号里面是累计时间；

Lock 表示锁定的时间，括号是累计；

Rows 表示返回的记录数，括号是累计。

除了慢查询日志之外，还有一个 SHOW PROFILE 工具可以使用。

4.2 SHOW PROFILE

SHOW PROFILE 是谷歌高级架构师 Jeremy Cole 贡献给 MySQL 社区的，可以查看SQL 语句执行的时候使用的资源，比如 CPU、IO 的消耗情况。

在 SQL 中输入 help profile 可以得到详细的帮助信息。

4.2.1 查看是否开启

select @@profiling;
set @@profiling=1;

4.2.2 查看 profile 统计

（命令最后带一个 s）

show profiles;

查看最后一个 SQL 的执行详细信息，从中找出耗时较多的环节（没有 s）。

show profile;

6.2E-5，小数点左移 5 位，代表 0.000062 秒。

也可以根据 ID 查看执行详细信息，在后面带上 for query + ID。

show profile for query 1;

除了慢日志和 show profile，如果要分析出当前数据库中执行的慢的 SQL，还可以通过查看运行线程状态和服务器运行信息、存储引擎信息来分析。

4.2.3 其他系统命令

show processlist 运行线程

show processlist;

这是很重要的一个命令，用于显示用户运行线程。可以根据 id 号 kill 线程。 也可以查表，效果一样：

select * from information_schema.processlist;

show status 服务器运行状态

https://dev.mysql.com/doc/refman/5.7/en/show-status.html

SHOW STATUS 用于查看 MySQL 服务器运行状态（重启后会清空），有 session和 global 两种作用域，格式：参数-值。

可以用 like 带通配符过滤。

SHOW GLOBAL STATUS LIKE 'com_select'; -- 查看 select 次数

show engine 存储引擎运行信息

https://dev.mysql.com/doc/refman/5.7/en/show-engine.html

show engine 用来显示存储引擎的当前运行信息，包括事务持有的表锁、行锁信息；事务的锁等待情况；线程信号量等待；文件 IO 请求；buffer pool 统计信息。

例如：

show engine innodb status;

如果需要将监控信息输出到错误信息 error log 中（15 秒钟一次），可以开启输出。

show variables like 'innodb_status_output%';

-- 开启输出：
SET GLOBAL innodb_status_output=ON;
SET GLOBAL innodb_status_output_locks=ON;

我们现在已经知道了这么多分析服务器状态、存储引擎状态、线程运行信息的命令，如果让你去写一个数据库监控系统，你会怎么做？

其实很多开源的慢查询日志监控工具，他们的原理其实也都是读取的系统的变量和状态。

现在我们已经知道哪些 SQL 慢了，为什么慢呢？慢在哪里？

MySQL 提供了一个执行计划的工具（在架构中我们有讲到，优化器最终生成的就是一个执行计划），其他数据库，例如 Oracle 也有类似的功能。

通过 EXPLAIN 我们可以模拟优化器执行 SQL 查询语句的过程，来知道 MySQL 是

怎么处理一条 SQL 语句的。通过这种方式我们可以分析语句或者表的性能瓶颈。

explain 可以分析 update、delete、insert 么？

MySQL 5.6.3以前只能分析 SELECT;

MySQL5.6.3以后就可以分析update、delete、insert 了。

我们将在下一篇文章为大家带来EXPLAIN 执行计划详解。

---

深入精通Mysql系列其他文章推荐：

《深入精通Mysql（一）》系列之Mysql整体架构和sql执行过程

《深入精通Mysql（二）》深入底层剖析Mysql索引（面试必问）

《深入精通Mysql（三）》深入底层剖析Mysql各种锁机制（面试必问）

《深入精通Mysql（四）》MySQL 事务机制，中高级开发面试必问！

《深入精通Mysql（五）》实战：Mysql实现主从复制

《深入精通Mysql（六）》系列之如何通过慢查询日志进行SQL分析和优化

《深入精通Mysql（七）》系列之如何通过EXPLAIN 执行计划分析SQL语句的性能瓶颈

《深入精通Mysql（八）》系列之十年架构师从架构层面进行Mysql性能优化