SQL优化扫描total_keys数的奇怪问题。

a398058068 · 2021 年9 月 17 日 07:58

【 TiDB 使用环境】
v5.2.1

同样的表结构total_keys差异巨大，导致执行时间大概几百ms，而且执行时间极其不稳定。

看了文档说是旧版本过多，疑问是 gc是10分钟一回收，然后看了这张表的update 15分钟的次数是500多，并且是按照id更新的。怎么会出现100W的旧版本。

建表语句，数据量大概200W

CREATE TABLE `device` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `access` varchar(20) DEFAULT NULL,
  `aid` int(11) DEFAULT NULL,
  `apk_first_install_time` bigint(20) DEFAULT NULL,
  `app_name` varchar(20) DEFAULT NULL,
  `app_package` varchar(255) DEFAULT NULL,
  `app_type` varchar(20) DEFAULT NULL,
  `app_version` varchar(20) DEFAULT NULL,
  `appkey` varchar(64) DEFAULT NULL,
  `apptheme` varchar(20) DEFAULT NULL,
  `ban_at` timestamp NULL DEFAULT NULL,
  `ban_count` int(11) DEFAULT NULL,
  `bind_phone` varchar(11) DEFAULT NULL,
  `cdid` varchar(64) DEFAULT NULL,
  `channel` varchar(40) DEFAULT NULL,
  `channel_id` varchar(10) DEFAULT NULL,
  `city` varchar(10) DEFAULT NULL,
  `clientudid` varchar(255) DEFAULT NULL,
  `cookie` longtext DEFAULT NULL,
  `cpu_abi` varchar(255) DEFAULT NULL,
  `cpu_support64` varchar(255) DEFAULT NULL,
  `create_at` timestamp NULL DEFAULT NULL,
  `device_brand` varchar(255) DEFAULT NULL,
  `device_id` varchar(255) DEFAULT NULL,
  `device_manufacturer` varchar(255) DEFAULT NULL,
  `device_model` varchar(255) DEFAULT NULL,
  `device_platform` varchar(255) DEFAULT NULL,
  `device_token` varchar(255) DEFAULT NULL,
  `display_density` varchar(255) DEFAULT NULL,
  `display_name` varchar(255) DEFAULT NULL,
  `dpi` int(11) DEFAULT NULL,
  `git_hash` varchar(255) DEFAULT NULL,
  `install_id` varchar(255) DEFAULT NULL,
  `is_ban` tinyint(1) DEFAULT NULL,
  `is_reject` tinyint(1) DEFAULT NULL,
  `is_system_app` int(11) DEFAULT NULL,
  `language` varchar(255) DEFAULT NULL,
  `mac` varchar(255) DEFAULT NULL,
  `magic_tag` varchar(255) DEFAULT NULL,
  `manifest_version_code` int(11) DEFAULT NULL,
  `minor_status` varchar(255) DEFAULT NULL,
  `not_request_sender` int(11) DEFAULT NULL,
  `oaid` varchar(255) DEFAULT NULL,
  `oaid_may_support` tinyint(1) DEFAULT NULL,
  `openudid` varchar(255) DEFAULT NULL,
  `os` varchar(255) DEFAULT NULL,
  `os_api` int(11) DEFAULT NULL,
  `os_version` varchar(255) DEFAULT NULL,
  `pick_count` int(11) DEFAULT NULL,
  `pre_installed_channel` varchar(255) DEFAULT NULL,
  `region` varchar(255) DEFAULT NULL,
  `rejected_at` timestamp NULL DEFAULT NULL,
  `release_build` varchar(255) DEFAULT NULL,
  `resolution` varchar(255) DEFAULT NULL,
  `rom` varchar(255) DEFAULT NULL,
  `rom_version` varchar(255) DEFAULT NULL,
  `sdk_flavor` varchar(255) DEFAULT NULL,
  `sdk_target_version` int(11) DEFAULT NULL,
  `sdk_version` varchar(255) DEFAULT NULL,
  `sig_hash` varchar(255) DEFAULT NULL,
  `sn` varchar(255) DEFAULT NULL,
  `ssmix` varchar(255) DEFAULT NULL,
  `timezone` int(11) DEFAULT NULL,
  `tz_name` varchar(255) DEFAULT NULL,
  `tz_offset` int(11) DEFAULT NULL,
  `udid` varchar(255) DEFAULT NULL,
  `update_version_code` int(11) DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `user_agent` varchar(255) DEFAULT NULL,
  `version_code` int(11) DEFAULT NULL,
  `version_name` varchar(255) DEFAULT NULL,
  `is_simulate` tinyint(1) DEFAULT NULL,
  `ban_type` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */,
  KEY `by_is_ban` (`is_ban`),
  KEY `by_pick_count` (`pick_count`),
  KEY `idx_is_ban_pick_count` (`is_ban`,`pick_count`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=3160124;

SELECT
  id
FROM
  device device0_
WHERE
  device0_.is_ban = 0
ORDER BY
  device0_.pick_count ASC
LIMIT
  1;

explain信息

Projection_7	1.00	1	root		time:329.2ms, loops:2, Concurrency:OFF	aweme_encryption.device.id, aweme_encryption.device.access, aweme_encryption.device.aid, aweme_encryption.device.apk_first_install_time, aweme_encryption.device.app_name, aweme_encryption.device.app_package, aweme_encryption.device.app_type, aweme_encryption.device.app_version, aweme_encryption.device.appkey, aweme_encryption.device.apptheme, aweme_encryption.device.ban_at, aweme_encryption.device.ban_count, aweme_encryption.device.ban_type, aweme_encryption.device.bind_phone, aweme_encryption.device.cdid, aweme_encryption.device.channel, aweme_encryption.device.channel_id, aweme_encryption.device.city, aweme_encryption.device.clientudid, aweme_encryption.device.cookie, aweme_encryption.device.cpu_abi, aweme_encryption.device.cpu_support64, aweme_encryption.device.create_at, aweme_encryption.device.device_brand, aweme_encryption.device.device_id, aweme_encryption.device.device_manufacturer, aweme_encryption.device.device_model, aweme_encryption.device.device_platform, aweme_encryption.device.device_token, aweme_encryption.device.display_density, aweme_encryption.device.display_name, aweme_encryption.device.dpi, aweme_encryption.device.git_hash, aweme_encryption.device.install_id, aweme_encryption.device.is_ban, aweme_encryption.device.is_reject, aweme_encryption.device.is_simulate, aweme_encryption.device.is_system_app, aweme_encryption.device.language, aweme_encryption.device.mac, aweme_encryption.device.magic_tag, aweme_encryption.device.manifest_version_code, aweme_encryption.device.minor_status, aweme_encryption.device.not_request_sender, aweme_encryption.device.oaid, aweme_encryption.device.oaid_may_support, aweme_encryption.device.openudid, aweme_encryption.device.os, aweme_encryption.device.os_api, aweme_encryption.device.os_version, aweme_encryption.device.pick_count, aweme_encryption.device.pre_installed_channel, aweme_encryption.device.region, aweme_encryption.device.rejected_at, aweme_encryption.device.release_build, aweme_encryption.device.resolution, aweme_encryption.device.rom, aweme_encryption.device.rom_version, aweme_encryption.device.sdk_flavor, aweme_encryption.device.sdk_target_version, aweme_encryption.device.sdk_version, aweme_encryption.device.sig_hash, aweme_encryption.device.sn, aweme_encryption.device.ssmix, aweme_encryption.device.timezone, aweme_encryption.device.tz_name, aweme_encryption.device.tz_offset, aweme_encryption.device.udid, aweme_encryption.device.update_version_code, aweme_encryption.device.updated_at, aweme_encryption.device.user_agent, aweme_encryption.device.version_code, aweme_encryption.device.version_name	39.0 KB	N/A
└─IndexLookUp_26	1.00	1	root		time:329.1ms, loops:2, index_task: {total_time: 328ms, fetch_handle: 328ms, build: 5.61µs, wait: 7.1µs}, table_task: {total_time: 1.64s, num: 1, concurrency: 5}	limit embedded(offset:0, count:1)	47.0 KB	N/A
  ├─Limit_25(Build)	1.00	1	cop[tikv]		time:327.9ms, loops:1, cop_task: {num: 3, max: 295.6ms, min: 1.19ms, avg: 109.3ms, p95: 295.6ms, max_proc_keys: 1, p95_proc_keys: 1, tot_proc: 282ms, tot_wait: 44ms, rpc_num: 3, rpc_time: 327.8ms, copr_cache_hit_ratio: 0.33}, tikv_task:{proc max:284ms, min:1ms, p80:284ms, p95:284ms, iters:3, tasks:3}, scan_detail: {total_process_keys: 1, total_keys: 925178, rocksdb: {delete_skipped_count: 10, key_skipped_count: 925204, block: {cache_hit_count: 538, read_count: 0, read_byte: 0 Bytes}}}	offset:0, count:1	N/A	N/A
  │ └─IndexRangeScan_23	1.00	1	cop[tikv]	table:device0_, index:idx_is_ban_pick_count(is_ban, pick_count)	tikv_task:{proc max:284ms, min:1ms, p80:284ms, p95:284ms, iters:3, tasks:3}	range:[0,0], keep order:true	N/A	N/A
  └─TableRowIDScan_24(Probe)	1.00	1	cop[tikv]	table:device0_	time:893.6µs, loops:2, cop_task: {num: 1, max: 807.3µs, proc_keys: 1, rpc_num: 1, rpc_time: 799.9µs, copr_cache_hit_ratio: 0.00}, tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 1, total_keys: 4, rocksdb: {delete_skipped_count: 0, key_skipped_count: 3, block: {cache_hit_count: 8, read_count: 0, read_byte: 0 Bytes}}}	keep order:false, stats:pseudo	N/A	N/A

copy了一张表同样的explain

Projection_7	1.00	1	root		time:6.41ms, loops:2, Concurrency:OFF	aweme_encryption.device_copy1.id, aweme_encryption.device_copy1.access, aweme_encryption.device_copy1.aid, aweme_encryption.device_copy1.apk_first_install_time, aweme_encryption.device_copy1.app_name, aweme_encryption.device_copy1.app_package, aweme_encryption.device_copy1.app_type, aweme_encryption.device_copy1.app_version, aweme_encryption.device_copy1.appkey, aweme_encryption.device_copy1.apptheme, aweme_encryption.device_copy1.ban_at, aweme_encryption.device_copy1.ban_count, aweme_encryption.device_copy1.ban_type, aweme_encryption.device_copy1.bind_phone, aweme_encryption.device_copy1.cdid, aweme_encryption.device_copy1.channel, aweme_encryption.device_copy1.channel_id, aweme_encryption.device_copy1.city, aweme_encryption.device_copy1.clientudid, aweme_encryption.device_copy1.cookie, aweme_encryption.device_copy1.cpu_abi, aweme_encryption.device_copy1.cpu_support64, aweme_encryption.device_copy1.create_at, aweme_encryption.device_copy1.device_brand, aweme_encryption.device_copy1.device_id, aweme_encryption.device_copy1.device_manufacturer, aweme_encryption.device_copy1.device_model, aweme_encryption.device_copy1.device_platform, aweme_encryption.device_copy1.device_token, aweme_encryption.device_copy1.display_density, aweme_encryption.device_copy1.display_name, aweme_encryption.device_copy1.dpi, aweme_encryption.device_copy1.git_hash, aweme_encryption.device_copy1.install_id, aweme_encryption.device_copy1.is_ban, aweme_encryption.device_copy1.is_reject, aweme_encryption.device_copy1.is_simulate, aweme_encryption.device_copy1.is_system_app, aweme_encryption.device_copy1.language, aweme_encryption.device_copy1.mac, aweme_encryption.device_copy1.magic_tag, aweme_encryption.device_copy1.manifest_version_code, aweme_encryption.device_copy1.minor_status, aweme_encryption.device_copy1.not_request_sender, aweme_encryption.device_copy1.oaid, aweme_encryption.device_copy1.oaid_may_support, aweme_encryption.device_copy1.openudid, aweme_encryption.device_copy1.os, aweme_encryption.device_copy1.os_api, aweme_encryption.device_copy1.os_version, aweme_encryption.device_copy1.pick_count, aweme_encryption.device_copy1.pre_installed_channel, aweme_encryption.device_copy1.region, aweme_encryption.device_copy1.rejected_at, aweme_encryption.device_copy1.release_build, aweme_encryption.device_copy1.resolution, aweme_encryption.device_copy1.rom, aweme_encryption.device_copy1.rom_version, aweme_encryption.device_copy1.sdk_flavor, aweme_encryption.device_copy1.sdk_target_version, aweme_encryption.device_copy1.sdk_version, aweme_encryption.device_copy1.sig_hash, aweme_encryption.device_copy1.sn, aweme_encryption.device_copy1.ssmix, aweme_encryption.device_copy1.timezone, aweme_encryption.device_copy1.tz_name, aweme_encryption.device_copy1.tz_offset, aweme_encryption.device_copy1.udid, aweme_encryption.device_copy1.update_version_code, aweme_encryption.device_copy1.updated_at, aweme_encryption.device_copy1.user_agent, aweme_encryption.device_copy1.version_code, aweme_encryption.device_copy1.version_name	39.0 KB	N/A
└─IndexLookUp_26	1.00	1	root		time:6.11ms, loops:2, index_task: {total_time: 3.43ms, fetch_handle: 3.41ms, build: 12.7µs, wait: 1.9µs}, table_task: {total_time: 18.8ms, num: 1, concurrency: 5}	limit embedded(offset:0, count:1)	47.0 KB	N/A
  ├─Limit_25(Build)	1.00	1	cop[tikv]		time:3.13ms, loops:1, cop_task: {num: 1, max: 2.97ms, proc_keys: 1, tot_proc: 2ms, rpc_num: 1, rpc_time: 2.96ms, copr_cache_hit_ratio: 0.00}, tikv_task:{time:1ms, loops:1}, scan_detail: {total_process_keys: 1, total_keys: 2, rocksdb: {delete_skipped_count: 0, key_skipped_count: 1, block: {cache_hit_count: 0, read_count: 2, read_byte: 29.8 KB}}}	offset:0, count:1	N/A	N/A
  │ └─IndexRangeScan_23	1.00	1	cop[tikv]	table:device0_, index:idx_is_ban_pick_count(is_ban, pick_count)	tikv_task:{time:1ms, loops:1}	range:[0,0], keep order:true	N/A	N/A
  └─TableRowIDScan_24(Probe)	1.00	1	cop[tikv]	table:device0_	time:1.89ms, loops:2, cop_task: {num: 1, max: 1.55ms, proc_keys: 0, rpc_num: 1, rpc_time: 1.53ms, copr_cache_hit_ratio: 1.00}, tikv_task:{time:18ms, loops:1}	keep order:false, stats:pseudo	N/A	N/A

奇怪的是total_keys数量差距很大

这道题我不会 · 2021 年9 月 17 日 08:15

可能是统计信息过旧导致优化器评估的结果不准确，麻烦重新收集下表统计信息，然后再观察下 SQL 的执行计划。

a398058068 · 2021 年9 月 17 日 08:17

早试过啦一开始也怀疑过。 analyze table device; 不管用。

a398058068 · 2021 年9 月 17 日 08:31

sql 我也在正文补充了下。

这道题我不会 · 2021 年9 月 22 日 01:30

1.从执行计划里看存在 stats:pseudo，感觉表的统计信息可能不够新，可以 show stats_healthy 看下表统计信息的健康度；
2.出现 total_keys 差异很大情况的发生频次有多少？相邻发生变化的 SQL 都是在 gc 时间内吗？

a398058068 · 2021 年9 月 23 日 02:56

手动更新了下表统计信息，现在健康度是100% 。 total_keys 大概执行5次有3次很高。所以sql执行时间波动很大。 10分钟gc一次是在同一个gc时间内执行的sql。

这道题我不会 · 2021 年9 月 23 日 03:57

5.0 中默认开启 GC in Compaction Filter 了，由 RocksDB 的 Compaction 过程来进行 GC，而不再使用一个单独的 GC worker 线程，不确定是不是 compaction 不及时导致堆积了一些旧版本的数据，从而引起扫描的 total_keys 数量差异很大。如果环境的重要性不高，可以考虑临时关闭下该特性，然后验证下 SQL 查询情况有无改善，参考：
https://docs.pingcap.com/zh/tidb/stable/garbage-collection-configuration#gc-in-compaction-filter-机制

a398058068 · 2021 年9 月 23 日 06:48

生产环境，奇怪的是就这个表效率有问题

这道题我不会 · 2021 年9 月 23 日 07:03

就这一张表上的 SQL 有这个问题吗？其他更新频率差不多的表上有这个现象吗？

a398058068 · 2021 年9 月 24 日 02:41

有比他频率高的没有这个问题。

spc_monkey · 2021 年9 月 24 日 04:11

像这种慢 SQL 的问题，建议提供 explian analyze 的结果，或提供慢日志中该慢 SQL的相关内容，尤其是：tidb_decode_plan 字段

a398058068 · 2021 年9 月 24 日 10:28

explain analyze在主题里就提供过了。

我在提供一份慢日志的信息

# Time: 2021-09-11T08:38:41.687736695+08:00
# Txn_start_ts: 427640939173642254
# Conn_ID: 429
# Query_time: 0.303293203
# Parse_time: 0.000237446
# Compile_time: 0.001220551
# Rewrite_time: 0.000269149
# Optimize_time: 0.000478652
# Wait_TS: 0.000012403
# Cop_time: 0.300929479 Process_time: 0.297 Wait_time: 0.001 Request_count: 4 Process_keys: 2 Total_keys: 683173 Rocksdb_delete_skipped_count: 16 Rocksdb_key_skipped_count: 683183 Rocksdb_block_cache_hit_count: 409
# DB: aweme_encryption
# Index_names: [device:idx_is_ban_pick_count]
# Is_internal: false
# Digest: 830eadf1d9567602fbce4d8b5545b4e5a1de2d73cc4ec0346539a63766500502
# Stats: device:427640932633149445
# Num_cop_tasks: 4
# Cop_proc_avg: 0.07425 Cop_proc_p90: 0.296 Cop_proc_max: 0.296 Cop_proc_addr: 172.18.150.41:20160
# Cop_wait_avg: 0.00025 Cop_wait_p90: 0.001 Cop_wait_max: 0.001 Cop_wait_addr: 172.18.150.193:20160
# Mem_max: 88124
# Prepared: false
# Plan_from_cache: false
# Plan_from_binding: false
# Has_more_results: false
# KV_total: 0.300927208
# PD_total: 0.00001447
# Backoff_total: 0
# Write_sql_response_total: 0.000013223
# Succ: true
# Plan: tidb_decode_plan('sx+YMAkzXzcJMAkxCWF3ZW1lX2VuY3J5cHRpb24uZGV2aWNlLmlkLCBhWhwAGGFjY2VzcyxmIAAAaW49AFBwa19maXJzdF9pbnN0YWxsX3RpbWVqTQAQcHBfbmF+IgAUcGFja2FnekcACHR5cHoiABh2ZXJzaW9uco4ACGtleXIgAAh0aGVurgAUYmFuX2F0ZkIAASAMY291bnojAHbMACBiaW5kX3Bob25q8AAEY2RupwEYY2hhbm5lbGaoAA0hAF9yRQAEaXRqLAEYY2xpZW50dXaHAAxvb2tpbsUAFHB1X2FiaWqnACxwdV9zdXBwb3J0NjRqJwAQcmVhdGVymQFpGRBfYnJhbmrfAg0mbhYBDSMsbWFudWZhY3R1cmVyZpkAES0Eb2RujQENJhxwbGF0Zm9ybYJPAAx0b2tlauYCLGRpc3BsYXlfZGVuc3LDAREpdqEDBGRwaqEBHGdpdF9oYXNoZrQAkShuVAEQaXNfYmFq0gAcaXNfcmVqZWNqVgMkaXNfc2ltdWxhdGpwAgElIHlzdGVtX2FwcGazABBsYW5ndXKQBAhtYWNmPwAgbWFnaWNfdGFnbiMAFG5pZmVzdJGzDF9jb2RquAAobWlub3Jfc3RhdHVquQUcbm90X3JlcXUBWAxzZW5kbpkCAG9y5gUBHgxfbWF5cXxmyQAMb3BlbnYEBABvarIADG9zX2FuXgIEb3MxIWaCAAxwaWNrfnMFCHByZdGwCGVkX438ZlMACHJlZ3JHBkmMBGVkcnEELHJlbGVhc2VfYnVpbGpyBBRyZXNvbHXhvGqQAABvahQECHJvbYYlASBzZGtfZmxhdm9qrAQBJBB0YXJnZVWWZpIAASyCbgcIc2lnehwEAHNuYwAMc21peGaCAA53CAB6ciIHBHR6etkEHHR6X29mZnNlat8CdtsCCHVwZMF3LQl6xAMJLXY0Ahx1c2VyX2FnZW4/CJZuABEmodsICTEJIS7YOjMwMS41bXMsIGxvb3BzOjIsIENvbmN1cnJlbmN5Ok9GRgkzOS4wIEtCCU4vQQoxCTMwXzI2CQ5nCjhsaW1pdCBlbWJlZGRlZCgpNgw6MCwgEusICDoxKWZjAEBpbmRleF90YXNrOiB7dG90YRYyChg6IDMwMC4yAY4oZmV0Y2hfaGFuZGwuFwBlOHw6IDUuNzbCtXMsIHdhaXQ6IDkuMTTCtXN9LCB0YWJsZU5ZACwxLjVzLCBudW06IDEBohnjFCA1fQk0NxnjJDIJMTZfMjUJMV8B5ULWABnVDaopOAVVAHARegVoMDMsIG1heDogMjk4LjEB1CBtaW46IDU0MS4BrSQsIGF2ZzogMTAwARsIcDk1LikALGF4X3Byb2Nfa2V5cwWxCHA5NToSAAh0b3QFEgE3ADcBQwERKQ8FbQxycGNfEYcBDCXtIWABLGRjb3ByX2NhY2hlX2hpdF9yYXRpbzogMC4zMyFECGlrdgnJAHsBhQXFBDQ3JZYBwgVeCHA4MBEUAbsNHxhpdGVyczozIYcMc2tzOgFNKHNjYW5fZGV0YWlsNesBVghlc3MZ3kkCCe10NjgzMTcwLCByb2Nrc2RiOiB7ZGVsZXRlX3NraXBwoakOSQsgOiAxNiwga2V5PhcAAT8cODEsIGJsb2NBXRnlDTggNDAxLCByZWFkEREBbAEPTGJ5dGU6IDAgQnl0ZXN9fX0JTi9BAQQcCjMJNDdfMjNNEUViADoWnQkIMF8sScsQOmlkeF8WsAgAX9l7ACgJEgQsIBkTNCksIHJhbmdlOlswLDBdAcIsZXAgb3JkZXI6dHJ1aZX+fgE1fgG+AQQcCjIJNDVfMjRawgAAaxmEGGZhbHNlLCAOVAgcczpwc2V1ZG9R8BA4NzguMmV0SfEAMmFGNvECADFN8Qg3ODVN4yHqLdghdlGTDQxJkxA3NzQuOQVZAGNalgIEMDA2lgIFOAAwlbMIMX0sxmcCADOGYgIAMCGfQmECADJqXAIAOLpaAg==')
# Plan_digest: a26348a7b499a1d6f2f8c80bd87bf0aaca0c88555abb5cd75820e4ee30372156
select device0_.id as id1_0_, device0_.access as access2_0_, device0_.aid as aid3_0_, device0_.apk_first_install_time as apk_firs4_0_, device0_.app_name as app_name5_0_, device0_.app_package as app_pack6_0_, device0_.app_type as app_type7_0_, device0_.app_version as app_vers8_0_, device0_.appkey as appkey9_0_, device0_.apptheme as appthem10_0_, device0_.ban_at as ban_at11_0_, device0_.ban_count as ban_cou12_0_, device0_.ban_type as ban_typ13_0_, device0_.bind_phone as bind_ph14_0_, device0_.cdid as cdid15_0_, device0_.channel as channel16_0_, device0_.channel_id as channel17_0_, device0_.city as city18_0_, device0_.clientudid as clientu19_0_, device0_.cookie as cookie20_0_, device0_.cpu_abi as cpu_abi21_0_, device0_.cpu_support64 as cpu_sup22_0_, device0_.create_at as create_23_0_, device0_.device_brand as device_24_0_, device0_.device_id as device_25_0_, device0_.device_manufacturer as device_26_0_, device0_.device_model as device_27_0_, device0_.device_platform as device_28_0_, device0_.device_token as device_29_0_, device0_.display_density as display30_0_, device0_.display_name as display31_0_, device0_.dpi as dpi32_0_, device0_.git_hash as git_has33_0_, device0_.install_id as install34_0_, device0_.is_ban as is_ban35_0_, device0_.is_reject as is_reje36_0_, device0_.is_simulate as is_simu37_0_, device0_.is_system_app as is_syst38_0_, device0_.language as languag39_0_, device0_.mac as mac40_0_, device0_.magic_tag as magic_t41_0_, device0_.manifest_version_code as manifes42_0_, device0_.minor_status as minor_s43_0_, device0_.not_request_sender as not_req44_0_, device0_.oaid as oaid45_0_, device0_.oaid_may_support as oaid_ma46_0_, device0_.openudid as openudi47_0_, device0_.os as os48_0_, device0_.os_api as os_api49_0_, device0_.os_version as os_vers50_0_, device0_.pick_count as pick_co51_0_, device0_.pre_installed_channel as pre_ins52_0_, device0_.region as region53_0_, device0_.rejected_at as rejecte54_0_, device0_.release_build as release55_0_, device0_.resolution as resolut56_0_, device0_.rom as rom57_0_, device0_.rom_version as rom_ver58_0_, device0_.sdk_flavor as sdk_fla59_0_, device0_.sdk_target_version as sdk_tar60_0_, device0_.sdk_version as sdk_ver61_0_, device0_.sig_hash as sig_has62_0_, device0_.sn as sn63_0_, device0_.ssmix as ssmix64_0_, device0_.timezone as timezon65_0_, device0_.tz_name as tz_name66_0_, device0_.tz_offset as tz_offs67_0_, device0_.udid as udid68_0_, device0_.update_version_code as update_69_0_, device0_.updated_at as updated70_0_, device0_.user_agent as user_ag71_0_, device0_.version_code as version72_0_, device0_.version_name as version73_0_ from device device0_ where device0_.is_ban=0 order by device0_.pick_count asc limit 1;

这道题我不会 · 2021 年9 月 26 日 07:11

从执行计划中看 SQL 先走的是 (is_ban,pick_count) 上的联合索引，这类状态字段 is_ban 会不会存在不同时间点数据量差异比较大的情况？比如批量更新，这样可能导致扫描到的中间结果集差异比较大。

yiqing2021 · 2021 年9 月 26 日 09:27

发下这张表的统计信息数据，表和列的统计信息都看看？SHOW STATS_HISTOGRAMS where Table_name=’ ’ ; select * from ANALYZE_STATUS where table_name=’’;

a398058068 · 2021 年9 月 28 日 02:00

SHOW STATS_HISTOGRAMS where Table_name='device'; 
aweme_encryption	device		id	0	2021-09-27 09:09:52	2037153	0	8	1
aweme_encryption	device		bind_phone	0	2021-09-27 09:09:52	0	2037153	0	0
aweme_encryption	device		is_ban	0	2021-09-27 09:09:52	2	0	8.83	0.38879578098037165
aweme_encryption	device		by_is_ban	1	2021-09-27 09:09:52	2	0	0	0
aweme_encryption	device		by_pick_count	1	2021-09-27 09:09:52	1020	0	0	0
aweme_encryption	device		idx_is_ban_pick_count	1	2021-09-27 09:09:52	1536	0	0	0
aweme_encryption	device		id	0	2021-09-27 09:09:52	2037153	0	8	1
aweme_encryption	device		bind_phone	0	2021-09-27 09:09:52	0	2037153	0	0
aweme_encryption	device		is_ban	0	2021-09-27 09:09:52	2	0	8.83	0.38879578098037165
aweme_encryption	device		by_is_ban	1	2021-09-27 09:09:52	2	0	0	0
aweme_encryption	device		by_pick_count	1	2021-09-27 09:09:52	1020	0	0	0
aweme_encryption	device		idx_is_ban_pick_count	1	2021-09-27 09:09:52	1536	0	0	0

SHOW ANALYZE STATUS where table_name=‘device’;
没有找到device的信息，不过手动执行过是成功健康度100%

a398058068 · 2021 年9 月 28 日 02:01

目前没有批量更新的情况

spc_monkey · 2021 年9 月 28 日 04:26

1、这个问题已有对应的 PR，https://github.com/tikv/tikv/pull/10573 ，后面版本会修复
2、现在解决办法，有点麻烦：（如果现在不是很紧急，建议等升级）
对这个 store 先后执行
tikv-ctl compact -d kv -c write
tikv-ctl compact -d kv -c default
tikv-ctl compact -d kv -c write --bottommost force
tikv-ctl compact -d kv -c lock --bottommost force

a398058068 · 2021 年9 月 28 日 06:06

好的。可以具体说一下问题原因么。了解一下。

spc_monkey · 2021 年9 月 28 日 06:47

业务存在大量读取，但只有小量写操作，不足以触发 compaction, 由于默认开启 compaction filter 没有 compaction 则不能删除过期的 mvcc 数据, 所以数据一直存在多个 mvcc 版本，导致每次读取都需要读到并跳过这些数据

system · 2022 年10 月 31 日 19:17

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

SQL优化 扫描total_keys数的奇怪问题。

SQL优化扫描total_keys数的奇怪问题。