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

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

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

1、避免使用select * 查詢

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

反例：

select * from TP_SYS_USER_0118;

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

正例：

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, ENAMEfrom TP_SYS_USER_0118unionselect OBJECT_ID, LOGIN_NAME, ENAMEfrom TP_SYS_USER;

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

select OBJECT_ID, LOGIN_NAME, ENAMEfrom TP_SYS_USER_0118union allselect OBJECT_ID, LOGIN_NAME, ENAMEfrom TP_SYS_USER;

從性能層面分析：

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

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

3、避免使用子查詢

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

子查詢的例子如下：

select s1.OBJECT_ID, s1.LOGIN_NAME, s1.ENAMEfrom TP_SYS_USER s1where OBJECT_ID in (select s2.OBJECT_ID from TP_DIM_OBJECT s2);

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

建議調整為連接查詢：

select s1.OBJECT_ID, s1.LOGIN_NAME, s1.ENAME from TP_SYS_USER s1inner 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.ENAMEfrom TP_SYS_USER s1 left join TP_SYS_USER_0118 s2 on s1.OBJECT_ID = s2.OBJECT_ID;

通過explain分析SQL的執(zhí)行計劃：

我們發(fā)現兩個表的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.ENAMEfrom TP_SYS_USER_0118 s1 left join TP_SYS_USER s2 on s1.OBJECT_ID = s2.OBJECT_ID;

通過explain分析SQL的執(zhí)行計劃：

我們可以看到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的執(zhí)行計劃：

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

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

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

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

通過explain分析SQL的執(zhí)行計劃：

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

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

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

通過explain分析SQL的執(zhí)行計劃：

執(zhí)行結果中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、在表中增加索引，優(yōu)先考慮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執(zhí)行計劃分析，共通之處是key為null，表示沒有走索引，也就意味著存在的索引USER_CODE并沒有發(fā)揮作用，索引失效。

10、提升group by的效率

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

反例：

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

分組是一個消耗性能的動作，我們?yōu)槭裁床幌燃舆^濾條件，縮小范圍數據范圍在分組呢？

正例：

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

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

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

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

例如：

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

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

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

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

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

12、業(yè)務邏輯盡量批量化完成

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

場景一：執(zhí)行單次插入操作：

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

執(zhí)行SQL:

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

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

場景二：執(zhí)行批量插入操作：

orderMapper.insertBatch(list):

執(zhí)行SQL:

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

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

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