MySQL查詢擷取分析

MXC肖某某發表於2021-01-19
MySql

一、查詢優化

1，mysql的調優大綱

  1. 慢查詢的開啟並捕獲
  2. explain+慢SQL分析
  3. show profile查詢SQL在Mysql伺服器裡面的執行細節和生命週期情況
  4. SQL資料庫伺服器的引數調優

2，小表驅動大表

　　mysql的join實現原理是，以驅動表的資料為基礎，“巢狀迴圈”去被驅動表匹配記錄。驅動表的索引會失效，而被驅動表的索引有效。

#假設 a表10000資料，b表20資料
select * from a join b on a.bid =b.id
a表驅動b表為:
for  20條資料
   匹配10000資料（根據on a.bid=b.id的連線條件，進行B+樹查詢）
查詢次數為:20+ log10000

b表驅動a表為
for 10000條資料
    匹配20條資料（根據on a.bid=b.id的連線條件，進行B+樹查詢）
查詢次數為:10000+ log20

3，in和exists

　　exists的使用

  1. EXISTS 語法：EXISTS(subquery) 只返回TRUE或FALSE，因此子查詢中的SELECT *也可以是SELECT 1或其他，官方說法是實際執行時會忽略SELECT清單，因此沒有區別
    • SELECT ... FROM table WHERE EXISTS(subquery)
    • 該語法可以理解為：將查詢的資料，放到子查詢中做條件驗證，根據驗證結果（TRUE或FALSE）來決定主查詢的資料結果是否得以保留。
  2. EXISTS子查詢的實際執行過程可能經過了優化而不是我們理解上的逐條對比，如果擔憂效率問題，可進行實際檢驗以確定是否有效率問題。
  3. EXISTS子查詢往往也可以用條件表示式、其他子查詢或者JOIN來替代，何種最優需要具體問題具體分析
#採用in則是，內表B驅動外表A
select * from A where id in (select id from B)
#採用exists則是，外表A驅動內表B
select * from A where exists(select 1 from B where B.id = A.id)

　　結論：

  1. 永遠記住小表驅動大表
  2. 當 B 表資料集小於 A 表資料集時，使用 in
  3. 當 A 表資料集小於 B 表資料集時，使用 exist

4，order by

建立表

create table tblA(
    #id int primary key not null auto_increment,
    age int,
    birth timestamp not null
);

insert into tblA(age, birth) values(22, now());
insert into tblA(age, birth) values(23, now());
insert into tblA(age, birth) values(24, now());
#建立複合索引
create index idx_A_ageBirth on tblA(age, birth);

　　MySQL查詢擷取分析

order by命中索引的情況

　　MySQL查詢擷取分析

order by未命中索引的情況

　　MySQL查詢擷取分析

結論：

  • MySQL支援兩種排序方式：Using index和Using filesort。filesort效率較低，而要使用index方式排序需滿足兩種使用條件儘可能在索引列上完成排序操作，遵照索引的最佳左字首
    • order by語句自身使用索引的最左前列
    • 使用where子句與order by子句條件列組合滿足最左前列
  • 如果order by不在索引列上，會使用filesort演算法：雙路排序和單路排序

    • MySQL4.1之前是使用雙路排序，字面意思是兩次掃描磁碟，最終得到資料。讀取行指標和order by列，對他們進行排序，然後掃描已經排序好的列表，按照列表中的值重新從列表中讀取對應的資料傳輸

    • 從磁碟讀取查詢需要的所有列，按照order by列在buffer對它們進行排序，然後掃描排序後的列表進行輸出，它的效率更快一些，避免了第二次讀取資料，並且把隨機IO變成順序IO，但是它會使用更多的空間，因為它把每一行都儲存在記憶體中了。

  • select * from user where name = "zs" order by age
#雙路排序
1)從 name 找到第一個滿足 name = 'zs' 的主鍵id
2)根據主鍵 id 取出整行，把排序欄位 age 和主鍵 id 這兩個欄位放到 sort buffer(排序快取) 中
3)從name 取下一個滿足 name = 'zs' 記錄的主鍵 id
4)重複 2、3 直到不滿足 name = 'zs'
5)對 sort_buffer 中的欄位 age 和主鍵 id 按照欄位 age進行排序
6)遍歷排序好的 id 和欄位 age ，按照 id 的值回到原表中取出 所有欄位的值返回給客戶端

#單路排序
1)從name找到第一個滿足 name ='zs' 條件的主鍵 id
2)根據主鍵 id 取出整行，取出所有欄位的值，存入 sort_buffer(排序快取)中
3)從索引name找到下一個滿足 name = 'zs' 條件的主鍵 id
4)重複步驟 2、3 直到不滿足 name = 'zs'
5)對 sort_buffer 中的資料按照欄位 age 進行排序，返回結果給客戶端
  • 單路排序的問題及優化 
  • 問題：
    由於單路是改進的演算法，總體而言好過雙路
    在sort_buffer中，方法B比方法A要多佔用很多空間，因為方法B是把所有欄位都取出，所以有可能取出的資料的總大小超出了sort_buffer的容量，導致每次只能取sort_buffer容量大小的資料，進行排序（建立tmp檔案，多路合併），排完再取取sort_buffer容量大小，再排…… 從而會導致多次I/O。
優化策略：
    增大sort_buffer_size引數的設定
    增大max_length_for_sort_data引數的設定
注意事項:
　　Order by時select *是一個大忌，只Query需要的欄位。因為欄位越多在記憶體中儲存的資料也就也多，這樣就導致每次I/O能載入的資料列越少。

5，group by優化

1)group by實質是先排序後進行分組，遵照索引的最佳左字首
2)當無法使用索引列，增大max_length_for_sort_data引數的設定+增大sort_buffer_size引數的設定
3)where高於having，能寫在where限定的條件就不要去having限定了
4)其餘的規則均和 order by 一致

二、慢查詢日誌

1，慢查詢日誌是什麼?

  • MySQL的慢查詢日誌是MySQL提供的一種日誌記錄，它用來記錄在MySQL中響應時間超過閥值的語句，具體指執行時間超過long_query_time值的SQL，則會被記錄到慢查詢日誌中。
  • long_query_time的預設值為10，意思是執行10秒以上的SQL語句會被記錄下來
  • 由他來檢視哪些SQL超出了我們的最大忍耐時間值，比如一條sql執行超過5秒鐘，我們就算慢SQL，希望能收集超過5秒的sql，結合之前explain進行全面分析。

2，慢查詢日誌的開啟

　　預設情況下，MySQL的慢查詢日誌是沒有開啟的。如果不是調優需要的話，一般不建議啟動該引數，因為開啟慢查詢日誌會影響到效能，慢查詢日誌支援將日誌記錄寫入檔案。

a）開啟慢查詢日誌

#檢視是否開啟慢日誌
show variables like 'slow_query_log%';
#開啟慢查詢日誌，想要永久有效在my.cnf中設定
set global slow_query_log = 1 ;

　　MySQL查詢擷取分析

b）設定慢查詢日誌的閾值

#檢視慢查詢日誌的閾值時間  預設為10s
show variables like 'long_query_time%';
#設定為3s 重啟失效，想要永久有效在my.cnf中設定
set global long_query_time = 3
#再次檢視，需要切換視窗檢視
show variables like 'long_query_time%';

　　MySQL查詢擷取分析

c）持久化慢查詢日誌和時間閾值

[mysqld]
#持久化慢查詢日誌
slow_query_log=1；
slow_query_log_file=/var/lib/mysql/hadoop102-slow.log
long_query_time=3；
log_output=FILE

d）慢查詢案例

#查詢等待4s
select sleep(4); 
#在linux系統中，檢視慢查詢日誌
cat /var/lib/mysql/hadoop102-slow.log

e）檢視當前系統中存在的慢查詢日誌條數

show global status like '%Slow_queries%';

3，日誌分析命令mysqldumpslow

a）引數解釋

-s：是表示按何種方式排序
 c：訪問次數
 l：鎖定時間
 r：返回記錄
 t：查詢時間
 al：平均鎖定時間
 ar：平均返回記錄數
 at：平均查詢時間
-t：即為返回前面多少條的資料
-g：後邊搭配一個正則匹配模式，大小寫不敏感的

b）常用方法

#得到返回記錄集最多的10個SQL
mysqldumpslow -s r -t 10 /var/lib/mysql/hadoop102-slow.log
#得到訪問次數最多的10個SQL
mysqldumpslow -s c -t 10 /var/lib/mysql/hadoop102-slow.log
#得到按照時間排序的前10條裡面含有左連線的查詢語句
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/hadoop102-slow.log
#這些命令時結合 | 和more使用
mysqldumpslow -s r -t 10 /var/lib/mysql/hadoop102-slow.log | more

三、批量寫資料指令碼

1，建表

MySQL查詢擷取分析 
CREATE TABLE dept
(
    deptno int unsigned primary key auto_increment,
    dname varchar(20) not null default '',
    loc varchar(8) not null default ''
)ENGINE=INNODB DEFAULT CHARSET=utf8;

CREATE TABLE emp
(
    id int unsigned primary key auto_increment,
    empno mediumint unsigned not null default 0,
    ename varchar(20) not null default '',
    job varchar(9) not null default '',
    mgr mediumint unsigned not null default 0,
    hiredate date not null,
    sal decimal(7,2) not null,
    comm decimal(7,2) not null,
    deptno mediumint unsigned not null default 0
)ENGINE=INNODB DEFAULT CHARSET=utf8;
View Code

2，設定是否可以信任儲存函式建立者

#檢視binlog狀態
show variables like 'log_bin%';
#新增可以信任儲存函式建立者
set global log_bin_trust_function_creators = 1;

　　MySQL查詢擷取分析

3，建立函式

  • 隨機產生字串的函式
# 定義兩個 $$ 表示結束 (替換原先的;)
delimiter $$ 
create function rand_string(n int) returns varchar(255)
begin
    declare chars_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(chars_str,floor(1+rand()*52),1));
        set i=i+1;
    end while;
    return return_str;
end $$
  • 隨機產生部門編號的函式
delimiter $$
create function rand_num() returns int(5)
begin
    declare i int default 0;
    set i=floor(100+rand()*10);
    return i;
end $$

4，建立儲存過程

  • 建立往emp表中插入資料的儲存過程
delimiter $$
create procedure insert_emp(in start int(10),in max_num int(10))
begin
    declare i int default 0;
    set autocommit = 0;
    repeat
        set i = i+1;
        insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values((start+i),rand_string(6),'salesman',0001,curdate(),2000,400,rand_num());
        until i=max_num
        end repeat;
    commit;
end $$
  • 建立往dept表中插入資料的儲存過程
delimiter $$
create procedure insert_dept(in start int(10),in max_num int(10))
begin
    declare i int default 0;
    set autocommit = 0;
    repeat
        set i = i+1;
        insert into dept(deptno,dname,loc) values((start+i),rand_string(10),rand_string(8));
        until i=max_num
        end repeat;
    commit;
end $$

5，呼叫儲存過程生成資料

#向 部門表插入10條資料
DELIMITER ;
CALL insert_dept(100, 10);
#向 員工表插入50w條資料
CALL insert_emp(100001, 500000);

四、show profiles

1，介紹

  • show profile是mysql提供可以用來分析當前會話中語句執行的資源消耗情況。可以用於SQL的調優測量。
  • 預設情況下，引數處於關閉狀態，並儲存最近15次的執行結果

2，開啟

#檢視 Show Profile 是否開啟
show variables like ‘profiling%’;
#開啟 Show Profile
set profiling=on;

3，使用show profiles

  • 建立測試資料
select * from emp group by id%10 limit 150000;
select * from emp group by id%10 limit 150000;
select * from emp group by id%10 order by 5;
select * from emp
select * from dept
select * from emp left join dept on emp.deptno = dept.deptno
  • 執行show profiles

　　MySQL查詢擷取分析

  • 執行 show profile cpu, block io for query Query_ID;

　　MySQL查詢擷取分析

    •  檢索引數
    • ALL：顯示所有的開銷資訊
BLOCK IO：顯示塊IO相關開銷
CONTEXT SWITCHES：上下文切換相關開銷
CPU：顯示CPU相關開銷資訊
IPC：顯示傳送和接收相關開銷資訊
MEMORY：顯示記憶體相關開銷資訊
PAGE FAULTS：顯示頁面錯誤相關開銷資訊
SOURCE：顯示和Source_function，Source_file，Source_line相關的開銷資訊
SWAPS：顯示交換次數相關開銷的資訊
    • 返回結果
    • converting HEAP to MyISAM：查詢結果太大，記憶體都不夠用了往磁碟上搬了。
Creating tmp table：建立臨時表，mysql 先將拷貝資料到臨時表，然後用完再將臨時表刪除
Copying to tmp table on disk：把記憶體中臨時表複製到磁碟，危險！！！
locked：鎖表 

五、全域性查詢日誌

　　切莫在生產環境配置啟用

  • 在my.cnf中配置
# 開啟
general_log=1
# 記錄日誌檔案的路徑
general_log_file=/path/logfile
# 輸出格式
log_output=FILE
  • 編碼啟用
set global general_log=1;
set global log_output='TABLE';
  • 配置完成之後，將會記錄到mysql庫裡的general_log表
select * from mysql.general_log;

 

相關文章