使用materialization
explain select `countries`.`id` AS `id`,`countries`.`sortname` AS `sortname`,`countries`.`name` AS `name`,`countries`.`phonecode` AS `phonecode` from `countries` where `countries`.`id` in (/* select#2 */ select `states`.`country_id` from `states`);
+----+--------------+-------------+--------+---------------+------------+---------+-------------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------+-------------+--------+---------------+------------+---------+-------------------+------+-------------+ | 1 | SIMPLE | countries | ALL | PRIMARY | NULL | NULL | NULL | 246 | Using where | | 1 | SIMPLE | <subquery2> | eq_ref | <auto_key> | <auto_key> | 4 | test.countries.id | 1 | NULL | | 2 | MATERIALIZED | states | ALL | NULL | NULL | NULL | NULL | 4120 | NULL | +----+--------------+-------------+--------+---------------+------------+---------+-------------------+------+-------------+
QUERY: explain select `countries`.`id` AS `id`,`countries`.`sortname` AS `sortname`,`countries`.`name` AS `name`,`countries`.`phonecode` AS `phonecode` from `countries` where `countries`.`id` in ( select `states`.`country_id` from `states`) TRACE: { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "join_preparation": { "select#": 2, "steps": [ { "expanded_query": "/* select#2 */ select `states`.`country_id` from `states`" }, { "transformation": { "select#": 2, "from": "IN (SELECT)", "to": "semijoin", "chosen": true } } ] } }, { "expanded_query": "/* select#1 */ select `countries`.`id` AS `id`,`countries`.`sortname` AS `sortname`,`countries`.`name` AS `name`,`countries`.`phonecode` AS `phonecode` from `countries` where `countries`.`id` in (/* select#2 */ select `states`.`country_id` from `states`)" } ] } }, { "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 `countries`.`id` AS `id`,`countries`.`sortname` AS `sortname`,`countries`.`name` AS `name`,`countries`.`phonecode` AS `phonecode` from `countries` semi join (`states`) where (1 and (`countries`.`id` = `states`.`country_id`))" } }, { "condition_processing": { "condition": "WHERE", "original_condition": "(1 and (`countries`.`id` = `states`.`country_id`))", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "(1 and multiple equal(`countries`.`id`, `states`.`country_id`))" }, { "transformation": "constant_propagation", "resulting_condition": "(1 and multiple equal(`countries`.`id`, `states`.`country_id`))" }, { "transformation": "trivial_condition_removal", "resulting_condition": "multiple equal(`countries`.`id`, `states`.`country_id`)" } ] } }, { "table_dependencies": [ { "table": "`countries`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] }, { "table": "`states`", "row_may_be_null": false, "map_bit": 1, "depends_on_map_bits": [ ] } ] }, { "ref_optimizer_key_uses": [ { "table": "`countries`", "field": "id", "equals": "`states`.`country_id`", "null_rejecting": false } ] }, { "pulled_out_semijoin_tables": [ ] }, { "rows_estimation": [ { "table": "`countries`", "table_scan": { "rows": 246, "cost": 1 } }, { "table": "`states`", "table_scan": { "rows": 4120, "cost": 12 } } ] }, { "execution_plan_for_potential_materialization": { "steps": [ { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`states`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 4120, "cost": 836, "chosen": true } ] }, "cost_for_plan": 836, "rows_for_plan": 4120, "chosen": true } ] } ] } }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`states`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 4120, "cost": 836, "chosen": true } ] }, "cost_for_plan": 836, "rows_for_plan": 4120, "semijoin_strategy_choice": [ { "strategy": "MaterializeScan", "choice": "deferred" } ], "rest_of_plan": [ { "plan_prefix": [ "`states`" ], "table": "`countries`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "PRIMARY", "rows": 1, "cost": 4120.2, "chosen": true }, { "access_type": "scan", "using_join_cache": true, "rows": 185, "cost": 152453, "chosen": false } ] }, "cost_for_plan": 5780, "rows_for_plan": 4120, "semijoin_strategy_choice": [ { "strategy": "MaterializeScan", "recalculate_access_paths_and_cost": { "tables": [ { "table": "`countries`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "PRIMARY", "rows": 1, "cost": 4120.2, "chosen": true }, { "access_type": "scan", "using_join_cache": true, "rows": 185, "cost": 152453, "chosen": false } ] } } ] }, "cost": 7430, "rows": 1, "duplicate_tables_left": true, "chosen": true }, { "strategy": "DuplicatesWeedout", "cost": 6606.2, "rows": 1, "duplicate_tables_left": false, "chosen": true } ], "chosen": true } ] }, { "plan_prefix": [ ], "table": "`countries`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "PRIMARY", "usable": false, "chosen": false }, { "access_type": "scan", "rows": 246, "cost": 50.2, "chosen": true } ] }, "cost_for_plan": 50.2, "rows_for_plan": 246, "semijoin_strategy_choice": [ ], "rest_of_plan": [ { "plan_prefix": [ "`countries`" ], "table": "`states`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "using_join_cache": true, "rows": 4120, "cost": 202721, "chosen": true } ] }, "cost_for_plan": 202771, "rows_for_plan": 1.01e6, "semijoin_strategy_choice": [ { "strategy": "FirstMatch", "recalculate_access_paths_and_cost": { "tables": [ { "table": "`states`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "using_join_cache": true, "rows": 4120, "cost": 202721, "chosen": true } ] } } ] }, "cost": 202771, "rows": 246, "chosen": true }, { "strategy": "MaterializeLookup", "cost": 1761.4, "rows": 246, "duplicate_tables_left": false, "chosen": true }, { "strategy": "DuplicatesWeedout", "cost": 405527, "rows": 246, "duplicate_tables_left": false, "chosen": false } ], "chosen": true } ] }, { "final_semijoin_strategy": "MaterializeLookup" } ] }, { "creating_tmp_table": { "tmp_table_info": { "row_length": 5, "key_length": 4, "unique_constraint": false, "location": "memory (heap)", "row_limit_estimate": 3355443 } } }, { "attaching_conditions_to_tables": { "original_condition": "(`<subquery2>`.`country_id` = `countries`.`id`)", "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`countries`", "attached": "(`countries`.`id` is not null)" }, { "table": "``.`<subquery2>`", "attached": null }, { "table": "`states`", "attached": null } ] } }, { "refine_plan": [ { "table": "`countries`", "access_type": "table_scan" }, { "table": "``.`<subquery2>`" }, { "table": "`states`", "access_type": "table_scan" } ] } ] } }, { "join_explain": { "select#": 1, "steps": [ ] } } ] }
MySQL限时解答,24小时内友哥专业解答
http://www.yougemysqldba.com
如有进一步需要请联系微信onesoft007
微博账号@友哥一指
http://www.yougemysqldba.com
如有进一步需要请联系微信onesoft007
微博账号@友哥一指