GreatSQL 生态认证来了！！！

GreatSQL社区

搜索

[已解决] 如何优化这个查询mysql 8

560 3 2023-11-28 16:15
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)


全部回复(3)
只看楼主
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
回复 采纳

举报

365db

3

主题

0

博客

27

贡献

新手上路

Rank: 1

积分
36
发消息
互动与交流
社区活动
论坛问答
加入社区
贡献者指南
学习与应用
文章博客
文档资料
视频资料
常见问题
发现社区
社区准则
建议反馈
社区之星

合作电话：010-64087828

社区邮箱：greatsql@greatdb.com

社区公众号
社区小助手
QQ群
友情链接
万里数据库开源中国墨天轮开放原子开源基金会OpenEuler
©2022 GreatSQL All Rights Reserved. ( 京ICP备06057874号 )
GMT+8, 2024-7-27 10:59 , Processed in 0.017226 second(s), 11 queries , Redis On.
快速回复 返回顶部 返回列表