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

避免不必要的列

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

分頁優(yōu)化

在數(shù)據(jù)量比較大，分頁比較深的情況下，需要考慮分頁的優(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ù)行，而不是通過原來的二級索引提取數(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 第一個參數(shù)對應(yīng)的主鍵值，根據(jù)這個主鍵值再去過濾并 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 使用二級索引查詢數(shù)據(jù)時會回表，但是如果索引的葉節(jié)點(diǎn)中已經(jīng)包含要查詢的字段，那它沒有必要再回表查詢了，這就叫覆蓋索引，還有一個簡單的理解查詢列都是索引列。

 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 查詢可能會導(dǎo)致索引失效，高版本引入了索引合并，解決了這個問題，不過建議大家在實(shí)際使用中還是規(guī)范寫法，能不用就少用。

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

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

解決方法：通過把不等于操作符改成 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)算，否則可能會導(dǎo)致存儲引擎無法正確的使用索引，從而影響了查詢的效率。

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

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

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

JOIN 的優(yōu)化

優(yōu)化子查詢

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

小表驅(qū)動大表

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

select name from 小表 left join 大表;

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

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

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

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

排序優(yōu)化

利用索引掃描做排序

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

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

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

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

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

UNION 優(yōu)化

條件下推

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

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

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

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

最新評論