MySQL性能分析工具的使用
在数据库调优种，我们的目标就是响应时间更快，吞吐量更大。利用宏观的监控工具和微观的日志分析可以帮我们快速找到调优的思路或方法。
一、数据库服务器的优化步骤
二、查看系统性能参数
在MySQL种，可以使用show status 语句查询一些MySQL数据库服务器的性能参数和执行频率。
show status 的语法如下：
show status [global | session] status like '参数'
一些常用的性能参数：
1、Connections：连接MySQL服务器的次数
2、Uptime：服务器上线的时间
3、Slow_queries：慢查询次数
4、Innodb_rows_read：select查询返回的行数
5、Innodb_rows_inserted：执行insert操作插入的行数
6、Innodb_rows_updated：执行update操作更新的行数
7、Innodb_ros_deleted：执行delete操作删除的行数
8、Com_select：查询操作的次数
9、Com_insert：插入操作的次数
10、Com_update：更新操作的次数
11、Com_delete：删除操作的次数
三、统计SQL的查询成本：last_query_cost
如果我们要想查看某条SQL的查询成本，可以在执行完该SQL之后，通过查看当前会话中的last_query_cost变量来得到他的查询成本。它通常也是我们评价一个查询执行效率的常用指标，这个查询成本对应的值是指SQL语句所需要读取的数据页数量。
show status like 'last_query_cost'
四、定位执行慢的SQL：慢查询日志
MySQL的慢查询日志，用来记录在MysQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是运行10秒以上(不含10秒)的语句，认为是超出了我们的最大忍耐时间值。
它的主要作用是，帮助我们发现那些执行时间特别长的sQL查询，并且有针对性地进行优化，从而提高系统的整体效率。当我们的数据库服务器发生阻塞、运行变慢的时候，检查一下慢查询日志，找到那些慢查询，对解决问题很有帮助。比如一条sql执行超过5秒钟，我们就算慢SQL，希望能收集超过5秒的sql，结合explain进行全面分析。
默认情况下，MySQL数据库没有开启慢查询日志，需要我们手动来设置这个参数。如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询由志会或多或少带来一定的性能影响。
慢查询日志支持将日志记录写入文件。
4.1、开启慢查询日志参数
1开启slow_query_log
在使用之前，需要先查看一下慢查询是否已经开启，执行一下命令：
show variables like 'slow_query_log'；
set global slow_query_log = on;
2、查看一下慢查询日志文件的位置
3、查询long_query_time的阈值，并修改指定的阈值
long_query_time 既是一个全局变量又是一个会话变量，所以需要同时设置,若只设置全局，则需新建会话才能生效。
show global variables like 'long_query_time'; #全局 show variables like 'long_query_time'; #会话 set global long_query_time = 1; #全局 set long_query_time = 1; #会话
补充：上面的操作只是对本次MySQL服务器有效，若服务器重启，则会失效。若想永久有效，需要修改MySQL的配置文件my.cnf
[mysqld] slow_query_log=ON #开启慢查询日志的开关 slow_query_log_file=/var/lib/mysql/localhost-slow.log #慢查询日志的目录和文件名信息 long_query_time=3 #设置慢查询的阙值为3秒，超出此设定值的SQL即被记录到慢查询日志 log_output=FILE
4.2、查询当前慢查询SQL的数量
show status like 'slow_queries';
4.3、慢查询测试及分析
执行以下SQL语句，创建student表以及插入student数据（400万条），用来测试。
#建表
CREATE TABLE `student`(
`id` INT 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;
SELECT @@log_bin_trust_function_creators;
SET GLOBAL log_bin_trust_function_creators = 1;
#函数1：创建随机产生字符串函数
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 ;
#函数2：创建随机数函数
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 ;
#创建存储过程
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 ;
#调用存储过程
CALL insert_stu1(100001,4000000);
执行一个查询，查看慢查询数量
select * from student where stuno = 3453451; select * from student where stuno = 3453451;
补充：控制慢查询日志的还有另一个变量：min_examined_row_limit。这个变量指的是查询扫描过的最少记录数。这个变量和查询执行时间共同判定一个查询是否为一个慢查询。如果查询到的记录数大于等于该变量值且执行时间查过了long_query_time，则这条SQL查询就会记录在日志文件中。
4.4、慢查询日志分析工具 mysqldumpslow
1、查看一下 mysqldumpslow的帮助信息
2、进入到日志文件目录下
cd /var/lib/mysql
3、根据上面的帮助信息，查询日志
语法：mysqldumpslow [options...] 日志文件路径
①根据查询时间排序并返回前几条数据
mysqldumpslow -s t -t 10 /var/lib/mysql/localhost-slow.log
通过 mysqldumpslow 找到哪条SQL执行过慢,之后可通过explain分析执行计划。（另开一篇详解EXPLAIN）
4.5、关闭慢查询日志
方式一：临时性关闭
set global slow_query_log = off;
执行以上语句后，重启服务器即可。
方式二：永久性关闭
修改MySQL的配置文件my.cnf，将slow_query_log的值改为off,保存后，重启MySQL服务器即可
重启服务器命令：systemctl restart mysqld;
