SQL優(yōu)化技巧分享

1、反例

SELECT*FROMuser

2、正例

SELECTid,username,telFROMuser

3、理由

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

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

二、避免在where子句中使用 or 來(lái)連接條件

1、反例

SELECT*FROMuserWHEREid=1ORsalary=5000

2、正例

（1）使用union all

SELECT*FROMuserWHEREid=1
UNIONALL
SELECT*FROMuserWHEREsalary=5000

（2）分開(kāi)兩條sql寫(xiě)

SELECT*FROMuserWHEREid=1

SELECT*FROMuserWHEREsalary=5000

3、理由

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

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

1、正例

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

2、理由

1. 因?yàn)橐嬖谔幚聿樵?xún)和連接時(shí)會(huì)逐個(gè)比較字符串中每一個(gè)字符；
2. 而對(duì)于數(shù)字型而言只需要比較一次就夠了；
3. 字符會(huì)降低查詢(xún)和連接的性能，并會(huì)增加存儲(chǔ)開(kāi)銷(xiāo)；

四、使用varchar代替char

1、反例

`address`char(100)DEFAULTNULLCOMMENT'地址'

2、正例

`address`varchar(100)DEFAULTNULLCOMMENT'地址'

3、理由

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

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

1、char的長(zhǎng)度是固定的，而varchar2的長(zhǎng)度是可以變化的。

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

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

3、何時(shí)用char，何時(shí)用varchar2?

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

varchar2雖然比char節(jié)省空間，但是假如一個(gè)varchar2列經(jīng)常被修改，而且每次被修改的數(shù)據(jù)的長(zhǎng)度不同，這會(huì)引起“行遷移”現(xiàn)象，而這造成多余的I/O，是數(shù)據(jù)庫(kù)設(shè)計(jì)中要盡力避免的，這種情況下用char代替varchar2會(huì)更好一些。char中還會(huì)自動(dòng)補(bǔ)齊空格，因?yàn)槟?code style="font-size:14px;padding:2px 4px;margin-right:2px;margin-left:2px;color:rgb(30,107,184);background-color:rgba(27,31,35,.05);font-family:'Operator Mono', Consolas, Monaco, Menlo, monospace;">insert到一個(gè)char字段自動(dòng)補(bǔ)充了空格的,但是select后空格沒(méi)有刪除，因此char類(lèi)型查詢(xún)的時(shí)候一定要記得使用trim，這是寫(xiě)本文章的原因。

如果開(kāi)發(fā)人員細(xì)化使用rpad()技巧將綁定變量轉(zhuǎn)換為某種能與char字段相比較的類(lèi)型（當(dāng)然，與截?cái)?code style="font-size:14px;padding:2px 4px;margin-right:2px;margin-left:2px;color:rgb(30,107,184);background-color:rgba(27,31,35,.05);font-family:'Operator Mono', Consolas, Monaco, Menlo, monospace;">trim數(shù)據(jù)庫(kù)列相比，填充綁定變量的做法更好一些，因?yàn)閷?duì)列應(yīng)用函數(shù)trim很容易導(dǎo)致無(wú)法使用該列上現(xiàn)有的索引），可能必須考慮到經(jīng)過(guò)一段時(shí)間后列長(zhǎng)度的變化。如果字段的大小有變化，應(yīng)用就會(huì)受到影響，因?yàn)樗仨毿薷淖侄螌挾取?/p>

正是因?yàn)橐陨显?，定寬的存?chǔ)空間可能導(dǎo)致表和相關(guān)索引比平常大出許多，還伴隨著綁定變量問(wèn)題，所以無(wú)論什么場(chǎng)合都要避免使用char類(lèi)型。

六、where中使用默認(rèn)值代替null

1、反例

SELECT*FROMuserWHEREageISNOTNULL

2、正例

SELECT*FROMuserWHEREage>0

3、理由

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

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

1、反例

SELECT*FROMuserWHEREsalary!=5000

SELECT*FROMuserWHEREsalary<>5000

2、理由

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

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

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

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

為什么？

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

九、提高group by語(yǔ)句的效率

1、反例

先分組，再過(guò)濾

selectjob,avg（salary）fromemployee
groupbyjob
havingjob='develop'orjob='test';

2、正例

先過(guò)濾，后分組

selectjob，avg（salary）fromemployee
wherejob='develop'orjob='test'
groupbyjob;

3、理由

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

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

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

delete語(yǔ)句每次刪除一行，并在事務(wù)日志中為所刪除的每行記錄一項(xiàng)。truncate table通過(guò)釋放存儲(chǔ)表數(shù)據(jù)所用的數(shù)據(jù)頁(yè)來(lái)刪除數(shù)據(jù)，并且只在事務(wù)日志中記錄頁(yè)的釋放。

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

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

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

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

1、降低寫(xiě)錯(cuò)SQL的代價(jià)

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

2、SQL效率很可能更高

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

3、避免長(zhǎng)事務(wù)

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

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

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

5、鎖表

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

十二、UNION操作符

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

selectusername,telfromuser
union
selectdepartmentnamefromdepartment

這個(gè)SQL在運(yùn)行時(shí)先取出兩個(gè)表的結(jié)果，再用排序空間進(jìn)行排序刪除重復(fù)的記錄，最后返回結(jié)果集，如果表數(shù)據(jù)量大的話(huà)可能會(huì)導(dǎo)致用磁盤(pán)進(jìn)行排序。推薦方案：采用UNION ALL操作符替代UNION，因?yàn)?code style="font-size:14px;padding:2px 4px;margin-right:2px;margin-left:2px;color:rgb(30,107,184);background-color:rgba(27,31,35,.05);font-family:'Operator Mono', Consolas, Monaco, Menlo, monospace;">UNION ALL操作只是簡(jiǎn)單的將兩個(gè)結(jié)果合并后就返回。

十三、批量插入性能提升

1、多條提交

INSERTINTOuser(id,username)VALUES(1,'哪吒編程');

INSERTINTOuser(id,username)VALUES(2,'妲己');

2、批量提交

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

3、理由

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

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

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

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

2、索引不宜太多，一般5個(gè)以?xún)?nèi)

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

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

1、反例

SELECT*FROMuserWHEREDATE_ADD(birthday,INTERVAL7DAY)>=NOW();

2、正例

SELECT*FROMuserWHEREbirthday>=DATE_ADD(NOW(),INTERVAL7DAY);

3、理由

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

十六、組合索引

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

createindexIDX_USERNAME_TELonuser(deptid,position,createtime);
selectusername,telfromuserwheredeptid=1andposition='java開(kāi)發(fā)'orderbydeptid,position,createtimedesc;

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

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

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

ALTERTABLEemployeeADDINDEXidx_name_salary(name,salary)

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

SELECT*FROMemployeeWHERENAME='哪吒編程'

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

SELECT*FROMemployeeWHEREsalary=5000

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

SELECT*FROMemployeeWHERENAME='哪吒編程'ANDsalary=5000

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

SELECT*FROMemployeeWHEREsalary=5000ANDNAME='哪吒編程'

6、理由

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

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

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

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

1、反例

select*fromcityswherenamelike'%大連'(不使用索引)

select*fromcityswherenamelike'%大連%'(不使用索引)

2、正例

select*fromcityswherenamelike'大連%'(使用索引)。

3、理由

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

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

1、type

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

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

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

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

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

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

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

4、很多時(shí)候用 exists 代替 in 是一個(gè)好的選擇

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

未使用索引

SELECT*FROMuserWHERENAME=110

（1） 因?yàn)椴患訂我?hào)時(shí)，是字符串跟數(shù)字的比較，它們類(lèi)型不匹配；

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

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

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

7、偽刪除設(shè)計(jì)

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

（1）可以避免亂碼問(wèn)題；

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

9、select count(*) from table；

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

10、避免在where中對(duì)字段進(jìn)行表達(dá)式操作

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

（2）字段干凈無(wú)表達(dá)式，索引生效；

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

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

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

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

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

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

13、去重distinct過(guò)濾字段要少

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

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

15、所有表必須使用Innodb存儲(chǔ)引擎

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

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

因?yàn)橛螛?biāo)的效率較差，如果游標(biāo)操作的數(shù)據(jù)超過(guò)1萬(wàn)行，那么就應(yīng)該考慮改寫(xiě)。

在這里插入圖片描述

審核編輯：湯梓紅