最近應(yīng)用開發(fā)的程過中現(xiàn)出了一個(gè)小問題，順便記載一下原因和法方--查詢字段

????1、選取最實(shí)用的字段屬性

????MySQL可以很好的持支大據(jù)數(shù)量的取存，但是一般說(shuō)來(lái)，據(jù)數(shù)庫(kù)中的表越小，在它下面行執(zhí)的查詢也就會(huì)越快。因此，在建創(chuàng)表的時(shí)候，為了得獲更好的能性，我們可以將表中字段的寬度設(shè)得盡可能小。例如，在定義郵政編碼這個(gè)字段時(shí)，如果將其設(shè)置為CHAR(255),然顯給據(jù)數(shù)庫(kù)增加了不必要的空間，甚至應(yīng)用VARCHAR種這類型也是余多的，因?yàn)镃HAR(6)以可就很好的成完任務(wù)了。樣同的，如果可以的話，我們應(yīng)當(dāng)應(yīng)用MEDIUMINT而不是BIGIN來(lái)定義整型字段。

????另外一個(gè)進(jìn)步效率的法方是在可能的情況下，應(yīng)當(dāng)盡量把字段設(shè)置為NOT NULL，這樣在來(lái)將行執(zhí)查詢的時(shí)候，據(jù)數(shù)庫(kù)不用去較比NULL值。

????對(duì)于某些本文字段，例如“份省”或者“性別”，我們可以將它們定義為ENUM類型。因?yàn)樵贛ySQL中，ENUM類型被作當(dāng)值數(shù)型據(jù)數(shù)來(lái)處置，而值數(shù)型據(jù)數(shù)被處置起來(lái)的度速要比本文類型快得多。這樣，我們又可以進(jìn)步據(jù)數(shù)庫(kù)的能性。

????2、應(yīng)用連接（JOIN）來(lái)取代子查詢(Sub-Queries)

????MySQL從4.1開始持支SQL的子查詢。這個(gè)術(shù)技可以應(yīng)用SELECT語(yǔ)句來(lái)建創(chuàng)一個(gè)單列的查詢結(jié)果，然后把這個(gè)結(jié)果作為濾過條件用在另一個(gè)查詢中。例如，我們要將客戶基本息信表中沒有任何訂單的客戶刪掉除，以可就利用子查詢先從售銷息信表中將全部收回訂單的客戶ID出取來(lái)，然后將結(jié)果傳遞給主查詢，如下所示：

????DELETE FROM customerinfo WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo )

????應(yīng)用子查詢可以一次性的成完很多邏輯上要需多個(gè)步調(diào)才能成完的SQL操縱，同時(shí)也可以防止事務(wù)或者表鎖死，并且寫起來(lái)也很易容。但是，有些情況下，子查詢可以被更有效率的連接（JOIN）.. 替換。例如，設(shè)假我們要將全部沒有訂單記載的用戶出取來(lái)，可以用面下這個(gè)查詢成完：

????SELECT * FROM customerinfo WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo )

????如果應(yīng)用連接（JOIN）.. 來(lái)成完這個(gè)查詢作工，度速將會(huì)快很多。尤其是當(dāng)salesinfo表中對(duì)CustomerID建有引索的話，能性將會(huì)更好，查詢?nèi)缦拢?

????SELECT * FROM customerinfo LEFT JOIN salesinfoON customerinfo.CustomerID=salesinfo. CustomerID WHERE salesinfo.CustomerID IS NULL

????連接（JOIN）.. 之所以更有效率一些，是因?yàn)?MySQL不要需在內(nèi)存中建創(chuàng)臨時(shí)表來(lái)成完這個(gè)邏輯上的要需兩個(gè)步調(diào)的查詢作工。

????3、應(yīng)用合聯(lián)(UNION)來(lái)取代手動(dòng)建創(chuàng)的臨時(shí)表

????MySQL 從 4.0 的本版開始持支 UNION 查詢，它可以把要需應(yīng)用臨時(shí)表的兩條或更多的 SELECT 查詢合并的一個(gè)查詢中。在客戶端的查詢會(huì)話束結(jié)的時(shí)候，臨時(shí)表會(huì)被主動(dòng)刪除，從而保障據(jù)數(shù)庫(kù)整潔、高效。應(yīng)用 UNION 來(lái)建創(chuàng)查詢的時(shí)候，我們只要需用 UNION作為關(guān)鍵字把多個(gè) SELECT 語(yǔ)句連接起來(lái)以可就了，要注意的是全部 SELECT 語(yǔ)句中的字段目數(shù)要想同。面下的例子就演示了一個(gè)應(yīng)用 UNION的查詢。

????SELECT Name, Phone FROM client UNION SELECT Name, BirthDate FROM author
UNION
SELECT Name, Supplier FROM product

????4、事務(wù)

????盡管我們可以應(yīng)用子查詢（Sub-Queries）、連接（JOIN）和合聯(lián)（UNION）來(lái)建創(chuàng)林林總總的查詢，但不是全部的據(jù)數(shù)庫(kù)操縱都可以只用一條或多數(shù)幾條SQL語(yǔ)句以可就成完的。更多的時(shí)候是要需用到一系列的語(yǔ)句來(lái)成完某種作工。但是在種這情況下，當(dāng)這個(gè)語(yǔ)句塊中的某一條語(yǔ)句行運(yùn)錯(cuò)出的時(shí)候，整個(gè)語(yǔ)句塊的操縱就會(huì)變得不確定起來(lái)。想設(shè)一下，要把某個(gè)據(jù)數(shù)同時(shí)插入兩個(gè)相干聯(lián)的表中，可能會(huì)現(xiàn)出這樣的情況：第一個(gè)表中勝利更新后，據(jù)數(shù)庫(kù)忽然現(xiàn)出意外狀態(tài)，成造第二個(gè)表中的操縱沒有成完，這樣，就會(huì)成造據(jù)數(shù)的不完整，甚至?xí)p壞據(jù)數(shù)庫(kù)中的據(jù)數(shù)。要防止種這情況，就應(yīng)當(dāng)應(yīng)用事務(wù)，它的用作是：要么語(yǔ)句塊中每條語(yǔ)句都操縱勝利，要么都失敗。換句話說(shuō)，就是可以堅(jiān)持據(jù)數(shù)庫(kù)中據(jù)數(shù)的一致性和完整性。事物以BEGIN 關(guān)鍵字開始，COMMIT關(guān)鍵字束結(jié)。在這之間的一條SQL操縱失敗，那么，ROLLBACK命令以可就把據(jù)數(shù)庫(kù)恢復(fù)到BEGIN開始之前的狀態(tài)。

????BEGIN;

????INSERT INTO salesinfo SET CustomerID=14;

????UPDATE inventory SET Quantity=11

????WHERE item='book';

????COMMIT;

????事務(wù)的另一個(gè)重要用作是當(dāng)多個(gè)用戶同時(shí)應(yīng)用雷同的據(jù)數(shù)源時(shí)，它可以利用鎖定據(jù)數(shù)庫(kù)的法方來(lái)為用戶供提一種安全的問訪方法，這樣可以保障用戶的操縱不被其它的用戶所干擾。

????5、鎖定表

????盡管事務(wù)是維護(hù)據(jù)數(shù)庫(kù)完整性的一個(gè)非常好的法方，但卻因?yàn)樗莫?dú)占性，有時(shí)會(huì)影響據(jù)數(shù)庫(kù)的能性，尤其是在很大的應(yīng)用統(tǒng)系中。由于在事務(wù)行執(zhí)的程過中，據(jù)數(shù)庫(kù)將會(huì)被鎖定，因此其它的用戶請(qǐng)求只能臨時(shí)待等直到該事務(wù)束結(jié)。如果一個(gè)據(jù)數(shù)庫(kù)統(tǒng)系只有多數(shù)幾個(gè)用戶

????來(lái)應(yīng)用，事務(wù)成造的影響不會(huì)成為一個(gè)太大的問題；但設(shè)假有不計(jì)其數(shù)的用戶同時(shí)問訪一個(gè)據(jù)數(shù)庫(kù)統(tǒng)系，例如問訪一個(gè)電子商務(wù)網(wǎng)站，就會(huì)生產(chǎn)較比嚴(yán)重的應(yīng)響延遲。

????其實(shí)，有些情況下我們可以通過鎖定表的法方來(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ǔ)句出取初始據(jù)數(shù)，通過一些算計(jì)，用 UPDATE 語(yǔ)句將新值更新到表中。包含有 WRITE 關(guān)鍵字的 LOCK TABLE 語(yǔ)句可以保障在 UNLOCK TABLES 命令被行執(zhí)之前，不會(huì)有其它的問訪來(lái)對(duì) inventory 停止插入、更新或者刪除的操縱。

????6、應(yīng)用外鍵

????鎖定表的法方可以維護(hù)據(jù)數(shù)的完整性，但是它卻不能保障據(jù)數(shù)的關(guān)聯(lián)性。這個(gè)時(shí)候我們以可就應(yīng)用外鍵。例如，外鍵可以保障每一條售銷記載都指向某一個(gè)存在的客戶。在這里，外鍵可以把customerinfo 表中的CustomerID映射到salesinfo表中CustomerID，任何一條沒有正當(dāng)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;

????注意例子中的參數(shù)“ON DELETE CASCADE”。該參數(shù)保障當(dāng) customerinfo 表中的一條客戶記載被刪除的時(shí)候，salesinfo 表中全部與該客戶相干的記載也會(huì)被主動(dòng)刪除。如果要在 MySQL 中應(yīng)用外鍵，一定要記住在建創(chuàng)表的時(shí)候?qū)⒈淼念愋投x為事務(wù)安全表 InnoDB類型。該類型不是 MySQL 表的默許類型。定義的法方是在 CREATE TABLE 語(yǔ)句中加上 TYPE=INNODB。如例中所示。

????7、應(yīng)用引索

????引索是進(jìn)步據(jù)數(shù)庫(kù)能性的經(jīng)常使用法方，它可以令據(jù)數(shù)庫(kù)服務(wù)器以比沒有引索快得多的度速檢索特定的行，尤其是在查詢語(yǔ)句當(dāng)中包含有MAX(), MIN()和ORDERBY這些命令的時(shí)候，能性進(jìn)步更為顯明。那該對(duì)哪些字段建立引索呢？一般說(shuō)來(lái)，引索應(yīng)建立在那些將用于JOIN, WHERE判斷和ORDER BY排序的字段上。盡量不要對(duì)據(jù)數(shù)庫(kù)中某個(gè)含有大批復(fù)重的值的字段建立引索。對(duì)于一個(gè)ENUM類型的字段說(shuō)來(lái)，現(xiàn)出大批復(fù)重值是很有可能的情況，例如customerinfo中的“province”.. 字段，在這樣的字段上建立引索將不會(huì)有什么助幫；相反，還有可能下降據(jù)數(shù)庫(kù)的能性。我們?cè)诮▌?chuàng)表的時(shí)候可以同時(shí)建創(chuàng)適合的引索，也可以應(yīng)用ALTER TABLE或CREATE INDEX在后以建創(chuàng)引索。此外，MySQL

????從本版3.23.23開始持支全文引索和搜索。全文引索在MySQL 中是一個(gè)FULLTEXT類型引索，但僅能用于MyISAM 類型的表。對(duì)于一個(gè)大的據(jù)數(shù)庫(kù)，將據(jù)數(shù)裝載到一個(gè)沒有FULLTEXT引索的表中，然后再應(yīng)用ALTER TABLE或CREATE INDEX建創(chuàng)引索，將是非常快的。但如果將據(jù)數(shù)裝載到一個(gè)已經(jīng)有FULLTEXT引索的表中，行執(zhí)程過將會(huì)非常慢。

????8、優(yōu)化的查詢語(yǔ)句

????絕大多數(shù)情況下，應(yīng)用引索可以進(jìn)步查詢的度速，但如果SQL語(yǔ)句應(yīng)用不當(dāng)恰的話，引索將沒法揮發(fā)它應(yīng)有的用作。面下是應(yīng)當(dāng)注意的幾個(gè)方面。首先，最好是在雷同類型的字段間停止較比的操縱。在MySQL 3.23版之前，這甚至是一個(gè)必須的條件。例如不能將一個(gè)建有引索的INT字段和BIGINT字段停止較比；但是作為特別的情況，在CHAR類型的字段和VARCHAR類型字段的字段巨細(xì)雷同的時(shí)候，可以將它們停止較比。其次，在建有引索的字段上盡量不要應(yīng)用函數(shù)停止操縱。

????例如，在一個(gè)DATE類型的字段上應(yīng)用YEAE()函數(shù)時(shí)，將會(huì)使引索不能揮發(fā)應(yīng)有的用作。所以，面下的兩個(gè)查詢雖然回返的結(jié)果一樣，但后者要比前者快得多。

????SELECT * FROM order WHERE YEAR(OrderDate)<2001;
SELECT * FROM order WHERE OrderDate<" 2001-01-01 ";

????樣同的形情也會(huì)發(fā)生在對(duì)值數(shù)型字段停止算計(jì)的時(shí)候：

????SELECT * FROM inventory WHERE Amount/7<24;
SELECT * FROM inventory WHERE Amount<24*7;

????下面的兩個(gè)查詢也是回返雷同的結(jié)果，但前面的查詢將比前面的一個(gè)快很多。第三，在搜索符字型字段時(shí)，我們有時(shí)會(huì)應(yīng)用 LIKE 關(guān)鍵字和通配符，種這做法雖然單簡(jiǎn)，但卻也是以牲犧統(tǒng)系能性為價(jià)代的。例如面下的查詢將會(huì)較比表中的每一條記載。

????SELECT * FROM books
WHERE name like "MySQL%"

????但是如果換用面下的查詢，回返的結(jié)果一樣，但度速就要快上很多：

????SELECT * FROM books
WHERE name>="MySQL"and name<"MySQM"

????最后，應(yīng)當(dāng)注意防止在查詢中讓MySQL停止主動(dòng)類型轉(zhuǎn)換，因?yàn)檗D(zhuǎn)換程過也會(huì)使引索變得不起用作。

?????? 本文并非原創(chuàng)。

文章結(jié)束給大家分享下程序員的一些笑話語(yǔ)錄： 一程序員告老還鄉(xiāng)，想安度晚年，于是決定在書法上有所造詣。省略數(shù)字……，準(zhǔn)備好文房4寶，揮起毛筆在白紙上鄭重的寫下：Hello World

查詢字段[置頂] 優(yōu)化MySQL數(shù)據(jù)庫(kù)性能的八種方法