使用duplicateweedout
mysql> explain extended select * from cities where id in (select phonecode from countries group by name); +----+-------------+-----------+--------+---------------+---------+---------+--------------------------+------+----------+-----------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+--------+---------------+---------+---------+--------------------------+------+----------+-----------------+ | 1 | SIMPLE | countries | ALL | NULL | NULL | NULL | NULL | 246 | 100.00 | Start temporary | | 1 | SIMPLE | cities | eq_ref | PRIMARY | PRIMARY | 4 | test.countries.phonecode | 1 | 100.00 | End temporary | +----+-------------+-----------+--------+---------------+---------+---------+--------------------------+------+----------+-----------------+ 2 rows in set, 1 warning (5.65 sec)
QUERY: explain extended select * from cities where id in (select phonecode from countries group by name) TRACE: { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "join_preparation": { "select#": 2, "steps": [ { "transformations_to_subquery": [ "removed_grouping" ] }, { "expanded_query": "/* select#2 */ select `countries`.`phonecode` from `countries`" }, { "transformation": { "select#": 2, "from": "IN (SELECT)", "to": "semijoin", "chosen": true } } ] } }, { "expanded_query": "/* select#1 */ select `cities`.`id` AS `id`,`cities`.`name` AS `name`,`cities`.`state_id` AS `state_id` from `cities` where `cities`.`id` in (/* select#2 */ select `countries`.`phonecode` from `countries`)" } ] } }, { "join_optimization": { "select#": 1, "steps": [ { "transformation": { "select#": 2, "from": "IN (SELECT)", "to": "semijoin", "chosen": true, "evaluating_constant_semijoin_conditions": [ ] } }, { "transformations_to_nested_joins": { "transformations": [ "semijoin" ], "expanded_query": "/* select#1 */ select `cities`.`id` AS `id`,`cities`.`name` AS `name`,`cities`.`state_id` AS `state_id` from `cities` semi join (`countries`) where (1 and (`cities`.`id` = `countries`.`phonecode`))" } }, { "condition_processing": { "condition": "WHERE", "original_condition": "(1 and (`cities`.`id` = `countries`.`phonecode`))", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "(1 and multiple equal(`cities`.`id`, `countries`.`phonecode`))" }, { "transformation": "constant_propagation", "resulting_condition": "(1 and multiple equal(`cities`.`id`, `countries`.`phonecode`))" }, { "transformation": "trivial_condition_removal", "resulting_condition": "multiple equal(`cities`.`id`, `countries`.`phonecode`)" } ] } }, { "table_dependencies": [ { "table": "`cities`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] }, { "table": "`countries`", "row_may_be_null": false, "map_bit": 1, "depends_on_map_bits": [ ] } ] }, { "ref_optimizer_key_uses": [ { "table": "`cities`", "field": "id", "equals": "`countries`.`phonecode`", "null_rejecting": false } ] }, { "pulled_out_semijoin_tables": [ ] }, { "rows_estimation": [ { "table": "`cities`", "table_scan": { "rows": 47478, "cost": 161 } }, { "table": "`countries`", "table_scan": { "rows": 246, "cost": 1 } } ] }, { "execution_plan_for_potential_materialization": { "steps": [ { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`countries`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 246, "cost": 50.2, "chosen": true } ] }, "cost_for_plan": 50.2, "rows_for_plan": 246, "chosen": true } ] } ] } }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`countries`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 246, "cost": 50.2, "chosen": true } ] }, "cost_for_plan": 50.2, "rows_for_plan": 246, "semijoin_strategy_choice": [ { "strategy": "MaterializeScan", "choice": "deferred" } ], "rest_of_plan": [ { "plan_prefix": [ "`countries`" ], "table": "`cities`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "PRIMARY", "rows": 1, "cost": 246.2, "chosen": true }, { "access_type": "scan", "using_join_cache": true, "rows": 35609, "cost": 1.75e6, "chosen": false } ] }, "cost_for_plan": 345.4, "rows_for_plan": 246, "semijoin_strategy_choice": [ { "strategy": "MaterializeScan", "recalculate_access_paths_and_cost": { "tables": [ { "table": "`cities`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "PRIMARY", "rows": 1, "cost": 246.2, "chosen": true }, { "access_type": "scan", "using_join_cache": true, "rows": 35609, "cost": 1.75e6, "chosen": false } ] } } ] }, "cost": 445.8, "rows": 1, "duplicate_tables_left": true, "chosen": true }, { "strategy": "DuplicatesWeedout", "cost": 396.8, "rows": 1, "duplicate_tables_left": false, "chosen": true } ], "chosen": true } ] }, { "plan_prefix": [ ], "table": "`cities`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "PRIMARY", "usable": false, "chosen": false }, { "access_type": "scan", "rows": 47478, "cost": 9656.6, "chosen": true } ] }, "cost_for_plan": 9656.6, "rows_for_plan": 47478, "semijoin_strategy_choice": [ ], "pruned_by_cost": true }, { "final_semijoin_strategy": "DuplicateWeedout" } ] }, { "attaching_conditions_to_tables": { "original_condition": "(`cities`.`id` = `countries`.`phonecode`)", "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`countries`", "attached": null }, { "table": "`cities`", "attached": null } ] } }, { "refine_plan": [ { "creating_tmp_table": { "tmp_table_info": { "row_length": 6, "key_length": 4, "unique_constraint": false, "location": "memory (heap)", "row_limit_estimate": 2796202 } } }, { "table": "`countries`", "access_type": "table_scan" }, { "table": "`cities`" } ] } ] } }, { "join_explain": { "select#": 1, "steps": [ ] } } ] }
MySQL限时解答,24小时内友哥专业解答
http://www.yougemysqldba.com
如有进一步需要请联系微信onesoft007
微博账号@友哥一指
http://www.yougemysqldba.com
如有进一步需要请联系微信onesoft007
微博账号@友哥一指