MySQL資料庫規范
一、基礎規范
【強制】使用InnoDB存儲引擎
解讀：InnoDB存儲引擎是MySQL默認存儲引擎，支持事務和行級鎖，並發性能更好，CPU及內存緩存頁優化使得資源利用率更高
【強制】使用utf8字符集，如果有字段需要存儲emoji表情之類的，則需要將字段或表設置成utf8mb4
解讀：萬國碼，無需轉碼，無亂碼風險，節省空間，utf8mb4向下兼容utf8
【強制】數據表、數據字段必須加入中文註釋
解讀：便於識別表和字段的用途
【強制】禁止使用存儲過程、視圖、觸發器、Event
解讀：高並發大數據的互聯網業務，架構設計思路是“解放資料庫CPU，將計算轉移到服務層”，並發量大的情況下，這些功能很可能將資料庫拖死，業務邏輯放到服務層具備更好的擴展性，能夠輕易實現“增機器就加性能”。資料庫擅長存儲與索引，CPU計算還是上移吧
【強制】禁止存儲大文件或者大照片
解讀：大文件和照片存儲在文件系統，資料庫裡存URI多好
二、命名規范
【強制】庫名、表名、字段名：小寫字母，下劃線風格，禁止數字開頭，禁止兩個下劃線中間隻出現數字，禁止復數名詞。
解讀：正例：getter_admin，task_config，level3_name 反例：GetterAdmin，taskConfig，level_3_name
【強制】命名中不允許出現MYSQL資料庫中的保留字。如desc、range、match、delayed等，請參考MySQL官方保留字。
【強制】索引命名格式為：索引類型_字段名。普通索引名idx_xxx，唯一索引名uniq_xxx
三、表設計規范
【強制】單實例表數目必須小於500，單表列數目必須小於30
【建議】無特殊情況下，建議顯式指定一個無業務用途的自增unsigned bigint型主鍵
解讀：主鍵遞增，數據行寫入可以提高插入性能，可以避免page分裂，減少表碎片提升空間和內存的使用
【建議】表必須有create_time和update_time兩個字段，並指定datetime類型
解讀：
建表時建議按照以下格式設計：
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP;
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
加字段時建議按照以下格式設計：
ADD COLUMN `create_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00';
ADD COLUMN `update_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP;
timestamp的使用有以下的限制，所以建議統一使用datetime類型
a) timestamp支持的時間范圍小，范圍是從’1970-01-01 00:00:01′ 到 ’2038-01-19 03:14:07′ UTC;
b) timestamp的插入和查詢受當地時區的影響;
c) timestamp的屬性會受到MySQL版本和SQL mode的影響。
【強制】禁止使用外鍵，如果有外鍵完整性約束，需要應用程式控制
解讀：外鍵會導致表與表之間耦合，update與delete操作都會涉及相關聯的表，十分影響sql的性能，甚至會造成死鎖。高並發情況下容易造成資料庫性能，大數據高並發業務場景資料庫使用以性能優先
【建議】單表行數超過500萬行或者單表容量超過2GB，才推薦進行分庫分表。
解讀：如果預計三年後的數據量根本達不到這個級別，請不要在創建表時就分庫分表。
四、字段設計規范
【強制】把字段設定為NOT NULL並且提供默認值
解讀：
以下隻是建議的默認值，可根據業務需要設置成其它默認值
tinyint/int/bigint 類型默認值：0
char/varchar 類型默認值：' '
date 類型默認值：'0000-00-00'
time 類型默認值：'00:00:00'
datetime 類型默認值：'0000-00-00 00:00:00'
a)null的列使索引/索引統計/值比較都更加復雜，對MySQL來說更難優化
b)null 這種類型MySQL內部需要進行特殊處理，增加資料庫處理記錄的復雜性;同等條件下，表中有較多空字段的時候，資料庫的處理性能會降低很多
c)null值需要更多的存儲空間，無論是表還是索引中每行中的null的列都需要額外的空間來標識
d)對null 的處理時候，隻能采用is null或is not null，而不能采用=、in、<、<>、!=、not in這些操作符號。如：where name!=’shenjian’，如果存在name為null值的記錄，查詢結果就不會包含name為null值的記錄
【建議】禁止使用TEXT、BLOB類型，可以做垂直拆分到子表中
解讀：會浪費更多的磁盤和內存空間，非必要的大量的大字段查詢會淘汰掉熱數據，導致內存命中率急劇降低，影響資料庫性能
【強制】存儲貨幣用decimal或整數類型，禁止使用float和double
解讀：float和double在存儲的時候，存在精度損失的問題
【建議】小數類型用decimal，禁止使用float和double
解讀：float和double在存儲的時候，存在精度損失的問題，很可能在值的比較時，得到不正確結果。如果存儲的數據范圍超過decimal的范圍，建議將數據拆成整數和小數分開存儲。
【建議】枚舉類型禁止使用ENUM，可使用TINYINT代替
解讀：
a)增加新的ENUM值要做DDL操作
b)ENUM的內部實際存儲就是整數
【建議】表達是與否概念的字段，必須使用is_xxx的方式命名，數據類型是unsigned tinyint( 1表示是，0表示否)
【建議】使用varchar(20)存儲手機號
解讀：
a)涉及到區號或者國傢代號，可能出現+-()
b)手機號不會做數學運算
c)varchar可以支持模糊查詢，例如：like“138%”
【建議】如果存儲的字符串長度幾乎相等，使用char定長字符串類型。
【建議】varchar是可變長字符串，不預先分配存儲空間，長度不要超過5000。
【建議】字段允許適當冗餘，以提高性能，但是必須考慮數據同步的情況。
解讀：冗餘字段應遵循：不是頻繁修改的字段，不是varchar超長字段，更不能是text字段。正例：商品類目名稱使用頻率高，字段長度短，名稱基本一成不變，可在相關聯的表中冗餘存儲類目名稱，避免關聯查詢。
【建議】選擇合適的字符存儲長度，不但節約資料庫表空間、節約索引存儲，更重要的是提升檢索速度。
五、索引設計規范
【建議】單表索引建議控制在5個以內
解讀：索引過多會增加存儲開銷和增刪改的開銷
【建議】禁止在更新十分頻繁、區分度不高的屬性上建立索引
解讀：
a)更新會變更B+樹，更新頻繁的字段建立索引會大大降低資料庫性能
b)“性別”這種區分度不大的屬性，建立索引是沒有什麼意義的，不能有效過濾數據，性能與全表掃描類似
【建議】盡量使用組合索引，建立組合索引時必須把區分度高的字段放在前面，字段數不允許超過5個
解讀：能夠更加有效的過濾數據，索引上字段超過5個時，實際已經起不到有效過濾數據的作用瞭
【建議】在排序、分組、取唯一字段上創建索引，經常與其他表進行關聯的表，在關聯字段上應該建立索引，經常出現在Where子句中的字段，特別是大表的字段，應該建立索引
【建議】業務上具有唯一特性的字段，即使是組合字段，也必須建成唯一索引。
解讀：不要以為唯一索引影響瞭insert速度，這個速度損耗可以忽略，但提高查找速度是明顯的;另外，即使在應用層做瞭非常完善的校驗和控制，隻要沒有唯一索引，根據墨菲定律，必然有臟數據產生。
【建議】在varchar字段上建立索引時，必須指定索引長度，沒必要對全字段建立索引，根據實際文本區分度決定索引長度。
解讀：索引的長度與區分度是一對矛盾體，一般對字符串類型數據，長度為20的索引，區分度會高達90%以上，可以使用count(distinct left(列名, 索引長度))/count(*)的區分度來確定。
六、SQL使用規范
【強制】禁用select *，必須指定列
解讀：
a)讀取不需要的列會增加CPU、IO、NET消耗
b)不能有效的利用覆蓋索引
c)使用SELECT *容易在增加或者刪除字段後出現程式BUG
【強制】insert語句需指定列，INSERT INTO table_name (列1, 列2,…) VALUES (值1, 值2,….)
解讀：避免在增加或者刪除字段後出現程式BUG
【強制】禁止使用屬性隱式轉換
解讀：SELECT uid FROM t_user WHERE phone=13812345678 會導致全表掃描，而不能命中phone索引，因為phone是varchar類型，需要添加引號
【建議】禁止在WHERE條件的屬性上使用函數或者表達式
解讀：SELECT uid FROM t_user WHERE from_unixtime(day)>='2017-02-15' 會導致全表掃描
正確的寫法是：SELECT uid FROM t_user WHERE day>= unix_timestamp('2017-02-15 00:00:00')
【強制】禁止負向查詢，以及%開頭的模糊查詢
解讀：
a)負向查詢條件：NOT、!=、<>、!<、!>、NOT IN、NOT LIKE等，會導致全表掃描
b)%開頭的模糊查詢，會導致全表掃描
【建議】禁止大表使用JOIN查詢，禁止大表使用子查詢
解讀：會產生臨時表，消耗較多內存與CPU，極大影響資料庫性能
【建議】禁止使用OR條件，必須改為IN查詢
解讀：舊版本Mysql的OR查詢是不能命中索引的，即使能命中索引，為何要讓資料庫耗費更多的CPU幫助實施查詢優化呢?
【強制】應用程式必須捕獲SQL異常，並有相應處理
【建議】超過三個表禁止join。需要join的字段，數據類型保持絕對一致;多表關聯查詢時，保證被關聯的字段需要有索引。
解讀：即使雙表join也要註意表索引、SQL性能。
【強制】頁面搜尋嚴禁左模糊或者全模糊，如果需要請走搜尋引擎來解決。
解讀：索引文件具有B-Tree的最左前綴匹配特性，如果左邊的值未確定，那麼無法使用此索引。
【建議】使用order by請註意利用索引的有序性。
解讀：order by 最後的字段是組合索引的一部分，並且放在索引組合順序的最後，避免出現file_sort的情況，影響查詢性能。
正例：where a=? and b=? order by c; 索引：a_b_c
反例：索引中有范圍查找，那麼索引有序性無法利用，如：WHERE a>10 ORDER BY b; 索引a_b無法排序。
【建議】利用覆蓋索引來進行查詢操作，來避免回表操作。
解讀：如果一本書需要知道第11章是什麼標題，會翻開第11章對應的那一頁嗎?目錄瀏覽一下就好，這個目錄就是起到覆蓋索引的作用。 正例：能夠建立索引的種類：主鍵索引、唯一索引、普通索引，而覆蓋索引是一種查詢的一種效果，用explain的結果，extra列會出現：using index。
【建議】利用延遲關聯或者子查詢優化超多分頁場景。
解讀：MySQL並不是跳過offset行，而是取offset+N行，然後返回放棄前offset行，返回N行，那當offset特別大的時候，效率就非常的低下，要麼控制返回的總頁數，要麼對超過特定閾值的頁數進行SQL改寫。 正例：先快速定位需要獲取的id段，然後再關聯： SELECT a.* FROM 表1 a, (select id from 表1 where 條件 LIMIT 100000,20 ) b where a.id=b.id
【建議】SQL性能優化的目標：至少要達到 range 級別，要求是ref級別，如果可以是consts最好。
解讀：
1)consts 單表中最多隻有一個匹配行(主鍵或者唯一索引)，在優化階段即可讀取到數據。 2)ref 指的是使用普通的索引(normal index)。
3)range 對索引進行范圍檢索。
反例：explain表的結果，type=index，索引物理文件全掃描，速度非常慢，這個index級別比較range還低，與全表掃描是小巫見大巫。
