Initial understanding of MySQL (5.6) Implementation plan

Disclaimer: The following are from the MySQL English manual 5.6.

1.MySQL all joins are using the Nest-loop join algorithm (nested loop algorithm).

2. For a set of Joins,mysql join algorithms, a row is read from the first table, and then the matching row is looked up one table at a time, and if a row is able to start from the first table and each table can find a matching row, output the "Big row" The original path is then returned to the previous table until you can find a table with matching rows in it, and then continue to find matching rows for each subsequent table.

The use of indexes in 3.MySQL will be affected by the cardinality of the index, cardinality is the cardinality, the meaning of the potential of the set, too low will cause the index to be deprecated, for example, if the value of sex is too simple to index a field, the index may not be used, Because the cardinality is too small. Tables can be parsed by analyze table tbl_name, which updates the table's statistics (Innodb,myisam is typically automatically updated).

4.

Explanations of the columns in the execution plan:

Column Name	Explain	Special Instructions
Id	The identifier for the Select	Select in the query of the ordinal, the same sequence number is indicated to be a group, the larger the number of the first execution, the lower the number of the outer layer, if the union result is null, the same group will be executed from top to bottom in the order.
Select_type	Select Type	There is no subquery or union is simple, otherwise there will be primary and union and so on, it is important to note that the type with uncacheable, indicating that cannot be cached, the outer row switch will cause recalculation of the Select
Table	The owning table of the output line	Table name or <unionM,N>,<derivedN>,<subqueryN>
Partitions	Matching partitions	partitions that involve tables
Type	Join type	The 5th has detailed description
Possible_keys	Index that may be selected	The index used to find rows, independent of the Order of execution, which means that it is not necessarily used, but may
Key	The actual selected index	It is possible to see a key that is not in the Possible_keys, that is, if the index overrides the selected column, even if the index cannot be used to find rows, it will make the scan faster, so MySQL will also use
Key_len	The length of the selected key	The length of the portion of MySQL used in a multipart index, which may have multiple values
Ref	Columns that need to be compared to the index	Column or constant
Rows	Estimated number of rows to be inspected	InnoDB is not necessarily accurate, just an estimate
Filtered	Percentage of rows filtered by the condition of the table	Estimation
Extra	Additional Information	Too much content, you need to check the documentation.

5.type (type of join):

System (table has only one row),

Const (the table has a maximum of one row to match),

Eq_ref (each merge row with the previous table reads only one row in the table, which is the best of all except system,const, characterized by the use of =, and all parts of the index participating in the join and the index is the index of the primary key or non-null unique key).

Ref (if you match only a few rows at a time, that's a good one, using = or <=>, can be a left overlay index or a non-primary key or a non-unique key),

Fulltext (full-text search),

Ref_or_null (similar to ref, but includes null),

Index_merge (indicates that an index merge optimization has occurred, this is more complex, the current understanding is to merge a single-table range index scan),

Unique_subquery (replace the subquery with the form "select Primary_key"),

Index_subquery (replace the subquery with the form "select Key_column"),

Range (constant range),

Index (one case is a full-table scan with index coverage, just look at the index (the exception to the key shown in table 3), and the other is),

All (full table scan).

6. Evaluate query performance by calculating the number of disk addresses:

A small table can read one line at a time because the index may be cached,

Large tables can be obtained by using the following formula: Log (Row_count)/log (INDEX_BLOCK_LENGTH/3 * 2/(index_length + data_pointer_length)) +1.

In the case of writing, it usually takes four addresses, one addressing to find the place to insert a new index, and two times to update the index (the average number of times a B-tree is adjusted after inserting a new node is two times.) Why? The principle of statistics? ), and the last time the row was written.

7.SELECT @ @optimizer_switch; or show variables like ' Optimizer_switch '; You can view some options for the optimizer.

8. What is the execution plan:

The set of operations, the optimizer chooses to perform the very efficient query is called the "Query execution plan", Also known as the EXPLAIN plan.

The sequence of operations that the optimizer chooses for the most efficient execution of queries is called an execution plan.

Initial understanding of MySQL (5.6) Implementation plan