有許多方法可以優(yōu)化. 最近，我在閱讀面試問題，并總結(jié)了一些優(yōu)化的方法.

優(yōu)化的方法

1. 選擇最適用的字段屬性

MySQL可以很好地支持對(duì)大量數(shù)據(jù)的訪問，但是通常來說，中的表越小，對(duì)其執(zhí)行的查詢越快. 因此，在創(chuàng)建表時(shí)，為了獲得更好的性能怎樣優(yōu)化，我們可以將表中字段的寬度設(shè)置得盡可能小.

例如，在定義郵政編碼字段時(shí)，如果將其設(shè)置為CHAR（255），則顯然會(huì)向添加不必要的空間. 即使使用VARCHAR也是多余的，因?yàn)镃HAR（6）可以很好地完成任務(wù). 同樣，如果可能，我們應(yīng)該使用MEDIUMINT而不是BIGIN來定義整數(shù)字段.

另一種提高效率的方法是在可能的情況下將字段設(shè)置為NOT NULL，以便將來在執(zhí)行查詢時(shí)不需要比較NULL值.

對(duì)于某些文本字段，例如“ province”或“ gender”，我們可以將它們定義為ENUM類型. 因?yàn)樵贛ySQL中，ENUM類型被視為數(shù)字?jǐn)?shù)據(jù)，并且數(shù)字?jǐn)?shù)據(jù)的處理比文本類型快得多. 這樣，我們可以提高的性能.

2. 使用JOIN代替子查詢

MySQL從4.1開始就支持SQL子查詢. 此技術(shù)可以使用SELECT語句創(chuàng)建單列查詢結(jié)果，然后將該結(jié)果用作另一個(gè)查詢中的過濾條件. 例如，如果我們要?jiǎng)h除“客戶基本信息”表中沒有任何訂單的客戶，則可以使用子查詢來首先從“銷售信息”表中獲取發(fā)布了訂單的所有客戶ID，然后將結(jié)果傳遞給主要查詢，如下所示:

DELETEFROMcustomerinfo

WHERECustomerIDNOTin（SELECTCustomerIDFROMsalesinfo）

使用子查詢可以完成許多SQL操作，這些操作在邏輯上需要一次完成多個(gè)步驟. 同時(shí)，它還可以避免事務(wù)或表鎖定，并且易于編寫. 但是，在某些情況下，子查詢可以由更高效的聯(lián)接（JOIN）代替. 例如，假設(shè)我們要檢索所有沒有訂單記錄的用戶，則可以使用以下查詢來完成:

SELECT * FROMcustomerinfo

WHERECustomerIDNOTin（SELECTCustomerIDFROMsalesinfo）

如果使用JOIN完成此查詢，則速度將更快. 特別是在salesinfo表中的CustomerID上有索引時(shí)，性能會(huì)更好. 查詢?nèi)缦?

SELECT * FROMcustomerinfo

LEFTJOINsalesinfoONcustomerinfo.CustomerID = salesinfo.CustomerID

WHEREsalesinfo.CustomerIDISNULL

加入（JOIN）. 之所以更有效，是因?yàn)镸ySQL不需要在內(nèi)存中創(chuàng)建臨時(shí)表即可完成此邏輯兩步查詢.

3，使用聯(lián)合（UNION）代替手動(dòng)創(chuàng)建的臨時(shí)表

MySQL自版本4.0起就支持聯(lián)合查詢，該聯(lián)合查詢可以將需要臨時(shí)表的兩個(gè)或多個(gè)選擇查詢組合為一個(gè)查詢. 在客戶端的查詢會(huì)話結(jié)束時(shí)，臨時(shí)表將被自動(dòng)刪除，以確保整潔高效. 當(dāng)使用union創(chuàng)建查詢時(shí)，我們只需要使用UNION作為關(guān)鍵字來連接多個(gè)select語句. 應(yīng)該注意的是，所有選擇語句中的字段數(shù)必須相同. 下面的示例演示了使用UNION的查詢.

SELECTName，PhoneFROMclientUNION

SELECTName，BirthDateFROMauthorUNION

SELECTName，SupplierFROMproduct

4. 事務(wù)

盡管我們可以使用子查詢，JOIN和UNION創(chuàng)建各種查詢，但并非所有操作都只能使用一個(gè)或幾個(gè)SQL語句. 通常，有必要使用一系列語句來完成某種工作. 但是在這種情況下，當(dāng)語句塊中的某個(gè)語句運(yùn)行不正確時(shí)，整個(gè)語句塊的操作將變得不確定. 想象一下，如果您想同時(shí)向兩個(gè)相關(guān)的表中插入某些數(shù)據(jù)，則可能會(huì)出現(xiàn)以下情況: 成功更新第一個(gè)表后，突然出現(xiàn)意外情況，從而導(dǎo)致第二個(gè)表中的操作無法完成這樣，它將導(dǎo)致數(shù)據(jù)不完整，甚至破壞中的數(shù)據(jù). 為避免這種情況，您應(yīng)該使用事務(wù)，它的作用是: 語句塊中每個(gè)語句的操作成功或全部失敗. 換句話說，可以保持中數(shù)據(jù)的一致性和完整性. 事情以BEGIN關(guān)鍵字開頭，以COMMIT關(guān)鍵字結(jié)尾. 同時(shí)，SQL操作失敗，那么ROLLBACK命令可以將恢復(fù)到BEGIN啟動(dòng)之前的狀態(tài).

BEGIN; INSERTINTOsalesinfoSETCustomerID = 14; UPDATEinventorySETQuantity = 11WHEREitem ='book'; COMMIT;

事務(wù)的另一個(gè)重要功能是，當(dāng)多個(gè)用戶同時(shí)使用同一數(shù)據(jù)源時(shí)，它可以使用鎖定的方法為用戶提供安全的訪問方法，這可以確保用戶的操作不會(huì)受其他用戶干擾的影響.

5. 鎖表

盡管事務(wù)是維護(hù)完整性的一種非常好的方法，但是由于其排他性，它有時(shí)會(huì)影響的性能，尤其是在大型應(yīng)用程序系統(tǒng)中. 由于將在執(zhí)行事務(wù)期間被鎖定，因此其他用戶請求只能暫時(shí)等待直到事務(wù)結(jié)束. 如果只有幾個(gè)用戶使用系統(tǒng)，則事務(wù)的影響不會(huì)成為大問題；但是如果有成千上萬的用戶同時(shí)訪問系統(tǒng)（例如電子商務(wù)網(wǎng)站），則響應(yīng)延遲會(huì)更加嚴(yán)重.

實(shí)際上，在某些情況下，我們可以通過鎖定表來獲得更好的性能. 下面的示例使用鎖定表的方法來完成上一個(gè)示例中的事務(wù)功能.

LOCKTABLEinventoryWRITESELECTQuantityFROMinventoryWHEREItem ='book';

...

UPDATEinventorySETQuantity = 11WHEREItem ='book'; UNLOCKTABLES

在這里，我們使用select語句獲取初始數(shù)據(jù)，并通過一些計(jì)算，使用update語句將新值更新到表中. 包含WRITE關(guān)鍵字的LOCKTABLE語句可以確保在執(zhí)行UNLOCKTABLES命令之前，沒有其他訪問權(quán)限來插入，更新或刪除清單.

6. 使用外鍵

鎖定表的方法可以維護(hù)數(shù)據(jù)的完整性，但是不能保證數(shù)據(jù)的相關(guān)性. 目前，我們可以使用外鍵.

例如，外鍵可以確保每個(gè)銷售記錄都指向一個(gè)現(xiàn)有客戶. 在這里，外鍵可以將customerinfo表中的CustomerID映射到salesinfo表中的CustomerID，并且沒有有效CustomerID的任何記錄都不會(huì)被更新或插入到salesinfo中.

CREATETABLEcustomerinfo（CustomerIDINTNOTNULL，PRIMARYKEY（CustomerID））TYPE = INNODB;

CREATETABLEsalesinfo（SalesIDINTNOTNULL，CustomerIDINTNOTNULL，

PRIMARYKEY（CustomerID，SalesID），??

FOREIGNKEY（CustomerID）REFERENCEScustomerinfo（CustomerID）ONDELETECASCADE）TYPE = INNODB;

在示例中，請注意參數(shù)“ ONDELETECASCADE”. 此參數(shù)保證當(dāng)刪除customerinfo表中的客戶記錄時(shí)，也會(huì)自動(dòng)刪除salesinfo表中與該客戶相關(guān)的所有記錄. 如果要在MySQL中使用外鍵，則必須記住在創(chuàng)建表時(shí)將表的類型定義為事務(wù)安全的InnoDB類型. 該類型不是MySQL表的默認(rèn)類型. 定義方法是將TYPE = INNODB添加到CREATETABLE語句. 如示例所示.

7. 使用索引

索引是提高性能的常用方法. 它可以使服務(wù)器檢索特定行的速度比沒有索引時(shí)要快得多，尤其是在查詢包含MAX（），MIN（）和ORDERBY命令when的情況下，性能提高更為明顯.

那應(yīng)該為哪些字段建立索引？

通常來說，索引應(yīng)該建立在將用于JOIN，WHERE判斷和ORDERBY排序的字段上. 盡量不要索引中包含大量重復(fù)值的字段. 對(duì)于ENUM類型的字段，很可能會(huì)有大量重復(fù)的值

例如，customerinfo中的“省” ...字段. 在此類字段上建立索引將無濟(jì)于事；相反，它也可能降低的性能. 創(chuàng)建表時(shí)，我們可以同時(shí)創(chuàng)建適當(dāng)?shù)乃饕�，或者將來可以使用ALTERTABLE或CREATEINDEX創(chuàng)建索引. 另外，MySQL從版本3.23.23開始支持全文索引和搜索. 全文索引是MySQL中的FULLTEXT類型索引，但只能用于MyISAM類型表. 對(duì)于大型，將數(shù)據(jù)加載到?jīng)]有FULLTEXT索引的表中非常快，然后使用ALTERTABLE或CREATEINDEX創(chuàng)建索引是非常快的. 但是，如果將數(shù)據(jù)加載到已經(jīng)具有FULLTEXT索引的表中，執(zhí)行過程將非常緩慢.

8. 優(yōu)化的查詢語句

在大多數(shù)情況下，使用索引可以提高查詢的速度，但是如果未正確使用SQL語句，則索引將無法發(fā)揮應(yīng)有的作用.

以下是應(yīng)注意的一些方面.

·首先，最好比較相同類型的字段之間的操作.

在MySQL 3.23之前，這甚至是必要條件. 例如，不能將索引的INT字段與BIGINT字段進(jìn)行比較. 但是，在特殊情況下，當(dāng)CHAR類型字段和VARCHAR類型字段的字段大小相同時(shí)，可以將它們進(jìn)行比較.

·其次，嘗試不要對(duì)索引字段使用函數(shù).

例如，當(dāng)在DATE類型字段上使用YEAE（）函數(shù)時(shí)，索引將無法正常運(yùn)行. 因此，盡管以下兩個(gè)查詢返回的結(jié)果相同，但后者比前者要快得多.

·第三怎樣優(yōu)化，在搜索字符字段時(shí)，有時(shí)會(huì)使用LIKE關(guān)鍵字和通配符. 盡管這種方法很簡單，但也會(huì)犧牲系統(tǒng)性能.

例如，以下查詢將比較表中的每條記錄.

SELECT * FROMbooks

類似“ MySQL％”的名字

但是，如果更改為以下查詢，結(jié)果將相同，但是速度會(huì)更快:

SELECT * FROMbooks

WHEREname> =“ MySQL” andname <“>

最后，應(yīng)注意避免讓MySQL在查詢中執(zhí)行自動(dòng)類型轉(zhuǎn)換，因?yàn)檗D(zhuǎn)換過程還將使索引失效.

本文來自本站，轉(zhuǎn)載請注明本文網(wǎng)址：
http://www.pc-fly.com/a/jisuanjixue/article-288799-1.html