实现有价值的IT服务
全部博文(709)
分类: Mysql/postgreSQL
2006-06-29 14:36:18
優化是一項複雜的任務,因為它最終需要對整個系統的理解。當用你的系統/應用的小知識做一些局部優化是可能的時候,你越想讓你的系統更優化,你必須知道它也越多。
因此,本章將試圖解釋並給出優化MySQL的不同方法的一些例子。但是記住總是有某些(逐漸變難)是系統更快的方法留著去做。
為了使一個系統更快的最重要部分當然是基本設計。你也需要知道你的系統將做這樣的事情,那就是你的瓶頸。
最常見的瓶頸是:
我們以系統級的東西開始,因為這些決策的某一些很早就做好了。在其他情況下,快速瀏覽這部分可能就夠了,因為它對大收獲並不重要,但是有一個關于在這個層次上收獲有多大的感覺總是好的。
使用的缺省OS確實重要!為了最大程度地使用多CPU,應該使用Solaris(因為線程工作得確實不錯)或Linux(因為2.2本的核心又確實不錯的SMP支持)。而且在32位的機器上,Linux缺省有2G的文件大小限制。當新的文件系統被釋出時( XFS ),希望這不久被修正。
因為我們沒在很多平台上運行生產MySQL,我們忠告你在可能選擇它前,測試你打算運行的平台。
其他建議:
--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
。 大多數下列測試在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庫。只有服務器對性能是關鍵的。
gcc
2.7.3是比Sun Pro C++ 4.2快13%。
由TcX提供的MySQL-Linux的分發用pgcc
編譯並靜態鏈接。
你可以從數據庫目錄移動表和數據庫到別處,並且用鏈接到新地點的符號代替它們。你可能想要這樣做,例如,轉移一個數據庫到有更多空閒空間的一個文件系統。
如果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))
你能用這個命令得到mysqld
服務器缺省緩衝區大小:
shell> mysqld --help
這個命令生成一張所有mysqld
選項和可配置變量的表。輸出包括缺省值並且看上去象這樣一些東西:
Possible variables for option --set-variable (-O) are: back_log current value: 5 connect_timeout current value: 5 delayed_insert_timeout current value: 300 delayed_insert_limit current value: 100 delayed_queue_size current value: 1000 flush_time current value: 0 interactive_timeout current value: 28800 join_buffer_size current value: 131072 key_buffer_size current value: 1048540 lower_case_table_names current value: 0 long_query_time current value: 10 max_allowed_packet current value: 1048576 max_connections current value: 100 max_connect_errors current value: 10 max_delayed_threads current value: 20 max_heap_table_size current value: 16777216 max_join_size current value: 4294967295 max_sort_length current value: 1024 max_tmp_tables current value: 32 max_write_lock_count current value: 4294967295 net_buffer_length current value: 16384 query_buffer_size current value: 0 record_buffer current value: 131072 sort_buffer current value: 2097116 table_cache current value: 64 thread_concurrency current value: 10 tmp_table_size current value: 1048576 thread_stack current value: 131072 wait_timeout current value: 28800
如果有一個mysqld
服務器正在運行,通過執行這個命令,你可以看到它實際上使用的變量的值:
shell> mysqladmin variables
每個選項在下面描述。對于緩衝區大小、長度和棧大小的值以字節給出,你能用于個後綴“K”或“M” 指出以K字節或兆字節顯示值。例如,16M
指出16兆字節。後綴字母的大小寫沒有關系;16M
和16m
是相同的。
你也可以用命令SHOW STATUS
自一個運行的服務器看見一些統計。見。
back_log
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
key_buffer_size
key_buffer_size
是用于索引塊的緩衝區大小,增加它可得到更好處理的索引(對所有讀和多重寫),到你能負擔得起那樣多。如果你使它太大,系統將開始換頁並且真的變慢了。記住既然MySQL不緩存讀取的數據,你將必須為OS文件系統緩存留下一些空間。為了在寫入多個行時得到更多的速度,使用LOCK TABLES
。見。
long_query_time
Slow_queries
記數器將被增加。
max_allowed_packet
net_buffer_length
字節,但是可在需要時增加到max_allowed_packet
個字節。缺省地,該值太小必能捕捉大的(可能錯誤)包。如果你正在使用大的BLOB
列,你必須增加該值。它應該象你想要使用的最大BLOB
的那麼大。
max_connections
mysqld
要求的文件描述符的數量。見下面對文件描述符限制的注釋。見。
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
max_allowed_packet
個字節。)
record_buffer
sort_buffer
ORDER BY
或GROUP BY
操作。見。
table_cache
mysqld
要求的文件描述符的數量。MySQL對每個唯一打開的表需要2個文件描述符,見下面對文件描述符限制的注釋。對于表緩存如何工作的信息,見。
tmp_table_size
The table tbl_name is full
形式的錯誤,如果你做很多高級GROUP BY
查詢,增加tmp_table_size
值。
thread_stack
crash-me
測試檢測到的許多限制依賴于該值。缺省隊一般的操作是足夠大了。見。
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
選項是最後一個;否則,命令行上在它之後列出的任何選項的效果將不在反映在輸出中。
table_cache
, max_connections
和max_tmp_tables
影響服務器保持打開的文件的最大數量。如果你增加這些值的一個或兩個,你可以遇到你的操作系統每個進程打開文件描述符的數量上強加的限制。然而,你可以能在許多系統上增加該限制。請教你的OS文檔找出如何做這些,因為改變限制的方法各系統有很大的不同。
table_cache
與max_connections
有關。例如,對于200個打開的連接,你應該讓一張表的緩衝至少有200 * n
,這里n
是一個聯結(join)中表的最大數量。
打開表的緩存可以增加到一個table_cache
的最大值(缺省為64;這可以用mysqld
的-O table_cache=#
選項來改變)。一個表絕對不被關閉,除非當緩存滿了並且另外一個線程試圖打開一個表時或如果你使用mysqladmin refresh
或mysqladmin flush-tables
。
當表緩存滿時,服務器使用下列過程找到一個緩存入口來使用:
對每個並發存取打開一個表。這意味著,如果你讓2個線程存取同一個表或在同一個查詢中存取表兩次(用AS
),表需要被打開兩次。任何表的第一次打開占2個文件描述符;表的每一次額外使用僅占一個文件描述符。對于第一次打開的額外描述符用于索引文件;這個描述符在所有線程之間共享。
如果你在一個目錄中有許多文件,打開、關閉和創建操作將會很慢。如果你執行在許多不同表上的SELECT
語句,當表緩存滿時,將有一點開銷,因為對每個必須打開的表,另外一個必須被關閉。你可以通過使表緩衝更大些來減少這個開銷。
當你運行mysqladmin status
時,你將看見象這樣的一些東西:
Uptime: 426 Running threads: 1 Questions: 11082 Reloads: 1 Open tables: 12
如果你僅有6個表,這可能有點令人困惑。
MySQL是多線程的,因此它可以同時在同一個表上有許多詢問。為了是2個線程在同一個文件上有不同狀態的問題減到最小,表由每個並發進程獨立地打開。這為數據文件消耗一些內存和一個額外的文件描述符。索引文件描述符在所有線程之間共享。
下表指出mysqld
服務器使用存儲器的一些方式。在應用的地方,給出與存儲器使用相關的服務器變量的名字。
key_buffer_size
)由所有線程分享;當需要時,分配服務器使用的其他緩衝區。見。
thread_stack
)、一個連接緩衝區(變量net_buffer_length
)和一個結果緩衝區(變量net_buffer_length
)。當需要時,連接緩衝區和結果緩衝區動態地被擴大到max_allowed_packet
。當一個查詢正在運行當前查詢的一個拷貝時,也分配字符串。
record_buffer
)。
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
選項增加臨時表的大小。見。在MySQL 3.20中,臨時表的最大尺寸是record_buffer*16
,因此如果你正在使用這個版本,你必須增加record_buffer
值。你也可以使用--big-tables
選項啟動mysqld
以總將臨時表存儲在磁盤上,然而,這將影響許多複雜查詢的速度。
malloc()
和free()
完成)。
3 * n
的一個緩衝區(這里n
是最大的行長度,不算BLOB
列)。一個BLOB
使用5 ~ 8個字節加上BLOB
數據。
BLOB
列的表,一個緩衝區動態地被擴大以便讀入更大的BLOB
值。如果你掃描一個表,分配與最大BLOB
值一樣大的一個緩衝區。
mysqladmin flush-tables
命令關閉所有不在用的表並在當前執行的線程結束時,標記所有在用的表准備被關閉。這將有效地釋放大多數在用的內存。 ps
和其他系統狀態程序可以報導mysqld
使用很多內存。這可以是在不同的內存地址上的線程棧造成的。例如,Solaris版本的ps
將棧間未用的內存算作已用的內存。你可以通過用swap -s
檢查可用交換區來驗証它。我們用商業內存漏洞探查器測試了mysqld
,因此應該有沒有內存漏洞。
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
。見。
你也能改變在“mysys/thr_lock.c”中的鎖代碼以使用一個單個隊列。在這種情況下,寫鎖定和讀鎖定將有同樣優先級,它可能幫助一些應用程序。
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
語句較低優先級。
mysqld
使得在一定數量的WRITE
鎖定後給出READ
鎖定。
SET SQL_LOW_PRIORITY_UPDATES=1
,你可從一個特定線程指定所有的更改應該由用低優先級完成。見。
HIGH_PRIORITY
屬性指明一個特定SELECT
是很重要的。見。
INSERT
結合SELECT
的問題,切換到使用新的MyISAM
表,因為它們支持並發的SELECT
和INSERT
。
INSERT
和SELECT
語句,DELAYED
屬性的INSERT
將可能解決你的問題。見。
SELECT
和DELETE
的問題,LIMIT
選項的DELETE
可以幫助你。見。 最基本的優化之一是使你的數據(和索引)在磁盤上(並且在內存中)占據的空間盡可能小。這能給出巨大的改進,因為磁盤讀入較快並且通常也用較少的主存儲器。如果在更小的列上做索引,索引也占據較少的資源。
你能用下面的技術使表的性能更好並且使存儲空間最小:
MEDIUMINT
經常比INT
好一些。
NOT NULL
。它使任何事情更快而且你為每列節省一位。注意如果在你的應用程序中你確實需要NULL
,你應該毫無疑問使用它,只是避免缺省地在所有列上有它。
VARCHAR
、TEXT
或BLOB
列),使用固定尺寸的記錄格式。這比較快但是不幸地可能會浪費一些空間。見。
索引被用來快速找出在一個列上用一特定值的行。沒有索引,MySQL不得不首先以第一條記錄開始並然後讀完整個表直到它找出相關的行。表越大,花費時間越多。如果表對于查詢的列有一個索引,MySQL能快速到達一個位置去搜尋到數據文件的中間,沒有必要考慮所有數據。如果一個表有1000行,這比順序讀取至少快100倍。注意你需要存取幾乎所有1000行,它較快的順序讀取,因為此時我們避免磁盤尋道。
所有的MySQL索引(PRIMARY
、UNIQUE
和INDEX
)在B樹中存儲。字符串是自動地壓縮前綴和結尾空間。見。
索引用于:
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 */
首先,一件事情影響所有的詢問。你有的許可系統設置越複雜,你得到更多的開銷。
如果你不讓任何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)
是找出是否你的查詢有問題的一個極好工具。
在大多數情況下,你能通過計算磁盤尋道估計性能。對小的表,你通常能在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增加)。為了避免這個增加,索引緩衝隨數據增加而增加。見。
總的來說,當你想要使一個較慢的SELECT ... WHERE
更快,檢查的第一件事情是你是否能增加一個索引。見。在不同表之間的所有引用通常應該用索引完成。你可以使用EXPLAIN
來確定哪個索引用于一條SELECT
語句。見。
一些一般的建議:
myisamchk --analyze
。這為每一個更新一個值,指出有相同值地平均行數(當然,對唯一索引,這總是1。)
myisamchk --sort-index --sort-records=1
(如果你想要在索引1上排序)。如果你有一個唯一索引,你想要根據該索引地次序讀取所有的記錄,這是使它更快的一個好方法。然而注意,這個排序沒有被最佳地編寫,並且對一個大表將花很長時間! 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
表達式也這樣做。
SELECT
語句是不可能的並且不返回行。
GROUP BY
或分組函數(COUNT()
、MIN()
……),HAVING
與WHERE
合並。
WHERE
以得到一個更快的WHERE
計算並且也盡快跳過記錄。
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;
ORDER BY
和GROUP BY
的列來自同一個表,那麼當廉潔時,該表首先被選中。
ORDER BY
子句和一個不同的GROUP BY
子句,或如果ORDER BY
或GROUP BY
包含不是來自聯結隊列中的第一個表的其他表的列,創建一個臨時表。
SQL_SMALL_RESULT
,MySQL將使用一個在內存中的表。
DISTINCT
被變換到在所有的列上的一個GROUP BY
,DISTINCT
與ORDER BY
結合也將在許多情況下需要一張臨時表。
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,...
在MySQL中,A LEFT JOIN B
實現如下:
B
被設置為依賴于表A
。
A
被設置為依賴于所有用在LEFT JOIN
條件的表(除B
外)。
LEFT JOIN
條件被移到WHERE
子句中。
WHERE
優化。
A
中有一行匹配WHERE
子句,但是在B
中沒有任何行匹配LEFT JOIN
條件,那麼在B
中生成所有列設置為NULL
的一行。
LEFT JOIN
來找出在某些表中不存在的行並且在WHERE
部分你有下列測試:column_name IS NULL
,這里column_name 被聲明為NOT NULL
的列,那麼MySQL
在它已經找到了匹配LEFT JOIN
條件的一行後,將停止在更多的行後尋找(對一特定的鍵組合)。 在一些情況中,當你使用LIMIT #
而不使用HAVING
時,MySQL將以不同方式處理查詢。
LIMIT
只選擇一些行,當MySQL一般比較喜歡做完整的表掃描時,它將在一些情況下使用索引。
LIMIT #
與ORDER BY
,MySQL一旦找到了第一個 #
行,將結束排序而不是排序整個表。
LIMIT #
和DISTINCT
時,MySQL一旦找到#
個唯一的行,它將停止。
GROUP BY
能通過順序讀取鍵(或在鍵上做排序)來解決,並然後計算摘要直到鍵值改變。在這種情況下,LIMIT #
將不計算任何不必要的GROUP
。
MySQL
已經發送了第一個#
行到客戶,它將放棄查詢。
LIMIT 0
將總是快速返回一個空集合。這對檢查查詢並且得到結果列的列類型是有用的。
LIMIT #
計算需要多少空間來解決查詢。 插入一個記錄的時間由下列組成:
這里的數字有點與總體時間成正比。這不考慮打開表的初始開銷(它為每個並發運行的查詢做一次)。
表的大小以N log N (B 樹)的速度減慢索引的插入。
加快插入的一些方法:
INSERT
語句。這比使用分開INSERT
語句快(在一些情況中幾倍)。
INSERT DELAYED
語句得到更高的速度。見。
MyISAM
,如果在表中沒有刪除的行,能在SELECT
:s正在運行的同時插入行。
LOAD DATA INFILE
。這通常比使用很多INSERT
語句快20倍。見。
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
。見。
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
得到一些更快的速度,擴大關鍵字緩衝區。見。
更改查詢被優化為有一個寫開銷的一個SELECT
查詢。寫速度依賴于被更新數據大小和被更新索引的數量。
使更改更快的另一個方法是推遲更改並且然後一行一行地做很多更改。如果你鎖定表,做一行一行地很多更改比一次做一個快。
注意,動態記錄格式的更改一個較長總長的記錄,可能切開記錄。因此如果你經常這樣做,時不時地OPTIMIZE TABLE
是非常重要的。見。
刪除一個記錄的時間精確地與索引數量成正比。為了更快速地刪除記錄,你可以增加索引緩存的大小。見。
從一個表刪除所有行比刪除行的一大部分也要得多。
用MySQL,當前(版本 3.23.5)你能從一個速度觀點在4可用表的格式之間選擇。
myisamchk
能很容易指出每行在哪兒開始和結束,因此它通常能回收所有記錄,除了部分被寫入的那個。注意,在MySQL中,所有索引總能被重建。
OPTIMIZE table
或myisamchk
整理一張表。如果你在同一個表中有象某些VARCHAR
或BLOB
列那樣存取/改變的靜態數據,將動態列移入另外一個表以避免碎片可能是一個好主意。
myisampack
工具生成。
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;
VARCHAR
、BLOB
或TEXT
列時候。
CHAR
、NUMERIC
和DECIMAL
列充填到列寬度。
myisamchk
),除非一個巨量的記錄被刪除並且你想要歸還空閒磁盤空間給操作系統。
VARCHAR
、BLOB
或TEXT
列,使用該格式。
''
),或對數字列哪個是零(這不同于包含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
刪除。
myisampack
實用程序制作的只讀表。所有具有MySQL擴展電子郵件支持的客戶可以為其內部使用保留一個myisampack
拷貝。
myisampack
的客戶能讀取用myisampack
壓縮的表。
0
的數字使用1位存儲。
0
到255
的範圍,一個BIGINT
列(8個字節)可以作為一個TINYINT
列(1字節)存儲。
ENUM
。
BLOB
或TEXT
列。
myisamchk
解壓縮。 MySQL能支持不同的索引類型,但是一般的類型是ISAM。這是一個B樹索引並且你能粗略地為索引文件計算大小為(key_length+4)*0.67
,在所有的鍵上的總和。(這是對最壞情況,當所有鍵以排序順序被插入時。)
字符串索引是空白壓縮的。如果第一個索引部分是一個字符串,它也將壓縮前綴。如果字符串列有很多尾部空白或是一個總不能用到全長的VARCHAR
列,空白壓縮使索引文件更小。如果很多字符串有相同的前綴,前綴壓縮是有幫助的。
堆桌子僅存在于內存中,因此如果mysqld
被關掉或崩潰,它們將丟失,但是因為它們是很快,不管怎樣它們是有用的。
MySQL內部的HEAP表使用沒有溢出區的100%動態哈希並且沒有與刪除有關的問題。
你只能通過使用在堆表中的一個索引的用等式存取東西(通常用=
操作符)。
堆表的缺點是:
ORDER BY
)。
對加快系統的未分類的建議是:
EXPLAIN
命令做到。見。
SELECT
查詢。這避免與鎖定表有關的問題。
SELECT * from table where hash='calculated hash on col1 and col2' and col_1='constant' and col_2='constant' and ..
。
VARCHAR
或BLOB
列。只要你使用單個VARCHAR
或BLOB
列,你將得到動態行長度。見。
UPDATE table set count=count+1 where index_column=constant
是很快的!當你使用象MySQL那樣的只有表級鎖定(多重讀/單個寫)的數據庫時,這確實重要。這也將給出大多數數據庫較好的性能,因為鎖定管理器在這種情況下有較少的事情要做。 11111111111111111111111
INSERT /*! DELAYED */
。這加快處理,因為很多記錄可以用一次磁盤寫入被寫入。
INSERT /*! LOW_PRIORITY */
。
SELECT /*! HIGH_PRIORITY */
來取得塞入隊列的選擇,它是即使有人等待做一個寫入也要完成的選擇。
INSERT
語句來存儲很多有一條SQL命令的行(許多SQL服務器支持它)。
LOAD DATA INFILE
裝載較大數量的數據。這比一般的插入快並且當myisamchk
集成在mysqld
中時,甚至將更快。
AUTO_INCREMENT
列構成唯一值。
OPTIMIZE TABLE
以避免碎片。見。
HEAP
表以得到更快的速度。見。
name
而不是customer_name
)。為了使你的名字能移植到其他SQL服務器,你應該使他們短于18 個字符。
MyISAM
,比起使用SQL 接口,你能得到2-5倍的速度提升。然而為了能做到它,數據必須是在與應用程序性在同一台機器的服務器上,並且通常它只應該被一個進程存取(因為外部文件鎖定確實很慢)。通過在MySQL服務器中引進底層MyISAM
命令能消除以上問題(如果需要,這可能是獲得更好性能的一個容易的方法)。借助精心設計的數據庫接口,應該相當容易支持這類優化。
你決定應該測試你的應用程序和數據庫,以發現瓶頸在哪兒。通過修正它(或通過用一個“啞模塊”代替瓶頸),你能容易確定下一個瓶頸(等等)。即使對你的應用程序來說,整體性能“足夠好”,你至少應該對每個瓶頸做一個“計劃”,如果某人“確實需要修正它”,如何解決它。
對于一些可移植的基准程序的例子,參見MySQL基准套件。見。你能利用這個套件的任何程序並且為你的需要修改它。通過這樣做,你能嘗試不同的你的問題的解決方案並測試哪一個對你是最快的解決方案。
在系統負載繁重時發生一些問題是很普遍的,並且我們有很多與我們聯系的客戶,他們在生產系統中有一個(測試)系統並且有負載問題。到目前為止,被一種這些的情況是與基本設計有關的問題(表掃描在高負載時表現不好)或OS/庫問題。如果系統已經不在生產系統中,它們大多數將很容易修正。
為了避免這樣的問題,你應該把一些力氣放在在可能最壞的負載下測試你的整個應用!
MySQL在分開的文件中存儲行數據和索引數據。許多(幾乎全部)其他數據庫在同一個文件中混合行和索引數據。我們相信,MySQL的選擇對非常廣泛的現代系統的來說是較好的。
存儲行數據的另一個方法是在一個分開的區域保存每列信息(例子是SDBM和Focus)。這將對每個存取多于一列的查詢獲得一個性能突破。因為在多于一列被存取時,這快速退化,我們相信這個模型對通用功能的數據庫不是最好。
更常見的情形是索引和數據一起被存儲(就象Oracle/Sybase)。在這種情況中,你將在索引的葉子頁面上找到行信息。有這布局的好處是它在許多情況下(取決于這個索引被緩衝得怎樣)節省一次磁盤讀。有這布局的壞處是:
因為MySQL使用極快的表鎖定(多次讀/一次寫),留下的最大問題是在同一個表中的一個插入的穩定數據流與慢速選擇的一個混合。
我們相信,在其他情況下,對大多數系統,異常快速的性能使它成為一個贏家。這種情形通常也可能通過表的多個副本來解決,但是它要花更多的力氣和硬件。
對一些常見的應用環境,我們也在開發一些擴展功能以解決此問題。
因為所有SQL服務器實現了SQL的不同部分,要花功夫編寫可移植的SQL應用程序。對很簡單的選擇/插入,它是很容易的,但是你需要越多,它越困難,而且如果你想要應用程序對很多數據庫都快,它變得更難!
為了使一個複雜應用程序可移植,你需要選擇它應該與之工作的很多SQL服務器。
當你能使用MySQL的crash-me 程序()來找出你能與之使用的數據庫服務器的選擇的函數、類型和限制。crash-me現在對任何可能的事情測試了很長時間,但是它仍然理解測試過的大約450件事情。
例如,如果你想要能使用Informix 或 DB2,你不應該有比18個字符更長的列名。
MySQL基准程序和crash-me是獨立于數據庫的。通過觀察我們怎麼處理它,你能得到一個感覺,你必須為編寫你的獨立于數據庫的應用程序做什麼。基准本身可在MySQL源代碼分發的“sql-bench”目錄下找到。他們用DBI數據庫接口以Perl寫成(它解決問題的存取部分)。
到看這個基准的結果。
正如你可在這些結果看見的那樣,所有數據庫都有一些弱點。這就是他們不同的設計折衷導致的不同行為。
如果你為數據庫的獨立性而努力,你需要獲得每個SQL服務器瓶頸的良好感受。MySQL在檢索和更新方面很快,但是在同一個表上混合讀者/寫者方面將有一個問題。在另一方面,當你試圖存取你最近更新了的行時,Oracle有一個很大問題(直到他們被清空到磁盤上)。事務數據庫總的來說在從記錄文件表中生成總結表不是很好,因為在這種情況下,行級鎖定幾乎沒用處。
為了使你的應用程序“確實獨立于數據庫”,你需要定義一個容易的可擴展的接口,用它你可操縱你的數據。因為C++在大多數系統上可以得到的,使用數據庫的一個C++ 類接口是有意義的。
如果你使用一些某個數據庫特定的功能(在MySQL中,象REPLACE
命令),你應該為SQL服務器編碼一個方法以實現同樣的功能 (但是慢些)。用MySQL,你能使用/*! */
語法把MySQL特定的關鍵詞加到查詢中。在/**/
中的代碼將被大多數其他SQL服務器視為一篇注釋(被忽略)。
如果高性能真的比准確性更重要,就像在一些web應用程序那樣。一種可能性是創建一個應用層,緩衝所有的結果以給你更高的性能。通過只是讓老的結果在短時間後‘過期’,你能保持緩存合理地刷新。這在極高負載的情況下是相當不錯的,在此情況下,你能動態地增加緩存到更大並且設定較高的過期時限直到一切回到正常。
在這種情況下,創建信息的表應該包含緩存初始大小和表一般應該被刷新幾次的信息。
在MySQL起初開發期間,MySQL的功能適合我們的最大客戶。他們為在瑞典的一些最大的零售商處理數據倉庫。
我們從所有商店得到所有紅利卡交易的每周總結並且我們被期望為所有店主提供有用的信息以幫助他們得出他們的廣告戰如何影響他們的顧客。
數據是相當巨量的(大約每月7百萬宗交易總結)並且我們保存4-10年來的數據需要呈現給用戶。我們每周顧客那里得到請求,他們想要“立刻”訪問來自該數據的新報告。
我們通過每月將所有信息存儲在壓縮的“交易”表中來解決它。我們有一套簡單的宏/腳本用來生成來自交易表的不同條件( 產品組,顧客id,商店...)的總結表。報告是由一個進行語法分析網頁的小perl腳本動態生成的網頁,在腳本中執行SQL語句並且插入結果。現在我們很想使用PHP或mod_perl,但是那時他們沒有。
對圖形數據,我們用C語言編寫了一個簡單的工具,它能基于SQL查詢的結果(對結果的一些處理)產生贈品,這也從分析HTML
文件的perl腳本中動態地執行。
在大多數情況下,一個新的報告通過簡單地複制一個現有腳本並且修改其中的SQL查詢來完成。在一些情況下,我們將需要把更多的字段加到一個現有的總結表中或產生一個新的,但是這也相當簡單,因為我們在磁盤上保存所有交易表。(目前我們有至少50G的交易表和200G的其他顧客數據)。
我們也讓我們的顧客直接用ODBC存取總結表以便高級用戶能自己試驗數據。
我們用非常中檔的Sun Ultra sparcstation ( 2x200 Mz )來處理,沒有任何問題。最近我們升級了服務器之一到一台2個CPU 400 Mz的Ultra sparc,並且我們現在計劃處理產品級的交易,這將意味著數據增加10番。我們認為我們能通過只是為我們的系統增加更多的磁盤就能趕上它。
我們也在試驗Intel-Linux以便能更便宜地得到更多的cpu動力。既然我們有二進制可移植的數據庫格式(在3.32中引入),我們將開始在應用程序的某些部分使用它。
我們最初的感覺是Linux在低到中等負載時執行的較好,但是你開始得到導致的高負載時,Solaris將表現更好,因為磁盤IO的極限,但是我們還沒有關于這方面的任何結論。在與一些Linux核心開發者的討論以後,這可能是 Linux 的副作用,它給批處理以太多的資源使得交互的性能變得很低。當大的批處理正在進行時,這使機器感覺很慢且無反應。希望這將在未來的Linux內核中解決。