7. 優化 · mysql5.1參考手冊

### 第7章：優化優化是一個復雜的任務，因為最終要求了解整個待優化的系統。盡管可以進行局部優化而不需要了解系統或應用程序，為了優化得更好，你必須知道更多的信息。本章解釋并給出不同的優化MySQL的方法示例。但要記住總有一些其它方法使系統更快，盡管需要更多的工作。 ### 7.1.?優化概述 [ 7.1.1. MySQL設計局限與折衷](#)[ 7.1.2. 為可移植性設計應用程序](#)[ 7.1.3. 我們已將MySQL用在何處？](#)[ 7.1.4. MySQL基準套件](#)[ 7.1.5. 使用自己的基準](#) 使一個系統更快的最重要因素當然是基本設計。此外，還需要知道系統正做什么樣的事情，以及瓶頸是什么。最常見的系統瓶頸是： - 磁盤搜索。需要花時間從磁盤上找到一個數據，用在現代磁盤的平均時間通常小于10ms，因此理論上我們能夠每秒大約搜索1000次。這個時間在新磁盤上提高不大并且很難為一個表進行優化。優化它的方法是將數據分布在多個磁盤上。 - 磁盤讀/寫。當磁盤放入正確位置后，我們需要從中讀取數據。對于現代的磁盤，一個磁盤至少傳輸10-20Mb/s的吞吐。這比搜索要容易優化，因為你能從多個磁盤并行地讀。 - CPU周期。我們將數據讀入內存后，需要對它進行處理以獲得我們需要的結果。表相對于內存較小是最常見的限制因素。但是對于小表，速度通常不成問題。 ????????? 內存帶寬。當CPU需要的數據超出CPU緩存時，主緩存帶寬就成為內存的一個瓶頸。這在大多數系統正是一個不常見的瓶頸但是你應該知道它。 ### 7.1.1.?MySQL設計局限與折衷當使用MyISAM存儲引擎時，MySQL使用極快速的表鎖定，以便允許多次讀或一次寫。使用該存儲引擎的最大問題出現在同一個表中進行混合穩定數據流更新與慢速選擇。如果這只是某些表的問題，你可以使用另一個存儲引擎。參見[第15章：](#)[_存儲引擎和表類型_](# "Chapter?15.?Storage Engines and Table Types")。 MySQL可以使用事務表和非事務表。為了更容易地讓非事務表順利工作(如果出現問題不能回滾)，MySQL采用下述規則。請注意這些規則_只適用于_不運行在嚴格模式下或為INSERT或UPDATE使用IGNORE規定程序時。 ????????? 所有列有默認值。請注意當運行在嚴格SQL模式(包括TRADITIONAL SQL模式)時，必須為NOT NULL列指定默認值。 ????????? 如果向列內插入不合適的或超出范圍的值，MySQL將該列設定為“最好的可能的值”，而不是報告錯誤。對于數字值，為0、可能的最小值或最大值。對于字符串，為空字符串或列內可以保存的字符串。請注意當運行在嚴格模式或TRADITIONAL SQL模式時該行為不適用。 ????????? 所有表達式的計算結果返回一個表示錯誤狀況的信號。例如，1/0返回NULL。(使用ERROR_FOR_DIVISION_BY_ZERO SQL模式可以更改該行為)。如果正使用非事務表，不應該使用MySQL來檢查列的內容。一般情況，最安全的(通常是最快的)方法徑是讓應用程序確保只向數據庫傳遞合法值。相關詳細信息參見[1.8.6節，“MySQL處理約束的方式”](# "1.8.6.?How MySQL Deals with Constraints")和[13.2.4節，“INSERT語法”](# "13.2.4.?INSERT Syntax")或[5.3.2節，“SQL服務器模式”](# "5.3.2.?The Server SQL Mode")。 ### 7.1.2.?為可移植性設計應用程序因為不同SQL服務器實現了標準SQL的不同部分，需要花功夫來編寫可移植的SQL應用程序。對很簡單的選擇/插入，很容易實現移植，但是需要的功能越多則越困難。如果想要應用程序對很多數據庫系統都快，它變得更難！為了使一個復雜應用程序可移植，你需要選擇它應該工作的SQL服務器，并確定這些服務器支持什么特性。所有數據庫都有一些弱點。這就是它們不同的設計折衷導致的不同行為。可以使用**MySQL**的**crash-me**程序來找出能用于數據庫服務器選擇的函數、類型和限制。**crash-me**并不能找出所有的特性，但是其廣度仍然很合理，可以進行大約450個測試。 **crash-me**可以提供的一種類型的信息的例子：如果想要使用Informix或DB2，不應該使用超過18個字符的列名。 ** crash-me**程序和**MySQL**基準程序是獨立于數據庫的。通過觀察它們是如何編寫的，編可以知道必須為編寫獨立于數據庫的應用程序做什么。基準本身可在**MySQL**源碼分發的“sql-bench”目錄下找到。它們用DBI數據庫接口以Perl寫成。使用DBI本身即可以解決部分移植性問題，因為它提供與數據庫無關的的存取方法。關于**crash-me**結果，訪問[http://dev.mysql.com/tech-resources/crash-me.php](http://dev.mysql.com/tech-resources/crash-me.php)。到[http://dev.mysql.com/tech-resources/benchmarks/](http://dev.mysql.com/tech-resources/benchmarks/)看這個基準的結果。如果你為數據庫的獨立性而努力，需要很好地了解每個SQL服務器的瓶頸。例如，**MySQL**在檢索和更新MyISAM表記錄方面很快，但是在同一個表上混合慢速讀者和寫者方面有一個問題。另一方面，當你試圖訪問最近更新了(直到它們被刷新到磁盤上)的行時，在Oracle中有一個很大的問題。事務數據庫總的來說在從記錄文件表中生成總結表方面不是很好，因為在這種情況下，行鎖定幾乎沒有用。為了使應用程序“確實”獨立于數據庫，需要定義一個容易擴展的接口，用它可操縱數據。因為C++在大多數系統上可以適用，使用數據庫的一個C++ 類接口是有意義的。如果你使用某個數據庫特定的功能(例如**MySQL專用的**REPLACE語句)，應該為SQL服務器編碼一個方法以實現同樣的功能。盡管慢些，但確允許其它服務器執行同樣的任務。用**MySQL**，可以使用/*! */語法把**MySQL**特定的關鍵詞加到查詢中。在/**/中的代碼將被其它大多數SQL服務器視為注釋(并被忽略)。如果高性能真的比準確性更重要，就像在一些web應用程序那樣，一種可行的方法是創建一個應用層，緩存所有的結果以便得到更高的性能。通過只是讓舊的結果在短時間后‘過期’，能保持緩存合理地刷新。這在極高負載的情況下是相當不錯的，在此情況下，能動態地增加緩存并且設定較高的過期時限直到一切恢復正常。在這種情況下，表創建信息應該包含緩存初始大小和表刷新頻率等信息。實施應用程序緩存的一種方法是使用MySQL查詢緩存。啟用查詢緩存后，服務器可以確定是否可以重新使用查詢結果。這樣簡化了你的應用程序。參見[5.13節，“MySQL查詢高速緩沖”](# "5.13.?The MySQL Query Cache")。 ### 7.1.3.?我們已將MySQL用在何處？該節描述了Mysql的早期應用程序。在MySQL最初開發期間，MySQL的功能適合大多數客戶。MySQL為瑞典的一些最大的零售商處理數據倉庫。我們從所有商店得到所有紅利卡交易的每周總結，并且我們期望為所有店主提供有用的信息以幫助他們得出他們的廣告戰如何影響他們的顧客。數據是相當巨量的(大約每月7百萬宗交易總結)并且我們保存4-10年來的數據需要呈現給用戶。我們每周從顧客那里得到請求，他們想要“立刻”訪問來自該數據的新報告。我們通過每月將所有信息存儲在壓縮的“交易”表中來解決它。我們有一套簡單的宏/腳本用來生成來自交易表的不同條件( 產品組、顧客id，商店...)的總結表。報告是由一個進行語法分析網頁的小perl腳本動態生成的網頁，在腳本中執行SQL語句并且插入結果。我們很想使用PHP或**mod_perl**，但是那時它們還不可用。對圖形數據，我們用C語言編寫了一個簡單的工具，它能基于那些結果處理SQL查詢結果并生成GIF圖形。該工具也從分析Web網頁的perl腳本中動態地執行。在大多數情況下，一個新的報告通過簡單地復制一個現有腳本并且修改其中的SQL查詢來完成。在一些情況下，我們將需要把更多的列加到一個現有的總結表中或產生一個新的，但是這也相當簡單，因為我們在磁盤上保存所有交易表。（目前我們大約有50G的交易表和200G的其它顧客數據)。我們也讓我們的顧客直接用ODBC訪問總結表以便高級用戶能自己用這些數據進行試驗。該系統工作得很好，我們可以毫無問題地用很適度的Sun Ultra SPARC工作站硬件(2x200MHz)來處理數據。該系統被逐步移植到了Linux中。 ### 7.1.4.?MySQL基準套件本節應該包含**MySQL**基準套件(和**crash-me**)的技術描述，但是該描述還沒寫成。目前，你可以通過在MySQL源碼分發中的“sql-bench”目錄下的代碼和結果了解基準套件是如何工作的。通過基準用戶可以了解一個給定的SQL實現在哪方面執行得很好或很糟糕。注意，這個基準是單線程的，它可以測量操作執行的最小時間。我們計劃將來在基準套件中添加多線程測試。要使用基準套件，必須滿足下面的要求： ????????? 基準套件隨MySQL源碼分發提供。可以從[http://dev.mysql.com/downloads/](http://dev.mysql.com/downloads/)下載分發，或者使用當前的開發源碼樹(參見[2.8.3節，“從開發源碼樹安裝”](# "2.8.3.?Installing from the Development Source Tree"))。 ????????? 基準腳本用Perl編寫而成，使用Perl DBI模塊訪問數據庫服務器，因此必須安裝DBI。還需要為每個待測試的服務器提供服務器專用DBD驅動程序。例如，要測試MySQL、PostgreSQL和DB2，必須安裝DBD::mysql、DBD::Pg和DBD::DB2模塊。參見[2.13節，“Perl安裝注意事項”](# "2.13.?Perl Installation Notes")。獲得MySQL源碼分發后，可以在sql-bench目錄找到基準套件。要運行基準測試，應構建MySQL，然后進入sql-bench目錄并執行run-all-tests腳本： shell> **cd sql-bench** shell> **perl run-all-tests --server=_server_name_** _server_name_是一個支持的服務器。要獲得所有選項和支持的服務器，調用命令： shell> perl run-all-tests --help **crash-me**腳本也位于sql-bench目錄。**crash-me**嘗試通過實際運行查詢確定數據庫支持的特性以及其功能和限制。例如，它確定： ????????? 支持什么列類型 ????????? 支持多少索引 ????????? 支持什么函數 ????????? 查詢可以多大 ????????? VARCHAR列可以多大可以從[http://dev.mysql.com/tech-resources/crash-me.php](http://dev.mysql.com/tech-resources/crash-me.php)發現許多不同數據庫服務器的**crash-me**的結果。關于基準測試結果的詳細信息，訪問[http://dev.mysql.com/tech-resources/benchmarks/](http://dev.mysql.com/tech-resources/benchmarks/)。 ### 7.1.5.?使用自己的基準一定要測試應用程序和數據庫，以發現瓶頸在哪兒。通過修正它(或通過用一個“啞模塊”代替瓶頸)，可以很容易地確定下一個瓶頸。即使你的應用程序的整體性能目前可以接受，至少應該對每個瓶頸做一個計劃，如果某天確實需要更好的性能，應知道如何解決它。關于一些可移植的基準程序的例子，參見MySQL基準套件。請參見[7.1.4節，“MySQL基準套件”](# "7.1.4.?The MySQL Benchmark Suite")。可以利用這個套件的任何程序并且根據你的需要修改它。通過這樣做，可以嘗試不同的問題的解決方案并測試哪一個是最好的解決方案。另一個免費基準套件是開放源碼數據庫基準套件，參見[http://osdb.sourceforge.net/](http://osdb.sourceforge.net/)。在系統負載繁重時出現一些問題是很普遍的，并且很多客戶已經與我們聯系了，他們在生產系統中有一個(測試)系統并且有負載問題。大多數情況下，性能問題經證明是與基本數據庫設計有關的問題(例如，表掃描在高負載時表現不好)或操作系統或庫問題。如果系統已經不在生產系統中，它們大多數將**很**容易修正。為了避免這樣的問題，應該把工作重點放在在可能最壞的負載下測試你的整個應用程序。你可以使用Super Smack。該工具可以從[http://jeremy.zawodny.com/mysql/super-smack/](http://jeremy.zawodny.com/mysql/super-smack/)獲得。正如它的名字所建議，它可以根據你的需要提供合理的系統，因此確保只用于你的開發系統。 ### 7.2.?優化SELECT語句和其它查詢 [7.2.1. EXPLAIN語法（獲取SELECT相關信息）``](#)[ 7.2.2. 估計查詢性能](#)[ 7.2.3. SELECT查詢的速度](#)[ 7.2.4. MySQL怎樣優化WHERE子句``](#)[ 7.2.5. 范圍優化](#)[ 7.2.6. 索引合并優化](#)[ 7.2.7. MySQL如何優化IS NULL``](#)[ 7.2.8. MySQL如何優化DISTINCT``](#)[ 7.2.9. MySQL如何優化LEFT JOIN和RIGHT JOIN](#)[ 7.2.10. MySQL如何優化嵌套Join](#)[ 7.2.11. MySQL如何簡化外部聯合](#)[ 7.2.12. MySQL如何優化ORDER BY``](#)[ 7.2.13. MySQL如何優化GROUP BY``](#)[ 7.2.14. MySQL如何優化LIMIT``](#)[ 7.2.15. 如何避免表掃描](#)[ 7.2.16. INSERT語句的速度](#)[ 7.2.17. UPDATE語句的速度](#)[ 7.2.18. DELETE語句的速度](#)[ 7.2.19. 其它優化技巧](#) 首先，影響所有語句的一個因素是：你的許可設置得越復雜，所需要的開銷越多。執行GRANT語句時使用簡單的許可，當客戶執行語句時，可以使MySQL降低許可檢查開銷。例如，如果未授予任何表級或列級權限，服務器不需要檢查tables_priv和columns_priv表的內容。同樣地，如果不對任何賬戶進行限制，服務器不需要對資源進行統計。如果查詢量很高，可以花一些時間使用簡化的授權結構來降低許可檢查開銷。如果你的問題是與具體MySQL表達式或函數有關，可以使用**mysql**客戶程序所帶的BENCHMARK()函數執行定時測試。其語法為BENCHMARK(_loop_count_,_expression_)。例如： mysql> SELECT BENCHMARK(1000000,1+1)； +------------------------+ | BENCHMARK(1000000,1+1) | +------------------------+ |????????????????????? 0 | +------------------------+ 1 row in set (0.32 sec) 上面結果在PentiumII 400MHz系統上獲得。它顯示**MySQL**在該系統上在0.32秒內可以執行1,000,000個簡單的+表達式運算。所有**MySQL**函數應該被高度優化，但是總有可能有一些例外。BENCHMARK()是一個找出是否查詢有問題的優秀的工具。 ### 7.2.1.?EXPLAIN語法（獲取SELECT相關信息）`` EXPLAIN tbl_name 或： EXPLAIN [EXTENDED] SELECT select_options EXPLAIN語句可以用作DESCRIBE的一個同義詞，或獲得關于MySQL如何執行SELECT語句的信息： ????????? EXPLAIN _tbl_name_是DESCRIBE _tbl_name_或SHOW COLUMNS FROM _tbl_name_的一個同義詞。 ????????? 如果在SELECT語句前放上關鍵詞EXPLAIN，**MySQL**將解釋它如何處理SELECT，提供有關表如何聯接和聯接的次序。該節解釋EXPLAIN的第2個用法。借助于EXPLAIN，可以知道什么時候必須為表加入索引以得到一個使用索引來尋找記錄的更快的SELECT。如果由于使用不正確的索引出現了問題，應運行ANALYZE TABLE更新表的統計（例如關鍵字集的勢），這樣會影響優化器進行的選擇。參見[13.5.2.1節，“ANALYZE TABLE語法”](# "13.5.2.1.?ANALYZE TABLE Syntax")。還可以知道優化器是否以一個最佳次序聯接表。為了強制優化器讓一個SELECT語句按照表命名順序的聯接次序，語句應以STRAIGHT_JOIN而不只是SELECT開頭。 EXPLAIN為用于SELECT語句中的每個表返回一行信息。表以它們在處理查詢過程中將被MySQL讀入的順序被列出。**MySQL**用一遍掃描多次聯接（_single-sweep multi-join_）的方式解決所有聯接。這意味著**MySQL**從第一個表中讀一行，然后找到在第二個表中的一個匹配行，然后在第3個表中等等。當所有的表處理完后，它輸出選中的列并且返回表清單直到找到一個有更多的匹配行的表。從該表讀入下一行并繼續處理下一個表。當使用EXTENDED關鍵字時，EXPLAIN產生附加信息，可以用SHOW WARNINGS瀏覽。該信息顯示優化器限定SELECT語句中的表和列名，重寫并且執行優化規則后SELECT語句是什么樣子，并且還可能包括優化過程的其它注解。 EXPLAIN的每個輸出行提供一個表的相關信息，并且每個行包括下面的列： ????????? id SELECT識別符。這是SELECT的查詢序列號。 ????????? select_type SELECT類型，可以為以下任何一種： o??????? SIMPLE 簡單SELECT(不使用UNION或子查詢) o??????? PRIMARY 最外面的SELECT o??????? UNION UNION中的第二個或后面的SELECT語句 o??????? DEPENDENT UNION UNION中的第二個或后面的SELECT語句，取決于外面的查詢 o??????? UNION RESULT UNION的結果。 o??????? SUBQUERY 子查詢中的第一個SELECT o??????? DEPENDENT SUBQUERY 子查詢中的第一個SELECT，取決于外面的查詢 o??????? DERIVED 導出表的SELECT(FROM子句的子查詢) ????????? table 輸出的行所引用的表。 ????????? type 聯接類型。下面給出各種聯接類型，按照從最佳類型到最壞類型進行排序： o??????? system 表僅有一行(=系統表)。這是const聯接類型的一個特例。 o??????? const 表最多有一個匹配行，它將在查詢開始時被讀取。因為僅有一行，在這行的列值可被優化器剩余部分認為是常數。const表很快，因為它們只讀取一次！ const用于用常數值比較PRIMARY KEY或UNIQUE索引的所有部分時。在下面的查詢中，_tbl_name_可以用于const表： SELECT * from tbl_name WHERE primary_key=1； ? SELECT * from tbl_name WHERE primary_key_part1=1和 primary_key_part2=2； o??????? eq_ref 對于每個來自于前面的表的行組合，從該表中讀取一行。這可能是最好的聯接類型，除了const類型。它用在一個索引的所有部分被聯接使用并且索引是UNIQUE或PRIMARY KEY。 eq_ref可以用于使用=操作符比較的帶索引的列。比較值可以為常量或一個使用在該表前面所讀取的表的列的表達式。在下面的例子中，MySQL可以使用eq_ref聯接來處理_ref_tables_： SELECT * FROM ref_table,other_table ? WHERE ref_table.key_column=other_table.column; ? SELECT * FROM ref_table,other_table ? WHERE ref_table.key_column_part1=other_table.column ??? AND ref_table.key_column_part2=1; o??????? ref 對于每個來自于前面的表的行組合，所有有匹配索引值的行將從這張表中讀取。如果聯接只使用鍵的最左邊的前綴，或如果鍵不是UNIQUE或PRIMARY KEY（換句話說，如果聯接不能基于關鍵字選擇單個行的話），則使用ref。如果使用的鍵僅僅匹配少量行，該聯接類型是不錯的。 ref可以用于使用=或<=>操作符的帶索引的列。在下面的例子中，MySQL可以使用ref聯接來處理_ref_tables_： SELECT * FROM ref_table WHERE key_column=expr; ? SELECT * FROM ref_table,other_table ? WHERE ref_table.key_column=other_table.column; ? SELECT * FROM ref_table,other_table ? WHERE ref_table.key_column_part1=other_table.column ??? AND ref_table.key_column_part2=1; o??????? ref_or_null 該聯接類型如同ref，但是添加了MySQL可以專門搜索包含NULL值的行。在解決子查詢中經常使用該聯接類型的優化。在下面的例子中，MySQL可以使用ref_or_null聯接來處理_ref_tables_： SELECT * FROM ref_table WHERE key_column=expr OR key_column IS NULL; 參見[7.2.7節，“MySQL如何優化IS NULL``”](# "7.2.7.?How MySQL Optimizes IS NULL")。 o??????? index_merge 該聯接類型表示使用了索引合并優化方法。在這種情況下，key列包含了使用的索引的清單，key_len包含了使用的索引的最長的關鍵元素。詳細信息參見[7.2.6節，“索引合并優化”](# "7.2.6.?Index Merge Optimization")。 o??????? unique_subquery 該類型替換了下面形式的IN子查詢的ref： value IN (SELECT primary_key FROM single_table WHERE some_expr) unique_subquery是一個索引查找函數，可以完全替換子查詢，效率更高。 o??????? index_subquery 該聯接類型類似于unique_subquery。可以替換IN子查詢，但只適合下列形式的子查詢中的非唯一索引： value IN (SELECT key_column FROM single_table WHERE some_expr) o??????? range 只檢索給定范圍的行，使用一個索引來選擇行。key列顯示使用了哪個索引。key_len包含所使用索引的最長關鍵元素。在該類型中ref列為NULL。當使用=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN或者IN操作符，用常量比較關鍵字列時，可以使用range： SELECT * FROM tbl_name WHERE key_column = 10; ? SELECT * FROM tbl_name WHERE key_column BETWEEN 10 and 20; ? SELECT * FROM tbl_name WHERE key_column IN (10,20,30); ? SELECT * FROM tbl_name WHERE key_part1= 10 AND key_part2 IN (10,20,30); o??????? index 該聯接類型與ALL相同，除了只有索引樹被掃描。這通常比ALL快，因為索引文件通常比數據文件小。當查詢只使用作為單索引一部分的列時，MySQL可以使用該聯接類型。 o??????? ALL 對于每個來自于先前的表的行組合，進行完整的表掃描。如果表是第一個沒標記const的表，這通常不好，并且通常在它情況下**_很_**差。通常可以增加更多的索引而不要使用ALL，使得行能基于前面的表中的常數值或列值被檢索出。 ????????? possible_keys possible_keys列指出**MySQL**能使用哪個索引在該表中找到行。注意，該列完全獨立于EXPLAIN輸出所示的表的次序。這意味著在possible_keys中的某些鍵實際上不能按生成的表次序使用。如果該列是NULL，則沒有相關的索引。在這種情況下，可以通過檢查WHERE子句看是否它引用某些列或適合索引的列來提高你的查詢性能。如果是這樣，創造一個適當的索引并且再次用EXPLAIN檢查查詢。參見[13.1.2節，“ALTER TABLE語法”](# "13.1.2.?ALTER TABLE Syntax")。為了看清一張表有什么索引，使用SHOW INDEX FROM _tbl_name_。 ????????? key key列顯示**MySQL**實際決定使用的鍵（索引）。如果沒有選擇索引，鍵是NULL。要想強制MySQL使用或忽視possible_keys列中的索引，在查詢中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。參見[13.2.7節，“SELECT語法”](# "13.2.7.?SELECT Syntax")。對于MyISAM和BDB表，運行ANALYZE TABLE可以幫助優化器選擇更好的索引。對于MyISAM表，可以使用**myisamchk --analyze**。參見[13.5.2.1節，“ANALYZE TABLE語法”](# "13.5.2.1.?ANALYZE TABLE Syntax")和[5.9.4節，“表維護和崩潰恢復”](# "5.9.4.?Table Maintenance and Crash Recovery")。 ????????? key_len key_len列顯示**MySQL**決定使用的鍵長度。如果鍵是NULL，則長度為NULL。注意通過key_len值我們可以確定MySQL將實際使用一個多部關鍵字的幾個部分。 ????????? ref ref列顯示使用哪個列或常數與key一起從表中選擇行。 ????????? rows rows列顯示**MySQL**認為它執行查詢時必須檢查的行數。 ????????? Extra 該列包含MySQL解決查詢的詳細信息。下面解釋了該列可以顯示的不同的文本字符串： o??????? Distinct MySQL發現第1個匹配行后，停止為當前的行組合搜索更多的行。 o??????? Not exists MySQL能夠對查詢進行LEFT JOIN優化，發現1個匹配LEFT JOIN標準的行后，不再為前面的的行組合在該表內檢查更多的行。下面是一個可以這樣優化的查詢類型的例子： SELECT * 從t1 LEFT JOIN t2 ON t1.id=t2.id ? WHERE t2.id IS NULL；假定t2.id定義為NOT NULL。在這種情況下，MySQL使用t1.id的值掃描t1并查找t2中的行。如果MySQL在t2中發現一個匹配的行，它知道t2.id絕不會為NULL，并且不再掃描t2內有相同的id值的行。換句話說，對于t1的每個行，MySQL只需要在t2中查找一次，無論t2內實際有多少匹配的行。 o??????? range checked for each record (index map: #) MySQL沒有發現好的可以使用的索引，但發現如果來自前面的表的列值已知，可能部分索引可以使用。對前面的表的每個行組合，MySQL檢查是否可以使用range或index_merge訪問方法來索取行。關于適用性標準的描述參見[7.2.5節，“范圍優化”](# "7.2.5.?Range Optimization")和[7.2.6節，“索引合并優化”](# "7.2.6.?Index Merge Optimization")，不同的是前面表的所有列值已知并且認為是常量。這并不很快，但比執行沒有索引的聯接要快得多。 o??????? Using filesort MySQL需要額外的一次傳遞，以找出如何按排序順序檢索行。通過根據聯接類型瀏覽所有行并為所有匹配WHERE子句的行保存排序關鍵字和行的指針來完成排序。然后關鍵字被排序，并按排序順序檢索行。參見[7.2.12節，“MySQL如何優化ORDER BY``”](# "7.2.12.?How MySQL Optimizes ORDER BY")。 o??????? Using index 從只使用索引樹中的信息而不需要進一步搜索讀取實際的行來檢索表中的列信息。當查詢只使用作為單一索引一部分的列時，可以使用該策略。 o??????? Using temporary 為了解決查詢，MySQL需要創建一個臨時表來容納結果。典型情況如查詢包含可以按不同情況列出列的GROUP BY和ORDER BY子句時。 o??????? Using where WHERE子句用于限制哪一個行匹配下一個表或發送到客戶。除非你專門從表中索取或檢查所有行，如果Extra值不為Using where并且表聯接類型為ALL或index，查詢可能會有一些錯誤。如果想要使查詢盡可能快，應找出Using filesort 和Using temporary的Extra值。 o??????? Using sort_union(...), Using union(...), Using intersect(...) 這些函數說明如何為index_merge聯接類型合并索引掃描。詳細信息參見[7.2.6節，“索引合并優化”](# "7.2.6.?Index Merge Optimization")。 o??????? Using index for group-by 類似于訪問表的Using index方式，Using index for group-by表示MySQL發現了一個索引，可以用來查詢GROUP BY或DISTINCT查詢的所有列，而不要額外搜索硬盤訪問實際的表。并且，按最有效的方式使用索引，以便對于每個組，只讀取少量索引條目。詳情參見[7.2.13節，“MySQL如何優化GROUP BY``”](# "7.2.13.?How MySQL Optimizes GROUP BY")。通過相乘EXPLAIN輸出的rows列的所有值，你能得到一個關于一個聯接如何的提示。這應該粗略地告訴你**MySQL**必須檢查多少行以執行查詢。當你使用max_join_size變量限制查詢時，也用這個乘積來確定執行哪個多表SELECT語句。參見[7.5.2節，“調節服務器參數”](# "7.5.2.?Tuning Server Parameters")。下列例子顯示出一個多表JOIN如何能使用EXPLAIN提供的信息逐步被優化。假定你有下面所示的SELECT語句，計劃使用EXPLAIN來檢查它： EXPLAIN SELECT tt.TicketNumber, tt.TimeIn, ?????????????? tt.ProjectReference, tt.EstimatedShipDate, ?????????????? tt.ActualShipDate, tt.ClientID, ?????????????? tt.ServiceCodes, tt.RepetitiveID, ?????????????? tt.CurrentProcess, tt.CurrentDPPerson, ?????????????? tt.RecordVolume, tt.DPPrinted, et.COUNTRY, ?????????????? et_1.COUNTRY, do.CUSTNAME ??????? FROM tt, et, et AS et_1, do ??????? WHERE tt.SubmitTime IS NULL ????????? AND tt.ActualPC = et.EMPLOYID ????????? AND tt.AssignedPC = et_1.EMPLOYID ????????? AND tt.ClientID = do.CUSTNMBR; 對于這個例子，假定： ????????? 被比較的列聲明如下： <table border="1" cellpadding="0" id="table1"><tr><td> 表</td> <td width="113"> 列</td> <td width="105"> 列類型</td> </tr><tr><td> tt</td> <td width="113"> ActualPC</td> <td width="105"> CHAR(10)</td> </tr><tr><td> tt</td> <td width="113"> AssignedPC</td> <td width="105"> CHAR(10)</td> </tr><tr><td> tt</td> <td width="113"> ClientID</td> <td width="105"> CHAR(10)</td> </tr><tr><td> et</td> <td width="113"> EMPLOYID</td> <td width="105"> CHAR(15)</td> </tr><tr><td> do</td> <td width="113"> CUSTNMBR</td> <td width="105"> CHAR(15)</td> </tr></table> ????????? 表有下面的索引： <table border="1" cellpadding="0" id="table2"><tr><td> 表</td> <td> 索引</td> </tr><tr><td> tt</td> <td> ActualPC</td> </tr><tr><td> tt</td> <td> AssignedPC</td> </tr><tr><td> tt</td> <td> ClientID</td> </tr><tr><td> et</td> <td> EMPLOYID(主鍵)</td> </tr><tr><td> do</td> <td> CUSTNMBR(主鍵)</td> </tr></table> ????????? tt.ActualPC值不是均勻分布的。開始，在進行優化前，EXPLAIN語句產生下列信息： ? table type possible_keys key? key_len ref? rows? Extra et??? ALL? PRIMARY?????? NULL NULL??? NULL 74 do??? ALL? PRIMARY?????? NULL NULL??? NULL 2135 et_1? ALL? PRIMARY?????? NULL NULL??? NULL 74 tt??? ALL? AssignedPC,?? NULL NULL??? NULL 3872 ?????????? ClientID, ?????????? ActualPC ????? range checked for each record (key map: 35) ? 因為type對每張表是ALL，這個輸出顯示**MySQL**正在對所有表產生一個笛卡爾乘積；即每一個行的組合！這將花相當長的時間，因為必須檢查每張表的行數的乘積！對于一個實例，這是74 * 2135 * 74 * 3872 = 45,268,558,720行。如果表更大，你只能想象它將花多長時間…… 這里的一個問題是**MySQL**能更高效地在聲明具有相同類型和尺寸的列上使用索引。在本文中，VARCHAR和CHAR是相同的，除非它們聲明為不同的長度。因為tt.ActualPC被聲明為CHAR(10)并且et.EMPLOYID被聲明為CHAR(15)，長度不匹配。為了修正在列長度上的不同，使用ALTER TABLE將ActualPC的長度從10個字符變為15個字符： mysql> ALTER TABLE tt MODIFY ActualPC VARCHAR(15); 現在tt.ActualPC和et.EMPLOYID都是VARCHAR(15)，再執行EXPLAIN語句產生這個結果： ? table type?? possible_keys key???? key_len ref???????? rows??? Extra tt??? ALL ???AssignedPC,?? NULL??? NULL??? NULL??????? 3872??? Using ???????????? ClientID,???????????????????????????????????????? where ???????????? ActualPC do??? ALL??? PRIMARY?????? NULL??? NULL??? NULL??????? 2135 ????? range checked for each record (key map: 1) et_1? ALL??? PRIMARY?????? NULL??? NULL??? NULL??????? 74 ????? range checked for each record (key map: 1) et??? eq_ref PRIMARY?????? PRIMARY 15????? tt.ActualPC 1 ? 這不是完美的，但是好一些了：rows值的乘積少了一個因子74。這個版本在幾秒內執行完。第2種方法能消除tt.AssignedPC = et_1.EMPLOYID和tt.ClientID = do.CUSTNMBR比較的列的長度失配問題： mysql> ALTER TABLE tt MODIFY AssignedPC VARCHAR(15), ??? ->??????????????? MODIFY ClientID?? VARCHAR(15); EXPLAIN產生的輸出顯示在下面： table type?? possible_keys key????? key_len ref?????????? rows Extra et??? ALL??? PRIMARY?????? NULL???? NULL??? NULL????????? 74 tt??? ref??? AssignedPC,?? ActualPC 15????? et.EMPLOYID?? 52?? Using ???????????? ClientID,???????????????????????????????????????? where ???????????? ActualPC et_1? eq_ref PRIMARY?????? PRIMARY? 15????? tt.AssignedPC 1 do??? eq_ref PRIMARY?????? PRIMARY? 15????? tt.ClientID?? 1 ? 這幾乎很好了。剩下的問題是，默認情況，**MySQL**假設在tt.ActualPC列的值是均勻分布的，并且對tt表不是這樣。幸好，很容易告訴**MySQL**來分析關鍵字分布： mysql> ANALYZE TABLE tt；現在聯接是“完美”的了，而且EXPLAIN產生這個結果： table type?? possible_keys key???? key_len ref?????????? rows Extra tt??? ALL??? AssignedPC??? NULL??? NULL??? NULL????????? 3872 Using ???????????? ClientID,??????????????????????????????????????? where ???????????? ActualPC et??? eq_ref PRIMARY?????? PRIMARY 15????? tt.ActualPC?? 1 et_1? eq_ref PRIMARY?????? PRIMARY 15????? tt.AssignedPC 1 do??? eq_ref PRIMARY?????? PRIMARY 15????? tt.ClientID?? 1 注意在從EXPLAIN輸出的rows列是一個來自**MySQL**聯接優化器的“教育猜測”。你應該檢查數字是否接近事實。如果不是，可以通過在SELECT語句里面使用STRAIGHT_JOIN并且試著在FROM子句以不同的次序列出表，可能得到更好的性能。 ### 7.2.2.?估計查詢性能在大多數情況下，可以通過計算磁盤搜索來估計性能。對小的表，通常能在1次磁盤搜索中找到行(因為索引可能被緩存)。對更大的表，可以使用B－樹索引進行估計，將需要log(_row_count_)/log(_index_block_length_/3 * 2/(_index_length_ + _data_pointer_length_))+1次搜索才能找到行。在**MySQL**中，索引塊通常是1024個字節，數據指針通常是4個字節，這對于有一個長度為3(中等整數)的索引的500,000行的表，通過公式可以計算出log(500,000)/log(1024/3*2/(3+4))+1= 4次搜索。上面的索引需要大約500,000 * 7 * 3/2 = 5.2MB，(假設典型情況下索引緩存區填充率為2/3)，可以將大部分索引保存在內存中，僅需要1-2調用從OS讀數據來找出行。然而對于寫，將需要4次搜索請求(如上)來找到在哪兒存放新索引，并且通常需要2次搜索來更新這個索引并且寫入行。注意，上述討論并不意味著應用程序的性能將緩慢地以log_N_ 退化！當表格變得更大時，所有內容緩存到OS或SQL服務器后，將僅僅或多或少地更慢。在數據變得太大不能緩存后，將逐漸變得更慢，直到應用程序只能進行磁盤搜索(以log_N_增加)。為了避免這個問題，隨數據增加而增加鍵高速緩沖區大小。對于MyISAM表, 由key_buffer_size系統變量控制鍵高速緩沖區大小。參見[7.5.2節，“調節服務器參數”](# "7.5.2.?Tuning Server Parameters")。 ### 7.2.3.?SELECT查詢的速度總的來說，要想使一個較慢速SELECT ... WHERE更快，應首先檢查是否能增加一個索引。不同表之間的引用通常通過索引來完成。你可以使用EXPLAIN語句來確定SELECT語句使用哪些索引。參見[7.4.5節，“MySQL如何使用索引”](# "7.4.5.?How MySQL Uses Indexes")和[7.2.1節，“EXPLAIN語法（獲取關于SELECT的信息）``”](# "7.2.1.?EXPLAIN Syntax (Get Information About a SELECT)")。下面是一些加速對MyISAM表的查詢的一般建議： ????????? 為了幫助**MySQL**更好地優化查詢，在一個裝載數據后的表上運行ANALYZE TABLE或**myisamchk --analyze**。這樣為每一個索引更新指出有相同值的行的平均行數的值（當然，如果只有一個索引，這總是1。）MySQL使用該方法來決定當你聯接兩個基于非常量表達式的表時選擇哪個索引。你可以使用SHOW INDEX FROM _tbl_name_并檢查Cardinality值來檢查表分析結果。**myisamchk --description --verbose**可以顯示索引分布信息。 ????????? 要想根據一個索引排序一個索引和數據，使用**myisamchk --sort-index --sort-records=1**（如果你想要在索引1上排序）。如果只有一個索引，想要根據該索引的次序讀取所有的記錄，這是使查詢更快的一個好方法。但是請注意，第一次對一個大表按照這種方法排序時將花很長時間！ ### 7.2.4.?MySQL怎樣優化WHERE子句`` 該節討論為處理WHERE子句而進行的優化。例子中使用了SELECT語句，但相同的優化也適用DELETE和UPDATE語句中的WHERE子句。請注意對MySQL優化器的工作在不斷進行中，因此該節并不完善。MySQL執行了大量的優化，本文中所列的并不詳盡。下面列出了MySQL執行的部分優化： ????????? 去除不必要的括號： ???????????????? ?????? ?((a AND b) AND c OR (((a AND b) AND (c AND d)))) ???????????????? -> (a AND b AND c) OR (a AND b AND c AND d) ????????? 常量重疊： ???????????????? ???(a b>5 AND b=c AND a=5 ????????? 去除常量條件(由于常量重疊需要)： ???????????????? ???(B>=5 AND B=5) OR (B=6 AND 5=5) OR (B=7 AND 5=6) ???????????????? -> B=5 OR B=6 ????????? 索引使用的常數表達式僅計算一次。 - 對于MyISAM和HEAP表，在一個單個表上的沒有一個WHERE的COUNT(*)直接從表中檢索信息。當僅使用一個表時，對NOT NULL表達式也這樣做。 - 無效常數表達式的早期檢測。**MySQL**快速檢測某些SELECT語句是不可能的并且不返回行。 - 如果不使用GROUP BY或分組函數(COUNT()、MIN()……)，HAVING與WHERE合并。 - 對于聯接內的每個表，構造一個更簡單的WHERE以便更快地對表進行WHERE計算并且也盡快跳過記錄。 - 所有常數的表在查詢中比其它表先讀出。常數表為： - 空表或只有1行的表。 - 與在一個PRIMARY KEY或UNIQUE索引的WHERE子句一起使用的表，這里所有的索引部分使用常數表達式并且索引部分被定義為NOT NULL。下列的所有表用作常數表： mysql> SELECT * FROM t WHERE primary_key=1; mysql> SELECT * FROM t1,t2 ?????????? WHERE t1.primary_key=1 AND t2.primary_key=t1.id; - 嘗試所有可能性便可以找到表聯接的最好聯接組合。如果所有在ORDER BY和GROUP BY的列來自同一個表，那么當聯接時，該表首先被選中。 - 如果有一個ORDER BY子句和不同的GROUP BY子句，或如果ORDER BY或GROUP BY包含聯接隊列中的第一個表之外的其它表的列，則創建一個臨時表。 - 如果使用SQL_SMALL_RESULT，**MySQL**使用內存中的一個臨時表。 - 每個表的索引被查詢，并且使用最好的索引，除非優化器認為使用表掃描更有效。是否使用掃描取決于是否最好的索引跨越超過30%的表。優化器更加復雜，其估計基于其它因素，例如表大小、行數和I/O塊大小，因此固定比例不再決定選擇使用索引還是掃描。 - 在一些情況下，**MySQL**能從索引中讀出行，甚至不查詢數據文件。如果索引使用的所有列是數值類，那么只使用索引樹來進行查詢。 - 輸出每個記錄前，跳過不匹配HAVING子句的行。下面是一些快速查詢的例子： SELECT COUNT(*) FROM tbl_name; ? SELECT MIN(key_part1),MAX(key_part1) FROM tbl_name; ? SELECT MAX(key_part2) FROM tbl_name ??? WHERE key_part1=constant; ? SELECT ... FROM tbl_name ??? ORDER BY key_part1,key_part2,... LIMIT 10; ? SELECT ... FROM tbl_name ??? ORDER BY key_part1 DESC, key_part2 DESC, ... LIMIT 10; 下列查詢僅使用索引樹就可以解決(假設索引的列為數值型)： SELECT key_part1,key_part2 FROM tbl_name WHERE key_part1=val; ? SELECT COUNT(*) FROM tbl_name ??? WHERE key_part1=val1 AND key_part2=val2; ? SELECT key_part2 FROM tbl_name GROUP BY key_part1; 下列查詢使用索引按排序順序檢索行，不用另外的排序： SELECT ... FROM tbl_name ??? ORDER BY key_part1,key_part2,... ; ? SELECT ... FROM tbl_name ??? ORDER BY key_part1 DESC, key_part2 DESC, ... ; ### 7.2.5.?范圍優化 [ 7.2.5.1. 單元素索引的范圍訪問方法](#)[ 7.2.5.2. 多元素索引的范圍訪問方法](#) range訪問方法使用單一索引來搜索包含在一個或幾個索引值距離內的表記錄的子集。可以用于單部分或多元素索引。后面的章節將詳細描述如何從WHERE子句提取區間。 #### 7.2.5.1.?單元素索引的范圍訪問方法對于單元素索引，可以用WHERE子句中的相應條件很方便地表示索引值區間，因此我們稱為_范圍條件_而不是“區間”。單元素索引范圍條件的定義如下： ????????? 對于BTREE和HASH索引，當使用=、<=>、IN、IS NULL或者IS NOT NULL操作符時，關鍵元素與常量值的比較關系對應一個范圍條件。 ????????? 對于BTREE索引，當使用>、<、>=、<=、BETWEEN、!=或者<>，或者LIKE '_pattern_'(其中 '_pattern_'不以通配符開始)操作符時，關鍵元素與常量值的比較關系對應一個范圍條件。 ????????? 對于所有類型的索引，多個范圍條件結合OR或AND則產生一個范圍條件。前面描述的“常量值”系指： ????????? 查詢字符串中的常量 ????????? 同一聯接中的const或system表中的列 ????????? 無關聯子查詢的結果 ????????? 完全從前面類型的子表達式組成的表達式下面是一些WHERE子句中有范圍條件的查詢的例子： SELECT * FROM t1 ????WHERE key_col > 1 ????AND key_col < 10; ? SELECT * FROM t1 ????WHERE key_col = 1 ????OR key_col IN (15,18,20); ? SELECT * FROM t1 ????WHERE key_col LIKE 'ab%' ????OR key_col BETWEEN 'bar' AND 'foo'; ? 請注意在常量傳播階段部分非常量值可以轉換為常數。 MySQL嘗試為每個可能的索引從WHERE子句提取范圍條件。在提取過程中，不能用于構成范圍條件的條件被放棄，產生重疊范圍的條件組合到一起，并且產生空范圍的條件被刪除。例如，考慮下面的語句，其中key1是有索引的列，nonkey沒有索引： SELECT * FROM t1 WHERE ?? (key1 < 'abc' AND (key1 LIKE 'abcde%' OR key1 LIKE '%b')) OR ?? (key1 < 'bar' AND nonkey = 4) OR ?? (key1 < 'uux' AND key1 > 'z'); key1的提取過程如下： 1.??? 用原始WHERE子句開始： 2.??? (key1 < 'abc' AND (key1 LIKE 'abcde%' OR key1 LIKE '%b')) OR 3.??? ?(key1 < 'bar' AND nonkey = 4) OR 4.??? ?(key1 < 'uux' AND key1 > 'z') 5.??? 刪除nonkey = 4和key1 LIKE '%b'，因為它們不能用于范圍掃描。刪除它們的正確途徑是用TRUE替換它們，以便進行范圍掃描時不會丟失匹配的記錄。用TRUE替換它們后，可以得到： 6.??????????? (key1 < 'abc' AND (key1 LIKE 'abcde%' OR TRUE)) OR 7.??????????? (key1 < 'bar' AND TRUE) OR 8.??????????? (key1 < 'uux' AND key1 > 'z') 9.??? 取消總是為true或false的條件： ????????? (key1 LIKE 'abcde%' OR TRUE)總是true ????????? (key1 < 'uux' AND key1 > 'z')總是false 用常量替換這些條件，我們得到： (key1 < 'abc' AND TRUE) OR (key1 < 'bar' AND TRUE) OR (FALSE) 刪除不必要的TRUE和FALSE常量，我們得到 (key1 < 'abc') OR (key1 < 'bar') 10.將重疊區間組合成一個產生用于范圍掃描的最終條件： 11.??????? (key1 < 'bar') 總的來說(如前面的例子所述)，用于范圍掃描的條件比WHERE子句限制少。MySQL再執行檢查以過濾掉滿足范圍條件但不完全滿足WHERE子句的行。范圍條件提取算法可以處理嵌套的任意深度的AND/OR結構，并且其輸出不依賴條件在WHERE子句中出現的順序。 #### 7.2.5.2.?多元素索引的范圍訪問方法多元素索引的范圍條件是單元素索引的范圍條件的擴展。多元素索引的范圍條件將索引記錄限制到一個或幾個關鍵元組內。使用索引的順序，通過一系列關鍵元組來定義關鍵元組區間。例如，考慮定義為key1(_key_part1_, _key_part2_, _key_part3_)的多元素索引，以及下面的按關鍵字順序所列的關鍵元組： key_part1? key_part2? key_part3 ? NULL?????? 1????????? 'abc' ? NULL?????? 1???? ?????'xyz' ? NULL?????? 2????????? 'foo' ?? 1???????? 1????????? 'abc' ?? 1???????? 1????????? 'xyz' ?? 1???????? 2????????? 'abc' ?? 2???????? 1????????? 'aaa' ? 條件_key_part1_ = 1定義了下面的范圍： (1，-inf，-inf) <= (key_part1，key_part2，key_part3) < (1，+inf，+inf) 范圍包括前面數據集中的第4、5和6個元組，可以用于范圍訪問方法。通過對比，條件_key_part3_ = 'abc'不定義單一的區間，不能用于范圍訪問方法。下面更加詳細地描述了范圍條件如何用于多元素索引中。 ????????? 對于HASH索引，可以使用包含相同值的每個區間。這說明區間只能由下面形式的條件產生： ???????????????? ?????key_part1 cmp const1 ???????????????? ?AND key_part2 cmp const2 ???????????????? ?AND ... ???????????????? AND key_partN cmp constN; 這里，_const1_，_const2_，...為常量，_cmp_是=、<=>或者IS NULL比較操作符之一，條件包括所有索引部分。(也就是說，有_N_個條件，每一個對應_N_-元素索引的每個部分）。關于常量的定義，參見[7.2.5.1節，“單元素索引的范圍訪問方法”](# "7.2.5.1.?Range Access Method for Single-Part Indexes")。例如，下面為三元素HASH索引的范圍條件： key_part1 = 1 AND key_part2 IS NULL AND key_part3 = 'foo' ????????? 對于BTREE索引，區間可以對結合AND的條件有用，其中每個條件用一個常量值通過=、<=>、IS NULL、>、<、>=、<=、!=、<>、BETWEEN或者LIKE '_pattern_' (其中'_pattern_'不以通配符開頭)比較一個關鍵元素。區間可以足夠長以確定一個包含所有匹配條件(或如果使用<>或!=，為兩個區間)的記錄的單一的關鍵元組。例如，對于條件： ???????????????? ? key_part1 = 'foo' AND key_part2 >= 10 AND key_part3 > 10 單一區間為： ('foo'，10，10) ?? < (key_part1，key_part2，key_part3) ????? < ('foo'，+inf，+inf) 創建的區間可以比原條件包含更多的記錄。例如，前面的區間包括值('foo'，11，0)，不滿足原條件。 ????????? 如果包含區間內的一系列記錄的條件結合使用OR，則形成包括一系列包含在區間并集的記錄的一個條件。如果條件結合使用了AND，則形成包括一系列包含在區間交集內的記錄的一個條件。例如，對于兩部分索引的條件： ???????????????? (key_part1 = 1 AND key_part2 < 2) ???????????????? OR (key_part1 > 5) 區間為： (1, -inf) < (_key_part1_, _key_part2_) < (1, 2) (5, -inf) < (_key_part1_, _key_part2_) 在該例子中，第1行的區間左側的約束使用了一個關鍵元素，右側約束使用了兩個關鍵元素。第2行的區間只使用了一個關鍵元素。EXPLAIN輸出的key_len列表示所使用關鍵字前綴的最大長度。在某些情況中，key_len可以表示使用的關鍵元素，但可能不是你所期望的。假定_key_part1_和_key_part2_可以為NULL。則key_len列顯示下面條件的兩個關鍵元素的長度： key_part1 >= 1 AND key_part2 < 2 但實際上，該條件可以變換為： key_part1 >= 1 AND key_part2 IS NOT NULL [ 7.2.5.1節，“單元素索引的范圍訪問方法”](# "7.2.5.1.?Range Access Method for Single-Part Indexes")描述了如何進行優化以結合或刪除單元素索引范圍條件的區間。多元素索引范圍條件的區間的步驟類似。 ### 7.2.6.?索引合并優化 [ 7.2.6.1. 索引合并交集訪問算法](#)[ 7.2.6.2. 索引合并并集訪問算法](#)[ 7.2.6.3. 索引合并排序并集訪問算法](#) _索引合并_方法用于通過range掃描搜索行并將結果合成一個。合并會產生并集、交集或者正在進行的掃描的交集的并集。在EXPLAIN輸出中，該方法表現為type列內的index_merge。在這種情況下，key列包含一列使用的索引，key_len包含這些索引的最長的關鍵元素。例如： SELECT * FROM _tbl_name_ WHERE _key_part1_ = 10 OR _key_part2_ = 20; ? SELECT * FROM _tbl_name_ ??? WHERE (_key_part1_ = 10 OR _key_part2_ = 20) AND _non_key_part_=30; ? SELECT * FROM t1, t2 ? ??WHERE (t1._key1 IN (1,2) OR t1.key2_ LIKE '_value_%') ??? AND t2._key1_=t1._some_col_; ? SELECT * FROM t1, t2 ??? WHERE t1._key1_=1 ??? AND (t2._key1_=t1._some_col_ OR t2._key2_=t1._some_col2_); ? 索引合并方法有幾種訪問算法 (參見EXPLAIN輸出的Extra字段)： ????????? 交集 ????????? 聯合 ????????? 排序并集后面幾節更加詳細地描述了這些方法。 **注釋：**索引合并優化算法具有以下幾個已知缺陷： ????????? 如果可以對某些關鍵字進行范圍掃描，則不考慮索引合并。例如，下面的查詢： ???????????????? SELECT * FROM t1 WHERE (goodkey1 < 10 OR goodkey2 < 20) AND badkey < 30; 對于該查詢，可以有兩個方案： 1.??? 使用(goodkey1 < 10 OR goodkey2 < 20)條件進行索引合并掃描。 2.??? 使用badkey < 30條件進行范圍掃描。然而，優化器只考慮第2個方案。如果這不是你想要的，你可以通過使用IGNORE INDEX或FORCE INDEX讓優化器考慮index_merge。下面的查詢使用索引合并執行： SELECT * FROM t1 FORCE INDEX(goodkey1,goodkey2) WHERE (goodkey1 < 10 OR goodkey2 < 20) AND badkey < 30; ? SELECT * FROM t1 IGNORE INDEX(badkey) WHERE (goodkey1 < 10 OR goodkey2 < 20) AND badkey < 30; ????????? 如果查詢有一個復雜的WHERE子句，有較深的AND/OR嵌套關系，MySQL不選擇該優選方案，通過下面的識別法則嘗試分布各條件： ???????????????? (x AND y) OR z = (x OR z) AND (y OR z) ???????????????? (x OR y) AND z = (x AND z) OR (y AND z) index_merge訪問方法的不同變量之間的選擇和其它訪問方法基于各適用選項的成本估計。 #### 7.2.6.1.?索引合并交集訪問算法該訪問算法可以用于當WHERE子句結合AND被轉換為不同的關鍵字的幾個范圍條件，每個條件為下面之一： ????????? 以這種形式，即索引有確切的_N_部分(即包括了所有索引部分)： ???????????????? key_part1=const1 AND key_part2=const2 ... AND key_partN=constN ????????? 任何InnoDB或BDB表的主鍵的范圍條件。下面是一些例子： SELECT * FROM _ innodb_table_ WHERE _primary_key_ < 10 AND _key_col1_=20; ? SELECT * FROM _tbl_name_ WHERE (_key1_part1_=1 AND _key1_part2_=2) AND _key2_=2; 索引合并交集算法同時對所有使用的索引進行掃描，并產生從合并的索引掃描接收的行序列的交集。如果使用的索引包括查詢中使用的所有列，所有表記錄均不搜索，并且在這種情況下EXPLAIN的輸出包含Extra字段中的Using index。下面是一個此類查詢的例子： SELECT COUNT(*) FROM t1 WHERE key1=1 AND key2=1; 如果使用的索引未包括查詢中使用的所有列，只有滿足所有使用的關鍵字的范圍條件才搜索所有記錄。如果某個合并條件是InnoDB或BDB表的主鍵的一個條件，不用于記錄查詢，但用于過濾使用其它條件搜索的記錄。 #### 7.2.6.2.?索引合并并集訪問算法該算法的適用標準類似于索引合并方法交集算法的標準。算法可以用于當WHERE子句結合OR被轉換為不同的關鍵字的幾個范圍條件的時候，每個條件為下面之一： ????????? 以這種形式，即索引有確切的_N_部分(即包括了所有索引部分)： ???????????????? key_part1=const1 AND key_part2=const2 ... AND key_partN=constN ????????? 任何InnoDB或BDB表的主鍵的范圍條件。 ????????? 索引合并方法交集算法適用的一個條件。下面是一些例子： SELECT * FROM t1 WHERE key1=1 OR key2=2 OR key3=3; ? SELECT * FROM innodb_table WHERE (key1=1 AND key2=2) OR ? (key3='foo' AND key4='bar') AND key5=5; #### 7.2.6.3.?索引合并排序并集訪問算法該訪問算法可以用于當WHERE子句結合OR被轉換為不同的關鍵字的幾個范圍條件，但索引合并方法聯合算法并不適用的時候。下面是一些例子： SELECT * FROM tbl_name WHERE key_col1 < 10 OR key_col2 < 20; ? SELECT * FROM tbl_name ???? WHERE (key_col1 > 10 OR key_col2 = 20) AND nonkey_col=30; 排序聯合算法和聯合算法的區別是排序聯合算法必須先索取所有記錄的行ID，然后在返回記錄前對它們進行排序。 ### 7.2.7.?MySQL如何優化IS NULL`` MySQL可以對可以結合_col_name_=_constant_value_使用的_col_name_IS NULL進行相同的優化。例如，MySQL可以使用索引和范圍用IS NULL搜索NULL。 SELECT * FROM tbl_name WHERE key_col IS NULL; ? SELECT * FROM tbl_name WHERE key_col <=> NULL; ? SELECT * FROM tbl_name ??? WHERE key_col=const1 OR key_col=const2 OR key_col IS NULL; 如果WHERE子句包括聲明為NOT NULL的列的_col_name_IS NULL條件，表達式則優化。當列會產生NULL時，不會進行優化；例如，如果來自LEFT JOIN右側的表。 MySQL也可以優化組合_col_name_ = _expr_ AND _col_name_ IS NULL，這是解決子查詢的一種常用形式。當使用優化時EXPLAIN顯示ref_or_null。該優化可以為任何關鍵元素處理IS NULL。下面是一些優化的查詢例子，假定表t2的列a和b有一個索引： SELECT * FROM t1 WHERE t1.a=expr OR t1.a IS NULL; ? SELECT * FROM t1, t2 WHERE t1.a=t2.a OR t2.a IS NULL; ? SELECT * FROM t1, t2 ??? WHERE (t1.a=t2.a OR t2.a IS NULL) AND t2.b=t1.b; ? SELECT * FROM t1, t2 ??? WHERE t1.a=t2.a AND (t2.b=t1.b OR t2.b IS NULL); ? SELECT * FROM t1, t2 ??? WHERE (t1.a=t2.a AND t2.a IS NULL AND ...) ??? OR (t1.a=t2.a AND t2.a IS NULL AND ...); ref_or_null首先讀取參考關鍵字，然后單獨搜索NULL關鍵字的行。請注意該優化只可以處理一個IS NULL。在后面的查詢中，MySQL只對表達式(t1.a=t2.a AND t2.a IS NULL)使用關鍵字查詢，不能使用b的關鍵元素： SELECT * FROM t1, t2 ???? WHERE (t1.a=t2.a AND t2.a IS NULL) ???? OR (t1.b=t2.b AND t2.b IS NULL); ### 7.2.8.?MySQL如何優化DISTINCT`` 在許多情況下結合ORDER BY的DISTINCT需要一個臨時表。請注意因為DISTINCT可能使用GROUP BY，必須清楚MySQL如何使用所選定列的一部分的ORDER BY或HAVING子句中的列。參見[12.10.3節，“具有隱含字段的GROUP BY”](# "12.10.3.?GROUP BY with Hidden Fields")。在大多數情況下，DISTINCT子句可以視為GROUP BY的特殊情況。例如，下面的兩個查詢是等效的： SELECT DISTINCT c1, c2, c3 FROM t1 WHERE c1 > const; ? SELECT c1, c2, c3 FROM t1 WHERE c1 > const GROUP BY c1, c2, c3; 由于這個等效性，適用于GROUP BY查詢的優化也適用于有DISTINCT子句的查詢。這樣，關于DISTINCT查詢的優化的更詳細的情況，參見[7.2.13節，“MySQL如何優化GROUP BY``”](# "7.2.13.?How MySQL Optimizes GROUP BY")。結合LIMIT _row_count_和DISTINCT后，MySQL發現唯一的_row_count_行后立即停止。如果不使用查詢中命名的所有表的列，MySQL發現第1個匹配后立即停止掃描未使用的表。在下面的情況中，假定t1在t2之前使用(可以用EXPLAIN檢查)，發現t2中的第1行后，MySQL不再(為t1中的任何行)讀t2： SELECT DISTINCT t1.a FROM t1, t2 where t1.a=t2.a; ### 7.2.9.?MySQL如何優化LEFT JOIN和RIGHT JOIN 在MySQL中，_A_ LEFT JOIN _B_ join_condition執行過程如下： ????????? 根據表_A_和_A_依賴的所有表設置表B。 ????????? 根據LEFT JOIN條件中使用的所有表(除了_B_)設置表_A_。 ????????? LEFT JOIN條件用于確定如何從表_B_搜索行。(換句話說，不使用WHERE子句中的任何條件）。 ????????? 可以對所有標準聯接進行優化，只是只有從它所依賴的所有表讀取的表例外。如果出現循環依賴關系，MySQL提示出現一個錯誤。 ????????? 進行所有標準WHERE優化。 ????????? 如果_A_中有一行匹配WHERE子句，但_B_中沒有一行匹配ON條件，則生成另一個_B_行，其中所有列設置為NULL。 ????????? 如果使用LEFT JOIN找出在某些表中不存在的行，并且進行了下面的測試：WHERE部分的_col_name_ IS NULL，其中_col_name_是一個聲明為 NOT NULL的列，MySQL找到匹配LEFT JOIN條件的一個行后停止(為具體的關鍵字組合)搜索其它行。 RIGHT JOIN的執行類似LEFT JOIN，只是表的角色反過來。聯接優化器計算表應聯接的順序。LEFT JOIN和STRAIGHT_JOIN強制的表讀順序可以幫助聯接優化器更快地工作，因為檢查的表交換更少。請注意這說明如果執行下面類型的查詢，MySQL進行全掃描b，因為LEFT JOIN強制它在d之前讀取： SELECT * ??? FROM a,b LEFT JOIN c ON (c.key=a.key) LEFT JOIN d ON (d.key=a.key) ??? WHERE b.key=d.key; 在這種情況下修復時用a的相反順序，b列于FROM子句中： SELECT * ??? FROM b,a LEFT JOIN c ON (c.key=a.key) LEFT JOIN d ON (d.key=a.key) ??? WHERE b.key=d.key; MySQL可以進行下面的LEFT JOIN優化：如果對于產生的NULL行，WHERE條件總為假，LEFT JOIN變為普通聯接。例如，在下面的查詢中如果t2.column1為NULL，WHERE子句將為false： SELECT * FROM t1 LEFT JOIN t2 ON (column1) WHERE t2.column2=5; 因此，可以安全地將查詢轉換為普通聯接： SELECT * FROM t1, t2 WHERE t2.column2=5 AND t1.column1=t2.column1; 這樣可以更快，因為如果可以使查詢更佳，MySQL可以在表t1之前使用表t2。為了強制使用表順序，使用STRAIGHT_JOIN。 ### 7.2.10.?MySQL如何優化嵌套Join 表示聯接的語法允許嵌套聯接。下面的討論引用了[13.2.7.1節，“JOIN語法”](# "13.2.7.1.?JOIN Syntax")中描述的聯接語法。同SQL標準比較，_table_factor_語法已經擴展了。后者只接受_table_reference_，而不是括號內所列的。 _table_reference_項列表內的每個逗號等價于內部聯接，這是一個保留擴展名。例如： SELECT * FROM t1 LEFT JOIN (t2, t3, t4) ???????????????? ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c) 等價于： SELECT * FROM t1 LEFT JOIN (t2 CROSS JOIN t3 CROSS JOIN t4) ???????????????? ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c) 在MySQL中，CROSS JOIN語法上等價于INNER JOIN (它們可以彼此代替。在標準SQL中，它們不等價。INNER JOIN結合ON子句使用；CROSS JOIN用于其它地方。總的來說，在只包含內部聯接操作的聯接表達式中可以忽略括號。刪除括號并將操作組合到左側后，聯接表達式： t1 LEFT JOIN (t2 LEFT JOIN t3 ON t2.b=t3.b OR t2.b IS NULL) ?? ON t1.a=t2.a 轉換為表達式： (t1 LEFT JOIN t2 ON t1.a=t2.a) LEFT JOIN t3 ??? ON t2.b=t3.b OR t2.b IS NULL 但是這兩個表達式不等效。要說明這點，假定表t1、t2和t3有下面的狀態： ????????? 表t1包含行{1}、{2} ????????? 表t2包含行{1,101} ????????? 表t3包含行{101} 在這種情況下，第1個表達式返回包括行{1,1,101,101}、{2,NULL,NULL,NULL}的結果，第2個表達式返回行{1,1,101,101}、{2,NULL,NULL,101}： mysql> SELECT * ??? -> FROM t1 ??? ->????? LEFT JOIN ??? ->????? (t2 LEFT JOIN t3 ON t2.b=t3.b OR t2.b IS NULL) ??? ->????? ON t1.a=t2.a; +------+------+------+------+ | a??? | a??? | b??? | b??? | +------+------+------+------+ |??? 1 |??? 1 |? 101 |? 101 | |??? 2 | NULL | NULL | NULL | +------+------+------+------+ ? mysql> SELECT * ??? -> FROM (t1 LEFT JOIN t2 ON t1.a=t2.a) ??? ->????? LEFT JOIN t3 ??? ->????? ON t2.b=t3.b OR t2.b IS NULL; +------+------+------+------+ | a??? | a??? | b??? | b??? | +------+------+------+------+ |??? 1 |??? 1 |? 101 |? 101 | |??? 2 | NULL | NULL |? 101 | +------+------+------+------+ 在下面的例子中，外面的聯接操作結合內部聯接操作使用： t1 LEFT JOIN (t2，t3) ON t1.a=t2.a 該表達式不能轉換為下面的表達式： t1 LEFT JOIN t2 ON t1.a=t2.a，t3. 對于給定的表狀態，第1個表達式返回行{1,1,101,101}、{2,NULL,NULL,NULL}，第2個表達式返回行{1,1,101,101}、{2,NULL,NULL,101}： mysql> SELECT * ??? -> FROM t1 LEFT JOIN (t2, t3) ON t1.a=t2.a; +------+------+------+------+ | a ???| a??? | b??? | b??? | +------+------+------+------+ |??? 1 |??? 1 |? 101 |? 101 | |??? 2 | NULL | NULL | NULL | +------+------+------+------+ ? mysql> SELECT * ??? -> FROM t1 LEFT JOIN t2 ON t1.a=t2.a, t3; +------+------+------+------+ | a??? | a??? | b??? | b??? | +------+------+------+------+ |??? 1 |??? 1 |? 101 |? 101 | |??? 2 | NULL | NULL |? 101 | +------+------+------+------+ 因此，如果我們忽略聯接表達式中的括號連同外面的聯接操作符，我們會改變原表達式的結果。更確切地說，我們不能忽視左外聯接操作的右操作數和右聯接操作的左操作數中的括號。換句話說，我們不能忽視外聯接操作中的內表達式中的括號。可以忽視其它操作數中的括號(外部表的操作數)。對于任何表t1、t2、t3和屬性t2.b和t3.b的任何條件P，下面的表達式： (t1,t2) LEFT JOIN t3 ON P(t2.b,t3.b) 等價于表達式 t1，t2 LEFT JOIN t3 ON P(t2.b,t3.b) 如果聯接表達式(_join_table_)中的聯接操作的執行順序不是從左到右，我們則應討論嵌套的聯接。這樣，下面的查詢： SELECT * FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON t2.b=t3.b) ON t1.a=t2.a ? WHERE t1.a > 1 ? SELECT * FROM t1 LEFT JOIN (t2, t3) ON t1.a=t2.a ? WHERE (t2.b=t3.b OR t2.b IS NULL) AND t1.a > 1 聯接表： t2 LEFT JOIN t3 ON t2.b=t3.b t2, t3 認為是嵌套的。第1個查詢結合左聯接操作則形成嵌套的聯接，而在第二個查詢中結合內聯接操作形成嵌套