本文來自：http://blog.csdn.net/kevinlifeng/article/details/43233227

Sql語句優化和索引

1.Innerjoin和左連線，右連線，子查詢

A.     inner join內連線也叫等值連線是，left/rightjoin是外連線。

SELECT A.id,A.name,B.id,B.name FROM A LEFT JOIN B ON A.id =B.id;

SELECT A.id,A.name,B.id,B.name FROM A RIGHT JOIN ON B A.id= B.id;

SELECT A.id,A.name,B.id,B.name FROM A INNER JOIN ON A.id =B.id;

經過來之多方面的證實inner join效能比較快，因為inner join是等值連線，或許返回的行數比較少。但是我們要記得有些語句隱形的用到了等值連線，如：

SELECT A.id,A.name,B.id,B.name FROM A,B WHERE A.id = B.id;

推薦：能用inner join連線儘量使用inner join連線

B．子查詢的效能又比外連線效能慢，儘量用外連線來替換子查詢。

  Select* from A where exists (select * from B where id>=3000 and A.uuid=B.uuid);

A表的資料為十萬級表，B表為百萬級表，在本機執行差不多用2秒左右，我們可以通過explain可以檢視到子查詢是一個相關子查詢(DEPENDENCE SUBQUERY);Mysql是先對外表A執行全表查詢，然後根據uuid逐次執行子查詢，如果外層表是一個很大的表，我們可以想象查詢效能會表現比這個更加糟糕。

  一種簡單的優化就是用innerjoin的方法來代替子查詢，查詢語句改為：

   Select* from A inner join B using(uuid) where b.uuid>=3000;

  這個語句執行測試不到一秒；

C．在使用ON 和 WHERE 的時候，記得它們的順序，如：

SELECT A.id,A.name,B.id,B.name FROM A LEFT JOIN B ON A.id =B.id WHERE B.NAME=’XXX’

執行過程會先執行ON 後面先過濾掉B表的一些行數。然而WHERE是後再過濾他們兩個連線產生的記錄。

不過在這裡提醒一下大家：ON後面的條件只能過濾出B表的條數，但是連線返回的記錄的行數還是A表的行數是一樣。如：

SELECT A.id,A.name,B.id,B.name FROM A LEFT JOIN B ON A.id =B.id;

返回的記錄數是A表的條數，ON後面的條件只起到過濾B表的記錄數，而

SELECT A.id,A.name,B.id,B.name FROM A ,B WHERE A.id = B.id

返回的條數，是笛卡爾積後，符合A.id = B.id這個條件的記錄

D．使用JOIN時候，應該用小的結果驅動打的結果（left join 左邊表結果儘量小，如果有條件應該放到左邊先處理，right join同理反向），同事儘量把牽涉到多表聯合的查詢拆分多個query(多個表查詢效率低，容易鎖表和阻塞)。如：

Select * from A left join B ona.id=B.ref_id where B.ref_id>10;

可以優化為：select * from (select * from A wehre id >10) T1 left join B onT1.id=B.ref_id;

2.建立索引,加快查詢效能.

A．在建立複合索引的時候，在where條件中用到的欄位在複合索引中，則最好把這個欄位放在複合索引的最左端，這樣才能使用索引，才能提高查詢。

B．保證連線的索引是相同的型別，意思就是A表和B表相關聯的欄位，必須是同型別的。這些型別都建立了索引，這樣才能兩個表都能使用索引，如果型別不一樣，至少有一個表使用不了索引。

C．索引，不僅僅是主鍵和唯一鍵，也可以是其他的任何列。在使用like其中一個有索引的欄位列的時候。

如： select *from A name like ‘xxx%’;

這個sql會使用name的索引（前提name建立了索引）；而下面的語句就使用不了索引

Select * from A name like ‘%xxx’;

因為‘%’代表任何字元，%xxx不知道怎麼去索引的，所以使用不了索引。

D.複合索引

比如有一條語句這樣的：select* from users where area =’beijing’ and age=22;

如果我們是在area和age上分別建立索引的話，由於mysql查詢每次只能使用一個索引，所以雖然這樣已經相對不做索引時全表掃描提高了很多效率，但是如果area，age兩列上建立複合索引的話將帶來更高的效率。如果我們建立了（area,age,salary）的複合索引，那麼其實相當於建立了（area,age,salary）,(area,age),(area)三個索引，這樣稱為最佳左字首特性。因此我們在建立複合索引的應該將最常用作限制條件的列放在最左邊，依次遞減。

E.索引不會包含有NULL值的列

只要列中包含有NULL值都將不會被包含在索引中（除非是唯一值的域，可以存在一個NULL），複合索引中只要有一列含有NULL值，那麼這一列對於此複合索引是無效的。所以我們在資料庫設計時不要讓欄位的預設值為NULL.

F.使用短索引

對串列進行索引，如果可能應該指定一個字首長度。例如，如果有一個CHAR(255)的列，如果在錢10個或者20字元內，多數值是唯一的，那麼就不要對整個列進行索引。短索引不僅可以提高查詢速度而且可以節省磁碟空間和I/O操作。

G．排序的索引問題

Mysql查詢只是用一個索引，因此如果where子句中已經使用了索引的話，那麼order by中的列是不會使用索引的。因此資料庫預設排序可以符合要求情況下不要使用排序操作；儘量不要包含多個列的排序，如果需要最好給這些列建立複合索引。

3.limit千萬級分頁的時候優化。

A．在我們平時用limit,如：

Select * from A order by id limit 1,10;

這樣在表資料很少的時候，看不出什麼效能問題，倘若到達千萬級，如：

Select * from A order by id limit10000000,10;

雖然都是隻查詢10記錄，但是這個就效能就讓人受不了了。所以為什麼當表資料很大的時候，我們還繼續用持久層框架如hibernate,ibatis就會有一些效能問題，除非持久層框架對這些大資料表做過優化。

B．在遇見上面的情況，我們可以用另外一種語句優化，如：

Select * from A where id>=(Select idfrom a limit 10000000,1) limit 10;

確實這樣快了很多，不過前提是，id欄位建立了索引。也許這個還不是最優的，其實還可以這樣寫：

Select * from A where id between 10000000and 10000010;

這樣的效率更加高。

4.儘量避免Select * 命令

A.從表中讀取越多的資料，查詢會變得更慢。它會增加磁碟的操作時間，還是在資料庫伺服器與web伺服器是獨立分開的情況下，你將會經歷非常漫長的網路延遲。僅僅是因為資料不必要的在伺服器之間傳輸。

5.儘量不要使用BY RAND()命令

 A．如果您真需要隨機顯示你的結果，有很多更好的途徑實現。而這個函式可能會為表中每一個獨立的行執行BY RAND()命令—這個會消耗處理器的處理能力，然後給你僅僅返回一行。

 

6.利用limit 1取得唯一行

 A．有時要查詢一張表時，你要知道需要看一行，你可能去查詢一條獨特的記錄。你可以使用limit 1.來終止資料庫引擎繼續掃描整個表或者索引,如：

Select * from A  where namelike ‘%xxx’ limit 1;

這樣只要查詢符合like ‘%xxx’的記錄，那麼引擎就不會繼續掃描表或者索引了。

 

7.儘量少排序

A.排序操作會消耗較多的CPU資源，所以減少排序可以在快取命中率高等

 

8.儘量少OR

 A.當where子句中存在多個條件以“或”並存的時候，Mysql的優化器並沒有很好的解決其執行計劃優化問題，再加上mysql特有的sql與Storage分層架構方式，造成了其效能比較地下，很多時候使用union all或者union(必要的時候)的方式代替“or”會得到更好的效果。

 

9.儘量用union all 代替union

 A.union和union all的差異主要是前者需要將兩個（或者多個）結果集合並後再進行唯一性過濾操作，這就會涉及到排序，增加大量的cpu運算，加大資源消耗及延遲。所以當我們可以確認不可能出現重複結果集或者不在乎重複結果集的時候，儘量使用union all而不是union.

10.避免型別轉換

A.這裡所說的“型別轉換”是指where子句中出現column欄位的型別和傳入的引數型別不一致的時候發生的型別轉換。人為的上通過轉換函式進行轉換，直接導致mysql無法使用索引。如果非要轉型，應該在傳入引數上進行轉換。

 

11.不要在列上進行運算

A. 如下面:select * fromusers where YEAR(adddate)<2007;將在每個行進行運算，這些導致索引失效進行全表掃描，因此我們可以改成：

Select * from users where adddate<’2007-01-01’;

 

12.儘量不要使用NOT IN和<>操作

A. NOT IN和<>操作都不會使用索引，而是將會進行全表掃描。NOT IN可以NOT EXISTS代替，id<>3則可以使用id>3 or id <3;如果NOT EXISTS是子查詢，還可以儘量轉化為外連線或者等值連線，要看具體sql的業務邏輯。

B．把NOT IN轉化為LEFT JOIN如：

SELECT * FROM customerinfo WHERE CustomerIDNOT in (SELECT CustomerID FROM salesinfo );

優化：

SELECT * FROM customerinfo LEFT JOINsalesinfoON customerinfo.CustomerID=salesinfo. CustomerID WHEREsalesinfo.CustomerID IS NULL;

 

13.使用批量插入節省互動（最好是使用儲存過程）

A. 儘量使用insert intousers(username,password) values(‘test1’,’pass1’), (‘test2’,’pass2’), (‘test3’,’pass3’);

 

14. 鎖定表

A. 儘管事務是維護資料庫完整性的一個非常好的方法,但卻因為它的獨佔性,有時會影響資料庫的效能,尤其是在很多的應用系統中.由於事務執行的過程中,資料庫將會被鎖定,因此其他的使用者請求只能暫時等待直到該事務結算.如果一個資料庫系統只有少數幾個使用者來使用,事務造成的影響不會成為一個太大問題;但假設有成千上萬的使用者同時訪問一個資料庫系統,例如訪問一個電子商務網站,就會產生比較嚴重的響應延遲.其實有些情況下我們可以通過鎖定表的方法來獲得更好的效能.如:

LOCK TABLE inventory write

Select quanity from inventory whereitem=’book’;

…

Update inventory set quantity=11 whereitem=’book’;

UNLOCK TABLES;

這裡，我們用一個select語句取出初始資料，通過一些計算，用update語句將新值更新到列表中。包含有write關鍵字的LOCK TABLE語句可以保證在UNLOCK TABLES命令被執行之前，不會有其他的訪問來對inventory進行插入，更新或者刪除的操作。

 

15.對多表關聯的查詢，建立檢視

A．對多表的關聯可能會有效能上的問題，我們可以對多表建立檢視，這樣操作簡單話，增加資料安全性，通過檢視，使用者只能查詢和修改指定的資料。且提高表的邏輯獨立性，檢視可以遮蔽原有表結構變化帶來的影響。

==============================以下來自另一個部落格

MySQL是一個強大的開源資料庫。隨著MySQL上的應用越來越多，MySQL逐漸遇到了瓶頸。這裡提供 101 條優化 MySQL 的建議。有些技巧適合特定的安裝環境，但是思路是相通的。我已經將它們分成了幾類以幫助你理解。

MySQL監控

MySQL伺服器硬體和OS（作業系統）調優：

1、有足夠的實體記憶體，能將整個InnoDB檔案載入到記憶體裡 —— 如果訪問的檔案在記憶體裡，而不是在磁碟上，InnoDB會快很多。

2、全力避免 Swap 操作 — 交換（swapping）是從磁碟讀取資料，所以會很慢。

3、使用電池供電的RAM（Battery-Backed RAM）。

4、使用一個高階磁碟陣列 — 最好是 RAID10 或者更高。

5、避免使用RAID5 — 和校驗需要確保完整性，開銷很高。

6、將你的作業系統和資料分開，不僅僅是邏輯上要分開，物理上也要分開 — 作業系統的讀寫開銷會影響資料庫的效能。

7、將臨時檔案和複製日誌與資料檔案分開 — 後臺的寫操作影響資料庫從磁碟檔案的讀寫操作。

8、更多的磁碟空間等於更高的速度。

9、磁碟速度越快越好。

10、SAS優於SATA。

11、小磁碟的速度比大磁碟的更快，尤其是在 RAID 中。

12、使用電池供電的快取 RAID（Battery-Backed Cache RAID）控制器。

13、避免使用軟磁碟陣列。

14. 考慮使用固態IO卡（不是磁碟）來作為資料分割槽 — 幾乎對所有量級資料，這種卡能夠支援 2 GBps 的寫操作。

15、在 Linux 系統上，設定 swappiness 的值為0 — 沒有理由在資料庫伺服器上快取檔案，這種方式在Web伺服器或桌面應用中用的更多。

16、儘可能使用 noatime 和 nodirtime 來掛載檔案系統 — 沒有必要為每次訪問來更新檔案的修改時間。

17、使用 XFS 檔案系統 — 一個比ext3更快的、更小的檔案系統，擁有更多的日誌選項，同時，MySQL在ext3上存在雙緩衝區的問題。

18、優化你的 XFS 檔案系統日誌和緩衝區引數 – -為了獲取最大的效能基準。

19、在Linux系統中，使用 NOOP 或 DEADLINE IO 排程器 — CFQ 和 ANTICIPATORY 排程器已經被證明比 NOOP 和 DEADLINE 慢。

20、使用 64 位作業系統 — 有更多的記憶體能用於定址和 MySQL 使用。

21、將不用的包和後臺程式從伺服器上刪除 — 減少資源佔用。

22、將使用 MySQL 的 host 和 MySQL自身的 host 都配置在一個 host 檔案中 — 這樣沒有 DNS 查詢。

23、永遠不要強制殺死一個MySQL程式 — 你將損壞資料庫，並執行備份。

24、讓你的伺服器只服務於MySQL — 後臺處理程式和其他服務會佔用資料庫的 CPU 時間。

 

MySQL 配置：

25、使用 innodb_flush_method=O_DIRECT 來避免寫的時候出現雙緩衝區。

26、避免使用 O_DIRECT 和 EXT3 檔案系統 — 這會把所有寫入的東西序列化。

27、分配足夠 innodb_buffer_pool_size ，來將整個InnoDB 檔案載入到記憶體 — 減少從磁碟上讀。

28、不要讓 innodb_log_file_size 太大，這樣能夠更快，也有更多的磁碟空間 — 經常重新整理有利降低發生故障時的恢復時間。

29、不要同時使用 innodb_thread_concurrency 和 thread_concurrency 變數 — 這兩個值不能相容。

30、為 max_connections 指定一個小的值 — 太多的連線將耗盡你的RAM，導致整個MySQL伺服器被鎖定。

31、保持 thread_cache 在一個相對較高的數值，大約是 16 — 防止開啟連線時候速度下降。

32、使用 skip-name-resolve — 移除 DNS 查詢。

33、如果你的查詢重複率比較高，並且你的資料不是經常改變，請使用查詢快取 — 但是，在經常改變的資料上使用查詢快取會對效能有負面影響。

34、增加 temp_table_size — 防止磁碟寫。

35、增加 max_heap_table_size — 防止磁碟寫。

36、不要將 sort_buffer_size 的值設定的太高 — 可能導致連線很快耗盡所有記憶體。

37、監控 key_read_requests 和 key_reads，以便確定 key_buffer 的值 — key 的讀需求應該比 key_reads 的值更高，否則使用 key_buffer 就沒有效率了。

38、設定 innodb_flush_log_at_trx_commit = 0 可以提高效能，但是保持預設值（1）的話，能保證資料的完整性，也能保證複製不會滯後。

39、有一個測試環境，便於測試你的配置，可以經常重啟，不會影響生產環境。

MySQL Schema 優化：

40、保證你的資料庫的整潔性。

41、歸檔老資料 — 刪除查詢中檢索或返回的多餘的行

42、在資料上加上索引。

43、不要過度使用索引，評估你的查詢。

44、壓縮 text 和 blob 資料型別 — 為了節省空間，減少從磁碟讀資料。

45、UTF 8 和 UTF16 比 latin1 慢。

46、有節制的使用觸發器。

47、保持資料最小量的冗餘 — 不要複製沒必要的資料.

48、使用連結表，而不是擴充套件行。

49、注意你的資料型別，儘可能的使用最小的。

50、如果其他資料需要經常需要查詢，而 blob/text 不需要，則將 blob/text 資料域其他資料分離。

51、經常檢查和優化表。

52、經常做重寫 InnoDB 表的優化。

53、有時，增加列時，先刪除索引，之後在加上索引會更快。

54、為不同的需求選擇不同的儲存引擎。

55、日誌表或審計表使用ARCHIVE儲存引擎 — 寫的效率更高。

56、將 session 資料儲存在 memcache 中，而不是 MySQL 中 — memcache 可以設定自動過期，防止MySQL對臨時資料高成本的讀寫操作。

57、如果字串的長度是可變的，則使用VARCHAR代替CHAR — 節約空間，因為CHAR是固定長度，而VARCHAR不是（utf8 不受這個影響）。

58、逐步對 schema 做修改 — 一個小的變化將產生的巨大的影響。

59、在開發環境測試所有 schema 變動，而不是在生產環境的映象上去做。

60、不要隨意改變你的配置檔案，這可能產生非常大的影響。

61、有時候，少量的配置會更好。

62、質疑使用通用的MySQL配置檔案。

查詢優化：

63、使用慢查詢日誌，找出執行慢的查詢。

64、使用 EXPLAIN 來決定查詢功能是否合適。

65、經常測試你的查詢，看是否需要做效能優化 — 效能可能會隨著時間的變化而變化。

66、避免在整個表上使用count(*) ，它可能會將整個表鎖住。

67、保持查詢一致，這樣後續類似的查詢就能使用查詢快取了。

68、如果合適，用 GROUP BY 代替 DISTINCT。

69、在 WHERE、GROUP BY 和 ORDER BY 的列上加上索引。

70、保證索引簡單，不要在同一列上加多個索引。

71、有時，MySQL 會選擇錯誤的索引，這種情況使用 USE INDEX。

72、使用 SQL_MODE=STRICT 來檢查問題。

73、索引欄位少於5個時，UNION 操作用 LIMIT，而不是 OR。

74、使用 INSERT ON DUPLICATE KEY 或 INSERT IGNORE 來代替 UPDATE，避免 UPDATE 前需要先 SELECT。

75、使用索引欄位和 ORDER BY 來代替 MAX。

76、避免使用 ORDER BY RAND()。

77、LIMIT M,N 在特定場景下會降低查詢效率，有節制使用。

78、使用 UNION 來代替 WHERE 子句中的子查詢。

79、對 UPDATE 來說，使用 SHARE MODE 來防止排他鎖。

80、重啟 MySQL 時，記得預熱資料庫，確保將資料載入到記憶體，提高查詢效率。

81、使用 DROP TABLE ，然後再 CREATE TABLE ，而不是 DELETE FROM ，以刪除表中所有資料。

82、最小化你要查詢的資料，只獲取你需要的資料，通常來說不要使用 *。

83、考慮持久連線，而不是多次建立連線，已減少資源的消耗。

84、基準查詢，包括伺服器的負載，有時一個簡單的查詢會影響其他的查詢。

85、當伺服器的負載增加時，使用SHOW PROCESSLIST來檢視慢的/有問題的查詢。

86、在存有生產環境資料副本的開發環境中，測試所有可疑的查詢。

MySQL備份過程：

87、在二級複製伺服器上進行備份。

88、備份過程中停止資料的複製，以防止出現資料依賴和外來鍵約束的不一致。

89、徹底停止MySQL之後，再從資料檔案進行備份。

90、如果使用MySQL dump進行備份，請同時備份二進位制日誌 — 確保複製過程不被中斷。

91、不要信任 LVM 快照的備份 — 可能會建立不一致的資料，將來會因此產生問題。

92、為每個表做一個備份，這樣更容易實現單表的恢復 — 如果資料與其他表是相互獨立的。

93、使用 mysqldump 時，指定 -opt 引數。

94、備份前檢測和優化表。

95、臨時禁用外來鍵約束，來提高匯入的速度。

96、臨時禁用唯一性檢查，來提高匯入的速度。

97、每次備份完後，計算資料庫/表資料和索引的大小，監控其增長。

98、使用定時任務（cron）指令碼，來監控從庫複製的錯誤和延遲。

99、定期備份資料。

100、定期測試備份的資料。