MySQL tuning----left JOIN

Previously summary: The requirement is an ordinary two-table connection to query out the detailed information. The table A is a large table, the test data are tens of thousands of, B is a small table about 1000 data.

1. In order to rush out of the non-optimized SQL (for the highlighted topic, the return field with * Instead of writing out a detailed field, where conditions are also removed):

SELECT       *    from        A a left        joins    b b       on       a.job_id = b.id

Time: 3.712s

The Rows field represents this step relative to the previous step results in the number of rows that need to be scanned, and you can see that the number of rows that the SQL needs to scan is 22711*1230, which is almost two tables overhead for Cartesian product (SELECT * from A, b).

MySQL uses a method called Block Nested-loop for the processing of joins. The Block Nested-loop algorithm uses the result set of the driver table (which can be simply understood as the preceding table) as the circular underlying data, and then one article queries the data in that result set by using the data in the resulting dataset as a filter to the next table, and then merges the results. If there is a third participating join, then the join result set of the previous two tables is used as the base data for the join, once again querying the data by looping through the criteria to the third table. Conclusion: The data volume of the driver table (preceding table) determines the total number of scans.

2. From the business

It's all a full table scan, and then I find that the left table data is meaningless in business if the right table is null, then inner JOIN instead.

Time: 2.745s

3. You can see that the type above is all

System/const/eq_ref/ref/range/index/all----decreasing efficiency from left to right

All are type all considering whether the condition of the connection can be the primary key, if there is a primary key, MySQL can use index query, the efficiency will be improved a lot.

Since a table is a link to a history table, the field is not a primary key, so you can only add this field to the primary key in table B.

Time: 2.672s

Is still very slow, because the index is not yet indexed, and the B-table ID field is now indexed with the following results:

Time: 0.109s

MySQL tuning----left JOIN