溫馨提示×

溫馨提示×

您好，登錄后才能下訂單哦！

密碼登錄×
登錄注冊×
獲取短信驗證碼
其他方式登錄
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
登 錄 注冊有禮
云服務器 香港服務器 高防服務器
最新更新 網站標簽 地圖導航
產品

從MySQL得到最大的性能 ------ 轉

發布時間：2020-08-09 23:36:08 來源：ITPUB博客 閱讀：148 作者：spaceair 欄目：MySQL數據庫

優化MySQL的參考。轉自網絡。

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

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

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

最常見的瓶頸是：

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

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

如果你以-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編譯并靜態鏈接。

10.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中的最后訪問時間，而且這將避免一些磁盤尋道。
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服務器缺省緩沖區大?。?br />
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。

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

不是當前使用的表被釋放，以最近最少使用（LRU）順序。
如果緩存滿了并且沒有表可以釋放，但是一個新表需要打開，緩存必須臨時被擴大。
如果緩存處于一個臨時擴大狀態并且一個表從在用變為不在用狀態，它被關閉并從緩存中釋放。
對每個并發存取打開一個表。這意味著，如果你讓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服務器使用存儲器的一些方式。在應用的地方，給出與存儲器使用相關的服務器變量的名字。

關鍵字緩沖區(變量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，因此應該有沒有內存漏洞。

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。

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

一個客戶發出一個花很長時間運行的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句法。
10.3 使你的數據盡可能小
最基本的優化之一是使你的數據(和索引)在磁盤上(并且在內存中)占據的空間盡可能小。這能給出巨大的改進，因為磁盤讀入較快并且通常也用較少的主存儲器。如果在更小的列上做索引，索引也占據較少的資源。

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

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的信息)。

一些一般的建議：

為了幫助MySQL更好地優化查詢，在它已經裝載了相關數據后，在一個表上運行myisamchk --analyze。這為每一個更新一個值，指出有相同值地平均行數（當然，對唯一索引，這總是1。）
為了根據一個索引排序一個索引和數據，使用myisamchk --sort-index --sort-records=1（如果你想要在索引1上排序)。如果你有一個唯一索引，你想要根據該索引地次序讀取所有的記錄，這是使它更快的一個好方法。然而注意，這個排序沒有被最佳地編寫，并且對一個大表將花很長時間！
10.5.3 MySQL怎樣優化WHERE子句
where優化被放在SELECT中，因為他們最主要在那里使用里，但是同樣的優化被用于DELETE和UPDATE語句。

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

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

刪除不必要的括號：
((a AND B) AND c OR (((a AND B) AND (c AND d))))
-> (a AND b AND c) OR (a AND b AND c AND d)

常數調入：
(a-> b>5 AND b=c AND a=5

刪除常數條件(因常數調入所需)：
(B>=5 AND B=5) OR (B=6 AND 5=5) OR (B=7 AND 5=6)
-> B=5 OR B=6

索引使用的常數表達式僅計算一次。
在一個單個表上的沒有一個WHERE的COUNT(*)直接從表中檢索信息。當僅使用一個表時，對任何NOT NULL表達式也這樣做。
無效常數表達式的早期檢測。MySQL快速檢測某些SELECT語句是不可能的并且不返回行。
如果你不使用GROUP BY或分組函數(COUNT()、MIN()……)，HAVING與WHERE合并。
為每個子聯結(sub join)，構造一個更簡單的WHERE以得到一個更快的WHERE計算并且也盡快跳過記錄。
所有常數的表在查詢中的任何其他表前被首先讀出。一個常數的表是：
一個空表或一個有1行的表。
與在一個UNIQUE索引、或一個PRIMARY KEY的WHERE子句一起使用的表，這里所有的索引部分使用一個常數表達式并且索引部分被定義為NOT NULL。
所有下列的表用作常數表：

mysql> SELECT * FROM t WHERE primary_key=1;
mysql> SELECT * FROM t1,t2
WHERE t1.primary_key=1 AND t2.primary_key=t1.id;

對聯結表的最好聯結組合是通過嘗試所有可能性來找到從MySQL得到最大的性能 ------ 轉。如果所有在ORDER BY和GROUP BY的列來自同一個表，那么當廉潔時，該表首先被選中。
如果有一個ORDER BY子句和一個不同的GROUP BY子句，或如果ORDER BY或GROUP BY包含不是來自聯結隊列中的第一個表的其他表的列，創建一個臨時表。
如果你使用SQL_SMALL_RESULT，MySQL將使用一個在內存中的表。
因為DISTINCT被變換到在所有的列上的一個GROUP BY，DISTINCT與ORDER BY結合也將在許多情況下需要一張臨時表。
每個表的索引被查詢并且使用跨越少于30% 的行的索引。如果這樣的索引沒能找到，使用一個快速的表掃描。
在一些情況下，MySQL能從索引中讀出行，甚至不咨詢數據文件。如果索引使用的所有列是數字的，那么只有索引樹被用來解答查詢。
在每個記錄被輸出前，那些不匹配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;

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

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實現如下：

表B被設置為依賴于表A。
表A被設置為依賴于所有用在LEFT JOIN條件的表(除B外)。
所有LEFT JOIN條件被移到WHERE子句中。
進行所有標準的聯結優化，除了一個表總是在所有它依賴的表之后被讀取。如果有一個循環依賴，MySQL將發出一個錯誤。
進行所有標準的WHERE優化。
如果在A中有一行匹配WHERE子句，但是在B中沒有任何行匹配LEFT JOIN條件，那么在B中生成所有列設置為NULL的一行。
如果你使用LEFT JOIN來找出在某些表中不存在的行并且在WHERE部分你有下列測試：column_name IS NULL，這里column_name 被聲明為NOT NULL的列，那么MySQL在它已經找到了匹配LEFT JOIN條件的一行后，將停止在更多的行后尋找(對一特定的鍵組合)。
10.5.5 MySQL怎樣優化LIMIT
在一些情況中，當你使用LIMIT #而不使用HAVING時，MySQL將以不同方式處理查詢。

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

連接：(3)
發送查詢給服務器：(2)
分析查詢：(2)
插入記錄：（1 x 記錄大?。?br />插入索引：（1 x 索引）
關閉：(1)
這里的數字有點與總體時間成正比。這不考慮打開表的初始開銷(它為每個并發運行的查詢做一次)。

表的大小以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創建表。例如使用mysql或Perl-DBI。
執行FLUSH TABLES，或外殼命令mysqladmin flush-tables。
使用myisamchk --keys-used=0 -rq /path/to/db/tbl_name。這將從表中刪除所有索引的使用。
用LOAD DATA INFILE把數據插入到表中，這將不更新任何索引，因此很快。
如果你有myisampack并且想要壓縮表，在它上面運行myisampack。見10.6.3 壓縮表的特征。
用myisamchk -r -q /path/to/db/tbl_name再創建索引。這將在將它寫入磁盤前在內存中創建索引樹，并且它更快，因為避免大量磁盤尋道。結果索引樹也被完美地平衡。
執行FLUSH TABLES，或外殼命令mysqladmin flush-tables。
這個過程將被構造進在MySQL的某個未來版本的LOAD DATA INFILE。

你可以鎖定你的表以加速插入。
mysql> LOCK TABLES a WRITE;
mysql> INSERT INTO a VALUES (1,23),(2,34),(4,33);
mysql> INSERT INTO a VALUES (8,26),(6,29);
mysql> UNLOCK TABLES;

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

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前完成，但是整體時間應該快大約40%。因為INSERT, UPDATE和DELETE操作在MySQL中是很快的，通過為多于大約5次連續不斷地插入或更新一行的東西加鎖，你將獲得更好的整體性能。如果你做很多一行的插入，你可以做一個LOCK TABLES，偶爾隨后做一個UNLOCK TABLES(大約每1000行)以允許另外的線程存取表。這仍然將導致獲得好的性能。當然，LOAD DATA INFILE對裝載數據仍然是更快的。

為了對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 靜態(定長)表的特點
這是缺省格式。它用在表不包含VARCHAR、BLOB或TEXT列時候。
所有的CHAR、NUMERIC和DECIMAL列充填到列寬度。
非?？?。
容易緩沖。
容易在崩潰后重建，因為記錄位于固定的位置。
不必被重新組織(用myisamchk)，除非一個巨量的記錄被刪除并且你想要歸還空閑磁盤空間給操作系統。
通常比動態表需要更多的磁盤空間。
10.6.2 動態表的特點
如果表包含任何VARCHAR、BLOB或TEXT列，使用該格式。
所有字符串列是動態的(除了那些長度不到4的列)。
每個記錄前置一個位圖，對字符串列指出哪個列是空的('')，或對數字列哪個是零(這不同于包含NULL值的列)。如果字符串列在刪除尾部空白后有零長度，或數字列有零值，它在位圖中標記并且不保存到磁盤上。非空字符串存儲為一個長度字節加字符串內容。
通常比定長表占更多的磁盤空間。
每個記錄僅使用所需的空間。如果一個記錄變得更大，它按需要被切開多段，這導致記錄碎片。
如果你與超過行長度的信息更新行，行將被分段。在這種情況中，你可能必須時時運行myisamchk -r以使性能更好。使用myisamchk -ei tbl_name做一些統計。
在崩潰后不容易重建，因為一個記錄可以是分很多段并且一個連接(碎片)可以丟失。
對動態尺寸記錄的期望行長度是：
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個字節的懲罰。無論何時更改引起記錄的增大，一個動態記錄被鏈接。每個新鏈接將至少是20個字節，因此下一增大將可能在同一鏈連中。如果不是，將有另外一個鏈接。你可以用myisamchk -ed檢查有多少鏈接。所有的鏈接可以用 myisamchk -r 刪除。

10.6.3 壓縮表的特點
一張用myisampack實用程序制作的只讀表。所有具有MySQL擴展電子郵件支持的客戶可以為其內部使用保留一個myisampack拷貝。
解壓縮代碼存在于所有MySQL分發，以便甚至沒有myisampack的客戶能讀取用myisampack壓縮的表。
占據很小的磁盤空間，使磁盤使用量減到最小。
每個記錄被單獨壓縮(很小的存取開銷)。對一個記錄的頭是定長的(1-3 字節)，取決于表中最大的記錄。每列以不同方式被壓縮。一些壓縮類型是：
通常對每列有一張不同的哈夫曼表。
后綴空白壓縮。
前綴空白壓縮。
用值0的數字使用1位存儲。
如果整數列的值有一個小范圍，列使用最小的可能類型來存儲。例如，如果所有的值在0到255的范圍，一個BIGINT列(8個字節)可以作為一個TINYINT列(1字節)存儲。
如果列僅有可能值的一個小集合，列類型被變換到ENUM。
列可以使用上面的壓縮方法的組合。
能處理定長或動態長度的記錄，然而不能處理BLOB或TEXT列。
能用myisamchk解壓縮。
MySQL能支持不同的索引類型，但是一般的類型是ISAM。這是一個B樹索引并且你能粗略地為索引文件計算大小為(key_length+4)*0.67，在所有的鍵上的總和。（這是對最壞情況，當所有鍵以排序順序被插入時。）

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


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

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

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

堆表的缺點是：

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

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

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

MySQL基準程序和crash-me是獨立于數據庫的。通過觀察我們怎么處理它，你能得到一個感覺，你必須為編寫你的獨立于數據庫的應用程序做什么?；鶞时旧砜稍贛ySQL源代碼分發的“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語句并且插入結果?，F在我們很想使用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內核中解決。
向AI問一下細節
推薦閱讀：
  1. 十三、hadoop常用服務端口
  2. Asp.net Mvc實現表單驗證氣泡提示效果

免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。

------ --

猜你喜歡

最新資訊
相關推薦

相關標簽

----- apache nginx dba python3 java redis api php linux jquery mysql id b2b2c openstack innodb phpcmsv9 文件 kali ds
AI
助
手
產品服務
地區劃分
專題活動
幫助支持
關于我們
售后咨詢
7*24小時在線電話：400-100-2938
7*24小時在線 QQ：800811969
關注億速云

億速云公眾號

手機網站二維碼

Copyright ? Yisu Cloud Ltd. All Rights Reserved. 2018 版權所有

廣州億速云計算有限公司粵ICP備17096448號-1 粵公網安備 44010402001142號增值電信業務經營許可證編號：B1-20181529

亚洲午夜精品一区二区_中文无码日韩欧免_久久香蕉精品视频_欧美主播一区二区三区美女