MySQL 派生表产生关联索引auto_key0导致SQL非常的慢

相同的SQL在maridb运行0.5秒，在MySQL8.0.26中运行要19秒

官方MySQL在处理子查时，优化器有个优化参数derived_merge，MySQL7开启添加，默认on.很多情况可以自动优化派生表，避免创建临时索引auto_key0和生成临时表数据做全扫描。
测试derived_merge的功能:
图片.png
关闭derived_merge参数后，SQL的执行顺序是：
1、执行子查询，select * from t1
2、把子查询的结果写到临时表 A表
3、回读，应用上层SELECT的WHERE条件 id=1

临时表没有索引，如果临时数据比较大，速度就会非常的慢。

客户的SQL：

(SELECT tb.t_id,
group_concat() ct_ids,
group_concat() main_ct_ids
FROM tt2 tb
WHERE tb.relation_type = ‘0’
GROUP BY tb.t_id),
wbr AS
(SELECT tb.t_id,
group_concat() ct_ids
FROM tt2 tb
WHERE tb.relation_type = ‘100’
GROUP BY tb.t_id),
v AS
(SELECT t.t_id,
group_concat() company_vip_level
FROM cty t
GROUP BY t.t_id),
b AS
(SELECT t.t_id, max(t.valid_flag) blacklist_flag
FROM csm_cct t
WHERE t.audit_status = ‘2’
GROUP BY t.t_id),
t AS
(SELECT t.t_id,
group_concat() tag_ids,
group_concat() tag_values,
group_concat(**) tag_value_descs
FROM tt1 t
GROUP BY t.t_id)
SELECT a.*
FROM (SELECT ***
FROM (SELECT ***
FROM ct c
WHERE 1 = 1
AND c.t_id >= ‘109008007318’
AND c.t_id <= ‘114000008603’ LIMIT 0, 2000) c
LEFT JOIN ctx cx
ON c.t_id = cx.t_id
AND cx.t_id >= ‘109008007318’
AND cx.t_id <= ‘114000008603’
LEFT JOIN br
ON c.t_id = br.t_id
LEFT JOIN wbr
ON c.t_id = wbr.t_id
LEFT JOIN v
ON c.t_id = v.t_id
LEFT JOIN b
ON c.t_id = b.t_id
LEFT JOIN t
ON c.t_id = t.t_id) a
WHERE 1 = 1 LIMIT 0, 99999999 G;

MySQL8.0.26执行计划：

客户的环境的derived_merge=on，不应该出现auto_key0。但是derived_merge 在有些SQL中会出现失效。

derived_merge优化在子查询遇到了如下5种情况的时候，derivedmerge优化也便失效了,便会开始使用临时的派生表，而这个派生表上的索引帮助了派生表关联查询

1. UNION
2. GROUP BY
3. DISTINCT
   4.用户自定义变量

客户的子查询中都是group by,生成的临时数据过滤效果差
解决方案

由于子查询都是通过t_id字段做left join，在子查询中添加t_id条件

(SELECT tb.t_id,
group_concat() ct_ids,
group_concat() main_ct_ids
FROM tt2 tb
WHERE tb.relation_type = ‘0’
AND tb.t_id >= ‘109008007318’
AND tb.t_id <= ‘114000008603’
GROUP BY tb.t_id),
wbr AS
(SELECT tb.t_id,
group_concat() ct_ids
FROM tt2 tb
WHERE tb.relation_type = ‘100’
AND tb.t_id >= ‘109008007318’
AND tb.t_id <= ‘114000008603’
GROUP BY tb.t_id),
v AS
(SELECT t.t_id,
group_concat() company_vip_level
FROM csm_ccy t
where t.t_id >= ‘109008007318’
AND t.t_id <= ‘114000008603’
GROUP BY t.t_id),
b AS
(SELECT t.t_id, max(t.valid_flag) blacklist_flag
FROM csm_cct t
WHERE t.audit_status = ‘2’
and t.t_id >= ‘109008007318’
AND t.t_id <= ‘114000008603’
GROUP BY t.t_id),
t AS
(SELECT t.t_id,
group_concat() tag_ids,
group_concat() tag_values,
group_concat(**) tag_value_descs
FROM tt1 t
where t.t_id >= ‘109008007318’
AND t.t_id <= ‘114000008603’
GROUP BY t.t_id)
SELECT a.*
FROM (SELECT ***
FROM (SELECT ***
FROM ct c
WHERE 1 = 1
AND c.t_id >= ‘109008007318’
AND c.t_id <= ‘114000008603’ LIMIT 0, 2000) c
LEFT JOIN ctx cx
ON c.t_id = cx.t_id
AND cx.t_id >= ‘109008007318’
AND cx.t_id <= ‘114000008603’
LEFT JOIN br
ON c.t_id = br.t_id
LEFT JOIN wbr
ON c.t_id = wbr.t_id
LEFT JOIN v
ON c.t_id = v.t_id
LEFT JOIN b
ON c.t_id = b.t_id
LEFT JOIN t
ON c.t_id = t.t_id) a
WHERE 1 = 1 LIMIT 0, 99999999 G;

查询速度0.4秒