SELECT * FROM my_order oINNER 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 oINNER JOIN my_appraise aON a.orderid = o.idAND is_reply = 0ORDER BY appraise_time DESCLIMIT 0, 20)UNION ALL(SELECT * FROM my_order oINNER JOIN my_appraise aON a.orderid = o.idAND is_reply = 1ORDER BY appraise_time DESCLIMIT 0, 20)) t ORDER BY is_reply ASC,appraisetime DESC LIMIT 20; 5、EXISTS语句
MySQL 对待 EXISTS 子句时，仍然采用嵌套子查询的执行方式 。如下面的 SQL 语句：
SELECT *FROM my_neighbor nLEFT JOIN my_neighbor_apply sraON n.id = sra.neighbor_idAND sra.user_id = 'xxx' WHERE n.topic_status < 4AND EXISTS(SELECT 1FROM message_info mWHERE n.id = m.neighbor_idAND m.inuser = 'xxx')AND n.topic_type <> 5 执行计划为：
+----+--------------------+-------+------+-----+------------------------------------------+---------+-------+---------+ -----+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+--------------------+-------+------+ -----+------------------------------------------+---------+-------+---------+ -----+| 1 | PRIMARY | n | ALL | | NULL | NULL | NULL | 1086041 | Using where || 1 | PRIMARY | sra | ref | | idx_user_id | 123 | const | 1 | Using where || 2 | DEPENDENT SUBQUERY | m | ref | | idx_message_info | 122 | const | 1 | Using index condition; Using where |+----+--------------------+-------+------+ -----+------------------------------------------+---------+-------+---------+ -----+去掉 exists 更改为 join，能够避免嵌套子查询，将执行时间从1.93秒降低为1毫秒 。
SELECT *FROM my_neighbor nINNER JOIN message_info mON n.id = m.neighbor_idAND m.inuser = 'xxx'LEFT JOIN my_neighbor_apply sraON n.id = sra.neighbor_idAND sra.user_id = 'xxx' WHERE n.topic_status < 4AND n.topic_type <> 5 新的执行计划：
+----+-------------+-------+--------+ -----+------------------------------------------+---------+ -----+------+ -----+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+--------+ -----+------------------------------------------+---------+ -----+------+ -----+| 1 | SIMPLE | m | ref | | idx_message_info | 122 | const | 1 | Using index condition || 1 | SIMPLE | n | eq_ref | | PRIMARY | 122 | ighbor_id | 1 | Using where || 1 | SIMPLE | sra | ref | | idx_user_id | 123 | const | 1 | Using where |+----+-------------+-------+--------+ -----+------------------------------------------+---------+ -----+------+ -----+6、条件下推
外部查询条件不能够下推到复杂的视图或子查询的情况有：
1、聚合子查询；2、含有 LIMIT 的子查询；3、UNION 或 UNION ALL 子查询；4、输出字段中的子查询；
如下面的语句，从执行计划可以看出其条件作用于聚合子查询之后：
SELECT * FROM (SELECT target,Count(*)FROM operationGROUP BY target) t WHERE target = 'rm-xxxx' +----+-------------+------------+-------+---------------+-------------+---------+-------+------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+------------+-------+---------------+-------------+---------+-------+------+-------------+| 1 | PRIMARY | <derived2> | ref | <auto_key0> | <auto_key0> | 514 | const | 2 | Using where || 2 | DERIVED | operation | index | idx_4 | idx_4 | 519 | NULL | 20 | Using index |+----+-------------+------------+-------+---------------+-------------+---------+-------+------+-------------+【这些常被忽视的SQL错误用法，你知道吗】确定从语义上查询条件可以直接下推后，重写如下：
SELECT target,Count(*) FROM operation WHERE target = 'rm-xxxx' GROUP BY target执行计划变为：
+----+-------------+-----------+------+---------------+-------+---------+-------+------+--------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-----------+------+---------------+-------+---------+-------+------+--------------------+| 1 | SIMPLE | operation | ref | idx_4 | idx_4 | 514 | const | 1 | Using where; Using index |+----+-------------+-----------+------+---------------+-------+---------+-------+------+--------------------+
推荐阅读
- 正山小种红茶居然还有这些好处？
- 二十四节气之大雪养生 这些养生原则需谨记
- 大雪节气如何养生？这些原则要牢记
- 冬季养生 千万别做这些事儿！
- 坚持饭后百步走 这些项目非常适合冬季运动
- 秋季皮肤过敏可能是这些原因？
- 三伏天吃什么 常吃这些轻松度过三伏天
- 夏季女人养生必知这些小常识
- 女人夏季嘴唇爱发干 试试这些小偏方
- 颈椎疾病爱在春季复发 这些最佳治疗法必不可错过