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