MYSQL查詢優(yōu)化心得

1、查詢優(yōu)化學(xué)習(xí)心得Mysql性能優(yōu)化有兩個方面，一個是邏輯查詢，一個是物理查詢。邏輯查詢主要從連接的等價轉(zhuǎn)換，連接消除，條件下推等方式對語句進行簡化。子查詢是邏輯優(yōu)化過程中需要重點關(guān)注的對象，子查詢用的不好，往往會造成過多的表掃描。關(guān)于子查詢的優(yōu)化，需要注意以下幾點：，在執(zhí)行計劃中出現(xiàn)subQuery, Depented SubQuery的查詢方式，說明子查詢語句不能優(yōu)化，查詢語句按默認從內(nèi)外往執(zhí)行。先執(zhí)行子查詢語句，得到一個中間結(jié)果集，再執(zhí)行外層語句。這種情況主要出現(xiàn)于非spj的子查詢語句中，比如含有distinct ,group by 聚合函數(shù)，order by limit,union.和相

2、關(guān)子查詢。，可以進行優(yōu)化的子查詢語句表現(xiàn)為 嵌套循環(huán)Nested Loop算法,連接方式可以是內(nèi)聯(lián)接(inner join)或者是半聯(lián)接(anti-semi-join)如果說在mysql中，內(nèi)聯(lián)接的Nested Loop屬于子查詢展開的話，那么半聯(lián)接類型的子查詢一般是不能夠展開的，屬于半聯(lián)接的運算符有exists,not exists，in,not in,半聯(lián)接是一種查找算法，只要查找到等值數(shù)據(jù)立刻退出本次循環(huán)，not exists,not in則不同，需要全表掃描。對于嵌套循環(huán)Nested Loop，mysql的一種優(yōu)化方式是Materialized,對子查詢語句先進行物化，再與外表進行Ne

3、sted Loop在執(zhí)行計劃中，看到derived的查詢類型，說明在Nested Loop中，該表作為驅(qū)動表，根據(jù)Nested Loop運算符，先遍歷驅(qū)動表的每一行，針對每一行記錄，再到內(nèi)表中查找對應(yīng)的記錄。驅(qū)動表為全表掃描的外表。3,對于循套嵌套Nested loop算法，驅(qū)動表的索引并不重要，但查找的內(nèi)表字段最好有索引，如果有索引，子查詢將會優(yōu)化成簡單查詢（simple），也許，這就是子查詢最優(yōu)的解法吧（轉(zhuǎn)成了簡單查詢）.4,>all,>any,>some運算符雖然是ansi聯(lián)接標準，但是由于這些運算符在語義上并不直觀，而且，這些運算符在查詢優(yōu)化階段也會被轉(zhuǎn)換成對應(yīng)max

4、,min運算符。所以實際并不常用。所以這類運算符也不能被優(yōu)化。=all,=some,=any 屬于等值聯(lián)接，它們可以被優(yōu)化。在sqlserver中，子查詢都轉(zhuǎn)換為嵌套循環(huán)Nested Loop.其性能受Nested Loop算法約束。5，在實際生產(chǎn)環(huán)境中，最常見的子查詢優(yōu)化是not in,not exists子查詢，一般會將其轉(zhuǎn)換成left join + where column is null。再一個就是注意子查詢和父查詢的相應(yīng)索引的使用。當(dāng)然，并非子查詢就是不好的:對于兩個表都比較小，使用子查詢，較低的查找開銷比聯(lián)結(jié)更優(yōu)勝.匹配只返回一個值，使用子查詢，與必須聯(lián)結(jié)整張表相比，只找錄一條記錄

5、并替換它花費的開銷要少很多匹配只返回相當(dāng)少的值，而且查詢列上沒有索引，使用子查詢，通常，單獨的一次或者甚至數(shù)次查找所花費的開銷都比散列聯(lián)結(jié)少另，子查詢也是必不可少，很多業(yè)務(wù)邏輯必須要用子查詢來實現(xiàn)。一些實驗的例子：相關(guān)子查詢 select * from t4 where t4.id4 = any ( select t5.id5 from t5 where t5.b5 = t4.b4); 非相關(guān)子查詢 select * from t1 where a1 = any (select a2 from t2 where t2.a2 = 10); IN 操作符 SELECT column_name(s)

6、 FROM table_name WHERE column_name IN (value1,value2,.)select * from t4 where t4.id4 in (1,3); ALLANYSOME操作符select * from t5 where t5.id5 > any ( select id4 from t4 where t4.id4>5); select * from t5 where t5.id5 > ALL ( select id4 from t4 where t4.id4>5); EXISTS操作符select * from t5 where

7、exists ( select * from t4 where t4.id4>t5.id5); 轉(zhuǎn)化成同義的IN語句： select * from t5 where id5 in ( select id5 from t4 where t4.id4>t5.id5); SPJ查詢： 選擇：select * from t4 where t4.id4=1; 投影: select id4, b4 from t4; 連接：select * from t4, t5 where t4.id4 = t5.id5;group by子查詢：SELECT Customer,SUM(OrderPrice)

8、FROM Orders GROUP BY Customer TOP-N : SELECT TOP 50 PERCENT * FROM Customers; mysql 不支持 等價于Mysql中 select * from t4 limit 10; LIMIT & order by: select * from t4 order by id4 desc limit 2,5; select n1,n2,n3 from ta inner join tb on ta.n1= ta.n2        &

9、#160;      現(xiàn)有表a                        name  hot                         a        12              &#

10、160;          b        10                         c        15                    表b:          &

11、#160;             name   hot                          d        12                          e       &

12、#160;10                          f         10                          g        8             

13、      表a左連接表b，查詢hot相同的數(shù)據(jù)                    select a.*,b.* from a left join b on a.hot = b.hot                    查詢結(jié)果：               

14、        name  hot   name  hot                          a       12     d       12                     

15、60;   b       10     e       10                          b       10     f        10                 

16、         c       15     null    null                    從上面可以看出，查詢結(jié)果表a的列都存在，表b的數(shù)據(jù)只顯示符合條件的項目                            &

17、#160; 再如表b左連接表a，查詢hot相同的數(shù)據(jù)                        select a.*,b.* from b left join a on a.hot = b.hot                        查詢結(jié)果為：           

18、;             name  hot   name  hot                          d       12     a       12                  &

19、#160;       e        10    b       10                          f        10     b      10           

20、              g        8     null    null                    再如表a右連接表b，查詢hot相同的數(shù)據(jù)                        select a.*

21、,b.* from a right join b on a.hot = b.hot                        查詢結(jié)果和上面的b left join a一樣優(yōu)化的另一個方面是物理查詢。在物理查詢方面，主要影響性能的地方是IO和cpu使用資源。對于優(yōu)化物理查詢，有一些建議：1,在保證實現(xiàn)功能的基礎(chǔ)上，盡量減少對數(shù)據(jù)庫的訪問次數(shù)(可以用緩存保存查詢結(jié)果，減少查詢次數(shù))；2,通過搜索參數(shù)，盡量減少對表的訪問行數(shù),最小化結(jié)果集， 從而減輕網(wǎng)絡(luò)負

22、擔(dān)；3,能夠分開的操作盡量分開處理，提高每次的響應(yīng)速度；4,在數(shù)據(jù)窗口使用SQL時，盡量把使用的索引放在選擇的首列；5,算法的結(jié)構(gòu)盡量簡單；6,在 查詢時，不要過多地使用通配符如SELECT * FROM T1語句，要用到幾列就選擇幾列如：SELECTCOL1,COL2 FROM T1；7,在可能的情況下盡量限制盡量結(jié)果集行數(shù)如：SELECT TOP 300 COL1,COL2,COL3 FROM T1,因為某些情況下用戶是不需要那么多的數(shù)據(jù)的。在沒有建索引的情況下，數(shù)據(jù)庫查找某一條數(shù)據(jù)，就必須進行全表掃描了，對所有數(shù)據(jù)進行一次遍歷，查找出符合條件的記錄。在數(shù)據(jù)量比較小的情況下，也許看不出明顯

23、的差別，但是當(dāng)數(shù)據(jù)量大的情況下，這種情況就是極為糟糕的了。所以，優(yōu)化查詢最重要的就是，盡量使語句符合查詢優(yōu)化器的規(guī)則避免全表掃描而使用索引查詢。具體要注意的：1.應(yīng)盡量避免在 where 子句中對字段進行 null 值判斷，否則將導(dǎo)致引擎放棄使用索引而進行全表掃描，如：select id from t where num is null可以在num上設(shè)置默認值0，確保表中num列沒有null值，然后這樣查詢：select id from t where num=02.應(yīng)盡量避免在 where 子句中使用!=或<>操作符，否則將引擎放棄使用索引而進行全表掃描。優(yōu)化器將無法通過索引來確

24、定將要命中的行數(shù),因此需要搜索該表的所有行。3.應(yīng)盡量避免在 where 子句中使用 or 來連接條件，否則將導(dǎo)致引擎放棄使用索引而進行全表掃描，如：select id from t where num=10 or num=20可以這樣查詢：select id from t where num=10union allselect id from t where num=204.in 和 not in 也要慎用，因為IN會使系統(tǒng)無法使用索引,而只能直接搜索表中的數(shù)據(jù)。如：select id from t where num in(1,2,3)對于連續(xù)的數(shù)值，能用 between 就不要用 in

25、了：select id from t where num between 1 and 35.盡量避免在索引過的字符數(shù)據(jù)中，使用非打頭字母搜索。這也使得引擎無法利用索引。關(guān)于物理查詢的優(yōu)化，重點放在索引的創(chuàng)建上。索引對查詢的速度有著至關(guān)重要的影響，索引也是進行數(shù)據(jù)庫性能調(diào)優(yōu)的起點。比如，數(shù)據(jù)庫中一個表有106條記錄，DBMS的頁面大小為 4K，并存儲100條記錄。如果沒有索引，查詢將對整個表進行掃描，最壞的情況下，如果所有數(shù)據(jù)頁都不在內(nèi)存，需要讀取104個頁面，如果這104個 頁面在磁盤上隨機分布，需要進行104次I/O，假設(shè)磁盤每次I/O時間為10ms(忽略數(shù)據(jù)傳輸時間)，則總共需要100s(

26、但實際上要好很多很 多)。如果建立B-Tree索引，則只需要進行l(wèi)og100(106)=3次頁面讀取，最壞情況下耗時30ms。當(dāng)應(yīng)用程序進行SQL查詢速度很慢時，想想是否可以建索引。MySQL支持很多數(shù)據(jù)類型，選擇合適的數(shù)據(jù)類型存儲數(shù)據(jù)對性能有很大的影響。(1) 越小的數(shù)據(jù)類型通常更好：越小的數(shù)據(jù)類型通常在磁盤、內(nèi)存和CPU緩存中都需要更少的空間，處理起來更快。(2) 簡單的數(shù)據(jù)類型更好：整型數(shù)據(jù)比起字符，處理開銷更小，因為字符串的比較更復(fù)雜。在MySQL中，應(yīng)該用內(nèi)置的日期和時間數(shù)據(jù)類型，而不是用字符串來存儲時間；以及用整型數(shù)據(jù)類型存儲IP地址。(3) 盡量避免NULL：應(yīng)該指定列為NOT

27、NULL，除非你想存儲NULL。在MySQL中，含有空值的列很難進行查詢優(yōu)化，因為它們使得索引、索引的統(tǒng)計信息以及比較運算更加復(fù)雜。你應(yīng)該用0、一個特殊的值或者一個空串代替空值。索引的特性：1, 如果對多列進行索引(組合索引)，MySQL僅能對索引最左邊的前綴進行有效的查找。比如：存在組合索引it1c1c2(c1,c2)，查詢語句select * from t1 where c1=1 and c2=2能夠使用該索引。查詢語句select * from t1 where c1=1也能夠使用該索引。但是，查詢語句select * from t1 where c2=2不能夠使用該索引，因為沒有組合索

28、引的引導(dǎo)列，即，要想使用c2列進行查找，必需出現(xiàn)c1等于某值。建立索引的原則：1.合理使用索引一個Table在一次query中只能使用一個索引,使用EXPLAIN語句來檢驗優(yōu)化程序的操作情況使用analyze幫助優(yōu)化程序?qū)λ饕氖褂眯Ч龀龈鼫蚀_的預(yù)測2.索引應(yīng)該創(chuàng)建在搜索、排序、歸組等操作所涉及的數(shù)據(jù)列上3.盡量將索引建立在重復(fù)數(shù)據(jù)少的數(shù)據(jù)列中，唯一所以最好例如：生日列，可以建立索引，但性別列不要建立索引4.盡量對比較短的值進行索引降低磁盤IO操作，索引緩沖區(qū)中可以容納更多的鍵值，提高命中率如果對一個長的字符串建立索引，可以指定一個前綴長度5.合理使用多列索引如果多個條件經(jīng)常需要組合起來查詢

29、,則要使用多列索引(因為一個表一次查詢只能使用一個索引,建立多個單列索引也只能使用一個)6.充分利用最左前綴也就是要合理安排多列索引中各列的順序，將最常用的排在前面7.不要建立過多的索引只有經(jīng)常應(yīng)用于where,order by,group by中的字段需要建立索引.8.利用慢查詢?nèi)罩静檎页雎樵?log-slow-queries, long_query_time)使用索引的一些建議：1.盡量比較數(shù)據(jù)類型相同的數(shù)據(jù)列2.盡可能地讓索引列在比較表達式中獨立, WHERE mycol < 4 / 2 使用索引,而WHERE mycol * 2 < 4不使用3.盡可能不對查詢字段加函數(shù),

30、如WHERE YEAR(date_col) < 1990改造成WHERE date_col < 1990-01-01WHERE TO_DAYS(date_col) - TO_DAYS(CURDATE() < cutoff 改造成WHERE date_col < DATE_ADD(CURDATE(), INTERVAL cutoff DAY)4.在LIKE模式的開頭不要使用通配符5.使用straight join可以強制優(yōu)化器按照FROM子句的次序來進行聯(lián)結(jié),可以select straight join,強制所有聯(lián)結(jié),也可以select * from a straight

31、 join b強制兩個表的順序.6.使用force index強制使用指定的索引.如 select * from song_lib force index(song_name) order by song_name比不用force index效率高7.盡量避免使用MySQL自動類型轉(zhuǎn)換,否則將不能使用索引.如將int型的num_col用where num_col=5MySQL中，有兩種方式生成有序結(jié)果集：一是使用filesort，二是按索引順序掃描。利用索引進行排序操作是非?？斓?，而且可以利用同一索引同時進 行查找和排序操作。當(dāng)索引的順序與ORDER BY中的列順序相同且所有的列是同一方向(全

32、部升序或者全部降序)時，可以使用索引來排序。如果查詢是連接多個表，僅當(dāng)ORDER BY中的所有列都是第一個表的列時才會使用索引。其它情況都會使用filesort。 當(dāng)MySQL不能使用索引進行排序時，就會利用自己的排序算法(快速排序算法)在內(nèi)存(sort buffer)中對數(shù)據(jù)進行排序，如果內(nèi)存裝載不下，它會將磁盤上的數(shù)據(jù)進行分塊，再對各個數(shù)據(jù)塊進行排序，然后將各個塊合并成有序的結(jié)果集（實際上就是 外排序）。對于filesort，MySQL有兩種排序算法。create table actor(actor_id int unsigned NOT NULL AUTO_INCRE

33、MENT,name      varchar(16) NOT NULL DEFAULT '',password        varchar(16) NOT NULL DEFAULT '',PRIMARY KEY(actor_id), KEY     (name) ENGINE=InnoDBinsert into actor(name,password) values('cat01

34、9;,'1234567');insert into actor(name,password) values('cat02','1234567');insert into actor(name,password) values('ddddd','1234567');insert into actor(name,password) values('aaaaa','1234567');mysql> explain select actor_id from actor order by

35、 actor_id G* 1. row *           id: 1 select_type: SIMPLE        table: actor         type: indexpossible_keys: NULL          key: PRIMARY      key_len: 4          ref: NULL         rows: 4        Extra: Using index1 row in set (0.00 sec) mysql> explain select