【 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 的 有啥好的优化建议吗？
就是上面分享的那几个
直接上TiFlash，简单粗暴
这么全面的文章，谢谢
目前没有部署tiflash实例
建议先explain看执行计划，如果统计信息不准，先搜一把统计信息。通过执行计划能够看出慢在哪里？然后再针对性分析。
看您这个sql其实不复杂，只是select子句部分多了很多的case when与计算字段，另外就是存在group 与order子句，看一下执行计划中评估走到的索引与数据量的情况，配合建立适合的索引应该能有效果。
此话题已在最后回复的 60 天后被自动关闭。不再允许新回复。