mysql高级---查询截取分析、慢查询日志、show profiles

查询截取分析

1. 慢查询的开启并捕获
2. explain和慢sql分析
3. show profile 查询sql在mysql服务器里面的执行细节和生命周期情况。
4. sql数据库服务器的参数调优

慢查询日志

介绍

Mysql提供的一种日志记录，用来记录mysql中响应时间超过阈值的语句，具体指运行时间超过Long_query_time值的sql语句，会被记录到慢查询日志中。
Long_query_time默认为10s。借此观察哪些语句超出了忍耐值。

使用

默认情况下，mysql数据库没有开启慢查询日志，需要手动开启并设置。如果不需要，一般不建议启动该参数，多少会对性能有影响。

查看慢查询日志

show variables like '%slow_query_log%';

开启慢查询日志

set global slow_query_log =1;

只对此次连接有效，重启后失效。

查看long_query_time

show variables like 'long_query_time%';

设置long_query_time

set global long_query_time = 3;
设置后没有效果，需要重新启动或新开一个对话才有效果。

重新连接后：

long_query_time通过判断运行时间大于此值的情况，不是大于等于！

查看慢日志

cat 慢查询日志地址

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

show global status like '%slow_queries%';

Mysqldumpslow

Mysql的日志分析工具

查看使用帮助

Mysqldumpslow --help

s:按照何种方式排序
c:访问次数
l:锁定时间
r:返回记录
t:查询时间
al:平均锁定时间
ar:平均返回记录数
at:平均查询时间

#得到返回记录集最多的10个sql
mysqldumpslow -s -r -t 10 /var/run/mysqld/mysqld-slow.log
#得到访问次数最多的10个sql
mysqldumpslow -s c -t 10 /var/run/mysqld/mysqld-slow.log
#得到按照时间排序的前10条里含有左连接的查询语句
mysqldumpslow -s t -t 10 -g "left join" /var/run/mysqld/mysqld-slow.log

最好结合more使用

批量插入数据脚本

建表

CREATE TABLE games(
	id int PRIMARY KEY auto_increment,
	name VARCHAR(20),
	author_id INT
);
CREATE TABLE authorss(
	id int PRIMARY KEY auto_increment,
	name VARCHAR(20),
	age int,
	sex varchar(10)
);

创建函数

#随机产生名	
delimiter $
CREATE FUNCTION rand_name(charstr VARCHAR(20),n int) RETURNs VARCHAR(100)
BEGIN
	DECLARE restr VARCHAR(100) DEFAULT '';
	DECLARE i INT DEFAULT 0 ;
	WHILE i < n DO
		SET restr = CONCAT(charstr,i+1);
		SET i = i+1;
	END WHILE;
	RETURN restr;
END $

创建存储过程

#插入游戏名
delimiter $
create PROCEDURE insert_game(IN START INt(10), IN max_num INT(10))
BEGIN 
	DECLARE i INT DEFAULT 0;
	SET autocommit = 0;
	REPEAT
		SET i = i+1;
		INSERT INTO games(name,author_id) VALUES(rand_name('rim',max_num),(START+i));
	UNTIL i = max_num
	END REPEAT;
	COMMIT;
END $
#插入作者
create PROCEDURE insert_atr(IN max_num INT(10))
BEGIN 
	DECLARE i INT DEFAULT 0;
	SET autocommit = 0;
	REPEAT
		SET i = i+1;
		INSERT INTO authorss(name,age,sex) VALUES(rand_name('xh',max_num),(18+i),'女');
	UNTIL i = max_num
	END REPEAT;
	COMMIT;
END $
call insert_game(5,10)$
CALL insert_atr(10)$

仅为案例，结果有点问题。

创建函数时如果报错

如果报错：This function has none of DETERMINISTIC…
由于开启过慢查询日志，开启了bin_log，必须为函数指定一个参数

show variables like 'log_bin_trust_function_creators';

set global log_bin_trust_function_creators = 1;

不能跨重启，永久方法：

Show profile

介绍

可以用来分析当前会话中语句执行的资源消耗情况。可以用于sql调优的测量.
默认情况下，参数处于关闭状态，保存最近15次的运行结果。

分析步骤

查看当前mysql版本是否支持

 show variables like 'profiling';

开启

set profiling = on ;

运行sql语句

查看结果

show profiles;

诊断sql

show profile cpu,block io for query 语句编号;

注意事项

• Converting HEAP to MyISAM 查询结果太大，内存不够用，使用磁盘。
• Creating tmp table 拷贝数据到临时表/用完再删除
• Copying to tmp table on disk 把内存中临时表复制到磁盘 危险
• Locked

全局查询日志

不要在生产环境启用！

配置启用

编码启用

set global general_log =1;
set global log_output = 'TABLE';

此后，你所编写的sql语句，将会记录到mysql库里的general_log表

#查看general_log表
select * from mysql.general_log;