MySQL開發設計規范有哪些

這期內容當中小編將會給大家帶來有關MySQL開發設計規范有哪些，文章內容豐富且以專業的角度為大家分析和敘述，閱讀完這篇文章希望大家可以有所收獲。

命名規范

l  庫名、表名、字段名禁止超過32個字符。

l  所有數據庫對象名稱必須使用小寫字母并用下劃線分割

不同的數據庫名  DbName dbname

不同的表名  Table table tabLe

l  所有數據庫對象名稱(庫名、表名、字段名)禁止使用MySQL保留關鍵字

select id,username,from,age from tb_user

有兩個from,MySQL并不清楚這兩個from有什么區別,執行上面會報錯

select id,username,`from`,age from tb_user 正確

l  數據庫對象的命名要能做到見名識義，并且最好不要超過32個字符

例如:用戶賬號表  user_account

     用戶數據庫  aisino_userdb

l  臨時庫表必須以tmp_開頭并以日期為后綴, 例如tmp_test01_20161218

l   備份庫備份表必須以bak_開頭并以日期戳為后綴, 例如bak_test01_20161218

l  所有存儲相同數據的列名和列類型必須一致

CREATE TABLE customer_inf(

customer_inf id int unsigned auto increment not null comment '自增id',

customer_id int unsigned not null comment 'customer_login表的自增id,

.........................................

CREATE TABLE order_master(

order_id id int unsigned not null auto increment comment '訂單ID',

customer_id int unsigned not null comment '下單人ID',

.........................................

基礎規范

l  使用INNODB存儲引擎

支持事務，行級鎖，更好的恢復性，高并發下性能更好

l  INNODB表必須有主鍵列，使用auto_increment

l  數據庫和表的字符集統一使用UTF8

統一字符集可以避免由于字符集轉換產生的亂碼

MySQL中UTF8字符集漢字占3個字節，ASCII碼占1個字節

l  表必須有主鍵

l  所有表和字段都需要添加注釋

使用comment添加表和列的備注

好處:從一開始就進行數據字典的維護和整理

l  表數量不超過300個

l  盡量控制單表數據量的大小，建議數據控制在500萬行以內

500萬并不是MySQL數據庫的限制，MySQL最多可以存儲多少萬數據？

這種限制取決于存儲設置和文件系統

  可以用歷史數據歸檔，分庫分表等手段來控制數據量大小

l  禁止在數據庫中存儲圖片，視頻和文件等二進制數據

把圖片或文件存儲到相應的文件服務器中，數據庫中只存放圖片或文件的地址信息

通常文件很大，查詢IO操作耗時,會影響數據庫的性能

利用更有效的利用緩存，避免讀入無用的冷數據

經常一起使用的列放到一個表中

l  禁止在線上做數據庫壓力測試

l  禁止從開發環境，測試環境直連生產環境數據庫

數據庫各個環境之間要進行隔離

l  臨時表和備份表必須定期清理(備份歸檔)

庫表設計

l  禁止使用分區表

分區表在物理上表現為多個文件，在邏輯上表現為一個表

謹慎選擇分區鍵，跨分區查詢效率可能更低

 對于大表建議采用物理分表的方式管理大數據

l  拆分大字段和訪問頻率低的字段，分離冷熱數據

盡量做到冷熱數據分離，減小表的寬帶

MySQL限制最多存儲4096列,每一行數據的大小不能超過65535個字節

減少磁盤 IO，保證熱數據的內存緩存命中率

l  按日期時間分表需符合YYYY[MM][DD][HH]格式

l  采用合適的分庫分表策略。

字段設計

l  避免使用TEXT,BLOB數據類型

建議把BLOB或TEXT列分離到單獨的擴展表中

l  優先選擇符合存儲需要的最小的數據類型, 使用INT UNSIGNED存儲IPV4

將字符串轉化為數字類型存儲:

INET_ATON('255.255.255.255')=4294967295

將數字類型轉化為字符串:

INET_NTOA(4294967295)='255.255.255.255'

l  使用TINYINT來代替ENUM類型

l  表字符集盡量選擇UTF8

l  避免使用ENUM枚舉數據類型

修改ENUM值需要使用ALTER語句

ENUM類型的ORDER BY操作效率低，需要額外操作

禁止使用數值作為ENUM的枚舉值

l  所有字段均定義為NOT NULL

索引NULL列需要額外的空間來保存，所以要占用更多的空間

進行比較和計算時候要對NULL值做特殊處理，索引會失效

l  使用UNSIGNED存儲非負整數

無符號值取值范圍:

UNSIGNED INT (0--4294967295)

l  同財務相關的金額類數據，必須使用decimal類型

Decimal類型為精準浮點數，在計算時不會丟失精度

占用空間由定義的寬度決定

可用于存儲比bigint更大的整數數據

l  盡量避免使用字符串存儲日期型數據

缺點1：無法用日期函數進行比較和計算

缺點2：用字符串存儲日期要占用更多的空間

l  使用TIMESTAMP或DATATIME類型存儲時間

TIMESTAMP  1970-01-01 00:00:01 --2038-01-19  03:14:07

l  INT類型固定占用4字節存儲,TIMESTAMP占用4字節和INT相同，但比INT可讀性高

l  禁止在數據庫中存儲明文密碼

索引規范

l  限制每張表的索引數量，建議單表索引數量不超過5個

l  禁止給表中的每一列都建立單獨的索引,單個索引中的字段數不超過5個

l  每個Innodb表都要有一個主鍵

不使用更新頻繁的列作為主鍵，不使用多列主鍵

主鍵建議選擇使用自增ID值

l  不使用更新頻繁的列

l  為經常需要排序、分組和聯合操作的字段建立索引

常見索引列建議

SELECT,UPDATE,DELETE語句的WHERE從句中的列

包含在ORDER BY,GROUP BY,DISTINCT中的字段

多表JOIN的關聯列

l  為常作為查詢條件的字段建立索引

l  刪除不再使用或者很少使用的索引

l  最左前綴匹配原則，非常重要的原則。

l  盡量選擇區分度高的列作為索引

l  避免建立冗余索引和重復索引

index(a,b,c), index(a,b) ,index(a)

 a列是冗余索引

l  對于頻繁的查詢優先考慮使用覆蓋索引

覆蓋索引：就是包含了所有查詢字段的索引

  全部字段不但是指where從句中出現的列，也包括出現在select從句，order by和group by從句中的列

  覆蓋索引的好處:避免Innodb表進行索引的二次查找

  可以把隨機IO變為順序IO加快查詢效率

SQL設計規范

l  避免隱式轉換,會導致索引失效

l  充分利用前綴索引

l  必須是最左前綴

l  JOIN消耗較多內存，產生臨時表

l  避免在數據庫中進行數學運算

l  WHERE從句中禁止對列進行函數轉換和計算

對列進行函數轉換或計算會導致無法使用索引

where date(createtime)='20160901'

改進:

 where createtime >= '20160901' and createtime < '20160902'

l  使用不等于（!= 或者 <>），無法使用索引

l  使用LIKE操作（如'%abc...'）時，無法使用索引

l  拒絕大SQL，拆分成小SQL

不支持SQL并行查詢，MySQL一個SQL只能使用一個CPU進行計算

l  避免使用JOIN關聯太多的表

每join一個表會多占用一部分內存(join_buffer_size)

會產生臨時表操作，影響查詢效率

 MySQL最多允許關聯61個表，建議不超過5個

l  程序連接不同的數據庫使用不同的賬號，禁止跨庫查詢

l  用UNION ALL而不是UNION

UNION會把所有數據放到臨時表中后再進行去重操作

UNION ALL不會再對結果集進行去重操作

l  禁止使用select *進行查詢及沒有字段列表的insert操作

l  禁止單條SQL語句同時更新多個表

l  盡量不使用select *,而使用SELECT <字段列表>查詢

SELECT *返回結果中包含很多并不需要的字段，消耗更多的CPU和IO以及網絡帶寬資源

無法使用覆蓋索引

行為規范

l  批量導入、導出數據必須提前通知DBA協助觀察

超過100萬行的批量寫操作，要分批多次進行操作

l  對大表數據結構的修改一定要謹慎，會造成嚴重的鎖表操作，尤其是生產環境，是不能忍受的

對于大表使用pt-online-schema-change修改表結構，不會鎖表

避免大表修改產生的主從延遲

避免在對表字段進行修改時進行鎖表

l  推廣活動或上線新功能須提前通知DBA,請求壓力評估

l  對于程序連接數據庫賬號，遵循權限最小原則

l  程序使用的賬號原則上不準有drop權限

l  不使用super權限連接數據庫

禁止為程序使用的賬號賦予super權限

super權限只能留給DBA處理問題的賬號使用

l  對單表的多次alter操作必須合并為一次操作

l  產品出現非數據庫導致的故障時及時通知DBA協助排查

l  數據庫數據丟失，及時聯系DBA進行恢復

l  重大項目的數據庫方案選型和設計必須提前通知DBA參與

l  對特別重要的庫表，提前與DBA溝通確定維護和備份優先級

l  不在業務高峰期批量更新、查詢數據庫

l  批量導入、導出數據須提前通知DBA，請求協助觀察

l  數據庫DDL及重要SQL及早提及DBA評審

l  提及線上DDL需求，所有SQL語句須有備注說明

上述就是小編為大家分享的MySQL開發設計規范有哪些了，如果剛好有類似的疑惑，不妨參照上述分析進行理解。如果想知道更多相關知識，歡迎關注億速云行業資訊頻道。