【 TiDB 使用环境】生产环境 /测试/ Poc
【 TiDB 版本】
【复现路径】做过哪些操作出现的问题
【遇到的问题：问题现象及影响】
【资源配置】进入到 TiDB Dashboard -集群信息 (Cluster Info) -主机(Hosts) 截图此页面
【附件：截图/日志/监控】
查询某用户是否存在风险表里
SELECT risk_value AS risk_t
FROM user_risk
WHERE risk_value = (
SELECT mobile
FROM user
WHERE id = 12345
)
AND risk_item = 1
AND risk_type = 1
AND is_deleted = 0
AND status = 1
表结构
CREATE TABLE `user_risk` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键id',
...
`risk_item` smallint(2) NOT NULL COMMENT '风险项',
`risk_type` smallint(2) NOT NULL COMMENT '风险类型',
`risk_value` varchar(128) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '风险项对应的值,混合型',
PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */,
...
KEY `risk_value` (`risk_item`,`risk_type`,`risk_value`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci AUTO_INCREMENT=8536500 COMMENT='风险表'
CREATE TABLE `user` (
`id` bigint(20) NOT NULL COMMENT '账户id',
`mobile` varchar(32) DEFAULT '' COMMENT '手机号',
...
PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */,
...
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='账户表'
执行以下查询，可以用到复合索引
SELECT risk_value AS risk_t
FROM user_risk
WHERE risk_value = (
SELECT mobile
FROM user
WHERE id = 12345
)
AND risk_item = 1
AND risk_type = 1
+-----------------------------+------------+-----------+---------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+-----------------------------+------------+-----------+---------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------+
| Projection_11 | 2433100.99 | root | | user_risk.risk_value |
| └─IndexReader_14 | 2433100.99 | root | | index:Selection_13 |
| └─Selection_13 | 2433100.99 | cop[tikv] | | eq(user_risk.risk_value, "xxx") |
| └─IndexRangeScan_12 | 3041376.23 | cop[tikv] | table:user_risk, index:idx_risk_value(risk_item, risk_type, risk_value) | range:[1 1,1 1], keep order:false |
+-----------------------------+------------+-----------+---------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------+
如果加上is_deleted=0 and status=1，则全表扫描
+----------------------------+------------+-----------+---------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+----------------------------+------------+-----------+---------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Projection_11 | 2351569.96 | root | | user_risk.risk_value |
| └─TableReader_14 | 2351569.96 | root | | data:Selection_13 |
| └─Selection_13 | 2351569.96 | cop[tikv] | | eq(user_risk.risk_value, "xxx"), eq(user_risk.is_deleted, 0), eq(user_risk.risk_item, 1), eq(user_risk.risk_type, 1), eq(user_risk.status, 1) |
| └─TableFullScan_12 | 8450909.00 | cop[tikv] | table:user_risk | keep order:false |
+----------------------------+------------+-----------+---------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
这种如何优化？按业务需求，执行select id from user where id=12345 and exist(select * from user_risk where user_risk=mobile and …)更符合语义，但是更慢。