資料庫結構的優化 - IT人

對查詢需要對多個表進行關聯; 更難進行索引優化###反正規化化的優缺點. 反正規化化的優點：. 可以很好的 ... Togglenavigation IT人 IT人 資料庫結構的優化 Panda_XiaoXi發表於 2019-02-27 資料庫 資料庫設計的步驟： 需求分析：全面瞭解產品設計的儲存需求 邏輯設計：設計資料的邏輯儲存結構 物理設計：根據所使用的資料庫特點進行表結構的設計 維護優化：根據實際的情況對索引、儲存引擎的優化 ###資料庫的正規化： 第一正規化：資料庫中的所有欄位都只是具有單一的屬性 第二正規化：在第一正規化的條件下，要求一個表中只具有一個業務主鍵，每個表只做一件事情 第三正規化：在第二正規化基礎上，消除表中的傳遞依賴關係 一、需求分析及邏輯設計 使用者模組 使用者必須註冊並等咯系統才能進行網上的交易 同一時間一個使用者只能在一個地方登陸 使用者的資訊:{使用者名稱，密碼，手機號，姓名，註冊日期，線上狀態，出生日期} 商品模組 商品資訊:{商品名稱，出版社名稱，圖書的價格，圖書描述，作者} 分類資訊：{分類名稱，分類描述} 商品分類資訊（對應關係表）：{商品名稱，分類名稱} 供應商模組 供應商的資訊：{出版社名稱，地址，電話，聯絡人，銀行賬號} 線上銷售模組 線上銷售所需要的資料：{訂單編號，下單使用者名稱，下單日期，訂單金額，訂單商品分類，訂單商品名，訂單商品的單價，訂單商品數量，支付金額，物流單號} 訂單表：{訂單編號，下單使用者名稱，下單日期，支付金額，物流單號} 訂單商品的關聯表：{訂單編號，訂單商品分類，訂單商品名，商品數量} 考慮效能的問題及商品價格發生變化： 商品資訊表的反正規化化設計： 商品資訊:{商品名稱，分類名稱，出版社名稱，圖書的價格，圖書描述，作者} 分類資訊：{分類名稱，分類描述} 線上銷售表的反正規化化設計： 訂單表：{訂單編號，下單使用者名稱，手機號，下單日期，支付金額，物流單號，訂單金額} 訂單商品的關聯表：{訂單編號，訂單商品分類，訂單商品名，商品數量，商品單價} 反正規化化後的查詢每個使用者的訂單的總金額的SQL語句： select下單使用者名稱,sum(訂單金額)from訂單表groupby下單使用者名稱； 反正規化化後的查詢下單使用者和訂單詳情的SQL語句： selecta.訂單編號，a.使用者名稱，a.手機號，b.商品名稱，b.商品的單價，b.商品數量from訂單表ajoin訂單商品的關聯表bona.訂單編號=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-DDHH:MM:SS[.fraction]格式儲存資料 datatime型別與時區無關，佔用8個位元組儲存空間 儲存的時間範圍：1000-01-0100：00:00到9999-12-3123:59:59 timestamp型別儲存從1970年1月1日到當前的秒數，以YYYY-MM-DDHH: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的主鍵和業務主鍵可以不同 建立資料庫結構 維護優化資料庫 相關文章 Multidex（二）之Dex預載入優化 2020-11-22 分支結構 2020-11-22 如何解決邏輯刪除與資料庫唯一約束衝突 2020-11-20 資料庫 資料結構與演算法-PTA選擇判斷複習題（持續更新中） 2020-11-21 演算法資料結構 軟體體系結構評估 2020-11-21 【c++】結構體sort排序 2020-11-21 C++ Keras輸出網路結構圖 2020-11-21 Keras mongodb核心原始碼實現、效能調優系列-為何要對開源mongodb資料庫核心做二次開發 2020-11-25 資料庫MongoDB 檔案的邏輯結構、檔案目錄 2020-11-21 記一次使用策略模式優化程式碼的經歷 2020-11-21 SQLServer批量生成資料庫內多個表的表結構 2020-11-21 資料庫SQL 結構體記憶體對齊 2020-11-21 13、nginx服務叢集搭建以及優化 2020-11-21 html常用編寫軟體以及基本結構 2020-11-22 MySQL必知必會：用十一張圖講清楚，當你CRUD時BufferPool中發生了什麼！以及BufferPool的優化！ 2020-11-22 MySQL ClickHouse資料庫資料定義手記之資料型別 2020-11-22 資料庫 Mysql資料庫之多表查詢、事務、DCL 2020-11-22 資料庫MySQL Redis資料結構之整數集合 2020-11-22 資料結構Redis 【資料結構】二叉樹的建立與遍歷 2020-11-22 演算法資料結構 02-線性結構3ReversingLinkedList(25分) 2020-11-22 最新文章 思邁特軟體Smartbi：資料分析的作用及基本原則 Mac最強CAD設計平臺 Matlab+Qt開發筆記（二）：Qt開啟mat檔案顯示讀取的資料 頁面靜態化技術演進 系統架構面臨的三大挑戰，看Kubernetes監控如何解決？ 積跬步至千里：QUIC協議在螞蟻集團落地之綜述 聊聊自定義實現的SPI如何與spring進行整合 程式設計師避坑指南36條 前端面試每日3+1——第932天 Hadoop原理與原始碼 SourceCodeReadingforVue3:Howdoes`hasChanged`work? Flutter保護你的APP資料安全