MySQL的最佳化

日期:2008-06-20  作者:喜騰小二  來源:PHPChina


一、我們可以且應該最佳化什麼?

硬體

操作係統/軟體庫

SQL伺服器(設定和查詢)

應用編程介麵(API)

應用程式

--------------------------------------------------------------------------------

二、最佳化硬體

如果妳需要龐大的資料庫表(>2G),妳應該考慮使用64位元的硬體結構,像Alpha、Sparc或即將推出的IA64。因為MySQL內部使用大量64位元的整數,64位元的CPU將提供更好的效能。

對大資料庫,最佳化的次序一般是RAM、快速硬碟、CPU能力。

更多的記憶體透過將最常用的鍵碼页面存放在記憶體中可以加速鍵碼的更新。

如果不使用事務安全(transaction-safe)的表或有大表並且想避免長檔案檢查,一臺UPS就能夠在電源故障時讓係統安全關閉。

對於資料庫存放在一個私用伺服器的係統,應該考慮1G的乙太網路。延遲與吞吐量同樣重要。

--------------------------------------------------------------------------------

三、最佳化磁碟

為係統、程式和暫時檔案配備一個私用磁碟,如果確是進行很多修改工作,將更新日誌和事務日誌放在私用磁碟上。
低尋道時間對資料庫磁碟非常重要。對與大表,妳可以估計妳將需要log(行數)/log(索引塊長度/3*2/(鍵碼長度 + 資料指針長度))+1次尋到才能找到一行。對於有500000行的表,索引Mediun int類型的列,需要log(500000) / log(1024/3*2/(3 + 2))+1=4次尋道。上述索引需要500000*7*3/2=5.2M的空間。實際上,大多數塊將被快取,所以大概隻需要1-2次尋道。
然而對於寫入(如上),妳將需要4次尋道請求來找到在哪裡存放新鍵碼,而且一般要2次尋道來更新索引並寫入一行。
對於非常大的資料庫,妳的應用將受到磁碟尋道速度的限制,隨着資料量的增加呈N log N資料級遞增。
將資料庫和表分在不同的磁碟上。在MySQL中,妳可以為此而使用符號連結。
條列磁碟(RAID 0)將提高讀和寫的吞吐量。
帶鏡像的條列(RAID 0+1)將更安全並提高讀取的吞吐量。寫入的吞吐量將有所降低。
不要對暫時檔案或可以很容易地重建的資料所在的磁碟使用鏡像或RAID(除了RAID 0)。
在Linux上,在引導時對磁碟使用指令hdparm -m16 -d1以啓用同時讀寫多個扇區和DMA功能。這可以將回應時間提高5~50%。
在Linux上,用async (預設)和noatime掛載磁碟(mount)。
對於某些特定應用,可以對某些特定表使用記憶體磁碟,但通常不需要。

--------------------------------------------------------------------------------

四、最佳化操作係統

不要交換區。如果記憶體不足,增加更多的記憶體或配置妳的係統使用較少記憶體。
不要使用NFS磁碟(會有NFS鎖定的問題)。
增加係統和MySQL伺服器的開啟檔案數量。(在safe_mysqld指令檔中加入ulimit -n #)。
增加係統的處理序和執行緒數量。
如果妳有相對較少的大表,告訴檔案係統不要將檔案打碎在不同的磁軌上(Solaris)。
使用支援大檔案的檔案係統(Solaris)。
選擇使用哪種檔案係統。在Linux上的Reiserfs對於開啟、讀寫都非常快。檔案檢查隻需幾秒種。

--------------------------------------------------------------------------------

五、選擇應用編程介麵

PERL
可在不同的操作係統和資料庫之間移植。
適宜快速原型。
應該使用DBI/DBD介麵。
PHP
比PERL易學。
使用比PERL少的資源。
透過升級到PHP4可以獲得更快的速度。
C
MySQL的原生介麵。
較快並賦予更多的控制。
低層,所以必須付出更多。
C++
較高層次,給妳更多的時間來編寫應用。
仍在開發中
ODBC
執行在Windows和Unix上。
幾乎可在不同的SQL伺服器間移植。
較慢。MyODBC隻是簡單的直通驅動程式,比用原生介麵慢19%。
有很多方法做同樣的事。很難像很多ODBC驅動程式那樣執行,在不同的領域還有不同的錯誤。
問題成堆。Microsoft偶爾還會改變介麵。
不明朗的未來。(Microsoft更推崇OLE而非ODBC)
ODBC
執行在Windows和Unix上。
幾乎可在不同的SQL伺服器間移植。
較慢。MyODBC隻是簡單的直通驅動程式,比用原生介麵慢19%。
有很多方法做同樣的事。很難像很多ODBC驅動程式那樣執行,在不同的領域還有不同的錯誤。
問題成堆。Microsoft偶爾還會改變介麵。
不明朗的未來。(Microsoft更推崇OLE而非ODBC)
JDBC
理論上可在不同的操作係統何時據庫間移植。
可以執行在web用戶端。
Python和其他
可能不錯,可我們不用它們。

--------------------------------------------------------------------------------

六、最佳化應用

應該集中精力解決問題。
在編寫應用時,應該決定什麼是最重要的:
速度
操作係統間的可移植性
SQL伺服器間的可移植性
使用持續的連線。.
快取應用中的資料以減少SQL伺服器的負載。
不要查詢應用中不需要的列。
不要使用SELECT * FROM table_name...
測試應用的所有部分,但將大部分精力放在在可能最壞的合理的負載下的測試整體應用。透過以一種模組化的方式進行,妳應該能用一個快速“啞模組”替代找到的瓶頸,然後很容易地標出下一個瓶頸。
如果在一個批次中進行大量修改,使用LOCK TABLES。例如將多個UPDATES或DELETES集中在一起。

--------------------------------------------------------------------------------

七、應該使用可移植的應用

Perl DBI/DBD
ODBC
JDBC
Python(或其他有普遍SQL介麵的語言)
妳應該隻使用存在於所有目的SQL伺服器中或可以很容易地用其他構造模擬的SQL構造。www.mysql.com上的Crash-me頁可以說明妳。
為操作係統/SQL伺服器編寫包裝程式來提供缺少的功能。

--------------------------------------------------------------------------------

八、如果妳需要更快的速度,妳應該:

找出瓶頸(CPU、磁碟、記憶體、SQL伺服器、操作係統、API或應用)並集中全力解決。
使用給予妳更快速度/靈活性的延伸。
逐漸瞭解SQL伺服器以便能為妳的問題使用可能最快的SQL構造並避免瓶頸。
最佳化表佈局和查詢。
使用複製以獲得更快的選擇(select)速度。
如果妳有一個慢速的網路連線資料庫,使用壓縮客戶/伺服器協定。
不要害怕時應用的第一個版本不能完美地移植,在妳解決問題時,妳總是可以在以後最佳化它。

--------------------------------------------------------------------------------

九、最佳化MySQL

挑選編譯器和編譯選項。
位妳的係統尋找最好的啓動選項。
通讀MySQL參考手冊並閱讀Paul DuBios的《MySQL》一書。(已有中文版-譯注)
多用EXPLAIN SELECT、SHOW VARIABLES、SHOW STATUS和SHOW PROCESSLIST。
瞭解查詢最佳化器的工作原理。
最佳化表的格式。
維護妳的表(myisamchk、CHECK TABLE、 OPTIMIZE TABLE)
使用MySQL的延伸功能以讓一切快速完成。
如果妳注意到了妳將在很多場合需要某些函式,編寫MySQL UDF函式。
不要使用表級或列級的GRANT,除非妳確實需要。
購買MySQL技術支援以說明妳解決問題:)

--------------------------------------------------------------------------------

十、編譯和安裝MySQL

透過位妳的係統挑選可能最好的編譯器,妳通常可以獲得10-30%的效能提高。
在Linux/Intel平臺上,用pgcc(gcc的奔騰晶元最佳化版)編譯MySQL。然而,二進位程式碼將隻能執行在Intel奔騰CPU上。
對於一種特定的平臺,使用MySQL參考手冊上推薦的最佳化選項。
一般地,對特定CPU的原生編譯器(如Sparc的Sun Workshop)應該比gcc提供更好的效能,但不總是這樣。
用妳將使用的字元集編譯MySQL。
靜態編譯生成mysqld的執行檔案(用--with-mysqld-ldflags=all-static)並用strip sql/mysqld整理最終的執行檔案。
注意,既然MySQL不使用C++延伸,不帶延伸支援編譯MySQL將贏得巨大的效能提高。
如果操作係統支援原生執行緒,使用原生執行緒(而不用mit-pthreads)。
用MySQL基準測試來測試最終的二進位程式碼。

--------------------------------------------------------------------------------

十一、維護

如果可能,偶爾執行一下OPTIMIZE table,這對大量更新的變長行非常重要。
偶爾用myisamchk -a更新一下表中的鍵碼分佈統計。記住在做之前關掉MySQL。
如果有碎片檔案,可能值得將所有檔案複製到另一個磁碟上,清除原來的磁碟並拷回檔案。
如果遇到問題,用myisamchk或CHECK table檢查表。
用mysqladmin -i10 precesslist extended-status監控MySQL的狀態。
用MySQL GUI客戶程式,妳可以在不同的視窗內監控處理序清單和狀態。
使用mysqladmin debug獲得有關鎖定和效能的資訊。

--------------------------------------------------------------------------------

十二、最佳化SQL

揚SQL之長,其它事情交由應用去做。使用SQL伺服器來做:

找出基於WHERE子句的行。
JOIN表
GROUP BY
ORDER BY
DISTINCT
不要使用SQL來做:

檢驗資料(如日期)
成為一隻計算器
技巧:

明智地使用鍵碼。
鍵碼適合搜尋,但不適合索引列的插入/更新。
保持資料為資料庫第三範式,但不要擔心冗餘資訊或這如果妳需要更快的速度,建立總結表。
在大表上不做GROUP BY,相反建立大表的總結表並查詢它。
UPDATE table set count=count+1 where key_column=constant非常快。
對於大表,或許最好偶爾生成總結表而不是一直保持總結表。
充分利用INSERT的預設值。

--------------------------------------------------------------------------------

十三、不同SQL伺服器的速度差別(以秒計)

 

透過鍵碼讀取2000000行: NT Linux
mysql 367 249
mysql_odbc 464  
db2_odbc 1206  
informix_odbc 121126  
ms-sql_odbc 1634  
oracle_odbc 20800  
solid_odbc 877  
sybase_odbc 17614  
 
插入350768行: NT Linux
mysql 381 206
mysql_odbc 619  
db2_odbc 3460  
informix_odbc 2692  
ms-sql_odbc 4012  
oracle_odbc 11291  
solid_odbc 1801  
sybase_odbc 4802  

在上述測試中,MySQL配置8M高速快取執行,其他資料庫以預設安裝執行。

--------------------------------------------------------------------------------

十四、重要的MySQL啓動選項

back_log 如果需要大量新連線,修改它。
thread_cache_size 如果需要大量新連線,修改它。
key_buffer_size 索引頁池,可以設成很大。
bdb_cache_size BDB表使用的記錄和鍵嗎高速快取。
table_cache 如果有很多的表和併發連線,修改它。
delay_key_write 如果需要快取所有鍵碼寫入,設定它。
log_slow_queries 找出需花大量時間的查詢。
max_heap_table_size 用於GROUP BY
sort_buffer 用於ORDER BY和GROUP BY
myisam_sort_buffer_size 用於REPAIR TABLE
join_buffer_size 在進行無鍵嗎的聯結時使用。

--------------------------------------------------------------------------------

十五、最佳化表

MySQL擁有一套豐富的類型。妳應該對每一列嘗試使用最有效的類型。
ANALYSE過程可以說明妳找到表的最優類型:SELECT * FROM table_name PROCEDURE ANALYSE()。
對於不儲存NULL值的列使用NOT NULL,這對妳想索引的列尤其重要。
將ISAM類型的表改為MyISAM。
如果可能,用固定的表格式建立表。
不要索引妳不想用的東西。
利用MySQL能按一個索引的首碼進行查詢的事實。如果妳有索引INDEX(a,b),妳不需要在a上的索引。
不在長CHAR/VARCHAR列上建立索引,而隻索引列的一個首碼以節省存儲空間。CREATE TABLE table_name (hostname CHAR(255) not null, index(hostname(10)))
對每個表使用最有效的表格式。
在不同表中儲存相同資訊的列應該有同樣的定義並俱有相同的列名。

--------------------------------------------------------------------------------

十六、MySQL如何次存儲資料

資料庫以目錄存儲。
表以檔案存儲。
列以變長或定長格式存儲在檔案中。對BDB表,資料以页面形式存儲。
支援基於記憶體的表。
資料庫和表可在不同的磁碟上用符號連線起來。
在Windows上,MySQL支援用.sym檔案內部符號連線資料庫。

--------------------------------------------------------------------------------

十七、MySQL表類型

HEAP表:固定行長的表,隻存儲在記憶體中並用HASH索引進行索引。
ISAM表:MySQL 3.22中的早期B-tree表格式。
MyIASM:IASM表的新版本,有如下延伸:
二進位層次的可移植性。
NULL列索引。
對變長行比ISAM表有更少的碎片。
支援大檔案。
更好的索引壓縮。
更好的鍵嗎統計分佈。
更好和更快的auto_increment處理。
來自Sleepcat的Berkeley DB(BDB)表:事務安全(有BEGIN WORK/COMMIT|ROLLBACK)。

--------------------------------------------------------------------------------

十八、MySQL行類型(專指IASM/MyIASM表)

如果所有列是定長格式(沒有VARCHAR、BLOB或TEXT),MySQL將以定長表格式建立表,否則表以動態長度格式建立。
定長格式比動態長度格式快很多並更安全。
動態長度行格式一般占用較少的存儲空間,但如果表頻繁更新,會產生碎片。
在某些情況下,不值得將所有VARCHAR、BLOB和TEXT列轉移到另一個表中,隻是獲得主表上的更快速度。
利用myiasmchk(對ISAM,pack_iasm),可以建立唯讀壓縮表,這使磁碟使用率最小,但使用慢速磁碟時,這非常不錯。壓縮表充分地利用將不再更新的日誌表

--------------------------------------------------------------------------------

十九、MySQL高速快取(所有執行緒共用,一次性分配)

鍵碼快取:key_buffer_size,預設8M。
表快取:table_cache,預設64。
執行緒快取:thread_cache_size,預設0。
主機名快取:可在編譯時修改,預設128。
記憶體對應表:目前僅用於壓縮表。
注意:MySQL沒有行高速快取,而讓操作係統處理。

--------------------------------------------------------------------------------

二十、MySQL快取區變數(非共用,按需分配)

sort_buffer:ORDER BY/GROUP BY
record_buffer:掃瞄表。
join_buffer_size:無鍵聯結
myisam_sort_buffer_size:REPAIR TABLE
net_buffer_length:對於讀SQL陳述式並快取結果。
tmp_table_size:暫時結果的HEAP表大小。
 

--------------------------------------------------------------------------------

二十一、MySQL表高速快取工作原理

每個MyISAM表的開啟實例(instance)使用一個索引檔案和一個資料檔案。如果表被兩個執行緒使用或在同一條查詢中使用兩次,MyIASM將共用索引檔案而是開啟資料檔案的另一個實例。
如果所有在高速快取中的表都在使用,快取將暫時增加到比表快取尺寸大些。如果是這樣,下一個被釋放的表將被關閉。
妳可以透過檢查mysqld的Opened_tables變數以檢查表快取是否太小。如果該值太高,妳應該增大表高速快取。
 

--------------------------------------------------------------------------------

二十二、MySQL延伸/最佳化-提供更快的速度

使用最佳化的表類型(HEAP、MyIASM或BDB表)。
對資料使用最佳化的列。
如果可能使用定長行。
使用不同的鎖定類型(SELECT HIGH_PRIORITY,INSERT LOW_PRIORITY)
Auto_increment
REPLACE (REPLACE INTO table_name VALUES (...))
INSERT DELAYED
LOAD DATA INFILE / LOAD_FILE()
使用多行INSERT一次插入多行。
SELECT INTO OUTFILE
LEFT JOIN, STRAIGHT JOIN
LEFT JOIN ,結合IS NULL
ORDER BY可在某些情況下使用鍵碼。
如果隻查詢在一個索引中的列,將隻使用索引樹解決查詢。
聯結一般比子查詢快(對大多數SQL伺服器亦如此)。
LIMIT
SELECT * from table1 WHERE a > 10 LIMIT 10,20
DELETE * from table1 WHERE a > 10 LIMIT 10
foo IN (常數清單) 高度最佳化。
GET_LOCK()/RELEASE_LOCK()
LOCK TABLES
INSERT和SELECT可同時執行。
UDF函式可裝載進一個正在執行的伺服器。
壓縮唯讀表。
CREATE TEMPORARY TABLE
CREATE TABLE .. SELECT
帶RAID選項的MyIASM表將檔案分割成很多檔案以突破某些檔案係統的2G限制。
Delay_keys
複製功能

--------------------------------------------------------------------------------

二十二、MySQL何時使用索引

對一個鍵碼使用>, >=, =, 1 and key_part1 explain select t3.DateOfAction, t1.TransactionID
-> from t1 join t2 join t3
-> where t2.ID = t1.TransactionID and t3.ID = t2.GroupID
-> order by t3.DateOfAction, t1.TransactionID;
+-------+--------+---------------+---------+---------+------------------+------+---------------------------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+-------+--------+---------------+---------+---------+------------------+------+---------------------------------+
| t1 | ALL | NULL | NULL | NULL | NULL | 11 | Using temporary; Using filesort |
| t2 | ref | ID | ID | 4 | t1.TransactionID | 13 | |
| t3 | eq_ref | PRIMARY | PRIMARY | 4 | t2.GroupID | 1 | |
+-------+--------+---------------+---------+---------+------------------+------+---------------------------------+
ALL和範圍類型提示一個潛在的問題。
 

--------------------------------------------------------------------------------

二十五、學會使用SHOW PROCESSLIST

使用SHOW processlist來發現正在做什麼:
+----+-------+-----------+----+---------+------+--------------+-------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------+-----------+----+---------+------+--------------+-------------------------------------+
| 6 | monty | localhost | bp | Query | 15 | Sending data | select * from station,station as s1 |
| 8 | monty | localhost | | Query | 0 | | show processlist |
+----+-------+-----------+----+---------+------+--------------+-------------------------------------+
在mysql或mysqladmin中用KILL來殺死溜掉的執行緒。
--------------------------------------------------------------------------------

二十六、如何知曉MySQL解決一條查詢

執行項列指令並試圖弄明白其輸出:
SHOW VARIABLES;
SHOW COLUMNS FROM ...G
EXPLAIN SELECT ...G
FLUSH STATUS;
SELECT ...;
SHOW STATUS;

--------------------------------------------------------------------------------

二十七、MySQL非常不錯

日誌
在進行很多連線時,連線非常快。
同時使用SELECT和INSERT的場合。
在不把更新與耗時太長的選擇結合時。
在大多數選擇/更新使用唯一鍵碼時。
在使用沒有長時間衝突鎖定的多個表時。
在用大表時(MySQL使用一個非常緊湊的表格式)。

--------------------------------------------------------------------------------

二十八、MySQL應避免的事情

用刪掉的行更新或插入表,結合要耗時長的SELECT。
在能放在WHERE子句中的列上用HAVING。
不使用鍵碼或鍵碼不夠唯一而進行JOIN。
在不同列類型的列上JOIN。
在不使用=比對整個鍵碼時使用HEAP表。
在MySQL監控程式中忘記在UPDATE或DELETE中使用一條WHERE子句。如果想這樣做,使用mysql客戶程式的--i-am-a-dummy選項。

--------------------------------------------------------------------------------

二十九、MySQL各種鎖定

內部表鎖定
LOCK TABLES(所有表類型適用)
GET LOCK()/RELEASE LOCK()
页面鎖定(對BDB表)
ALTER TABLE也在BDB表上進行表鎖定
LOCK TABLES允許一個表有多個讀者和一個寫者。
一般WHERE鎖定俱有比READ鎖定高的優先順序以避免讓寫入方乾等。對於不重要的寫入方,可以使用LOW_PRIORITY關鍵字讓鎖定處理器優選讀取方。
UPDATE LOW_PRIORITY SET value=10 WHERE id=10;

--------------------------------------------------------------------------------

三十、給MySQL更多資訊以更好地解決問題的技巧 注意妳總能去掉(加注釋)MySQL功能以使查詢可移植:

SELECT /*! SQL_BUFFER_RESULTS */ ...
SELECT SQL_BUFFER_RESULTS ...
將強制MySQL生成一個暫時結果集。只要所有暫時結果集生成後,所有表上的鎖定均被釋放。這能在遇到表鎖定問題時或要花很長時間將結果傳給用戶端時有所說明。
SELECT SQL_SMALL_RESULT ... GROUP BY ...
告訴最佳化器結果集將隻包含很少的行。
SELECT SQL_BIG_RESULT ... GROUP BY ...
告訴最佳化器結果集將包含很多行。
SELECT STRAIGHT_JOIN ...
強制最佳化器以出現在FROM子句中的次序聯結表。
SELECT ... FROM table_name [USE INDEX (index_list) | IGNORE INDEX (index_list)] table_name2
強制MySQL使用/略過列出的索引。

--------------------------------------------------------------------------------

三十一、事務的例子

MyIASM表如何進行事務處理:
mysql> LOCK TABLES trans READ, customer WRITE;
mysql> select sum(value) from trans where customer_id=some_id;
mysql> update customer set total_value=sum_from_previous_statement
where customer_id=some_id;
mysql> UNLOCK TABLES;
BDB表如何進行事務:
mysql> BEGIN WORK;
mysql> select sum(value) from trans where customer_id=some_id;
mysql> update customer set total_value=sum_from_previous_statement
where customer_id=some_id;
mysql> COMMIT;
注意妳可以透過下列陳述式迴避事務:
UPDATE customer SET value=value+new_value WHERE customer_id=some_id;

--------------------------------------------------------------------------------

三十二、使用REPLACE的例子

REPLACE的功能極像INSERT,除了如果一條老記錄在一個唯一索引上俱有與新紀錄相同的值,那麼老記錄在新紀錄插入前則被移除。不使用 SELECT 1 FROM t1 WHERE key=#
IF found-row
LOCK TABLES t1
DELETE FROM t1 WHERE key1=#
INSERT INTO t1 VALUES (...)
UNLOCK TABLES t1;
ENDIF
而用
REPLACE INTO t1 VALUES (...)

--------------------------------------------------------------------------------

三十三、一般技巧

使用短主鍵。聯結表時使用數字而非字串。
當使用多部分鍵碼時,第一部分應該時最常用的部分。
有疑問時,首先使用更多重複的列以獲得更好地鍵碼壓縮。
如果在同一臺機器上執行MySQL客戶和伺服器,那麼在連線MySQL時則使用通訊端而不是TCP/IP(這可以提高效能7.5%)。可在連線MySQL伺服器時不指定主機名或主機名為localhost來做到。
如果可能,使用--skip-locking(在某些OS上為預設),這將關閉外部鎖定並將提高效能。
使用應用層哈希值而非長鍵碼:
SELECT * FROM table_name WHERE hash=MD5(concat(col1,col2)) AND
col_1='constant' AND col_2='constant'
在檔案中儲存需要以檔案形式訪問的BLOB,在資料庫中隻儲存檔案名。
移除所有行比移除一大部分行要快。
如果SQL不夠快,研究一下訪問資料的較底層介麵。

--------------------------------------------------------------------------------

三十四、使用MySQL 3.23的好處

MyISAM:可移植的大表格式
HEAP:記憶體中的表
Berkeley DB:支援事務的表。
眾多提高的限制
動態字元集
更多的STATUS變數
CHECK和REPAIR表
更快的GROUP BY和DISTINCT
LEFT JOIN ... IF NULL的最佳化
CREATE TABLE ... SELECT
CREATE TEMPORARY table_name (...)
暫時HEAP表到MyISAM表的自動轉換
複製
mysqlhotcopy指令檔

--------------------------------------------------------------------------------

三十五、正在積極開發的重要功能

改進事務處理
失敗安全的複製
內文搜尋
多個表的移除(之後完成多個表的更新)
更好的鍵碼快取
原子RENAME (RENAME TABLE foo as foo_old, foo_new as foo)
查詢高速快取
MERGE TABLES
一個更好的GUI客戶程式

<<<返回技術中心

技術文章

站內新聞

我要啦免费统计