There are many hints for table joins that can be specified in oracle: ordered hint instructs oracle to join in the order of the tables after the from keyword; leading hint instructs the query optimizer to use the specified table as the first table of the join, that is, the driver Table; use_nl hint instructs the query optimizer to use nested loops to connect the specified table and other row sources, and will force the specified table to be the inner table.
There is a corresponding straight_join in mysql, because mysql only supports the connection method of nested loops, so the straight_join here is similar to the use_nl hint in oracle.When the mysql optimizer processes the association of multiple tables, it is very likely to select the wrong driver table for association, which leads to an increase in the number of associations, which makes the execution of the sql statement very slow.At this time, an experienced DBA is required.Judging and selecting the correct driver table, straight_join will play a role at this time.Let's take a look at the case of using straight_join for optimization:

1.User instance: a sql of spxxxxxx is executed very slowly, the sql is as follows:

2.View the execution plan:

3.View index:

4.Adjust the index, the A table optimization adopts the covering index:

5.View the execution plan:

After the adjustment, the implementation has a slight effect, but it is not obvious, and the key point has not been found:

6.The execution time still takes a long time.The time consumption is mainly spent in the Using filesort.The amount of data involved in the sorting is as much as 38W, so the drive table needs to be converted; try to use the user table as the drive table: use straight_join to force the connection Sequence:

The execution time has changed qualitatively, down to 2.56 seconds;

7.In the first step of analyzing the execution plan: Using where; Using temporary; Using filesort, the user table can also use a covering index to avoid the appearance of using where, so continue to adjust the index:

8.Execution time reduced to 1.43 seconds: