缓存和性能问题

【 TiDB 使用环境】
5.4 ,3个tikv,

【概述】
select * from gpsdata where deviceSn = ‘60038675305’ and occurtime < ‘2022-01-22 09:56:43’ order by occurtime desc limit 1;

  1. 在mysql5.7执行上面语句,用时:第一次0.011秒左右,第二0.0092秒左右
  2. 在tidb执行上面语句,用时:每次0.650秒左右

备注: mysql是单点环境,硬件比tidb单点的配置还差
表数据有300W行,没启用tiflash

【背景】 做过哪些操作
优化了innodb内存相关参数,其他组件和系统参数都是默认的

【问题】 当前遇到的问题

  1. mysql同一个语句第2次有缓存作用,速度会快些,tidb怎么实现相关的功能或参数调整优化
  2. 查询速度tidb要慢很多,特别是加了order by,不加0.220左右

可能耗时在加了 order by 后的 TopN 这一步,能否发一下 explain analyze 结果看看。
tidb 支持 coprocessor cache 可以缓存下推到 tikv 的结果集
https://docs.pingcap.com/zh/tidb/stable/coprocessor-cache

tidb也是有block cache的。
需要看看tidb的 explain analyze。

trace SQL看下结果,哪步比较耗时

±---------------------------------±--------±--------±----------±--------------------------------------------------------------±----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------±-----------------------------------------------------------±--------±-----+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
±---------------------------------±--------±--------±----------±--------------------------------------------------------------±----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------±-----------------------------------------------------------±--------±-----+
| Limit_12 | 1.00 | 1 | root | | time:527.8ms, loops:2 | offset:0, count:1 | N/A | N/A |
| └─IndexLookUp_28 | 1.00 | 1 | root | | time:527.8ms, loops:1, index_task: {total_time: 525.1ms, fetch_handle: 148.7ms, build: 372.9ms, wait: 3.44ms}, table_task: {total_time: 742.8ms, num: 109, concurrency: 5} | | 19.5 MB | N/A |
| ├─IndexRangeScan_25(Build) | 59.34 | 2093600 | cop[tikv] | table:eplate_gpsdata, index:adas_gpsdata_occurtime(occurtime) | time:4.04ms, loops:1942, cop_task: {num: 3, max: 1.04ms, min: 984.5µs, avg: 1ms, p95: 1.04ms, rpc_num: 3, rpc_time: 2.94ms, copr_cache_hit_ratio: 1.00}, tikv_task:{proc max:513ms, min:77ms, p80:513ms, p95:513ms, iters:2058, tasks:3} | range:[-inf,2022-01-22 09:56:43), keep order:true | N/A | N/A |
| └─Selection_27(Probe) | 1.00 | 147 | cop[tikv] | | time:80ms, loops:110, cop_task: {num: 115, max: 15.4ms, min: 297.3µs, avg: 784.9µs, p95: 2.78ms, max_proc_keys: 1856, p95_proc_keys: 1024, tot_proc: 24ms, tot_wait: 4ms, rpc_num: 115, rpc_time: 88.6ms, copr_cache_hit_ratio: 0.86}, tikv_task:{proc max:54ms, min:0s, p80:47ms, p95:51ms, iters:2442, tasks:115}, scan_detail: {total_process_keys: 9307, total_process_keys_size: 1623617, total_keys: 18679, rocksdb: {delete_skipped_count: 0, key_skipped_count: 18093, block: {cache_hit_count: 845, read_count: 0, read_byte: 0 Bytes}}} | eq(eplate.eplate_gpsdata.devicesn, “89860620160038675305”) | N/A | N/A |
| └─TableRowIDScan_26 | 59.34 | 1942528 | cop[tikv] | table:eplate_gpsdata | tikv_task:{proc max:51ms, min:0s, p80:45ms, p95:49ms, iters:2442, tasks:115} | keep order:false | N/A | N/A |
±---------------------------------±--------±--------±----------±--------------------------------------------------------------±----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------±-----------------------------------------------------------±--------±-----+
5 rows in set (0.53 sec)

IndexRangeScan 过滤后还有大量结果集需要回表,时间主要消耗在 IndexLookUp 这一步,可以在 occurtime,deviceSn 上加个组合索引,减少回表

1 个赞

我主要是想了解用了 order by后,性能比mysql区别特别大,有没有参数可调整或系统改善下,也是比较普通的语句

从上面的执行计划看,加不加 order by 影响不大,没有进行额外的排序,而是利用到了索引的有序性

不带order by,0.018秒,我上面描述不带orderby写错了

‘Limit_9’, ‘1.00’, ‘1’, ‘root’, ‘’, ‘time:4.01ms, loops:2’, ‘offset:0, count:1’, ‘N/A’, ‘N/A’
‘└─IndexLookUp_21’, ‘1.00’, ‘1’, ‘root’, ‘’, ‘time:4.01ms, loops:1, index_task: {total_time: 3.89ms, fetch_handle: 1.85ms, build: 7.66µs, wait: 2.03ms}, table_task: {total_time: 13.8ms, num: 8, concurrency: 5}’, ‘’, ‘214.9 KB’, ‘N/A’
’ ├─IndexRangeScan_14(Build)’, ‘1.00’, ‘19729’, ‘cop[tikv]’, ‘table:s, index:adas_gpsdata_deviceSn(deviceSn)’, ‘time:1.49ms, loops:9, cop_task: {num: 1, max: 1.32ms, proc_keys: 0, rpc_num: 1, rpc_time: 1.3ms, copr_cache_hit_ratio: 1.00}, tikv_task:{time:11ms, loops:24}’, ‘range:[“89860621260050021369”,“89860621260050021369”], keep order:false’, ‘N/A’, ‘N/A’
’ └─Limit_20(Probe)’, ‘1.00’, ‘4’, ‘cop[tikv]’, ‘’, ‘time:9.52ms, loops:12, cop_task: {num: 4, max: 1.94ms, min: 1.65ms, avg: 1.82ms, p95: 1.94ms, max_proc_keys: 32, p95_proc_keys: 32, rpc_num: 4, rpc_time: 7.23ms, copr_cache_hit_ratio: 0.00}, tikv_task:{proc max:0s, min:0s, p80:0s, p95:0s, iters:4, tasks:4}, scan_detail: {total_process_keys: 128, total_process_keys_size: 20506, total_keys: 256, rocksdb: {delete_skipped_count: 0, key_skipped_count: 128, block: {cache_hit_count: 274, read_count: 0, read_byte: 0 Bytes}}}’, ‘offset:0, count:1’, ‘N/A’, ‘N/A’
’ └─Selection_16’, ‘1.00’, ‘128’, ‘cop[tikv]’, ‘’, ‘tikv_task:{proc max:0s, min:0s, p80:0s, p95:0s, iters:4, tasks:4}’, ‘lt(eplate.eplate_gpsdata.occurtime, 2022-03-07 17:35:54.000000)’, ‘N/A’, ‘N/A’
’ └─TableRowIDScan_15’, ‘1.00’, ‘128’, ‘cop[tikv]’, ‘table:s’, ‘tikv_task:{proc max:0s, min:0s, p80:0s, p95:0s, iters:4, tasks:4}’, ‘keep order:false’, ‘N/A’, ‘N/A’

上面没有 order by 的 IndexRangeScan 只扫描 19729,相比加了 order by 需要扫描 2093600,可能跟数据分布有关,后者按照 occurtime 排序后返回的数据需要回表经过 deviceSn 条件过滤,当找不到匹配条件的记录,需要扫描更多的记录才能命中;这种情况还是建议加组合索引过滤后再回表

数据是分布在不同的地方,不是连续的,我去掉deviceSn条件带 order by 很正常,但有deviceSn条件和order by 时,mysql确实不是只快一丁点,表结构和索引一致,mysql服务器配置还低。tidb在sql查询上看看能不能去优化,不过还是非常感谢您的解答,再次谢谢

另外请教下,不考虑并发的情况下,单独一个SQL语句跑,PD的数量对其执行效率与关吗?

PD 的数量只是保证高可用,跟性能没啥关系

此话题已在最后回复的 1 分钟后被自动关闭。不再允许新回复。