作 者： 晏子
編譯
藉助諸如Apach、Perl、PHP和Python等工具，構建一個MySQL應用時很容易的。然而確保它們運行快速，則需要一點洞察力。本文就是你需要知道的東西。
MySQL對於成為一個非常快速的資料庫伺服器有著當之無愧的名聲，它也非常容易設置和使用。隨著它作為網站後端資料庫得聲望日增，其效果在去年開始有明顯提高。但是很多MySQL用戶更多地知道如何創建一個資料庫並編寫對它的查詢。就像成千上萬的人通過載閑暇時用Linux做實驗來學習Unix那樣，很多人通過玩MySQL學習關係資料庫。這些MySQL新手的大多數既沒有關係資料庫理論的背景，又沒有時間閱讀MySQL手冊全文。
因此，我們決定研究某些方法，你可以用針對優化性能來調節MySQL。在讀完本文後，你將理解一些幫助你設計你的MySQL資料庫和查詢的技術，值得你的應用很有效率。我們將假定你熟悉MySQL和SQL基礎，但不假定你有這兩方面的廣博知識。
只存儲你需要的信息
這聽上去是常識，但人們常常採取「廚房下水道」的方式進行資料庫設計。他們認為可能項要得每樣東西都要存儲並設計資料庫保存所有者這些數據。你需要對你的需求現實些，並確定取確實需要什麼信息。你常常能隨意產生一些數據而不把它存在資料庫表中。在這種情況下，從一個應用開發者的角度看也有道理這樣做。
例如，在線目錄的產品表可能包含各種產品的名稱、介紹、尺寸、重量和價格。除了價格，你可能想存儲每個項目相關的稅和運輸成本。但實際上不必這樣做。首先稅和運輸成本可以方便地（由你的應用或MySQL）計算出來。其次，如果稅和運輸成本改變了，你可能必須編寫必要的查詢更新每個產品記錄中的稅和運輸的費率。
有時人們認為這太難不能在以後往資料庫表中加入欄位，所以他們感覺不得不定義儘可能多的列。這是明顯的概念錯誤。在MySQL中，你可以用ALTER TABLE命令方便地修改表定義以適應你改變的需求。
例如，如果你突然認識到你需要給你的產品表增加一個級別列（可能你想允許用戶在你的目錄中給產品評級），你可以這樣做：
ALTER TABLE products ADD rank INTEGER
這給你的產品表增加了一個整數類型的級別列，你能用ALTER TABLE做什麼的完整介紹參見MySQL手冊。
只要求你需要的東西--要清晰
就像說「只存儲你需要的東西」那樣，這可能看來是常識，但這一點常常被忽視，為什麼呢？因為在一個應用開發時，需求經常改變，所以很多查詢最終看來是這樣：
SELECT * FROM sometable
當你不能肯定你將需要哪一列時，要求所有列明顯是最省力的事情，然而隨著你的表不斷增大和修改，這可能變成一個性能問題。最好是在你的最初開發完成後再花些時間並確定你真正從你的查詢中需要什麼：
SELECT name, rank, description FROM products
這帶來了一個相關的觀點，即代碼維護比性能更重要。大多數變成語言（Perl、Python、PHP、Java等）允許通過欄位名和數字編號訪問一條查詢的結果，這意味著你可以訪問命名欄位或欄位０都可以得到相同的數據。
長期看，最好使用列名而不是其編號位置，為什麼？因為一個表中或一條查詢中地列的相對位置可以改變。它們在表中可能因為重複使用ALTER TABLE而改變，它們在查詢中將因重寫了查詢而忘記更新應用邏輯來匹配而改變。
當然，你仍然需要小心改變列名！但如果你使用列名而非標號位置，如列名改變，你可以用grep搜索源代碼或使用編輯器的搜索能力查找你需要修改的代碼。
規範化你的表結構
如果你以前從未聽說過「數據規範化」，不要害怕。規範化可能是一個複雜的專題，你可以從只理解最基本的規範化概念中正真正獲益。
理解它的最容易的方法是認為你的表是一個電子報表。如果你想以一個報表跟蹤你的CD收藏，你可以如圖１種那樣進行設計：
圖１
album track1 track2 track10
----- ------ ------ -------
Billboard Top Hits - 1984 Loverboy Shout St. Elmo's Fire
(Billy Ocean) (Tears for Fears) (John Parr)
這看上去很合理。大多數CD只有10首曲子，對否？不盡然。如果你擁有一張有100首曲子的CD且幾張超過20首改怎麼辦。這意味著用這種方法，在極端的情況下，你將需要一個非常寬的表格（或一個超過100個欄位的表）來保存所有的數據。
規範化表結構的目標是使「空單元」的數量最少，在上述CD表的情況下，如果你允許CD可能包含100首曲子，你會有很多這樣的空單元。不管你何時處理可能擴展到類似該CD表那樣數量的欄位列表，它是你需要將你的數據分割成2個或更多表的標誌，然後你一起訪問並獲得你需要的數據。
很多關係資料庫的新手不真正知道關係資料庫管理系統中關係是什麼。簡單地說，就像一組信息存在可以基於共性數據聯結（JOIN）在一起的不同表中，很不幸，這聽上去更學術化和含糊，但CD資料庫提出了一個具體情況，我們可以研究如何規範數據。
每個CD列表有一個固定的屬性（標題、藝術家、年份、分類）集和一個不定的屬性（曲目表）集的理解給了我們一些如何分成成能相互關聯的表的思路。
你可以創建一個所有專輯及其固定屬性的表，另一個包含這些專輯的所有曲目的表。這樣不是水平思考（像表格），你垂直思考--就好像你創建列表而不是行--並建立一個如圖2的表結構：
專輯的編號（MySQL鏡自動為你生成，因為我們在列上使用了AUTO_INCREMENT屬性）關聯不同曲目到一給定專輯，tracks表中的album_id欄位匹配專輯表中的一個id。這樣要獲得給定專輯的所有曲目，你應該用如下查詢：
SELECT tracks.num, tracks.name
FROM albums, tracks
WHERE albums.title = 'Billboard Top Hits - 1984'
AND albums.id = tracks.album_id
該結構即靈活又有效。靈活性來自你可以在以後將數據加入系統而不必重新你已完整的工作的事實。例如，如果你想增加每一張專輯的藝術家信息，你可以床架一個artists表，關聯到albums表，就像tracks那樣。你無需修改現有的結構--只是增加它。
有效性來自於在你的數據中沒有明顯的數據重複且沒有大量的空洞（空單元）的實施。這樣MySQL在你的資料庫表中既不存儲多餘的數據，也不比花額外的精力搜索大量空區域。
如果你對關係資料庫是新手，規範化你的數據看起來有點奇怪，但在存儲和檢索數據時，它使MySQL非常有效，並給予你擴展和伸縮你的應用卻不必多次重構你的資料庫的靈活性。儘可能早的花時間想清楚資料庫設計，並考慮你的需求怎樣隨時間增長，前期花的時間永遠是值得的。
複合索引
複合索引（有時稱組合索引）是急於多個列的單一索引。MySQL在處理一條查詢時每個表只使用一個索引，這意味著如果你有多個經常出現在WHERE子句中的列，你可能要通過創建一個複合索引來加快這些查詢。
考慮下列表結構片斷：
CREATE TABLE people (
last_name VARCHAR(50) NOT NULL,
first_name VARCHAR(50) NOT NULL,
favorite_color VARCHAR(10) NOT NULL,
.
.
.
);
如果你常常基於last_name和first_name查詢表，你可以從last_name和first_name的複合索引中獲益：
INDEX last_first (last_name, first_name)
由於MySQL構建複合索引的方式，它可以使用last_first索引來回答基於last_name本身或last_name與first_name兩者的索引。這是因為如果列涉及複合索引的「最左前綴」的形式，MySQL將只使用一個複合索引。
所以如果一個複合索引有多個列合成：
INDEX big_index (a, b, c, d, e, f, g, h, i)
MySQL可以用它來回答基於a、或a和b、或a和b和c、或a和b和c和d的查詢。但它不能使用big_index處理基於e、或c和f、或g和i的查詢，因為這些序列沒有一個是從索引的最左邊開始的。
複合索引嘗被用於加快某些複雜查詢，但你需要理解起局限，而且你永遠應該進行一些測試，而不是簡單地假設這樣一個索引將會有幫助。
使用索引加快查詢
當MySQL試圖回達一條查詢時，它查看有關你的數據的各種統計，並決定如何以最快的速度找出你想要的數據。對於前小節的查詢，MySQL將讀取albums表的所有titles並把它們與「Billboard Top Hits --1984」進行比較看是否匹配。它一旦找到一個匹配還不能停止，因為有相同曲目的專輯不止一個（如你可以有12張CD標有「Greatest Hits」），結果MySQL必須讀取表中的每一行。這常稱為「全表掃描」且可以避免。
你應該避免全表掃描，因為：
CPU開銷：如果你沒有很多專輯，檢查所有這些標題的處理相對快些。但如果你需要在你的資料庫中存儲很多專輯呢？你有的專輯越多，花的時間越長。在專輯數量或檢查它們所花的時間時間存在一種線性關係。
併發性：在MySQL正在從表中讀取數據時，它鎖定表使得沒有其他人可以寫入，但可以讀取。當MySQL更新或刪除表中的行時，它鎖定表使得沒有其他人可以從它讀取。
磁碟開銷：在一個大數據表上，一次全表掃描將消耗大量磁碟I/O。這可能明顯地減慢你的資料庫伺服器 -- 特別是如果你的伺服器是較慢的IDE驅動器。
最好是讓全表掃描將到最少 -- 特別是你的應用需要以規模或用戶數伸縮。MySQL最新版確實有幾個併發性方面的改善（BDB、InnoDB和Gemini表類型）。
在這裡索引可以幫助你，簡單地放一個，一個索引允許MySQL很快地確定任何給定值如「Billboard Top Hits -- 1984」是否將匹配表中的任何行。
怎樣做到的呢？當你告訴MySQL索引一個特定列時，它在幕後創建另一個數據結構（索引）並用它存儲關於被索引列中的值的某些額外信息（被索引的值常稱為健碼）。這是一種簡化，MySQL將所有鍵碼存儲在一個樹狀數據結構中。該數據結構允許MySQL非常快速地找到特定鍵碼。
當MySQL發現列上有一個索引，它將使用索引而不是執行一個全表掃描。這節省了CPU時間（不必讀取所有可能的值）和磁碟I/O，而且它改善了併發性，因為MySQL只鎖定表足夠長的時間來獲得所需的行（基於它在索引中找什麼）。當你在表中有大量的數據，最終的改善可能非常明顯。
對圖3的albums表的CREATE TABLE語句的改進：
圖3
CREATE TABLE albums (
id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(80)NOT NULL,
INDEX title_idx (title)
);
正如你所見的，語句只是簡單地在定義后增加了一個INDEX行告訴MySQL在albums表中的title列上創建名為title_idx的索引。你可以給一個表增加多個索引，就像你可在表中有多個列一樣。單個索引也可以有多個列合成。
要給現有的表加上一個索引而不是重建表，你可以用ALTER TABLE命令：
ALTER TABLE albums ADD INDEX title_idx (title)
查詢處理
如果你的查詢複雜，MySQL用於精確確定如何獲取數據的原則可能變得難於理解。幸運的是，有幾個一般原則和一條命令允許你獲得正在做什麼的更好的理解。首先，原則是：
如果MySQL確定了簡單地掃描全表更快些，則它L將不使用索引。一般地，如果一個索引告訴MySQL訪問表中大概30%的行，它放棄索引並簡單地執行全表掃描。
如果多索引可以用來滿足查詢，MySQL將使用最嚴格的一個 -- 即導致最少的行被提取的那個。
如果你正在選擇的列是一個索引的所有部分，MySQL可以直接從索引中讀取鎖需的數據，絕不接觸（或鎖定）表本身。
當聯結幾個表時，MySQL將首先從可能返回最少行的表中讀取數據。你指定表的次序可能與MySQL使用它們的次序不同，這也影響到最終返回給你的行的次序，所以如果你需要行以特定的次序出現，要保證在你的查詢中使用一個ORDER子句。
已經說了很多了，重要的是認識到MySQL所做的一些決策實際上是基於猜測，就像人類進行大量猜測一樣，偶爾也會出錯。
如果你懷疑已經發生或只是想理解MySQL怎樣處理一條查詢，你可以使用EXPLAIN命令。簡單地在你的查詢前面加上EXPLAIN這個字，並要求MySQL執行它，MySQL不執行查詢，相反將報告有助於查詢的候選索引列表和所知道的有關它們的一切。
EXPLAIN輸出的完整討論參見MySQL參考手冊。
不要過分使用索引
已經知道索引使查詢更快，你可能傾向於索引你表中的每個列。但是得益於索引的性能提高是有代價的，在表中每次執行INSERT、UPDATE、REPLACE或DELETE，MySQL不得不更新表上的每個索引以反映變化。
那麼你如何確定何時食用它們呢？最常見的答案是「看情況」。它依賴你運行的查詢類型和你運行它們的頻度，它依賴於你的數據，它依賴於你的期望和需求。你得到了答案 -- 它依賴於很多事情。
在列上有索引的理由是MySQL縮窄其搜索範圍以便儘可能會的匹配行（且避免全表掃描）。你可以認為索引是對列中的每個唯一值只包含一項。在索引中，MySQL必須考慮任何重複值，這些重複值稍微降低效率和索引的用途。
所以在索引一個列之前，考慮數據重複的百分比，如果該百分比太高，你可能覺察不到用索引帶來的任何性能改善。
要以更簡明的術語描述，它清楚地知道在albums表中索引曲目欄位，因為有可能有大量不同的值，而且重複非常少。但如果在albums表中有一個分類列，索引它則可能價值不大，一般的CD收集包含多少不同的類型呢？將會有很多重複的類型值。
另一件要考慮的事情是你的查詢可能使用的頻度。MySQL只能對出現在一條查詢種的WHERE子句中的特定列使用索引，試圖回答查詢：
SELECT * FROM albums WHERE id = 500
MySQL不能使用在title上的查詢，該查詢要求MySQL基於其id查找記錄而不是其title。
如果你很少使用查詢中WHERE子句中的一個列，它可能不值得索引該列。可能在極少的情況下容人全表掃描比要求MySQL在每次修改時保持索引更新總體上更有效些。.
有疑問時，進行測試。你總能運行某些到索引或不帶索引的基準測試看哪一個更快，只要試圖讓你的基準測試逼真些。如果你的查詢有20%是UPDATE，80%是SLEECT，肯定你的基準測試能發映出來，詳見MySQL參考手冊。
使用REPLACE查詢
有可能你想往表中插入一條記錄，除非它已經存在。如果記錄已經存在，你想UPDATE它。不是重寫代碼中做這件事的邏輯，並需運行多個查詢，而是MySQL使用REPLACE來做此項工作。
如果id是6的專輯假定有曲目「Shaking the Tree」，你可以這樣寫查詢：
REPLACE INTO albums VALUES (6, 'Shaking the Tree')
重要的是理解REPLACE如何確定一條記錄是否在表中存在。MySQL將在表上使用PRIMARY KEY或UNIQUE KEY來執行檢查，如果都不存在，REPLACE效果上變成了INSERT。
使用臨時表
當工作在非常大的表上時，你可能偶爾需要運行很多查詢獲得一個大量數據的小的子集，不是對整個表運行這些查詢，而是讓MySQL每次找出所需的少數記錄，將記錄選擇到一個臨時表可能更快些，然後多這些表運行查詢。
創建臨時表很容易，給正常的CREATE TABLE語句加上TEMPORARY關鍵字：
CREATE TEMPORARY TABLE tmp_table (
name VARCHAR(10) NOT NULL,
value INTEGER NOT NULL
)
臨時表將在你連接MySQL期間存在。當你斷開時，MySQL將自動刪除表並釋放所用的空間。當然你可以在仍然連接的時候刪除表並釋放空間。
DROP TABLE tmp_table
如果在你創建名為tmp_table臨時表時名為tmp_table的表在資料庫中已經存在，臨時表將有必要屏蔽（隱藏）非臨時表tmp_table。
如果你聲明臨時表是一個HEAP表，MySQL也允許你指定在內存中創建它：
CREATE TEMPORARY TABLE tmp_table (
name VARCHAR(10) NOT NULL,
value INTEGER NOT NULL
) TYPE = HEAP
因為HEAP表存儲在內存中，你對它運行的查詢可能比磁碟上的臨時錶快些。然而，HEAP表與一般的表有些不同，且有自身的限制。詳見MySQL參考手冊。
正如前面的建議，你應該測試臨時表看看它們是否真的比對大量資料庫運行查詢快。如果數據很好地索引，臨時表可能一點不快。
臨時表在MySQL 3.23.0和更新版才有。
只用最新版的MySQL
2001年一月中旬，MySQL 3.23宣布穩定。除了后很多新功能外，它也比3.22系列更快和更具伸縮性。
MySQL的更新版不斷推出。如果你控制著你的MySQL伺服器，最好是嘗試保持版本相對最新。除了有最新功能和錯誤修正，你常常會看到較新的MySQL發行有明顯的性能提高。
其他資源
除了閱讀MySQL手冊，你也可以諮詢：
你的DBA：如果你在一個共享伺服器上使用MySQL，某個人被指定為資料庫管理員（DBA），這是一個好機會。如果你的DBA非常熟悉MySQL，它可能可以在分析和優化你的表結構和查詢上幫你一下。
MySQL用戶郵件列表：有非常活躍的郵件列表，在其中MySQL用戶經常彼此尋求幫助。新手和老手願意分享其知識並彼此幫助解決共同的問題。實際上，有些MySQL開發團隊成員很好地監視著郵件列表。
MySQL書籍：不要與MySQL參考手冊混淆，Paul DuBois寫了一本極好的書名為《MySQL》的書（有中文譯本）。
(2001.8.29)
