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