mysql中sql的生命周期是什么

134次閱讀

沒有評論

共計 5823 個字符，預計需要花費 15 分鐘才能閱讀完成。

自動寫代碼機器人，免費開通

這篇文章主要介紹 mysql 中 sql 的生命周期是什么，文中介紹的非常詳細，具有一定的參考價值，感興趣的小伙伴們一定要看完！

MYSQL Query Processing

sql 的執行過程和 mysql 體系架構基本一致

執行過程：

連接器：

建立與 MySQL 的連接，用于查詢 SQL 語句，判斷權限。

查詢緩存：如果語句不在查詢緩存中，就會繼續后面的執行階段。執行完成后，執行結果會被存入查詢緩存中如果查詢命中緩存，MySQL 不需要執行后面的復雜操作，就可以直接返回結果，提升效率分析器：

對 SQL 語句進行硬解析，分析器先會做詞法分析。分析 SQL 語句的組成成分。判斷輸入的 SQL 語句是否滿足語法規則。

優化器：

優化器是在表里面有多個索引的時候，決定使用哪個索引；或者在一個語句有多表關聯（join）的時候，決定各個表的連接順序。不同的執行方法的邏輯結果是一樣的，但是執行的效率會有不同，而優化器的作用就是決定選擇使用哪一個方案。

執行器：有索引：第一次調用的是取滿足條件的第一行這個接口，之后循環取滿足條件的下一行這個接口，最終把查詢結果返回客戶端無索引：調用 InnoDB 引擎接口取這個表的第一行，判斷 sql 查詢條件，如果不是則跳過，如果是則將這行存在結果集中；調用引擎接口取下一行，重復相同的判斷邏輯，直到取到這個表的最后一行。執行器將上述遍歷過程中所有滿足條件的行組成的記錄集作為結果集返回給客戶端理解執行計劃

EXPLAIN 命令輸出 MySQL 將如何執行你的 SQL 語句，但不會返回數據

如何使用

[root@localhost][(none)] explain select * from 表名 where project_id = 36;
+----+-------------+--------------------------+------------+------+---------------+------------+---------+-------+--------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------------------------+------------+------+---------------+------------+---------+-------+--------+----------+-------+
| 1 | SIMPLE | 表名 | NULL | ref | project_id | project_id | 4 | const | 797964 | 100.00 | NULL |
+----+-------------+--------------------------+------------+------+---------------+------------+---------+-------+--------+----------+-------+ 復制代碼

idid 相同執行順序由上至下 id 不同，id 值越大優先級越高，越先被執行 select_typeSIMPLE：簡單的 select 查詢，查詢中不包含子查詢或者 unionPRIMARY：查詢中包含子部分，最外層查詢則被標記為 primaryDERIVED：是子查詢 from 的一部分 DEPENDENT SUBQUERY：子查詢中的第一個 SELECT，子查詢依賴于外層查詢的結果 SUBQUERY 表示在 select 或 where 列表中包含了子查詢，MATERIALIZED：表示 where 后面 in 條件的子查詢 UNION：表示 union 中的第二個或后面的 select 語句 UNION RESULT：union 的結果 table 表對象 type

system const eq_ref ref range index ALL（查詢效率）

system：表中只有一條數據，這個類型是特殊的 const 類型 const：針對于主鍵或唯一索引的等值查詢掃描，最多只返回一個行數據。速度非常快，因為只讀取一次即可。eq_ref：此類型通常出現在多表的 join 查詢，表示對于前表的每一個結果，都只能匹配到后表的一行結果，并且查詢的比較操作通常是 =，查詢效率較高 ref：此類型通常出現在多表的 join 查詢，針對于非唯一或非主鍵索引，或者是使用了最左前綴規則索引的查詢 range：范圍掃描這個類型通常出現在 , , =, , =, IS NULL, = , BETWEEN, IN() 操作中 index：索引樹掃描 ALL：全表掃描（full table scan）possible_keys 可能使用的索引，注意不一定會使用查詢涉及到的字段上若存在索引，則該索引將被列出來當該列為 NULL 時就要考慮當前的 SQL 是否需要優化了 key 顯示 MySQL 在查詢中實際使用的索引，若沒有使用索引，顯示 NULL。查詢中若使用了覆蓋索引(覆蓋索引：索引的數據覆蓋了需要查詢的所有數據)，則該索引僅出現在 key 列表中 key_length 索引長度 ref 表示上述表的連接匹配條件，即哪些列或常量被用于查找索引列上的值 rows 返回估算的結果集數目，并不是準確的值 filtered 示返回結果的行數占需讀取行數的百分比，filtered 的值越大越好 extraUsing where：表示優化器需要通過索引回表，之后到 server 層進行過濾查詢數據 Using index：表示直接訪問索引就足夠獲取到所需要的數據，不需要回表 Using index condition：在 5.6 版本后加入的新特性（Index Condition Pushdown）Using index for group-by：使用了索引來進行 GROUP BY 或者 DISTINCT 的查詢 Using filesort：當 Extra 中有 Using filesort 時, 表示 MySQL 需額外的排序操作, 不不能通過索引順序達到排序效果. 一般有 Using filesort, 都建議優化去掉, 因為這樣的查詢 CPU 資源消耗大 Using temporary 臨時表被使用，時常出現在 GROUP BY 和 ORDER BY 子句情況下。（sort buffer 或者磁盤被使用）

光看 filesort 字面意思，可能以為是要利用磁盤文件進行排序，實則不全然。當 MySQL 不能使用索引進行排序時，就會利用自己的排序算法 (快速排序算法) 在內存 (sort buffer) 中對數據進行排序，如果內存裝載不下，它會將磁盤上的數據進行分塊，再對各個數據塊進行排序，然后將各個塊合并成有序的結果集（實際上就是外排序）。

當對連接操作進行排序時，如果 ORDER BY 僅僅引用第一個表的列，MySQL 對該表進行 filesort 操作，然后進行連接處理，此時，EXPLAIN 輸出“Using filesort”；否則，MySQL 必須將查詢的結果集生成一個臨時表，在連接完成之后行行 filesort 操作，此時，EXPLAIN 輸出“Using temporary;Using filesort”。

提高查詢效率正確使用索引

為解釋方便，來一個 demo：

DROP TABLE IF EXISTS user; 
CREATE TABLE user( 
id int AUTO_INCREMENT PRIMARY KEY, 
user_name varchar(30) NOT NULL, 
gender bit(1) NOT NULL DEFAULT b’1’, 
city varchar(50) NOT NULL, 
age int NOT NULL 
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
ALTER TABLE user ADD INDEX idx_user(user_name , city , age); 
復制代碼

什么樣的索引可以被使用？** 全匹配：**SELECT * FROM user WHERE user_name= JueJin AND age= 5 AND city= 上海（與 where 后查詢條件的順序無關）匹配最左前綴：(user_name)、(user_name, city)、(user_name , city , age)（滿足最左前綴查詢條件的順序與索引列的順序無關，如：(city, user_name)、(age, city, user_name)）** 匹配列前綴：**SELECT * FROM user WHERE user_name LIKE W% ** 匹配范圍值：**SELECT * FROM user WHERE user_name BETWEEN W% AND Z% 什么樣的索引無法被使用？**where 查詢條件中不包含索引列中的最左索引列，則無法使用到索引：**

SELECT * FROM user WHERE city= 上海

SELECT * FROM user WHERE age= 26

SELECT * FROM user WHERE age= 26 AND city=‘上海

** 即使 where 的查詢條件是最左索引列，也無法使用索引查詢用戶名以 N 結尾的用戶：**

SELECT * FROM user WHERE user_name LIKE %N

** 如果 where 查詢條件中有某個列的范圍查詢，其右邊的所有列都無法使用索引優化查詢：**

SELECT * FROM user WHERE user_name= JueJin AND city LIKE 上 % AND age=31;

** 索引列不能是表達式的一部分，也不能作為函數的參數，否則無法使用索引查詢：**

SELECT * FROM user WHERE user_name=concat(user_name, PLUS

選擇合適的索引列順序在組合索引的創建中索引列的順序非常重要，正確的索引順序依賴于使用該索引的查詢的查詢方式對于組合索引的索引順序可以將選擇性最高的列放到索引最前列，該法則與前綴索引的選擇性方法一致并不是說所有的組合索引的順序都使用該法則就能確定，還需要根據具體的查詢場景來確定具體的索引順序覆蓋索引條件如果一個索引中包含所有要查詢的字段的值，那么就稱之為覆蓋索引

SELECT user_name, city, age FROM user WHERE user_name= Tony AND age= 28 AND city= 上海

因為要查詢的字段 (user_name, city, age) 都包含在組合索引的索引列中，所以就使用了覆蓋索引查詢，查看是否使用了覆蓋索引可以通過執行計劃中的 Extra 中的值為 Using index 則證明使用了覆蓋索引，覆蓋索引可以極大的提高訪問性能。

使用索引進行排序

在排序操作中如果能使用到索引來排序，那么可以極大地提高排序的速度，要使用索引來排序需要滿足以下兩點即可：

ORDER BY 子句后的列順序要與組合索引的列順序一致，且所有排序列的排序方向 (正序 / 倒序) 需一致所查詢的字段值需要包含在索引列中，及滿足覆蓋索引

排序可用 demo：

SELECT user_name, city, age FROM user_test ORDER BY user_name;SELECT user_name, city, age FROM user_test ORDER BY user_name,city;SELECT user_name, city, age FROM user_test ORDER BY user_name DESC,city DESC;SELECT user_name, city, age FROM user_test WHERE user_name= Tony ORDER BY city;

排序不可用 demo：

SELECT user_name, city, age FROM user_test ORDER BY user_name gender;SELECT user_name, city, age, gender FROM user_test ORDER BY user_name;SELECT user_name, city, age FROM user_test ORDER BY user_name ASC,city DESC;SELECT user_name, city, age FROM user_test WHERE user_name LIKE W% ORDER BY city; 數據獲取建議

不要返回應用戶程序所不需要的數據限制返回數

LIMIT：MySQL 并不能按照需求返回數據量，也就是 MySQL 總是會查詢出全部數據，使用 LIMIT 子句其實是為了減小網絡數據傳輸的壓力，并不會減小數據的讀取行數。

去掉不需要的列

SELECT * 語句取出表中的所有字段，不論該字段的數據對調用的應用程序是否有用，這會對服務器資源造成浪費，甚至會對服務器的性能產生一定的影響如果表的結構在以后發生了改變，那么 SELECT * 語句可能會取到不正確的數據執行 SELECT * 語句時，首先要查找出表中有哪些列，然后才能開始執行 SELECT * 語句，這在某些情況會產生性能問題使用 SELECT * 語句將不會使到覆蓋索引，不利于查詢的性能優化

正確使用索引的優點

避免全表掃描單表查詢時，全表掃描需要查詢每一行多表查詢時，全表掃描至少需要檢索所有表中每一行提高速度可以迅速定位結果集的第一行排除不相關的結果對于 MIN()或者 MAX()值不必檢查每一行提高排序和分組的效率在可以使用覆蓋索引的情況下避免 row loop-up 索引的代價如果存在過多索引，數據修改將會變得緩慢受影響的索引需要被更新對于寫密集型環境壓力很大索引消耗過多磁盤空間 InnoDB 存儲引擎將索引和數據存儲在一起需要監控磁盤空間索引最佳實踐

對于如下列考慮使用索引

WHERE 子句中的列 ORDER BY 或 GROUP BY 子句中的列表連接條件列

考慮針對字符串型列使用前綴索引

可以更快速地比較與 loop up 減少磁盤 I /OSELECT 語句效率低下時考慮避免全表掃描嘗試增加索引 WHERE 語句表連接條件利用 ANALYZE TABLE 來收集統計信息考慮存儲引擎層的優化調優表連接方法在 ON 或 USING 子句的列上增加索引利用 SELECT STRAIGHT_JOIN 來強制表連接順序在 ORDER BY 和 GROUP BY 的列上增加索引 join 連接不一定比子查詢效率高

以上是 mysql 中 sql 的生命周期是什么的所有內容，感謝各位的閱讀！希望分享的內容對大家有幫助，更多相關知識，歡迎關注丸趣 TV 行業資訊頻道！

向 AI 問一下細節

丸趣 TV 網 – 提供最優質的資源集合！

正文完