|
执行计划简化为：
- +----+-------------+-------+------+---------------+-------+---------+-------+------+-----------------------------------------------------+
- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
- +----+-------------+-------+------+---------------+-------+---------+-------+------+-----------------------------------------------------+
- | 1 | PRIMARY | | | | | | | | Impossible WHERE noticed after reading const tables |
- | 2 | DERIVED | o | ref | idx_2,idx_5 | idx_5 | 8 | const | 1 | Using where; Using filesort |
- +----+-------------+-------+------+---------------+-------+---------+-------+------+-----------------------------------------------------+
4、混合排序
MySQL 不能利用索引进行混合排序。但在某些场景，还是有机会使用特殊方法提升性能的。
- SELECT *
- FROM my_order o
- INNER JOIN my_appraise a ON a.orderid = o.id
- ORDER BY a.is_reply ASC,
- a.appraise_time DESC
- LIMIT 0, 20
执行计划显示为全表扫描：
- +----+-------------+-------+--------+-------------+---------+---------+---------------+---------+-+
- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra
- +----+-------------+-------+--------+-------------+---------+---------+---------------+---------+-+
- | 1 | SIMPLE | a | ALL | idx_orderid | NULL | NULL | NULL | 1967647 | Using filesort |
- | 1 | SIMPLE | o | eq_ref | PRIMARY | PRIMARY | 122 | a.orderid | 1 | NULL |
- +----+-------------+-------+--------+---------+---------+---------+-----------------+---------+-+
由于 is_reply 只有0和1两种状态，我们按照下面的方法重写后，执行时间从1.58秒降低到2毫秒。
- SELECT *
- FROM ((SELECT *
- FROM my_order o
- INNER JOIN my_appraise a
- ON a.orderid = o.id
- AND is_reply = 0
- ORDER BY appraise_time DESC
- LIMIT 0, 20)
- UNION ALL
- (SELECT *
- FROM my_order o
- INNER JOIN my_appraise a
- ON a.orderid = o.id
- AND is_reply = 1
- ORDER BY appraise_time DESC
- LIMIT 0, 20)) t
- ORDER BY is_reply ASC,
- appraisetime DESC
- LIMIT 20;
5、EXISTS语句 （编辑：厦门网）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
