表的設計與優化 · mysql數據庫系列

# 單表設計與優化 ## 1）設計規范化表，消除數據冗余（以使用正確字段類型最明顯）：數據庫范式是確保數據庫結構合理，滿足各種查詢需要、避免數據庫操作異常的數據庫設計方式。滿足范式要求的表，稱為規范化表，范式產生于20世紀70年代初，一般表設計滿足前三范式就可以，在這里簡單介紹一下前三范式。第一范式（1NF）無重復的列所謂第一范式（1NF）是指在關系模型中，對域添加的一個規范要求，所有的域都應該是原子性的，即數據庫表的每一列都是不可分割的原子數據項，而不能是集合，數組，記錄等非原子數據項。第二范式（2NF）屬性在1NF的基礎上，非碼屬性必須完全依賴于碼[在1NF基礎上消除非主屬性對主碼的部分函數依賴] 第三范式（3NF）屬性在1NF基礎上，任何非主屬性不依賴于其它非主屬性[在2NF基礎上消除傳遞依賴。通俗點講：第一范式：屬性(字段)的原子性約束，要求屬性具有原子性，不可再分割；第二范式：記錄的惟一性約束，要求記錄有惟一標識，每條記錄需要有一個屬性來做為實體的唯一標識，即每列都要和主鍵相關。第三范式：屬性(字段)冗余性的約束，即任何字段不能由其他字段派生出來，在通俗點就是：主鍵沒有直接關系的數據列必須消除(消除的辦法就是再創建一個表來存放他們，當然外鍵除外)。即：確保每列都和主鍵列直接相關,而不是間接相關。如果數據庫設計達到了完全的標準化，則把所有的表通過關鍵字連接在一起時，不會出現任何數據的復本(repetition)。標準化的優點是明顯的，它避免了數據冗余，自然就節省了空間，也對數據的一致性(consistency)提供了根本的保障，杜絕了數據不一致的現象，同時也提高了效率。 ## （2）適當的冗余，增加計算列：（實際開發中必須思考的點）數據庫設計的實用原則是：在數據冗余和處理速度之間找到合適的平衡點。滿足范式的表一定是規范化的表，但不一定是最佳的設計。很多情況下會為了提高數據庫的運行效率，常常需要降低范式標準：適當增加冗余，達到以空間換時間的目的。比如我們有一個表，產品名稱，單價，庫存量，總價值。這個表是不滿足第三范式的，因為“總價值”可以由“單價”乘以“數量”得到，說明“金額”是冗余字段。但是，增加“總價值”這個冗余字段，可以提高查詢統計的速度，這就是以空間換時間的作法。合理的冗余可以分散數據量大的表的并發壓力，也可以加快特殊查詢的速度，冗余字段可以有效減少數據庫表的連接，提高效率。其中”總價值”就是一個計算列，在數據庫中有兩種類型：數據列和計算列，數據列就是需要我們手動或者程序給予賦值的列，計算列是源于表中其他的數據計算得來，比如這里的”總價值” 在SQL中創建計算列： ``` create table goods( id int auto_increment not null, c1 int, c2 int, c3 int as (c1+c2), //這個就是計算列啦 primary key(id) ) ``` ## （3）索引的設計：表優化的重要途徑，百萬級別的表沒有索引，注定卡死。 MySQL的分區 MySQL分區表是在數據庫層面，MySQL自己實現的分表功能，在很大程度上簡化了分表的難度。物理存儲上分區存儲,每個分區有獨立的文件,應用程序上還是一張表Range（范圍）–這種模式允許將數據劃分不同范圍。例如可以將一個表通過年份劃分成若干個分區。MySQL5.1開始支持表分區 RANGE分區 -根據范圍分區，范圍應該連續但是不重疊，使用PARTITION BY RANGE, VALUES LESS THAN關鍵字。不使用COLUMNS關鍵字時RANGE括號內必須為整數字段名或返回確定整數的函數。 List（預定義列表）–這種模式允許系統通過預定義的列表的值來對數據進行分割。 Hash（哈希）–這中模式允許通過對表的一個或多個列的HashKey進行計算，最后通過這個Hash碼不同數值對應的數據區域進行分區。例如可以建立一個對表主鍵進行分區的表。 Key（鍵值）-上面Hash模式的一種延伸，這里的Hash Key是MySQL系統產生的。分區的好處是：可以讓單表存儲更多的數據分區表的數據更容易維護，可以通過清楚整個分區批量刪除大量數據，也可以增加新的分區來支持新插入的數據。另外，還可以對一個獨立分區進行優化、檢查、修復等操作部分查詢能夠從查詢條件確定只落在少數分區上，速度會很快分區表的數據還可以分布在不同的物理設備上，從而搞笑利用多個硬件設備可以使用分區表賴避免某些特殊瓶頸，例如InnoDB單個索引的互斥訪問、ext3文件系統的inode鎖競爭可以備份和恢復單個分區分區的限制和缺點：一個表最多只能有1024個分區如果分區字段中有主鍵或者唯一索引的列，那么所有主鍵列和唯一索引列都必須包含進來分區表無法使用外鍵約束 NULL值會使分區過濾無效所有分區必須使用相同的存儲引擎 MySQL從5.1開始支持分區功能。分區一句話就是：把一張表按照某種規則（range/list/hash/key等）分成多個區域（頁/文件）保存。 MySQL的分表為什么我們需要分表：當一張的數據達到幾百萬時，你查詢一次所花的時間會變多，如果有聯合查詢的話，我想有可能會死在那兒了。分表的目的就在于此，減小數據庫的負擔，縮短查詢時間。 mysql中有一種機制是表鎖定和行鎖定，為什么要出現這種機制，是為了保證數據的完整性，我舉個例子來說吧，如果有二個sql都要修改同一張表的同一條數據，這個時候怎么辦呢，是不是二個sql都可以同時修改這條數據呢？很顯然mysql對這種情況的處理是，一種是表鎖定（myisam存儲引擎），一個是行鎖定（innodb存儲引擎）。表鎖定表示你們都不能對這張表進行操作，必須等我對表操作完才行。行鎖定也一樣，別的sql必須等我對這條數據操作完了，才能對這條數據進行操作。如果數據太多，一次執行的時間太長，等待的時間就越長，這也是我們為什么要分表的原因。單庫數據庫-->數據庫讀寫分離-->緩存技術-->搜索技術-->數據的垂直拆分-->數據的水平拆分 MySQL的分庫大型網站解決存儲瓶頸的問題，我們要找準存儲這個關鍵點，因為數據庫其實是存儲和運算的組合體，但是在我們這個場景下，存儲是第一位的，當存儲是瓶頸時候我們要狠下心來盡量多的拋棄數據的計算特點，所以上文中我提出我們數據庫就不要濫用計算功能了例如觸發器、存儲過程等等。數據庫剝離計算功能不代表不要數據的計算功能，因為沒有數據的計算功能數據庫也就沒價值了，那么我們要將數據庫的計算功能進行遷移，遷移到程序里面，一般大型系統程序和數據庫都是分開部署到不同服務器上，因此程序里處理數據計算就不會影響到數據庫所在服務器的性能，就可以讓安裝數據庫的服務器專心服務于存儲。我們要盡一切可能的把數據庫的變化對服務層的影響降到最低，最好是數據庫做拆分后，現有業務不要任何的更改，那么我們就得設計一個全新的數據訪問層，這個數據訪問層將數據庫和服務層進行解耦，任何數據庫的變化都由數據訪問層消化，數據訪問層對外接口要高度統一，不要輕易改變。如果我們設計了數據訪問層來解決數據庫拆分的問題，數據訪問層加上數據庫其實就組合出了一個分布式數據庫的解決方案，由此可見拆分數據庫的難度是很高的，因為數據庫將擁有分布式的特性，而分布式開發就意味開發難度的增加。對于分布式事務的處理，我們盡量要從具體問題具體分析，不要一感覺這個事務操作本質是分布式事務就去尋找通用的分布式事務技術手段，這樣的想法其實是回避困難的思想，結果可能會是把問題搞得更加復雜。