|
由于最后 WHERE 条件以及排序均针对最左主表，因此可以先对 my_order 排序提前缩小数据量再做左连接。SQL 重写后如下，执行时间缩小为1毫秒左右。
- SELECT *
- FROM (
- SELECT *
- FROM my_order o
- WHERE ( o.display = 0 )
- AND ( o.ostaus = 1 )
- ORDER BY o.selltime DESC
- LIMIT 0, 15
- ) o
- LEFT JOIN my_userinfo u
- ON o.uid = u.uid
- LEFT JOIN my_productinfo p
- ON o.pid = p.pid
- ORDER BY o.selltime DESC
- limit 0, 15
再检查执行计划：子查询物化后（select_type=DERIVED)参与 JOIN。虽然估算行扫描仍然为90万，但是利用了索引以及 LIMIT 子句后，实际执行时间变得很小。
- +----+-------------+------------+--------+---------------+---------+---------+-------+--------+----------------------------------------------------+
- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
- +----+-------------+------------+--------+---------------+---------+---------+-------+--------+----------------------------------------------------+
- | 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 15 | Using temporary; Using filesort |
- | 1 | PRIMARY | u | eq_ref | PRIMARY | PRIMARY | 4 | o.uid | 1 | NULL |
- | 1 | PRIMARY | p | ALL | PRIMARY | NULL | NULL | NULL | 6 | Using where; Using join buffer (Block Nested Loop) |
- | 2 | DERIVED | o | index | NULL | idx_1 | 5 | NULL | 909112 | Using where |
- +----+-------------+------------+--------+---------------+---------+---------+-------+--------+----------------------------------------------------+
8、中间结果集下推 （编辑：晋中站长网）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
