SQL优化之SQL排查与数据分析

SQL排查

慢查询日志

	用于记录MySQL提供的一种日志记录，用于记录MySQL中响应时间超过阀值的SQL语句
	
	阀值：long_query_time：(default)10s
	(十秒没查完就会被定义为一条慢SQL)
	
	慢查询日志默认是关闭（开发调优打开，部署关闭）

开启慢查询日志

检查是否开启了慢查询日志命令

	show variables like '%slow_query_log%';

临时开启（在内存中开启）

set global slow_query_log = 1 ;  --在内存种开启
exit  -- 退出到终端
service mysql restart  -- 重启服务就会关闭

长久开启

		/etc/my.cnf # 打开这个配置文件，追加以下配置
		vi /etc/my.cnf # 打开文件
		[mysqld]
		slow_query_log=1
		slow_query_log_file=/var/lib/mysql/localhost-slow.log

修改慢查询阀值

查询阀值状态

show variables like '%long_query_time%' ;

设置临时阀值

设置完重新登录生效，不需要重启服务

set global long_query_time = 5 ;-- 表示5秒未完成是慢查询
 --设置完毕后，重新登陆后起效 （不需要重启服务）

设置永久阀值

		/etc/my.cnf #中追加配置：
		vi /etc/my.cnf 
		[mysqld]
		long_query_time=3

测试慢查询日志

		select sleep(4);-- 可以看出我已经将阀值设置为3，这个语句就是慢查询
		show global status like '%slow_queries%';--查询超过阀值的SQL

 慢查询执行完成，再看看这个有没有记录这条慢查询

那么我需要具体看看是那条语句呢？

可以这样，打开慢查询日志文件（当然是在终端上）

	cat /var/lib/mysql/localhost-slow.log  # 先前我们咱们在上面声明了一个路径，现在我们看看

是不是我们之前写的那个垃圾SQL.

但是这样很麻烦，那么有什么工具嘛？(了解)

mysqldumpslow(MySQL自带工具)

常见命令

	mysqldumpslow --help-- 看所有参数
	-s-- 排序方式
	-r-- 逆序
	-l-- 锁定时间
	-g-- 正则匹配模式	
	--获取返回记录最多的3个SQL
		mysqldumpslow -s r -t 3  /var/lib/mysql/localhost-slow.log

	--获取访问次数最多的3个SQL
		mysqldumpslow -s c -t 3 /var/lib/mysql/localhost-slow.log

	--按照时间排序，前10条包含left join查询语句的SQL
		mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/localhost-slow.log
	
	语法：
		mysqldumpslow 各种参数  慢查询日志的文件

海量数据分析

模拟数据准备

存储过程与存储函数

1. 存储过程（无retrun）===>本案例用于插入数据
2. 存储函数 （有return）====>本案例用于存储数据代码

create database testdata ;
use testdata
create table dept
(
dno int(5) primary key default 0,
dname varchar(20) not null default '',
loc varchar(30) default ''
)engine=innodb default charset=utf8;

create table emp
(
eid int(5) primary key,
ename varchar(20) not null default '',
job varchar(20) not null default '',
deptno int(5) not null default 0
)engine=innodb default charset=utf8;

---------------------------------------------------
-- 通过存储函数 插入海量数据：
--	创建存储函数：
-- randstring(6)  ->aXiayx  用于模拟员工名称
-- $表示结束符号的替换，默认是;
delimiter $ 
	create function randstring(n int)   returns varchar(255) 
	begin
		declare  all_str varchar(100) default 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ' ;
		declare return_str varchar(255) default '' ;
		declare i int default 0 ; 
		while i<n		 
		do									
			set return_str = concat(  return_str,      substring(all_str,   FLOOR(1+rand()*52)   ,1)       );
			set i=i+1 ;
		end while ;
		return return_str;
		
	end $ 

--------------------------------------------------
--产生随机整数
	create function ran_num() returns int(5)
	begin
		declare i int default 0;
		set i =floor( rand()*100 ) ;
		return i ;

	end $
	


	--通过存储过程插入海量数据：emp表中  ，  10000,   100000
	create procedure insert_emp( in eid_start int(10),in data_times int(10))
	begin 
		declare i int default 0;
		set autocommit = 0 ;
		
		repeat
			
			insert into emp values(eid_start + i, randstring(5) ,'other' ,ran_num()) ;
			set i=i+1 ;
			until i=data_times
		end repeat ;
		commit ;
	end $


	--通过存储过程插入海量数据：dept表中  
		create procedure insert_dept(in dno_start int(10) ,in data_times int(10))
		begin
			declare i int default 0;
			set autocommit = 0 ;
			repeat
			
				insert into dept values(dno_start+i ,randstring(6),randstring(8)) ;
				set i=i+1 ;
				until i=data_times
			end repeat ;
		commit ;
			

		end$
---------------------------------------------
--插入数据
		delimiter ; 
		call insert_emp(1000,800000) ;
		call insert_dept(10,30) ;

异常分析
1.如果报错：You have an error in your SQL syntax，说明SQL语句语法有错，需要修改SQL语句；
2. 如果报错This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)
产生原因： 存储过程/存储函数在创建时 与之前的 开启慢查询日志冲突了 

异常2的冲突解决方案

临时解决（开启log_bin_trust_function_creators ）

show variables like '%log_bin_trust_function_creators%';  --查看二进制日志是否开启
set global log_bin_trust_function_creators = 1;-- 开启 二进制日志文件

长久解决

/etc/my.cnf 
[mysqld]
log_bin_trust_function_creators = 1

分析海量数据

方案1：profiles

show profiles ; --默认关闭
	show variables like '%profiling%';
	set profiling = on ; 
	show profiles ;--会记录所有profiling打开之后的  全部SQL查询语句所花费的时间。
			--缺点：不够精确，只能看到 总共消费的时间，不能看到各个硬件消费的时间（cpu  io ）

方案2：SQL诊断（精确分析）

-- 上一步查询的的Query_Id
show profile all for query 
-- 上一步查询的的Query_Id
show profile cpu,block io for query

方案3：全局日志查询

记录开启之后的 全部SQL语句。 
（这次全局的记录操作 仅仅在调优、开发过程中打开即可，在最终的部署实施时 一定关闭）

show variables like '%general_log%';--查看全局日志
--执行的所有SQL记录在表中
		set global general_log = 1 ;--开启全局日志
		set global log_output='table' ; --设置 将全部的SQL 记录在表中

		--执行的所有SQL记录在文件中
		set global log_output='file' ;
		set global general_log = on ;
		set global general_log_file='/tmp/general.log' ;
		--开启后，会记录所有SQL ： 会被记录 mysql.general_log表中。
		select * from  mysql.general_log ;