Understanding the driver tables in MySQL query, optimizing queries, and driving large tables with small tables

Why should I use a small table to drive a large table

1, the definition of the driver table

When a multi-table join query is made, [driver table] is defined as:

1) When a join condition is specified, a table with fewer records rows that meet the query criteria is [driver table]

2) when no join condition is specified, the table with the less number of rows is [driver table] (important!)

Advice: If you don't know who to make the driver table, who joins who, please let MySQL run the self-judgment

Now that the join condition is not specified, the table with fewer rows is [driver table], and you are not quite sure of the complex Nested Loop join you are writing (as shown in the following example), do not specify who left/right join who, please give it to the MySQL optimizer runtime decision.

If you have special confidence in yourself

2. mysql Related query concept:

The algorithm associated with the MySQL table is the Nest loop Join, which uses the result set of the driver table as the loop base data, and then queries the data in the next table with the data in the result set as a filter, and then merges the results.

Example: User table 10,000 data, class table 20 data

SELECT * from the user U left join Class C U.userid=c.userid

This requires the user table to be cycled 10,000 times to query, and if you use the class table to drive the user table will only need to loop 20 times to be able to query out

Cases:

SELECT * FROM class C left join user u C.userid=u.userid

Small result set driven large result set

De.cel concluded in 2012 that, whether you or MySQL, the goal of optimization is to minimize the number of loops in the join nested loop.

This ensures that the large result set (Important) is always driven with a small result set!

Second, the optimization of the Joint table query

Optimize first step: Sort by field of driver table

Left join does not change, why do you want to sort by the fields of the non-driver table? As we said before, "the driver table can be sorted directly, and the non-driver table (the field sort) needs to sort the merged result (temporary table) of the circular query!" Of

Explain

SELECT mb.id ...

From MB left JOIN Mbei on mb.id=mbei.mb_id INNER Joinu on Mb.uid=u.uid

WHERE 1=1

ORDER by Mb.id DESC

Limit 0,10

Also satisfies the business scenario and achieves the following rows minimum:

Optimize the second step: Remove all joins, let MySQL decide, explain the first table is the driver table, the data volume is smaller than the other two tables!

Explain
SELECT
 from Mb,mbei,u   
WHERE
    mb.id=mbei.mb_id
     and mb.uid=u.user_id
Order  by desc
Limit 0,ten

Immediately, the driver table is the same as the small table Mbei:

ID select_type table   type    possible_keys      key          key_len  ref                rows    Extra
1 Simple Mbei all mb_id (NULL) (NULL)                                 13388 Using filesort
1  Simple        MB      eq_ref  primary,userid  PRIMARY 4            mbei.mb_id   1
1  Simple        u         eq_ref  PRIMARY            PRIMARY 4            mb.uid           1  Using Index

Iii. Summary

1. Don't trust your luck too much!

2. Don't trust the speed of SQL execution in your development environment!

3, please pick up explain weapon, if you see the following phenomenon, please optimize:

1) A using temporary appears

2) rows too much, or almost all of the records of the table

3) key is (NULL)

4) Possible_keys an excessive (optional) index

Understanding the driver tables in MySQL query, optimizing queries, and driving large tables with small tables