最近涉及數(shù)據(jù)庫相關(guān)操作較多,公司現(xiàn)有規(guī)范也不是太全面,就根據(jù)網(wǎng)上各路大神的相關(guān)規(guī)范,整理了一些自用的規(guī)范用法,萬望指正。
數(shù)據(jù)庫環(huán)境
dev: 開發(fā)環(huán)境
開發(fā)可讀寫,可修改表結(jié)構(gòu)。開發(fā)人員可以修改表結(jié)構(gòu),可以隨意修改其中的數(shù)據(jù)但是需要保證不影響其他開發(fā)同事。
test: 測試環(huán)境
開發(fā)可讀寫,開發(fā)人員可以通過工具修改表結(jié)構(gòu)。
online: 線上環(huán)境
開發(fā)人員不允許直接在線上環(huán)境進行數(shù)據(jù)庫操作,如果需要操作必須找DBA進行操作并進行相應記錄,禁止進行壓力測試。
重點的問題,各個環(huán)境的mysql服務器對應的用戶權(quán)限,一定要做到權(quán)限劃分明確,有辨識度,能具體區(qū)分業(yè)務場景等。
命名規(guī)范
基本命名規(guī)則
- 使用有意義的英文詞匯,詞匯中間以下劃線分隔。(不要用拼音)
- 只能使用英文字母,數(shù)字,下劃線,并以英文字母開頭。
- 庫、表、字段全部采用小寫,不要使用駝峰式命名。
- 避免用ORACLE、MySQL的保留字,如desc,關(guān)鍵字如index。
- 命名禁止超過32個字符,須見名之意,建議使用名詞不是動詞
- 數(shù)據(jù)庫,數(shù)據(jù)表一律使用前綴
- 臨時庫、表名必須以tmp為前綴,并以日期為后綴
- 備份庫、表必須以bak為前綴,并以日期為后綴
為什么庫、表、字段全部采用小寫?
在 MySQL 中,數(shù)據(jù)庫和表對就于那些目錄下的目錄和文件。因而,操作系統(tǒng)的敏感性決定數(shù)據(jù)庫和表命名的大小寫敏感。
- Windows下是不區(qū)分大小寫的。
- Linux下大小寫規(guī)則
- 數(shù)據(jù)庫名與表名是嚴格區(qū)分大小寫的;
- 表的別名是嚴格區(qū)分大小寫的;
- 列名與列的別名在所有的情況下均是忽略大小寫的;
- 變量名也是嚴格區(qū)分大小寫的;
- 如果已經(jīng)設置了駝峰式的命名如何解決?需要在MySQL的配置文件my.ini中增加 lower_case_table_names = 1即可。
表命名
同一個模塊的表盡可能使用相同的前綴,表名稱盡可能表達含義。所有日志表均以 log_ 開頭
字段命名
- 表達其實際含義的英文單詞或簡寫。布爾意義的字段以is_作為前綴,后接動詞過去分詞。
- 各表之間相同意義的字段應同名。各表之間相同意義的字段,以去掉模塊前綴的表名_字段名命名。
- 外鍵字段用表名_字段名表示其關(guān)聯(lián)關(guān)系。
- 表的主鍵一般都約定成為id,自增類型,是別的表的外鍵均使用xxx_id的方式來表明。
索引命名
- 非唯一索引必須按照“idx_字段名稱_字段名稱[_字段名]”進行命名
- 唯一索引必須按照“uniq_字段名稱_字段名稱[_字段名]”進行命名
約束命名
- 主鍵約束:pk_表名稱。
- 唯一約束:uk_表名稱_字段名。(應用中需要同時有唯一性檢查邏輯。)
表設計規(guī)范
表引擎取決于實際應用場景;日志及報表類表建議用myisam,與交易,審核,金額相關(guān)的表建議用innodb引擎。如無說明,建表時一律采用innodb引擎
默認使用utf8mb4字符集,數(shù)據(jù)庫排序規(guī)則使用utf8mb4_general_ci,(由于數(shù)據(jù)庫定義使用了默認,數(shù)據(jù)表可以不再定義,但為保險起見,建議都寫上
為什么字符集不選擇utf8,排序規(guī)則不使用utf8_general_ci
采用utf8編碼的MySQL無法保存占位是4個字節(jié)的Emoji表情。為了使后端的項目,全面支持客戶端輸入的Emoji表情,升級編碼為utf8mb4是最佳解決方案。對于JDBC連接串設置了characterEncoding為utf8或者做了上述配置仍舊無法正常插入emoji數(shù)據(jù)的情況,需要在代碼中指定連接的字符集為utf8mb4。
所有表、字段均應用 comment 列屬性來描述此表、字段所代表的真正含義,如枚舉值則建議將該字段中使用的內(nèi)容都定義出來。
如無說明,表中的第一個id字段一定是主鍵且為自動增長,禁止在非事務內(nèi)作為上下文作為條件進行數(shù)據(jù)傳遞。禁止使用varchar類型作為主鍵語句設計。
如無說明,表必須包含create_time和modify_time字段,即表必須包含記錄創(chuàng)建時間和修改時間的字段
如無說明,表必須包含is_del,用來標示數(shù)據(jù)是否被刪除,原則上數(shù)據(jù)庫數(shù)據(jù)不允許物理刪除。
- 用盡量少的存儲空間來存數(shù)一個字段的數(shù)據(jù)
- 能用int的就不用char或者varchar
- 能用tinyint的就不用int
- 使用UNSIGNED存儲非負數(shù)值。
- 不建議使用ENUM、SET類型,使用TINYINT來代替
- 使用短數(shù)據(jù)類型,比如取值范圍為0-80時,使用TINYINT UNSIGNED
- 存儲精確浮點數(shù)必須使用DECIMAL替代FLOAT和DOUBLE
- 時間字段,除特殊情況一律采用int來記錄unix_timestamp
- 存儲年使用YEAR類型。
- 存儲日期使用DATE類型。
- 存儲時間(精確到秒)建議使用TIMESTAMP類型,因為TIMESTAMP使用4字節(jié),DATETIME使用8個字節(jié)。
- 建議使用INT UNSIGNED存儲IPV4。
- 盡可能不使用TEXT、BLOB類型
- 禁止在數(shù)據(jù)庫中使用VARBINARY、BLOB存儲圖片、文件等。建議使用其他方式存儲(TFS/SFS),MySQL只保存指針信息。
- 單條記錄大小禁止超過8k(列長度(中文)_3(UTF8)+列長度(英文)_1)
datetime與timestamp有什么不同?
相同點:
TIMESTAMP列的顯示格式與DATETIME列相同。顯示寬度固定在19字符,并且格式為YYYY-MM-DD HH:MM:SS。
不同點:
TIMESTAMP
- 4個字節(jié)儲存,時間范圍:1970-01-01 08:00:01 ~ 2038-01-19 11:14:07值以UTC格式保存,涉及時區(qū)轉(zhuǎn)化 ,存儲時對當前的時區(qū)進行轉(zhuǎn)換,檢索時再轉(zhuǎn)換回當前的時區(qū)。
- datetime8個字節(jié)儲存,時間范圍:1000-01-01 00:00:00 ~ 9999-12-31 23:59:59
- 實際格式儲存,與時區(qū)無關(guān)
如何使用TIMESTAMP的自動賦值屬性?
將當前時間作為ts的默認值:ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP。當行更新時,更新ts的值:ts TIMESTAMP DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP。
可以將1和2結(jié)合起來:ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP。
如何使用INT UNSIGNED存儲ip?
使用INT UNSIGNED而不是char(15)來存儲ipv4地址,通過MySQL函數(shù)inet_ntoa和inet_aton來進行轉(zhuǎn)化。Ipv6地址目前沒有轉(zhuǎn)化函數(shù),需要使用DECIMAL或者兩個bigINT來存儲。
- 如無備注,所有字段都設置NOT NULL,并設置默認值;
- 禁止在數(shù)據(jù)庫中存儲明文密碼
- 如無備注,所有的布爾值字段,如is_hot、is_deleted,都必須設置一個默認值,并設為0;
- 如無備注,排序字段order_id在程序中默認使用降序排列;
- 整形定義中不添加長度,比如使用INT,而不是INT[4]
INT[M],M值代表什么含義?
注意數(shù)值類型括號后面的數(shù)字只是表示寬度而跟存儲范圍沒有關(guān)系。很多人他們認為INT(4)和INT(10)其取值范圍分別是 (-9999到9999)和(-9999999999到9999999999),這種理解是錯誤的。其實對整型中的 M值與 ZEROFILL 屬性結(jié)合使用時可以實現(xiàn)列值等寬。不管INT[M]中M值是多少,其取值范圍還是 (-2147483648到2147483647 有符號時),(0到4294967295無符號時)。
顯示寬度并不限制可以在列內(nèi)保存的值的范圍,也不限制超過列的指定寬度的值的顯示。當結(jié)合可選擴展屬性ZEROFILL使用時默認補充的空格用零代替。例如:對于聲明為INT(5) ZEROFILL的列,值4檢索為00004。請注意如果在整數(shù)列保存超過顯示寬度的一個值,當MySQL為復雜聯(lián)接生成臨時表時會遇到問題,因為在這些情況下MySQL相信數(shù)據(jù)適合原列寬度,如果為一個數(shù)值列指定ZEROFILL, MySQL自動為該列添加UNSIGNED屬性。
使用VARBINARY存儲大小寫敏感的變長字符串
什么時候用CHAR,什么時候用VARCHAR?
CHAR和VARCHAR類型類似,但它們保存和檢索的方式不同。它們的最大長度和是否尾部空格被保留等方面也不同。CHAR和VARCHAR類型聲明的長度表示你想要保存的最大字符數(shù)。例如,CHAR(30)可以占用30個字符。
CHAR列的長度固定為創(chuàng)建表時聲明的長度。長度可以為從0到255的任何值。當保存CHAR值時,在它們的右邊填充空格以達到指定的長度。當檢索到CHAR值時,尾部的空格被刪除掉。在存儲或檢索過程中不進行大小寫轉(zhuǎn)換。
VARCHAR列中的值為可變長字符串。長度可以指定為0到65,535之間的值。(VARCHAR的最大有效長度由最大行大小和使用的字符集確定。整體最大長度是65,532字節(jié))。同CHAR對比,VARCHAR值保存時只保存需要的字符數(shù),另加一個字節(jié)來記錄長度(如果列聲明的長度超過255,則使用兩個字節(jié))。VARCHAR值保存時不進行填充。當值保存和檢索時尾部的空格仍保留,符合標準SQL。
char適合存儲用戶密碼的MD5哈希值,它的長度總是一樣的。對于經(jīng)常改變的值,char也好于varchar,因為固定長度的行不容易產(chǎn)生碎片,對于很短的列,char的效率也高于varchar。char(1)字符串對于單字節(jié)字符集只會占用一個字節(jié),但是varchar(1)則會占用2個字節(jié),因為1個字節(jié)用來存儲長度信息。
索引設計規(guī)范
MySQL的查詢速度依賴良好的索引設計,因此索引對于高性能至關(guān)重要。合理的索引會加快查詢速度(包括UPDATE和DELETE的速度,MySQL會將包含該行的page加載到內(nèi)存中,然后進行UPDATE或者DELETE操作),不合理的索引會降低速度。MySQL索引查找類似于新華字典的拼音和部首查找,當拼音和部首索引不存在時,只能通過一頁一頁的翻頁來查找。當MySQL查詢不能使用索引時,MySQL會進行全表掃描,會消耗大量的IO。索引的用途:去重、加速定位、避免排序、覆蓋索引。
什么是覆蓋索引
InnoDB存儲引擎中,secondary index(非主鍵索引)中沒有直接存儲行地址,存儲主鍵值。如果用戶需要查詢secondary index中所不包含的數(shù)據(jù)列時,需要先通過secondary index查找到主鍵值,然后再通過主鍵查詢到其他數(shù)據(jù)列,因此需要查詢兩次。覆蓋索引的概念就是查詢可以通過在一個索引中完成,覆蓋索引效率會比較高,主鍵查詢是天然的覆蓋索引。合理的創(chuàng)建索引以及合理的使用查詢語句,當使用到覆蓋索引時可以獲得性能提升。比如SELECT email,uid FROM user_email WHERE uid=xx,如果uid不是主鍵,適當時候可以將索引添加為index(uid,email),以獲得性能提升。
索引的基本規(guī)范
- 索引數(shù)量控制,單張表中索引數(shù)量不超過5個,單個索引中的字段數(shù)不超過5個。
- 綜合評估數(shù)據(jù)密度和分布
- 考慮查詢和更新比例
為什么一張表中不能存在過多的索引?
InnoDB的secondary index使用b+tree來存儲,因此在UPDATE、DELETE、INSERT的時候需要對b+tree進行調(diào)整,過多的索引會減慢更新的速度。
對字符串使用前綴索引,前綴索引長度不超過8個字符,建議優(yōu)先考慮前綴索引,必要時可添加偽列并建立索引。
不要索引blob/text等字段,不要索引大型字段,這樣做會讓索引占用太多的存儲空間
什么是前綴索引?
前綴索引說白了就是對文本的前幾個字符(具體是幾個字符在建立索引時指定)建立索引,這樣建立起來的索引更小,所以查詢更快。前綴索引能有效減小索引文件的大小,提高索引的速度。但是前綴索引也有它的壞處:MySQL 不能在 ORDER BY 或 GROUP BY 中使用前綴索引,也不能把它們用作覆蓋索引(Covering Index)。
建立前綴索引的語法:ALTER TABLE table_name ADD KEY(column_name(prefix_length));
主鍵準則
- 表必須有主鍵
- 不使用更新頻繁的列
- 盡量不選擇字符串列
- 不使用UUID MD5 HASH
- 默認使用非空的唯一鍵
- 建議選擇自增或發(fā)號器
重要的SQL必須被索引,核心SQL優(yōu)先考慮覆蓋索索引
- UPDATE、DELETE語句的WHERE條件列
- ORDER BY、GROUP BY、DISTINCT的字段
- 多表JOIN的字段
區(qū)分度最大的字段放在前面
- 選擇篩選性更優(yōu)的字段放在最前面,比如單號、userid等,type,status等篩選性一般不建議放在最前面
- 索引根據(jù)左前綴原則,當建立一個聯(lián)合索引(a,b,c),則查詢條件里面只有包含(a)或(a,b)或(a,b,c)的時候才能走索引,(a,c)作為條件的時候只能使用到a列索引,所以這個時候要確定a的返回列一定不能太多,不然語句設計就不合理,(b,c)則不能走索引
- 合理創(chuàng)建聯(lián)合索引(避免冗余),(a,b,c) 相當于 (a) 、(a,b) 、(a,b,c)
索引禁忌
- 不在低基數(shù)列上建立索引,例如“性別”
- 不在索引列進行數(shù)學運算和函數(shù)運算
- 不要索引常用的小型表
- 盡量不使用外鍵
- 外鍵用來保護參照完整性,可在業(yè)務端實現(xiàn)
- 對父表和子表的操作會相互影響,降低可用性
- INNODB本身對online DDL的限制
MYSQL 中索引的限制
MYISAM 存儲引擎索引長度的總和不能超過 1000 字節(jié)
BLOB 和 TEXT 類型的列只能創(chuàng)建前綴索引
MYSQL 目前不支持函數(shù)索引
使用不等于 (!= 或者 >) 的時候, MYSQL 無法使用索引。
過濾字段使用函數(shù)運算 (如 abs (column)) 后, MYSQL無法使用索引。
join語句中join條件字段類型不一致的時候MYSQL無法使用索引
使用 LIKE 操作的時候如果條件以通配符開始 (如 ‘%abc…')時, MYSQL無法使用索引。
使用非等值查詢的時候, MYSQL 無法使用 Hash 索引。
語句設計規(guī)范
使用預編譯語句
- 只傳參數(shù),比傳遞SQL語句更高效
- 一次解析,多次使用
- 降低SQL注入概率
避免隱式轉(zhuǎn)換
會導致索引失效
充分利用前綴索引
- 必須是最左前綴
- 不可能同時用到兩個范圍條件
- 不使用%前導的查詢,如like “%ab”
不使用負向查詢,如not in/like
- 無法使用索引,導致全表掃描
- 全表掃描導致buffer pool利用率降低
避免使用存儲過程、觸發(fā)器、UDF、events等
- 讓數(shù)據(jù)庫做最擅長的事
- 降低業(yè)務耦合度,為sacle out、sharding留有余地
- 避開BUG
避免使用大表的JOIN
MySQL最擅長的是單表的主鍵/二級索引查詢
JOIN消耗較多內(nèi)存,產(chǎn)生臨時表
避免在數(shù)據(jù)庫中進行數(shù)學運算
- MySQL不擅長數(shù)學運算和邏輯判斷
- 無法使用索引
減少與數(shù)據(jù)庫的交互次數(shù)
- INSERT … ON DUPLICATE KEY UPDATE
- REPLACE INTO、INSERT IGNORE 、INSERT INTO VALUES(),(),()
- UPDATE … WHERE ID IN(10,20,50,…)
合理的使用分頁
限制分頁展示的頁數(shù)只能點擊上一頁、下一頁采用延遲關(guān)聯(lián)
如何正確的使用分頁?
假如有類似下面分頁語句:SELECT * FROM table ORDER BY id LIMIT 10000, 10由于MySQL里對LIMIT OFFSET的處理方式是取出OFFSET+LIMIT的所有數(shù)據(jù),然后去掉OFFSET,返回底部的LIMIT。所以,在OFFSET數(shù)值較大時,MySQL的查詢性能會非常低。可以使用id > n 的方式進行解決:
使用id > n 的方式有局限性,對于id不連續(xù)的問題,可以通過翻頁的時候同時傳入最后一個id方式來解決。
http://example.com/page.php?last=100
select * from table where id100 order by id desc limit 10
//上一頁
http://example.com/page.php?first=110
select * from table where id>110 order by id desc limit 10
這種方式比較大的缺點是,如果在瀏覽中有插入/刪除操作,翻頁不會更新,而總頁數(shù)可能仍然是根據(jù)新的count(*) 來計算,最終可能會產(chǎn)生某些記錄訪問不到。為了修補這個問題,可以繼續(xù)引入當前頁碼以及在上次翻頁以后是否有插入/刪除等影響總記錄數(shù)的操作并進行緩存
select * from table where id >= (select id from table order by id limit #offset#, 1)
- 拒絕大SQL,拆分成小SQL
- 充分利用QUERY CACHE
- 充分利用多核CPU
- 使用in代替or,in的值不超過1000個
- 禁止使用order by rand()
- 使用EXPLAIN診斷,避免生成臨時表
EXPLAIN語句(在MySQL客戶端中執(zhí)行)可以獲得MySQL如何執(zhí)行SELECT語句的信息。通過對SELECT語句執(zhí)行EXPLAIN,可以知曉MySQL執(zhí)行該SELECT語句時是否使用了索引、全表掃描、臨時表、排序等信息。盡量避免MySQL進行全表掃描、使用臨時表、排序等。詳見官方文檔。
用union all而不是union
union all與 union有什么區(qū)別?
union和union all關(guān)鍵字都是將兩個結(jié)果集合并為一個,但這兩者從使用和效率上來說都有所不同。
union在進行表鏈接后會篩選掉重復的記錄,所以在表鏈接后會對所產(chǎn)生的結(jié)果集進行排序運算,刪除重復的記錄再返回結(jié)果。如:
select * from test_union1
union select * from test_union2
這個SQL在運行時先取出兩個表的結(jié)果,再用排序空間進行排序刪除重復的記錄,最后返回結(jié)果集,如果表數(shù)據(jù)量大的話可能會導致用磁盤進行排序。
而union all只是簡單的將兩個結(jié)果合并后就返回。這樣,如果返回的兩個結(jié)果集中有重復的數(shù)據(jù),那么返回的結(jié)果集就會包含重復的數(shù)據(jù)了。
從效率上說,union all要比union快很多,所以,如果可以確認合并的兩個結(jié)果集中不包含重復的數(shù)據(jù)的話,那么就使用union all,如下:
select * from test_union1 union all select * from test_union2
- 程序應有捕獲SQL異常的處理機制
- 禁止單條SQL語句同時更新多個表
- 不使用select * ,SELECT語句只獲取需要的字段
- 消耗CPU和IO、消耗網(wǎng)絡帶寬
- 無法使用覆蓋索引
- 減少表結(jié)構(gòu)變更帶來的影響
- 因為大,select/join 可能生成臨時表
- UPDATE、DELETE語句不使用LIMIT
- INSERT語句必須顯式的指明字段名稱,不使用INSERT INTO table()
- INSERT語句使用batch提交(INSERT INTO table VALUES(),(),()……),values的個數(shù)不超過500
- 統(tǒng)計表中記錄數(shù)時使用COUNT(*),而不是COUNT(primary_key)和COUNT(1) 備注:僅針對Myisam
- 數(shù)據(jù)更新建議使用二級索引先查詢出主鍵,再根據(jù)主鍵進行數(shù)據(jù)更新
- 禁止使用跨庫查詢
- 禁止使用子查詢,建議將子查詢轉(zhuǎn)換成關(guān)聯(lián)查詢
- 針對varchar類型字段的程序處理,請驗證用戶輸入,不要超出其預設的長度;
分表規(guī)范
單表一到兩年內(nèi)數(shù)據(jù)量超過500w或數(shù)據(jù)容量超過10G考慮分表,需提前考慮歷史數(shù)據(jù)遷移或應用自行刪除歷史數(shù)據(jù),采用等量均衡分表或根據(jù)業(yè)務規(guī)則分表均可。要分表的數(shù)據(jù)表必須與DBA商量分表策略
- 用HASH進行散表,表名后綴使用十進制數(shù),下標從0開始
- 按日期時間分表需符合YYYY[MM][dd][HH]格式
- 采用合適的分庫分表策略。例如千庫十表、十庫百表等
- 禁止使用分區(qū)表,分區(qū)表對分區(qū)鍵有嚴格要,分區(qū)表在表變大后執(zhí)行DDL、SHARDING、單表恢復等都變得更加困難。
- 拆分大字段和訪問頻率低的字段,分離冷熱數(shù)據(jù)
行為規(guī)范
- 批量導入、導出數(shù)據(jù)必須提前通知DBA協(xié)助觀察
- 禁止在線上從庫執(zhí)行后臺管理和統(tǒng)計類查詢
- 禁止有super權(quán)限的應用程序賬號存在
- 產(chǎn)品出現(xiàn)非數(shù)據(jù)庫導致的故障時及時通知DBA協(xié)助排查
- 推廣活動或上線新功能必須提前通知DBA進行流量評估
- 數(shù)據(jù)庫數(shù)據(jù)丟失,及時聯(lián)系DBA進行恢復
- 對單表的多次alter操作必須合并為一次操作
- 不在MySQL數(shù)據(jù)庫中存放業(yè)務邏輯
- 重大項目的數(shù)據(jù)庫方案選型和設計必須提前通知DBA參與
- 對特別重要的庫表,提前與DBA溝通確定維護和備份優(yōu)先級
- 不在業(yè)務高峰期批量更新、查詢數(shù)據(jù)庫其他規(guī)范
- 提交線上建表改表需求,必須詳細注明所有相關(guān)SQL語句
其他規(guī)范
日志類數(shù)據(jù)不建議存儲在MySQL上,優(yōu)先考慮Hbase或OceanBase,如需要存儲請找DBA評估使用壓縮表存儲。
以上就是超詳細MySQL使用規(guī)范分享的詳細內(nèi)容,更多關(guān)于MySQL使用規(guī)范的資料請關(guān)注腳本之家其它相關(guān)文章!
您可能感興趣的文章:- MySQL 使用規(guī)范總結(jié)
- MySQL數(shù)據(jù)庫使用規(guī)范總結(jié)
- 老鳥帶你開發(fā)專業(yè)規(guī)范的MySQL啟動腳本
- MySQL開發(fā)規(guī)范與使用技巧總結(jié)
- mysql數(shù)據(jù)庫開發(fā)規(guī)范【推薦】
- MySQL數(shù)據(jù)庫命名規(guī)范及約定
- Mysql建表與索引使用規(guī)范詳解
- MYSQL 數(shù)據(jù)庫命名與設計規(guī)范
- 專業(yè)級的MySQL開發(fā)設計規(guī)范及SQL編寫規(guī)范