表關聯查詢不等於最佳化一案例

原SQL如下：

SELECT            o.order_id            FROM orders o            JOIN shipping_orders so ON so.order_id = o.order_id            JOIN shippings s ON s.shipping_no = so.shipping_no            WHERE o.payment_method = 'COD'AND o.status IN(3, 4, 6, 11, 12)AND s.logistic_track_no <> ''AND o.delivery_time < unix_timestamp('2013-09-01')AND o.order_id NOT IN (SELECT order_id FROM cod_order_extinfos);

shipping_orders表和shippings表做關聯，取出不等於空的記錄，由於s.logistic_track_no <> ''不等於無法使用到索引，會全表掃描，所以最佳化的目的是先取出shippings表為空白的記錄，這樣結果集會很小，再進行join串連時會快得多。

改寫如下：

SELECT            o.order_id            FROM orders o            JOIN shipping_orders so ON so.order_id = o.order_id            LEFT JOIN (select shipping_no,logistic_track_no from shippings where logistic_track_no = '') s            ON s.shipping_no = so.shipping_no            WHERE o.payment_method = 'COD'AND o.status IN(3, 4, 6, 11, 12)AND s.logistic_track_no is nullAND o.delivery_time < unix_timestamp('2010-12-31')AND  NOT EXISTS ( SELECT order_id FROM cod_order_extinfos coe where coe.order_id=o.order_id);

最佳化前執行時間：650) this.width=650;" src="http://www.bkjia.com/uploads/allimg/131229/2059341318-0.jpg" title="最佳化前.jpg" alt="161750987.jpg" />

最佳化後執行時間：

650) this.width=650;" src="http://www.bkjia.com/uploads/allimg/131229/2059346495-1.jpg" title="最佳化後.jpg" alt="161828823.jpg" />

本文出自 “賀春暘的技術專欄” 部落格，請務必保留此出處http://hcymysql.blog.51cto.com/5223301/1300162