程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> 【mysql】數據庫使用的一些規范，mysql數據庫規范

【mysql】數據庫使用的一些規范，mysql數據庫規范

編輯：MySQL綜合教程

【mysql】數據庫使用的一些規范，mysql數據庫規范

一、MySQL存在的問題

優化器對復雜SQL支持不好
對SQL標准支持不好
大規模集群方案不成熟，主要指中間件
ID生成器，全局自增ID
異步邏輯復制，數據安全性問題
Online DDL
HA方案不完善
備份和恢復方案還是比較復雜，需要依賴外部組件
展現給用戶信息過少，排查問題困難
眾多分支，讓人難以選擇

二、數據庫環境介紹

通常來講，各個互聯網公司的數據庫分為5個數據庫環境：

dev : 開發環境, 開發可讀寫,可修改表結構; 常用的163的數據庫表; 開發人員可以修改表結構, 可以隨意修改其中的數據; 但是需要保證不影響其他開發同事
qa : 測試環境, 開發可讀寫, 開發人員可以通過工具修改表結構
sim: 模擬環境, 開發可讀寫, 通過web平台;發起上線請求時，會先在這個環境上進行預執行，這個環境也可供部署上線演練或壓力測試使用可以讀寫
real: 生產數據庫從庫（准實時同步）,只讀環境,不允許修改數據,不允許修改表結構; 供線上問題查找,數據查詢等使用
online: 線上環境;開發人員不允許直接在線上環境進行數據庫操作,如果需要操作必須找DBA進行操作並進行相應記錄

這些環境的機器，一定要做到權限劃分明確，讀寫帳號分離，並且有辨識度，能區分具體業務。例如用戶名w_wap, r_wap 能看出來，讀寫帳號是wap應用的

三、數據庫開發規范

開發規范本身也包含幾部分：基本命名和約束規范，字段設計規范，索引規范，使用規范等

規范存在意義

保證線上數據庫schema規范
減少出問題概率
方便自動化管理
規范需要長期堅持，對開發和DBA是一個雙贏的事情

約束規范

表字符集選擇UTF8 ，如果需要存儲emoj表情，需要使用UTF8mb4(MySQL 5.5.3以後支持)
存儲引擎使用InnoDB
變長字符串盡量使用VARCHAR VARBINARY
不在數據庫中存儲圖片、文件
設計表的時候需要添加注釋
單表數據量控制在1億以下，單表物理大小不超過10GB，行平均長度不超過8KB
禁止在線上做數據庫壓⼒測試
禁止從測試、開發環境直連數據庫

create database test_crm default character set=utf8;

基本命名規范

庫名、表名、字段名禁止使用保留字
庫名、表名、字段名、索引名使用小寫字母，以下劃線分割，需要見名知意
庫名、表名、字段名、索引名不要設計過長，禁止超過32個字符，盡可能用最少的字符表達出表的用途
臨時庫、臨時表名必須以tmp為前綴，並以日期為後綴
備份庫、表必須以bak為前綴，並以日期為後綴
庫名、表名、字段名、索引名使用名詞作為數據庫名稱，並且只用英文，不用中文拼音
庫名使用英文字母，全部小寫，控制在3-7個字母以內
庫名如果有多個單詞，則使用下劃線隔開，不建義駝峰命名

分表規范

禁止使用分區表
拆分大字段和訪問頻率低的字段，分離冷熱數據
使用HASH進行散表，表名後綴使用十進制數，下標從0開始
按⽇期時間分表需符合YYYY[MM][DD][HH]格式
采用合適的分庫分表策略

字段規范

所有字段均定義為NOT NULL ，除非你真的想存NULL，但是我想不到需要用Null的情況
字段類型在滿足需求條件下越小越好，使用UNSIGNED存儲非負整數，實際使用時候存儲負數場景不多
使用TIMESTAMP存儲時間，使用UNSIGNED INT存儲IPv4 地址而不是CHAR(15) ，這種方式只能存儲IPv4，存儲不了IPv6
使用VARCHAR存儲變長字符串，當然要注意varchar(M)裡的M指的是字符數不是字節數；
使用DECIMAL代替FLOAT和DOUBLE存儲精確浮點數
盡可能不用BLOB TEXT
使用TINYINT來代替ENUM類型，將字符轉化為數字
禁止在數據庫中存儲明文密碼
使用VARBINARY存儲大小寫敏感的變⻓字符串

索引規范

單個索引字段數不超過5，單表索引數量不超過5，索引設計遵循B+ Tree索引最左前綴匹配原則
選擇區分度高的列作為索引，區分度高的放在前面
對字符串使用前綴索引，前綴索引長度不超過8個字符
建議優先考慮前綴索引，必要時可添加偽列並建立索引
建立的索引能覆蓋80%主要的查詢，不求全，解決問題的主要矛盾
DML和order by和group by字段要建立合適的索引
避免索引的隱式轉換
避免冗余索引
關於主鍵：表必須有主鍵；不使用更新頻繁的列；不選擇字符串列；不使用UUID MD5 HASH ；默認使用非空的唯一鍵，建議選擇自增或發號器
重要的SQL必須被索引：UPDATE、DELETE語句的WHERE條件列；ORDER BY、GROUP BY、DISTINCT的字段；多表JOIN的字段
核心SQL優先考慮覆蓋索引
不在低基數列上建立索引，例如“性別”
不在索引列進行數學運算和函數運算
盡量不使⽤外鍵，外鍵用來保護參照完整性，可在業務端實現；對父親和子表的操作會相互影響，降低可用性；INNODB本身對online DDL的限制
不使⽤%前導的查詢，如like “%ab”
不使用負向查詢，如not in/like "無法使用索引，導致全表掃描

隱式轉換例子，字段定義為varchar，但傳入的值是個int，就會導致全表掃描，要求程序端要做好類型檢查

字段：remark varchar(50) NOT Null

mysql>SELECT id, gift_code FROM gift WHERE deal_id = 640 AND remark=115127;
1 row in set (0.14 sec)
mysql>SELECT id, gift_code FROM pool_gift WHEREdeal_id = 640 AND remark=‘115127’; 
1 row in set (0.005 sec)