|
一， 數(shù)據(jù)庫(kù)對(duì)象命名規(guī)范 表名，字段名，視圖名，存儲(chǔ)過(guò)程名，函數(shù)名，觸發(fā)器名稱(chēng)統(tǒng)一使用大寫(xiě)字母命名。使用最接近數(shù)據(jù)庫(kù)對(duì)象含義的英文單詞，單詞組合，或縮寫(xiě)來(lái)命名，盡可能做得見(jiàn)名知意。 表名： 以T_開(kāi)頭，比如：T_COMMONCONTENT 字段名：大寫(xiě)英文單詞或縮寫(xiě) 視圖名：以V_開(kāi)頭。 存儲(chǔ)過(guò)程名：以P_開(kāi)頭 函數(shù)名：以F_開(kāi)頭 觸發(fā)器名：以TR_開(kāi)頭 索引名： 以IDX_開(kāi)頭
二， 數(shù)據(jù)庫(kù)設(shè)計(jì) 2.1 數(shù)據(jù)類(lèi)型選擇 選擇原則：在滿(mǎn)足需求的前提下，盡可能選擇范圍較小的數(shù)據(jù)類(lèi)型來(lái)定義字段。涉及貨幣的字段都選擇精確度高的decimal定長(zhǎng)數(shù)據(jù)類(lèi)型來(lái)表示。按以下優(yōu)先級(jí)來(lái)選擇數(shù)據(jù)類(lèi)型：
高優(yōu)先級(jí) 低優(yōu)先級(jí)
TINYINT--------->BIGINT---àCHAR-----àVARCHAR-------àTEXT-----àLONGTEXT
在滿(mǎn)足業(yè)務(wù)定義需求的前提下，能選擇整型的就用整型，位數(shù)短的用短整型，如果位數(shù)不夠就用長(zhǎng)整型，位數(shù)逐步增加。不能用整型的優(yōu)先選用字符型（CHAR）,字符型不夠選用變長(zhǎng)字符型（VARCHAR）,最后才考慮選用文本型（TEXT）。對(duì)只有一位長(zhǎng)度的字段，比如狀態(tài)值等，統(tǒng)一使用TINYINT類(lèi)型。總之，在數(shù)據(jù)類(lèi)型的選擇上，做到寧短勿長(zhǎng)，這樣即能節(jié)省存儲(chǔ)空間，又能提高處理速度。
以下是各種整數(shù)類(lèi)型的存儲(chǔ)字節(jié)數(shù)和數(shù)值表達(dá)范圍：
2.2 數(shù)據(jù)庫(kù)設(shè)計(jì)
2.2.1 主外鍵定義
每個(gè)表都要求定義主鍵和外鍵約束，通過(guò)外鍵的定義，可以保障數(shù)據(jù)的一致性和完整性。定義外鍵時(shí)，統(tǒng)一采用ON DELETE SET NULL ON UPDATE SET NULL方式。這種方式在刪除被參照表的數(shù)據(jù)時(shí)，數(shù)據(jù)庫(kù)將自動(dòng)把參照表中的相關(guān)記錄的相關(guān)字段置空。這樣的好處是，當(dāng)參照表的記錄被刪除時(shí)，子表記錄可以完整的保存下來(lái)。
定義主鍵健外鍵例子：
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 DELETE SET NULL ON UPDATE SET NULL ) TYPE = INNODB;
2.2.2 數(shù)據(jù)表中字段順序
在數(shù)據(jù)表的設(shè)計(jì)時(shí)統(tǒng)一規(guī)定各字段依以下順序設(shè)置：
主鍵---------->同步字段(version)----------->外鍵--------->其它字段
2.2.3 索引創(chuàng)建問(wèn)題
索引對(duì)大數(shù)據(jù)量表查詢(xún)來(lái)說(shuō)，比較重要，過(guò)多過(guò)少的索引，不合理的索引都會(huì)直接影響到SQL的查詢(xún)性能，建議到測(cè)試時(shí)統(tǒng)一由數(shù)據(jù)庫(kù)支持人員創(chuàng)建。
2.2.4 表拆分問(wèn)題 在進(jìn)行數(shù)據(jù)庫(kù)表設(shè)計(jì)時(shí)，盡可能使SQL查詢(xún)的表關(guān)聯(lián)數(shù)不要超過(guò)3個(gè)，如果表關(guān)連太多，可能需要考慮使用冗余字段來(lái)減少表關(guān)聯(lián)的個(gè)數(shù)。關(guān)聯(lián)表太多的SQL查詢(xún)，很可能效率比較差并且優(yōu)化困難。 如果設(shè)計(jì)的表包括大數(shù)據(jù)量的TEXT或BLOB類(lèi)型字段，盡量把大數(shù)據(jù)量大的TEXT（BLOB）字段拆成單獨(dú)的表，避免導(dǎo)致整個(gè)表的查詢(xún)都慢。
2.2.5 數(shù)據(jù)庫(kù)接口定義 表設(shè)計(jì)統(tǒng)一采用PowerDesigner工具完成，除設(shè)計(jì)本模塊的表外，還需要標(biāo)出外模塊的參照表或有數(shù)據(jù)關(guān)系的表，建議用虛線標(biāo)出外模塊相關(guān)的表。
三， SQL編寫(xiě)規(guī)范 3.1 SQL簡(jiǎn)化和減少數(shù)據(jù)庫(kù)查詢(xún)次數(shù) 在滿(mǎn)足業(yè)務(wù)需求的前提下，盡可能使編寫(xiě)的SQL簡(jiǎn)單，不要選擇多余的字段和不必要的嵌套查詢(xún)，越簡(jiǎn)單的SQL，查詢(xún)性能可能越好，也越好進(jìn)行性能優(yōu)化。
比如： 不要用SELECT *：SELECT語(yǔ)句中寫(xiě)出必要的要選擇的全部列名，增強(qiáng)語(yǔ)句可讀性，避免不必要的選擇；SELECT *增加了對(duì)所有字段的依賴(lài)，當(dāng)表增加了字段后，有可能發(fā)生錯(cuò)誤；此外還可能增加了數(shù)據(jù)的流量，查詢(xún)了一些實(shí)際不需要的字段。 其它SQL查詢(xún)也要求選擇真正需要選擇的字段，避免選擇出多余的字段。 另外，盡可能減少數(shù)據(jù)庫(kù)操作的次數(shù)，建議一次性查出你需要的結(jié)果集，先放到應(yīng)用內(nèi)存中進(jìn)行遍歷處理。
3.2 索引使用問(wèn)題 3.2.1 在查詢(xún)列上避免使用表達(dá)式
在WHERE中，數(shù)據(jù)庫(kù)函數(shù)、計(jì)算表達(dá)式等等，要盡可能將放在等號(hào)右邊。否則會(huì)使所比較的字段上的索引失效； SELECT ＊ FROM service_promotion WHERE TO_CHAR(gmt_modified,’yyyy-mm-dd’) = ‘20001-09-01’;
而應(yīng)使用：
SELECT * FROM service_promotion WHERE gmt_modified >= DATE_FORMAT('2009-07-26 20:49:33','%Y-%m-%d'); AND gmt_modified < DATE_FORMAT('2009-07-26 20:49:33','%Y-%m-%d');
3.2.2 查詢(xún)條件列類(lèi)型的隱含轉(zhuǎn)換 盡量注意比較值與查詢(xún)列數(shù)據(jù)類(lèi)型的一致性(int與int比較、char與char比較)，避免使用數(shù)據(jù)庫(kù)的類(lèi)型自動(dòng)轉(zhuǎn)換功能,比如： SELECT * FROM category WHERE id = ‘123’; -- id在表中定義為int類(lèi)型
3.2.3 Like查詢(xún)
在進(jìn)行模糊查詢(xún)時(shí)，不要使用WHERE columnname like ‘%字符串%’這樣的查詢(xún)形式，這樣的查詢(xún)語(yǔ)句不能使用列上所建的索引，當(dāng)數(shù)據(jù)量稍微有點(diǎn)大就會(huì)導(dǎo)致有嚴(yán)重的性能問(wèn)題。如果確實(shí)有這樣的需求，應(yīng)該考慮用別的方式實(shí)現(xiàn)。這個(gè)問(wèn)題需要在表設(shè)計(jì)的時(shí)候就考慮到。
3.2.4 排序，分組查詢(xún)
大量的排序操作將嚴(yán)重影響系統(tǒng)性能，所以盡量減少order by和group by排序操作。如果有大數(shù)據(jù)量的表確實(shí)存在大范圍的分組查詢(xún)求和運(yùn)算，建議通過(guò)建立臨時(shí)統(tǒng)計(jì)表，通過(guò)觸發(fā)器或后臺(tái)作業(yè)來(lái)完成統(tǒng)計(jì)數(shù)據(jù)的計(jì)算。對(duì)大數(shù)據(jù)量表的排序查詢(xún)，除可以在排序列上建立索引外，應(yīng)該盡量通過(guò)表設(shè)計(jì)加條件減少排序范圍來(lái)實(shí)現(xiàn)排序操作。 對(duì)于進(jìn)行分組求和操作的SQL，可以通過(guò)增加選擇項(xiàng)來(lái)避免排序，比如,在sql語(yǔ)句的末尾增加order by null選項(xiàng)避免進(jìn)行資源消耗量極大的排序操作。
Select id,sum(moneys) from sales2 group by id order by null;
3.2.5 視圖，存儲(chǔ)過(guò)程，函數(shù)，觸發(fā)器的使用 復(fù)雜，重復(fù)性使用率高的SQL查詢(xún)語(yǔ)句，建議定義成視圖（VIEW）來(lái)使用，好處是將來(lái)可以在數(shù)據(jù)庫(kù)后臺(tái)進(jìn)行修改，優(yōu)化等維護(hù)，同時(shí)也可以簡(jiǎn)化應(yīng)用端的代碼編寫(xiě)。對(duì)操作任務(wù)量大，比如定時(shí)性的數(shù)據(jù)統(tǒng)計(jì)，計(jì)算任務(wù)，建議編寫(xiě)成存儲(chǔ)過(guò)程，函數(shù)來(lái)實(shí)現(xiàn)。盡量少用觸發(fā)器，特別是使用帶有大量數(shù)據(jù)操作任務(wù)的觸發(fā)器，那樣的觸發(fā)器會(huì)降低表的更新速度。而且大量使用觸發(fā)器會(huì)給數(shù)據(jù)庫(kù)的維護(hù)帶來(lái)更大的難度。
3.2.6 使用批操作提高數(shù)據(jù)插入效率 如果數(shù)據(jù)插入量比較大，建議寫(xiě)成批量操作的形式，將能大大提高數(shù)據(jù)庫(kù)插入的效率，比如：
INSERT INTO `T_VISITIP` VALUES (38094,'10.10.10.0',10,'2007-02- 28'), (38095,'10.10.10.1',10,'2007-02- 28'), (38096,'10.10.10.2',10,'2007-02- 28'), (38097,'10.10.10.3',10,'2007-02- 28') , ………………………………..;
begin; INSERT INTO a VALUES (1,23),(2,34),(4,33); INSERT INTO a VALUES (8,26),(6,29); ……………………… commit;
3.2.7 慎用union或union all 慎用union或union all，特別是合并后再進(jìn)行排序操作的SQL,碰到數(shù)據(jù)量比較大時(shí)，進(jìn)行優(yōu)化會(huì)非常困難。
3.2.8 優(yōu)化嵌套查詢(xún) 使用連接（JOIN）來(lái)代替子查詢(xún)(Sub-Queries) ，在有的情況下可能能大大提高sql執(zhí)行的速度。
子查詢(xún)： SELECT * FROM customerinfo WHERE CustomerID in (SELECT CustomerID FROM salesinfo ) 改寫(xiě)成： SELECT Ci.* FROM customerinfo ci, salesinfo sf WHERE ci. CustomerID=sf. CustomerID
四， 數(shù)據(jù)庫(kù)有用知識(shí) 4.1 中文排序，大小寫(xiě)字母排序 如果排序時(shí)想?yún)^(qū)分大小寫(xiě)，并按照中文的首字母排序，請(qǐng)這樣定義你的字符字段類(lèi)型，比如：
CREATE TABLE `test1` ( `a` int(11) default NULL, `b` varbinary(30) default NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
排序例子： mysql> select * from test1 order by b; +------+----------+ | a | b | +------+----------+ | 1 | 1111 | | 1 | 22222 | | 1 | AA | | 1 | Ab | | 1 | Az | | 1 | aA | | 1 | aa | | 1 | 阿拉斯加 | | 1 | 地震 | | 1 | 丫頭 | | 1 | 中國(guó) | +------+----------+ 11 rows in set (0.00 sec)
4.2 字符集選擇 Z-SHOP數(shù)據(jù)庫(kù)將使用UTF-8多語(yǔ)言字符集，該字符集可以存儲(chǔ)支持多種語(yǔ)言。如果是linux系統(tǒng)，可以在mysql配置文件/etc/my.cnf的[mysqld]部分添加 default-character-set=utf8
重啟mysql數(shù)據(jù)庫(kù)后，在服務(wù)器上創(chuàng)建的表使用的就是utf8字符集。如果是windows系統(tǒng)，mysql的配置文件是my.ini。客戶(hù)端一般可以選用gbk字符集就能滿(mǎn)足開(kāi)發(fā)要求了。
4.3 存儲(chǔ)引擎選擇 Z-SHOP數(shù)據(jù)庫(kù)將主要使用INNODB存儲(chǔ)引擎，如果是linux系統(tǒng)，可以在mysql配置文件/etc/my.cnf的[mysqld]部分添加 default-storage-engine=innodb 重啟mysql數(shù)據(jù)庫(kù)后，在服務(wù)器上創(chuàng)建的表使用的就是innodb存儲(chǔ)引擎。如果是windows系統(tǒng)，mysql的配置文件是my.ini。 |
|
|
