一文搞懂mysql两大重要存储引擎innodb和myisam区别-业务优化实战mysql5.7

业务场景

 

mysql5.7 版本，一个一百万数据表 a， 一个3千多的维表 t 一个两百多的维表补充表 s，业务逻辑如下：

1. 维表t和维表s关联 ，拿到在表t且不在表s的数据，结果记为b
2. 大表a和b关联 ，且要筛选a表的数据时间区间在一年内
3. 最后基于第二步结果 对大a表发现机构字段、b表指标编号、违法行为为维度 做分组聚合 求违法数

sql如下：

 

​

select rpad(substr(fxjg,1,4),10,'0') fxjg,a.zbbh,a.zbmc,sum(wfnum) wfnum
from
(
select a.tjsj,a.fxjg,a.wfnum,b1.zbbh,b1.zbmc 
from bcht_bd_dsjmh.app_vio_gdsd_yzwf_hour -- 报表表名
a
join (
select t.zbbh,t.zbmc,t.wfxw from base_report_wf as t
left join (select zbbh,zbmc,wfxw from base_report_wf_user where userid = '8a948b446f82e6ed016f82ea6f760000'  and reportid = 'app_vio_gdsd_yzwf_hour' ) as s
on t.zbbh = s.zbbh and t.wfxw = s.wfxw 
where t.reportid = 'app_vio_gdsd_yzwf_hour'
and s.wfxw is null
)b on a.zbbh like CONCAT('%',b.zbbh,'%') and a.wfxw=b.wfxw
-- join 
-- (select zbbh ,wfxw,zbmc from base_report_wf a where  a.reportid = -'app_vio_gdsd_yzwf_hour'
-- and  not exists (select 1 from base_report_wf_user b where --b.userid='8a948b446f82e6ed016f82ea6f760000' 
-- and b.reportid = 'app_vio_gdsd_yzwf_hour' and a.zbbh=b.zbbh and a.wfxw=b.wfxw)
-- )b1 on instr(a.zbbh,b1.zbbh)>0 and a.wfxw=b1.wfxw
where a.day_id>='20130101' and a.day_id<='20210130' 
)a
group by rpad(substr(fxjg,1,4),10,'0') ,zbbh,zbmc

[点击并拖拽以移动]
​

问题

 

此sql执行在测试库需要12s，在生产库十几min，觉得有些慢，毕竟一百万的数据不是特别多，而且客户经常会查这个报表sql，一查就是1年的数据。而服务接口设置的时间是30s，之前查询总是超时错误。

看了之前生产库的执行计划

最后一步的 rows估算的大的离谱，好几分钟都查不出来，执行计划肯定是有问题得了。

 

解决

 

更新统计信息

先试了下重新更新下大表的统计信息 ，没有效果

因为对trace不熟，先手工测试 大表只关联一个维表a的情况，发现执行计划没问题

 

sql改写

然后测试把这个子查询

-- select t.zbbh,t.zbmc,t.wfxw from base_report_wf as t
-- left join (select zbbh,zbmc,wfxw from base_report_wf_user where userid = '8a948b446f82e6ed016f82ea6f760000'  and reportid = 'app_vio_gdsd_yzwf_hour' ) as s
-- on t.zbbh = s.zbbh and t.wfxw = s.wfxw 
-- where t.reportid = 'app_vio_gdsd_yzwf_hour'
-- and s.wfxw is null
-- )b

拆开，写成和大表a并列 的两个join ，执行计划依然有问题

最后 想到了用not exists 改写子查询测试下，因为not exists 是 ANTI JOIN ，属于部分连接的反义 ，与join 或者left join的enqu 等值连接不同，

希望能够给执行计划带来变化，能够产生解决问题的契机

 

初步效果

果然 执行计划发生了变化，最后一步的rows变成了百万级别，符合预期，生产库也能在20s左右查询出来了。

 

继续优化

 

但是这个速度还是不太理想，找了一些库级别的调优参数，但都是基于OLTP型业务的，对批量读写报表查询场景感觉没什么用。

最后想到了 之前浏览文档发现的mysql 存储引擎参数 engine，它可以对表级的engine设置。于是在测试库分别设置测试了一下速度

alter table bcht_bd_dsjmh.app_vio_gdsd_yzwf_hour engine=myisam;

 

 

alter table bcht_bd_dsjmh.app_vio_gdsd_yzwf_hour engine=innodb;

其他不变的情况下 ，只对大表A进行engine变更为myisam，查询速度提高了一倍.

 

思考

myisam虽然不支持外键和事物 ，但是更适合批量读写的OLAP场景。可以根据业务情况合适选择