Friends from the production environment may encounter:
After a period of time, the query statements that run well may suddenly become bad.
A major possible cause is that the data distribution has changed.
As a result, the MySQL optimizer's selection of the driver table changes, and the index becomes invalid.
Therefore, when you are idle and have a cup of coffee, you should collect two more table statistics.
At this time, straight_join debuted
MySQL only supports nested loop join. For detailed usage of this nested join, refer to the previous blog: Click to open the link.
Compared with Oracle, it is not known that straight_join is equivalent to use_nl in Oracle, so the principle and applicability are roughly the same,
However, the selection of the driver table, the MySQL Optimizer may not be as intelligent as Oracle, and MySQL adopts a simple and crude method:
If the result set of a table is small, the table is driven by the table.
You can select straight_join for two reasons.
① The MySQL optimizer is not powerful and the driver table is incorrectly selected
② Applicable scenarios of nested loop join:
==> It is generally used when the connected table has an index and the index is highly selective (that is, selecti.pdf is close to 1 ).
==> That is, the record set of the driver table is relatively small (<10000) and the inner table must have a valid access method (index)
General Optimization operations:
① Show full processlist; <== find the TOP-SQL
② Explain + TOP-SQL; <== query the SQL Execution Plan
Note: In the explain result, the table that appears in the first row is the driver table.
A classic optimization example:
When the explain output contains: "using filesort" or even "using temporary 」
We should polish our eyes like a chicken or blood, and keep our best posture at all times.
At this moment, the optimization is much easier, and the sorting field should be in the driving table as much as possible.
By David Lin
2013-06-23
Good luck
