SQL排查、分析海量数据以及锁机制

1. SQL排查

1.1 慢查询日志: mysql提供的一种日志记录, 用户记录MySQL中响应时间超过阈值的SQL语句(long_query_time, 默认10秒), 慢查询日志默认是关闭的, 建议开发调优时打开, 最终部署的时候关闭

1.1.1 检查是否开启了慢查询日志

show variables like '%slow_query_log%';

临时开启:

set global slow_query_log = 1; -- 在内存中开启
exit;
service mysql restart

永久开启:

vim /etc/my.cnf -- 追加配置
[mysqld]
slow_query_log=1
slow_query_log_file=/var/lib/mysql/localhost-slow.log

1.1.2 慢查询阈值

show variables like '%long_query_time%'; -- 查看
-- 临时设置阈值
set global long_query_time = 3; 
永久设置阈值：
vim /etc/my.cnf 中追加配置 
[mysqld]
long_query_time=3

-- 查询
select sleep(4);
select sleep(3);
-- 查询超过阈值的SQL
show global status like '%slow_queries%';
-- 慢查询的sql被记录在了日志中,因此可以通过日志查看具体的慢SQL
cat /data/mysql/zizhou-slow.log 

9.2 通过mysqldumpslow工具查看慢SQL,可以通过一些过滤条件,快速查找需要定位的慢SQL

mysqldumpslow --help
-- 获取返回记录最多的3个SQL
mysqldumpslow -s r -t 3 /data/mysql/zizhou-slow.log
-- 获取访问次数最多的3个SQL
mysqldumpslow -s c -t 3 /data/mysql/zizhou-slow.log
-- 按照时间排序, 前10条包含left join 查询语句的SQL
mysqldumpslow -s t -t 10 -g "left join" /data/mysql/zizhou-slow.log
-- 语法
mysqldumpslow 各种参数 慢查询日志的文件

mysqldumpslow命令的帮助:

2. 分析海量数据

2.1 模拟海量数据, 存储过程(无return)/存储函数(有return)

create database test_data;
use test_data

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

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=utf8mb4;

2.1.1 通过存储函数,插入海量数据

-- 创建存储函数
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 $ 
	
delimiter $ 
	create function ran_num() returns int(5)
	begin
		declare i int default 0;
		set i =floor( rand()*100 ) ;
		return i ;

	end $

2.1.2 通过存储过程,插入海量数据

-- emp表
delimiter $ 
	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表
delimiter $ 
	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$

2.1.3 插入数据

delimiter ; 
call insert_emp(1000,800000) ;
call insert_dept(10,30) ;

2.1.4 问题解决

如果报错: 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)
是因为存储过程/存储函数在创建时, 与之前开启的慢查询日志冲突了
解决冲突:

-- 临时解决(开启log_bin_trust_function_creators)
show variables like '%log_bin_trust_function_creators%';
set global log_bin_trust_function_creators = 1;

-- 永久解决
vim /etc/my.cnf追加配置
[mysqld]
log_bin_trust_function_creators = 1

2.2 分析海量数据

2.2.1 profiles

show profiles; -- 默认关闭
show variables like '%profiling%';
set profiling = on;

show profiles 会记录profiling打开之后的全部SQL查询语句所花费的时间. 缺点: 不够精确,只能看到总共消费的时间,不能看到各个硬件消费的时间(cpu, io)

2.2.2 精确分析: sql诊断

show profile all for query 上一步查询的Query_id;
show profile cpu,block io for query 上一步查询的Query_id;

2.2.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 ;

3. 锁机制:解决因资源共享而造成的并发问题

例如: A和B同时买最后一件衣服X
A: X加锁 --> 试衣服 -->下单 --> 付款 --> 打包 --> 解锁
B: 发现X已被加锁, 等待X解锁, X已售空

3.1 分类

3.1.1 操作类型:

1. 读锁(共享锁): 对同一个数据(衣服), 多个读操作可以同时进行, 互不干扰
2. 写锁(互斥锁): 如果当前写操作没有完毕(买衣服的一系列操作), 则无法进行其他的读写操作

3.1.2 操作范围

3. 表锁: 一次性对一张表整体加锁, 如MyISAM引擎使用表锁, 开销小, 加锁快; 无死锁, 但是锁的范围大, 容易发生锁冲突, 并发度低
4. 行锁: 一次对一条数据加锁, 如InnoDB存储引擎使用行锁, 开销大, 加锁慢, 容易出现死锁; 锁的范围较小, 并发度高(很小概率发生高并发问题:脏读/幻读/不可重复读/丢失更新等问题)
5. 页锁

3.2 表锁示例(MyISAM)

-- (1) 表锁: 自增操作,MYSQL/SQLSERVER支持;oracle需要借助于序列来实现自增
create table table_lock(
	id int primary key auto_increment,
    name varchar(20)
)engine=myisam;
insert into table_lock(name) values('a1');
insert into table_lock(name) values('a2');
insert into table_lock(name) values('a3');
insert into table_lock(name) values('a4');
insert into table_lock(name) values('a5');
commit;
-- 增加锁
lock table 表1 read/write,表2 read/write
-- 查看加锁的表
show open tables;

会话session: 每一个访问数据的dos命令行、数据库客户端工具，都是一个会话

-- 加读锁
	-- 会话0:
			lock table table_lock read;
			select * from table_lock; -- 读(查), 可以
			delete from table_lock where id = 1; -- 写,不可以
			
			select * from emp; -- 读,不可以
			delete from emp where id = 1; -- 写,不可以
	-- 结论1: 如果某一个会话对A表加了read锁, 则该会话可以对A表进行读操作,不能进行写操作;且该会话不能对其他表进行读,写操作; 
	-- 即如果给A表加了读锁, 则当前会话只能对A表进行读操作
	
	-- 会话1(被锁的表):
			select * from table_lock;   --读（查），可以
			delete from table_lock where id =1 ; --写，会“等待”会话0将锁释放
			
	-- 会话1(其他表):
			select * from emp ;  --读（查），可以
			delete from emp where eno = 1; --写，可以
	-- 结论2:会话0给A表加了锁;其他会话的操作: a.可以对其他表(A表以外的表)进行读,写操作	b.对A表可以进行读操作, 但写操作需要等待锁释放
	unlock tables; -- 释放锁
	
	-- 读锁总结: 在当前会话只锁一张A表, 只能对A表进行读操作(写操作不行), 其他表不能进行读写操作.其他会话可以对A表进行读操作,写操作需要等会话0将锁释放,对其他表可以进行读写操作
	
	
-- 加写锁
	-- 会话0:
		lock table table_lock write;
	-- 写锁总结: 在当前会话只锁一张A表, 可以对加锁的A表进行读写操作,但是不能对其他表进行读写操作.在其他会话, 要对加锁的A表进行增删改查的操作,需要等待当前会话将锁释放

2.2.1 MySQL表级锁的锁模式

MyISAM在执行查询语句(select)前, 会自动将涉及到的所有表加读锁, 在执行更新操作(DML)前, 会自动给涉及到的表加写锁, 所以对MyISAM表进行操作,会有以下情况:

1. 对MyISAM表的读操作(加读锁), 不会阻塞其他进程(会话)对同一张表的读需求, 但会阻塞对同一张表的写需求, 只有当读锁释放后, 才能进行其他进程的写操作
2. 对MyISAM表的写操作(加写锁), 会阻塞其他进程(会话)对同一张表的读写操作, 只有当写锁释放之后, 才会执行其他进程的读写操作

2.2.2 分析表锁定

-- 查看哪些表加了锁(1代表加了锁)
show open tables;
-- 分析表锁定的严重性
show status like 'table%';
-- Table_locks_immediate: 即可能获取到的锁数
-- Table_locks_waited: 需要等待的表锁数(该值越大, 说明存在越大的锁竞争)

一般建议:Table_locks_immediate/Table_locks_waited > 5000, 建议采用InnoDB引擎, 否则使用MyISAM引擎

2.3 行锁示例(InnoDB)

create table line_lock(
	id int(5) primary key auto_increment,
    name varchar(20)
) engine=innodb;
insert into line_lock(name) values('1');
insert into line_lock(name) values('2');
insert into line_lock(name) values('3');
insert into line_lock(name) values('4');
insert into line_lock(name) values('5');
-- mysql默认自动commit, oracle默认不会自动commit; 为了研究行锁, 暂时将自动commit关闭, 以后需要通过commit提交
set autocommit = 0;

2.3.1 操作同样的数据

-- 会话0:写操作
insert into line_lock value('a6');
-- 会话1:写操作同样的数据
update line_lock set name='ax' where id = 6;

结论:

1. 如果会话对某条数据进行DML操作(研究时关闭了autocommit的情况下), 则其他会话必须等待会话X结束事务(commit/rollback)后, 才能对数据a进行操作
2. 表锁是通过unlock tables, 也可以通过事务解锁, 行锁是通过事务解锁

2.3.2 操作不同的数据

-- 会话0:写操作
insert into line_lock value(8,'a8');
-- 会话1:写操作
update line_lock set name = 'ax3' where id = 5;

结论: 行锁,一次锁一行数据;因此, 如果操作的是不同数据, 则互不干扰

2.3.3 行锁的注意事项

1. 如果没有索引, 则行锁会转为表锁

show index from line_lock;
alter table line_lock add index idx_line_lock_name(name);

-- 会话0:写操作
update line_lock set name = 'ai' where name = '3';
-- 会话1:写操作, 不同的数据
update line_lock set name = 'aix' where name = '4';

-- 可以发现, 数据被阻塞了(加锁)
-- 原因:如果索引发生了类型转换, 则索引失效, 因此此次操作, 会从行锁转为表锁

2.行锁的一种特殊情况: 间隙锁, 值在范围内, 却不存在

-- 此时line_lock表中没有id=7的数据
update line_lock set name = 'X' where id > 1 and id < 9; -- 即在where范围中, 没有id=7的数据, 则id=7的数据成为间隙

间隙锁: mysql会自动给间隙加索引, 即本demo中会自动给id = 7的数据加间隙锁(行锁)
行锁: 如果没有where, 则实际加索引的范围就是where后面的范围, 不是实际的值

如果仅仅只是查询数据, 能否加锁? 可以! 通过for update对query语句进行加锁

set autocommit = 0;
start transaction;
begin;
select * from line_lock where id = 2 for update;

行锁: InnoDB默认采用行锁, 相较于表锁性能消耗较大, 但有着并发能力强,效率高的优势, 因此建议高并发用InnoDB,否则使用MyISAM
行锁分析:

show status like '%innodb_row_lock%';
-- Innodb_row_lock_current_waits: 当前正在等待锁的数量
-- Innodb_row_lock_time: 等待总时长,从系统启动到现在一共等待的时间
-- Innodb_row_lock_time_avg: 平均等待市场,从系统启动到现在平均等待的时间
-- Innodb_row_lock_time_max: 最大等待时长, 从系统启动到现在最大一次等待的时间
-- Innodb_row_lock_waits: 等待次数,从系统启动到现在一共等待的次数

本篇是对B站颜群老师SQL优化视频的笔记梳理, 感兴趣的可以去看下视频: SQL优化