軟件測試中從MySQL得到最大的性能

軟件測試中從MySQL得到最大的性能

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

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

1 優化概述

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

最常見的瓶頸是：

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

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

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

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

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

其他建議：

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

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

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

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

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

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

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

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

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

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

2.2 磁盤問題

* 正如前面所述，磁盤尋道是一個性能的大瓶頸。當數據開始增長以致緩存變得不可能時，這個問題變得越來越明顯。對大數據庫，在那你或多或少地要隨機存取數據，你可以依靠你將至少需要一次磁盤尋道來讀取并且幾次磁盤尋道寫入。為了使這個問題最小化，使用有低尋道時間的磁盤。
* 為了增加可用磁盤軸的數量(并且從而減少尋道開銷)，符號聯接文件到不同磁盤或分割磁盤是可能的。

使用符號連接

這意味著你將索引/數據文件符號從正常的數據目錄鏈接到其他磁盤(那也可以被分割的)。這使得尋道和讀取時間更好(如果磁盤不用于其他事情)。見10.2.2.1 使用數據庫和表的符號鏈接。

分割

分割意味著你有許多磁盤并把第一塊放在第一個磁盤上，在第二塊放在第二個磁盤上，并且第 n塊在第(n mod number_of_disks)磁盤上，等等。這意味著，如果你的正常數據大小于分割大小(或完美地排列過)，你將得到較好一些的性能。注意，分割是否很依賴于OS和分割大小。因此用不同的分割大小測試你的應用程序。見10.8 使用你自己的基準。注意對分割的速度差異很依賴于參數，取決于你如何分割參數和磁盤數量，你可以得出以數量級的不同。注意你必須選擇為隨機或順序存取優化。

* 為了可靠，你可能想要使用襲擊RAID 0+1(分割+鏡像)，但是在這種情況下，你將需要2*N個驅動器來保存N個驅動器的數據。如果你有錢，這可能是最好的選擇！然而你也可能必須投資一些卷管理軟件投資以高效地處理它。
* 一個好選擇是讓稍重要的數據(它能再生)上存在RAID 0磁盤上，而將確實重要的數據(像主機信息和日志文件)存在一個RAID 0+1或RAID N磁盤上。如果因為更新奇偶位你有許多寫入，RAID N可能是一個問題。
* 你也可以對數據庫使用的文件系統設置參數。一個容易的改變是以noatime選項掛裝文件系統。這是它跳過更新在inode中的最后訪問時間，而且這將避免一些磁盤尋道。

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))

2.3 調節服務器參數

你能用這個命令得到mysqld服務器缺省緩沖區大?。?/P>

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選項是最后一個；否則，命令行上在它之后列出的任何選項的效果將不在反映在輸出中。

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。

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

* 不是當前使用的表被釋放，以最近最少使用（LRU）順序。
* 如果緩存滿了并且沒有表可以釋放，但是一個新表需要打開，緩存必須臨時被擴大。
* 如果緩存處于一個臨時擴大狀態并且一個表從在用變為不在用狀態，它被關閉并從緩存中釋放。

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

2.5 在同一個數據庫中創建大量數據庫表的缺點

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

2.6 為什么有這么多打開的表？

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

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

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

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

2.7 MySQL怎樣使用內存

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

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

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

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”中的鎖代碼以使用一個單個隊列。在這種情況下，寫鎖定和讀鎖定將有同樣優先級，它可能幫助一些應用程序。

2.9 數據庫表級鎖定的問題

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

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

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

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

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

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

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

* 一個客戶發出一個花很長時間運行的SELECT。
* 然后其他客戶在一個使用的表上發出一個UPDATE；這個客戶將等待直到SELECT完成。
* 另一個客戶在同一個表上發出另一個SELECT語句；因為UPDATE比SELECT有更高的優先級，該SELECT將等待UPDATE的完成。它也將等待第一個SELECT完成！

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

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

3 使你的數據盡可能小

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

你能用下面的技術使表的性能更好并且使存儲空間最?。?/P>

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

4 MySQL索引的使用

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

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

索引用于：

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

假定你發出下列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 */