優(yōu)化SQL的一些小技巧

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

1、反例

SELECT*FROMuser

2、正例

SELECTid,username,telFROMuser

3、理由

節(jié)省資源、減少網絡開銷。

可能用到覆蓋索引，減少回表，提高查詢效率。

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

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

1、反例

SELECT*FROMuserWHEREid=1ORsalary=5000

2、正例

（1）使用 union all

SELECT*FROMuserWHEREid=1
UNIONALL
SELECT*FROMuserWHEREsalary=5000

（2）分開兩條 sql 寫

SELECT*FROMuserWHEREid=1

SELECT*FROMuserWHEREsalary=5000

3、理由

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

對于or沒有索引的salary這種情況，假設它走了id的索引，但是走到salary查詢條件時，它還得全表掃描；

也就是說整個過程需要三步：全表掃描 + 索引掃描 + 合并。如果它一開始就走全表掃描，直接一遍掃描就搞定；

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

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

1、正例

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

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

2、理由

因為引擎在處理查詢和連接時會逐個比較字符串中每一個字符；

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

字符會降低查詢和連接的性能，并會增加存儲開銷；

四、使用 varchar 代替 char

1、反例

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

2、正例

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

3、理由

varchar變長字段按數(shù)據(jù)內容實際長度存儲，存儲空間小，可以節(jié)省存儲空間；

char按聲明大小存儲，不足補空格；

其次對于查詢來說，在一個相對較小的字段內搜索，效率更高；

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

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

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

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

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

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

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

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

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

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

1、反例

SELECT*FROMuserWHEREageISNOTNULL

2、正例

SELECT*FROMuserWHEREage>0

3、理由

并不是說使用了is null或者is not null就會不走索引了，這個跟mysql版本以及查詢成本都有關；

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

其實是因為一般情況下，查詢的成本高，優(yōu)化器自動放棄索引的；

如果把null值，換成默認值，很多時候讓走索引成為可能，同時，表達意思也相對清晰一點；

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

1、反例

SELECT*FROMuserWHEREsalary!=5000

SELECT*FROMuserWHEREsalary<>5000

2、理由

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

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

實現(xiàn)業(yè)務優(yōu)先，實在沒辦法，就只能使用，并不是不能使用

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

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

inner join 內連接，只保留兩張表中完全匹配的結果集；

left join 會返回左表所有的行，即使在右表中沒有匹配的記錄；

right join 會返回右表所有的行，即使在左表中沒有匹配的記錄；

為什么？

如果 inner join 是等值連接，返回的行數(shù)比較少，所以性能相對會好一點；

使用了左連接，左邊表數(shù)據(jù)結果盡量小，條件盡量放到左邊處理，意味著返回的行數(shù)可能比較少；

這是 mysql 優(yōu)化原則，就是小表驅動大表，小的數(shù)據(jù)集驅動大的數(shù)據(jù)集，從而讓性能更優(yōu)；

九、提高 group by 語句的效率

1、反例

先分組，再過濾

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

2、正例

先過濾，后分組

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

3、理由

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

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

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

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

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

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

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

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

1、降低寫錯 SQL 的代價

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

2、SQL 效率很可能更高

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

3、避免長事務

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

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

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

5、鎖表

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

十二、UNION 操作符

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

selectusername,telfromuser
union
selectdepartmentnamefromdepartment

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

十三、批量插入性能提升

1、多條提交

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

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

2、批量提交

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

3、理由

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

十四、表連接不宜太多，索引不宜太多，一般 5 個以內

1、表連接不宜太多，一般 5 個以內

關聯(lián)的表個數(shù)越多，編譯的時間和開銷也就越大

每次關聯(lián)內存中都生成一個臨時表

應該把連接表拆開成較小的幾個執(zhí)行，可讀性更高

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

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

2、索引不宜太多，一般 5 個以內

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

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

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

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

一個表的索引數(shù)最好不要超過 5 個，若太多需要考慮一些索引是否有存在的必要；

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

1、反例

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

2、正例

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

3、理由

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

十六、組合索引

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

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

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

十七、復合索引最左特性

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

ALTERTABLEemployeeADDINDEXidx_name_salary(name,salary)

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

SELECT*FROMemployeeWHERENAME='哪吒編程'

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

SELECT*FROMemployeeWHEREsalary=5000

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

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

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

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

6、理由

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

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

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

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

1、反例

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

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

2、正例

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

3、理由

首先盡量避免模糊查詢，如果必須使用，不采用全模糊查詢，也應盡量采用右模糊查詢， 即like ‘…%’，是會使用索引的；

左模糊like ‘%...’無法直接使用索引，但可以利用reverse + function index的形式，變化成like ‘…%’；

全模糊查詢是無法優(yōu)化的，一定要使用的話建議使用搜索引擎。

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

1、type

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

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

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

ref：對于每個來自于前面的表的行組合，所有有匹配索引值的行將從這張表中讀??；

range：只檢索給定范圍的行，使用一個索引來選擇行。當使用 =、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN 或者 IN 操作符，用常量比較關鍵字列時，可以使用 range；

index：該聯(lián)接類型與 ALL 相同，除了只有索引樹被掃描。這通常比 ALL 快，因為索引文件通常比數(shù)據(jù)文件??；

all：全表掃描；

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

實際 sql 優(yōu)化中，最后達到 ref 或 range 級別。

2、Extra 常用關鍵字

Using index：只從索引樹中獲取信息，而不需要回表查詢；

Using where：WHERE 子句用于限制哪一個行匹配下一個表或發(fā)送到客戶。除非你專門從表中索取或檢查所有行，如果 Extra 值不為 Using where 并且表聯(lián)接類型為 ALL 或 index，查詢可能會有一些錯誤。需要回表查詢。

Using temporary：mysql 常建一個臨時表來容納結果，典型情況如查詢包含可以按不同情況列出列的GROUP BY和ORDER BY子句時；

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

1、設計表的時候，所有表和字段都添加相應的注釋。

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

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

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

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

未使用索引

SELECT*FROMuserWHERENAME=110

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

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

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

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

7、偽刪除設計

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

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

（2）可以避免，不同字符集比較轉換，導致的索引失效問題；

9、select count(*) from table；

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

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

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

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

11、關于臨時表

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

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

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

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

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

13、去重 distinct 過濾字段要少

帶 distinct 的語句占用cpu時間高于不帶distinct的語句

當查詢很多字段時，如果使用distinct，數(shù)據(jù)庫引擎就會對數(shù)據(jù)進行比較，過濾掉重復數(shù)據(jù)

然而這個比較、過濾的過程會占用系統(tǒng)資源，如cpu時間

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

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

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

16、盡量避免使用游標

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

審核編輯：湯梓紅