慢 SQL 的優(yōu)化，主要從兩個(gè)方面考慮，SQL 語句本身的優(yōu)化，以及數(shù)據(jù)庫(kù)設(shè)計(jì)的優(yōu)化。

避免不必要的列

SQL 查詢的時(shí)候，應(yīng)該只查詢需要的列，而不是包含額外的列，像select *這種寫法應(yīng)該盡量避免。

分頁(yè)優(yōu)化

在數(shù)據(jù)量比較大，分頁(yè)比較深的情況下，需要考慮分頁(yè)的優(yōu)化。

 select * from tabel where type = 2 and level = 9 order by id asc limit 100000,10;

延遲關(guān)聯(lián)

先通過where條件提取出主鍵，在將該表與原數(shù)據(jù)表關(guān)聯(lián)，通過主鍵 id 提取數(shù)據(jù)行，而不是通過原來的二級(jí)索引提取數(shù)據(jù)行

 select a.* from table a,
 (select id from table where type = 2 and level = 9 order by id asc limit 100000,10) b
 where a.id = b.id;

id 偏移量

偏移量就是找到 limit 第一個(gè)參數(shù)對(duì)應(yīng)的主鍵值，根據(jù)這個(gè)主鍵值再去過濾并 limit

 select * from table where id >
 (select id from table where type = 2 and level = 9 order by id asc limit 190 );

索引優(yōu)化

合理的設(shè)計(jì)和使用索引，是優(yōu)化慢 SQL 的利器。

1.利用覆蓋索引

InnoDB 使用二級(jí)索引查詢數(shù)據(jù)時(shí)會(huì)回表，但是如果索引的葉節(jié)點(diǎn)中已經(jīng)包含要查詢的字段，那它沒有必要再回表查詢了，這就叫覆蓋索引，還有一個(gè)簡(jiǎn)單的理解查詢列都是索引列。

 select b from test where a = "wanna";

 alter table test add index idx_a_b (a,b);

2.避免使用 or 查詢

在 MySQL 5.0之前的版本要盡量避免使用 or 查詢，可以使用 union 或者子查詢來替代，因?yàn)樵缙诘?MySQL 版本使用 or 查詢可能會(huì)導(dǎo)致索引失效，高版本引入了索引合并，解決了這個(gè)問題，不過建議大家在實(shí)際使用中還是規(guī)范寫法，能不用就少用。

3.避免使用 != 或者 <> 操作符

SQL 中，不等于操作符會(huì)導(dǎo)致查詢引擎放棄查詢索引，引起全表掃描，即時(shí)比較的字段上有索引

解決方法：通過把不等于操作符改成 or，可以使用索引，避免全表掃描

 id <> 'aaa' ===> id > 'aaa' or id < 'aaa'

4.適當(dāng)使用前綴索引

適當(dāng)?shù)氖褂们熬Y索引，可以降低索引的控件占用，提高索引的查詢效率。

比如，郵箱的后綴都是固定的@xxx.com，那么類似這種后面幾位為固定值的字段就非常適合定義為前綴索引

 alter table test add index dix_emaile_prefix (email(6));

需要注意的是，前綴索引也存在缺點(diǎn)，MySQL 無法利用前綴索引做 order by和group by操作，也無法作為覆蓋索引。

5.避免列上函數(shù)運(yùn)算

要避免在列字段上進(jìn)行算術(shù)運(yùn)算符或其他表達(dá)式運(yùn)算，否則可能會(huì)導(dǎo)致存儲(chǔ)引擎無法正確的使用索引，從而影響了查詢的效率。

 select * from test where id + 1 = 50;
 select * from test where month(updateTime) = 7;

6.正確的使用聯(lián)合索引

使用聯(lián)合索引的時(shí)候，注意最左匹配原則。

JOIN 的優(yōu)化

優(yōu)化子查詢

盡量使用 join 語句來替代子查詢，因?yàn)樽硬樵兪乔短撞樵儯短撞樵儠?huì)新建創(chuàng)建一張臨時(shí)表，而臨時(shí)表的創(chuàng)建與銷毀會(huì)占用一定的系統(tǒng)資源以及花費(fèi)一定的時(shí)間，同時(shí)對(duì)于返回結(jié)果集比較大的子查詢，其對(duì)查詢性能的影響更大。

小表驅(qū)動(dòng)大表

關(guān)聯(lián)查詢的時(shí)候要拿小表去驅(qū)動(dòng)大表，因?yàn)殛P(guān)聯(lián)的時(shí)候，MySQL 內(nèi)部會(huì)遍歷驅(qū)動(dòng)表，再去連接被驅(qū)動(dòng)表。

select name from 小表 left join 大表;

適當(dāng)增加冗余字段

增加冗余字段可以減少大量的連表查詢，因?yàn)槎鄰埍淼倪B表查詢性能很低，所有可以適當(dāng)?shù)脑黾尤哂嘧侄?，以減少多張表的關(guān)聯(lián)查詢，這是以空間換時(shí)間的優(yōu)化策略。

避免使用 JOIN 關(guān)聯(lián)太多表

《阿里巴巴 Java 開發(fā)手冊(cè)》規(guī)定不要 join 超過三張表，第一 join 太多降低查詢的速度，第二 join 的 buffer 會(huì)占用更多的內(nèi)存。

排序優(yōu)化

利用索引掃描做排序

MySQL 有兩種方式生成有序結(jié)果：一是對(duì)結(jié)果集進(jìn)行排序的操作，而是按照索引順序掃描得出的結(jié)果，索引是排好序的數(shù)據(jù)結(jié)果，自然是有序的。

但是如果索引不能覆蓋查詢所需列（覆蓋索引），就會(huì)沒掃描一條記錄回表查詢一次（逐個(gè)獲?。?，這個(gè)讀操作是隨機(jī) IO，通常會(huì)比順序全表掃描還慢，有時(shí)會(huì)直接放棄使用索引轉(zhuǎn)為全表掃描。

因此，在設(shè)計(jì)索引時(shí)，盡可能使用同一個(gè)索引既滿足排序又用于查找行。

-- 索引(a,b,c)
select b,c from test where a like 'aa%' order by b,c;

只有當(dāng)索引的列順序和order by子句的順序完全一致，并且所有列的排序方向都一樣時(shí)，才能夠使用索引來對(duì)結(jié)果做排序。

UNION 優(yōu)化

條件下推

MySQL 處理 union 的策略是先創(chuàng)建臨時(shí)表，然后將各個(gè)查詢結(jié)果填充到臨時(shí)表中最后再來做查詢，很多優(yōu)化策略在 union 查詢中都會(huì)失效，因?yàn)樗鼰o法利用索引。

所以需要將where、limit等子句下推到 union 的各個(gè)子查詢中，以便優(yōu)化器可以充分利用這些條件進(jìn)行優(yōu)化。

此外，除非確實(shí)需要服務(wù)器去推，一定要試用union all，如果不加all關(guān)鍵字，MySQL 會(huì)給臨時(shí)表加上 distinct選項(xiàng)，這會(huì)導(dǎo)致對(duì)整個(gè)臨時(shí)表做唯一性檢查，代價(jià)很高。

到此這篇關(guān)于MySQL中慢SQL優(yōu)化的不同方式介紹的文章就介紹到這了,更多相關(guān)MySQL慢SQL優(yōu)化內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論