源碼下載網(wǎng)淺析MySQL 查詢優(yōu)化器
2024-07-24 12:40:36
供稿：網(wǎng)友
優(yōu)化器（The Optimizer） 這篇描述MySQL查詢優(yōu)化器的工作原理。MySQL查詢優(yōu)化器主要為執(zhí)行的查詢決斷最有效的路線（routine，走向）。 一。源代碼和概念 這部分討論優(yōu)化器關(guān)鍵概念，術(shù)語，及在MySQL源代碼怎么對應(yīng)的。 1.定義 狹義定義：優(yōu)化器，就是DBMS為查詢時決斷要往哪種執(zhí)行路徑的一系列路線。 MySQL是經(jīng)常調(diào)整查詢的路線，所以你得把這篇描述的邏輯和在源代碼里的做比較。為了使比較容易些，這里會包含相關(guān)文件和路徑的注解，例如源代碼/sql/sql_select.cc，optimize_cond()函數(shù)。 當(dāng)一個查詢被轉(zhuǎn)成另一種查詢時，其結(jié)果是一樣的，就會發(fā)生語句轉(zhuǎn)化。如下這個查詢 SELECT ... WHERE 5 = a 就會被轉(zhuǎn)化成為 SELECT ... WHERE a = 5 最明顯的語句轉(zhuǎn)化是少的，有些語句轉(zhuǎn)化，是為了更快的執(zhí)行。 2.優(yōu)化器源代碼 如下偽代碼顯示了/sql/sql_select.cc中handle_select()函數(shù)的邏輯結(jié)構(gòu)。（源代碼/sql/sql_select.cc處理SQL查詢） handle_select() mysql_select() JOIN::prepare() setup_fields() JOIN::optimize() /* optimizer is from here ... */ optimize_cond() opt_sum_query() make_join_statistics() get_quick_record_count() choose_plan() /* Find the best way to access tables */ /* as specified by the user. */ optimize_straight_join() best_access_path() /* Find a (sub-)optimal plan among all or subset */ /* of all possible query plans where the user */ /* controls the exhaustiveness of the search. */ greedy_search() best_extension_by_limited_search() best_access_path() /* Perform an exhaustive search for an optimal plan */ find_best() make_join_select() /* ... to here */ JOIN::exec() 縮進(jìn)行顯示了哪個函數(shù)調(diào)用哪個函數(shù)，如handle_select()函數(shù)調(diào)用mysql_select()函數(shù)，mysql_select()函數(shù)會調(diào)用JOIN::prepare()、JOIN::optimize()、JOIN::exec()，以及類推。mysql_select()函數(shù)的第一部分是調(diào)用JOIN::prepare()，此函數(shù)用來上下文分析、元數(shù)據(jù)建立和一些語句轉(zhuǎn)化。查詢優(yōu)化器函數(shù)JOIN::optimize()和其所有優(yōu)化處理中的子路線。當(dāng)執(zhí)行完JOIN::optimize()函數(shù)后，JOIN::exec()接管并完成JOIN::optimize()函數(shù)優(yōu)化決斷后的執(zhí)行工作。 雖然有JOIN字出現(xiàn)，其實(shí)查詢優(yōu)化器的工作會處理所有的查詢類型，不單單JOIN聯(lián)接查詢。 二。首要優(yōu)化 這部分討論服務(wù)器執(zhí)行的最重要優(yōu)化。 1.優(yōu)化常數(shù)關(guān)系 常數(shù)等值傳遞 如下的表達(dá)式會發(fā)生語句轉(zhuǎn)化： WHERE column1 = column2 AND column2 = 'x' 這種表達(dá)式，眾所周知，如果A=B && B=C => A=C（可等值傳遞），上句表達(dá)式轉(zhuǎn)化后變成： WHERE column1='x' AND column2='x' 當(dāng)且僅當(dāng)，操作符為如下的任何一個，在column1 <操作符> column2條件中就會發(fā)生語句轉(zhuǎn)化： =, <, >, <=, >=, <>, <=>, LIKE 注意：等值傳遞的轉(zhuǎn)化，不適合于BETWEEN。可能也不適合于LIKE，這是后話。 常數(shù)等值傳遞同樣發(fā)生在循環(huán)中，前一步傳遞的輸出作為后一步傳遞的輸入。 源代碼見/sql/sql_select.cc，change_cond_ref_to_const()函數(shù)?；?sql/sql_select.cc，propagate_cond_constants()函數(shù)。 剔除死代碼 總是TRUE的條件會發(fā)生語句轉(zhuǎn)化，如： WHERE 0=0 AND column1='y' 這種情況下，第一個條件會被剔除，最后為： column1='y' 源代碼見/sql/sql_select.cc，remove_eq_conds()。 總是FLASE的條件也會發(fā)生語句轉(zhuǎn)化，如： WHERE (0 = AND s1 = OR s1 = 7 小括號和前兩個條件總是FLASE，最后為： WHERE s1 = 7 還有一些情況下，當(dāng)WHERE語句中代表不可能的條件，查詢優(yōu)化器可能會全部剔除語句，如下： WHERE (0 = AND s1 = 5) 因?yàn)檫@條件永遠(yuǎn)不為TRUE，在EXPLAIN分析中會顯示Impossible WHERE。簡單地說，MySQL會說WHERE條件被優(yōu)化過。 如果一個字段不能為NULL，優(yōu)化器會剔除所有不相關(guān)的IS NULL的條件，這樣 WHERE not_null_column IS NULL 這種條件總為FLASE情況；且 WHERE not_null_column IS NOT NULL 這種條件總為TRUE情況，所以這種字段查詢的條件也被剔除。這種判斷是很微妙的。舉個例：在一個OUT JOIN外聯(lián)接，被定義成NOT NULL字段仍然含有NULL值，優(yōu)化器就會單獨(dú)排除IS NULL條件在這種特殊情況中。 優(yōu)化器不會檢查所有的Impossible WHERE的條件，因?yàn)檫@方面可能性太多了。例如： CREATE TABLE Table1 (column1 CHAR(1)); ... SELECT * FROM Table1 WHERE column1 = 'Popgo'; 優(yōu)化器不會剔除這種查詢的條件，即使在CREATE TABLE定義中使之成為不可能的條件。 可合并的常數(shù)值 如下表達(dá)式會發(fā)生語句轉(zhuǎn)化： WHERE column1 = 1 + 2 最后為： WHERE column1 = 3 在之前說的常數(shù)等值傳遞 ，優(yōu)化器很容易將這種查詢語句合并在一起。這操作就簡化了結(jié)果。 常數(shù)值和常數(shù)表 MySQL常數(shù)值，有時不單單指在查詢的SQL語句的字面意思上，也可在常數(shù)表（constant tables）的內(nèi)容里。常數(shù)表（constant tables）被定義為： 1。無記錄或一條記錄的表 2。表的表達(dá)式被WHERE條件約束，而且包含的表達(dá)式形式column = "constant"，或者表的主鍵的所有字段，或者任何唯一鍵的所有字段（唯一鍵的字段定義為NOT NULL） 例如，Table0表的定義包含： ... PRIMARY KEY (column1,column2) 然后，查詢表達(dá)式： FROM Table0 ... WHERE column1=5 AND column2=7 ... 會返回常數(shù)表（constant tables）。更多簡單地，如果Table1表的定義包含： ... unique_not_null_column INT NOT NULL UNIQUE 然后，查詢表達(dá)式： FROM Table1 ... WHERE unique_not_null_column=5 也會返回常數(shù)表（constant tables）。 這個規(guī)則指一個常數(shù)表（constant tables）至多有一條記錄值。MySQL就會優(yōu)先評估是否為常數(shù)表（constant tables），并找出那個值。這樣，MySQL會將這值插入查詢語句。如這個例子： SELECT Table1.unique_not_null_column, Table2.any_column FROM Table1, Table2 WHERE Table1.unique_not_null_column = Table2.any_column AND Table1.unique_not_null_column = 5; MySQL評估這語句時，首先就會發(fā)現(xiàn)，按照常數(shù)表（constant tables）的第二點(diǎn)定義，查詢條件為unique_not_null_column的表Table1是一個常數(shù)表（constant tables），它就會取得這個值。 如果取值失敗，也就是在表Table1里unique_not_null_column = 沒值，EXPLAIN后結(jié)果： Impossible WHERE noticed after reading const tables 相反，如果取值成功，也就是在表Table1里unique_not_null_column = 為一條記錄值，MySQL會轉(zhuǎn)化為如下語句： SELECT 5, Table2.any_column FROM Table1, Table2 WHERE 5 = Table2.any_column AND 5 = 5; 事實(shí)上，這是一個很好的例子。優(yōu)化器因前面提到的常數(shù)等值傳遞進(jìn)行一些語句轉(zhuǎn)化。另外，為什么要先描述常數(shù)等值傳遞，因?yàn)樗贛ySQL確認(rèn)什么是常數(shù)表（constant tables）前就先進(jìn)行了。優(yōu)化器步驟的順序，有時是有差別。 雖然很多查詢都沒常數(shù)表（constant tables）參考。應(yīng)該牢記，以后無論什么時候，常數(shù)constant字被提及，它是指任何一個字面上的值或者一個常數(shù)表（constant tables）的內(nèi)容。
