Mysql第七天查詢優化2

接上文，其他的一些Mysql對於查詢的優化，或者Mysql的不足，我們應該注意怎樣優化。

關聯子查詢

Mysql的子查詢實現比較有問題， 特別是對於IN(子查詢)，這樣的方式。
比如:查詢一個訂單表中，所有支付方式為4也就是京東網銀錢包的訂單：

EXPLAIN SELECT * FROM virtual_order vo WHERE vo.jd_order_id IN (SELECT jd_order_id FROM biz_pay_task p WHERE p.pay_type=4);

// 執行結果：
+—-+——————–+——-+—————–+—————+————-+———+——+——+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+——————–+——-+—————–+—————+————-+———+——+——+————-+
| 1 | PRIMARY | vo | ALL | NULL | NULL | NULL | NULL | 50 | Using where |
| 2 | DEPENDENT SUBQUERY | p | unique_subquery | jd_order_id | jd_order_id | 8 | func | 1 | Using where |
+—-+——————–+——-+—————–+—————+————-+———+——+——+————-+
實際上，上面的sql會被轉化為：

 SELECT * FROM virtual_order vo WHERE EXISTS (SELECT jd_order_id FROM biz_pay_task p WHERE p.pay_type=4 AND vo.jd_order_id=p.jd_order_id);

這意味著，先對vo進行了全表掃描，然後再對每一個jd_order_id去執行子查詢，看是否返回true
EXISTS關鍵字決定了 其子查詢是否能夠有返回結果。

這種情況下，我們期望怎樣執行呢？當然是先執行子查詢，有結果了之後利用IN的二分查詢來加快檢索速度。
這樣的話，我們就可以使用GROUP_CONCAT()來手工拼一個IN裡面的內容，
或者改寫為如下的內連線：

SELECT * FROM virtual_order vo INNER JOIN biz_pay_task p USING(jd_order_id) WHERE p.pay_type=4 ;

但是關聯子查詢並不是一定比左連，內聯等慢的。
比如NOT EXISTS的情況下，會有終止返回的優化，還是有可能變得比較快。 針對於這種，最好的方式就是進行測試，得到實際的執行結果，來進行判斷。

UNION ALL的限制

(SQL1) UNION ALL (SQL2) LIMIT 20;
這種情況會把 SQL1和SQL2中的所有記錄放到臨時表中然後去LIMIT20.比較好的做法是。分別LIMIT 如下：
(SQL1 LIMIT 20) UNION ALL (SQL2 LIMIT 20) LIMIT 20;

等值傳遞

當遇到ON, USING的時候會誘發等值傳遞的優化，但是如果要傳遞的是IN()，或者其他比較複雜的等值時，有可能傳遞給內層之後影響內層的效能。

不能並行執行

沒有雜湊關聯

可以使用自建雜湊欄位加索引

鬆散索引掃描

也就是跳著掃描。比如有一個聚合索引(a, b)如果WHERE中只有b的條件，那麼因為用不到這個索引，就會變成全表掃描。 我們期望的是先從a的索引開始掃描，然後對於a的沒一個索引鍵中因為b是有序的，因此這個時候又可以對於每個鍵中的b的區間範圍進行索引掃描。比如如下的結構：
|a|b|
|1|1|
|1|2|
|1|3|
|1|4|
|2|1|
|2|2|
|2|3|
|2|4|
WHERE b between 2 and 3 掃描方式變為a1-b2-b3-a2-b2-b3，這樣跳著掃描可以減少掃描行數，大幅度增加效能。
Mysql的主要應用掉在於GROUP BY 的語句中。Extra欄位顯示”Using index for group-by”
這裡有一個更好的說明： 請點選

最大值和最小值優化

SELECT MIN(id) from user WHERE name=”wzj”;
這裡會做全表掃描。 但是如果是從主鍵開始掃描的話，可以快很多。Mysql自己不能完成這種行為，但是我們可以手工的完成它，比如使用如下的語句：
SELECT id FROM user USE INDEX(PRIMARY) WHERE name=”wzj” LIMIT 1;

查詢優化器的提示 hint

主要是認為的控制一下Mysql的優化過程，列舉一些，更具體的需要看Mysql的官方文件了。

• DELAYED 插入資料放入緩衝區，提高對客戶端的響應
• STRAIGHT_JOIN 這個之前使用過，可以強制指定驅動表
• SQL_SMALL_RESULT 和SQL_BIG_RESULT 告訴優化器對於SELECT GROUPBY這種的返回集的大概大小，如果小則用記憶體而不用臨時表
• SQL_CACHE 告訴優化器這個結果存在快取中
• USE INDEX 這個告訴優化器使用或者不適用哪些索引
• 還有一些配置項，之前也已經講過了，有深度太多跳過選擇執行計劃的等等。
  另外隨著版本的更新可能會引起不一樣的行為，因此最好每次升級都進行測試。可以使用Percona Tookit 的pt-upgrade工具來檢查結果。

優化特定型別的查詢

優化COUNT()

• COUNT(fieldname) 統計欄位中不為NULL的值
• COUNT(*) 統計所有行，這個的效能要好一些
• MYISAM之所以COUNT(*)快，是因為實時更新了總行數，但是對於加了WHERE的就不快了。
• 利用MYISAM很快的特性，對於WHERE b>5,可以使用COUNT(*) – WHERE b < 5的特性，這樣可以加快一些效能。
• 使用估算值，在特定場景下，可以使用去除DISTINCT，或者是WHERE後面對於結果集影響很小的條件，只求出估算值
• 更復雜的方式可以使用匯總表，索引覆蓋，Memcached這樣的外部快取。快速，精確和簡單，最多隻能滿足其二，所以優化肯定會有一個限度

優化關聯查詢

• 卻表ON和USING子句的列上有索引， 建立索引時考慮好關聯的順序，只需要在驅動表上建立索引
• 確保任何GROUP BY和ORDER BY 表示式只涉及一個表的列，這樣才能使用索引
• 注意不要使用全連，用內聯和外聯

優化子查詢

儘量用關聯代替
Mysql5.6中已經自帶了這種優化，因此可以不是很關注了

優化LIMIT分頁

• 利用覆蓋索引，先查出有索引的列，然後使用連線操作
• 再有是先記錄下當前查詢到的最後的位置，然後再下一次查詢是上WHERE子句過濾掉這個位置之前的值。比如SELECT * FROM user ORDER BY id DESC LIMIT 20;
  這個時候得到最小的id是10000，那麼下面就可以使用SELECT * FROM user WHERE id < 10000 ORDER BY id DESC LIMIT 20;來得到下20行，這樣子來提升效率。
• 其他的方法，還可以使用冗餘表，只包含主鍵和需要排序的資料列。

分頁優化

• SQL_CALC_FOUND_ROWS 這個hint並不能真正的提高效率，其還是要統計所有的行的數量
• 使用EXPLAIN語句中的rows結果來做估算
• 讀取21條，看是否有第21條來決定是否有下一頁
• 讀取1000條，其他的資料使用更多按鈕來展示。

UNION優化

• 把優化的語句放到各個子查詢中

• 確定只有必須要去除重複行的時候採用UNION,否則使用UNION ALL，免得對所有結果進行DISTINCT

  靜態查詢分析

  Percona 的pt-query-advisor

例子

構建佇列表

mysql並不是最好的選擇，一般會有如下兩個問題要解決：

• 增長。 這種可以使用歸檔的方式
• 併發。 即有可能多個使用者操作同一個任務，為了避免種種情況，可以加鎖，但是加鎖效率比較低。
  對於增長可以歸檔，可以分片。
  對於併發，我們可以使用樂觀鎖
  或者使用如下的優化，我們一般基本的做法可以是這個樣子的：給任務表增加一個woner欄位。 然後執行任務的時候：

BEGIN;
SELECT id FROM task WHERE owner = 0 AND status=0 LIMIT 10 FOR UPDATE;
-- result 1,2
// 程式碼中dosomething.
UPDATE task SET status=1 , owner = CONNECTON_ID() WHERE id IN (--result);
COMMIT;

這樣會造成使用相同索引的相同查詢有鎖定， 可以使用如下的方式代替：

BEGTIN;
UPDATE task set status=1 , owner=CONNECTION_ID() WHERE owner = 0 AND status=0 LIMIT 10;
COMMIT;
SET AUTOCOMMIT = 0;
SELECT id FROM task where owner = CONNECTION_ID() AND status = 1;
// do something

這種可以大幅的減少鎖的時間，但是後續的處理可能會比較多，比如如果執行具體的事情失敗，則還需要回寫內容。
上面的這種也稍顯複雜，最好還是使用悲觀鎖。

或者是直接使用redis，是一個更好的佇列容器。

查詢兩點之間的距離

這個主要是需要注意，什麼情況下使用索引。顯然轉化為矩形的情況下會使用索引。 使用矩形得出曉得資料集之後再進行精確計算。