To improve efficiency, please provide the following information. Clear problem descriptions can be resolved faster:
[TiDB Usage Environment] TiDB 6.1
[Overview]
[SQL]
SELECT
a.*
FROM
records a
LEFT JOIN company b ON b.eid = a.eid
WHERE
b.id = ?
ORDER BY
a.change_date DESC
Query business records under the company by the company ID in table b. The id in table b is the primary key, eid is the index key, table a has no primary key, and the unique key is composed of eid and u_id, with eid being the associated field.
[Problem] Data migrated from MySQL to TiDB, the original table’s auto-increment primary key was discarded, and a multi-field composite unique key was adopted. The original business query SQL in Navicat has no issues and takes 0.3s, but in the program, Mybatis queries are very slow, taking tens of seconds or even minutes. The database connection pool used is Hikari.
If the question is related to performance optimization or fault troubleshooting, please download the script and run it. Please select all and copy-paste the terminal output results for uploading.
Confirm where the issue is. You can check the execution time of the SQL in the dashboard’s slow SQL query. If it is very slow, then check the resource usage of TiDB.
You still need to specifically look at the execution plan of your SQL. First of all, your SQL is a fake outer join, and the optimizer will directly rewrite it into the following inner join:
SELECT a.*
FROM records a, company b
WHERE a.eid = b.eid
AND b.id = ?
ORDER BY a.change_date DESC
Then, according to your description, it is estimated that the JOIN method between table a and table b has changed:
If table a drives table b, then table a will be a full table scan, and table b will use the eid index (IndexJoin) or the id primary key (HashJoin).
If table b drives table a, then table b will use the id primary key, and table a will use the (eid, u_id) index (IndexJoin) or a full table scan.
So there are roughly at least the above 4 JOIN methods. Which one is better depends on the scale of the number of rows in table a and the filtering ability of the a.eid field. Therefore, you need to compare the execution plans of SQL under Mybatis and Navicat.
|
username: TiDBer_mGBoAnW9
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: