如何优化这个查询mysql 8

365db 发表于 2023-11-28 16:15:07

1.查询语句SELECT menu_id, item_id, SUM(status) >= 0 "status" FROM item_activation_in_menu activation WHERE menu_id IN ('2d6e4dbc-9ccf-49cd-a924-ba5b646fc07d', 'f8042ee6-0de3-497c-8842-fc2699405fc5', 'fd436116-3b59-44aa-be74-115b4175b971', '7335cd44-c32a-40eb-88d3-e36e8efb24e2', '0d5fbd56-64a2-4d2b-b533-629f09afdd2b', '2ebf6f40-7e08-4888-93ce-499e241a9d85') GROUP BY menu_id, item_id;
现在的索引(menu_id, item_id,status).表有765万行，count(distinct menu_id)的值是16，从trace中发现是走了全表扫描。使用hint skip_scan无效。下面是trace信息。
mysql> select trace from `information_schema`.`optimizer_trace`\G*************************** 1. row ***************************trace: {"steps": [ { "join_preparation": { "select#": 1, "steps": [ { "IN_uses_bisection": true }, { "expanded_query": "/* select#1 */ select `item_activation_in_menu`.`menu_id` AS `menu_id`,`item_activation_in_menu`.`item_id` AS `item_id`,(sum(`item_activation_in_menu`.`status`) >= 0) AS `status` from `item_activation_in_menu` where (`item_activation_in_menu`.`menu_id` in ('2d6e4dbc-9ccf-49cd-a924-ba5b646fc07d','f8042ee6-0de3-497c-8842-fc2699405fc5','fd436116-3b59-44aa-be74-115b4175b971','7335cd44-c32a-40eb-88d3-e36e8efb24e2','0d5fbd56-64a2-4d2b-b533-629f09afdd2b','2ebf6f40-7e08-4888-93ce-499e241a9d85')) group by `item_activation_in_menu`.`menu_id`,`item_activation_in_menu`.`item_id`" } } }, { "join_optimization": { "select#": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "(`item_activation_in_menu`.`menu_id` in ('2d6e4dbc-9ccf-49cd-a924-ba5b646fc07d','f8042ee6-0de3-497c-8842-fc2699405fc5','fd436116-3b59-44aa-be74-115b4175b971','7335cd44-c32a-40eb-88d3-e36e8efb24e2','0d5fbd56-64a2-4d2b-b533-629f09afdd2b','2ebf6f40-7e08-4888-93ce-499e241a9d85'))", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "(`item_activation_in_menu`.`menu_id` in ('2d6e4dbc-9ccf-49cd-a924-ba5b646fc07d','f8042ee6-0de3-497c-8842-fc2699405fc5','fd436116-3b59-44aa-be74-115b4175b971','7335cd44-c32a-40eb-88d3-e36e8efb24e2','0d5fbd56-64a2-4d2b-b533-629f09afdd2b','2ebf6f40-7e08-4888-93ce-499e241a9d85'))" }, { "transformation": "constant_propagation", "resulting_condition": "(`item_activation_in_menu`.`menu_id` in ('2d6e4dbc-9ccf-49cd-a924-ba5b646fc07d','f8042ee6-0de3-497c-8842-fc2699405fc5','fd436116-3b59-44aa-be74-115b4175b971','7335cd44-c32a-40eb-88d3-e36e8efb24e2','0d5fbd56-64a2-4d2b-b533-629f09afdd2b','2ebf6f40-7e08-4888-93ce-499e241a9d85'))" }, { "transformation": "trivial_condition_removal", "resulting_condition": "(`item_activation_in_menu`.`menu_id` in ('2d6e4dbc-9ccf-49cd-a924-ba5b646fc07d','f8042ee6-0de3-497c-8842-fc2699405fc5','fd436116-3b59-44aa-be74-115b4175b971','7335cd44-c32a-40eb-88d3-e36e8efb24e2','0d5fbd56-64a2-4d2b-b533-629f09afdd2b','2ebf6f40-7e08-4888-93ce-499e241a9d85'))" } } }, { "substitute_generated_columns": { } }, { "table_dependencies": [ { "table": "`item_activation_in_menu`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ } }, { "ref_optimizer_key_uses": [ }, { "rows_estimation": [ { "table": "`item_activation_in_menu`", "range_analysis": { "table_scan": { "rows": 7690706, "cost": 830416 }, "potential_range_indexes": [ { "index": "PRIMARY", "usable": false, "cause": "not_applicable" }, { "index": "item_act_item_id_fk", "usable": false, "cause": "not_applicable" }, { "index": "menu_id", "usable": true, "key_parts": [ "menu_id", "id" }, { "index": "idx_mid_iid_st", "usable": true, "key_parts": [ "menu_id", "item_id", "status", "id" } ], "best_covering_index_scan": { "index": "idx_mid_iid_st", "cost": 2.64865e+06, "chosen": false, "cause": "cost" }, "setup_range_conditions": [ ], "group_index_range": { "chosen": false, "cause": "not_applicable_aggregate_function" }, "skip_scan_range": { "chosen": false, "cause": "has_group_by" }, "analyzing_range_alternatives": { "range_scan_alternatives": [ { "index": "menu_id", "ranges": [ "0d5fbd56-64a2-4d2b-b533-629f09afdd2b <= menu_id <= 0d5fbd56-64a2-4d2b-b533-629f09afdd2b", "2d6e4dbc-9ccf-49cd-a924-ba5b646fc07d <= menu_id <= 2d6e4dbc-9ccf-49cd-a924-ba5b646fc07d", "2ebf6f40-7e08-4888-93ce-499e241a9d85 <= menu_id <= 2ebf6f40-7e08-4888-93ce-499e241a9d85", "7335cd44-c32a-40eb-88d3-e36e8efb24e2 <= menu_id <= 7335cd44-c32a-40eb-88d3-e36e8efb24e2", "f8042ee6-0de3-497c-8842-fc2699405fc5 <= menu_id <= f8042ee6-0de3-497c-8842-fc2699405fc5", "fd436116-3b59-44aa-be74-115b4175b971 <= menu_id <= fd436116-3b59-44aa-be74-115b4175b971" ], "index_dives_for_eq_ranges": true, "rowid_ordered": false, "using_mrr": false, "index_only": false, "rows": 4467039, "cost": 4.90682e+06, "chosen": false, "cause": "cost" }, { "index": "idx_mid_iid_st", "ranges": [ "0d5fbd56-64a2-4d2b-b533-629f09afdd2b <= menu_id <= 0d5fbd56-64a2-4d2b-b533-629f09afdd2b", "2d6e4dbc-9ccf-49cd-a924-ba5b646fc07d <= menu_id <= 2d6e4dbc-9ccf-49cd-a924-ba5b646fc07d", "2ebf6f40-7e08-4888-93ce-499e241a9d85 <= menu_id <= 2ebf6f40-7e08-4888-93ce-499e241a9d85", "7335cd44-c32a-40eb-88d3-e36e8efb24e2 <= menu_id <= 7335cd44-c32a-40eb-88d3-e36e8efb24e2", "f8042ee6-0de3-497c-8842-fc2699405fc5 <= menu_id <= f8042ee6-0de3-497c-8842-fc2699405fc5", "fd436116-3b59-44aa-be74-115b4175b971 <= menu_id <= fd436116-3b59-44aa-be74-115b4175b971" ], "index_dives_for_eq_ranges": true, "rowid_ordered": false, "using_mrr": false, "index_only": true, "rows": 4488053, "cost": 1.54567e+06, "chosen": false, "cause": "cost" } ], "analyzing_roworder_intersect": { "usable": false, "cause": "too_few_roworder_scans" } } } } }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`item_activation_in_menu`", "best_access_path": { "considered_access_paths": [ { "rows_to_scan": 7690706, "access_type": "scan", "resulting_rows": 7.69071e+06, "cost": 830414, "chosen": true, "use_tmp_table": true } }, "condition_filtering_pct": 100, "rows_for_plan": 7.69071e+06, "cost_for_plan": 830414, "sort_cost": 7.69071e+06, "new_cost_for_plan": 8.52112e+06, "chosen": true } }, { "attaching_conditions_to_tables": { "original_condition": "(`item_activation_in_menu`.`menu_id` in ('2d6e4dbc-9ccf-49cd-a924-ba5b646fc07d','f8042ee6-0de3-497c-8842-fc2699405fc5','fd436116-3b59-44aa-be74-115b4175b971','7335cd44-c32a-40eb-88d3-e36e8efb24e2','0d5fbd56-64a2-4d2b-b533-629f09afdd2b','2ebf6f40-7e08-4888-93ce-499e241a9d85'))", "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`item_activation_in_menu`", "attached": "(`item_activation_in_menu`.`menu_id` in ('2d6e4dbc-9ccf-49cd-a924-ba5b646fc07d','f8042ee6-0de3-497c-8842-fc2699405fc5','fd436116-3b59-44aa-be74-115b4175b971','7335cd44-c32a-40eb-88d3-e36e8efb24e2','0d5fbd56-64a2-4d2b-b533-629f09afdd2b','2ebf6f40-7e08-4888-93ce-499e241a9d85'))" } } }, { "optimizing_distinct_group_by_order_by": { "simplifying_group_by": { "original_clause": "`item_activation_in_menu`.`menu_id`,`item_activation_in_menu`.`item_id`", "items": [ { "item": "`item_activation_in_menu`.`menu_id`" }, { "item": "`item_activation_in_menu`.`item_id`" } ], "resulting_clause_is_simple": true, "resulting_clause": "`item_activation_in_menu`.`menu_id`,`item_activation_in_menu`.`item_id`" } } }, { "reconsidering_access_paths_for_index_ordering": { "clause": "GROUP BY", "steps": [ ], "index_order_summary": { "table": "`item_activation_in_menu`", "index_provides_order": true, "order_direction": "asc", "index": "idx_mid_iid_st", "plan_changed": false } } }, { "finalizing_table_conditions": [ { "table": "`item_activation_in_menu`", "original_table_condition": "(`item_activation_in_menu`.`menu_id` in ('2d6e4dbc-9ccf-49cd-a924-ba5b646fc07d','f8042ee6-0de3-497c-8842-fc2699405fc5','fd436116-3b59-44aa-be74-115b4175b971','7335cd44-c32a-40eb-88d3-e36e8efb24e2','0d5fbd56-64a2-4d2b-b533-629f09afdd2b','2ebf6f40-7e08-4888-93ce-499e241a9d85'))", "final_table_condition ": "(`item_activation_in_menu`.`menu_id` in ('2d6e4dbc-9ccf-49cd-a924-ba5b646fc07d','f8042ee6-0de3-497c-8842-fc2699405fc5','fd436116-3b59-44aa-be74-115b4175b971','7335cd44-c32a-40eb-88d3-e36e8efb24e2','0d5fbd56-64a2-4d2b-b533-629f09afdd2b','2ebf6f40-7e08-4888-93ce-499e241a9d85'))" } }, { "refine_plan": [ { "table": "`item_activation_in_menu`" } }, { "considering_tmp_tables": [ } } }, { "join_execution": { "select#": 1, "steps": [ } }}1 row in set (0.01 sec)

KAiTO 发表于 2023-11-29 17:23:42

这个查询好像走了全表扫描，应该是优化器觉得索引访问代价高，可以用用USE INDEX 提示优化器使用索引，如果不行就强制使用索引FORCE INDEX试试

365db 发表于 2023-12-5 10:51:02

KAiTO 发表于 2023-11-29 17:23
这个查询好像走了全表扫描，应该是优化器觉得索引访问代价高，可以用用USE INDEX 提示优化器使用索引，如果 ...

试了，没用....

yejr 发表于 2023-12-5 17:04:07

365db 发表于 2023-12-5 10:51
试了，没用....
参考提问的艺术 https://greatsql.cn/blog-16-1314.html

页: [1]

GreatSQL社区's Archiver

如何优化这个查询mysql 8