mysql-logo.gif (3082 bytes)MySQL中文參考手冊

譯者：晏子 (clyan@sohu.com）
GB 碼主頁：http://linuxdb.yeah.net

Big5 轉碼者：statue (statue@bbs.yzu.edu.tw）
詞彙轉換：彭武興 (wilson@mailbox.com.tw)
Big5 碼主頁： http://cnpa.yzu.edu.tw/~cfc/docs/mysqldoc_big5/manual_toc.html
Big5 碼分站： http://php.wilson.gs/mysqldoc/big5/manual_toc.html

第一章, 前一章, 下一章, 最後一章，目錄.

10 從MySQL得到最大的性能

最佳化是一項複雜的任務，因為它最終需要對整個系統的理解。當用你的系統/應用的小知識做一些局部最佳化是可能的時候，你越想讓你的系統更最佳化，你必須知道它也越多。

因此，本章將試圖解釋並給出最佳化MySQL的不同方法的一些例子。但是記住總是有某些(逐漸變難)是系統更快的方法留著去做。

10.1 最佳化概述

為了使一個系統更快的最重要部分當然是基本設計。你也需要知道你的系統將做這樣的事情，那就是你的瓶頸。

最常見的瓶頸是：

10.2 系統/編譯時和啟動參數的調節

我們以系統級的東西開始，因為這些決策的某一些很早就做好了。在其他情況下，快速瀏覽這部分可能就夠了，因為它對大收獲並不重要，但是有一個關於在這個層次上收獲有多大的感覺總是好的。

使用的內定OS確實重要！為了最大程度地使用多CPU，應該使用Solaris(因為執行緒工作得確實不錯)或Linux(因為2.2本的核心又確實不錯的SMP支援)。而且在32位的機器上，Linux內定有2G的文件大小限制。當新的文件系統被釋出時( XFS )，希望這不久被修正。

因為我們沒在很多平台上運行生產MySQL，我們忠告你在可能選擇它前，測試你打算運行的平台。

其他建議：

10.2.1 編譯和鏈接怎樣影響MySQL的速度

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

當你用-static鏈接時，你得到最快的可執行文件。使用Unix套接字而非TCP/IP連接一個資料庫也可給出好一些的性能。

在Linux上，當用pgcc和-O6編譯時，你將得到最快的代碼。為了用這些選項編譯“sql_yacc.cc”，你需要大約200M內存，因為gcc/pgcc需要很多內存使所有函數嵌入（inline）。在配置MySQL時，你也應該設定CXX=gcc以避免包括libstdc++庫(它不需要)。

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

在Intel上，你應該例如使用pgcc或Cygnus CodeFusion編譯器得到最大速度。我們已經測試了新的 Fujitsu編譯器，但是它是還沒足夠不出錯來最佳化編譯MySQL。

這裡是我們做過的一些測量表：

由TcX提供的MySQL-Linux的分發用pgcc編譯並靜態鏈接。

10.2.2 磁碟問題

10.2.2.1 為資料庫和表使用符號鏈接

你可以從資料庫目錄移動表和資料庫到別處，並且用鏈接到新地點的符號代替它們。你可能想要這樣做，例如，轉移一個資料庫到有更多空閑空間的一個文件系統。

如果MySQL注意到一個表是一個符號鏈接，它將解析符號鏈接並且使用其實際指向的表，它可工作在支援realpath()調用的所有系統上(至少Linux和Solaris支援realpath())！在不支援realpath()的系統上，你應該不同時通過真實路徑和符號鏈接訪問表！如果你這樣做，表在任何更新後將不一致。

MySQL內定不支援資料庫鏈接。只要你不在資料庫之間做一個符號鏈接，一切將工作正常。假定你在MySQL數據目錄下有一個資料庫db1，並且做了一個符號鏈接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/mf_format.c”中的代碼： 

if (!lstat(to,&stat_buff))  /* Check if it's a symbolic link */
    if (S_ISLNK(stat_buff.st_mode) && realpath(to,buff))

把代碼改變為這樣： 

if (realpath(to,buff))

10.2.3 調節伺服器參數

你能用這個命令得到mysqld伺服器內定緩衝區大小： 

shell> mysqld --help

這個命令產生一張所有mysqld選項和可配置變數的表。輸出包括內定值並且看上去像這樣一些東西： 

Possible variables for option --set-variable (-O) are:
back_log              current value: 5
connect_timeout       current value: 5
delayed_insert_timeout  current value: 300
delayed_insert_limit  current value: 100
delayed_queue_size    current value: 1000
flush_time            current value: 0
interactive_timeout   current value: 28800
join_buffer_size      current value: 131072
key_buffer_size       current value: 1048540
lower_case_table_names  current value: 0
long_query_time       current value: 10
max_allowed_packet    current value: 1048576
max_connections       current value: 100
max_connect_errors    current value: 10
max_delayed_threads   current value: 20
max_heap_table_size   current value: 16777216
max_join_size         current value: 4294967295
max_sort_length       current value: 1024
max_tmp_tables        current value: 32
max_write_lock_count  current value: 4294967295
net_buffer_length     current value: 16384
query_buffer_size     current value: 0
record_buffer         current value: 131072
sort_buffer           current value: 2097116
table_cache           current value: 64
thread_concurrency    current value: 10
tmp_table_size        current value: 1048576
thread_stack          current value: 131072
wait_timeout          current value: 28800

如果有一個mysqld伺服器正在運行，通過執行這個命令，你可以看到它實際上使用的變數的值： 

shell> mysqladmin variables

每個選項在下面描述。對於緩衝區大小、長度和棧大小的值以字節給出，你能用於個後綴“K”或“M” 指出以K字節或兆字節顯示值。例如，16M指出16兆字節。後綴字母的大小寫沒有關系﹔16M和16m是相同的。

你也可以用命令SHOW STATUS自一個運行的伺服器看見一些統計。見7.21 SHOW語法(得到表、列的資訊)。

back_log
要求MySQL能有的連接數量。當主要MySQL執行緒在一個很短時間內得到非常多的連接請求，這就起作用，然後主執行緒花些時間(盡管很短)檢查連接並且啟動一個新執行緒。back_log值指出在MySQL暫時停止回答新請求之前的短時間內多少個請求可以被存在堆疊中。只有如果期望在一個短時間內有很多連接，你需要增加它，換句話說，這值對到來的TCP/IP連接的偵聽隊列的大小。你的作業系統在這個隊列大小上有它自己的限制。 Unix listen(2)系統調用的手冊頁應該有更多的細節。檢查你的OS文檔找出這個變數的最大值。試圖設定back_log高於你的作業系統的限制將是無效的。
connect_timeout
mysqld伺服器在用Bad handshake（糟糕的交握）應答前正在等待一個連接報文的秒數。
delayed_insert_timeout
一個INSERT DELAYED執行緒應該在終止之前等待INSERT語句的時間。
delayed_insert_limit
在插入delayed_insert_limit行後，INSERT DELAYED處理器將檢查是否有任何SELECT語句未執行。如果這樣，在繼續前執行允許這些語句。
delayed_queue_size
應該為處理INSERT DELAYED分配多大一個隊列(以行數)。如果排隊滿了，任何進行INSERT DELAYED的客戶將等待直到隊列又有空間了。
flush_time
如果這被設置為非零值，那麼每flush_time秒所有表將被關閉(以釋放資源和sync到磁碟)。
interactive_timeout
伺服器在關上它前在一個交互連接上等待行動的秒數。一個交互的客戶被定義為對mysql_real_connect()使用CLIENT_INTERACTIVE選項的客戶。也可見wait_timeout。
join_buffer_size
用於全部聯結(join)的緩衝區大小(不是用索引的聯結)。緩衝區對2個表間的每個全部聯結分配一次緩衝區，當增加索引不可能時，增加該值可得到一個更快的全部聯結。（通常得到快速聯結的最佳方法是增加索引。）
key_buffer_size
索引塊是緩沖的並且被所有的執行緒共享。key_buffer_size是用於索引塊的緩衝區大小，增加它可得到更好處理的索引(對所有讀和多重寫)，到你能負擔得起那樣多。如果你使它太大，系統將開始換頁並且真的變慢了。記住既然MySQL不緩存讀取的數據，你將必須為OS文件系統緩存留下一些空間。為了在寫入多個行時得到更多的速度，使用LOCK TABLES。見7.24LOCK TABLES/UNLOCK TABLES語法。
long_query_time
如果一個查詢所用時間超過它(以秒計)，Slow_queries記數器將被增加。
max_allowed_packet
一個包的最大尺寸。消息緩衝區被初始化為net_buffer_length字節，但是可在需要時增加到max_allowed_packet個字節。內定地，該值太小必能捕捉大的(可能錯誤)包。如果你正在使用大的BLOB列，你必須增加該值。它應該像你想要使用的最大BLOB的那麼大。
max_connections
允許的同時客戶的數量。增加該值增加mysqld要求的文件描述符的數量。見下面對文件描述符限制的注釋。見18.2.4 Too many connections錯誤。
max_connect_errors
如果有多於該數量的從一台主機中斷的連接，這台主機阻止進一步的連接。你可用FLUSH HOSTS命令疏通一台主機。
max_delayed_threads
不要啟動多於的這個數字的執行緒來處理INSERT DELAYED語句。如果你試圖在所有INSERT DELAYED執行緒在用後向一張新表插入數據，行將被插入，就像DELAYED屬性沒被指定那樣。
max_join_size
可能將要讀入多於max_join_size個記錄的聯結將返回一個錯誤。如果你的用戶想要執行沒有一個WHERE子句、花很長時間並且返回百萬行的聯結，設置它。
max_sort_length
在排序BLOB或TEXT值時使用的字節數(每個值僅頭max_sort_length個字節被使用﹔其餘的被忽略)。
max_tmp_tables
（該選擇目前還不做任何事情)。一個客戶能同時保持打開的臨時表的最大數量。
net_buffer_length
通信緩衝區在查詢之間被重置到該大小。通常這不應該被改變，但是如果你有很少的內存，你能將它設置為查詢期望的大小。（即，客戶發出的SQL語句期望的長度。如果語句超過這個長度，緩衝區自動地被擴大，直到max_allowed_packet個字節。）
record_buffer
每個進行一個順序掃描的執行緒為其掃描的每張表分配這個大小的一個緩衝區。如果你做很多順序掃描，你可能想要增加該值。
sort_buffer
每個需要進行排序的執行緒分配該大小的一個緩衝區。增加這值加速ORDER BY或GROUP BY操作。見18.5 MySQL在哪兒儲存臨時文件。
table_cache
為所有執行緒打開表的數量。增加該值能增加mysqld要求的文件描述符的數量。MySQL對每個唯一打開的表需要2個文件描述符，見下面對文件描述符限制的注釋。對於表緩存如何工作的資訊，見10.2.4 MySQL怎樣打開和關閉表。
tmp_table_size
如果一張臨時表超出該大小，MySQL產生一個The table tbl_name is full形式的錯誤，如果你做很多高級GROUP BY查詢，增加tmp_table_size值。
thread_stack
每個執行緒的棧大小。由crash-me測試檢測到的許多限制依賴於該值。內定隊一般的操作是足夠大了。見10.8 使用你自己的效能。
wait_timeout
伺服器在關閉它之前在一個連接上等待行動的秒數。也可見interactive_timeout。

MySQL使用是很具伸縮性的算法，因此你通常能用很少的內存運行或給MySQL更多的被存以得到更好的性能。

如果你有很多內存和很多表並且有一個中等數量的客戶，想要最大的性能，你應該一些像這樣的東西： 

shell> safe_mysqld -O key_buffer=16M -O table_cache=128 \
           -O sort_buffer=4M -O record_buffer=1M &

如果你有較少的內存和大量的連接，使用這樣一些東西： 
shell> safe_mysqld -O key_buffer=512k -O sort_buffer=100k \
           -O record_buffer=100k &

或甚至： 
shell> safe_mysqld -O key_buffer=512k -O sort_buffer=16k \
           -O table_cache=32 -O record_buffer=8k -O net_buffer=1K &

如果有很多連接，“交換問題”可能發生，除非mysqld已經被配置每個連接使用很少的內存。當然如果你對所有連接有足夠的內存，mysqld執行得更好。

注意，如果你改變mysqld的一個選項，它實際上只對伺服器的那個例子保持。

為了明白一個參數變化的效果，這樣做： 

shell> mysqld -O key_buffer=32m --help

保証--help選項是最後一個﹔否則，命令行上在它之後列出的任何選項的效果將不在反映在輸出中。

10.2.4 MySQL怎樣打開和關閉資料庫表

table_cache, max_connections和max_tmp_tables影響伺服器保持打開的文件的最大數量。如果你增加這些值的一個或兩個，你可以遇到你的作業系統每個進程打開文件描述符的數量上強加的限制。然而，你可以能在許多系統上增加該限制。請教你的OS文檔找出如何做這些，因為改變限制的方法各系統有很大的不同。

table_cache與max_connections有關。例如，對於200個打開的連接，你應該讓一張表的緩沖至少有200 * n，這裡n是一個聯結(join)中表的最大數量。

打開表的緩存可以增加到一個table_cache的最大值（內定為64﹔這可以用mysqld的-O table_cache=#選項來改變）。一個表絕對不被關閉，除非當緩存滿了並且另外一個執行緒試圖打開一個表時或如果你使用mysqladmin refresh或mysqladmin flush-tables。

當表緩存滿時，伺服器使用下列程序找到一個緩存入口來使用：

對每個並發存取打開一個表。這意味著，如果你讓2個執行緒存取同一個表或在同一個查詢中存取表兩次(用AS)，表需要被打開兩次。任何表的第一次打開占2個文件描述符﹔表的每一次額外使用僅占一個文件描述符。對於第一次打開的額外描述符用於索引文件﹔這個描述符在所有執行緒之間共享。

10.2.5 在同一個資料庫中創建大量資料庫表的缺點

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

10.2.6 為什麼有這麼多打開的表？

當你運行mysqladmin status時，你將看見像這樣的一些東西： 

Uptime: 426 Running threads: 1 Questions: 11082 Reloads: 1 Open tables: 12

如果你僅有6個表，這可能有點令人困惑。

MySQL是多執行緒的，因此它可以同時在同一個表上有許多詢問。為了是2個執行緒在同一個文件上有不同狀態的問題減到最小，表由每個並發進程獨立地打開。這為數據文件消耗一些內存和一個額外的文件描述符。索引文件描述符在所有執行緒之間共享。

10.2.7 MySQL怎樣使用內存

下表指出mysqld伺服器使用儲存器的一些方式。在應用的地方，給出與儲存器使用相關的伺服器變數的名字。

ps和其他系統狀態程式可以報導mysqld使用很多內存。這可以是在不同的內存地址上的執行緒棧造成的。例如，Solaris版本的ps將棧間未用的內存算作已用的內存。你可以通過用swap -s檢查可用交換區來驗証它。我們用商業內存漏洞探查器測試了mysqld，因此應該有沒有內存漏洞。

10.2.8 MySQL怎樣鎖定資料庫表

MySQL中所有鎖定不會是死鎖的。這通過總是在一個查詢前立即請求所有必要的鎖定並且總是以同樣的順序鎖定表來管理。

對WRITE，MySQL使用的鎖定方法原理如下：

對READ，MySQL使用的鎖定方法原理如下：

當一個鎖定被釋放時，鎖定可被寫鎖定隊列中的執行緒得到，然後是讀鎖定隊列中的執行緒。

這意味著，如果你在一個表上有許多更改，SELECT語句將等待直到有沒有更多的更改。

為了解決在一個表中進行很多INSERT和SELECT操作的情況，你可在一張臨時表中插入行並且偶爾用來自臨時表的記錄更新真正的表。

這可用下列代碼做到： 

mysql> LOCK TABLES real_table WRITE, insert_table WRITE;
mysql> insert into real_table select * from insert_table;
mysql> delete from insert_table;
mysql> UNLOCK TABLES;

如果你在一些特定的情況字下區分檢索的優先次序，你可以使用LOW_PRIORITY選項的INSERT。見7.14 INSERT句法。

你也能改變在“mysys/thr_lock.c”中的鎖代碼以使用一個單個隊列。在這種情況下，寫鎖定和讀鎖定將有同樣優先級，它可能幫助一些應用程式。

10.2.9 資料庫表級鎖定的問題

MySQL的表鎖定代碼是不會死鎖的。

MySQL使用表級鎖定(而不是行級鎖定或列級鎖定)以達到很高的鎖定速度。對於大表，表級鎖定對大多數應用程式來說比行級鎖定好一些，但是當然有一些缺陷。

在MySQL3.23.7和更高版本中，一個人能把行插入到MyISAM表同時其他執行緒正在讀該表。注意，目前只有在表中內有刪除的行時才工作。

表級鎖定使很多執行緒能夠同時讀一個表，但是如果一個執行緒想要寫一個表，它必須首先得到獨占存取權。在更改期間，所有其他想要存取該特定表的執行緒將等到更改就緒。

因為資料庫的更改通常被視為比SELECT更重要，更新一個表的所有語句比從一個表中檢索資訊的語句有更高的優先級。這應該保証更改不被“餓死”，因為一個人針對一個特定表會發出很多繁重的查詢。

從MySQL 3.23.7開始，一個人可以能使用max_write_lock_count變數強制MySQL在一個表上一個特定數量的插入後發出一個SELECT。

對此一個主要的問題如下：

對這個問題的一些可能的解決方案是：

10.3 使你的數據盡可能小

最基本的最佳化之一是使你的數據(和索引)在磁碟上(並且在內存中)占據的空間盡可能小。這能給出巨大的改進，因為磁碟讀入較快並且通常也用較少的主儲存器。如果在更小的列上做索引，索引也占據較少的資源。

你能用下面的技術使表的性能更好並且使儲存空間最小：

10.4 MySQL索引的使用

索引被用來快速找出在一個列上用一特定值的行。沒有索引，MySQL不得不首先以第一條記錄開始並然後讀完整個表直到它找出相關的行。表越大，花費時間越多。如果表對於查詢的列有一個索引，MySQL能快速到達一個位置去搜尋到數據文件的中間，沒有必要考慮所有數據。如果一個表有1000行，這比順序讀取至少快100倍。注意你需要存取幾乎所有1000行，它較快的順序讀取，因為此時我們避免磁碟尋道。

所有的MySQL索引(PRIMARY、UNIQUE和INDEX)在B樹中儲存。字符串是自動地壓縮前綴和結尾空間。見7.27 CREATE INDEX句法。

索引用於：

假定你發出下列SELECT語句： 

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

如果一個多列索引存在於col1和col2上，適當的行可以直接被取出。如果分開的單行列索引存在於col1和col2上，最佳化器試圖通過決定哪個索引將找到更少的行並來找出更具限制性的索引並且使用該索引取行。

如果表有一個多列索引，任何最左面的索引前綴能被最佳化器使用以找出行。例如，如果你有一個3行列索引(col1,col2,col3)，你已經索引了在(col1)、(col1,col2)和(col1,col2,col3)上的搜索能力。

如果列不構成索引的最左面前綴，MySQL不能使用一個部分的索引。假定你下面顯示的SELECT語句： 

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

如果一個索引存在於(col1、col2、col3)上，只有上面顯示的第一個查詢使用索引。第二個和第三個查詢確實包含索引的列，但是(col2)和(col2、col3)不是(col1、col2、col3)的最左面前綴。

如果LIKE參數是一個不以一個通配符字符起始的一個常數字符串，MySQL也為LIKE比較使用索引。例如，下列SELECT語句使用索引： 

mysql> select * from tbl_name where key_col LIKE "Patrick%";
mysql> select * from tbl_name where key_col LIKE "Pat%_ck%";

在第一條語句中，只考慮有"Patrick" <= key_col < "Patricl"的行。在第二條語句中，只考慮有"Pat" <= key_col < "Pau"的行。

下列SELECT語句將不使用索引： 

mysql> select * from tbl_name where key_col LIKE "%Patrick%";
mysql> select * from tbl_name where key_col LIKE other_col;

在第一條語句中，LIKE值以一個通配符字符開始。在第二條語句中，LIKE值不是一個常數。

如果 column_name 是一個索引，使用column_name IS NULL的搜索將使用索引。

MySQL通常使用找出最少數量的行的索引。一個索引被用於你與下列操作符作比較的列：=、>、>=、<、<=、BETWEEN和一個有一個非通配符前綴像'something%'的LIKE的列。

任何不跨越的在WHERE子句的所有AND層次的索引不用來最佳化詢問。

下列WHERE子句使用索引：

... WHERE index_part1=1 AND index_part2=2
... WHERE index=1 OR A=10 AND index=2      /* index = 1 OR index = 2 */
... WHERE index_part1='hello' AND index_part_3=5
          /* optimized like "index_part1='hello'" */

這些WHERE子句不使用索引： 

... WHERE index_part2=1 AND index_part3=2  /* index_part_1 is not used */
... WHERE index=1 OR A=10                  /* No index */
... WHERE index_part1=1 OR index_part2=10  /* No index spans all rows */

10.5 存取或更新數據的查詢速度

首先，一件事情影響所有的詢問。你有的許可系統設置越複雜，你得到更多的開銷。

如果你不讓任何GRANT語句執行，MySQL將稍微最佳化許可檢查。因此如果你有很大量，值得花時間來避免授權，否則更多的許可檢查有更大的開銷。

如果你的問題是與一些明顯的MySQL函數有關，你總能在MySQL客戶中計算其時間：

mysql> select benchmark(1000000,1+1);
+------------------------+
| benchmark(1000000,1+1) |
+------------------------+
|                      0 |
+------------------------+
1 row in set (0.32 sec)

上面顯示MySQL能在PentiumII 400MHz上以0.32秒執行1,000,000個+表達式。

所有MySQL函數應該被高度最佳化，但是以可能有一些例外並且benchmark(loop_count,expression)是找出是否你的查詢有問題的一個極好工具。

10.5.1 估計查詢性能

在大多數情況下，你能通過計算磁碟尋道估計性能。對小的表，你通常能在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.2M，(假設索引緩衝區被充滿到2/3(它是典型的))，你將可能在內存中有索引的大部分並且你將可能僅需要1-2調用從OS讀數據來找出行。

然而對於寫，你將需要 4 次尋道請求(如上)來找到在哪兒存放新索引並且通常需2次尋道更新這個索引並且寫入行。

注意，上述不意味著你的應用程式將緩慢地以 N log N 退化！當表格變得更大時，只要一切被OS或SQL伺服器緩沖，事情將僅僅或多或少地更慢。在數據變得太大不能被緩沖後，事情將開始變得更慢直到你的應用程式僅僅受磁碟尋道限制(它以N log N增加)。為了避免這個增加，索引緩沖隨數據增加而增加。見10.2.3 調節伺服器參數。

10.5.2 SELECT查詢的速度

總的來說，當你想要使一個較慢的SELECT ... WHERE更快，檢查的第一件事情是你是否能增加一個索引。見10.4 MySQL 索引的使用。在不同表之間的所有引用通常應該用索引完成。你可以使用EXPLAIN來確定哪個索引用於一條SELECT語句。見7.22 EXPLAIN句法(得到關於一條SELECT的資訊)。

一些一般的建議：

10.5.3 MySQL怎樣最佳化WHERE子句

where最佳化被放在SELECT中，因為他們最主要在那裡使用裡，但是同樣的最佳化被用於DELETE和UPDATE語句。

也要注意，本節是不完全的。MySQL確實作了許多最佳化而我們沒有時間全部記錄他們。

由MySQL實施的一些最佳化列在下面：

下面是一些很快的查詢例子： 

mysql> SELECT COUNT(*) FROM tbl_name;
mysql> SELECT MIN(key_part1),MAX(key_part1) FROM tbl_name;
mysql> SELECT MAX(key_part2) FROM tbl_name
           WHERE key_part_1=constant;
mysql> SELECT ... FROM tbl_name
           ORDER BY key_part1,key_part2,... LIMIT 10;
mysql> SELECT ... FROM tbl_name
           ORDER BY key_part1 DESC,key_part2 DESC,... LIMIT 10;

下列查詢僅使用索引樹就可解決(假設索引列是數字的)： 

mysql> SELECT key_part1,key_part2 FROM tbl_name WHERE key_part1=val;
mysql> SELECT COUNT(*) FROM tbl_name
           WHERE key_part1=val1 AND key_part2=val2;
mysql> SELECT key_part2 FROM tbl_name GROUP BY key_part1;

下列查詢使用索引以排序順序檢索，不用一次另外的排序： 

mysql> SELECT ... FROM tbl_name ORDER BY key_part1,key_part2,...
mysql> SELECT ... FROM tbl_name ORDER BY key_part1 DESC,key_part2 DESC,...

10.5.4 MySQL怎樣最佳化LEFT JOIN

在MySQL中，A LEFT JOIN B實現如下：

10.5.5 MySQL怎樣最佳化LIMIT

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

10.5.6 INSERT查詢的速度

插入一個記錄的時間由下列組成：

這裡的數字有點與總體時間成正比。這不考慮打開表的初始開銷(它為每個並發運行的查詢做一次)。

表的大小以N log N (B 樹)的速度減慢索引的插入。

加快插入的一些方法：

為了對LOAD DATA INFILE和INSERT得到一些更快的速度，擴大關鍵字緩衝區。見10.2.3 調節伺服器參數。

10.5.7 UPDATE查詢的速度

更改查詢被最佳化為有一個寫開銷的一個SELECT查詢。寫速度依賴於被更新數據大小和被更新索引的數量。

使更改更快的另一個方法是推遲更改並且然後一行一行地做很多更改。如果你鎖定表，做一行一行地很多更改比一次做一個快。

注意，動態記錄格式的更改一個較長總長的記錄，可能切開記錄。因此如果你經常這樣做，時不時地OPTIMIZE TABLE是非常重要的。見7.9 OPTIMIZE TABLE句法。

10.5.8 DELETE查詢的速度

刪除一個記錄的時間精確地與索引數量成正比。為了更快速地刪除記錄，你可以增加索引緩存的大小。見10.2.3 調節伺服器參數。

從一個表刪除所有行比刪除行的一大部分也要得多。

10.6 選擇一種表類型

用MySQL，當前(版本 3.23.5)你能從一個速度觀點在4可用表的格式之間選擇。

靜態MyISAM
這種格式是最簡單且最安全的格式，它也是在磁碟格式最快的。速度來自於數據能在磁碟上被找到的難易方式。當所定有一個索引和靜態格式的東西時，它很簡單，只是行長度乘以行數量。而且在掃描一張表時，用每次磁碟讀取來讀入常數個記錄是很容易的。安全性來自於如果當寫入一個靜態MyISAM文件時，你的計算機崩潰，myisamchk能很容易指出每行在哪兒開始和結束，因此它通常能回收所有記錄，除了部分被寫入的那個。注意，在MySQL中，所有索引總能被重建。
動態MyISAM
這種格式有點複雜，因為每一行必須有一個頭說明它有多長。當一個記錄在更改時變長時，它也可以在多於一個位置上結束。你能使用OPTIMIZE table或myisamchk整理一張表。如果你在同一個表中有像某些VARCHAR或BLOB列那樣存取/改變的靜態數據，將動態列移入另外一個表以避免碎片可能是一個好主意。
壓縮MyISAM
這是一個只讀類型，用可選的myisampack工具產生。
內存(HEAP 堆）
這種表格式對小型/中型查找表十分有用。對拷貝/創建一個常用的查找表(用聯結)到一個(也許臨時)HEAP表有可能加快多個表聯結。假定我們想要做下列聯結，用同樣數據可能要幾倍時間。 
SELECT tab1.a, tab3.a FROM tab1, tab2, tab3
        WHERE tab1.a = tab2.a and tab2.a = tab3.a and tab2.c != 0;

為了加速它，我們可用tab2和tab3的聯結創建一張臨時表，因為用相同列( tab1.a )查找。這裡是創建該表和結果選擇的命令。 

CREATE TEMPORARY TABLE test TYPE=HEAP
        SELECT
                tab2.a as a2, tab3.a as a3
        FROM
                tab2, tab3
        WHERE
                tab2.a = tab3.a and c = 0;
SELECT tab1.a, test.a3 from tab1, test where tab1.a = test.a1;
SELECT tab1.b, test.a3 from tab1, test where tab1.a = test.a1 and something;

10.6.1 靜態(定長)表的特點

10.6.2 動態表的特點

10.6.3 壓縮表的特點

MySQL能支援不同的索引類型，但是一般的類型是ISAM。這是一個B樹索引並且你能粗略地為索引文件計算大小為(key_length+4)*0.67，在所有的鍵上的總和。（這是對最壞情況，當所有鍵以排序順序被插入時。）

字符串索引是空白壓縮的。如果第一個索引部分是一個字符串，它也將壓縮前綴。如果字符串列有很多尾部空白或是一個總不能用到全長的VARCHAR列，空白壓縮使索引文件更小。如果很多字符串有相同的前綴，前綴壓縮是有幫助的。

10.6.4 內存表的特點

堆桌子僅存在於內存中，因此如果mysqld被關掉或崩潰，它們將丟失，但是因為它們是很快，不管怎樣它們是有用的。

MySQL內部的HEAP表使用沒有溢出區的100%動態哈希並且沒有與刪除有關的問題。

你只能通過使用在堆表中的一個索引的用等式存取東西(通常用=操作符)。

堆表的缺點是：

  1. 你要為你想要同時使用的所有堆表需要足夠的額外內存。
  2. 你不能在索引的一個部分上搜索。
  3. 你不能順序搜索下一個條目(即使用這個索引做一個ORDER BY)。
  4. MySQL也不能算出在2個值之間大概有多少行。這被最佳化器使用來決定使用哪個索引，但是在另一方面甚至不需要磁碟尋道。

10.7 其他最佳化技巧

對加快系統的未分類的建議是：

10.8 使用你自己的效能測試

你決定應該測試你的應用程式和資料庫，以發現瓶頸在哪兒。通過修正它(或通過用一個“啞模組”代替瓶頸)，你能容易確定下一個瓶頸(等等)。即使對你的應用程式來說，整體性能“足夠好”，你至少應該對每個瓶頸做一個“計劃”，如果某人“確實需要修正它”，如何解決它。

對於一些可移植的效能程式的例子，參見MySQL效能套件。見11 MySQL 效能套件。你能利用這個套件的任何程式並且為你的需要修改它。通過這樣做，你能嘗試不同的你的問題的解決方案並測試哪一個對你是最快的解決方案。

在系統負載繁重時發生一些問題是很普遍的，並且我們有很多與我們聯繫的客戶，他們在生產系統中有一個(測試)系統並且有負載問題。到目前為止，被一種這些的情況是與基本設計有關的問題(表掃描在高負載時表現不好)或OS/庫問題。如果系統已經不在生產系統中，它們大多數將很容易修正。

為了避免這樣的問題，你應該把一些力氣放在在可能最壞的負載下測試你的整個應用！

10.9 設計選擇

MySQL在分開的文件中儲存行數據和索引數據。許多(幾乎全部)其他資料庫在同一個文件中混合行和索引數據。我們相信，MySQL的選擇對非常廣泛的現代系統的來說是較好的。

儲存行數據的另一個方法是在一個分開的區域保存每列資訊(例子是SDBM和Focus)。這將對每個存取多於一列的查詢獲得一個性能突破。因為在多於一列被存取時，這快速退化，我們相信這個模型對通用功能的資料庫不是最好。

更常見的情形是索引和數據一起被儲存(就像Oracle/Sybase)。在這種情況中，你將在索引的葉子頁面上找到行資訊。有這布局的好處是它在許多情況下(取決於這個索引被緩沖得怎樣)節省一次磁碟讀。有這布局的壞處是：

10.10 MySQL設計局限/折衷

因為MySQL使用極快的表鎖定(多次讀/一次寫)，留下的最大問題是在同一個表中的一個插入的穩定數據流與慢速選擇的一個混合。

我們相信，在其他情況下，對大多數系統，異常快速的性能使它成為一個贏家。這種情形通常也可能通過表的多個副本來解決，但是它要花更多的力氣和硬件。

對一些常見的應用環境，我們也在開發一些擴展功能以解決此問題。

10.11 可移植性

因為所有SQL伺服器實現了SQL的不同部分，要花功夫編寫可移植的SQL應用程式。對很簡單的選擇/插入，它是很容易的，但是你需要越多，它越困難，而且如果你想要應用程式對很多資料庫都快，它變得更難！

為了使一個複雜應用程式可移植，你需要選擇它應該與之工作的很多SQL伺服器。

當你能使用MySQL的crash-me 程式（http://www.mysql.com/crash-me-choose.htmy）來找出你能與之使用的資料庫伺服器的選擇的函數、類型和限制。crash-me現在對任何可能的事情測試了很長時間，但是它仍然理解測試過的大約450件事情。

例如，如果你想要能使用Informix 或 DB2，你不應該有比18個字符更長的列名。

MySQL效能程式和crash-me是獨立於資料庫的。通過觀察我們怎麼處理它，你能得到一個感覺，你必須為編寫你的獨立於資料庫的應用程式做什麼。效能本身可在MySQL原始碼版本的“sql-bench”目錄下找到。他們用DBI資料庫介面以Perl寫成(它解決問題的存取部分)。

到http://www.mysql.com/benchmark.html看這個效能的結果。

正如你可在這些結果看見的那樣，所有資料庫都有一些弱點。這就是他們不同的設計折衷導致的不同行為。

如果你為資料庫的獨立性而努力，你需要獲得每個SQL伺服器瓶頸的良好感受。MySQL在檢索和更新方面很快，但是在同一個表上混合讀者/寫者方面將有一個問題。在另一方面，當你試圖存取你最近更新了的行時，Oracle有一個很大問題(直到他們被清空到磁碟上)。事務資料庫總的來說在從記錄文件表刈谹成總結表不是很好，因為在這種情況下，行級鎖定幾乎沒用處。

為了使你的應用程式“確實獨立於資料庫”，你需要定義一個容易的可擴展的介面，用它你可操縱你的數據。因為C++在大多數系統上可以得到的，使用資料庫的一個C++ 類介面是有意義的。

如果你使用一些某個資料庫特定的功能(在MySQL中，像REPLACE命令)，你應該為SQL伺服器編碼一個方法以實現同樣的功能 (但是慢些)。用MySQL，你能使用/*! */語法把MySQL特定的關鍵詞加到查詢中。在/**/中的代碼將被大多數其他SQL伺服器視為一篇注釋(被忽略)。

如果高性能真的比準確性更重要，就像在一些web應用程式那樣。一種可能性是創建一個應用層，緩沖所有的結果以給你更高的性能。通過只是讓老的結果在短時間後‘過期’，你能保持緩存合理地刷新。這在極高負載的情況下是相當不錯的，在此情況下，你能動態地增加緩存到更大並且設定較高的過期時限直到一切回到正常。

在這種情況下，創建資訊的表應該包含緩存初始大小和表一般應該被刷新幾次的資訊。

10.12 我們已將MySQL用在何處？

在MySQL起初開發期間，MySQL的功能適合我們的最大客戶。他們為在瑞典的一些最大的零售商處理數據倉庫。

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

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

我們通過每月將所有資訊儲存在壓縮的“交易”表中來解決它。我們有一套簡單的宏/腳本用來產生來自交易表的不同條件( 產品組，顧客id，商店...)的總結表。報告是由一個進行語法分析網頁的小perl腳本動態產生的網頁，在腳本中執行SQL語句並且插入結果。現在我們很想使用PHP或mod_perl，但是那時他們沒有。

對圖形數據，我們用C語言編寫了一個簡單的工具，它能基於SQL查詢的結果(對結果的一些處理)產生贈品，這也從分析HTML文件的perl腳本中動態地執行。

在大多數情況下，一個新的報告通過簡單地複製一個現有腳本並且修改其中的SQL查詢來完成。在一些情況下，我們將需要把更多的字段加到一個現有的總結表中或產生一個新的，但是這也相當簡單，因為我們在磁碟上保存所有交易表。（目前我們有至少50G的交易表和200G的其他顧客數據)。

我們也讓我們的顧客直接用ODBC存取總結表以便高級用戶能自己試驗數據。

我們用非常中檔的Sun Ultra sparcstation ( 2x200 Mz )來處理，沒有任何問題。最近我們升級了伺服器之一到一台2個CPU 400 Mz的Ultra sparc，並且我們現在計劃處理產品級的交易，這將意味著數據增加10番。我們認為我們能通過只是為我們的系統增加更多的磁碟就能趕上它。

我們也在試驗Intel-Linux以便能更便宜地得到更多的cpu動力。既然我們有二進制可移植的資料庫格式(在3.32中引入)，我們將開始在應用程式的某些部分使用它。

我們最初的感覺是Linux在低到中等負載時執行的較好，但是你開始得到導致的高負載時，Solaris將表現更好，因為磁碟IO的極限，但是我們還沒有關於這方面的任何結論。在與一些Linux核心開發者的討論以後，這可能是 Linux 的副作用，它給批處理以太多的資源使得交互的性能變得很低。當大的批處理正在進行時，這使機器感覺很慢且無反應。希望這將在未來的Linux內核中解決。

第一章, 前一章, 下一章, 最後一章，目錄.