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優化
)
