MySQL最佳實踐 從以下兩方面講MYSQL最佳實踐： 1. 表結構最佳實踐 2.QUERY最佳實踐 表結構最佳實踐： 1.越小通常越好 TINYINT/SMALLINT/MEDIUMINT/INT/BIGINT/DECIMAL/DOUBLE 在滿足需求的前提下，盡量選擇占用字節數小的數據類型。如上，能用TINYINT(1 byte)時，絕不用 SMALLINT(2byte)/MEDIUMINT(3byte). 不會有負數存儲時，盡量用UNSIGNED的類型。 若可行，則盡量用INT替代FLOAT，DECIMAL等形式。如存儲價格時，100*price 轉成整型。 這麼做的主要目的，是節省存儲空間。數據所占空間越小，查詢時需要走得路就越少，從而節省時間。 2.簡單就好 DATE/TIMESTAMP/DATETIME (3 byte/ 4byte/8byte) 道理同上。說明一點： TIMESTAMP是從1970年到2038就沒了的。 DATETIME則從1000到9999年為止的。所以，存儲空間省還是不省？值不值得省，完全取決於業務需求了。 3.盡量避免NULL 額外BYTE消耗，增加查詢復雜度 (vs.空) 只有需要區分空與NULL時，才可NULL，否則最好都有個默認的空值。 對數字型，默認值一般是0，或0.0之類的。這是有別於NULL的，好理解。 對於string型，空可表示為兩種：'' 和NULL.兩者是很有區別的。 假設你寫col字段為空的查詢： 1）無NULL的情況： select * from t where col＝'' 2) 有NULL的情況 select * from t where col is NULL or col='' 這都算小case。當你OUTER JOIN時，你就會痛苦一點說：col為NULL是因為JOIN不上而NULL還是它沒值而NULL？ 當然，這都是大體的方針，guideline, 具體還得看應用場景。 4.IP用數字存儲 15 bytes vs. 4bytes (INET_ATON,INET_NTOA) 省省省。MYSQL提供了ip轉數字，和數字轉IP的上述兩個函數，所以，還是能省則省吧。 5.靜態表會更快（固定長度的表） 無VARCHAR, TEXT, BLOB可變長度的string類型的字段，則稱此表為靜態表。否則，為動態表。 缺點：浪費點空間 （所謂靜態，就是類型設多大，它就直接分配多大的地方給你，不管你用得著用不著。） 現在靜態表應該很罕見吧。設計表時，在滿足需求的情況下，若能靜動分離，是最好的，若不能，也就算了。靜態表的好處是，查詢快，因為讀時，讀完這條記錄，它知道跳多遠，能讀到下一條記錄。動態表則不然，因為任何一條記錄的長度是動態的。 6. 垂直分割 優點：降低表復雜度和字段數目,(如可分離靜態和動態表) 缺點：過度分割導致多JOIN，性能更低 看業務，看數據量去平衡取捨。沒有絕對的好壞，都得因地制宜。 7. 字符集選擇 減少數量，而減少IO 我們大部分業務，直接GBK夠了。UTF8完全是一種浪費。 你想啊，GBK任何一個字都只用2字節就夠了。而UTF8因為支持了太多國家的語言，導致存儲中文時，需要2－4byte. 所以除非可能國際化，不然還真沒有必要。 8. 適度冗余 （空間換時間） 適度冗余最直接的目的，一般是為了減少JOIN。就是通過JOIN才能得到的另一張表內的字段，以冗余的形式，在當前表中再存儲一遍。這樣不需要JOIN了。當數據量大到一定程度時，這種做法是比較常見的。 9. 主鍵不要設得太大 (InnoDB) 這句話只適用於InnoDB. 這涉及到InnoDB數據存儲形式。它是以B加樹的形式存儲數據文件的。即，INNODB把數據文件存成跟索引文件一樣了。所以，每次你讀記錄的時候，都要通過主鍵去查詢。這也是為啥InnoDB中建表時，必須要有個自增長主鍵的原因。 假設表T，字段(id, owner, title, c1,c2,c3,c4).假設你想在owner字段上建個索引，則對應的索引上會存儲兩個字段，一個是OWNER，另一個則是ID（主鍵），用於到數據文件中讀取對應記錄的。若你把ID設成BIGINT，那就意味著，你每建一個索引，對應地都要加上這個8byte長的字段，那你索引體積蹭地一下上去了。 10. 增長ID的重要性 (InnoDB) InnoDB的數據文件本身就是索引文件，且是基於主鍵的索引文件。所以，這是為啥必須要有個主鍵的原因。你即使不設，它還是會默默地給你加個主鍵的。這是其一。 其二，為是得是增長呢？需要是增長的原因是，添加新的記錄時，你只需要後面append就行了，若不是按順序增長的，則插入新記錄時，它首先要找到合適的位置，然後看有沒有空間給它插，若沒有，得讓後面的往後挪，來給它騰位置。一兩條就算了，若千萬個都這麼干時，你說，這插入得多慢？慢不說，還給你搞得遍地是碎片，多不爽。 11. CHAR vs. VARCHAR: 不講編碼，因為char還是varchar都會跟著編碼走的。 其實想說的是，在大分部值的長度明確且 較短時(如存儲md5值)，適合選擇char(或更好的是binary in this case). 只有值的長度長短不一時，即較長的很長，較短的很短時，還是要選擇用varchar的。 char是你定義幾個char,它就直接分配你幾個char. varchar(n) 的實際空間占用長度是，n char + 1-2 byte.講到空間節省，這個確實會更節省一點。 若整個表定義中，只要有一個VARCHAR,那char與varchar基本區別不大了，除非說，你定義的是char(1). QUERY最佳實踐： 1. 不要用 “SELECT *”,否則，會讀多，傳輸多，且增加可避免的表掃描 我就不廢話了，基本人盡皆知的道規矩。 2. 不要 like ‘%item%’ but ‘item%’ 前面有％，這索引就沒辦法利用了。所以，若想用索引加快查詢速度，那前面別加%. 3. Cardinaltiy (基數) & Selectivity (選擇比) Cardinality: 不同值的個數。如表t中其有100條記錄， 字段owner也有100條值，但其中10個不相同的值。這10就是這字段的Cardinality. Selectivity: 10/100 = 10%就是這字段的selectivity. 這概念主要用來判斷此字段是否適合建索引。Cardinality越大，Selectivity越高的字段，越是理想的建索引的對象。有時數據庫會根據這個值來決定，是利用索引還是掃表。所以說，不是你建了索引，人家就會用的。而且，索引不可太多，多了反而會拖慢更新速度。 4. ORDER BY created DESC的優化 時間排序是應用中比較常見的需求。細想，這時間不是自增長的嘛？那跟ID自增長不是一回事兒嘛？ 所以說，在ORDER BY 時，用自增長的主鍵ID，會比用created，省一個FILE SORT操作。快很多的。 5. Count(1), count(*), count(owner)的區別 count(1)等同於count(*)，等同於count(任何一個NOT NULL的字段) count(owner)：若owner是可NULL的，則數出來的數跟上面的三種情況會少的。少的正好是那些owner is null的個數。 6. Don`t JOIN ON 不同數據類型 A表user_id作為B表的外鍵，這種很常見。此時，需注意user_id字段的類型，在兩張表裡都要保持一致。這樣節省不必要的開支，比如，數據庫替你做類型轉換等。 7. 不要用全文索引(full-text index) 當前只有MyISAM才支持全文索引。而且，不太好用，可自定義性比較差，所以完全無視它即可。若真需要做全文索引，還是考慮用Lucene, Solr, ElasticSearch, Sphinx, Groonga, Xapian等吧。個個都是行家裡手，功能齊全，可定義性強，隨你搞。 8. Limit n,m 慢，慎用 大部分人翻頁，可能都是靠這個的。數據量大時，這顯然會很慢。網上有人推薦說，第一次查出來後，記住當前頁的最後一個ID，然後，在查詢下一頁時，把這個ID做為限制條件加進去，然後取limit pagesize。 諸如此類，若細想，應該是能想出點兒可行之策的我覺的。其實，當數據量很大時，你可以換個角度想，如繼續在limit n,m上做文章能還是直接換個查詢方式，如用搜索引擎等。 9. 多字段索引 這個無需多說吧，道理應該是司空見慣了。 CREATE INDEX idx_col123 ON t (col1,col2,col3); 用法則： where col1='' and col2='' and col3='' where col1='' and col2='' where col1='' where col1='' and col3='' (col1時用索引，col3時一行行驗證過濾的) 你想想B Tree啥樣就知道了。（mysql裡應該是B+Tree, 查詢時，邏輯相仿，區別不大） 10. 一個SELECT能否用多個索引？ 可以。Mysql高一點的版本推出了merge optimization，支持的就是這功能。 11. JOIN vs. EXISTS 哪個更快？ 1）沒有定論，主要看JOIN的表大小，和one/many – to – one/many關聯關系. 2) 需要明確的是：EXIST相比JOIN的優勢在於 first match就返回，JOIN是能match的全部match. 3) JOIN相對於EXIST的優勢在於可以根據實際情況選擇執行的順序(join order)，MySQL5.6之前，如果where中有EXISTS 執行順序總是從外道內,現在好像變得更智能了。 4）小表JOIN大表時，用EXISTS可能更快。 A a JOIN B b ON （a.id=b.aid) WHERE a.owner='aaa' and b.cat='bbb'; 執行順序如下： （取出A表內所有滿足條件owner='aaa' 的記錄） JOIN ＝ 兩個for內一一匹配 （取出B表內所有滿足條件cat='bbb'的記錄） A a WHERE owner='aaa' and EXISTS (SELECT 1 from B where cat='bbb' and a.id=aid) 執行順序如下： for (取出A表內所有滿足條件owner='aaa' 的記錄) for (取出B表內所有滿足條件cat='bbb'的記錄) check if a.id=b.aid 說明一點：裡面的for，若無索引，得一條條全讀一遍B表的數據。若有索引，則只需讀一條對應記錄即可。 根據上面執行邏輯，外加表大小和關聯關系，你可以推導出用哪個更好，再測幾次，看看執行計劃啥的，大體就有定論了。
