一般程式設計師必須掌握的SQL最佳化技巧

不管是工作中，還是面試中，基本上都需要搞定一些SQL最佳化技巧，比如說使用explain查看SQL的執行計劃，然後，針對執行計劃對SQL進行最佳化。

關於explain的使用以及相關欄位的分析，現在基本上已經是程式設計師標配了。

不會的，請認真看完。

1、什麼是MySQL執行計劃

要對執行計劃有個比較好的理解，需要先對MySQL的基礎結構及查詢基本原理有簡單的了解。

MySQL本身的功能架構分為三個部分，分別是 應用層、邏輯層、物理層，不只是MySQL ，其他大多數資料庫產品都是依照這個架構來劃分的。

• 應用層，主要負責與客戶端進行交互，建立鏈接，記住鏈接狀態，返回數據，響應請求，這一層是和客戶端打交道的。
• 邏輯層，主要負責查詢處理、事務管理等其他資料庫功能處理，以查詢為例。

先接收到查詢SQL之後，資料庫會立即指派一個執行緒來處理，第一步查詢處理器會對SQL查詢進行最佳化，最佳化後會產生執行計劃，然後交由計劃執行器來執行。

計劃執行器需要訪問更底層的事務管理器，存儲管理器來操作數據，他們各自的分工各有不同，最終通過調用物理層的文件獲取到查詢結構信息，將最終結果響應給應用層。

• 物理層，實際實體磁碟上儲存的文件，主要有分文資料文件，日誌檔案。

透過上面的描述，生成執行計劃是執行一條SQL不可或缺的步驟，一個SQL效能的好壞，可以透過檢視執行計畫很直觀的看出來，執行計劃提供了各種查詢類型與級別，方面我們進行查看以及作為性能分析的依據。

2、如何分析執行計畫

MySQL為我們提供了 explain 關鍵字來直覺的檢視一條SQL的執行計畫。

explain顯示了MySQL如何使用索引來處理select語句以及連接表，可以幫助選擇更好的索引和寫出更優化的查詢語句。

下面我們使用explain 做一個查詢，如下：

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)

查詢結構中有12列，理解每一列的含義，對理解執行計劃至關重要，下面用一個表格的形式進行說明。

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.

以上是一般程式設計師必須掌握的SQL最佳化技巧的詳細內容。更多資訊請關注PHP中文網其他相關文章！