數(shù)據(jù)庫使用規(guī)范

關(guān)于庫：

【強制】庫的名稱必須控制在32個字符以內(nèi)蓖救，英文一律小寫。

【強制】庫的名稱格式：業(yè)務(wù)系統(tǒng)名稱_子系統(tǒng)名。

【強制】庫名只能使用英文字母豫喧，數(shù)字舆蝴，下劃線谦絮，并以英文字母開頭。

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

【建議】臨時庫、表名以tmp_ 為前綴赠潦，并以日期為后綴叫胖，備份庫、表以 bak_ 為前綴她奥，并以日期為后綴瓮增。

關(guān)于表：

【強制】表和列的名稱必須控制在32個字符以內(nèi)，表名只能使用字母哩俭、數(shù)字和下劃線绷跑，一律小寫。

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

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

【強制】列名盡量不用關(guān)鍵字（如type,order等）隙赁。

【強制】創(chuàng)建表時必須顯式指定表存儲引擎類型垦藏，如無特殊需求，一律為InnoDB伞访。

【強制】建表必須有comment掂骏。

【強制】對于超過100W行的大表進行alter table，必須經(jīng)過DBA審核厚掷，并在業(yè)務(wù)低峰期執(zhí)行弟灼，多個alter需整合在一起级解。

因為alter table會產(chǎn)生表鎖，期間阻塞對于該表的所有寫入袜爪，對于業(yè)務(wù)可能會產(chǎn)生極大影響蠕趁。

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

(1)強制要求主鍵為id，類型為int或bigint辛馆，且為auto_increment 建議使用unsigned無符號型俺陋。

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

因為如果設(shè)為主鍵且主鍵值為隨機插入苔可，則會導(dǎo)致innodb內(nèi)部page分裂和大量隨機I/O缴挖，性能下降。

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

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

因為使用NULL值會存在每一行都會占用額外存儲空間棚点、數(shù)據(jù)遷移容易出錯、聚合函數(shù)計算結(jié)果偏差等問題湾蔓。

【建議】中間表用于保留中間結(jié)果集瘫析，名稱必須以tmp_ 開頭。備份表用于備份或抓取源表快照默责，名稱必須以bak_開頭贬循。中間表和備份表定期清理桃序。

【示范】一個較為規(guī)范的建表語句：


CREATE TABLE user_info (

  `id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主鍵',

  `user_id` bigint(11) NOT NULL COMMENT '用戶id',

  `username` varchar(45) NOT NULL COMMENT '真實姓名',

  `email` varchar(30) NOT NULL COMMENT '用戶郵箱',

  `nickname` varchar(45) NOT NULL COMMENT '昵稱',

  `birthday` date NOT NULL COMMENT '生日',

  `sex` tinyint(4) DEFAULT '0' COMMENT '性別',

  `short_introduce` varchar(150) DEFAULT NULL COMMENT '一句話介紹自己亏掀，最多50個漢字',

  `user_resume` varchar(300) NOT NULL COMMENT '用戶提交的簡歷存放地址',

  `user_register_ip` int NOT NULL COMMENT '用戶注冊時的源ip',

  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創(chuàng)建時間',

  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改時間',

  `user_review_status` tinyint NOT NULL COMMENT '用戶資料審核狀態(tài)，1為通過泛释，2為審核中怜校，3為未通過茄茁，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)站用戶基本信息'

關(guān)于索引：

【強制】InnoDB表必須主鍵為id int/bigint auto_increment,且主鍵值禁止被更新付燥。

【強制】InnoDB和MyISAM存儲引擎表键科，索引類型必須為BTREE勋颖。

【建議】主鍵的名稱以 pk_ 開頭饭玲，唯一鍵以 uniq_ 或 uk_ 開頭茄厘，普通索引以 idx_ 開頭次哈，一律使用小寫格式亿乳，以字段的名稱或縮寫作為后綴葛假。

【建議】單個表上的索引個數(shù)不能超過8個聊训。

【建議】在建立索引時带斑，多考慮建立聯(lián)合索引，并把區(qū)分度最高的字段放在最前面挂滓。如列userid的區(qū)分度可由select count(distinct userid)計算出來赶站。

【建議】在多表join的SQL里贝椿，保證被驅(qū)動表的連接列上有索引瑟蜈，這樣join執(zhí)行效率最高踪栋。

【建議】建表或加索引時，保證表里互相不存在冗余索引囤官。

對于MySQL來說党饮，如果表里已經(jīng)存在key(a,b)，則key(a)為冗余索引蹲堂，需要刪除。

SQL編寫：

【強制】程序端SELECT語句必須指定具體字段名稱朽基，禁止寫成 *。

【強制】程序端insert語句指定具體字段名稱霎俩，不要寫成insert into t1 values(…)。

【強制】除靜態(tài)表或小表（100行以內(nèi)）学密，DML語句必須有where條件腻暮，且使用索引查找。

【強制】where條件里等號左右字段類型必須一致毯侦，否則無法利用索引哭靖。

【強制】WHERE 子句中禁止只使用全模糊的LIKE條件進行查找试幽，必須有其他等值或范圍查詢條件卦碾，否則無法利用索引。

【強制】索引列不要使用函數(shù)或表達式，否則無法利用索引。如where length(name)='Admin'或where user_id+2=10023钝诚。

【建議】insert into…values(XX),(XX),(XX).. 這里XX的值不要超過5000個疹鳄。

值過多雖然上線很很快拧略，但會引起主從同步延遲。

【建議】SELECT語句不要使用UNION瘪弓，推薦使用UNION ALL袱饭，并且UNION子句個數(shù)限制在5個以內(nèi)川无。

因為union all不需要去重，節(jié)省數(shù)據(jù)庫資源虑乖，提高性能懦趋。

【強制】禁止跨db的join語句。

【建議】不建議使用子查詢疹味，建議將子查詢SQL拆開結(jié)合程序多次查詢仅叫，或使用join來代替子查詢。

【建議】線上環(huán)境糙捺，多表join不要超過5個表诫咱。

【建議】在多表join中，盡量選取結(jié)果集較小的表作為驅(qū)動表洪灯，來join其他表坎缭。

【建議】批量操作數(shù)據(jù)時，需要控制事務(wù)處理間隔時間婴渡，進行必要的sleep幻锁。

建議】事務(wù)里包含SQL不超過5個

因為過長的事務(wù)會導(dǎo)致鎖數(shù)據(jù)較久，MySQL內(nèi)部緩存边臼、連接消耗過多等問題哄尔。

【建議】事務(wù)里更新語句盡量基于主鍵或unique key，如update … where id=XX;

否則會產(chǎn)生間隙鎖柠并，內(nèi)部擴大鎖定范圍岭接，導(dǎo)致系統(tǒng)性能下降，產(chǎn)生死鎖臼予。

【建議】減少使用order by鸣戴，和業(yè)務(wù)溝通能不排序就不排序，或?qū)⑴判蚍诺匠绦蚨巳プ稣呈啊rder by窄锅、group by、distinct這些語句較為耗費CPU缰雇，數(shù)據(jù)庫的CPU資源是極其寶貴的入偷。

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

【建議】包含了order by暇咆、group by锋爪、distinct這些查詢的語句丙曙，where條件過濾出來的結(jié)果集請保持在1000行以內(nèi)，否則SQL會很慢其骄。