一、SQL的執(zhí)行順序

順序：FROM——ON——JOIN——WHERE——GROUP BY——SUM、COUNT——HAVING——SELECT——DISTINCT——ORDER BY——LIMIT

1.對查詢進行優(yōu)化，應盡量避免全表掃描，首先應考慮在 where 及 order by 涉及的列上建立索引。

對于聯(lián)合索引來說，要遵守最左前綴法則

舉列來說索引含有字段id、name、school，可以直接用id字段，也可以id、name這樣的順序，但是name;school都無法使用這個索引。所以在創(chuàng)建聯(lián)合索引的時候一定要注意索引字段順序，常用的查詢字段放在最前面。

注意范圍查詢語句

對于聯(lián)合索引來說，如果存在范圍查詢，比如between、>、<等條件時，會造成后面的索引字段失效。

2.應盡量避免在 where 子句中對字段進行 null 值判斷，否則將導致引擎放棄使用索引而進行全表掃描，如：

select id from t where num is null

可以在num上設置默認值0，確保表中num列沒有null值，然后這樣查詢：

select id from t where num=0

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

4.應盡量避免在 where 子句中使用 or 來連接條件，否則將導致引擎放棄使用索引而進行全表掃描，如：

select id from t where num=10 or num=20

可以這樣查詢：

select id from t where num=10

union all

select id from t where num=20

5.in和 not in 也要慎用，否則會導致全表掃描，如：

select id from t where num in(1,2,3)

對于連續(xù)的數(shù)值，能用 between 就不要用 in 了：

select id from t where num between 1 and 3

6.下面的查詢也將導致全表掃描：

select id from t where name like '%abc%'

7.應盡量避免在 where 子句中對字段進行表達式操作，這將導致引擎放棄使用索引而進行全表掃描。如：

select id from t where num/2=100

應改為:

select id from t where num=100*2

8.應盡量避免在where子句中對字段進行函數(shù)操作，這將導致引擎放棄使用索引而進行全表掃描。如：

select id from t where substring(name,1,3)='abc'--name以abc開頭的id

應改為:

select id from t where name like 'abc%'

9.不要在 where 子句中的“=”左邊進行函數(shù)、算術運算或其他表達式運算，否則系統(tǒng)將可能無法正確使用索引。

10.在使用索引字段作為條件時，如果該索引是復合索引，那么必須使用到該索引中的第一個字段作為條件時才能保證系統(tǒng)使用該索引，否則該索引將不會被使用，并且應盡可能的讓字段順序與索引順序相一致。

11.不要寫一些沒有意義的查詢，如需要生成一個空表結構：

select col1,col2 into #t from t where 1=0

這類代碼不會返回任何結果集，但是會消耗系統(tǒng)資源的，應改成這樣：

create table #t(...)

12.很多時候用 exists 代替 in 是一個好的選擇：

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)

區(qū)分in和exists主要是造成了驅動順序的改變（這是性能變化的關鍵），如果是exists，那么以外層表為驅動表，先被訪問，如果是IN，那么先執(zhí)行子查詢。所以IN適合于外表大而內表小的情況；EXISTS適合于外表小而內表大的情況。

關于not in和not exists，推薦使用not exists，不僅僅是效率問題，not in可能存在邏輯問題。如何高效的寫出一個替代not exists的SQL語句？

原SQL語句：

select colname … from A表 where a.id not in (select b.id from B表)

高效的SQL語句：

select colname … from A表 Left join B表 on where a.id = b.id where b.id is null

取出的結果集如下圖表示，A表不在B表中的數(shù)據(jù)：

13.并不是所有索引對查詢都有效，SQL是根據(jù)表中數(shù)據(jù)來進行查詢優(yōu)化的，當索引列有大量數(shù)據(jù)重復時，SQL查詢可能不會去利用索引，如一表中有字段sex，male、female幾乎各一半，那么即使在sex上建了索引也對查詢效率起不了作用。

14.索引并不是越多越好，索引固然可以提高相應的 select 的效率，但同時也降低了 insert 及 update 的效率，

因為 insert 或 update 時有可能會重建索引，所以怎樣建索引需要慎重考慮，視具體情況而定。

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

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

這是因為引擎在處理查詢和連接時會逐個比較字符串中每一個字符，而對于數(shù)字型而言只需要比較一次就夠了。

16.盡可能的使用 varchar 代替 char ，因為首先變長字段存儲空間小，可以節(jié)省存儲空間，

其次對于查詢來說，在一個相對較小的字段內搜索效率顯然要高些。

17.任何地方都不要使用 select * from t ，用具體的字段列表代替“*”，不要返回用不到的任何字段。

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

19.臨時表并不是不可使用，適當?shù)厥褂盟鼈兛梢允鼓承├谈行?，例如，當需要重復引用大型表或常用表中的某個數(shù)據(jù)集時。但是，對于一次性事件，最好使用導出表。

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

以提高速度；如果數(shù)據(jù)量不大，為了緩和系統(tǒng)表的資源，應先create table，然后insert。

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

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

23.使用基于游標的方法或臨時表方法之前，應先尋找基于集的解決方案來解決問題，基于集的方法通常更有效。

24.與臨時表一樣，游標并不是不可使用。對小型數(shù)據(jù)集使用 FAST_FORWARD 游標通常要優(yōu)于其他逐行處理方法，尤其是在必須引用幾個表才能獲得所需的數(shù)據(jù)時。

在結果集中包括“合計”的例程通常要比使用游標執(zhí)行的速度快。如果開發(fā)時間允許，基于游標的方法和基于集的方法都可以嘗試一下，看哪一種方法的效果更好。

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

26.盡量避免向客戶端返回大數(shù)據(jù)量，若數(shù)據(jù)量過大，應該考慮相應需求是否合理。

27.關于JOIN優(yōu)化

LEFT JOIN A表為驅動表，INNER JOIN MySQL會自動找出那個數(shù)據(jù)少的表作用驅動表，RIGHT JOIN B表為驅動表。

注意：

1）MySQL中沒有full join，可以用以下方式來解決：

select * from A left join B on B.name = A.namewhere B.name is nullunion allselect * from B;

2）盡量使用inner join，避免left join：

參與聯(lián)合查詢的表至少為2張表，一般都存在大小之分。如果連接方式是inner join，在沒有其他過濾條件的情況下MySQL會自動選擇小表作為驅動表，但是left join在驅動表的選擇上遵循的是左邊驅動右邊的原則，即left join左邊的表名為驅動表。

3）合理利用索引：

被驅動表的索引字段作為on的限制字段。

4）利用小表去驅動大表：

從原理圖能夠直觀的看出如果能夠減少驅動表的話，減少嵌套循環(huán)中的循環(huán)次數(shù)，以減少 IO總量及CPU運算的次數(shù)。

二、執(zhí)行計劃——EXPLAIN

執(zhí)行計劃，是SQL在數(shù)據(jù)庫中執(zhí)行時的表現(xiàn)情況,通常用于SQL性能分析,優(yōu)化等場景。在MySQL使用 explain 關鍵字來查看SQL的執(zhí)行計劃。

基本的語法：EXPLAIN(select * from table)

在常規(guī)SQL語句前面加上EXPLAIN即可

參數(shù)解釋：

1、id：數(shù)字越大越先執(zhí)行，一樣大則從上往下執(zhí)行，如果為NULL則表示是結果集，不需要用來查詢。

2、select_type：

simple：不需要union的操作或者是不包含子查詢的簡單select語句。

primary：需要union操作或者含有子查詢的select語句。

union：連接兩個select查詢，第一個查詢是dervied派生表，第二個及后面的表select_type都是union。

dependent union：與union一樣，出現(xiàn)在union 或union all語句中，但是這個查詢要受到外部查詢的影響。

union result：包含union的結果集。

subquery：除了from字句中包含的子查詢外，其他地方出現(xiàn)的子查詢都可能是subquery。

dependent subquery：與dependent union類似，表示這個subquery的查詢要受到外部表查詢的影響。

derived：from字句中出現(xiàn)的子查詢，也叫做派生表，其他數(shù)據(jù)庫中可能叫做內聯(lián)視圖或嵌套select。

3、table

表名，如果是用了別名，則顯示別名

4、type

依次從好到差：system，const，eq_ref，ref，fulltext，ref_or_null，unique_subquery，index_subquery，range，index_merge，index，ALL，除了all之外，其他的type都可以使用到索引，除了index_merge之外，其他的type只可以用到一個索引。

system：表中只有一行數(shù)據(jù)或者是空表。

const：使用唯一索引或者主鍵，返回記錄一定是1行記錄的等值where條件時，通常type是const。

eq_ref：出現(xiàn)在要連接過個表的查詢計劃中，驅動表只返回一行數(shù)據(jù)，且這行數(shù)據(jù)是第二個表的主鍵或者唯一索引，且必須為not null，唯一索引和主鍵是多列時，只有所有的列都用作比較時才會出現(xiàn)eq_ref。

ref：不像eq_ref那樣要求連接順序，也沒有主鍵和唯一索引的要求，只要使用相等條件檢索時就可能出現(xiàn)，常見與輔助索引的等值查找。

fulltext：全文索引檢索，要注意，全文索引的優(yōu)先級很高，若全文索引和普通索引同時存在時，mysql不管代價，優(yōu)先選擇使用全文索引。

ref_or_null：與ref方法類似，只是增加了null值的比較。實際用的不多。

unique_subquery：用于where中的in形式子查詢，子查詢返回不重復值唯一值。

index_subquery：用于in形式子查詢使用到了輔助索引或者in常數(shù)列表，子查詢可能返回重復值，可以使用索引將子查詢去重。

range：索引范圍掃描，常見于使用>,<,is null,between ,in ,like等運算符的查詢中。

index_merge：表示查詢使用了兩個以上的索引，最后取交集或者并集，常見and ，or的條件使用了不同的索引。

index：索引全表掃描，把索引從頭到尾掃一遍，常見于使用索引列就可以處理不需要讀取數(shù)據(jù)文件的查詢、可以使用索引排序或者分組的查詢。

all：這個就是全表掃描數(shù)據(jù)文件，然后再在server層進行過濾返回符合要求的記錄。

5、possible_keys：查詢可能使用到的索引。

6、key：查詢真正使用到的索引。

7、key_len：用于處理查詢的索引長度。

8、ref：常數(shù)等值查詢顯示const，連接查詢則顯示表的關聯(lián)字段。

9、rows：執(zhí)行計劃中估算的掃描行數(shù)，不是精確值。

10、filtered：表示存儲引擎返回的數(shù)據(jù)在server層過濾后，剩下多少滿足查詢的記錄數(shù)量的比例。

11、extra：該字段信息較多，這里就不一一敘述了。

在實際的使用過程中我們需要重點去關注type、key、key_len、rows、extra這幾個參數(shù)，type要努力優(yōu)化到range級別，all要盡量少的出現(xiàn)，在查詢的過程中要盡量使用索引，提高效率，在extra里面出現(xiàn)Using filesort, Using temporary是不太好的，要去優(yōu)化提高性能。

mysql的四種隔離等級

READ UNCOMMITED(未提交讀)

在RERAD UNCOMMITED級別，事務中的修改，即使沒有提交，對其他事務也都是可見的。事務可以讀取未提交的數(shù)據(jù)，這也成為臟讀（Dirty Read）。這個級別會導致很多問題，從性能上說READ UNCOMMITED 不會比其他的級別好太多，但缺乏其他級別的好多好處，除非有非常必要的理由，在實際的應用中一般很少使用READ UNCOMMITED.

READ COMMITED (提交讀)

大多數(shù)數(shù)據(jù)庫系統(tǒng)的默認隔離級別都是READ COMMITED （但是MYSQL不是）。READ COMMITED 滿足前面提到的隔離性的簡單定義：一個事務開始時，只能看到已經提交的事務所做的修改。換句話說，一個事務從開始到提交之前，所做的任何修改對其他事務都 是不可見的。這個級別有時候也叫做不可重復的（nonerepeatable read），因為兩次執(zhí)行同樣的查詢，可能會得到不一樣的結果。

REPEATABLE READ (可重復讀)

REPEATABLE READ (可重復讀) 解決了臟讀問題。該級別保證了在同一個事務中多次讀取同樣的記錄的結果是一致的。但是，理論上，可重復讀隔離級別還是無法解決另一個幻讀 （PhantomRead）的問題。所謂幻讀，指的是當某個事務在讀取某個范圍內的記錄時，另外一個事務又在該范圍內插入了新的記錄，當之前的事務再次讀 取該范圍的記錄時，會產生幻行（Phantom Row）。InnoDB和XtraDB 存儲引擎通過多版并發(fā)控制（MVCC ,Multivesion Concurrency Control ）解決了幻讀問題。

可重復讀是Mysql 默認的事務隔離級別。

SERIALIZABLE(可串行化)

SERIALIZABLE是最高的隔離級別。它通過強制事務串行，避免了前面說的幻讀問題。簡單的來說，SERIALIZABLE會在讀的每一行數(shù)據(jù)上 都加上鎖，所以可能導致大量的超時和鎖征用問題。實際應用中也很少用到這個隔離級別，只有在非常需要確保數(shù)據(jù)的一致性而且可以接受沒有并發(fā)的情況，才可考 慮用該級別。

簡單理解

定義

(1)READ UNCOMMITED（未提交可讀）-- 事務1修改了數(shù)據(jù)A后未提交，事務2可以讀到被事務1修改的數(shù)據(jù)A，但是事務1可能最終不提交，那么事務2讀到的就是錯誤的數(shù)據(jù)(臟數(shù)據(jù))

(2)READ COMMITED（提交了可讀）-- 事務1讀取了數(shù)據(jù)A后未提交，事務2修改了數(shù)據(jù)A且提交,然后事務1再去讀數(shù)據(jù)A會發(fā)現(xiàn)前后兩次讀結果不同，這就是不可重復讀（因為重復讀結果不一樣）

(3)REAPATABLE READ（可重復讀）-- 事務1讀取了數(shù)據(jù)A后未提交，事務2修改了數(shù)據(jù)A且提交，然后事務1再去讀數(shù)據(jù)A會發(fā)現(xiàn)前后兩次讀結果相同，這就是可重復讀 -- MYSQL默認級別

(4)SERIALIZABLE（可串行化）-- 每一行數(shù)據(jù)加鎖，導致所有事務都必須串行執(zhí)行，但是代價很大

問題

臟讀:事務內修改的數(shù)據(jù)在未提交時對外可見，(2)(3)(4)解決該問題

不可重復讀:事務內相同數(shù)據(jù)的讀結果不一致，(3)(4)解決該問題

幻讀:事務1讀取了范圍A-范圍B的數(shù)據(jù)且未提交，事務2在A-B之間插入新的一行數(shù)據(jù)后提交，事務1再去讀取會發(fā)現(xiàn)多了一行數(shù)據(jù)，就像幻覺一樣,只有(4)與MVCC多版本并發(fā)控制解決

隔離級別　　　　臟讀可能性　　　　不可重復讀可能性　　　　幻讀可能性　　　　加鎖讀

READ UNCOMMITED　　YES　　　YES　　　　　　　　　　YES　　　　　　　NO

READ COMMITED　　　　NO　　　YES　　　　　　　　　　YES　　　　　　　NO

REPEATABLE READ　　　NO　　　NO　　　　　　　　　　 YES　　　　　　 NO

SERIALIZABLE 　　　　　NO 　　 NO　　　　　　　　　　　NO　　　　　　　YES