MySQL開發設計規範（完整版）

命名規範

l  庫名、表名、欄位名禁止超過32個字元。

l  所有資料庫物件名稱必須使用小寫字母並用下劃線分割

不同的資料庫名  DbName dbname

不同的表名  Table table tabLe

l  所有資料庫物件名稱(庫名、表名、欄位名)禁止使用MySQL保留關鍵字

select id,username,from,age from tb_user

有兩個from,MySQL並不清楚這兩個from有什麼區別,執行上面會報錯

select id,username,`from`,age from tb_user 正確

l  資料庫物件的命名要能做到見名識義，並且最好不要超過32個字元

例如:使用者賬號表  user_account

     使用者資料庫  aisino_userdb

l  臨時庫表必須以tmp_開頭並以日期為字尾, 例如tmp_test01_20161218

l   備份庫備份表必須以bak_開頭並以日期戳為字尾, 例如bak_test01_20161218

l  所有儲存相同資料的列名和列型別必須一致

CREATE TABLE customer_inf(

customer_inf id int unsigned auto increment not null comment '自增id',

customer_id int unsigned not null comment 'customer_login表的自增id,

.........................................

CREATE TABLE order_master(

order_id id int unsigned not null auto increment comment '訂單ID',

customer_id int unsigned not null comment '下單人ID',

.........................................

基礎規範

l  使用INNODB儲存引擎

支援事務，行級鎖，更好的恢復性，高併發下效能更好

l  INNODB表必須有主鍵列，使用auto_increment

l  資料庫和表的字符集統一使用UTF8

統一字符集可以避免由於字符集轉換產生的亂碼

MySQL中UTF8字符集漢字佔3個位元組，ASCII碼佔1個位元組

l  表必須有主鍵

l  所有表和欄位都需要新增註釋

使用comment新增表和列的備註

好處:從一開始就進行資料字典的維護和整理

l  表數量不超過300個

l  儘量控制單表資料量的大小，建議資料控制在500萬行以內

500萬並不是MySQL資料庫的限制，MySQL最多可以儲存多少萬資料？

這種限制取決於儲存設定和檔案系統

  可以用歷史資料歸檔，分庫分表等手段來控制資料量大小

l  禁止在資料庫中儲存圖片，影片和檔案等二進位制資料

把圖片或檔案儲存到相應的檔案伺服器中，資料庫中只存放圖片或檔案的地址資訊

通常檔案很大，查詢IO操作耗時,會影響資料庫的效能

利用更有效的利用快取，避免讀入無用的冷資料

經常一起使用的列放到一個表中

l  禁止線上上做資料庫壓力測試

l  禁止從開發環境，測試環境直連生產環境資料庫

資料庫各個環境之間要進行隔離

l  臨時表和備份表必須定期清理(備份歸檔)

庫表設計

l  禁止使用分割槽表

分割槽表在物理上表現為多個檔案，在邏輯上表現為一個表

謹慎選擇分割槽鍵，跨分割槽查詢效率可能更低

 對於大表建議採用物理分表的方式管理大資料

l  拆分大欄位和訪問頻率低的欄位，分離冷熱資料

儘量做到冷熱資料分離，減小表的寬頻

MySQL限制最多儲存4096列,每一行資料的大小不能超過65535個位元組

減少磁碟 IO，保證熱資料的記憶體快取命中率

l  按日期時間分表需符合YYYY[MM][DD][HH]格式

l  採用合適的分庫分表策略。

欄位設計

l  避免使用TEXT,BLOB資料型別

建議把BLOB或TEXT列分離到單獨的擴充套件表中

l  優先選擇符合儲存需要的最小的資料型別, 使用INT UNSIGNED儲存IPV4

將字串轉化為數字型別儲存:

INET_ATON('255.255.255.255')=4294967295

將數字型別轉化為字串:

INET_NTOA(4294967295)='255.255.255.255'

l  使用TINYINT來代替ENUM型別

l  表字符集儘量選擇UTF8

l  避免使用ENUM列舉資料型別

修改ENUM值需要使用ALTER語句

ENUM型別的ORDER BY操作效率低，需要額外操作

禁止使用數值作為ENUM的列舉值

l  所有欄位均定義為NOT NULL

索引NULL列需要額外的空間來儲存，所以要佔用更多的空間

進行比較和計算時候要對NULL值做特殊處理，索引會失效

l  使用UNSIGNED儲存非負整數

無符號值取值範圍:

UNSIGNED INT (0--4294967295)

l  同財務相關的金額類資料，必須使用decimal型別

Decimal型別為精準浮點數，在計算時不會丟失精度

佔用空間由定義的寬度決定

可用於儲存比bigint更大的整數資料

l  儘量避免使用字串儲存日期型資料

缺點1：無法用日期函式進行比較和計算

缺點2：用字串儲存日期要佔用更多的空間

l  使用TIMESTAMP或DATATIME型別儲存時間

TIMESTAMP  1970-01-01 00:00:01 --2038-01-19  03:14:07

l  INT型別固定佔用4位元組儲存,TIMESTAMP佔用4位元組和INT相同，但比INT可讀性高

l  禁止在資料庫中儲存明文密碼

索引規範

l  限制每張表的索引數量，建議單表索引數量不超過5個

l  禁止給表中的每一列都建立單獨的索引,單個索引中的欄位數不超過5個

l  每個Innodb表都要有一個主鍵

不使用更新頻繁的列作為主鍵，不使用多列主鍵

主鍵建議選擇使用自增ID值

l  不使用更新頻繁的列

l  為經常需要排序、分組和聯合操作的欄位建立索引

常見索引列建議

SELECT,UPDATE,DELETE語句的WHERE從句中的列

包含在ORDER BY,GROUP BY,DISTINCT中的欄位

多表JOIN的關聯列

l  為常作為查詢條件的欄位建立索引

l  刪除不再使用或者很少使用的索引

l  最左字首匹配原則，非常重要的原則。

l  儘量選擇區分度高的列作為索引

l  避免建立冗餘索引和重複索引

index(a,b,c), index(a,b) ,index(a)

 a列是冗餘索引

l  對於頻繁的查詢優先考慮使用覆蓋索引

覆蓋索引：就是包含了所有查詢欄位的索引

  全部欄位不但是指where從句中出現的列，也包括出現在select從句，order by和group by從句中的列

  覆蓋索引的好處:避免Innodb表進行索引的二次查詢

  可以把隨機IO變為順序IO加快查詢效率

SQL設計規範

l  避免隱式轉換,會導致索引失效

l  充分利用字首索引

l  必須是最左字首

l  JOIN消耗較多記憶體，產生臨時表

l  避免在資料庫中進行數學運算

l  WHERE從句中禁止對列進行函式轉換和計算

對列進行函式轉換或計算會導致無法使用索引

where date(createtime)='20160901'

改進:

 where createtime >= '20160901' and createtime < '20160902'

l  使用不等於（!= 或者 <>），無法使用索引

l  使用LIKE操作（如'%abc...'）時，無法使用索引

l  拒絕大SQL，拆分成小SQL

不支援SQL並行查詢，MySQL一個SQL只能使用一個CPU進行計算

l  避免使用JOIN關聯太多的表

每join一個表會多佔用一部分記憶體(join_buffer_size)

會產生臨時表操作，影響查詢效率

 MySQL最多允許關聯61個表，建議不超過5個

l  程式連線不同的資料庫使用不同的賬號，禁止跨庫查詢

l  用UNION ALL而不是UNION

UNION會把所有資料放到臨時表中後再進行去重操作

UNION ALL不會再對結果集進行去重操作

l  禁止使用select *進行查詢及沒有欄位列表的insert操作

l  禁止單條SQL語句同時更新多個表

l  儘量不使用select *,而使用SELECT <欄位列表>查詢

SELECT *返回結果中包含很多並不需要的欄位，消耗更多的CPU和IO以及網路頻寬資源

無法使用覆蓋索引

行為規範

l  批次匯入、匯出資料必須提前通知DBA協助觀察

超過100萬行的批次寫操作，要分批多次進行操作

l  對大表資料結構的修改一定要謹慎，會造成嚴重的鎖表操作，尤其是生產環境，是不能忍受的

對於大表使用pt-online-schema-change修改表結構，不會鎖表

避免大表修改產生的主從延遲

避免在對錶欄位進行修改時進行鎖表

l  推廣活動或上線新功能須提前通知DBA,請求壓力評估

l  對於程式連線資料庫賬號，遵循許可權最小原則

l  程式使用的賬號原則上不準有drop許可權

l  不使用super許可權連線資料庫

禁止為程式使用的賬號賦予super許可權

super許可權只能留給DBA處理問題的賬號使用

l  對單表的多次alter操作必須合併為一次操作

l  產品出現非資料庫導致的故障時及時通知DBA協助排查

l  資料庫資料丟失，及時聯絡DBA進行恢復

l  重大專案的資料庫方案選型和設計必須提前通知DBA參與

l  對特別重要的庫表，提前與DBA溝通確定維護和備份優先順序

l  不在業務高峰期批次更新、查詢資料庫

l  批次匯入、匯出資料須提前通知DBA，請求協助觀察

l  資料庫DDL及重要SQL及早提及DBA評審

l  提及線上DDL需求，所有SQL語句須有備註說明