導(dǎo)讀：

關(guān)于MySQL數(shù)據(jù)庫(kù)規(guī)范，相信大家多少看過(guò)一些文檔。本篇文章給大家詳細(xì)分類(lèi)總結(jié)了數(shù)據(jù)庫(kù)相關(guān)規(guī)范，從庫(kù)表命名設(shè)計(jì)規(guī)范講起，到索引設(shè)計(jì)規(guī)范，后面又給出SQL編寫(xiě)方面的建議。相信這些規(guī)范適用于大多數(shù)公司，也希望大家都能按照規(guī)范來(lái)使用我們的數(shù)據(jù)庫(kù)，這樣我們的數(shù)據(jù)庫(kù)才能發(fā)揮出更高的性能。

關(guān)于庫(kù)：

1.【強(qiáng)制】庫(kù)的名稱(chēng)必須控制在32個(gè)字符以?xún)?nèi)，英文一律小寫(xiě)。

2.【強(qiáng)制】庫(kù)的名稱(chēng)格式：業(yè)務(wù)系統(tǒng)名稱(chēng)_子系統(tǒng)名。

3.【強(qiáng)制】庫(kù)名只能使用英文字母，數(shù)字，下劃線(xiàn)，并以英文字母開(kāi)頭。

4.【強(qiáng)制】創(chuàng)建數(shù)據(jù)庫(kù)時(shí)必須顯式指定字符集，并且字符集只能是utf8或者utf8mb4。創(chuàng)建數(shù)據(jù)庫(kù)SQL舉例：Create database db1 default character set utf8;

5.【建議】臨時(shí)庫(kù)、表名以tmp_ 為前綴，并以日期為后綴，備份庫(kù)、表以 bak_ 為前綴，并以日期為后綴。

關(guān)于表

1.【強(qiáng)制】表和列的名稱(chēng)必須控制在32個(gè)字符以?xún)?nèi)，表名只能使用字母、數(shù)字和下劃線(xiàn)，一律小寫(xiě)。

2.【強(qiáng)制】表名要求模塊名強(qiáng)相關(guān)，同一模塊使用的表名盡量使用統(tǒng)一前綴。

3.【強(qiáng)制】創(chuàng)建表時(shí)必須顯式指定字符集為utf8或utf8mb4。

4.【強(qiáng)制】列名盡量不用關(guān)鍵字（如type,order等）。

5.【強(qiáng)制】創(chuàng)建表時(shí)必須顯式指定表存儲(chǔ)引擎類(lèi)型，如無(wú)特殊需求，一律為InnoDB。

6.【強(qiáng)制】建表必須有comment。

7.【強(qiáng)制】對(duì)于超過(guò)100W行的大表進(jìn)行alter table，必須經(jīng)過(guò)DBA審核，并在業(yè)務(wù)低峰期執(zhí)行，多個(gè)alter需整合在一起。

因?yàn)閍lter table會(huì)產(chǎn)生表鎖，期間阻塞對(duì)于該表的所有寫(xiě)入，對(duì)于業(yè)務(wù)可能會(huì)產(chǎn)生極大影響。

8.【建議】建表時(shí)關(guān)于主鍵：表必須有主鍵

(1)強(qiáng)制要求主鍵為id，類(lèi)型為int或bigint，且為auto_increment 建議使用unsigned無(wú)符號(hào)型。

(2)標(biāo)識(shí)表里每一行主體的字段不要設(shè)為主鍵，建議設(shè)為其他字段如user_id，order_id等，并建立unique key索引。

因?yàn)槿绻O(shè)為主鍵且主鍵值為隨機(jī)插入，則會(huì)導(dǎo)致innodb內(nèi)部page分裂和大量隨機(jī)I/O，性能下降。

9.【建議】核心表（如用戶(hù)表）必須有行數(shù)據(jù)的創(chuàng)建時(shí)間字段create_time和最后更新時(shí)間字段update_time，便于查問(wèn)題。

10.【建議】表中所有字段盡量都是NOT NULL屬性，業(yè)務(wù)可以根據(jù)需要定義DEFAULT值。

因?yàn)槭褂肗ULL值會(huì)存在每一行都會(huì)占用額外存儲(chǔ)空間、數(shù)據(jù)遷移容易出錯(cuò)、聚合函數(shù)計(jì)算結(jié)果偏差等問(wèn)題。

11.【建議】中間表用于保留中間結(jié)果集，名稱(chēng)必須以tmp_ 開(kāi)頭。備份表用于備份或抓取源表快照，名稱(chēng)必須以bak_開(kāi)頭。中間表和備份表定期清理。

12.【示范】一個(gè)較為規(guī)范的建表語(yǔ)句：

CREATE TABLE user_info ( `id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主鍵', `user_id` bigint(11) NOT NULL COMMENT '用戶(hù)id', `username` varchar(45) NOT NULL COMMENT '真實(shí)姓名', `email` varchar(30) NOT NULL COMMENT '用戶(hù)郵箱', `nickname` varchar(45) NOT NULL COMMENT '昵稱(chēng)', `birthday` date NOT NULL COMMENT '生日', `sex` tinyint(4) DEFAULT '0' COMMENT '性別', `short_introduce` varchar(150) DEFAULT NULL COMMENT '一句話(huà)介紹自己，最多50個(gè)漢字', `user_resume` varchar(300) NOT NULL COMMENT '用戶(hù)提交的簡(jiǎn)歷存放地址', `user_register_ip` int NOT NULL COMMENT '用戶(hù)注冊(cè)時(shí)的源ip', `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創(chuàng)建時(shí)間', `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改時(shí)間', `user_review_status` tinyint NOT NULL COMMENT '用戶(hù)資料審核狀態(tài)，1為通過(guò)，2為審核中，3為未通過(guò)，4為還未提交審核', PRIMARY KEY (`id`), UNIQUE KEY `uniq_user_id` (`user_id`), KEY `idx_username`(`username`), KEY `idx_create_time_status`(`create_time`,`user_review_status`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='網(wǎng)站用戶(hù)基本信息'

關(guān)于索引

1.【強(qiáng)制】InnoDB表必須主鍵為id int/bigint auto_increment,且主鍵值禁止被更新。

2.【強(qiáng)制】InnoDB和MyISAM存儲(chǔ)引擎表，索引類(lèi)型必須為BTREE。

3.【建議】主鍵的名稱(chēng)以 pk_ 開(kāi)頭，唯一鍵以 uniq_ 或  uk_  開(kāi)頭，普通索引以 idx_ 開(kāi)頭，一律使用小寫(xiě)格式，以字段的名稱(chēng)或縮寫(xiě)作為后綴。

4.【建議】單個(gè)表上的索引個(gè)數(shù)不能超過(guò)8個(gè)。

5.【建議】在建立索引時(shí)，多考慮建立聯(lián)合索引，并把區(qū)分度最高的字段放在最前面。如列userid的區(qū)分度可由select count(distinct userid)計(jì)算出來(lái)。

6.【建議】在多表join的SQL里，保證被驅(qū)動(dòng)表的連接列上有索引，這樣join執(zhí)行效率最高。

7.【建議】建表或加索引時(shí)，保證表里互相不存在冗余索引。
對(duì)于MySQL來(lái)說(shuō)，如果表里已經(jīng)存在key(a,b)，則key(a)為冗余索引，需要?jiǎng)h除。

SQL編寫(xiě)

1.【強(qiáng)制】程序端SELECT語(yǔ)句必須指定具體字段名稱(chēng)，禁止寫(xiě)成 *。

2.【強(qiáng)制】程序端insert語(yǔ)句指定具體字段名稱(chēng)，不要寫(xiě)成insert into t1 values(…)。

3.【強(qiáng)制】除靜態(tài)表或小表（100行以?xún)?nèi)），DML語(yǔ)句必須有where條件，且使用索引查找。

4.【強(qiáng)制】where條件里等號(hào)左右字段類(lèi)型必須一致，否則無(wú)法利用索引。

5.【強(qiáng)制】WHERE 子句中禁止只使用全模糊的LIKE條件進(jìn)行查找，必須有其他等值或范圍查詢(xún)條件，否則無(wú)法利用索引。

6.【強(qiáng)制】索引列不要使用函數(shù)或表達(dá)式，否則無(wú)法利用索引。如where length(name)='Admin'或where user_id+2=10023。

7.【建議】insert into…values(XX),(XX),(XX).. 這里XX的值不要超過(guò)5000個(gè)。
值過(guò)多雖然上線(xiàn)很很快，但會(huì)引起主從同步延遲。

8.【建議】SELECT語(yǔ)句不要使用UNION，推薦使用UNION ALL，并且UNION子句個(gè)數(shù)限制在5個(gè)以?xún)?nèi)。
因?yàn)閡nion all不需要去重，節(jié)省數(shù)據(jù)庫(kù)資源，提高性能。

9.【強(qiáng)制】禁止跨db的join語(yǔ)句。

10.【建議】不建議使用子查詢(xún)，建議將子查詢(xún)SQL拆開(kāi)結(jié)合程序多次查詢(xún)，或使用join來(lái)代替子查詢(xún)。

11.【建議】線(xiàn)上環(huán)境，多表join不要超過(guò)5個(gè)表。

12.【建議】在多表join中，盡量選取結(jié)果集較小的表作為驅(qū)動(dòng)表，來(lái)join其他表。

13.【建議】批量操作數(shù)據(jù)時(shí)，需要控制事務(wù)處理間隔時(shí)間，進(jìn)行必要的sleep。

14.【建議】事務(wù)里包含SQL不超過(guò)5個(gè)
因?yàn)檫^(guò)長(zhǎng)的事務(wù)會(huì)導(dǎo)致鎖數(shù)據(jù)較久，MySQL內(nèi)部緩存、連接消耗過(guò)多等問(wèn)題。

15.【建議】事務(wù)里更新語(yǔ)句盡量基于主鍵或unique key，如update … where id=XX;
否則會(huì)產(chǎn)生間隙鎖，內(nèi)部擴(kuò)大鎖定范圍，導(dǎo)致系統(tǒng)性能下降，產(chǎn)生死鎖。

16.【建議】減少使用order by，和業(yè)務(wù)溝通能不排序就不排序，或?qū)⑴判蚍诺匠绦蚨巳プ觥rder by、group by、distinct這些語(yǔ)句較為耗費(fèi)CPU，數(shù)據(jù)庫(kù)的CPU資源是極其寶貴的。

17.【建議】order by、group by、distinct這些SQL盡量利用索引直接檢索出排序好的數(shù)據(jù)。如where a=1 order by b可以利用key(a,b)。

18.【建議】包含了order by、group by、distinct這些查詢(xún)的語(yǔ)句，where條件過(guò)濾出來(lái)的結(jié)果集請(qǐng)保持在1000行以?xún)?nèi)，否則SQL會(huì)很慢。

以上就是MySQL數(shù)據(jù)庫(kù)使用規(guī)范的詳細(xì)內(nèi)容，更多關(guān)于MySQL使用規(guī)范的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！

最新評(píng)論