一、我們可以且應該優化什麼？
硬件
操作系統/軟件庫
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構造。[url]www.MySQL.com上的Crash-me頁可以幫助你。[/url]
為操作系統/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關鍵字讓鎖定處理器優選讀取方。
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客戶程序
