http://www.21ds.net/article/print.php/35 MySQL的優化 2002-10-08 不詳 http://www.jsp001.com 列印自: phpArticle 文章管理系統,PHP,MySQL,WYSIWYG Editor,文章系統 地址: http://www.21ds.net/article/article.php/35 我們可以且應該優化什麽？ 硬體 作業系統/軟體庫 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何時使用索引 對一個鍵碼使用>, >=, =, <, <=, IF NULL和BETWEEN SELECT * FROM table_name WHERE key_part1=1 and key_part2 > 5; SELECT * FROM table_name WHERE key_part1 IS NULL; 當使用不以通配符開始的LIKE SELECT * FROM table_name WHERE key_part1 LIKE 'jani%' 在進行聯結時從另一個表中提取行時 SELECT * from t1,t2 where t1.col=t2.key_part 找出指定索引的MAX()或MIN()值 SELECT MIN(key_part2),MAX(key_part2) FROM table_name where key_part1=10 一個鍵碼的字首使用ORDER BY或GROUP BY SELECT * FROM foo ORDER BY key_part1,key_part2,key_part3 在所有用在查詢中的列是鍵碼的一部分時間 SELECT key_part3 FROM table_name WHERE key_part1=1 MySQL何時不使用索引 如果MySQL能估計出它將可能比掃描整張表還要快時，則不使用索引。例如如果key_part1均勻分佈在1和100之間， 下列查詢中使用索引就不是很好： SELECT * FROM table_name where key_part1 > 1 and key_part1 < 90 如果使用HEAP表且不用=搜索所有鍵碼部分。 在HEAP表上使用ORDER BY。 如果不是用鍵碼第一部分 SELECT * FROM table_name WHERE key_part2=1 如果使用以一個通配符開始的LIKE SELECT * FROM table_name WHERE key_part1 LIKE '%jani%' 搜索一個索引而在另一個索引上做ORDER BY SELECT * from table_name WHERE key_part1 = # ORDER BY key2 學會使用EXPLAIN 對於每一條你認爲太慢的查詢使用EXPLAIN! mysql> 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關?br /> t秩盟ùp砥饔叛《寥》健? 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客戶程式