tidb等值查询，在实际有匹配数据的情况下无法查询到数据，

🪐 TiDB 技术问题 性能调优
1

为提高效率，提问时请提供以下信息，问题描述清晰可优先响应。

  • 【TiDB 版本】：3.0.5
  • 【问题描述】：tidb等值查询，在实际有数据库的情况下，无法查询到数据，

若提问为性能优化、故障排查类问题，请下载脚本运行。终端输出打印结果，请务必全选并复制粘贴上传。

2

– 直接查询无返回值；
mysql> select risk_code
from tidb_bi.saas_risk_result
where risk_code = ‘RISK_01_NOT_IN_INCUMBENT_LIST’;
Empty set

– 列出列的不同值，包含该值
mysql> select DISTINCT risk_code from tidb_bi.saas_risk_result;
±---------------------------------+
| risk_code |
±---------------------------------+
| RISK_01_ABSENCE |
| RISK_01_NOT_IN_INCUMBENT_LIST |
| RISK_01_NOT_MOVED_AFTER_SIGN |
±---------------------------------+
10 rows in set (0.48 sec)

– 通过指定ID加上，等值条件正常返回值；
mysql> select risk_code from tidb_bi.saas_risk_result where risk_code = ‘RISK_01_NOT_IN_INCUMBENT_LIST’ and id = 2945
-> ;
±------------------------------+
| risk_code |
±------------------------------+
| RISK_01_NOT_IN_INCUMBENT_LIST |
±------------------------------+
1 row in set (0.10 sec)

– 且相互比较也是一致的，等值的
mysql> select risk_code=‘RISK_01_NOT_IN_INCUMBENT_LIST’ from tidb_bi.saas_risk_result where id = 2945
;
±------------------------------------------+
| risk_code=‘RISK_01_NOT_IN_INCUMBENT_LIST’ |
±------------------------------------------+
| 1 |
±------------------------------------------+
1 row in set (0.13 sec)

3

你好，

辛苦将一下 sql 结果反馈文本结果。

explain select risk_code
from tidb_bi.saas_risk_result
where risk_code = ‘RISK_01_NOT_IN_INCUMBENT_LIST’;

select risk_code from tidb_bi.saas_risk_result where risk_code = ‘RISK_01_NOT_IN_INCUMBENT_LIST’ and id = 2945;

在执行 select risk_code from tidb_bi.saas_risk_result where risk_code = ‘RISK_01_NOT_IN_INCUMBENT_LIST’; 语句时，对应的 tidb.log 看是否有信息返回，并一起上传 下

4

mysql> explain select risk_code
from tidb_bi.saas_risk_result
where risk_code = ‘RISK_01_NOT_IN_INCUMBENT_LIST’;

+-------------------+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id                | count | task | operator info                                                                                                                                                                                            |
+-------------------+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| IndexReader_6     | 0.00  | root | index:IndexScan_5                                                                                                                                                                                        |
| └─IndexScan_5 | 0.00  | cop  | table:saas_risk_result, index:risk_code, range:["RISK_01_NOT_IN_INCUMBENT_LIST                                   ","RISK_01_NOT_IN_INCUMBENT_LIST                                   "], keep order:false |
+-------------------+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.06 sec)
5

[2020/07/07 16:13:16.352 +08:00] [INFO] [coprocessor.go:743] ["[TIME_COP_WAIT] resp_time:483.373261ms txnStartTS:417886265888210985 region_id:2024999 store_addr:192.168.111.243:20160 kv_wait_ms:478"]
[2020/07/07 16:13:16.836 +08:00] [INFO] [coprocessor.go:743] ["[TIME_COP_WAIT] resp_time:457.343797ms txnStartTS:417886266032390145 region_id:2024999 store_addr:192.168.111.243:20160 kv_wait_ms:443"]
[2020/07/07 16:13:18.769 +08:00] [INFO] [coprocessor.go:743] ["[TIME_COP_WAIT] resp_time:326.99042ms txnStartTS:417886266556678145 region_id:2048094 store_addr:192.168.111.243:20160 kv_wait_ms:272"]
[2020/07/07 16:13:18.984 +08:00] [INFO] [coprocessor.go:743] ["[TIME_COP_WAIT] resp_time:450.300096ms txnStartTS:417886266595999750 region_id:72 store_addr:192.168.111.243:20160 kv_wait_ms:444"]
[2020/07/07 16:13:19.638 +08:00] [INFO] [coprocessor.go:743] ["[TIME_COP_WAIT] resp_time:866.977645ms txnStartTS:417886266648428551 region_id:2024999 store_addr:192.168.111.243:20160 kv_wait_ms:865"]
[2020/07/07 16:13:22.399 +08:00] [INFO] [coprocessor.go:743] ["[TIME_COP_WAIT] resp_time:414.018032ms txnStartTS:417886267500396550 region_id:72 store_addr:192.168.111.243:20160 kv_wait_ms:404"]
[2020/07/07 16:13:25.824 +08:00] [INFO] [coprocessor.go:743] ["[TIME_COP_WAIT] resp_time:1.06221031s txnStartTS:417886268221292576 region_id:44 store_addr:192.168.111.243:20160 kv_wait_ms:1057"]

6

– 使用 in 查询可以正常返回值
mysql> select risk_code
from tidb_bi.saas_risk_result
where risk_code in (‘RISK_01_NOT_IN_INCUMBENT_LIST’) limit 5;
±------------------------------+
| risk_code |
±------------------------------+
| RISK_01_NOT_IN_INCUMBENT_LIST |
| RISK_01_NOT_IN_INCUMBENT_LIST |
| RISK_01_NOT_IN_INCUMBENT_LIST |
| RISK_01_NOT_IN_INCUMBENT_LIST |
| RISK_01_NOT_IN_INCUMBENT_LIST |
±------------------------------+
5 rows in set (0.06 sec)

    • 列表条目
7

您好，麻烦反馈下表结构，多谢。

8

。

Field Type Collation Null Key Default Extra
id bigint(32) unsigned NO PRI auto_increment
customer_center_id int(11) YES
customer_no varchar(32) utf8mb4_bin YES
staff_id int(11) YES
risk_code varchar(64) YES MUL
risk_desc varchar(255) utf8mb4_bin YES
risk_time datetime YES MUL
9

抱歉此问题，需要等研发同事确认后，答复，多谢。

10

好的，麻烦了

11

:ok_hand:

12

https://github.com/pingcap/tidb/pull/14007 此问题在 V3.0.8之后的版本修复，建议升级到最新版本 v3.0.16，多谢。