7. 優化 · MySQL 5.1參考手冊

### 第7章：優化 ** 目錄** [7.1. 優化概述](#)[7.1.1. MySQL設計局限與折衷](#)[ 7.1.2. 為可移植性設計應用程序](#)[ 7.1.3. 我們已將MySQL用在何處？](#)[ 7.1.4. MySQL基準套件](#)[ 7.1.5. 使用自己的基準](#)[ 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. 其它優化技巧](#)[ 7.3. 鎖定事宜](#)[ 7.3.1. 鎖定方法](#)[ 7.3.2. 表鎖定事宜](#)[ 7.4. 優化數據庫結構](#)[ 7.4.1. 設計選擇](#)[ 7.4.2. 使你的數據盡可能小](#)[ 7.4.3. 列索引](#)[ 7.4.4. 多列索引](#)[ 7.4.5. MySQL如何使用索引](#)[ 7.4.6. MyISAM鍵高速緩沖](#)[7.4.7. MyISAM索引統計集合](#)[ 7.4.8. MySQL如何計算打開的表](#)[ 7.4.9. MySQL如何打開和關閉表](#)[ 7.4.10. 在同一個數據庫中創建多個表的缺陷](#)[ 7.5. 優化MySQL服務器](#)[ 7.5.1. 系統因素和啟動參數的調節](#)[ 7.5.2. 調節服務器參數](#)[ 7.5.3. 控制查詢優化器的性能](#)[ 7.5.4. 編譯和鏈接怎樣影響MySQL的速度](#)[ 7.5.5. MySQL如何使用內存](#)[ 7.5.6. MySQL如何使用DNS](#)[ 7.6. 磁盤事宜](#)[ 7.6.1. 使用符號鏈接](#) 優化是一個復雜的任務，因為最終要求了解整個待優化的系統。盡管可以進行局部優化而不需要了解系統或應用程序，為了優化得更好，你必須知道更多的信息。本章解釋并給出不同的優化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個查詢結合左聯接操作則形成嵌套的聯接，而在第二個查詢中結合內聯接操作形成嵌套聯接。在第1個查詢中，括號可以忽略：聯接表達式的語法結構與聯接操作的執行順序相同。但對于第2個查詢，括號不能省略，盡管如果沒有括號，這里的聯接表達式解釋不清楚。(在外部擴展語法中，需要第2個查詢的(t2，t3)的括號，盡管從理論上對查詢分析時不需要括號：這些查詢的語法結構將仍然不清楚，因為LEFT JOIN和ON將充當表達式(t2,t3)的左、右界定符的角色)。前面的例子說明了這些點： ·???????? 對于只包含內聯接(而非外聯接)的聯接表達式，可以刪除括號。你可以移除括號并從左到右評估(或實際上，你可以按任何順序評估表)。 ·???????? 總的來說，對外聯接卻不是這樣。去除括號可能會更改結果。 ·???????? 總的來說，對外聯接和內聯接的結合，也不是這樣。去除括號可能會更改結果。含嵌套外聯接的查詢按含內聯接的查詢的相同的管道方式執行。更確切地說，利用了嵌套環聯接算法。讓我們回憶嵌套環聯接執行查詢時采用什么算法。假定我們有一個如下形式的表T1、T2、T3的聯接查詢： ~~~ SELECT * FROM T1 INNER JOIN T2 ON P1(T1,T2) ~~~ ~~~ ???????????????? INNER JOIN T3 ON P2(T2,T3) ~~~ ~~~ ? WHERE P(T1,T2,T3). ~~~ 這里，P1(T1,T2)和P2(T3,T3)是一些聯接條件(表達式)，其中P(t1,t2,t3)是表T1、T2、T3的列的一個條件。嵌套環聯接算法將按下面的方式執行該查詢： ? ~~~ FOR each row t1 in T1 { ~~~ ~~~ ? FOR each row t2 in T2 such that P1(t1,t2) { ~~~ ~~~ ??? FOR each row t3 in T3 such that P2(t2,t3) { ~~~ ~~~ ????? IF P(t1,t2,t3) { ~~~ ~~~ ???????? t:=t1||t2||t3; OUTPUT t; ~~~ ~~~ ????? } ~~~ ~~~ ??? } ~~~ ~~~ ? } ~~~ ~~~ } ~~~ 符號t1||t2||t3表示“連接行t1、t2和t3的列組成的行”。在下面的一些例子中，出現行名的NULL表示NULL用于行的每個列。例如，t1||t2||NULL表示“連接行t1和t2的列以及t3的每個列的NULL組成的行”。現在讓我們考慮帶嵌套的外聯接的查詢： ~~~ SELECT * FROM T1 LEFT JOIN ~~~ ~~~ ????????????? (T2 LEFT JOIN T3 ON P2(T2,T3)) ~~~ ~~~ ????????????? ON P1(T1,T2) ~~~ ~~~ ? WHERE P(T1,T2,T3)。 ~~~ 對于該查詢我們修改嵌套環模式可以得到： ~~~ FOR each row t1 in T1 { ~~~ ~~~ ? BOOL f1:=FALSE; ~~~ ~~~ ? FOR each row t2 in T2 such that P1(t1,t2) { ~~~ ~~~ ??? BOOL f2:=FALSE; ~~~ ~~~ ??? FOR each row t3 in T3 such that P2(t2,t3) { ~~~ ~~~ ????? IF P(t1,t2,t3) { ~~~ ~~~ ??????? t:=t1||t2||t3; OUTPUT t; ~~~ ~~~ ???? ?} ~~~ ~~~ ????? f2=TRUE; ~~~ ~~~ ????? f1=TRUE; ~~~ ~~~ ??? } ~~~ ~~~ ??? IF (!f2) { ~~~ ~~~ ????? IF P(t1,t2,NULL) { ~~~ ~~~ ??????? t:=t1||t2||NULL; OUTPUT t; ~~~ ~~~ ????? } ~~~ ~~~ ????? f1=TRUE; ~~~ ~~~ ??? } ~~~ ~~~ ? } ~~~ ~~~ ? IF (!f1) { ~~~ ~~~ ??? IF P(t1,NULL,NULL) { ~~~ ~~~ ????? t:=t1||NULL||NULL; OUTPUT t; ~~~ ~~~ ??? } ~~~ ~~~ ? } ~~~ ~~~ } ~~~ ~~~ ? ~~~ 總的來說，對于外聯接操作中的第一個內表的嵌套環，引入了一個標志，在環之前關閉并且在環之后打開。如果對于外部表的當前行，如果匹配表示內操作數的表，則標志打開。如果在循環結尾處標志仍然關閉，則對于外部表的當前行，沒有發現匹配。在這種情況下，對于內表的列，應使用NULL值補充行。結果行被傳遞到輸出進行最終檢查或傳遞到下一個嵌套環，但只能在行滿足所有嵌入式外聯接的聯接條件時。在我們的例子中，嵌入了下面表達式表示的外聯接表： ~~~ (T2 LEFT JOIN T3 ON P2(T2,T3)) ~~~ 請注意對于有內聯接的查詢，優化器可以選擇不同的嵌套環順序，例如： ~~~ FOR each row t3 in T3 { ~~~ ~~~ ? FOR each row t2 in T2 such that P2(t2,t3) { ~~~ ~~~ ??? FOR each row t1 in T1 such that P1(t1,t2) { ~~~ ~~~ ????? IF P(t1,t2,t3) { ~~~ ~~~ ???????? t:=t1||t2||t3; OUTPUT t; ~~~ ~~~ ????? } ~~~ ~~~ ??? } ~~~ ~~~ ? } ~~~ ~~~ } ~~~ 對于有外聯接的查詢，優化器可以只選擇這樣的順序：外表的環優先于內表的環。這樣，對于有外聯接的查詢，只可能有一種嵌套順序。在下面的查詢中，優化器將評估兩個不同的嵌套： ~~~ SELECT * T1 LEFT JOIN (T2,T3) ON P1(T1,T2) AND P2(T1,T3) ~~~ ~~~ ? WHERE P(T1,T2,T3) ~~~ 嵌套為： ~~~ FOR each row t1 in T1 { ~~~ ~~~ ? BOOL f1:=FALSE; ~~~ ~~~ ? FOR each row t2 in T2 such that P1(t1,t2) { ~~~ ~~~ ??? FOR each row t3 in T3 such that P2(t1,t3) { ~~~ ~~~ ????? IF P(t1,t2,t3) { ~~~ ~~~ ??????? t:=t1||t2||t3; OUTPUT t; ~~~ ~~~ ????? } ~~~ ~~~ ????? f1:=TRUE ~~~ ~~~ ??? } ~~~ ~~~ ? } ~~~ ~~~ ? IF (!f1) { ~~~ ~~~ ??? IF P(t1,NULL,NULL) { ~~~ ~~~ ????? t:=t1||NULL||NULL; OUTPUT t; ~~~ ~~~ ??? } ~~~ ~~~ ? } ~~~ ~~~ } ~~~ 和 ~~~ FOR each row t1 in T1 { ~~~ ~~~ ? BOOL f1:=FALSE; ~~~ ~~~ ? FOR each row t3 in T3 such that P2(t1,t3) { ~~~ ~~~ ??? FOR each row t2 in T2 such that P1(t1,t2) { ~~~ ~~~ ????? IF P(t1,t2,t3) { ~~~ ~~~ ??????? t:=t1||t2||t3; OUTPUT t; ~~~ ~~~ ????? } ~~~ ~~~ ????? f1:=TRUE ~~~ ~~~ ??? } ~~~ ~~~ ? } ~~~ ~~~ ? IF (!f1) { ~~~ ~~~ ??? IF P(t1,NULL,NULL) { ~~~ ~~~ ????? t:=t1||NULL||NULL; OUTPUT t; ~~~ ~~~ ??? } ~~~ ~~~ ? } ~~~ ~~~ } ~~~ 在兩個嵌套中，必須在外環中處理T1，因為它用于外聯接中。T2和T3用于內聯接中，因此聯接必須在內環中處理。但是，因為該聯接是一個內聯接，T2和T3可以以任何順序處理。當討論內聯接嵌套環的算法時，我們忽略了部分詳情，可能對查詢執行的性能的影響會很大。我們沒有提及所謂的“下推”條件。假定可以用連接公式表示我們的WHERE條件P(T1,T2,T3)： ~~~ P(T1,T2,T2) = C1(T1) AND C2(T2) AND C3(T3)。 ~~~ 在這種情況下，MySQL實際使用了下面的嵌套環方案來執行帶內聯接得到查詢： ~~~ FOR each row t1 in T1 such that C1(t1) { ~~~ ~~~ ? FOR each row t2 in T2 such that P1(t1,t2) AND C2(t2)? { ~~~ ~~~ ??? FOR each row t3 in T3 such that P2(t2,t3) AND C3(t3) { ~~~ ~~~ ????? IF P(t1,t2,t3) { ~~~ ~~~ ???????? t:=t1||t2||t3; OUTPUT t; ~~~ ~~~ ????? } ~~~ ~~~ ??? } ~~~ ~~~ ? } ~~~ ~~~ } ~~~ 你會看見每個連接 C1(T1)，C2(T2)，C3(T3)被從最內部的環內推出到可以對它進行評估的最外的環中。如果C1(T1)是一個限制性很強的條件，下推條件可以大大降低從表T1傳遞到內環的行數。結果是查詢大大加速。對于有外聯接的查詢，只有查出外表的當前的行可以匹配內表后，才可以檢查WHERE條件。這樣，對內嵌套環下推的條件不能直接用于帶外聯接的查詢。這里我們必須引入有條件下推前提，由遇到匹配后打開的標志保護。對于帶下面的外聯接的例子 ~~~ P(T1,T2,T3)=C1(T1) AND C(T2) AND C3(T3) ~~~ 使用受保護的下推條件的嵌套環方案看起來應為： ~~~ FOR each row t1 in T1 such that C1(t1) { ~~~ ~~~ ? BOOL f1:=FALSE; ~~~ ~~~ ? FOR each row t2 in T2 ~~~ ~~~ ????? such that P1(t1,t2) AND (f1?C2(t2):TRUE) { ~~~ ~~~ ??? BOOL f2:=FALSE; ~~~ ~~~ ??? FOR each row t3 in T3 ~~~ ~~~ ??????? such that P2(t2,t3) AND (f1&&f2?C3(t3):TRUE) { ~~~ ~~~ ????? IF (f1&&f2?TRUE:(C2(t2) AND C3(t3))) { ~~~ ~~~ ????? ??t:=t1||t2||t3; OUTPUT t; ~~~ ~~~ ????? } ~~~ ~~~ ????? f2=TRUE; ~~~ ~~~ ????? f1=TRUE; ~~~ ~~~ ??? } ~~~ ~~~ ??? IF (!f2) { ~~~ ~~~ ????? IF (f1?TRUE:C2(t2) && P(t1,t2,NULL)) { ~~~ ~~~ ??????? t:=t1||t2||NULL; OUTPUT t; ~~~ ~~~ ????? } ~~~ ~~~ ????? f1=TRUE; ~~~ ~~~ ??? } ~~~ ~~~ ? } ~~~ ~~~ ? IF (!f1 && P(t1,NULL,NULL)) { ~~~ ~~~ ????? t:=t1||NULL||NULL; OUTPUT t; ~~~ ~~~ ? } ~~~ ~~~ } ~~~ 總的來說，可以從聯接條件（例如P1(T1,T2)和P(T2,T3)）提取下推前提。在這種情況下，下推前提也受一個標志保護，防止檢查由相應外聯接操作所產生的NULL-補充的行的斷言。請注意如果從判斷式的WHERE條件推導出，根據從一個內表到相同嵌套聯接的另一個表的關鍵字進行的訪問被禁止。(在這種情況下，我們可以使用有條件關鍵字訪問，但是該技術還未用于MySQL 5.1中）。 ### 7.2.11.?MySQL如何簡化外部聯合在許多情況下，一個查詢的FROM子句的表的表達式可以簡化。在分析階段，帶右外聯接操作的查詢被轉換為只包含左聯接操作的等效查詢。總的來說，根據以下原則進行轉換： ~~~ (T1, ...) RIGHT JOIN (T2,...) ON P(T1,...,T2,...) = ~~~ ~~~ (T2, ...) LEFT JOIN (T1,...) ON P(T1,...,T2,...) ~~~ 所有T1 INNER JOIN T2 ON P(T1,T2)形式的內聯接表達式被替換為T1,T2、P(T1,T2)，并根據WHERE條件(或嵌入連接的聯接條件，如果有)聯接為一個連接。當優化器為用外聯接操作的聯接查詢評估方案時，它只考慮在訪問內表之前訪問外表的操作的方案。優化器選項受到限制，因為只有這樣的方案允許我們用嵌套環機制執行帶外聯接操作的查詢。假定我們有一個下列形式的查詢： SELECT * T1 LEFT JOIN T2 ON P1(T1,T2) ? WHERE P(T1,T2) AND R(T2) R(T2)大大減少了表T2中匹配的行數。如果我們這樣執行查詢，優化器將不會有其它選擇，只能在訪問表T2之前訪問表T1，從而導致執行方案非常低。幸運的是，如果WHERE條件拒絕null，MySQL可以將此類查詢轉換為沒有外聯接操作的查詢。如果為該操作構建的NULL補充的行評估為FALSE或UNKNOWN，則該條件稱為對于某個外聯接操作拒絕null。因此，對于該外聯接： ~~~ T1 LEFT JOIN T2 ON T1.A=T2.A ~~~ 類似下面的條件為拒絕null： ~~~ T2.B IS NOT NULL, ~~~ ~~~ T2.B > 3, ~~~ ~~~ T2.C <= T1.C, ~~~ ~~~ T2.B < 2 OR T2.C > 1 ~~~ 類似下面的條件不為拒絕null： ~~~ T2.B IS NULL, ~~~ ~~~ T1.B < 3 OR T2.B IS NOT NULL, ~~~ ~~~ T1.B < 3 OR T2.B > 3 ~~~ 檢查一個外聯接操作的條件是否拒絕null的總原則很簡單。以下情況下為拒絕null的條件： ·???????? 形式為A IS NOT NULL，其中A是任何內表的一個屬性 ·???????? 包含內表引用的判斷式，當某個參量為NULL時評估為UNKNOWN ·???????? 包含用于連接的拒絕null的條件的聯合 ·???????? 拒絕null的條件的邏輯和一個條件可以對于一個查詢中的一個外聯接操作為拒絕null的而對于另一個不為拒絕null的。在下面的查詢中： SELECT * FROM T1 LEFT JOIN T2 ON T2.A=T1.A ???????????????? LEFT JOIN T3 ON T3.B=T1.B ? WHERE T3.C > 0 WHERE條件對于第2個外聯接操作為拒絕null的但對于第1個不為拒絕null的。如果WHERE條件對于一個查詢中的一個外聯接操作為拒絕null的，外聯接操作被一個內聯接操作代替。例如，前面的查詢被下面的查詢代替： SELECT * FROM T1 LEFT JOIN T2 ON T2.A=T1.A ??????? ?????????INNER JOIN T3 ON T3.B=T1.B ? WHERE T3.C > 0 對于原來的查詢，優化器將評估只與一個訪問順序T1、T2、T3兼容的方案。在替換的查詢中，還考慮了訪問順序T3、T1、T2。一個外聯接操作的轉化可以觸發另一個的轉化。這樣，查詢： SELECT * FROM T1 LEFT JOIN T2 ON T2.A=T1.A ???????????????? LEFT JOIN T3 ON T3.B=T2.B ? WHERE T3.C > 0 將首先轉換為查詢： SELECT * FROM T1 LEFT JOIN T2 ON T2.A=T1.A ???????????????? INNER JOIN T3 ON T3.B=T2.B ? WHERE T3.C > 0 該查詢等效于查詢： SELECT * FROM (T1 LEFT JOIN T2 ON T2.A=T1.A), T3 ? WHERE T3.C > 0 AND T3.B=T2.B 現在剩余的外聯接操作也可以被一個內聯接替換，因為條件T3.B=T2.B為拒絕null的，我們可以得到一個根本沒有外聯接的查詢： SELECT * FROM (T1 INNER JOIN T2 ON T2.A=T1.A), T3 ? WHERE T3.C > 0 AND T3.B=T2.B 有時我們可以成功替換嵌入的外聯接操作，但不能轉換嵌入的外聯接。下面的查詢： SELECT * FROM T1 LEFT JOIN ????????????? (T2 LEFT JOIN T3 ON T3.B=T2.B) ????????????? ON T2.A=T1.A ? WHERE T3.C > 0 被轉換為： SELECT * FROM T1 LEFT JOIN ????????????? (T2 INNER JOIN T3 ON T3.B=T2.B) ????????????? ON T2.A=T1.A ? WHERE T3.C > 0，只能重新寫為仍然包含嵌入式外聯接操作的形式： SELECT * FROM T1 LEFT JOIN ????????????? (T2,T3) ????????????? ON (T2.A=T1.A AND T3.B=T2.B) ? WHERE T3.C > 0。如果試圖轉換一個查詢中的嵌入式外聯接操作，我們必須考慮嵌入式外聯接的聯接條件和WHERE條件。在下面的查詢中： ~~~ SELECT * FROM T1 LEFT JOIN ~~~ ~~~ ????????????? (T2 LEFT JOIN T3 ON T3.B=T2.B) ~~~ ~~~ ????????????? ON T2.A=T1.A AND T3.C=T1.C ~~~ ~~~ ? WHERE T3.D > 0 OR T1.D > 0 ~~~ ~~~ WHERE條件對于嵌入式外聯接不為拒絕null的，但嵌入式外聯接T2.A=T1.A AND T3.C=T1.C的聯接條件為拒絕null的。因此該查詢可以轉換為： ~~~ SELECT * FROM T1 LEFT JOIN ????????????? (T2, T3) ????????????? ON T2.A=T1.A AND T3.C=T1.C AND T3.B=T2.B ? WHERE T3.D > 0 OR T1.D > 0 ### 7.2.12.?MySQL如何優化ORDER BY`` 在某些情況中，MySQL可以使用一個索引來滿足ORDER BY子句，而不需要額外的排序。即使ORDER BY不確切匹配索引，只要WHERE子句中的所有未使用的索引部分和所有額外的ORDER BY 列為常數，就可以使用索引。下面的查詢使用索引來解決ORDER BY部分： SELECT * FROM t1 ??? ORDER BY *key_part1*,*key_part2*,... ; ??? SELECT * FROM t1 ??? WHERE *key_part1*=*constant* ??? ORDER BY *key_part2*; ??? SELECT * FROM t1 ??? ORDER BY *key_part1* DESC, *key_part2* DESC; ??? SELECT * FROM t1 ??? WHERE *key_part1*=1 ??? ORDER BY *key_part1* DESC, *key_part2* DESC; 在某些情況下，MySQL*不能*使用索引來解決ORDER BY，盡管它仍然使用索引來找到匹配WHERE子句的行。這些情況包括： ·???????? 對不同的關鍵字使用ORDER BY： ~~~ ·??????????????? SELECT * FROM t1 ORDER BY key1, key2； ~~~ ·???????? 對關鍵字的非連續元素使用ORDER BY： ~~~ ·??????????????? SELECT * FROM t1 WHERE key2=constant ORDER BY key_part2； ~~~ ·???????? 混合ASC和DESC： ~~~ ·??????????????? SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC； ~~~ ·???????? 用于查詢行的關鍵字與ORDER BY中所使用的不相同： ~~~ ·??????????????? SELECT * FROM t1 WHERE key2=constant ORDER BY key1； ~~~ ·???????? 你正聯接許多表，并且ORDER BY中的列并不是全部來自第1個用于搜索行的非常量表。(這是EXPLAIN輸出中的沒有const聯接類型的第1個表）。 ·???????? 有不同的ORDER BY和GROUP BY表達式。 ·???????? 使用的表索引的類型不能按順序保存行。例如，對于HEAP表的HASH索引情況即如此。通過EXPLAIN SELECT ...ORDER BY，可以檢查MySQL是否可以使用索引來解決查詢。如果Extra列內有Using filesort，則不能解決查詢。參見[7.2.1節，“EXPLAIN語法（獲取關于SELECT的信息）``”](# "7.2.1.?EXPLAIN Syntax (Get Information About a SELECT)")。文件排序優化不僅用于記錄排序關鍵字和行的位置，并且還記錄查詢需要的列。這樣可以避免兩次讀取行。文件排序算法的工作象這樣： 1.??? 讀行匹配WHERE子句的行，如前面所示。 2.??? 對于每個行，記錄構成排序關鍵字和行位置的一系列值，并且記錄查詢需要的列。 3.??? 根據排序關鍵字排序元組 4.??? 按排序的順序檢索行，但直接從排序的元組讀取需要的列，而不是再一次訪問表。該算法比以前版本的Mysql有很大的改進。為了避免速度變慢，該優化只用于排序元組中的extra列的總大小不超過max_length_for_sort_data系統變量值的時候。(將該變量設置得太高的的跡象是將看到硬盤活動太頻繁而CPU活動較低）。如果想要增加ORDER BY的速度，首先看是否可以讓MySQL使用索引而不是額外的排序階段。如果不能，可以嘗試下面的策略： ·???????? 增加sort_buffer_size變量的大小。 ·???????? 增加read_rnd_buffer_size變量的大小。 ·???????? 更改tmpdir指向具有大量空閑空間的專用文件系統。該選項接受幾個使用round-robin(循環)模式的路徑。在Unix中路徑應用冒號(‘:’)區間開，在Windows、NetWare和OS/2中用分號(‘；’)。可以使用該特性將負載均分到幾個目錄中。*注釋：*路徑應為位于不同*物理*硬盤上的文件系統的目錄，而不是同一硬盤的不同的分區。默認情況下，MySQL排序所有GROUP BY *col1*，*col2*，...查詢的方法如同在查詢中指定ORDER BY*col1*，*col2*，...。如果顯式包括一個包含相同的列的ORDER BY子句，MySQL可以毫不減速地對它進行優化，盡管仍然進行排序。如果查詢包括GROUP BY但你想要避免排序結果的消耗，你可以指定ORDER BY NULL禁止排序。例如： INSERT INTO foo SELECT a, COUNT(*) FROM bar GROUP BY a ORDER BY NULL; ### 7.2.13.?MySQL如何優化GROUP BY`` [ 7.2.13.1. 松散索引掃描](#)[ 7.2.13.2. 緊湊索引掃描](#) 滿足GROUP BY子句的最一般的方法是掃描整個表并創建一個新的臨時表，表中每個組的所有行應為連續的，然后使用該臨時表來找到組并應用累積函數(如果有)。在某些情況中，MySQL能夠做得更好，通過索引訪問而不用創建臨時表。為GROUP BY使用索引的最重要的前提條件是所有GROUP BY列引用同一索引的屬性，并且索引按順序保存其關鍵字(例如，這是B-樹索引，而不是HASH索引)。是否用索引訪問來代替臨時表的使用還取決于在查詢中使用了哪部分索引、為該部分指定的條件，以及選擇的累積函數。有兩種方法通過索引訪問執行GROUP BY查詢，如下面的章節所描述。在第1個方法中，組合操作結合所有范圍判斷式使用(如果有)。第2個方法首先執行范圍掃描，然后組合結果元組。 #### 7.2.13.1.?松散索引掃描使用索引時最有效的途徑是直接搜索組域。通過該訪問方法，MySQL使用某些關鍵字排序的索引類型(例如，B-樹)的屬性。該屬性允許使用索引中的查找組而不需要考慮滿足所有WHERE條件的索引中的所有關鍵字。既然該訪問方法只考慮索引中的關鍵字的一小部分，它被稱為*松散索引掃描*。如果沒有WHERE子句，松散索引掃描讀取的關鍵字數量與組數量一樣多，可以比所有關鍵字數小得多。如果WHERE子句包含范圍判斷式(關于range聯接類型的討論參見[7.2.1節，“EXPLAIN語法（獲取關于SELECT的信息）``”](# "7.2.1.?EXPLAIN Syntax (Get Information About a SELECT)"))，松散索引掃描查找滿足范圍條件的每個組的第1個關鍵字，并且再次讀取盡可能最少數量的關鍵字。在下面的條件下是可以的： ·???????? 查詢針對一個單表。 ·???????? GROUP BY包括索引的第1個連續部分(如果對于GROUP BY，查詢有一個DISTINCT子句，則所有顯式屬性指向索引開頭)。 ·???????? 只使用累積函數(如果有)MIN()和MAX()，并且它們均指向相同的列。 ·???????? 索引的任何其它部分（除了那些來自查詢中引用的GROUP BY）必須為常數(也就是說，必須按常量數量來引用它們)，但MIN()或MAX()函數的參數例外。此類查詢的EXPLAIN輸出顯示Extra列的Using indexforgroup-by。下面的查詢提供該類的幾個例子，假定表t1(c1,c2,c3,c4)有一個索引idx(c1，c2，c3)： SELECT c1, c2 FROM t1 GROUP BY c1, c2; SELECT DISTINCT c1, c2 FROM t1; SELECT c1, MIN(c2) FROM t1 GROUP BY c1; SELECT c1, c2 FROM t1 WHERE c1 < *const* GROUP BY c1, c2; SELECT MAX(c3), MIN(c3), c1, c2 FROM t1 WHERE c2 > *const* GROUP BY c1, c2; SELECT c2 FROM t1 WHERE c1 < *const* GROUP BY c1, c2; SELECT c1, c2 FROM t1 WHERE c3 = *const* GROUP BY c1, c2；由于上述原因，不能用該快速選擇方法執行下面的查詢： 1.????? 除了MIN()或MAX()，還有其它累積函數，例如： ~~~ ???? SELECT c1, SUM(c2) FROM t1 GROUP BY c1; ~~~ 2.????? GROUP BY子句中的域不引用索引開頭，如下所示： ~~~ ???? SELECT c1,c2 FROM t1 GROUP BY c2, c3; ~~~ 3.????? 查詢引用了GROUP BY部分后面的關鍵字的一部分，并且沒有等于常量的等式，例如： ~~~ ???? SELECT c1,c3 FROM t1 GROUP BY c1, c2； ~~~ #### 7.2.13.2.?緊湊索引掃描緊湊式索引掃描可以為索引掃描或一個范圍索引掃描，取決于查詢條件。如果不滿足松散索引掃描條件，GROUP BY查詢仍然可以不用創建臨時表。如果WHERE子句中有范圍條件，該方法只讀取滿足這些條件的關鍵字。否則，進行索引掃描。該方法讀取由WHERE子句定義的每個范圍的所有關鍵字，或沒有范圍條件式掃描整個索引，我們將它定義為緊湊式索引掃描。請注意對于緊湊式索引掃描，只有找到了滿足范圍條件的所有關鍵字后才進行組合操作。要想讓該方法工作，對于引用GROUP BY關鍵字元素的前面、中間關鍵字元素的查詢中的所有列，有一個常量等式條件即足夠了。等式條件中的常量填充了搜索關鍵字中的“差距”，可以形成完整的索引前綴。這些索引前綴可以用于索引查找。如果需要排序GROUP BY結果，并且能夠形成索引前綴的搜索關鍵字，MySQL還可以避免額外的排序操作，因為使用有順序的索引的前綴進行搜索已經按順序檢索到了所有關鍵字。上述的第一種方法不適合下面的查詢，但第2種索引訪問方法可以工作(假定我們已經提及了表t1的索引idx)： ·???????? GROUP BY中有一個差距，但已經由條件c2 = 'a'覆蓋。 ~~~ ???? SELECT c1，c2，c3 FROM t1 WHERE c2 = 'a' GROUP BY c1，c3; ~~~ ·???????? GROUP BY不以關鍵字的第1個元素開始，但是有一個條件提供該元素的常量： ~~~ ???? SELECT c1，c2，c3 FROM t1 WHERE c1 = 'a' GROUP BY c2，c3; ~~~ ### 7.2.14.?MySQL如何優化LIMIT`` 在一些情況中，當你使用LIMIT *row_count*而不使用HAVING時，**MySQL**將以不同方式處理查詢。 ·???????? 如果你用LIMIT只選擇一些行，當**MySQL選擇**做完整的表掃描時，它將在一些情況下使用索引。 ·???????? 如果你使用LIMIT *row_count*與ORDER BY，**MySQL**一旦找到了排序結果的第一個*row_count*行，將結束排序而不是排序整個表。如果使用索引，將很快。如果必須進行文件排序（filesort），必須選擇所有匹配查詢沒有LIMIT子句的行，并且在確定已經找到第1個*row_count*行前，必須對它們的大部分進行排序。在任何一種情況下，一旦找到了行，則不需要再排序結果的其它部分，并且MySQL不再進行排序。 ·???????? 當結合LIMIT *row_count*和DISTINCT時，**MySQL**一旦找到*row_count*個唯一的行，它將停止。 ·???????? 在一些情況下，GROUP BY能通過順序讀取鍵(或在鍵上做排序)來解決，然后計算摘要直到關鍵字的值改變。在這種情況下，LIMIT *row_count*將不計算任何不必要的GROUP BY值。 ·???????? 只要MySQL已經發送了需要的行數到客戶，它將放棄查詢，除非你正使用SQL_CALC_FOUND_ROWS。 ·???????? LIMIT 0將總是快速返回一個空集合。這對檢查查詢的有效性是有用的。當使用MySQL API時，它也可以用來得到結果列的列類型。(該技巧在MySQL Monitor中不工作，只顯示Empty set；應使用SHOW COLUMNS或DESCRIBE）。 ·???????? 當服務器使用臨時表來進行查詢時，使用LIMIT *row_count*子句來計算需要多少空間。 ### 7.2.15.?如何避免表掃描 EXPLAIN的輸出顯示了當MySQL使用表掃描來解決查詢時使用的所有類型列。這通常在如下條件下發生： ·???????? 表很小，掃描表比查找關鍵字速度快。這對于少于10行并且行較短的表比較普遍。 ·???????? 在ON或WHERE子句中沒有適用的索引列的約束。 ·???????? 正用常量值比較索引列，并且MySQL已經計算到(基于索引樹)常數覆蓋了表的很大部分并且表掃描將會比較快。參見[7.2.4節，“MySQL怎樣優化WHERE子句``”](# "7.2.4.?How MySQL Optimizes WHERE Clauses")。 ·???????? 你正通過另一個列使用一個低的集的勢的關鍵字(許多行匹配關鍵字)。在這種情況下，MySQL假設通過使用關鍵字它可能會進行許多關鍵字查找，表掃描將會更快。對于小表，表掃描通常合適。對于大表，嘗試下面的技巧以避免優化器錯選了表掃描： ·???????? 使用ANALYZE TABLE *tbl_name*為掃描的表更新關鍵字分布。參見[13.5.2.1節，“ANALYZE TABLE語法”](# "13.5.2.1.?ANALYZE TABLE Syntax")。 ·???????? 對掃描的表使用FORCEINDEX告知MySQL，相對于使用給定的索引表掃描將非常耗時。參見[13.2.7節，“SELECT語法”](# "13.2.7.?SELECT Syntax")。 ~~~ ·??????????????? SELECT * FROM t1, t2 FORCE INDEX (index_for_column) ~~~ ~~~ ·??????????????? ????WHERE t1.col_name=t2.col_name； ~~~ ·???????? 用--max-seeks-for-key=1000選項啟動**mysqld**或使用SET max_seeks_for_key=1000告知優化器假設關鍵字掃描不會超過1,000次關鍵字搜索。參見[5.3.3節，“服務器系統變量”](# "5.3.3.?Server System Variables")。 ### 7.2.16.?INSERT語句的速度插入一個記錄需要的時間由下列因素組成，其中的數字表示大約比例： - 連接：(3) - 發送查詢給服務器：(2) - 分析查詢：(2) - 插入記錄：（1x記錄大小） - 插入索引：（1x索引） - 關閉：(1) 這不考慮打開表的初始開銷，每個并發運行的查詢打開。表的大小以log*N* (B樹)的速度減慢索引的插入。加快插入的一些方法： ·???????? 如果同時從同一個客戶端插入很多行，使用含多個VALUE的INSERT語句同時插入幾行。這比使用單行INSERT語句快(在某些情況下快幾倍)。如果你正向一個非空表添加數據，可以調節bulk_insert_buffer_size變量，使數據插入更快。參見[5.3.3節，“服務器系統變量”](# "5.3.3.?Server System Variables")。 ·???????? 如果你從不同的客戶端插入很多行，能通過INSERT DELAYED語句加快速度。參見[13.2.4節，“INSERT語法”](# "13.2.4.?INSERT Syntax")。 ·???????? 用MyISAM，如果在表中沒有刪除的行，能在SELECT語句正在運行的同時插入行。 ·???????? 當從一個文本文件裝載一個表時，使用LOAD DATA INFILE。這通常比使用很多INSERT語句快20倍。參見[13.2.5節，“LOAD DATA INFILE語法”](# "13.2.5.?LOAD DATA INFILE Syntax")。 ·???????? 當表有很多索引時，有可能要多做些工作使得LOAD DATA INFILE更快些。使用下列過程： 1. 有選擇地用CREATE TABLE創建表。 1. 執行FLUSH TABLES語句或命令**mysqladmin flush-tables**。 1. 使用**myisamchk --keys-used=0 -rq * /path/to/db/tbl_name***。這將從表中取消所有索引的使用。 1. 用LOAD DATA INFILE把數據插入到表中，因為不更新任何索引，因此很快。 1. 如果只想在以后讀取表，使用**myisampack**壓縮它。參見[15.1.3.3節，“壓縮表特性”](# "15.1.3.3.?Compressed Table Characteristics")。 1. 用**myisamchk -r -q *****/path/to/db/tbl_name***重新創建索引。這將在寫入磁盤前在內存中創建索引樹，并且它更快，因為避免了大量磁盤搜索。結果索引樹也被完美地平衡。 1. 執行FLUSH TABLES語句或**mysqladmin flush-tables**命令。請注意如果插入一個空MyISAM表，LOAD DATA INFILE也可以執行前面的優化；主要不同處是可以讓**myisamchk**為創建索引分配更多的臨時內存，比執行LOAD DATA INFILE語句時為服務器重新創建索引分配得要多。也可以使用ALTER TABLE *tbl_name* DISABLE KEYS代替**myisamchk --keys-used=0 -rq */path/to/db/tbl_name***，使用ALTER TABLE *tbl_name* ENABLE KEYS代替**myisamchk -r -q *****/path/to/db/tbl_name***。使用這種方式，還可以跳過FLUSH TABLES。 ·???????? 鎖定表可以加速用多個語句執行的INSERT操作： - LOCK TABLES a WRITE; - INSERT INTO a VALUES (1,23),(2,34),(4,33); - INSERT INTO a VALUES (8,26),(6,29); - UNLOCK TABLES; 這樣性能會提高，因為索引緩存區僅在所有INSERT語句完成后刷新到磁盤上一次。一般有多少INSERT語句即有多少索引緩存區刷新。如果能用一個語句插入所有的行，就不需要鎖定。對于事務表，應使用BEGIN和COMMIT代替LOCK TABLES來加快插入。鎖定也將降低多連接測試的整體時間，盡管因為它們等候鎖定最大等待時間將上升。例如： Connection 1 does 1000 inserts Connections 2, 3, and 4 do 1 insert Connection 5 does 1000 inserts 如果不使用鎖定，2、3和4將在1和5前完成。如果使用鎖定，2、3和4將可能不在1或5前完成，但是整體時間應該快大約40%。 INSERT、UPDATE和DELETE操作在**MySQL**中是很快的，通過為在一行中多于大約5次連續不斷地插入或更新的操作加鎖，可以獲得更好的整體性能。如果在一行中進行多次插入，可以執行LOCK TABLES，隨后立即執行UNLOCK TABLES(大約每1000行)以允許其它的線程訪問表。這也會獲得好的性能。 INSERT裝載數據比LOAD DATA INFILE要慢得多，即使是使用上述的策略。 ·???????? 為了對LOAD DATA INFILE和INSERT在MyISAM表得到更快的速度，通過增加key_buffer_size系統變量來擴大鍵高速緩沖區。參見[7.5.2節，“調節服務器參數”](# "7.5.2.?Tuning Server Parameters")。 ? ### 7.2.17.?UPDATE語句的速度更新查詢的優化同SELECT查詢一樣，需要額外的寫開銷。寫速度依賴于更新的數據大小和更新的索引的數量。沒有更改的索引不被更新。使更改更快的另一個方法是推遲更改然后在一行內進行多次更新。如果鎖定表，同時做多個更新比一次做一個快得多。請注意對使用動態記錄格式的MyISAM表，更新一個較長總長的記錄可能會切分記錄。如果經常這樣該，偶爾使用OPTIMIZE TABLE很重要。參見[13.5.2.5節，“OPTIMIZE TABLE語法”](# "13.5.2.5.?OPTIMIZE TABLE Syntax")。 ### 7.2.18.?DELETE語句的速度刪除一個記錄的時間與索引數量確切成正比。為了更快速地刪除記錄，可以增加鍵高速緩沖的大小。參見[7.5.2節，“調節服務器參數”](# "7.5.2.?Tuning Server Parameters")。如果想要刪除一個表的所有行，使用TRUNCATE TABLE *tbl_name*而不要用DELETE FROM *tbl_name*。參見[13.2.9節，“TRUNCATE語法”](# "13.2.9.?TRUNCATE Syntax")。 ### 7.2.19.?其它優化技巧該節列出了提高查詢速度的各種技巧： ·???????? 使用持久的連接數據庫以避免連接開銷。如果不能使用持久的連接并且你正啟動許多新的與數據庫的連接，可能要更改thread_cache_size變量的值。參見[7.5.2節，“調節服務器參數”](# "7.5.2.?Tuning Server Parameters")。 ·???????? 總是檢查所有查詢確實使用已經在表中創建了的索引。在**MySQL**中，可以用EXPLAIN命令做到。參見[7.2.1節，“EXPLAIN語法（獲取關于SELECT的信息）``”](# "7.2.1.?EXPLAIN Syntax (Get Information About a SELECT)")。 ·???????? 嘗試避免在頻繁更新的表上執行復雜的SELECT查詢，以避免與鎖定表有關的由于讀、寫沖突發生的問題。 ·???????? 對于沒有刪除的行的MyISAM表，可以在另一個查詢正從表中讀取的同時在末尾插入行。如果這很重要，應考慮按照避免刪除行的方式使用表。另一個可能性是在刪除大量行后運行OPTIMIZE TABLE。參見[15.1節，“MyISAM存儲引擎”](# "15.1.?The MyISAM Storage Engine")。 ·???????? 要修復任何ARCHIVE表可以發生的壓縮問題，可以執行OPTIMIZE TABLE。參見[15.8節，“ARCHIVE存儲引擎”](# "15.8.?The ARCHIVE Storage Engine")。 ·???????? 如果你主要按*expr1*，*expr2*，...順序檢索行，使用ALTER TABLE ... ORDER BY *expr1*, *expr2*, ...。對表大量更改后使用該選項，可以獲得更好的性能。 ·???????? 在一些情況下，使得基于來自其它表的列的信息引入一個“ 哈希”的列有意義。如果該列較短并且有合理的唯一值，它可以比在許多列上的一個大索引快些。在**MySQL**中，很容易使用這個額外列： ~~~ ·??????????????? SELECT * FROM tbl_name ~~~ ~~~ ·??????????????? ?????WHERE hash_col=MD5(CONCAT(col1,col2)) ~~~ ~~~ ·??????????????? ?????AND col1='constant' AND col2='constant'; ~~~ ·???????? 對于頻繁更改的MyISAM表，應試圖避免所有變長列(VARCHAR、BLOB和TEXT)。如果表包括單一的變長列則使用動態記錄格式。參見[第15章：](#)[*存儲引擎和表類型*](# "Chapter?15.?Storage Engines and Table Types")。 ·???????? 只是因為行太大，將一張表分割為不同的表一般沒有什么用處。為了訪問行，最大的性能沖擊是磁盤搜索以找到行的第一個字節。在找到數據后，大多數新型磁盤對大多數應用程序來說足夠快，能讀入整個行。確實有必要分割的唯一情形是如果它是使用動態記錄格式使之變為固定的記錄大小的MyISAM表(見上述)，或如果你需要很頻繁地掃描表而不需要大多數列。參見[第15章：](#)[*存儲引擎和表類型*](# "Chapter?15.?Storage Engines and Table Types")。 ·???????? 如果你需要很經常地計算結果，例如基于來自很多行的信息的計數，引入一個新表并實時更新計數器可能更好一些。下面形式的更新會更快一些： ~~~ UPDATE tbl_name SET count_col=count_col+1 WHERE key_col=constant; ~~~ 當你使用象MyISAM那樣的只有表級鎖定的**MySQL存儲引擎**(多重讀/單個寫)時，這確實很重要。這也給大多數數據庫較好的性能，因為行鎖定管理器在這種情況下有較少的事情要做。 ·???????? 如果你需要從大的記錄文件表中收集統計信息，使用總結性的表而不是掃描整個表。維護總結應該比嘗試做“實時”統計要快些。當有變化時從日志重新生成新的總結表比改變運行的應用(取決于業務決策)要快得多。 - 如果可能，應該將報告分類為“實時”或“統計”，這里統計報告所需的數據僅僅基于從實際數據定期產生的總結表中產生。 - 充分利用列有默認值的事實。只有當插入的值不同于默認值時，才明確地插入值。這減少MySQL需要做的語法分析從而提高插入速度。 - 在一些情況下，包裝并存儲數據到一個BLOB列中是很方便的。在這種情況下，必須在你的應用中增加額外的代碼來打包/解包信息，但是這種方法可以在某些階段節省很多訪問。當有不符合行和列表結構的數據時，這很實用。 - 在一般情況下，應該嘗試以非冗余方式(查看數據庫理論中的*第三正則形式*)保存數據，但是為了獲得更快的速度，可以復制信息或創建總結表。 - 存儲過程或UDF(用戶定義函數)可能是獲得更好性能的一個好方法，詳細信息參見[第20章：](#)[*存儲程序和函數*](# "Chapter?20.?Stored Procedures and Functions")和[27.2節，“為MySQL添加新函數”](# "27.2.?Adding New Functions to MySQL")。 - 總是能通過在應用程序中緩存查詢/答案并嘗試同時執行很多插入/更新來獲得一些好處。如果數據庫支持鎖定表(象**MySQL**和Oracle)，這應該有助于確保索引緩存只在所有更新后刷新一次。還可以利用MySQL的查詢緩存來獲得類似的結果；參見[5.13節，“MySQL查詢高速緩沖”](# "5.13.?The MySQL Query Cache")。 - 當不需要知道何時寫入數據時，使用INSERT DELAYED。這樣可以加快處理，因為很多記錄可以通過一次磁盤寫入被寫入。 - 當你想要讓選擇顯得更重要時，使用INSERT /*! LOW_PRIORITY */。 - 使用INSERT LOW_PRIORITY來取得插入隊列的檢索，也就是即使有另一個客戶等待寫入也要執行SELECT。 - 使用多行INSERT語句通過一個SQL命令來存儲很多行(許多SQL服務器支持它，包括MySQL)。 - 使用LOAD DATA INFILE裝載較大數量的數據。這比使用INSERT要快得多。 - 使用AUTO_INCREMENT列構成唯一值。 - 當MyISAM使用動態表格式時，偶爾使用OPTIMIZE TABLE可以避免碎片。參見[15.1.3節，“MyISAM表的存儲格式”](# "15.1.3.?MyISAM Table Storage Formats")。 - 可能時使用MEMORY表以得到更快的速度。[參見](#)[15.4節，“MEMORY (HEAP)存儲引擎”](# "15.4.?The MEMORY (HEAP) Storage Engine")。 - 在Web服務器中，圖象和其它二進制資產應該作為文件存儲。也就是僅在數據庫中存儲的本文件的引用而不是文件本身。大多數Web服務器在緩存文件方面比數據庫內容要好得多，因此使用文件一般要快得多。 - 對經常訪問的不重要數據(如為沒有在Web 瀏覽器中啟用cookie的用戶最后顯示的標語的相關信息)使用內存表。在許多Web應用程序環境中也可以使用用戶會話來處理可變狀態數據。 - 在不同表中具有相同信息的列應該被聲明為相同的并有相同的名字。嘗試使名字簡單化。例如，在customer表中使用name而不是customer_name。為了使名字能移植到其它SQL服務器，應該使名字短于18個字符。 - 如果確實需要很高的速度，應該研究一下不同SQL服務器支持的數據存儲的低層接口！例如直接訪問**MySQL**MyISAM存儲引擎，比起使用SQL接口，速度可以提高2-5倍。為了能實現，數據必須與應用程序在同一臺服務器上，并且通常只應該被一個進程訪問(因為外部文件鎖定確實很慢)。通過在**MySQL**服務器中引進低層MyISAM命令能消除以上問題(如果需要，這可能是獲得更好性能的一個簡單的方法)。通過精心設計數據庫接口，應該能相當容易地支持這類優化。 - 如果正使用數字數據，在許多情況下，從一個數據庫訪問信息(使用實時連接)比訪問一個文本文件快些。這是因為數據庫中的信息比文本文件更緊湊，因此這將涉及更少的磁盤訪問。還可以在應用程序中節省代碼，因為不須分析文本文件來找出行和列的邊界。 ·???????? 通過復制可以提高某些操作的性能。可以在復制服務器中分布客戶的檢索以均分負載。為了防止備份時主服務器變慢，可以使用一個從服務器來備份。參見[第6章：](#)[*MySQL中的復制*](# "Chapter?6.?Replication in MySQL")。 ·???????? 用DELAY_KEY_WRITE=1選項聲明MyISAM表可以使索引更新更快，因為在表關閉之前它們不刷新到硬盤上。不利之處是當表打開時如果殺掉服務器，應確保用--myisam-recover選項運行服務器保證沒有問題，或者在重啟服務器之前運行**myisamchk**。(然而，即使在這種情況下，應通過使用DELAY_KEY_WRITE保證不丟失數據，因為關鍵字信息總是可以從數據行產生）。 ? ### 7.3.?鎖定事宜 [ 7.3.1. 鎖定方法](#)[ 7.3.2. 表鎖定事宜](#) ### 7.3.1.?鎖定方法 MySQL 5.1支持對MyISAM和MEMORY表進行表級鎖定，對BDB表進行頁級鎖定，對InnoDB表進行行級鎖定。在許多情況下，可以根據培訓猜測應用程序使用哪類鎖定類型最好，但一般很難說出某個給出的鎖類型就比另一個好。一切取決于應用程序，應用程序的不同部分可能需要不同的鎖類型。為了確定是否想要使用行級鎖定的存儲引擎，應看看應用程序做什么并且混合使用什么樣的選擇和更新語句。例如，大多數Web應用程序執行許多選擇，而很少進行刪除，只對關鍵字的值進行更新，并且只插入少量具體的表。基本MySQL MyISAM設置已經調節得很好。在MySQL中對于使用表級鎖定的存儲引擎，表鎖定時不會死鎖的。這通過總是在一個查詢開始時立即請求所有必要的鎖定并且總是以同樣的順序鎖定表來管理。對WRITE，**MySQL**使用的表鎖定方法原理如下： - 如果在表上沒有鎖，在它上面放一個寫鎖。 - 否則，把鎖定請求放在寫鎖定隊列中。對READ，**MySQL**使用的鎖定方法原理如下： - 如果在表上沒有寫鎖定，把一個讀鎖定放在它上面。 - 否則，把鎖請求放在讀鎖定隊列中。當一個鎖定被釋放時，鎖定可被寫鎖定隊列中的線程得到，然后是讀鎖定隊列中的線程。這意味著，如果你在一個表上有許多更新，SELECT語句將等待直到沒有更多的更新。可以通過檢查table_locks_waited和table_locks_immediate狀態變量來分析系統上的表鎖定爭奪： mysql> **SHOW STATUS LIKE 'Table%';** +-----------------------+---------+ | Variable_name???????? | Value?? | +-----------------------+---------+ | Table_locks_immediate | 1151552 | | Table_locks_waited??? | 15324?? | +-----------------------+---------+ 如果INSERT語句不沖突，可以自由為MyISAM表混合并行的INSERT和SELECT語句而不需要鎖定。也就是說，你可以在其它客戶正讀取MyISAM表的時候插入行。如果數據文件中間不包含空閑塊，不會發生沖突，因為在這種情況下，記錄總是插入在數據文件的尾部。(從表的中部刪除或更新的行可能導致空洞）。如果有空洞，當所有空洞填入新的數據時，并行的插入能夠重新自動啟用。如果不能同時插入，為了在一個表中進行多次INSERT和SELECT操作，可以在臨時表中插入行并且立即用臨時表中的記錄更新真正的表。這可用下列代碼做到： mysql> **LOCK TABLES real_table WRITE, insert_table WRITE;** mysql> **INSERT INTO real_table SELECT * FROM insert_table;** mysql> **TRUNCATE TABLE insert_table;** mysql> **UNLOCK TABLES;** ~~~ ? ~~~ InnoDB使用行鎖定，BDB使用頁鎖定。對于這兩種存儲引擎，都可能存在死鎖。這是因為，在SQL語句處理期間，InnoDB自動獲得行鎖定和BDB獲得頁鎖定，而不是在事務啟動時獲得。行級鎖定的優點： ·???????? 當在許多線程中訪問不同的行時只存在少量鎖定沖突。 ·???????? 回滾時只有少量的更改。 ·???????? 可以長時間鎖定單一的行。行級鎖定的缺點： ·???????? 比頁級或表級鎖定占用更多的內存。 ·???????? 當在表的大部分中使用時，比頁級或表級鎖定速度慢，因為你必須獲取更多的鎖。 ·???????? 如果你在大部分數據上經常進行GROUP BY操作或者必須經常掃描整個表，比其它鎖定明顯慢很多。 ·???????? 用高級別鎖定，通過支持不同的類型鎖定，你也可以很容易地調節應用程序，因為其鎖成本小于行級鎖定。在以下情況下，表鎖定優先于頁級或行級鎖定： ·???????? 表的大部分語句用于讀取。 ·???????? 對嚴格的關鍵字進行讀取和更新，你可以更新或刪除可以用單一的讀取的關鍵字來提取的一行： ~~~ ·??????????????? UPDATE tbl_name SET column=value WHERE unique_key_col=key_value; ~~~ ~~~ ·??????????????? DELETE FROM tbl_name WHERE unique_key_col=key_value; ~~~ ·???????? SELECT結合并行的INSERT語句，并且只有很少的UPDATE或DELETE語句。 ·???????? 在整個表上有許多掃描或GROUP BY操作，沒有任何寫操作。不同于行級或頁級鎖定的選項： ·???????? 版本(例如，為并行的插入在MySQL中使用的技術)，其中可以一個寫操作，同時有許多讀取操作。這說明數據庫或表支持數據依賴的不同視圖，取決于訪問何時開始。其它共同的術語是“時間跟蹤”、“寫復制”或者“按需復制”。 ·???????? 按需復制在許多情況下優先于頁級或行級鎖定。然而，在最壞的情況下，它可能比使用常規鎖定使用更多的內存。 ·???????? 除了行級鎖定外，你可以使用應用程序級鎖定，例如在MySQL中使用GET_LOCK()和RELEASE_LOCK()。這些是建議性鎖定，它們只能在運行良好的應用程序中工作。 ### 7.3.2.?表鎖定事宜為達到最高鎖定速度，除InnoDB和BDB之外，對所有存儲引擎，MySQL使用表鎖定(而不是頁、行或者列鎖定)。對于InnoDB和BDB表，如果你用LOCK TABLES顯式鎖定表，MySQL只使用表鎖定。對于這些表類型，我們建議你根本不要使用LOCK TABLES，因為InnoDB使用自動行級鎖定而BDB使用頁級鎖定來保證事務隔離。對于大表，對于大多數應用程序，表鎖定比行鎖定更好，但存在部分缺陷。表鎖定使許多線程同時從一個表中進行讀取操作，但如果一個線程想要對表進行寫操作，它必須首先獲得獨占訪問。更新期間，所有其它想要訪問該表的線程必須等待直到更新完成。表更新通常情況認為比表檢索更重要，因此給予它們更高的優先級。這應確保更新一個表的活動不能“餓死”，即使該表上有很繁重的SELECT活動。表鎖定在這種情況下會造成問題，例如當線程正等待，因為硬盤已滿并且在線程可以處理之前必須有空閑空間。在這種情況下，所有想要訪問出現問題的表的線程也被設置成等待狀態，直到有更多的硬盤空間可用。表鎖定在下面的情況下也存在問題： ·???????? 一個客戶發出長時間運行的查詢。 ·???????? 然后，另一個客戶對同一個表進行更新。該客戶必須等待直到SELECT完成。 ·???????? 另一個客戶對同一個表上發出了另一個SELECT語句。因為UPDATE比SELECT優先級高，該SELECT語句等待UPDATE完成，并且等待第1個SELECT完成。下面描述了一些方法來避免或減少表鎖定造成的競爭： ·???????? 試圖使SELECT語句運行得更快。你可能必須創建一些摘要(summary)表做到這點。 ·???????? 用--low-priority-updates啟動**mysqld**。這將給所有更新(修改)一個表的語句以比SELECT語句低的優先級。在這種情況下，在先前情形的第2個SELECT語句將在UPDATE語句前執行，而不需要等候第1個SELECT完成。 ·???????? 可以使用SET LOW_PRIORITY_UPDATES=1語句指定具體連接中的所有更新應使用低優先級。參見[13.5.3節，“SET語法”](# "13.5.3.?SET Syntax") ·???????? 可以用LOW_PRIORITY屬性給與一個特定的INSERT、UPDATE或DELETE語句較低優先級。 ·???????? 可以用HIGH_PRIORITY屬性給與一個特定的SELECT語句較高優先級。參見[13.2.7節，“SELECT語法”](# "13.2.7.?SELECT Syntax")。 ·???????? 為**max_write_lock_count**系統變量指定一個低值來啟動**mysqld**來強制MySQL在具體數量的插入完成后臨時提高所有等待一個表的SELECT語句的優先級。這樣允許在一定數量的WRITE鎖定后給出READ鎖定。 ·???????? 如果你有關于INSERT結合SELECT的問題，切換到使用新的MyISAM表，因為它們支持并發的SELECT和INSERT。 ·???????? 如果你對同一個表混合插入和刪除，INSERT DELAYED將會有很大的幫助。參見[13.2.4.2節，“INSERT DELAYED語法”](# "13.2.4.2.?INSERT DELAYED Syntax")。 ·???????? 如果你對同一個表混合使用SELECT和DELETE語句出現問題，DELETE的LIMIT選項可以有所幫助。參見[13.2.1節，“DELETE語法”](# "13.2.1.?DELETE Syntax")。 ·???????? 對SELECT語句使用SQL_BUFFER_RESULT可以幫助使表鎖定時間變短。參見[13.2.7節，“SELECT語法”](# "13.2.7.?SELECT Syntax")。 ·???????? 可以更改mysys/thr_lock.c中的鎖代碼以使用單一的隊列。在這種情況下，寫鎖定和讀鎖定將具有相同的優先級，對一些應用程序會有幫助。這里是一些MySQL中表鎖定相關的技巧： ·???????? 如果不混合更新與需要在同一個表中檢查許多行的選擇，可以進行并行操作。 ·???????? 可以使用LOCK TABLES來提高速度，因為在一個鎖定中進行許多更新比沒有鎖定的更新要快得多。將表中的內容切分為幾個表也可以有所幫助。 ·???????? 如果在MySQL中表鎖定時遇到速度問題，可以將你的表轉換為InnoDB或BDB表來提高性能。參見[15.2節，“InnoDB存儲引擎”](# "15.2.?The InnoDB Storage Engine")和[15.5節，“BDB (BerkeleyDB)存儲引擎”](# "15.5.?The BDB (BerkeleyDB) Storage Engine")。 ### 7.4.?優化數據庫結構 [ 7.4.1. 設計選擇](#)[ 7.4.2. 使你的數據盡可能小](#)[ 7.4.3. 列索引](#)[ 7.4.4. 多列索引](#)[ 7.4.5. MySQL如何使用索引](#)[ 7.4.6. MyISAM鍵高速緩沖](#)[7.4.7. MyISAM索引統計集合](#)[ 7.4.8. MySQL如何計算打開的表](#)[ 7.4.9. MySQL如何打開和關閉表](#)[ 7.4.10. 在同一個數據庫中創建多個表的缺陷](#) ### 7.4.1.?設計選擇 MySQL將行數據和索引數據保存在不同的文件中。許多(幾乎所有)其它數據庫將行數據和索引數據混合保存在用一個文件中。我們認為MySQL 選擇對廣范圍的現代系統更好一些。保存行數據的另一種方式是將每個列的信息保存在單獨的區域(例如SDBM和Focus)。這樣會對每個訪問多個列的查詢造成性能問題。因為當訪問多個列時退化得很快，我們認為該模型對一般數據庫不合適。更常見的情形是索引和數據保存在一起(例如Oracle/Sybase)。在這種情況下，你可以在索引的葉級頁找到行的信息。該布局比較好的事情是在許多情況下，根據索引緩存得怎樣，可以保存一個硬盤讀取。該布局的不利之處表現在： ·???????? 表掃描要慢得多，因為你必須通讀索引以獲得數據。 ·???????? 你不能只使用表來檢索查詢的數據。 ·???????? 你需要使用更多的空間，因為你必須從節點復制索引(你不能保存節點上的行)。 ·???????? 刪除要經過一段時間后才退化表(因為刪除時通常不會更新節點上的索引)。 ·???????? 只緩存索引數據會更加困難。 ### 7.4.2.?使你的數據盡可能小最基本的優化之一是使表在磁盤上占據的空間盡可能小。這能給出巨大的改進，因為磁盤讀入較快，并且在查詢執行過程中小表的內容被處理時占用較少的主存儲器。如果在更小的列上做索引，索引也占據較少的資源。 MySQL支持許多不同的存儲引擎(表類型)和行格式。對于每個表，可以確定使用哪個存儲引擎和索引方法。為應用程序選擇合適的表格式可以大大提高性能。參見[第15章：](#)[*存儲引擎和表類型*](# "Chapter?15.?Storage Engines and Table Types")。可以使用下面的技術可以使表的性能更好并且使存儲空間最小： - 盡可能地使用最有效(最小)的數據類型。**MySQL**有很多節省磁盤空間和內存的專業化類型。 - 盡可能使用較小的整數類型使表更小。例如，MEDIUMINT經常比INT好一些，因為MEDIUMINT列使用的空間要少25%。 - 如果可能，聲明列為NOT NULL。它使任何事情更快而且每列可以節省一位。注意如果在應用程序中確實需要NULL，應該毫無疑問使用它，只是避免默認地在所有列上有它。 - 對于MyISAM表，如果沒有任何變長列(VARCHAR、TEXT或BLOB列)，使用固定尺寸的記錄格式。這比較快但是不幸地可能會浪費一些空間。參見[15.1.3節，“MyISAM表的存儲格式”](# "15.1.3.?MyISAM Table Storage Formats")。即使你已經用CREATE選項讓VARCHAR列ROW_FORMAT=fixed，也可以提示想使用固定長度的行。 - 在MySQL/InnoDB中，InnoDB表使用更緊湊的存儲格式。在以前版本的MySQL中，InnoDB記錄包含一些冗余信息，例如列數目和每個列的長度，即使對于固定大小的列。默認情況，創建的表為緊湊格式(ROW_FORMAT=COMPACT)。如果想要降級舊版本的MySQL/InnoDB，可以用ROW_FORMAT=REDUNDANT要求舊的格式。 - 緊湊的InnoDB格式也改變了包含UTF-8數據的CHAR列的保存方式。在ROW_FORMAT=REDUNDANT格式中，UTF-8 CHAR(*n*)占用3**n*字節，假定UTF-8編碼的字符的最大長度是3字節。許多語言可以主要用單字節UTF-8字符來編寫，固定的存儲長度通常會浪費空間。通過根據需要剝離尾部的空格，ROW_FORMAT=COMPACT格式為這些列分配可變數量的n..3*n字節。最小存儲長度按順序保存為n字節，以在典型情況下幫助更新。 - 每張表的主索引應該盡可能短。這使一行的識別容易而有效。 - 只創建你確實需要的索引。索引對檢索有好處，但是當你需要快速存儲東西時就變得糟糕。如果主要通過搜索列的組合來存取一個表，對它們做一個索引。第一個索引部分應該是最常用的列。如果從表中選擇時總是使用許多列，應該首先以更多的副本使用列以獲得更好的索引壓縮。 - 如果很可能一個索引在頭幾個字符上有唯一的前綴，僅僅索引該前綴比較好。**MySQL**支持對一個字符列的最左邊部分創建一個索引(參見[13.1.4節，“CREATE INDEX語法”](# "13.1.4.?CREATE INDEX Syntax"))。更短的索引會更快，不僅因為它們占較少的磁盤空間，而且因為它們將在索引緩存中提供更多的訪問，因此磁盤搜索更少。參見[7.5.2節，“調節服務器參數”](# "7.5.2.?Tuning Server Parameters")。 ·???????? 在一些情形下，將一個經常被掃描的表分割為2個表是有益的。特別是如果它是一個動態格式的表，并且可能使用一個掃描表時能用來找出相關行的較小靜態格式的表。 ### 7.4.3.?列索引所有MySQL列類型可以被索引。對相關列使用索引是提高SELECT操作性能的最佳途徑。根據存儲引擎定義每個表的最大索引數和最大索引長度。參見[第15章：](#)[*存儲引擎和表類型*](# "Chapter?15.?Storage Engines and Table Types")。所有存儲引擎支持每個表至少16個索引，總索引長度至少為256字節。大多數存儲引擎有更高的限制。在索引定義中用*col_name*(*length*)語法，你可以創建一個只使用CHAR或VARCHAR列的第1個*length*字符的索引。按這種方式只索引列值的前綴可以使索引文件小得多。 MyISAM和InnoDB存儲引擎還支持對BLOB和TEXT列的索引。當索引一個BLOB或TEXT列時，你*必須*為索引指定前綴長度。例如： ~~~ CREATE TABLE test (blob_col BLOB, INDEX(blob_col(10))); ~~~ 在MySQL 5.1中，對于MyISAM和InnoDB表，前綴可以達到1000字節長。請注意前綴的限制應以字節為單位進行測量，而CREATE TABLE語句中的前綴長度解釋為字符數。*當為使用多字節字符集的列指定前綴長度時一定要加以考慮*。還可以創建FULLTEXT索引。該索引可以用于全文搜索。只有MyISAM存儲引擎支持FULLTEXT索引，并且只為CHAR、VARCHAR和TEXT列。索引總是對整個列進行，不支持局部(前綴)索引。詳情參見[12.7節，“全文搜索功能”](# "12.7.?Full-Text Search Functions")。也可以為空間列類型創建索引。只有MyISAM存儲引擎支持空間類型。空間索引使用R-樹。默認情況MEMORY(HEAP)存儲引擎使用hash索引，但也支持B-樹索引。 ### 7.4.4.?多列索引 MySQL可以為多個列創建索引。一個索引可以包括15個列。對于某些列類型，可以索引列的前綴(參見[7.4.3節，“列索引”](# "7.4.3.?Column Indexes"))。多列索引可以視為包含通過連接索引列的值而創建的值的排序的數組。 MySQL按這樣的方式使用多列索引：當你在WHERE子句中為索引的第1個列指定已知的數量時，查詢很快，即使你沒有指定其它列的值。假定表具有下面的結構： ~~~ CREATE TABLE test ( ~~~ ~~~ ??? id INT NOT NULL, ~~~ ~~~ ??? last_name CHAR(30) NOT NULL, ~~~ ~~~ ??? first_name CHAR(30) NOT NULL, ~~~ ~~~ ??? PRIMARY KEY (id), ~~~ ~~~ ??? INDEX name (last_name,first_name) ~~~ ~~~ ); ~~~ name索引是一個對last_name和first_name的索引。索引可以用于為last_name，或者為last_name和first_name在已知范圍內指定值的查詢。因此，name索引用于下面的查詢： ~~~ SELECT * FROM test WHERE last_name='Widenius'; ~~~ ~~~ ? ~~~ ~~~ SELECT * FROM test ~~~ ~~~ ??? WHERE last_name='Widenius' AND first_name='Michael'; ~~~ ~~~ ? ~~~ ~~~ SELECT * FROM test ~~~ ~~~ ??? WHERE last_name='Widenius' ~~~ ~~~ ??? AND (first_name='Michael' OR first_name='Monty'); ~~~ ~~~ ? ~~~ ~~~ SELECT * FROM test ~~~ ~~~ ??? WHERE last_name='Widenius' ~~~ ~~~ ??? AND first_name >='M' AND first_name < 'N'; ~~~ 然而，name索引*不*用于下面的查詢： ~~~ SELECT * FROM test WHERE first_name='Michael'; ~~~ ~~~ ? ~~~ ~~~ SELECT * FROM test ~~~ ~~~ ??? WHERE last_name='Widenius' OR first_name='Michael'; ~~~ MySQL使用索引提高查詢性能的方式將在[7.4.5節，“MySQL如何使用索引”](# "7.4.5.?How MySQL Uses Indexes")中討論。 ### 7.4.5.?MySQL如何使用索引索引用于快速找出在某個列中有一特定值的行。不使用索引，MySQL必須從第1條記錄開始然后讀完整個表直到找出相關的行。表越大，花費的時間越多。如果表中查詢的列有一個索引，MySQL能快速到達一個位置去搜尋到數據文件的中間，沒有必要看所有數據。如果一個表有1000行，這比順序讀取至少快100倍。注意如果你需要訪問大部分行，順序讀取要快得多，因為此時我們避免磁盤搜索。大多數MySQL索引(PRIMARY KEY、UNIQUE、INDEX和FULLTEXT)在B樹中存儲。只是空間列類型的索引使用R-樹，并且MEMORY表還支持hash索引。字符串自動地壓縮前綴和結尾空格。參見[13.1.4節，“CREATE INDEX語法”](# "13.1.4.?CREATE INDEX Syntax")。總的來說，按后面的討論使用索引。本節最后描述hash索引(用于MEMORY表)的特征。索引用于下面的操作： ·???????? 快速找出匹配一個WHERE子句的行。 ·???????? 刪除行。如果可以在多個索引中進行選擇，MySQL通常使用找到最少行的索引。 ·???????? 當執行聯接時，從其它表檢索行。 ·???????? 對具體有索引的列*key_col*找出MAX()或MIN()值。由預處理器進行優化，檢查是否對索引中在key_*col*之前發生所有關鍵字元素使用了WHERE *key_part_#* = *constant*。在這種情況下，MySQL為每個MIN()或MAX()表達式執行一次關鍵字查找，并用常數替換它。如果所有表達式替換為常量，查詢立即返回。例如： ~~~ ·??????????????? SELECT MIN(key_part2),MAX(key_part2) ~~~ ~~~ ·??????????????? ????FROM tbl_name WHERE key_part1=10; ~~~ ·???????? 如果對一個可用關鍵字的最左面的前綴進行了排序或分組(例如，ORDER BY key_part_1,key_part_2)，排序或分組一個表。如果所有關鍵字元素后面有DESC，關鍵字以倒序被讀取。參見[7.2.12節，“MySQL如何優化ORDER BY``”](# "7.2.12.?How MySQL Optimizes ORDER BY")。 ·???????? 在一些情況中，可以對一個查詢進行優化以便不用查詢數據行即可以檢索值。如果查詢只使用來自某個表的數字型并且構成某些關鍵字的最左面前綴的列，為了更快，可以從索引樹檢索出值。 ~~~ ·??????????????? SELECT key_part3 FROM tbl_name ~~~ ~~~ ·??????????????? ????WHERE key_part1=1 ~~~ 假定你執行下面的SELECT語句： ~~~ mysql> SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2; ~~~ 如果col1和col2上存在一個多列索引，可以直接取出相應行。如果col1和col2上存在單列索引，優化器試圖通過決定哪個索引將找到更少的行來找出更具限制性的索引并且使用該索引取行。如果表有一個多列索引，優化器可以使用最左面的索引前綴來找出行。例如，如果有一個3列索引(col1,col2,col3)，則已經對(col1)、(col1,col2)和(col1,col2,col3)上的搜索進行了索引。如果列不構成索引最左面的前綴，**MySQL**不能使用局部索引。假定有下面顯示的SELECT語句。 ~~~ ? ~~~ ~~~ SELECT * FROM tbl_name WHERE col1=val1; ~~~ ~~~ SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2; ~~~ ~~~ ? ~~~ ~~~ SELECT * FROM tbl_name WHERE col2=val2; ~~~ ~~~ SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3; ~~~ ~~~ ? ~~~ 如果 (col1，col2，col3)有一個索引，只有前2個查詢使用索引。第3個和第4個查詢確實包括索引的列，但(col2)和(col2，col3)不是 (col1，col2，col3)的最左邊的前綴。也可以在表達式通過=、>、>=、<、<=或者BETWEEN操作符使用B-樹索引進行列比較。如果LIKE的參數是一個不以通配符開頭的常量字符串，索引也可以用于LIKE比較。例如，下面的SELECT語句使用索引： ~~~ SELECT * FROM tbl_name WHERE key_col LIKE 'Patrick%'; ~~~ ~~~ SELECT * FROM tbl_name WHERE key_col LIKE 'Pat%_ck%'; ~~~ 在第1個語句中，只考慮帶'Patrick' <=key_*col* < 'Patricl'的行。在第2個語句中，只考慮帶'Pat' <=key_*col* < 'Pau'的行。下面的SELECT語句不使用索引： ~~~ SELECT * FROM tbl_name WHERE key_col LIKE '%Patrick%'; ~~~ ~~~ SELECT * FROM tbl_name WHERE key_col LIKE other_col; ~~~ 在第一條語句中，LIKE值以一個通配符字符開始。在第二條語句中，LIKE值不是一個常數。如果使用... LIKE '%*string*%'并且*string*超過3個字符，MySQL使用*Turbo Boyer-Moore算法*初始化字符串的模式然后使用該模式來更快地進行搜索。如果*col_name*被索引，使用*col_name* IS NULL的搜索將使用索引。任何不跨越WHERE子句中的所有AND級的索引不用于優化查詢。換句話說，為了能夠使用索引，必須在每個AND組中使用索引前綴。下面的WHERE子句使用索引： ~~~ ... WHERE index_part1=1 AND index_part2=2 AND other_column=3 ~~~ ~~~ ??? /* index = 1 OR index = 2 */ ~~~ ~~~ ... WHERE index=1 OR A=10 AND index=2 ~~~ ~~~ ??? /* optimized like "index_part1='hello'" */ ~~~ ~~~ ... WHERE index_part1='hello' AND index_part3=5 ~~~ ~~~ ??? /* Can use index on index1 but not on index2 or index3 */ ~~~ ~~~ ... WHERE index1=1 AND index2=2 OR index1=3 AND index3=3; ~~~ 下面的WHERE子句不使用索引： ~~~ ??? /* index_part1 is not used */ ~~~ ~~~ ... WHERE index_part2=1 AND index_part3=2 ~~~ ~~~ ? ~~~ ~~~ ??? /*? Index is not used in both parts of the WHERE clause? */ ~~~ ~~~ ... WHERE index=1 OR A=10 ~~~ ~~~ ? ~~~ ~~~ ??? /* No index spans all rows? */ ~~~ ~~~ ... WHERE index_part1=1 OR index_part2=10 ~~~ 有時MySQL不使用索引，即使有可用的索引。一種情形是當優化器估計到使用索引將需要MySQL訪問表中的大部分行時。(在這種情況下，表掃描可能會更快些，因為需要的搜索要少）。然而，如果此類查詢使用LIMIT只搜索部分行，MySQL則使用索引，因為它可以更快地找到幾行并在結果中返回。 Hash索引還有一些其它特征： ·???????? 它們只用于使用=或<=>操作符的等式比較(但*很快*)。它們用于比較操作符，例如發現范圍值的<。 ·???????? 優化器不能使用hash索引來加速ORDER BY操作。(該類索引不能用來按順序搜索下一個條目）。 ·???????? MySQL不能確定在兩個值之間大約有多少行(這被范圍優化器用來確定使用哪個索引)。如果你將一個MyISAM表改為hash-索引的MEMORY表，會影響一些查詢。 ·???????? 只能使用整個關鍵字來搜索一行。(用B-樹索引，任何關鍵字的最左面的前綴可用來找到行）。 ### 7.4.6.?MyISAM鍵高速緩沖 [ 7.4.6.1. 共享鍵高速緩沖訪問](#)[ 7.4.6.2. 多鍵高速緩沖](#)[ 7.4.6.3. 中點插入策略](#)[ 7.4.6.4. 索引預加載](#)[ 7.4.6.5. 鍵高速緩沖塊大小](#)[ 7.4.6.6. 重構鍵高速緩沖](#) 為了使硬盤I/O最小化，MyISAM存儲引擎使用一個被許多數據庫管理系統使用的策略。它使用一個緩存機制將經常訪問的表鎖在內存中： ·???????? 對于索引塊，維護一個稱之為*鍵高速*緩沖**(或*鍵高速緩沖區*)的特殊結構。該結構包含大量塊緩存區，其中放置了最常用的索引塊。 ·???????? 對于數據塊，MySQL不使用特殊緩存。而使用原生的操作系統文件系統的緩存。本節首先描述了MyISAM鍵高速緩沖的基本操作。然后討論了提高鍵高速緩沖性能并使你更好地控制緩存操作的最新的更改： ·???????? 多個線程可以并行訪問緩存。 ·???????? 可以設置多個鍵高速緩沖，并將表索引指定給具體緩存。可以使用key_buffer_size系統變量控制鍵高速緩沖的大小。如果該變量設置為零，不使用鍵高速緩沖。如果key_buffer_size值太小不能分配最小數量的塊緩存區(8)，也不使用鍵高速緩沖。如果鍵高速緩沖不工作，只使用操作系統提供的原生文件系統緩存區訪問索引文件。(換句話說，使用與表數據塊相同的策略表來訪問索引塊）。索引塊是一個連續的訪問MyISAM索引文件的單位。通常一個索引塊的大小等于索引B-樹節點的大小。(在硬盤上使用B-樹數據結構表示索引。樹底部的節點為葉子節點。葉子節點上面的節點為非葉子節點）。鍵高速緩沖結構中的所有塊緩存區大小相同。該大小可以等于、大于或小于表索引塊的大小。通常這兩個值中的一個是另一個的幾倍。當必須訪問表索引塊中的數據時，服務器首先檢查是否它可以用于鍵高速緩沖中的某些塊緩存區。如果適用，服務器訪問鍵高速緩沖中的數據而不是硬盤上的數據。也就是說，從緩存讀取或寫入緩存，而不是從硬盤讀寫。否則，服務器選擇一個包含一個不同的表索引塊的緩存塊緩存區，并用需要的表索引塊的拷貝替換那里的數據。一旦新的索引塊位于緩存中，可以訪問索引數據。如果用于替換的塊已經被修改了，塊被視為“臟了”。在這種情況下，在替換前，其內容被刷新到它來自的表索引。通常服務器遵從*LRU(最近最少使用)*策略：當選擇一個塊用于替換時，它選擇最近最少使用的索引塊。為了使該選擇更容易，鍵高速緩沖模塊維護所有使用的塊的專門隊列(*LRU**鏈*)。當訪問塊時，它被放到隊列最后。當塊需要替換時，隊列開頭的塊是最近最少使用的塊，并成為第1個候選者。 #### 7.4.6.1.?共享鍵高速緩沖訪問在以下條件下，線程可以同時訪問鍵高速緩沖緩存區： ·???????? 沒有被更新的緩存區可以被多個線程訪問。 ·???????? 正被更新的緩存區讓需要使用它的線程等待直到更新完成。 ·???????? 多個線程可以發起請求替換緩存塊，只要它們不彼此干擾(也就是說，只要它們需要不同的索引塊，并且使不同的緩存塊被替換)。對鍵高速緩沖的共享訪問允許服務器大大提高吞吐量。 #### 7.4.6.2.?多鍵高速緩沖對鍵高速緩沖的共享訪問可以提高性能但不能完全消除線程之間的竟爭。它們仍然競爭對鍵高速緩沖緩存區的訪問進行管理的控制結構。為了進一步降低鍵高速緩沖訪問竟爭，MySQL 5.1還提供了多個鍵高速緩沖，允許你為不同的鍵高速緩沖分配不同的表索引。有多個鍵高速緩沖時，當為給定的MyISAM表處理查詢時，服務器必須知道使用哪個緩存。默認情況，所有MyISAM表索引被緩存到默認鍵高速緩沖中。要想為具體鍵高速緩沖分配表索引，應使用CACHE INDEX語句(參見[13.5.5.1節，“CACHE INDEX語法”](# "13.5.5.1.?CACHE INDEX Syntax"))。例如，下面的語句將表t1、t2和t3的索引分配給名為hot_cache的鍵高速緩沖： ~~~ mysql> CACHE INDEX t1, t2, t3 IN hot_cache; ~~~ ~~~ +---------+--------------------+----------+----------+ ~~~ ~~~ | Table?? | Op???????????????? | Msg_type | Msg_text | ~~~ ~~~ +---------+--------------------+----------+----------+ ~~~ ~~~ | test.t1 | assign_to_keycache | status?? | OK?????? | ~~~ ~~~ | test.t2 | assign_to_keycache | status?? | OK?????? | ~~~ ~~~ | test.t3 | assign_to_keycache | status?? | OK?????? | ~~~ ~~~ +---------+--------------------+----------+----------+ ~~~ ~~~ ? ~~~ 可以用SET GLOBAL參數設置語句或使用服務器啟動選項設置在CACHE INDEX語句中引用的鍵高速緩沖的大小來創建鍵高速緩沖。例如： ~~~ mysql> SET GLOBAL keycache1.key_buffer_size=128*1024; ~~~ 要想刪除鍵高速緩沖，將其大小設置為零： ~~~ mysql> SET GLOBAL keycache1.key_buffer_size=0; ~~~ 請注意不能刪除默認鍵高速緩沖。刪除默認鍵高速緩沖的嘗試將被忽略： ~~~ mysql> set global key_buffer_size = 0; ~~~ ~~~ ? ~~~ ~~~ mysql> show variables like 'key_buffer_size'; ~~~ ~~~ +-----------------+---------+ ~~~ ~~~ | Variable_name?? | Value?? | ~~~ ~~~ +-----------------+---------+ ~~~ ~~~ | key_buffer_size | 8384512 | ~~~ ~~~ +-----------------+---------+ ~~~ ~~~ ? ~~~ 鍵高速緩沖變量是結構式系統變量，有一個名和組件。對于keycache1.key_buffer_size，keycache1是緩存變量名，key_buffer_size是緩存組件。關于引用結構式鍵高速緩沖系統變量所使用的語法的描述，參見[9.4.1節，“結構式系統變量”](# "9.4.1.?Structured System Variables") 默認情況下，表索引被分配給服務器啟動時創建的主要(默認)鍵高速緩沖。當鍵高速緩沖被刪除后，所有分配給它的索引被重新分配給默認鍵高速緩沖。對于一個忙的服務器，我們建議采用使用三個鍵高速緩沖的策略： ·???????? 占用為所有鍵高速緩沖分配的空間的20%的“熱”鍵高速緩沖。該緩存用于頻繁用于搜索但沒有更新的表。 ·???????? 占用為所有鍵高速緩沖分配的空間的20%的“冷”鍵高速緩沖。該緩存用于中等大小、大量修改的表，例如臨時表。 ·???????? 占用鍵高速緩沖空間的20%的“溫”鍵高速緩沖。使用它作為默認鍵高速緩沖，默認情況被所有其它表使用。使用3個鍵高速緩沖有好處的一個原因是對一個鍵高速緩沖結構的訪問不會阻擋對其它的訪問。訪問分配給一個緩存的表的查詢不會與訪問分配給其它緩存的表的查詢競爭。由于其它原因也會提高性能： ·???????? 熱緩存只用于檢索查詢，因此其內容決不會被修改。結果是，無論何時需要從硬盤上拉入索引塊，選擇用于替換的緩存塊的內容不需要先刷新。 ·???????? 對于分配給熱緩存的索引，如果沒有查詢需要索引掃描，很有可能對應索引B-樹的非葉子節點的索引塊仍然在緩存中。 ·???????? 當更新的節點位于緩存中并且不需要先從硬盤讀入時，為臨時表頻繁執行的更新操作會執行得更快。如果臨時表的索引的大小可以與冷鍵高速緩沖相比較，很可能更新的節點位于緩存中。 CACHE INDEX在一個表和鍵高速緩沖之間建立一種聯系，但每次服務器重啟時該聯系被丟失。如果你想要每次服務器重啟時該聯系生效，一個發辦法是使用選項文件：包括配置鍵高速緩沖的變量設定值，和一個init-file選項用來命名包含待執行的CACHE INDEX語句的一個文件。例如： ~~~ key_buffer_size = 4G ~~~ ~~~ hot_cache.key_buffer_size = 2G ~~~ ~~~ cold_cache.key_buffer_size = 2G ~~~ ~~~ init_file=/path/to/data-directory/mysqld_init.sql ~~~ 每次服務器啟動時執行mysqld_init.sql中的語句。該文件每行應包含一個SQL語句。下面的例子分配幾個表，分別對應hot_cache和cold_cache： ~~~ CACHE INDEX a.t1, a.t2, b.t3 IN hot_cache ~~~ ~~~ CACHE INDEX a.t4, b.t5, b.t6 IN cold_cache ~~~ #### 7.4.6.3.?中點插入策略默認情況，鍵高速緩沖管理系統采用LRU策略選擇要收回的鍵高速緩沖塊，但它也支持更復雜的方法，稱之為“中點插入策略”。當使用中點插入策略時，LRU鏈被分為兩個部分：一條熱子鏈和一條溫子鏈。兩部分之間的劃分點不固定，但鍵高速緩沖管理系統關注溫部分不“太短”，總是包含至少key_cache_division_limit比例的鍵高速緩沖塊。key_cache_division_limit是結構式鍵高速緩沖變量的一個組件，因此其值是一個可以根據每個緩存進行設置的參數。當一個索引塊從表中讀入鍵高速緩沖，它被放入溫子鏈的末端。經過一定量的訪問后(訪問塊)，它被提升給熱子鏈。目前，需要用來提升一個塊(3)的訪問次數與所有索引塊的相同。提升到熱子鏈的塊被放到子鏈的末端。塊然后在該子鏈中循環。如果塊在子鏈的開頭停留足夠長的時間，它被降到溫鏈。該時間由鍵高速緩沖key_cache_age_threshold組件的值確定。對于包含*N*個塊的鍵高速緩沖，閾值表示，熱子鏈開頭的沒有在最后*N* *key_cache_age_threshold/100次訪問中被訪問的塊將被移動到溫子鏈開頭。該塊然后變為第1個擠出的候選者，因為替換的塊總是來自溫子鏈的開頭。中點插入策略允許你將更有價值的塊總是在緩存中。如果你想使用簡單的LRU策略，使key_cache_division_limit值保持其默認值100。若執行的查詢要求索引掃描有效推出所有索引塊對應有數值的高級B-樹節點的緩存，中點插入策略可以幫助提高性能。要想避免，必須使用中點插入策略，而key_cache_division_limit設置為遠小于100。然后在索引掃描操作過程中，有數值的經常訪問的節點被保留在熱子鏈中。 #### 7.4.6.4.?索引預加載如果鍵高速緩沖內有足夠的塊以容納整個索引的塊，或者至少容納對應其非葉節點的塊，則在使用前，預裝含索引塊的鍵高速緩沖很有意義。預裝可以以更有效的方式將表索引塊放入鍵高速緩沖緩存區中：通過順序地從硬盤讀取索引塊。不進行預裝，塊仍然根據查詢需要放入鍵高速緩沖中。盡管塊將仍然在緩存中（因為有足夠的緩存區保存它們），它們以隨機方式從硬盤上索取，而不是以順序方式。要想將索引預裝到緩存中，使用LOAD INDEX INTO CACHE語句。例如，下面的語句可以預裝表t1和t2索引的節點(索引塊)： ~~~ mysql> LOAD INDEX INTO CACHE t1, t2 IGNORE LEAVES; ~~~ ~~~ +---------+--------------+----------+----------+ ~~~ ~~~ | Table?? | Op?????????? | Msg_type | Msg_text | ~~~ ~~~ +---------+--------------+----------+----------+ ~~~ ~~~ | test.t1 | preload_keys | status?? | OK?????? | ~~~ ~~~ | test.t2 | preload_keys | status?? | OK?????? | ~~~ ~~~ +---------+--------------+----------+----------+ ~~~ IGNORE LEAVES修改器只允許預裝索引非葉節點所用的塊。這樣，上述的語句預裝t1中的所有索引塊，但只預裝t2中的非葉節點對應的塊。如果已經使用CACHE INDEX語句為一個索引分配了一個鍵高速緩沖，預裝可以將索引塊放入該緩存。否則，索引被裝入默認鍵高速緩沖。 #### 7.4.6.5.?鍵高速緩沖塊大小可以使用key_cache_block_size變量為具體的鍵高速緩沖指定塊緩存區的大小。這樣允許為索引文件調節I/O操作的性能。當讀緩存區的大小等于原生操作系統I/O緩存區的大小時，可以獲得I/O操作的最佳性能。但是將關鍵字節點的大小設置為等于I/O緩存區的大小并不總是能保證最佳整體性能。當讀取大的葉節點時，服務器讀入大量的不需要的數據，結果防止讀入其它葉子的節點。目前，你不能控制表內索引塊的大小。該大小由服務器在創建.MYI索引文件時設置，取決于表定義中索引的關鍵字大小。在大多數情況下，它被設置為與I/O緩存區大小相等。 #### 7.4.6.6.?重構鍵高速緩沖鍵高速緩沖可以通過更新其參數值隨時重新構建。例如： ~~~ mysql> SET GLOBAL cold_cache。key_buffer_size=4*1024*1024； ~~~ 如果你為key_buffer_size或key_cache_block_size鍵高速緩沖組件分配的值與組件當前的值不同，服務器將毀掉緩存的舊結構并根據新值創建一個新的。如果緩存包含任何臟的塊，服務器在銷毀前將它們保存到硬盤上并重新創建緩存。如果你設置其它鍵高速緩沖參數，則不會發生重新構建。當重新構建鍵高速緩沖時，服務器首先將任何臟緩存區的內容刷新到硬盤上。之后，緩存內容不再需要。然而，重新構建并不阻塞需要使用分配給緩存的索引的查詢。相反，服務器使用原生文件系統緩存直接訪問表索引。文件系統緩存不如使用鍵高速緩沖有效，因此盡管查詢可以執行，但速度會減慢。緩存被重新構建后，它又可以緩存分配給它的索引了，并且索引不再使用文件系統緩存。 ### 7.4.7.?MyISAM索引統計集合存儲引擎搜集優化器使用的表的統計信息。表統計基于數數值組，其中數數值組是一系列有相同的關鍵字前綴值的記錄。對于優化器，重要的統計即為數數值組的平均大小。 MySQL用下述方式使用平均數數值組： ·???????? 估計必須為每個ref訪問讀取多少行 ·???????? 估計部分聯接將產生多少行；也就是說，下述形式的操作將產生的行數： ~~~ ·??????????????? ? (...) JOIN tbl_name ON tbl_name.key = expr ~~~ 隨著索引的平均數數值組大小的增加，索引將更沒有用，因為每個查找的平均行數增加：為了讓索引有利于優化目的，最好是每個索引值對應表內的少量行數。當某個給定的索引值產生較多行時，索引更加沒有用，MySQL更不可能使用它。平均數數值組大小與表的集的勢相關，即數數值組的數目。SHOW INDEX語句顯示集的勢值（基于*N*/*S*），其中*N*是表內的記錄數，*S*是平均數數值組大小。該比例產生表內數數值組的大約數。對于基于<=>比較操作符的聯接，NULL并不視為與任何其它值不同：NULL <=> NULL，正如對于其它*N*，*N* <=> *N*。然而，對于基于=操作符的聯接，NULL與非NULL值不同：當*expr1*或*expr2*(或兩者)為NULL時，*expr1* = *expr2*不為真。這樣影響比較形式*tbl_name.key* = *expr*的ref訪問：如果*expr*當前的值為NULL，MySQL不會訪問表，因為比較不能為真。對于=比較，表內有多少NULL值并不重要。為了優化目的，相關值為非NULL數值組的平均大小。然而，MySQL目前不允許搜集或使用該平均大小。對于MyISAM表，你可以使用myisam_stats_method系統變量部分控制表統計信息的搜集。該變量有兩個可能的不同值，如下所示： ·???????? 當myisam_stats_method為nulls_equal時，所有NULL值被視為相等的(也就是說，它們都形成一個數值組)。如果NULL數值組大小遠大于平均非NULL數值組大小，該方法向上傾斜平均數數值組大小。這樣使索引對于優化器來說比它實際為查找非NULL值的聯接更加沒有用。結果是，nulls_equal方法會使優化器進行ref訪問時本應使用索引而沒有使用。 ·???????? 當myisam_stats_method為nulls_unequal時，NULL值不視為相同。相反，每個NULL值形成一個單獨的數值組，大小為1。如果你有許多NULL值，該方法向下傾斜平均數數值組大小。如果平均非NULL數值組較大，統計大小為1的每個組的NULL值會使優化器過高估計查找非NULL值的聯接的索引值。結果是，當其它方法會更好時，nulls_unequal方法會使優化器為ref查找使用該索引。如果你要使用許多使用<=>而不是=的聯接，在比較過程中NULL值并不特殊，一個NULL等于另一個NULL。在這種情況下，nulls_equal是合適的統計方法。 myisam_stats_method系統變量有全局和會話值。設置全局值會影響MyISAM為所有MyISAM表的統計的搜集。設置會話值只影響當前客戶連接的統計的搜集。這說明你可以強制用給定的方法重新生成表的統計的搜集，而不需要因為設置myisam_stats_method的會話值而影響其它客戶。可以使用下面任一方法來重新生成表的統計信息： ·???????? 設置myisam_stats_method，然后執行CHECK TABLE語句 ·???????? 執行**myisamchk --stats_method=method_name --analyze** ·???????? 更改表，使其統計信息不為最新(例如，插入一行然后刪除它)，然后設置myisam_stats_method并執行ANALYZE TABLE語句使用myisam_stats_method的一些警告：你可以強制顯式搜集表的統計信息，如上所述。然而，MySQL也可以自動搜集統計信息。例如，如果在為表執行語句的過程中，一些語句修改了表，MySQL可以搜集統計信息。(例如，大批插入或刪除，或者執行ALTER TABLE語句時可能發生）。如果發生，使用myisam_stats_method此時所有的值搜集統計信息。這樣，如果你使用一個方法搜集統計信息，但當后面自動搜集一個表的統計信息時myisam_stats_method被設置為另一個方法，將使用其它方法。對于給定的MyISAM表，還不能說出使用哪個方法來產生統計信息。 myisam_stats_method只適合MyISAM表。其它存儲引擎只有一個方法來搜集表的統計信息。通常它接近于nulls_equal方法。 ### 7.4.8.?MySQL如何計算打開的表當運行**mysqladmin status**時，將看見象這樣的一些東西： ~~~ Uptime: 426 Running threads: 1 Questions: 11082 ~~~ ~~~ Reloads: 1 Open tables: 12 ~~~ ~~~ ? ~~~ 如果你僅有6個表，Open tables值為12可能有點令人困惑。 ** MySQL**是多線程的，因此許多客戶可以同時在同一個表上進行查詢。為了使多個客戶線程在同一個表上有不同狀態的問題減到最小，表被每個并發進程獨立地打開。這樣需要額外的內存但一般會提高性能。對于MyISAM表，數據文件需要為每個打開表的客戶提供一個額外的文件描述符。(索引文件描述符在所有線程之間共享)。下一節中提供了該主題的更多的信息。參見[7.4.9節，“MySQL如何打開和關閉表”](# "7.4.9.?How MySQL Opens and Closes Tables")。 ### 7.4.9.?MySQL如何打開和關閉表 table_cache、max_connections和max_tmp_tables系統變量影響服務器保持打開的文件的最大數量。如果你增加這些值其中的一個或兩個，會遇到操作系統為每個進程打開文件描述符的數量強加的限制。許多操作系統允許你增加打開的文件的限制，盡管該方法隨系統的不同而不同。查閱操作系統文檔以確定是否可以增加限制以及如何操作。 table_cache與max_connections有關。例如，對于200個并行運行的連接，應該讓表的緩存至少有200 * *N*，這里*N*是可以執行的查詢的一個聯接中表的最大數量。還需要為臨時表和文件保留一些額外的文件描述符。確保操作系統可以處理table_cache設置所指的打開的文件描述符的數目。如果table_cacheis設得太高，MySQL可能為文件描述符耗盡資源并拒絕連接，不能執行查詢，并且很不可靠。還必須考慮到MyISAM存儲引擎需要為每個打開的表提供兩個文件描述符。可以在**mysqld_safe**中使用--open-files-limit啟動選項來增加MySQL適用的文件描述符的數量。參見[A.2.17節，“文件未找到”](# "A.2.17.?File Not Found")。打開表的緩存可以保持在table_cache條。默認為64；可以用**mysqld**的--table_cache選項來更改。請注意 MySQL可以臨時打開更多的表以執行查詢。在下面的條件下，未使用的表將被關閉并從表緩存中移出： ·???????? 當緩存滿了并且一個線程試圖打開一個不在緩存中的表時。 ·???????? 當緩存包含超過table_cache個條目，并且緩存中的表不再被任何線程使用。 ·???????? 當表刷新操作發生。當執行FLUSH TABLES語句或執行**mysqladmin flush-tables**或**mysqladmin refresh**命令時會發生。當表緩存滿時，服務器使用下列過程找到一個緩存入口來使用： ·???????? 當前未使用的表被釋放，以最近最少使用順序。 ·???????? 如果緩存滿了并且沒有表可以釋放，但是一個新表需要打開，緩存必須臨時被擴大。如果緩存處于一個臨時擴大狀態并且一個表從在用變為不在用狀態，它被關閉并從緩存中釋放。對每個并發訪問打開一個表。這意味著，如果2個線程訪問同一個表或在同一個查詢中訪問表兩次(例如，將表連接為自身時)，表需要被打開兩次。每個并行的打開要求在表緩存中有一個條目。任何表的第一次打開占2個文件描述符：一個用于數據文件另一個用于索引文件。表的每一次額外使用僅占一個數據文件的文件描述符。索引文件描述符在所有線程之間共享。如果你正用HANDLER *tbl_name* OPEN語句打開一個表，將為該線程專門分配一個表。該表不被其它線程共享，只有線程調用HANDLER *tbl_name* CLOSE或線程終止后才被關閉。表關閉后，被拉回表緩存中(如果緩存不滿)。參見[13.2.3節，“HANDLER語法”](# "13.2.3.?HANDLER Syntax")。可以通過檢查**mysqld**的狀態變量Opened_tables確定表緩存是否太小： ~~~ mysql> SHOW STATUS LIKE 'Opened_tables'; ~~~ ~~~ +---------------+-------+ ~~~ ~~~ | Variable_name | Value | ~~~ ~~~ +---------------+-------+ ~~~ ~~~ | Opened_tables | 2741? | ~~~ ~~~ +---------------+-------+ ~~~ 如果值很大，即使你沒有發出許多FLUSH TABLES語句，也應增加表緩存的大小。參見[5.3.3節，“服務器系統變量”](# "5.3.3.?Server System Variables")和[5.3.4節，“服務器狀態變量”](# "5.3.4.?Server Status Variables")。 ### 7.4.10.?在同一個數據庫中創建多個表的缺陷如果在同一個數據庫目錄中有許多MyISAM表，打開、關閉和創建操作將會很慢。如果對許多不同的表執行SELECT語句，當表緩存滿時，將有一點開銷，因為對每個必須打開的表，另外一個必須被關閉。可以通過使表緩存更大些來減少這個開銷。 ### 7.5.?優化MySQL服務器 [ 7.5.1. 系統因素和啟動參數的調節](#)[ 7.5.2. 調節服務器參數](#)[ 7.5.3. 控制查詢優化器的性能](#)[ 7.5.4. 編譯和鏈接怎樣影響MySQL的速度](#)[ 7.5.5. MySQL如何使用內存](#)[ 7.5.6. MySQL如何使用DNS](#) ### 7.5.1.?系統因素和啟動參數的調節我們從系統級因素開始，因為必須盡早地進行部分決策以獲得較大性能。在其它情況下，快速瀏覽該節就足夠了。但是，了解一下更改該層次的參數能夠獲得多少性能提高是很有意義的。使用的操作系統很重要。為了更好地使用多CPU機器，應使用Solaris(因為其線程工作得很好)或Linux(因為2.4和以后的內核有很好的SMP支持)。請注意默認情況舊的Linux內核有一個2GB的文件大小限制。如果有這樣的一個內核并且需要文件大于2GB，應得到ext2文件系統的大文件支持(LFS)補丁。其它文件系統例如ReiserFS和XFS沒有此2GB限制。將MySQL用于生產前，我們建議你在想用的平臺上對它進行測試。其它技巧： ·???????? 如果有足夠的RAM，可以移除所有的交換設備。有些操作系統即使有自由內存也使用交換設備。 ·???????? 使用--skip-external-locking MySQL選項以避免外部鎖定。該選項默認開啟。請注意只要你只運行一個服務器，--skip-external-locking選項不影響MySQL的功能。只要記住運行**myisamchk**之前關閉服務器(或鎖定并刷新相關表)。在一些系統上該選項是強制的，因為在任何情況下外部鎖定均不工作。不能使用--skip-external-locking的唯一情況是對相同的數據運行多個MySQL服務器(非客戶)的情況，或者如果沒有事先告訴服務器刷新并鎖定一個表即運行**myisamchk**來檢查(非修復)該表。請注意一般不建議使用多個MySQL服務器來并行訪問相同的數據，除了使用MySQL Cluster時。即使使用--skip-external-locking，仍然可以使用LOCK TABLES和UNLOCK TABLES。 ### 7.5.2.?調節服務器參數可以用這個命令得到**mysqld**服務器默認緩存區的大小： ~~~ shell> mysqld --verbose --help ~~~ 這個命令生成所有**mysqld**選項和可配置變量的列表。輸出包括默認值并且看上去象這樣： ~~~ help????????????????????????????? TRUE ~~~ ~~~ abort-slave-event-count?????????? 0 ~~~ ~~~ allow-suspicious-udfs???????????? FALSE ~~~ ~~~ auto-increment-increment????????? 1 ~~~ ~~~ auto-increment-offset????? ???????1 ~~~ ~~~ automatic-sp-privileges?????????? TRUE ~~~ ~~~ basedir?????????????????????????? /home/jon/bin/mysql/ ~~~ ~~~ bdb?????????????????????????????? FALSE ~~~ ~~~ bind-address????????????????????? (No default value) ~~~ ~~~ character-set-client-handshake??? TRUE ~~~ ~~~ character-set-server ?????????????latin1 ~~~ ~~~ character-sets-dir??????????????? /home/jon/bin/mysql/share/mysql/charsets/ ~~~ ~~~ chroot??????????????????????????? (No default value) ~~~ ~~~ collation-server????????????????? latin1_swedish_ci ~~~ ~~~ completion-type?????????????????? 0 ~~~ ~~~ concurrent-insert? ???????????????1 ~~~ ~~~ console?????????????????????????? FALSE ~~~ ~~~ datadir?????????????????????????? /home/jon/bin/mysql/var/ ~~~ ~~~ default-character-set???????????? latin1 ~~~ ~~~ default-collation???????????????? latin1_swedish_ci ~~~ ~~~ default-time-zone???????????????? (No default value) ~~~ ~~~ disconnect-slave-event-count????? 0 ~~~ ~~~ enable-locking??????????????????? FALSE ~~~ ~~~ enable-pstack???????????????????? FALSE ~~~ ~~~ engine-condition-pushdown???????? FALSE ~~~ ~~~ external-locking????????????????? FALSE ~~~ ~~~ gdb?????????????????????????????? FALSE ~~~ ~~~ large-pages?? ????????????????????FALSE ~~~ ~~~ init-connect????????????????????? (No default value) ~~~ ~~~ init-file???????????????????????? (No default value) ~~~ ~~~ init-slave??????????????????????? (No default value) ~~~ ~~~ innodb??????????????????????????? TRUE ~~~ ~~~ innodb_checksums??????????????? ??TRUE ~~~ ~~~ innodb_data_home_dir????????????? (No default value) ~~~ ~~~ innodb_doublewrite??????????????? TRUE ~~~ ~~~ innodb_fast_shutdown????????????? 1 ~~~ ~~~ innodb_file_per_table???????????? FALSE ~~~ ~~~ innodb_flush_log_at_trx_commit??? 1 ~~~ ~~~ innodb_flush_method?????????????? (No default value) ~~~ ~~~ innodb_locks_unsafe_for_binlog??? FALSE ~~~ ~~~ innodb_log_arch_dir?????????????? (No default value) ~~~ ~~~ innodb_log_group_home_dir???????? (No default value) ~~~ ~~~ innodb_max_dirty_pages_pct??????? 90 ~~~ ~~~ innodb_max_purge_lag????????????? 0 ~~~ ~~~ innodb_status_file?????????? ?????FALSE ~~~ ~~~ innodb_table_locks??????????????? TRUE ~~~ ~~~ innodb_support_xa???????????????? TRUE ~~~ ~~~ isam????????????????????????????? FALSE ~~~ ~~~ language????????????????????????? /home/jon/bin/mysql/share/mysql/english ~~~ ~~~ local-infile????????????????????? TRUE ~~~ ~~~ log?????????? ????????????????????/home/jon/bin/mysql/var/master1.log ~~~ ~~~ log-bin?????????????????????????? /home/jon/bin/mysql/var/master1 ~~~ ~~~ log-bin-index???????????????????? (No default value) ~~~ ~~~ log-bin-trust-routine-creators??? FALSE ~~~ ~~~ log-error???????????????????????? /home/jon/bin/mysql/var/master1.err ~~~ ~~~ log-isam????????????????????????? myisam.log ~~~ ~~~ log-queries-not-using-indexes???? FALSE ~~~ ~~~ log-short-format????????????????? FALSE ~~~ ~~~ log-slave-updates???????????????? FALSE ~~~ ~~~ log-slow-admin-statements???????? FALSE ~~~ ~~~ log-slow-queries????????????????? (No default value) ~~~ ~~~ log-tc??????????????????????????? tc.log ~~~ ~~~ log-tc-size?????????????????????? 24576 ~~~ ~~~ log-update??????????????????????? (No default value) ~~~ ~~~ log-warnings????????????????????? 1 ~~~ ~~~ low-priority-updates????????????? FALSE ~~~ ~~~ master-connect-retry????????????? 60 ~~~ ~~~ master-host?????????????????????? (No default value) ~~~ ~~~ master-info-file????????????????? master.info ~~~ ~~~ master-password?????????????????? (No default value) ~~~ ~~~ master-port?????????????????????? 3306 ~~~ ~~~ master-retry-count??????????????? 86400 ~~~ ~~~ master-ssl??????????????????????? FALSE ~~~ ~~~ master-ssl-ca???????????????????? (No default value) ~~~ ~~~ master-ssl-capath???????????????? (No default value) ~~~ ~~~ master-ssl-cert?????????????????? (No default value) ~~~ ~~~ master-ssl-cipher???????????????? (No default value) ~~~ ~~~ master-ssl-key??????????????????? (No default value) ~~~ ~~~ master-user?????????????????????? test ~~~ ~~~ max-binlog-dump-events??????????? 0 ~~~ ~~~ memlock?????????????????????????? FALSE ~~~ ~~~ myisam-recover??????????????????? OFF ~~~ ~~~ ndbcluster??????????????????????? FALSE ~~~ ~~~ ndb-connectstring???????????????? (No default value) ~~~ ~~~ ndb-mgmd-host???????????????????? (No default value) ~~~ ~~~ ndb-nodeid??????????????????????? 0 ~~~ ~~~ ndb-autoincrement-prefetch-sz???? 32 ~~~ ~~~ ndb-distibution?????????????????? KEYHASH ~~~ ~~~ ndb-force-send??????????????????? TRUE ~~~ ~~~ ndb_force_send??????????????????? TRUE ~~~ ~~~ ndb-use-exact-count?????????????? TRUE ~~~ ~~~ ndb_use_exact_count?????????????? TRUE ~~~ ~~~ ndb-shm?????????????????????????? FALSE ~~~ ~~~ ndb-optimized-node-selection????? TRUE ~~~ ~~~ ndb-cache-check-time????????????? 0 ~~~ ~~~ ndb-index-stat-enable???????????? TRUE ~~~ ~~~ ndb-index-stat-cache-entries????? 32 ~~~ ~~~ ndb-index-stat-update-freq??????? 20 ~~~ ~~~ new?????????????????????????????? FALSE ~~~ ~~~ old-alter-table?????????????????? FALSE ~~~ ~~~ old-passwords???????????????????? FALSE ~~~ ~~~ old-style-user-limits???????????? FALSE ~~~ ~~~ pid-file????????????????????????? /home/jon/bin/mysql/var/hostname.pid1 ~~~ ~~~ port????????????????????????????? 3306 ~~~ ~~~ relay-log???????????????????????? (No default value) ~~~ ~~~ relay-log-index?????????????????? (No default value) ~~~ ~~~ relay-log-info-file?????????????? relay-log.info ~~~ ~~~ replicate-same-server-id????????? FALSE ~~~ ~~~ report-host?????????????????????? (No default value) ~~~ ~~~ report-password?????????????????? (No default value) ~~~ ~~~ report-port?????????????????????? 3306 ~~~ ~~~ report-user?????????????????????? (No default value) ~~~ ~~~ rpl-recovery-rank???????????????? 0 ~~~ ~~~ safe-user-create????????????????? FALSE ~~~ ~~~ secure-auth?????????????????????? FALSE ~~~ ~~~ server-id???????????????????????? 1 ~~~ ~~~ show-slave-auth-info????????????? FALSE ~~~ ~~~ skip-grant-tables???????????????? FALSE ~~~ ~~~ skip-slave-start??????? ??????????FALSE ~~~ ~~~ slave-load-tmpdir???????????????? /tmp/ ~~~ ~~~ socket??????????????????????????? /tmp/mysql.sock ~~~ ~~~ sporadic-binlog-dump-fail???????? FALSE ~~~ ~~~ sql-mode????????????????????????? OFF ~~~ ~~~ symbolic-links??????????????????? TRUE ~~~ ~~~ tc-heuristic-recover???????????? ?(No default value) ~~~ ~~~ temp-pool???????????????????????? TRUE ~~~ ~~~ timed_mutexes???????????????????? FALSE ~~~ ~~~ tmpdir??????????????????????????? (No default value) ~~~ ~~~ use-symbolic-links??????????????? TRUE ~~~ ~~~ verbose?????????????????????????? TRUE ~~~ ~~~ warnings????????????????? ????????1 ~~~ ~~~ back_log????????????????????????? 50 ~~~ ~~~ binlog_cache_size???????????????? 32768 ~~~ ~~~ bulk_insert_buffer_size?????????? 8388608 ~~~ ~~~ connect_timeout?????????????????? 5 ~~~ ~~~ date_format?????????????????????? (No default value) ~~~ ~~~ datetime_format?????????????????? (No default value) ~~~ ~~~ default_week_format?????????????? 0 ~~~ ~~~ delayed_insert_limit????????????? 100 ~~~ ~~~ delayed_insert_timeout??????????? 300 ~~~ ~~~ delayed_queue_size??????????????? 1000 ~~~ ~~~ expire_logs_days????????????????? 0 ~~~ ~~~ flush_time??????????????????????? 0 ~~~ ~~~ ft_max_word_len?? ????????????????84 ~~~ ~~~ ft_min_word_len?????????????????? 4 ~~~ ~~~ ft_query_expansion_limit????????? 20 ~~~ ~~~ ft_stopword_file????????????????? (No default value) ~~~ ~~~ group_concat_max_len????????????? 1024 ~~~ ~~~ innodb_additional_mem_pool_size?? 1048576 ~~~ ~~~ innodb_autoextend_increment?? ????8 ~~~ ~~~ innodb_buffer_pool_awe_mem_mb???? 0 ~~~ ~~~ innodb_buffer_pool_size?????????? 8388608 ~~~ ~~~ innodb_concurrency_tickets??????? 500 ~~~ ~~~ innodb_file_io_threads??????????? 4 ~~~ ~~~ innodb_force_recovery???????????? 0 ~~~ ~~~ innodb_lock_wait_timeout????????? 50 ~~~ ~~~ innodb_log_buffer_size?? ?????????1048576 ~~~ ~~~ innodb_log_file_size????????????? 5242880 ~~~ ~~~ innodb_log_files_in_group???????? 2 ~~~ ~~~ innodb_mirrored_log_groups??????? 1 ~~~ ~~~ innodb_open_files???????????????? 300 ~~~ ~~~ innodb_sync_spin_loops??????????? 20 ~~~ ~~~ innodb_thread_concurrency???????? 20 ~~~ ~~~ innodb_commit_concurrency???????? 0 ~~~ ~~~ innodb_thread_sleep_delay???????? 10000 ~~~ ~~~ interactive_timeout?????????????? 28800 ~~~ ~~~ join_buffer_size????????????????? 131072 ~~~ ~~~ key_buffer_size?????????????????? 8388600 ~~~ ~~~ key_cache_age_threshold?????????? 300 ~~~ ~~~ key_cache_block_size??????????? ??1024 ~~~ ~~~ key_cache_division_limit????????? 100 ~~~ ~~~ long_query_time?????????????????? 10 ~~~ ~~~ lower_case_table_names??????????? 0 ~~~ ~~~ max_allowed_packet??????????????? 1048576 ~~~ ~~~ max_binlog_cache_size???????????? 4294967295 ~~~ ~~~ max_binlog_size?????????????????? 1073741824 ~~~ ~~~ max_connect_errors??????????????? 10 ~~~ ~~~ max_connections?????????????????? 100 ~~~ ~~~ max_delayed_threads?????????????? 20 ~~~ ~~~ max_error_count?????????????????? 64 ~~~ ~~~ max_heap_table_size?????????????? 16777216 ~~~ ~~~ max_join_size???????????????????? 4294967295 ~~~ ~~~ max_length_for_sort_data????????? 1024 ~~~ ~~~ max_relay_log_size??????????????? 0 ~~~ ~~~ max_seeks_for_key???????????????? 4294967295 ~~~ ~~~ max_sort_length?????????????????? 1024 ~~~ ~~~ max_tmp_tables??????????????????? 32 ~~~ ~~~ max_user_connections????????????? 0 ~~~ ~~~ max_write_lock_count????????????? 4294967295 ~~~ ~~~ multi_range_count???????????????? 256 ~~~ ~~~ myisam_block_size???????????????? 1024 ~~~ ~~~ myisam_data_pointer_size????????? 6 ~~~ ~~~ myisam_max_extra_sort_file_size?? 2147483648 ~~~ ~~~ myisam_max_sort_file_size???????? 2147483647 ~~~ ~~~ myisam_repair_threads???????????? 1 ~~~ ~~~ myisam_sort_buffer_size?????????? 8388608 ~~~ ~~~ myisam_stats_method?????????????? nulls_unequal ~~~ ~~~ net_buffer_length???????????????? 16384 ~~~ ~~~ net_read_timeout????????????????? 30 ~~~ ~~~ net_retry_count?????????????????? 10 ~~~ ~~~ net_write_timeout???????????????? 60 ~~~ ~~~ open_files_limit????????????? ????0 ~~~ ~~~ optimizer_prune_level???????????? 1 ~~~ ~~~ optimizer_search_depth??????????? 62 ~~~ ~~~ preload_buffer_size?????????????? 32768 ~~~ ~~~ query_alloc_block_size??????????? 8192 ~~~ ~~~ query_cache_limit???????????????? 1048576 ~~~ ~~~ query_cache_min_res_unit????????? 4096 ~~~ ~~~ query_cache_size ?????????????????0 ~~~ ~~~ query_cache_type????????????????? 1 ~~~ ~~~ query_cache_wlock_invalidate????? FALSE ~~~ ~~~ query_prealloc_size?????????????? 8192 ~~~ ~~~ range_alloc_block_size??????????? 2048 ~~~ ~~~ read_buffer_size????????????????? 131072 ~~~ ~~~ read_only???????????????????????? FALSE ~~~ ~~~ read_rnd_buffer_size????????????? 262144 ~~~ ~~~ div_precision_increment?????????? 4 ~~~ ~~~ record_buffer???????????????????? 131072 ~~~ ~~~ relay_log_purge?????????????????? TRUE ~~~ ~~~ relay_log_space_limit???????????? 0 ~~~ ~~~ slave_compressed_protocol???????? FALSE ~~~ ~~~ slave_net_timeout??????? ?????????3600 ~~~ ~~~ slave_transaction_retries???????? 10 ~~~ ~~~ slow_launch_time????????????????? 2 ~~~ ~~~ sort_buffer_size????????????????? 2097144 ~~~ ~~~ sync-binlog?????????????????????? 0 ~~~ ~~~ sync-frm????????????????????????? TRUE ~~~ ~~~ sync-replication????????????????? 0 ~~~ ~~~ sync-replication-slave-id???????? 0 ~~~ ~~~ sync-replication-timeout????????? 10 ~~~ ~~~ table_cache?????????????????????? 64 ~~~ ~~~ table_lock_wait_timeout?????????? 50 ~~~ ~~~ thread_cache_size???????????????? 0 ~~~ ~~~ thread_concurrency??????????????? 10 ~~~ ~~~ thread_stack????????????????????? 196608 ~~~ ~~~ time_format?????????????????????? (No default value) ~~~ ~~~ tmp_table_size??????????????????? 33554432 ~~~ ~~~ transaction_alloc_block_size????? 8192 ~~~ ~~~ transaction_prealloc_size???????? 4096 ~~~ ~~~ updatable_views_with_limit??????? 1 ~~~ ~~~ wait_timeout????????????????????? 28800 ~~~ 如果有一個**mysqld**服務器正在運行，通過連接它并執行這個命令，可以看到實際上使用的變量的值： ~~~ mysql> SHOW VARIABLES; ~~~ 還可以通過下面的語句看到運行服務器的統計和狀態指標： ~~~ mysql>SHOW STATUS； ~~~ 使用**mysqladmin**還可以獲得系統變量和狀態信息： ~~~ shell> mysqladmin variables ~~~ ~~~ shell> mysqladmin extended-status ~~~ 關于所有系統和狀態變量的完全描述參見[5.3.3節，“服務器系統變量”](# "5.3.3.?Server System Variables")和[5.3.4節，“服務器狀態變量”](# "5.3.4.?Server Status Variables")。 MySQL使用完全可以升級的算法，因此通常運行時可以用很少的內存。然而，通常情況若給MySQL更多的內存性能會更好。當調節MySQL服務器時，要配置的兩個最重要的變量是key_buffer_size和table_cache。在試圖更改其它變量前你應先確信已經適當地配置了這些變量。下面的例子顯示了部分典型的不同的運行時配置的變量值。 ·???????? 如果至少有256MB內存和許多表，想要在中等數量的客戶時獲得最大性能，應使用： ~~~ ·??????????????? shell> mysqld_safe --key_buffer_size=64M --table_cache=256 \ ~~~ ~~~ ·??????????????? ???????????--sort_buffer_size=4M --read_buffer_size=1M & ~~~ ·???????? 如果只有128MB內存和少量表，但仍然要進行大量的排序，可以使用： ~~~ ·??????????????? shell> mysqld_safe --key_buffer_size=16M --sort_buffer_size=1M ~~~ 如果有許多并行連接，交換問題會發生，除非**mysqld**已經配置成為每個連接分配很少的內存。如果有足夠的內存用于所有連接，**mysqld**會執行得更好。 ·???????? 對于少量內存和大量連接，應使用： ~~~ ·??????????????? shell> mysqld_safe --key_buffer_size=512K --sort_buffer_size=100K \ ~~~ ~~~ ·??????????????? ???????????--read_buffer_size=100K & ~~~ 或甚至為： ~~~ shell> mysqld_safe --key_buffer_size=512K --sort_buffer_size=16K \ ~~~ ~~~ ?????????? --table_cache=32 --read_buffer_size=8K \ ~~~ ~~~ ?????????? --net_buffer_length=1K & ~~~ 如果正對遠遠大于可用內存的表執行GROUP BY或ORDER BY操作，應增加read_rnd_buffer_size的值以加速排序操作后面的行讀取。如果已經安裝了MySQL，support-files目錄包含一些不同的my.cnf示例文件：my-huge.cnf、my-大.cnf、my-medium.cnf和my-small.cnf。可以使用這些文件來優化系統。請注意如果在命令行中為**mysqld**或**mysqld_safe**指定一個選項，它只在該次服務器調用中保持有效。要想每次服務器運行時使用該選項，將它放在選項文件中。要想看參數更改的效果，應執行： ~~~ shell> mysqld --key_buffer_size=32M --verbose ---help ~~~ 變量值列于輸出的最后。確保--verbose和---help選項在最后。否則，在命令行中列于它們后面的選項的效果不會反映到輸出中。關于調節InnoDB存儲引擎的信息，參見[15.2.11節，“InnoDB性能調節提示”](# "15.2.11.?InnoDB Performance Tuning Tips")。 ### 7.5.3.?控制查詢優化器的性能查詢優化器的任務是發現執行SQL查詢的最佳方案。因為“好”方案和“壞”方案之間的性能差別會巨大(也就是說，秒相對于小時或甚至天)，大多數查詢優化器，包括MySQL的查詢優化器，總或多或少地在所有可能的查詢評估方案中搜索最佳方案。對于聯接查詢，MySQL優化器所調查的可能的方案數隨查詢中所引用的表的數目呈指數增長。對于小數量的表(典型小于7-10)，這不是一個問題。然而，當提交的查詢更大時，查詢優化所花的時間會很容易地成為服務器性能的主要瓶頸。查詢優化的一個更加靈活的方法是允許用戶控制優化器詳盡地搜索最佳查詢評估方案。一般思想是優化器調查的方案越少，它編譯一個查詢所花費的時間越少。另一方面，因為優化器跳過了一些方案，它可能錯過一個最佳方案。優化器關于方案數量評估的行為可以通過兩個系統變量來控制： ·???????? optimizer_prune_level變量告訴優化器根據對每個表訪問的行數的估計跳過某些方案。我們的試驗顯示該類“有根據的猜測”很少錯過最佳方案，并且可以大大降低查詢編輯次數。這就是為什么默認情況該選項為on(optimizer_prune_level=1)。然而，如果你認為優化器錯過了一個更好的查詢方案，則該選項可以關閉(optimizer_prune_level=0)，風險是查詢編輯花費的時間更長。請注意即使使用該啟發，優化器仍然可以探測呈指數數目的方案。 ·???????? optimizer_search_depth變量告訴優化器對于每個未完成的“未來的”方案，應查看多深，以評估是否應對它進一步擴大。optimizer_search_depth值較小會使查詢編輯次數大大減小。例如，如果optimizer_search_depth接近于查詢中表的數量，對12、13或更多表的查詢很可能需要幾小時甚至幾天的時間來編譯。同時，如果用optimizer_search_depth等于3或4編輯，對于同一個查詢，編譯器編譯時間可以少于1分鐘。如果不能確定合理的optimizer_search_depth值，該變量可以設置為0，告訴優化器自動確定該值。 ### 7.5.4.?編譯和鏈接怎樣影響MySQL的速度下列大多數測試是在Linux上并用**MySQL**基準進行的，但是它們能對其它操作系統和工作負載給出一些指示。當你用-static鏈接時，可以得到最快的可執行文件。在Linux上，最好用**pgcc**和-O3編譯服務器。為了用這些選項編譯“sql_yacc.cc”，需要大約200M內存，因為**gcc**或**pgcc**需要大量的內存使所有函數嵌入（inline）。在配置**MySQL**時，也應該設定CXX=gcc以避免包括libstdc++庫(它不需要)。請注意對于某些版本的**pgcc**，生成的二進制只能運行在真Pentium處理器上，即使你使用編譯器選項說明你想讓最終的代碼在所有x586-類處理器上工作(例如AMD)。只通過使用一個較好的編譯器或較好的編譯器選項，在應用中能得到10-30%的加速。如果你自己編譯SQL服務器，這特別重要！當我們測試Cygnus CodeFusion或Fujitsu編譯器時，二者均還沒足夠不出錯來讓MySQL啟用優化進行編譯。標準MySQL二進制分發編譯為支持所有字符集。當你自己編譯MySQL時，應只包括將使用的字符集的支持。通過**configure**的--with-charset選項來控制。這里是我們做過的一些測量表： ·???????? 如果你使用**pgcc**并用-O6編譯，**mysqld**服務器比用**gcc** 2.95.2快11%。 ·???????? 如果你動態地鏈接(沒有-static)，在Linux中結果慢了13%。注意你仍能在客戶應用程序中使用動態鏈接MySQL庫。只有服務器對性能是關鍵的。 ·???????? 如果你用strip mysqld剝離**mysqld**二進制，生成的二進制可以快4%。 ·???????? 對于在同一主機上運行的客戶與服務器之間的連接，如果你使用TCP/IP而非Unix套接字文件進行連接，結果慢7.5%。(在Unix中，如果你連接localhost主機，MySQL默認使用一個套接字文件）。 ·???????? 對于從客戶到服務器的TCP/IP連接，從另一臺主機連接一臺遠程服務器要比連接同一主機上的服務器慢8-11%，即使通過100Mb/s以太網進行連接。 ·???????? 當使用安全連接運行我們的基準測試時(所有數據用內部SSL支持進行加密)，性能比未加密連接慢55%。 ·???????? 如果你用--with-debug=full編譯，大多數查詢慢20%。部分查詢時間會很長；例如，MySQL基準的運行要慢35%。如果你使用--with-debug(沒有=full)，速度只下降15%。對于用--with-debug=full編譯的**mysqld**版本，可以用--skip-safemalloc選項啟動以便在運行時禁用內存檢查。執行速度則接近用--with-debug配置的時候。 ·???????? 在Sun UltraSPARC-Iie上，用Forte 5.0編譯的服務器比用**gcc** 3.2編譯的要快4%。 ·???????? 在Sun UltraSPARC-Iie上，用Forte 5.0編譯的32位模式服務器比64位模式服務器要快4%。 ·???????? 用**gcc** 2.95.2編譯帶-mcpu=v8 -Wa的UltraSPARC，使用-xarch=v8plusa選項性能會提高4%。 ·???????? 在Solaris 2.5.1上，在單個處理器上MIT-pthreads比帶原生線程的Solaris慢8-12%。如果有更大的負載/cpus，差別應該更大。 ·???????? 在Linux-x86上使用**gcc**編譯而不用幀指針(-fomit-frame-pointer or -fomit-frame-pointer -ffixed-ebp)，可以使**mysqld**快1-4%。 MySQL AB提供的Linux上的二進制MySQL分發一般用**pgcc**編譯。我們必須返回到常規**gcc**，因為**pgcc**中有一個bug，使生成的二進制不能在AMD上運行。我們將繼續使用**gcc**直到該bug被解決。同時，如果你有一個非AMD機，你可以用**pgcc**編譯構建一個更快的二進制。標準MySQL Linux二進制是通過靜態鏈接，以使它更快并且更加易于移植。 ### 7.5.5.?MySQL如何使用內存下面的列表中列出了**mysqld**服務器使用內存的一些方法。在適用的地方，給出了內存相關的系統變量名： ·???????? 鍵緩存(變量key_buffer_size)被所有線程共享；服務器使用的其它緩存則根據需要分配。參見[7.5.2節，“調節服務器參數”](# "7.5.2.?Tuning Server Parameters")。 ·???????? 每個連接使用具體線程的空間： o??????? 堆棧(默認64KB，變量thread_stack) o??????? 連接緩存區(變量net_buffer_length) o??????? 結果緩存區(變量net_buffer_length) 連接緩存區和結果緩存區可以根據需要動態擴充到max_allowed_packet。當某個查詢運行時，也為當前查詢字符串分配內存。 ·???????? 所有線程共享相同的基本內存。 ·???????? 只有壓縮MyISAM表映射到內存。這是因為4GB的32位內存空間不足以容納大多數大表。當64位地址空間的系統變得越來越普遍后，我們可以增加常規的內存映射支持。 ·???????? 對表進行順序掃描的請求將分配一個*緩存區*(變量read_buffer_size)。 ·???????? 當按任意順序讀取行時(例如，按照排序順序)，將分配一個隨機讀 *緩存區*(變量read_rnd_buffer_size)以避免硬盤搜索。 ·???????? 所有聯合在一個令牌內完成，并且大多數聯合甚至可以不用臨時表即可以完成。大多數臨時表是基于內存的(HEAP)表。具有大的記錄長度的臨時表 (所有列的長度的和)或包含BLOB列的表存儲在硬盤上。如果某個內部heap（堆積）表大小超過tmp_table_size，MySQL可以根據需要自動將內存中的heap表改為基于硬盤的MyISAM表。還可以通過設置**mysqld**的tmp_table_size選項來增加臨時表的大小，或設置客戶程序中的SQL選項SQL_BIG_TABLE。參見[13.5.3節，“SET語法”](# "13.5.3.?SET Syntax")。 ·???????? 進行排序的大多數請求將分配一個排序緩存區，并根據結果集的大小為兩個臨時文件分配零。參見[A.4.4節，“MySQL將臨時文件儲存在哪里”](# "A.4.4.?Where MySQL Stores Temporary Files")。 ·???????? 幾乎所有解析和計算在局部內存中完成。小項目不需要內存，因此避免了普通的慢內存分配和釋放。只為不期望的大字符串分配內存；使用函數malloc()和free()來完成。 ·???????? 對于每個打開的MyISAM表，索引文件打開一次；數據文件為每個并行運行的線程打開一次。對于每個并行線程，將分配一個表結構、一個每個列的列結構和大小為3 * *N*的緩存區(其中*N*是最大行的長度，而不是計算BLOB列)。一個BLOB列需要5至8個字節加上BLOB數據的長度。MyISAM存儲引擎維護一個額外的行緩存區供內部應用。 ·???????? 對于每個具有BLOB列的表，將對緩存區進行動態擴大以讀入大的BLOB值。如果你掃描一個表，則分配一個與最大的BLOB值一樣大的緩存區。 ·???????? 所有使用的表的句柄結構保存在高速緩存中并以FIFO管理。默認情況，高速緩存有64個入口。如果某個表同時被兩個運行的線程使用，高速緩存則為該提供兩個入口。參見[7.4.9節，“MySQL如何打開和關閉表”](# "7.4.9.?How MySQL Opens and Closes Tables")。 ·???????? 當并行執行的線程結束時，FLUSH TABLE語句或**mysqladmin flush-table**命令可以立即關閉所有不使用的表并將所有使用中的表標記為已經關閉。這樣可以有效釋放大多數使用中的內存。FLUSH TABLE在關閉所有表之前不返回結果。 ps和其它系統狀態程序可以報導mysqld使用很多內存。這可以是在不同的內存地址上的線程棧造成的。例如，Solaris版本的ps將棧間未用的內存算作已用的內存。你可以通過用swap -s檢查可用交換區來驗證它。我們用商業內存漏洞探查器測試了mysqld，因此應該有沒有內存漏洞。 ### 7.5.6.?MySQL如何使用DNS 當新的客戶連接**mysqld**時，**mysqld**創建一個新的線程來處理請求。該線程先檢查是否主機名在主機名緩存中。如果不在，線程試圖解析主機名： ·???????? 如果操作系統支持線程安全gethostbyaddr_r ()和gethostbyname_r()調用，線程使用它們來執行主機名解析。 ·???????? 如果操作系統不支持線程安全調用，線程鎖定一個互斥體并調用gethostbyaddr()和gethostbyname()。在這種情況下，在第1個線程解鎖互斥體前，沒有其它線程可以解析不在主機名緩存中的主機名。你可以用--skip-name-resolve選項啟動**mysqld**來禁用DNS主機名查找。然而，在這種情況下，你只可以使用MySQL中的授權表中的IP號。如果你有一個很慢的DNS和許多主機，你可以通過用--skip-name-resolve禁用DNS查找或增加HOST_CACHE_SIZE定義(默認值：128)并重新編譯**mysqld**來提高性能。你可以用--skip-host-cache選項啟動服務器來禁用主機名緩存。要想清除主機名緩存，執行FLUSH HOSTS語句或執行**mysqladmin flush-hosts**命令。如果你想要完全禁止TCP/IP連接，用--skip-networking選項啟動**mysqld**。 ### 7.6.?磁盤事宜 [ 7.6.1. 使用符號鏈接](#) ·???????? 磁盤搜索是巨大的性能瓶頸。當數據量變得非常大以致于緩存性能變得不可能有效時，該問題變得更加明顯。對于大數據庫，其中你或多或少地隨機訪問數據，你可以確信對讀取操作需要至少一次硬盤搜索，寫操作需要多次硬盤搜索。要想使該問題最小化，應使用搜索次數較少的磁盤。 ·???????? 通過鏈接文件到不同的磁盤或對硬盤分段來增加可用磁盤錠數量（因此降低搜索成本）： o??????? 使用符號鏈接這說明，對于MyISAM表，你符號鏈接索引文件和/或數據文件，從它們數據目錄內的通常位置到另一個硬盤（也可以被條紋化）。這將使搜索和讀次數達到最好，假定硬盤不再為其它目的使用。參見[7.6.1節，“使用符號鏈接”](# "7.6.1.?Using Symbolic Links")。 o??????? 分條分條意味著你有許多磁盤，將第1個塊放到第1個硬盤，第2個塊放到第2個磁盤，并且第*N*塊在（*N* mod number_of_disks）磁盤上等等。這意味著如果正常數據大小小于分條大小（或完全匹配），能夠得到最佳性能。分條完全取決于操作系統和分條大小，因此用不同的條紋大小對應用程序進行基準測試。參見[7.1.5節，“使用自己的基準”](# "7.1.5.?Using Your Own Benchmarks")。分條的不同速度完全依賴于參數。依賴于怎樣設置條紋參數和硬盤數量，可以根據不同數量級別得到不同的標準。你必須進行選擇以便優化隨機或順序存取。 ·???????? 為了高可靠性你可能想使用RAID 0+1（條紋加鏡像），但在這種情況下，需要2*N塊磁盤來保持N個磁盤的數據。如果你肯為它花錢，這可能是最好的選項。然而，你可能還必須投資一部分資金到卷管理軟件中以便有效地管理它。 ·???????? 一個較好的選擇是根據數據類型的重要性程度改變RAID級別。例如，保存可以在RAID 0硬盤上重新生成的不太重要的數據，但保存真正重要數據（例如主機信息和日志）到RAID 0+1或RAID N硬盤。如果你有許多寫操作，RAID N可能會存在問題，因為需要時間來更新校驗位。 ·???????? 在Linux上，通過配置磁盤接口時使用hdparm，你可以獲得更多的性能。（在一般負載下達到100%并不困難。）下面的hdparm選項應該非常適用于MySQL，并且可能適用于許多其它應用程序： ~~~ ???? hdparm -m 16 -d 1 ~~~ 請注意，當使用該命令時，性能和可靠性依賴于硬件，因此我們強烈建議，使用hdparm后，完全測試你的系統。為獲取更多詳細信息，請查閱hdparm手冊幫助頁。如果hdparm使用的不好，會導致文件系統破壞，因此試驗之前請做好每個備份! ·???????? 也可以設置數據庫使用的文件系統參數：如果不需要知道文件最后一次訪問的時間（這對數據庫服務器并沒有實際的用途），可以用-o noatime選項安裝文件系統。這將跳過對文件系統中的節點的最后一次訪問時間的更新，從而能夠避免一些硬盤搜索。在許多操作系統中，用-o async選項安裝，可以將文件系統設置為異步更新。如果電腦相當穩定，這應該給予你更高的性能而不需要犧牲太多的可靠性。（該標志是Linux中默認開啟。） ### 7.6.1.?使用符號鏈接 [7.6.1.1. 在Unix上使用針對數據庫的符號鏈接](#)[7.6.1.2. 在Unix平臺上使用表的符號鏈接 ](#)[7.6.1.3. 在Windows平臺上使用關于數據庫的符號鏈接](#) 你可以將表和數據庫從數據庫目錄移動到其它的位置并且用指向新位置的符號鏈接進行替換。你可能需要這樣作，例如，移動數據庫到一個有更多空閑空間的文件系統中或通過將表分散到不同的磁盤上以增加系統的訪問速度。推薦的方法值需要將數據庫通過符號鏈接指到不同的磁盤。符號鏈接表僅作為是最后的辦法。 #### 7.6.1.1.?在Unix上使用針對數據庫的符號鏈接在Unix中，符號鏈接一個數據庫的方法是，首先在一些有空閑空間的硬盤上創建一個目錄，然后從MySQL數據目錄中創建它的一個符號鏈接。 ~~~ shell> mkdir /dr1/databases/test ~~~ ~~~ shell> ln -s /dr1/databases/test /path/to/datadir ~~~ MySQL不支持鏈接一個目錄到多個數據庫。用一個符號鏈接替換一個數據庫目錄，只要你不在數據庫之間制作符號鏈接。假定你有一個數據庫db1在MySQL數據目錄下，然后生成一個符號鏈接db2指向 db1： ~~~ shell> cd /path/to/datadir ~~~ ~~~ shell> ln -s db1 db2 ~~~ 對于db1中的任何表tbl_a，在db2也出現并且還是表tbl_a。如果一個客戶更新db1.tbl_a并且另一個客戶更新db2.tbl_a，可能會出現問題。然而，如果你真的需要這樣做，可以通過改變源文件mysys/my_symlink.c來實現，在該文件中你應查找下面的語句： if (!(MyFlags & MY_RESOLVE_LINK) || ??? (!lstat(filename,&stat_buff) && S_ISLNK(stat_buff.st_mode))) 將該語句修改為： if (1) 請注意，對于所有的Windows服務器，默認啟用符號鏈接支持。 #### 7.6.1.2.?在Unix平臺上使用表的符號鏈接 ? 你不應在沒有完全可操作的realpath()調用的系統中對表進行符號鏈接。（Linux和Solaris支持realpath())。可以通過發出一個SHOW VARIABLES LIKE 'have_symlink'語句，檢查系統是否支持符號鏈接。只有MyISAM表完全支持符號鏈接。對于其它表類型，如果試圖在操作系統中的文件上用前面的任何語句使用符號鏈接，可能會出現奇怪的問題。對于MyISAM表的符號鏈接的處理如下： ·???????? 在數據目錄指，一定會有表定義文件、數據文件和索引文件。數據文件和索引文件可以移到別處和在數據目錄中符號鏈接替代。表定義文件不能進行符號鏈接替換。 ·???????? 可以分別通過符號鏈接將數據文件和索引文件指到不同的目錄。 ·???????? 如果**mysqld**沒有運行，符號鏈接可以從服務器命令行使用ln -s手動完成。同樣，通過使用DATA DIRECTORY和INDEX DIRECTORY選項創建表，你可以指示運行的MySQL服務器執行符號鏈接。參見[13.1.5節，“CREATE TABLE語法”](# "13.1.5.?CREATE TABLE Syntax")。 ·???????? ** myisamchk**不用數據文件或索引文件替換符號鏈接。它直接工作在符號鏈接指向的文件。任何臨時文件創建在數據文件或索引文件所處的目錄中。 ·???????? **注釋：**當你刪掉一個表時，如果該表使用了符號鏈接，*符號鏈接和該符號鏈接指向的文件都被刪除掉*。這就是你不應以系統root用戶運行**mysqld**或允許系統用戶對MySQL數據庫目錄有寫訪問權限的原因。 ·???????? 如果你用ALTER TABLE ... RENAME重命名一個表并且不將表移到另一個數據庫，數據庫目錄中的符號鏈接被重新命名為一個新名字并且數據文件和索引文件也相應地重新命名。 ·???????? 如果你用ALTER TABLE ... RENAME移動一個表到另一個數據庫，表移動到另一個數據庫目錄。舊的符號鏈接和其所指向的文件被刪除。換句話說，新表不再被鏈接。 ·???????? 如果不使用符號鏈接，你應對**mysqld**使用--skip-symbolic-links選項以確保沒有人能夠使用**mysqld**來刪除或重新命名數據目錄之外的文件。表符號鏈接還不支持以下操作： ·???????? ALTER TABLE忽略DATA DIRECTORY 和INDEX DIRECTORY表選項。 ·???????? BACKUP TABLE 和RESTORE TABLE不考慮符號鏈接。 ·???????? .frm文件必須*絕不能是一個符號*鏈接（如前面所述，只有數據和索引文件可以是符號鏈接）。如果試圖這樣做（例如，生成符號鏈接）會產生不正確的結果。假定你在MySQL數據目錄下有一個數據庫db1，該數據庫有一個表tbl1，并且在db1目錄中你制作了一個符號鏈接tbl2指向tbl1： ~~~ ·??????????????? shell> cd /path/to/datadir/db1 ~~~ ~~~ ·??????????????? shell> ln -s tbl1.frm tbl2.frm ~~~ ~~~ ·??????????????? shell> ln -s tbl1.MYD tbl2.MYD ~~~ ~~~ ·??????????????? shell> ln -s tbl1.MYI tbl2.MYI ~~~ 如果一個線程讀取db1.tbl1同時另一個線程更新 db1.tbl2會發生問題： o??????? 查詢緩存將變為“傻瓜” (它沒有辦法知道tbl1是否被更新，因此它返回過時的結果)。 o??????? tbl2上的ALTER語句也會失敗。 #### 7.6.1.3.?在Windows平臺上使用關于數據庫的符號鏈接 Windows的**mysqld-max**和mysql-max-nt服務器使用-DUSE_SYMDIR選項編譯成。允許你放置數據庫目錄到一個不同的硬盤，通過設置一個符號鏈接指向它。這類似于Unix中的符號鏈接，盡管設置鏈接的過程不同。符號鏈接默認為啟用。如果你不需要，使用skip-symbolic-links選項來禁用它： ~~~ [mysqld] ~~~ skip-symbolic-links 在Windows中，通過在數據目錄中創建一個文件，該文件包含目標目錄的路徑，你可以為MySQL數據庫創建一個符號鏈接。該文件應該被命名為db_name.sym，其中*db_name*是數據庫名。假定MySQ數據目錄是C:\mysql\data并且你想要數據庫foo放置在D:\data\foo。設置一個符號鏈接如下所示： 1.???確保D:\data\foo目錄存在，如果必要創建它。如果你在數據目錄內有一個命名為foo的數據庫目錄，你應移動它到D:\data目錄。否則，符號鏈接無效。為避免出現問題，當你移動數據庫目錄時服務器不應該運行。 2.???創建一個文本文件C:\mysql\data\foo.sym，該本文文件包含路徑名D:\data\foo\。此后，數據庫foo創建的所有表將創建在D:\data\foo文件中。*請注意，如果在MySQL數據目錄中存在同名的數據庫目錄，不能使用符號鏈接*。這是MySQL參考手冊的翻譯版本，關于MySQL參考手冊，請訪問[dev.mysql.com](http://dev.mysql.com/doc/mysql/en)。原始參考手冊為英文版，與英文版參考手冊相比，本翻譯版可能不是最新的。