mysql開發規范文檔 1.mysql數據庫命名規范 2.表結構設計、數據類型選擇 3.開發注意事項 www.2cto.com 1.mysql數據庫命名規范 1.1 命名規范： Table/view/procedure/function/package： object類型簡稱+功能模塊簡稱的小寫字母+“_”+業務意義小寫單詞，如: 主鍵/外鍵/索引： object類型簡稱+”_”+表名簡稱+字段名 object類型簡稱： Table：t View：v Procedure：p Function：fn Package：pk 主鍵 :pk 外鍵 :fk 索引 :idx www.2cto.com Eg: Trader後台功能：tbk_operate_log 搜索功能表：ts_operate_log 索引命令：idx_tablename_column 2.表結構設計、數據類型選擇 2.1 常用數據類型： 整型:tinyint,smallint,mediumint,int,bigint。一般int就夠用了 浮點型：DECIMAL(M,D)精准，不建議用DECIMAL，建議乘以固定倍數轉換成整數存儲，可以節省存儲空間，且不會帶來任何附加維護成本 TINYINT>SMALLINT>MEDIUMINT>INT>BIGINT>DECIMAL(存儲空間逐漸變大,而性能卻逐漸變小)。 自增序列類型的字段只能使用int或bigint，且明確標識出無符號型（unsigned），當該字段超過42億時，才使用bigint 字符型：varchar，char，ENUM和SET，text 字符列選擇類型時，盡量不要使用TEXT數據類型，lob類型更是要堅決杜絕，僅當字符數超過20000時，可以采用text類型，且所有使用text類型的字段，必須和原表拆分，與原表主鍵單獨存儲在另外一個表裡。它的處理方式決定了它的性能要低於char或者是varchar類型的處理。定長字段，建議使用CHAR類型，不定長字段盡量使用VARCHAR，且僅僅設定適當的最大長度，而不是非常隨意的給一個很大的最大長度限定，因為不同的長度范圍，MySQL也會有不一樣的存儲處理。對於狀態字段，可以采用char類型，也可以嘗試使用ENUM來存放，因為可以極大的降低存儲空間，而且即使需要增加新的類型，只要增加於末尾，修改結構也不需要重建表數據。如果是存放可預先定義的屬性數據呢？可以嘗試使用SET類型，即使存在多種屬性，同樣可以游刃有余，同時還可以節省不小的存儲空間。 日期時間：常用TIMESTAMP，date 需要精確（年月日時分秒）的時間字段，可以使用datetime，timestamp ；如果時間字段只需要精確到天，那就用date類型 2.2 表結構基本設計： 2.2.1.字段字段使用not null： MySQL NULL類型和Oracle的NULL有差異，會進入索引中，如果是一個組合索引，那麼這個NULL類型的字段會極大影響整個索引的效率。此外，NULL 在索引中的處理也是特殊的，也會占用額外的存放空間 2.2.2.適當的拆分/冗余 A.當我們的表中存在類似於 TEXT 或者是很大的 VARCHAR類型的大字段的時候，如果我們大部分訪問這張表的時候都不需要這個字段，我們就該義無反顧的將其拆分到另外的獨立表中，以減少常用數據所占用的存儲空間。這樣做的一個明顯好處就是每個數據塊中可以存儲的數據條數可以大大增加，既減少物理 IO 次數，也能大大提高內存中的緩存命中率。 B.被頻繁引用且只能通過Join 2張（或者更多）大表的方式才能得到的獨立小字段,這樣的場景由於每次Join僅僅只是為了取得某個小字段的值，Join到的記錄又大，會造成大量不必要的IO，完全可以通過空間換取時間的方式來優化。不過，冗余的同時需要確保數據的一致性不會遭到破壞，確保更新的同時冗余字段也被更新。 2.2.3.控制表的大小 mysql在處理大表（char的表>500W行，或int表>1000W）時，性能就開始明顯降低，所以要采用不同的方式控制單表容量 A：根據數據冷熱，對數據分級存儲，歷史歸檔 B：采用分庫/分表/分區表，橫向拆分控制單表容量 C：對於OLTP系統，控制單事務的資源消耗，遇到大事務可以拆解，采用化整為零模式，避免特例影響大眾 D：單庫不要超過500個表 E：單表字段數不要太多，最多不要大於50個 2.2.4.表的定義參數 mysql> show create table utf8\G; *************************** 1. row *************************** Table: utf8 Create Table: CREATE TABLE `utf8` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(10) DEFAULT NULL, `createtime` timestamp NULL DEFAULT NULL, `istrue` tinyint(4) DEFAULT '1', `a` decimal(10,0) DEFAULT NULL, `b` decimal(10,4) DEFAULT NULL, `c` datetime DEFAULT NULL, `d` date DEFAULT NULL, `e` enum('a','b','c') DEFAULT NULL, `f` set('a','b','c') DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `name_UNIQUE` (`name`), UNIQUE KEY `createtime_UNIQUE` (`createtime`) ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 ROW_FORMAT=dynamic engine：根據自己的業務需要選擇合適的存儲引擎，一般事務表選擇innodb，只讀表選擇myisam AUTO_INCREMENT：自增列的初始化值 CHARSET：根據自己業務需求，定義表的字符集，對於多種語言環境選擇utf8 ROW_FORMAT:行的存儲格式 說明： mysql文件存儲格式 antelope Barracuda mysql行存儲格式 antelope：提供compact和redundant兩種行格式，redundant為兼容之前版本保留的。默認情況下保存的格式為compact格式 Barracuda：Innodb plugin引入新的文件格式，Barracuda文件格式包含兩種新的行記錄格式：Dynamic和Compressed。Compressed對於字符類型數據有很好壓縮功能，可以提高效率 2.2.5.創建合適所索引 索引需要額外的維護成本、訪問成本和空間成本，所以創建索引一定要謹慎，使單個索引盡量覆蓋多的sql，更新頻率比較高的表要控制索引的數量。 A. 對於非常大更新量的數據，索引的維護成本會非常高，如果其檢索需求很少，而且對檢索效率並沒有非常高的要求的時候，並不建議創建索引，或者是盡量減少索引。 B. 對於數據量極小到通過索引檢索還不如直接遍歷來得快的數據，也並不適合使用索引。 C. 應該盡量讓查找條件盡可能多的在索引中，盡可能通過索引完成所有過濾，回表只是取出額外的數據字段。 D. 字段的順序對組合索引效率有至關重要的作用，過濾效果越好的字段需要更靠前 E. 需要讀取的數據量占整個數據量的比例較大或者說索引的過濾效果並不是太好的時候，使用索引並不一定優於全表掃描。 F. 在實際使用過程中，一次數據訪問一般只能利用1個索引，這一點在索引創建過程中一定要注意，不是說一條SQL語句中Where子句裡面每個條件都有索引能對應上就可以了. G．在高並發環境不要使用外鍵，太容易產生死鎖，應由程序保證約束 H．字符字段必須使用前綴索引。 3. 開發注意事項 3.1 不在索引列上進行數學運算或函數運算 3.2 避免大sql，拆解多個小sql 3.3 避免是用select * 3.4 用in() /union替換or，並注意in的個數小於300 3.5 避免使用%前綴模糊前綴查詢 3.6 避免使用子查詢 ----end----
