MySQL Optimization explain command introduction (ii)

Type column

The MySQL manual notes that the type column is used to describe the join type, but we think it is more appropriate to consider this column as a description of how Access type--, or MySQL, determines how to look for data in a table, as shown below from worst case to best case access type:

All

The all type, commonly said full table scan, usually means that MySQL needs to scan the entire table from beginning to end to find qualifying Row records (with the exception of the limit-qualified query statement, which generally has a supplemental description of the "Using Distinct/not exists" in the extra column).

Index

Basic and full table scan, but is through the sequential scanning of the index, the main optimization is to avoid sorting, the biggest disadvantage is to scan the table through the index order, usually means the random data from the storage space, IO costs will be very large.

If you also have a "using index" description in the extra column, it means that MySQL uses an overlay index to implement the sweep table instead of using the index order to scan the table, which is better performance than sweeping the table by index order.

Range

A range traversal is a restricted version of an index traversal, starting at a certain point in the index, returning all row records in a range, which is faster than scanning the entire index, which is typically accompanied by keywords such as between or > in the WHERE clause.

When MySQL uses an index to find a series of values contained in or OR, it is also shown as a range traversal, but these two are very different types of access, with significant performance differences.

Ref

This is a type of index access (index lookup) that returns a match to a particular single-valued row record, in fact a mixed type of lookup and traversal, because of the possible multi-line match. This index lookup can only be used for non-unique index or non-unique prefix index of a unique index. The ref type is called because the index is used for comparison with some reference values (reference value), and the reference value can only be one constant or a value from a table in the previous one in a multiple-table query.

Ref_or_null is a variant of ref, meaning that after the first lookup, MySQL must also do a second lookup to find a record that is null.

Eq_ref

This is the index lookup type that MySQL knows will only return a match result. When compared against a primary key or a unique index and some reference values, this type of access is a good way for MySQL to optimize for this type because there is no need to estimate the range of matching row records or to continue looking after a row of records is found.

Const,system

This is the type of access when MySQL is able to optimize some parts of a query to a constant. If you place the primary key in the WHERE clause and select the primary key in the Select section, MySQL translates it into a constant and efficiently removes the table from the join execution.

Null

The null access type identifies that MySQL can parse the query during the optimization phase, and does not even need to actually access the table data or indexes during the execution phase, such as getting the minimum value from the index column, just looking at the index and not requiring any table data to be accessed during execution.

Possible_keys column

This column, based on the column names that are accessed in the query statement and used for comparison operations, shows the list of index names that might be used for the query, which was generated early in the optimization phase, so some of these indexes might not be needed after the later optimization phase.

Key column

This column shows how MySQL actually uses the index to optimize access to the table, and if the index does not appear in Possible_keys, it indicates that MySQL is the index selected for other special reasons-for example, if the query has no WHERE clause, MySQL may also prefer to use an overlay index.

In other words, Possible_keys reveals which indexes help make row-record queries more efficient, while key shows how the optimizer actually decides to use it to minimize the cost of the query, and here's a practical example.

Key_len column

This column shows the length (number of bytes) of the index used by MySQL, and if MySQL uses only a subset of the columns in the index, this value can be used to calculate which columns are actually used by MySQL. Note MySQL5.5 and the following versions can only match with the leftmost prefix. In this example, the primary key for Sakila.film_actor consists of two smallint columns, and one smallint is two bytes, so the primary key index length should be 4 bytes:

According to Key_len, we can infer that the above query statement uses index lookups only on the first column (actor_id). When calculating column usage, you also need to take into account the character set that is actually used by the Type column field:

The index length of column A and column B in the above query is 13 bytes, the column A is only three characters in size, but the UTF8 character set uses up to 3 bytes to identify one character, and column B is a 4-byte integer.

MySQL does not always tell you how much an index is used, and if you use a like query that matches a prefix, it will show the total length of the column being used.

The Key_len column identifies the maximum length that the index field might be used for, not the actual number of bytes used in the data in the table, even though the above column A does not contain any value longer than one character, and MySQL still shows a 13-byte length, meaning that Key_len is calculated from the table's definition, Rather than the actual data in the table.

Ref column

This field identifies which columns or constants in the previous table are used for the index in the key column, and here is an example of join and alias use, noting that the ref column reflects how the film table is not called F in the query statement:

Rows column

This column shows the number of record rows that the MySQL estimated query execution needs to actually read, and for a multi-nested query statement, this number is not just the actual number of rows that MySQL thinks he needs to read from the table. It will be the number of rows in each subquery phase that MySQL estimates need to be read to meet the current query criteria.

According to the statistics of the table has been indexed selection, this estimate may be very MySQL5.0, and previously, will not consider the effect of limit, as the following example:

You can do this by multiplying the value of this column in each row by the number of row records that the entire query statement is about to check, and the following example will probably check for 2600 rows of records:

Note that this number is the number of rows that MySQL estimates will check, not the number of rows in the result set, and that optimizations such as connection caching do not affect the value of this row, even though MySQL may not actually check every row of data it estimates, and MySQL does not know any optimizations made by the operating system or the hardware cache.

Filtered column

When using explain extended, MySQL5.1 and above will have this column, which makes a pessimistic estimate of the row hundreds of that satisfies the where statement or join condition, and if you multiply the value of the rows column by this percentage, you can get the number of rows that MySQL estimates will join with the previous table data, which the optimizer uses to apply to all /index/range and Index_merge access types.

Let's say the following is a standard structure:

Insert 1000 filler into the table as random text data to prevent MySQL from using an overlay index for our queries:

MySQL could have used a range access to check the id<500 row records from the table with an ID, but considering that this could only optimize about half of the row access, it thought that a full table would cost less, MySQL then selects the full table sweep table when the row records are filtered through the conditions in the WHERE clause. With the estimate of range access, MySQL knows how many rows are filtered by the Where condition, so the value in the filtered column is 49.4%.

Extra column

This column shows the additional information that is not available in the other columns, and the MySQL document shows most of the values that can appear in this column, the most important ones you can see frequently are as follows:

Using Index

MySQL uses an overlay index to avoid direct access to table data, and does not confuse the overlay index with the index access type.

Using where

When MySQL retrieves data from the storage engine, it uses the Where condition to filter, and many of the conditions in the WHERE clause involve indexed columns can be detected at the time the index is read, so not all queries with a WHERE clause will have a "Using where" message. Sometimes the existence of this information implies that the query may be optimized by different indexing strategies.

Using Temporary

MySQL uses a temporary table to store the query results.

Using Filesort

MySQL will sort the results by using an external sort, rather than reading the table data in indexed order, MySQL has two filesort algorithms that can be sorted on memory or hard disk, explain does not indicate which Filesort algorithm is used, It does not indicate whether to sort on memory or hard disk.

Range checked for each record (index map:n)

This means that there is no good indexing policy, and each row in the JOIN clause recalculates the index, and n represents a redundant index bitmap in Possible_keys.

MySQL Optimization explain command introduction (ii)