優(yōu)化是一項(xiàng)復(fù)雜的任務(wù)，因?yàn)樗罱K需要對(duì)整個(gè)系統(tǒng)的理解。當(dāng)用你的系統(tǒng)/應(yīng)用的小知識(shí)做一些局部?jī)?yōu)化是可能的時(shí)候，你越想讓你的系統(tǒng)更優(yōu)化，你必須知道它也越多。

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

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

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

我們以系統(tǒng)級(jí)的東西開(kāi)始，因?yàn)檫@些決策的某一些很早就做好了。在其他情況下，快速瀏覽這部分可能就夠了，因?yàn)樗鼘?duì)大收獲并不重要，但是有一個(gè)關(guān)于在這個(gè)層次上收獲有多大的感覺(jué)總是好的。

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

因?yàn)槲覀儧](méi)在很多平臺(tái)上運(yùn)行生產(chǎn)MySQL，我們忠告你在可能選擇它前，測(cè)試你打算運(yùn)行的平臺(tái)。

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

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

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

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

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

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

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

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

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

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

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

現(xiàn)在，對(duì)在db1中的任一表tbl_a，在db2種也好象有一個(gè)表tbl_a。如果一個(gè)線程更新db1.tbl_a并且另一個(gè)線程更新db2.tbl_a，將有問(wèn)題。

如果你確實(shí)需要這樣，你必須改變下列在“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ù)

你能用這個(gè)命令得到mysqld服務(wù)器缺省緩沖區(qū)大小： shell> mysqld --help

這個(gè)命令生成一張所有mysqld選項(xiàng)和可配置變量的表。輸出包括缺省值并且看上去象這樣一些東西： 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

如果有一個(gè)mysqld服務(wù)器正在運(yùn)行，通過(guò)執(zhí)行這個(gè)命令，你可以看到它實(shí)際上使用的變量的值： shell> mysqladmin variables

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

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

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

如果你有很多內(nèi)存和很多表并且有一個(gè)中等數(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 &

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

注意，如果你改變mysqld的一個(gè)選項(xiàng)，它實(shí)際上只對(duì)服務(wù)器的那個(gè)例子保持。

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

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

table_cache, max_connections和max_tmp_tables影響服務(wù)器保持打開(kāi)的文件的最大數(shù)量。如果你增加這些值的一個(gè)或兩個(gè)，你可以遇到你的操作系統(tǒng)每個(gè)進(jìn)程打開(kāi)文件描述符的數(shù)量上強(qiáng)加的限制。然而，你可以能在許多系統(tǒng)上增加該限制。請(qǐng)教你的OS文檔找出如何做這些，因?yàn)楦淖兿拗频姆椒ǜ飨到y(tǒng)有很大的不同。

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

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

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

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

如果你在一個(gè)目錄中有許多文件，打開(kāi)、關(guān)閉和創(chuàng)建操作將會(huì)很慢。如果你執(zhí)行在許多不同表上的SELECT語(yǔ)句，當(dāng)表緩存滿時(shí)，將有一點(diǎn)開(kāi)銷(xiāo)，因?yàn)閷?duì)每個(gè)必須打開(kāi)的表，另外一個(gè)必須被關(guān)閉。你可以通過(guò)使表緩沖更大些來(lái)減少這個(gè)開(kāi)銷(xiāo)。 10.2.6 為什么有這么多打開(kāi)的表？

當(dāng)你運(yùn)行mysqladmin status時(shí)，你將看見(jiàn)象這樣的一些東西： Uptime: 426 Running threads: 1 Questions: 11082 Reloads: 1 Open tables: 12

如果你僅有6個(gè)表，這可能有點(diǎn)令人困惑。

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

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

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

MySQL中所有鎖定不會(huì)是死鎖的。這通過(guò)總是在一個(gè)查詢前立即請(qǐng)求所有必要的鎖定并且總是以同樣的順序鎖定表來(lái)管理。

對(duì)WRITE，MySQL使用的鎖定方法原理如下： 如果在表上沒(méi)有鎖，放一個(gè)鎖在它上面。 否則，把鎖定請(qǐng)求放在寫(xiě)鎖定隊(duì)列中。

對(duì)READ，MySQL使用的鎖定方法原理如下： 如果在表上沒(méi)有寫(xiě)鎖定，把一個(gè)讀鎖定放在它上面。 否則，把鎖請(qǐng)求放在讀鎖定隊(duì)列中。

當(dāng)一個(gè)鎖定被釋放時(shí)，鎖定可被寫(xiě)鎖定隊(duì)列中的線程得到，然后是讀鎖定隊(duì)列中的線程。

這意味著，如果你在一個(gè)表上有許多更改，SELECT語(yǔ)句將等待直到有沒(méi)有更多的更改。

為了解決在一個(gè)表中進(jìn)行很多INSERT和SELECT操作的情況，你可在一張臨時(shí)表中插入行并且偶爾用來(lái)自臨時(shí)表的記錄更新真正的表。

這可用下列代碼做到： 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選項(xiàng)的INSERT。見(jiàn)7.14 INSERT句法。

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

MySQL的表鎖定代碼是不會(huì)死鎖的。

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

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

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

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

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

對(duì)此一個(gè)主要的問(wèn)題如下： 一個(gè)客戶發(fā)出一個(gè)花很長(zhǎng)時(shí)間運(yùn)行的SELECT。 然后其他客戶在一個(gè)使用的表上發(fā)出一個(gè)UPDATE；這個(gè)客戶將等待直到SELECT完成。 另一個(gè)客戶在同一個(gè)表上發(fā)出另一個(gè)SELECT語(yǔ)句；因?yàn)閁PDATE比SELECT有更高的優(yōu)先級(jí)，該SELECT將等待UPDATE的完成。它也將等待第一個(gè)SELECT完成！

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

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

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

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

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

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

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

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

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

如果列不構(gòu)成索引的最左面前綴，MySQL不能使用一個(gè)部分的索引。假定你下面顯示的SELECT語(yǔ)句： 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;

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

如果LIKE參數(shù)是一個(gè)不以一個(gè)通配符字符起始的一個(gè)常數(shù)字符串，MySQL也為L(zhǎng)IKE比較使用索引。例如，下列SELECT語(yǔ)句使用索引： mysql> select * from tbl_name where key_col LIKE "Patrick%"; mysql> select * from tbl_name where key_col LIKE "Pat%_ck%";

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

下列SELECT語(yǔ)句將不使用索引： mysql> select * from tbl_name where key_col LIKE "%Patrick%"; mysql> select * from tbl_name where key_col LIKE other_col;

在第一條語(yǔ)句中，LIKE值以一個(gè)通配符字符開(kāi)始。在第二條語(yǔ)句中，LIKE值不是一個(gè)常數(shù)。

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

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

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

下列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ù)的查詢速度

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

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

如果你的問(wèn)題是與一些明顯的MySQL函數(shù)有關(guān)，你總能在MySQL客戶中計(jì)算其時(shí)間： 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個(gè)+表達(dá)式。

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

在大多數(shù)情況下，你能通過(guò)計(jì)算磁盤(pán)尋道估計(jì)性能。對(duì)小的表，你通常能在1次磁盤(pán)尋道中找到行(因?yàn)檫@個(gè)索引可能被緩沖)。對(duì)更大的表，你能估計(jì)它(使用 B++ 樹(shù)索引)，你將需要：log(row_count)/log(index_block_length/3*2/(index_length + data_pointer_length))+1次尋道找到行。

在MySQL中，索引塊通常是1024個(gè)字節(jié)且數(shù)據(jù)指針通常是4個(gè)字節(jié)，這對(duì)一個(gè)有一個(gè)索引長(zhǎng)度為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ù)來(lái)找出行。

然而對(duì)于寫(xiě)，你將需要 4 次尋道請(qǐng)求(如上)來(lái)找到在哪兒存放新索引并且通常需2次尋道更新這個(gè)索引并且寫(xiě)入行。

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

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

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

where優(yōu)化被放在SELECT中，因?yàn)樗麄冏钪饕谀抢锸褂美铮峭瑯拥膬?yōu)化被用于DELETE和UPDATE語(yǔ)句。

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

由MySQL實(shí)施的一些優(yōu)化列在下面： 刪除不必要的括號(hào)： ((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ù)表達(dá)式僅計(jì)算一次。 在一個(gè)單個(gè)表上的沒(méi)有一個(gè)WHERE的COUNT(*)直接從表中檢索信息。當(dāng)僅使用一個(gè)表時(shí)，對(duì)任何NOT NULL表達(dá)式也這樣做。 無(wú)效常數(shù)表達(dá)式的早期檢測(cè)。MySQL快速檢測(cè)某些SELECT語(yǔ)句是不可能的并且不返回行。 如果你不使用GROUP BY或分組函數(shù)(COUNT()、MIN()……)，HAVING與WHERE合并。 為每個(gè)子聯(lián)結(jié)(sub join)，構(gòu)造一個(gè)更簡(jiǎn)單的WHERE以得到一個(gè)更快的WHERE計(jì)算并且也盡快跳過(guò)記錄。 所有常數(shù)的表在查詢中的任何其他表前被首先讀出。一個(gè)常數(shù)的表是： 一個(gè)空表或一個(gè)有1行的表。 與在一個(gè)UNIQUE索引、或一個(gè)PRIMARY KEY的WHERE子句一起使用的表，這里所有的索引部分使用一個(gè)常數(shù)表達(dá)式并且索引部分被定義為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; 對(duì)聯(lián)結(jié)表的最好聯(lián)結(jié)組合是通過(guò)嘗試所有可能性來(lái)找到。如果所有在ORDER BY和GROUP BY的列來(lái)自同一個(gè)表，那么當(dāng)廉潔時(shí)，該表首先被選中。 如果有一個(gè)ORDER BY子句和一個(gè)不同的GROUP BY子句，或如果ORDER BY或GROUP BY包含不是來(lái)自聯(lián)結(jié)隊(duì)列中的第一個(gè)表的其他表的列，創(chuàng)建一個(gè)臨時(shí)表。 如果你使用SQL_SMALL_RESULT，MySQL將使用一個(gè)在內(nèi)存中的表。 因?yàn)镈ISTINCT被變換到在所有的列上的一個(gè)GROUP BY，DISTINCT與ORDER BY結(jié)合也將在許多情況下需要一張臨時(shí)表。 每個(gè)表的索引被查詢并且使用跨越少于30% 的行的索引。如果這樣的索引沒(méi)能找到，使用一個(gè)快速的表掃描。 在一些情況下，MySQL能從索引中讀出行，甚至不咨詢數(shù)據(jù)文件。如果索引使用的所有列是數(shù)字的，那么只有索引樹(shù)被用來(lái)解答查詢。 在每個(gè)記錄被輸出前，那些不匹配HAVING子句的行被跳過(guò)。

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

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

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

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

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

加快插入的一些方法： 如果你同時(shí)從同一客戶插入很多行，使用多個(gè)值表的INSERT語(yǔ)句。這比使用分開(kāi)INSERT語(yǔ)句快(在一些情況中幾倍)。 如果你從不同客戶插入很多行，你能通過(guò)使用INSERT DELAYED語(yǔ)句得到更高的速度。見(jiàn)7.14 INSERT句法。 注意，用MyISAM，如果在表中沒(méi)有刪除的行，能在SELECT:s正在運(yùn)行的同時(shí)插入行。 當(dāng)從一個(gè)文本文件裝載一個(gè)表時(shí)，使用LOAD DATA INFILE。這通常比使用很多INSERT語(yǔ)句快20倍。見(jiàn)7.16 LOAD DATA INFILE句法。 當(dāng)表有很多索引時(shí)，有可能多做些工作使得LOAD DATA INFILE更快些。使用下列過(guò)程： 有選擇地用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并且想要壓縮表，在它上面運(yùn)行myisampack。見(jiàn)10.6.3 壓縮表的特征。 用myisamchk -r -q /path/to/db/tbl_name再創(chuàng)建索引。這將在將它寫(xiě)入磁盤(pán)前在內(nèi)存中創(chuàng)建索引樹(shù)，并且它更快，因?yàn)楸苊獯罅看疟P(pán)尋道。結(jié)果索引樹(shù)也被完美地平衡。 執(zhí)行FLUSH TABLES，或外殼命令mysqladmin flush-tables。

這個(gè)過(guò)程將被構(gòu)造進(jìn)在MySQL的某個(gè)未來(lái)版本的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ū)僅被清洗到磁盤(pán)上一次，在所有INSERT語(yǔ)句完成后。一般有與有不同的INSERT語(yǔ)句那樣奪的索引緩沖區(qū)清洗。如果你能用一個(gè)單個(gè)語(yǔ)句插入所有的行，鎖定就不需要。鎖定也將降低多連接測(cè)試的整體時(shí)間，但是對(duì)某些線程最大等待時(shí)間將上升(因?yàn)樗麄兊却i)。例如： 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前完成，但是整體時(shí)間應(yīng)該快大約40%。因?yàn)镮NSERT, UPDATE和DELETE操作在MySQL中是很快的，通過(guò)為多于大約5次連續(xù)不斷地插入或更新一行的東西加鎖，你將獲得更好的整體性能。如果你做很多一行的插入，你可以做一個(gè)LOCK TABLES，偶爾隨后做一個(gè)UNLOCK TABLES(大約每1000行)以允許另外的線程存取表。這仍然將導(dǎo)致獲得好的性能。當(dāng)然，LOAD DATA INFILE對(duì)裝載數(shù)據(jù)仍然是更快的。

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

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

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

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

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

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

10.6 選擇一種表類型

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

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

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

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

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

堆桌子僅存在于內(nèi)存中，因此如果mysqld被關(guān)掉或崩潰，它們將丟失，但是因?yàn)樗鼈兪呛芸欤还茉鯓铀鼈兪怯杏玫摹?

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

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

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

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

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

對(duì)于一些可移植的基準(zhǔn)程序的例子，參見(jiàn)MySQL基準(zhǔn)套件。見(jiàn)11 MySQL 基準(zhǔn)套件。你能利用這個(gè)套件的任何程序并且為你的需要修改它。通過(guò)這樣做，你能?chē)L試不同的你的問(wèn)題的解決方案并測(cè)試哪一個(gè)對(duì)你是最快的解決方案。

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

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

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

存儲(chǔ)行數(shù)據(jù)的另一個(gè)方法是在一個(gè)分開(kāi)的區(qū)域保存每列信息(例子是SDBM和Focus)。這將對(duì)每個(gè)存取多于一列的查詢獲得一個(gè)性能突破。因?yàn)樵诙嘤谝涣斜淮嫒r(shí)，這快速退化，我們相信這個(gè)模型對(duì)通用功能的數(shù)據(jù)庫(kù)不是最好。

更常見(jiàn)的情形是索引和數(shù)據(jù)一起被存儲(chǔ)(就象Oracle/Sybase)。在這種情況中，你將在索引的葉子頁(yè)面上找到行信息。有這布局的好處是它在許多情況下(取決于這個(gè)索引被緩沖得怎樣)節(jié)省一次磁盤(pán)讀。有這布局的壞處是： 表掃描更慢，因?yàn)槟惚仨氉x完索引以獲得數(shù)據(jù)。 你損失很多空間，因?yàn)槟惚仨氈貜?fù)來(lái)自節(jié)點(diǎn)的索引(因?yàn)槟悴荒茉诠?jié)點(diǎn)上存儲(chǔ)行） 刪除將隨時(shí)間變化惡化數(shù)據(jù)庫(kù)表(因?yàn)楣?jié)點(diǎn)中的索引在刪除后通常不被更新)。 你不能僅使用索引表為一個(gè)查詢檢索數(shù)據(jù)。 索引數(shù)據(jù)很難緩沖。 10.10 MySQL設(shè)計(jì)局限/折衷

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

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

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

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

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

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

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

MySQL基準(zhǔn)程序和crash-me是獨(dú)立于數(shù)據(jù)庫(kù)的。通過(guò)觀察我們?cè)趺刺幚硭隳艿玫揭粋€(gè)感覺(jué)，你必須為編寫(xiě)你的獨(dú)立于數(shù)據(jù)庫(kù)的應(yīng)用程序做什么。基準(zhǔn)本身可在MySQL源代碼分發(fā)的“sql-bench”目錄下找到。他們用DBI數(shù)據(jù)庫(kù)接口以Perl寫(xiě)成(它解決問(wèn)題的存取部分)。

到http://www.mysql.com/benchmark.html看這個(gè)基準(zhǔn)的結(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