MySQL SQL optimization and SQL execution plan

SQL optimization

Disable SELECT *
Use SELECT COUNT (*) to count rows
Try to be less computationally
Try to avoid full table scans, and if possible, index the filtered columns
Try to avoid null judgments on a field in a WHERE clause
Try to avoid using! = or <> in the WHERE clause
Try to avoid using an or connection in the WHERE clause
Try to avoid expression evaluation of fields
Avoid function operations on fields as much as possible
Try to avoid filtering connections using prefix columns that are not composite indexes
As little as possible, if you can, build the index
Join as little as possible
Try to use join instead of subquery
Try to avoid using in,not in or having with the WHERE clause, using exists,not exists instead
Try to avoid both ends of fuzzy matching like%***%
Try to use UNION all instead of union
Filter as early as possible
Avoid type conversions
Try BULK Insert
Prioritize high-concurrency SQL rather than large SQL with low frequency
Explain every SQL as much as possible
As far as possible from a global perspective

SQL Execution Plan

On the MySQL command line, execute the explain command to see the SQL execution plan

650) this.width=650; "src=" Https://s1.51cto.com/oss/201711/16/f224e3afebda391623c221484531af4e.png "title=" Qq20171116145900.png "alt=" F224e3afebda391623c221484531af4e.png "/>

Explanation of the Explain column

Table
Shows which table the data for this row is about.

Type
All:full table Scan, MySQL will traverse the full table to find a matching row.
Index:full index Scan,index and all are distinguished by the index type only to traverse the tree.
Range: Index range scan, the scan of the index starts at a point, returns rows that match the value of the domain, common to queries for between, <, >, and so on.
Ref: A non-unique index scan that returns all rows that match a single value. It is common to find a non-unique prefix that uses a non-unique index that is a unique index.
Eq_ref: Unique index Scan, for each index key, only one record in the table matches it. Common to primary key or unique index scans
Const, System: Use these types of access when MySQL optimizes a part of the query and converts it to a constant. If you place the primary key in the where list, MySQL can convert the query to a constant. System is a special case of a const type that uses system when the queried table has only one row.
Null:mysql decomposes statements during optimization, and does not even have access to tables or indexes when executed.

Possible_keys
Displays the indexes that may be applied in this table. If it is empty, there is no possible index. You can select an appropriate statement from the where statement for the related domain.

Key
The index that is actually used. If NULL, the index is not used. In rare cases, MySQL chooses an index that is poorly optimized. In this case, use Index (indexname) can be used in the SELECT statement to force an index or use ignore index (indexname) to force MySQL to ignore the index.

Key_len
The length of the index to use. Without loss of accuracy, the shorter the better.

Ref
Shows which column of the index is used and, if possible, a constant.

Rows
MySQL considers the number of rows that must be checked to return the requested data.

Extra
Using index: This value indicates that the overwrite index (covering index) is used in the corresponding select operation Note: MySQL can use the index to return fields from the select list without having to read the data file again according to the index An index that contains all the data that satisfies the needs of the query is called the Overwrite index.
Using where: means that the MySQL server is "filtered" after the storage Engine is recorded (Post-filter), and if the query fails to use the index, the role of using where is only a reminder that MySQL will filter the result set with a WHERE clause.
Using temporary: Indicates that MySQL needs to use temporary tables to store result sets, which are common in sorting and grouping queries.
Sorting operations that cannot be done with indexes in the using Filesort:mysql are called "File sorting".

MySQL SQL optimization and SQL execution plan