優(yōu)化思路

慢sql的優(yōu)化無非是從兩個(gè)方向著手

• SQL語句本身的優(yōu)化
• 據(jù)庫設(shè)計(jì)的優(yōu)化

下面進(jìn)行漸進(jìn)式的分享一些常見優(yōu)化手段

避免查詢不必要的列

查詢應(yīng)該精準(zhǔn)的查出需要的列，對于select * 的寫法要避免，因?yàn)樗凶侄尾槌鰜聿粌Hsql查詢執(zhí)行慢，若是直接返回給前端，大量的數(shù)據(jù)也會影響網(wǎng)絡(luò)傳輸效率。

分頁優(yōu)化

對于數(shù)據(jù)量特別大，這時(shí)分頁會比較深，查詢掃描的數(shù)據(jù)量會比較大效率自然低，我們就需要進(jìn)行分頁優(yōu)化

假設(shè)我們有一個(gè)包含大量訂單記錄的訂單表，其中每個(gè)訂單都有一個(gè)唯一的不包含業(yè)務(wù)邏輯的主鍵，并且我們想要查詢最近一個(gè)月的訂單并按照訂單id從小到大進(jìn)行分頁顯示某頁。

假設(shè)出現(xiàn)深分頁的sql如下：

select * from orders where order_date >= date_sub(now(), interval 1 month)
 order by id limit 100000, 10;

執(zhí)行此SQL時(shí)需要先掃描到100000行，然后再去取10行，但是隨著掃描的記錄數(shù)越多，SQL的性能就會越差，因?yàn)閽呙璧挠涗浽蕉啵琈ySQL需要掃描越多的數(shù)據(jù)來定位到具體的多少行，這樣耗費(fèi)大量的 IO 成本和時(shí)間成本。

對于解決該深分頁問題通常有兩種方法

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

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

優(yōu)化后sql:

select o.*
from (
    select id
    from orders
    where order_date >= date_sub(now(), interval 1 month)
    order by id
    limit 100000, 10
) as sub
join orders as o on sub.id = o.id;

優(yōu)化后SQL中的子查詢只取主鍵id，可避免通過二級索引中的主鍵去回表查詢，這樣性能會快一些。

2.id偏移量

偏移量就是找到 limit 第一個(gè)參數(shù)對應(yīng)的主鍵值，根據(jù)這個(gè)主鍵值再去過濾并 limit，這種方法又稱為基于游標(biāo)的分頁。基于游標(biāo)的分頁的前提是需要保證主鍵或排序列的連續(xù)性、唯一性。

優(yōu)化后sql:

select *
from orders
where id >= (select id from orders order by id limit 100000, 1)
order by id
limit 10;

這種方法相對于原來直接使用偏移量和限制結(jié)果數(shù)量的方式，可以在大數(shù)據(jù)集上提供更穩(wěn)定和一致的性能，因?yàn)樗恍枰獟呙韬吞^大量的行。

索引優(yōu)化

通過合理的設(shè)計(jì)和使用索引，能夠有效優(yōu)化sql性能，這也是我們使用最多的手段。

下面介紹一下如何進(jìn)行索引優(yōu)化：

使用覆蓋索引

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

示例：

select product_name, price
from products
where category_id = 1;
create index idx_category_id on products (category_id, product_name, price);

避免使用or查詢

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

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

SQL中，不等于操作符會導(dǎo)致查詢引擎放棄查詢索引，引起全表掃描，即使比較的字段上有索引。解決方法:通過把不等于操作符改成 or，可以使用索引，避免全表掃描

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

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

適當(dāng)?shù)厥褂们熬Y索引，可以降低索引的空間占用，提高索引的查詢效率。比如，郵箱的后綴都是固定的“@xxx.com”，那么類似這種后面幾位為固定值的字段就非常適合定義為前綴索引

create index idx_email_prefix on users (email(6)); -- 假設(shè)后綴長度為6

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

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

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

select order_id
from orders
where total_amount / 2 > 100

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

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

JOIN優(yōu)化

優(yōu)化子查詢

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

小表驅(qū)動大表

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

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ā)手冊》規(guī)定不要 join 超過三張表，第一join 太多降低査詢的速度，第二 join 的buffer 會占用更多的內(nèi)存。

排序優(yōu)化

利用索引掃描做排序

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

但是如果索引不能覆蓋查詢所需列(覆蓋索引)，就會每掃描一條記錄回表查詢一次(逐個(gè)獲取)，這個(gè)讀操作是隨機(jī) IO，通常會比順序全表掃描還慢，有時(shí)會直接放棄使用索引轉(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í)，才能夠使用索引來對結(jié)果做排序。

UNION 優(yōu)化

條件下推

MySQL處理 union 的策略是先創(chuàng)建臨時(shí)表，然后將各個(gè)查詢結(jié)果填充到臨時(shí)表中最后再來做查詢，很多優(yōu)化策略在 union 查詢中都會失效，因?yàn)樗鼰o法利用索引。所以需要將 where、limit 等子句下推到 union 的各個(gè)子查詢中，以便優(yōu)化器可以充分利用這些條件進(jìn)行優(yōu)化。

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

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

最新評論