乱爱性全过程免费视频/午夜毛片视频/中文字幕av三区/一级视频网址

當(dāng)前位置： 維易PHP培訓(xùn)學(xué)院 > MYSQL教程 > 內(nèi)容正文

Mysql實(shí)例探究MySQL優(yōu)化器對(duì)索引和JOIN順序的選擇

作者：VEPHP   時(shí)間 2017-10-23

《Mysql實(shí)例探究MySQL優(yōu)化器對(duì)索引和JOIN順序的選擇》要點(diǎn)：
本文介紹了Mysql實(shí)例探究MySQL優(yōu)化器對(duì)索引和JOIN順序的選擇，希望對(duì)您有用。如果有疑問，可以聯(lián)系我們。

本文通過一個(gè)案例來看看MySQL優(yōu)化器如何選擇索引和JOIN順序.表布局和數(shù)據(jù)準(zhǔn)備參考本文最后部分"測(cè)試環(huán)境".這里主要介紹MySQL優(yōu)化器的主要執(zhí)行流程,而不是介紹一個(gè)優(yōu)化器的各個(gè)組件(這是另一個(gè)話題).MYSQL入門

?? 我們知道,MySQL優(yōu)化器只有兩個(gè)自由度：順序選擇；單表拜訪方式；這里將詳細(xì)剖析下面的SQL,看看MySQL優(yōu)化器如何做出每一步的選擇.MYSQL入門

explain
select *
from
 employee as A,department as B
where
   A.LastName = 'zhou'
 and B.DepartmentID = A.DepartmentID
 and B.DepartmentName = 'TBX';

1. 可能的選擇MYSQL入門

?? 這里看到JOIN的順序可以是A|B或者B|A,單表拜訪方式也有多種,對(duì)于A表可以選擇：全表掃描和索引`IND_L_D`(A.LastName = 'zhou')或者`IND_DID`(B.DepartmentID = A.DepartmentID).對(duì)于B也有三個(gè)選擇：全表掃描、索引IND_D、IND_DN.
2. MySQL優(yōu)化器如何做
2.1 概述MYSQL入門

?? MySQL優(yōu)化器主要工作包括以下幾部分：Query Rewrite(包括Outer Join轉(zhuǎn)換等)、const table detection、range analysis、JOIN optimization(順序和拜訪方式選擇)、plan refinement.這個(gè)案例從range analysis開始.
2.2 range analysisMYSQL入門

?? 這部分包含所有Range和index merge成本評(píng)估(參考1 參考2).這里,等值表達(dá)式也是一個(gè)range,所以這里會(huì)評(píng)估其成本,計(jì)算出found records(表示對(duì)應(yīng)的等值表達(dá)式,大概會(huì)選擇出多少條記錄).MYSQL入門

?? 本案例中,range analysis會(huì)針對(duì)A表的條件A.LastName = 'zhou'和B表的B.DepartmentName = 'TBX'分別做闡發(fā).其中：MYSQL入門

表A A.LastName = 'zhou' found records: 51
表B B.DepartmentName = 'TBX' found records: 1MYSQL入門

?? 這兩個(gè)條件都不是range,但是這里計(jì)算的值仍然會(huì)存儲(chǔ),在后面的ref拜訪方式評(píng)估的時(shí)候使用.這里的值是根據(jù)records_in_range接口返回,而對(duì)于InnoDB每次調(diào)用這個(gè)函數(shù)都會(huì)進(jìn)行一次索引頁的采樣,這是一個(gè)很消耗性能的操作,對(duì)于很多其他的關(guān)系數(shù)據(jù)庫是使用"直方圖"的統(tǒng)計(jì)數(shù)據(jù)來避免這次操作(相信MariaDB后續(xù)版本也將實(shí)現(xiàn)直方圖統(tǒng)計(jì)信息).
2.3 順序和拜訪方式的選擇：窮舉MYSQL入門

?? MySQL通過枚舉所有的left-deep樹(也可以說所有的left-deep樹就是整個(gè)MySQL優(yōu)化器的搜索空間),來找到最優(yōu)的執(zhí)行順序和拜訪方式.
2.3.1 排序MYSQL入門

?? 優(yōu)化器先根據(jù)found records對(duì)所有表進(jìn)行一個(gè)排序,記錄少的放前面.所以,這里次序是B、A.
2.3.2 greedy searchMYSQL入門

?? 當(dāng)表的數(shù)量較少(少于search_depth,默認(rèn)是63)的時(shí)候,這里直接蛻化為一個(gè)窮舉搜索,優(yōu)化器將窮舉所有的left-deep樹找到最優(yōu)的執(zhí)行計(jì)劃.另外,優(yōu)化器為了減少因?yàn)樗阉骺臻g龐大帶來巨大的窮舉消耗,所以使用了一個(gè)"偷懶"的參數(shù)prune_level(默認(rèn)打開),具體如何"偷懶",可以參考JOIN順序選擇的復(fù)雜度.不過至少必要有三個(gè)表以上的關(guān)聯(lián)才會(huì)有"偷懶",所以本案例不適用.
2.3.3 窮舉MYSQL入門

?? JOIN的第一個(gè)表可以是：A或者B；如果第一個(gè)表選擇了A,第二個(gè)表可以選擇B；如果第一個(gè)表選擇了B,第二個(gè)表可以選擇A；MYSQL入門

?? 因?yàn)榍懊娴呐判?B表的found records更少,所以JOIN次序窮舉時(shí)的第一個(gè)表先選擇B(這個(gè)是有講究的).MYSQL入門

(*) 選擇第一個(gè)JOIN的表為B
? (**) 確定B表的拜訪方式
??? 因?yàn)锽表為第一個(gè)表,所以無法使用索引IND_D(B.DepartmentID = A.DepartmentID),而只能使用IND_DN(B.DepartmentName = 'TBX')
????? 使用IND_DN索引的成本計(jì)算：1.2；其中IO成本為1.
????? 是否使用全表掃描：這里會(huì)比較使用索引的IO成本和全表掃描的IO成本,前者為1,后者為2；所以忽略全表掃描
??? 所以,B表的拜訪方式ref,使用索引IND_DMYSQL入門

? (**) 從剩余的表中窮舉選出第二個(gè)JOIN的表,這里剩余的表為：A
? (**) 將A表加入JOIN,并確定其拜訪方式
??? 可以使用的索引為：`IND_L_D`(A.LastName = 'zhou')或者`IND_DID`(B.DepartmentID = A.DepartmentID)
??? 依次計(jì)算使用索引IND_L_D、IND_DID的成本：
??? (***) IND_L_D A.LastName = 'zhou'
????????? 在range analysis階段給出了A.LastName = 'zhou'對(duì)應(yīng)的記錄約為：51.
????????? 所以,計(jì)算IO成本為：51；ref做IO成本計(jì)算時(shí)會(huì)做一次修正,將其修正為worst_seek(參考)
????????? 修正后IO成本為：15,總成本為：25.2
??? (***) IND_DID B.DepartmentID = A.DepartmentID
????????? 這是一個(gè)需要知道前面表的結(jié)果,才能計(jì)算的成本.所以range analysis是無法分析的
????????? 這里,我們看到前面表為B,found_record是1,所以A.DepartmentID只需要對(duì)應(yīng)一條記錄就可以了
????????? 因?yàn)榫唧w取值不知道,也沒有直方圖,所以只能簡單依據(jù)索引統(tǒng)計(jì)信息來計(jì)算：
??????????? 索引IND_DID的列A.DepartmentID的Cardinality為1349,全表記錄數(shù)為1349
??????????? 所以,每一個(gè)值對(duì)應(yīng)一條記錄,而前面表B只有一條記錄,所以這里的found_record計(jì)算為1*1 = 1
??????????? 所以IO成本為：1,總成本為1.2
??? (***) IND_L_D成本為25.2；IND_DID成本為1.2,所以選擇后者為當(dāng)前表的拜訪方式
? (**) 確定A使用索引IND_DID,拜訪方式為ref
? (**) JOIN順序B|A,總成本為：1.2+1.2 = 2.4MYSQL入門

(*) 選擇第一個(gè)JOIN的表為A
? (**) 確定A表的拜訪方式
?????? 因?yàn)锳表是第一個(gè)表,所以無法使用索引`IND_DID`(B.DepartmentID = A.DepartmentID)
?????? 那么只能使用索引`IND_L_D`(A.LastName = 'zhou')
???????? 使用IND_L_D索引的成本計(jì)算,總成本為25.2；參考前面計(jì)算；
? (**) 這里拜訪A表的成本已經(jīng)是25.2,比之前的最優(yōu)成本2.4要大,忽略該順序
?????? 所以,這次窮舉搜索到此結(jié)束MYSQL入門

?? 把上面的過程簡化如下：MYSQL入門

(*) 選擇第一個(gè)JOIN的表為B
? (**) 確定B表的拜訪方式
? (**) 從剩余的表中窮舉選出第二個(gè)JOIN的表,這里剩余的表為：A
? (**) 將A表加入JOIN,并確定其拜訪方式
??? (***) IND_L_D A.LastName = 'zhou'
??? (***) IND_DID B.DepartmentID = A.DepartmentID
??? (***) IND_L_D成本為25.2；IND_DID成本為1.2,所以選擇后者為當(dāng)前表的拜訪方式
? (**) 確定A使用索引IND_DID,拜訪方式為ref
? (**) JOIN順序B|A,總成本為：1.2+1.2 = 2.4MYSQL入門

(*) 選擇第一個(gè)JOIN的表為A
? (**) 確定A表的拜訪方式
? (**) 這里拜訪A表的成本已經(jīng)是25.2,比之前的最優(yōu)成本2.4要大,忽略該順序MYSQL入門

?? 至此,MySQL優(yōu)化器就確定了所有表的最佳JOIN順序和拜訪方式.
3. 測(cè)試環(huán)境MYSQL入門

MySQL: 5.1.48-debug-log innodb plugin 1.0.9
CREATE TABLE `department` (
 `DepartmentID` int(11) DEFAULT NULL,
 `DepartmentName` varchar(20) DEFAULT NULL,
 KEY `IND_D` (`DepartmentID`),
 KEY `IND_DN` (`DepartmentName`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;
CREATE TABLE `employee` (
 `LastName` varchar(20) DEFAULT NULL,
 `DepartmentID` int(11) DEFAULT NULL,
 KEY `IND_L_D` (`LastName`),
 KEY `IND_DID` (`DepartmentID`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;
for i in `seq 1 1000` ; do mysql -vvv -uroot test -e 'insert into department values (600000*rand(),repeat(char(65+rand()*58),rand()*20))'; done
for i in `seq 1 1000` ; do mysql -vvv -uroot test -e 'insert into employee values (repeat(char(65+rand()*58),rand()*20),600000*rand())'; done
for i in `seq 1 50` ; do mysql -vvv -uroot test -e 'insert into employee values ("zhou",27760)'; done
for i in `seq 1 200` ; do mysql -vvv -uroot test -e 'insert into employee values (repeat(char(65+rand()*58),rand()*20),27760)'; done
for i in `seq 1 1` ; do mysql -vvv -uroot test -e 'insert into department values (27760,"TBX")'; done
show index from employee;
+----------+------------+----------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
| Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+----------+------------+----------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
| employee |     1 | IND_L_D |      1 | LastName   | A     |    1349 |   NULL | NULL  | YES | BTREE   |     |
| employee |     1 | IND_DID |      1 | DepartmentID | A     |    1349 |   NULL | NULL  | YES | BTREE   |     |
+----------+------------+----------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
show index from department;
+------------+------------+----------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+------------+------------+----------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+
| department |     1 | IND_D  |      1 | DepartmentID  | A     |    1001 |   NULL | NULL  | YES | BTREE   |     |
| department |     1 | IND_DN  |      1 | DepartmentName | A     |    1001 |   NULL | NULL  | YES | BTREE   |     |
+------------+------------+----------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+

4. 構(gòu)造一個(gè)Bad caseMYSQL入門

?? 因?yàn)殛P(guān)聯(lián)條件中MySQL使用索引統(tǒng)計(jì)信息做本錢預(yù)估,所以數(shù)據(jù)分布不均勻的時(shí)候,就容易做出錯(cuò)誤的判斷.簡單的我們構(gòu)造下面的案例：MYSQL入門

?? 表和索引結(jié)構(gòu)不變,依照下面的方式構(gòu)造數(shù)據(jù)：MYSQL入門

for i in `seq 1 10000` ; do mysql -uroot test -e 'insert into department values (600000*rand(),repeat(char(65+rand()*58),rand()*20))'; done
for i in `seq 1 10000` ; do mysql -uroot test -e 'insert into employee values (repeat(char(65+rand()*58),rand()*20),600000*rand())'; done
for i in `seq 1 1` ; do mysql -uroot test -e 'insert into employee values ("zhou",27760)'; done
for i in `seq 1 10` ; do mysql -uroot test -e 'insert into department values (27760,"TBX")'; done
for i in `seq 1 1000` ; do mysql -uroot test -e 'insert into department values (27760,repeat(char(65+rand()*58),rand()*20))';
done
explain
select *
from
 employee as A,department as B
where
   A.LastName = 'zhou'
 and B.DepartmentID = A.DepartmentID
 and B.DepartmentName = 'TBX';
+----+-------------+-------+------+-----------------+---------+---------+---------------------+------+-------------+
| id | select_type | table | type | possible_keys  | key   | key_len | ref         | rows | Extra    |
+----+-------------+-------+------+-----------------+---------+---------+---------------------+------+-------------+
| 1 | SIMPLE   | A   | ref | IND_L_D,IND_DID | IND_L_D | 43   | const        |  1 | Using where |
| 1 | SIMPLE   | B   | ref | IND_D,IND_DN  | IND_D  | 5    | test.A.DepartmentID |  1 | Using where |
+----+-------------+-------+------+-----------------+---------+---------+---------------------+------+-------------+

?? 可以看到這里,MySQL執(zhí)行方案對(duì)表department使用了索引IND_D,那么A表命中一條記錄為(zhou,27760)；根據(jù)B.DepartmentID=27760將返回1010條記錄,然后根據(jù)條件DepartmentName = 'TBX'進(jìn)行過濾.MYSQL入門

?? 這里可以看到如果B表選擇索引IND_DN,效果要更好,因?yàn)镈epartmentName = 'TBX'僅僅返回10條記錄,再依據(jù)條件A.DepartmentID=B.DepartmentID過濾之.
MYSQL入門

歡迎參與《Mysql實(shí)例探究MySQL優(yōu)化器對(duì)索引和JOIN順序的選擇》討論，分享您的想法，維易PHP學(xué)院為您提供專業(yè)教程。

轉(zhuǎn)載請(qǐng)注明本頁網(wǎng)址：
http://www.fzlkiss.com/jiaocheng/13104.html


    標(biāo)簽：