前言
本文將結合實例demo,闡述30條有關於優化SQL的建議,多數是實際開發中總結出來的,希望對大家有幫助。
1、查詢SQL儘量不要使用select *,而是select具體字段。
反例子:
<code>select
*from
employee; /<code>
正例子:
<code>select
id
,name
from
employee; /<code>
理由:
- 只取需要的字段,節省資源、減少網絡開銷。
- select * 進行查詢時,很可能就不會使用到覆蓋索引了,就會造成回表查詢。
2、如果知道查詢結果只有一條或者只要最大/最小一條記錄,建議用limit 1
假設現在有employee員工表,要找出一個名字叫jay的人.
<code>CREATE
TABLE
`employee`
(`id`
int
(11
)NOT
NULL
,`name`
varchar
(255
)DEFAULT
NULL
,`age`
int
(11
)DEFAULT
NULL
,`date`
datetimeDEFAULT
NULL
,`sex`
int
(1
)DEFAULT
NULL
, PRIMARYKEY
(`id`
) )ENGINE
=InnoDB
DEFAULT
CHARSET
=utf8; /<code>
反例:
<code>select
id
,name
from
employeewhere
name
\='jay'
/<code>
正例
<code>select
id
,name
from
employeewhere
name
='jay'
limit
1
; /<code>
理由:
- 加上limit 1後,只要找到了對應的一條記錄,就不會繼續向下掃描了,效率將會大大提高。
- 當然,如果name是唯一索引的話,是不必要加上limit 1了,因為limit的存在主要就是為了防止全表掃描,從而提高性能,如果一個語句本身可以預知不用全表掃描,有沒有limit ,性能的差別並不大。
3、應儘量避免在where子句中使用or來連接條件
新建一個user表,它有一個普通索引userId,表結構如下:
<code>CREATE
TABLE
`user`
(`id`
int
(11
)NOT
NULL
AUTO_INCREMENT,`userId`
int
(11
)NOT
NULL
,`age`
int
(11
)NOT
NULL
,`name`
varchar
(255
)NOT
NULL
, PRIMARYKEY
(`id`
),KEY
`idx_userId`
(`userId`
) )ENGINE
=InnoDB
DEFAULT
CHARSET
=utf8; /<code>
假設現在需要查詢userid為1或者年齡為18歲的用戶,很容易有以下SQL
反例:
<code>select
*from
user
where
userid=1
or
age =18
/<code>
正例:
<code>//使用union allselect
*from
user
where
userid=1
union
all
select
*from
user
where
age =18
//或者分開兩條sql
寫:select
*from
user
where
userid=1
select
*from
user
where
age =18
/<code>
理由:
- 使用or可能會使索引失效,從而全表掃描。
對於or+沒有索引的age這種情況,假設它走了userId的索引,但是走到age查詢條件時,它還得全表掃描,也就是需要三步過程:全表掃描+索引掃描+合併如果它一開始就走全表掃描,直接一遍掃描就完事。mysql是有優化器的,處於效率與成本考慮,遇到or條件,索引可能失效,看起來也合情合理。
4、優化limit分頁
我們日常做分頁需求時,一般會用 limit 實現,但是當偏移量特別大的時候,查詢效率就變得低下。
反例:
<code>select
id
,name
,agefrom
employeelimit
10000
,10
/<code>
正例:
<code>//方案一 :返回上次查詢的最大記錄(偏移量)select
id
,name
from
employeewhere
id
>10000
limit
10.
//方案二:order
by
+ 索引select
id
,name
from
employeeorder
by
id
limit
10000
,10
//方案三:在業務允許的情況下限制頁數:/<code>
理由:
- 當偏移量最大的時候,查詢效率就會越低,因為Mysql並非是跳過偏移量直接去取後面的數據,而是先把偏移量+要取的條數,然後再把前面偏移量這一段的數據拋棄掉再返回的。
- 如果使用優化方案一,返回上次最大查詢記錄(偏移量),這樣可以跳過偏移量,效率提升不少。
- 方案二使用order by+索引,也是可以提高查詢效率的。
- 方案三的話,建議跟業務討論,有沒有必要查這麼後的分頁啦。因為絕大多數用戶都不會往後翻太多頁。
5、優化你的like語句
日常開發中,如果用到模糊關鍵字查詢,很容易想到like,但是like很可能讓你的索引失效。
反例:
<code>select
userId,name
from
user
where
userIdlike
'%123'
; /<code>
正例:
<code>select
userId,name
from
user
where
userIdlike
'123%'
; /<code>
理由:
- 把%放前面,並不走索引,如下:
- 把% 放關鍵字後面,還是會走索引的。如下:
6、使用where條件限定要查詢的數據,避免返回多餘的行
假設業務場景是這樣:查詢某個用戶是否是會員。曾經看過老的實現代碼是這樣。。。
反例:
<code>List userIds = sqlMap.queryList("select userId from user where isVip=1"
);boolean
isVip = userIds.contains(userId); /<code>
正例:
<code>Long userId = sqlMap.queryObject("select
userIdfrom
user
where
userId='userId'
and
isVip='1'
") boolean isVip = userId!=null;
/<code>
理由:
- 需要什麼數據,就去查什麼數據,避免返回不必要的數據,節省開銷。
7、儘量避免在索引列上使用mysql的內置函數
業務需求:查詢最近七天內登陸過的用戶(假設loginTime加了索引)
反例:
<code>select
userId,loginTimefrom
loginuserwhere
Date
\_ADD(loginTime,Interval
7
DAY
) \>\=now
();/<code>
正例:
<code>explain
select
userId,loginTimefrom
loginuserwhere
loginTime \>\=Date
\_ADD(NOW
(),INTERVAL
-7
DAY
);/<code>
理由:
- 索引列上使用mysql的內置函數,索引失效
- 如果索引列不加內置函數,索引還是會走的。
8、應儘量避免在where子句中對字段進行表達式操作,這將導致系統放棄使用索引而進行全表掃
反例:
<code>select
*from
user
where
age-1
=10
; /<code>
正例:
<code>select
*from
user
where
age =11
; /<code>
理由:
- 雖然age加了索引,但是因為對它進行運算,索引直接迷路了。。。
9、Inner join 、left join、right join,優先使用Inner join,如果是left join,左邊表結果儘量小
- Inner join 內連接,在兩張表進行連接查詢時,只保留兩張表中完全匹配的結果集
- left join 在兩張表進行連接查詢時,會返回左表所有的行,即使在右表中沒有匹配的記錄。
- right join 在兩張表進行連接查詢時,會返回右表所有的行,即使在左表中沒有匹配的記錄。
都滿足SQL需求的前提下,推薦優先使用Inner join(內連接),如果要使用left join,左邊表數據結果儘量小,如果有條件的儘量放到左邊處理。
反例:
<code>select
*from
tab1 t1left
join
tab2 t2on
t1.size = t2.sizewhere
t1.id>2
;/<code>
正例:
<code>select
*from
(select
*from
tab1where
id
>2
) t1left
join
tab2 t2on
t1.size = t2.size;/<code>
理由:
- 如果inner join是等值連接,或許返回的行數比較少,所以性能相對會好一點。
- 同理,使用了左連接,左邊表數據結果儘量小,條件儘量放到左邊處理,意味著返回的行數可能比較少。
10、應儘量避免在where子句中使用!=或<>操作符,否則將引擎放棄使用索引而進行全表掃描。
反例:
<code>select
age,name
from
user
where
age <>18
; /<code>
正例:
<code>select
age,name
from
user
where
age <18
;select
age,name
from
user
where
age >18
; /<code>
理由:
- 使用!=和 <>很可能會讓索引失效
11、使用聯合索引時,注意索引列的順序,一般遵循最左匹配原則。
表結構:(有一個聯合索引idxuseridage,userId在前,age在後)
<code>CREATE
TABLE
`user`
(`id`
int
(11
)NOT
NULL
AUTO_INCREMENT,`userId`
int
(11
)NOT
NULL
,`age`
int
(11
)DEFAULT
NULL
,`name`
varchar
(255
)NOT
NULL
, PRIMARYKEY
(`id`
),KEY
`idx_userid_age`
(`userId`
,`age`
)USING
BTREE )ENGINE
=InnoDB
AUTO_INCREMENT=2
DEFAULT
CHARSET
=utf8; /<code>
反例:
<code>select
*from
user
where
age =10
; /<code>
正例:
<code> //符合最左匹配原則select
*from
user
where
userid =10
; /<code>
理由:
- 當我們創建一個聯合索引的時候,如(k1,k2,k3),相當於創建了(k1)、(k1,k2)和(k1,k2,k3)三個索引,這就是最左匹配原則。
- 聯合索引不滿足最左原則,索引一般會失效,但是這個還跟Mysql優化器有關的。
12、對查詢進行優化,應考慮在where及order by涉及的列上建立索引,儘量避免全表掃描。
反例:
<code>select
*from
user
where
address ='深圳'
order
by
age ; /<code>
正例:
<code>添加索引alter
table
user
add
index
idx_address_age (address,age) /<code>
13、如果插入數據過多,考慮批量插入。
反例:
<code>for
(Useru
:list){INSERT
into
user
(name,age)values
(#name#,#age#) } /<code>
正例:
<code>//一次500批量插入,分批進行insert
into
user
(name
,age)values
"list"item="item"
index
="index"
separator=","
> ( /<code>
理由:
- 批量插入性能好,更加省時間
打個比喻:假如你需要搬一萬塊磚到樓頂,你有一個電梯,電梯一次可以放適量的磚(最多放500),你可以選擇一次運送一塊磚,也可以一次運送500塊磚,你覺得哪個時間消耗大?
14、在適當的時候,使用覆蓋索引。
覆蓋索引能夠使得你的SQL語句不需要回表,僅僅訪問索引就能夠得到所有需要的數據,大大提高了查詢效率。
反例:
<code>select
*from
userwhere
userid like'%123%'
/<code>
正例:
<code>//id為主鍵,那麼為普通索引,即覆蓋索引登場了。select
id
,name
from
user
where
useridlike
'%123%'
; /<code>
15、慎用distinct關鍵字
distinct 關鍵字一般用來過濾重複記錄,以返回不重複的記錄。在查詢一個字段或者很少字段的情況下使用時,給查詢帶來優化效果。但是在字段很多的時候使用,卻會大大降低查詢效率。
反例:
<code>SELECT
DISTINCT
*from
user
; /<code>
正例:
<code>select
DISTINCT
name
from
user
; /<code>
理由:
- 帶distinct的語句cpu時間和佔用時間都高於不帶distinct的語句。因為當查詢很多字段時,如果使用distinct,數據庫引擎就會對數據進行比較,過濾掉重複數據,然而這個比較、過濾的過程會佔用系統資源,cpu時間。
16、刪除冗餘和重複索引
反例:
<code>KEY`idx_userId`
(`userId`
) KEY`idx_userId_age`
(`userId`
,`age`
) /<code>
正例:
<code>KEY
`idx_userId_age
` (`userId`
,`age`
) /<code>
理由:
- 重複的索引需要維護,並且優化器在優化查詢的時候也需要逐個地進行考慮,這會影響性能的。
17、如果數據量較大,優化你的修改/刪除語句。
避免同時修改或刪除過多數據,因為會造成cpu利用率過高,從而影響別人對數據庫的訪問。
反例:
<code>delete
from
user where id <100000
;for
(User user:list){delete
from
user; } /<code>
正例:
<code>//分批進行刪除,如每次500delete
user
where
id
<500
delete
productwhere
id
>=500
and
id
<1000
; /<code>
理由:
- 一次性刪除太多數據,可能會有lock wait timeout exceed的錯誤,所以建議分批操作。
18、where子句中考慮使用默認值代替null。
反例:
<code>select
*from
user
where
ageis
not
null
; /<code>
正例:
<code>//設置0為默認值select
*from
user
where
age>0
; /<code>
理由:
- 並不是說使用了is null 或者 is not null 就會不走索引了,這個跟mysql版本以及查詢成本都有關。
如果mysql優化器發現,走索引比不走索引成本還要高,肯定會放棄索引,這些條件 !=,>isnull,isnotnull經常被認為讓索引失效,其實是因為一般情況下,查詢的成本高,優化器自動放棄索引的。
- 如果把null值,換成默認值,很多時候讓走索引成為可能,同時,表達意思會相對清晰一點。
19、不要有超過5個以上的表連接
- 連表越多,編譯的時間和開銷也就越大。
- 把連接表拆開成較小的幾個執行,可讀性更高。
- 如果一定需要連接很多表才能得到數據,那麼意味著糟糕的設計了。
20、exist&in的合理利用
假設表A表示某企業的員工表,表B表示部門表,查詢所有部門的所有員工,很容易有以下SQL:
<code>select
*from
Awhere
deptIdin
(
select
deptIdfrom
B); /<code>
這樣寫等價於:
先查詢部門表B
<code>select
deptIdfrom
B/<code>
再由部門deptId,查詢A的員工
<code>select
*from
Awhere
A.deptId = B.deptId/<code>
可以抽象成這樣的一個循環:
<code>List<> resultSet ;for
(int
i=0
;ifor(int
j=0
;jif(A[i].id==B[j].id) { resultSet.add
(A[i]);break
; } } } /<code>
顯然,除了使用in,我們也可以用exists實現一樣的查詢功能,如下:
- select * from A where exists (select 1 from B where A.deptId = B.deptId);
因為exists查詢的理解就是,先執行主查詢,獲得數據後,再放到子查詢中做條件驗證,根據驗證結果(true或者false),來決定主查詢的數據結果是否得意保留。
那麼,這樣寫就等價於:
<code>select
*from
A,先從A表做循環select
*from
Bwhere
A.deptId = B.deptId,再從B表做循環./<code>
同理,可以抽象成這樣一個循環:
<code>List<> resultSet ;for
(int
i=0
;ifor(int
j=0
;jif(A[i].deptId==B[j].deptId) { resultSet.add
(A[i]);break
; } } } /<code>
數據庫最費勁的就是跟程序鏈接釋放。假設鏈接了兩次,每次做上百萬次的數據集查詢,查完就走,這樣就只做了兩次;相反建立了上百萬次鏈接,申請鏈接釋放反覆重複,這樣系統就受不了了。即mysql優化原則,就是小表驅動大表,小的數據集驅動大的數據集,從而讓性能更優。
因此,我們要選擇最外層循環小的,也就是,如果B的數據量小於A,適合使用in,如果B的數據量大於A,即適合選擇exist。
21、儘量用union all替換 union
如果檢索結果中不會有重複的記錄,推薦union all 替換 union。
反例:
<code>select
*from
user
where
userid=1
union
select
*from
user
where
age =10
/<code>
正例:
<code>select
*from
user
where
userid=1
union
all
select
*from
user
where
age =10
/<code>
理由:
- 如果使用union,不管檢索結果有沒有重複,都會嘗試進行合併,然後在輸出最終結果前進行排序。如果已知檢索結果沒有重複記錄,使用union all 代替union,這樣會提高效率。
22、索引不宜太多,一般5個以內。
- 索引並不是越多越好,索引雖然提高了查詢的效率,但是也降低了插入和更新的效率。
- insert或update時有可能會重建索引,所以建索引需要慎重考慮,視具體情況來定。
- 一個表的索引數最好不要超過5個,若太多需要考慮一些索引是否沒有存在的必要。
23、儘量使用數字型字段,若只含數值信息的字段儘量不要設計為字符型
反例:
<code>`king_id`
varchar(20
) NOT NULL COMMENT'守護者Id'
/<code>
正例:
<code>`king_id`
int
(11
) NOT NULL COMMENT'守護者Id'
/<code>
理由:
- 相對於數字型字段,字符型會降低查詢和連接的性能,並會增加存儲開銷。
24、索引不適合建在有大量重複數據的字段上,如性別這類型數據庫字段。
因為SQL優化器是根據表中數據量來進行查詢優化的,如果索引列有大量重複數據,Mysql查詢優化器推算髮現不走索引的成本更低,很可能就放棄索引了。
25、儘量避免向客戶端返回過多數據量。
假設業務需求是,用戶請求查看自己最近一年觀看過的直播數據。
反例:
<code>//一次性查詢所有數據回來select
*from
LivingInfowhere
watchId =useIdand
watchTime >=Date_sub
(now
(),Interval
1
Y) /<code>
正例:
<code>//分頁查詢select
*from
LivingInfowhere
watchId =useIdand
watchTime>=Date_sub
(now
(),Interval
1
Y)limit
offset
,pageSize //如果是前端分頁,可以先查詢前兩百條記錄,因為一般用戶應該也不會往下翻太多頁,select
*from
LivingInfowhere
watchId =useIdand
watchTime>=Date_sub
(now
(),Interval
1
Y)limit
200
; /<code>
26、當在SQL語句中連接多個表時,請使用表的別名,並把別名前綴於每一列上,這樣語義更加清晰。
反例:
<code>select
*from
Ainner
join
Bon
A.deptId = B.deptId; /<code>
正例:
<code>select
memeber.name,deptment.deptNamefrom
Amember
inner
join
B deptmenton
member.deptId = deptment.deptId; /<code>
27、儘可能使用varchar/nvarchar 代替 char/nchar。
反例:
<code>`deptName`char
(100
) DEFAULTNULL
COMMENT'部門名稱'
/<code>
正例:
<code>`deptName` varchar(100
)DEFAULT
NULL
COMMENT'部門名稱'
理由:/<code>
- 因為首先變長字段存儲空間小,可以節省存儲空間。
- 其次對於查詢來說,在一個相對較小的字段內搜索,效率更高。
28、為了提高group by 語句的效率,可以在執行到該語句前,把不需要的記錄過濾掉。
反例:
<code>select
job,avg
(salary)from
employeegroup
by
jobhaving
job ='president'
or
job ='managent'
/<code>
正例:
<code>select
job,avg
(salary)from
employeewhere
job ='president'
or
job ='managent'
group
by
job; /<code>
29、如果字段類型是字符串,where時一定用引號括起來,否則索引失效
反例:
<code>select
*from
user
where
userid =123
; /<code>
正例:
<code>select
*from
user
where
userid ='123'
; /<code>
理由:
- 為什麼第一條語句未加單引號就不走索引了呢?這是因為不加單引號時,是字符串跟數字的比較,它們類型不匹配,MySQL會做隱式的類型轉換,把它們轉換為浮點數再做比較。
30、使用explain 分析你SQL的計劃
日常開發寫SQL的時候,儘量養成一個習慣吧。用explain分析一下你寫的SQL,尤其是走不走索引這一塊。
<code>explain
select
*from
user
where
userid =10086
or
age =18
; /<code>
原文:https://segmentfault.com/a/1190000022389741