MySQL optimization (4): Explain analysis

Explain is MySQL's own query optimizer, which is responsible for the optimizer module of the SELECT statement, which simulates the optimizer's execution of SQL query statements to know how MySQL handles SQL, and the syntax is simple: Explain + SQL

Here are a few properties that are queried by explain

(Common performance bottlenecks--

Cpu:cpu saturation typically occurs when data is loaded into memory or read from disk

IO: Disk I/O bottlenecks occur when loading data is much larger than memory capacity

Performance bottlenecks for server hardware: Top,free,iostat,vmstat to see the performance status of the system

Use:

(1) reading order of the table, ID

(2) Operation type of data read operation, Select_type

(3) Which indexes can be used

(4) Which indexes are actually used

(5) A reference between tables

(6) How many rows per table are queried by the optimizer rows

1. ID: reflects the order in which the tables are read, or the order in which the SELECT clauses are executed in the query.

The small table drives the big table forever, Three types of cases:

(1) The same ID, the order of execution is from top to bottom

(2) The ID is different, if it is a subquery, the ID number is incremented, the higher the ID value, the higher the priority, the more executed first

(3) ID exists the same, there are different, all groups, the larger the ID the first execution, if the ID is the same, from the top down order execution

　　

Derived is derived from the meaning of the virtual table, Derived2 2 corresponds to ID2

2, Select_type: reflects the MySQL understanding of the query type

(1) Simplicity: Simple select query with no subquery or union in the query.

(2) Primary: If the query contains any complex word parts, the outermost query is marked as primary.

(3) subquery in the Subquery:select or where list.

(4) derived (derivative): The subquery contained in the From list, MySQL recursively executes these subqueries, putting the results in the temporary table.

(5) Union: If the second select appears after the union, it is marked as union, and if the Union is contained in a subquery of the FROM clause, the outer select is marked as derived

(6) Result set after Union result:union

3, table: reflect this line of data is about which table4. Type: Sort by Access type

Reflects the state of the SQL optimization, at least the range level, preferably up to ref

Query efficiency: System > Const > EQ_REF > Ref > Range > Index > All

(Full sort: System > Const > EQ_REF > Ref > Fulltext > Ref_or_null > Index_merge > Unique_subquery > in Dex_subquery > Range > Index >all)　　

(1) System: from single table only one row of records (equal to the system table), which is a special case of the const type, generally does not appear

(2) Const: The query condition used a constant, the index is found once, often in the use of primary key or unique index appears.

Where Id=1 writes dead, so the type is const

(3) Eq_ref: Unique index Scan, for each index key, only one record in the table matches, common to primary key or unique index scan.

(4) Ref: A non-unique index scan that returns all rows that match a single value, which is essentially an index access, may find multiple qualifying rows, and the difference with Eq_ref is that eq_ref only matches one record.

(5) Range: Retrieves only the rows for a given range, using an index to select rows. The key column shows which index is used, typically between, <, >, in, and so on in the where statement.

This range Scan index scan is better than a full table scan because it only needs to start at some point in the index and end at the other point without scanning all indexes. The difference between eq_ref and ref is that the filter condition is not a fixed value and is a range.

　　

(6) The difference between Index:full index Scan,index and all is that index type only traverses the index tree. This is usually faster than all, because the index file is usually smaller than the data file.

　　

The ID information to get, just the ID on the index, read from the index

(All and index are read-only, but index is read from the index and all is read from the hard disk)

(7) All: Full table scan, if the query data volume is very large, the full table scan efficiency is very low.

5, Possible_keys, key, Key_len: reflect which index is actually used, whether the index is invalid

(1) Possible_keys:mysql speculate on what indexes may be used, but not necessarily the actual use of the query

(2) Key: The actual index used, or null, may not be indexed or index invalidated.

(If an overwrite index is used in the query, the index appears only in the key list.)

Overwrite index: The number of fields and indexes that follow the Select, in the same order

(3) Key_len: Represents the number of bytes used in the index, which allows you to calculate the length of the index used in the query. The same query results, the shorter the better.

The value displayed by Key_len is the maximum possible length of the indexed field, not the actual length, that is, Key_len is calculated from the table definition and not retrieved through the table.

6. Ref: reflects which columns or constants are used to find the values on the index column

7, rows: According to table statistics and index selection, roughly estimate the number of rows to be read to find the required record

　　

Only 641 rows are found by primary key index

The number of rows that need to be queried becomes less when the associated composite index is built.

8, Extra

(1)using Filesort: a sort that cannot be done with an index in MySQL, it uses an external index to sort the data instead of reading in the order of the indexes in the table.

The data is sorted first when the index is created, and the using Filesort generally occurs because the condition after the order by causes the index to fail and is best optimized.

Order by is ranked best with the order and number of indexes that are built

(2) usingtemporary: Uses temporary tables to save intermediate results, and MySQL uses temporary tables when sorting query results. It is common to sort the order by and group by queries group BY.

The impact is greater, so either do not build the index, or the order of group by is consistent with the index

(3)using index: Indicates that the corresponding select operation uses the overwrite index, avoids accessing the table data row, the efficiency is good

　　Overwrite index : The data column after select is only available from the index and does not have to read the data row, and the number of indexes (query columns are less than or equal to the number of indexes), the order is consistent.

So if you want to overwrite the index, be aware that the column of select takes only the columns that need to be used, without select *, and if all fields are indexed together, the index file will be too large and performance will degrade.

　　

The using where is present, indicating that the index is used to perform the lookup of the index key value

If a using where is not present, the index is used to read the data rather than perform a find action.

(4) Using where: Indicates that the where filter is used

(5) Using join buffer: Connection cache used

(6) The value of the Impossible where:where clause is false

(7) Select tables optimized away

(8) Distinct: Optimize the distinct operation, stop looking for the same value after the first matching tuple is found

MySQL optimization (4): Explain analysis