Optimization of Slow Queries Without Using Indexes

Translated
1

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: 慢查优化，不走索引

| username: TiDB_C罗

[TiDB Usage Environment] Production Environment / Testing / Poc
[TiDB Version]
[Reproduction Path] What operations were performed that caused the issue
[Encountered Issue: Issue Phenomenon and Impact]
[Resource Configuration] Go to TiDB Dashboard - Cluster Info - Hosts and take a screenshot of this page
[Attachments: Screenshots / Logs / Monitoring]
Query whether a certain user exists in the risk table

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

Table structure

CREATE TABLE `user_risk` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'Primary key id',
  ...
  `risk_item` smallint(2) NOT NULL COMMENT 'Risk item',
  `risk_type` smallint(2) NOT NULL COMMENT 'Risk type',
  `risk_value` varchar(128) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Value corresponding to the risk item, mixed type',
  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='Risk table'


CREATE TABLE `user` (
  `id` bigint(20) NOT NULL COMMENT 'Account id',
  `mobile` varchar(32)  DEFAULT '' COMMENT 'Mobile number',
  ...
  PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */,
  ...
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='Account table'

Executing the following query can use the composite index

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                                      |
+-----------------------------+------------+-----------+---------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------+

If you add is_deleted=0 and status=1, it will perform a full table scan


+----------------------------+------------+-----------+---------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 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                                                                                                                                                                                                                             |
+----------------------------+------------+-----------+---------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

How to optimize this? According to business requirements, executing select id from user where id=12345 and exist(select * from user_risk where user_risk=mobile and ....) is more semantically appropriate, but slower.

2
| username: 小鱼吃大鱼 | Original post link

Can subqueries be changed to join queries?

3
| username: 这里介绍不了我 | Original post link
  1. Change subquery to join query
  2. Add an index to the mobile field in the user table
  3. Add an index to the risk_value field in the user_risk table
  4. Adding is_deleted=0 and status=1 does not use the index because the optimizer considers the cost of the index idx_risk_value(risk_item, risk_type, risk_value) to be too high
4
| username: caiyfc | Original post link

If using the idx_risk_value index is indeed very good, then just directly bind the execution plan with a hint.

5
| username: caiyfc | Original post link 
SELECT /*+ USE_INDEX(user_risk,idx_risk_value) */ 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
6
| username: 小龙虾爱大龙虾 | Original post link

This is just a single table query.

This index does not use the risk_value column, I don’t see why.

7
| username: TiDB_C罗 | Original post link

This query can be used, but adding is_deleted and status=1 makes it unusable. As long as it’s not an index covering, select * will not use the composite index and will perform a full table scan.

8
| username: 这里介绍不了我 | Original post link

The optimizer considers that index too inefficient and won’t use it.

9
| username: 小龙虾爱大龙虾 | Original post link

Look here, the risk_value column is not being used.

10
| username: TiDB_C罗 | Original post link

Try adjusting the order of the idx_risk_value (risk_item, risk_type, risk_value) index and see if it works.

11
| username: caiyfc | Original post link

The first one can use the index because it doesn’t need to go back to the table, right? With such a large amount of data, the cost of going back to the table is high, right?

12
| username: TiDB_C罗 | Original post link

This can use an index, even if it involves a table lookup, the index is still used. Once risk_value = (subquery) or risk_value in (subquery) is used, it becomes unreasonably slow.

13
| username: caiyfc | Original post link

Your estimated rows difference is too large. With only one row of data estimated, the cost of table lookup is definitely low.

14
| username: 小龙虾爱大龙虾 | Original post link

Please execute explain analyze.

15
| username: TiDB_C罗 | Original post link

The first query uses a subquery to filter user_risk records, while the second query directly filters user_risk records based on a specific risk_value. The execution plans show that the first query involves a HashJoin and a full table scan, making it slower and more resource-intensive. The second query uses an IndexLookUp and IndexRangeScan, making it faster and more efficient.

16
| username: TiDB_C罗 | Original post link

How to disable the hash join between the subquery result and the outer table?

17
| username: 小龙虾爱大龙虾 | Original post link

There seems to be an issue. Please share the plan replayer for everyone to take a look.

18
| username: TiDBer_QKDdYGfz | Original post link

I don’t understand either, waiting for the experts to answer and clarify.

19
| username: TiDB_C罗 | Original post link

Closing the thread: Different column collations caused the index to be unusable. I had posted a similar issue before. Thanks everyone for your hard work, much appreciated.

+-----------------+----------------+--------------------+
| TABLE_NAME      | COLUMN_NAME    | COLLATION_NAME     |
+-----------------+----------------+--------------------+
| user            | mobile         | utf8mb4_bin        |
| user_risk       | risk_value     | utf8mb4_unicode_ci |
+-----------------+----------------+--------------------+
20
| username: zhaokede | Original post link

I have encountered a similar issue. When creating the table, the collation character set was not specified. Later, when adding fields, the collation character set of the fields was all messed up.