查询速度慢时间不稳定。

a398058068 · 2022 年3 月 10 日 08:59

为提高效率，请提供以下信息，问题描述清晰能够更快得到解决：
【 TiDB 使用环境】
5.4

【概述】场景 + 问题概述

【背景】做过哪些操作

【现象】业务和数据库现象
查询速度慢时间不稳定。

【问题】当前遇到的问题，参考 AskTUG 的 Troubleshooting 读性能慢-慢语句

【统计信息是否最新】
表的健康度查了下是93
【执行计划内容】

Projection_7	1.00	1	root		time:3.24s, 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
└─Limit_12	1.00	1	root		time:3.24s, loops:2	offset:0, count:1	N/A	N/A
  └─IndexLookUp_31	1.00	1	root		time:3.24s, loops:1, index_task: {total_time: 3.24s, fetch_handle: 157.9ms, build: 62.1ms, wait: 3.02s}, table_task: {total_time: 15.7s, num: 28, concurrency: 5}		75.2 MB	N/A
    ├─IndexRangeScan_28(Build)	20.19	290619	cop[tikv]	table:device0_, index:idx_is_ban_pick_count(is_ban, pick_count)	time:136.9ms, loops:292, cop_task: {num: 6, max: 191.6ms, min: 465µs, avg: 33.8ms, p95: 191.6ms, max_proc_keys: 170371, p95_proc_keys: 170371, tot_proc: 175ms, tot_wait: 8ms, rpc_num: 6, rpc_time: 202.8ms, copr_cache_hit_ratio: 0.67}, tikv_task:{proc max:557ms, min:0s, p80:525ms, p95:557ms, iters:301, tasks:6}, scan_detail: {total_process_keys: 170371, total_process_keys_size: 9370405, total_keys: 481193, rocksdb: {delete_skipped_count: 35, key_skipped_count: 481226, block: {cache_hit_count: 292, read_count: 0, read_byte: 0 Bytes}}}	range:[0,0], keep order:true	N/A	N/A
    └─Selection_30(Probe)	1.00	50019	cop[tikv]		time:15.5s, loops:81, cop_task: {num: 156, max: 3.03s, min: 442.1µs, avg: 131.3ms, p95: 761.3ms, max_proc_keys: 18671, p95_proc_keys: 2344, tot_proc: 18.2s, tot_wait: 890ms, rpc_num: 156, rpc_time: 20.5s, copr_cache_hit_ratio: 0.90}, tikv_task:{proc max:371ms, min:0s, p80:38ms, p95:134ms, iters:492, tasks:156}, scan_detail: {total_process_keys: 66635, total_process_keys_size: 76915888, total_keys: 86393, rocksdb: {delete_skipped_count: 26706, key_skipped_count: 96794, block: {cache_hit_count: 428526, read_count: 0, read_byte: 0 Bytes}}}	eq(aweme_encryption.device.app_version, "16.8.0")	N/A	N/A
      └─TableRowIDScan_29	20.19	188384	cop[tikv]	table:device0_	tikv_task:{proc max:369ms, min:0s, p80:38ms, p95:134ms, iters:492, tasks:156}	keep order:false	N/A	N/A

    【 SQL 文本、schema 以及 数据分布】

# Time: 2022-03-10T15:22:02.621267251+08:00
# Txn_start_ts: 431724146042077210
# User@Host: aweme_encryption_user[aweme_encryption_user] @ 10.165.0.207 [10.165.0.207]
# Conn_ID: 255617
# Query_time: 3.183456646
# Parse_time: 0.00029965
# Compile_time: 0.002087851
# Rewrite_time: 0.000321452
# Optimize_time: 0.001108982
# Wait_TS: 0.000018281
# Cop_time: 5.714274076 Process_time: 6.078 Wait_time: 4.621 Request_count: 159 Process_keys: 193278 Total_keys: 379990 Rocksdb_delete_skipped_count: 184690 Rocksdb_key_skipped_count: 608805 Rocksdb_block_cache_hit_count: 216587
# DB: aweme_encryption
# Index_names: [device:idx_is_ban_pick_count]
# Is_internal: false
# Digest: c9882ea23e1bdf464454c70ce463b162a8e6d8442f4b8e29f5aab5db9cf99a1e
# Stats: device:431724024840060951
# Num_cop_tasks: 159
# Cop_proc_avg: 0.038226415 Cop_proc_p90: 0.001 Cop_proc_max: 1.418 Cop_proc_addr: 172.18.150.41:20160
# Cop_wait_avg: 0.029062893 Cop_wait_p90: 0.064 Cop_wait_max: 0.097 Cop_wait_addr: 172.18.150.41:20160
# Mem_max: 19015169
# Prepared: false
# Plan_from_cache: false
# Plan_from_binding: false
# Has_more_results: false
# KV_total: 18.355833734
# PD_total: 0.000014164
# Backoff_total: 0
# Write_sql_response_total: 0.00003083
# Result_rows: 1
# Succ: true
# IsExplicitTxn: false
# Plan: tidb_decode_plan('7CGYMAkzXzcJMAkxCWF3ZW1lX2VuY3J5cHRpb24uZGV2aWNlLmlkLCBhWhwAGGFjY2VzcyxmIAAAaW49AFBwa19maXJzdF9pbnN0YWxsX3RpbWVqTQAQcHBfbmF+IgAUcGFja2FnekcACHR5cHoiABh2ZXJzaW9uco4ACGtleXIgAAh0aGVurgAUYmFuX2F0ZkIAASAMY291bnojAHbMACBiaW5kX3Bob25q8AAEY2RupwEYY2hhbm5lbGaoAA0hAF9yRQAEaXRqLAEYY2xpZW50dXaHAAxvb2tpbsUAFHB1X2FiaWqnACxwdV9zdXBwb3J0NjRqJwAQcmVhdGVymQFpGRBfYnJhbmrfAg0mbhYBDSMsbWFudWZhY3R1cmVyZpkAES0Eb2RujQENJhxwbGF0Zm9ybYJPAAx0b2tlauYCLGRpc3BsYXlfZGVuc3LDAREpdqEDBGRwaqEBHGdpdF9oYXNoZrQAkShuVAEQaXNfYmFq0gAcaXNfcmVqZWNqVgMkaXNfc2ltdWxhdGpwAgElIHlzdGVtX2FwcGazABBsYW5ndXKQBAhtYWNmPwAgbWFnaWNfdGFnbiMAFG5pZmVzdJGzDF9jb2RquAAobWlub3Jfc3RhdHVquQUcbm90X3JlcXUBWAxzZW5kbpkCAG9y5gUBHgxfbWF5cXxmyQAMb3BlbnYEBABvarIADG9zX2FuXgIEb3MxIWaCAAxwaWNrfnMFCHByZdGwCGVkX438ZlMACHJlZ3JHBkmMBGVkcnEELHJlbGVhc2VfYnVpbGpyBBRyZXNvbHXhvGqQAABvahQECHJvbYYlASBzZGtfZmxhdm9qrAQBJBB0YXJnZVWWZpIAASyCbgcIc2lnehwEAHNuYwAMc21peGaCAA53CAB6ciIHBHR6etkEHHR6X29mZnNlat8CdtsCCHVwZMF3LQl6xAMJLXY0Ahx1c2VyX2FnZW4/CJZuABEmodsICTEJIS7QOjMuMTdzLCBsb29wczoyLCBDb25jdXJyZW5jeTpPRkYJMzkuMCBLQglOL0EKMQkxNl8xMgkOZQopJQw6MCwgEtoIBDoxVlEADAlOL0EBBBwKMgkzMF8zMRKhClIrAEwxLCBpbmRleF90YXNrOiB7dG90YRZJCgQ6IA2lUGZldGNoX2hhbmRsZTogMS44MnMsIGVLNDogNjIuNm1zLCB3YWl0ARwoMjhzfSwgdGFibGVOUgAwNi44cywgbnVtOiAyNgHEGfYkIDV9CTE4LjEgTQn2RDMJNDdfMjgJMV8wCTIwLjE5CQVWADrpoQQwXw24EDppZHhfybQAX5l/ACgJEgQsIBkTmCksIHJhbmdlOlswLDBdLCBrZWVwIG9yZGVyOnRydWUJMjg5NjYyCSWeDDEuODE5ngQ4MgGqAHAR0AW+FDYsIG1heAHyBSoIbWluAQwEMDMhDCRhdmc6IDMwNi4yAQ4IcDk1ARsJJ1BheF9wcm9jX2tleXM6IDExNTUyMCwBI04XAAh0b3QFFwE/ADQhdAERKWkEMzkBWwxycGNfEY4BDAW8IaEENHMFslhyX2NhY2hlX2hpdF9yYXRpbzogMC42NyGhCGlrdgnQAHsBkAXMCDQ4NQFVAcsAMQEJFHA4MDo0MwXSAcQNHyBpdGVyczoyOTkh5khza3M6Nn0sIHNjYW5fZGV0YWlsVUMBWAhlc3M66wBJXy4cADhfc2l6ZTogNjM1MzYwMCwNIikhdDI5NjkwNSwgcm9ja3NkYjoge2RlbGV0ZV9za2lwcME/Dt8LHDogMTgxMzQxIdQAeT4bACw0NzgyNDQsIGJsb2NB4DkSDTwgMzY2LCByZWFkEREAMA0PPGJ5dGU6IDAgQnl0ZXN9fX1hUAEEGAozCTFfMzBFmRQxCWVxKGFe3Q0EcHCRkDwsICIxNi44LjAiKQk5OTI0iQUMNi42NlVnBDM4JbQplgAgSWYIMTUzVWgAMkGSIZ0cIDYxOC40wrXhzEFqCDY1LiW5IaUUIDI2OC43Ib46awIMMjA0OEpqAgA0JchVaAA0CZEBEUloCDQuNWGxQV1FaQWRAQ5NawAwamkCADhBGgB0SmkCADJZaQFAQWgAMoVFAcIEMTJF20lnCDQzNVFnCDE1M5JpAhA3Nzc1OFFGSmgCGDkwNzQwOTRBv4XqSWkMODMwOIpoAgQzMwE5AGtGZgIUMTMwNTYxamYCFDIxNjIyMU1aTXqCaQIYNAk0NV8yOUVqVgMFncUoZmFsc2UJMTQ1NzCBxkbgAwQyOGXMQUNOdwFFNlJ3ASAJTi9BCU4vQQo=')
# Plan_digest: f59a68551d20799ffad8f265dee8c1cbf58a76aa1261edb5c116a7fba0a77ba1
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_.app_version='16.8.0' and device0_.is_ban=0 order by device0_.pick_count asc limit 1;

【业务影响】

【TiDB 版本】

【附件】相关日志及监控（https://metricstool.pingcap.com/)

TiUP Cluster Display 信息
TiUP CLuster Edit config 信息
TiDB-Overview Grafana监控
TiDB Grafana 监控
TiKV Grafana 监控
PD Grafana 监控
对应模块日志（包含问题前后 1 小时日志）

h5n1 · 2022 年3 月 10 日 09:04

表结构和SQL发下，时间不稳定指的是同一SQL时快时慢吗？使用的什么类型的磁盘？

a398058068 · 2022 年3 月 10 日 09:06

CREATE TABLE `device` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `access` varchar(255) DEFAULT NULL,
  `aid` int(11) DEFAULT NULL,
  `apk_first_install_time` bigint(20) DEFAULT NULL,
  `app_name` varchar(255) DEFAULT NULL,
  `app_package` varchar(255) DEFAULT NULL,
  `app_type` varchar(255) DEFAULT NULL,
  `app_version` varchar(255) DEFAULT NULL,
  `appkey` varchar(255) DEFAULT NULL,
  `apptheme` varchar(255) DEFAULT NULL,
  `ban_at` timestamp NULL DEFAULT NULL,
  `ban_count` int(11) DEFAULT NULL,
  `bind_phone` varchar(255) DEFAULT NULL,
  `cdid` varchar(255) DEFAULT NULL,
  `channel` varchar(255) DEFAULT NULL,
  `channel_id` varchar(255) DEFAULT NULL,
  `city` varchar(255) 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`),
  KEY `by_app_version` (`app_version`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=10112962;

a398058068 · 2022 年3 月 10 日 09:07

SQL 上面的tidb_slow_query.log 里有。
ssd硬盘。目前发现只有这个表存在这个问题。查了对应的健康度很正常。定位了半天没看出来什么端倪。

h5n1 · 2022 年3 月 10 日 09:09

表数据量多少，这2列的唯一值数量哪个更多些，复合索引应该把唯一性好的放在首列，这样能减少读索引key数量。
Process_keys: 193278 Total_keys: 379990 表的历史版本也有点多

a398058068 · 2022 年3 月 10 日 09:15

pick_count 唯一值数量更多。

关于表提示版本多。除了缩短快照保存时间，还有其他的办法么。

h5n1 · 2022 年3 月 10 日 09:16

版本只能靠GC来处理

a398058068 · 2022 年3 月 10 日 09:44

目前调换了索引顺序和缩短了gc保留的时间，有些改善。执行时间大概在1.5s~700ms之间漂浮。

h5n1 · 2022 年3 月 10 日 09:53

可能因为有物理读会造成有时变慢

a398058068 · 2022 年3 月 10 日 09:57

就是一个普通的sql。也不是特别复杂。
这个sql占用了90%的资源。很头疼。

h5n1 · 2022 年3 月 10 日 10:01

sql发下看看

a398058068 · 2022 年3 月 10 日 10:01

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_.app_version='16.8.0' and device0_.is_ban=0 order by device0_.pick_count asc limit 1;

Projection_7	1.00	1	root		time:9.91s, 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
└─Limit_12	1.00	1	root		time:9.91s, loops:2	offset:0, count:1	N/A	N/A
  └─IndexLookUp_29	1.00	1	root		time:9.91s, loops:1, index_task: {total_time: 1.84s, fetch_handle: 476ms, build: 53.4ms, wait: 1.31s}, table_task: {total_time: 47.6s, num: 28, concurrency: 5}		162.2 MB	N/A
    ├─Selection_27(Build)	20.76	291642	cop[tikv]		time:460.2ms, loops:293, cop_task: {num: 8, max: 487.5ms, min: 461µs, avg: 78.6ms, p95: 487.5ms, max_proc_keys: 874615, p95_proc_keys: 874615, tot_proc: 603ms, tot_wait: 11ms, rpc_num: 8, rpc_time: 628.9ms, copr_cache_hit_ratio: 0.75}, tikv_task:{proc max:1.06s, min:123ms, p80:899ms, p95:1.06s, iters:6631, tasks:8}, scan_detail: {total_process_keys: 1099223, total_process_keys_size: 60457265, total_keys: 1101895, rocksdb: {delete_skipped_count: 296, key_skipped_count: 1102249, block: {cache_hit_count: 684, read_count: 0, read_byte: 0 Bytes}}}	eq(aweme_encryption.device.is_ban, 0)	N/A	N/A
    │ └─IndexFullScan_25	494.62	6751873	cop[tikv]	table:device0_, index:idx_pick_count_is_ban(pick_count, is_ban)	tikv_task:{proc max:1.02s, min:121ms, p80:859ms, p95:1.02s, iters:6631, tasks:8}	keep order:true	N/A	N/A
    └─Selection_28(Probe)	1.00	110208	cop[tikv]		time:47.4s, loops:144, cop_task: {num: 164, max: 9.41s, min: 383.4µs, avg: 419.8ms, p95: 623ms, max_proc_keys: 20480, p95_proc_keys: 8749, tot_proc: 1m6.3s, tot_wait: 1.11s, rpc_num: 164, rpc_time: 1m8.8s, copr_cache_hit_ratio: 0.71}, tikv_task:{proc max:408ms, min:0s, p80:25ms, p95:179ms, iters:589, tasks:164}, scan_detail: {total_process_keys: 181029, total_process_keys_size: 210021474, total_keys: 400576, rocksdb: {delete_skipped_count: 65027, key_skipped_count: 520125, block: {cache_hit_count: 830636, read_count: 38, read_byte: 2.35 MB}}}	eq(aweme_encryption.device.app_version, "16.8.0")	N/A	N/A
      └─TableRowIDScan_26	20.76	250121	cop[tikv]	table:device0_	tikv_task:{proc max:407ms, min:0s, p80:25ms, p95:174ms, iters:589, tasks:164}	keep order:false	N/A	N/A

h5n1 · 2022 年3 月 10 日 10:04

这个组合条件有多少记录

a398058068 · 2022 年3 月 10 日 10:05

14W

h5n1 · 2022 年3 月 10 日 10:20

量有点多

a398058068 · 2022 年3 月 10 日 10:25

目前cpu负载也比较高 qps在500左右。不知道是不是单纯的是因为qps导致的cpu高

h5n1 · 2022 年3 月 10 日 10:26

可以看tikv detail的thread CPU 哪类比较高。

a398058068 · 2022 年3 月 10 日 10:26

h5n1 · 2022 年3 月 10 日 10:27

还是读消耗的，看看其他的慢SQL有优化余地吗

a398058068 · 2022 年3 月 10 日 10:31

其他sql都挺正常的。看起来只有这个不正常。另外最上面Others 里不知道是什么。

查询速度慢 时间不稳定。

查询速度慢时间不稳定。