【推薦】MySQL資料庫設計SQL規範

来源:https://www.cnblogs.com/satcon/archive/2023/02/02/17086424.html
-Advertisement-
Play Games

1 命名規範 1、【強制】庫名、表名、欄位名必須使用小寫字母並採用下劃線分割,禁止拼音英文混用;(禁用-,-相當於運算符) 2、【建議】庫名、表名、欄位名在滿足業務需求的條件下使用最小長度; 如information --> info;address --> addr等 3、【強制】庫名、表名、欄位 ...


1 命名規範

1、【強制】庫名、表名、欄位名必須使用小寫字母並採用下劃線分割,禁止拼音英文混用;(禁用-,-相當於運算符)

2、【建議】庫名、表名、欄位名在滿足業務需求的條件下使用最小長度;

如information --> info;address --> addr等

3、【強制】庫名、表名、欄位名禁止使用MySQL保留關鍵字,如from,table等

詳見https://dev.mysql.com/doc/refman/5.7/en/keywords.html

4、【強制】臨時庫、臨時表名必須以tmp為首碼並以日期為尾碼,例如tmp_user_20201231;

5、【強制】備份庫、備份表名必須以bak為首碼並以日期為尾碼,例如bak_user_20201231;

6、【強制】非唯一索引命名idx_欄位1_欄位名2,唯一索引uniq_欄位名1_欄位名2;

2 基本規範

1、【強制】使用INNODB存儲引擎

支持事務、行級鎖、併發性能更好、CPU及記憶體緩存頁優化使得資源利用率更高;

2、【強制】使用UTF8或UTF8MB4字元集;

萬國碼,無需轉碼,無亂碼風險,節省空間;

3、【強制】表、欄位必須有comments(中文註釋);

中文註釋信息必須保證完整、明確和準確;表和欄位含義發生變更時,comments(中文註釋)必須做同步修改;

4、【強制】不在資料庫中存儲圖片、文件等大數據;

系統對資料庫的讀/寫速度 < 系統對文件的直接處理速度
資料庫對大數據欄位的處理,效率不高

5、【強制】禁止線上上做資料庫壓力測試;

6、【強制】禁止使用存儲過程、視圖、觸發器、Event;

跨庫查詢,視圖等可以考慮用寬表查詢

3 庫表設計規範

1、【強制】表必須有主鍵,例如自增主鍵,使用int或bigint,具體看預估業務量;

主鍵遞增,數據行寫入可以提高插入性能,可以避免page分裂,減少表碎片提升空間和記憶體的使用;
主鍵要選擇較短的數據類型, Innodb引擎普通索引都會保存主鍵的值,較短的數據類型可以有效的減少索引的磁碟空間,提高索引的緩存效率;
無主鍵的表刪除,在row模式的主從架構,會導致備庫夯住;

2、【建議】單表欄位數目建議不要過多,建議不要超過64

單表欄位數太多會使得MySQL處理InnoDB返回數據之間的映射成本太高。

3、【強制】禁止使用外鍵,如果有外鍵完整性約束,需要應用程式控制

外鍵用來保護參照完整性,可在業務端實現,對父表和子表的操作會相互影響,降低可用性,甚至會造成死鎖。

4.【建議】所有表要有如下系統欄位,且按照如下順序

Name

Code

DataType

Length

Not Null

Default

主鍵

id

Bigint或int

 

(表中的第一個欄位)

……

 

 

 

其他業務欄位

刪除標識

is_delete

Tinyint

1

0(未刪除)

創建時間

create_time

DateTime

 

記錄創建時間

更新時間

update_time

DateTime

 

記錄更新時間

創建人

create_user

Varchar(50)

50

 

更新人

update_user

Varchar(50)

50

 

時間戳

ts

timestamp

 

當前時間:資料庫自動維護

 

4 索引設計規範

索引是一把雙刃劍,它可以提高查詢效率但也會降低插入和更新的速度並占用磁碟空間

1、【建議】單張表中索引數量不超過5個(不包括主鍵)

索引不是越多越好,按實際需要進行創建,每個額外的索引都要占用額外的磁碟空間,並降低寫操作的性能;

2、【建議】單個索引中的欄位數不超過5個

對字元串使用首碼索引,首碼索引長度不超過10個字元;如果有一個CHAR(200)列,如果在前10個字元內,多數值是惟一的,那麼就不要對整個列進行索引。對前10個字元進行索引能夠節省大量索引空間,也可能會使查詢更快;

3、【強制】創建複合索引時, 必須把區分度高的欄位放在前面

4、【建議】不建議在更新十分頻繁、區分度不高的屬性上建立索引,特殊場景除外,例如只有0和1,1只占非常小的部分,只會去查詢1的情況。

5、【強制】避免冗餘或重覆索引

合理創建聯合索引(避免冗餘),index(a、b、c)相當於index(a)、index(a、b)、index(a、b、c);

5 欄位設計規範

1、【建議】不建議使用TEXT、BLOB類型

會浪費更多的磁碟和記憶體空間,非必要的大量的大欄位查詢會淘汰掉熱數據,導致記憶體命中率急劇降低,影響資料庫性能;如果實在有某個欄位過長需要使用 TEXT、BLOB 類型,則建議獨立出來一張表,用主鍵來對應,避免影響原表的查詢效率。

2、【強制】用DECIMAL代替FLOAT和DOUBLE存儲精確浮點數

浮點數相對於定點數的優點是在長度一定的情況下,浮點數能夠表示更大的數據範圍;浮點數的缺點是會引起精度問題

3、【強制】欄位必須定義合適的數據類型

只存儲數字的欄位定義成數字類型,只存儲字元的欄位定義成字元類型, 定長的字元定義成char ,儘可能用存儲空間小的類型,只存儲日期的欄位定義成日期類型,以減少使用過程中的數據類型轉換

4、【強制】禁止使用ENUM,可使用TINYINT代替

增加新的ENUM值要做DDL操作

5、【建議】欄位長度儘量按實際需要進行分配,不要隨意分配一個很大的容量

VARCHAR(N),N表示的是字元數不是位元組數,比如VARCHAR(255),可以最大可存儲255個漢字,需要根據實際的寬度來選擇N;
VARCHAR(N),N儘可能小,因為MySQL一個表中所有的VARCHAR欄位最大長度是65535個位元組,進行排序和創建臨時表一類的記憶體操作時,會使用N的長度申請記憶體;

6、【建議】如果可能的話所有欄位均定義為not null且提供預設值

null的列使索引/索引統計/值比較都更加複雜,對MySQL來說更難優化;
null 這種類型MySQL內部需要進行特殊處理,增加資料庫處理記錄的複雜性;同等條件下,表中有較多空欄位的時候,資料庫的處理性能會降低很多;
null值需要更多的存儲空間,無論是表還是索引中每行中的null的列都需要額外的空間來標識;
對null 的處理時候,只能採用is null或is not null,而不能採用=、in、<、<>、!=、not in這些操作符號。如:where name!=’shenjian’,如果存在name為null值的記錄,查詢結果就不會包含name為null值的記錄;

7、【建議】建議使用TIMESTAMP存儲時間. 因為TIMESTAMP使用4位元組,DATETIME使用8個位元組,同時TIMESTAMP具有自動賦值以及自動更新的特性,具體看業務需求。

6 SQL設計規範

1、【建議】使用預編譯語句prepared statement(針對jdbc及mybatis)

只傳參數,比傳遞SQL語句更高效,一次解析,多次使用,降低SQL註入概率;

2、【強制】禁止在WHERE條件的屬性上使用函數或者表達式

無法使用索引導致全表掃描;

3、【強制】避免隱式轉換(查詢條件左右兩側類型不匹配)

會導致索引失效而全表掃描,如userid為int類型,select userid from table where userid='1234';相當於隱式地使用了函數將int類型轉換為字元串。

4、【強制】禁止使用INSERT INTO t_xxx VALUES (xxx)必須顯示指定插入的列屬性,否則容易在增加或者刪除欄位後出現程式bug。

5、【強制】禁止使用SELECT *,只獲取必要的欄位,需要顯示說明列屬性

讀取不需要的列會增加CPU、IO、NET消耗,不能有效的利用覆蓋索引,減少表結構變更帶來的影響;

6、【建議】避免使用大表的join, 大表使用子查詢

MySQL最擅長的是單表的主鍵/二級索引查詢,大表join會產生臨時表,消耗較多記憶體與CPU,極大影響資料庫性能;

7、【建議】拒絕大SQL,拆分成小SQL

充分利用多核CPU;

8、【建議】考慮使用limit N,少用limit M, N,特別是大表或M比較大的時候

9、【建議】減少或避免排序,儘量利用索引本身的有序 ,例如where條件中無id時order by id優化器會選擇主鍵索引,但是 where 條件里又沒有主鍵條件,導致全表掃描。

10、【建議】使用union all而不是union

儘量使用UNION  ALL,減少使用UNION,因為UNION  ALL不去重,而少了排序操作,速度相對比UNION要快,如果沒有去重的需求,優先使用UNION ALL;

11、【強制】避免使用全表掃描,配置表和小表(數據總量小於1萬條)例外。如果數據量比較小,或認為不會超過10000條數據,可以加上LIMIT限制;

12【強制】同表的增刪欄位、索引合併一條DDL語句執行,提高執行效率,減少與資料庫的交互。

7 行為規範

1、【強制】大數據量導入、導出數據必須提前通知DBA協助觀察(以100w行作為參考基準,具體和表欄位數量相關);

2、【強制】大數據量更新數據,如update、delete操作,需要DBA進行審查,併在執行過程中觀察服務負載等各種狀況;

3、【強制】禁止有super許可權的應用程式賬號存在;

4、【強制】促銷活動或上線新功能必須提前一周通知DBA進行流量評估;

5、【強制】資料庫數據丟失,第一時間聯繫DBA進行恢復;

6、【強制】不在MySQL資料庫中存放業務邏輯;

7、【強制】對特別重要的庫表,提前與DBA溝通確定維護和備份優先順序;

8、【強制】不在業務高峰期批量更新、查詢資料庫;


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 簡介 在文章《GraalVM和Spring Native嘗鮮,一步步讓Springboot啟動飛起來,66ms完成啟動》中,我們介紹瞭如何使用Spring Native和buildtools插件,打包出本地鏡像,也打包成Docker鏡像。本文探索一下,如果不通過這個插件來生成鏡像。這樣我們可以控制更 ...
  • 記錄一下Winform程式打包過程 參考文章:VS2017 WinFrom打包設置與教程 下載 Visual Studio Installer 拓展插件 從VS2017開始VS已預設不再集成Installer拓展,所以需要手動下載安裝。 可以在 工具 - 插件和更新 裡面的插件商店裡面搜索安裝。 制 ...
  • 前言 本文寫給想學C#的朋友,目的是以較快的速度入門 C#好學嗎? 對於這個問題,我以前的回答是:好學!但仔細想想,不是這麼回事,對於新手來說,C#沒有那麼好學。 如果你要入門Java,那學Java Web就行了,但是C#方向比較多,你是學控制台程式、WebAPI、ASP.NET、Winform還是 ...
  • 記錄一下過程. Arm Mbed 應該屬於Arm的機構或者是Arm資助的機構. 常用的 DAPLink 基本上都是從這個項目派生的. 倉庫主要是使用 Keil, 對 GCC 的支持是 2020 年才正式合併進來的. Ubuntu 下使用 GCC Arm 編譯 ...
  • ##一、進入系統引導界面進行配置 ###引導項說明: 安裝centos7系統(*) 測試光碟鏡像並安裝系統 排錯模式(修複系統 重置系統密碼) 補充:centos7系統網卡名稱 預設系統的網卡名稱 eth0 eth1 --centos6 預設系統的網卡名稱 ens33 ens34 --centos7 ...
  • 本教程說明如何在當Windows系統無法正常啟動時,採取重建活動分區的方式來嘗試修複,目的在於不使用第三方軟體和不重裝系統的前提下對系統啟動問題進行最小代價修複。 該教程來源為windows-10-bootrec-fixboot-access-is-denied,本文僅對其稍作修改。 如果系統啟動後 ...
  • 一、資源下載 Keil5下載鏈接: https://www.keil.com/download/product/ STM32 標準庫晶元包下載鏈接: https://www.keil.com/dd2/pack/ JDK下載鏈接: https://www.oracle.com/java/technol ...
  • 一:背景 1. 講故事 在有關SQLSERVER的各種參考資料中,經常會看到如下四種事務隔離級別。 READ UNCOMMITTED READ COMMITTED SERIALIZABLE REPEATABLE READ 隨之而來的是大量的文字解釋,還會附帶各種 臟讀, 幻讀, 不可重覆讀 常常會把 ...
一周排行
    -Advertisement-
    Play Games
  • 下麵是一個標準的IDistributedCache用例: public class SomeService(IDistributedCache cache) { public async Task<SomeInformation> GetSomeInformationAsync (string na ...
  • 這個庫提供了在啟動期間實例化已註冊的單例,而不是在首次使用它時實例化。 單例通常在首次使用時創建,這可能會導致響應傳入請求的延遲高於平時。在註冊時創建實例有助於防止第一次Request請求的SLA 以往我們要在註冊的時候實例單例可能會這樣寫: //註冊: services.AddSingleton< ...
  • 最近公司的很多項目都要改單點登錄了,不過大部分都還沒敲定,目前立刻要做的就只有一個比較老的項目 先改一個試試手,主要目標就是最短最快實現功能 首先因為要保留原登錄方式,所以頁面上的改動就是在原來登錄頁面下加一個SSO登錄入口 用超鏈接寫的入口,頁面改造後如下圖: 其中超鏈接的 href="Staff ...
  • Like運算符很好用,特別是它所提供的其中*、?這兩種通配符,在Windows文件系統和各類項目中運用非常廣泛。 但Like運算符僅在VB中支持,在C#中,如何實現呢? 以下是關於LikeString的四種實現方式,其中第四種為Regex正則表達式實現,且在.NET Standard 2.0及以上平... ...
  • 一:背景 1. 講故事 前些天有位朋友找到我,說他們的程式記憶體會偶發性暴漲,自己分析了下是非托管記憶體問題,讓我幫忙看下怎麼回事?哈哈,看到這個dump我還是非常有興趣的,居然還有這種游戲幣自助機類型的程式,下次去大玩家看看他們出幣的機器後端是不是C#寫的?由於dump是linux上的程式,剛好win ...
  • 前言 大家好,我是老馬。很高興遇到你。 我們為 java 開發者實現了 java 版本的 nginx https://github.com/houbb/nginx4j 如果你想知道 servlet 如何處理的,可以參考我的另一個項目: 手寫從零實現簡易版 tomcat minicat 手寫 ngin ...
  • 上一次的介紹,主要圍繞如何統一去捕獲異常,以及為每一種異常添加自己的Mapper實現,並且我們知道,當在ExceptionMapper中返回非200的Response,不支持application/json的響應類型,而是寫死的text/plain類型。 Filter為二方包異常手動捕獲 參考:ht ...
  • 大家好,我是R哥。 今天分享一個爽飛了的面試輔導 case: 這個杭州兄弟空窗期 1 個月+,面試了 6 家公司 0 Offer,不知道問題出在哪,難道是杭州的 IT 崩盤了麽? 報名面試輔導後,經過一個多月的輔導打磨,現在成功入職某上市公司,漲薪 30%+,955 工作制,不咋加班,還不捲。 其他 ...
  • 引入依賴 <!--Freemarker wls--> <dependency> <groupId>org.freemarker</groupId> <artifactId>freemarker</artifactId> <version>2.3.30</version> </dependency> ...
  • 你應如何運行程式 互動式命令模式 開始一個互動式會話 一般是在操作系統命令行下輸入python,且不帶任何參數 系統路徑 如果沒有設置系統的PATH環境變數來包括Python的安裝路徑,可能需要機器上Python可執行文件的完整路徑來代替python 運行的位置:代碼位置 不要輸入的內容:提示符和註 ...