技術文章 » 關聯式資料庫之mysql三：從一條sql的生命週期說起

關聯式資料庫之mysql三：從一條sql的生命週期說起

2020-11-13 18:00:31

欄目介紹關聯式資料庫的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  |
+----+-------------+--------------------------+------------+------+---------------+------------+---------+-------+--------+----------+-------+複製程式碼

id

  • id相同執行順序由上至下
  • id不同，id值越大優先順序越高，越先被執行

select_type

  • SIMPLE：簡單的 select 查詢，查詢中不包含子查詢或者 union
  • PRIMARY：查詢中包含子部分，最外層查詢則被標記為 primary
  • DERIVED：是子查詢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 的值越大越好

extra

  • Using 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 * 語句將不會使到覆蓋索引，不利於查詢的效能優化

正確使用索引的優點

  • 避免全表掃描
  1. 單表查詢時，全表掃描需要查詢每一行
  2. 多表查詢時，全表掃描至少需要檢索所有表中每一行
  • 提高速度
  1. 可以迅速定位結果集的第一行
  2. 排除不相關的結果
  3. 對於MIN()或者MAX()值不必檢查每一行
  • 提高排序和分組的效率
  • 在可以使用覆蓋索引的情況下避免row loop-up

索引的代價

  • 如果存在過多索引，資料修改將會變得緩慢
  1. 受影響的索引需要被更新
  2. 對於寫密集型環境壓力很大
  • 索引消耗過多磁碟空間
  1. InnoDB儲存引擎將索引和資料儲存在一起
  2. 需要監控磁碟空間

索引最佳實踐

對於如下列考慮使用索引

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

考慮針對字串型列使用字首索引

  • 可以更快速地比較與loop up
  • 減少磁碟I/O

SELECT語句效率低下時考慮

  • 避免全表掃描
  • 嘗試增加索引
  1. WHERE語句
  2. 表連線條件
  • 利用ANALYZE TABLE來收集統計資訊
  • 考慮儲存引擎層的優化

調優表連線方法

  • 在ON或USING子句的列上增加索引
  • 利用SELECT STRAIGHT_JOIN來強制表連線順序
  • 在ORDER BY和GROUP BY的列上增加索引
  • join連線不一定比子查詢效率高

更多相關免費學習推薦：(視訊)

以上就是關聯式資料庫之mysql三：從一條sql的生命週期說起的詳細內容，更多請關注TW511.COM其它相關文章！