MariaDB从10.6升级到11.0.2后查询变慢

cwxwcias  于 11个月前  发布在  其他
关注(0)|答案(1)|浏览(155)

最近我们更新到了最新的稳定版本11.0.2 Maria db，突然我在我的一些查询上遇到了缓慢的问题，如果相同的查询在10.6中运行，它在10.6中运行得很快，它运行了5秒，但升级后它运行了近20秒，我尝试了this answer too但没有帮助，如果有任何建议来克服这个问题。

SELECT
    `ft_fieldName` AS `Field Name`,
    `prm_id` AS `recipeid`,
    '' AS 'Pricing Type',
    'BOM' AS 'GROUP',
    '' AS 'Qty',
    JSON_UNQUOTE(
        JSON_EXTRACT(cd_custmzdcolrdetails, '$.299')
    ) AS `colorname`,
   
    JSON_UNQUOTE(
        JSON_EXTRACT(cd_custmzdcolrdetails, '$.300')
    ) AS `Color Code`,
  
  
   
    JSON_UNQUOTE(
        JSON_EXTRACT(cd_custmzdcolrdetails, '$.357')
    ) AS `Unit Cost`,
    `dropdown_name` AS `Unit Type`,
    COUNT(pgmm_productmapid) AS pgmm_fabriccolormapid
    
FROM
    `bm_productrecipemap` FORCE INDEX(`prm_status`)
INNER JOIN `bm_productgroupmaterialmap` ON `pgmm_id` = `prm_fieldvalueid` AND `pgmm_status` = 0  
INNER JOIN `bm_fabriccolourmap` ON `pfm_id` = `pgmm_fabriccolormapid` 
INNER JOIN `bm_fabricdetails` ON `pfm_fabricmapid` = `fd_id` AND `fd_status` = 0
INNER JOIN `bm_colordetails` ON `pfm_colourmapid` = `cd_id` AND `cd_status` = 0
INNER JOIN `bm_productInfo` ON `pi_productid` = `pgmm_productmapid` AND `pi_status` = 0
INNER JOIN `bm_productrecipe` ON `pr_id` = `prm_recipeid` AND `pr_status` = 0
INNER JOIN `bm_fieldtypes` ON `ft_id` = `prm_fieldtypeid` AND `ft_status` = 0
LEFT JOIN `bm_common_dropdown_list` ON `dropdown_primaryid` = JSON_UNQUOTE(
        JSON_EXTRACT(cd_custmzdcolrdetails, '$.355')
    ) AND `dropdown_tag_name` = "unit_type"
WHERE
    `prm_status` = 0 AND `prm_recipeid` = 18 AND `prm_fieldtypeid` = 22
GROUP BY
    `pgmm_fabriccolormapid`,
    `pgmm_productmapid`

11.0.2EXPLAIN QUERY
10.6EXPLAIN QUERY

适用于11.0.2

{
  "query_optimization": {
    "r_total_time_ms": 7.173012053
  },
  "query_block": {
    "select_id": 1,
    "cost": 18268.14136,
    "r_loops": 1,
    "r_total_time_ms": 9430.630307,
    "filesort": {
      "sort_key": "bm_productgroupmaterialmap.pgmm_fabriccolormapid, bm_productgroupmaterialmap.pgmm_productmapid",
      "r_loops": 1,
      "r_total_time_ms": 3.427513217,
      "r_used_priority_queue": false,
      "r_output_rows": 19460,
      "r_buffer_size": "437Kb",
      "r_sort_mode": "sort_key,rowid",
      "temporary_table": {
        "nested_loop": [
          {
            "table": {
              "table_name": "bm_productrecipe",
              "access_type": "const",
              "possible_keys": ["PRIMARY", "pr_status"],
              "key": "PRIMARY",
              "key_length": "4",
              "used_key_parts": ["pr_id"],
              "ref": ["const"],
              "r_loops": 0,
              "rows": 1,
              "r_rows": null,
              "filtered": 100,
              "r_filtered": null
            }
          },
          {
            "table": {
              "table_name": "bm_fieldtypes",
              "access_type": "const",
              "possible_keys": ["PRIMARY", "ft_status"],
              "key": "PRIMARY",
              "key_length": "4",
              "used_key_parts": ["ft_id"],
              "ref": ["const"],
              "r_loops": 0,
              "rows": 1,
              "r_rows": null,
              "filtered": 100,
              "r_filtered": null
            }
          },
          {
            "table": {
              "table_name": "bm_productrecipemap",
              "access_type": "ref",
              "possible_keys": ["prm_status"],
              "key": "prm_status",
              "key_length": "4",
              "used_key_parts": ["prm_status"],
              "ref": ["const"],
              "loops": 1,
              "r_loops": 1,
              "rows": 1245496,
              "r_rows": 1250925,
              "cost": 1241.246338,
              "r_table_time_ms": 4927.520843,
              "r_other_time_ms": 139.7680558,
              "filtered": 100,
              "r_filtered": 26.45178568,
              "attached_condition": "bm_productrecipemap.prm_recipeid = 18 and bm_productrecipemap.prm_fieldtypeid = 22"
            }
          },
          {
            "table": {
              "table_name": "bm_productgroupmaterialmap",
              "access_type": "eq_ref",
              "possible_keys": [
                "PRIMARY",
                "pgmm_status",
                "pgmm_fabriccolormapid",
                "pgmm_productmapid"
              ],
              "key": "PRIMARY",
              "key_length": "4",
              "used_key_parts": ["pgmm_id"],
              "ref": [
                "CREATIVECURTAINSSUSSEX.bm_productrecipemap.prm_fieldvalueid"
              ],
              "loops": 1245496,
              "r_loops": 330892,
              "rows": 1,
              "r_rows": 1,
              "cost": 1115.676226,
              "r_table_time_ms": 720.8451661,
              "r_other_time_ms": 171.4830087,
              "filtered": 100,
              "r_filtered": 100,
              "attached_condition": "bm_productgroupmaterialmap.pgmm_status = 0 and bm_productgroupmaterialmap.pgmm_id = bm_productrecipemap.prm_fieldvalueid"
            }
          },
          {
            "table": {
              "table_name": "bm_fabriccolourmap",
              "access_type": "eq_ref",
              "possible_keys": ["PRIMARY", "pfm_fabricmapid", "pfm_colourmapid"],
              "key": "PRIMARY",
              "key_length": "4",
              "used_key_parts": ["pfm_id"],
              "ref": [
                "CREATIVECURTAINSSUSSEX.bm_productgroupmaterialmap.pgmm_fabriccolormapid"
              ],
              "loops": 1245496,
              "r_loops": 330892,
              "r_table_loops": 286792,
              "rows": 1,
              "r_rows": 1,
              "cost": 1114.467906,
              "r_table_time_ms": 597.0053344,
              "r_other_time_ms": 53.0959404,
              "filtered": 100,
              "r_filtered": 100,
              "attached_condition": "bm_fabriccolourmap.pfm_id = bm_productgroupmaterialmap.pgmm_fabriccolormapid and bm_fabriccolourmap.pfm_colourmapid is not null"
            }
          },
          {
            "table": {
              "table_name": "bm_fabricdetails",
              "access_type": "eq_ref",
              "possible_keys": ["PRIMARY", "fd_status"],
              "key": "fd_status",
              "key_length": "8",
              "used_key_parts": ["fd_status", "fd_id"],
              "ref": [
                "const",
                "CREATIVECURTAINSSUSSEX.bm_fabriccolourmap.pfm_fabricmapid"
              ],
              "loops": 1245496,
              "r_loops": 330892,
              "r_table_loops": 286219,
              "rows": 1,
              "r_rows": 0.999782406,
              "cost": 1089.076122,
              "r_table_time_ms": 440.3039905,
              "r_other_time_ms": 48.2909558,
              "filtered": 100,
              "r_filtered": 100,
              "attached_condition": "bm_fabriccolourmap.pfm_fabricmapid = bm_fabricdetails.fd_id",
              "using_index": true
            }
          },
          {
            "table": {
              "table_name": "bm_colordetails",
              "access_type": "eq_ref",
              "possible_keys": ["PRIMARY", "cd_status", "cd_fabricmapid"],
              "key": "PRIMARY",
              "key_length": "4",
              "used_key_parts": ["cd_id"],
              "ref": ["CREATIVECURTAINSSUSSEX.bm_fabriccolourmap.pfm_colourmapid"],
              "loops": 1245496,
              "r_loops": 330820,
              "r_table_loops": 286760,
              "rows": 1,
              "r_rows": 1,
              "cost": 1114.520335,
              "r_table_time_ms": 642.1860278,
              "r_other_time_ms": 584.9013592,
              "filtered": 100,
              "r_filtered": 100,
              "attached_condition": "bm_colordetails.cd_status = 0 and bm_fabriccolourmap.pfm_colourmapid = bm_colordetails.cd_id"
            }
          },
          {
            "table": {
              "table_name": "bm_productInfo",
              "access_type": "eq_ref",
              "possible_keys": ["PRIMARY", "pi_status"],
              "key": "pi_status",
              "key_length": "8",
              "used_key_parts": ["pi_status", "pi_productid"],
              "ref": [
                "const",
                "CREATIVECURTAINSSUSSEX.bm_productgroupmaterialmap.pgmm_productmapid"
              ],
              "loops": 1245496,
              "r_loops": 330820,
              "r_table_loops": 1,
              "rows": 1,
              "r_rows": 1,
              "cost": 1089.068749,
              "r_table_time_ms": 0.005281613,
              "r_other_time_ms": 0.021677216,
              "filtered": 100,
              "r_filtered": 100,
              "attached_condition": "bm_productInfo.pi_productid = bm_productgroupmaterialmap.pgmm_productmapid",
              "using_index": true
            }
          },
          {
            "table": {
              "table_name": "bm_common_dropdown_list",
              "access_type": "ref",
              "possible_keys": ["dropdown_primaryid"],
              "key": "dropdown_primaryid",
              "key_length": "5",
              "used_key_parts": ["dropdown_primaryid"],
              "ref": ["func"],
              "loops": 1245496,
              "r_loops": 330820,
              "rows": 5,
              "r_rows": 0.003545735,
              "cost": 7216.307461,
              "r_table_time_ms": 265.3482518,
              "r_other_time_ms": 829.0345013,
              "filtered": 100,
              "r_filtered": 4.347826087,
              "attached_condition": "trigcond(bm_common_dropdown_list.dropdown_tag_name = 'unit_type' and bm_common_dropdown_list.dropdown_primaryid = json_unquote(json_extract(bm_colordetails.cd_custmzdcolrdetails,'$.355')))"
            }
          }
        ]
      }
    }
  }
}

10.6

{
  "query_block": {
    "select_id": 1,
    "r_loops": 1,
    "r_total_time_ms": 4362.336552,
    "filesort": {
      "sort_key": "bm_productgroupmaterialmap.pgmm_fabriccolormapid, bm_productgroupmaterialmap.pgmm_productmapid",
      "r_loops": 1,
      "r_total_time_ms": 2.977471882,
      "r_used_priority_queue": false,
      "r_output_rows": 19460,
      "r_buffer_size": "437Kb",
      "r_sort_mode": "sort_key,rowid",
      "temporary_table": {
        "table": {
          "table_name": "bm_productrecipe",
          "access_type": "const",
          "possible_keys": ["PRIMARY", "pr_status"],
          "key": "PRIMARY",
          "key_length": "4",
          "used_key_parts": ["pr_id"],
          "ref": ["const"],
          "r_loops": 0,
          "rows": 1,
          "r_rows": null,
          "filtered": 100,
          "r_filtered": null
        },
        "table": {
          "table_name": "bm_fieldtypes",
          "access_type": "const",
          "possible_keys": ["PRIMARY", "ft_status"],
          "key": "PRIMARY",
          "key_length": "4",
          "used_key_parts": ["ft_id"],
          "ref": ["const"],
          "r_loops": 0,
          "rows": 1,
          "r_rows": null,
          "filtered": 100,
          "r_filtered": null
        },
        "table": {
          "table_name": "bm_productrecipemap",
          "access_type": "ref",
          "possible_keys": ["prm_status"],
          "key": "prm_status",
          "key_length": "4",
          "used_key_parts": ["prm_status"],
          "ref": ["const"],
          "r_loops": 1,
          "rows": 621390,
          "r_rows": 1250923,
          "r_table_time_ms": 1525.038047,
          "r_other_time_ms": 72.17298694,
          "filtered": 100,
          "r_filtered": 26.45182797,
          "attached_condition": "bm_productrecipemap.prm_recipeid = 18 and bm_productrecipemap.prm_fieldtypeid = 22"
        },
        "table": {
          "table_name": "bm_productgroupmaterialmap",
          "access_type": "eq_ref",
          "possible_keys": [
            "PRIMARY",
            "pgmm_status",
            "pgmm_fabriccolormapid",
            "pgmm_productmapid"
          ],
          "key": "PRIMARY",
          "key_length": "4",
          "used_key_parts": ["pgmm_id"],
          "ref": ["CREATIVECURTAINSSUSSEX.bm_productrecipemap.prm_fieldvalueid"],
          "r_loops": 330892,
          "rows": 1,
          "r_rows": 1,
          "r_table_time_ms": 493.5064977,
          "r_other_time_ms": 140.3322178,
          "filtered": 49.99987411,
          "r_filtered": 100,
          "attached_condition": "bm_productgroupmaterialmap.pgmm_status = 0 and bm_productgroupmaterialmap.pgmm_id = bm_productrecipemap.prm_fieldvalueid"
        },
        "table": {
          "table_name": "bm_productInfo",
          "access_type": "eq_ref",
          "possible_keys": ["PRIMARY", "pi_status"],
          "key": "PRIMARY",
          "key_length": "4",
          "used_key_parts": ["pi_productid"],
          "ref": [
            "CREATIVECURTAINSSUSSEX.bm_productgroupmaterialmap.pgmm_productmapid"
          ],
          "r_loops": 330892,
          "rows": 1,
          "r_rows": 1,
          "r_table_time_ms": 0.00474078,
          "r_other_time_ms": 0.002440078,
          "filtered": 86.79244995,
          "r_filtered": 100,
          "attached_condition": "bm_productInfo.pi_status = 0 and bm_productInfo.pi_productid = bm_productgroupmaterialmap.pgmm_productmapid"
        },
        "table": {
          "table_name": "bm_fabriccolourmap",
          "access_type": "eq_ref",
          "possible_keys": ["PRIMARY", "pfm_fabricmapid", "pfm_colourmapid"],
          "key": "PRIMARY",
          "key_length": "4",
          "used_key_parts": ["pfm_id"],
          "ref": [
            "CREATIVECURTAINSSUSSEX.bm_productgroupmaterialmap.pgmm_fabriccolormapid"
          ],
          "r_loops": 330892,
          "rows": 1,
          "r_rows": 1,
          "r_table_time_ms": 309.0945127,
          "r_other_time_ms": 30.59452498,
          "filtered": 100,
          "r_filtered": 100,
          "attached_condition": "bm_fabriccolourmap.pfm_id = bm_productgroupmaterialmap.pgmm_fabriccolormapid and bm_fabriccolourmap.pfm_colourmapid is not null"
        },
        "table": {
          "table_name": "bm_fabricdetails",
          "access_type": "eq_ref",
          "possible_keys": ["PRIMARY", "fd_status"],
          "key": "PRIMARY",
          "key_length": "4",
          "used_key_parts": ["fd_id"],
          "ref": ["CREATIVECURTAINSSUSSEX.bm_fabriccolourmap.pfm_fabricmapid"],
          "r_loops": 330892,
          "rows": 1,
          "r_rows": 1,
          "r_table_time_ms": 290.3357413,
          "r_other_time_ms": 35.36422041,
          "filtered": 49.99639511,
          "r_filtered": 99.97824063,
          "attached_condition": "bm_fabricdetails.fd_status = 0 and bm_fabriccolourmap.pfm_fabricmapid = bm_fabricdetails.fd_id"
        },
        "table": {
          "table_name": "bm_colordetails",
          "access_type": "eq_ref",
          "possible_keys": ["PRIMARY", "cd_status", "cd_fabricmapid"],
          "key": "PRIMARY",
          "key_length": "4",
          "used_key_parts": ["cd_id"],
          "ref": ["CREATIVECURTAINSSUSSEX.bm_fabriccolourmap.pfm_colourmapid"],
          "r_loops": 330820,
          "rows": 1,
          "r_rows": 1,
          "r_table_time_ms": 317.5412715,
          "r_other_time_ms": 373.7023579,
          "filtered": 50,
          "r_filtered": 100,
          "attached_condition": "bm_colordetails.cd_status = 0 and bm_fabriccolourmap.pfm_colourmapid = bm_colordetails.cd_id"
        },
        "table": {
          "table_name": "bm_common_dropdown_list",
          "access_type": "ref",
          "possible_keys": ["dropdown_primaryid"],
          "key": "dropdown_primaryid",
          "key_length": "5",
          "used_key_parts": ["dropdown_primaryid"],
          "ref": ["func"],
          "r_loops": 330820,
          "rows": 2,
          "r_rows": 0.002363823,
          "r_table_time_ms": 182.2695986,
          "r_other_time_ms": 581.7986746,
          "filtered": 100,
          "r_filtered": 4.347826087,
          "attached_condition": "trigcond(bm_common_dropdown_list.dropdown_tag_name = 'unit_type' and bm_common_dropdown_list.dropdown_primaryid = json_unquote(json_extract(bm_colordetails.cd_custmzdcolrdetails,'$.355')))"
        }
      }
    }
  }
}
mariadb

1条答案

按热度按时间
zfciruhq

zfciruhq1#

在bm_productrecipemap中有1250925行。使用的索引是prm_status，它似乎是单个项目的索引。26%的表似乎有prm_status=0。在该表中，r_table_time_ms中可见约3秒的差异。
更改索引以包含where子句的其他部分将需要更少的主表阅读和更少的索引读取。

ALTER TABLE bm_productrecipemap DROP INDEX prm_status,
    ADD INDEX prm_status(prm_status, prm_recipeid, prm_fieldtypeid);

其他表的访问速度似乎也较慢。我会把这个交给开发人员来检查。

赞(0）分享  回复(0）举报  11个月前
我来回答

相关问题

    查看更多
    微信公众号

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com