查看mysql性能工具

1. 调优思路

在数据库调优中，我们的目标就是响应时间更快，吞吐量更大。利用宏观的监控工具和微观的日志分析可以帮我们快速找到调优的思路和方式。

以下提供数据库优化步骤，该如何思考呢? 这里把思考的流程整理成下面这张图。整个流程划分成了观察(Show status)和行动(Action)两个部分。字母s的部分代表观察(会使用相应的分析工具)，字母A代表的部分是行动(对应分析可以采取的行动)。

2. 调优参数

在MySQL中，可以使用 SHOW STATUS 语句查询一些MySQL数据库服务器的性能参数、执行频率。

SHOW STATUS语句语法如下：

SHOW [GLOBAL|SESSION] STATUS LIKE '参数';

一些常用的性能参数如下：

Connections：连接MySQL服务器的次数。
Uptime：MySQL服务器的上线时间。
Slow_queries：慢查询的次数。
Innodb_rows_read：Select查询返回的行数
Innodb_rows_inserted：执行INSERT操作插入的行数
Innodb_rows_updated：执行UPDATE操作更新的行数
Innodb_rows_deleted：执行DELETE操作删除的行数
Com_select：查询操作的次数。
Com_insert：插入操作的次数。对于批量插入的 INSERT 操作，只累加一次。
Com_update：更新操作的次数。
Com_delete：删除操作的次数。

若查询MySQL服务器的连接次数，则可以执行如下语句:

SHOW STATUS LIKE 'Connections';

若查询服务器工作时间，则可以执行如下语句:

SHOW STATUS LIKE 'Uptime';

若查询MySQL服务器的慢查询次数，则可以执行如下语句:

SHOW STATUS LIKE 'Slow_queries';

慢查询次数参数可以结合慢查询日志找出慢查询语句，然后针对慢查询语句进行表结构优化或者查询语句优化。

再比如，如下的指令可以查看相关的指令情况：

SHOW STATUS LIKE 'Innodb_rows_%';

3. 统计SQL的查询成本: last_query_cost

一条SQL查询语句在执行前需要查询执行计划，如果存在多种执行计划的话，MySQL会计算每个执行计划所需要的成本，从中选择成本最小的一个作为最终执行的执行计划。

如果我们想要查看某条SQL语句的查询成本，可以在执行完这条SQL语句之后，通过查看当前会话中的last_query_cost变量值来得到当前查询的成本。它通常也是我们评价一个查询的执行效率的一个常用指标。这个查询成本对应的是SQL 语句所需要读取的读页的数量。

我们依然使用第8章的 student_info 表为例：

CREATE TABLE `student_info` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `student_id` INT NOT NULL ,
    `name` VARCHAR(20) DEFAULT NULL,
    `course_id` INT NOT NULL ,
    `class_id` INT(11) DEFAULT NULL,
    `create_time` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

如果我们想要查询 id=900001 的记录，然后看下查询成本，我们可以直接在聚簇索引上进行查找：

SELECT student_id, class_id, NAME, create_time FROM student_info WHERE id = 900001;

运行结果（1 条记录，运行时间为 0.042s ）

然后再看下查询优化器的成本，实际上我们只需要检索一个页即可：

mysql> SHOW STATUS LIKE 'last_query_cost';
+-----------------+----------+
| Variable_name   |   Value  |
+-----------------+----------+
| Last_query_cost | 1.000000 |
+-----------------+----------+

如果我们想要查询 id 在 900001 到 9000100 之间的学生记录呢？

SELECT student_id, class_id, NAME, create_time FROM student_info WHERE id BETWEEN 900001 AND 900100;

运行结果（100 条记录，运行时间为 0.046s ）：

然后再看下查询优化器的成本，这时我们大概需要进行 20 个页的查询。

mysql> SHOW STATUS LIKE 'last_query_cost';
+-----------------+-----------+
| Variable_name   |   Value   |
+-----------------+-----------+
| Last_query_cost | 21.134453 |
+-----------------+-----------+

你能看到页的数量是刚才的 20 倍，但是查询的效率并没有明显的变化，实际上这两个 SQL 查询的时间基本上一样，就是因为采用了顺序读取的方式将页面一次性加载到缓冲池中，然后再进行查找。虽然页数量（last_query_cost）增加了不少，但是通过缓冲池的机制，并没有增加多少查询时间。

使用场景：它对于比较开销是非常有用的，特别是我们有好几种查询方式可选的时候。

SQL查询时一个动态的过程，从页加载的角度来看，我们可以得到以下两点结论：
位置决定效率。如果页就在数据库 缓冲池 中，那么效率是最高的，否则还需要从 内存 或者 磁盘 中进行读取，当然针对单个页的读取来说，如果页存在于内存中，会比在磁盘中读取效率高很多。
批量决定效率。如果我们从磁盘中对单一页进行随机读，那么效率是很低的(差不多10ms)，而采用顺序读取的方式，批量对页进行读取，平均一页的读取效率就会提升很多，甚至要快于单个页面在内存中的随机读取。
所以说，遇到I/O并不用担心，方法找对了，效率还是很高的。我们首先要考虑数据存放的位置，如果是进程使用的数据就要尽量放到缓冲池中，其次我们可以充分利用磁盘的吞吐能力，一次性批量读取数据，这样单个页的读取效率也就得到了提升。
在 MySQL 中，"last query cost"（最后查询成本）是优化器对刚刚执行的 SQL 查询计算出的 "执行成本"，它反映了 MySQL 认为执行这个查询需要消耗的资源量（主要是 I/O 和 CPU 开销）。
简单说，这个成本值是 MySQL 优化器的 "决策依据" 之一。当你执行一条 SQL 时，MySQL 不会直接执行，而是先由优化器分析多种可能的执行方案（比如用哪个索引、表连接顺序等），并计算每种方案的成本，最后选择成本最低的方案来执行。
"last query cost" 就是优化器为实际执行的那个方案计算出的成本值，它可以帮你：
判断查询是否高效（成本越低越好）
对比不同 SQL 写法的性能差异
分析索引是否被有效使用
3.1 怎么查看？
需要用 EXPLAIN ANALYZE 命令（MySQL 8.0 及以上版本支持），它会执行查询并返回实际执行计划，其中包含成本信息。
举个例子：
sql
-- 查看查询的成本
EXPLAIN ANALYZE SELECT * FROM users WHERE age > 30;
执行后，结果中会有类似这样的信息：
plaintext
-> Filter: (users.age > 30)  (cost=10.50 rows=50)
   -> Table scan on users  (cost=10.50 rows=100)
这里的 cost=10.50 就是这条查询的执行成本。
3.2 成本值怎么看？
成本是一个相对值（不是具体时间或资源数值），用于 MySQL 内部比较不同执行方案
数值越小，说明 MySQL 认为这个查询越 "便宜"（执行效率越高）
同一个查询在不同表数据量、索引状态下，成本值会变化（数据越多，成本通常越高）
3.3 实际用途举例
比如你想优化一条查询，先看原始写法的成本：
sql
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 123;
-- 假设成本是 1000
然后添加索引后再看：
sql
CREATE INDEX idx_user_id ON orders(user_id);
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 123;
-- 假设成本变成 10
成本从 1000 降到 10，说明索引生效了，查询更高效。
3.4 注意点
EXPLAIN（不带 ANALYZE）只能看到预估成本，EXPLAIN ANALYZE 才会显示实际执行的成本
成本低不绝对等于执行速度快（受硬件、锁等因素影响），但大多数情况下是正相关的
主要用于优化复杂查询（多表连接、子查询等），简单查询的成本参考意义不大
通过观察这个值，你可以更直观地判断 SQL 优化是否有效果，是 MySQL 性能调优的实用工具之一。
和show status like last query cos这条命令什么区别
你提到的 SHOW STATUS LIKE 'Last_query_cost' 和之前说的 EXPLAIN ANALYZE 查看的 "查询成本"，虽然都和 "成本" 相关，但本质上有很大区别，主要体现在 来源、含义和用途 上：
3.5 SHOW STATUS LIKE 'Last_query_cost'
含义：显示 MySQL 优化器对最后一次执行的查询计算出的预估成本（注意是 "预估"，不是实际执行后的成本）。
特点：
是一个全局状态变量，只记录最后一次执行的 SQL 的预估成本。
不需要重新执行查询，直接返回最近一次查询的优化器预估结果。
数值是优化器在选择执行计划时计算的理论成本（和 EXPLAIN 里的 cost 一致，不是实际执行后的成本）。
用法：
sql
-- 先执行任意查询
SELECT * FROM users WHERE age > 30;

-- 查看该查询的预估成本
SHOW STATUS LIKE 'Last_query_cost';
结果类似：
plaintext
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| Last_query_cost | 10.50000 |
+-----------------+----------+

4. 定位执行慢的 SQL：慢查询日志

4.1 开启慢查询日志参数

1. 开启 slow_query_log

在使用前，我们需要先查下慢查询是否已经开启，使用下面这条命令即可：

mysql > show variables like '%slow_query_log';

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

mysql > set global slow_query_log='ON';

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

你能看到这时慢查询分析已经开启，同时文件保存在 /var/lib/mysql/atguigu02-slow.log 文件中。

2. 修改 long_query_time 阈值

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

mysql > show variables like '%long_query_time%';

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

#测试发现：设置global的方式对当前session的long_query_time失效。对新连接的客户端有效。所以可以一并
执行下述语句
mysql > set global long_query_time = 1;
mysql> show global variables like '%long_query_time%';

mysql> set long_query_time=1;
mysql> show variables like '%long_query_time%';

补充：配置文件中一并设置参数

如下的方式相较于前面的命令行方式，可以看做是永久设置的方式。

修改 my.cnf 文件，[mysqld] 下增加或修改参数 long_query_time、slow_query_log 和 slow_query_log_file 后，然后重启 MySQL 服务器。

[mysqld]
slow_query_log=ON  # 开启慢查询日志开关
slow_query_log_file=/var/lib/mysql/atguigu-low.log  # 慢查询日志的目录和文件名信息
long_query_time=3  # 设置慢查询的阈值为3秒，超出此设定值的SQL即被记录到慢查询日志
log_output=FILE

如果不指定存储路径，慢查询日志默认存储到MySQL数据库的数据文件夹下。如果不指定文件名，默认文件名为hostname_slow.log。

4.2 查看慢查询数目

查询当前系统中有多少条慢查询记录

SHOW GLOBAL STATUS LIKE '%Slow_queries%';

4.3 案例演示

步骤1. 建表

CREATE TABLE `student` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `stuno` INT NOT NULL ,
    `name` VARCHAR(20) DEFAULT NULL,
    `age` INT(3) DEFAULT NULL,
    `classId` INT(11) DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

步骤2：设置参数 log_bin_trust_function_creators

创建函数，假如报错：

This function has none of DETERMINISTIC......

命令开启：允许创建函数设置：

set global log_bin_trust_function_creators=1; # 不加global只是当前窗口有效。

步骤3：创建函数

随机产生字符串：（同上一章）

DELIMITER //
CREATE FUNCTION rand_string(n INT)
	RETURNS VARCHAR(255) #该函数会返回一个字符串
BEGIN
	DECLARE chars_str VARCHAR(100) DEFAULT
'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
	DECLARE return_str VARCHAR(255) DEFAULT '';
    DECLARE i INT DEFAULT 0;
    WHILE i < n DO
    	SET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
    	SET i = i + 1;
    END WHILE;
    RETURN return_str;
END //
DELIMITER ;

# 测试
SELECT rand_string(10);

产生随机数值：（同上一章）

DELIMITER //
CREATE FUNCTION rand_num (from_num INT ,to_num INT) RETURNS INT(11)
BEGIN
    DECLARE i INT DEFAULT 0;
    SET i = FLOOR(from_num +RAND()*(to_num - from_num+1)) ;
    RETURN i;
END //
DELIMITER ;

#测试：
SELECT rand_num(10,100);

步骤4：创建存储过程

DELIMITER //
CREATE PROCEDURE insert_stu1( START INT , max_num INT )
BEGIN
DECLARE i INT DEFAULT 0;
    SET autocommit = 0; #设置手动提交事务
    REPEAT #循环
    SET i = i + 1; #赋值
    INSERT INTO student (stuno, NAME ,age ,classId ) VALUES
    ((START+i),rand_string(6),rand_num(10,100),rand_num(10,1000));
    UNTIL i = max_num
    END REPEAT;
    COMMIT; #提交事务
END //
DELIMITER ;

步骤5：调用存储过程

#调用刚刚写好的函数, 4000000条记录,从100001号开始

CALL insert_stu1(100001,4000000);

4.4 测试及分析

1. 测试

mysql> SELECT * FROM student WHERE stuno = 3455655;
+---------+---------+--------+------+---------+
|   id    |  stuno  |  name  | age  | classId |
+---------+---------+--------+------+---------+
| 3523633 | 3455655 | oQmLUr |  19  |    39   |
+---------+---------+--------+------+---------+
1 row in set (2.09 sec)

mysql> SELECT * FROM student WHERE name = 'oQmLUr';
+---------+---------+--------+------+---------+
|   id    |  stuno  |  name  |  age | classId |
+---------+---------+--------+------+---------+
| 1154002 | 1243200 | OQMlUR | 266  |   28    |
| 1405708 | 1437740 | OQMlUR | 245  |   439   |
| 1748070 | 1680092 | OQMlUR | 240  |   414   |
| 2119892 | 2051914 | oQmLUr | 17   |   32    |
| 2893154 | 2825176 | OQMlUR | 245  |   435   |
| 3523633 | 3455655 | oQmLUr | 19   |   39    |
+---------+---------+--------+------+---------+
6 rows in set (2.39 sec)

从上面的结果可以看出来，查询学生编号为“3455655”的学生信息花费时间为2.09秒。查询学生姓名为 “oQmLUr”的学生信息花费时间为2.39秒。已经达到了秒的数量级，说明目前查询效率是比较低的，下面的小节我们分析一下原因。

2. 分析

show status like 'slow_queries';

4.5 慢查询日志分析工具：mysqldumpslow

在生产环境中，如果要手工分析日志，查找、分析SQL，显然是个体力活，MySQL提供了日志分析工具 mysqldumpslow 。

查看mysqldumpslow的帮助信息

mysqldumpslow --help

mysqldumpslow 命令的具体参数如下：

-a: 不将数字抽象成N，字符串抽象成S
-s: 是表示按照何种方式排序：
- c: 访问次数
- l: 锁定时间
- r: 返回记录
- t: 查询时间
- al:平均锁定时间
- ar:平均返回记录数
- at:平均查询时间（默认方式）
- ac:平均查询次数
-t: 即为返回前面多少条的数据；
-g: 后边搭配一个正则匹配模式，大小写不敏感的；

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

mysqldumpslow -s t -t 5 /var/lib/mysql/atguigu01-slow.log

[root@bogon ~]# mysqldumpslow -s t -t 5 /var/lib/mysql/atguigu01-slow.log

Reading mysql slow query log from /var/lib/mysql/atguigu01-slow.log
Count: 1 Time=2.39s (2s) Lock=0.00s (0s) Rows=13.0 (13), root[root]@localhost
SELECT * FROM student WHERE name = 'S'

Count: 1 Time=2.09s (2s) Lock=0.00s (0s) Rows=2.0 (2), root[root]@localhost
SELECT * FROM student WHERE stuno = N

Died at /usr/bin/mysqldumpslow line 162, <> chunk 2.

工作常用参考：

#得到返回记录集最多的10个SQL
mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log

#得到访问次数最多的10个SQL
mysqldumpslow -s c -t 10 /var/lib/mysql/atguigu-slow.log

#得到按照时间排序的前10条里面含有左连接的查询语句
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/atguigu-slow.log

#另外建议在使用这些命令时结合 | 和more 使用 ，否则有可能出现爆屏情况
mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log | more

4.6 关闭慢查询日志

MySQL服务器停止慢查询日志功能有两种方法：

方式1：永久性方式

[mysqld]
slow_query_log=OFF

或者，把slow_query_log一项注释掉或删除

[mysqld]
#slow_query_log =OFF

重启MySQL服务，执行如下语句查询慢日志功能。

SHOW VARIABLES LIKE '%slow%'; #查询慢查询日志所在目录
SHOW VARIABLES LIKE '%long_query_time%'; #查询超时时长

方式2：临时性方式

使用SET语句来设置。

（1）停止MySQL慢查询日志功能，具体SQL语句如下。

SET GLOBAL slow_query_log=off;

（2）重启MySQL服务，使用SHOW语句查询慢查询日志功能信息，具体SQL语句如下。

SHOW VARIABLES LIKE '%slow%';
#以及
SHOW VARIABLES LIKE '%long_query_time%';

4.7 删除慢查询日志

使用SHOW语句显示慢查询日志信息，具体SQL语句如下。

SHOW VARIABLES LIKE `slow_query_log%`;

从执行结果可以看出，慢查询日志的目录默认为MySQL的数据目录，在该目录下 手动删除慢查询日志文件 即可。

使用命令 mysqladmin flush-logs 来重新生成查询日志文件，具体命令如下，执行完毕会在数据目录下重新生成慢查询日志文件。

mysqladmin -uroot -p flush-logs slow

提示
慢查询日志都是使用mysqladmin flush-logs命令来删除重建的。使用时一定要注意，一旦执行了这个命令，慢查询日志都只存在新的日志文件中，如果需要旧的查询日志，就必须事先备份。
5. 查看 SQL 执行成本：SHOW PROFILE
show profile 在《逻辑架构》章节中讲过，这里作为复习。
show profile 是 MySQL 提供的可以用来分析当前会话中 SQL 都做了什么、执行的资源消耗工具的情况，可用于 sql 调优的测量。默认情况下处于关闭状态，并保存最近15次的运行结果。
我们可以在会话级别开启这个功能。
mysql > show variables like 'profiling';
通过设置 profiling='ON' 来开启 show profile:
mysql > set profiling = 'ON';
然后执行相关的查询语句。接着看下当前会话都有哪些 profiles，使用下面这条命令：
mysql > show profiles;
你能看到当前会话一共有 2 个查询。如果我们想要查看最近一次查询的开销，可以使用：
mysql > show profile;
mysql> show profile cpu,block io for query 2
show profile的常用查询参数：
① ALL：显示所有的开销信息。
② BLOCK IO：显示块IO开销。
③ CONTEXT SWITCHES：上下文切换开销。
④ CPU：显示CPU开销信息。
⑤ IPC：显示发送和接收开销信息。
⑥ MEMORY：显示内存开销信息。
⑦ PAGE FAULTS：显示页面错误开销信息。
⑧ SOURCE：显示和Source_function，Source_file， Source_line相关的开销信息。
⑨ SWAPS：显示交换次数开销信息。
日常开发需注意的结论：
① converting HEAP to MyISAM: 查询结果太大，内存不够，数据往磁盘上搬了。
② Creating tmp table：创建临时表。先拷贝数据到临时表，用完后再删除临时表。
③ Copying to tmp table on disk：把内存中临时表复制到磁盘上，警惕！
④ locked。
如果在show profile诊断结果中出现了以上4条结果中的任何一条，则sql语句需要优化。
注意：
不过SHOW PROFILE命令将被弃用，我们可以从 information_schema 中的 profiling 数据表进行查看。

菜单

分享

1. 调优思路

2. 调优参数

3. 统计SQL的查询成本: last_query_cost

3.1 怎么查看？

3.2 成本值怎么看？

3.3 实际用途举例

3.4 注意点

3.5 `SHOW STATUS LIKE 'Last_query_cost'`

4. 定位执行慢的 SQL：慢查询日志

4.1 开启慢查询日志参数

4.2 查看慢查询数目

4.3 案例演示

4.4 测试及分析

4.5 慢查询日志分析工具：mysqldumpslow

4.6 关闭慢查询日志

4.7 删除慢查询日志

5. 查看 SQL 执行成本：SHOW PROFILE

评论

Mysql语句执行过程

什么是服务限流？

单机下服务限流

Mysql文件系统

Mysql索引递进之路_01

Mysql索引递进之路_02

Mysql索引递进之路_03

Mysql索引的分类

Mysql索引递进之路_06

Mysql索引递进之路_04

分享

查看mysql性能工具

1. 调优思路

2. 调优参数

3. 统计SQL的查询成本: last_query_cost

3.1 怎么查看？

3.2 成本值怎么看？

3.3 实际用途举例

3.4 注意点

3.5 SHOW STATUS LIKE 'Last_query_cost'

4. 定位执行慢的 SQL：慢查询日志

4.1 开启慢查询日志参数

4.2 查看慢查询数目

4.3 案例演示

4.4 测试及分析

4.5 慢查询日志分析工具：mysqldumpslow

4.6 关闭慢查询日志

4.7 删除慢查询日志

5. 查看 SQL 执行成本：SHOW PROFILE

评论

3.5 `SHOW STATUS LIKE 'Last_query_cost'`