資料庫結構的優化

Panda_XiaoXi發表於2019-02-27
資料庫優化

資料庫設計的步驟：

  1. 需求分析：全面瞭解產品設計的儲存需求
  2. 邏輯設計：設計資料的邏輯儲存結構
  3. 物理設計：根據所使用的資料庫特點進行表結構的設計
  4. 維護優化：根據實際的情況對索引、儲存引擎的優化 ###資料庫的正規化：
  5. 第一正規化：資料庫中的所有欄位都只是具有單一的屬性
  6. 第二正規化：在第一正規化的條件下，要求一個表中只具有一個業務主鍵，每個表只做一件事情
  7. 第三正規化：在第二正規化基礎上，消除表中的傳遞依賴關係

一、需求分析及邏輯設計

使用者模組
  • 使用者必須註冊並等咯系統才能進行網上的交易
  • 同一時間一個使用者只能在一個地方登陸
  • 使用者的資訊:{使用者名稱，密碼，手機號，姓名，註冊日期，線上狀態，出生日期}
商品模組
  • 商品資訊:{商品名稱，出版社名稱，圖書的價格，圖書描述，作者}
  • 分類資訊：{分類名稱，分類描述}
  • 商品分類資訊（對應關係表）：{商品名稱，分類名稱}
供應商模組
  • 供應商的資訊：{出版社名稱，地址，電話，聯絡人，銀行賬號}
線上銷售模組
  • 線上銷售所需要的資料：{訂單編號，下單使用者名稱，下單日期，訂單金額，訂單商品分類，訂單商品名，訂單商品的單價，訂單商品數量，支付金額，物流單號}
  • 訂單表：{訂單編號，下單使用者名稱，下單日期，支付金額，物流單號}
  • 訂單商品的關聯表：{訂單編號，訂單商品分類，訂單商品名，商品數量}
考慮效能的問題及商品價格發生變化：

  • 商品資訊表的反正規化化設計：

    商品資訊:{商品名稱，分類名稱，出版社名稱，圖書的價格，圖書描述，作者}

    分類資訊：{分類名稱，分類描述}

  • 線上銷售表的反正規化化設計：

    訂單表：{訂單編號，下單使用者名稱，手機號，下單日期，支付金額，物流單號，訂單金額}

    訂單商品的關聯表：{訂單編號，訂單商品分類，訂單商品名，商品數量，商品單價}

  • 反正規化化後的查詢每個使用者的訂單的總金額的SQL語句：

    select 下單使用者名稱,sum(訂單金額) from 訂單表 group by 下單使用者名稱；

  • 反正規化化後的查詢下單使用者和訂單詳情的SQL語句：

    select a.訂單編號，a.使用者名稱，a.手機號，b.商品名稱，b.商品的單價，b.商品數量 from 訂單表 a join 訂單商品的關聯表 b on a.訂單編號=b.訂單編號； ###正規化化的優缺點

正規化化的優點：
  • 可以儘量的減少冗餘資料
  • 正規化的更新操作比反正規化化要快
  • 正規化化的表通常比反正規化化的表更小
正規化化的缺點：
  • 對查詢需要對多個表進行關聯
  • 更難進行索引優化 ###反正規化化的優缺點
反正規化化的優點：
  • 可以很好的減少表的關聯
  • 可以對查詢進行索引優化
反正規化化的缺點：
  • 存在資料冗餘及資料維護異常
  • 對資料的修改需要更多的成本

二、資料庫的物理設計階段

  • 定義資料庫、表及欄位的命名規範（可讀性原則、表意性原則、長名原則）
  • 選擇合適的儲存引擎

儲存引擎對比

  • 為表中的欄位選擇合適的資料型別（當一個列可以選擇多種資料型別時，應該優先考慮數字型別，其次是日期型別或者二進位制型別，最後是字元型別。對於相同級別的資料型別，應該優先選擇佔用空間小的資料型別）

    整數型別：

      tinyint(1個位元組)、smallint(2個位元組)、
  mediumint(3個位元組)、int(4個位元組)、bigint(8個位元組)
複製程式碼

    實數型別：

      float(4個位元組，不為精確型別)、double(8個位元組，不為精確型別)、
  decimal(每4個位元組存9個數字，小數點佔一個位元組，為精確型別)
複製程式碼

    varchar和char型別：

      varchar型別儲存特點：
  	用於儲存變長字串，只佔用必要的儲存空間；
  	列的最大長度小於255時則只佔用一個額外位元組用於記錄字串長度；
  	列的最大長度大於255則，要佔用兩個額外位元組用於記錄字串長度

  如何對varchar列選擇合適的寬度：

  	使用最小的符合需求的長度；
  	varchar(5)和varchar(200)儲存MySQL字串效能不同

  varchar的適用場景：

  	字串列的最大長度比平均長度大很多；
  	字串列很少被更新的字串的列；
  	使用了多字符集儲存字串

  char型別儲存特點：

  	char型別是定長的；
  	字串儲存在char型別的列中會刪除末尾的空格；
  	char型別儲存的最大的寬度是255

  char型別的適用的場景：

  	適合儲存所長度近似的值（eg:md5的值、手機號、身份證號）
  	適合儲存長度短小的字串
  	適合儲存儲存經常更新的字串
複製程式碼

    日期型別：

      datatime型別以YYYY-MM-DD HH:MM:SS[.fraction]格式儲存資料
  datatime型別與時區無關，佔用8個位元組儲存空間
  儲存的時間範圍：1000-01-01 00：00:00到9999-12-31 23:59:59

  timestamp型別儲存從1970年1月1日到當前的秒數，以YYYY-MM-DD HH:MM:SS[.fraction]顯示，佔用4個位元組儲存空間
  timestamp型別顯示依賴於所指定的時區
  timestamp型別在行資料修改時可以自動修改timestamp列的值
  timestamp儲存的時間範圍1970-01-01到2038-01-19

  date型別和time型別（mysql5.7之後加入）：
  	date型別佔用的位元組數比使用字串、datatime、int儲存要少，使用date型別只需要3個位元組；
  	date型別使用Date型別還可以利用日期時間函式進行日期之間的計算；
  	date型別儲存的日期範圍1000-01-01到9999-12-31之間的日期

  	time型別用於儲存時間資料：HH:MM:SS
  儲存日期時間型別的注意事項：
  	不要使用字串型別來儲存日期時間資料；
  		日期時間型別通常比字串型別所佔用的儲存空間小；
  		日期時間型別在進行查詢過濾時可以利用日期來進行對比；
  		日期時間型別有豐富的處理函式，可以方便的對時間型別的進行日期計算
  	使用Int儲存日期時間不如使用Timestamp型別
複製程式碼

    InnoDB如何選擇主鍵： 主鍵應該儘可能的小； 主鍵應該是順序增長的（以減少隨機IO），增加資料的插入效率； InnoDB的主鍵和業務主鍵可以不同

  • 建立資料庫結構

  • 維護優化資料庫

相關文章