“This is the first day of my participation in the First Challenge 2022. For details: First Challenge 2022”
When filling the pit for the seniors, I found that an operation takes more than 5s to start, and all the SQL is bara out, and I found that without modifying the business logic, it can be solved only this one, although the optimization of 0.5s -> 0.1s is not too obvious, but at least I will optimize the operation a little bit 🐶
SQL > alter TABLE t join t1;
The time of this time is about 0.5s, but the result of the query has 2170 records, that is, only 2000+ data is expected in the T table, the execution speed is a little wrong
Use explan to analyze the execution of this SQL
Found that all table indexes are up. So, one of the biggest possibilities is that when a t table is linked to t1 for additional data, T1 does a full table search every time it matches
Because I don’t quite understand the execution plantype=allWe decided to look at the query speed and execution analysis of the right join and the inner join:
The right connection
It is equivalent to connecting and matching the data in table T with the master table of TABLE T1. It can be seen from the execution plan that T1 scans the whole table, but table T actually uses the primary key to optimize the matching efficiency when matching.
In the connection
The execution plan for both the right join and the inner join is the same, except that the query returns a different result
Comparison found right join and inner join query efficiency is the highest, because the associated table goes to the index query; While the left join query, both tables are go full table query
From the analysis on the SQL, t table using the id field, this is usually a primary key, the t1 table using the xx_id fields, each time to go full table index, big probability is not create indexes for it
Open the attribute_1 table design and see that no indexes (except primary keys) are defined.
Now append the regular index to xx_id
Tests verify the performance of the original SQL after the index is appended
As you can see, the performance of the query has improved significantly by 0.5s -> 0.09s
Check the execution plan:
The left table continues the full table query, but the right table does actually walk the index, which is why the performance is improved and confirms my earlier guess
Perhaps most of the optimizations in mysql are to use indexes whenever possible and avoid full table searches?
Original article, without permission, prohibit reprinting
-Leonard: Create by the comfort of salt fish