SQL optimization skills that ordinary programmers must master

Whether it is at work or in an interview, you basically need to master some SQL optimization skills. For example, use explain to view the execution plan of SQL, and then optimize the SQL according to the execution plan. .

Regarding the use of explain and the analysis of related fields, it is now basically standard for programmers.

No, please read it carefully.

1. What is a MySQL execution plan?

To have a better understanding of the execution plan, you need to first have a simple understanding of the basic structure of MySQL and the basic principles of querying. learn.

The functional architecture of MySQL itself is divided into three parts, namely the application layer, the logical layer, and the physical layer. Not only MySQL, but most other database products are divided according to this architecture.

• The application layer is mainly responsible for interacting with the client, establishing links, remembering the link status, returning data, and responding to requests. This layer deals with the client.
• The logic layer is mainly responsible for query processing, transaction management and other database function processing, taking query as an example.

After first receiving the query SQL, the database will immediately allocate a thread to process it. In the first step, the query processor will optimize the SQL query and generate an execution plan after optimization. Then it is handed over to the plan executor for execution.

The plan executor needs to access the lower-level transaction manager and storage manager to operate data. Their respective divisions of labor are different. Finally, the query structure information is obtained by calling the file of the physical layer and the final result is responded. to the application layer.

• Physical layer, the files stored on the actual physical disk mainly include penny data files and log files.

Through the above description, generating an execution plan is an essential step for executing an SQL. The performance of an SQL can be intuitively seen by viewing the execution plan. The execution plan Various query types and levels are provided, which we review and serve as the basis for performance analysis.

2. How to analyze the execution plan

MySQL provides us with the explain keyword to visually view the execution plan of a SQL.

explain shows how MySQL uses indexes to process select statements and join tables, which can help choose better indexes and write more optimized query statements.

Below we use explain to make a query, as follows:

mysql> explain select * from payment;
+----+-------------+---------+------------+------+---------------+------+---------+------+-------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+-------+----------+-------+
|  1 | SIMPLE      | payment | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 16086 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------+------+---------+------+-------+----------+-------+
1 row in set, 1 warning (0.01 sec)

There are 12 columns in the query structure. Understanding the meaning of each column is crucial to understanding the execution plan. The following is in the form of a table Be explained.

SELECT identifier, this is the query sequence number of SELECT. SELECT type, which can be any of the following: The table referenced by the output rowIf the query is based on a partitioned table, the display query will access partition. Join type. The various join types are given below, sorted from best to worst:

Column name	Description
##id
select_type	SIMPLE: Simple SELECT (do not use UNION or Subquery)PRIMARY:Outermost SELECTUNION:The second or following SELECT statement in UNIONDEPENDENT UNION:The second or following SELECT statement in UNION The subsequent SELECT statement depends on the outer query UNION RESULT: The result of UNION SUBQUERY: The first SELECT in the subquery DEPENDENT SUBQUERY: In the subquery The first SELECT depends on the outer queryDERIVED: SELECT of the derived table (subquery of the FROM clause)
table
partitions
type	system: The table has only one row (=system table). This is a special case of the const join type. const: The table has at most one matching row, which will be read at the beginning of the query. Because there is only one row, the column values ​​in this row can be treated as constants by the rest of the optimizer. const tables are fast because they are read only once! eq_ref: For each combination of rows from the previous table, read one row from this table. This is probably the best join type, besides const types. ref: For each combination of rows from the previous table, all rows with matching index values ​​will be read from this table. ref_or_null: This join type is like ref, but adds MySQL to specifically search for rows containing NULL values. index_merge: This join type indicates that the index merge optimization method is used. unique_subquery: This type replaces the ref of the IN subquery in the following form: value IN (SELECT primary_key FROM single_table WHERE some_expr) unique_subquery is an index lookup function that can completely replace the subquery and is more efficient. . index_subquery: This join type is similar to unique_subquery. IN subqueries can be replaced, but only for non-unique indexes in subqueries of the following form: value IN (SELECT key_column FROM single_table WHERE some_expr) range: Retrieve only rows in a given range, use An index to select rows (recommended, at worst this level is required). index: This join type is the same as ALL, except that only the index tree is scanned. This is usually faster than ALL because index files are usually smaller than data files. ALL: Perform a complete table scan for each row combination from the previous table, indicating that the query needs to be optimized. Generally speaking, it is necessary to ensure that the query reaches at least the range level, and preferably reaches the ref. The above system is the best, in descending order, ALL is the worst
possible_keys	Indicate which index MySQL can use to find in the table Line
key	shows the key (index) that MySQL actually decided to use. If no index is selected, the key is NULL.
key_len	Displays the key length that MySQL decides to use. If the key is NULL, the length is NULL. The shorter the length the better without losing accuracy
ref	Show which column or constant is used with the key from the table Select rows in .
rows	Displays the number of rows that MySQL thinks it must examine when executing the query. Multiplying data across multiple rows provides an estimate of the number of rows to process.
filtered	Shows the percentage estimate of the number of rows filtered by the condition.
Extra	This column contains the details of how MySQL resolved the query Distinct:MySQL found the 1st After a row is matched, stop searching for more rows for the current combination of rows. Select tables optimized away MySQL returns data without traversing the table or index at all, indicating that it has been optimized to the point that it cannot be optimized anymore Not exists:MySQL can perform query optimization LEFT JOIN optimization, after finding a row matching the LEFT JOIN standard, no more rows will be checked in the table for the previous row combination. range checked for each record (index map: #):MySQL did not find a good index that can be used, but found that if the column values ​​​​from the previous table are known, some indexes may be used. Using filesort: MySQL needs an extra pass to figure out how to retrieve the rows in sorted order, indicating that the query needs to be optimized. Using index: Retrieve column information from the table by reading the actual rows using only the information in the index tree without further searching. Using temporary: In order to solve the query, MySQL needs to create a temporary table to accommodate the results, which means the query needs to be optimized. Using where: The WHERE clause is used to limit which row matches the next table or is sent to the customer. Using sort_union(...), Using union(...), Using intersect(...): These functions illustrate how to merge index scans for the index_merge join type. Using index for group-by: Similar to the Using index method of accessing a table, Using index for group-by means that MySQL has found an index that can be used to query all columns of GROUP BY or DISTINCT queries. , without having to additionally search the hard drive to access the actual table.

#According to the above table, it can provide good help in execution plan analysis.

Note: If it is to cope with the interview, it is best to be able to memorize it. If you cannot memorize it all, you should also be able to say 123, and then say, you can’t remember so much. , you can read the relevant documents to optimize SQL accordingly.

The above is the detailed content of SQL optimization skills that ordinary programmers must master. For more information, please follow other related articles on the PHP Chinese website!