后台查询语句SELECTo.orders_id,s.orders_status_name,ot.text,af.affiliate_idFROMordersoLEFTJOINorders_totalotON(o.orders_id=ot.orders_id)LEFTJOINaffilia
后台查询语句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。
本文原创发布php中文网,转载请注明出处,感谢您的尊重!