Low Efficiency of SQL Statement Conditional Query

Translated
1

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

Original topic: sql语句条件查询 效率低

| username: Atlan

Bug Report
I have a table with more than 720,000 rows of data. When using a query statement, if the WHERE condition field does not have an index, it will greatly reduce the query efficiency (200ms-800ms). Detailed information is as follows (already desensitized):

Table creation statement

DROP TABLE IF EXISTS `t_collect_record_1659571200`;
CREATE TABLE `t_collect_record_1659571200` (
  `id` bigint(20) NOT NULL /*T![auto_rand] AUTO_RANDOM(5) */,
  `uid` varchar(64) NOT NULL ,
  `src` int(8) NOT NULL ,
  `collect_time` bigint(20) NOT NULL ,
  PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */,
  KEY `uid_ctime_id` (`uid`,`collect_time`,`id`) COMMENT 'uid_ctime_id'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin /*T![auto_rand_base] AUTO_RANDOM_BASE=780001 */;

Query statement

SELECT
  `id`,
  `src`,
  `collect_time`
FROM
  `t_collect_record_1659571200`
WHERE
  `uid` = "11100000018"
  AND `collect_time` <= 1660800000
  AND `collect_time` >= 1659579000
  AND IF (0=10,TRUE,id > 10)
  AND IF (0=1,TRUE,src = 1) # Slow query if this condition is met
LIMIT
  30;

Execution process comparison (modified time to avoid caching)

For formatting purposes, it has been uploaded to GitHub

【TiDB Version】
v5.4.2
【Impact of the Bug】
Increased query time
【Possible steps to reproduce the issue】

  1. Execute the table creation statement
  2. Insert about 700,000 rows of data
  3. Execute the query statement
    【Observed unexpected behavior】
  4. When the second IF condition is met, the query time is 800ms; otherwise, it is only 200ms
    【Expected behavior】
  5. Query time around 200ms
    【Related components and specific versions】

【Other background information or screenshots】

image
image1380×680 60.9 KB
If an environment is needed, you can contact QQ: 2029229189

2
| username: h5n1 | Original post link

When the condition is IF (0=1, TRUE, src = 1), the addition of the condition src=1 means that IndexRangeScan cannot filter out all the records that meet the condition and must go back to the table to filter the records with src=1. This results in the inability to push down the limit to TiKV. However, when the condition is IF (1=1, TRUE, src = 1), it returns true, and since the index includes other condition columns, the limit can be directly pushed down to the index range, resulting in fewer returned data and thus faster performance. For this specific SQL, you can create an index that includes the src field (uid, collect_time, id, src).

3
| username: Atlan | Original post link

At this point, pushing down the limit will definitely include the information to be queried. Why wouldn’t it be pushed down?

4
| username: Atlan | Original post link

Pushing down the limit to all KVs (the returned data will definitely be greater than the limit number to be queried), and then having the TiDB server determine which data to return. Wouldn’t this be more efficient than a full table scan?

5
| username: h5n1 | Original post link

  1. Theoretically, the optimization method should be as follows (version 5.2.3), because there is no sorting, the limit can be applied to the result after the table lookup. You are using the newer version 5.4.2, and I am not sure if there are any other environmental impacts. You can raise an issue on GitHub.

  2. In your environment, the time consumption is mainly on indexrangescan. Even if the limit is pushed down to the Probe end, there won’t be a significant performance improvement. If performance impact is quite sensitive, it is recommended to try adding the src column to the composite index first.

6
| username: Atlan | Original post link
  1. In actual use, I would use ORDER BY, but for simplicity, I have removed it here.
  2. I am using LIMIT 30. In theory, it should only scan 30 entries and return them when scanning the index. Why does it scan all the entries here?
7
| username: Atlan | Original post link

The main question is why, when scanning the index, it scans all the table entries instead of the number set by the limit.

8
| username: h5n1 | Original post link
  1. The order by cannot be pushed down. Check this: planner: Limit cannot be pushed down through IndexLookUp when the keep-order is true and it has a table-side Selection · Issue #21250 · pingcap/tidb · GitHub
  2. Because you added the condition src=1, only scanning 30 index data is incorrect. You need to use the rowid obtained from the index to go back to the table and get the records with src=1.
9
| username: Atlan | Original post link

Is this the process here? Scan all index data and return to the TiDB server, then send a message to TiKV to filter src. TiKV scans the index and returns to TiDB, then sends it to TiKV to determine src?

10
| username: Atlan | Original post link

Shouldn’t this be handled directly in TiKV?

11
| username: h5n1 | Original post link

Index scan is to scan the index to get the rowid and return it to the TiDB server, then go back to TiKV to scan the records based on the rowid, and then filter through selection for conditions not included in the index, such as src=1. After that, the result is returned to TiDB. In theory, the final data that meets the conditions should be able to use Limit, just like the screenshot I posted of version 5.2.3.

12
| username: Atlan | Original post link

Here’s a question: when TiKV returns the rowIDs obtained by scanning the index, does it return 700,000 rows at once or based on some kind of pagination? If it’s pagination, then when TiDB receives the paginated rowIDs, it directly pushes down the filter. In theory, with this pagination, I should already have the data that meets the composite conditions. I don’t need to pay attention to the subsequent rowIDs, right?

13
| username: h5n1 | Original post link

Return in batches similar to pagination. Since the limit cannot be pushed down, all rowids need to go back to the table.

14
| username: Atlan | Original post link

I don’t quite understand. Isn’t it supposed to push down to TiKV to check after getting a page of rowIDs? Get all rowIDs and then paginate to push down to TiKV to filter src? In the first case, I basically get all the data in one query. Then it’s done.

15
| username: h5n1 | Original post link

The image you provided is not visible. Please provide the text you need translated.

16
| username: Atlan | Original post link
  1. Why is it designed this way? Isn’t it obviously advantageous to query page by page directly?
17
| username: h5n1 | Original post link

This should be related to key composition and coprocessor task construction. It is necessary to obtain all rowids first, and then construct cop tasks based on the region key range and send them to TiKV.

18
| username: Atlan | Original post link

I found a method for this scenario in this link. Direct link: Using paging to improve performance for indexlookup · Issue #30578 · pingcap/tidb · GitHub. By using the tidb_enable_paging option, the logic here can be changed so that after TiKV returns a batch of rowID data, it directly goes to the table to query. It won’t aggregate at the TiDB server now. This achieves low latency of 200ms to return data, which is basically the same as when there is no src condition.

Variable link: 系统变量 | PingCAP 文档中心

19
| username: h5n1 | Original post link

:call_me_hand: Post the execution plan after enabling it.

20
| username: Atlan | Original post link

Here, we thought about it. It might be like this. If we don’t aggregate all rowIDs at the TiDB server layer but instead return a batch of rowIDs directly for table lookup, and if the data that meets the conditions is in the last batch of rowIDs returned, then the TiDB server will issue many invalid queries, resulting in multiple table lookups, causing network and disk I/O to explode. When TiDB gets all the rowIDs, it can optimize the number of queries here. For example, here it returned 700,000 rowIDs but only 200 rows were finally looked up. This indeed seems reasonable.