宜宾网站建设费用,制作简单的网页代码,网站搭建开发,企业网项目建设实践相同的SQL在maridb运行0.5秒#xff0c;在MySQL8.0.26中运行要19秒
官方MySQL在处理子查时#xff0c;优化器有个优化参数derived_merge#xff0c;MySQL7开启添加#xff0c;默认on.很多情况可以自动优化派生表#xff0c;避免创建临时索引auto_key0和生成临时表数据做…相同的SQL在maridb运行0.5秒在MySQL8.0.26中运行要19秒
官方MySQL在处理子查时优化器有个优化参数derived_mergeMySQL7开启添加默认on.很多情况可以自动优化派生表避免创建临时索引auto_key0和生成临时表数据做全扫描。 测试derived_merge的功能: 图片.png 关闭derived_merge参数后SQL的执行顺序是 1、执行子查询select * from t1 2、把子查询的结果写到临时表 A表 3、回读应用上层SELECT的WHERE条件 id1
临时表没有索引如果临时数据比较大速度就会非常的慢。
客户的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_mergeon不应该出现auto_key0。但是derived_merge 在有些SQL中会出现失效。
derived_merge优化在子查询遇到了如下5种情况的时候derivedmerge优化也便失效了,便会开始使用临时的派生表而这个派生表上的索引帮助了派生表关联查询
UNIONGROUP BYDISTINCT 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秒
