How to query tables without using indexes in TiDB

Translated
1

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

Original topic: tidb怎么查询没有走索引的表

| username: tidb狂热爱好者

【TiDB Usage Environment】Production environment or Test environment or POC
【TiDB Version】
Is there a log for this?
Or is there a way to find those SQLs that didn’t use indexes?
It would be best if it could automatically add indexes.
【Encountered Problem】
【Reproduction Path】What operations were performed to encounter the problem
【Problem Phenomenon and Impact】

【Attachments】

Please provide the version information of each component, such as cdc/tikv, which can be obtained by executing cdc version/tikv-server --version.

2
| username: xfworld | Original post link

None~

3
| username: tidb狂热爱好者 | Original post link

Not too sad.

4
| username: tidb狂热爱好者 | Original post link

There should be a way, you just haven’t found it yet. Wait for someone knowledgeable to answer.

5
| username: tidb狂热爱好者 | Original post link

Answering my own question
System Read/Write Hotspot Query
The read/write hotspot query mainly uses the tidb_hot_regions and tikv_region_peers system tables in information_schema.

Scenario 1: Query the current read/write hotspot table

select db_name, table_name, index_name,
       type,   -- Read/Write hotspot category
       sum(FLOW_BYTES),   -- Traffic per minute
       count(1),
       group_concat(h.region_id),
       count(DISTINCT p.store_id),
       group_concat(p.store_id)
from TIDB_HOT_REGIONS h
join TIKV_REGION_PEERS p 
on h.region_id = p.region_id
and p.IS_LEADER = 1
group by db_name, table_name, index_name, type;
6
| username: cs58_dba | Original post link

You can only go fishing for slow SQL, right?

7
| username: Kongdom | Original post link

You can pay attention to the top SQL on the dashboard homepage.

8
| username: h5n1 | Original post link

Use SELECT tidb_decode_plan('xxx...') to parse the plan_digest in the slow SQL or statements_summary related tables, and then match the result with the keyword FullTableScan.

9
| username: Raymond | Original post link

This solution is good.

10
| username: tidb狂热爱好者 | Original post link

This is a wise solution.

11
| username: tidb狂热爱好者 | Original post link

Is this finding the problem? Are there any solutions that can automatically solve the problem?
For example, rewriting SQL or adding indexes.

12
| username: Raymond | Original post link

You can rewrite SQL through SQL binding. You can refer to the official documentation:

13
| username: tidb狂热爱好者 | Original post link

Thank you very much. This kind of spark from the collision of ideas.

14
| username: tidb狂热爱好者 | Original post link

This doesn’t have automatic recommendation and evolution functions, so you still have to think hard. Alibaba Cloud has long implemented SQL recommendations, so why doesn’t TiDB have it?

15
| username: system | Original post link

This topic was automatically closed 1 minute after the last reply. No new replies are allowed.