php - 求這個sql怎麼優化下

Question

sql語句是：
explain SELECT bp.amount / (select count(*) from yd_order_product where order = bp.order and Product = bp.product) as amountorder

= bp.order and Product = bp。 .type from yd_batch_product as bp left join yd_order_product as op on op.order = bp.order and op.product = bp.product left join yd_batch as b on bp.batch = b.id o .id left join yd_stock as s on bp.product = s.id where o.deleted = '0' and s.forbidden = '0' and b.createdDate Between '2015-10-13 00:00:00'和'2017-04-30 23:59:59' 和s.store 在(1,2,3,4,5,6) 和s.chainID = 1

請問下這個第一行的型別值為什麼會是ALL🎜

Answer 1

@TroyLiu ，你給的建議，創建的索引太多了，建立order/product/batch/amount組合索引的思路感覺不對。

sql優化的核心思路是，對大數據的表能夠盡量根據已有的where語句中的條件對資料進行過濾，在這個例子中：
yd_batch_product表的數量量應該是最大的，但在where語句中沒有過濾條件，導致執行效率較差。

出現「Using temporary; Using filesort」的原因是，group by中的字段，不完全在第一個表yd_batch_product表字段範圍內。

建議：
1、yd_batch/yd_order/yd_stock用的是LEFT JOIN，但是在where語句中有這些表格欄位的過濾條件，因此和INNER JOIN是等價的，因此連接方式可以改成INNER JOIN，這樣mysql在判斷執行計劃的時候，就不是只能先查詢yd_batch_product 表了。
2、yd_batch表的createdDate欄位建立索引，看執行計劃中第一個驅動表是否有變化，如果createdDate的條件過濾性很強，執行計劃中第一個表應該是yd_batch。
3、yd_stock表，如何store和chainID組合後過濾性強，可以建立組合索引，和上一步同樣判斷執行計畫是否有變化。

當然，最理想的情況是從業務和資料出發，能找出yd_batch_product表上能大量過濾資料的條件，然後根據這個條件建立索引。如yd_batch表的createdDate字段在yd_batch_product表上也有類似的冗餘字段，就使用這個字段建立索引。

---

更新問題後的建議：
1、TYPE=ALL表示用的是全表掃描，雖然createdDate已經創建索引，但資料庫評估後認為使用全表掃描的成本更低，總體上認為是正常（createdDate的範圍較大，含一年多資料數，共1萬多條）。如果想用索引可以把範圍縮小的幾天內，看執行計畫是否有變化。當然也可以使用提示強制使用索引，但效率就不一定高了。
2、yd_order_product表查詢了兩次，尤其是在執行計劃中select的子查詢，應該盡量避免。

Answer 2

首先 yd_batch_product 表裡面資料量很大，而且沒有正確建立索引，導致整個查詢沒有利用到索引。
由 table bp 的對應 key 列為 NULL 可以看出。

優化參考：

1. 為 yd_batch_product 表在 order/product/batch/amount 欄位上建立聯合索引，注意聯合索引順序.(amount 也加入索引裡面，為的是使用覆蓋索引)

2. 如果 yd_batch_product 沒有其他的查詢，則考慮刪除表上所有的單列索引。

3. yd_stock 表在 forbidden/store/chainID 欄位上建立聯合索引（注意索引順序）。

4. yd_order 資料表在 deleted 欄位上建立單一欄位索引。

5. yd_batch 表在 createdDate 欄位上建立單一欄位索引。

根據目前的分析，可以做以上的最佳化。
由於沒有資料表做測試，如有錯誤，請指正。