|
去掉 exists 更改为 join，能够避免嵌套子查询，将执行时间从1.93秒降低为1毫秒。
- SELECT *
- FROM my_neighbor n
- INNER JOIN message_info m
- ON n.id = m.neighbor_id
- AND m.inuser = 'xxx'
- LEFT JOIN my_neighbor_apply sra
- ON n.id = sra.neighbor_id
- AND sra.user_id = 'xxx'
- WHERE n.topic_status < 4
- AND 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、条件下推
外部查询条件不能够下推到复杂的视图或子查询的情况有：
- 聚合子查询；
- 含有 LIMIT 的子查询；
- UNION 或 UNION ALL 子查询；
- 输出字段中的子查询；
如下面的语句，从执行计划可以看出其条件作用于聚合子查询之后：
- SELECT *
- FROM (SELECT target,
- Count(*)
- FROM operation
- GROUP 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 |
- +----+-------------+------------+-------+---------------+-------------+---------+-------+------+-------------+
（编辑：南平站长网）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
