13.s日志查询

mysql慢查询

• 慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中相应时间超过时间阈值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。
• 具体指运行时间超过long_query_time值的SQL。则会被记录到慢查询日志中，long_query_time的默认值为10(s)，
• 默认MySQL数据库没有开启慢查询日志

SHOW VARIABLES LIKE '%slow_query_log%'
#如果要永久开启，就要修改配置文件my.cnf
set global slow_query_log=1;
#设置慢的阈值时间
set global long_query_time=3;
#需要重新连接或新开一个会话才能看到修改值

#查看慢查询语句的条数
show global status like '%Slow_queries%';

日志分析工具mysqldumpslow

s:按照何种方式排序

c:访问次数

l:锁定时间

r:返回记录

t:查询时间

al:平均锁定时间

ar:平均返回记录数

at:平均查询时间

t:返回前面多少条数据

g:后面搭配一个正则匹配模式，大小写不敏感

#得到访问记录集最多的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
#建议把这些命令结合 | 和more使用，
mysqldumpslow -s c -t 10 /var/lib/mysql/atguigu-slow.log | more

批量数据脚本

插入1000W条数据

1. 建表

2. 设置参数

#由于开启过慢查询日志，因为开启了bin-log，我们就必须为我们function指定一个参数
show variables like 'log_bin_trust_function_creators';
set global log_bin_trust_function_creators=1;

3.创建参数，保证每条数据不相同

• 随机产生字符串
• 随机产生编号

DELIMITER $$
CREATE FUNCTION rand_string(n INT) RETURN VARCHAR(255)
BEGIN
	DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyz'
	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()*26),1));
	SET i=i+1;
	END WHILE;
	RETURN return_str;
END $$
#该变结束符为$$

DELIMITER $$
CREATE FUNCTION rand_num() RETURN INT(5)
BEGIN
	DECLARE i INT DEFAULT 0;
	SET i = FLOOR(100+RAND()*10);
END $$

4.创建存储过程

DELIMITER $$
CREATE PROCEDURE insert_dept(IN START INT(10),IN max_num INT(10))
BEGIN
	DECLARE i INT DEFAULT 0;
	#autocommit设置为0
	SET autocommit = 0;
	REPEAT
	SET i = i+1;
	INSERT INTO emp(deptno,dname,job,loc) VALUES ((START+i),rand_string(10),rand_string(8));
	UNTIL i = max_num
	END REPEAT;
	COMMIT;
END $$

5.调用存储过程

DELIMITER ;
CALL insert_dept(100,10);

一、含义不同

1、存储过程：du存储过程是 SQL 语句和可选控制流语句的预编译集合，以一个名称存储并作为一个单元处理。

2、函数：是由一个或多个 SQL 语句组成的子程序，可用于封装代码以便重新使用。 函数限制比较多，如不能用临时表，只能用表变量等

二、使用条件不同

1、存储过程：可以在单个存储过程中执行一系列 SQL 语句。而且可以从自己的存储过程内引用其它存储过程，这可以简化一系列复杂语句。

2、函数：自定义函数诸多限制，有许多语句不能使用，许多功能不能实现。函数可以直接引用返回值，用表变量返回记录集。但是，用户定义函数不能用于执行一组修改全局数据库状态的操作。

三、执行方式不同

1、存储过程：存储过程可以返回参数，如记录集（记录集本身是从指定数据库中检索到的数据的集合），函数只能返回值或者表对象。存储过程的参数有in，out，inout三种，存储过程声明时不需要返回类型。

2、函数：函数参数只有in，而函数需要描述返回类型，且函数中必须包含一个有效的return语句。

Show Profile

频繁IO / 运算复杂

1. 开启功能

show variables like 'profiling'
set profiling=on;

2. 运行SQL

select * from emp group by id%10 limit 150000
select * from emp group by id%20 order by 5

2. 看结果

show profiles

2. 诊断SQL，show profile cpu（处理器运算）,block io（IO） for query
3. 日常开发结论

• converting HEAP to MyISAM 查询结果太大，内存不够大
• creating tmp table 创建临时表
• Copying to tmp table on disk 把内存中临时表复制到磁盘，危险！
• locked

全局查询日志

#只允许在测试环境使用

set global general_log=1;
#在此处分析记录的mysql语句
select * from mysql.general_log