本文從八個(gè)方面來(lái)講解如何全新優(yōu)化MySQL數據庫性能。
1、選取最適用的字段屬性
MySQL可以很好的支持大數據量的存取，但是一般說(shuō)來(lái)，數據庫中的表越小，在它上面執行的查詢(xún)也就會(huì )越快。因此，在創(chuàng )建表的時(shí)候，為了獲得更好的性能，我們可以將表中字段的寬度設得盡可能小。例如，在定義郵政編碼這個(gè)字段時(shí)，如果將其設置為CHAR(255),顯然給數據庫增加了不必要的空間，甚至使用VARCHAR這種類(lèi)型也是多余的，因為CHAR(6)就可以很好的完成任務(wù)了。同樣的，如果可以的話(huà)，我們應該使用MEDIUMINT而不是BIGIN來(lái)定義整型字段。
另外一個(gè)提高效率的方法是在可能的情況下，應該盡量把字段設置為NOT NULL，這樣在將來(lái)執行查詢(xún)的時(shí)候，數據庫不用去比較NULL值。
對于某些文本字段，例如“省份”或者“性別”，我們可以將它們定義為ENUM類(lèi)型。因為在MySQL中，ENUM類(lèi)型被當作數值型數據來(lái)處理，而數值型數據被處理起來(lái)的速度要比文本類(lèi)型快得多。這樣，我們又可以提高數據庫的性能。
2、使用連接（JOIN）來(lái)代替子查詢(xún)(Sub-Queries)
MySQL從4.1開(kāi)始支持SQL的子查詢(xún)。這個(gè)技術(shù)可以使用SELECT語(yǔ)句來(lái)創(chuàng )建一個(gè)單列的查詢(xún)結果，然后把這個(gè)結果作為過(guò)濾條件用在另一個(gè)查詢(xún)中。例如，我們要將
DELETE FROM customerinfo
WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo )
使用子查詢(xún)可以一次性的完成很多邏輯上需要多個(gè)步驟才能完成的SQL操作，同時(shí)也可以避免事務(wù)或者表鎖死，并且寫(xiě)起來(lái)也很容易。但是，有些情況下，子查詢(xún)可以被更有效率的連接（JOIN）.. 替代。例如，假設我們要將所有沒(méi)有訂單記錄的用戶(hù)取出來(lái)，可以用下面這個(gè)查詢(xún)完成：
SELECT * FROM customerinfo
WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo )
如果使用連接（JOIN）.. 來(lái)完成這個(gè)查詢(xún)工作，速度將會(huì )快很多。尤其是當salesinfo表中對CustomerID建有索引的話(huà)，性能將會(huì )更好，查詢(xún)如下：
SELECT * FROM customerinfo
LEFT JOIN salesinfoON customerinfo.CustomerID=salesinfo.
CustomerID
WHERE salesinfo.CustomerID IS NULL
連接（JOIN）.. 之所以更有效率一些，是因為 MySQL不需要在內存中創(chuàng )建臨時(shí)表來(lái)完成這個(gè)邏輯上的需要兩個(gè)步驟的查詢(xún)工作。
3、使用聯(lián)合(UNION)來(lái)代替手動(dòng)創(chuàng )建的臨時(shí)表
MySQL 從 4.0 的版本開(kāi)始支持 UNION 查詢(xún)，它可以把需要使用臨時(shí)表的兩條或更多的 SELECT 查詢(xún)合并的一個(gè)查詢(xún)中。在客戶(hù)端的查詢(xún)會(huì )話(huà)結束的時(shí)候，臨時(shí)表會(huì )被自動(dòng)刪除，從而保證數據庫整齊、高效。使用 UNION 來(lái)創(chuàng )建查詢(xún)的時(shí)候，我們只需要用 UNION作為關(guān)鍵字把多個(gè) SELECT 語(yǔ)句連接起來(lái)就可以了，要注意的是所有 SELECT 語(yǔ)句中的字段數目要想同。下面的例子就演示了一個(gè)使用 UNION的查詢(xún)。
SELECT Name, Phone FROM client
UNION
SELECT Name, BirthDate FROM author
UNION
SELECT Name, Supplier FROM product
4、事務(wù)
盡管我們可以使用子查詢(xún)（Sub-Queries）、連接（JOIN）和聯(lián)合（UNION）來(lái)創(chuàng )建各種各樣的查詢(xún)，但不是所有的數據庫操作都可以只用一條或少數幾條SQL語(yǔ)句就可以完成的。更多的時(shí)候是需要用到一系列的語(yǔ)句來(lái)完成某種工作。但是在這種情況下，當這個(gè)語(yǔ)句塊中的某一條語(yǔ)句運行出錯的時(shí)候，整個(gè)語(yǔ)句塊的操作就會(huì )變得不確定起來(lái)。設想一下，要把某個(gè)數據同時(shí)插入兩個(gè)相關(guān)聯(lián)的表中，可能會(huì )出現這樣的情況：第一個(gè)表中成功更新后，數據庫突然出現意外狀況，造成第二個(gè)表中的操作沒(méi)有完成，這樣，就會(huì )造成數據的不完整，甚至會(huì )破壞數據庫中的數據。要避免這種情況，就應該使用事務(wù)，它的作用是：要么語(yǔ)句塊中每條語(yǔ)句都操作成功，要么都失敗。換句話(huà)說(shuō)，就是可以保持數據庫中數據的一致性和完整性。事物以BEGIN 關(guān)鍵字開(kāi)始，COMMIT關(guān)鍵字結束。在這之間的一條SQL操作失敗，那么，ROLLBACK命令就可以把
BEGIN;
INSERT INTO salesinfo SET CustomerID=14;
UPDATE inventory SET Quantity=11
WHERE item=‘book‘;
COMMIT;
事務(wù)的另一個(gè)重要作用是當多個(gè)用戶(hù)同時(shí)使用相同的數據源時(shí)，它可以利用鎖定數據庫的方法來(lái)為用戶(hù)提供一種安全的訪(fǎng)問(wèn)方式，這樣可以保證用戶(hù)的操作不被其它的用戶(hù)所干擾。
5、鎖定表
盡管事務(wù)是維護數據庫完整性的一個(gè)非常好的方法，但卻因為它的獨占性，有時(shí)會(huì )影響數據庫的性能，尤其是在很大的應用系統中。由于在事務(wù)執行的過(guò)程中，數據庫將會(huì )被鎖定，因此其它的用戶(hù)請求只能暫時(shí)等待直到該事務(wù)結束。如果一個(gè)數據庫系統只有少數幾個(gè)用戶(hù)
來(lái)使用，事務(wù)造成的影響不會(huì )成為一個(gè)太大的問(wèn)題；但假設有成千上萬(wàn)的用戶(hù)同時(shí)訪(fǎng)問(wèn)一個(gè)數據庫系統，例如訪(fǎng)問(wèn)一個(gè)
其實(shí)，有些情況下我們可以通過(guò)鎖定表的方法來(lái)獲得更好的性能。下面的例子就用鎖定表的方法來(lái)完成前面一個(gè)例子中事務(wù)的功能。
LOCK TABLE inventory WRITE
SELECT Quantity FROM inventory
WHEREItem=‘book‘;
...
UPDATE inventory SET Quantity=11
WHEREItem=‘book‘;
UNLOCK TABLES
這里，我們用一個(gè) SELECT 語(yǔ)句取出初始數據，通過(guò)一些計算，用 UPDATE 語(yǔ)句將新值更新到表中。包含有 WRITE 關(guān)鍵字的 LOCK TABLE 語(yǔ)句可以保證在 UNLOCK TABLES 命令被執行之前，不會(huì )有其它的訪(fǎng)問(wèn)來(lái)對 inventory 進(jìn)行插入、更新或者刪除的操作。
6、使用外鍵
鎖定表的方法可以維護數據的完整性，但是它卻不能保證數據的關(guān)聯(lián)性。這個(gè)時(shí)候我們就可以使用外鍵。例如，外鍵可以保證每一條銷(xiāo)售記錄都指向某一個(gè)存在的客戶(hù)。在這里，外鍵可以把customerinfo 表中的CustomerID映射到salesinfo表中CustomerID，任何一條沒(méi)有合法CustomerID的記錄都不會(huì )被更新或插入到salesinfo中。
CREATE TABLE customerinfo
(
CustomerID INT NOT NULL ,
PRIMARY KEY ( CustomerID )
) TYPE = INNODB;
CREATE TABLE salesinfo
(
SalesID INT NOT NULL,
CustomerID INT NOT NULL,
PRIMARY KEY(CustomerID, SalesID),
FOREIGN KEY (CustomerID) REFERENCES customerinfo
(CustomerID) ON DELETECASCADE
) TYPE = INNODB;
注意例子中的參數“ON DELETE CASCADE”。該參數保證當 customerinfo 表中的一條客戶(hù)記錄被刪除的時(shí)候，salesinfo 表中所有與該客戶(hù)相關(guān)的記錄也會(huì )被自動(dòng)刪除。如果要在 MySQL 中使用外鍵，一定要記住在創(chuàng )建表的時(shí)候將表的類(lèi)型定義為事務(wù)安全表 InnoDB類(lèi)型。該類(lèi)型不是 MySQL 表的默認類(lèi)型。定義的方法是在 CREATE TABLE 語(yǔ)句中加上 TYPE=INNODB。如例中所示。
7、使用索引
索引是提高數據庫性能的常用方法，它可以令數據庫服務(wù)器以比沒(méi)有索引快得多的速度檢索特定的行，尤其是在查詢(xún)語(yǔ)句當中包含有MAX(), MIN()和ORDERBY這些命令的時(shí)候，性能提高更為明顯。那該對哪些字段建立索引呢？一般說(shuō)來(lái)，索引應建立在那些將用于JOIN, WHERE判斷和ORDER BY排序的字段上。盡量不要對數據庫中某個(gè)含有大量重復的值的字段建立索引。對于一個(gè)ENUM類(lèi)型的字段來(lái)說(shuō)，出現大量重復值是很有可能的情況，例如customerinfo中的“province”.. 字段，在這樣的字段上建立索引將不會(huì )有什么幫助；相反，還有可能降低數據庫的性能。我們在創(chuàng )建表的時(shí)候可以同時(shí)創(chuàng )建合適的索引，也可以使用ALTER TABLE或CREATE INDEX在以后創(chuàng )建索引。此外，MySQL
從版本3.23.23開(kāi)始支持全文索引和搜索。全文索引在MySQL 中是一個(gè)FULLTEXT類(lèi)型索引，但僅能用于MyISAM 類(lèi)型的表。對于一個(gè)大的數據庫，將數據裝載到一個(gè)沒(méi)有FULLTEXT索引的表中，然后再使用ALTER TABLE或CREATE INDEX創(chuàng )建索引，將是非?？斓?。但如果將數據裝載到一個(gè)已經(jīng)有FULLTEXT索引的表中，執行過(guò)程將會(huì )非常慢。
8、優(yōu)化的查詢(xún)語(yǔ)句
絕大多數情況下，使用索引可以提高查詢(xún)的速度，但如果SQL語(yǔ)句使用不恰當的話(huà)，索引將無(wú)法發(fā)揮它應有的作用。下面是應該注意的幾個(gè)方面。首先，最好是在相同類(lèi)型的字段間進(jìn)行比較的操作。在MySQL 3.23版之前，這甚至是一個(gè)必須的條件。例如不能將一個(gè)建有索引的INT字段和BIGINT字段進(jìn)行比較；但是作為特殊的情況，在CHAR類(lèi)型的字段和VARCHAR類(lèi)型字段的字段大小相同的時(shí)候，可以將它們進(jìn)行比較。其次，在建有索引的字段上盡量不要使用函數進(jìn)行操作。
例如，在一個(gè)DATE類(lèi)型的字段上使用YEAE()函數時(shí)，將會(huì )使索引不能發(fā)揮應有的作用。所以，下面的兩個(gè)查詢(xún)雖然返回的結果一樣，但后者要比前者快得多。
SELECT * FROM order WHERE YEAR(OrderDate)<2001;
SELECT * FROM order WHERE OrderDate<"2001-01-01";
同樣的情形也會(huì )發(fā)生在對數值型字段進(jìn)行計算的時(shí)候：
SELECT * FROM inventory WHERE Amount/7<24;
SELECT * FROM inventory WHERE Amount<24*7;
上面的兩個(gè)查詢(xún)也是返回相同的結果，但后面的查詢(xún)將比前面的一個(gè)快很多。第三，在搜索字符型字段時(shí)，我們有時(shí)會(huì )使用 LIKE 關(guān)鍵字和通配符，這種做法雖然簡(jiǎn)單，但卻也是以犧牲系統性能為代價(jià)的。例如下面的查詢(xún)將會(huì )比較表中的每一條記錄。
SELECT * FROM books
WHERE name like "MySQL%"
但是如果換用下面的查詢(xún)，返回的結果一樣，但速度就要快上很多：
SELECT * FROM books
WHERE name>="MySQL"and name<"MySQM"
最后，應該注意避免在查詢(xún)中讓MySQL進(jìn)行自動(dòng)類(lèi)型轉換，因為轉換過(guò)程也會(huì )使索引變得不起作用。
聯(lián)系客服
