連接查詢的優(yōu)化

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

一些經(jīng)驗(yàn)總結(jié)點(diǎn)：

1、ON、USING字句中的列確認(rèn)有索引，如果連接的順序?yàn)锽、A，那么只需在A表的列上創(chuàng)建索引即可，無(wú)需在B中建索引，可以減少不必要索引開銷。

查詢舉例：

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

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

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

3、考慮在應(yīng)用層實(shí)現(xiàn)連接查詢，例如可以在JAVA中把復(fù)雜的查詢分解為幾個(gè)簡(jiǎn)單查詢，得到一個(gè)較小的結(jié)果集合，處理遍歷后，再根據(jù)條件獲取完整數(shù)據(jù)，這樣做往往更高效，因?yàn)榘褦?shù)據(jù)分離，更不容易變化，有利于數(shù)據(jù)庫(kù)緩存數(shù)據(jù)。

舉例如下：

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

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

GROUP BY、DISTINCT、ORDER BY 語(yǔ)句優(yōu)化

這些語(yǔ)句默認(rèn)都要進(jìn)行ORDER BY排序，優(yōu)化的思路比較類似。

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

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

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

舉例如下：

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

子查詢優(yōu)化

由于子查詢可讀性比較符合開發(fā)人員的思路習(xí)慣，所以都習(xí)慣編寫子查詢，但子查詢?cè)谏a(chǎn)環(huán)境中，是最常見(jiàn)的性能瓶頸。

對(duì)于數(shù)據(jù)庫(kù)來(lái)說(shuō)，大部分情況下，連接比子查詢更快，優(yōu)化器一般可以生成更佳的執(zhí)行計(jì)劃，可以余弦裝載數(shù)據(jù)，更高效的處理查詢，子查詢生成的臨時(shí)表也沒(méi)有索引，因此效率會(huì)更低。

目前的實(shí)踐來(lái)說(shuō)，子查詢應(yīng)該盡量改寫成JOIN的寫法

舉個(gè)常見(jiàn)的例子

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

我們可以轉(zhuǎn)化為連接的方式：

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

優(yōu)化IN列表

對(duì)于IN列表，MySQL會(huì)排序里面的值，并使用二分查找方式去定位數(shù)據(jù)，把IN字句改寫成OR形式其實(shí)沒(méi)什么用。IN列表不建議太長(zhǎng)，對(duì)于高并發(fā)業(yè)務(wù)，建議不超過(guò)幾十個(gè)。優(yōu)化思路可以轉(zhuǎn)化為多個(gè)等于的查詢。例如下面的語(yǔ)句，如果ID值很多，其實(shí)性能不會(huì)太好。

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

優(yōu)化思路：

可以從程序業(yè)務(wù)層出發(fā)，先查詢SELECT id FROM B，然后獲取到ID的值，逐步和 SELECT * FROM A進(jìn)行拼接，轉(zhuǎn)化為 SELECT * FROM A where ID =？的形式。

優(yōu)化UNION

UNION語(yǔ)句默認(rèn)是去除重復(fù)記錄，需要用到排序操作，如果結(jié)果集很大，成本會(huì)很高，建議盡量使用UNION ALL 語(yǔ)句，對(duì)于UNION多個(gè)分表場(chǎng)景，應(yīng)盡可能在數(shù)據(jù)庫(kù)分表的時(shí)候，就確定各個(gè)分表數(shù)據(jù)唯一性，這樣就無(wú)需使用UNION來(lái)去重了。

另外查詢語(yǔ)句外的WHERE條件并不會(huì)應(yīng)用到每個(gè)單獨(dú)的UNION子句中，所以每個(gè)UNION子句都添加where條件。

優(yōu)化BLOB、TEXT類型字段的查詢

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

優(yōu)化思路：

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

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

3、考慮使用COMPRESS（），再存儲(chǔ)。