当前位置：首页 > article >正文

MySQL性能分析工具的使用

article 2024/11/23 10:35:55

1.查看系统性能参数

2.统计SQL的查询成本：last_query_cost

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

3.1开启慢查询日志参数

1.开启slow_query_log

2. 修改long_query_time阈值

3.2 查看慢查询数目

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

5.分析查询语句：EXPLAIN

1.查看系统性能参数

可以使用 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：删除操作的次数。

例：

（1）查询MySQL服务器的连接次数

 show status like 'Connections';
/*
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Connections   | 44    |
+---------------+-------+
*/

（2）查询MySQL服务器的慢查询次数

SHOW STATUS LIKE 'slow_queries' ;
/*
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries  | 0     |
+---------------+-------+
*/

慢查询次数参数可以结合慢查询日志找出慢查询语句，然后针对慢查询语句进行表结构优化或者查询语句优化。再比如，如下的指令可以查看相关的指令情况:

SHOW STATUS LIKE 'Innodb_rows_%';
/*
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Innodb_rows_deleted  | 0     |
| Innodb_rows_inserted | 224   |
| Innodb_rows_read     | 214   |
| Innodb_rows_updated  | 0     |
+----------------------+-------+
*/

2.统计SQL的查询成本：last_query_cost

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

SELECT student_id, class_id, NAME, create_time FROM student_info
WHERE id = 900001;
#运行结果（1 条记录，运行时间为 0.042s ）

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

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 个页的查询

 SHOW STATUS LIKE 'last_query_cost';
 /*
+-----------------+-----------+
| Variable_name | Value       |
+-----------------+-----------+
| Last_query_cost | 21.134453 |
+-----------------+-----------+
*/

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

结论:

1.位置决定效率。如果页就在数据库缓冲池中，那么效率是最高的，否则还需要从内存或者磁盘中进行读取，当然针对单个页的读取来说，如果页存在于内存中，会比在磁盘中读取效率高很多。

2.批量决定效率。如果从磁盘中对单一页进行随机读，那么效率是很低的（差不多10ms)，而采用顺序读取的方式，批主对页进行读取，平均一页的读取效率就会提升很多，甚至要快于单个页面在内存中的随机读取。

所以说，遇到I/O并不用担心，方法找对了，效率还是很高的。首先要考虑数据存放的位置，如果是经常使用的数据就要尽量放到缓冲池中，其次可以充分利用磁盘的吞吐能力，一次性批量读取数据，这样单个页的读取效率也就得到了提升。

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

3.1开启慢查询日志参数

1.开启slow_query_log

show variables like 'slow_query_log';
/*
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| slow_query_log | OFF   |
+----------------+-------+
*/

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

set global slow_query_log='ON';

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

show variables like '%slow_query_log%';

+---------------------+-----------------------------------------+
| Variable_name       | Value                                   |
+---------------------+-----------------------------------------+
| slow_query_log      | ON                                      |
| slow_query_log_file | /var/lib/mysql/centos7-mysql-1-slow.log |
+---------------------+-----------------------------------------+
2 rows in set (0.00 sec)

2. 修改long_query_time阈值

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

show variables like '%long_query_time%';
/*
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
*/

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

set global long_query_time = 1;
show global 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-slow.log#慢查询日志的目录和文件名信息
long_query_time=3 #设置慢查询的阈值为3秒。超出此设定值的SQL即被记录到慢查询日志
log_output=FTLE

3.2 查看慢查询数目

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

SHOW GLOBAL STATUS LIKE '%Slow_queries%';
/*
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries  | 0     |
+---------------+-------+
*/

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

Show Profile是MySQL提供的可以用来分析当前会话中SQL都做了什么、执行的资源消耗情况的工具，可用于sql调优的测量。默认情况下处于关闭状态，并保存最近15次的运行结果。

可以在会话级别开启这个功能

show variables like 'profiling';
/*
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling     | OFF   |
+---------------+-------+
*/

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

set profiling = 'ON';

show profile的常用查询参数:

ALL:显示所有的开销信息。
BLOCK IO:显示块IO开销。
CONTEXT SWITCHES:上下文切换开销。
CPU:显示CPU开销信息。
IPC:显示发送和接收开销信息
MEMORY:显示内存开销信息。
PAGE FAULTS:显示页面错误开销信息。
SOURCE:显示和Source_function，Source_file，Source_line相关的开销信息。
SWAPS:显示交换次数开销信息。

show profile使用演示：

select * from student where stuno =343455;
--

select * from student where name = 'vyituS';
--

show profiles;

/*
+----------+------------+---------------------------------------------+
| Query_ID | Duration   | Query                                       |
+----------+------------+---------------------------------------------+
|        1 | 0.00174700 | show variables like 'profiling'             |
|        2 | 1.52700950 | select * from student where stuno =343455   |
|        3 | 1.20279475 | select * from student where name = 'vyituS' |
+----------+------------+---------------------------------------------+
3 rows in set, 1 warning (0.00 sec)
*/


show profile;

/*
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000083 |
| Executing hook on transaction  | 0.000004 |
| starting                       | 0.000009 |
| checking permissions           | 0.000006 |
| Opening tables                 | 0.000044 |
| init                           | 0.000004 |
| System lock                    | 0.000008 |
| optimizing                     | 0.000008 |
| statistics                     | 0.000019 |
| preparing                      | 0.000018 |
| executing                      | 1.202507 |
| end                            | 0.000024 |
| query end                      | 0.000005 |
| waiting for handler commit     | 0.000010 |
| closing tables                 | 0.000012 |
| freeing items                  | 0.000023 |
| cleaning up                    | 0.000013 |
+--------------------------------+----------+
17 rows in set, 1 warning (0.00 sec)

*/

mysql> show profile cpu, block io for query 2;

+--------------------------------+----------+----------+------------+--------------+---------------+
| Status                         | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+--------------------------------+----------+----------+------------+--------------+---------------+
| starting                       | 0.000111 | 0.000066 |   0.000039 |            0 |             0 |
| Executing hook on transaction  | 0.000006 | 0.000002 |   0.000001 |            0 |             0 |
| starting                       | 0.000009 | 0.000005 |   0.000003 |            0 |             0 |
| checking permissions           | 0.000006 | 0.000004 |   0.000003 |            0 |             0 |
| Opening tables                 | 0.000044 | 0.000028 |   0.000016 |            0 |             0 |
| init                           | 0.000004 | 0.000002 |   0.000002 |            0 |             0 |
| System lock                    | 0.000008 | 0.000005 |   0.000002 |            0 |             0 |
| optimizing                     | 0.000009 | 0.000006 |   0.000004 |            0 |             0 |
| statistics                     | 0.000057 | 0.000036 |   0.000022 |            0 |             0 |
| preparing                      | 0.000023 | 0.000013 |   0.000008 |            0 |             0 |
| executing                      | 1.526632 | 1.208456 |   0.519889 |       361248 |             0 |
| end                            | 0.000021 | 0.000008 |   0.000005 |            0 |             0 |
| query end                      | 0.000005 | 0.000003 |   0.000001 |            0 |             0 |
| waiting for handler commit     | 0.000012 | 0.000008 |   0.000005 |            0 |             0 |
| closing tables                 | 0.000013 | 0.000008 |   0.000004 |            0 |             0 |
| freeing items                  | 0.000037 | 0.000024 |   0.000014 |            0 |             0 |
| cleaning up                    | 0.000016 | 0.000009 |   0.000006 |            0 |             0 |
+--------------------------------+----------+----------+------------+--------------+---------------+
17 rows in set, 1 warning (0.00 sec)

5.分析查询语句：EXPLAIN

EXPLAIN 或 DESCRIBE语句的语法形式如下：

EXPLAIN SELECT select_options
#或者
DESCRIBE SELECT select_options

如果我们想看看某个查询的执行计划的话，可以在具体的查询语句前边加一个 EXPLAIN ，就像这样：

EXPLAIN SELECT 1;
/*
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | No tables used |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
*/

注意: 执行EXPLAIN时并没有真正的执行该后面的语句，因此可以安全的查看执行计划。

EXPLAIN语句输出的各个列的作用如下：

列名	描述
id	在一个大的查询语句中每个SELECT关键字都对应一个 `唯一的id`
select_type	SELECT关键字对应的那个查询的类型
table	表名
partitions	匹配的分区信息
type	针对单表的访问方法
possible_keys	可能用到的索引
key	实际上使用的索引
key_len	实际使用到的索引长度(单位：字节)
ref	当使用索引列等值查询时，与索引列进行等值匹配的对象信息
rows	预估的需要读取的记录条数
filtered	某个表经过搜索条件过滤后剩余记录条数的百分比
Extra	一些额外的信息