第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. 最佳化概述

使一個系統更快的最重要因素當然是基本設計。此外,還需要知道系統正做什麼樣的事情,以及瓶頸是什麼。

最常見的系統瓶頸是:

·         內存帶寬。當CPU需要的數據超出CPU緩存時,主緩存帶寬就成為內存的一個瓶頸。這在大多數系統正是一個不常見的瓶頸但是您應該知道它。

7.1.1. MySQL設計局限與折衷

當使用MyISAM儲存引擎時,MySQL使用極快速的資料表鎖定,以便允許多次讀或一次寫。使用該儲存引擎的最大問題出現在同一個資料表中進行混合穩定數據流更新與慢速選擇。如果這只是某些資料表的問題,您可以使用另一個儲存引擎。參見第15章:儲存引擎和資料表類型

MySQL可以使用事務資料表和非事務資料表。為了更容易地讓非事務資料表順利工作(如果出現問題不能回滾)MySQL採用下述規則。請注意這些規則只適用於不運行在嚴格模式下或為INSERTUPDATE使用IGNORE規定程式時。

·         所有列有預設值。請注意當運行在嚴格SQL模式(包括TRADITIONAL SQL模式)時,必須為NOT NULL列指定預設值。

·         如果向列內插入不合適的或超出範圍的值,MySQL將該列設定為「最好的可能的」,而不是報告錯誤。對於數字值,為0、可能的最小值或最大值。對於字串,為空字串或列內可以保存的字串。請注意當運行在嚴格模式或TRADITIONAL SQL模式時該行為不 適用。

·         所有資料表達式的計算結果返回一個資料表示錯誤狀況的信號。例如,1/0返回NULL(使用ERROR_FOR_DIVISION_BY_ZERO SQL模式可以更改該行為)

如果正使用非事務資料表,不應該使用MySQL來檢查列的內容。一般情況,最安全的(通常是最快的)方法徑是讓應用程式確保只向資料庫傳遞合法值。

相關詳細訊息參見1.8.6節,「MySQL處理約束的方式」13.2.4節,「INSERT語法」5.3.2節,「SQL伺服器模式」

7.1.2. 為可移植性設計應用程式

因為不同SQL伺服器實現了標準SQL的不同部分,需要花功夫來編寫可移植的SQL應用程式。對很簡單的選擇/插入,很容易實現移植,但是需要的功能越多則越困難。如果想要應用程式對很多資料庫系統都快,它變得更難!

為了使一個複雜應用程式可移植,您需要選擇它應該工作的SQL伺服器,並確定這些伺服器支援什麼特性。

所有資料庫都有一些弱點。這就是它們不同的設計折衷導致的不同行為。

可以使用MySQLcrash-me程式來找出能用於資料庫伺服器選擇的函數、類型和限制。crash-me並不能找出所有的特性,但是其廣度仍然很合理,可以進行大約450個測試。

crash-me可以提供的一種類型的訊息的例子:如果想要使用InformixDB2,不應該使用超過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/benchmarks/看這個基準的結果。

如果您為資料庫的獨立性而努力,需要很好地瞭解每個SQL伺服器的瓶頸。例如,MySQL在檢索和更新MyISAM資料表記錄方面很快,但是在同一個資料表上混合慢速讀者和寫者方面有一個問題。另一方面,當您試圖訪問最近更新了(直到它們被刷新到磁盤上)的行時,在Oracle中有一個很大的問題。事務資料庫總的來說在從記錄檔案資料表中生成總結資料表方面不是很好,因為在這種情況下,行鎖定幾乎沒有用。

為了使應用程式「確實」獨立於資料庫,需要定義一個容易延伸的接口,用它可操縱數據。因為C++在大多數系統上可以適用,使用資料庫的一個C++ 類接口是有意義的。

如果您使用某個資料庫特定的功能(例如MySQL專用的REPLACE語句),應該為SQL伺服器編碼一個方法以實現同樣的功能。儘管慢些,但確允許其它伺服器執行同樣的任務。

MySQL,可以使用/*! */語法把MySQL特定的關鍵詞加到查詢中。在/**/中的代碼將被其它大多數SQL伺服器視為註釋(並被忽略)

如果高性能真的比準確性更重要,就像在一些web應用程式那樣,一種可行的方法是建立一個應用層,緩存所有的結果以便得到更高的性能。通過只是讓舊的結果在短時間後『過期』,能保持緩存合理地刷新。這在極高負載的情況下是相當不錯的,在此情況下,能動態地增加緩存並且設定較高的過期時限直到一切恢復正常。

在這種情況下,資料表建立訊息應該包含緩存初始大小和資料表刷新頻率等訊息。

實施應用程式緩存的一種方法是使用MySQL查詢緩存。啟用查詢緩存後,伺服器可以確定是否可以重新使用查詢結果。這樣簡化了您的應用程式。參見5.13節,「MySQL查詢高速緩衝」

7.1.3. 我們已將MySQL用在何處?

該節描述了Mysql的早期應用程式。

MySQL最初開發期間,MySQL的功能適合大多數客戶。MySQL為瑞典的一些最大的零售商處理數據倉庫。

我們從所有商店得到所有紅利卡交易的每週總結,並且我們期望為所有店主提供有用的訊息以幫助他們得出他們的廣告戰如何影響他們的顧客。

數據是相當巨量的(大約每月7百萬宗交易總結)並且我們保存4-10年來的數據需要呈現給用戶。我們每週從顧客那裡得到請求,他們想要「立刻」訪問來自該數據的新報告。

我們通過每月將所有訊息儲存在壓縮的「交易」資料表中來解決它。我們有一套簡單的宏/指令用來生成來自交易資料表的不同條件( 產品組、顧客id,商店...)的總結資料表。報告是由一個進行語法分析網頁的小perl指令動態生成的網頁,在指令中執行SQL語句並且插入結果。我們很想使用PHPmod_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/下載分發,或者使用當前的開發原始碼樹(參見2.8.3節,「從開發原始碼樹安裝」)

·         基準指令用Perl編寫而成,使用Perl DBI模塊訪問資料庫伺服器,因此必須安裝DBI。還需要為每個待測試的伺服器提供伺服器專用DBD驅動程式。例如,要測試MySQLPostgreSQLDB2,必須安裝DBD::mysqlDBD::PgDBD::DB2模塊。參見2.13節,「Perl安裝注意事項」

獲得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發現許多不同資料庫伺服器的crash-me的結果。關於基準測試結果的詳細訊息,訪問http://dev.mysql.com/tech-resources/benchmarks/

7.1.5. 使用自己的基準

一定要測試應用程式和資料庫,以發現瓶頸在哪兒。通過修正它(或通過用一個「啞模塊」代替瓶頸),可以很容易地確定下一個瓶頸。即使您的應用程式的整體性能目前可以接受,至少應該對每個瓶頸做一個計劃,如果某天確實需要更好的性能,應知道如何解決它。

關於一些可移植的基準程式的例子,參見MySQL基準套件。請參見7.1.4節,「MySQL基準套件」。可以利用這個套件的任何程式並且根據您的需要修改它。通過這樣做,可以嘗試不同的問題的解決方案並測試哪一個是最好的解決方案。

另一個免費基準套件是開放原始碼資料庫基準套件,參見http://osdb.sourceforge.net/

在系統負載繁重時出現一些問題是很普遍的,並且很多客戶已經與我們聯繫了,他們在生產系統中有一個(測試)系統並且有負載問題。大多數情況下,性能問題經證明是與基本資料庫設計有關的問題(例如,資料表掃瞄在高負載時資料表現不好)或作業系統或庫問題。如果系統已經不在生產系統中,它們大多數將容易修正。

為了避免這樣的問題,應該把工作重點放在在可能最壞的負載下測試您的整個應用程式。您可以使用Super Smack。該工具可以從http://jeremy.zawodny.com/mysql/super-smack/獲得。正如它的名字所建議,它可以根據您的需要提供合理的系統,因此確保只用於您的開發系統。

7.2. 最佳化SELECT語句和其它查詢

首先,影響所有語句的一個因素是:您的授權設置得越複雜,所需要的開銷越多。

執行GRANT語句時使用簡單的授權,當客戶執行語句時,可以使MySQL降低授權檢查開銷。例如,如果未授予任何資料表級或列級權限,伺服器不需要檢查tables_privcolumns_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_nameDESCRIBE tbl_nameSHOW COLUMNS FROM tbl_name的一個同義詞。

·         如果在SELECT語句前放上關鍵詞EXPLAINMySQL將解釋它如何處理SELECT,提供有關資料表如何聯接和聯接的次序。

該節解釋EXPLAIN的第2個用法。

借助於EXPLAIN,可以知道什麼時候必須為資料表加入索引以得到一個使用索引來尋找記錄的更快的SELECT

如果由於使用不正確的索引出現了問題,應運行ANALYZE TABLE更新資料表的統計(例如關鍵字集的勢),這樣會影響最佳化器進行的選擇。參見13.5.2.1節,「ANALYZE TABLE語法」

還可以知道最佳化器是否以一個最佳次序聯接資料表。為了強制最佳化器讓一個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 KEYUNIQUE索引的所有部分時。在下面的查詢中,tbl_name可以用於const資料表:

SELECT * from tbl_name WHERE primary_key=1
 
SELECT * from tbl_name
WHERE primary_key_part1=1primary_key_part2=2

o        eq_ref

對於每個來自於前面的資料表的行組合,從該資料表中讀取一行。這可能是最好的聯接類型,除了const類型。它用在一個索引的所有部分被聯接使用並且索引是UNIQUEPRIMARY 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

對於每個來自於前面的資料表的行組合,所有有匹配索引值的行將從這張資料表中讀取。如果聯接只使用鍵的最左邊的前綴,或如果鍵不是UNIQUEPRIMARY 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

o        index_merge

該聯接類型資料表示使用了索引合併最佳化方法。在這種情況下,key列包含了使用的索引的清單,key_len包含了使用的索引的最長的關鍵元素。詳細訊息參見7.2.6節,「索引合併最佳化」

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語法」

為了看清一張資料表有什麼索引,使用SHOW INDEX FROM tbl_name

·         key

key列顯示MySQL實際決定使用的鍵(索引)。如果沒有選擇索引,鍵是NULL。要想強制MySQL使用或忽視possible_keys列中的索引,在查詢中使用FORCE INDEXUSE INDEX或者IGNORE INDEX。參見13.2.7節,「SELECT語法」

對於MyISAMBDB資料表,運行ANALYZE TABLE可以幫助最佳化器選擇更好的索引。對於MyISAM資料表,可以使用myisamchk --analyze。參見13.5.2.1節,「ANALYZE TABLE語法」5.9.4節,「資料表維護和崩潰恢復」

·         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中的行。如果MySQLt2中發現一個匹配的行,它知道t2.id絕不會為NULL,並且不再掃瞄t2內有相同的id值的行。換句話說,對於t1的每個行,MySQL只需要在t2中搜尋一次,無論t2內實際有多少匹配的行。

o        range checked for each record (index map: #)

MySQL沒有發現好的可以使用的索引,但發現如果來自前面的資料表的列值已知,可能部分索引可以使用。對前面的資料表的每個行組合,MySQL檢查是否可以使用rangeindex_merge訪問方法來索取行。關於適用性標準的描述參見7.2.5節,「範圍最佳化」7.2.6節,「索引合併最佳化」,不同的是前面資料表的所有列值已知並且認為是常量。

這並不很快,但比執行沒有索引的聯接要快得多。

o        Using filesort

MySQL需要額外的一次傳遞,以找出如何按排序順序檢索行。通過根據聯接類型瀏覽所有行並為所有匹配WHERE子句的行保存排序關鍵字和行的指針來完成排序。然後關鍵字被排序,並按排序順序檢索行。參見7.2.12節,「MySQL如何最佳化ORDER BY

o        Using index

從只使用索引樹中的訊息而不需要進一步搜索讀取實際的行來檢索資料表中的列訊息。當查詢只使用作為單一索引一部分的列時,可以使用該策略。

o        Using temporary

為了解決查詢,MySQL需要建立一個臨時資料表來容納結果。典型情況如查詢包含可以按不同情況列出列的GROUP BYORDER BY子句時。

o        Using where

WHERE子句用於限制哪一個行匹配下一個資料表或發送到客戶。除非您專門從資料表中索取或檢查所有行,如果Extra值不為Using where並且資料表聯接類型為ALLindex,查詢可能會有一些錯誤。

如果想要使查詢盡可能快,應找出Using filesort Using temporaryExtra值。

o        Using sort_union(...), Using union(...), Using intersect(...)

這些函數說明如何為index_merge聯接類型合併索引掃瞄。詳細訊息參見7.2.6節,「索引合併最佳化」

o        Using index for group-by

類似於訪問資料表的Using index方式,Using index for group-by資料表示MySQL發現了一個索引,可以用來查詢GROUP BYDISTINCT查詢的所有列,而不要額外搜索硬盤訪問實際的資料表。並且,按最有效的方式使用索引,以便對於每個組,只讀取少量索引條目。詳情參見7.2.13節,「MySQL如何最佳化GROUP BY

通過相乘EXPLAIN輸出的rows列的所有值,您能得到一個關於一個聯接如何的提示。這應該粗略地告訴您MySQL必須檢查多少行以執行查詢。當您使用max_join_size變數限制查詢時,也用這個乘積來確定執行哪個多資料表SELECT語句。參見7.5.2節,「調節伺服器參數」

下列例子顯示出一個多資料表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;

對於這個例子,假定:

·         被比較的列聲明如下:

資料表

列類型

tt

ActualPC

CHAR(10)

tt

AssignedPC

CHAR(10)

tt

ClientID

CHAR(10)

et

EMPLOYID

CHAR(15)

do

CUSTNMBR

CHAR(15)

·         資料表有下面的索引:

資料表

索引

tt

ActualPC

tt

AssignedPC

tt

ClientID

et

EMPLOYID(主鍵)

do

CUSTNMBR(主鍵)

·         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能更高效地在聲明具有相同類型和尺寸的列上使用索引。在本文中,VARCHARCHAR是相同的,除非它們聲明為不同的長度。因為tt.ActualPC被聲明為CHAR(10)並且et.EMPLOYID被聲明為CHAR(15),長度不匹配。

為了修正在列長度上的不同,使用ALTER TABLEActualPC的長度從10個字元變為15個字元:

mysql> ALTER TABLE tt MODIFY ActualPC VARCHAR(15);

現在tt.ActualPCet.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.EMPLOYIDtt.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次搜索來更新這個索引並且寫入行。

注意,上述討論並不意味著應用程式的性能將緩慢地以logN 退化!當資料表格變得更大時,所有內容緩存到OSSQL伺服器後,將僅僅或多或少地更慢。在數據變得太大不能緩存後,將逐漸變得更慢,直到應用程式只能進行磁盤搜索(logN增加)。為了避免這個問題,隨數據增加而增加 鍵高速緩衝區大小。對於MyISAM資料表, key_buffer_size系統變數控制 鍵高速緩衝區大小。參見7.5.2節,「調節伺服器參數」

7.2.3. SELECT查詢的速度

總的來說,要想使一個較慢速SELECT ... WHERE更快,應首先檢查是否能增加一個索引。不同資料表之間的引用通常通過索引來完成。您可以使用EXPLAIN語句來確定SELECT語句使用哪些索引。參見7.4.5節,「MySQL如何使用索引」7.2.1節,「EXPLAIN語法(獲取關於SELECT的訊息)

下面是一些加速對MyISAM資料表的查詢的一般建議:

·         為了幫助MySQL更好地最佳化查詢,在一個裝載數據後的資料表上運行ANALYZE TABLEmyisamchk --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語句,但相同的最佳化也適用DELETEUPDATE語句中的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 AND b=c) AND a=5
·                -> 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

·         索引使用的常數資料表達式僅計算一次。

  • 對於MyISAMHEAP資料表,在一個單個資料表上的沒有一個WHERECOUNT(*)直接從資料表中檢索訊息。當僅使用一個資料表時,對NOT NULL資料表達式也這樣做。
  • 無效常數資料表達式的早期檢測。MySQL快速檢測某些SELECT語句是不可能的並且不返回行。
  • 如果不使用GROUP BY或分組函數(COUNT()MIN()……)HAVINGWHERE合併。
  • 對於聯接內的每個資料表,構造一個更簡單的WHERE以便更快地對資料表進行WHERE計算並且也盡快跳過記錄。
  • 所有常數的資料表在查詢中比其它資料表先讀出。常數資料表為:
    • 空資料表或只有1行的資料表。
    • 與在一個PRIMARY KEYUNIQUE索引的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 BYGROUP BY的列來自同一個資料表,那麼當聯接時,該資料表首先被選中。
  • 如果有一個ORDER BY子句和不同的GROUP BY子句,或如果ORDER BYGROUP BY包含聯接隊列中的第一個資料表之外的其它資料表的列,則建立一個臨時資料表。
  • 如果使用SQL_SMALL_RESULTMySQL使用內存中的一個臨時資料表。
  • 每個資料表的索引被查詢,並且使用最好的索引,除非最佳化器認為使用資料表掃瞄更有效。是否使用掃瞄取決於是否最好的索引跨越超過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. 範圍最佳化

range訪問方法使用單一索引來搜索包含在一個或幾個索引值距離內的資料表記錄的子集。可以用於單部分或多元素索引。後面的章節將詳細描述如何從WHERE子句提取區間。

7.2.5.1. 單元素索引的範圍訪問方法

對於單元素索引,可以用WHERE子句中的相應條件很方便地資料表示索引值區間,因此我們稱為範圍條件而不是「區間」。

單元素索引範圍條件的定義如下:

·         對於BTREEHASH索引,當使用=<=>INIS NULL或者IS NOT NULL操作符時,關鍵元素與常量值的比較關係對應一個範圍條件。

·         對於BTREE索引,當使用><>=<=BETWEEN!=或者<>,或者LIKE 'pattern'(其中 'pattern'不以通配符開始)操作符時,關鍵元素與常量值的比較關係對應一個範圍條件。

·         對於所有類型的索引,多個範圍條件結合ORAND則產生一個範圍條件。

前面描述的「量值」系指:

·         查詢字串中的常量

·         同一聯接中的constsystem資料表中的列

·         無關聯子查詢的結果

·         完全從前面類型的子資料表達式組成的資料表達式

下面是一些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 = 4key1 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.    取消總是為truefalse的條件:

·         (key1 LIKE 'abcde%' OR TRUE)總是true

·         (key1 < 'uux' AND key1 > 'z')總是false

用常量替換這些條件,我們得到:

(key1 < 'abc' AND TRUE) OR (key1 < 'bar' AND TRUE) OR (FALSE)

刪除不必要的TRUEFALSE常量,我們得到

(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_part1key_part2key_part3) < (1+inf+inf)

範圍包括前面數據集中的第456個元組,可以用於範圍訪問方法。

通過對比,條件key_part3 = 'abc'不定義單一的區間,不能用於範圍訪問方法。

下面更加詳細地描述了範圍條件如何用於多元素索引中。

·         對於HASH索引,可以使用包含相同值的每個區間。這說明區間只能由下面形式的條件產生:

·                     key_part1 cmp const1
·                 AND key_part2 cmp const2
·                 AND ...
·                AND key_partN cmp constN;

這裡,const1const2...為常量,cmp=<=>或者IS NULL比較操作符之一,條件包括所有索引部分。(也就是說,有N 個條件,每一個對應N-元素索引的每個部分)

關於常量的定義,參見7.2.5.1節,「單元素索引的範圍訪問方法」

例如,下面為三元素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'1010)
   < (key_part1key_part2key_part3)
      < ('foo'+inf+inf)

建立的區間可以比原條件包含更多的記錄。例如,前面的區間包括值('foo'110),不滿足原條件。

·         如果包含區間內的一系列記錄的條件結合使用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_part1key_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.6. 索引合併最佳化

索引合併方法用於通過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 INDEXFORCE 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

·         任何InnoDBBDB資料表的主鍵的範圍條件。

下面是一些例子:

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;

如果使用的索引未包括查詢中使用的所有列,只有滿足所有使用的關鍵字的範圍條件才搜索所有記錄。

如果某個合併條件是InnoDBBDB資料表的主鍵的一個條件,不用於記錄查詢,但用於過濾使用其它條件搜索的記錄。

7.2.6.2. 索引合併並集訪問算法

該算法的適用標準類似於索引合併方法交集算法的標準。算法可以用於當WHERE子句結合OR被轉換為不同的關鍵字的幾個範圍條件的時候,每個條件為下面之一:

·         以這種形式,即索引有確切的N部分(即包括了所有索引部分)

·                key_part1=const1 AND key_part2=const2 ... AND key_partN=constN

·         任何InnoDBBDB資料表的主鍵的範圍條件。

·         索引合併方法交集算法適用的一個條件。

下面是一些例子:

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的列ab有一個索引:

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 BYDISTINCT需要一個臨時資料表。

請注意因為DISTINCT可能使用GROUP BY,必須清楚MySQL如何使用所選定列的一部分的ORDER BYHAVING子句中的列。參見12.10.3節,「具有隱含字段的GROUP BY」

在大多數情況下,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

結合LIMIT row_countDISTINCT後,MySQL發現唯一的row_count行後立即停止。

如果不使用查詢中命名的所有資料表的列,MySQL發現第1個匹配後立即停止掃瞄未使用的資料表。在下面的情況中,假定t1t2之前使用(可以用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執行過程如下:

·         根據資料表AA依賴的所有資料表設置資料表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 JOINSTRAIGHT_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.column1NULLWHERE 子句將為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語法」中描述的聯接語法。

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

但是這兩個資料表達式不等效。要說明這點,假定資料表t1t2t3有下面的狀態:

·         資料表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 (t2t3) ON t1.a=t2.a

該資料表達式不能轉換為下面的資料表達式:

t1 LEFT JOIN t2 ON t1.a=t2.at3.

對於給定的資料表狀態,第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 |
+------+------+------+------+

因此,如果我們忽略聯接資料表達式中的括號連同外面的聯接操作符,我們會改變原資料表達式的結果。

更確切地說,我們不能忽視左外聯接操作的右操作數和右聯接操作的左操作數中的括號。換句話說,我們不能忽視外聯接操作中的內資料表達式中的括號。可以忽視其它操作數中的括號(外部資料表的操作數)

對於任何資料表t1t2t3和屬性t2.bt3.b的任何條件P,下面的資料表達式:

(t1,t2) LEFT JOIN t3 ON P(t2.b,t3.b)

等價於資料表達式

t1t2 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個查詢的(t2t3)的括號,儘管從理論上對查詢分析時不需要括號:這些查詢的語法結構將仍然不清楚,因為LEFT JOINON將充當資料表達式(t2,t3)的左、右界定符的角色)

前面的例子說明了這些點:

·         對於只包含內聯接(而非外聯接)的聯接資料表達式,可以刪除括號。您可以移除括號並從左到右評估(或實際上,您可以按任何順序評估資料表)

·         總的來說,對外聯接卻不是這樣。去除括號可能會更改結果。

·         總的來說,對外聯接和內聯接的結合,也不是這樣。去除括號可能會更改結果。

含嵌套外聯接的查詢按含內聯接的查詢的相同的管道方式執行。更確切地說,利用了嵌套環聯接算法。讓我們回憶嵌套環聯接執行查詢時採用什麼算法。

假定我們有一個如下形式的資料表T1T2T3的聯接查詢:

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)是資料表T1T2T3的列的一個條件。

嵌套環聯接算法將按下面的方式執行該查詢:

 

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資料表示「連接行t1t2t3的列組成的行」。在下面的一些例子中,出現行名的NULL資料表示NULL用於行的每個列。例如,t1||t2||NULL資料表示「連接行t1t2的列以及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,因為它用於外聯接中。T2T3用於內聯接中,因此聯接必須在內環中處理。但是,因為該聯接是一個內聯接,T2T3可以以任何順序處理。

當討論內聯接嵌套環的算法時,我們忽略了部分詳情,可能對查詢執行的性能的影響會很大。我們沒有提及所謂的「下推」條件。假定可以用連接公式資料表示我們的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,T2P(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條件拒絕nullMySQL可以將此類查詢轉換為沒有外聯接操作的查詢。如果為該操作構建的NULL補充的行評估為FALSEUNKNOWN,則該條件稱為對於某個外聯接操作拒絕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

對於原來的查詢,最佳化器將評估只與一個訪問順序T1T2T3兼容的方案。在替換的查詢中,還考慮了訪問順序T3T1T2

一個外聯接操作的轉化可以觸發另一個的轉化。這樣,查詢:

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

·         混合ASCDESC

·                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 BYGROUP BY資料表達式。

·         使用的資料表索引的類型不能按順序保存行。例如,對於HEAP資料表的HASH索引情況即如此。

通過EXPLAIN SELECT ...ORDER BY,可以檢查MySQL是否可以使用索引來解決查詢。如果Extra列內有Using filesort,則不能解決查詢。參見7.2.1節,「EXPLAIN語法(獲取關於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中路徑應用冒號(:)區間開,在WindowsNetWareOS/2中用分號()。可以使用該特性將負載均分到幾個目錄中。註釋:路徑應為位於不同物理硬盤上的檔案系統的目錄,而不是同一硬盤的不同的分區。

預設情況下,MySQL排序所有GROUP BY col1col2...查詢的方法如同在查詢中指定ORDER BY col1col2...。如果顯式包括一個包含相同的列的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

滿足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的訊息)), 鬆散索引掃瞄搜尋滿足範圍條件的每個組的第1個關鍵字,並且再次讀取盡可能最少數量的關鍵字。在下面的條件下是可以的:

·         查詢針對一個單資料表。

·         GROUP BY包括索引的第1個連續部分(如果對於GROUP BY,查詢有一個DISTINCT子句,則所有顯式屬性指向索引開頭)

·         只使用累積函數(如果有)MIN()MAX(),並且它們均指向相同的列。

·         索引的任何其它部分(除了那些來自查詢中引用的GROUP BY)必須為常數(也就是說,必須按常量數量來引用它們),但MIN()MAX() 函數的參數例外。

此類查詢的EXPLAIN輸出顯示Extra列的Using indexforgroup-by

下面的查詢提供該類的幾個例子,假定資料表t1(c1,c2,c3,c4)有一個索引idx(c1c2c3)

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 c1c2c3 FROM t1 WHERE c2 = 'a' GROUP BY c1c3;

·         GROUP BY不以關鍵字的第1個元素開始,但是有一個條件提供該元素的常量:

     SELECT c1c2c3 FROM t1 WHERE c1 = 'a' GROUP BY c2c3;

7.2.14. MySQL如何最佳化LIMIT

在一些情況中,當您使用LIMIT row_count而不使用HAVING時,MySQL將以不同方式處理查詢。

·         如果您用LIMIT只選擇一些行,當MySQL選擇做完整的資料表掃瞄時,它將在一些情況下使用索引。

·         如果您使用LIMIT row_countORDER BYMySQL一旦找到了排序結果的第一個row_count行,將結束排序而不是排序整個資料表。如果使用索引,將很快。如果必須進行檔案排序(filesort),必須選擇所有匹配查詢沒有LIMIT子句的行,並且在確定已經找到第1row_count行前,必須對它們的大部分進行排序。在任何一種情況下,一旦找到了行,則不需要再排序結果的其它部分,並且MySQL不再進行排序。

·         當結合LIMIT row_countDISTINCT時,MySQL一旦找到row_count個唯一的行,它將停止。

·         在一些情況下,GROUP BY能通過順序讀取鍵(或在鍵上做排序)來解決,然後計算摘要直到關鍵字的值改變。在這種情況下,LIMIT row_count將不計算任何不必要的GROUP BY值。

·         只要MySQL已經發送了需要的行數到客戶,它將放棄查詢,除非您正使用SQL_CALC_FOUND_ROWS

·         LIMIT 0將總是快速返回一個空集合。這對檢查查詢的有效性是有用的。當使用MySQL API時,它也可以用來得到結果列的列類型。(該技巧在MySQL Monitor中不工作,只顯示Empty set;應使用SHOW COLUMNSDESCRIBE

·         當伺服器使用臨時資料表來進行查詢時,使用LIMIT row_count子句來計算需要多少空間。

7.2.15. 如何避免資料表掃瞄

EXPLAIN的輸出顯示了當MySQL使用資料表掃瞄來解決查詢時使用的所有類型列。這通常在如下條件下發生:

·         資料表很小,掃瞄資料表比搜尋關鍵字速度快。這對於少於10行並且行較短的資料表比較普遍。

·         ONWHERE子句中沒有適用的索引列的約束。

·         正用常量值比較索引列,並且MySQL已經計算到(基於索引樹)常數覆蓋了資料表的很大部分並且資料表掃瞄將會比較快。參見7.2.4節,「MySQL怎樣最佳化WHERE子句

·         您正通過另一個列使用一個低的集的勢的關鍵字(許多行匹配關鍵字)。在這種情況下,MySQL假設通過使用關鍵字它可能會進行許多關鍵字搜尋,資料表掃瞄將會更快。

對於小資料表,資料表掃瞄通常合適。對於大資料表,嘗試下面的技巧以避免最佳化器錯選了資料表掃瞄:

·         使用ANALYZE TABLE tbl_name為掃瞄的資料表更新關鍵字分佈。參見13.5.2.1節,「ANALYZE TABLE語法」

·         對掃瞄的資料表使用FORCE INDEX告知MySQL,相對於使用給定的索引資料表掃瞄將非常耗時。參見13.2.7節,「SELECT語法」

·                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節,「伺服器系統變數」

7.2.16. INSERT語句的速度

插入一個記錄需要的時間由下列因素組成,其中的數字資料表示大約比例:

  • 連接:(3)
  • 發送查詢給伺服器:(2)
  • 分析查詢:(2)
  • 插入記錄:(1x記錄大小)
  • 插入索引:(1x索引)
  • 關閉:(1)

這不考慮打開資料表的初始開銷,每個並發運行的查詢打開

資料表的大小以logN (B)的速度減慢索引的插入。

加快插入的一些方法:

·         如果同時從同一個客戶端插入很多行,使用含多個VALUEINSERT語句同時插入幾行。這比使用單行INSERT語句快(在某些情況下快幾倍)。如果您正向一個非空資料表新增數據,可以調節bulk_insert_buffer_size變數,使數據插入更快。參見5.3.3節,「伺服器系統變數」

·         如果您從不同的客戶端插入很多行,能通過INSERT DELAYED語句加快速度。參見13.2.4節,「INSERT語法」

·         MyISAM,如果在資料表中沒有刪除的行,能在SELECT語句正在運行的同時插入行。

·         當從一個文本檔案裝載一個資料表時,使用LOAD DATA INFILE。這通常比使用很多INSERT語句快20倍。參見13.2.5節,「LOAD DATA INFILE語法」

·         當資料表有很多索引時,有可能要多做些工作使得LOAD DATA INFILE更快些。使用下列過程:

    1. 有選擇地用CREATE TABLE建立資料表。
    2. 執行FLUSH TABLES語句或命令mysqladmin flush-tables
    3. 使用myisamchk --keys-used=0 -rq /path/to/db/tbl_name。這將從資料表中取消所有索引的使用。
    4. LOAD DATA INFILE把數據插入到資料表中,因為不更新任何索引,因此很快。
    5. 如果只想在以後讀取資料表,使用myisampack壓縮它。參見15.1.3.3節,「壓縮資料表特性」
    6. myisamchk -r -q /path/to/db/tbl_name重新建立索引。這將在寫入磁盤前在內存中建立索引樹,並且它更快,因為避免了大量磁盤搜索。結果索引樹也被完美地平衡。
    7. 執行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語句即有多少索引緩存區刷新。如果能用一個語句插入所有的行,就不需要鎖定。

對於事務資料表,應使用BEGINCOMMIT代替LOCK TABLES來加快插入。

鎖定也將降低多連接測試的整體時間,儘管因為它們等候鎖定最大等待時間將上升。例如:

Connection 1 does 1000 inserts

Connections 2, 3, and 4 do 1 insert

Connection 5 does 1000 inserts

如果不使用鎖定,234將在15前完成。如果使用鎖定,234將可能不在15前完成,但是整體時間應該快大約40%

INSERTUPDATEDELETE操作在MySQL中是很快的,通過為在一行中多於大約5次連續不斷地插入或更新的操作加鎖,可以獲得更好的整體性能。如果在一行中進行多次插入,可以執行LOCK TABLES,隨後立即執行UNLOCK TABLES(大約每1000)以允許其它的線程訪問資料表。這也會獲得好的性能。

INSERT裝載數據比LOAD DATA INFILE要慢得多,即使是使用上述的策略。

·         為了對LOAD DATA INFILEINSERTMyISAM資料表得到更快的速度,通過增加key_buffer_size系統變數來擴大 鍵高速緩衝區。參見7.5.2節,「調節伺服器參數」

 

7.2.17. UPDATE語句的速度

更新查詢的最佳化同SELECT查詢一樣,需要額外的寫開銷。寫速度依賴於更新的數據大小和更新的索引的數量。沒有更改的索引不被更新。

使更改更快的另一個方法是推遲更改然後在一行內進行多次更新。如果鎖定資料表,同時做多個更新比一次做一個快得多。

請注意對使用動態記錄格式的MyISAM資料表,更新一個較長總長的記錄可能會切分記錄。如果經常這樣該,偶爾使用OPTIMIZE TABLE很重要。參見13.5.2.5節,「OPTIMIZE TABLE語法」

7.2.18. DELETE語句的速度

刪除一個記錄的時間與索引數量確切成正比。為了更快速地刪除記錄,可以增加鍵高速緩衝的大小。參見7.5.2節,「調節伺服器參數」

如果想要刪除一個資料表的所有行,使用TRUNCATE TABLE tbl_name 而不要用DELETE FROM tbl_name。參見13.2.9節,「TRUNCATE語法」

7.2.19. 其它最佳化技巧

該節列出了提高查詢速度的各種技巧:

·         使用持久的連接資料庫以避免連接開銷。如果不能使用持久的連接並且您正啟動許多新的與資料庫的連接,可能要更改thread_cache_size變數的值。參見7.5.2節,「調節伺服器參數」

·         總是檢查所有查詢確實使用已經在資料表中建立了的索引。在MySQL中,可以用EXPLAIN命令做到。參見7.2.1節,「EXPLAIN語法(獲取關於SELECT的訊息)

·         嘗試避免在頻繁更新的資料表上執行複雜的SELECT查詢,以避免與鎖定資料表有關的由於讀、寫衝突發生的問題。

·         對於沒有刪除的行的MyISAM資料表,可以在另一個查詢正從資料表中讀取的同時在末尾插入行。如果這很重要,應考慮按照避免刪除行的方式使用資料表。另一個可能性是在刪除大量行後運行OPTIMIZE TABLE。參見15.1節,「MyISAM儲存引擎」

·         要修復任何ARCHIVE資料表可以發生的壓縮問題,可以執行OPTIMIZE TABLE。參見15.8節,「ARCHIVE儲存引擎」

·         如果您主要按expr1expr2...順序檢索行,使用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資料表,應試圖避免所有變長列(VARCHARBLOBTEXT)。如果資料表包括單一的變長列則使用動態記錄格式。參見第15章:儲存引擎和資料表類型

·         只是因為行太大,將一張資料表分割為不同的資料表一般沒有什麼用處。為了訪問行,最大的性能衝擊是磁盤搜索以找到行的第一個字節。在找到數據後,大多數新型磁盤對大多數應用程式來說足夠快,能讀入整個行。確實有必要分割的唯一情形是如果它是使用動態記錄格式使之變為固定的記錄大小的MyISAM資料表(見上述),或如果您需要很頻繁地掃瞄資料表而不需要大多數列。參見第15章:儲存引擎和資料表類型

·         如果您需要很經常地計算結果,例如基於來自很多行的訊息的計數,引入一個新資料表並實時更新計數器可能更好一些。下面形式的更新會更快一些:

UPDATE tbl_name SET count_col=count_col+1 WHERE key_col=constant;

當您使用象MyISAM那樣的只有資料表級鎖定的MySQL儲存引擎(多重讀/單個寫)時,這確實很重要。這也給大多數資料庫較好的性能,因為行鎖定管理器在這種情況下有較少的事情要做。

·         如果您需要從大的記錄檔案資料表中收集統計訊息,使用總結性的資料表而不是掃瞄整個資料表。維護總結應該比嘗試做「實時」統計要快些。當有變化時從日誌重新生成新的總結資料表比改變運行的應用(取決於業務決策)要快得多。

  • 如果可能,應該將報告分類為「實時」或「統計」,這裡統計報告所需的數據僅僅基於從實際數據定期產生的總結資料表中產生。
  • 充分利用列有預設值的事實。只有當插入的值不同於預設值時,才明確地插入值。這減少MySQL需要做的語法分析從而提高插入速度。
  • 在一些情況下,包裝並儲存數據到一個BLOB列中是很方便的。在這種情況下,必須在您的應用中增加額外的代碼來打包/解包訊息,但是這種方法可以在某些階段節省很多訪問。當有不符合行和列資料表結構的數據時,這很實用。
  • 在一般情況下,應該嘗試以非冗余方式(查看資料庫理論中的第三正則形式)存數據,但是為了獲得更快的速度,可以複製訊息或建立總結資料表。
  • 儲存過程或UDF(用戶定義函數)可能是獲得更好性能的一個好方法,詳細訊息參見第20章:儲存程式和函數27.2節,「為MySQL新增新函數」
  • 總是能通過在應用程式中緩存查詢/答案並嘗試同時執行很多插入/更新來獲得一些好處。如果資料庫支援鎖定資料表(MySQLOracle),這應該有助於確保索引緩存只在所有更新後刷新一次。還可以利用MySQL的查詢緩存來獲得類似的結果;參見5.13節,「MySQL查詢高速緩衝」
  • 當不需要知道何時寫入數據時,使用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資料表的儲存格式」
  • 可能時使用MEMORY資料表以得到更快的速度。參見15.4節,「MEMORY (HEAP)儲存引擎」
  • Web伺服器中,圖像和其它二進制資產應該作為檔案儲存。也就是僅在資料庫中儲存的本檔案的引用而不是檔案本身。大多數Web伺服器在緩存檔案方面比資料庫內容要好得多,因此使用檔案一般要快得多。
  • 對經常訪問的不重要數據(如為沒有在Web 瀏覽器中啟用cookie的用戶最後顯示的標語的相關訊息)使用內存資料表。在許多Web應用程式環境中也可以使用用戶會話來處理可變狀態數據。
  • 在不同資料表中具有相同訊息的列應該被聲明為相同的並有相同的名字。嘗試使名字簡單化。例如,在customer資料表中使用name而不是customer_name。為了使名字能移植到其它SQL伺服器,應該使名字短於18個字元。
  • 如果確實需要很高的速度,應該研究一下不同SQL伺服器支援的數據儲存的低層接口!例如直接訪問MySQL MyISAM儲存引擎,比起使用SQL接口,速度可以提高2-5倍。為了能實現,數據必須與應用程式在同一台伺服器上,並且通常只應該被一個程序訪問(因為外部檔案鎖定確實很慢)。通過在MySQL伺服器中引進低層MyISAM命令能消除以上問題(如果需要,這可能是獲得更好性能的一個簡單的方法)。通過精心設計資料庫接口,應該能相當容易地支援這類最佳化。
  • 如果正使用數字數據,在許多情況下,從一個資料庫訪問訊息(使用實時連接)比訪問一個文本檔案快些。這是因為資料庫中的訊息比文本檔案更緊湊,因此這將涉及更少的磁盤訪問。還可以在應用程式中節省代碼,因為不須分析文本檔案來找出行和列的邊界。

·         通過複製可以提高某些操作的性能。可以在複製伺服器中分佈客戶的檢索以均分負載。為了防止備份時主伺服器變慢,可以使用一個從伺服器來備份。參見第6章:MySQL中的複製

·         DELAY_KEY_WRITE=1選項聲明MyISAM資料表可以使索引更新更快,因為在資料表關閉之前它們不刷新到硬盤上。不利之處是當資料表打開時如果殺掉伺服器,應確保用--myisam-recover選項運行伺服器保證沒有問題,或者在重啟伺服器之前運行myisamchk(然而,即使在這種情況下,應通過使用DELAY_KEY_WRITE保證不丟失數據,因為關鍵字訊息總是可以從數據行產生)

 

7.3. 鎖定事宜

7.3.1. 鎖定方法

MySQL 5.1支援對MyISAMMEMORY資料表進行資料表級鎖定,對BDB資料表進行頁級鎖定,對InnoDB資料表進行行級鎖定。

在許多情況下,可以根據培訓猜測應用程式使用哪類鎖定類型最好,但一般很難說出某個給出的鎖類型就比另一個好。一切取決於應用程式,應用程式的不同部分可能需要不同的鎖類型。

為了確定是否想要使用行級鎖定的儲存引擎,應看看應用程式做什麼並且混合使用什麼樣的選擇和更新語句。例如,大多數Web應用程式執行許多選擇,而很少進行刪除,只對關鍵字的值進行更新,並且只插入少量具體的資料表。基本MySQL MyISAM設置已經調節得很好。

MySQL中對於使用資料表級鎖定的儲存引擎,資料表鎖定時不會死鎖的。這通過總是在一個查詢開始時立即請求所有必要的鎖定並且總是以同樣的順序鎖定資料表來管理。

WRITEMySQL使用的資料表鎖定方法原理如下:

  • 如果在資料表上沒有鎖,在它上面放一個寫鎖。
  • 否則,把鎖定請求放在寫鎖定隊列中。

READMySQL使用的鎖定方法原理如下:

  • 如果在資料表上沒有寫鎖定,把一個讀鎖定放在它上面。
  • 否則,把鎖請求放在讀鎖定隊列中。

當一個鎖定被釋放時,鎖定可被寫鎖定隊列中的線程得到,然後是讀鎖定隊列中的線程。

這意味著,如果您在一個資料表上有許多更新,SELECT語句將等待直到沒有更多的更新。

可以通過檢查table_locks_waitedtable_locks_immediate狀態變數來分析系統上的資料表鎖定爭奪:

mysql> SHOW STATUS LIKE 'Table%';

+-----------------------+---------+

| Variable_name         | Value   |

+-----------------------+---------+

| Table_locks_immediate | 1151552 |

| Table_locks_waited    | 15324   |

+-----------------------+---------+

如果INSERT語句不衝突,可以自由為MyISAM資料表混合併行的INSERTSELECT語句而不需要鎖定。也就是說,您可以在其它客戶正讀取MyISAM資料表的時候插入行。如果數據檔案中間不包含空閒塊,不會發生衝突,因為在這種情況下,記錄總是插入在數據檔案的尾部。(從資料表的中部刪除或更新的行可能導致空洞)如果有空洞,當所有空洞填入新的數據時,並行的插入能夠重新自動啟用。

如果不能同時插入,為了在一個資料表中進行多次INSERTSELECT操作,可以在臨時資料表中插入行並且立即用臨時資料表中的記錄更新真正的資料表。

這可用下列代碼做到:

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語句,並且只有很少的UPDATEDELETE語句。

·         在整個資料表上有許多掃瞄或GROUP BY操作,沒有任何寫操作。

不同於行級或頁級鎖定的選項:

·         版本(例如,為並行的插入在MySQL中使用的技術),其中可以一個寫操作,同時有許多讀取操作。這說明資料庫或資料表支援數據依賴的不同視圖,取決於訪問何時開始。其它共同的術語是「時間跟蹤」、「寫複製」或者「按需複製」。

·         按需複製在許多情況下優先於頁級或行級鎖定。然而,在最壞的情況下,它可能比使用常規鎖定使用更多的內存。

·         除了行級鎖定外,您可以使用應用程式級鎖定,例如在MySQL中使用GET_LOCK()RELEASE_LOCK()。這些是建議性鎖定,它們只能在運行良好的應用程式中工作。

7.3.2. 資料表鎖定事宜

為達到最高鎖定速度,除InnoDBBDB之外,對所有儲存引擎,MySQL使用資料表鎖定(而不是頁、行或者列鎖定)

對於InnoDBBDB資料表,如果您用LOCK TABLES顯式鎖定資料表,MySQL只使用資料表鎖定。對於這些資料表類型,我們建議您根本不要使用LOCK TABLES,因為InnoDB使用自動行級鎖定而BDB使用頁級鎖定來保證事務隔離。

對於大資料表,對於大多數應用程式,資料表鎖定比行鎖定更好,但存在部分問題。

資料表鎖定使許多線程同時從一個資料表中進行讀取操作,但如果一個線程想要對資料表進行寫操作,它必須首先獲得獨佔訪問。更新期間,所有其它想要訪問該資料表的線程必須等待直到更新完成。

資料表更新通常情況認為比資料表檢索更重要,因此給予它們更高的優先級。這應確保更新一個資料表的活動不能「餓死」,即使該資料表上有很繁重的SELECT活動。

資料表鎖定在這種情況下會造成問題,例如當線程正等待,因為硬盤已滿並且線上程可以處理之前必須有空閒空間。在這種情況下,所有想要訪問出現問題的資料表的線程也被設置成等待狀態,直到有更多的硬盤空間可用。

資料表鎖定在下面的情況下也存在問題:

·         一個客戶發出長時間運行的查詢。

·         然後,另一個客戶對同一個資料表進行更新。該客戶必須等待直到SELECT完成。

·         另一個客戶對同一個資料表上發出了另一個SELECT語句。因為UPDATESELECT優先級高,該SELECT語句等待UPDATE完成,並且等待第1SELECT完成。

下面描述了一些方法來避免或減少資料表鎖定造成的競爭:

·         試圖使SELECT語句運行得更快。您可能必須建立一些摘要(summary)資料表做到這點。

·         --low-priority-updates啟動mysqld。這將給所有更新(修改)一個資料表的語句以比SELECT語句低的優先級。在這種情況下,在先前情形的第2SELECT語句將在UPDATE語句前執行,而不需要等候第1SELECT完成。

·         可以使用SET LOW_PRIORITY_UPDATES=1語句指定具體連接中的所有更新應使用低優先級。參見13.5.3節,「SET語法」

·         可以用LOW_PRIORITY屬性給與一個特定的INSERTUPDATEDELETE語句較低優先級。

·         可以用HIGH_PRIORITY屬性給與一個特定的SELECT語句較高優先級。參見13.2.7節,「SELECT語法」

·         max_write_lock_count系統變數指定一個低值來啟動mysqld來強制MySQL在具體數量的插入完成後臨時提高所有等待一個資料表的SELECT語句的優先級。這樣允許在一定數量的WRITE鎖定後給出READ鎖定。

·         如果您有關於INSERT結合SELECT的問題,切換到使用新的MyISAM資料表,因為它們支援並發的SELECTINSERT

·         如果您對同一個資料表混合插入和刪除,INSERT DELAYED將會有很大的幫助。參見13.2.4.2節,「INSERT DELAYED語法」

·         如果您對同一個資料表混合使用SELECTDELETE語句出現問題,DELETELIMIT選項可以有所幫助。參見13.2.1節,「DELETE語法」

·         SELECT語句使用SQL_BUFFER_RESULT可以幫助使資料表鎖定時間變短。參見13.2.7節,「SELECT語法」

·         可以更改mysys/thr_lock.c中的鎖代碼以使用單一的隊列。在這種情況下,寫鎖定和讀鎖定將具有相同的優先級,對一些應用程式會有幫助。

這裡是一些MySQL中資料表鎖定相關的技巧:

·         如果不混合更新與需要在同一個資料表中檢查許多行的選擇,可以進行並行操作。

·         可以使用LOCK TABLES來提高速度,因為在一個鎖定中進行許多更新比沒有鎖定的更新要快得多。將資料表中的內容切分為幾個資料表也可以有所幫助。

·         如果在MySQL中資料表鎖定時遇到速度問題,可以將您的資料表轉換為InnoDBBDB資料表來提高性能。參見15.2節,「InnoDB儲存引擎」15.5節,「BDB (BerkeleyDB)儲存引擎」

7.4. 最佳化資料庫結構

7.4.1. 設計選擇

MySQL將行數據和索引數據保存在不同的檔案中。許多(幾乎所有)其它資料庫將行數據和索引數據混合保存在用一個檔案中。我們認為MySQL 選擇對廣範圍的現代系統更好一些。

保存行數據的另一種方式是將每個列的訊息保存在單獨的區域(例如SDBMFocus)。這樣會對每個訪問多個列的查詢造成性能問題。因為當訪問多個列時退化得很快,我們認為該模型對一般資料庫不合適。

更常見的情形是索引和數據保存在一起(例如Oracle/Sybase)。在這種情況下,您可以在索引的葉級頁找到行的訊息。該佈局比較好的事情是在許多情況下,根據索引緩存得怎樣,可以保存一個硬盤讀取。該佈局的不利之處資料表現在:

·         資料表掃瞄要慢得多,因為您必須通讀索引以獲得數據。

·         您不能只使用資料表來檢索查詢的數據。

·         您需要使用更多的空間,因為您必須從節點複製索引(您不能保存節點上的行)

·         刪除要經過一段時間後才退化資料表(因為刪除時通常不會更新節點上的索引)

·         只緩存索引數據會更加困難。

7.4.2. 使您的數據盡可能小

最基本的最佳化之一是使資料表在磁盤上佔據的空間盡可能小。這能給出巨大的改進,因為磁盤讀入較快,並且在查詢執行過程中小資料表的內容被處理時佔用較少的主儲存器。如果在更小的列上做索引,索引也佔據較少的資源。

MySQL支援許多不同的儲存引擎(資料表類型)和行格式。對於每個資料表,可以確定使用哪個儲存引擎和索引方法。為應用程式選擇合適的資料表格式可以大大提高性能。參見第15章:儲存引擎和資料表類型

可以使用下面的技術可以使資料表的性能更好並且使儲存空間最小:

  • 盡可能地使用最有效(最小)的數據類型。MySQL有很多節省磁盤空間和內存的專業化類型。
  • 盡可能使用較小的整數類型使資料表更小。例如,MEDIUMINT經常比INT好一些,因為MEDIUMINT列使用的空間要少25%
  • 如果可能,聲明列為NOT NULL。它使任何事情更快而且每列可以節省一位。注意如果在應用程式中確實需要NULL,應該毫無疑問使用它,只是避免 預設地在所有列上有它。
  • 對於MyISAM資料表,如果沒有任何變長列(VARCHARTEXTBLOB),使用固定尺寸的記錄格式。這比較快但是不幸地可能會浪費一些空間。參見15.1.3節,「MyISAM資料表的儲存格式」。即使您已經用CREATE選項讓VARCHARROW_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語法」)。更短的索引會更快,不僅因為它們占較少的磁盤空間,而且因為它們將在索引緩存中提供更多的訪問,因此磁盤搜索更少。參見7.5.2節,「調節伺服器參數」

·         在一些情形下,將一個經常被掃瞄的資料表分割為2個資料表是有益的。特別是如果它是一個動態格式的資料表,並且可能使用一個掃瞄資料表時能用來找出相關行的較小靜態格式的資料表。

7.4.3. 列索引

所有MySQL列類型可以被索引。對相關列使用索引是提高SELECT操作性能的最佳途徑。

根據儲存引擎定義每個資料表的最大索引數和最大索引長度。參見第15章:儲存引擎和資料表類型。所有儲存引擎支援每個資料表至少16個索引,總索引長度至少為256字節。大多數儲存引擎有更高的限制。

在索引定義中用col_name(length)語法,您可以建立一個只使用CHARVARCHAR列的第1length字元的索引。按這種方式只索引列值的前綴可以使索引檔案小得多。

MyISAMInnoDB儲存引擎還支援對BLOBTEXT列的索引。當索引一個BLOBTEXT列時,您必須為索引指定前綴長度。例如:

CREATE TABLE test (blob_col BLOB, INDEX(blob_col(10)));

MySQL 5.1中,對於MyISAMInnoDB資料表,前綴可以達到1000字節長。請注意前綴的限制應以字節為單位進行測量,而CREATE TABLE語句中的前綴長度解釋為字元數。當為使用多字節字元編碼的列指定前綴長度時一定要加以考慮

還可以建立FULLTEXT索引。該索引可以用於全文搜索。只有MyISAM儲存引擎支援FULLTEXT索引,並且只為CHARVARCHARTEXT列。索引總是對整個列進行,不支援局部(前綴)索引。詳情參見12.7節,「全文搜索功能」

也可以為空間列類型建立索引。只有MyISAM儲存引擎支援空間類型。空間索引使用R-樹。

預設情況MEMORY(HEAP)儲存引擎使用hash索引,但也支援B-樹索引。

7.4.4. 多列索引

MySQL可以為多個列建立索引。一個索引可以包括15個列。對於某些列類型,可以索引列的前綴(參見7.4.3節,「列索引」)

多列索引可以視為包含通過連接索引列的值而建立的值的排序的數組。

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_namefirst_name的索引。索引可以用於為last_name,或者為last_namefirst_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. MySQL如何使用索引

索引用於快速找出在某個列中有一特定值的行。不使用索引,MySQL必須從第1條記錄開始然後讀完整個資料表直到找出相關的行。資料表越大,花費的時間越多。如果資料表中查詢的列有一個索引,MySQL能快速到達一個位置去搜尋到數據檔案的中間,沒有必要看所有數據。如果一個資料表有1000行,這比順序讀取至少快100倍。注意如果您需要訪問大部分行,順序讀取要快得多,因為此時我們避免磁盤搜索。

大多數MySQL索引(PRIMARY KEYUNIQUEINDEXFULLTEXT)B樹中儲存。只是空間列類型的索引使用R-樹,並且MEMORY資料表還支援hash索引。

字串自動地壓縮前綴和結尾空格。參見13.1.4節,「CREATE INDEX語法」

總的來說,按後面的討論使用索引。本節最後描述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

·         在一些情況中,可以對一個查詢進行最佳化以便不用查詢數據行即可以檢索值。如果查詢只使用來自某個資料表的數字型並且構成某些關鍵字的最左面前綴的列,為了更快,可以從索引樹檢索出值。

·                SELECT key_part3 FROM tbl_name
·                    WHERE key_part1=1

假定您執行下面的SELECT語句:

mysql> SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;

如果col1col2上存在一個多列索引,可以直接取出相應行。如果col1col2上存在單列索引,最佳化器試圖通過決定哪個索引將找到更少的行來找出更具限制性的索引並且使用該索引取行。

如果資料表有一個多列索引,最佳化器可以使用最左面的索引前綴來找出行。例如,如果有一個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;
 

如果 (col1col2col3)有一個索引,只有前2個查詢使用索引。第3個和第4個查詢確實包括索引的列,但(col2)(col2col3)不是 (col1col2col3)的最左邊的前綴。

也可以在資料表達式通過=>>=<<=或者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鍵高速緩衝

為了使硬盤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語法」)

例如,下面的語句將資料表t1t2t3的索引分配給名為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_sizekeycache1是緩存變數名,key_buffer_size是緩存組件。關於引用結構式 鍵高速緩衝系統變數所使用的語法的描述,參見9.4.1節,「結構式系統變數」

預設情況下,資料表索引被分配給伺服器啟動時建立的主要(預設)鍵高速緩衝。當 鍵高速緩衝被刪除後,所有分配給它的索引被重新分配給預設鍵高速緩衝。

對於一個忙的伺服器,我們建議採用使用三個鍵高速緩衝的策略:

·         佔用為所有鍵高速緩衝分配的空間的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_cachecold_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語句。例如,下面的語句可以預裝資料表t1t2索引的節點(索引塊)

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_cachekey_buffer_size=4*1024*1024

如果您為key_buffer_sizekey_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值不同:當expr1expr2(或兩者)NULL時,expr1 = expr2不為真。這樣影響比較形式tbl_name.key = exprref訪問:如果expr當前的值為NULLMySQL不會訪問資料表,因為比較不能為真。

對於=比較,資料表內有多少NULL值並不重要。為了最佳化目的,相關值為非NULL數值組的平均大小。然而,MySQL目前不允許搜集或使用該平均大小。

對於MyISAM資料表,您可以使用myisam_stats_method系統變數部分控制資料表統計訊息的搜集。該變數有兩個可能的不同值,如下所示:

·         myisam_stats_methodnulls_equal時,所有NULL值被視為相等的(也就是說,它們都形成一個數值組)

如果NULL數值組大小遠大於平均非NULL數值組大小,該方法向上傾斜平均數數值組大小。這樣使索引對於最佳化器來說比它實際為搜尋非NULL值的聯接更加沒有用。結果是,nulls_equal方法會使最佳化器進行ref訪問時本應使用索引而沒有使用。

·         myisam_stats_methodnulls_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. MySQL如何打開和關閉資料表

table_cachemax_connectionsmax_tmp_tables系統變數影響伺服器保持打開的檔案的最大數量。如果您增加這些值其中的一個或兩個,會遇到作業系統為每個程序打開檔案描述符的數量強加的限制。許多作業系統允許您增加打開的檔案的限制,儘管該方法隨系統的不同而不同。查閱作業系統文檔以確定是否可以增加限制以及如何操作。

table_cachemax_connections有關。例如,對於200個並行運行的連接,應該讓資料表的緩存至少有200 * N,這裡N是可以執行的查詢的一個聯接中資料表的最大數量。還需要為臨時資料表和檔案保留一些額外的檔案描述符。

確保作業系統可以處理table_cache設置所指的打開的檔案描述符的數目。如果table_cacheis設得太高,MySQL可能為檔案描述符耗盡資源並拒絕連接,不能執行查詢,並且很不可靠。還必須考慮到MyISAM儲存引擎需要為每個打開的資料表提供兩個檔案描述符。可以在mysqld_safe中使用--open-files-limit啟動選項來增加MySQL適用的檔案描述符的數量。參見A.2.17節,「檔案未找到」

打開資料表的緩存可以保持在table_cache條。 預設為64;可以用mysqld--table_cache選項來更改。請注意 MySQL可以臨時打開更多的 資料表以執行查詢。

在下面的條件下,未使用的資料表將被關閉並從資料表緩存中移出:

·         當緩存滿了並且一個線程試圖打開一個不在緩存中的資料表時。

·         當緩存包含超過table_cache個條目,並且緩存中的資料表不再被任何線程使用。

·         當資料表刷新操作發生。當執行FLUSH TABLES語句或執行mysqladmin flush-tablesmysqladmin refresh命令時會發生。

當資料表緩存滿時,伺服器使用下列過程找到一個緩存入口來使用:

·         當前未使用的資料表被釋放,以最近最少使用順序。

·         如果緩存滿了並且沒有資料表可以釋放,但是一個新資料表需要打開,緩存必須臨時被擴大。

如果緩存處於一個臨時擴大狀態並且一個資料表從在用變為不在用狀態,它被關閉並從緩存中釋放。

對每個並發訪問打開一個資料表。這意味著,如果2個線程訪問同一個資料表或在同一個查詢中訪問資料表兩次(例如,將資料表連接為自身時),資料表需要被打開兩次。每個並行的打開要求在資料表緩存中有一個條目。任何資料表的第一次打開占2個檔案描述符:一個用於數據檔案另一個用於索引檔案。資料表的每一次額外使用僅佔一個數據檔案的檔案描述符。索引檔案描述符在所有線程之間共享。

如果您正用HANDLER tbl_name OPEN語句打開一個資料表,將為該線程專門分配一個資料表。該資料表不被其它線程共享,只有線程使用HANDLER tbl_name CLOSE或線程終止後才被關閉。資料表關閉後,被拉回資料表緩存中(如果緩存不滿)。參見13.2.3節,「HANDLER語法」

可以通過檢查mysqld的狀態變數Opened_tables確定資料表緩存是否太小:

mysql> SHOW STATUS LIKE 'Opened_tables';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Opened_tables | 2741  |
+---------------+-------+

如果值很大,即使您沒有發出許多FLUSH TABLES語句,也應增加資料表緩存的大小。參見5.3.3節,「伺服器系統變數」5.3.4節,「伺服器狀態變數」

7.4.10. 在同一個資料庫中建立多個資料表的問題

如果在同一個資料庫目錄中有許多MyISAM資料表,打開、關閉和建立操作將會很慢。如果對許多不同的資料表執行SELECT語句,當資料表緩存滿時,將有一點開銷,因為對每個必須打開的資料表,另外一個必須被關閉。可以通過使資料表緩存更大些來減少這個開銷。

7.5. 最佳化MySQL伺服器

7.5.1. 系統因素和啟動參數的調節

我們從系統級因素開始,因為必須盡早地進行部分決策以獲得較大性能。在其它情況下,快速瀏覽該節就足夠了。但是,瞭解一下更改該層次的參數能夠獲得多少性能提高是很有意義的。

使用的作業系統很重要。為了更好地使用多CPU機器,應使用Solaris(因為其線程工作得很好)Linux(因為2.4和以後的內核有很好的SMP支援)。請注意預設情況舊的Linux內核有一個2GB的檔案大小限制。如果有這樣的一個內核並且需要檔案大於2GB,應得到ext2檔案系統的大檔案支援(LFS)補丁。其它檔案系統例如ReiserFSXFS沒有此2GB限制。

MySQL用於生產前,我們建議您在想用的平台上對它進行測試。

其它技巧:

·         如果有足夠的RAM,可以移除所有的交換設備。有些作業系統即使有自由內存也使用交換設備。

·         使用--skip-external-locking MySQL選項以避免外部鎖定。該選項預設開啟。

請注意只要您只運行一個伺服器,--skip-external-locking選項不影響MySQL的功能。只要記住運行myisamchk之前關閉伺服器(或鎖定並刷新相關資料表)。在一些系統上該選項是強制的,因為在任何情況下外部鎖定均不工作。

不能使用--skip-external-locking的唯一情況是對相同的數據運行多個MySQL伺服器(非客戶)的情況,或者如果沒有事先告訴伺服器刷新並鎖定一個資料表即運行myisamchk來檢查(非修復)該資料表。請注意一般不建議使用多個MySQL伺服器來並行訪問相同的數據,除了使用MySQL Cluster時。

即使使用--skip-external-locking,仍然可以使用LOCK TABLESUNLOCK 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.4節,「伺服器狀態變數」

MySQL使用完全可以升級的算法,因此通常運行時可以用很少的內存。然而,通常情況若給MySQL更多的內存性能會更好。

當調節MySQL伺服器時,要配置的兩個最重要的變數是key_buffer_sizetable_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 BYORDER BY操作,應增加read_rnd_buffer_size的值以加速排序操作後面的行讀取。

如果已經安裝了MySQLsupport-files目錄包含一些不同的my.cnf示範檔案:my-huge.cnfmy-.cnfmy-medium.cnfmy-small.cnf。可以使用這些檔案來最佳化系統。

請注意如果在命令行中為mysqldmysqld_safe指定一個選項,它只在該次伺服器使用中保持有效。要想每次伺服器運行時使用該選項,將它放在選項檔案中。

要想看參數更改的效果,應執行:

shell> mysqld --key_buffer_size=32M --verbose ---help

變數值列於輸出的最後。確保--verbose---help選項在最後。否則,在命令行中列於它們後面的選項的效果不會反映到輸出中。

關於調節InnoDB儲存引擎的訊息,參見15.2.11節,「InnoDB性能調節提示」

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接近於查詢中資料表的數量,對1213或更多資料表的查詢很可能需要幾小時甚至幾天的時間來編譯。同時,如果用optimizer_search_depth等於34編輯,對於同一個查詢,編譯器編譯時間可以少於1分鐘。如果不能確定合理的optimizer_search_depth值,該變數可以設置為0,告訴最佳化器自動確定該值。

7.5.4. 編譯和連結怎樣影響MySQL的速度

下列大多數測試是在Linux上並用MySQL基準進行的,但是它們能對其它作業系統和工作負載給出一些指示。

當您用-static連結時,可以得到最快的可執行檔案。

Linux上,最好用pgcc-O3編譯伺服器。為了用這些選項編譯「sql_yacc.cc」,需要大約200M內存,因為gccpgcc需要大量的內存使所有函數嵌入(inline)。在配置MySQL時,也應該設定CXX=gcc以避免包括libstdc++(它不需要)。請注意對於某些版本的pgcc,生成的二進制只能運行在真Pentium處理器上,即使您使用編譯器選項說明您想讓最終的代碼在所有x586-類處理器上工作(例如AMD)

只通過使用一個較好的編譯器或較好的編譯器選項,在應用中能得到10-30%的加速。如果您自己編譯SQL伺服器,這特別重要!

當我們測試Cygnus CodeFusionFujitsu編譯器時,二者均還沒足夠不出錯來讓MySQL啟用最佳化進行編譯。

標準MySQL二進制分發編譯為支援所有字元編碼。當您自己編譯MySQL時,應只包括將使用的字元編碼的支援。通過configure--with-charset選項來控制。

這裡是我們做過的一些測量資料表:

·         如果您使用pgcc並用-O6編譯,mysqld伺服器比用gcc 2.95.211%

·         如果您動態地連結(沒有-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 -WaUltraSPARC,使用-xarch=v8plusa選項性能會提高4%

·         Solaris 2.5.1上,在單個處理器上MIT-pthreads比帶原生線程的Solaris8-12%。如果有更大的負載/cpus,差別應該更大。

·         Linux-x86上使用gcc編譯而不用幀指針(-fomit-frame-pointer or -fomit-frame-pointer -ffixed-ebp),可以使mysqld1-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節,「調節伺服器參數」

·         每個連接使用具體線程的空間:

o        堆棧(預設64KB,變數thread_stack)

o        連接緩存區(變數net_buffer_length)

o        結果緩存區(變數net_buffer_length)

連接緩存區和結果緩存區可以根據需要動態擴充到max_allowed_packet。當某個查詢運行時,也為當前查詢字串分配內存。

·         所有線程共享相同的基本內存。

·         只有壓縮MyISAM資料表映射到內存。這是因為4GB32位內存空間不足以容納大多數大資料表。當64位地址空間的系統變得越來越普遍後,我們可以增加常規的內存映射支援。

·         對資料表進行順序掃瞄的請求將分配一個緩存區(變數read_buffer_size)

·         當按任意順序讀取行時(例如,按照排序順序),將分配一個隨機讀 緩存區(變數read_rnd_buffer_size)以避免硬盤搜索。

·         所有聯合在一個令牌內完成,並且大多數聯合甚至可以不用臨時資料表即可以完成。大多數臨時資料表是基於內存的(HEAP)資料表。具有大的記錄長度的臨時資料表 (所有列的長度的和)或包含BLOB列的資料表儲存在硬盤上。

如果某個內部heap(堆積)資料表大小超過tmp_table_sizeMySQL可以根據需要自動將內存中的heap資料表改為基於硬盤的MyISAM資料表。還可以通過設置mysqldtmp_table_size選項來增加臨時資料表的大小,或設置客戶程式中的SQL選項SQL_BIG_TABLE。參見13.5.3節,「SET語法」

·         進行排序的大多數請求將分配一個排序緩存區,並根據結果集的大小為兩個臨時檔案分配零。參見A.4.4節,「MySQL將臨時檔案儲存在哪裡」

·         幾乎所有解析和計算在局部內存中完成。小項目不需要內存,因此避免了普通的慢內存分配和釋放。只為不期望的大字串分配內存;使用函數malloc()free()來完成。

·         對於每個打開的MyISAM資料表,索引檔案打開一次;數據檔案為每個並行運行的線程打開一次。對於每個並行線程,將分配一個資料表結構、一個每個列的列結構和大小為3 * N的緩存區(其中N是最大行的長度,而不是計算BLOB)。一個BLOB列需要58個字節加上BLOB數據的長度。MyISAM 儲存引擎維護一個額外的行緩存區供內部應用。

·         對於每個具有BLOB列的資料表,將對緩存區進行動態擴大以讀入大的BLOB 值。如果您掃瞄一個資料表,則分配一個與最大的BLOB值一樣大的緩存區。

·         所有使用的資料表的句柄結構保存在高速緩存中並以FIFO管理。預設情況,高速緩存有64個入口。如果某個資料表同時被兩個運行的線程使用,高速緩存則為該提供兩個入口。參見7.4.9節,「MySQL如何打開和關閉資料表」

·         當並行執行的線程結束時,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節,「使用符號連結」

o        分條

分條意味著您有許多磁盤,將第1個塊放到第1個硬盤,第2個塊放到第2個磁盤,並且第N塊在(N mod number_of_disks)磁盤上等等。這意味著如果正常數據大小小於分條大小(或完全匹配),能夠得到最佳性能。分條完全取決於作業系統和分條大小,因此用不同的條紋大小對應用程式進行基準測試。參見7.1.5節,「使用自己的基準」

分條的不同速度完全依賴於參數。依賴於怎樣設置條紋參數和硬盤數量,可以根據不同數量級別得到不同的標準。您必須進行選擇以便最佳化隨機或順序存取。

·         為了高可靠性您可能想使用RAID 0+1(條紋加鏡像),但在這種情況下,需要2*N塊磁盤來保持N個磁盤的數據。如果您肯為它花錢,這可能是最好的選項。然而,您可能還必須投資一部分資金到卷管理軟件中以便有效地管理它。

·         一個較好的選擇是根據數據類型的重要性程度改變RAID級別。例如,保存可以在RAID 0硬盤上重新生成的不太重要的數據,但保存 真正重要數據(例如主機訊息和日誌)到RAID 0+1RAID 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上使用針對資料庫的符號連結

Unix中,符號連結一個資料庫的方法是,首先在一些有空閒空間的硬盤上建立一個目錄,然後從MySQL數據目錄中建立它的一個符號連結。

shell> mkdir /dr1/databases/test
shell> ln -s /dr1/databases/test /path/to/datadir

MySQL不支援連結一個目錄到多個資料庫。用一個符號連結替換一個資料庫目錄,只要您不在資料庫之間製作符號連結。假定您有一個資料庫db1MySQL數據目錄下,然後生成一個符號連結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()使用的系統中對資料表進行符號連結。(LinuxSolaris支援realpath())。可以通過發出一個SHOW VARIABLES LIKE 'have_symlink'語句,檢查系統是否支援符號連結。

只有MyISAM資料表完全支援符號連結。對於其它資料表類型,如果試圖在作業系統中的檔案上用前面的任何語句使用符號連結,可能會出現奇怪的問題。

對於MyISAM資料表的符號連結的處理如下:

·         在數據目錄指,一定會有資料表定義檔案、數據檔案和索引檔案。數據檔案和索引檔案可以移到別處和在數據目錄中符號連結替代。資料表定義檔案不能進行符號連結替換。

·         可以分別通過符號連結將數據檔案和索引檔案指到不同的目錄。

·         如果mysqld沒有運行,符號連結可以從伺服器命令行使用ln -s手動完成。同樣,通過使用DATA DIRECTORYINDEX DIRECTORY選項建立資料表,您可以指示運行的MySQL伺服器執行符號連結。參見13.1.5節,「CREATE TABLE語法」

·         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平台上使用關於資料庫的符號連結

Windowsmysqld-maxmysql-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。 原始參考手冊為英文版,與英文版參考手冊相比,本翻譯版可能不是最新的。