mysql性能分析

 查看系统性能参数

在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：删除操作的次数。

例如：

# 慢查询次数
show status like 'Slow_queries'; 

统计SQL的查询成本：last_query_cost

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

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

假设表中有100万同日奥数

如果我们想要查询 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查询是一个动态的过程，从页加载的角度来看，我们可以得到以下两点结论:

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

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

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

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

MySQL的慢查询日志，用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是运行10秒以上(不含10秒)的语句，认为是超出了我们的最大忍耐时间值。|

它的主要作用是，帮助我们发现那些执行时间特别长的SQL查询，并且有针对性地进行优化，从而提高系统的整体效率。当我们的数据库服务器发生阻塞、运行变慢的时候，检查一下慢查询日志，找到那些慢查询，对解决问题很有帮助。比如一条sq|执行超过5秒钟，我们就算慢SQL，希望能收集超过5秒的sql，结合explain进行全面分析。

默认情况下，MySQL数据库没有开启慢查询日志，需要我们手动来设置这个参数。如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响

慢查询日志支持将日志记录写入文件。

一.开启慢查询日志参数

1.开启slow_query_log

mysql > show variables like '%slow_query_log%';
mysql > set global slow_query_log='ON';

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

 

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

2. 修改long_query_time阈值

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

mysql > show variables like '%long_query_time%';

# 测试发现：设置global的方式对当前session的long_query_time失效。对新连接的客户端有效。所以可以一并执行下述语句
mysql> set global long_query_time = 1 ;
mysql> show global variables like ' %long-query_time% ';
 
# 即更改global 也更改了session变量
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/my-slow.log #慢查询日志的目录和文件名信息
long_query_time=3 #设置慢查询的阈值为3秒，超出此设定值的SQL即被记录到慢查询日志
log_output=FILE

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

二.查看慢查询数目

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

SHOW GLOBAL STATUS LIKE '%Slow_queries%';

三.测试及分析

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';

有2条慢sql 

 

 

补充说明:

show variables like 'min%';

除了上述变量，控制慢查询日志的还有一个系统变量: min_examined_row_limit。这个变量的意思是，查询扫描过的最少记录数。这个变量和查询执行时间，共同组成了判别一个查询是否是慢查询的条件。如果查询扫描过的记录数大于等于这个变量的值，并且查询执行时间超过long_query_time的值，那么，这个查询就被记录到慢查询日志中; 反之，则不被记录到慢查询日志中。

mysql> show variables like 'min%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| min_examined_row_limit | 0     |
+------------------------+-------+
1 row in set (0.00 sec)

你也可以根据需要，通过修改“my.ini"文件，来修改"min_examined_row_limit”的值。

 

四.慢查询日志分析工具：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

五. 关闭慢查询日志

除了调优需要开，正常还是不要开了

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

方式1：永久性方式

[mysqld]
slow_query_log=OFF

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

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

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

 方式2：临时性方式

使用SET语句来设置。 （1）停止MySQL慢查询日志功能，具体SQL语句如下。

SET GLOBAL slow_query_log=off;

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

六. 删除慢查询日志

mysql> show variables like '%slow_query_log%';
+---------------------+----------------------------+
| Variable_name       | Value                      |
+---------------------+----------------------------+
| slow_query_log      | ON                         |
| slow_query_log_file | /var/lib/mysql/my-slow.log |
+---------------------+----------------------------+
2 rows in set (0.07 se

从执行结果可以看出，慢查询日志的目录默认为MySQL的数据目录，在该目录下手动删除慢查询日志文件即可。使用命令mysqladmin flush-logs 来重新生成查询日志文件，具体命令如下，执行完毕会在数据目录下重新生成慢查询日志文件。

# 不使用这个命令，没办法自己创建
mysqladmin -uroot -p flush-logs slow 
 
## 这个命令后输入密码
 
## 这个命令可以重置其他日志 例如undo日志

slow  :只重置 慢查询日志

提示

慢查询日志都是使用mysqladmin flush-logs命令来删除重建的。使用时-定要注意，一旦执行了这个命令，慢 查询日志都只存在新的日志文件中，如果需要旧的查询日志，就必须事先备份。