面試官：SQL優(yōu)化 21 連擊 + 思維導(dǎo)圖

1、反例

SELECT * FROM user

2、正例

SELECT id,username,tel FROM user

3、理由

1. 節(jié)省資源、減少網(wǎng)絡(luò)開銷。
2. 可能用到覆蓋索引，減少回表，提高查詢效率。

注意：為節(jié)省時間，下面的樣例字段都用*代替了。

二、避免在where子句中使用 or 來連接條件

1、反例

SELECT * FROM user WHERE id=1 OR salary=5000

2、正例

（1）使用union all

SELECT * FROM user WHERE id=1 
UNION ALL
SELECT * FROM user WHERE salary=5000

（2）分開兩條sql寫

SELECT * FROM user WHERE id=1

SELECT * FROM user WHERE salary=5000

3、理由

1. 使用or可能會使索引失效，從而全表掃描；
2. 對于or沒有索引的salary這種情況，假設(shè)它走了id的索引，但是走到salary查詢條件時，它還得全表掃描；
3. 也就是說整個過程需要三步：全表掃描+索引掃描+合并。如果它一開始就走全表掃描，直接一遍掃描就搞定；
4. 雖然mysql是有優(yōu)化器的，出于效率與成本考慮，遇到or條件，索引還是可能失效的；

三、盡量使用數(shù)值替代字符串類型

1、正例

1. 主鍵（id）：primary key優(yōu)先使用數(shù)值類型int，tinyint
2. 性別（sex）：0代表女，1代表男；數(shù)據(jù)庫沒有布爾類型，mysql推薦使用tinyint

2、理由

1. 因為引擎在處理查詢和連接時會逐個比較字符串中每一個字符；
2. 而對于數(shù)字型而言只需要比較一次就夠了；
3. 字符會降低查詢和連接的性能，并會增加存儲開銷；

四、使用varchar代替char

1、反例

`address` char(100) DEFAULT NULL COMMENT '地址'

2、正例

`address` varchar(100) DEFAULT NULL COMMENT '地址'

3、理由

1. varchar變長字段按數(shù)據(jù)內(nèi)容實際長度存儲，存儲空間小，可以節(jié)省存儲空間；
2. char按聲明大小存儲，不足補空格；
3. 其次對于查詢來說，在一個相對較小的字段內(nèi)搜索，效率更高；

五、技術(shù)延伸，char與varchar2的區(qū)別？

1、char的長度是固定的，而varchar2的長度是可以變化的。

比如，存儲字符串“101”，對于char(10)，表示你存儲的字符將占10個字節(jié)（包括7個空字符），在數(shù)據(jù)庫中它是以空格占位的，而同樣的varchar2(10)則只占用3個字節(jié)的長度，10只是最大值，當(dāng)你存儲的字符小于10時，按實際長度存儲。

2、char的效率比varchar2的效率稍高。

3、何時用char，何時用varchar2?

char和varchar2是一對矛盾的統(tǒng)一體，兩者是互補的關(guān)系，varchar2比char節(jié)省空間，在效率上比char會稍微差一點，既想獲取效率，就必須犧牲一點空間，這就是我們在數(shù)據(jù)庫設(shè)計上常說的“以空間換效率”。

varchar2雖然比char節(jié)省空間，但是假如一個varchar2列經(jīng)常被修改，而且每次被修改的數(shù)據(jù)的長度不同，這會引起“行遷移”現(xiàn)象，而這造成多余的I/O，是數(shù)據(jù)庫設(shè)計中要盡力避免的，這種情況下用char代替varchar2會更好一些。char中還會自動補齊空格，因為你insert到一個char字段自動補充了空格的,但是select后空格沒有刪除，因此char類型查詢的時候一定要記得使用trim，這是寫本文章的原因。

如果開發(fā)人員細化使用rpad()技巧將綁定變量轉(zhuǎn)換為某種能與char字段相比較的類型（當(dāng)然，與截斷trim數(shù)據(jù)庫列相比，填充綁定變量的做法更好一些，因為對列應(yīng)用函數(shù)trim很容易導(dǎo)致無法使用該列上現(xiàn)有的索引），可能必須考慮到經(jīng)過一段時間后列長度的變化。如果字段的大小有變化，應(yīng)用就會受到影響，因為它必須修改字段寬度。

正是因為以上原因，定寬的存儲空間可能導(dǎo)致表和相關(guān)索引比平常大出許多，還伴隨著綁定變量問題，所以無論什么場合都要避免使用char類型。

六、where中使用默認值代替null

1、反例

SELECT * FROM user WHERE age IS NOT NULL

2、正例

SELECT * FROM user WHERE age>0

3、理由

1. 并不是說使用了is null或者 is not null就會不走索引了，這個跟mysql版本以及查詢成本都有關(guān)；
2. 如果mysql優(yōu)化器發(fā)現(xiàn)，走索引比不走索引成本還要高，就會放棄索引，這些條件 !=，<>，is null，is not null經(jīng)常被認為讓索引失效；
3. 其實是因為一般情況下，查詢的成本高，優(yōu)化器自動放棄索引的；
4. 如果把null值，換成默認值，很多時候讓走索引成為可能，同時，表達意思也相對清晰一點；

七、避免在where子句中使用!=或<>操作符

1、反例

SELECT * FROM user WHERE salary!=5000

SELECT * FROM user WHERE salary<>5000

2、理由

1. 使用!=和<>很可能會讓索引失效
2. 應(yīng)盡量避免在where子句中使用!=或<>操作符，否則引擎將放棄使用索引而進行全表掃描
3. 實現(xiàn)業(yè)務(wù)優(yōu)先，實在沒辦法，就只能使用，并不是不能使用

八、inner join 、left join、right join，優(yōu)先使用inner join

三種連接如果結(jié)果相同，優(yōu)先使用inner join，如果使用left join左邊表盡量小。

• inner join 內(nèi)連接，只保留兩張表中完全匹配的結(jié)果集；
• left join會返回左表所有的行，即使在右表中沒有匹配的記錄；
• right join會返回右表所有的行，即使在左表中沒有匹配的記錄；

為什么？

• 如果inner join是等值連接，返回的行數(shù)比較少，所以性能相對會好一點；
• 使用了左連接，左邊表數(shù)據(jù)結(jié)果盡量小，條件盡量放到左邊處理，意味著返回的行數(shù)可能比較少；
• 這是mysql優(yōu)化原則，就是小表驅(qū)動大表，小的數(shù)據(jù)集驅(qū)動大的數(shù)據(jù)集，從而讓性能更優(yōu)；

九、提高group by語句的效率

1、反例

先分組，再過濾

select job, avg（salary） from employee 
group by job
having job ='develop' or job = 'test';

2、正例

先過濾，后分組

select job，avg（salary） from employee 
where job ='develop' or job = 'test' 
group by job;

3、理由

可以在執(zhí)行到該語句前，把不需要的記錄過濾掉

十、清空表時優(yōu)先使用truncate

truncate table在功能上與不帶 where子句的 delete語句相同：二者均刪除表中的全部行。但 truncate table比 delete速度快，且使用的系統(tǒng)和事務(wù)日志資源少。

delete語句每次刪除一行，并在事務(wù)日志中為所刪除的每行記錄一項。truncate table通過釋放存儲表數(shù)據(jù)所用的數(shù)據(jù)頁來刪除數(shù)據(jù)，并且只在事務(wù)日志中記錄頁的釋放。

truncate table刪除表中的所有行，但表結(jié)構(gòu)及其列、約束、索引等保持不變。新行標(biāo)識所用的計數(shù)值重置為該列的種子。如果想保留標(biāo)識計數(shù)值，請改用 DELETE。如果要刪除表定義及其數(shù)據(jù)，請使用 drop table語句。

對于由 foreign key約束引用的表，不能使用 truncate table，而應(yīng)使用不帶  where子句的 DELETE 語句。由于 truncate table不記錄在日志中，所以它不能激活觸發(fā)器。

truncate table不能用于參與了索引視圖的表。

十一、操作delete或者update語句，加個limit或者循環(huán)分批次刪除

1、降低寫錯SQL的代價

清空表數(shù)據(jù)可不是小事情，一個手抖全沒了，刪庫跑路？如果加limit，刪錯也只是丟失部分數(shù)據(jù)，可以通過binlog日志快速恢復(fù)的。

2、SQL效率很可能更高

SQL中加了limit 1，如果第一條就命中目標(biāo)return， 沒有limit的話，還會繼續(xù)執(zhí)行掃描表。

3、避免長事務(wù)

delete執(zhí)行時,如果age加了索引，MySQL會將所有相關(guān)的行加寫鎖和間隙鎖，所有執(zhí)行相關(guān)行會被鎖住，如果刪除數(shù)量大，會直接影響相關(guān)業(yè)務(wù)無法使用。

4、數(shù)據(jù)量大的話，容易把CPU打滿

如果你刪除數(shù)據(jù)量很大時，不加 limit限制一下記錄數(shù)，容易把cpu打滿，導(dǎo)致越刪越慢。

5、鎖表

一次性刪除太多數(shù)據(jù)，可能造成鎖表，會有l(wèi)ock wait timeout exceed的錯誤，所以建議分批操作。

十二、UNION操作符

UNION在進行表鏈接后會篩選掉重復(fù)的記錄，所以在表鏈接后會對所產(chǎn)生的結(jié)果集進行排序運算，刪除重復(fù)的記錄再返回結(jié)果。實際大部分應(yīng)用中是不會產(chǎn)生重復(fù)的記錄，最常見的是過程表與歷史表UNION。如：

select username,tel from user
union
select departmentname from department

這個SQL在運行時先取出兩個表的結(jié)果，再用排序空間進行排序刪除重復(fù)的記錄，最后返回結(jié)果集，如果表數(shù)據(jù)量大的話可能會導(dǎo)致用磁盤進行排序。推薦方案：采用UNION ALL操作符替代UNION，因為UNION ALL操作只是簡單的將兩個結(jié)果合并后就返回。

十三、批量插入性能提升

1、多條提交

INSERT INTO user (id,username) VALUES(1,'哪吒編程');

INSERT INTO user (id,username) VALUES(2,'妲己');

2、批量提交

INSERT INTO user (id,username) VALUES(1,'哪吒編程'),(2,'妲己');

3、理由

默認新增SQL有事務(wù)控制，導(dǎo)致每條都需要事務(wù)開啟和事務(wù)提交，而批量處理是一次事務(wù)開啟和提交，效率提升明顯，達到一定量級，效果顯著，平時看不出來。

十四、表連接不宜太多，索引不宜太多，一般5個以內(nèi)

1、表連接不宜太多，一般5個以內(nèi)

1. 關(guān)聯(lián)的表個數(shù)越多，編譯的時間和開銷也就越大
2. 每次關(guān)聯(lián)內(nèi)存中都生成一個臨時表
3. 應(yīng)該把連接表拆開成較小的幾個執(zhí)行，可讀性更高
4. 如果一定需要連接很多表才能得到數(shù)據(jù)，那么意味著這是個糟糕的設(shè)計了
5. 阿里規(guī)范中，建議多表聯(lián)查三張表以下

2、索引不宜太多，一般5個以內(nèi)

1. 索引并不是越多越好，雖其提高了查詢的效率，但卻會降低插入和更新的效率；
2. 索引可以理解為一個就是一張表，其可以存儲數(shù)據(jù)，其數(shù)據(jù)就要占空間；
3. 索引表的數(shù)據(jù)是排序的，排序也是要花時間的；
4. insert或update時有可能會重建索引，如果數(shù)據(jù)量巨大，重建將進行記錄的重新排序，所以建索引需要慎重考慮，視具體情況來定；
5. 一個表的索引數(shù)最好不要超過5個，若太多需要考慮一些索引是否有存在的必要；

十五、避免在索引列上使用內(nèi)置函數(shù)

1、反例

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

2、正例

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

3、理由

使用索引列上內(nèi)置函數(shù)，索引失效。

十六、組合索引

排序時應(yīng)按照組合索引中各列的順序進行排序，即使索引中只有一個列是要排序的，否則排序性能會比較差。

create index IDX_USERNAME_TEL on user(deptid,position,createtime);
select username,tel from user where deptid= 1 and position = 'java開發(fā)' order by deptid,position,createtime desc; 

實際上只是查詢出符合 deptid= 1 and position = 'java開發(fā)'條件的記錄并按createtime降序排序，但寫成order by createtime desc性能較差。

十七、復(fù)合索引最左特性

1、創(chuàng)建復(fù)合索引

ALTER TABLE employee ADD INDEX idx_name_salary (name,salary)

2、滿足復(fù)合索引的最左特性，哪怕只是部分，復(fù)合索引生效

SELECT * FROM employee WHERE NAME='哪吒編程'

3、沒有出現(xiàn)左邊的字段，則不滿足最左特性，索引失效

SELECT * FROM employee WHERE salary=5000

4、復(fù)合索引全使用，按左側(cè)順序出現(xiàn) name,salary，索引生效

SELECT * FROM employee WHERE NAME='哪吒編程' AND salary=5000

5、雖然違背了最左特性，但MySQL執(zhí)行SQL時會進行優(yōu)化，底層進行顛倒優(yōu)化

SELECT * FROM employee WHERE salary=5000 AND NAME='哪吒編程'

6、理由

復(fù)合索引也稱為聯(lián)合索引，當(dāng)我們創(chuàng)建一個聯(lián)合索引的時候，如(k1,k2,k3)，相當(dāng)于創(chuàng)建了（k1）、(k1,k2)和(k1,k2,k3)三個索引，這就是最左匹配原則。

聯(lián)合索引不滿足最左原則，索引一般會失效。

十八、優(yōu)化like語句

模糊查詢，程序員最喜歡的就是使用like，但是like很可能讓你的索引失效。

1、反例

select * from citys where name like '%大連' (不使用索引)

select * from citys where name like '%大連%' (不使用索引)

2、正例

select * from citys where name like '大連%' (使用索引) 。

3、理由

• 首先盡量避免模糊查詢，如果必須使用，不采用全模糊查詢，也應(yīng)盡量采用右模糊查詢， 即like ‘…%’，是會使用索引的；
• 左模糊like ‘%...’無法直接使用索引，但可以利用reverse + function index的形式，變化成 like ‘…%’；
• 全模糊查詢是無法優(yōu)化的，一定要使用的話建議使用搜索引擎。

十九、使用explain分析你SQL執(zhí)行計劃

1、type

1. system：表僅有一行，基本用不到；
2. const：表最多一行數(shù)據(jù)配合，主鍵查詢時觸發(fā)較多；
3. eq_ref：對于每個來自于前面的表的行組合，從該表中讀取一行。這可能是最好的聯(lián)接類型，除了const類型；
4. ref：對于每個來自于前面的表的行組合，所有有匹配索引值的行將從這張表中讀??；
5. range：只檢索給定范圍的行，使用一個索引來選擇行。當(dāng)使用=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN或者IN操作符，用常量比較關(guān)鍵字列時，可以使用range；
6. index：該聯(lián)接類型與ALL相同，除了只有索引樹被掃描。這通常比ALL快，因為索引文件通常比數(shù)據(jù)文件小；
7. all：全表掃描；
8. 性能排名：system > const > eq_ref > ref > range > index > all。
9. 實際sql優(yōu)化中，最后達到ref或range級別。

2、Extra常用關(guān)鍵字

• Using index：只從索引樹中獲取信息，而不需要回表查詢；
• Using where：WHERE子句用于限制哪一個行匹配下一個表或發(fā)送到客戶。除非你專門從表中索取或檢查所有行，如果Extra值不為Using where并且表聯(lián)接類型為ALL或index，查詢可能會有一些錯誤。需要回表查詢。
• Using temporary：mysql常建一個臨時表來容納結(jié)果，典型情況如查詢包含可以按不同情況列出列的GROUP BY和ORDER BY子句時；

二十、一些其它優(yōu)化方式

1、設(shè)計表的時候，所有表和字段都添加相應(yīng)的注釋。

2、SQL書寫格式，關(guān)鍵字大小保持一致，使用縮進。

3、修改或刪除重要數(shù)據(jù)前，要先備份。

4、很多時候用 exists 代替 in 是一個好的選擇

5、where后面的字段，留意其數(shù)據(jù)類型的隱式轉(zhuǎn)換。

未使用索引

SELECT * FROM user WHERE NAME=110

（1） 因為不加單引號時，是字符串跟數(shù)字的比較，它們類型不匹配； 

（2）MySQL會做隱式的類型轉(zhuǎn)換，把它們轉(zhuǎn)換為數(shù)值類型再做比較；

6、盡量把所有列定義為NOT NULL

NOT NULL列更節(jié)省空間，NULL列需要一個額外字節(jié)作為判斷是否為 NULL的標(biāo)志位。NULL列需要注意空指針問題，NULL列在計算和比較的時候，需要注意空指針問題。

7、偽刪除設(shè)計

8、數(shù)據(jù)庫和表的字符集盡量統(tǒng)一使用UTF8

（1）可以避免亂碼問題；

（2）可以避免，不同字符集比較轉(zhuǎn)換，導(dǎo)致的索引失效問題；

9、select count(*) from table；

這樣不帶任何條件的count會引起全表掃描，并且沒有任何業(yè)務(wù)意義，是一定要杜絕的。

10、避免在where中對字段進行表達式操作

（1）SQL解析時，如果字段相關(guān)的是表達式就進行全表掃描 ；

（2）字段干凈無表達式，索引生效；

11、關(guān)于臨時表

（1）避免頻繁創(chuàng)建和刪除臨時表，以減少系統(tǒng)表資源的消耗；

（2）在新建臨時表時，如果一次性插入數(shù)據(jù)量很大，那么可以使用 select into 代替 create table，避免造成大量 log；

（3）如果數(shù)據(jù)量不大，為了緩和系統(tǒng)表的資源，應(yīng)先create table，然后insert；

（4）如果使用到了臨時表，在存儲過程的最后務(wù)必將所有的臨時表顯式刪除。先 truncate table ，然后 drop table ，這樣可以避免系統(tǒng)表的較長時間鎖定；

12、索引不適合建在有大量重復(fù)數(shù)據(jù)的字段上，比如性別，排序字段應(yīng)創(chuàng)建索引

13、去重distinct過濾字段要少

1. 帶distinct的語句占用cpu時間高于不帶distinct的語句
2. 當(dāng)查詢很多字段時，如果使用distinct，數(shù)據(jù)庫引擎就會對數(shù)據(jù)進行比較，過濾掉重復(fù)數(shù)據(jù)
3. 然而這個比較、過濾的過程會占用系統(tǒng)資源，如cpu時間

14、盡量避免大事務(wù)操作，提高系統(tǒng)并發(fā)能力

15、所有表必須使用Innodb存儲引擎

Innodb「支持事務(wù)，支持行級鎖，更好的恢復(fù)性」，高并發(fā)下性能更好，所以呢，沒有特殊要求（即Innodb無法滿足的功能如：列存儲，存儲空間數(shù)據(jù)等）的情況下，所有表必須使用Innodb存儲引擎。

16、盡量避免使用游標(biāo)

因為游標(biāo)的效率較差，如果游標(biāo)操作的數(shù)據(jù)超過1萬行，那么就應(yīng)該考慮改寫。

程序汪資料鏈接
程序汪接的7個私活都在這里，經(jīng)驗整理
Java項目分享  最新整理全集，找項目不累啦 07版
堪稱神級的Spring Boot手冊，從基礎(chǔ)入門到實戰(zhàn)進階
臥槽！字節(jié)跳動《算法中文手冊》火了，完整版 PDF 開放下載！
臥槽！阿里大佬總結(jié)的《圖解Java》火了，完整版PDF開放下載！
字節(jié)跳動總結(jié)的設(shè)計模式 PDF 火了，完整版開放下載！
歡迎添加程序汪個人微信 itwang009  進粉絲群或圍觀朋友圈