High Performance MySQL chapter 6th query performance optimization

In the MySQL execution plan:

ID

ID used to indicate the order of execution, ID the same as a group, first execute ID A large number of groups, and then a small group of numbers are executed. within a group with the same ID, the order is executed from top to bottom.

Type

Indicates how MySQL finds the desired row in the table, also known as the "access type", the common types are as follows:

From left to right, from worst to best.

All represents a full table scan, index represents an index full scan, a range index range scan, ref is a non-unique index scan, and the common effect is on the = comparison, but is not unique . eq_ref: Unique index Scan.

Possible_keys

Indicates which index MySQL can use to find rows in the table, and if there are indexes on the fields involved in the query, the index will be listed but not necessarily used by the query. Note: If you use an overlay index scan, this is empty.

Key

Displays the index that MySQL actually uses in the query, and displays NULL if no index is used

Key_len

Represents the number of bytes used in the index, which evaluates the length of the index used in the query

Rows

Indicates that MySQL estimates the number of rows to be read to find the required records based on table statistics and index selection

Ref:

Temporarily observed, this value is only available when type is ref. is typically a const or column.

Extra columns:

Use index: This does not mean that the index is used. The index can be viewed through the key column, and the key columns show the name of the index being used. Use index indicates that an overlay index scan is used. That is, there is no access to the data file, and all the data is obtained from the index file.

Use where: This does not mean that the where condition is used, but rather that the service layer gets the data from the storage engine and then the where filter.

Using index Condition: Indicates that the indexes are used to read the data in the table, the index is scanned first, and the corresponding data is read according to the key that the index points to

Using Filesort: Using local files for sorting

Using temporary: temporary tables are used

Impossible where: In the optimization phase, the optimizer can determine, based on the table definition, where conditions are not possible at all, such as the possibility that the primary health cannot be empty

Using Join buffer (block Nested loop): MySQL uses the optimized nest loop algorithm to read multiple blocks at a time.

Query cache:

Before parsing a SQL, if the query cache is open, MySQL will check whether the query (based on SQL hash as key) exists in the cache, and if so, then the SQL will return the results before parsing and generating the execution plan.

Query optimizer:

Oracle uses cost-based optimizer.

You can use Last_query_cost to get the cost of the previous query in the current reply:

1. /* Disable query caching using Sql_no_cache */
2. Select Sql_no_cache Count (*) from T_person;
3. Show status like 'last_query_cost ';

The results returned in 10.499 indicate that the MySQL query optimizer thought that it would take approximately 10 data pages for random lookups to complete the query. The result is based on a series of data, such as the number of pages per table or index, the cardinality of the index, the length of the index and data rows, and the distribution of the index.

Due to inaccurate statistics, or the implementation mechanism of MySQL itself, in some cases, the cost of calculation is not accurate.

The optimizations that MySQL can handle are:

• To redefine the order of associated tables
• Convert an outer join to an inner join
• Use equivalent transformation rules such as (5=5 and a> 5) to be rewritten (a>5)
• Optimize count (), Min (), Max (), such as the index of the column to take min only need to take B-tree to find the first node.
• Estimate and convert to a constant expression. Functions that do not change, such as the Min function mentioned above, are converted to constants.

High Performance MySQL chapter 6th query performance optimization