无合适where条件过滤时尽量选择order by后的字段以驱动表进行查询
后台查询语句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 JOINorders_status sON 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 JOINorders_status sON 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使用该条件！
页:
[1]