柚子快報(bào)邀請(qǐng)碼778899分享：MySQL慢SQL優(yōu)化方案匯總

http://yzkb.51969.com/

?

??個(gè)人主頁(yè):? ? ?蒾酒

?系列專欄：《mysql經(jīng)驗(yàn)總結(jié)》

目錄

寫(xiě)在前面

優(yōu)化思路

避免查詢不必要的列

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

索引優(yōu)化

JOIN優(yōu)化

排序優(yōu)化

UNION 優(yōu)化

寫(xiě)在最后

?最近發(fā)現(xiàn)了一個(gè)巨牛的人工智能學(xué)習(xí)網(wǎng)站，通俗易懂，風(fēng)趣幽默，忍不住分享一下給大家。

? ??點(diǎn)擊跳轉(zhuǎn)到學(xué)習(xí)網(wǎng)站

寫(xiě)在前面

本文介紹了MySQL常見(jiàn)的優(yōu)化慢sql的手段，堅(jiān)持看完相信對(duì)你有幫助。

同時(shí)歡迎訂閱MySQL系列專欄，持續(xù)分享MySQL的使用經(jīng)驗(yàn)。

優(yōu)化思路

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

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

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

避免查詢不必要的列

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

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

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

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

假設(shè)出現(xiàn)深分頁(yè)的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的性能就會(huì)越差，因?yàn)閽呙璧挠涗浽蕉啵琈ySQL需要掃描越多的數(shù)據(jù)來(lái)定位到具體的多少行，這樣耗費(fèi)大量的 IO 成本和時(shí)間成本。

對(duì)于解決該深分頁(yè)問(wèn)題通常有兩種方法

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

先通過(guò) where 條件提取出主鍵，在將該表與原數(shù)據(jù)表關(guān)聯(lián)，通過(guò)主鍵 id 提取數(shù)據(jù)行，而不是通過(guò)原來(lái)的二級(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，可避免通過(guò)二級(jí)索引中的主鍵去回表查詢，這樣性能會(huì)快一些。

2.id偏移量

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

優(yōu)化后sql:

select *

from orders

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

order by id

limit 10;

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

索引優(yōu)化

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

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

使用覆蓋索引

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

示例：

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 或者子查詢來(lái)替代，因?yàn)樵缙诘腗ySQL版本使用 or 查詢可能會(huì)導(dǎo)致索引失效，高版本引入了索引合并，解決了這個(gè)問(wèn)題，不過(guò)建議大家在實(shí)際使用中還是規(guī)范寫(xiě)法，能不用就少用。

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

SQL中，不等于操作符會(huì)導(dǎo)致查詢引擎放棄查詢索引，引起全表掃描，即使比較的字段上有索引。解決方法:通過(guò)把不等于操作符改成 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è)后綴長(zhǎng)度為6

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

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

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

select order_id

from orders

where total_amount / 2 > 100

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

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

JOIN優(yōu)化

優(yōu)化子查詢

盡量使用 Join 語(yǔ)句來(lái)替代子?xùn)嗽?，因?yàn)樽訓(xù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 開(kāi)發(fā)手冊(cè)》規(guī)定不要 join 超過(guò)三張表，第一join 太多降低査詢的速度，第二 join 的buffer 會(huì)占用更多的內(nèi)存。

排序優(yōu)化

利用索引掃描做排序

MYSQL有兩種方式生成有序結(jié)果:一是對(duì)結(jié)果集進(jìn)行排序的操作，二是按照索引順序掃描得出的結(jié)果，索引是排好序的數(shù)據(jù)結(jié)構(gòu)，自然是有序的。 但是如果索引不能覆蓋查詢所需列(覆蓋索引)，就會(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í)，才能夠使用索引來(lái)對(duì)結(jié)果做排序。

UNION 優(yōu)化

條件下推

MySQL處理 union 的策略是先創(chuàng)建臨時(shí)表，然后將各個(gè)查詢結(jié)果填充到臨時(shí)表中最后再來(lái)做查詢，很多優(yōu)化策略在 union 查詢中都會(huì)失效，因?yàn)樗鼰o(wú)法利用索引。所以需要將 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à)很高。

寫(xiě)在最后

MySQL優(yōu)化慢SQL的6種方式到這里就結(jié)束了，本文介紹了常見(jiàn)慢sql優(yōu)化的有效方式。任何問(wèn)題評(píng)論區(qū)或私信討論，歡迎指正。

柚子快報(bào)邀請(qǐng)碼778899分享：MySQL慢SQL優(yōu)化方案匯總

http://yzkb.51969.com/

相關(guān)鏈接