MySQL的執(zhí)行過程 SQL語句性能優(yōu)化常用策略

作者：京東云開發(fā)者-京東科技 梁發(fā)文

回顧 MySQL 的執(zhí)行過程，幫助介紹如何進(jìn)行 sql 優(yōu)化。

（1）客戶端發(fā)送一條查詢語句到服務(wù)器；

（2）服務(wù)器先查詢緩存，如果命中緩存，則立即返回存儲(chǔ)在緩存中的數(shù)據(jù)；

（3）未命中緩存后，MySQL 通過關(guān)鍵字將 SQL 語句進(jìn)行解析，并生成一顆對(duì)應(yīng)的解析樹，MySQL 解析器將使用 MySQL 語法進(jìn)行驗(yàn)證和解析。

例如，驗(yàn)證是否使用了錯(cuò)誤的關(guān)鍵字，或者關(guān)鍵字的使用是否正確；

（4）預(yù)處理是根據(jù)一些 MySQL 規(guī)則檢查解析樹是否合理，比如檢查表和列是否存在，還會(huì)解析名字和別名，然后預(yù)處理器會(huì)驗(yàn)證權(quán)限；

根據(jù)執(zhí)行計(jì)劃查詢執(zhí)行引擎，調(diào)用 API 接口調(diào)用存儲(chǔ)引擎來查詢數(shù)據(jù)；

（5）將結(jié)果返回客戶端，并進(jìn)行緩存；

SQL 語句性能優(yōu)化常用策略

1、 為 WHERE 及 ORDER BY 涉及的列上建立索引

對(duì)查詢進(jìn)行優(yōu)化，應(yīng)盡量避免全表掃描，首先應(yīng)考慮在 WHERE 及 ORDER BY 涉及的列上建立索引。

2、where 中使用默認(rèn)值代替 null 應(yīng)盡量避免在 WHERE 子句中對(duì)字段進(jìn)行 NULL 值判斷，創(chuàng)建表時(shí) NULL 是默認(rèn)值，但大多數(shù)時(shí)候應(yīng)該使用 NOT NULL，或者使用一個(gè)特殊的值，如 0，-1 作為默認(rèn)值。

為啥建議 where 中使用默認(rèn)值代替 null，四個(gè)原因：

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

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

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

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

3、慎用！= 或 <> 操作符。

MySQL 只有對(duì)以下操作符才使用索引：<，<=，=，>，>=，BETWEEN，IN，以及某些時(shí)候的 LIKE。

所以：應(yīng)盡量避免在 WHERE 子句中使用！= 或 <> 操作符， 會(huì)導(dǎo)致全表掃描。

4、慎用 OR 來連接條件

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

應(yīng)盡量避免在 WHERE 子句中使用 OR 來連接條件，否則將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描，

可以使用 UNION 合并查詢：

select id from t where num=10

union all

select id from t where num=20

一個(gè)關(guān)鍵的問題是否用到索引。他們的速度只同是否使用索引有關(guān)，如果查詢需要用到聯(lián)合索引，用 UNION all 執(zhí)行的效率更高。多個(gè) OR 的字句沒有用到索引，改寫成 UNION 的形式再試圖與索引匹配。

5、慎用 IN 和 NOT IN

IN 和 NOT IN 要慎用，否則會(huì)導(dǎo)致全表掃描。對(duì)于連續(xù)的數(shù)值，能用 BETWEEN 就不要用 IN：select id from t where num between 1 and 3。

6、慎用 左模糊 like ‘%…’

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

比如：

select id from t where name like‘% abc%’ select id from t where name like‘% abc’ 而 select id from t where name like‘a(chǎn)bc%’才用到索引。

所以：

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

7、WHERE 條件使用參數(shù)會(huì)導(dǎo)致全表掃描。

如下面語句將進(jìn)行全表掃描：

select id from t where num=@num

因?yàn)?SQL 只有在運(yùn)行時(shí)才會(huì)解析局部變量，但優(yōu)化程序不能將訪問計(jì)劃的選擇推 遲到 運(yùn)行時(shí)；

它必須在編譯時(shí)進(jìn)行選擇。然而，如果在編譯時(shí)建立訪問計(jì)劃，變量的值還是未知的，因而無法作為索引選擇的輸入項(xiàng)。

所以， 可以改為強(qiáng)制查詢使用索引：

select id from t with (index (索引名)) where num=@num

8、用 EXISTS 代替 IN 是一個(gè)好的選擇

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

select num from a where num in (select num from b) 用下面的語句替換：select num from a where exists (select 1 from b where num=a.num)

9、索引并不是越多越好

索引固然可以提高相應(yīng)的 SELECT 的效率，但同時(shí)也降低了 INSERT 及 UPDATE 的效。

因?yàn)?INSERT 或 UPDATE 時(shí)有可能會(huì)重建索引，所以怎樣建索引需要慎重考慮，視具體情況而定。

一個(gè)表的索引數(shù)最好不要超過 6 個(gè)，若太多則應(yīng)考慮一些不常使用到的列上建的索引是否有必要。

10、盡量使用數(shù)字型字段

（1）因?yàn)橐嬖谔幚聿樵兒瓦B接時(shí)會(huì)逐個(gè)比較字符串中每一個(gè)字符；

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

（3）字符會(huì)降低查詢和連接的性能，并會(huì)增加存儲(chǔ)開銷；

所以：盡量使用數(shù)字型字段，若只含數(shù)值信息的字段盡量不要設(shè)計(jì)為字符型，這會(huì)降低查詢和連接的性能，并會(huì)增加存儲(chǔ)開銷。

11、盡可能的使用 varchar, nvarchar 代替 char, nchar

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

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

（3）其次對(duì)于查詢來說，在一個(gè)相對(duì)較小的字段內(nèi)搜索，效率更高；

因?yàn)槭紫茸冮L字段存儲(chǔ)空間小，可以節(jié)省存儲(chǔ)空間，其次對(duì)于查詢來說，在一個(gè)相對(duì)較小的字段內(nèi)搜索效率顯然要高些。

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

最好不要使用返回所有：select * from t ，用具體的字段列表代替 “*”，不要返回用不到的任何字段。

select * 的弊端：

（1）增加很多不必要的消耗，比如 CPU、IO、內(nèi)存、網(wǎng)絡(luò)帶寬；

（2）增加了使用覆蓋索引的可能性；

（3）增加了回表的可能性；

（4）當(dāng)表結(jié)構(gòu)發(fā)生變化時(shí)，前端也需要更改；

（5）查詢效率低；

15、將需要查詢的結(jié)果預(yù)先計(jì)算好

將需要查詢的結(jié)果預(yù)先計(jì)算好放在表中，查詢的時(shí)候再 Select，而不是查詢的時(shí)候進(jìn)行計(jì)算。

16、IN 后出現(xiàn)最頻繁的值放在最前面

如果一定用 IN，那么：在 IN 后面值的列表中，將出現(xiàn)最頻繁的值放在最前面，出現(xiàn)得最少的放在最后面，減少判斷的次數(shù)。

17、盡量使用 EXISTS 代替 select count (1) 來判斷是否存在記錄。

count 函數(shù)只有在統(tǒng)計(jì)表中所有行數(shù)時(shí)使用，而且 count (1) 比 count (*) 更有效率。

18、用批量插入或批量更新

當(dāng)有一批處理的插入或更新時(shí)，用批量插入或批量更新，絕不會(huì)一條條記錄的去更新。

（1）多條提交

INSERT INTO user (id,username) VALUES(1,'xx'); INSERT INTO user (id,username) VALUES(2,'yy');

（2）批量提交

INSERT INTO user (id,username) VALUES (1,'xx'),(2,'yy'); 默認(rèn)新增 SQL 有事務(wù)控制，導(dǎo)致每條都需要事務(wù)開啟和事務(wù)提交，而批量處理是一次事務(wù)開啟和提交，效率提升明顯，達(dá)到一定量級(jí)，效果顯著，平時(shí)看不出來。

19、將不需要的記錄在 GROUP BY 之前過濾掉

提高 GROUP BY 語句的效率，可以通過將不需要的記錄在 GROUP BY 之前過濾掉。

下面兩個(gè)查詢返回相同結(jié)果，但第二個(gè)明顯就快了許多。

低效：

SELECT JOB, AVG (SAL) FROM EMP GROUP BY JOB HAVING JOB = 'PRESIDENT' OR JOB = 'MANAGER' 高效：

SELECT JOB, AVG(SAL) FROM EMP WHERE JOB = 'PRESIDENT' OR JOB = 'MANAGER' GROUP BY JOB

20、避免死鎖

在你的存儲(chǔ)過程和觸發(fā)器中訪問同一個(gè)表時(shí)總是以相同的順序；事務(wù)應(yīng)經(jīng)可能地縮短，在一個(gè)事務(wù)中應(yīng)盡可能減少涉及到的數(shù)據(jù)量；永遠(yuǎn)不要在事務(wù)中等待用戶輸入。

21、索引創(chuàng)建規(guī)則：

表的主鍵、外鍵必須有索引；

數(shù)據(jù)量超過 300 的表應(yīng)該有索引；

經(jīng)常與其他表進(jìn)行連接的表，在連接字段上應(yīng)該建立索引；

經(jīng)常出現(xiàn)在 WHERE 子句中的字段，特別是大表的字段，應(yīng)該建立索引；

索引應(yīng)該建在選擇性高的字段上；

索引應(yīng)該建在小字段上，對(duì)于大的文本字段甚至超長字段，不要建索引；

復(fù)合索引的建立需要進(jìn)行仔細(xì)分析，盡量考慮用單字段索引代替；

正確選擇復(fù)合索引中的主列字段，一般是選擇性較好的字段；

復(fù)合索引的幾個(gè)字段是否經(jīng)常同時(shí)以 AND 方式出現(xiàn)在 WHERE 子句中？單字段查詢是否極少甚至沒有？如果是，則可以建立復(fù)合索引；否則考慮單字段索引；

如果復(fù)合索引中包含的字段經(jīng)常單獨(dú)出現(xiàn)在 WHERE 子句中，則分解為多個(gè)單字段索引；

如果復(fù)合索引所包含的字段超過 3 個(gè)，那么仔細(xì)考慮其必要性，考慮減少復(fù)合的字段；

如果既有單字段索引，又有這幾個(gè)字段上的復(fù)合索引，一般可以刪除復(fù)合索引；

頻繁進(jìn)行數(shù)據(jù)操作的表，不要建立太多的索引；刪除無用的索引，避免對(duì)執(zhí)行計(jì)劃造成負(fù)面影響；

表上建立的每個(gè)索引都會(huì)增加存儲(chǔ)開銷，索引對(duì)于插入、刪除、更新操作也會(huì)增加處理上的開銷。

另外，過多的復(fù)合索引，在有單字段索引的情況下，一般都是沒有存在價(jià)值的；相反，還會(huì)降低數(shù)據(jù)增加刪除時(shí)的性能，特別是對(duì)頻繁更新的表來說，負(fù)面影響更大。盡量不要對(duì)數(shù)據(jù)庫中某個(gè)含有大量重復(fù)的值的字段建立索引。

22、在寫 SQL 語句時(shí)，應(yīng)盡量減少空格的使用

查詢緩沖并不自動(dòng)處理空格，因此，在寫 SQL 語句時(shí)，應(yīng)盡量減少空格的使用，尤其是在 SQL 首和尾的空格（因?yàn)椴樵兙彌_并不自動(dòng)截取首尾空格）。

23、每張表都設(shè)置一個(gè) ID 做為其主鍵

我們應(yīng)該為數(shù)據(jù)庫里的每張表都設(shè)置一個(gè) ID 做為其主鍵，而且最好的是一個(gè) INT 型的（推薦使用 UNSIGNED），并設(shè)置上自動(dòng)增加的 AUTO_INCREMENT 標(biāo)志。

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

（1）type

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

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

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

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

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

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

all：全表掃描；

性能排名：system > const > eq_ref > ref > range > index > all。實(shí)際 sql 優(yōu)化中，最后達(dá)到 ref 或 range 級(jí)別。

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

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

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

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

25、當(dāng)只要一行數(shù)據(jù)時(shí)使用 LIMIT 1

當(dāng)你查詢表的有些時(shí)候，你已經(jīng)知道結(jié)果只會(huì)有一條結(jié)果，但因?yàn)槟憧赡苄枰?fetch 游標(biāo)，或是你也許會(huì)去檢查返回的記錄數(shù)。

在這種情況下，加上 LIMIT 1 可以增加性能。

這樣一來，MySQL 數(shù)據(jù)庫引擎會(huì)在找到一條數(shù)據(jù)后停止搜索，而不是繼續(xù)往后查少下一條符合記錄的數(shù)據(jù)。

26、將大的 DELETE，UPDATE、INSERT 查詢變成多個(gè)小查詢

能寫一個(gè)幾十行、幾百行的 SQL 語句是不是顯得逼格很高？然而，為了達(dá)到更好的性能以及更好的數(shù)據(jù)控制，你可以將他們變成多個(gè)小查詢。

27、合理分表 盡量控制單表數(shù)據(jù)量的大小，建議控制在 500 萬以內(nèi)

500 萬并不是 MySQL 數(shù)據(jù)庫的限制，過大會(huì)造成修改表結(jié)構(gòu)，備份，恢復(fù)都會(huì)有很大的問題。

可以用歷史數(shù)據(jù)歸檔（應(yīng)用于日志數(shù)據(jù)），分庫分表（應(yīng)用于業(yè)務(wù)數(shù)據(jù)）等手段來控制數(shù)據(jù)量大小。