方便更快捷的说明问题,可以按需填写(可删除)
使用环境:
用shardingsphere-proxy 5.5.0对mysql数据库中一个表将时间字段按年分表后,即使执行查询未跨表的sql所花费的时间比直接查询未分表的sql还要长,是什么原因呢?
场景、问题:
- !SHARDING
tables:
spm_prj_security_inspection_schedule_detail:
actualDataNodes: ds_0.spm_prj_security_inspection_schedule_detail_${2022…2026}
tableStrategy:
standard:
shardingColumn: starttime
shardingAlgorithmName: year_range
keyGenerateStrategy:
column: order_id
keyGeneratorName: snowflake
shardingAlgorithms:
year_range:
type: INTERVAL
props:
datetime-pattern: ‘yyyy-MM-dd HH:mm:ss’
datetime-lower: ‘2022-01-01 00:00:00’
datetime-upper: ‘2026-12-31 23:59:59’
sharding-suffix-pattern: yyyy
datetime-interval-amount: 1
datetime-interval-unit: YEARS
已进行操作:
执行sql语句:select id from spm_prj_security_inspection_schedule_detail where starttime<‘2026’ and starttime>‘2025’;
使用shardingsphere-proxy时实际执行的sql:select id from spm_prj_security_inspection_schedule_detail_2025 where starttime<‘2026’ and starttime>‘2025’;
执行时长:4秒
不使用shardingsphere-proxy直接在mysql执行select id from spm_prj_security_inspection_schedule_detail where starttime<‘2026’ and starttime>‘2025’;
只需要0.44秒