MySQL (ii) Slow query analysis (i)

As the following table structure:

CREATE TABLE' trade_order ' (' order_id ' )bigint( -) unsigned not NULLAuto_increment COMMENT'Order Number', ' Total_price 'bigint( -)DEFAULT NULLCOMMENT'Order Total Price', ' Item_name 'varchar( -)DEFAULT NULLCOMMENT'Product Name', ' mobile 'varchar( -)DEFAULT NULLCOMMENT'Order the phone', ' gmt_create 'timestamp  not NULL DEFAULT Current_timestamp, ' gmt_modify 'timestamp NULL DEFAULT NULL  on UPDATE Current_timestamp,  PRIMARY KEY(' order_id ')) ENGINE=InnoDB auto_increment=239 DEFAULTCHARSET=UTF8MB4;

CREATE TABLE' Trade_sub_order ' (' ID ')bigint( -) unsigned not NULLauto_increment, ' order_id 'bigint( -)DEFAULT NULLCOMMENT'Order Number', ' Item_price 'bigint( -)DEFAULT NULLCOMMENT'Commodity price', ' item_nums 'int( One)DEFAULT NULLCOMMENT'Number of goods', ' gmt_create 'timestamp  not NULL DEFAULT Current_timestamp, ' gmt_modify 'timestamp NULL DEFAULT NULL  on UPDATE Current_timestamp,  PRIMARY KEY(' id '),KEY' Order_index ' (' order_id ')) ENGINE=InnoDB auto_increment=1227 DEFAULTCHARSET=UTF8MB4;

There are significant differences in the execution plans for the following 2 formulations when executing a joint query.

The first type:

Select *  from  Left Join Order  by desc;

The second type:

Select *  from  Left Join Order  by desc;

The difference between the two is that the table field in order by is different.

Look at the execution plan:

The first type of execution plan:

The second type of execution plan:

The difference between rows is ignored because the data is constantly being added to the process.

The first method has a primary key index and does not require the use of temporary tables. The second type of full-table scan, using temporary tables, uses file sorting.

The key problem here is that the MySQL Table association algorithm is "Nest loop Join", which is to use the result set of the driver table as the circular base data, then query the data by the data in the result set as the filter condition to the next table, then merge the result (temporary table).

In explain, the table that appears in the first row is the driver table, and the sort that drives the table field can be done directly, and for a non-driven table field, the merge result (temporary table) of the circular query needs to be sorted. So in the second way, the using temporary will appear. And because the non-driver table fields are ordered, the full table data of the driver tables is used as the result set of the driver table, resulting in a full table scan, which cannot be used to the primary key index.

So, in explain, with using temporary, you need to be concerned about whether to use non-driver table fields for sorting processing.

Definition of driver table:

When you make a multi-table connection query,

• A table with a low number of records that satisfies query criteria is specified as the driver table;
• No connection condition specified, table with fewer rows is the driver table;

MySQL (ii) Slow query analysis (i)