count优化问题

🪐 TiDB 技术问题 性能调优
,
1

【 TiDB 使用环境】生产环境 /测试/ Poc
【 TiDB 版本】
【复现路径】做过哪些操作出现的问题
【遇到的问题：问题现象及影响】
【资源配置】进入到 TiDB Dashboard -集群信息 (Cluster Info) -主机(Hosts) 截图此页面
【附件：截图/日志/监控】

SELECT
model_group.id AS model_group_id,
model_group.unique_identification AS model_group_unique_identification,
model_group.name AS model_group_name,
model.id AS model_id,
model.name AS model_name,
model.sort_num AS sort_num,
model.model_type AS model_type,
model.unique_identification AS model_unique_identification,
model.model_icon AS model_icon – ,
– COUNT( instance.id ) AS instance_count
FROM
model_group
LEFT JOIN model ON model_group.id = model.model_group_id
LEFT JOIN instance ON model.id = instance.model_id
WHERE
model_group.model_group_type = 0
GROUP BY
model_group.id,
model.id
ORDER BY

model_group.id,
model.id

上面这个sql的COUNT( instance.id ) AS instance_count一加上就慢，这个可以怎么优化？

2

贴一下两个sql的执行计划

3

统计适合tiflash

4
Sort_11 1.00 root cmdb.model_group.id, cmdb.model.id
└─Projection_13 1.00 root cmdb.model_group.id, cmdb.model_group.unique_identification, cmdb.model_group.name, cmdb.model.id, cmdb.model.name, cmdb.model.sort_num, cmdb.model.model_type, cmdb.model.unique_identification, cmdb.model.model_icon
└─HashAgg_14 1.00 root group by:cmdb.model.id, cmdb.model_group.id, funcs:firstrow(cmdb.model_group.id)->cmdb.model_group.id, funcs:firstrow(cmdb.model_group.name)->cmdb.model_group.name, funcs:firstrow(cmdb.model_group.unique_identification)->cmdb.model_group.unique_identification, funcs:firstrow(cmdb.model.id)->cmdb.model.id, funcs:firstrow(cmdb.model.name)->cmdb.model.name, funcs:firstrow(cmdb.model.model_icon)->cmdb.model.model_icon, funcs:firstrow(cmdb.model.unique_identification)->cmdb.model.unique_identification, funcs:firstrow(cmdb.model.sort_num)->cmdb.model.sort_num, funcs:firstrow(cmdb.model.model_type)->cmdb.model.model_type
└─IndexJoin_19 0.02 root left outer join, inner:IndexLookUp_18, outer key:cmdb.model_group.id, inner key:cmdb.model.model_group_id, equal cond:eq(cmdb.model_group.id, cmdb.model.model_group_id)
├─TableReader_37(Build) 0.01 root data:Selection_36
│ └─Selection_36 0.01 cop[tikv] eq(cmdb.model_group.model_group_type, 0)
│ └─TableFullScan_35 15.00 cop[tikv] table:model_group keep order:false, stats:pseudo
└─IndexLookUp_18(Probe) 0.02 root
├─IndexRangeScan_16(Build) 0.02 cop[tikv] table:model, index:idx_model_group_id(model_group_id) range: decided by [eq(cmdb.model.model_group_id, cmdb.model_group.id)], keep order:false
└─TableRowIDScan_17(Probe) 0.02 cop[tikv] table:model keep order:false
5

统计计划过期了 analyze table

6

执行下explain analyze 你的sql，
加count跟不加count的两个执行计划都发下

7

是不是没加COUNT( instance.id ) AS instance_count的sql，根本就没有和instance表关联啊？

1 个赞
8

加count的

Sort_12 1.00 root cmdb.model_group.id, cmdb.model.id
└─Projection_14 1.00 root cmdb.model_group.id, cmdb.model_group.unique_identification, cmdb.model_group.name, cmdb.model.id, cmdb.model.name, cmdb.model.sort_num, cmdb.model.model_type, cmdb.model.unique_identification, cmdb.model.model_icon, Column#36
└─HashAgg_15 1.00 root group by:cmdb.model.id, cmdb.model_group.id, funcs:count(cmdb.instance.id)->Column#36, funcs:firstrow(cmdb.model_group.id)->cmdb.model_group.id, funcs:firstrow(cmdb.model_group.name)->cmdb.model_group.name, funcs:firstrow(cmdb.model_group.unique_identification)->cmdb.model_group.unique_identification, funcs:firstrow(cmdb.model.id)->cmdb.model.id, funcs:firstrow(cmdb.model.name)->cmdb.model.name, funcs:firstrow(cmdb.model.model_icon)->cmdb.model.model_icon, funcs:firstrow(cmdb.model.unique_identification)->cmdb.model.unique_identification, funcs:firstrow(cmdb.model.sort_num)->cmdb.model.sort_num, funcs:firstrow(cmdb.model.model_type)->cmdb.model.model_type
└─IndexHashJoin_20 75.46 root left outer join, inner:IndexReader_17, outer key:cmdb.model.id, inner key:cmdb.instance.model_id, equal cond:eq(cmdb.model.id, cmdb.instance.model_id)
├─IndexJoin_31(Build) 0.02 root left outer join, inner:IndexLookUp_30, outer key:cmdb.model_group.id, inner key:cmdb.model.model_group_id, equal cond:eq(cmdb.model_group.id, cmdb.model.model_group_id)
│ ├─TableReader_49(Build) 0.01 root data:Selection_48
│ │ └─Selection_48 0.01 cop[tikv] eq(cmdb.model_group.model_group_type, 0)
│ │ └─TableFullScan_47 15.00 cop[tikv] table:model_group keep order:false, stats:pseudo
│ └─IndexLookUp_30(Probe) 0.02 root
│ ├─IndexRangeScan_28(Build) 0.02 cop[tikv] table:model, index:idx_model_group_id(model_group_id) range: decided by [eq(cmdb.model.model_group_id, cmdb.model_group.id)], keep order:false
│ └─TableRowIDScan_29(Probe) 0.02 cop[tikv] table:model keep order:false
└─IndexReader_17(Probe) 75.46 root index:IndexRangeScan_16
└─IndexRangeScan_16 75.46 cop[tikv] table:instance, index:uk_model_id_name(model_id, name) range: decided by [eq(cmdb.instance.model_id, cmdb.model.id)], keep order:false

不加count的

Sort_11 1.00 root cmdb.model_group.id, cmdb.model.id
└─Projection_13 1.00 root cmdb.model_group.id, cmdb.model_group.unique_identification, cmdb.model_group.name, cmdb.model.id, cmdb.model.name, cmdb.model.sort_num, cmdb.model.model_type, cmdb.model.unique_identification, cmdb.model.model_icon
└─HashAgg_14 1.00 root group by:cmdb.model.id, cmdb.model_group.id, funcs:firstrow(cmdb.model_group.id)->cmdb.model_group.id, funcs:firstrow(cmdb.model_group.name)->cmdb.model_group.name, funcs:firstrow(cmdb.model_group.unique_identification)->cmdb.model_group.unique_identification, funcs:firstrow(cmdb.model.id)->cmdb.model.id, funcs:firstrow(cmdb.model.name)->cmdb.model.name, funcs:firstrow(cmdb.model.model_icon)->cmdb.model.model_icon, funcs:firstrow(cmdb.model.unique_identification)->cmdb.model.unique_identification, funcs:firstrow(cmdb.model.sort_num)->cmdb.model.sort_num, funcs:firstrow(cmdb.model.model_type)->cmdb.model.model_type
└─IndexJoin_19 0.02 root left outer join, inner:IndexLookUp_18, outer key:cmdb.model_group.id, inner key:cmdb.model.model_group_id, equal cond:eq(cmdb.model_group.id, cmdb.model.model_group_id)
├─TableReader_37(Build) 0.01 root data:Selection_36
│ └─Selection_36 0.01 cop[tikv] eq(cmdb.model_group.model_group_type, 0)
│ └─TableFullScan_35 15.00 cop[tikv] table:model_group keep order:false, stats:pseudo
└─IndexLookUp_18(Probe) 0.02 root
├─IndexRangeScan_16(Build) 0.02 cop[tikv] table:model, index:idx_model_group_id(model_group_id) range: decided by [eq(cmdb.model.model_group_id, cmdb.model_group.id)], keep order:false
└─TableRowIDScan_17(Probe) 0.02 cop[tikv] table:model keep order:false
9

加和没加的都补充发了

10

建议换一种放执行计划的方式，这样太难看了

11

你的执行计划没加analyze，所以看不到真实的数据数量，根据执行计划的预估条数，能发现相关的表的统计信息可能过旧了。导致优化器不能选择最优的执行计划。
根据执行计划看，不加count的sql确实没有和instance表关联，少了一次关联，所以更快

12

你没发现不加count的执行计划根本就没有instance这个表吗？你少查个表，肯定要快点吧。。。。

1 个赞
13

加上count后，查询的表完全不一样了，查了instance