针对慢sql的优化方向

【 TiDB 使用环境】生产环境 /测试/ Poc
【 TiDB 版本】6.0
【复现路径】做过哪些操作出现的问题
【遇到的问题:问题现象及影响】针对sql的查询慢,和采集数据插入某张表的速度很慢,主要在sql方面有哪些优化方向呢?还有就是关于算子的效率的优先级,希望各位大佬能够排个序,就是我现在知道了各种算子为什么会出现在那里,但是我不知道在针对数据量大小不同的情况下,例如有表连接,什么算子是最优的,新手数据库小白,希望各位大佬指点
【资源配置】
【附件:截图/日志/监控】

可以参考社区大佬写的SOP

还有官方文档中也给出了专栏讲解
https://docs.pingcap.com/zh/tidb/stable/sql-tuning-overview

不过,最好还是发个例子出来,case by case 的拆解分析一下。

select
orgId,
business_date,
medical_type,
sum(pacs_total) as pacs_total,
sum(pacs_amount) as pacs_amount,
sum(ct_total) as ct_total,
sum(ct_amount) as ct_amount,
sum(es_total) as es_total,
sum(es_amount) as es_amount,
sum(mr_total) as mr_total,
sum(mr_amount) as mr_amount,
sum(us_total) as us_total,
sum(us_amount) as us_amount,
sum(fs_total) as fs_total,
sum(fs_amount) as fs_amount,
sum(xd_total) as xd_total,
sum(xd_amount) as xd_amount,
sum(qt_total) as qt_total,
sum(qt_amount) as qt_amount
from
(select
pacs.MEDICAL_INSTITUT_CODE as orgId,
date(pacs.REPORT_DATE) as business_date,
pacs.VISIT_SIGN as medical_type,
count(pacs.id) as pacs_total,
0.0000 as pacs_amount,
count(case when pacs.JCLB = ‘001’ then pacs.id else null end) as ct_total,
0.0000 as ct_amount,
count(case when pacs.JCLB = ‘002’ then pacs.id else null end) as es_total,
0.0000 as es_amount,
count(case when pacs.JCLB = ‘003’ then pacs.id else null end) as mr_total,
0.0000 as mr_amount,
count(case when pacs.JCLB = ‘004’ then pacs.id else null end) as us_total,
0.0000 as us_amount,
count(case when pacs.JCLB = ‘005’ then pacs.id else null end) as fs_total,
0.0000 as fs_amount,
count(case when pacs.JCLB = ‘006’ then pacs.id else null end) as xd_total,
0.0000 as xd_amount,
count(case when pacs.JCLB not in (‘001’,‘002’,‘003’,‘004’,‘005’,‘006’) then pacs.id else null end) as qt_total,
0.0000 as qt_amount
from yiliao.TB_RIS_REPORT pacs use index(REPORT_DATE)
where pacs.REPORT_DATE >= ‘2021-01-01’
and pacs.REPORT_DATE < ‘2021-01-07 23:59:59’
group by pacs.MEDICAL_INSTITUT_CODE,pacs.VISIT_SIGN,date(pacs.APPLY_TIME)
union all
select
orgId,
business_date,
medical_type,
0 as pacs_total,
pacs_amount,
0 as ct_total,
ct_amount,
0 as es_total,
es_amount,
0 as mr_total,
mr_amount,
0 as us_total,
us_amount,
0 as fs_total,
fs_amount,
0 as xd_total,
xd_amount,
0 as qt_total,
qt_amount
from (
select
mx.MEDICAL_INSTITUT_CODE as orgId,
mx.FEE_DETAIL_TIME as business_date,
1 as medical_type,
sum(mx.ITEM_AMOUNT) as pacs_amount,
sum(case when mx.DETAIL_FEE_SUB_TYPE = ‘0606’ then mx.ITEM_AMOUNT else 0 end) as ct_amount,
sum(case when mx.DETAIL_FEE_SUB_TYPE = ‘0605’ then mx.ITEM_AMOUNT else 0 end) as es_amount,
sum(case when mx.DETAIL_FEE_SUB_TYPE = ‘0608’ then mx.ITEM_AMOUNT else 0 end) as mr_amount,
sum(case when mx.DETAIL_FEE_SUB_TYPE = ‘0601’ then mx.ITEM_AMOUNT else 0 end) as us_amount,
sum(case when mx.DETAIL_FEE_SUB_TYPE = ‘0602’ then mx.ITEM_AMOUNT else 0 end) as fs_amount,
sum(case when mx.DETAIL_FEE_SUB_TYPE = ‘0603’ then mx.ITEM_AMOUNT else 0 end) as xd_amount,
sum(case when ifnull(mx.DETAIL_FEE_SUB_TYPE,‘0609’) in (‘0604’,‘0607’,‘0609’) then mx.ITEM_AMOUNT else 0 end) as qt_amount
from
(
SELECT ITEM_AMOUNT,DETAIL_FEE_SUB_TYPE, MEDICAL_INSTITUT_CODE,VISITING_SERIAL_NUMBER,date(FEE_DETAIL_TIME) as FEE_DETAIL_TIME from
yiliao.TB_CIS_OP_FEE_DETAIL mx use index(FEE_DETAIL_TIME)
where mx.DETAIL_FEE_TYPE = ‘06’ and mx.FEE_DETAIL_TIME>=‘2021-01-01’ and mx.FEE_DETAIL_TIME< ‘2021-01-07 23:59:59’
) mx
group by mx.MEDICAL_INSTITUT_CODE,mx.FEE_DETAIL_TIME
union all
select
mx.MEDICAL_INSTITUT_CODE as orgId,
mx.FEE_DETAIL_TIME as business_date,
2 as medical_type,
sum(mx.ITEM_AMOUNT) as pacs_amount,
sum(case when mx.DETAIL_FEE_SUB_TYPE = ‘0606’ then mx.ITEM_AMOUNT else 0 end) as ct_amount,
sum(case when mx.DETAIL_FEE_SUB_TYPE = ‘0605’ then mx.ITEM_AMOUNT else 0 end) as es_amount,
sum(case when mx.DETAIL_FEE_SUB_TYPE = ‘0608’ then mx.ITEM_AMOUNT else 0 end) as mr_amount,
sum(case when mx.DETAIL_FEE_SUB_TYPE = ‘0601’ then mx.ITEM_AMOUNT else 0 end) as us_amount,
sum(case when mx.DETAIL_FEE_SUB_TYPE = ‘0602’ then mx.ITEM_AMOUNT else 0 end) as fs_amount,
sum(case when mx.DETAIL_FEE_SUB_TYPE = ‘0603’ then mx.ITEM_AMOUNT else 0 end) as xd_amount,
sum(case when ifnull(mx.DETAIL_FEE_SUB_TYPE,‘0609’) in (‘0604’,‘0607’,‘0609’) then mx.ITEM_AMOUNT else 0 end) as qt_amount
from
(SELECT ITEM_AMOUNT,DETAIL_FEE_SUB_TYPE, MEDICAL_INSTITUT_CODE,VISITING_SERIAL_NUMBER,date(FEE_DETAIL_TIME) as FEE_DETAIL_TIME from
yiliao.TB_CIS_INHOS_FEE_DETAIL mx use index(FEE_DETAIL_TIME) where mx.DETAIL_FEE_TYPE = ‘06’ and mx.FEE_DETAIL_TIME>=‘2021-01-01’ and mx.FEE_DETAIL_TIME< ‘2021-01-07 23:59:59’
) mx
group by mx.MEDICAL_INSTITUT_CODE,mx.FEE_DETAIL_TIME
) mx) mx
group by orgId,business_date,medical_type

这个是一个例子,我想知道优化方向

1、看到你使用了很多hit的写法,可以先explain analyze看看有没有命中
2、这种大语句,一般我都拆分逐个查询分析优化,就是通过看explain analyze,看看有没有可以优化的地方
3、一般这种大语句都是可以考虑从业务层面去优化的
4、看你这边版本到6了,可以使用临时表来解决一些性能问题了
5、这种的,如果最后还是没有头绪,建议看一下大佬写的SOP,逐个去分析

谢谢大佬

有关于sop的文章的吗?我学习一下

像这种 group by order by 的 有啥好的优化建议吗?

就是上面分享的那几个

:sunglasses:直接上TiFlash,简单粗暴

这么全面的文章,谢谢

目前没有部署tiflash实例

:handshake: :handshake: :handshake: