MYSQL视图非常慢

Question

>我通过连接多个表创建了视图，以避免动态查询的加载时间。 但它通过花费更多时间给出相同的结果。

我的系统配置是：8GB RAM i5 CPU，并在MySQL 5.7.28上运行。 这是我的查询：

创建此类视图的原因是： 我使用 php 循环生成了这个查询。 因为我需要值作为列，所有内容都应该在一个视图中，我可以轻松访问。 在更短的时间内，但不工作。 如果你能建议一些更好的方法来设计视图或避免，生成查询与帮助你PHP循环将是最好的解决方案。 下面是所有表的定义。

attribute_value

属性

gemstone_attribute_value

gemstone_attribute

宝石

类别

产品

全局 ： https://pastebin.com/mp4cJf90

GLOBAL_VARIABLE ： https://pastebin.com/Z538iku1

mysql_tuner_report ： https://pastebin.com/DLbYgJ5Q

这是 mysql 配置变量：config 第一：

create view ring_view as  SELECT
`p`.`product_id` AS `product_id`,
`p`.`name` AS `product_name`,
`p`.`group_id` AS `group_id`,
`p`.`gem_group_id` AS `gem_group_id`,
`p`.`gem_id` AS `gem_id`,
`p`.`sku` AS `product_sku`,
`p`.`fake_sku` AS `fake_sku`,
`p`.`desp` AS `product_desp`,
`p`.`short` AS `product_short`,
`p`.`price` AS `product_price`,
`p`.`sale_price` AS `product_sale_price`,
(`p`.`price` - `p`.`sale_price`) AS `product_discount`,
`p`.`status` AS `product_status`,
`p`.`slug` AS `product_slug`,
`p`.`meta_title` AS `product_meta_title`,
`p`.`meta_keywords` AS `product_meta_keywords`,
`p`.`meta_desp` AS `product_meta_desp`,
`p`.`metal_color` AS `metal_type`,
`p`.`category_id` AS `category_id`,
`pc`.`name` AS `category_name`,
`pc`.`slug` AS `category_slug`,
`pc`.`parent` AS `category_parent`,
`pc`.`status` AS `category_status`,
(CASE WHEN(av.attribute_id = '47') THEN av.value END) AS 'at_Ring_Style_1',
(CASE WHEN(av.attribute_id = '51') THEN av.value END) AS 'at_Height_1',
(CASE WHEN(av.attribute_id = '52') THEN av.value END) AS 'at_Sizes_1',
(CASE WHEN(av.attribute_id = '53') THEN av.value END) AS 'at_Rhodium_Plated_1',
(CASE WHEN(av.attribute_id = '33') THEN av.value END) AS 'at_Shape_2',
(CASE WHEN(av.attribute_id = '36') THEN av.value END) AS 'at_Gemstone_2',
(CASE WHEN(av.attribute_id = '1') THEN av.value END) AS 'at_Ring_Style_2',
(CASE WHEN(av.attribute_id = '54') THEN av.value END) AS 'at_Stone_Breakdown_2',
(CASE WHEN(av.attribute_id = '2') THEN av.value END) AS 'at_Type_2',
(CASE WHEN(av.attribute_id = '41') THEN av.value END) AS 'at_Setting_2',
(CASE WHEN(av.attribute_id = '42') THEN av.value END) AS 'at_Rhodium_Finish_2',
(CASE WHEN(av.attribute_id = '48') THEN av.value END) AS 'at_Bracelet_Style_3',
(CASE WHEN(av.attribute_id = '37') THEN av.value END) AS 'at_Rhodium_Plated_3',
(CASE WHEN(av.attribute_id = '38') THEN av.value END) AS 'at_Approximate_Weight_3',
(CASE WHEN(av.attribute_id = '10') THEN av.value END) AS 'at_Diameter_3',
(CASE WHEN(av.attribute_id = '11') THEN av.value END) AS 'at_Length_3',
(CASE WHEN(av.attribute_id = '12') THEN av.value END) AS 'at_Enhancement_3',
(CASE WHEN(av.attribute_id = '14') THEN av.value END) AS 'at_Clasp_Type_3',
(CASE WHEN(av.attribute_id = '25') THEN av.value END) AS 'at_Chain_Length_4',
(CASE WHEN(av.attribute_id = '49') THEN av.value END) AS 'at_Necklace_Style_4',
(CASE WHEN(av.attribute_id = '15') THEN av.value END) AS 'at_Chain_Type_4',
(CASE WHEN(av.attribute_id = '16') THEN av.value END) AS 'at_Back_Type_5',
(CASE WHEN(av.attribute_id = '50') THEN av.value END) AS 'at_Earring_Style_5',
(CASE WHEN(av.attribute_id = '43') THEN av.value END) AS 'at_Backing_5',
(CASE WHEN(av.attribute_id = '44') THEN av.value END) AS 'at_Rhodium_Plated_5',
(CASE WHEN(av.attribute_id = '28') THEN av.value END) AS 'at_Height_8',
(
CASE WHEN(av.attribute_id = '29') THEN av.value
END
) AS 'at_Band_Fit_8',
(
CASE WHEN(av.attribute_id = '32') THEN av.value
END
) AS 'at_Gender_8',
(
CASE WHEN(av.attribute_id = '39') THEN av.value
END
) AS 'at_Ring_Style_8',
(
CASE WHEN(av.attribute_id = '40') THEN av.value
END
) AS 'at_Gemstone_8',
(
CASE WHEN(av.attribute_id = '45') THEN av.value
END
) AS 'at_Gemstone_9',
(
CASE WHEN(av.attribute_id = '46') THEN av.value
END
) AS 'at_Product_Type_9',
(
CASE WHEN(av.attribute_id = '35') THEN av.value
END
) AS 'at_Style_9',
(
CASE WHEN(gv.gem_attr_id = '1') THEN gv.value
END
) AS 'gs_number_of_center_cushion_amethyst_1',
(
CASE WHEN(gv.gem_attr_id = '2') THEN gv.value
END
) AS 'gs_minimum_clarity_1',
(
CASE WHEN(gv.gem_attr_id = '3') THEN gv.value
END
) AS 'gs_dimensions_1',
(
CASE WHEN(gv.gem_attr_id = '4') THEN gv.value
END
) AS 'gs_setting_type_1',
(
CASE WHEN(gv.gem_attr_id = '5') THEN gv.value
END
) AS 'gs_number_of_center_cushion_amethyst_2',
(
CASE WHEN(gv.gem_attr_id = '6') THEN gv.value
END
) AS 'gs_minimum_clarity_2',
(
CASE WHEN(gv.gem_attr_id = '7') THEN gv.value
END
) AS 'gs_dimensions_2',
(
CASE WHEN(gv.gem_attr_id = '8') THEN gv.value
END
) AS 'gs_setting_type_2',
(
CASE WHEN(gv.gem_attr_id = '9') THEN gv.value
END
) AS 'gs_number_of_center_cushion_amethyst_3',
(
CASE WHEN(gv.gem_attr_id = '10') THEN gv.value
END
) AS 'gs_minimum_clarity_3',
(
CASE WHEN(gv.gem_attr_id = '11') THEN gv.value
END
) AS 'gs_dimensions_3',
(
CASE WHEN(gv.gem_attr_id = '12') THEN gv.value
END
) AS 'gs_setting_type_3',
(
CASE WHEN(gv.gem_attr_id = '13') THEN gv.value
END
) AS 'gs_enhancement_3',
(
CASE WHEN(gv.gem_attr_id = '14') THEN gv.value
END
) AS 'gs_number_of_center_cushion_amethyst_4',
(
CASE WHEN(gv.gem_attr_id = '15') THEN gv.value
END
) AS 'gs_dimensions_4',
(
CASE WHEN(gv.gem_attr_id = '16') THEN gv.value
END
) AS 'gs_setting_type_4',
(
CASE WHEN(gv.gem_attr_id = '21') THEN gv.value
END
) AS 'gs_dimensions_6',
(
CASE WHEN(gv.gem_attr_id = '22') THEN gv.value
END
) AS 'gs_setting_type_6',
(
CASE WHEN(gv.gem_attr_id = '23') THEN gv.value
END
) AS 'gs_number_of_side_round_pink_tourmalines_6',
(
CASE WHEN(gv.gem_attr_id = '24') THEN gv.value
END
) AS 'gs_minimum_size__6',
(
CASE WHEN(gv.gem_attr_id = '25') THEN gv.value
END
) AS 'gs_number_of_center_round_garnets_6',
(
CASE WHEN(gv.gem_attr_id = '26') THEN gv.value
END
) AS 'gs_number_of_center_cushion_amethyst_7',
(
CASE WHEN(gv.gem_attr_id = '27') THEN gv.value
END
) AS 'gs_dimensions_7',
(
CASE WHEN(gv.gem_attr_id = '28') THEN gv.value
END
) AS 'gs_setting_type_7',
(
CASE WHEN(gv.gem_attr_id = '29') THEN gv.value
END
) AS 'gs_enhancement_7',
(
CASE WHEN(gv.gem_attr_id = '30') THEN gv.value
END
) AS 'gs_dimensions_8',
(
CASE WHEN(gv.gem_attr_id = '31') THEN gv.value
END
) AS 'gs_setting_type_8',
(
CASE WHEN(gv.gem_attr_id = '32') THEN gv.value
END
) AS 'gs_enhancement_8',
(
CASE WHEN(gv.gem_attr_id = '33') THEN gv.value
END
) AS 'gs_number_of_center_oval_opals_8',
(
CASE WHEN(gv.gem_attr_id = '34') THEN gv.value
END
) AS 'gs_setting_type_9',
(
CASE WHEN(gv.gem_attr_id = '35') THEN gv.value
END
) AS 'gs_minimum_size_9',
(
CASE WHEN(gv.gem_attr_id = '36') THEN gv.value
END
) AS 'gs_minimum_number_of_side_round_topazes_9',
(
CASE WHEN(gv.gem_attr_id = '37') THEN gv.value
END
) AS 'gs_setting_type_10',
(
CASE WHEN(gv.gem_attr_id = '38') THEN gv.value
END
) AS 'gs_minimum_size_10',
(
CASE WHEN(gv.gem_attr_id = '39') THEN gv.value
END
) AS 'gs_number_of_side_round_peridots_10',
(
CASE WHEN(gv.gem_attr_id = '40') THEN gv.value
END
) AS 'gs_minimum_clarity_11',
(
CASE WHEN(gv.gem_attr_id = '41') THEN gv.value
END
) AS 'gs_dimensions_11',
(
CASE WHEN(gv.gem_attr_id = '42') THEN gv.value
END
) AS 'gs_setting_type_11',
(
CASE WHEN(gv.gem_attr_id = '43') THEN gv.value
END
) AS 'gs_enhancement_11',
(
CASE WHEN(gv.gem_attr_id = '44') THEN gv.value
END
) AS 'gs_number_of_side_oval_rubies_11',
(
CASE WHEN(gv.gem_attr_id = '45') THEN gv.value
END
) AS 'gs_minimum_clarity_12',
(
CASE WHEN(gv.gem_attr_id = '46') THEN gv.value
END
) AS 'gs_dimensions_12',
(
CASE WHEN(gv.gem_attr_id = '47') THEN gv.value
END
) AS 'gs_setting_type_12',
(
CASE WHEN(gv.gem_attr_id = '48') THEN gv.value
END
) AS 'gs_enhancement_12',
(
CASE WHEN(gv.gem_attr_id = '49') THEN gv.value
END
) AS 'gs_number_of_center_emerald_sapphires_12',
(
CASE WHEN(gv.gem_attr_id = '50') THEN gv.value
END
) AS 'gs_minimum_number_of_round_tanzanitescolor_12',
(
CASE WHEN(gv.gem_attr_id = '51') THEN gv.value
END
) AS 'gs_setting_type_13',
(
CASE WHEN(gv.gem_attr_id = '52') THEN gv.value
END
) AS 'gs_minimum_size_13',
(
CASE WHEN(gv.gem_attr_id = '53') THEN gv.value
END
) AS 'gs_number_of_center_round_garnets_13',
(
CASE WHEN(gv.gem_attr_id = '54') THEN gv.value
END
) AS 'gs_number_of_round_Saphire_14',
(
CASE WHEN(gv.gem_attr_id = '55') THEN gv.value
END
) AS 'gs_enhancement_14',
(
CASE WHEN(gv.gem_attr_id = '56') THEN gv.value
END
) AS 'gs_minimum_size_14',
(
CASE WHEN(gv.gem_attr_id = '57') THEN gv.value
END
) AS 'gs_minimum_clarity_14',
(
CASE WHEN(gv.gem_attr_id = '58') THEN gv.value
END
) AS 'gs_setting_type_14',
(
CASE WHEN(gv.gem_attr_id = '59') THEN gv.value
END
) AS 'gs_number_of_center_cushion_madeira_citrines_15',
(
CASE WHEN(gv.gem_attr_id = '60') THEN gv.value
END
) AS 'gs_enhancement_15',
(
CASE WHEN(gv.gem_attr_id = '61') THEN gv.value
END
) AS 'gs_minimum_clarity_15',
(
CASE WHEN(gv.gem_attr_id = '62') THEN gv.value
END
) AS 'gs_dimensions_15',
(
CASE WHEN(gv.gem_attr_id = '63') THEN gv.value
END
) AS 'gs_setting_type_15',
(
CASE WHEN(gv.gem_attr_id = '64') THEN gv.value
END
) AS 'gs_minimum_number_of_diamonds_16',
(
CASE WHEN(gv.gem_attr_id = '65') THEN gv.value
END
) AS 'gs_total_carat_weight_16',
(
CASE WHEN(gv.gem_attr_id = '66') THEN gv.value
END
) AS 'gs_average_color_16',
(
CASE WHEN(gv.gem_attr_id = '67') THEN gv.value
END
) AS 'gs_average_clarity_16',
(
CASE WHEN(gv.gem_attr_id = '68') THEN gv.value
END
) AS 'gs_setting_type_16',
(
CASE WHEN(gv.gem_attr_id = '70') THEN gv.value
END
) AS 'gs_Shape_5',
(
CASE WHEN(gv.gem_attr_id = '71') THEN gv.value
END
) AS 'gs_Color_5',
(
CASE WHEN(gv.gem_attr_id = '72') THEN gv.value
END
) AS 'gs_Clarity_5',
(
CASE WHEN(gv.gem_attr_id = '73') THEN gv.value
END
) AS 'gs_Cut_Grade_5',
(
CASE WHEN(gv.gem_attr_id = '74') THEN gv.value
END
) AS 'gs_Polish_5',
(
CASE WHEN(gv.gem_attr_id = '75') THEN gv.value
END
) AS 'gs_Symmetry_5',
(
CASE WHEN(gv.gem_attr_id = '76') THEN gv.value
END
) AS 'gs_Fluor_Intensity_5',
(
CASE WHEN(gv.gem_attr_id = '77') THEN gv.value
END
) AS 'gs_Fluor_Color_5',
(
CASE WHEN(gv.gem_attr_id = '78') THEN gv.value
END
) AS 'gs_Measurements_5',
(
CASE WHEN(gv.gem_attr_id = '79') THEN gv.value
END
) AS 'gs_Fancy_Color_5',
(
CASE WHEN(gv.gem_attr_id = '80') THEN gv.value
END
) AS 'gs_Fancy_Color_Intensity_5',
(
CASE WHEN(gv.gem_attr_id = '81') THEN gv.value
END
) AS 'gs_Fancy_Color_Overtone_5',
(
CASE WHEN(gv.gem_attr_id = '82') THEN gv.value
END
) AS 'gs_Depth_%_5',
(
CASE WHEN(gv.gem_attr_id = '83') THEN gv.value
END
) AS 'gs_Table_%_5',
(
CASE WHEN(gv.gem_attr_id = '84') THEN gv.value
END
) AS 'gs_Girdle_Min_5',
(
CASE WHEN(gv.gem_attr_id = '85') THEN gv.value
END
) AS 'gs_Girdle_Max_5',
(
CASE WHEN(gv.gem_attr_id = '86') THEN gv.value
END
) AS 'gs_Girdle_Per_5',
(
CASE WHEN(gv.gem_attr_id = '87') THEN gv.value
END
) AS 'gs_Girdle_Condition_5',
(
CASE WHEN(gv.gem_attr_id = '88') THEN gv.value
END
) AS 'gs_Culet_Size_5',
(
CASE WHEN(gv.gem_attr_id = '89') THEN gv.value
END
) AS 'gs_Culet_Condition_5',
(
CASE WHEN(gv.gem_attr_id = '90') THEN gv.value
END
) AS 'gs_Crown_Height_5',
(
CASE WHEN(gv.gem_attr_id = '91') THEN gv.value
END
) AS 'gs_Crown_Angle_5',
(
CASE WHEN(gv.gem_attr_id = '92') THEN gv.value
END
) AS 'gs_Pavilion_Depth_5',
(
CASE WHEN(gv.gem_attr_id = '93') THEN gv.value
END
) AS 'gs_Aavilion_Angle_5',
(
CASE WHEN(gv.gem_attr_id = '94') THEN gv.value
END
) AS 'gs_Shade_5',
(
CASE WHEN(gv.gem_attr_id = '95') THEN gv.value
END
) AS 'gs_Carat_Weight_5',
(
CASE WHEN(gv.gem_attr_id = '96') THEN gv.value
END
) AS 'gs_No_of_Stones_5',
(
CASE WHEN(gv.gem_attr_id = '97') THEN gv.value
END
) AS 'gs_Setting_5',
(
CASE WHEN(gv.gem_attr_id = '98') THEN gv.value
END
) AS 'gs_Approx._Dimensions_1',
(
CASE WHEN(gv.gem_attr_id = '99') THEN gv.value
END
) AS 'gs_Approx._Dimensions_2',
(
CASE WHEN(gv.gem_attr_id = '100') THEN gv.value
END
) AS 'gs_Approx._Dimensions_3',
(
CASE WHEN(gv.gem_attr_id = '101') THEN gv.value
END
) AS 'gs_Approx._Dimensions_4',
(
CASE WHEN(gv.gem_attr_id = '102') THEN gv.value
END
) AS 'gs_Approx._Dimensions_6',
(
CASE WHEN(gv.gem_attr_id = '103') THEN gv.value
END
) AS 'gs_Approx._Dimensions_7',
(
CASE WHEN(gv.gem_attr_id = '104') THEN gv.value
END
) AS 'gs_Approx._Dimensions_8',
(
CASE WHEN(gv.gem_attr_id = '105') THEN gv.value
END
) AS 'gs_Approx._Dimensions_9',
(
CASE WHEN(gv.gem_attr_id = '106') THEN gv.value
END
) AS 'gs_Approx._Dimensions_10',
(
CASE WHEN(gv.gem_attr_id = '107') THEN gv.value
END
) AS 'gs_Approx._Dimensions_11',
(
CASE WHEN(gv.gem_attr_id = '108') THEN gv.value
END
) AS 'gs_Approx._Dimensions_12',
(
CASE WHEN(gv.gem_attr_id = '109') THEN gv.value
END
) AS 'gs_Approx._Dimensions_13',
(
CASE WHEN(gv.gem_attr_id = '110') THEN gv.value
END
) AS 'gs_Approx._Dimensions_14',
(
CASE WHEN(gv.gem_attr_id = '111') THEN gv.value
END
) AS 'gs_Approx._Dimensions_15',
(
CASE WHEN(gv.gem_attr_id = '112') THEN gv.value
END
) AS 'gs_Approx._Dimensions_16',
(
CASE WHEN(gv.gem_attr_id = '113') THEN gv.value
END
) AS 'gs_Approx._Total_Carat_Weight_1',
(
CASE WHEN(gv.gem_attr_id = '114') THEN gv.value
END
) AS 'gs_Approx._Total_Carat_Weight_2',
(
CASE WHEN(gv.gem_attr_id = '115') THEN gv.value
END
) AS 'gs_Approx._Total_Carat_Weight_3',
(
CASE WHEN(gv.gem_attr_id = '116') THEN gv.value
END
) AS 'gs_Approx._Total_Carat_Weight_4',
(
CASE WHEN(gv.gem_attr_id = '117') THEN gv.value
END
) AS 'gs_Approx._Total_Carat_Weight_6',
(
CASE WHEN(gv.gem_attr_id = '118') THEN gv.value
END
) AS 'gs_Approx._Total_Carat_Weight_7',
(
CASE WHEN(gv.gem_attr_id = '119') THEN gv.value
END
) AS 'gs_Approx._Total_Carat_Weight_8',
(
CASE WHEN(gv.gem_attr_id = '120') THEN gv.value
END
) AS 'gs_Approx._Total_Carat_Weight_9',
(
CASE WHEN(gv.gem_attr_id = '121') THEN gv.value
END
) AS 'gs_Approx._Total_Carat_Weight_10',
(
CASE WHEN(gv.gem_attr_id = '122') THEN gv.value
END
) AS 'gs_Approx._Total_Carat_Weight_11',
(
CASE WHEN(gv.gem_attr_id = '123') THEN gv.value
END
) AS 'gs_Approx._Total_Carat_Weight_12',
(
CASE WHEN(gv.gem_attr_id = '124') THEN gv.value
END
) AS 'gs_Approx._Total_Carat_Weight_13',
(
CASE WHEN(gv.gem_attr_id = '125') THEN gv.value
END
) AS 'gs_Approx._Total_Carat_Weight_14',
(
CASE WHEN(gv.gem_attr_id = '126') THEN gv.value
END
) AS 'gs_Approx._Total_Carat_Weight_15',
(
CASE WHEN(gv.gem_attr_id = '127') THEN gv.value
END
) AS 'gs_Approx._Total_Carat_Weight_16',
(
CASE WHEN(gv.gem_attr_id = '128') THEN gv.value
END
) AS 'gs_Quality_Grade_1',
(
CASE WHEN(gv.gem_attr_id = '129') THEN gv.value
END
) AS 'gs_Quality_Grade_2',
(
CASE WHEN(gv.gem_attr_id = '130') THEN gv.value
END
) AS 'gs_Quality_Grade_3',
(
CASE WHEN(gv.gem_attr_id = '131') THEN gv.value
END
) AS 'gs_Quality_Grade_4',
(
CASE WHEN(gv.gem_attr_id = '132') THEN gv.value
END
) AS 'gs_Quality_Grade_6',
(
CASE WHEN(gv.gem_attr_id = '133') THEN gv.value
END
) AS 'gs_Quality_Grade_7',
(
CASE WHEN(gv.gem_attr_id = '134') THEN gv.value
END
) AS 'gs_Quality_Grade_8',
(
CASE WHEN(gv.gem_attr_id = '135') THEN gv.value
END
) AS 'gs_Quality_Grade_9',
(
CASE WHEN(gv.gem_attr_id = '136') THEN gv.value
END
) AS 'gs_Quality_Grade_10',
(
CASE WHEN(gv.gem_attr_id = '137') THEN gv.value
END
) AS 'gs_Quality_Grade_11',
(
CASE WHEN(gv.gem_attr_id = '138') THEN gv.value
END
) AS 'gs_Quality_Grade_12',
(
CASE WHEN(gv.gem_attr_id = '139') THEN gv.value
END
) AS 'gs_Quality_Grade_13',
(
CASE WHEN(gv.gem_attr_id = '140') THEN gv.value
END
) AS 'gs_Quality_Grade_14',
(
CASE WHEN(gv.gem_attr_id = '141') THEN gv.value
END
) AS 'gs_Quality_Grade_15',
(
CASE WHEN(gv.gem_attr_id = '142') THEN gv.value
END
) AS 'gs_Quality_Grade_16',
(
CASE WHEN(gv.gem_attr_id = '143') THEN gv.value
END
) AS 'gs_Stone_Breakdown_5'
FROM  products p
LEFT JOIN attribute_value av ON p.group_id = av.group_id
LEFT JOIN attribute attr ON av.attribute_id = attr.attribute_id
LEFT JOIN gemstone_attribute_value gv ON p.gem_group_id = gv.gem_group_id
LEFT JOIN gemstone_attribute gttr ON gv.gem_attr_id = gttr.gemstone_attribute_id
LEFT JOIN gemstone g ON gttr.gemstone_id = g.gemstone_id
LEFT JOIN category pc ON pc.category_id = attr.category_id
GROUP BY p.product_id

视图需要 1.2 秒来创建，但是当我尝试使用查询或 phpmyadmin 访问时。 它需要太多时间来响应。 甚至大多数时候MySQL服务器都消失了。 我必须从终端杀死进程。

Answer 1

如果我们对视图的左连接 j1-j6 进行编号，请考虑以下观察结果，

attribute_value tbl 有一个冗余索引， 添加密钥attribute_id(aval_id(， 应该可能是 添加密钥attribute_id(attribute_id(， 以避免冗余索引并可用于 J2。

属性 TBL 具有category_idvarchar(255( 默认值 '0'， 应该是数据类型category_idint(11( 不为空...， 用于 j6 并为列添加索引。

gemstone_attribute_value TBL 有 添加键gemstone_abbribute_id(g_aval_id(， 这将是一个冗余索引，可能应该被删除。

gemstone_attribute TBL 有gemstone_idvarchar(255( 默认值 '0'， 应该是数据类型gemstone_idint(11( 不为空...， 用于 j5 并为列添加索引，

您的 CASE WHEN 行表示 gv.gem_attr_id = 'nnn' 可以不带单引号，以避免每次比较都进行数据类型转换gem_attr_id因为 DATA 是 INT 数据类型并节省 CPU 周期。

有关其他意见/建议，请查看我的个人资料以进行联系。