mysql開發規範文檔

Author：sakte

Time：2013/02/27

 

mysql開發規範文檔

 

1.mysql資料庫命名規範

2.表結構設計、資料類型選擇

3.開發注意事項

 

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

 

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----