Explain parsing SQL statements

https://mp.weixin.qq.com/s?__biz=MzI4Njc5NjM1NQ==&mid=2247485618&idx=1&sn= ed892f572b81e6aa6a7be5c43c2e7351&chksm= ebd6379edca1be88f7d3703db31115ced7c5cacd814b684731d6bc1f66f547f341b6c6451da4&mpshare=1&scene=1& srcid=0624pu7gnargoypwuirzy09n&key= 22fa0893379b36a8d0c5220e223bd1e2fb32a12c5b739ed4bb284d7e464f06cc3217e7abd9344379974f34e6f36a8e54b63014573a3f02cabbd5471f7 95796502430dd6b9fe9178d08aeb45a7ff51d7f&ascene=0&uin=mta2nzuxmdaynq%3d%3d&devicetype=imac+ Macbookair6%2c2+osx+osx+10.10.5+build (14f2511) &version=11020012&lang=zh_cn&pass_ticket=vzul0wl% 2bamady8selicvnpor9k1k%2btnus6wnjljlwo%2fa7ylxk%2bcnb%2bzenylouwii

MySQL Index optimization analysis

Using the Explain keyword, you can simulate the optimizer executing a SQL query statement to learn how MySQL handles SQL statements.

+----+-------------+-------+------------+------+---------------+-----+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-----+---------+------+------+----------+-------+

Id

The sequence number of the select query, which contains a set of numbers that can be duplicated, indicating the order in which the SQL statements are executed in the query. There are generally three cases:
The first: The IDs are all the same, and the execution order of SQL is from top to bottom;
The second type: The IDs are all different, the execution order of SQL is based on the priority of the ID;
The third type: ID exists in both the same and different. First execution is based on the priority of the ID, and then from top to bottom based on the same ID.

Select_type

Select query type, mainly used for distinguishing common queries, federated queries, nested complex queries
Simple: Easy Select query with no subquery or union in the query
Primary: If any complex subqueries are included in the query, the outermost query is marked as primary
Subquery: A subquery is included in the Select or where list
Derived: subqueries included in the From list are marked as derived (derived) MySQL recursively executes these subqueries, placing the results in a temporary table.
Union: If the second select appears after the union, it is marked as union, and if the Union is contained in a subquery in the FROM clause, the outer select is marked as: derived
Union result: Select to get results from Union table

Partitions

The partition used by the table, if you want to count the amount of company orders for ten years, you can divide the data into 10 districts, each representing a district. This can greatly improve the query efficiency.

Type

This is a very important parameter, connection type, common to have: all, index, range, ref, EQ_REF, const, system, NULL eight levels. Performance from best to worst Sort: System > Const > EQ_REF > Ref > Range > Index > All.

For Java programmers, it is a good and responsible programmer to ensure that the query reaches at least the range level or if it is best to achieve ref.

All: The full table scan is no doubt the worst, and if the millions level of data, the full table scan will be very slow.

Index: full index scan is much better than all, after all, finding data from the index tree is faster than finding it from the full table.

Range: retrieves only the rows for the given range , using the index to match the rows. The range is reduced, of course, faster than full-table scans and full-index file scans. SQL statements typically have queries such as between,in,>,< .

Ref: A non-unique index scan , which is essentially an index access, returns all rows that match a single value. For example, to query all of the company's research and development team colleagues, the result of matching is more than a unique value .

Eq_ref: Unique index scan , for each index key, a record in the table matches it. For example, to query the company's CEO, the matching results can only be a record ,

Const: indicates that it can be found by index one time, and const is used to compare primary key or unique index. Because only one row of data is matched, it is soon possible for MySQL to convert the query to a constant if the primary key is in the where list.

System: table has only one record (equals system table), this is a const type of special column, usually does not appear, understand can

Full table Scan-full index Scan-range index-non-unique index-unique index-primary health

Possible_keys

Displays the index that the query statement might use (one or more or null), not necessarily the actual use of the query. For informational use only.

Key

Displays the index that the query statement actually uses . If NULL, the index is not used.

Key_len

Displays the number of bytes used in the index, which can be computed by Key_len the index length used in the query. The shorter the index length without loss of accuracy, the better. The value displayed by Key_len is the most likely length of the indexed field, not the actual length, that is, Key_len is calculated from the table definition and is not retrieved from the table.

Ref

Shows which column or constant of the index is used to find the value on the index column.

Rows

Based on table statistics and index selection, the number of rows that need to be read is roughly estimated, and the larger the value, the better.

Extra

using Filesort: Explains that MySQL uses an external index to sort the data instead of reading in the order of the indexes in the table. Sorting operations that are not available for indexing in MySQL are called "File sorting." This will immediately optimize SQL.

Using temporary: 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. This is more about optimizing SQL immediately.

Using index: Indicates that the corresponding select operation uses an overlay index (covering index) to avoid accessing the table's data rows, which is a good result! if a using where is present, 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.

Typically, developers create appropriate indexes based on the where condition of the query, but a good index design should take into account the entire query. In fact, MySQL can use the index to get the column data directly. If the leaf node of the index contains the data to be queried, then there is no need to return to the table query, which means that the index contains (also known as overwriting) the values of all fields that need to be queried, which we call an overriding index.

SELECT *--"SELECT column

Covering index: Also known as index overlay, that is, the data column of select is only available from the index and does not have to read the data rows, and MySQL can use the index to return the fields in the select list without having to read the data file again according to the index.

using index condition: A new feature added after the 5.6 release, the optimizer chooses whether to use an index or a full table traversal in the presence of the index, in proportion to the number of bars and the total number of ranges that match the range.

Using Where: indicates that a where filter is used.

Using join buffer: Indicates that the connection cache is used.

The value of the Impossible Where:where statement is always false, unavailable, and cannot be used to get any elements.

Distinct: optimizes the distinct operation and stops finding the same value after the first matching tuple is found.

Filtered

A percentage value that is used with the values of the Rows column to estimate the result set of the previous table in the query execution plan (QEP) to determine the number of cycles for the join operation. Small tables drive large tables, reducing the number of connections.

Through the explain parameter introduction, we can know:
1. Reading order (ID) of the table
2. operation type of the data read operation (type)
3. which indexes are actually used (key)
4. References between tables (ref)
5. How many rows per table are being queried by the optimizer (rows)

Explain parsing SQL statements