柚子快報邀請碼778899分享:MySQL慢SQL優(yōu)化方案匯總
柚子快報邀請碼778899分享:MySQL慢SQL優(yōu)化方案匯總
?
??個人主頁:? ? ?蒾酒
?系列專欄:《mysql經(jīng)驗總結》
目錄
寫在前面
優(yōu)化思路
避免查詢不必要的列
分頁優(yōu)化
索引優(yōu)化
JOIN優(yōu)化
排序優(yōu)化
UNION 優(yōu)化
寫在最后
?最近發(fā)現(xiàn)了一個巨牛的人工智能學習網(wǎng)站,通俗易懂,風趣幽默,忍不住分享一下給大家。
? ??點擊跳轉到學習網(wǎng)站
寫在前面
本文介紹了MySQL常見的優(yōu)化慢sql的手段,堅持看完相信對你有幫助。
同時歡迎訂閱MySQL系列專欄,持續(xù)分享MySQL的使用經(jīng)驗。
優(yōu)化思路
慢sql的優(yōu)化無非是從兩個方向著手
SQL語句本身的優(yōu)化據(jù)庫設計的優(yōu)化
下面進行漸進式的分享一些常見優(yōu)化手段
避免查詢不必要的列
查詢應該精準的查出需要的列,對于select * 的寫法要避免,因為所有字段查出來不僅sql查詢執(zhí)行慢,若是直接返回給前端,大量的數(shù)據(jù)也會影響網(wǎng)絡傳輸效率。
分頁優(yōu)化
對于數(shù)據(jù)量特別大,這時分頁會比較深,查詢掃描的數(shù)據(jù)量會比較大效率自然低,我們就需要進行分頁優(yōu)化
假設我們有一個包含大量訂單記錄的訂單表,其中每個訂單都有一個唯一的不包含業(yè)務邏輯的主鍵,并且我們想要查詢最近一個月的訂單并按照訂單id從小到大進行分頁顯示某頁。
假設出現(xiàn)深分頁的sql如下:
select * from orders where order_date >= date_sub(now(), interval 1 month)
order by id limit 100000, 10;
執(zhí)行此SQL時需要先掃描到100000行,然后再去取10行,但是隨著掃描的記錄數(shù)越多,SQL的性能就會越差,因為掃描的記錄越多,MySQL需要掃描越多的數(shù)據(jù)來定位到具體的多少行,這樣耗費大量的 IO 成本和時間成本。
對于解決該深分頁問題通常有兩種方法
1.延遲關聯(lián)
先通過 where 條件提取出主鍵,在將該表與原數(shù)據(jù)表關聯(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 第一個參數(shù)對應的主鍵值,根據(jù)這個主鍵值再去過濾并 limit,這種方法又稱為基于游標的分頁。基于游標的分頁的前提是需要保證主鍵或排序列的連續(xù)性、唯一性。
優(yōu)化后sql:
select *
from orders
where id >= (select id from orders order by id limit 100000, 1)
order by id
limit 10;
這種方法相對于原來直接使用偏移量和限制結果數(shù)量的方式,可以在大數(shù)據(jù)集上提供更穩(wěn)定和一致的性能,因為它不需要掃描和跳過大量的行。
索引優(yōu)化
通過合理的設計和使用索引,能夠有效優(yōu)化sql性能,這也是我們使用最多的手段。
下面介紹一下如何進行索引優(yōu)化:
使用覆蓋索引
InnoDB使用二級索引查詢數(shù)據(jù)時會回表,但是如果索引的葉節(jié)點中已經(jīng)包含要查詢的字段,那它沒有必要再回表查詢了,這就叫覆蓋索引,還有一個簡單的理解查詢列都是索引列。
示例:
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 或者子查詢來替代,因為早期的MySQL版本使用 or 查詢可能會導致索引失效,高版本引入了索引合并,解決了這個問題,不過建議大家在實際使用中還是規(guī)范寫法,能不用就少用。
避免使用 != 或者 <>操作符
SQL中,不等于操作符會導致查詢引擎放棄查詢索引,引起全表掃描,即使比較的字段上有索引。解決方法:通過把不等于操作符改成 or,可以使用索引,避免全表掃描
id <>'aaa'
id >'aaa'or id<'aaa
適當使用前綴索引
適當?shù)厥褂们熬Y索引,可以降低索引的空間占用,提高索引的查詢效率。比如,郵箱的后綴都是固定的“@xxx.com”,那么類似這種后面幾位為固定值的字段就非常適合定義為前綴索引
create index idx_email_prefix on users (email(6)); -- 假設后綴長度為6
需要注意的是,前綴索引也存在缺點,MySQL無法利用前綴索引做 order by和 group by 操作,也無法作為覆蓋索引。
避免列上函數(shù)運算
要避免在列字段上進行算術運算或其他表達式運算,否則可能會導致存儲引擎無法正確使用索引,從而影響了查詢的效率。
select order_id
from orders
where total_amount / 2 > 100
正確使用聯(lián)合索引
使用聯(lián)合索引的時候,注意最左匹配原則。?
JOIN優(yōu)化
優(yōu)化子查詢
盡量使用 Join 語句來替代子査詢,因為子査詢是嵌套查詢,而嵌套查詢會新創(chuàng)建一張臨時表,而臨時表的創(chuàng)建與銷毀會占用一定的系統(tǒng)資源以及花費一定的時間,同時對于返回結果集比較大的子查詢,其對查詢性能的影響更大?
小表驅動大表
關聯(lián)查詢的時候要拿小表去驅動大表,因為關聯(lián)的時候,MySQL內部會遍歷驅動表,再去連接被驅動表
select name from小表 left join 大表;
適當增加冗余字段
增加冗余字段可以減少大量的連表查詢,因為多張表的連表查詢性能很低,所有可以適當?shù)脑黾尤哂嘧侄?,以減少多張表的關聯(lián)查詢,這是以空間換時間的優(yōu)化策略。
避免使用 JOIN 關聯(lián)太多的表
《阿里巴巴 Java 開發(fā)手冊》規(guī)定不要 join 超過三張表,第一join 太多降低査詢的速度,第二 join 的buffer 會占用更多的內存。
排序優(yōu)化
利用索引掃描做排序
MYSQL有兩種方式生成有序結果:一是對結果集進行排序的操作,二是按照索引順序掃描得出的結果,索引是排好序的數(shù)據(jù)結構,自然是有序的。 但是如果索引不能覆蓋查詢所需列(覆蓋索引),就會每掃描一條記錄回表查詢一次(逐個獲取),這個讀操作是隨機 IO,通常會比順序全表掃描還慢,有時會直接放棄使用索引轉為全表掃描。因此,在設計索引時,盡可能使用同一個索引既滿足排序又用于查找行。
#索引為 a,b,c
select b,c from test where a like 'aa%' order by b,c;
只有當索引的列順序和 ORDER BY 子句的順序完全一致,并且所有列的排序方向都一樣時,才能夠使用索引來對結果做排序。
UNION 優(yōu)化
條件下推
MySQL處理 union 的策略是先創(chuàng)建臨時表,然后將各個查詢結果填充到臨時表中最后再來做查詢,很多優(yōu)化策略在 union 查詢中都會失效,因為它無法利用索引。所以需要將 where、limit 等子句下推到 union 的各個子查詢中,以便優(yōu)化器可以充分利用這些條件進行優(yōu)化。
此外,除非確實需要服務器去重,一定要使用 union all,如果不加 all 關鍵字,MySQL 會給臨時表加上distinct 選項,這會導致對整個臨時表做唯一性檢查,代價很高。
寫在最后
MySQL優(yōu)化慢SQL的6種方式到這里就結束了,本文介紹了常見慢sql優(yōu)化的有效方式。任何問題評論區(qū)或私信討論,歡迎指正。
柚子快報邀請碼778899分享:MySQL慢SQL優(yōu)化方案匯總
相關鏈接
本文內容根據(jù)網(wǎng)絡資料整理,出于傳遞更多信息之目的,不代表金鑰匙跨境贊同其觀點和立場。
轉載請注明,如有侵權,聯(lián)系刪除。