一、查詢(xún) SQL 盡量不要使用 select *，而是具體字段

1、反例

SELECT*FROMuser

2、正例

SELECTid,username,telFROMuser

3、理由

節(jié)省資源、減少網(wǎng)絡(luò)開(kāi)銷(xiāo)。

可能用到覆蓋索引，減少回表，提高查詢(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、理由

使用or可能會(huì)使索引失效，從而全表掃描；

對(duì)于or沒(méi)有索引的salary這種情況，假設(shè)它走了id的索引，但是走到salary查詢(xún)條件時(shí)，它還得全表掃描；

也就是說(shuō)整個(gè)過(guò)程需要三步：全表掃描 + 索引掃描 + 合并。如果它一開(kāi)始就走全表掃描，直接一遍掃描就搞定；

雖然mysql是有優(yōu)化器的，出于效率與成本考慮，遇到or條件，索引還是可能失效的；

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

1、正例

主鍵（id）：primary key優(yōu)先使用數(shù)值類(lèi)型int，tinyint

性別（sex）：0 代表女，1 代表男；數(shù)據(jù)庫(kù)沒(méi)有布爾類(lèi)型，mysql推薦使用tinyint

2、理由

因?yàn)橐嬖谔幚聿樵?xún)和連接時(shí)會(huì)逐個(gè)比較字符串中每一個(gè)字符；

而對(duì)于數(shù)字型而言只需要比較一次就夠了；

字符會(huì)降低查詢(xún)和連接的性能，并會(huì)增加存儲(chǔ)開(kāi)銷(xiāo)；

四、使用 varchar 代替 char

1、反例

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

2、正例

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

3、理由

varchar變長(zhǎng)字段按數(shù)據(jù)內(nèi)容實(shí)際長(zhǎng)度存儲(chǔ)，存儲(chǔ)空間小，可以節(jié)省存儲(chǔ)空間；

char按聲明大小存儲(chǔ)，不足補(bǔ)空格；

其次對(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)槟鉯nsert到一個(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)鄑rim數(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、理由

并不是說(shuō)使用了is null或者is not null就會(huì)不走索引了，這個(gè)跟mysql版本以及查詢(xún)成本都有關(guān)；

如果mysql優(yōu)化器發(fā)現(xiàn)，走索引比不走索引成本還要高，就會(huì)放棄索引，這些條件!=，<>，is null，is not null經(jīng)常被認(rèn)為讓索引失效；

其實(shí)是因?yàn)橐话闱闆r下，查詢(xún)的成本高，優(yōu)化器自動(dòng)放棄索引的；

如果把null值，換成默認(rèn)值，很多時(shí)候讓走索引成為可能，同時(shí)，表達(dá)意思也相對(duì)清晰一點(diǎn)；

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

1、反例

SELECT*FROMuserWHEREsalary!=5000

SELECT*FROMuserWHEREsalary<>5000

2、理由

使用!=和<>很可能會(huì)讓索引失效

應(yīng)盡量避免在where子句中使用!=或<>操作符，否則引擎將放棄使用索引而進(jìn)行全表掃描

實(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)有l(wèi)imit的話(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ì)有 lock 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)閁NION 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)

關(guān)聯(lián)的表個(gè)數(shù)越多，編譯的時(shí)間和開(kāi)銷(xiāo)也就越大

每次關(guān)聯(lián)內(nèi)存中都生成一個(gè)臨時(shí)表

應(yīng)該把連接表拆開(kāi)成較小的幾個(gè)執(zhí)行，可讀性更高

如果一定需要連接很多表才能得到數(shù)據(jù)，那么意味著這是個(gè)糟糕的設(shè)計(jì)了

阿里規(guī)范中，建議多表聯(lián)查三張表以下

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

索引并不是越多越好，雖其提高了查詢(xún)的效率，但卻會(huì)降低插入和更新的效率；

索引可以理解為一個(gè)就是一張表，其可以存儲(chǔ)數(shù)據(jù)，其數(shù)據(jù)就要占空間；

索引表的數(shù)據(jù)是排序的，排序也是要花時(shí)間的；

insert或update時(shí)有可能會(huì)重建索引，如果數(shù)據(jù)量巨大，重建將進(jìn)行記錄的重新排序，所以建索引需要慎重考慮，視具體情況來(lái)定；

一個(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

system：表僅有一行，基本用不到；

const：表最多一行數(shù)據(jù)配合，主鍵查詢(xún)時(shí)觸發(fā)較多；

eq_ref：對(duì)于每個(gè)來(lái)自于前面的表的行組合，從該表中讀取一行。這可能是最好的聯(lián)接類(lèi)型，除了 const 類(lèi)型；

ref：對(duì)于每個(gè)來(lái)自于前面的表的行組合，所有有匹配索引值的行將從這張表中讀??；

range：只檢索給定范圍的行，使用一個(gè)索引來(lái)選擇行。當(dāng)使用 =、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN 或者 IN 操作符，用常量比較關(guān)鍵字列時(shí)，可以使用 range；

index：該聯(lián)接類(lèi)型與 ALL 相同，除了只有索引樹(shù)被掃描。這通常比 ALL 快，因?yàn)樗饕募ǔ１葦?shù)據(jù)文件??；

all：全表掃描；

性能排名：system > const > eq_ref > ref > range > index > all。

實(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ò)濾字段要少

帶 distinct 的語(yǔ)句占用cpu時(shí)間高于不帶distinct的語(yǔ)句

當(dāng)查詢(xún)很多字段時(shí)，如果使用distinct，數(shù)據(jù)庫(kù)引擎就會(huì)對(duì)數(shù)據(jù)進(jìn)行比較，過(guò)濾掉重復(fù)數(shù)據(jù)

然而這個(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ě)。

審核編輯：湯梓紅