使用union 外加count
explain extended select count(*) from (select id from states where country_id < 100 union (select id from countries where id > 10 and name='aaa')) t; +----+--------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------+ | 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 4356 | 100.00 | NULL | | 2 | DERIVED | states | ALL | NULL | NULL | NULL | NULL | 4120 | 100.00 | Using where | | 3 | UNION | countries | range | PRIMARY | PRIMARY | 4 | NULL | 236 | 100.00 | Using where | | NULL | UNION RESULT | <union2,3> | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary | +----+--------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------+
QUERY: explain extended select count(*) from (select id from states where country_id < 100 union (select id from countries where id > 10 and name='aaa')) t TRACE: { "steps": [ { "join_preparation": { "select#": 2, "steps": [ { "expanded_query": "/* select#2 */ select `states`.`id` AS `id` from `states` where (`states`.`country_id` < 100)" } ] } }, { "join_preparation": { "select#": 3, "steps": [ { "expanded_query": "/* select#3 */ select `countries`.`id` AS `id` from `countries` where ((`countries`.`id` > 10) and (`countries`.`name` = 'aaa'))" } ] } }, { "creating_tmp_table": { "tmp_table_info": { "table": "intermediate_tmp_table", "row_length": 5, "key_length": 4, "unique_constraint": false, "location": "memory (heap)", "row_limit_estimate": 3355443 } } }, { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select count(0) AS `count(*)` from (/* select#2 */ select `states`.`id` AS `id` from `states` where (`states`.`country_id` < 100) union (/* select#3 */ select `countries`.`id` AS `id` from `countries` where ((`countries`.`id` > 10) and (`countries`.`name` = 'aaa')))) `t`" } ] } }, { "join_optimization": { "select#": 1, "steps": [ { "join_optimization": { "select#": 2, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "(`states`.`country_id` < 100)", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "(`states`.`country_id` < 100)" }, { "transformation": "constant_propagation", "resulting_condition": "(`states`.`country_id` < 100)" }, { "transformation": "trivial_condition_removal", "resulting_condition": "(`states`.`country_id` < 100)" } ] } }, { "table_dependencies": [ { "table": "`states`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] } ] }, { "ref_optimizer_key_uses": [ ] }, { "rows_estimation": [ { "table": "`states`", "table_scan": { "rows": 4120, "cost": 12 } } ] }, { "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 } ] }, { "attaching_conditions_to_tables": { "original_condition": "(`states`.`country_id` < 100)", "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`states`", "attached": "(`states`.`country_id` < 100)" } ] } }, { "refine_plan": [ { "table": "`states`", "access_type": "table_scan" } ] } ] } }, { "join_optimization": { "select#": 3, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "((`countries`.`id` > 10) and (`countries`.`name` = 'aaa'))", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "((`countries`.`id` > 10) and (`countries`.`name` = 'aaa'))" }, { "transformation": "constant_propagation", "resulting_condition": "((`countries`.`id` > 10) and (`countries`.`name` = 'aaa'))" }, { "transformation": "trivial_condition_removal", "resulting_condition": "((`countries`.`id` > 10) and (`countries`.`name` = 'aaa'))" } ] } }, { "table_dependencies": [ { "table": "`countries`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] } ] }, { "ref_optimizer_key_uses": [ ] }, { "rows_estimation": [ { "table": "`countries`", "range_analysis": { "table_scan": { "rows": 246, "cost": 52.3 }, "potential_range_indices": [ { "index": "PRIMARY", "usable": true, "key_parts": [ "id" ] } ], "setup_range_conditions": [ ], "group_index_range": { "chosen": false, "cause": "not_group_by_or_distinct" }, "analyzing_range_alternatives": { "range_scan_alternatives": [ { "index": "PRIMARY", "ranges": [ "10 < id" ], "index_dives_for_eq_ranges": true, "rowid_ordered": true, "using_mrr": false, "index_only": false, "rows": 236, "cost": 48.419, "chosen": true } ], "analyzing_roworder_intersect": { "usable": false, "cause": "too_few_roworder_scans" } }, "chosen_range_access_summary": { "range_access_plan": { "type": "range_scan", "index": "PRIMARY", "rows": 236, "ranges": [ "10 < id" ] }, "rows_for_plan": 236, "cost_for_plan": 48.419, "chosen": true } } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`countries`", "best_access_path": { "considered_access_paths": [ { "access_type": "range", "rows": 236, "cost": 95.619, "chosen": true } ] }, "cost_for_plan": 95.619, "rows_for_plan": 236, "chosen": true } ] }, { "attaching_conditions_to_tables": { "original_condition": "((`countries`.`id` > 10) and (`countries`.`name` = 'aaa'))", "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`countries`", "attached": "((`countries`.`id` > 10) and (`countries`.`name` = 'aaa'))" } ] } }, { "refine_plan": [ { "table": "`countries`", "access_type": "range" } ] } ] } }, { "table_dependencies": [ { "table": " `t`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] } ] }, { "rows_estimation": [ { "table": " `t`", "table_scan": { "rows": 4356, "cost": 227 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": " `t`", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 4356, "cost": 1099, "chosen": true } ] }, "cost_for_plan": 1099, "rows_for_plan": 4356, "chosen": true } ] }, { "attaching_conditions_to_tables": { "original_condition": null, "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": " `t`", "attached": null } ] } }, { "refine_plan": [ { "table": " `t`", "access_type": "table_scan" } ] } ] } }, { "join_explain": { "select#": 1, "steps": [ { "creating_tmp_table": { "tmp_table_info": { "table": " `t`", "row_length": 5, "key_length": 0, "unique_constraint": false, "location": "memory (heap)", "row_limit_estimate": 3355443 } } }, { "join_explain": { "select#": 2, "steps": [ ] } }, { "join_explain": { "select#": 3, "steps": [ ] } }, { "join_preparation": { "select#": "fake", "steps": [ { "expanded_query": "/* select#fake */ select `id` AS `id` from dual" } ] } }, { "join_optimization": { "select#": "fake", "steps": [ { "table_dependencies": [ { "table": "``.``", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] } ] }, { "rows_estimation": [ { "table": "``.``", "table_scan": { "rows": 0, "cost": 10 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "``.``", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 0, "cost": 10, "chosen": true } ] }, "cost_for_plan": 10, "rows_for_plan": 0, "chosen": true } ] }, { "attaching_conditions_to_tables": { "original_condition": null, "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "``.``", "attached": null } ] } }, { "refine_plan": [ { "table": "``.``", "access_type": "table_scan" } ] } ] } }, { "join_explain": { "select#": "fake", "steps": [ ] } } ] } } ] }
MySQL限时解答,24小时内友哥专业解答
http://www.yougemysqldba.com
如有进一步需要请联系微信onesoft007
微博账号@友哥一指
http://www.yougemysqldba.com
如有进一步需要请联系微信onesoft007
微博账号@友哥一指