TiDB Index Selection

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

Original topic: tidb 索引选择

| username: 我是人间不清醒

[TiDB Usage Environment] Production Environment
[TiDB Version] 7.1.3
[Reproduction Path] Operations performed that led to the issue
[Encountered Issue: Using a single site with two centers, the master is replicated to the slave via ticdc. A slow query was found in the monitoring, and the explain analyze results on the master and slave showed significant differences in index selection.]


The WHERE clause is as follows:

    district_id LIKE '130982207%'
    OR district_id = '130982501205'
   AND p.area_id = '1309'
  AND p.birthday >= '2024-01-01'
  AND p.birthday <= '2024-01-31 23:59:59'
  AND p.is_deleted != 1
  AND p.create_type_code = 2
  AND 1 = 1
  p.entering_time DESC
  20 OFFSET 0;

How can I optimize this to ensure the master database hits the correct index?

| username: 小于同学 | Original post link

Could it be because your first condition p.birthday >= ‘2024-01-01’ is not standard? Try changing it to p.birthday >= ‘2024-01-01 00:00:00’.

| username: 我是人间不清醒 | Original post link

No, the data in the two centers is exactly the same. The index selection is different during execution. I just adjusted the SQL statement according to what you said, but there was no change.

| username: 啦啦啦啦啦 | Original post link

How about manually analyzing the involved tables?

| username: 我是人间不清醒 | Original post link

Manually executed analyze table before execution.

| username: 啦啦啦啦啦 | Original post link

How about creating an SQL execution plan binding?

| username: tidb菜鸟一只 | Original post link

Is the replica version the same as the primary version?
If not, add IGNORE_INDEX (p, index on area_id + entering_time field) or specify FORCE_INDEX (p, index on area_id + birthday field) on the primary.

| username: 我是人间不清醒 | Original post link

It is effective, but the development side requires significant modifications. For master-slave data consistency, the configuration of the slave database should be lower. The slave database is mainly used for BI and reporting.

| username: redgame | Original post link

We have used FORCE_INDEX.

| username: 啦啦啦啦啦 | Original post link

In fact, R&D testing does not need to change the SQL. You can refer to this and bind the execution plan at the database level.

| username: 我是人间不清醒 | Original post link

I’ll give it a try, haven’t done it before. Does anyone have experience to share about binding execution plans? What should I be aware of, since this is for production and I’m being cautious?

| username: 啦啦啦啦啦 | Original post link

We use it quite a lot here and haven’t encountered any issues. The bound execution plans can also be closed or deleted at any time. You can refer to the community usage cases at 专栏 - 使用SPM固定执行计划 | TiDB 社区.

| username: 我是人间不清醒 | Original post link

Thank you, I am trying.

| username: kevinsong | Original post link

Update statistics. If it doesn’t work, rebuild the index.

| username: tidb菜鸟一只 | Original post link

If the versions are consistent, it is indeed difficult to determine where the problem lies. You can also use the method of binding execution plans.

| username: 我是人间不清醒 | Original post link

Okay, I’ll try binding the execution plan first.

| username: 像风一样的男子 | Original post link

I have encountered a similar issue where the execution plans of the primary and replica are inconsistent. This is because the primary database was upgraded from a lower version, and index merge was not enabled by default.

| username: TiDBer_5cwU0ltE | Original post link

Try updating the statistics, it might solve the problem. Sometimes it’s really hard to understand how the optimizer makes its choices, let’s study it slowly.

| username: TiDBer_RjzUpGDL | Original post link

Execution plan binding

| username: 我是人间不清醒 | Original post link

Thank you all, executing the binding plan resolved it.