狠狠色丁香婷婷综合尤物/久久精品综合一区二区三区/中国有色金属学报/国产日韩欧美在线观看 - 国产一区二区三区四区五区tv

LOGO OA教程 ERP教程 模切知識交流 PMS教程 CRM教程 開發文檔 其他文檔  
 
網站管理員

聊聊SQL優化的幾個小技巧
admin
2024年2月7日 18:52 本文熱度 610

sql優化是一個大家都比較關注的熱門話題，無論你在面試，還是工作中，都很有可能會遇到。

那么，如何優化Sql呢？本章節分享了12個小技巧，我們一起來學習下。

1、避免使用select * 查詢

很多時候，為了使用起來方便簡單，我們喜歡直接使用select * 來查詢數據。

反例：


select * from TP_SYS_USER_0118;


但實際開發過程中，我們可能并不需要返回所有的字段列，而是其中的某幾列，這個時候建議大家直接列出查詢字段。

正例：


select OBJECT_ID,LOGIN_NAME,ENAME from TP_SYS_USER_0118;


使用select * 的弊端：

  • 1.性能問題：SELECT * 會檢索表中所有列的數據，包括可能不需要的列，如果表中有大量列或存在大字段的列，將會嚴重影響性能。

  • 2.安全性問題：SELECT * 查詢可能返回敏感數據，通過明確定義所需的列，可以減少意外暴露敏感信息的風險。

2、使用union all替換union

union：union操作符會合并兩個查詢結果集，并去除重復的行，只保留一個副本。


select OBJECT_ID, LOGIN_NAME, ENAME
from TP_SYS_USER_0118
union
select OBJECT_ID, LOGIN_NAME, ENAMEfrom TP_SYS_USER;


union all：union all 不去除重復行，直接將兩個查詢結果集合并在一起。


select OBJECT_ID, LOGIN_NAME, ENAME
from TP_SYS_USER_0118
union all
select OBJECT_ID, LOGIN_NAME, ENAMEfrom TP_SYS_USER;


從性能層面分析：

  • 1、由于 union 需要執行去重操作，因此在處理大量數據時，性能可能較低。而union all 則是直接合并結果，不需要去重。

在實際使用過程中，我們還是要視情況而定，比如說我們就是需要去重復的行數據，則需要使用union。

3、避免使用子查詢

如果我們想要從兩張或者多張表中查詢到數據，通常的實現方式：連接查詢或者子查詢。

子查詢的例子如下：


select s1.OBJECT_ID, s1.LOGIN_NAME, s1.ENAME
from TP_SYS_USER s1
where OBJECT_ID in (select s2.OBJECT_ID from TP_DIM_OBJECT s2);


網上查詢有關不建議使用子查詢的緣由是：需要創建臨時表，查詢完畢后，需要再刪除這些臨時表，有一些額外的性能消耗。

建議調整為連接查詢：


select s1.OBJECT_ID, s1.LOGIN_NAME, s1.ENAME from TP_SYS_USER s1
inner join TP_DIM_OBJECT s2 
on s1.OBJECT_ID=s2.OBJECT_ID;


4、多表查詢時一定要以小驅大

例如我們使用left join 連表查詢：

場景一：以大表驅動小表


# TP_SYS_USER大表驅動TP_SYS_USER_0118小表
select s1.OBJECT_ID, s1.LOGIN_NAME, s1.ENAME, s2.OBJECT_ID, s2.LOGIN_NAME, s2.ENAME
from TP_SYS_USER s1 left join TP_SYS_USER_0118 s2 
on s1.OBJECT_ID = s2.OBJECT_ID;


通過explain分析SQL的執行計劃：

我們發現兩個表的type都是ALL：表示全表分析，然后才找到匹配的行，我們可以通過字段rows看到查詢的行數據。

場景二:以小表驅動大表


# TP_SYS_USER_0118小表驅動TP_SYS_USER大表
select s1.OBJECT_ID, s1.LOGIN_NAME, s1.ENAME, s2.OBJECT_ID, s2.LOGIN_NAME, s2.ENAME
from TP_SYS_USER_0118 s1 left join TP_SYS_USER s2 
on s1.OBJECT_ID = s2.OBJECT_ID;


通過explain分析SQL的執行計劃：

我們可以看到s2表的type是eq_ref：表示查詢時命中主鍵 primary key 或者 unique key 索引，這里使用的是primary key。并且查詢rows的行數是1

通過explain分析我們可以很明顯的對比出來，用小表驅動大表的時候，查詢效率更高些。

或者我們也可以使用inner join 來替換left join的以小表驅動大表的方案。

5、不要使用like左模糊和全模糊查詢

場景一：使用like右模糊查詢


# USER_CODE存在索引
select * from TP_SYS_USER where USER_CODE like 'yg02%';


通過explain分析SQL的執行計劃：

我們可以看到key（實際使用的索引）是有值的：

執行結果中key有值：表示查詢中實際使用的索引為IP_SYS_USER_USER_CODE。

場景二：使用like左模糊查詢


# USER_CODE存在索引
select * from TP_SYS_USER where USER_CODE like '%yg02';


通過explain分析SQL的執行計劃：

執行結果中key為null表示沒有使用索引查詢。

場景三：使用like全模糊查詢


# USER_CODE存在索引 
select * from TP_SYS_USER where USER_CODE like '%yg02%';


通過explain分析SQL的執行計劃：

執行結果中key為null表示沒有使用索引查詢。

通過以上幾種場景對比，我們可以看到like左模糊查詢和全模糊查詢都是沒有走索引的，所以查詢效率較低，我們不建議這么使用。

6、exists&in的合理利用

exists&in一般用于子查詢。


# 使用in子查詢select s1.* from TP_DIM_OBJECT s1where OBJECT_ID in (select s2.OBJECT_ID from TP_SYS_USER s2);
# 使用exists子查詢select s1.* from TP_DIM_OBJECT s1where exists(select 1 from TP_SYS_USER s2 where s1.OBJECT_ID=s2.OBJECT_ID); s1.OBJECT_ID=s2.OBJECT_ID);


  • exists：一般情況下，如果子查詢的數據量大，可能exists會更高效，因為它只關系是否存在給外表匹配的行，而不需要返回具體的數據。

  • in：使用in一般會先進行內表查詢，然后對外表查詢，匹配結果集；當子查詢尋得結果集較小，外表較大時，使用in效率更高。

7、在表中增加索引，優先考慮where和order by使用到的字段

通過為字段添加索引，來提升查詢效率，例如：


# USER_CODE 添加索引
select s1.OBJECT_ID, s1.LOGIN_NAME, s1.ENAME from TP_SYS_USER s1 where USER_CODE='yg02';


8、避免在索引上使用內置函數

反例:


SELECT * FROM user WHERE DATE_ADD(birthday,INTERVAL 7 DAY) >=NOW();


正例：


SELECT * FROM user WHERE birthday >= DATE_ADD(NOW(),INTERVAL 7 DAY);


使用內置函數會導致索引失效。

9、!=、<>、not in、not like、or...要慎用

例如如下幾種場景：已知USER_CODE存在索引

通過explain執行計劃分析，共通之處是key為null，表示沒有走索引，也就意味著存在的索引USER_CODE并沒有發揮作用，索引失效。

10、提升group by的效率

在實際業務，我們經常性的會用到group by 來分組獲取數據，不知道小伙伴是否有這樣的習慣：先group by 然后在通過having過濾條件。

反例：


select TYPE_ID, count(*)from TP_DIM_OBJECTgroup by TYPE_IDhaving TYPE_ID != '11ecfdf11bef0e5d968093c522e0eb78';


分組是一個消耗性能的動作，我們為什么不先加過濾條件，縮小范圍數據范圍在分組呢？

正例：


select TYPE_ID, count(*)from TP_DIM_OBJECTwhere TYPE_ID != '11ecfdf11bef0e5d968093c522e0eb78'group by TYPE_ID;


使用where條件在分組前，就把多余的數據過濾掉了，這樣分組時效率就會更高一些。

11、明確僅返回一條數據的語句可以使用limit 1

在業務開發過程中，我們有沒有遇到過這樣的場景，按照時間排序，我們只需要獲取最新的數據。

例如：


select s1.OBJECT_ID, s1.LOGIN_NAME, s1.ENAMEfrom TP_SYS_USER s1order by CREATE_DATE desc;


在業務邏輯中，我們可能通過代碼邏輯底層使用如上SQL獲取到的數據集合，然后在通過collection.get(0)，獲取到第一條數據。

雖然這種做法沒啥問題，但是它的效率很低，怎么優化呢？


select s1.OBJECT_ID, s1.LOGIN_NAME, s1.ENAMEfrom TP_SYS_USER s1order by CREATE_DATE desc limit 1;


如果我們很明確我們的業務需求，就是獲取最新的數據，我們可以直接在排序后加上limit 1，表示只獲取結果的最新1條。

12、業務邏輯盡量批量化完成

如果存在業務需求，我們需要插一批數據入庫。

場景一：執行單次插入操作：


for(Order order: list){orderMapper.insert(order):}


執行SQL:


insert into order(id,code,user_id) values(123,'001',100);


這個過程是在for循環中執行的，我們需要多次的請求數據執行插入操作。

場景二：執行批量插入操作：


orderMapper.insertBatch(list):


執行SQL:


insert into order(id,code,user_id) values(123,'001',100),(124,'002',100),(125,'003',101);


這個過程，我們可以一次完成，不需要多次請求數據庫。相比較場景一的多次請求，相對而言我們批量插入的操作會大大提升客戶端的請求性能。

如果批量插入的數據量過大，我們也建議分開執行，比如200條一次。


該文章在 2024/2/7 18:52:31 編輯過
關鍵字查詢
相關文章
正在查詢...
點晴ERP是一款針對中小制造業的專業生產管理軟件系統,系統成熟度和易用性得到了國內大量中小企業的青睞。
點晴PMS碼頭管理系統主要針對港口碼頭集裝箱與散貨日常運作、調度、堆場、車隊、財務費用、相關報表等業務管理，結合碼頭的業務特點，圍繞調度、堆場作業而開發的。集技術的先進性、管理的有效性于一體，是物流碼頭及其他港口類企業的高效ERP管理信息系統。
點晴WMS倉儲管理系統提供了貨物產品管理,銷售管理,采購管理,倉儲管理,倉庫管理,保質期管理,貨位管理,庫位管理,生產管理,WMS管理系統,標簽打印,條形碼,二維碼管理,批號管理軟件。
點晴免費OA是一款軟件和通用服務都免費，不限功能、不限時間、不限用戶的免費OA協同辦公管理系統。
Copyright 2010-2025 ClickSun All Rights Reserved