MySql常用查詢優(yōu)化策略有哪些

本篇內(nèi)容介紹了“MySql常用查詢優(yōu)化策略有哪些”的有關(guān)知識(shí)，在實(shí)際案例的操作過程中，不少人都會(huì)遇到這樣的困境，接下來(lái)就讓小編帶領(lǐng)大家學(xué)習(xí)一下如何處理這些情況吧！希望大家仔細(xì)閱讀，能夠?qū)W有所成！

查詢優(yōu)化

可以說(shuō)，對(duì)于大多數(shù)系統(tǒng)來(lái)說(shuō)，讀多寫少一定是常態(tài)，這就表示涉及到查詢的SQL是非常高頻的操作；

前置準(zhǔn)備，給一張測(cè)試表添加10萬(wàn)條數(shù)據(jù)

使用下面的存儲(chǔ)過程給單表造一批數(shù)據(jù)，將表?yè)Q成自己的就好了

create procedure addMyData()

	begin

		declare num int;
		set num =1;
		
		while num <= 100000 do
		
			insert into XXX_table values(
				replace(uuid(),'-',''),concat('測(cè)試',num),concat('cs',num),'123456'
			);
 
			set num =num +1;
		end while;

	end ;

然后調(diào)用該存儲(chǔ)過程

call addMyData();

本篇準(zhǔn)備了3張表，分別為學(xué)生（student）表，班級(jí)（class）表，賬戶(account)表，各自有50萬(wàn)，1萬(wàn)和10萬(wàn)條數(shù)據(jù)用于測(cè)試；

1、分頁(yè)查詢優(yōu)化

分頁(yè)查詢是開發(fā)中經(jīng)常會(huì)遇到的，有一種情況是，當(dāng)分頁(yè)的數(shù)量非常大的時(shí)候，查詢的時(shí)候往往非常耗時(shí)，比如查詢student表，使用下面的sql查詢，耗時(shí)達(dá)到0.2秒；

實(shí)踐經(jīng)驗(yàn)告訴我們，越往后，分頁(yè)查詢效率越低，這就是分頁(yè)查詢的問題所在， 因?yàn)?，?dāng)在進(jìn)行分頁(yè)查詢時(shí)，如果執(zhí)行  limit 400000,10  ，此時(shí)需要 MySQL 排序前4000 10  記 錄，僅僅返回400000 - 4 00010 的記錄，其他記錄丟棄，查詢排序的代價(jià)非常大

優(yōu)化思路：

一般分頁(yè)查詢時(shí)，通過創(chuàng)建 覆蓋索引 能夠比較好地提高性能，可以通過覆蓋索引加子查詢形式進(jìn)行優(yōu)化；  

1） 在索引上完成排序分頁(yè)操作，最后根據(jù)主鍵關(guān)聯(lián)回原表查詢所需要的其他列內(nèi)容

SELECT * FROM student t1,(SELECT id FROM student ORDER BY id LIMIT 400000,10) t2 WHERE t1.id =t2.id;

執(zhí)行上面的sql，可以看到響應(yīng)時(shí)間有一定的提升；

2）對(duì)于主鍵自增的表，可以把Limit 查詢轉(zhuǎn)換成某個(gè)位置的查詢

select * from student where id > 400000 limit 10;

執(zhí)行上面的sql，可以看到響應(yīng)時(shí)間有一定的提升；

2、關(guān)聯(lián)查詢優(yōu)化

在實(shí)際的業(yè)務(wù)開發(fā)過程中，關(guān)聯(lián)查詢可以說(shuō)隨處可見，關(guān)聯(lián)查詢的優(yōu)化核心思路是，最好為關(guān)聯(lián)查詢的字段添加索引，這是關(guān)鍵，具體到不同的場(chǎng)景，還需要具體分析，這個(gè)跟mysql的引擎在執(zhí)行優(yōu)化策略的方案選擇時(shí)有一定關(guān)系；

2.1 左連接或右連接

下面是一個(gè)使用left join 的查詢，可以預(yù)想到這條sql查詢的結(jié)果集非常大

select t.* from student t left join class cs on t.classId = cs.id;

登錄后復(fù)制

為了檢查下sql的執(zhí)行效率，使用explain做一下分析，可以看到，第一張表即left join左邊的表student走了全表掃描，而class表走了主鍵索引，盡管結(jié)果集較大，還是走了索引；

針對(duì)這種場(chǎng)景的查詢，思路如下：

• 讓查詢的字段盡量包含在主鍵索引或者覆蓋索引中；

• 查詢的時(shí)候盡量使用分頁(yè)查詢；

關(guān)于左連接（右連接）的explain結(jié)果補(bǔ)充說(shuō)明

• 左連接左邊的表一般為驅(qū)動(dòng)表，右邊的表為被驅(qū)動(dòng)表；

• 盡可能讓數(shù)據(jù)集小的表作為驅(qū)動(dòng)表，減少mysql內(nèi)部循環(huán)的次數(shù)；

• 兩表關(guān)聯(lián)時(shí)，explain結(jié)果展示中，第一欄一般為驅(qū)動(dòng)表；

2.2 關(guān)聯(lián)查詢關(guān)聯(lián)的字段建立索引

看下面的這條sql，其關(guān)聯(lián)字段非表的主鍵，而是普通的字段；

explain select u.* from tenant t left join `user` u on u.account = t.tenant_name where t.removed is null and u.removed is null;

登錄后復(fù)制

通過explain分析可以發(fā)現(xiàn)，左邊的表走了全表掃描，可以考慮給左邊的表的tenant_name和user表的account 各自創(chuàng)建索引；

create index idx_name on tenant(tenant_name);

create index idx_account on `user`(account);

再次使用explain分析結(jié)果如下

可以看到第二行type變?yōu)閞ef，rows的數(shù)量?jī)?yōu)化比較明顯。這是由左連接特性決定的，LEFT JOIN條件用于確定如何從右表搜索行，左邊一定都有，所以右邊是我們的關(guān)鍵點(diǎn),一定需要建立索引 。

2.3 內(nèi)連接關(guān)聯(lián)的字段建立索引

我們知道，左連接和右連接查詢的數(shù)據(jù)分別是完全包含左表數(shù)據(jù)，完全包含右表數(shù)據(jù)，而內(nèi)連接（inner join 或join） 則是取交集（共有的部分），在這種情況下，驅(qū)動(dòng)表的選擇是由mysql優(yōu)化器自動(dòng)選擇的；

在上面的基礎(chǔ)上，首先移除兩張表的索引

ALTER TABLE `user` DROP INDEX idx_account;
ALTER TABLE `tenant` DROP INDEX idx_name;

使用explain語(yǔ)句進(jìn)行分析

然后給user表的account字段添加索引，再次執(zhí)行explain我們發(fā)現(xiàn)，user表竟然被當(dāng)作是被驅(qū)動(dòng)表了；

此時(shí)，如果我們給tenant表的tenant_name加索引，并移除user表的account索引，得出的結(jié)果竟然都沒有走索引，再次說(shuō)明，使用內(nèi)連接的情況下，查詢優(yōu)化器將會(huì)根據(jù)自己的判斷進(jìn)行選擇；

3、子查詢優(yōu)化

子查詢?cè)谌粘＞帉憳I(yè)務(wù)的SQL時(shí)也是使用非常頻繁的做法，不是說(shuō)子查詢不能用，而是當(dāng)數(shù)據(jù)量超出一定的范圍之后，子查詢的性能下降是很明顯的，關(guān)于這一點(diǎn)，本人在日常工作中深有體會(huì)；

比如下面這條sql，由于student表數(shù)據(jù)量較大，執(zhí)行起來(lái)耗時(shí)非常長(zhǎng)，可以看到耗費(fèi)了將近3秒；

select st.* from student st where st.classId in (
	
	select id from class where id > 100

);

登錄后復(fù)制

通過執(zhí)行explain進(jìn)行分析得知，內(nèi)層查詢 id > 100的子查詢盡管用上了主鍵索引，但是由于結(jié)果集太大，帶入到外層查詢，即作為in的條件時(shí)，查詢優(yōu)化器還是走了全表掃描；

針對(duì)上面的情況，可以考慮下面的優(yōu)化方式

select st.id from student st join class cl on st.classId = cl.id where cl.id > 100;

子查詢性能低效的原因

• 子查詢時(shí)，MySQL需要為內(nèi)層查詢語(yǔ)句的查詢結(jié)果建立一個(gè)臨時(shí)表 ，然后外層查詢語(yǔ)句從臨時(shí)表中查詢記錄，查詢完畢后，再撤銷這些臨時(shí)表 。這樣會(huì)消耗過多的CPU和IO資源,產(chǎn)生大量的慢查詢；

• 子查詢結(jié)果集存儲(chǔ)的臨時(shí)表，不論是內(nèi)存臨時(shí)表還是磁盤臨時(shí)表都不能走索引 ，所以查詢性能會(huì)受到一定的影響；

• 對(duì)于返回結(jié)果集比較大的子查詢，其對(duì)查詢性能的影響也就越大；

使用mysql查詢時(shí)，可以使用連接（JOIN）查詢來(lái)替代子查詢。連接查詢不需要建立臨時(shí)表 ，其速度比子查詢要快 ，如果查詢中使用索引的話，性能就會(huì)更好，盡量不要使用NOT IN 或者 NOT EXISTS，用LEFT JOIN xxx ON xx WHERE xx IS NULL替代；

一個(gè)真實(shí)的案例

在下面的這段sql中，優(yōu)化前使用的是子查詢，在一次生產(chǎn)問題的性能分析中，發(fā)現(xiàn)某個(gè)tenant_id下的數(shù)據(jù)達(dá)到了35萬(wàn)多，這樣直接導(dǎo)致某個(gè)列表頁(yè)面的接口查詢耗時(shí)達(dá)到了5秒左右；

找到了問題的根源后，嘗試使用上面的優(yōu)化思路進(jìn)行解決即可，優(yōu)化后的sql大概如下，

4、排序（order by）優(yōu)化

在mysql，排序主要有兩種方式

• Using filesort : 通過表索引或全表掃描，讀取滿足條件的數(shù)據(jù)行，然后在排序緩沖區(qū)sort
  buffer中完成排序操作，所有不是通過索引直接返回排序結(jié)果的排序都叫 FileSort 排序；

• Using index : 通過有序的索引順序掃描直接返回有序數(shù)據(jù)，這種情況即為 using index，不需要額外排序，操作效率高；

對(duì)于以上兩種排序方式，Using index的性能高，而Using filesort的性能低，我們?cè)趦?yōu)化排序操作時(shí)，盡量要優(yōu)化為 Using index

4.1 使用age字段進(jìn)行排序

由于age字段未加索引，查詢結(jié)果按照age排序的時(shí)候發(fā)現(xiàn)使用了filesort，排序性能較低；

給age字段添加索引，再次使用order by時(shí)就走了索引；

4.2 使用多字段進(jìn)行排序

通常在實(shí)際業(yè)務(wù)中，參與排序的字段往往不只一個(gè)，這時(shí)候，就可以對(duì)參與排序的多個(gè)字段創(chuàng)建聯(lián)合索引；

如下根據(jù)stuno和age排序

給stuno和age添加聯(lián)合索引

create index idx_stuno_age on `student`(stuno,age);

再次分析時(shí)結(jié)果如下，此時(shí)排序走了索引

關(guān)于多字段排序時(shí)的注意事項(xiàng)

1）排序時(shí)，需要滿足最左前綴法則,否則也會(huì)出現(xiàn) filesort；

在上面我們創(chuàng)建的聯(lián)合索引順序是stuno和age，即stuno在前面，而age在后，如果查詢的時(shí)候調(diào)換排序順序會(huì)怎樣呢？通過分析結(jié)果發(fā)現(xiàn)，走了filesort；

2）排序時(shí)，排序的類型保持一致

在保持字段排序順序不變時(shí)，默認(rèn)情況下，如果都按照升序或者降序時(shí)，order by可以使用index，如果一個(gè)是升序，另一個(gè)是降序會(huì)如何呢？分析發(fā)現(xiàn)，這種情況下也會(huì)走filesort；

5、分組（group by）優(yōu)化

group by 的優(yōu)化策略和order by 的優(yōu)化策略非常像，主要列舉如下幾個(gè)要點(diǎn)：

• group by 即使沒有過濾條件用到索引，也可以直接使用索引；

• group by 先排序再分組，遵照索引建的最佳左前綴法則；

• 當(dāng)無(wú)法使用索引列時(shí)，增大 max_length_for_sort_data 和 sort_buffer_size 參數(shù)的設(shè)置；

• where效率高于having，能寫在where限定的條件就不要寫在having中了；

• 減少使用order by，能不排序就不排序，或?qū)⑴判蚍诺匠绦蛉プ?。Order by、groupby、distinct這些語(yǔ)句較為耗費(fèi)CPU，數(shù)據(jù)庫(kù)的CPU資源是極其寶貴的；

• 如果sql包含了order by、group by、distinct這些查詢的語(yǔ)句，where條件過濾出來(lái)的結(jié)果集請(qǐng)保持在1000行以內(nèi)，否則SQL會(huì)很慢；

5.1 給group by的字段添加索引

如果字段未加索引，分析結(jié)果如下，這種結(jié)果性能顯然很低效

給stuno添加索引之后

給stuno和age添加聯(lián)合索引

如果不遵循最佳左前綴，group by 性能將會(huì)比較低效

遵循最佳左前綴的情況如下

6、count 優(yōu)化

count() 是一個(gè)聚合函數(shù)，對(duì)于返回的結(jié)果集，一行行判斷，如果 count 函數(shù)的參數(shù)不是NULL，累計(jì)值就加 1，否則不加，最后返回累計(jì)值；

用法：count（*）、count（主鍵）、count（字段）、count（數(shù)字）

如下列舉了count的幾種寫法的詳細(xì)說(shuō)明

經(jīng)驗(yàn)值總結(jié)

按照效率排序來(lái)看，count(字段) < count(主鍵 id) < count(1) ≈ count(*)，所以盡量使用 count(*)

“MySql常用查詢優(yōu)化策略有哪些”的內(nèi)容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業(yè)相關(guān)的知識(shí)可以關(guān)注億速云網(wǎng)站，小編將為大家輸出更多高質(zhì)量的實(shí)用文章！