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_datacreate 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) begindeclare 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)begindeclare 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 ;repeatinsert into emp values(eid_start + i, randstring(5) ,'other' ,ran_num()) ;set i=i+1 ;until i=data_timesend repeat ;commit ;end $-- dept表
delimiter $ create procedure insert_dept(in dno_start int(10) ,in data_times int(10))begindeclare i int default 0;set autocommit = 0 ;repeatinsert into dept values(dno_start+i ,randstring(6),randstring(8)) ;set i=i+1 ;until i=data_timesend 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 操作类型:
读锁(共享锁): 对同一个数据(衣服), 多个读操作可以同时进行, 互不干扰写锁(互斥锁): 如果当前写操作没有完毕(买衣服的一系列操作), 则无法进行其他的读写操作
3.1.2 操作范围
表锁: 一次性对一张表整体加锁, 如MyISAM引擎使用表锁, 开销小, 加锁快; 无死锁, 但是锁的范围大, 容易发生锁冲突, 并发度低行锁: 一次对一条数据加锁, 如InnoDB存储引擎使用行锁, 开销大, 加锁慢, 容易出现死锁; 锁的范围较小, 并发度高(很小概率发生高并发问题:脏读/幻读/不可重复读/丢失更新等问题)- 页锁
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表进行操作,会有以下情况:
对MyISAM表的读操作(加读锁), 不会阻塞其他进程(会话)对同一张表的读需求, 但会阻塞对同一张表的写需求, 只有当读锁释放后, 才能进行其他进程的写操作对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;
结论:
- 如果会话对某条数据进行DML操作(研究时关闭了autocommit的情况下), 则其他会话必须等待会话X结束事务(commit/rollback)后, 才能对数据a进行操作
- 表锁是通过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优化