用shardingsphere-proxy做分表,查询sql(未跨表)的时间比使用原始mysql查询时间要长很多是什么原因

方便更快捷的说明问题,可以按需填写(可删除)

使用环境:

用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秒

现状:

京ICP备2021015875号