織夢資料庫設計規則文檔

欄位表和欄位命名

表欄位命名和前面提到的變數相關命名保持一致的規則，內容相關模型盡量採用複數形式，例如：dede_archives、dede_addonimages，儲存多項內容的欄位，或代表數量的欄位，也應當以複數方式命名，例如keywords（關鍵詞）、scores（積分）。

表如果有關聯欄位，盡量保證統一的命名，比如dede_arctype中的typeid和dede_archives中的typeid。

代表id自增量的欄位，通常用以下的幾種方式：

1.最常用的核心id，或經常在URL中進行調用的，盡量用簡寫的形式，例如tid、pid、uid；

2.有功能性作用，URL中偶爾用到的id，使用全稱的形式，例如mid；

3.沒有功能性作用，只為管理和維護方便而設的id，可以使用全稱的形式，也可只將其命名為id；

所有與表、欄位相關的命名，請務必大量參考DedeCMS現有欄位的命名方式，以保證命名的系統性和統一性。

欄位結構

允許NULL值的欄位，資料庫在進行比較操作時，會先判斷其是否為NULL，非NULL時才進行值的必對。因此基於效率的考慮，所有欄位均不可為空，即全部NOT NULL；

預計不會儲存非負數的欄位，例如各項id、文章數等，必須設定為UNSIGNED類型。UNSIGNED類型比非UNSIGNED類型所能儲存的正整數範圍大一倍，因此能獲得更大的數值儲存空間；

儲存開關、選項資料的欄位，通常使用tinyint(1)非UNSIGNED類型，少數情況也可能使用enum()結果集的方式。tinyint作為開關欄位時，通常1為開啟；0為關閉；-1為特殊資料，例如N/A(不可用)；高於1的為特殊結果或開關位元組合(詳見程式中相關代碼)；

MEMORY/HEAP類型的表中，要尤其注意規劃節約使用儲存空間，這將節約更多記憶體。例如：將IP地址的儲存拆分為4個tinyint(3) UNSIGNED類型的欄位，不採用char(15)的方式；

任何類型的資料表，欄位空間應當本著足夠用，不浪費的原則，數實值型別的欄位取值範圍見下表：

欄位類型	儲存空間(b)	UNSIGNED	取值範圍
tinyint	1	否	-128~127
		是	0~255
smallint	2	否	-32768~32767
		是	0~65535
mediumint	3	否	-8388608~8388607
		是	0~255
int	4	否	-2147483648~2147483647
		是	0~4294967295
bigint	8	否	-9223372036854775808~9223372036854775807
		是	0~18446744073709551615

SQL語句

所有SQL語句中，除了表名、欄位名稱以外，全部語句和函數均需大寫，應當杜絕小寫方式或大小寫混雜的寫法。例如select * from cdb_members;是不符合規範的寫法。

很長的SQL語句應當有適當的斷行，依據JOIN、FROM、ORDER BY等關鍵字進行界定。

通常情況下，在對多表進行操作時，要根據不同表名稱，對每個表指定一個1~2個字母的縮寫，以利於語句簡潔和可讀性。

如下的語句範例，是符合規範的：

SELECT a.*, c.* FROM #@__arvhives a, #@__addonarticle c WHERE c.aid=a.id AND a.id='$aid'

效能與效率定長與變長表

包含任何varchar、text等變長欄位的資料表，即為變長表，反之則為定長表。

1.對於變長表，由於記錄大小不同，在其上進行許多刪除和更改將會使表中的片段更多。需要定期運行OPTIMIZE TABLE以保持效能。而定長表就沒有這個問題；

2.如果表中有可變長的欄位，將它們轉換為定長欄位能夠改進效能，因為定長記錄易於處理。但在試圖這樣做之前，應該考慮下列問題：

3.使用定長列涉及某種折衷。它們更快，但佔用的空間更多。char(n) 類型列的每個值總要佔用n 個位元組(即使空串也是如此)，因為在表中儲存時，值的長度不夠將在右邊補空格；

4.而varchar(n)類型的列所佔空間較少，因為只給它們分配儲存每個值所需要的空間，每個值再加一個位元組用於記錄其長度。因此，如果在char和varchar類型之間進行選擇，需要對時間與空間作出折衷；

5.變長表到定長表的轉換，不能只轉換一個可變長欄位，必須對它們全部進行轉換。而且必須使用一個ALTER TABLE語句同時全部轉換，否則轉換將不起作用；

6.有時不能使用定長類型，即使想這樣做也不行。例如對於比255字元更長的串，沒有定長類型；

7.在設計表結構時如果能夠使用定長資料類型盡量用定長的，因為定長表的查詢、檢索、更新速度都很快。必要時可以把部分關鍵的、承擔頻繁訪問的表拆分，例如定長資料一個表，非定長資料一個表。例如DedeCMS的dede_archives和dede_addonarticle表、dede_member和dede_member_person表等。因此規劃資料結構時需要進行全域考慮；

進行表結構設計時，應當做到恰到好處，反覆推敲，從而實現最優的資料存放區體系。

運算與檢索

數值運算一般比字串運算更快。例如比較運算，可在單一運算中對數進行比較。而串運算涉及幾個逐位元組的比較，如果串更長的話，這種比較還要多。

如果串列的值數目有限，應該利用普通整型或emum類型來獲得數值運算的優越性。

更小的欄位類型永遠比更大的欄位類型處理要快得多。對於字串，其處理時間與串長度直接相關。一般情況下，較小的表處理更快。對於定長表，應該選擇最小的類型，只要能儲存所需範圍的值即可。例如，如果mediumint夠用，就不要選擇bigint。對於可變長類型，也仍然能夠節省空間的。一個TEXT 類型的值用2 位元組記錄值的長度，而一個LONGTEXT 則用4位元組記錄其值的長度。如果儲存的值長度永遠不會超過64KB，使用TEXT 將使每個值節省2位元組。

結構最佳化與索引最佳化

索引能加快查詢速度，而索引最佳化和查詢最佳化是相輔相成的，既可以依據查詢對索引進行最佳化，也可以依據現有索引對查詢進行最佳化，這取決於修改查詢或索引，哪個對現有產品架構和效率的影響最小。

索引最佳化與查詢最佳化是多年經驗積累的結晶，在此無法詳述，但仍然給出幾條最基本的準則。

首先，根據產品的實際運行和被訪問情況，找出哪些SQL語句是最常被執行的。最常被執行和最常出現在程式中是完全不同的概念。最常被執行的SQL語句，又可被劃分為對大表(資料條目多的)和對小表(資料條目少的)的操作。無論大表或小表，有可分為讀(SELECT)多、寫(UPDATE/INSERT)多或讀寫都多的操作。

對常被執行的SQL語句而言，對大表操作需要尤其注意：

1.寫操作多的，通常可使用寫入緩衝的方法，先將需要寫或需要更新的資料緩衝至檔案或其他表，定期對大表進行批量寫操作，例如:點擊數延遲更新機制，就是依據此原理實現。同時，應盡量使得常被讀寫的大表為定長類型，即便原本的結構中大表並非定長。大表定長化，可以通過改變資料存放區結構和資料讀取方式，將一個大表拆成一個讀寫多的定長表，和一個讀多寫少的變長表來實現；

2.讀操作多的，需要依據SQL查詢頻率設定專門針對高頻SQL語句的索引和聯合索引。

其次，看SQL語句的條件和排序欄位是否動態性很高(即根據不同功能開關或屬性，SQL查詢條件和排序欄位的變化很大的情況)，動態性過高的SQL語句是無法通過索引進行最佳化的。惟一的辦法只有將資料緩衝起來，定期更新，適用於結果對實效性要求不高的場合。

MySQL索引，常用的有PRIMARY KEY、INDEX、UNIQUE幾種，詳情請查閱MySQL文檔。通常，在單表資料值不重複的情況下，PRIMARY KEY和UNIQUE索引比INDEX更快，請酌情使用。

事實上，索引是將條件查詢、排序的讀操作資源消耗，分布到了寫操作中，索引越多，耗費磁碟空間越大，寫操作越慢。因此，索引決不能盲目添加。對欄位索引與否，最根本的出發點，依次仍然是SQL語句執行的機率、表的大小和寫操作的頻繁程度。

查詢最佳化

MySQL中並沒有提供針對查詢條件的最佳化功能，因此需要開發人員在程式中對查詢條件的先後順序人工進行最佳化。例如如下的SQL語句：

SELECT * FROM table WHERE a>’0’ AND b<’1’ ORDER BY c LIMIT 10;

事實上無論a>’0’還是b<’1’哪個條件在前，得到的結果都是一樣的，但查詢速度就大不相同，尤其在對大表進行操作時。

開發人員需要牢記這個原則：最先出現的條件，一定是過濾和排除掉更多結果的條件；第二出現的次之；以此類推。因而，表中不同欄位的值的分布，對查詢速度有著很大影響。而ORDER BY中的條件，只與索引有關，與條件順序無關。

除了條件順序最佳化以外，針對固定或相對固定的SQL查詢語句，還可以通過對索引結構進行最佳化，進而實現相當高的查詢速度。原則是：在大多數情況下，根據WHERE條件的先後順序和ORDER BY的排序欄位的先後順序而建立的聯合索引，就是與這條SQL語句匹配的最優索引結構。儘管，事實的產品中不能只考慮一條SQL語句，也不能不考慮空間佔用而建立太多的索引。

同樣以上面的SQL語句為例，最優的當table表的記錄達到百萬甚至千萬級後，可以明顯的看到索引最佳化帶來的速度提升。

依據上麵條件最佳化和索引最佳化的兩個原則，當table表的值為如下方案時，可以得出最優的條件順序方案：

欄位a	欄位b	欄位c
1	7	11
2	8	10
3	9	13
-1	0	12
最優條件：b<’1’ AND a>’0’ 最優索引：INDEX abc (b, a, c) 原因：b<’1’作為第一條件可以先過濾掉75%的結果。如果以a>’0’作為第一條件，則只能先過濾掉25%的結果注意1：欄位c由於未出現於條件中，故條件順序最佳化與其無關 注意2：最優索引由最優條件順序得來，而非由例子中的SQL語句得來注意3：索引並非修改資料存放區的物理順序，而是通過對應特定位移量的物理資料而實現的虛擬指標

EXPLAIN語句是檢測索引和查詢能否良好匹配的簡便方法。在phpMyAdmin或其他MySQL用戶端中運行EXPLAIN+查詢語句，例如EXPLAIN SELECT * FROM table WHERE a>’0’ AND b<’1’ ORDER BY c;這種形式，即使得開發人員無需類比上百萬條資料，也可以驗證索引是否合理，相關細節請參考MySQL說明。

值得提出的是，Using filesort是最不應當出現的情況，如果EXPLAIN得出此結果，說明資料庫為這個查詢專門建立了一個用以緩衝結果的暫存資料表檔案，並在查詢結束後刪除。眾所周知，硬碟I/O速度始終是電腦儲存的瓶頸，因此，查詢中應當盡全力避免高執行頻率的SQL語句使用filesort。儘管，開發人員永遠都不可能保證產品中的全部SQL語句都不會使用filesort。

限於篇幅，本文檔遠遠沒有涵蓋資料庫最佳化的方方面面，例如：聯合索引與普通索引的可重用性、JOIN串連的索引設計、MEMORY/HEAP表等。資料庫最佳化實際上就是在很多因素和利弊間不斷權衡、修改，惟有在成功與失敗經驗中反覆推敲才能得出的經驗，這種經驗往往就是最難能可貴和價值連城的。