mysql - join mysql 中未使用索引

Question

我正在尝试提高连接两个表的某些查询的性能，我已将 SKUConfig 列的名为 skuconf 的索引添加到表 gio_A_Master_SL_temp 中，并为表中的 sku_config 列添加了名为 skuconf 的索引gio_cat_hist_SL_temp，但我可以通过解释命令看到索引没有在表的联合中使用(仅在创建派生表时)

这是查询

EXPLAIN SELECT
a.Country,
a.MonthNum,
a.CatBP,
a.Cat1,
a.Cat2,
a.Cat3,
sum(a.SKU_sold) as SKU_sold,
sum(a.items) AS items,
sum(a.Revenue) AS revenue,
sum(b.SKU_visible) AS SKU_visible

FROM

        (SELECT
            Country,
            MonthNum,
            SKUConfig,
            CatBP,
            Cat1,
            Cat2,
            Cat3,
            count(DISTINCT SKUConfig) AS SKU_sold,
            sum(OrderAfterCan) AS items,
            sum(NMV) AS Revenue
        FROM
            gio_A_Master_SL_temp

        GROUP BY
            SKUConfig) a


        LEFT JOIN 

        (SELECT
            sku_config,

            count(*) AS SKU_Visible
        FROM
            gio_cat_hist_SL_temp

        GROUP BY
            sku_config) b ON a.SKUConfig = b.sku_config

GROUP BY a.CatBP, a.Cat1, a.Cat2, a.Cat3
;

这是解释结果的图像

EXPLAIN RESULT

我添加索引的列是 varchar(28) 类型，并且它们都具有与 SHOW FULL COLUMN 命令显示的相同的排序规则

SKUconfig   varchar(28) utf8_general_ci YES MUL         select,insert,update,references
sku_config  varchar(28) utf8_general_ci YES MUL         select,insert,update,references 

我不知道为什么联接没有使用索引，有什么方法可以使索引适用于表的联接？也非常欢迎任何改进查询的建议。提前致谢。

Answer 1

试试这个:

    (SELECT
        a.Country,
        a.MonthNum,
        a.SKUConfig,
        a.CatBP,
        a.Cat1,
        a.Cat2,
        a.Cat3,
        count(DISTINCT a.SKUConfig) AS SKU_sold,
        sum(a.OrderAfterCan) AS items,
        sum(a.NMV) AS Revenue,
        count(b.SKUConfig) AS SKU_Visible
    FROM
        gio_A_Master_SL_temp a
    LEFT JOIN 
        gio_cat_hist_SL_temp b
      ON 
       a.SKUConfig = b.sku_config
    GROUP BY
        SKUConfig
  ) combine_result