優(yōu)化是一項復(fù)雜的任務(wù)，因為它最終需要對整個系統(tǒng)的理解。當用你的系統(tǒng)/應(yīng)用的小知識做一些局部優(yōu)化是可能的時候，你越想讓你的系統(tǒng)更優(yōu)化，你必須知道它也越多。

因此，本章將試圖解釋并給出優(yōu)化MySQL的不同方法的一些例子。但是記住總是有某些(逐漸變難)是系統(tǒng)更快的方法留著去做。 10.1 優(yōu)化概述

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

最常見的瓶頸是： 磁盤尋道。磁盤花時間找到一個數(shù)據(jù)，用在1999年的現(xiàn)代磁盤其平均時間通常小于10ms，因此理論上我們能大約一秒尋道 1000 次。這個時間用新磁盤提高很慢并且很難對一個表優(yōu)化。優(yōu)化它的方法是將數(shù)據(jù)散布在多個磁盤上。 當磁盤在我們需要讀數(shù)據(jù)的正確位置時，磁盤讀/寫。用1999年的現(xiàn)代，一個磁盤傳輸類似10-20Mb/s。這必尋道更容易優(yōu)化，因為你能從多個磁盤并行地讀。 CPU周期。當我們讀數(shù)據(jù)進內(nèi)存時，(或如果它已經(jīng)在那里)我們需要處理它以達到我們的結(jié)果。當我們有相對內(nèi)存較小的表時，這是最常見的限制因素，但是用小表速度通常不是問題。 內(nèi)存帶寬。當CPU需要超出適合cpu緩存的數(shù)據(jù)時，緩存帶寬就成為內(nèi)存的一個瓶頸。這是對大多數(shù)系統(tǒng)的一個不常見的瓶頸但是你應(yīng)該知道它。 10.2 系統(tǒng)/編譯時和啟動參數(shù)的調(diào)節(jié)

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

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

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

其他建議： 如果你有足夠的RAM，你能刪除所有交換設(shè)備。一些操作系統(tǒng)在某些情況下將使用一個SWAP設(shè)備，即使你有空閑的內(nèi)存。 使用--skip-locking的MySQL選項避免外部鎖定。注意這將不影響MySQL功能，只要它僅運行在一個服務(wù)器上。只要在你運行myisamchk以前，記得要停掉服務(wù)器(或鎖定相關(guān)部分)。在一些系統(tǒng)上這個開關(guān)是強制的，因為外部鎖定不是在任何情況下都工作。當用MIT-pthreads編譯時，--skip-locking選項缺省為打開(on)，因為flock()沒在所有的平臺上被MIT-pthreads充分支持。唯一的情況是如果你對同一數(shù)據(jù)運行MySQL服務(wù)器(不是客戶)，你不能使用--skip-locking之時，否則對沒有先清掉(flushing)或先鎖定mysqld服務(wù)器的表上運行myisamchk。你仍然能使用LOCK TABLES/ UNLOCK TABLES，即使你正在使用--skip-locking。 10.2.1 編譯和鏈接怎樣影響MySQL的速度

大多數(shù)下列測試在Linux上并用MySQL基準進行的，但是它們應(yīng)該對其他操作系統(tǒng)和工作負載給出一些指示。

當你用-static鏈接時，你得到最快的可執(zhí)行文件。使用Unix套接字而非TCP/IP連接一個數(shù)據(jù)庫也可給出好一些的性能。

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

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

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

這里是我們做過的一些測量表： 如果你以-O6使用pgcc并且編譯任何東西，mysqld服務(wù)器是比用gcc快11%（用字符串99的版本)。 如果你動態(tài)地鏈接(沒有-static)，結(jié)果慢了13%。注意你仍能使用一個動態(tài)連接的MySQL庫。只有服務(wù)器對性能是關(guān)鍵的。 如果你使用TCP/IP而非Unix套接字，結(jié)果慢7.5%。 在一個Sun SPARCstation 10上，gcc2.7.3是比Sun Pro C++ 4.2快13%。 在Solaris 2.5.1上，在單個處理器上MIT-pthreads比帶原生線程的Solaris慢8-12%。以更多的負載/cpus，差別應(yīng)該變得更大。

由TcX提供的MySQL-Linux的分發(fā)用pgcc編譯并靜態(tài)鏈接。 10.2.2 磁盤問題 正如前面所述，磁盤尋道是一個性能的大瓶頸。當數(shù)據(jù)開始增長以致緩存變得不可能時，這個問題變得越來越明顯。對大數(shù)據(jù)庫，在那你或多或少地要隨機存取數(shù)據(jù)，你可以依靠你將至少需要一次磁盤尋道來讀取并且?guī)状未疟P尋道寫入。為了使這個問題最小化，使用有低尋道時間的磁盤。 為了增加可用磁盤軸的數(shù)量(并且從而減少尋道開銷)，符號聯(lián)接文件到不同磁盤或分割磁盤是可能的。 使用符號連接 這意味著你將索引/數(shù)據(jù)文件符號從正常的數(shù)據(jù)目錄鏈接到其他磁盤(那也可以被分割的)。這使得尋道和讀取時間更好(如果磁盤不用于其他事情)。見10.2.2.1 使用數(shù)據(jù)庫和表的符號鏈接。 分割 分割意味著你有許多磁盤并把第一塊放在第一個磁盤上，在第二塊放在第二個磁盤上，并且第 n塊在第(n mod number_of_disks)磁盤上，等等。這意味著，如果你的正常數(shù)據(jù)大小于分割大小(或完美地排列過)，你將得到較好一些的性能。注意，分割是否很依賴于OS和分割大小。因此用不同的分割大小測試你的應(yīng)用程序。見10.8 使用你自己的基準。注意對分割的速度差異很依賴于參數(shù)，取決于你如何分割參數(shù)和磁盤數(shù)量，你可以得出以數(shù)量級的不同。注意你必須選擇為隨機或順序存取優(yōu)化。 為了可靠，你可能想要使用襲擊RAID 0+1(分割+鏡像)，但是在這種情況下，你將需要2*N個驅(qū)動器來保存N個驅(qū)動器的數(shù)據(jù)。如果你有錢，這可能是最好的選擇！然而你也可能必須投資一些卷管理軟件投資以高效地處理它。 一個好選擇是讓稍重要的數(shù)據(jù)(它能再生)上存在RAID 0磁盤上，而將確實重要的數(shù)據(jù)(像主機信息和日志文件)存在一個RAID 0+1或RAID N磁盤上。如果因為更新奇偶位你有許多寫入，RAID N可能是一個問題。 你也可以對數(shù)據(jù)庫使用的文件系統(tǒng)設(shè)置參數(shù)。一個容易的改變是以noatime選項掛裝文件系統(tǒng)。這是它跳過更新在inode中的最后訪問時間，而且這將避免一些磁盤尋道。 10.2.2.1 為數(shù)據(jù)庫和表使用符號鏈接

你可以從數(shù)據(jù)庫目錄移動表和數(shù)據(jù)庫到別處，并且用鏈接到新地點的符號代替它們。你可能想要這樣做，例如，轉(zhuǎn)移一個數(shù)據(jù)庫到有更多空閑空間的一個文件系統(tǒng)。

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

MySQL缺省不支持數(shù)據(jù)庫鏈接。只要你不在數(shù)據(jù)庫之間做一個符號鏈接，一切將工作正常。假定你在MySQL數(shù)據(jù)目錄下有一個數(shù)據(jù)庫db1，并且做了一個符號鏈接db2指向db1： shell> cd /path/to/datadir shell> ln -s db1 db2

現(xiàn)在，對在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))

把代碼改變?yōu)檫@樣： if (realpath(to,buff)) 10.2.3 調(diào)節(jié)服務(wù)器參數(shù)

你能用這個命令得到mysqld服務(wù)器缺省緩沖區(qū)大小： 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服務(wù)器正在運行，通過執(zhí)行這個命令，你可以看到它實際上使用的變量的值： shell> mysqladmin variables

每個選項在下面描述。對于緩沖區(qū)大小、長度和棧大小的值以字節(jié)給出，你能用于個后綴“K”或“M” 指出以K字節(jié)或兆字節(jié)顯示值。例如，16M指出16兆字節(jié)。后綴字母的大小寫沒有關(guān)系；16M和16m是相同的。

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

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

如果你有很多內(nèi)存和很多表并且有一個中等數(shù)量的客戶，想要最大的性能，你應(yīng)該一些象這樣的東西： shell> safe_mysqld -O key_buffer=16M -O table_cache=128 \ -O sort_buffer=4M -O record_buffer=1M & 如果你有較少的內(nèi)存和大量的連接，使用這樣一些東西： 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 &

如果有很多連接，“交換問題”可能發(fā)生，除非mysqld已經(jīng)被配置每個連接使用很少的內(nèi)存。當然如果你對所有連接有足夠的內(nèi)存，mysqld執(zhí)行得更好。

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

為了明白一個參數(shù)變化的效果，這樣做： shell> mysqld -O key_buffer=32m --help

保證--help選項是最后一個；否則，命令行上在它之后列出的任何選項的效果將不在反映在輸出中。 10.2.4 MySQL怎樣打開和關(guān)閉數(shù)據(jù)庫表

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

table_cache與max_connections有關(guān)。例如，對于200個打開的連接，你應(yīng)該讓一張表的緩沖至少有200 * n，這里n是一個聯(lián)結(jié)(join)中表的最大數(shù)量。

打開表的緩存可以增加到一個table_cache的最大值（缺省為64；這可以用mysqld的-O table_cache=#選項來改變）。一個表絕對不被關(guān)閉，除非當緩存滿了并且另外一個線程試圖打開一個表時或如果你使用mysqladmin refresh或mysqladmin flush-tables。

當表緩存滿時，服務(wù)器使用下列過程找到一個緩存入口來使用： 不是當前使用的表被釋放，以最近最少使用（LRU）順序。 如果緩存滿了并且沒有表可以釋放，但是一個新表需要打開，緩存必須臨時被擴大。 如果緩存處于一個臨時擴大狀態(tài)并且一個表從在用變?yōu)椴辉谟脿顟B(tài)，它被關(guān)閉并從緩存中釋放。

對每個并發(fā)存取打開一個表。這意味著，如果你讓2個線程存取同一個表或在同一個查詢中存取表兩次(用AS)，表需要被打開兩次。任何表的第一次打開占2個文件描述符；表的每一次額外使用僅占一個文件描述符。對于第一次打開的額外描述符用于索引文件；這個描述符在所有線程之間共享。 10.2.5 在同一個數(shù)據(jù)庫中創(chuàng)建大量數(shù)據(jù)庫表的缺點

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

當你運行mysqladmin status時，你將看見象這樣的一些東西： Uptime: 426 Running threads: 1 Questions: 11082 Reloads: 1 Open tables: 12

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

MySQL是多線程的，因此它可以同時在同一個表上有許多詢問。為了是2個線程在同一個文件上有不同狀態(tài)的問題減到最小，表由每個并發(fā)進程獨立地打開。這為數(shù)據(jù)文件消耗一些內(nèi)存和一個額外的文件描述符。索引文件描述符在所有線程之間共享。 10.2.7 MySQL怎樣使用內(nèi)存

下表指出mysqld服務(wù)器使用存儲器的一些方式。在應(yīng)用的地方，給出與存儲器使用相關(guān)的服務(wù)器變量的名字。 關(guān)鍵字緩沖區(qū)(變量key_buffer_size)由所有線程分享；當需要時，分配服務(wù)器使用的其他緩沖區(qū)。見10.2.3 調(diào)節(jié)服務(wù)器參數(shù)。 每個連接使用一些線程特定的空間；一個棧(缺省64K，變量thread_stack)、一個連接緩沖區(qū)(變量net_buffer_length)和一個結(jié)果緩沖區(qū)(變量net_buffer_length)。當需要時，連接緩沖區(qū)和結(jié)果緩沖區(qū)動態(tài)地被擴大到max_allowed_packet。當一個查詢正在運行當前查詢的一個拷貝時，也分配字符串。 所有線程共享同一基存儲器。 目前還沒有什么是內(nèi)存映射的(除了壓縮表，但是那是另外一個的故事)。這是因為4GB的32位存儲器空間對最大的數(shù)據(jù)庫來所不是足夠大的。當一個64位尋址空間的系統(tǒng)變得更普遍時，我們可以為內(nèi)存映射增加全面的支持。 每個做順序掃描的請求分配一個讀緩沖區(qū)(變量record_buffer)。 所有聯(lián)結(jié)均用一遍完成并且大多數(shù)聯(lián)結(jié)可以甚至不用一張臨時表來完成。最臨時的表是基于內(nèi)存的(HEAP)表。有較大記錄長度(以所有列的長度之和計算)的臨時表或包含BLOB列的表在磁盤上存儲。在MySQL版本3.23.2前一個問題是如果一張HEAP表超過tmp_table_size的大小，你得到錯誤The table tbl_name is full。在更新的版本中，這通過必要時自動將在內(nèi)存的(HEAP)表轉(zhuǎn)變?yōu)橐粋€基于磁盤(MyISAM)的表來處理。為了解決這個問題，你可以通過設(shè)置mysqld的tmp_table_size選項，或通過在客戶程序中設(shè)置SQL的SQL_BIG_TABLES選項增加臨時表的大小。見7.25 SET OPTION句法。在MySQL 3.20中，臨時表的最大尺寸是record_buffer*16，因此如果你正在使用這個版本，你必須增加record_buffer值。你也可以使用--big-tables選項啟動mysqld以總將臨時表存儲在磁盤上，然而，這將影響許多復(fù)雜查詢的速度。 大多數(shù)做排序的請求分配一個排序緩沖區(qū)和一個或二個臨時文件。見18.5 MySQL在哪兒存儲臨時文件。 幾乎所有的語法分析和計算都在一家本地存儲器中完成。對小項目沒有內(nèi)存開銷并且一般的較慢存儲器分配和釋放被避免。內(nèi)存僅為出乎意料的大字符串分配(這用malloc()和free()完成)。 每個索引文件只被打開一次，并且數(shù)據(jù)文件為每個并發(fā)運行的線程打開一次。對每個并發(fā)線程，分配一個表結(jié)構(gòu)、對每列的列結(jié)構(gòu)和大小為3 * n的一個緩沖區(qū)(這里n是最大的行長度，不算BLOB列)。一個BLOB使用5 ～ 8個字節(jié)加上BLOB數(shù)據(jù)。 對每個有BLOB列的表，一個緩沖區(qū)動態(tài)地被擴大以便讀入更大的BLOB值。如果你掃描一個表，分配與最大BLOB值一樣大的一個緩沖區(qū)。 對所有在用的表的表處理器被保存在一個緩存中并且作為一個FIFO管理。通常緩存有64個入口。如果一個表同時被2個運行的線程使用，緩存為此包含2個入口。見10.2.4 MySQL如何打開和關(guān)閉數(shù)據(jù)庫表。 一個mysqladmin flush-tables命令關(guān)閉所有不在用的表并在當前執(zhí)行的線程結(jié)束時，標記所有在用的表準備被關(guān)閉。這將有效地釋放大多數(shù)在用的內(nèi)存。

ps和其他系統(tǒng)狀態(tài)程序可以報導(dǎo)mysqld使用很多內(nèi)存。這可以是在不同的內(nèi)存地址上的線程棧造成的。例如，Solaris版本的ps將棧間未用的內(nèi)存算作已用的內(nèi)存。你可以通過用swap -s檢查可用交換區(qū)來驗證它。我們用商業(yè)內(nèi)存漏洞探查器測試了mysqld，因此應(yīng)該有沒有內(nèi)存漏洞。 10.2.8 MySQL怎樣鎖定數(shù)據(jù)庫表

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;

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

你也能改變在“mysys/thr_lock.c”中的鎖代碼以使用一個單個隊列。在這種情況下，寫鎖定和讀鎖定將有同樣優(yōu)先級，它可能幫助一些應(yīng)用程序。 10.2.9 數(shù)據(jù)庫表級鎖定的問題

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

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

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

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

因為數(shù)據(jù)庫的更改通常被視為比SELECT更重要，更新一個表的所有語句比從一個表中檢索信息的語句有更高的優(yōu)先級。這應(yīng)該保證更改不被“餓死”，因為一個人針對一個特定表會發(fā)出很多繁重的查詢。

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

對此一個主要的問題如下： 一個客戶發(fā)出一個花很長時間運行的SELECT。 然后其他客戶在一個使用的表上發(fā)出一個UPDATE；這個客戶將等待直到SELECT完成。 另一個客戶在同一個表上發(fā)出另一個SELECT語句；因為UPDATE比SELECT有更高的優(yōu)先級，該SELECT將等待UPDATE的完成。它也將等待第一個SELECT完成！

對這個問題的一些可能的解決方案是： 試著使SELECT語句運行得更快；你可能必須創(chuàng)建一些摘要(summary)表做到這點。 用--low-priority-updates啟動mysqld。這將給所有更新(修改)一個表的語句以比SELECT語句低的優(yōu)先級。在這種情況下，在先前情形的最后的SELECT語句將在INSERT語句前執(zhí)行。 你可以用LOW_PRIORITY屬性給與一個特定的INSERT、UPDATE或DELETE語句較低優(yōu)先級。 為max_write_lock_count指定一個低值來啟動mysqld使得在一定數(shù)量的WRITE鎖定后給出READ鎖定。 通過使用SQL命令：SET SQL_LOW_PRIORITY_UPDATES=1，你可從一個特定線程指定所有的更改應(yīng)該由用低優(yōu)先級完成。見7.25 SET OPTION句法。 你可以用HIGH_PRIORITY屬性指明一個特定SELECT是很重要的。見7.12 SELECT句法。 如果你有關(guān)于INSERT結(jié)合SELECT的問題，切換到使用新的MyISAM表，因為它們支持并發(fā)的SELECT和INSERT。 如果你主要混合INSERT和SELECT語句，DELAYED屬性的INSERT將可能解決你的問題。見7.14 INSERT句法。 如果你有關(guān)于SELECT和DELETE的問題，LIMIT選項的DELETE可以幫助你。見7.11 DELETE句法。 10.3 使你的數(shù)據(jù)盡可能小

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

你能用下面的技術(shù)使表的性能更好并且使存儲空間最小： 盡可能地使用最有效(最小)的類型。MySQL有很多節(jié)省磁盤空間和內(nèi)存的專業(yè)化類型。 如果可能使表更小，使用較小的整數(shù)類型。例如，MEDIUMINT經(jīng)常比INT好一些。 如果可能，聲明列為NOT NULL。它使任何事情更快而且你為每列節(jié)省一位。注意如果在你的應(yīng)用程序中你確實需要NULL，你應(yīng)該毫無疑問使用它，只是避免缺省地在所有列上有它。 如果你沒有任何變長列(VARCHAR、TEXT或BLOB列)，使用固定尺寸的記錄格式。這比較快但是不幸地可能會浪費一些空間。見10.6 選擇一種表類型。 每張桌子應(yīng)該有盡可能短的主索引。這使一行的辨認容易而有效。 對每個表，你必須決定使用哪種存儲/索引方法。見9.4 MySQL表類型。也可參見10.6 選擇一種表類型。 只創(chuàng)建你確實需要的索引。索引對檢索有好處但是當你需要快速存儲東西時就變得糟糕。如果你主要通過搜索列的組合來存取一個表，以它們做一個索引。第一個索引部分應(yīng)該是最常用的列。如果你總是使用許多列，你應(yīng)該首先以更多的副本使用列以獲得更好的列索引壓縮。 如果很可能一個索引在頭幾個字符上有唯一的前綴，僅僅索引該前綴比較好。MySQL支持在一個字符列的一部分上的索引。更短的索引更快，不僅因為他們占較少的磁盤空間而且因為他們將在索引緩存中給你更多的命中率并且因此有更少磁盤尋道。見10.2.3 調(diào)節(jié)服務(wù)器參數(shù)。 在一些情形下，分割一個經(jīng)常被掃描進2個表的表是有益的。特別是如果它是一個動態(tài)格式的表并且它可能使一個能用來掃描后找出相關(guān)行的較小靜態(tài)格式的表。 10.4 MySQL索引的使用

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

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

索引用于： 快速找出匹配一個WHERE子句的行。 當執(zhí)行聯(lián)結(jié)時，從其他表檢索行。 對特定的索引列找出MAX()或MIN()值。 如果排序或分組在一個可用鍵的最左面前綴上進行(例如，ORDER BY key_part_1,key_part_2)，排序或分組一個表。如果所有鍵值部分跟隨DESC，鍵以倒序被讀取。 在一些情況中，一個查詢能被優(yōu)化來檢索值，不用咨詢數(shù)據(jù)文件。如果對某些表的所有使用的列是數(shù)字型的并且構(gòu)成某些鍵的最左面前綴，為了更快，值可以從索引樹被檢索出來。

假定你發(fā)出下列SELECT語句： mysql> SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;

如果一個多列索引存在于col1和col2上，適當?shù)男锌梢灾苯颖蝗〕觥Ｈ绻珠_的單行列索引存在于col1和col2上，優(yōu)化器試圖通過決定哪個索引將找到更少的行并來找出更具限制性的索引并且使用該索引取行。

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

如果列不構(gòu)成索引的最左面前綴，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參數(shù)是一個不以一個通配符字符起始的一個常數(shù)字符串，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值不是一個常數(shù)。

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

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

任何不跨越的在WHERE子句的所有AND層次的索引不用來優(yōu)化詢問。

下列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 存取或更新數(shù)據(jù)的查詢速度

首先，一件事情影響所有的詢問。你有的許可系統(tǒng)設(shè)置越復(fù)雜，你得到更多的開銷。

如果你不讓任何GRANT語句執(zhí)行，MySQL將稍微優(yōu)化許可檢查。因此如果你有很大量，值得花時間來避免授權(quán)，否則更多的許可檢查有更大的開銷。

如果你的問題是與一些明顯的MySQL函數(shù)有關(guān)，你總能在MySQL客戶中計算其時間： mysql> select benchmark(1000000,1+1); +------------------------+ | benchmark(1000000,1+1) | +------------------------+ | 0 | +------------------------+ 1 row in set (0.32 sec)

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

所有MySQL函數(shù)應(yīng)該被高度優(yōu)化，但是以可能有一些例外并且benchmark(loop_count,expression)是找出是否你的查詢有問題的一個極好工具。 10.5.1 估計查詢性能

在大多數(shù)情況下，你能通過計算磁盤尋道估計性能。對小的表，你通常能在1次磁盤尋道中找到行(因為這個索引可能被緩沖)。對更大的表，你能估計它(使用 B++ 樹索引)，你將需要：log(row_count)/log(index_block_length/3*2/(index_length + data_pointer_length))+1次尋道找到行。

在MySQL中，索引塊通常是1024個字節(jié)且數(shù)據(jù)指針通常是4個字節(jié)，這對一個有一個索引長度為3(中等整數(shù))的 500,000 行的表給你：log(500,000)/log(1024/3*2/(3+4)) + 1= 4 次尋道。

象上面的索引將要求大約 500,000 * 7 * 3/2 = 5.2M，(假設(shè)索引緩沖區(qū)被充滿到2/3(它是典型的))，你將可能在內(nèi)存中有索引的大部分并且你將可能僅需要1-2調(diào)用從OS讀數(shù)據(jù)來找出行。

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

注意，上述不意味著你的應(yīng)用程序?qū)⒕徛匾?N log N 退化！當表格變得更大時，只要一切被OS或SQL服務(wù)器緩沖，事情將僅僅或多或少地更慢。在數(shù)據(jù)變得太大不能被緩沖后，事情將開始變得更慢直到你的應(yīng)用程序僅僅受磁盤尋道限制(它以N log N增加)。為了避免這個增加，索引緩沖隨數(shù)據(jù)增加而增加。見10.2.3 調(diào)節(jié)服務(wù)器參數(shù)。 10.5.2 SELECT查詢的速度

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

一些一般的建議： 為了幫助MySQL更好地優(yōu)化查詢，在它已經(jīng)裝載了相關(guān)數(shù)據(jù)后，在一個表上運行myisamchk --analyze。這為每一個更新一個值，指出有相同值地平均行數(shù)（當然，對唯一索引，這總是1。） 為了根據(jù)一個索引排序一個索引和數(shù)據(jù)，使用myisamchk --sort-index --sort-records=1（如果你想要在索引1上排序)。如果你有一個唯一索引，你想要根據(jù)該索引地次序讀取所有的記錄，這是使它更快的一個好方法。然而注意，這個排序沒有被最佳地編寫，并且對一個大表將花很長時間！ 10.5.3 MySQL怎樣優(yōu)化WHERE子句

where優(yōu)化被放在SELECT中，因為他們最主要在那里使用里，但是同樣的優(yōu)化被用于DELETE和UPDATE語句。

也要注意，本節(jié)是不完全的。MySQL確實作了許多優(yōu)化而我們沒有時間全部記錄他們。

由MySQL實施的一些優(yōu)化列在下面： 刪除不必要的括號： ((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) 常數(shù)調(diào)入： (a b>5 AND b=c AND a=5 刪除常數(shù)條件(因常數(shù)調(diào)入所需)： (B>=5 AND B=5) OR (B=6 AND 5=5) OR (B=7 AND 5=6) -> B=5 OR B=6 索引使用的常數(shù)表達式僅計算一次。 在一個單個表上的沒有一個WHERE的COUNT(*)直接從表中檢索信息。當僅使用一個表時，對任何NOT NULL表達式也這樣做。 無效常數(shù)表達式的早期檢測。MySQL快速檢測某些SELECT語句是不可能的并且不返回行。 如果你不使用GROUP BY或分組函數(shù)(COUNT()、MIN()……)，HAVING與WHERE合并。 為每個子聯(lián)結(jié)(sub join)，構(gòu)造一個更簡單的WHERE以得到一個更快的WHERE計算并且也盡快跳過記錄。 所有常數(shù)的表在查詢中的任何其他表前被首先讀出。一個常數(shù)的表是： 一個空表或一個有1行的表。 與在一個UNIQUE索引、或一個PRIMARY KEY的WHERE子句一起使用的表，這里所有的索引部分使用一個常數(shù)表達式并且索引部分被定義為NOT NULL。

所有下列的表用作常數(shù)表： mysql> SELECT * FROM t WHERE primary_key=1; mysql> SELECT * FROM t1,t2 WHERE t1.primary_key=1 AND t2.primary_key=t1.id; 對聯(lián)結(jié)表的最好聯(lián)結(jié)組合是通過嘗試所有可能性來找到。如果所有在ORDER BY和GROUP BY的列來自同一個表，那么當廉潔時，該表首先被選中。 如果有一個ORDER BY子句和一個不同的GROUP BY子句，或如果ORDER BY或GROUP BY包含不是來自聯(lián)結(jié)隊列中的第一個表的其他表的列，創(chuàng)建一個臨時表。 如果你使用SQL_SMALL_RESULT，MySQL將使用一個在內(nèi)存中的表。 因為DISTINCT被變換到在所有的列上的一個GROUP BY，DISTINCT與ORDER BY結(jié)合也將在許多情況下需要一張臨時表。 每個表的索引被查詢并且使用跨越少于30% 的行的索引。如果這樣的索引沒能找到，使用一個快速的表掃描。 在一些情況下，MySQL能從索引中讀出行，甚至不咨詢數(shù)據(jù)文件。如果索引使用的所有列是數(shù)字的，那么只有索引樹被用來解答查詢。 在每個記錄被輸出前，那些不匹配HAVING子句的行被跳過。

下面是一些很快的查詢例子： 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;

下列查詢僅使用索引樹就可解決(假設(shè)索引列是數(shù)字的)： 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怎樣優(yōu)化LEFT JOIN

在MySQL中，A LEFT JOIN B實現(xiàn)如下： 表B被設(shè)置為依賴于表A。 表A被設(shè)置為依賴于所有用在LEFT JOIN條件的表(除B外)。 所有LEFT JOIN條件被移到WHERE子句中。 進行所有標準的聯(lián)結(jié)優(yōu)化，除了一個表總是在所有它依賴的表之后被讀取。如果有一個循環(huán)依賴，MySQL將發(fā)出一個錯誤。 進行所有標準的WHERE優(yōu)化。 如果在A中有一行匹配WHERE子句，但是在B中沒有任何行匹配LEFT JOIN條件，那么在B中生成所有列設(shè)置為NULL的一行。 如果你使用LEFT JOIN來找出在某些表中不存在的行并且在WHERE部分你有下列測試：column_name IS NULL，這里column_name 被聲明為NOT NULL的列，那么MySQL在它已經(jīng)找到了匹配LEFT JOIN條件的一行后，將停止在更多的行后尋找(對一特定的鍵組合)。 10.5.5 MySQL怎樣優(yōu)化LIMIT

在一些情況中，當你使用LIMIT #而不使用HAVING時，MySQL將以不同方式處理查詢。 如果你用LIMIT只選擇一些行，當MySQL一般比較喜歡做完整的表掃描時，它將在一些情況下使用索引。 如果你使用LIMIT #與ORDER BY，MySQL一旦找到了第一個 # 行，將結(jié)束排序而不是排序整個表。 當結(jié)合LIMIT #和DISTINCT時，MySQL一旦找到#個唯一的行，它將停止。 在一些情況下，一個GROUP BY能通過順序讀取鍵(或在鍵上做排序)來解決，并然后計算摘要直到鍵值改變。在這種情況下，LIMIT #將不計算任何不必要的GROUP。 只要MySQL已經(jīng)發(fā)送了第一個#行到客戶，它將放棄查詢。 LIMIT 0將總是快速返回一個空集合。這對檢查查詢并且得到結(jié)果列的列類型是有用的。 臨時表的大小使用LIMIT #計算需要多少空間來解決查詢。 10.5.6 INSERT查詢的速度

插入一個記錄的時間由下列組成： 連接：(3) 發(fā)送查詢給服務(wù)器：(2) 分析查詢：(2) 插入記錄：（1 x 記錄大小） 插入索引：（1 x 索引） 關(guān)閉：(1)

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

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

加快插入的一些方法： 如果你同時從同一客戶插入很多行，使用多個值表的INSERT語句。這比使用分開INSERT語句快(在一些情況中幾倍)。 如果你從不同客戶插入很多行，你能通過使用INSERT DELAYED語句得到更高的速度。見7.14 INSERT句法。 注意，用MyISAM，如果在表中沒有刪除的行，能在SELECT:s正在運行的同時插入行。 當從一個文本文件裝載一個表時，使用LOAD DATA INFILE。這通常比使用很多INSERT語句快20倍。見7.16 LOAD DATA INFILE句法。 當表有很多索引時，有可能多做些工作使得LOAD DATA INFILE更快些。使用下列過程： 有選擇地用CREATE TABLE創(chuàng)建表。例如使用mysql或Perl-DBI。 執(zhí)行FLUSH TABLES，或外殼命令mysqladmin flush-tables。 使用myisamchk --keys-used=0 -rq /path/to/db/tbl_name。這將從表中刪除所有索引的使用。 用LOAD DATA INFILE把數(shù)據(jù)插入到表中，這將不更新任何索引，因此很快。 如果你有myisampack并且想要壓縮表，在它上面運行myisampack。見10.6.3 壓縮表的特征。 用myisamchk -r -q /path/to/db/tbl_name再創(chuàng)建索引。這將在將它寫入磁盤前在內(nèi)存中創(chuàng)建索引樹，并且它更快，因為避免大量磁盤尋道。結(jié)果索引樹也被完美地平衡。 執(zhí)行FLUSH TABLES，或外殼命令mysqladmin flush-tables。

這個過程將被構(gòu)造進在MySQL的某個未來版本的LOAD DATA INFILE。 你可以鎖定你的表以加速插入。mysql> LOCK TABLES a WRITE; mysql> REPLACE INTO a VALUES (1,23),(2,34),(4,33); mysql> REPLACE INTO a VALUES (8,26),(6,29); mysql> UNLOCK TABLES;

主要的速度差別是索引緩沖區(qū)僅被清洗到磁盤上一次，在所有INSERT語句完成后。一般有與有不同的INSERT語句那樣奪的索引緩沖區(qū)清洗。如果你能用一個單個語句插入所有的行，鎖定就不需要。鎖定也將降低多連接測試的整體時間，但是對某些線程最大等待時間將上升(因為他們等待鎖)。例如： thread 1 does 1000 inserts thread 2, 3, and 4 does 1 insert thread 5 does 1000 inserts

如果你不使用鎖定，2、3和4將在1和5前完成。如果你使用鎖定，2、3和4將可能不在1或5前完成，但是整體時間應(yīng)該快大約40%。因為INSERT, UPDATE和DELETE操作在MySQL中是很快的，通過為多于大約5次連續(xù)不斷地插入或更新一行的東西加鎖，你將獲得更好的整體性能。如果你做很多一行的插入，你可以做一個LOCK TABLES，偶爾隨后做一個UNLOCK TABLES(大約每1000行)以允許另外的線程存取表。這仍然將導(dǎo)致獲得好的性能。當然，LOAD DATA INFILE對裝載數(shù)據(jù)仍然是更快的。

為了對LOAD DATA INFILE和INSERT得到一些更快的速度，擴大關(guān)鍵字緩沖區(qū)。見10.2.3 調(diào)節(jié)服務(wù)器參數(shù)。 10.5.7 UPDATE查詢的速度

更改查詢被優(yōu)化為有一個寫開銷的一個SELECT查詢。寫速度依賴于被更新數(shù)據(jù)大小和被更新索引的數(shù)量。

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

注意，動態(tài)記錄格式的更改一個較長總長的記錄，可能切開記錄。因此如果你經(jīng)常這樣做，時不時地OPTIMIZE TABLE是非常重要的。見7.9 OPTIMIZE TABLE句法。 10.5.8 DELETE查詢的速度

刪除一個記錄的時間精確地與索引數(shù)量成正比。為了更快速地刪除記錄，你可以增加索引緩存的大小。見10.2.3 調(diào)節(jié)服務(wù)器參數(shù)。

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

10.6 選擇一種表類型

用MySQL，當前(版本 3.23.5)你能從一個速度觀點在4可用表的格式之間選擇。 靜態(tài)MyISAM 這種格式是最簡單且最安全的格式，它也是在磁盤格式最快的。速度來自于數(shù)據(jù)能在磁盤上被找到的難易方式。當所定有一個索引和靜態(tài)格式的東西時，它很簡單，只是行長度乘以行數(shù)量。而且在掃描一張表時，用每次磁盤讀取來讀入常數(shù)個記錄是很容易的。安全性來自于如果當寫入一個靜態(tài)MyISAM文件時，你的計算機崩潰，myisamchk能很容易指出每行在哪兒開始和結(jié)束，因此它通常能回收所有記錄，除了部分被寫入的那個。注意，在MySQL中，所有索引總能被重建。 動態(tài)MyISAM 這種格式有點復(fù)雜，因為每一行必須有一個頭說明它有多長。當一個記錄在更改時變長時，它也可以在多于一個位置上結(jié)束。你能使用OPTIMIZE table或myisamchk整理一張表。如果你在同一個表中有象某些VARCHAR或BLOB列那樣存取/改變的靜態(tài)數(shù)據(jù)，將動態(tài)列移入另外一個表以避免碎片可能是一個好主意。 壓縮MyISAM 這是一個只讀類型，用可選的myisampack工具生成。 內(nèi)存(HEAP 堆） 這種表格式對小型/中型查找表十分有用。對拷貝/創(chuàng)建一個常用的查找表(用聯(lián)結(jié))到一個(也許臨時)HEAP表有可能加快多個表聯(lián)結(jié)。假定我們想要做下列聯(lián)結(jié)，用同樣數(shù)據(jù)可能要幾倍時間。 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的聯(lián)結(jié)創(chuàng)建一張臨時表，因為用相同列( tab1.a )查找。這里是創(chuàng)建該表和結(jié)果選擇的命令。 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 靜態(tài)(定長)表的特點 這是缺省格式。它用在表不包含VARCHAR、BLOB或TEXT列時候。 所有的CHAR、NUMERIC和DECIMAL列充填到列寬度。 非常快。 容易緩沖。 容易在崩潰后重建，因為記錄位于固定的位置。 不必被重新組織(用myisamchk)，除非一個巨量的記錄被刪除并且你想要歸還空閑磁盤空間給操作系統(tǒng)。 通常比動態(tài)表需要更多的磁盤空間。 10.6.2 動態(tài)表的特點 如果表包含任何VARCHAR、BLOB或TEXT列，使用該格式。 所有字符串列是動態(tài)的(除了那些長度不到4的列)。 每個記錄前置一個位圖，對字符串列指出哪個列是空的('')，或?qū)?shù)字列哪個是零(這不同于包含NULL值的列)。如果字符串列在刪除尾部空白后有零長度，或數(shù)字列有零值，它在位圖中標記并且不保存到磁盤上。非空字符串存儲為一個長度字節(jié)加字符串內(nèi)容。 通常比定長表占更多的磁盤空間。 每個記錄僅使用所需的空間。如果一個記錄變得更大，它按需要被切開多段，這導(dǎo)致記錄碎片。 如果你與超過行長度的信息更新行，行將被分段。在這種情況中，你可能必須時時運行myisamchk -r以使性能更好。使用myisamchk -ei tbl_name做一些統(tǒng)計。 在崩潰后不容易重建，因為一個記錄可以是分很多段并且一個連接(碎片)可以丟失。 對動態(tài)尺寸記錄的期望行長度是：3 + (number of columns + 7) / 8 + (number of char columns) + packed size of numeric columns + length of strings + (number of NULL columns + 7) / 8

對每個連接有6個字節(jié)的懲罰。無論何時更改引起記錄的增大，一個動態(tài)記錄被鏈接。每個新鏈接將至少是20個字節(jié)，因此下一增大將可能在同一鏈連中。如果不是，將有另外一個鏈接。你可以用myisamchk -ed檢查有多少鏈接。所有的鏈接可以用 myisamchk -r 刪除。 10.6.3 壓縮表的特點 一張用myisampack實用程序制作的只讀表。所有具有MySQL擴展電子郵件支持的客戶可以為其內(nèi)部使用保留一個myisampack拷貝。 解壓縮代碼存在于所有MySQL分發(fā)，以便甚至沒有myisampack的客戶能讀取用myisampack壓縮的表。 占據(jù)很小的磁盤空間，使磁盤使用量減到最小。 每個記錄被單獨壓縮(很小的存取開銷)。對一個記錄的頭是定長的(1-3 字節(jié))，取決于表中最大的記錄。每列以不同方式被壓縮。一些壓縮類型是： 通常對每列有一張不同的哈夫曼表。 后綴空白壓縮。 前綴空白壓縮。 用值0的數(shù)字使用1位存儲。 如果整數(shù)列的值有一個小范圍，列使用最小的可能類型來存儲。例如，如果所有的值在0到255的范圍，一個BIGINT列(8個字節(jié))可以作為一個TINYINT列(1字節(jié))存儲。 如果列僅有可能值的一個小集合，列類型被變換到ENUM。 列可以使用上面的壓縮方法的組合。 能處理定長或動態(tài)長度的記錄，然而不能處理BLOB或TEXT列。 能用myisamchk解壓縮。

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

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

10.6.4 內(nèi)存表的特點

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

MySQL內(nèi)部的HEAP表使用沒有溢出區(qū)的100%動態(tài)哈希并且沒有與刪除有關(guān)的問題。

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

堆表的缺點是： 你要為你想要同時使用的所有堆表需要足夠的額外內(nèi)存。 你不能在索引的一個部分上搜索。 你不能順序搜索下一個條目(即使用這個索引做一個ORDER BY)。 MySQL也不能算出在2個值之間大概有多少行。這被優(yōu)化器使用來決定使用哪個索引，但是在另一方面甚至不需要磁盤尋道。 10.7 其他優(yōu)化技巧

對加快系統(tǒng)的未分類的建議是： 使用持久的連接數(shù)據(jù)庫以避免連接開銷。 總是檢查你的所有詢問確實使用你已在表中創(chuàng)建了的索引。在MySQL中，你可以用EXPLAIN命令做到。見7.22 EXPLAIN句法(得到關(guān)于SELECT的信息)。 嘗試避免在被更改了很多的表上的復(fù)雜的SELECT查詢。這避免與鎖定表有關(guān)的問題。 在一些情況下，使得基于來自其他表的列的信息引入一個“ 哈希”的列有意義。如果該列較短并且有合理的唯一值，它可以比在許多列上的一個大索引快些。在MySQL中，很容易使用這個額外列：SELECT * from table where hash='calculated hash on col1 and col2' and col_1='constant' and col_2='constant' and .. 。 對于有很多更改的表，你應(yīng)該試著避免所有VARCHAR或BLOB列。只要你使用單個VARCHAR或BLOB列，你將得到動態(tài)行長度。見9.4 MySQL表類型。 只是因為行太大，分割一張表為不同的表一般沒有什么用處。為了存取行，最大的性能命沖擊是磁盤尋道以找到行的第一個字節(jié)。在找到數(shù)據(jù)后，大多數(shù)新型磁盤對大多數(shù)應(yīng)用程序來說足夠快，能讀入整個行。它確實有必要分割的唯一情形是如果其動態(tài)行尺寸的表(見上述)能變?yōu)楣潭ǖ男写笮。蛉绻愫茴l繁地需要掃描表格而不需要大多數(shù)列。見9.4 MySQL表類型。 如果你很經(jīng)常地需要基于來自很多行的信息計算(如計數(shù))，引入一個新表并實時更新計數(shù)器可能更好一些。類型的更改UPDATE table set count=count+1 where index_column=constant是很快的！當你使用象MySQL那樣的只有表級鎖定(多重讀/單個寫)的數(shù)據(jù)庫時，這確實重要。這也將給出大多數(shù)數(shù)據(jù)庫較好的性能，因為鎖定管理器在這種情況下有較少的事情要做。 11111111111111111111111 如果你需要從大的記錄文件表中收集統(tǒng)計信息，使用總結(jié)性的表而不是掃描整個表。維護總結(jié)應(yīng)該比嘗試做“實時”統(tǒng)計要快些。當有變化而不是必須改變運行的應(yīng)用時，從記錄文件重新生成新的總結(jié)表(取決于業(yè)務(wù)決策)要快多了！ 如果可能，應(yīng)該將報告分類為“實時”或“統(tǒng)計”，這里統(tǒng)計報告所需的數(shù)據(jù)僅僅基于從實際數(shù)據(jù)產(chǎn)生的總結(jié)表中產(chǎn)生。 充分利用列有缺省值的事實。當被插入值不同于缺省值時，只是明確地插入值。這減少MySQL需要做的語法分析并且改進插入速度。 在一些情況下，包裝并存儲數(shù)據(jù)到一個BLOB中是很方便的。在這種情況下，你必須在你的應(yīng)用中增加額外的代碼來打包/解包BLOB中的東西，但是這種方法可以在某些階段節(jié)省很多存取。當你有不符合靜態(tài)的表結(jié)構(gòu)的數(shù)據(jù)時，這很實用。 在一般情況下，你應(yīng)該嘗試以第三范式保存數(shù)據(jù)，但是如果你需要這些以獲得更快的速度，你應(yīng)該不用擔心重復(fù)或創(chuàng)建總結(jié)表。 存儲過程或UDF(用戶定義函數(shù))可能是獲得更好性能的一個好方法，然而如果你使用某些不支持它的數(shù)據(jù)庫，在這種情況中，你應(yīng)該總是有零一個方法(較慢的)做這些。 你總是能通過在你的應(yīng)用程序中緩沖查詢/答案并嘗試同時做很多插入/更新來獲得一些好處。如果你的數(shù)據(jù)庫支持鎖定表(象MySQL和Oracle)，這應(yīng)該有助于確保索引緩沖在所有更新后只清空一次。 但你不知道何時寫入你的數(shù)據(jù)時，使用INSERT /*! DELAYED */。這加快處理，因為很多記錄可以用一次磁盤寫入被寫入。 當你想要讓你的選擇顯得更重要時，使用INSERT /*! LOW_PRIORITY */。 使用SELECT /*! HIGH_PRIORITY */來取得塞入隊列的選擇，它是即使有人等待做一個寫入也要完成的選擇。 使用多行INSERT語句來存儲很多有一條SQL命令的行(許多SQL服務(wù)器支持它）。 使用LOAD DATA INFILE裝載較大數(shù)量的數(shù)據(jù)。這比一般的插入快并且當myisamchk集成在mysqld中時，甚至將更快。 使用AUTO_INCREMENT列構(gòu)成唯一值。 當使用動態(tài)表格式時，偶爾使用OPTIMIZE TABLE以避免碎片。見7.9O PTIMIZE TABLE句法。 可能時使用HEAP表以得到更快的速度。見9.4 MySQL表類型。 當使用一個正常Web服務(wù)器設(shè)置時，圖象應(yīng)該作為文件存儲。這僅在數(shù)據(jù)庫中存儲的一本文件的引用。這樣做的主要原因是是一個正常的Web服務(wù)器在緩沖文件比數(shù)據(jù)庫內(nèi)容要好得多，因此如果你正在使用文件，較容易得到一個較快的系統(tǒng)。 對經(jīng)常存取的不重要數(shù)據(jù)(象有關(guān)對沒有cookie用戶最后顯示標語的信息）使用內(nèi)存表。 在不同表中具有相同信息的列應(yīng)該被聲明為相同的并有相同的名字。在版本 3.23 前，你只能靠較慢的聯(lián)結(jié)。嘗試使名字簡單化(在客戶表中使用name而不是customer_name)。為了使你的名字能移植到其他SQL服務(wù)器，你應(yīng)該使他們短于18 個字符。 如果你需要確實很高的速度，你應(yīng)該研究一下不同SQL服務(wù)器支持的數(shù)據(jù)存儲的底層接口！例如直接存取MySQL MyISAM，比起使用SQL 接口，你能得到2-5倍的速度提升。然而為了能做到它，數(shù)據(jù)必須是在與應(yīng)用程序性在同一臺機器的服務(wù)器上，并且通常它只應(yīng)該被一個進程存取(因為外部文件鎖定確實很慢)。通過在MySQL服務(wù)器中引進底層MyISAM命令能消除以上問題(如果需要，這可能是獲得更好性能的一個容易的方法)。借助精心設(shè)計的數(shù)據(jù)庫接口，應(yīng)該相當容易支持這類優(yōu)化。 在許多情況下，從一個數(shù)據(jù)庫存取數(shù)據(jù)(使用一個實時連接)比存取一個文本文件快些，只是因為數(shù)據(jù)庫比文本文件更緊湊(如果你使用數(shù)字數(shù)據(jù))并且這將涉及更少的磁盤存取。你也節(jié)省代碼，因為你不須分析你的文本文件來找出行和列的邊界。 你也能使用復(fù)制加速。見19.1 數(shù)據(jù)庫復(fù)制。 10.8 使用你自己的基準測試

你決定應(yīng)該測試你的應(yīng)用程序和數(shù)據(jù)庫，以發(fā)現(xiàn)瓶頸在哪兒。通過修正它(或通過用一個“啞模塊”代替瓶頸)，你能容易確定下一個瓶頸(等等)。即使對你的應(yīng)用程序來說，整體性能“足夠好”，你至少應(yīng)該對每個瓶頸做一個“計劃”，如果某人“確實需要修正它”，如何解決它。

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

在系統(tǒng)負載繁重時發(fā)生一些問題是很普遍的，并且我們有很多與我們聯(lián)系的客戶，他們在生產(chǎn)系統(tǒng)中有一個(測試)系統(tǒng)并且有負載問題。到目前為止，被一種這些的情況是與基本設(shè)計有關(guān)的問題(表掃描在高負載時表現(xiàn)不好)或OS/庫問題。如果系統(tǒng)已經(jīng)不在生產(chǎn)系統(tǒng)中，它們大多數(shù)將很容易修正。

為了避免這樣的問題，你應(yīng)該把一些力氣放在在可能最壞的負載下測試你的整個應(yīng)用！ 10.9 設(shè)計選擇

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

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

更常見的情形是索引和數(shù)據(jù)一起被存儲(就象Oracle/Sybase)。在這種情況中，你將在索引的葉子頁面上找到行信息。有這布局的好處是它在許多情況下(取決于這個索引被緩沖得怎樣)節(jié)省一次磁盤讀。有這布局的壞處是： 表掃描更慢，因為你必須讀完索引以獲得數(shù)據(jù)。 你損失很多空間，因為你必須重復(fù)來自節(jié)點的索引(因為你不能在節(jié)點上存儲行） 刪除將隨時間變化惡化數(shù)據(jù)庫表(因為節(jié)點中的索引在刪除后通常不被更新)。 你不能僅使用索引表為一個查詢檢索數(shù)據(jù)。 索引數(shù)據(jù)很難緩沖。 10.10 MySQL設(shè)計局限/折衷

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

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

對一些常見的應(yīng)用環(huán)境，我們也在開發(fā)一些擴展功能以解決此問題。 10.11 可移植性

因為所有SQL服務(wù)器實現(xiàn)了SQL的不同部分，要花功夫編寫可移植的SQL應(yīng)用程序。對很簡單的選擇/插入，它是很容易的，但是你需要越多，它越困難，而且如果你想要應(yīng)用程序?qū)芏鄶?shù)據(jù)庫都快，它變得更難！

為了使一個復(fù)雜應(yīng)用程序可移植，你需要選擇它應(yīng)該與之工作的很多SQL服務(wù)器。

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

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

MySQL基準程序和crash-me是獨立于數(shù)據(jù)庫的。通過觀察我們怎么處理它，你能得到一個感覺，你必須為編寫你的獨立于數(shù)據(jù)庫的應(yīng)用程序做什么。基準本身可在MySQL源代碼分發(fā)的“sql-bench”目錄下找到。他們用DBI數(shù)據(jù)庫接口以Perl寫成(它解決問題的存取部分)。

到http://www.mysql.com/benchmark.html看這個基準的結(jié)果。

http://www.cncms.com.cn/mysql/3870.htm

http://www.diybl.com/course/7_databases/mysql/Mysqljs/2008829/138799.html

http://andyao.javaeye.com/blog/123197