MySQL 規範 (資料庫表設計規範)

A_aliane發表於2019-03-08
MySql資料庫

一、資料庫設計

一般都使用 INNODB 儲存引擎，除非讀寫比率<1%,才考慮使用 MYISAM 儲存引擎;其 他儲存引擎請在 DBA 的建議下使用。

Stored procedure (包括儲存過程，函式，觸發器)對於 MYSQL 來說還不是很成熟， 沒有完善的出錯記錄處理，不建議使用。

UUID()，USER()這樣的MySQL INSIDE 函式對於複製來說是很危險的，會導致主備資料不一致，所以請不要使用。如果一定要使用UUID作為主鍵，讓應用程式來產生。

不要使用外來鍵約束，如果資料存在外來鍵關係，請在程式層面實現。
採用 UTF8 編碼。

二、資料庫物件設計規範

1、表

設計

  • 在設計時儘量包含兩個日期欄位:crt_time(建立日期),upd_time(修改日期)且 非空, 對錶的記錄進行更新的時候，必須包含對 upd_time欄位的更新。
  • 必須要有主鍵，主鍵儘量用自增欄位型別，推薦型別為INT或者BIGINT型別。
  • 需要多表join的欄位，資料型別保持絕對一致。
  • Mysql 的表儘量設定成 KV(Key-Value)結構，這樣便於擴充套件和維護。
  • 當表的欄位數非常多時，可以將表分成兩張表，一張作為條件查詢表，一張作為詳細內容表(主要是為了效能考慮)。
  • 當欄位的型別為列舉型或布林型時，建議使用 char(1)型別。
  • 同一表中，所有varchar欄位的長度加起來，不能大於65535.如果有這樣的需求，請使用 TEXT/LONGTEXT 型別。
  • 由於MYSQL表DDL維護成本很高，所以在適當的時候，可以有一定的欄位容餘。 比如:Value1,Value2,Value3 這樣的欄位。

命名

  • 同一個模組的表儘可能使用相同的字首，表名儘可能表達含義，例如: CRM_SAL_FUND_ITEM。
  • 欄位命名應儘可能使用表達實際含義的英文單詞或縮寫， 如，公司 ID，不要使用:corporation_id, 而用:corp_id 即可。
    *布林值型別的欄位命名為is+描述。如member表上表示是否為enabled的會員的字 段命名為 IsEnabled。

2、索引

命名

  • _ind,各部分以下劃線()分割。
  • 多單片語成的columnname，取前幾個單詞首字母，加末單片語成column_name。如: sample 表 member_id 上的索引:sample_mid_ind。

3. 約束

設計

  • 主鍵最好是無意義的，由Sequence產生的ID欄位，型別為number，不建議使用組合主鍵。
  • 若要達到唯一性限制的效果，不要建立uniqueindex，必須顯式建立普通索引和約束 (pk 或 uk)，即先建立一個以約束名命名的普通索引，然後建立一個約束，用 using index …指定索引。
  • 當刪除約束的時候，為了確保不影響到 index，最好加上 keep index 引數。
  • 主鍵的內容不能被修改。
  • 外來鍵約束一般不在資料庫上建立，只表達一個邏輯的概念，由程式控制。
  • 當萬不得已必須使用外健的話，必須在外健列建立 INDEX。

命名

  • 主鍵約束: pk 結尾，_pk;
  • unique 約束:_uk 結尾，uk;
  • check 約束: _ck 結尾，ck;
  • 外來鍵約束: _fk 結尾，以 pri 連線本表與主表，_pri_fk;

千萬資料表的優化過程

方案一：優化現有mysql資料庫。優點：不影響現有業務，源程式不需要修改程式碼，成本最低。缺點：有優化瓶頸，資料量過億就玩完了。

1.資料庫設計和表建立時就要考慮效能

mysql資料庫本身高度靈活，造成效能不足，嚴重依賴開發人員能力。也就是說開發人員能力高，則mysql效能高。這也是很多關係型資料庫的通病，所以公司的dba通常工資巨高。

設計表時要注意：

  • 表欄位避免null值出現，null值很難查詢優化且佔用額外的索引空間，推薦預設數字0代替null。
  • 儘量使用INT而非BIGINT，如果非負則加上UNSIGNED（這樣數值容量會擴大一倍），當然能使用TINYINT、SMALLINT、MEDIUM_INT更好。
  • 使用列舉或整數代替字串型別
  • 儘量使用TIMESTAMP而非DATETIME
  • 單表不要有太多欄位，建議在20以內
  • 用整型來存IP

索引

  • 索引並不是越多越好，要根據查詢有針對性的建立，考慮在WHERE和ORDER BY命令上涉及的列建立索引，可根據EXPLAIN來檢視是否用了索引還是全表掃描
  • 應儘量避免在WHERE子句中對欄位進行NULL值判斷，否則將導致引擎放棄使用索引而進行全表掃描
  • 值分佈很稀少的欄位不適合建索引，例如"性別"這種只有兩三個值的欄位
  • 字元欄位只建字首索引
  • 字元欄位最好不要做主鍵
  • 不用外來鍵，由程式保證約束
  • 儘量不用UNIQUE，由程式保證約束
  • 使用多列索引時主意順序和查詢條件保持一致，同時刪除不必要的單列索引

簡言之就是使用合適的資料型別，選擇合適的索引

選擇合適的資料型別

  • 使用可存下資料的最小的資料型別，整型 < date,time < char,varchar < blob
    *使用簡單的資料型別，整型比字元處理開銷更小，因為字串的比較更復雜。如，int型別儲存時間型別，bigint型別轉ip函式
  • 使用合理的欄位屬性長度，固定長度的表會更快。使用enum、char而不是varchar
    *儘可能使用not null定義欄位
  • 儘量少用text，非用不可最好分表

選擇合適的索引列

  • 查詢頻繁的列，在where，group by，order by，on從句中出現的列
  • where條件中<，<=，=，>，>=，between，in，以及like 字串+萬用字元（%）出現的列
  • 長度小的列，索引欄位越小越好，因為資料庫的儲存單位是頁，一頁中能存下的資料越多越好
  • 離散度大（不同的值多）的列，放在聯合索引前面。檢視離散度，通過統計不同的列值來實現，count越大，離散程度越高：

2.sql的編寫需要注意優化

  • 1.使用limit對查詢結果的記錄進行限定
  • 2.避免select *，將需要查詢的欄位列出來
  • 3.使用連線（join）來代替子查詢
  • 4.拆分大的delete或insert語句
  • 5.可通過開啟慢查詢日誌來找出較慢的SQL
  • 6.不做列運算：SELECT id WHERE age + 1 = 10，任何對列的操作都將導致表掃描，它包括資料庫教程函式、計算表示式等等，查詢時要儘可能將操作移至等號右邊
  • 7.sql語句儘可能簡單：一條sql只能在一個cpu運算；大語句拆小語句，減少鎖時間；一條大sql可以堵死整個庫
  • 8.OR改寫成IN：OR的效率是n級別，IN的效率是log(n)級別，in的個數建議控制在200以內
  • 9.不用函式和觸發器，在應用程式實現
  • 10.避免%xxx式查詢
  • 11.少用JOIN
  • 12.使用同型別進行比較，比如用'123'和'123'比，123和123比
  • 13.儘量避免在WHERE子句中使用!=或<>操作符，否則將引擎放棄使用索引而進行全表掃描
  • 14.對於連續數值，使用BETWEEN不用IN：SELECT id FROM t WHERE num BETWEEN 1 AND 5
  • 15.列表資料不要拿全表，要使用LIMIT來分頁，每頁數量也不要太大

引擎

目前廣泛使用的是MyISAM和InnoDB兩種引擎：

MyISAM

MyISAM引擎是MySQL 5.1及之前版本的預設引擎，它的特點是：

  • 1.不支援行鎖，讀取時對需要讀到的所有表加鎖，寫入時則對錶加排它鎖
  • 2.不支援事務
  • 3.不支援外來鍵
  • 4.不支援崩潰後的安全恢復
  • 5.在表有讀取查詢的同時，支援往表中插入新紀錄
  • 6.支援BLOB和TEXT的前500個字元索引，支援全文索引
  • 7.支援延遲更新索引，極大提升寫入效能
  • 8.對於不會進行修改的表，支援壓縮表，極大減少磁碟空間佔用

InnoDB

  • InnoDB在MySQL 5.5後成為預設索引，它的特點是：
  • 1.支援行鎖，採用MVCC來支援高併發
  • 2.支援事務
  • 3.支援外來鍵
  • 4.支援崩潰後的安全恢復
  • 5.不支援全文索引

總體來講，MyISAM適合SELECT密集型的表，而InnoDB適合INSERT和UPDATE密集型的表

MyISAM速度可能超快，佔用儲存空間也小，但是程式要求事務支援，故InnoDB是必須的，故該方案無法執行，放棄！

3.分割槽

MySQL在5.1版引入的分割槽是一種簡單的水平拆分，使用者需要在建表的時候加上分割槽引數，對應用是透明的無需修改程式碼

對使用者來說，分割槽表是一個獨立的邏輯表，但是底層由多個物理子表組成，實現分割槽的程式碼實際上是通過對一組底層表的物件封裝，但對SQL層來說是一個完全封裝底層的黑盒子。MySQL實現分割槽的方式也意味著索引也是按照分割槽的子表定義，沒有全域性索引

使用者的SQL語句是需要針對分割槽表做優化，SQL條件中要帶上分割槽條件的列，從而使查詢定位到少量的分割槽上，否則就會掃描全部分割槽，可以通過EXPLAIN PARTITIONS來檢視某條SQL語句會落在那些分割槽上，從而進行SQL優化，我測試，查詢時不帶分割槽條件的列，也會提高速度，故該措施值得一試。

分割槽的好處是：

  • 1.可以讓單表儲存更多的資料
  • 2.分割槽表的資料更容易維護，可以通過清楚整個分割槽批量刪除大量資料，也可以增加新的分割槽來支援新插入的資料。另外，還可以對一個獨立分割槽進行優化、檢查、修復等操作
  • 3.部分查詢能夠從查詢條件確定只落在少數分割槽上，速度會很快
  • 4.分割槽表的資料還可以分佈在不同的物理裝置上，從而搞笑利用多個硬體裝置
  • 5.可以使用分割槽表賴避免某些特殊瓶頸，例如InnoDB單個索引的互斥訪問、ext3檔案系統的inode鎖競爭
  • 6.可以備份和恢復單個分割槽

分割槽的限制和缺點：

  • 1.一個表最多隻能有1024個分割槽
  • 2.如果分割槽欄位中有主鍵或者唯一索引的列，那麼所有主鍵列和唯一索引列都必須包含進來
  • 3.分割槽表無法使用外來鍵約束
  • 4.NULL值會使分割槽過濾無效
  • 5.所有分割槽必須使用相同的儲存引擎

分割槽的型別：

  1. 1.RANGE分割槽：基於屬於一個給定連續區間的列值，把多行分配給分割槽
  2. 2.LIST分割槽：類似於按RANGE分割槽，區別在於LIST分割槽是基於列值匹配一個離散值集合中的某個值來進行選擇
  3. 3.HASH分割槽：基於使用者定義的表示式的返回值來進行選擇的分割槽，該表示式使用將要插入到表中的這些行的列值進行計算。這個函式可以包含MySQL中有效的、產生非負整數值的任何表示式
  4. 4.KEY分割槽：類似於按HASH分割槽，區別在於KEY分割槽只支援計算一列或多列，且MySQL伺服器提供其自身的雜湊函式。必須有一列或多列包含整數值
  5. 5.具體關於mysql分割槽的概念請自行google或查詢官方文件

我首先根據月份把上網記錄表RANGE分割槽了12份，查詢效率提高6倍左右，效果不明顯，故：換id為HASH分割槽，分了64個分割槽，查詢速度提升顯著。問題解決！

結果如下：PARTITION BY HASH (id)PARTITIONS 64

select count() from readroom_website; --11901336行記錄

/ 受影響行數: 0 已找到記錄: 1 警告: 0 持續時間 1 查詢: 5.734 sec. /

select * from readroom_website where month(accesstime) =11 limit 10;

/ 受影響行數: 0 已找到記錄: 10 警告: 0 持續時間 1 查詢: 0.719 sec. */

4.分表

分表就是把一張大表，按照如上過程都優化了，還是查詢卡死，那就把這個表分成多張表，把一次查詢分成多次查詢，然後把結果組合返回給使用者。

分表分為垂直拆分和水平拆分，通常以某個欄位做拆分項。比如以id欄位拆分為100張表： 表名為 tableName_id%100

但：分表需要修改源程式程式碼，會給開發帶來大量工作，極大的增加了開發成本，故：只適合在開發初期就考慮到了大量資料存在，做好了分表處理，不適合應用上線了再做修改，成本太高！！！而且選擇這個方案，都不如選擇我提供的第二第三個方案的成本低！故不建議採用。

5.分庫

把一個資料庫分成多個，建議做個讀寫分離就行了，真正的做分庫也會帶來大量的開發成本，得不償失！不推薦使用。

方案二：升級資料庫型別，換一種100%相容mysql的資料庫。優點：不影響現有業務，源程式不需要修改程式碼，你幾乎不需要做任何操作就能提升資料庫效能，缺點：多花錢

mysql效能不行，那就換個。為保證源程式程式碼不修改，保證現有業務平穩遷移，故需要換一個100%相容mysql的資料庫。

開源選擇

1.tiDB https://github.com/pingcap/tidb

2.Cubrid https://www.cubrid.org/

3.開源資料庫會帶來大量的運維成本且其工業品質和MySQL尚有差距，有很多坑要踩，如果你公司要求必須自建資料庫，那麼選擇該型別產品。

雲資料選擇

1.阿里雲POLARDB

2.https://www.aliyun.com/product/polardb?spm...

官方介紹語：POLARDB 是阿里雲自研的下一代關係型分散式雲原生資料庫，100%相容MySQL，儲存容量最高可達 100T，效能最高提升至 MySQL 的 6 倍。POLARDB 既融合了商業資料庫穩定、可靠、高效能的特徵，又具有開源資料庫簡單、可擴充套件、持續迭代的優勢，而成本只需商用資料庫的 1/10。

1.阿里雲OcenanBase

2.淘寶使用的，扛得住雙十一，效能卓著，但是在公測中，我無法嘗試，但值得期待

3.阿里雲HybridDB for MySQL (原PetaData)

4.https://www.aliyun.com/product/petadata?sp...

官方介紹：雲資料庫HybridDB for MySQL （原名PetaData）是同時支援海量資料線上事務（OLTP）和線上分析（OLAP）的HTAP（Hybrid Transaction/Analytical Processing）關係型資料庫。
。

1.騰訊雲DCDB

2.https://cloud.tencent.com/product/dcdb_for...

官方介紹：DCDB又名TDSQL，一種相容MySQL協議和語法，支援自動水平拆分的高效能分散式資料庫——即業務顯示為完整的邏輯表，資料卻均勻的拆分到多個分片中；每個分片預設採用主備架構，提供災備、恢復、監控、不停機擴容等全套解決方案，適用於TB或PB級的海量資料場景。

方案三：一步到位，大資料解決方案，更換newsql/nosql資料庫。優點：擴充套件性強，成本低，沒有資料容量瓶頸，缺點：需要修改源程式程式碼

資料量過億了，沒得選了，只能上大資料了。

開源解決方案

hadoop家族。hbase/hive懟上就是了。但是有很高的運維成本，一般公司是玩不起的，沒十萬投入是不會有很好的產出的！

雲解決方案

這個就比較多了，也是一種未來趨勢，大資料由專業的公司提供專業的服務，小公司或個人購買服務，大資料就像水/電等公共設施一樣，存在於社會的方方面面。

國內做的最好的當屬阿里雲。

使用超級舒服，按量付費，成本極低。

MaxCompute可以理解為開源的Hive，提供sql/mapreduce/ai演算法/python指令碼/shell指令碼等方式運算元據，資料以表格的形式展現，以分散式方式儲存，採用定時任務和批處理的方式處理資料。DataWorks提供了一種工作流的方式管理你的資料處理任務和排程監控。

相關文章