v6.1.1
一张2亿多数据的表 ，发现一个简单的带limit的SQL全表扫描执行要9分多钟。表除主键无其他索引PRIMARY KEY (cdrrowkey) /*T![clustered_index] NONCLUSTERED */ ，所有字段都是varchar(100)。SQL 扫描大部分时间都在cop 请求上。该表只有insert没有修改删除操作，从执行计划中可以看到 total_keys 和process keys数量差不多，基本没有mvcc的key，但key_skipped_count 要4亿多，这是跳过的那些key? 虽然是hdd盘(10 tikv,64C) 但2亿数据也不应该这么慢
SELECT count (?) `cnt` FROM ( SELECT * FROM `t_irov_usage_99_202309` WHERE `imsi` IN (xx,xx,...) AND `wlwflag` <> ? LIMIT ? ) `t`
另外这里平均扫描数为啥0？
较空闲状态时手动执行SQL，执行要5分30秒：
执行计划.txt (254.8 KB)
直接上tiflash是不是好点
为什么不直接count（*） 加个子查询效率差太多，key_skipped_count 是RocksDB 扫数据时遇到的已删除 (tombstone) Key 数量，你gc时间是不是设置太长了
找到个这个解释： internal_key_skipped_count increases on every Next() even for valid keys, so it doesn’t mean that it skipped that many unnecessary keys.
1、从rocksdb的信息可以看到发生的物理读其实很小，才15MB：block: {cache_hit_count: 4388130, read_count: 5557, read_byte: 19.5 MB}，所以应该和磁盘没有什么关系。
2、从TableFullScan_14算子可以看到全表扫描花费的时间较短：tikv_task:{proc max:3.27s, min:860ms, p80:1.93s, p95:2.21s, iters:209355, tasks:1414}。这种全表扫描copTask效率比较符合预期，不算慢，因此和表数据量以及全表扫描没有太大关系。
3、从Selection_15算子中可以看到存在大量的or(eq(cast(jfjsrh_gj.t_irov_usage_99_202309.imsi, double BINARY)，该算子执行耗时非常高：tikv_task:{proc max:20.3s, min:5.42s, p80:11.9s, p95:13.4s, iters:209355, tasks:1414}。最小的一个copTask都需要执行 min:5.42s,共执行1414次，占用大量的CPU时间（可能会占用较多CPU资源影响其它SQL执行效率）。
因此主要慢在Selection_15算子这一步，这里是做了where col in (…)条件过滤，过滤耗时太大。从actRows总本步骤才输出4条记录（总体limit 1000）可以看到符合条件的记录很少，所以怀疑是in (…太多常量…)中符合条件的记录数很少，导致一直不断的匹配+cast数据类型转换导致效率低下。
对于该问题归纳为 select count(*) from a where a.varchar_col1 in (1,2,3…) 因为in中常量太多且总是匹配不到且在做Selection过滤时候做cast转换导致大量消耗CPU进而导致整体效率低下。
将问题进一步分析看出在哪里：
当in中常量太多的情况下
1、在不发生数据转换的情况下，如果每行记录都能在in中匹配得到，那么效率相比都匹配不到是否会较高？
2、在发生数据转换的情况下，如果每行记录都能在in中匹配得到，那么效率相比都匹配不到是否会较高？
验证如下：
mysql> desc a;
+-------+--------------+------+------+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+------+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(100) | YES | | NULL | |
+-------+--------------+------+------+---------+-------+
2 rows in set (0.01 sec)
--name是varchar类型，只有一个1。
mysql> select count(*) from a;
+----------+
| count(*) |
+----------+
| 32768 |
+----------+
1 row in set (0.02 sec)
mysql> select distinct name from a;
+------+
| name |
+------+
| 1 |
+------+
1 row in set (0.02 sec)
mysql> select distinct id from a;
+------+
| id |
+------+
| 1 |
+------+
1 row in set (0.02 sec)
--关闭查询缓存
mysql> show config where name like 'tikv-client.copr-cache.capacity-mb';
+------+---------------------+------------------------------------+-------+
| Type | Instance | Name | Value |
+------+---------------------+------------------------------------+-------+
| tidb | 192.168.31.201:4003 | tikv-client.copr-cache.capacity-mb | 0 |
+------+---------------------+------------------------------------+-------+
1 row in set (0.01 sec)
验证情况1：
分别对in(常量)中将’1’作为开头，中间，最后，以及不包含1为条件进行验证（排除逆序等数据库内部判断方式，避免测试不准）
in常量开头包含’1’：
select count(*) from tpch1.a where name in ('1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18','19','20','21','22','23','24','25','26','27','28','29','30','31','32','33','34','35','36','37','38','39','40','41','42','43','44','45','46','47','48','49','50','51','52','53','54','55','56','57','58','59','60','61','62','63','64','65','66','67','68','69','70','71','72','73','74','75','76','77','78','79','80','81','82','83','84','85','86','87','88','89','90','91','92','93','94','95','96','97','98','99');
多次执行该语句，取平均耗时的执行计划：
mysql> explain analyze select count(*) from tpch1.a where name in ('1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18','19','20','21','22','23','24','25','26','27','28','29','30','31','32','33','34','35','36','37','38','39','40','41','42','43','44','45','46','47','48','49','50','51','52','53','54','55','56','57','58','59','60','61','62','63','64','65','66','67','68','69','70','71','72','73','74','75','76','77','78','79','80','81','82','83','84','85','86','87','88','89','90','91','92','93','94','95','96','97','98','99');
+------------------------------+----------+---------+-----------+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+------------------------------+----------+---------+-----------+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+------+
| StreamAgg_20 | 1.00 | 1 | root | | time:21.1ms, loops:2, RU:27.508586 | funcs:count(Column#6)->Column#4 | 210 Bytes | N/A |
| └─TableReader_21 | 1.00 | 1 | root | | time:21.1ms, loops:2, cop_task: {num: 1, max: 21ms, proc_keys: 32768, tot_proc: 19.6ms, tot_wait: 187.6µs, rpc_num: 1, rpc_time: 20.9ms, copr_cache: disabled, build_task_duration: 11µs, max_distsql_concurrency: 1} | data:StreamAgg_9 | 323 Bytes | N/A |
| └─StreamAgg_9 | 1.00 | 1 | cop[tikv] | | tikv_task:{time:19ms, loops:33}, scan_detail: {total_process_keys: 32768, total_process_keys_size: 1343488, total_keys: 32769, get_snapshot_time: 29.2µs, rocksdb: {delete_skipped_count: 16384, key_skipped_count: 49152, block: {cache_hit_count: 43}}} | funcs:count(1)->Column#6 | N/A | N/A |
| └─Selection_19 | 3244.03 | 32768 | cop[tikv] | | tikv_task:{time:19ms, loops:33} | in(tpch1.a.name, "1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "20", "21", "22", "23", "24", "25", "26", "27", "28", "29", "30", "31", "32", "33", "34", "35", "36", "37", "38", "39", "40", "41", "42", "43", "44", "45", "46", "47", "48", "49", "50", "51", "52", "53", "54", "55", "56", "57", "58", "59", "60", "61", "62", "63", "64", "65", "66", "67", "68", "69", "70", "71", "72", "73", "74", "75", "76", "77", "78", "79", "80", "81", "82", "83", "84", "85", "86", "87", "88", "89", "90", "91", "92", "93", "94", "95", "96", "97", "98", "99") | N/A | N/A |
| └─TableFullScan_18 | 32768.00 | 32768 | cop[tikv] | table:a | tikv_task:{time:18ms, loops:33} | keep order:false, stats:pseudo | N/A | N/A |
+------------------------------+----------+---------+-----------+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+------+
5 rows in set (0.02 sec)
in常量中间包含’1’：
select count(*) from tpch1.a where name in ('-50','-49','-48','-47','-46','-45','-44','-43','-42','-41','-40','-39','-38','-37','-36','-35','-34','-33','-32','-31','-30','-29','-28','-27','-26','-25','-24','-23','-22','-21','-20','-19','-18','-17','-16','-15','-14','-13','-12','-11','-10','-9','-8','-7','-6','-5','-4','-3','-2','-1','0','1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18','19','20','21','22','23','24','25','26','27','28','29','30','31','32','33','34','35','36','37','38','39','40','41','42','43','44','45','46','47','48','49');
多次执行该语句，取平均耗时的执行计划：
mysql> explain analyze select count(*) from tpch1.a where name in ('-50','-49','-48','-47','-46','-45','-44','-43','-42','-41','-40','-39','-38','-37','-36','-35','-34','-33','-32','-31','-30','-29','-28','-27','-26','-25','-24','-23','-22','-21','-20','-19','-18','-17','-16','-15','-14','-13','-12','-11','-10','-9','-8','-7','-6','-5','-4','-3','-2','-1','0','1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18','19','20','21','22','23','24','25','26','27','28','29','30','31','32','33','34','35','36','37','38','39','40','41','42','43','44','45','46','47','48','49');
+------------------------------+----------+---------+-----------+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+------------------------------+----------+---------+-----------+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+------+
| StreamAgg_20 | 1.00 | 1 | root | | time:18.5ms, loops:2, RU:26.695383 | funcs:count(Column#6)->Column#4 | 210 Bytes | N/A |
| └─TableReader_21 | 1.00 | 1 | root | | time:18.5ms, loops:2, cop_task: {num: 1, max: 18.3ms, proc_keys: 32768, tot_proc: 17.2ms, tot_wait: 121.6µs, rpc_num: 1, rpc_time: 18.3ms, copr_cache: disabled, build_task_duration: 9.34µs, max_distsql_concurrency: 1} | data:StreamAgg_9 | 323 Bytes | N/A |
| └─StreamAgg_9 | 1.00 | 1 | cop[tikv] | | tikv_task:{time:15ms, loops:33}, scan_detail: {total_process_keys: 32768, total_process_keys_size: 1343488, total_keys: 32769, get_snapshot_time: 22.6µs, rocksdb: {delete_skipped_count: 16384, key_skipped_count: 49152, block: {cache_hit_count: 43}}} | funcs:count(1)->Column#6 | N/A | N/A |
| └─Selection_19 | 3276.80 | 32768 | cop[tikv] | | tikv_task:{time:14ms, loops:33} | in(tpch1.a.name, "-50", "-49", "-48", "-47", "-46", "-45", "-44", "-43", "-42", "-41", "-40", "-39", "-38", "-37", "-36", "-35", "-34", "-33", "-32", "-31", "-30", "-29", "-28", "-27", "-26", "-25", "-24", "-23", "-22", "-21", "-20", "-19", "-18", "-17", "-16", "-15", "-14", "-13", "-12", "-11", "-10", "-9", "-8", "-7", "-6", "-5", "-4", "-3", "-2", "-1", "0", "1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "20", "21", "22", "23", "24", "25", "26", "27", "28", "29", "30", "31", "32", "33", "34", "35", "36", "37", "38", "39", "40", "41", "42", "43", "44", "45", "46", "47", "48", "49") | N/A | N/A |
| └─TableFullScan_18 | 32768.00 | 32768 | cop[tikv] | table:a | tikv_task:{time:10ms, loops:33} | keep order:false, stats:pseudo | N/A | N/A |
+------------------------------+----------+---------+-----------+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+------+
5 rows in set (0.02 sec)
in常量结尾包含’1’：
select count(*) from tpch1.a where name in ('-98','-97','-96','-95','-94','-93','-92','-91','-90','-89','-88','-87','-86','-85','-84','-83','-82','-81','-80','-79','-78','-77','-76','-75','-74','-73','-72','-71','-70','-69','-68','-67','-66','-65','-64','-63','-62','-61','-60','-59','-58','-57','-56','-55','-54','-53','-52','-51','-50','-49','-48','-47','-46','-45','-44','-43','-42','-41','-40','-39','-38','-37','-36','-35','-34','-33','-32','-31','-30','-29','-28','-27','-26','-25','-24','-23','-22','-21','-20','-19','-18','-17','-16','-15','-14','-13','-12','-11','-10','-9','-8','-7','-6','-5','-4','-3','-2','-1','0','1');
多次执行该语句，取平均耗时的执行计划：
mysql> explain analyze select count(*) from tpch1.a where name in ('-98','-97','-96','-95','-94','-93','-92','-91','-90','-89','-88','-87','-86','-85','-84','-83','-82','-81','-80','-79','-78','-77','-76','-75','-74','-73','-72','-71','-70','-69','-68','-67','-66','-65','-64','-63','-62','-61','-60','-59','-58','-57','-56','-55','-54','-53','-52','-51','-50','-49','-48','-47','-46','-45','-44','-43','-42','-41','-40','-39','-38','-37','-36','-35','-34','-33','-32','-31','-30','-29','-28','-27','-26','-25','-24','-23','-22','-21','-20','-19','-18','-17','-16','-15','-14','-13','-12','-11','-10','-9','-8','-7','-6','-5','-4','-3','-2','-1','0','1');
+------------------------------+----------+---------+-----------+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+------------------------------+----------+---------+-----------+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+------+
| StreamAgg_20 | 1.00 | 1 | root | | time:17ms, loops:2, RU:26.311064 | funcs:count(Column#6)->Column#4 | 210 Bytes | N/A |
| └─TableReader_21 | 1.00 | 1 | root | | time:17ms, loops:2, cop_task: {num: 1, max: 16.8ms, proc_keys: 32768, tot_proc: 16ms, tot_wait: 89.6µs, rpc_num: 1, rpc_time: 16.8ms, copr_cache: disabled, build_task_duration: 11.8µs, max_distsql_concurrency: 1} | data:StreamAgg_9 | 323 Bytes | N/A |
| └─StreamAgg_9 | 1.00 | 1 | cop[tikv] | | tikv_task:{time:16ms, loops:33}, scan_detail: {total_process_keys: 32768, total_process_keys_size: 1343488, total_keys: 32769, get_snapshot_time: 23.7µs, rocksdb: {delete_skipped_count: 16384, key_skipped_count: 49152, block: {cache_hit_count: 43}}} | funcs:count(1)->Column#6 | N/A | N/A |
| └─Selection_19 | 3276.80 | 32768 | cop[tikv] | | tikv_task:{time:16ms, loops:33} | in(tpch1.a.name, "-98", "-97", "-96", "-95", "-94", "-93", "-92", "-91", "-90", "-89", "-88", "-87", "-86", "-85", "-84", "-83", "-82", "-81", "-80", "-79", "-78", "-77", "-76", "-75", "-74", "-73", "-72", "-71", "-70", "-69", "-68", "-67", "-66", "-65", "-64", "-63", "-62", "-61", "-60", "-59", "-58", "-57", "-56", "-55", "-54", "-53", "-52", "-51", "-50", "-49", "-48", "-47", "-46", "-45", "-44", "-43", "-42", "-41", "-40", "-39", "-38", "-37", "-36", "-35", "-34", "-33", "-32", "-31", "-30", "-29", "-28", "-27", "-26", "-25", "-24", "-23", "-22", "-21", "-20", "-19", "-18", "-17", "-16", "-15", "-14", "-13", "-12", "-11", "-10", "-9", "-8", "-7", "-6", "-5", "-4", "-3", "-2", "-1", "0", "1") | N/A | N/A |
| └─TableFullScan_18 | 32768.00 | 32768 | cop[tikv] | table:a | tikv_task:{time:15ms, loops:33} | keep order:false, stats:pseudo | N/A | N/A |
+------------------------------+----------+---------+-----------+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+------+
5 rows in set (0.02 sec)
in常量不包含’1’：
SQL语句：
select count(*) from tpch1.a where name in ('2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18','19','20','21','22','23','24','25','26','27','28','29','30','31','32','33','34','35','36','37','38','39','40','41','42','43','44','45','46','47','48','49','50','51','52','53','54','55','56','57','58','59','60','61','62','63','64','65','66','67','68','69','70','71','72','73','74','75','76','77','78','79','80','81','82','83','84','85','86','87','88','89','90','91','92','93','94','95','96','97','98','99','100');
多次执行该语句，取平均耗时的执行计划：
mysql> explain analyze select count(*) from tpch1.a where name in ('2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18','19','20','21','22','23','24','25','26','27','28','29','30','31','32','33','34','35','36','37','38','39','40','41','42','43','44','45','46','47','48','49','50','51','52','53','54','55','56','57','58','59','60','61','62','63','64','65','66','67','68','69','70','71','72','73','74','75','76','77','78','79','80','81','82','83','84','85','86','87','88','89','90','91','92','93','94','95','96','97','98','99','100');
+------------------------------+----------+---------+-----------+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+------------------------------+----------+---------+-----------+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+------+
| StreamAgg_20 | 1.00 | 1 | root | | time:19.7ms, loops:2, RU:27.148572 | funcs:count(Column#6)->Column#4 | 210 Bytes | N/A |
| └─TableReader_21 | 1.00 | 0 | root | | time:19.7ms, loops:1, cop_task: {num: 1, max: 19.6ms, proc_keys: 32768, tot_proc: 18.5ms, tot_wait: 98µs, rpc_num: 1, rpc_time: 19.6ms, copr_cache: disabled, build_task_duration: 36.6µs, max_distsql_concurrency: 1} | data:StreamAgg_9 | 301 Bytes | N/A |
| └─StreamAgg_9 | 1.00 | 0 | cop[tikv] | | tikv_task:{time:19ms, loops:33}, scan_detail: {total_process_keys: 32768, total_process_keys_size: 1343488, total_keys: 32769, get_snapshot_time: 25µs, rocksdb: {delete_skipped_count: 16384, key_skipped_count: 49152, block: {cache_hit_count: 43}}} | funcs:count(1)->Column#6 | N/A | N/A |
| └─Selection_19 | 3244.03 | 0 | cop[tikv] | | tikv_task:{time:19ms, loops:33} | in(tpch1.a.name, "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "20", "21", "22", "23", "24", "25", "26", "27", "28", "29", "30", "31", "32", "33", "34", "35", "36", "37", "38", "39", "40", "41", "42", "43", "44", "45", "46", "47", "48", "49", "50", "51", "52", "53", "54", "55", "56", "57", "58", "59", "60", "61", "62", "63", "64", "65", "66", "67", "68", "69", "70", "71", "72", "73", "74", "75", "76", "77", "78", "79", "80", "81", "82", "83", "84", "85", "86", "87", "88", "89", "90", "91", "92", "93", "94", "95", "96", "97", "98", "99", "100") | N/A | N/A |
| └─TableFullScan_18 | 32768.00 | 32768 | cop[tikv] | table:a | tikv_task:{time:15ms, loops:33} | keep order:false, stats:pseudo | N/A | N/A |
+------------------------------+----------+---------+-----------+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+------+
5 rows in set (0.02 sec)
可以看到：
在不发生 数据转换的情况下，如果每行记录都能在 in中匹配得到，那么效率相比都匹配不到的效率并不会变高。
验证情况2：
in常量包含1，需要做cast转换：
select count(*) from tpch1.a where name in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99);
多次执行该语句，取平均耗时的执行计划：
mysql> explain analyze select count(*) from tpch1.a where name in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99);
+------------------------------+----------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+------------------------------+----------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+------+
| StreamAgg_20 | 1.00 | 1 | root | | time:208ms, loops:2, RU:89.833495 | funcs:count(Column#6)->Column#4 | 210 Bytes | N/A |
| └─TableReader_21 | 1.00 | 1 | root | | time:208ms, loops:2, cop_task: {num: 1, max: 207.9ms, proc_keys: 32768, tot_proc: 206.6ms, tot_wait: 111.3µs, rpc_num: 1, rpc_time: 207.9ms, copr_cache: disabled, build_task_duration: 10.4µs, max_distsql_concurrency: 1} | data:StreamAgg_9 | 325 Bytes | N/A |
| └─StreamAgg_9 | 1.00 | 1 | cop[tikv] | | tikv_task:{time:207ms, loops:33}, scan_detail: {total_process_keys: 32768, total_process_keys_size: 1343488, total_keys: 32769, get_snapshot_time: 30.1µs, rocksdb: {delete_skipped_count: 16384, key_skipped_count: 49152, block: {cache_hit_count: 43}}} | funcs:count(1)->Column#6 | N/A | N/A |
| └─Selection_19 | 32768.00 | 32768 | cop[tikv] | | tikv_task:{time:207ms, loops:33} | or(or(or(or(or(or(eq(cast(tpch1.a.name, double BINARY), 1), or(eq(cast(tpch1.a.name, double BINARY), 2), eq(cast(tpch1.a.name, double BINARY), 3))), or(eq(cast(tpch1.a.name, double BINARY), 4), or(eq(cast(tpch1.a.name, double BINARY), 5), eq(cast(tpch1.a.name, double BINARY), 6)))), or(or(eq(cast(tpch1.a.name, double BINARY), 7), or(eq(cast(tpch1.a.name, double BINARY), 8), eq(cast(tpch1.a.name, double BINARY), 9))), or(eq(cast(tpch1.a.name, double BINARY), 10), or(eq(cast(tpch1.a.name, double BINARY), 11), eq(cast(tpch1.a.name, double BINARY), 12))))), or(or(or(eq(cast(tpch1.a.name, double BINARY), 13), or(eq(cast(tpch1.a.name, double BINARY), 14), eq(cast(tpch1.a.name, double BINARY), 15))), or(eq(cast(tpch1.a.name, double BINARY), 16), or(eq(cast(tpch1.a.name, double BINARY), 17), eq(cast(tpch1.a.name, double BINARY), 18)))), or(or(eq(cast(tpch1.a.name, double BINARY), 19), or(eq(cast(tpch1.a.name, double BINARY), 20), eq(cast(tpch1.a.name, double BINARY), 21))), or(eq(cast(tpch1.a.name, double BINARY), 22), or(eq(cast(tpch1.a.name, double BINARY), 23), eq(cast(tpch1.a.name, double BINARY), 24)))))), or(or(or(or(eq(cast(tpch1.a.name, double BINARY), 25), or(eq(cast(tpch1.a.name, double BINARY), 26), eq(cast(tpch1.a.name, double BINARY), 27))), or(eq(cast(tpch1.a.name, double BINARY), 28), or(eq(cast(tpch1.a.name, double BINARY), 29), eq(cast(tpch1.a.name, double BINARY), 30)))), or(or(eq(cast(tpch1.a.name, double BINARY), 31), or(eq(cast(tpch1.a.name, double BINARY), 32), eq(cast(tpch1.a.name, double BINARY), 33))), or(eq(cast(tpch1.a.name, double BINARY), 34), or(eq(cast(tpch1.a.name, double BINARY), 35), eq(cast(tpch1.a.name, double BINARY), 36))))), or(or(or(eq(cast(tpch1.a.name, double BINARY), 37), or(eq(cast(tpch1.a.name, double BINARY), 38), eq(cast(tpch1.a.name, double BINARY), 39))), or(eq(cast(tpch1.a.name, double BINARY), 40), or(eq(cast(tpch1.a.name, double BINARY), 41), eq(cast(tpch1.a.name, double BINARY), 42)))), or(or(eq(cast(tpch1.a.name, double BINARY), 43), or(eq(cast(tpch1.a.name, double BINARY), 44), eq(cast(tpch1.a.name, double BINARY), 45))), or(or(eq(cast(tpch1.a.name, double BINARY), 46), eq(cast(tpch1.a.name, double BINARY), 47)), or(eq(cast(tpch1.a.name, double BINARY), 48), eq(cast(tpch1.a.name, double BINARY), 49))))))), or(or(or(or(or(eq(cast(tpch1.a.name, double BINARY), 50), or(eq(cast(tpch1.a.name, double BINARY), 51), eq(cast(tpch1.a.name, double BINARY), 52))), or(eq(cast(tpch1.a.name, double BINARY), 53), or(eq(cast(tpch1.a.name, double BINARY), 54), eq(cast(tpch1.a.name, double BINARY), 55)))), or(or(eq(cast(tpch1.a.name, double BINARY), 56), or(eq(cast(tpch1.a.name, double BINARY), 57), eq(cast(tpch1.a.name, double BINARY), 58))), or(eq(cast(tpch1.a.name, double BINARY), 59), or(eq(cast(tpch1.a.name, double BINARY), 60), eq(cast(tpch1.a.name, double BINARY), 61))))), or(or(or(eq(cast(tpch1.a.name, double BINARY), 62), or(eq(cast(tpch1.a.name, double BINARY), 63), eq(cast(tpch1.a.name, double BINARY), 64))), or(eq(cast(tpch1.a.name, double BINARY), 65), or(eq(cast(tpch1.a.name, double BINARY), 66), eq(cast(tpch1.a.name, double BINARY), 67)))), or(or(eq(cast(tpch1.a.name, double BINARY), 68), or(eq(cast(tpch1.a.name, double BINARY), 69), eq(cast(tpch1.a.name, double BINARY), 70))), or(or(eq(cast(tpch1.a.name, double BINARY), 71), eq(cast(tpch1.a.name, double BINARY), 72)), or(eq(cast(tpch1.a.name, double BINARY), 73), eq(cast(tpch1.a.name, double BINARY), 74)))))), or(or(or(or(eq(cast(tpch1.a.name, double BINARY), 75), or(eq(cast(tpch1.a.name, double BINARY), 76), eq(cast(tpch1.a.name, double BINARY), 77))), or(eq(cast(tpch1.a.name, double BINARY), 78), or(eq(cast(tpch1.a.name, double BINARY), 79), eq(cast(tpch1.a.name, double BINARY), 80)))), or(or(eq(cast(tpch1.a.name, double BINARY), 81), or(eq(cast(tpch1.a.name, double BINARY), 82), eq(cast(tpch1.a.name, double BINARY), 83))), or(eq(cast(tpch1.a.name, double BINARY), 84), or(eq(cast(tpch1.a.name, double BINARY), 85), eq(cast(tpch1.a.name, double BINARY), 86))))), or(or(or(eq(cast(tpch1.a.name, double BINARY), 87), or(eq(cast(tpch1.a.name, double BINARY), 88), eq(cast(tpch1.a.name, double BINARY), 89))), or(eq(cast(tpch1.a.name, double BINARY), 90), or(eq(cast(tpch1.a.name, double BINARY), 91), eq(cast(tpch1.a.name, double BINARY), 92)))), or(or(eq(cast(tpch1.a.name, double BINARY), 93), or(eq(cast(tpch1.a.name, double BINARY), 94), eq(cast(tpch1.a.name, double BINARY), 95))), or(or(eq(cast(tpch1.a.name, double BINARY), 96), eq(cast(tpch1.a.name, double BINARY), 97)), or(eq(cast(tpch1.a.name, double BINARY), 98), eq(cast(tpch1.a.name, double BINARY), 99)))))))) | N/A | N/A |
| └─TableFullScan_18 | 32768.00 | 32768 | cop[tikv] | table:a | tikv_task:{time:11ms, loops:33} | keep order:false, stats:pseudo | N/A | N/A |
+------------------------------+----------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+------+
5 rows in set (0.21 sec)
in常量不包含1，需要做cast转换：
select count(*) from tpch1.a where name in (2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100);
多次执行该语句，取平均耗时的执行计划：
mysql> explain analyze select count(*) from tpch1.a where name in (2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100);
+------------------------------+----------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+------------------------------+----------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+------+
| StreamAgg_20 | 1.00 | 1 | root | | time:205.9ms, loops:2, RU:88.956662 | funcs:count(Column#6)->Column#4 | 210 Bytes | N/A |
| └─TableReader_21 | 1.00 | 0 | root | | time:205.9ms, loops:1, cop_task: {num: 1, max: 205.8ms, proc_keys: 32768, tot_proc: 203.9ms, tot_wait: 223µs, rpc_num: 1, rpc_time: 205.8ms, copr_cache: disabled, build_task_duration: 29.6µs, max_distsql_concurrency: 1} | data:StreamAgg_9 | 303 Bytes | N/A |
| └─StreamAgg_9 | 1.00 | 0 | cop[tikv] | | tikv_task:{time:204ms, loops:33}, scan_detail: {total_process_keys: 32768, total_process_keys_size: 1343488, total_keys: 32769, get_snapshot_time: 39.9µs, rocksdb: {delete_skipped_count: 16384, key_skipped_count: 49152, block: {cache_hit_count: 43}}} | funcs:count(1)->Column#6 | N/A | N/A |
| └─Selection_19 | 32768.00 | 0 | cop[tikv] | | tikv_task:{time:204ms, loops:33} | or(or(or(or(or(or(eq(cast(tpch1.a.name, double BINARY), 2), or(eq(cast(tpch1.a.name, double BINARY), 3), eq(cast(tpch1.a.name, double BINARY), 4))), or(eq(cast(tpch1.a.name, double BINARY), 5), or(eq(cast(tpch1.a.name, double BINARY), 6), eq(cast(tpch1.a.name, double BINARY), 7)))), or(or(eq(cast(tpch1.a.name, double BINARY), 8), or(eq(cast(tpch1.a.name, double BINARY), 9), eq(cast(tpch1.a.name, double BINARY), 10))), or(eq(cast(tpch1.a.name, double BINARY), 11), or(eq(cast(tpch1.a.name, double BINARY), 12), eq(cast(tpch1.a.name, double BINARY), 13))))), or(or(or(eq(cast(tpch1.a.name, double BINARY), 14), or(eq(cast(tpch1.a.name, double BINARY), 15), eq(cast(tpch1.a.name, double BINARY), 16))), or(eq(cast(tpch1.a.name, double BINARY), 17), or(eq(cast(tpch1.a.name, double BINARY), 18), eq(cast(tpch1.a.name, double BINARY), 19)))), or(or(eq(cast(tpch1.a.name, double BINARY), 20), or(eq(cast(tpch1.a.name, double BINARY), 21), eq(cast(tpch1.a.name, double BINARY), 22))), or(eq(cast(tpch1.a.name, double BINARY), 23), or(eq(cast(tpch1.a.name, double BINARY), 24), eq(cast(tpch1.a.name, double BINARY), 25)))))), or(or(or(or(eq(cast(tpch1.a.name, double BINARY), 26), or(eq(cast(tpch1.a.name, double BINARY), 27), eq(cast(tpch1.a.name, double BINARY), 28))), or(eq(cast(tpch1.a.name, double BINARY), 29), or(eq(cast(tpch1.a.name, double BINARY), 30), eq(cast(tpch1.a.name, double BINARY), 31)))), or(or(eq(cast(tpch1.a.name, double BINARY), 32), or(eq(cast(tpch1.a.name, double BINARY), 33), eq(cast(tpch1.a.name, double BINARY), 34))), or(eq(cast(tpch1.a.name, double BINARY), 35), or(eq(cast(tpch1.a.name, double BINARY), 36), eq(cast(tpch1.a.name, double BINARY), 37))))), or(or(or(eq(cast(tpch1.a.name, double BINARY), 38), or(eq(cast(tpch1.a.name, double BINARY), 39), eq(cast(tpch1.a.name, double BINARY), 40))), or(eq(cast(tpch1.a.name, double BINARY), 41), or(eq(cast(tpch1.a.name, double BINARY), 42), eq(cast(tpch1.a.name, double BINARY), 43)))), or(or(eq(cast(tpch1.a.name, double BINARY), 44), or(eq(cast(tpch1.a.name, double BINARY), 45), eq(cast(tpch1.a.name, double BINARY), 46))), or(or(eq(cast(tpch1.a.name, double BINARY), 47), eq(cast(tpch1.a.name, double BINARY), 48)), or(eq(cast(tpch1.a.name, double BINARY), 49), eq(cast(tpch1.a.name, double BINARY), 50))))))), or(or(or(or(or(eq(cast(tpch1.a.name, double BINARY), 51), or(eq(cast(tpch1.a.name, double BINARY), 52), eq(cast(tpch1.a.name, double BINARY), 53))), or(eq(cast(tpch1.a.name, double BINARY), 54), or(eq(cast(tpch1.a.name, double BINARY), 55), eq(cast(tpch1.a.name, double BINARY), 56)))), or(or(eq(cast(tpch1.a.name, double BINARY), 57), or(eq(cast(tpch1.a.name, double BINARY), 58), eq(cast(tpch1.a.name, double BINARY), 59))), or(eq(cast(tpch1.a.name, double BINARY), 60), or(eq(cast(tpch1.a.name, double BINARY), 61), eq(cast(tpch1.a.name, double BINARY), 62))))), or(or(or(eq(cast(tpch1.a.name, double BINARY), 63), or(eq(cast(tpch1.a.name, double BINARY), 64), eq(cast(tpch1.a.name, double BINARY), 65))), or(eq(cast(tpch1.a.name, double BINARY), 66), or(eq(cast(tpch1.a.name, double BINARY), 67), eq(cast(tpch1.a.name, double BINARY), 68)))), or(or(eq(cast(tpch1.a.name, double BINARY), 69), or(eq(cast(tpch1.a.name, double BINARY), 70), eq(cast(tpch1.a.name, double BINARY), 71))), or(or(eq(cast(tpch1.a.name, double BINARY), 72), eq(cast(tpch1.a.name, double BINARY), 73)), or(eq(cast(tpch1.a.name, double BINARY), 74), eq(cast(tpch1.a.name, double BINARY), 75)))))), or(or(or(or(eq(cast(tpch1.a.name, double BINARY), 76), or(eq(cast(tpch1.a.name, double BINARY), 77), eq(cast(tpch1.a.name, double BINARY), 78))), or(eq(cast(tpch1.a.name, double BINARY), 79), or(eq(cast(tpch1.a.name, double BINARY), 80), eq(cast(tpch1.a.name, double BINARY), 81)))), or(or(eq(cast(tpch1.a.name, double BINARY), 82), or(eq(cast(tpch1.a.name, double BINARY), 83), eq(cast(tpch1.a.name, double BINARY), 84))), or(eq(cast(tpch1.a.name, double BINARY), 85), or(eq(cast(tpch1.a.name, double BINARY), 86), eq(cast(tpch1.a.name, double BINARY), 87))))), or(or(or(eq(cast(tpch1.a.name, double BINARY), 88), or(eq(cast(tpch1.a.name, double BINARY), 89), eq(cast(tpch1.a.name, double BINARY), 90))), or(eq(cast(tpch1.a.name, double BINARY), 91), or(eq(cast(tpch1.a.name, double BINARY), 92), eq(cast(tpch1.a.name, double BINARY), 93)))), or(or(eq(cast(tpch1.a.name, double BINARY), 94), or(eq(cast(tpch1.a.name, double BINARY), 95), eq(cast(tpch1.a.name, double BINARY), 96))), or(or(eq(cast(tpch1.a.name, double BINARY), 97), eq(cast(tpch1.a.name, double BINARY), 98)), or(eq(cast(tpch1.a.name, double BINARY), 99), eq(cast(tpch1.a.name, double BINARY), 100)))))))) | N/A | N/A |
| └─TableFullScan_18 | 32768.00 | 32768 | cop[tikv] | table:a | tikv_task:{time:15ms, loops:33} | keep order:false, stats:pseudo | N/A | N/A |
+------------------------------+----------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+------+
5 rows in set (0.21 sec)
可以看到：
在发生 数据转换的情况下，如果每行记录都能在 in中匹配得到，那么效率相比都匹配不到的效率并不会变高。整体相比不发生数据转换的情况效率低很多。
因此这个问题主要是因为数据类型不一致导致的效率低下。
说了半天，回到你的那个问题，为何当前表只有insert（没有delete、update）但key_skipped_count 要4亿多然而total_keys才2亿？我也不清楚 
，我认为key_skipped_count 应该也是2亿多才对，期待大牛解答。
从上面这个测试来看数据类型不一致效率低10倍左右（随着in条件多可能更慢）。为何效率会这么低呢，从执行计划上看起来是对in中每次常量做匹配时候都要对a.name做cast数据类型转换。如果Selection算子只对cast(tpch1.a.name, double BINARY) 处理一次，然后和in里面内容做filter那么效率不会就高多了么？所以我感觉这块数据库产品本身还是有优化空间的。
听君一席话胜似一席话！
这个key_skipped_count值我自己测试了一下，随便建一个表，插4条数据，select * from t。key_skipped_count=4.
然后update 整表，key_skipped_count=8
再delete 一条，key_skipped_count=9
这个统计的应该就是包含mvcc信息的值。而且我等了20分钟，gc的safe point过去了，这个key_skipped_count还是等于9.
应该和delete何时释放空间的问题一样，要等到compaction才会释放。
这个值才会降下去。
rocksdb里面对这个值的注释也有一大段。
我看了下也无非就是delete /tombstone之类的说法。
key_skipped_count 是 rocksdb 里面的 key 的概念
而 total_keys / process_keys 是我们的 mvcc key 的概念
两者虽然都叫 “key”，但实际上是不同的东西
rockdb 提供的是 key-value 的 api
tidb 会用这套 kv 的 api 实现 mvcc 的 api
mvcc 的 key 是这样的，比如说一开始，写入一条 a = 3 的记录
那么 mvcc 层的 api 对外展示是 a => 3
而 mvcc 层的内部实现是 a_v0 => 3
从 rocksdb 层提供的 api 观察是 a_v0 => 3
而 rocksdb 层内部实现，a_v0[0] => 3
之后修改这条记录, 变成 a = 4
那么 mvcc 层的 api 对外展示是 a => 4
而 mvcc 层的内部实现是
a_v0 => 3
a_v1 => 4
到 rocksdb 层也是这样，因为 mvcc 是用 rocksdb 提供的 api 来实现的
a_v0 => 3
a_v1 => 4
接下来，我们删除 a
那么 mvcc 层的 api 对外展示是 a => 3
而 mvcc 层的内部实现是
a_v0 => 3
a_v1 => 4
a_v2 => tombstone
删除都不是真的删除，而是标记为 tombstome
我们 mvcc 层的 GC 是做什么呢?是清理掉 mvcc 遗留的历史版本数据，比如这里，做完 GC 之后会变成:
a_v2 => tombstone
这两条 key 去哪里了呢? 这两条 key 被我们调用 rocksdb 的 api，delete 掉了
a_v0 => 3
a_v1 => 4
关键点来了！rocksdb 的删除，它也不是真正的删除，在它内部其实也是标记删除，直到 compaction 之后才真正意义上删除。所以这两个 key 即使在 mvcc 层做过 GC 了，也还在 rocksdb internal 的 tombstone，影响 key_skipped_count … 直到在 rocksdb 那层也做过 compaction
此话题已在最后回复的 60 天后被自动关闭。不再允许新回复。