MySQL之InnoDB存儲(chǔ)引擎：淺談查詢(xún)優(yōu)化
眾所周知，MySQL中存在所謂的查詢(xún)優(yōu)化器。顧名思義，其可對(duì)用戶(hù)提交的SQL查詢(xún)語(yǔ)句進(jìn)行優(yōu)化，以便可以通過(guò)某種更高效的方式來(lái)執(zhí)行
化簡(jiǎn)語(yǔ)句
查詢(xún)優(yōu)化器最常見(jiàn)的優(yōu)化措施，即是對(duì)我們SQL語(yǔ)句進(jìn)行化簡(jiǎn)
移除多余括號(hào)
通常我們?yōu)榱吮苊庥洃浉鞣N操作符的優(yōu)先級(jí)，會(huì)在SQL語(yǔ)句中頻繁使用括號(hào)來(lái)避免出錯(cuò)。當(dāng)然，也不用擔(dān)心大量使用括號(hào)會(huì)不會(huì)造成什么性能上的缺陷，因?yàn)椴樵?xún)優(yōu)化器會(huì)移除掉SQL語(yǔ)句中多余的括號(hào)
例如，下面的SQL表達(dá)式中使用大量的括號(hào)
( (b<c AND a=22) OR ( (b=18) AND (c<22) ) )
查詢(xún)優(yōu)化器移除多余括號(hào)，即變?yōu)?/p>
(b<c AND a=22) OR (b=18 AND c<22)
Constant Propagation 常量傳播
字段a與某個(gè)常量進(jìn)行等值查詢(xún)，與此同時(shí)，其還通過(guò)AND連接了另外一個(gè)表達(dá)式。如果該表達(dá)式中含有a字段，則查詢(xún)優(yōu)化器可將該表達(dá)式中的a字段直接替換為等值查詢(xún)中的常量值。即所謂的Constant Propagation常量傳播
例如下面的SQL表達(dá)式
a=5 AND c<a
則優(yōu)化的結(jié)果為
a=5 AND c<5
Equality Propagation 等同性傳播
若存在多個(gè)字段間的等值查詢(xún)且使用AND進(jìn)行連接，則可使用Equality Propagation等同性傳播進(jìn)行簡(jiǎn)化
如下面的SQL語(yǔ)句
a=b AND d=b AND d=996
則優(yōu)化的結(jié)果為
a=996 AND b=996 AND d=996
移除無(wú)用條件
如果某表達(dá)式結(jié)果恒為T(mén)RUE或FALSE，則查詢(xún)優(yōu)化器會(huì)將其移除進(jìn)行簡(jiǎn)化
如下面的SQL語(yǔ)句
(a=123 OR c!=c) OR (69=69 AND d>996)
則優(yōu)化的結(jié)果為
a=123 OR d>996
計(jì)算表達(dá)式
通常如果表達(dá)式中只包含常量，查詢(xún)優(yōu)化器會(huì)提前進(jìn)行計(jì)算其結(jié)果
如下面的SQL語(yǔ)句
d = 34+35
則優(yōu)化的結(jié)果為
d = 69
值得一提的是，如果字段不是以單獨(dú)的形式出現(xiàn)的，而是以函數(shù)等形式出現(xiàn)時(shí)，MySQL則不會(huì)對(duì)其進(jìn)行優(yōu)化。示例表達(dá)式如下所示
-- example 1
ABS(a) > 996
-- example 2
-a < -996
合并having、where子句
當(dāng)SQL查詢(xún)語(yǔ)句中沒(méi)有 聚集函數(shù)(例如SUM、MAX等)、GROUP BY子句時(shí)，查詢(xún)優(yōu)化器會(huì)對(duì)having、where子句進(jìn)行合并
常量表檢測(cè)
MySQL中以下兩種場(chǎng)景的查詢(xún)會(huì)非常快：
所查數(shù)據(jù)表中最多只有一條數(shù)據(jù)記錄 對(duì) 主鍵 或 唯一二級(jí)索引 進(jìn)行等值查詢(xún)，當(dāng)然對(duì)于后者(唯一二級(jí)索引)需為非NULL值的等值查詢(xún)
由于InnoDB存儲(chǔ)引擎下關(guān)于表中記錄數(shù)的統(tǒng)計(jì)是不準(zhǔn)確的，故第1點(diǎn)不適用InnoDB存儲(chǔ)引擎。第2點(diǎn)是因?yàn)樵摬樵?xún)最多只會(huì)查到1條記錄。由于在上述兩種場(chǎng)景下，查詢(xún)速度是非常快的，故將它們查詢(xún)時(shí)所用的表稱(chēng)之為constant tables常量表。故查詢(xún)優(yōu)化器在分析一個(gè)SQL查詢(xún)語(yǔ)句時(shí)，會(huì)首先去執(zhí)行constant tables常量表的查詢(xún)，然后用該查詢(xún)結(jié)果來(lái)替換該SQL查詢(xún)語(yǔ)句中的條件，最后再分析其余表的查詢(xún)成本
這里假設(shè)有兩張表t1、t2，它們的主鍵字段均為id。其中，t1表中主鍵id字段為234的記錄的age字段為24。則對(duì)于下面SQL查詢(xún)語(yǔ)句而言
SELECT * FROM t1 INNER JOIN t2
ON t1.age = t2.age
WHERE t1.id = 234;
t1表即是常量表。故在分析t2表的各種查詢(xún)實(shí)現(xiàn)方式的成本前，會(huì)先執(zhí)行對(duì)t1表的查詢(xún)，然后利用該查詢(xún)結(jié)果來(lái)替換相關(guān)條件，則上述SQL語(yǔ)句優(yōu)化結(jié)果如下
SELECT (從常量表t1中獲取到的記錄的各字段的常量值), t2.*
FROM t1 INNER JOIN t2
ON t2.age = 24
外連接消除
對(duì)于內(nèi)連接而言，驅(qū)動(dòng)表與被驅(qū)動(dòng)表的角色不是固定的，故MySQL可通過(guò)調(diào)整、優(yōu)化連接順序來(lái)選擇查詢(xún)成本最低的方案來(lái)執(zhí)行；而對(duì)于外連接而言，其驅(qū)動(dòng)表與被驅(qū)動(dòng)表的角色卻是固定的。故對(duì)于一個(gè)外連接而言，如果能夠?qū)⑵滢D(zhuǎn)換為內(nèi)連接，則即可進(jìn)一步通過(guò)調(diào)整、優(yōu)化連接順序來(lái)降低查詢(xún)成本。具體地，在外連接查詢(xún)的where子句中如果存在對(duì)被驅(qū)動(dòng)表字段值不為NULL的查詢(xún)條件，即所謂的reject-null空值拒絕，則外連接即可相互轉(zhuǎn)換為內(nèi)連接。關(guān)于這一點(diǎn)其實(shí)也很好理解，因?yàn)樵谕膺B接查詢(xún)中，如果驅(qū)動(dòng)表的記錄在被驅(qū)動(dòng)表中找不到相應(yīng)匹配的記錄，則依然需要將其放到查詢(xún)結(jié)果中，只不過(guò)對(duì)于被驅(qū)動(dòng)表的字段用NULL值填充即可。故一旦外連接查詢(xún)的被驅(qū)動(dòng)表where子句滿(mǎn)足reject-null空值拒絕，外連接即可被消除、優(yōu)化為內(nèi)連接查詢(xún)
例如對(duì)于下面的左外連接查詢(xún)而言
select * from stu_info left join stu_score
on stu_info.id = stu_score.id
where score > 20;
由于被驅(qū)動(dòng)表stu_score滿(mǎn)足reject-null空值拒絕條件，則該外連接查詢(xún)可被優(yōu)化為內(nèi)連接查詢(xún)
select * from stu_info inner join stu_score
on stu_info.id = stu_score.id
where score > 20;
這里，我們亦可通過(guò)查看該外連接查詢(xún)的執(zhí)行計(jì)劃來(lái)進(jìn)行驗(yàn)證
explain select * from stu_info left join stu_score
on stu_info.id = stu_score.id
where score > 20;
從執(zhí)行計(jì)劃的結(jié)果中，我們可以看出其是將stu_score作為驅(qū)動(dòng)表、stu_info作為被驅(qū)動(dòng)表。換言之，查詢(xún)優(yōu)化器先消除外連接查詢(xún)并將其轉(zhuǎn)換為內(nèi)連接查詢(xún)，然后再通過(guò)調(diào)整連接順序來(lái)實(shí)現(xiàn)最低成本的查詢(xún)
參考文獻(xiàn)
MySQL是怎樣運(yùn)行的