Mysql第四天 資料庫設計

標籤：

不考慮主備。叢集等方案，基於業務上的設計主要是表結構及表間關係的設計。

而關於表中欄位主要是依據業務來進行定義，我們能夠指定的大概有這麼幾項：

• 儲存引擎 一般用InnoDB,特殊需求特殊選用
• 字元集和校正規則
  特別說一下校正規則是指兩個字元之間的比較規則， 比方A=a的話就是不區分大寫和小寫，會影響order by等。 bin通常是區分大寫和小寫， 一般用general
• 欄位定義 欄位怎麼選取類型
• 索引 後面再說
• 特殊用途表 比方做緩衝，匯總等

欄位的資料類型選擇三個原則：

• 更小的資料類型。 比方能用tiny int就不用int
• 更簡單的資料類型， int比varchar要簡單，會用到更少的磁碟以及操作時所須要的CPU。再比方用int來儲存ip
• 盡量避免null. 盡量用not null語句。 null會帶來額外的儲存空間，加索引後也須要特殊的處理。

整數

• [UNSIGNED] TINYINT, SMALLINT, INT,BIGINT. 範圍越來越大。

  
  顯然越小的越省空間

• 能夠指定寬度 INT(11). 這個僅僅是互動工具的顯示寬度。跟實際範圍無關。定義時能夠不指定，還能提升效率
• 建表是能夠選擇zerofill的

實數

• float和double是不精確的類型
• 能夠指定精度 double(12,4)是全位元和小數位元，由於在插入的時候超過部分會進行四捨五入，因此建議不指定。

• 另外。使用浮點數由於要轉化為2進位表示再進行儲存或者計算所以可能造成精度問題
  比方

update biz_pay_task set order_price = 131.07232;// 查出的值將會是 131.07233

• decimal用於儲存精確的小數，相同情況下會比浮點型的佔用個多儲存範圍。計算的時候也會轉化為double,因此非必要不用
• 再設計上還能夠考慮使用bigint取代decimal.

字串類型

• CHAR是定長的，因此在頻繁更新的時候不easy產生片段
• CHAR適合儲存MD5這種結果是定長的資料
• CHAR適合儲存小位元組。比方標誌位等。比VARCHAR更省空間
• VCHAR是變長的，頻繁更新會有片段
• BINARY 是二進位字串，當中是二進位的字面表達，排序等等會轉化為位元進行
• IP地址。 這個能夠特殊對待。使用INET_ATON()和INET_NTOA()來儲存ip地址為無符號數

時間類型

• DATETIME 19位標準顯示， 能夠使用date_format進行結構化查詢
• TIMESTAMP 19位顯示，範圍比DATETIME小，可是省空間，不能為NULL。

• TIMESTAMP能夠設定自己主動更新。這樣非常適合做為updatetime這種欄位

主外鍵主鍵

• 由於主鍵回作為索引。越緊湊越小越好，事實上也就是越好排序越好。
• 有的人可能會想使用uuid,可是由於較長，最好使用UNHEX()函數改為數字，存入BINARY中，檢索的時候使用HEX()方法再轉為十六進位格式

外鍵

• 能夠設定刪除外鍵的約束行為 預設報錯。 cascade相同刪除。 no action 什麼也不做，可是會破壞一致性。
• 另外能夠使用set foreign_key_checks=0 能夠臨時關閉檢查，這樣在諸如備份這種特殊操作的時候能夠加快效能。

表欄位外，怎樣對錶進行分割以及劃分是範式主要討論的問題

三大範式

由於五範式的有用性太低，僅僅考慮三大範式
來一張學生選課表
Student_Course(studentId, studentName, collegeId, collegeName, courseId, courseName, credit)

第一範式 列中的值不可分割

上面假設一個學生選了多門課，我們有例如以下的辦法： courseName中用,號分割。

這顯然不能滿足第一範式了。

我們還有個辦法就是使用(studentId， courseId)來作為這個聯合主鍵。這樣就會有非常多反覆行了。
這個也是經典的多對多關係引起的問題。

第二範式 消除部分依賴

能夠覺得是拆分一個多對多為兩個1對多
上面的資料studentName 部分依賴於(studentId, courseId)
會引入例如以下的問題：

• 資料冗餘： 假設一個人選擇了N門課。就會造成studentName, collegueId, collegueName,courseName, credit這些都反覆n次
• easy更新錯誤，比方假設改動了credit，就須要改動非常多行
• 假設新開了一門課程，假設沒人選修的話就不能插入
• 假設一個課程沒人選修，那麼會造成課程也被刪除了。
  改動之後的設計：
  Student(studentId, studentName, collegeId, collegeName)
  Course( courseId, courseName, credit1)
  Student_Course(studentId, courseId)

第三範式 消除傳遞依賴

傳遞依賴跟部分依賴非常easy混淆。會跟本表適用於做什麼的有非常大的關係
這部分的主要目的是進一步去除反覆資料，提出1對多
比方上面的學生課程表。 其主碼顯然是studentId和courseId。 這樣非常easy推斷出部分依賴
在第二範式分解之後的student表中， 學生資訊的主鍵應該是studentId,另外除他以外有一個不能作為主鍵，可是卻有可能是另外欄位所以來的碼為的欄位：collegeId。這樣StudentId->collegeId->collegeName。這就是傳遞依賴。

進一步分割之後：
Student(studentId, studentName, collegeId)
Collegue(collegeId, collegeName)

範式與反範式

範式的優缺點：
- 降低反覆
- 更快的更新
- 更少的須要group by等語句
- 缺點：查詢時會涉及很多其它的關聯

反範式優缺點：
- 缺點：冗餘行及有可能更新錯誤
- 不須要關聯

一些取捨
有時是須要混用範式和反範式的。 特別在一些須要額外的欄位進行索引，統計及排序的情況下。

這樣可能會帶來更新上的麻煩。須要依據實際情況詳細權衡。

其它一些應用

• 匯總表 通常是定時的計算一些匯總資訊，報表系統使用比較多
• 緩衝表 比方使用MyISAM引擎建立該表。留作建立索引。這樣就能夠把全部可能用作索引的欄位單獨提出在一個表中，加快索引。

  這種情況分表的技術中也可能會用到

• 計數器表

CREATE TABLE counter(    cnt int unsigned not null DEFAULT 0) ENGINE = InnoDB;

假設是插入的時候每次遞增1。這樣就會每次都會對這一行進行獨佔鎖定。比較好的解決方案：

CREATE TABLE counter(    slot tinyint unsigned not null primary key,    cnt int unsigned not null DEFAULT 0) ENGINE = InnoDB;UPDATE hit_counter SET cnt = cnt + 1 where slot = FLOOR(RAND() * 100);

然後插入100行預設的資料
然後更新的時候就能夠盡量少的避免並發鎖行
就能夠使用SUM欄位算出總的點擊量

假設須要每天都計算的話，那麼可能的表結構為：

CREATE TABLE counter(    day date not null;    slot tinyint unsigned not null,    cnt int unsigned not null DEFAULT 0,    primary key(day, slot)) ENGINE = InnoDB;INSERT INTO counter VALUES(CURRENT_DATE, FLOAT(RAND() * 100), 1) ON DUPLICATE KEY UPDATE cnt = cnt + 1;

ON DUPLICATE KEY,假設出現了反覆的key則更新則不是新增。

##加快DDL
DDL會堵塞服務。因此應該越快越好。
一般的方式有該備庫切庫。

又一次建立一個表，該表之後重名民
能夠通過物化視圖facebook的工具來動態改動

Mysql第四天 資料庫設計