title: Mysql查詢
date: 2020-01-16 21:29:13
categories: 數(shù)據(jù)庫
tags:
- mysql
- 單表查詢
- 連接查詢
- 執(zhí)行計劃
description: MySQL Server有一個稱為查詢優(yōu)化器的模塊庵楷，一條查詢語句進(jìn)行語法解析之后就會被交給查詢優(yōu)化器來進(jìn)行優(yōu)化罢艾，優(yōu)化的結(jié)果就是生成一個所謂的執(zhí)行計劃

MySQL Server有一個稱為查詢優(yōu)化器的模塊，優(yōu)化的結(jié)果就是生成一個所謂的執(zhí)行計劃尽纽，這個執(zhí)行計劃表明了應(yīng)該使用哪些索引進(jìn)行查詢咐蚯，表之間的連接順序是啥樣的，最后會按照執(zhí)行計劃中的步驟調(diào)用存儲引擎提供的方法來真正的執(zhí)行查詢弄贿，并將查詢結(jié)果返回給用戶春锋。先來瞅瞅MySQL怎么執(zhí)行單表查詢

我們以下面這個表為例進(jìn)行講解

CREATE TABLE single_table (
    id INT NOT NULL AUTO_INCREMENT,
    key1 VARCHAR(100),
    key2 INT,
    key3 VARCHAR(100),
    key_part1 VARCHAR(100),
    key_part2 VARCHAR(100),
    key_part3 VARCHAR(100),
    common_field VARCHAR(100),
    PRIMARY KEY (id),
    KEY idx_key1 (key1),
    UNIQUE KEY idx_key2 (key2),
    KEY idx_key3 (key3),
    KEY idx_key_part(key_part1, key_part2, key_part3)
) Engine=InnoDB CHARSET=utf8;

對于單表查詢來說，大致分為兩種：

1. 使用全表掃描
2. 使用索引進(jìn)行查詢差凹，但又根據(jù)情況分為多種類型期奔，mysql把執(zhí)行查詢語句的方式成為訪問方法

下面介紹詳細(xì)的幾種類型：

1. const：通過主鍵或者唯一二級索引列來定位一條記錄的訪問方法定義為：const，也就是這種查詢只需要常數(shù)時間
   SELECT * FROM single_table WHERE id = 1438;

2. ref: 由于普通二級索引不具備唯一性危尿，所以有可能匹配多條記錄呐萌，從而導(dǎo)致多次回表查詢,注意唯一二級索引不限制null的數(shù)量，所以對于null的查詢最多是ref
   SELECT * FROM single_table WHERE key1 = 'abc';

3. ref_or_null: 有時候需要不僅匹配某個值谊娇，還查找NULL記錄
   SELECT * FROM single_demo WHERE key1 = 'abc' OR key1 IS NULL;

4. range: MySQL把這種利用索引進(jìn)行范圍匹配的訪問方法稱之為：range搁胆。
   SELECT * FROM single_table WHERE key2 IN (1438, 6328) OR (key2 >= 38 AND key2 <= 79);

5. index: mysql把可以直接采用采用遍歷二級索引記錄的執(zhí)行方式稱之為index，如下：
   SELECT key_part1, key_part2, key_part3 FROM single_table WHERE key_part2 = 'abc';
   它的查詢只有三個列邮绿，但條件并不是最左邊的列，這個時候我們既不能用索引攀例，也不應(yīng)該用全表查詢船逮，而是通過遍歷二級索引來查找（不需要回表查詢）

6. all： 全表掃描

索引合并

一般情況下我們最多只會用到單個二級索引，但如果查詢條件包括多個二級索引的時候粤铭，可能會觸發(fā)索引合并

1. Intersection合并
   SELECT * FROM single_table WHERE key1 = 'a' AND key3 = 'b';
   如上的查詢就有可能過程如下：

   • 從idx_key1二級索引對應(yīng)的B+樹中取出key1 = 'a'的相關(guān)記錄
   • 從idx_key3二級索引對應(yīng)的B+樹中取出key3 = 'b'的相關(guān)記錄挖胃。
   • 取交集再回表查詢信息

   之所以這樣做，因為索引查詢是一個順序I/O,而回表查則是一個隨機(jī)I/O,有兩種特殊情況：

   1. 對于聯(lián)合索引來說梆惯，在聯(lián)合索引中的每個列都必須等值匹配酱鸭，不能出現(xiàn)只出現(xiàn)匹配部分列的情況
   2. 主鍵列可以是范圍匹配，為什么垛吗？因為二級索引查詢出的結(jié)果是按照主鍵排序的凹髓，所以可以取交集

2. Union合并
   與Intersection取交集相對應(yīng)，Union取的是并集比如：
   SELECT * FROM single_table WHERE key1 = 'a' OR key3 = 'b'
   查詢限制也如Intersection類似怯屉，如果是范圍就不能合并蔚舀，為什么饵沧？不是取并集嗎？因為這兩個子集取出來時并不是排好序的赌躺，無法合并狼牺。那么我們可以先排好序再合并嗎，這樣就可以利用并集了礼患？可以是钥，這就是Sort-Union。

3. Sort-Union合并
   SELECT * FROM single_table WHERE key1 < 'a' OR key3 > 'z'
   我們可以這樣:

   • 先根據(jù)key1 < 'a'條件從idx_key1二級索引總獲取記錄缅叠，并按照記錄的主鍵值進(jìn)行排序
   • 再根據(jù)key3 > 'z'條件從idx_key3二級索引總獲取記錄悄泥，并按照記錄的主鍵值進(jìn)行排序
   • 因為上述的兩個二級索引主鍵值都是排好序的，剩下的操作和Union索引合并方式就一樣了

為什么沒有Sort-Intersetcion的說法痪署？這是假設(shè)Sort-Union的適用場景是根據(jù)搜索條件搜索出來的記錄數(shù)比較少码泞，所以排序消耗不大，而交集是因為一個條件出來的比較多狼犯，所以排序消耗較大

連接查詢

對于兩表連接來說余寥，驅(qū)動表只會被訪問一遍，但被驅(qū)動表卻要被訪問到好多遍悯森，具體訪問幾遍取決于對驅(qū)動表執(zhí)行單表查詢后的結(jié)果集中的記錄條數(shù)宋舷。偽代碼如下：

for each row in t1 {   #此處表示遍歷滿足對t1單表查詢結(jié)果集中的每一條記錄
    for each row in t2 {   #此處表示對于某條t1表的記錄來說，遍歷滿足對t2單表查詢結(jié)果集中的每一條記錄
        for each row in t3 {   #此處表示對于某條t1和t2表的記錄組合來說瓢姻，對t3表進(jìn)行單表查詢
            if row satisfies join conditions, send to client
        }
    }
}

想想一下祝蝠，如果被驅(qū)動表每次都是全表掃描，那么當(dāng)數(shù)據(jù)大時將是一個災(zāi)難幻碱，怎么辦绎狭？

SELECT * FROM t1, t2 WHERE t1.m1 > 1 AND t1.m1 = t2.m2 AND t2.n2 < 'd';

1. 使用索引加快連接速度
   如上，在第一步假設(shè)我們查出來ti.m1>1的只有一條褥傍，那么第二步就是執(zhí)行以下查詢：
   SELECT * FROM t2 WHERE t2.m2 = 2 AND t2.n2 < 'd';
   所以如果我們對被驅(qū)動表也就是t2的m2字段加入索引儡嘶，那么查找的方式將是const或者ref，mysql將這個過程稱為eq_ref,如果在n2字段中加入恍风，索引則過程變?yōu)閞ange

2. 基于塊的嵌套循環(huán)查詢
   當(dāng)被驅(qū)動表中的數(shù)據(jù)非常多時蹦狂，每次訪問被驅(qū)動表，被驅(qū)動表的記錄會被加載到內(nèi)存中朋贬，在內(nèi)存中的每一條記錄只會和驅(qū)動表結(jié)果集的一條記錄做匹配凯楔，之后就會被從內(nèi)存中清除掉。然后再從驅(qū)動表結(jié)果集中拿出另一條記錄锦募，再一次把被驅(qū)動表的記錄加載到內(nèi)存中一遍摆屯，周而復(fù)始，驅(qū)動表結(jié)果集中有多少條記錄御滩，就得把被驅(qū)動表從磁盤上加載到內(nèi)存中多少次鸥拧，顯然這個I/O代價也太大了党远，因此mysql提出了join buffer的概念：

   join buffer就是執(zhí)行連接查詢前申請的一塊固定大小的內(nèi)存，先把若干條驅(qū)動表結(jié)果集中的記錄裝在這個join buffer中富弦，然后開始掃描被驅(qū)動表沟娱，每一條被驅(qū)動表的記錄一次性和join buffer中的多條驅(qū)動表記錄做匹配，因為匹配的過程都是在內(nèi)存中完成的腕柜，所以這樣可以顯著減少被驅(qū)動表的I/O代價济似。

基于成本的優(yōu)化

成本介紹

我們之前老說MySQL執(zhí)行一個查詢可以有不同的執(zhí)行方案，它會選擇其中成本最低盏缤，案去真正的執(zhí)行查詢砰蠢。在MySQL中一條查詢語句的執(zhí)行成本是由下邊這兩個方面組成的

1. I/O成本：從磁盤到內(nèi)存這個加載的過程損耗的時間稱之為I/O成本
2. CPU成：讀取以及檢測記錄是否滿足對應(yīng)的搜索條件、對結(jié)果集進(jìn)行排序等這些操作損耗的時間稱之為CPU成本唉铜。

mysql讀取一個頁面花費的成本是1.0台舱，讀取以及檢測一條記錄是否符合搜索條件的成本默認(rèn)是0.2

單表查詢的成本

基于成本的優(yōu)化步驟：

1. 根據(jù)搜索條件，找出所有可能使用的索引
2. 計算全表掃描的代價
3. 計算使用不同索引執(zhí)行查詢的代價
4. 對比各方案潭流，找出成本最低的一個

我們使用一個例子來分析

SELECT * FROM single_table WHERE 
    key1 IN ('a', 'b', 'c') AND 
    key2 > 10 AND key2 < 1000 AND 
    key3 > key2 AND 
    key_part1 LIKE '%hello%' AND
    common_field = '123';

1. 找出可能使用的索引： key1沿后、key2. （其中key3沒有和常數(shù)比較的畴，key_part1 沒有使用常數(shù)或者前綴）

2. 計算全表掃描的代價： IO成本+CPU成本憨闰，因此我們需要兩個信息遇绞，一個是聚簇索引占用的頁數(shù)，一個是表中的記錄數(shù)
   使用show table status like 'single_table';查詢表狀態(tài)

   • Rows讼撒，記錄條數(shù)(10059)
   • Data_length(1589248) =頁數(shù)量 x 每個頁的大小浑厚，我們默認(rèn)用的16kb的頁面大小，所以頁面數(shù)=1589248 ÷ 16 ÷ 1024 = 97

   所以全表掃描的IO代價是97x1.0+1.1=98.1根盒，其中1.1是微調(diào)值钳幅，cpu成本是：10059x0.2+1.0=2012.8，總成本是2110.9

3. 計算不同索引執(zhí)行查詢的代價：

   • idx_key2的成本分析： key2 > 10 AND key2 < 1000炎滞，對于二級索引+回表的方式贡这，mysql主要依賴：

     • 范圍區(qū)間數(shù)量，mysql查詢優(yōu)化器粗暴的認(rèn)為讀取索引的一個范圍區(qū)間和讀取一個頁面的成本以及回表操作都是是相同的厂榛，所以對于(10,1000)這個區(qū)間，成本是1x1.0=1.0

     • 需要回表的記錄數(shù)： 步驟一丽惭，先根據(jù)key2=10這個條件击奶，找出最左邊的記錄，步驟二责掏，再根據(jù)key=1000找出最右邊的記錄柜砾，然后統(tǒng)計出記錄條數(shù)。如果超過10頁换衬，則計算10頁的平均值再算出記錄之間的頁面的數(shù)量即可痰驱，根據(jù)上述算法測得idx_key2在區(qū)間(10, 1000)之間大約有95條記錄

   所以讀取這95條二級索引的cpu的成本就是95 x 0.2 + 0.01 = 19.01证芭，回表操作的IO成本為95x1.0
   然后再計算其他的匹配條件cpu成本 95x0.2，一共是134.01

   • 使用idx_key1執(zhí)行查詢的成本分析担映，key1 IN(a,b,c)废士，也就是三個單點區(qū)間35 + 44 + 39 = 118
     IO成本：3.0 + 118 x 1.0 = 121.0
     CPU成本： 118 x 0.2 + 0.01 + 118 x 0.2 = 47.21

基于索引統(tǒng)計數(shù)據(jù)的成本計算

MySQL把這種通過直接訪問索引對應(yīng)的B+樹來計算某個范圍區(qū)間對應(yīng)的索引記錄條數(shù)的方式稱之為index dive
但如果單點區(qū)間太多，比如in條件有上萬的參數(shù)蝇完，那么就有上萬次index dive操作官硝，性能消耗太大，于是提供給了一個系統(tǒng)變量eq_range_index_dive_limit短蜕，也就是如果IN語句中的參數(shù)小于這個值氢架，將使用index dive的方式，但大于或等于的時候就不是了朋魔，而是使用索引統(tǒng)計數(shù)據(jù)來估算

mysql> SHOW INDEX FROM single_table;
+--------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------
| Table        | Non_unique | Key_name     | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type
+--------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+-----------
| single_table |          0 | PRIMARY      |            1 | id          | A         |       10059 |     NULL | NULL   |      | BTREE
| single_table |          0 | idx_key2     |            1 | key2        | A         |       10059 |     NULL | NULL   | YES  | BTREE 
| single_table |          1 | idx_key1     |            1 | key1        | A         |       10059 |     NULL | NULL   | YES  | BTREE
| single_table |          1 | idx_key3     |            1 | key3        | A         |       10059 |     NULL | NULL   | YES  | BTREE
| single_table |          1 | idx_key_part |            1 | key_part1   | A         |       10059 |     NULL | NULL   | YES  | BTREE
| single_table |          1 | idx_key_part |            2 | key_part2   | A         |       10059 |     NULL | NULL   | YES  | BTREE
| single_table |          1 | idx_key_part |            3 | key_part3   | A         |       10059 |     NULL | NULL   | YES  | BTREE
+--------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+---------

我們重點關(guān)注Cardinality屬性岖研，也就是基數(shù)的意思，表示列中不重復(fù)值的個數(shù)警检。
那么根據(jù)索引統(tǒng)計計算就是這種方式： 行數(shù)/Cardinality =一個值的重復(fù)次數(shù)孙援，簡單粗暴,那么10000個單點區(qū)間就是10000X1=10000條回表記錄

連接查詢的成本

連接查詢總成本 = 單次訪問驅(qū)動表的成本 + 驅(qū)動表扇出數(shù) x 單次訪問被驅(qū)動表的成本
我們把對驅(qū)動表進(jìn)行查詢后得到的記錄條數(shù)稱之為驅(qū)動表的扇出

1. 對于外連接，因為驅(qū)動表和被驅(qū)動表是固定的所以只需要：
   分別為驅(qū)動表和被驅(qū)動表選擇成本最低的訪問方法解滓。
2. 對于內(nèi)連接來說赃磨，驅(qū)動表和被驅(qū)動表是可以互換的，所以需要考慮不同順序的查詢