50多條使用mysql數(shù)據(jù)庫(kù)優(yōu)化建議

1.對(duì)查詢進(jìn)行優(yōu)化，應(yīng)盡量避免全表掃描，首先應(yīng)考慮在WHERE及ORDER BY涉及的列上建立索引。

缺省情況下建立的索引是非群集索引，但有時(shí)它并不是最佳的。在非群集索引下，數(shù)據(jù)在物理上隨機(jī)存放在數(shù)據(jù)頁(yè)上。合理的索引設(shè)計(jì)要建立在對(duì)各種查詢的分析和預(yù)測(cè)上。一般來(lái)說(shuō)：

a.有大量重復(fù)值，且經(jīng)常有范圍查詢（>,<,>=,<=）和ORDER BY、 GROUP BY發(fā)生的列，可考慮建立群集索引；

b.經(jīng)常同時(shí)存取多列，且沒列都含有重復(fù)值，可考慮建立組合索引，選擇度高的列建議作為索引的第一個(gè)字段；

c.組合索引要盡量使關(guān)鍵查詢形成索引覆蓋，其前導(dǎo)列一定是使用最頻繁的列。索引雖有助于提高性能，但不是索引越多越好，恰好相反，過(guò)多的索引會(huì)導(dǎo)致系統(tǒng)低效。用戶在表中沒加進(jìn)一個(gè)索引，維護(hù)索引集合就要做相應(yīng)的更新工作。

2.應(yīng)盡量避免在WHERE子句中對(duì)字段進(jìn)行NULL值判斷，否則將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描；

    SQL代碼：SELECT id FROM t WHERE num IS NULL;
    可以在num上設(shè)置默認(rèn)值0，確保表中num列沒有null值，然后這樣查詢：
    SQL代碼：SELECT id FROM t WHERE num = 0;

3.應(yīng)盡量避免在WHERE子句中使用!=或<>操作符，否則引擎將放棄使用索引而進(jìn)行全表掃描。

4.應(yīng)盡量避免在WHERE子句中使用OR來(lái)連接條件，否則將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描。

    SQL代碼：SELECT id FROM t WHERE num = 10 OR num = 20;
    可以這樣查詢：
    SQL代碼：SELECT id FROM t WHERE num = 10 UNION ALL SELECT id FROM t WHERE num = 20;

5.IN和NOT IN也要慎用，否則會(huì)導(dǎo)致全表掃描，如：

    SQL代碼：SELECT id FROM t WHERE num IN(1,2,3);

對(duì)于連續(xù)的數(shù)值，能用BETWEEN就不要用IN了

    SQL代碼：SELECT id FROM t WHERE num BETWEEN 1 AND 3;

6.下面的查詢也將導(dǎo)致全表掃描：

    SQL代碼：SELECT id FROM t WHERE name LIKE '%c%';

若要提高效率，可以考慮全文檢索。

7.如果在WHERE子句中使用參數(shù)，也會(huì)導(dǎo)致全表掃描。因?yàn)镾QL只有在運(yùn)行時(shí)才會(huì)解析局部變量，但優(yōu)化程序不能將訪問計(jì)劃的選擇推遲到運(yùn)行時(shí)，它必須在編譯時(shí)進(jìn)行選擇。然而，如果在編譯時(shí)建立訪問計(jì)劃，變量的值還是未知的，因而無(wú)法作為索引選擇的輸入項(xiàng)。如下面語(yǔ)句將進(jìn)行全表掃描：

    SQL代碼：SELECT id FROM t WHERE num = @numl;

可以改為強(qiáng)制查詢使用索引：

    SQL代碼：SELECT id FROM t WITH(INDEX(索引名)) WHERE num = @num;

8.應(yīng)盡量避免在WHERE子句中對(duì)字段進(jìn)行表達(dá)式操作，這將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描：

    SQL代碼：SELECT id FROM t WHERE num / 20 = 100;

可以這樣查詢：

    SQL代碼：SELECT id FROM t WHERE num = 100 * 2;

9.應(yīng)盡量避免在WHERE子句中對(duì)該字段進(jìn)行函數(shù)操作，這將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描，如：

    SQL代碼：SELECT id FROM t WHERE SUBSTRING(name, 1, 3) = 'name';
    應(yīng)改為：
    SQL代碼：SELECT id FROM t WHERE name LIKE 'abc%';

10.不要在WHERE子句中的=左邊進(jìn)行函數(shù)、算數(shù)運(yùn)算或其他表達(dá)式運(yùn)算，否則系統(tǒng)將可能無(wú)法正確使用索引。

11.在使用索引字段作為條件時(shí)，如果該索引是復(fù)合索引，那么必須使用到該索引中的第一個(gè)字段作為條件時(shí)才能保證系統(tǒng)使用該索引，否則該索引將不會(huì)被使用，并且應(yīng)盡可能的讓字段順序與索引順序一致、

12.不要寫一些沒有意義的查詢，如需要生成一個(gè)空表結(jié)構(gòu)：

    SQL代碼：SELECT col1, col2 INTO #t FROM t WHERE 1 = 0;

這類代碼不會(huì)返回任何結(jié)果集，但是會(huì)消耗系統(tǒng)資源的，應(yīng)改成這樣：

    SQL代碼：CREATE TABLE #t(...);

13.很多時(shí)候用EXISTS代替IN是個(gè)好的選擇：

    SQL代碼：SELECT num FROM a WHERE num IN (SELECT num FROM b);

用下面的語(yǔ)句替換：

    SQL代碼：SELECT num FROM a WHERE EXISTS (SELECT 1 FROM b WHERE num = a.num);

14.并不是所有的索引都對(duì)查詢有效，SQL是根據(jù)表中數(shù)據(jù)來(lái)進(jìn)行查詢優(yōu)化的，當(dāng)索引列有大量數(shù)據(jù)重復(fù)時(shí)，SQL查詢可能不會(huì)去利用索引，如一表中有字段male、female幾乎各占一半，那么即使在這一列上建立了索引也對(duì)查詢效率起不了作用。

15.索引并不是越多越好，索引固然可以提高相應(yīng)的SELECT的效率，但同時(shí)也降低了INSERT及UODATE的效率，因?yàn)?code>INSERT或UPDATE時(shí)有可能會(huì)重建索引，索引怎樣檢索因需要慎重考慮，視具體情況而定。一個(gè)表的索引數(shù)最好不要超過(guò)6個(gè)，若太多則應(yīng)該考慮一些不常用到的列上建的索引是否有必要。

16.應(yīng)盡可能的避免更新CLUSTERED索引數(shù)據(jù)列的順序就是表記錄的物理存儲(chǔ)順序，一旦該列值改變將導(dǎo)致整個(gè)表記錄的順序的調(diào)整，會(huì)耗費(fèi)相當(dāng)大的資源。所應(yīng)用系統(tǒng)需要頻繁更新CLUSTERED索引數(shù)據(jù)列，那么需要考慮是否應(yīng)將該索引建為CLUSTERED索引。

17.盡量使用數(shù)字型字段，若只含數(shù)值信息的字段盡量不要設(shè)計(jì)為字符型，這會(huì)降低查詢和連接的性能，并會(huì)增加存儲(chǔ)開銷。這是因?yàn)橐嬖谔幚聿樵兒瓦B接時(shí)會(huì)逐個(gè)比較字符串中每一個(gè)字符，而對(duì)于數(shù)字型而言，只需要比較一次就夠了。

18.盡可能的使用VARCHAR/NVARCHAR代替CHAR/NCAHR，因?yàn)槭紫茸冮L(zhǎng)字段存儲(chǔ)空間小，可以節(jié)省存儲(chǔ)空間，其次對(duì)于查詢來(lái)說(shuō)，在一個(gè)相對(duì)較小的字段內(nèi)搜索效率顯然是要高一些。

19.任何地方都不要使用SELECT * FROM t;，用具體的字段列表代替“*”，不要返回用不到的任何字段。

20.盡量使用表變量來(lái)代替臨時(shí)表。如果表變量包含大量數(shù)據(jù)，請(qǐng)注意索引非常有限（只有主鍵索引）。

21.避免頻繁創(chuàng)建和刪除臨時(shí)表，以減少系統(tǒng)表資源的消耗。

22.臨時(shí)表并不是不可使用，適當(dāng)?shù)厥褂盟T可以使某些例程更有效，例如，當(dāng)需要重復(fù)引用大型表或常用表中的某個(gè)數(shù)據(jù)集時(shí)。但是，對(duì)于一次性事件，最好使用導(dǎo)出表。

23.在新建臨時(shí)表時(shí)，如果一次性插入數(shù)據(jù)量很大，那么可以使用SELECT INTO代替CREAT TABEL,避免造成大量LOG，以提高速度，如果數(shù)據(jù)量不大，為了緩和系統(tǒng)表的資源，應(yīng)先CREATE TABLE，然后INSERT。

24.如果使用到了臨時(shí)表，在存儲(chǔ)過(guò)程的最后務(wù)必將所有臨時(shí)表顯式刪除，先TRUNCATE TABLE，然后DROP TABLE，這樣可以避免系統(tǒng)表的較長(zhǎng)時(shí)間鎖定。

25.盡量避免使用游標(biāo)，因?yàn)橛螛?biāo)的效率較差，如果游標(biāo)操作的數(shù)據(jù)超過(guò)1萬(wàn)行，那么就應(yīng)該考慮該下改寫。

26.使用基于游標(biāo)的方法或臨時(shí)表方法之前，應(yīng)先尋找基于集的解決方案來(lái)解決問題，基于集的方法通常更有效。

27.與臨時(shí)表一樣，游標(biāo)并不是不可使用，對(duì)小型數(shù)據(jù)集使用FAST_FORWARD游標(biāo)通常要優(yōu)于其他逐行處理方法，尤其是在必須引用幾個(gè)表才能獲得所需的數(shù)據(jù)時(shí)。在結(jié)果集中包括“合計(jì)”的例程通常要比使用游標(biāo)執(zhí)行的速度快。如果開發(fā)時(shí)間允許，基于游標(biāo)的方法和基于集的方法都可以嘗試一下，看哪一種方法的效果更好。

28.在所有的存儲(chǔ)過(guò)程和觸發(fā)器的開始處設(shè)置SET NOCOUNT ON，在結(jié)束時(shí)設(shè)置SET NOCOUNT OFF。無(wú)需在執(zhí)行存儲(chǔ)過(guò)程和觸發(fā)器的每個(gè)語(yǔ)句后向客戶端發(fā)送DONE_IN_PROC消息。

29.盡量避免大事務(wù)操作，提高系統(tǒng)并發(fā)能力。

30.頂起分析表和檢查表。

分析表的語(yǔ)法：ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tb1_name[, tb1_name]...以上語(yǔ)句用于分析和存儲(chǔ)表的關(guān)鍵字分布，分析的結(jié)果將可以使得系統(tǒng)得到準(zhǔn)確的統(tǒng)計(jì)信息，使得SQL能夠生成正確的執(zhí)行計(jì)劃。如果用戶感覺實(shí)際執(zhí)行計(jì)劃并不是預(yù)期的執(zhí)行計(jì)劃，執(zhí)行一次分析表可能會(huì)解決問題。在分析期間，使用一個(gè)讀取鎖定對(duì)表進(jìn)行鎖定。這對(duì)于MyISAMDB、BDB和InniDB有作用。

例如分析一個(gè)數(shù)據(jù)表：ANALYZE TABLE table_name

檢查表的語(yǔ)法：CHECK TABLE tb1_name[, tb1_name]...[option]...option = {QUICK | FAST | MEDIUM | EXTENDED | CHANGED}

檢查表的作用是檢查一個(gè)或者多個(gè)表是否有錯(cuò)誤，CHECK TABLE對(duì)MyISAM和InnoDB表有作用，對(duì)于MyISAM表，關(guān)鍵字統(tǒng)計(jì)數(shù)據(jù)被更新。

CHECK TABLE也可以檢查視圖是否有錯(cuò)誤，比如在視圖定義中被引用的表不存在。

31.定期優(yōu)化表

優(yōu)化表的語(yǔ)法：OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tb1_name [, tb1_name]...如果刪除了標(biāo)的一大部分，或者如果已經(jīng)對(duì)含有可變長(zhǎng)度行的表（含有VARCHAR、BLOB或者TEXT列的表）進(jìn)行更多更改，則應(yīng)使用OPTIMIZE TABLE命令來(lái)進(jìn)行表優(yōu)化。這個(gè)命令可以將表中的空間碎片進(jìn)行合并，并且可以消除由于刪除或者更新造成的空間浪費(fèi)，但OPTIMIZE TABLE命令只對(duì)MyISAM、BDB和InnoDB表起作用。

例如：OPTIMIZE TABLE table_name;

注意：ANALYZE、CHECK、OPTIMIZE執(zhí)行期間將對(duì)表進(jìn)行鎖定，因此一定注意要在MySQL數(shù)據(jù)庫(kù)不繁忙的時(shí)候執(zhí)行相關(guān)的操作。

32.存儲(chǔ)引擎的選擇。如果數(shù)據(jù)表需要事務(wù)處理，應(yīng)該考慮使用InnoDB，因?yàn)樗耆螦CID特性。如果不需要事務(wù)處理，使用默認(rèn)存儲(chǔ)引擎MyISAM是比較明智的。

MyISAM適用于一些需要大量查詢的應(yīng)用，但其對(duì)于有大量寫操作并不是很好。設(shè)置你只是需要UPDATE一個(gè)字段，整個(gè)表都會(huì)被鎖起來(lái)，而別的進(jìn)程，就算是讀進(jìn)程都無(wú)法操作直到讀操作完成。另外，MyISAM對(duì)于SELECT COUNT(*)這類的計(jì)算是超快無(wú)比的。

33.InnoDB的趨勢(shì)會(huì)是一個(gè)非常復(fù)雜的存儲(chǔ)引擎，對(duì)于一些小的應(yīng)用，它會(huì)比MyISAM還慢。但是它支持行鎖，于是在寫操作比較多的時(shí)候，會(huì)更優(yōu)秀。并且，它還支持更多的高級(jí)操作，比如，事務(wù)。

34.EXPLAIN你的SELECT查詢

使用EXPLAIN關(guān)鍵字可以讓你知道MySQL是如何處理你的SQL語(yǔ)句的。這可以幫助你分析你的查詢語(yǔ)句或是表結(jié)構(gòu)的性能瓶頸。

EXPLAIN的查詢結(jié)果還會(huì)告訴你你的索引主鍵如何被利用，你的數(shù)據(jù)表是如何被搜索和排序的……等等，等等。

挑一個(gè)你的SELECT語(yǔ)句（推薦挑選那個(gè)最復(fù)雜的，有多表級(jí)聯(lián)的），把關(guān)鍵字EXPLAIN加到前面。你可以使用phpmyadmin來(lái)做這個(gè)事。然后，你會(huì)看到一張表格。下面的這個(gè)示例中，我們忘記加上了GROUP_ID索引，并且有表連接：

35.當(dāng)是要一行數(shù)據(jù)是使用LIMIT 1

當(dāng)你查詢表的有些時(shí)候，你已經(jīng)知道結(jié)果只會(huì)有一條，但因?yàn)槟憧赡苄枰?code>FETCH游標(biāo)，或是你也許會(huì)去檢查返回的記錄數(shù)。在這種情況下，加上LIMIT 1可能增加性能。這樣MySQL引擎會(huì)在找到一條數(shù)據(jù)后停止搜索，而不是繼續(xù)往后查找下一條符合記錄的數(shù)據(jù)。

    沒有效率的：
    $r = MYSQL_QUERY("SELECT * FROM user WHERE country = 'China'");IF(MYSQL_NUM_ROWS($r) > 0){//...}
    有效率的：
    $r = MYSQL_QUERY("SELECT * FROM user WHERE country = 'China' LIMIT 1");IF(MYSQL_NUM_ROWS($r) > 0){//...}

36.在JION表的時(shí)候使用相同類型的列，并將其索引

如果你的應(yīng)用程序有很多JION查詢，你應(yīng)該確認(rèn)兩個(gè)表中JION字段是被建過(guò)索引的。這樣MySQL內(nèi)部會(huì)啟動(dòng)為你優(yōu)化JION的語(yǔ)句的機(jī)制。

而且，這些被用來(lái)JION的字段，應(yīng)該是相同的類型的。例如：如果你要把DECIMAL字段和一個(gè)INT字段JION在一起，MySQL就無(wú)法使用它們的索引。對(duì)于那些STRING類型，還需要有相同的字符集才行。

    //在STATE中查找company
    $r = MYSQL_QUERY("SELECT company_name
        FROM users
        LEFT JION companies ON (users.state = companies.state)
        WHERE users.id = $user_id");

37.千萬(wàn)不要ORDER BY RAND()

想打亂返回的數(shù)據(jù)行？隨機(jī)挑一個(gè)數(shù)據(jù)？真不知道誰(shuí)發(fā)明了這種用法，但很多新手很喜歡這樣用。但你卻不了解這樣做有多么可怕的性能問題。

=如果你真的想把返回的數(shù)據(jù)行打亂了，你有N種方法可以達(dá)到這個(gè)目的，這樣使用只讓你的數(shù)據(jù)庫(kù)性能懲治書記的下降。這里的問題是：MySQL會(huì)不得不去執(zhí)行RAND()函數(shù)（很耗CPU時(shí)間），而且這是為了每一行汲取去記行，然后再對(duì)其排序。就算是你用了LIMIT 1也無(wú)濟(jì)于事（因?yàn)橐判颍?/p>

下面的示例是隨機(jī)挑一條記錄

    //千萬(wàn)不要這樣做：
    $r = MYSQL_QUERY("SELECT username FROM user ORDER BY RAND() LIMIT 1");
    //這樣做會(huì)更好：
    $r = MYSQL_QUERY("SELECT COUNT(*) FROM user");
    $d = MYSQL_FETCH_ROW($r);
    $rand = MT_RAND(0, $d[0] - 1);
    $r = MYSQL_QUERY("SELECT username FROM user LIMIT $rand, 1");

38.永遠(yuǎn)為每一張表設(shè)置一個(gè)ID

我們應(yīng)該為數(shù)據(jù)庫(kù)里的每張表都設(shè)置一個(gè)ID作為其主鍵，而且最好的是一個(gè)INT型的（推薦使用UNSIGNED）,并設(shè)置上自動(dòng)增加的AUTO_INCREMENT標(biāo)志。

就算是你users表有一個(gè)主鍵叫“email”的字段，你也別讓它成為主鍵。使用VARCHAR類型來(lái)當(dāng)主鍵會(huì)使性能下降。另外，在你的程序中，你應(yīng)該使用表的ID來(lái)構(gòu)造你的數(shù)據(jù)結(jié)構(gòu)。

而且，在MySQL數(shù)據(jù)引擎下，還有一些操作需要使用主鍵，在這些情況下，主鍵的性能和設(shè)置變得非常重要，比如，集群，分區(qū)……

在這里，只有一個(gè)情況是例外，那就是“關(guān)聯(lián)表”的“外鍵”。比如：有一個(gè)“學(xué)生表”有學(xué)生的ID，有一個(gè)“課程表”有課程ID，那么“成績(jī)表”就是“關(guān)聯(lián)表”了，其關(guān)聯(lián)了學(xué)生表和課程表嗎，在成績(jī)表中，學(xué)生ID和課程ID叫做“外鍵”其共同組成主鍵。

39.從PROCEDURE ANALYSE()取得建議

PROCEDURE ANALYSE()會(huì)讓MySQL幫你去分析你的字段和其實(shí)際的數(shù)據(jù)，并會(huì)給你一些有用的建議。只有表中有實(shí)際的數(shù)據(jù)，這些建議才會(huì)變得有用，因?yàn)橐鲆恍┐蟮臎Q定是需要有數(shù)據(jù)作為基礎(chǔ)的。

例如，如果你創(chuàng)建一個(gè)INT字段作為你的主鍵，然而并沒有太多的數(shù)據(jù)，那么，PROCEDURE ANALYSE()會(huì)建議你把這個(gè)字段的類型改為MEDIUMINT?；蚴悄闶褂昧艘粋€(gè)VARCHAR字段，因?yàn)閿?shù)據(jù)不多，你可能會(huì)得到一個(gè)讓你把它改成ENUM的建議。這些建議可能因?yàn)閿?shù)據(jù)不夠多，所以決策做得不夠準(zhǔn)。

一定要注意，這些只是建議，只有當(dāng)你的表里的數(shù)據(jù)越來(lái)越多是，這些建議才會(huì)變得準(zhǔn)確。一定要記住，你才是最終做決定的人。

40.字段盡可能的使用NOT NULL約束

除非你有一個(gè)很特別的原因去使用NULL值，你應(yīng)該總是讓你的字段保持NOT NULL。這看起來(lái)好像有點(diǎn)爭(zhēng)議，請(qǐng)往下看：

首先，問問自己“Empty”和“NULL”有多大區(qū)別（如果是INT，那就是0和NULL）？如果你覺得它們之間沒什么區(qū)別，那么你就不要使用NULL
。（你知道嗎？在Oracle里，NULL和Empty的字符串是一樣的?。?/p>

不要以為NULL不需要空間，其需要額外的空間，并且，在你進(jìn)行比較的時(shí)候，你的程序會(huì)更復(fù)雜。當(dāng)然，這里并不是說(shuō)你就不要使用NULL了，現(xiàn)實(shí)情況是很復(fù)雜的，依然在有些情況下，你需要使用NULL值。

下面摘自MySQL自己的文檔：

“NULL columns require additional space in the row to record whether their values are NULL. For MyISAM tables, each NULL column takes one bit extra, rounded up to the nearest byte.”

如果你要保存NULL,手動(dòng)去設(shè)置它，而不是把它設(shè)為默認(rèn)。建議使用0、特殊值或者空串代替NULL值

41.Prepared Statements

Prepared Statements很像存儲(chǔ)過(guò)程，是一種運(yùn)行在后臺(tái)的SQL語(yǔ)句集合，我們可以從使用Prepared Statements獲得很多好處，無(wú)論是性能問題還是安全問題。

Prepared Statements可以檢查一些你綁定好的變量，這樣可以保護(hù)你的程序不會(huì)受到“SQL注入式攻擊”，當(dāng)然，你也可以手動(dòng)地檢查你的這些變量，然而，手動(dòng)的檢查容易出問題，而且很經(jīng)常會(huì)被程序員忘了。當(dāng)我們使用一些framework或是ORM的時(shí)候，這樣的問題會(huì)好一些。

在性能方面，當(dāng)一個(gè)相同的查詢被使用多次的時(shí)候，這回為你帶來(lái)客觀的性能優(yōu)勢(shì)。你可以給這些Prepared Statements定義一些參數(shù)，而MySQL只會(huì)解析一次。

因?yàn)樽钚掳姹镜腗ySQL在傳輸Prepared Statements時(shí)使用二進(jìn)制形式，所以這會(huì)使得網(wǎng)絡(luò)傳輸非常有效率。

當(dāng)然，也有一些情況下，我們需要避免使用Prepared Statements，因?yàn)槠洳恢С植樵兙彺?。但?jù)說(shuō)版本5.1后支持了。

42.把IP地址存成UNSIGNED INT

很多程序員都會(huì)創(chuàng)建一個(gè)VARCHAR(15)字段來(lái)存放字符串形式的IP而不是整型的IP。如果你用整型來(lái)存放，只需要4個(gè)字節(jié)，并且你可以有定長(zhǎng)的字段。而且，這會(huì)為你帶來(lái)查詢上的優(yōu)勢(shì)，尤其是當(dāng)你需要使用這樣的WHERE條件：IP BETWEEN ip1 AND ip2。

我們必需要使用UNSIGNED INT因?yàn)镮P地址會(huì)使用整個(gè)32位的無(wú)符號(hào)整型。

而你的查詢，你可以使用INET_NTOA()把一個(gè)整型轉(zhuǎn)成一個(gè)字符串IP。在PHP中，也有這樣的函數(shù)IP2LONG()和LONG2IP()。

43.固定長(zhǎng)度的表會(huì)更快

如果表中所有字段都是“固定長(zhǎng)度”的，整個(gè)表會(huì)被認(rèn)為是“static”或“fixed-length”。例如，表中沒有如下類型的字段：VARCHAR，TEXT，BLOB。只要你包括了其中一個(gè)這些字段，那么這個(gè)表就不是“固定長(zhǎng)度靜態(tài)表”了，這樣，MySQL引擎會(huì)用另一種方法來(lái)處理。

固定長(zhǎng)度的表會(huì)提高性能，因?yàn)镸ySQL搜索得會(huì)更快一些，因?yàn)檫@些固定的長(zhǎng)度是很容易計(jì)算下一個(gè)數(shù)據(jù)的偏移量的，所以讀取的自然也會(huì)很快。而如果字段不是定長(zhǎng)的，那么，每一次要找下一條的話，需要程序找到主鍵。

而且，固定長(zhǎng)度的表也更容易被緩存和重建。不過(guò)，唯一的副作用是，固定長(zhǎng)度的字段會(huì)浪費(fèi)一些空間，因?yàn)槎ㄩL(zhǎng)的字段無(wú)論你用不用，他都要分配那么多的空間。

使用“垂直分割”技術(shù)，你可以分割你的表成為兩個(gè)：一個(gè)是定長(zhǎng)的，一個(gè)是不定長(zhǎng)的。

45.垂直分割表

“垂直分割”是一種把數(shù)據(jù)庫(kù)中的表按列變成幾張表的方法，這樣可以降低表的復(fù)雜度和字段的數(shù)目，從而達(dá)到優(yōu)化的目的。

示例一：在users表中有一個(gè)字段是家庭住址，這個(gè)字段是可選字段，相比起，而且你在數(shù)據(jù)庫(kù)操作的時(shí)候除了個(gè)人信息外，你并不需要經(jīng)常讀取或是改寫這個(gè)字段。那么，你為什么不把他放在另一張表中呢？這樣會(huì)讓你的表有更好的性能，大家想想是不是，大量的時(shí)候，我對(duì)于用戶表來(lái)說(shuō)，只有用戶ID、用戶名、口令和用戶角色等會(huì)被經(jīng)常使用。小一點(diǎn)的表總會(huì)有好的性能。

示例二：你有一個(gè)叫“l(fā)ast_login”的字段，它會(huì)在每次用戶登錄時(shí)被更新。但是，每次更新時(shí)會(huì)導(dǎo)致該表的查詢緩存被清空。所以，你可以把這個(gè)字段放在另一個(gè)表中，這樣就不會(huì)影響你對(duì)用戶ID、用戶名和用戶角色的不停讀取了，因?yàn)椴樵兙彺鏁?huì)幫你增加很多性能。

另外，你需要注意的是，這些被分出去的字段所形成的表，你不會(huì)經(jīng)常性地去JION他們，不然的話，這樣的性能會(huì)比不分割時(shí)還要差，而且，會(huì)是指數(shù)級(jí)地下降。

46.拆分大的DELETE或INSERT語(yǔ)句

如果你需要在一個(gè)在線的網(wǎng)站上去執(zhí)行一個(gè)大的DELETE或INSERT查詢，你需要非常小心，要避免你的操作讓你的整個(gè)網(wǎng)站停止服務(wù)，因?yàn)檫@兩個(gè)操作是會(huì)鎖表的，表一鎖住了，別的操作都進(jìn)不來(lái)了。

Apache會(huì)有很多的子進(jìn)程或線程。所以，其工作起來(lái)想當(dāng)有效率，而我們的服務(wù)器也不希望有太多的子進(jìn)程、線程和數(shù)據(jù)庫(kù)鏈接，這是極大的占服務(wù)器資源的事情，尤其是內(nèi)存。

如果你把你的表鎖上一段時(shí)間，比如30秒鐘，那么對(duì)于一個(gè)有很高訪問量的站點(diǎn)來(lái)說(shuō)，這30秒所積累的訪問進(jìn)程、線程、數(shù)據(jù)庫(kù)鏈接、打開的文件數(shù)，可能不僅僅會(huì)讓你的WEB服務(wù)Crash，還可能會(huì)讓你整臺(tái)服務(wù)器馬上掛了。

所以，如果你有一個(gè)很大的處理，你一定要把其拆分，使用LIMIT條件是一個(gè)很好的辦法。下面是一個(gè)示例：

    WHILE(1){
        //每次只做1000條
        MYSQL_QUERY("DELETE FROM logs WHERE log_date <= '2009-11-01' LIMIT 1000");
        IF(MYSQL_AFFECTED_ROWS() == 0){
            //沒得刪了，退出！
            //BREAK;
        }
        //每次都要休息一會(huì)兒
        USLEEP(50000)
    }

47.越小的列會(huì)越快

對(duì)于大多數(shù)的數(shù)據(jù)庫(kù)引擎來(lái)說(shuō)，硬盤操作可能是最重大的瓶頸。所以，把你的數(shù)據(jù)變得緊湊會(huì)對(duì)這種情況非常有幫助，因?yàn)檫@減少了對(duì)硬盤的訪問。

參看MySQL的文檔Storage Requirements查看所有的數(shù)據(jù)類型。

如果一個(gè)表只有幾列罷了（比如說(shuō)字典表，配置表），那么，我們就沒理由使用INT來(lái)做主鍵，使用MEDIUMINT、SMALLINT、或是更小的TINYINT會(huì)更經(jīng)濟(jì)一些。如果你不需要記錄時(shí)間，使用DATE要比DATETIME好很多。

當(dāng)然，你也需要留夠足夠的擴(kuò)展空間，參看Slashdot的例子（2009年11月06日），一個(gè)簡(jiǎn)單的ALTER TABLE語(yǔ)句花了3個(gè)多小時(shí)，因?yàn)槔锩嬗幸磺Я偃f(wàn)條數(shù)據(jù)。

48.使用一個(gè)對(duì)象關(guān)系映射器（Object Relational Mapper）

使用ORM你能獲得可靠的性能增長(zhǎng)。一個(gè)ORM可以做的所有事情，也能被手動(dòng)地邊寫出來(lái)，但是這需要一個(gè)高級(jí)專家。

ORM的最重要的是“LAZY LOADING”，也就是說(shuō)，只有在需要去取值的時(shí)候才會(huì)去真正地去做。但你也需要小心這種機(jī)制的副作用，因?yàn)檫@很有可能會(huì)因?yàn)橐?chuàng)建很多很多小的查詢，反而會(huì)降低性能。ORM還可以把你的SQL語(yǔ)句打包成一個(gè)事務(wù)，這會(huì)比單獨(dú)執(zhí)行它們快得多得多。

49.小心“永久鏈接”

“永久鏈接”的目的是用來(lái)減少重新創(chuàng)建MySQL鏈接的次數(shù)。當(dāng)一個(gè)鏈接被創(chuàng)建，它會(huì)永遠(yuǎn)處在連接的狀態(tài)，就算是數(shù)據(jù)庫(kù)操作已經(jīng)結(jié)束了。而且，自從我們的Apache開始重用它的子進(jìn)程后——也就是說(shuō)，下一次的HTTP請(qǐng)求會(huì)重用Apache的子進(jìn)程，并重用相同的MySQL鏈接。

50.范圍列（>,<,BETWEEN AND）可以用到索引，但是范圍列后面的列無(wú)法用到索引。同時(shí)，索引最多用于一個(gè)范圍列，因此如果查詢條件中有兩個(gè)范圍列則無(wú)法全用到作引。

51.如果需要在大字段上建立索引，可以考慮使用前綴索引。

建立前綴索引的語(yǔ)法為：

    ALTER TABLE table_name ADD KEY(column_name(prefix_length));

52.將大字段、訪問頻率低的字段拆分到單獨(dú)的表中存儲(chǔ)。分離冷熱數(shù)據(jù)，有利于有效利用緩存，防止讀入無(wú)用的冷數(shù)據(jù)，較少磁盤IO，同時(shí)保證熱數(shù)據(jù)常駐內(nèi)存提高緩存命中率。

53.MySQL的新增和修改列的操作相當(dāng)于重建表，表設(shè)計(jì)要一步到位，盡量避免大表的DDL操作。（TIPS：可以預(yù)定義一些列留作將來(lái)業(yè)務(wù)擴(kuò)展，如：當(dāng)前只需要10個(gè)字段，考慮到未來(lái)發(fā)展，可以預(yù)留10個(gè)字段，表上總共創(chuàng)建20個(gè)字段）

54.為了降低索引維護(hù)成本，禁止冗余索引，增大IO壓力。（a，b，c）、（a，b），后者為冗余索引?？梢岳们熬Y索引來(lái)達(dá)到加速目的，減輕維護(hù)負(fù)擔(dān)。

55.WHERE子句中的數(shù)據(jù)掃描不超過(guò)表總數(shù)據(jù)量的30%。

如何選擇prefic_length的長(zhǎng)度，具體參考：前綴索引，一種優(yōu)化索引大小的解決方案

補(bǔ)充：

• 在海量查詢時(shí)盡量少用格式轉(zhuǎn)換。

• 任何對(duì)列的操作都將導(dǎo)致表掃描，它包括數(shù)據(jù)庫(kù)教程函數(shù)、計(jì)算表達(dá)式等等，查詢時(shí)要盡可能將操作移至符號(hào)右邊。

• IN、OR子句常會(huì)使用工作表，是索引失效。如果不產(chǎn)生大量重復(fù)值，可以考慮把子句拆開。拆開的子句中應(yīng)該包含索引。

• 盡量少用CLOB、TEXT、BLOB大類型。

• 如果你的數(shù)據(jù)只有你所知的少量的幾個(gè)量，最好使用ENUM類型。

  ENUM類型是非常快和緊湊的。在實(shí)際上，其保存的是TINYINT，但其外表上顯示為字符串。這樣一來(lái)，用這個(gè)字段來(lái)做一些選項(xiàng)列表變得相當(dāng)完美。

  如果你有一個(gè)字段，比如“性別”，“國(guó)家”，“民族”，“狀態(tài)”或者“部門”，你知道這些字段的取值是有限而且固定的，那么，你應(yīng)該使用ENUM而不是VARCHAR。

  MySQL也有一個(gè)“建議”（見第十條）告訴你怎么去重新組織你的表結(jié)構(gòu)。當(dāng)你有一個(gè)VARCHAR字段時(shí)，這建議會(huì)告訴你把其改成ENUM類型。使用PROCEDURE ANALYSE()你可以得到相關(guān)的建議。

• 合理運(yùn)用庫(kù)、分表、與分區(qū)表提高數(shù)據(jù)存放和提取速度。具體參考：MySQL分表和分區(qū)的區(qū)別、分庫(kù)分表區(qū)別