【SQL】之性能分析|慢查询|explain

数据库服务器的优化步骤

整个流程划分成了观察（Show status） 和行动（Action） 两个部分。字母 S 的部分代表观察（会使用相应的分析工具），字母 A 代表的部分是行动（对应分析可以采取的行动）。

查看系统性能参数：

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

统计sql查询成本：last_query_cost

last_query_cost
成本：sql语句所需要读取的页的数量

页在数据缓冲池中查询效率最高，否则还要从内存或磁盘中读取。所以说，经常访问的数据要放在缓冲池中。
如果从磁盘中对单一页面进行随机读，效率很低，而用顺序读取方式，批量对页进行读取，效率更高。

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

long_query_time默认值为10，即运行10秒以上的语句，认为超出了最大忍耐时间值。慢查询日志默认不开启。除非调优需要，不建议开启，会影响性能。
SHOW VARIABLES LIKE 'slow_query_log%'; 查看慢查询日志开启状态
查看慢查询时间阈值：SHOW VARIABLES LIKE '%long_query_time%'; 默认10s。
修改：
SET GLOBAL long_query_time = 1;
set long_query_time=1;
查看：
SHOW GLOBAL VARIABLES LIKE '%long_query_time%';
SHOW VARIABLES LIKE '%long_query_time%';

查看慢查询数目

SHOW STATUS LIKE 'slow_queries';
数据准备：

SHOW VARIABLES LIKE '%slow_query_log';
SHOW VARIABLES LIKE '%long_query_time%';
SET GLOBAL long_query_time = 1;
SHOW GLOBAL VARIABLES LIKE '%long_query_time%';

SET long_query_time=1;
SHOW VARIABLES LIKE '%long_query_time%';

USE atguigudb1;

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;

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 COUNT(*) FROM student;

SELECT * FROM student WHERE stuno = 3453451; #3.749s

SELECT * FROM student WHERE NAME = 'Hlkqad'; #2.494s

SHOW STATUS LIKE 'slow_queries';	#4

日志分析工具：mysqldumpslow
在Linux中，使用mysqldumpslow：
mysqldumpslow --help 查看帮助信息
按照查询时间排序，查看前五条 SQL 语句：mysqldumpslow -s t -t 5 /var/lib/mysql/xxx（主机名）-slow.log （慢查询日志所在位置）

删除慢查询日志：rm hadoop1-slow.log

查看 SQL 执行成本：SHOW PROFILE

show profile的常用查询参数：
① ALL：显示所有的开销信息。
② BLOCK IO：显示块IO开销。
③ CONTEXT SWITCHES：上下文切换开销。
④ CPU：显示CPU开销信息。
⑤ IPC：显示发送和接收开销信息。
⑥ MEMORY：显示内存开销信息。
⑦ PAGE FAULTS：显示页面错误开销信息。
⑧ SOURCE：显示和Source_function，Source_file，Source_line相关的开销信息。
⑨ SWAPS：显示交换次数开销信息。

分析查询语句：explain

查看语句的执行计划

数据准备：

USE atguigudb1;
CREATE TABLE s1 (
id INT AUTO_INCREMENT,
key1 VARCHAR(100),
key2