【推薦】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
  • Timer是什麼 Timer 是一種用於創建定期粒度行為的機制。 與標準的 .NET System.Threading.Timer 類相似,Orleans 的 Timer 允許在一段時間後執行特定的操作,或者在特定的時間間隔內重覆執行操作。 它在分散式系統中具有重要作用,特別是在處理需要周期性執行的 ...
  • 前言 相信很多做WPF開發的小伙伴都遇到過表格類的需求,雖然現有的Grid控制項也能實現,但是使用起來的體驗感並不好,比如要實現一個Excel中的表格效果,估計你能想到的第一個方法就是套Border控制項,用這種方法你需要控制每個Border的邊框,並且在一堆Bordr中找到Grid.Row,Grid. ...
  • .NET C#程式啟動閃退,目錄導致的問題 這是第2次踩這個坑了,很小的編程細節,容易忽略,所以寫個博客,分享給大家。 1.第一次坑:是windows 系統把程式運行成服務,找不到配置文件,原因是以服務運行它的工作目錄是在C:\Windows\System32 2.本次坑:WPF桌面程式通過註冊表設 ...
  • 在分散式系統中,數據的持久化是至關重要的一環。 Orleans 7 引入了強大的持久化功能,使得在分散式環境下管理數據變得更加輕鬆和可靠。 本文將介紹什麼是 Orleans 7 的持久化,如何設置它以及相應的代碼示例。 什麼是 Orleans 7 的持久化? Orleans 7 的持久化是指將 Or ...
  • 前言 .NET Feature Management 是一個用於管理應用程式功能的庫,它可以幫助開發人員在應用程式中輕鬆地添加、移除和管理功能。使用 Feature Management,開發人員可以根據不同用戶、環境或其他條件來動態地控制應用程式中的功能。這使得開發人員可以更靈活地管理應用程式的功 ...
  • 在 WPF 應用程式中,拖放操作是實現用戶交互的重要組成部分。通過拖放操作,用戶可以輕鬆地將數據從一個位置移動到另一個位置,或者將控制項從一個容器移動到另一個容器。然而,WPF 中預設的拖放操作可能並不是那麼好用。為瞭解決這個問題,我們可以自定義一個 Panel 來實現更簡單的拖拽操作。 自定義 Pa ...
  • 在實際使用中,由於涉及到不同編程語言之間互相調用,導致C++ 中的OpenCV與C#中的OpenCvSharp 圖像數據在不同編程語言之間難以有效傳遞。在本文中我們將結合OpenCvSharp源碼實現原理,探究兩種數據之間的通信方式。 ...
  • 一、前言 這是一篇搭建許可權管理系統的系列文章。 隨著網路的發展,信息安全對應任何企業來說都越發的重要,而本系列文章將和大家一起一步一步搭建一個全新的許可權管理系統。 說明:由於搭建一個全新的項目過於繁瑣,所有作者將挑選核心代碼和核心思路進行分享。 二、技術選擇 三、開始設計 1、自主搭建vue前端和. ...
  • Csharper中的表達式樹 這節課來瞭解一下表示式樹是什麼? 在C#中,表達式樹是一種數據結構,它可以表示一些代碼塊,如Lambda表達式或查詢表達式。表達式樹使你能夠查看和操作數據,就像你可以查看和操作代碼一樣。它們通常用於創建動態查詢和解析表達式。 一、認識表達式樹 為什麼要這樣說?它和委托有 ...
  • 在使用Django等框架來操作MySQL時,實際上底層還是通過Python來操作的,首先需要安裝一個驅動程式,在Python3中,驅動程式有多種選擇,比如有pymysql以及mysqlclient等。使用pip命令安裝mysqlclient失敗應如何解決? 安裝的python版本說明 機器同時安裝了 ...