連接查詢的優化

無論什么數據庫，多表連接的查詢成本都是比較高的，因此對于高并發應用，應該盡量減少有連接的查詢，多表連接的個數不要超過4張表。一般數據量少的時候，連接開小不大，一般不會有性能問題，當數據量變大后，那么性能問題就會比較突出。所以在數據庫初期最好能確定哪個表能成為大表，然后進行反范式設計減少連接的表，例如增加冗余字段等等，或者在業務代碼中進行連接計算。

一些經驗總結點：

1、ON、USING字句中的列確認有索引，如果連接的順序為B、A，那么只需在A表的列上創建索引即可，無需在B中建索引，可以減少不必要索引開銷。

查詢舉例：

SELECT B.*,A.* FROM B JOIN A ON B.col1 = A.col2 

MYSQL會全表掃描B表，對B表的每一行記錄去尋找A表記錄，所以需用A表COL2列上索引來提高效率。

2、使用EXPLAIN 檢查連接，看ROWS列，如果該列值太高，比如幾千，上萬的，那么就需要考慮是否索引無效后者連接表的順序不對了。

3、考慮在應用層實現連接查詢，例如可以在JAVA中把復雜的查詢分解為幾個簡單查詢，得到一個較小的結果集合，處理遍歷后，再根據條件獲取完整數據，這樣做往往更高效，因為把數據分離，更不容易變化，有利于數據庫緩存數據。

舉例如下：

SELECT a.* FROM A WHERE a.id IN(1,2,3,4,5,6,7,8,9,10); 

如果id=1~8的記錄已經被存儲在緩存REDIS中了，那么我們只需要查詢id=9和10的數據，這樣減少了很多數據庫連接交互，可以提高性能。

GROUP BY、DISTINCT、ORDER BY 語句優化

這些語句默認都要進行ORDER BY排序，優化的思路比較類似。

1、如果多張表進行連接查詢，ORDER BY 的列應屬于連接順序的第一張表。如果不在同一個表中，那么可以考慮冗余一些列，或者合并表。

2、需要保證索引列和ORDER BY的列相同，且各列按照相同的方向進行排序。

3、指定ORDER BY NULL，默認情況下，MYSQL將排序所有GROUP BY的查詢，如果想要避免排序結果所產生的消耗，可以指定ORDER BY NULL。

舉例如下：

select count(1) from sys_dept group by dept_id order by null limit 3 

子查詢優化

由于子查詢可讀性比較符合開發人員的思路習慣，所以都習慣編寫子查詢，但子查詢在生產環境中，是最常見的性能瓶頸。

對于數據庫來說，大部分情況下，連接比子查詢更快，優化器一般可以生成更佳的執行計劃，可以余弦裝載數據，更高效的處理查詢，子查詢生成的臨時表也沒有索引，因此效率會更低。

目前的實踐來說，子查詢應該盡量改寫成JOIN的寫法

舉個常見的例子

SELECT c1 FROM t1 where t1.c1 IN (SELECT c1 FROM t2); 

我們可以轉化為連接的方式：

SELECT c1 FROM t1.c1 FROM t1,t2 WHERE t1.c1=t2.c2 

優化IN列表

對于IN列表，MySQL會排序里面的值，并使用二分查找方式去定位數據，把IN字句改寫成OR形式其實沒什么用。IN列表不建議太長，對于高并發業務，建議不超過幾十個。優化思路可以轉化為多個等于的查詢。例如下面的語句，如果ID值很多，其實性能不會太好。

SELECT * FROM A where A.ID IN(SELECT id FROM B) 

優化思路：

可以從程序業務層出發，先查詢SELECT id FROM B，然后獲取到ID的值，逐步和 SELECT * FROM A進行拼接，轉化為 SELECT * FROM A where ID =？的形式。

優化UNION

UNION語句默認是去除重復記錄，需要用到排序操作，如果結果集很大，成本會很高，建議盡量使用UNION ALL 語句，對于UNION多個分表場景，應盡可能在數據庫分表的時候，就確定各個分表數據唯一性，這樣就無需使用UNION來去重了。

另外查詢語句外的WHERE條件并不會應用到每個單獨的UNION子句中，所以每個UNION子句都添加where條件。

優化BLOB、TEXT類型字段的查詢

由于mysql內存臨時表暫不支持BLOB、TEXT類型，如果包含他們的查詢就要用到基于磁盤的臨時表，性能會很低，所以如無必要，查詢條件就不要這2種類型。

優化思路：

1、如果必須使用，可以考慮拆分表，把BLOB、TEXT字段分離到單獨的表中。

2、如果有許多大字段，可以考慮合并這些字段到一個字段，存儲一個大200KB比存儲20個10KB更有效。

3、考慮使用COMPRESS（），再存儲。