後臺查詢語句
SELECT o.orders_id, s.orders_status_name, ot.text ,af.affiliate_id FROM orders o LEFT JOIN orders_total ot ON (o.orders_id = ot.orders_id) LEFT JOIN affiliate_sales AS afs ON afs.affiliate_orders_id = o.orders_id LEFT JOIN affiliate_affiliate AS af ON af.affiliate_id = afs.affiliate_id LEFT JOIN orders_status s ON o.orders_status = s.orders_status_id WHERE s.language_id = `1` AND (ot.class = `ot_total` OR ot.orders_total_id IS NULL) ORDER BY o.orders_id DESC LIMIT 0, 20
有客戶反應某後臺查詢非常慢,通過程式找到對應的sql，如上！
explain發現
+----+-------------+-------+--------+----------------------------+----------------------------+---------+-----------------------------+-------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+----------------------------+----------------------------+---------+-----------------------------+-------+----------------------------------------------+ | 1 | SIMPLE | s | ALL | PRIMARY | NULL | NULL | NULL | 21 | Using where; Using temporary; Using filesort | | 1 | SIMPLE | o | ref | orders_status | orders_status | 4 | banggood.s.orders_status_id | 31747 | | | 1 | SIMPLE | ot | ref | idx_orders_total_orders_id | idx_orders_total_orders_id | 4 | banggood.o.orders_id | 19 | Using where | | 1 | SIMPLE | afs | ref | PRIMARY | PRIMARY | 4 | banggood.o.orders_id | 11 | Using index | | 1 | SIMPLE | af | eq_ref | PRIMARY | PRIMARY | 4 | banggood.afs.affiliate_id | 1 | Using index | +----+-------------+-------+--------+----------------------------+----------------------------+---------+-----------------------------+-------+----------------------------------------------+
s表被作為驅動表，s表為全表掃描,o表使用了status型別的可選擇性非常低的欄位作為索引。
初步一看就知道索引使用不恰當！
我們可以看到這條語句where條件中，沒有什麼合適的可驅動條件；但是，在order by中，發現order by o.orders_id(orders_id為orders表的主鍵)。我們就可以利用這個特性！
強制使用orders表的orders_id索引進行驅動！
更改如下：
EXPLAIN SELECT o.orders_id, s.orders_status_name, ot.text ,af.affiliate_id FROM orders o FORCE INDEX(PRIMARY) LEFT JOIN orders_total ot ON (o.orders_id = ot.orders_id) LEFT JOIN affiliate_sales AS afs ON afs.affiliate_orders_id = o.orders_id LEFT JOIN affiliate_affiliate AS af ON af.affiliate_id = afs.affiliate_id LEFT JOIN orders_status s ON o.orders_status = s.orders_status_id WHERE s.language_id = `1` AND (ot.class = `ot_total` OR ot.orders_total_id IS NULL) ORDER BY o.orders_id DESC LIMIT 0, 20; +----+-------------+-------+--------+----------------------------+----------------------------+---------+--------------------------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+----------------------------+----------------------------+---------+--------------------------------+------+-------------+ | 1 | SIMPLE | o | index | NULL | PRIMARY | 4 | NULL | 1 | | | 1 | SIMPLE | s | eq_ref | PRIMARY | PRIMARY | 8 | banggood.o.orders_status,const | 1 | Using where | | 1 | SIMPLE | ot | ref | idx_orders_total_orders_id | idx_orders_total_orders_id | 4 | banggood.o.orders_id | 19 | Using where | | 1 | SIMPLE | afs | ref | PRIMARY | PRIMARY | 4 | banggood.o.orders_id | 11 | Using index | | 1 | SIMPLE | af | eq_ref | PRIMARY | PRIMARY | 4 | banggood.afs.affiliate_id | 1 | Using index | +----+-------------+-------+--------+----------------------------+----------------------------+---------+--------------------------------+------+-------------+
對比兩次profiling；
前者：
+--------------------------------+------------+-----------+------------+--------------+---------------+ | Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out | +--------------------------------+------------+-----------+------------+--------------+---------------+ | starting | 0.000027 | 0.000000 | 0.000000 | 0 | 0 | | Waiting for query cache lock | 0.000006 | 0.000000 | 0.000000 | 0 | 0 | | checking query cache for query | 0.000130 | 0.000000 | 0.000000 | 0 | 0 | | checking permissions | 0.000007 | 0.000000 | 0.000000 | 0 | 0 | | checking permissions | 0.000003 | 0.000000 | 0.000000 | 0 | 0 | | checking permissions | 0.000003 | 0.000000 | 0.000000 | 0 | 0 | | checking permissions | 0.000003 | 0.000000 | 0.000000 | 0 | 0 | | checking permissions | 0.000007 | 0.000000 | 0.000000 | 0 | 0 | | Opening tables | 0.000130 | 0.000000 | 0.000000 | 0 | 8 | | System lock | 0.000017 | 0.000000 | 0.000000 | 0 | 0 | | Waiting for query cache lock | 0.000033 | 0.000000 | 0.000000 | 0 | 0 | | init | 0.000057 | 0.000000 | 0.000000 | 0 | 0 | | optimizing | 0.000026 | 0.000000 | 0.000000 | 0 | 0 | | statistics | 0.000041 | 0.000000 | 0.000000 | 0 | 0 | | preparing | 0.000031 | 0.000000 | 0.000000 | 0 | 0 | | Creating tmp table | 0.000111 | 0.001000 | 0.000000 | 0 | 0 | | executing | 0.000007 | 0.000000 | 0.000000 | 0 | 0 | | Copying to tmp table | 3.541123 | 0.968852 | 2.357642 | 75800 | 0 | | converting HEAP to MyISAM | 0.239566 | 0.038994 | 0.198969 | 0 | 262152 | | Copying to tmp table on disk | 174.185144 | 13.864893 | 35.361625 | 2135152 | 2500280 | | Sorting result | 20.923419 | 0.127980 | 3.017541 | 2770408 | 27536 | | Sending data | 0.045078 | 0.000000 | 0.002999 | 1208 | 0 | | end | 0.000018 | 0.000000 | 0.000000 | 0 | 0 | | removing tmp table | 0.881884 | 0.018997 | 0.160976 | 760 | 8 | | end | 0.003960 | 0.000000 | 0.002000 | 448 | 0 | | query end | 0.000012 | 0.000000 | 0.000000 | 0 | 0 | | closing tables | 0.031745 | 0.000000 | 0.000999 | 936 | 0 | | freeing items | 0.015499 | 0.000000 | 0.003000 | 808 | 0 | | Waiting for query cache lock | 0.000017 | 0.000000 | 0.000000 | 0 | 0 | | freeing items | 0.000791 | 0.000000 | 0.000000 | 0 | 0 | | Waiting for query cache lock | 0.000009 | 0.000000 | 0.000000 | 0 | 0 | | freeing items | 0.000003 | 0.000000 | 0.000000 | 0 | 0 | | storing result in query cache | 0.000009 | 0.000000 | 0.000000 | 0 | 0 | | logging slow query | 0.000003 | 0.000000 | 0.000000 | 0 | 0 | | logging slow query | 0.000010 | 0.000000 | 0.000000 | 0 | 0 | | cleaning up | 0.000007 | 0.000000 | 0.000000 | 0 | 0 | +--------------------------------+------------+-----------+------------+--------------+---------------+
各種cpu，io損耗，慘不忍睹！其中最大的消耗是Copying to tmp table on disk。
優化後的profiling
+----------------------+----------+----------+------------+--------------+---------------+ | Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out | +----------------------+----------+----------+------------+--------------+---------------+ | starting | 0.000139 | 0.000000 | 0.000000 | 0 | 0 | | checking permissions | 0.000008 | 0.000000 | 0.000000 | 0 | 0 | | checking permissions | 0.000003 | 0.000000 | 0.000000 | 0 | 0 | | checking permissions | 0.000003 | 0.000000 | 0.000000 | 0 | 0 | | checking permissions | 0.000003 | 0.000000 | 0.000000 | 0 | 0 | | checking permissions | 0.000007 | 0.000000 | 0.000000 | 0 | 0 | | Opening tables | 0.000125 | 0.000000 | 0.000000 | 0 | 8 | | System lock | 0.000018 | 0.000000 | 0.000000 | 0 | 0 | | init | 0.000057 | 0.000000 | 0.000000 | 0 | 0 | | optimizing | 0.000026 | 0.000000 | 0.000000 | 0 | 0 | | statistics | 0.000043 | 0.000000 | 0.000000 | 0 | 0 | | preparing | 0.000030 | 0.000000 | 0.000000 | 0 | 0 | | executing | 0.000005 | 0.000000 | 0.000000 | 0 | 0 | | Sorting result | 0.000006 | 0.000000 | 0.000000 | 0 | 0 | | Sending data | 0.000573 | 0.000000 | 0.000000 | 0 | 0 | | end | 0.000008 | 0.000000 | 0.000000 | 0 | 0 | | query end | 0.000004 | 0.000000 | 0.000000 | 0 | 0 | | closing tables | 0.000014 | 0.000000 | 0.000000 | 0 | 0 | | freeing items | 0.000062 | 0.000000 | 0.000000 | 0 | 0 | | logging slow query | 0.000005 | 0.000000 | 0.000000 | 0 | 0 | | cleaning up | 0.000007 | 0.000000 | 0.000000 | 0 | 0 | +----------------------+----------+----------+------------+--------------+---------------+
相對於前者來說，後者小號可以忽略不計！
前後兩者執行時間之比是196s比0.01s！
總結：
如果碰到where中沒有適當條件來篩選資料的時候，看到order by中有比較好的條件，我們第一個就要想到用order by中的條件驅動查詢！
但是mysql優化器並沒有使用到該條件，可以強制force index使用該條件！