前言

顯而易見，SQL運(yùn)用廣 ，但數(shù)據(jù)庫(kù)堆棧性能問題也事故百出。

前段時(shí)間我們一個(gè)同事犯了一個(gè)大錯(cuò)，一個(gè)關(guān)聯(lián)查詢的sql沒加分區(qū)過(guò)濾數(shù)據(jù)盡可能縮小數(shù)據(jù)范圍，中間結(jié)果集很大，導(dǎo)致整個(gè)集群空間撐爆 ，間接導(dǎo)致集群內(nèi)部的其他應(yīng)用也崩潰，這都屬于是嚴(yán)重的生產(chǎn)事故。

sql生產(chǎn)大致事件如下：

sql優(yōu)化是一個(gè)大家都比較關(guān)注的熱門話題。

如果某天你負(fù)責(zé)的某個(gè)線上接口，出現(xiàn)了性能問題，需要做優(yōu)化。那么你首先想到的很有可能是優(yōu)化sql語(yǔ)句，因?yàn)樗母脑斐杀鞠鄬?duì)于代碼來(lái)說(shuō)也要小得多。

此篇文章從12個(gè)方面，分享了sql優(yōu)化的一些小技巧，希望對(duì)你有所幫助

那么，如何優(yōu)化sql語(yǔ)句呢？

1.列裁剪

即避免使用select * ,可以只讀取查詢中所需要用到的列，而忽略其它列。

在實(shí)際業(yè)務(wù)場(chǎng)景中，可能我們真正需要使用的只有其中一兩列。查了很多數(shù)據(jù)，但是不用，傻傻增加數(shù)據(jù)庫(kù)堆棧性能，如：內(nèi)存或者cpu。

此外，多查出來(lái)的數(shù)據(jù)，通過(guò)網(wǎng)絡(luò)IO傳輸?shù)倪^(guò)程中，也會(huì)增加數(shù)據(jù)傳輸?shù)臅r(shí)間。

舉個(gè)栗子：

select * from word_text;  【x錯(cuò)】
select word,100,num from word_text; 【√正確】

2.分區(qū)裁剪 提升group by的效率

栗子：

select userId ,userAccount from f_user group by userId where userId < 500; 【x錯(cuò)】
select userId ,userAccount from f_user where userId < 500  group by userId ; 【√正確】

我們有很多業(yè)務(wù)場(chǎng)景需要使用group by關(guān)鍵字，它主要的功能是去重和分組。

通常它會(huì)跟having一起配合使用，表示分組后再根據(jù)一定的條件過(guò)濾數(shù)據(jù)。

這種寫法性能不好，它先把所有的訂單根據(jù)用戶id分組之后，再去過(guò)濾用戶id大于等于200的用戶。

分組是一個(gè)相對(duì)耗時(shí)的操作，為什么我們不先縮小數(shù)據(jù)的范圍之后，再分組呢？

使用where條件在分組前，就把多余的數(shù)據(jù)過(guò)濾掉了，這樣分組時(shí)效率就會(huì)更高一些。

其實(shí)這是一種思路，不僅限于group by的優(yōu)化。我們的sql語(yǔ)句在做一些耗時(shí)的操作之前，應(yīng)盡可能縮小數(shù)據(jù)范圍，這樣能提升sql整體的性能。

3.謂詞下推

predicate

所謂的謂詞下推就是在join之前的階段提前對(duì)表進(jìn)行過(guò)濾優(yōu)化，使得最后參與join的表的數(shù)據(jù)量更小

謂詞下推 --不掃描全表的表才能實(shí)現(xiàn)謂詞下推，下面謂詞是指name字段

    select 
    * 
    from
    (
    select * from table1
    ) t1
    join
    (
    select * from table2
    ) t2
    on 
    t1.id=t2.id
    where 
    t1.name='user1' and t2.name='user2';

    -- 謂詞下推
    -- 針對(duì)內(nèi)連接和全連接無(wú)論篩選條件寫在哪里都不能實(shí)現(xiàn)謂詞下推
    select 
    * 
    from
    (
    select * from table1 where name='user1'
    ) t1
    join
    (
    select * from table2 where name='user2'
    ) t2
    on 
    t1.id=t2.id

    -- 左連接謂詞下推
    -- 如下能夠?qū)崿F(xiàn)謂詞下推的例子，如下t1表是不能夠?qū)崿F(xiàn)謂詞下推，會(huì)掃描全表（左表是全表），但是t2表是可以實(shí)現(xiàn)謂詞下推，
    -- join的時(shí)候只掃描滿足where條件的那部分?jǐn)?shù)據(jù)
    select 
    * 
    from
    (
    select * from table1 where name='user1'
    ) t1
    left join
    (
    select * from table2 where name='user2'
    ) t2
    on 
    t1.id=t2.id;

    -- 右連接時(shí)謂詞下推
    select 
    * 
    from
    (
    select * from table1 where name='user1'
    ) t1
    right join
    (
    select * from table2 where name='user2'
    ) t2
    on 

4.用union all代替union

sql語(yǔ)句使用union關(guān)鍵字后，可以獲取排重后的數(shù)據(jù)。

而如果使用union all關(guān)鍵字，可以獲取所有數(shù)據(jù)，包含重復(fù)的數(shù)據(jù)。

反栗子：

(select userId ,userAccount from f_user where userId=1 ) 
union 
((select userId ,userAccount from f_user where userId=2);

排重的過(guò)程需要遍歷、排序和比較，它更耗時(shí)，更消耗cpu資源。

所以如果能用union all的時(shí)候，盡量不用union。

正栗子：

(select userId ,userAccount from f_user where userId=1 ) 
union all
((select userId ,userAccount from f_user where userId=2);

除非是有些特殊的場(chǎng)景，比如union all之后，結(jié)果集中出現(xiàn)了重復(fù)數(shù)據(jù)，而業(yè)務(wù)場(chǎng)景中是不允許產(chǎn)生重復(fù)數(shù)據(jù)的，這時(shí)可以使用union。

5.小表驅(qū)動(dòng)大表

 in和exists用法

小表驅(qū)動(dòng)大表，也就是說(shuō)用小表的數(shù)據(jù)集驅(qū)動(dòng)大表的數(shù)據(jù)集。

假如有f_post（帖子表）和f_user_relationship_thumb(用戶關(guān)系表)兩張表，其中f_post表有10000條數(shù)據(jù)，而f_user_relationship_thumb表有100條數(shù)據(jù)。

這時(shí)如果想查一下，僅查我的好友帖子列表。

可以使用in關(guān)鍵字實(shí)現(xiàn)：**(備注：**以下是從用戶關(guān)系表中獲得我的好友，再通過(guò)好友userAccount關(guān)聯(lián)帖子表中的userAccount獲得好友帖子數(shù)據(jù))

 select * FROM f_post where userAccount
        in (
            SELECT userAccount FROM f_user_relationship_thumb
            WHERE friendsId = #{userId} AND reviewStatus = 1
            UNION
            SELECT friendsUserAccount as userAccount FROM f_user_relationship_thumb
            WHERE userId = #{userId} AND reviewStatus = 1
            LIMIT 500
        )

也可以使用exists關(guān)鍵字實(shí)現(xiàn)：

 select * FROM f_post where userAccount
        exists (
            SELECT userAccount FROM f_user_relationship_thumb
            WHERE friendsId = #{userId} AND reviewStatus = 1
            UNION
            SELECT friendsUserAccount as userAccount FROM f_user_relationship_thumb
            WHERE userId = #{userId} AND reviewStatus = 1
            LIMIT 500
        )

前面提到的這種業(yè)務(wù)場(chǎng)景，使用in關(guān)鍵字去實(shí)現(xiàn)業(yè)務(wù)需求，更加合適。

為什么呢？

因?yàn)槿绻鹲ql語(yǔ)句中包含了in關(guān)鍵字，則它會(huì)優(yōu)先執(zhí)行in里面的子查詢語(yǔ)句，然后再執(zhí)行in外面的語(yǔ)句。如果in里面的數(shù)據(jù)量很少，作為條件查詢速度更快。

而如果sql語(yǔ)句中包含了exists關(guān)鍵字，它優(yōu)先執(zhí)行exists左邊的語(yǔ)句（即主查詢語(yǔ)句）。然后把它作為條件，去跟右邊的語(yǔ)句匹配。如果匹配上，則可以查詢出數(shù)據(jù)。如果匹配不上，數(shù)據(jù)就被過(guò)濾掉了。

這個(gè)需求中，其中f_post表有10000條數(shù)據(jù)，而f_user_relationship_thumb表有100條數(shù)據(jù)。f_post表是大表，f_user_relationship_thumb表是小表。如果f_post表在左邊，則用in關(guān)鍵字性能更好。

結(jié)論：

• 其實(shí)in、exists在算法復(fù)雜度Log N層面來(lái)講，大差不差
• in 適用于左邊大表，右邊小表。
• exists 適用于左邊小表，右邊大表。

不管是用in，還是exists關(guān)鍵字，其核心思想都是用小表驅(qū)動(dòng)大表。

真實(shí)栗子如圖：

6.批量操作 減少對(duì)數(shù)據(jù)庫(kù)的操作次數(shù)

如果你有一批數(shù)據(jù)經(jīng)過(guò)業(yè)務(wù)處理之后，需要插入數(shù)據(jù)，該怎么辦？

栗子：批量上傳圖片

反栗子：

for(photoNames photoName: list){
   postMapper.insert(photoName):
}

在循環(huán)中逐條插入數(shù)據(jù)。

insert into f_post(id,photoName) 
values(001,'/assets/userPost/photoName1');
...

該操作需要多次請(qǐng)求數(shù)據(jù)庫(kù)，才能完成這批數(shù)據(jù)的插入。

但眾所周知，我們?cè)诖a中，每次遠(yuǎn)程請(qǐng)求數(shù)據(jù)庫(kù)，是會(huì)消耗一定性能的。而如果我們的代碼需要請(qǐng)求多次數(shù)據(jù)庫(kù)，才能完成本次業(yè)務(wù)功能，勢(shì)必會(huì)消耗更多的性能。

那么如何優(yōu)化呢？

正例√：

postMapper.insertBatch(list):sql

提供一個(gè)批量插入數(shù)據(jù)的方法。

insert into f_post(id,photoName) 
values(001,'/assets/userPost/photoName1'),(001,'/assets/userPost/photoName2'),(001,'/assets/userPost/photoName3');

這時(shí)只需要遠(yuǎn)程請(qǐng)求一次數(shù)據(jù)庫(kù)，sql性能會(huì)得到提升，數(shù)據(jù)量越多，提升越大。

但需要注意的是，不建議一次批量操作太多的數(shù)據(jù)，如果數(shù)據(jù)太多數(shù)據(jù)庫(kù)響應(yīng)也會(huì)很慢。批量操作需要把握一個(gè)度，建議每批數(shù)據(jù)盡量控制在500以內(nèi)。如果數(shù)據(jù)多于500，則分多批次處理。

7.巧用limit

舉個(gè)栗子，有時(shí)候，我們需要查詢某些數(shù)據(jù)中的第一條，比如：查詢某個(gè)用戶下的第一個(gè)帖子，想看看他第一次的發(fā)帖時(shí)間。

反栗子：

select id, createTime 
 from f_post
where userAccount='500佰' 
order by createTime asc;

根據(jù)用戶userAccount查詢帖子，按帖子創(chuàng)建時(shí)間排序，先查出該用戶所有的帖子數(shù)據(jù)，得到一個(gè)帖子集合。然后在代碼中，獲取第一個(gè)元素的數(shù)據(jù)，即第一個(gè)帖子的數(shù)據(jù)，就能獲取第一次發(fā)帖時(shí)間。

List<Post> list = postMapper.getPostList();
Post post = list.get(0);

雖說(shuō)這種做法在功能上沒有問題，但它的效率非常不高，需要先查詢出所有的數(shù)據(jù)，有點(diǎn)浪費(fèi)資源。

那么，如何優(yōu)化呢？

正栗子：

select id, createTime 
 from f_post
where userAccount='500佰' 
order by createTime asc
limit 1;

使用limit 1，只返回該用戶第一次發(fā)帖的那一條數(shù)據(jù)即可。

8. in中值太多

簡(jiǎn)單說(shuō)，對(duì)于批量查詢接口，我們通常會(huì)使用in關(guān)鍵字過(guò)濾出數(shù)據(jù)。比如：想通過(guò)指定的一些id，批量查詢出用戶信息。

sql語(yǔ)句如下：

select * FROM f_post where userId in ( 1,2,3...9999999);

如果我們不做任何限制，該查詢語(yǔ)句一次性可能會(huì)查詢出非常多的數(shù)據(jù)，很容易導(dǎo)致接口超時(shí)。

這時(shí)該怎么辦呢？

select * FROM f_post where userId in (1,2,3...100) limit 500;

可以在sql中對(duì)數(shù)據(jù)用limit做限制。

不過(guò)我們更多的是要在業(yè)務(wù)代碼中加限制，偽代碼如下：

public List<Post> getPost(List<String> uids) {
   if(CollectionUtils.isEmpty(uids)) {
      return null;
   }
   if(uids.size() > 500) {
      throw new BusinessException("每次最多允許查詢500條記錄")
   }
   return mapper.getPostList(uids);
}

還有一個(gè)方案就是：如果uids超過(guò)500條記錄，可以分批用多線程去查詢數(shù)據(jù)。每批只查500條記錄，最后把查詢到的數(shù)據(jù)匯總到一起返回。不過(guò)這只是一個(gè)臨時(shí)方案，不適合于uids實(shí)在太多的場(chǎng)景。因?yàn)閡ids太多，即使能快速查出數(shù)據(jù)，但如果返回的數(shù)據(jù)量太大了，網(wǎng)絡(luò)傳輸也是非常消耗性能的，要注意這一點(diǎn)。

9.高效的分頁(yè)查詢

簡(jiǎn)單說(shuō)，有時(shí)候，列表頁(yè)在查詢數(shù)據(jù)時(shí)，為了避免一次性返回過(guò)多的數(shù)據(jù)影響接口性能，我們一般會(huì)對(duì)查詢接口去做分頁(yè)處理。

分頁(yè)一般用的limit關(guān)鍵字：

select userId,userName,userAvatar 
from f_user limit 10,20;

如果表中數(shù)據(jù)量少，用limit關(guān)鍵字做分頁(yè)，沒啥問題。但如果表中數(shù)據(jù)量很多，用它就會(huì)出現(xiàn)性能問題。

比如現(xiàn)在分頁(yè)參數(shù)變成了：

select userId,userName,userAvatar 
from f_user limit 1000000,20;

這樣會(huì)查到1000020條數(shù)據(jù)，然后丟棄前面的1000000條，只查后面的20條數(shù)據(jù)，這個(gè)是消浪費(fèi)據(jù)庫(kù)資源和增加堆棧性能。

那么，這種海量數(shù)據(jù)該怎么分頁(yè)呢？

所謂高效分頁(yè),優(yōu)化sql：

select userId,userName,userAvatar 
from f_user where userId > 1000000 limit 20;

先找到上次分頁(yè)最大的id，然后利用id上的索引查詢。不過(guò)該方案，要求id是連續(xù)的，并且有序的。

還能使用between優(yōu)化分頁(yè)。

select userId,userName,userAvatar 
from f_user where userId between 1000000 and 1000020;

需要注意的是between要在唯一索引上分頁(yè)，不然會(huì)出現(xiàn)每頁(yè)大小不一致的問題。

10.用連接查詢代替子查詢

為啥？原因在哪？

原因是執(zhí)行子查詢時(shí)，需要?jiǎng)?chuàng)建臨時(shí)表，查詢完畢后，需要再刪除這些臨時(shí)表，有一些額外的性能消耗。

子查詢的栗子如下：

如果需要從兩張以上的表中查詢出數(shù)據(jù)的話，一般有兩種實(shí)現(xiàn)方式：子查詢 和 連接查詢。

 select * FROM f_post where userAccount
        in (
            SELECT userAccount FROM f_user_relationship_thumb
            WHERE friendsId = #{userId} AND reviewStatus = 1
            UNION
            SELECT friendsUserAccount as userAccount FROM f_user_relationship_thumb
            WHERE userId = #{userId} AND reviewStatus = 1
            LIMIT 500
        )

**執(zhí)行邏輯：**子查詢語(yǔ)句可以通過(guò)in關(guān)鍵字實(shí)現(xiàn)，一個(gè)查詢語(yǔ)句的條件落在另一個(gè)select語(yǔ)句的查詢結(jié)果中。程序先運(yùn)行在嵌套在最內(nèi)層的語(yǔ)句，再運(yùn)行外層的語(yǔ)句。

如果涉及的表數(shù)量不多的話，子查詢語(yǔ)句的優(yōu)點(diǎn)是簡(jiǎn)單，結(jié)構(gòu)化。

這時(shí)可以改成連接查詢減小臨時(shí)額外的性能消耗。具體栗子如下：

 select * FROM f_post where userAccount
        JOIN (
            SELECT userAccount FROM f_user_relationship_thumb
            WHERE friendsId = #{userId} AND reviewStatus = 1
            UNION
            SELECT friendsUserAccount as userAccount as remarks FROM f_user_relationship_thumb
            WHERE userId = #{userId} AND reviewStatus = 1
            LIMIT 500
        ) fr ON pm.userAccount = fr.userAccount

11.連續(xù)join的表不宜超過(guò)3個(gè)

根據(jù)阿里巴巴開發(fā)者手冊(cè)的規(guī)定，join表的數(shù)量不應(yīng)該超過(guò)3個(gè)。

連續(xù)join多表
select a.name,b.name.c.name,d.name,e.name,f.name
from a 
inner join b on a.id = b.a_id
inner join c on c.b_id = b.id
inner join d on d.c_id = c.id
inner join e on e.d_id = d.id
inner join f on f.e_id = e.id
inner join g on g.f_id = f.id

**原因：**如果join太多，在選擇索引的時(shí)候會(huì)非常復(fù)雜，很容易選錯(cuò)索引。

解決辦法：如果實(shí)現(xiàn)業(yè)務(wù)場(chǎng)景中需要查詢出另外幾張表中的數(shù)據(jù)，可以在a、b、c表中冗余專門的字段

12. join時(shí)要注意

涉及到多張表聯(lián)合查詢的時(shí)候，一般會(huì)使用join關(guān)鍵字。

而join使用最多的是left join和inner join。

• left join：求兩個(gè)表的交集外加左表剩下的數(shù)據(jù) 。
• inner join：求兩個(gè)表交集的數(shù)據(jù)。
• 另外不管是left join、inner join還是其他jion操作，切記一定不要使用多個(gè)連續(xù)的jion 這樣會(huì)極大的增加查詢數(shù)據(jù)庫(kù)堆棧。

如果兩張表使用inner join關(guān)聯(lián)，會(huì)自動(dòng)選擇兩張表中的小表，去驅(qū)動(dòng)大表，所以性能上不會(huì)有太大的問題。

如果兩張表使用left join關(guān)聯(lián)，會(huì)默認(rèn)用left join關(guān)鍵字左邊的表，去驅(qū)動(dòng)它右邊的表。如果左邊的表數(shù)據(jù)很多時(shí)，就會(huì)出現(xiàn)性能問題。

特別提醒：在用left join關(guān)聯(lián)查詢時(shí)，左邊要用小表，右邊可以用大表。如果能用inner join的地方，盡量少用left join。

閱讀到此，您已經(jīng)超越99%的技術(shù)boy

最后：

如果這篇sql優(yōu)化文章對(duì)您有所幫助，或者有所啟發(fā)的話，給作者一個(gè)小關(guān)，您的支持是的精神糧食。

到此這篇關(guān)于sql優(yōu)化之減少數(shù)據(jù)庫(kù)堆棧性能的12個(gè)小技巧總結(jié)的文章就介紹到這了,更多相關(guān)sql減少數(shù)據(jù)庫(kù)堆棧性能內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論