MySQL資料庫設計常用技巧和設計規範（三）：資料庫設計規範

資料庫設計規範

• 資料庫設計規範
• • 2.1 資料庫設計
  • • 2.1.1 庫名
    • 2.1.2 表結構
    • 2.1.3 列資料類型優化
    • 2.1.4 索引設計
    • 2.1.5 分庫分表、分區表
    • 2.1.6 字元集
    • 2.1.7 程式層DAO設計建議
    • 2.1.8 一個規範的建表語句示例
  • 2.2 SQL編寫
  • • 2.2.1 DML語句
    • 2.2.2 多表連接配接
    • 2.2.3 事務
    • 2.2.5 線上禁止使用的SQL語句

資料庫設計規範

2.1 資料庫設計

以下所有規範會按照【高危】、【強制】、【建議】三個級别進行标注，遵守優先級從高到低。

對于不滿足【高危】和【強制】兩個級别的設計，DBA會強制打回要求修改。

2.1.1 庫名

• 【強制】庫的名稱必須控制在32個字元以内，相關子產品的表名與表名之間盡量提現join的關系，如user表和user_login表。
• 【強制】庫的名稱格式：業務系統名稱_子系統名，同一子產品使用的表名盡量使用統一字首。
• 【強制】一般分庫名稱命名格式是庫通配名_編号，編号從0開始遞增，比如wenda_001以時間進行分庫的名稱格式是“庫通配名_時間”
• 【強制】建立資料庫時必須顯式指定字元集，并且字元集隻能是utf8或者utf8mb4。建立資料庫SQL舉例：create database db1 default character set utf8;。

2.1.2 表結構

• 【強制】表和列的名稱必須控制在32個字元以内，表名隻能使用字母、數字和下劃線，一律小寫。
• 【強制】表名要求子產品名強相關，如師資系統采用”sz”作為字首，管道系統采用”qd”作為字首等。
• 【強制】建立表時必須顯式指定字元集為utf8或utf8mb4。
• 【強制】建立表時必須顯式指定表存儲引擎類型，如無特殊需求，一律為InnoDB。當需要使用除InnoDB/MyISAM/Memory以外的存儲引擎時，必須通過DBA稽核才能在生産環境中使用。因為Innodb表支援事務、行鎖、當機恢複、MVCC等關系型資料庫重要特性，為業界使用最多的MySQL存儲引擎。而這是其他大多數存儲引擎不具備的，是以首推InnoDB。
• 【強制】建表必須有comment
• 【建議】建表時關于主鍵：(1)強制要求主鍵為id，類型為int或bigint，且為auto_increment(2)辨別表裡每一行主體的字段不要設為主鍵，建議設為其他字段如user_id，order_id等，并建立unique key索引（可參考cdb.teacher表設計）。因為如果設為主鍵且主鍵值為随機插入，則會導緻innodb内部page分裂和大量随機I/O，性能下降。
• 【建議】核心表（如使用者表，金錢相關的表）必須有行資料的建立時間字段create_time和最後更新時間字段update_time，便于查問題。
• 【建議】表中所有字段必須都是NOT NULL屬性，業務可以根據需要定義DEFAULT值。因為使用NULL值會存在每一行都會占用額外存儲空間、資料遷移容易出錯、聚合函數計算結果偏差等問題。
• 【建議】建議對表裡的blob、text等大字段，垂直拆分到其他表裡，僅在需要讀這些對象的時候才去select。
• 【建議】反範式設計：把經常需要join查詢的字段，在其他表裡備援一份。如user_name屬性在user_account，user_login_log等表裡備援一份，減少join查詢。
• 【強制】中間表用于保留中間結果集，名稱必須以tmp_開頭。備份表用于備份或抓取源表快照，名稱必須以bak_開頭。中間表和備份表定期清理。
• 【強制】對于超過100W行的大表進行alter table，必須經過DBA稽核，并在業務低峰期執行。因為alter table會産生表鎖，期間阻塞對于該表的所有寫入，對于業務可能會産生極大影響。

2.1.3 列資料類型優化

• 【建議】表中的自增列（auto_increment屬性），推薦使用bigint類型。因為無符号int存儲範圍為-2147483648~2147483647（大約21億左右），溢出後會導緻報錯。
• 【建議】業務中選擇性很少的狀态status、類型type等字段推薦使用tinytint或者smallint類型節省存儲空間。
• 【建議】業務中IP位址字段推薦使用int類型，不推薦用char(15)。因為int隻占4位元組，可以用如下函數互相轉換，而char(15)占用至少15位元組。一旦表資料行數到了1億，那麼要多用1.1G存儲空間。 SQL：select inet_aton(‘192.168.2.12’); select inet_ntoa(3232236044); PHP: ip2long(‘192.168.2.12’); long2ip(3530427185);
• 【建議】不推薦使用enum，set。 因為它們浪費空間，且枚舉值寫死了，變更不友善。推薦使用tinyint或smallint。
• 【建議】不推薦使用blob，text等類型。它們都比較浪費硬碟和記憶體空間。在加載表資料時，會讀取大字段到記憶體裡進而浪費記憶體空間，影響系統性能。建議和PM、RD溝通，是否真的需要這麼大字段。Innodb中當一行記錄超過8098位元組時，會将該記錄中選取最長的一個字段将其768位元組放在原始page裡，該字段餘下内容放在overflow-page裡。不幸的是在compact行格式下，原始page和overflow-page都會加載。
• 【建議】存儲金錢的字段，建議用int，程式端乘以100和除以100進行存取。因為int占用4位元組，而double占用8位元組，空間浪費。
• 【建議】文本資料盡量用varchar存儲。因為varchar是變長存儲，比char更省空間。MySQL server層規定一行所有文本最多存65535位元組，是以在utf8字元集下最多存21844個字元，超過會自動轉換為mediumtext字段。而text在utf8字元集下最多存21844個字元，mediumtext最多存224/3個字元，longtext最多存232個字元。一般建議用varchar類型，字元數不要超過2700。

• 【建議】時間類型盡量選取timestamp。因為datetime占用8位元組，timestamp僅占用4位元組，但是範圍為1970-01-01 00:00:01到2038-01-01 00:00:00。更為高階的方法，選用int來存儲時間，使用SQL函數unix_timestamp()和from_unixtime()來進行轉換。

  詳細存儲大小參加下圖：

  

2.1.4 索引設計

• 【強制】InnoDB表必須主鍵為id int/bigint auto_increment,且主鍵值禁止被更新。
• 【建議】主鍵的名稱以“pk_”開頭，唯一鍵以“uk_”或“uq_”開頭，普通索引以“idx_”開頭，一律使用小寫格式，以表名/字段的名稱或縮寫作為字尾。
• 【強制】InnoDB和MyISAM存儲引擎表，索引類型必須為BTREE；MEMORY表可以根據需要選擇HASH或者BTREE類型索引。
• 【強制】單個索引中每個索引記錄的長度不能超過64KB。
• 【建議】單個表上的索引個數不能超過7個。
• 【建議】在建立索引時，多考慮建立聯合索引，并把區分度最高的字段放在最前面。如列userid的區分度可由select count(distinct userid)計算出來。
• 【建議】在多表join的SQL裡，保證被驅動表的連接配接列上有索引，這樣join執行效率最高。
• 【建議】建表或加索引時，保證表裡互相不存在備援索引。對于MySQL來說，如果表裡已經存在key(a,b)，則key(a)為備援索引，需要删除。

2.1.5 分庫分表、分區表

• 【強制】分區表的分區字段（partition-key）必須有索引，或者是組合索引的首列。
• 【強制】單個分區表中的分區（包括子分區）個數不能超過1024。
• 【強制】上線前RD或者DBA必須指定分區表的建立、清理政策。
• 【強制】通路分區表的SQL必須包含分區鍵。
• 【建議】單個分區檔案不超過2G，總大小不超過50G。建議總分區數不超過20個。
• 【強制】對于分區表執行alter table操作，必須在業務低峰期執行。
• 【強制】采用分庫政策的，庫的數量不能超過1024
• 【強制】采用分表政策的，表的數量不能超過4096
• 【建議】單個分表不超過500W行，ibd檔案大小不超過2G，這樣才能讓資料分布式變得性能更佳。
• 【建議】水準分表盡量用取模方式，日志、報表類資料建議采用日期進行分表。

2.1.6 字元集

• 【強制】資料庫本身庫、表、列所有字元集必須保持一緻，為utf8或utf8mb4。
• 【強制】前端程式字元集或者環境變量中的字元集，與資料庫、表的字元集必須一緻，統一為utf8。

2.1.7 程式層DAO設計建議

• 【建議】新的代碼不要用model，推薦使用手動拼SQL+綁定變量傳入參數的方式。因為model雖然可以使用面向對象的方式操作db，但是其使用不當很容易造成生成的SQL非常複雜，且model層自己做的強制類型轉換性能較差，最終導緻資料庫性能下降。
• 【建議】前端程式連接配接MySQL或者redis，必須要有連接配接逾時和失敗重連機制，且失敗重試必須有間隔時間。
• 【建議】前端程式報錯裡盡量能夠提示MySQL或redis原生态的報錯資訊，便于排查錯誤。
• 【建議】對于有連接配接池的前端程式，必須根據業務需要配置初始、最小、最大連接配接數，逾時時間以及連接配接回收機制，否則會耗盡資料庫連接配接資源，造成線上事故。
• 【建議】對于log或history類型的表，随時間增長容易越來越大，是以上線前RD或者DBA必須建立表資料清理或歸檔方案。
• 【建議】在應用程式設計階段，RD必須考慮并規避資料庫中主從延遲對于業務的影響。盡量避免從庫短時延遲（20秒以内）對業務造成影響，建議強制一緻性的讀開啟事務走主庫，或更新後過一段時間再去讀從庫。
• 【建議】多個并發業務邏輯通路同一塊資料（innodb表）時，會在資料庫端産生行鎖甚至表鎖導緻并發下降，是以建議更新類SQL盡量基于主鍵去更新。
• 【建議】業務邏輯之間加鎖順序盡量保持一緻，否則會導緻死鎖。
• 【建議】對于單表讀寫比大于10:1的資料行或單個列，可以将熱點資料放在緩存裡（如mecache或redis），加快通路速度，降低MySQL壓力。

2.1.8 一個規範的建表語句示例

一個較為規範的建表語句為：

CREATE TABLE user (
  `id` bigint(11) NOT NULL AUTO_INCREMENT,
  `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 '昵稱',
  `avatar` int(11) 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 COMMENT ‘使用者記錄建立的時間’,
  `update_time` timestamp NOT NULL COMMENT ‘使用者資料修改的時間’,
  `user_review_status` tinyint NOT NULL COMMENT ‘使用者資料稽核狀态，1為通過，2為稽核中，3為未通過，4為還未送出稽核’,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_user_id` (`user_id`),
  KEY `idx_username`(`username`),
  KEY `idx_create_time`(`create_time`,`user_review_status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='網站使用者基本資訊';
           

2.2 SQL編寫

2.2.1 DML語句

• 【強制】SELECT語句必須指定具體字段名稱，禁止寫成*。因為select *會将不該讀的資料也從MySQL裡讀出來，造成網卡壓力。且表字段一旦更新，但model層沒有來得及更新的話，系統會報錯。
• 【強制】insert語句指定具體字段名稱，不要寫成insert into t1 values(…)，道理同上。
• 【建議】insert into…values(XX),(XX),(XX)…。這裡XX的值不要超過5000個。值過多雖然上線很很快，但會引起主從同步延遲。
• 【建議】SELECT語句不要使用UNION，推薦使用UNION ALL，并且UNION子句個數限制在5個以内。因為union all不需要去重，節省資料庫資源，提高性能。
• 【建議】in值清單限制在500以内。例如select… where userid in(….500個以内…)，這麼做是為了減少底層掃描，減輕資料庫壓力進而加速查詢。
• 【建議】事務裡批量更新資料需要控制數量，進行必要的sleep，做到少量多次。
• 【強制】事務涉及的表必須全部是innodb表。否則一旦失敗不會全部復原，且易造成主從庫同步終端。
• 【強制】寫入和事務發往主庫，隻讀SQL發往從庫。
• 【強制】除靜态表或小表（100行以内），DML語句必須有where條件，且使用索引查找。
• 【強制】生産環境禁止使用hint，如sql_no_cache，force index，ignore key，straight join等。因為hint是用來強制SQL按照某個執行計劃來執行，但随着資料量變化我們無法保證自己當初的預判是正确的，是以我們要相信MySQL優化器！
• 【強制】where條件裡等号左右字段類型必須一緻，否則無法利用索引。
• 【建議】SELECT|UPDATE|DELETE|REPLACE要有WHERE子句，且WHERE子句的條件必需使用索引查找。
• 【強制】生産資料庫中強烈不推薦大表上發生全表掃描，但對于100行以下的靜态表可以全表掃描。查詢資料量不要超過表行數的25%，否則不會利用索引。
• 【強制】WHERE 子句中禁止隻使用全模糊的LIKE條件進行查找，必須有其他等值或範圍查詢條件，否則無法利用索引。
• 【建議】索引列不要使用函數或表達式，否則無法利用索引。如where length(name)='Admin’或where user_id+2=10023。
• 【建議】減少使用or語句，可将or語句優化為union，然後在各個where條件上建立索引。如where a=1 or b=2優化為where a=1… union …where b=2, key(a),key(b)。
• 【建議】分頁查詢，當limit起點較高時，可先用過濾條件進行過濾。如select a,b,c from t1 limit 10000,20;優化為: select a,b,c from t1 where id>10000 limit 20;。

2.2.2 多表連接配接

• 【強制】禁止跨db的join語句。因為這樣可以減少子產品間耦合，為資料庫拆分奠定堅實基礎。
• 【強制】禁止在業務的更新類SQL語句中使用join，比如update t1 join t2…。
• 【建議】不建議使用子查詢，建議将子查詢SQL拆開結合程式多次查詢，或使用join來代替子查詢。
• 【建議】線上環境，多表join不要超過3個表。
• 【建議】多表連接配接查詢推薦使用别名，且SELECT清單中要用别名引用字段，資料庫.表格式，如select a from db1.table1 alias1 where …。
• 【建議】在多表join中，盡量選取結果集較小的表作為驅動表，來join其他表。

2.2.3 事務

• 【建議】事務中INSERT|UPDATE|DELETE|REPLACE語句操作的行數控制在2000以内，以及WHERE子句中IN清單的傳參個數控制在500以内。
• 【建議】批量操作資料時，需要控制事務處理間隔時間，進行必要的sleep，一般建議值5-10秒。
• 【建議】對于有auto_increment屬性字段的表的插入操作，并發需要控制在200以内。
• 【強制】程式設計必須考慮“資料庫事務隔離級别”帶來的影響，包括髒讀、不可重複讀和幻讀。線上建議事務隔離級别為repeatable-read。
• 【建議】事務裡包含SQL不超過5個（支付業務除外）。因為過長的事務會導緻鎖資料較久，MySQL内部緩存、連接配接消耗過多等雪崩問題。
• 【建議】事務裡更新語句盡量基于主鍵或unique key，如update … where id=XX; 否則會産生間隙鎖，内部擴大鎖定範圍，導緻系統性能下降，産生死鎖。
• 【建議】盡量把一些典型外部調用移出事務，如調用webservice，通路檔案存儲等，進而避免事務過長。

• 【建議】對于MySQL主從延遲嚴格敏感的select語句，請開啟事務強制通路主庫。

  2.2.4 排序和分組

• 【建議】減少使用order by，和業務溝通能不排序就不排序，或将排序放到程式端去做。order by、group by、distinct這些語句較為耗費CPU，資料庫的CPU資源是極其寶貴的。
• 【建議】order by、group by、distinct這些SQL盡量利用索引直接檢索出排序好的資料。如where a=1 order by可以利用key(a,b)。
• 【建議】包含了order by、group by、distinct這些查詢的語句，where條件過濾出來的結果集請保持在1000行以内，否則SQL會很慢。

2.2.5 線上禁止使用的SQL語句

• 【高危】禁用update|delete t1 … where a=XX limit XX; 這種帶limit的更新語句。因為會導緻主從不一緻，導緻資料錯亂。建議加上order by PK。
• 【高危】禁止使用關聯子查詢，如update t1 set … where name in(select name from user where…);效率極其低下。
• 【強制】禁用procedure、function、trigger、views、event、外鍵限制。因為他們消耗資料庫資源，降低資料庫執行個體可擴充性。推薦都在程式端實作。
• 【強制】禁用insert into …on duplicate key update…在高并發環境下，會造成主從不一緻。
• 【強制】禁止聯表更新語句，如update t1,t2 where t1.id=t2.id…。

該篇文章轉載至一位大佬的總結，總結很詳細。如果還有補充，請在評論中添加。

https://github.com/jly8866/archer/blob/master/src/docs/mysql_db_design_guide.md