mysql 截取 效率_[mysql]查詢截取分析-查詢優化

分析SQL慢的方式

觀察，至少跑1天，看看生産的慢SQL情況。

開啟慢查詢日志，設定門檻值，比如超過5秒鐘的就是慢SQL，并将它抓取出來。

explain + 慢SQL分析。

show Profile。

運維經理 OR DBA，進行MySQL資料庫伺服器的參數調優。

總結：

1 慢查詢的捕獲

2 explain+慢SQL分析

3 show profile查詢SQL在Mysql伺服器裡面的執行細節和生命周期情況

4 SQL資料庫伺服器的參數調優

查詢優化

1小表驅動大表

//選擇這種

for(int i = 1; i <= 5; i ++){

for(int j = 1; j <= 1000; j++){

}

}

// ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~

for(int i = 1; i <= 1000; i ++){

for(int j = 1; j <= 5; j++){

}

}

優化原則：小表驅動大表，即小的資料集驅動大的資料集

SELECT * FROM `A` WHERE `id` IN (SELECT `id` FROM `B`)

等價于：

for select id from B

for select * from A where A.id=B.id

當B資料的資料小于A表的資料集是，用in優于exists

SELECT * FROM `A` WHERE EXISTS (SELECT 1 FROM `B` WHERE `B`.id = `A`.id);

等價于：

for select * from A

for select * from B where B.id = A.id

當A表的資料集系小于B表資料集時，用exists優于in

注意：A表于B表的ID字段應建立索引

EXISTS

文法：`SELECT....FROM tab WHERE EXISTS(subquery);

該文法可以了解為：将主查詢的資料，放到子查詢中做條件驗證，根據驗證結果(true或是false)來決定主查詢的資料結果是否得以保留。

提示

EXISTS(subquery)子查詢隻傳回true或者false，是以子查詢中的SELECT *可以是SELECT 1 OR SELECT X，它們并沒有差別。

EXISTS(subquery)子查詢的實際執行過程可能經過了優化而不是我們了解上的逐條對比，如果擔心效率問題，可進行實際檢驗以确定是否有效率問題

EXISTS(subquery)子查詢往往也可以用條件表達式，其他子查詢或者JOIN替代，何種最優需要具體問題具體分析。

2ORDER BY優化

ORDER BY後的排序條件，與索引(順序)一緻不會産生filesort

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);

select * from tblA;

案例分析

#1，不會産生filesort

EXPLAIN SELECT * FROM `talA` WHERE `age` > 20 ORDER BY `age`;

#2，不會産生filesort

EXPLAIN SELECT * FROM `talA` WHERE `age` > 20 ORDER BY `age`,`birth`;

#3，産生了filesort

EXPLAIN SELECT * FROM `talA` WHERE `age` > 20 ORDER BY `birth`;

#4，産生了filesort

EXPLAIN SELECT * FROM `talA` WHERE `age` > 20 ORDER BY `birth`,`age`;

#5，産生了filesort

EXPLAIN SELECT * FROM `talA` ORDER BY `birth`;

#6，産生了

EXPLAIN SELECT * FROM `talA` WHERE `birth` > '2020-08-04 07:42:21' ORDER BY `birth`;

#7，沒有産生

EXPLAIN SELECT * FROM `talA` WHERE `birth` > '2020-08-04 07:42:21' ORDER BY `age`;

#8，産生了，order by預設升序，順序不一緻導緻産生了(如果都是ASC/DESC就不會産生)

EXPLAIN SELECT * FROM `talA` ORDER BY `age` ASC, `birth` DESC;

MySQL支援兩種方式的排序，FileSort和Index，Index的效率高，它指MySQL掃描索引本身完成排序。FileSort方式效率較低。

ORDER BY滿足兩情況，會使用Index方式排序：

ORDER BY語句使用索引最左前列。

使用WHERE子句于ORDER BY 子句條件列組合滿足索引最左前列

結論：盡可能在索引列上完成排序操作，遵照索引建的最佳的左字首原則。

如果不在索引列上，filesort有兩種算法：Mysql就要啟動雙路排序和單路排序

雙路排序：MySQL4.1之前使用雙路排序，字面意思就是兩次掃描磁盤，最終得到資料，讀取行指針和ORDER BY列，対他們進行排序，然後掃描已經排序好的清單，按照清單中的值重新從清單中讀取對應的資料輸出。

取一批資料，要對磁盤進行兩次掃描，衆所周知，IO是很耗時的，是以在MySQL4.1之後，出現了改進的算法，就是單路排序算法。

單路算法：從磁盤讀取查詢需要的所有列，按照ORDER BY列在buffer対它們進行排序，然後掃描排序後的清單進行輸出，它的效率更快一些，避免了第二次讀取資料。并且把随機IO變成了順序IO，但是它會使用更多的空間，因為它把每一行都儲存在記憶體中了。

結論及引申出的問題：

由于單路時後出的，總體而言好過雙路。

使用單路的問題：

在sort_ buffer中， 方法B比方法A要多占用很多空間，因為方法B是把所有字段都取出,是以有可能取出的資料的總大小超出了sort_ buffer的容量， 導緻每次隻能取sort_ buffer容量大小的資料，進行排序(建立tmp檔案， 多路合并)，排完再取取sort buffer容量大小， 再排....從未而多次I/O:

本來想省一 次/O操作，反而導緻了大量的I/O操作，反而得不償失。

優化政策

增大sort_buffer_size參數的設定

增大max_length_for_sort_data的參數設定

Why：提高Order By速度

ORDER BY時使用SELECT *是大忌，查什麼字段就寫什麼字段，這點非常重要。在這裡的影響是：

當查詢的字段大小總和小于max_length_for_sort_data而且排序字段不是TEXT|BLOB類型時，會使用單路排序算法，否則使用多路排序算法。

兩種排序算法的資料都有可能超出sort_buffer緩沖區的容量，超出之後，會建立tmp臨時檔案進行合并排序，導緻多次IO，但是單路排序算法的風險會更大一些，是以要增大sort_buffer_size參數的設定。

嘗試提高sort_buffer_size：不管使用哪種算法，提高這個參數都會提高效率，當然，要根據系統的能力去提高，因為這個參數是針對每個程序的。

嘗試提高max_length_for_sort_data：提高這個參數，會增加用單路排序算法的機率。但是如果設定的太高，資料總容量sort_buffer_size的機率就增大，明顯症狀是高的磁盤IO活動和低的處理器使用率。

小總結

3 GROUP BY優化

GROUP BY實質是先排序後進行分組，遵照索引建的最佳左字首。

當無法使用索引列時，會使用Using filesort進行排序，增大max_length_for_sort_data參數的設定和增大sort_buffer_size參數的設定，會提高性能。

WHERE執行順序高于HAVING，能寫在WHERE限定條件裡的就不要寫在HAVING中了。