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

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

1、避免使用select * 查詢

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

反例：

select * from TP_SYS_USER_0118;

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

正例：

select OBJECT_ID,LOGIN_NAME,ENAME from TP_SYS_USER_0118;

使用select * 的弊端：

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

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

2、使用union all替換union

union：union操作符會(huì)合并兩個(gè)查詢結(jié)果集，并去除重復(fù)的行，只保留一個(gè)副本。

select OBJECT_ID, LOGIN_NAME, ENAMEfrom TP_SYS_USER_0118unionselect OBJECT_ID, LOGIN_NAME, ENAMEfrom TP_SYS_USER;

union all：union all 不去除重復(fù)行，直接將兩個(gè)查詢結(jié)果集合并在一起。

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

從性能層面分析：

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

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

3、避免使用子查詢

如果我們想要從兩張或者多張表中查詢到數(shù)據(jù)，通常的實(shí)現(xiàn)方式：連接查詢或者子查詢。

子查詢的例子如下：

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);

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

建議調(diào)整為連接查詢：

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、多表查詢時(shí)一定要以小驅(qū)大

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

場(chǎng)景一：以大表驅(qū)動(dòng)小表

# TP_SYS_USER大表驅(qū)動(dòng)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í)行計(jì)劃：

我們發(fā)現(xiàn)兩個(gè)表的type都是ALL：表示全表分析，然后才找到匹配的行，我們可以通過字段rows看到查詢的行數(shù)據(jù)。

場(chǎng)景二:以小表驅(qū)動(dòng)大表

# TP_SYS_USER_0118小表驅(qū)動(dòng)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í)行計(jì)劃：

我們可以看到s2表的type是eq_ref：表示查詢時(shí)命中主鍵 primary key 或者 unique key 索引，這里使用的是primary key。并且查詢r(jià)ows的行數(shù)是1

通過explain分析我們可以很明顯的對(duì)比出來，用小表驅(qū)動(dòng)大表的時(shí)候，查詢效率更高些。

或者我們也可以使用inner join 來替換left join的以小表驅(qū)動(dòng)大表的方案。

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

場(chǎng)景一：使用like右模糊查詢

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

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

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

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

場(chǎng)景二：使用like左模糊查詢

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

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

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

場(chǎng)景三：使用like全模糊查詢

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

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

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

通過以上幾種場(chǎng)景對(duì)比，我們可以看到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：一般情況下，如果子查詢的數(shù)據(jù)量大，可能exists會(huì)更高效，因?yàn)樗魂P(guān)系是否存在給外表匹配的行，而不需要返回具體的數(shù)據(jù)。

• in：使用in一般會(huì)先進(jìn)行內(nèi)表查詢，然后對(duì)外表查詢，匹配結(jié)果集；當(dāng)子查詢尋得結(jié)果集較小，外表較大時(shí)，使用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、避免在索引上使用內(nèi)置函數(shù)

反例:

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

正例：

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

使用內(nèi)置函數(shù)會(huì)導(dǎo)致索引失效。

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

例如如下幾種場(chǎng)景：已知USER_CODE存在索引

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

10、提升group by的效率

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

反例：

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

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

正例：

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

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

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

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

例如：

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

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

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

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

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

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

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

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

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

執(zhí)行SQL:

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

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

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

orderMapper.insertBatch(list):

執(zhí)行SQL:

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

這個(gè)過程，我們可以一次完成，不需要多次請(qǐng)求數(shù)據(jù)庫。相比較場(chǎng)景一的多次請(qǐng)求，相對(duì)而言我們批量插入的操作會(huì)大大提升客戶端的請(qǐng)求性能。

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