搜尋

首頁 >資料庫 >mysql教程 >關係資料庫之mysql三：從一條sql的生命週期說起

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

coldplay.xixi
coldplay.xixi轉載
2020-11-13 17:16:472883瀏覽

mysql教學欄位介紹關聯式資料庫的sql的生命週期。

關係資料庫之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  |
+----+-------------+--------------------------+------------+------+---------------+------------+---------+-------+--------+----------+-------+复制代码

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, ageage )都包含在組合索引的索引列中，所以就使用了覆蓋索引查詢，查看是否使用了覆蓋索引可以通過執行計劃中的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; , 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_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教學########## (影片)#########

以上是關係資料庫之mysql三：從一條sql的生命週期說起的詳細內容。更多資訊請關注PHP中文網其他相關文章！

陳述：
本文轉載於：juejin.im。如有侵權，請聯絡admin@php.cn刪除
上一篇：MySQL 執行計劃explain與索引資料結構推演下一篇：MySQL 執行計劃explain與索引資料結構推演

相關文章

看更多