MySQL開發規範與提示總結，mysql開發提示

MySQL開發規範與提示總結，mysql開發提示
1.命名規範

1.庫名、表名、欄位名必須使用小寫字母，並採用底線分割。
a)MySQL有配置參數lower_case_table_names，不可動態更改，linux系統預設為 0，即庫表名以實際情況儲存，大小寫敏感。如果是1，以小寫儲存，大小寫不敏感。如果是2，以實際情況儲存，但以小寫比較。
b)如果大小寫混合使用，可能存在abc，Abc，ABC等多個表共存，容易導致混亂。
c)欄位名顯示區分大小寫，但實際使⽤用不區分，即不可以建立兩個名字一樣但大小寫不一樣的欄位。
d)為了統一規範， 庫名、表名、欄位名使用小寫字母。

2.庫名、表名、欄位名禁止超過32個字元。
庫名、表名、欄位名支援最多64個字元，但為了統一規範、易於辨識以及減少傳輸量，禁止超過32個字元。

3.使用INNODB儲存引擎。
INNODB引擎是MySQL5.5版本以後的預設引擘，支援事務、行級鎖，有更好的資料恢複能力、更好的並發效能，同時對多核、大記憶體、SSD等硬體支援更好，支援資料熱備份等，因此INNODB相比MyISAM有明顯優勢。

4.庫名、表名、欄位名禁止使用MySQL保留字。
當庫名、表名、欄位名等屬性含有保留字時，SQL語句必須用反引號引用屬性名稱，這將使得SQL語句書寫、SHELL指令碼中變數的轉義等變得⾮非常複雜。

5.禁止使用分區表。
分區表對分區鍵有嚴格要求；分區表在表變大後，執⾏行DDL、SHARDING、單表恢複等都變得更加困難。因此禁止使用分區表，並建議業務端手動SHARDING。

6.建議使用UNSIGNED儲存非負數值。
同樣的位元組數，非負儲存的數值範圍更大。如TINYINT有符號為 -128-127，無符號為0-255。

7.建議使用INT UNSIGNED儲存IPV4。
用UNSINGED INT儲存IP地址佔用4位元組，CHAR(15)則佔用15位元組。另外，電腦處理整數類型比字串類型快。使用INT UNSIGNED而不是CHAR(15)來儲存IPV4地址，通過MySQL函數inet_ntoa和inet_aton來進行轉化。IPv6地址目前沒有轉化函數，需要使用DECIMAL或兩個BIGINT來儲存。
例如:

SELECT INET_ATON('209.207.224.40'); 3520061480SELECT INET_NTOA(3520061480); 209.207.224.40

8.強烈建議使用TINYINT來代替ENUM類型。
ENUM類型在需要修改或增加枚舉值時，需要線上DDL，成本較高；ENUM列值如果含有數字類型，可能會引起預設值混淆。

9.使用VARBINARY儲存大小寫敏感的變長字串或二進位內容。
VARBINARY預設區分大小寫，沒有字元集概念，速度快。

10.INT類型固定佔用4位元組儲存
例如INT(4)僅代表顯示字元寬度為4位，不代表格儲存體長度。數實值型別括弧後面的數字只是表示寬度而跟儲存範圍沒有關係，比如INT(3)預設顯示3位，空格補齊，超出時正常顯示，python、java用戶端等不具備這個功能。

11.區分使用DATETIME和TIMESTAMP。
儲存年使用YEAR類型。儲存日期使用DATE類型。 儲存時間(精確到秒)建議使用TIMESTAMP類型。
DATETIME和TIMESTAMP都是精確到秒，優先選擇TIMESTAMP，因為TIMESTAMP只有4個位元組，而DATETIME8個位元組。同時TIMESTAMP具有自動賦值以及⾃自動更新的特性。注意：在5.5和之前的版本中，如果一個表中有多個timestamp列，那麼最多隻能有一列能具有自動更新功能。

如何使用TIMESTAMP的自動賦值屬性?
a)自動初始化，而且自動更新：

column1 TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATECURRENT_TIMESTAMP

b)只是自動初始化：

 column1 TIMESTAMP DEFAULT CURRENT_TIMESTAMP

c)自動更新，初始化的值為0：

 column1 TIMESTAMP DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP

d)初始化的值為0：

column1 TIMESTAMP DEFAULT 0

12.所有欄位均定義為NOT NULL。
a)對錶的每一行，每個為NULL的列都需要額外的空間來標識。
b)B樹索引時不會儲存NULL值，所以如果索引欄位可以為NULL，索引效率會下降。
c)建議用0、特殊值或空串代替NULL值。

MySQL提示

1.將大欄位、訪問頻率低的欄位拆分到單獨的表中儲存，分離冷熱資料。
有利於有效利用緩衝，防⽌止讀入無用的冷資料，較少磁碟IO，同時保證熱資料常駐記憶體提⾼高快取命中率。

2.禁止在資料庫中儲存純文字密碼。
採用加密字串儲存密碼，並保證密碼不可解密，同時採用隨機字串加鹽保證密碼安全。

3.表必須有主鍵，推薦使用UNSIGNED自增列作為主鍵。
表沒有主鍵，INNODB會預設設定隱藏的主鍵列；沒有主鍵的表在定位元據行的時候非常困難，也會降低基於行複製的效率。

4.禁止冗餘索引。
索引是雙刃劍，會增加維護負擔，增⼤大IO壓力。(a,b,c)、(a,b)，後者為冗餘索引。可以利用首碼索引來達到加速目的，減輕維護負擔。

5.禁止重複索引。
primary key a;uniq index a;重複索引增加維護負擔、佔用磁碟空間，同時沒有任何益處。

6.不在低基數列上建立索引，例如“性別”。
大部分情境下，低基數列上建立索引的精確尋找，相對於不建立索引的全表掃描沒有任何優勢，而且增大了IO負擔。

7.合理使用覆蓋索引減少IO，避免排序。
覆蓋索引能從索引中擷取需要的所有欄位，從⽽而避免回表進行二次尋找，節省IO。
INNODB儲存引擎中，secondary index(非主鍵索引，又稱為輔助索引、二級索引)沒有直接儲存行地址，而是儲存主索引值。
如果使用者需要查詢secondary index中所不包含的資料列，則需要先通過secondary index尋找到主索引值，然後再通過主鍵查詢到其他資料列，因此需要查詢兩次。覆蓋索引則可以在⼀一個索引中擷取所有需要的資料，因此效率較高。
例如SELECT email，uid FROM user_email WHERE uid=xx，如果uid不是主鍵，適當時候可以將索引添加為index(uid，email)，以獲得效能提升。

8.用IN代替OR。SQL語句中IN包含的值不應過多，應少於1000個。
IN是範圍尋找，MySQL內部會對IN的列表值進行排序後尋找，比OR效率更高。

9.表字元集使用UTF8，必要時可申請使用UTF8MB4字元集。
a)UTF8字元集儲存漢字佔用3個位元組，儲存英文字元佔用一個位元組。
b)UTF8統一而且通用，不會出現轉碼出現亂碼風險。
c)如果遇到EMOJ等Emoji的儲存需求，可申請使用UTF8MB4字元集。

10.用UNION ALL代替UNION。
UNION ALL不需要對結果集再進行排序。

11.禁止使用order by rand()。
order by rand()會為表增加一個偽列，然後用rand()函數為每一行資料計算出rand()值，然後基於該行排序，這通常都會產生磁碟上的暫存資料表，因此效率非常低。建議先使用rand()函數獲得隨機的主索引值，然後通過主鍵
擷取資料。

12.建議使用合理的分頁方式以提高分頁效率。
假如有類似下面分頁語句:

SELECT * FROM table ORDER BY TIME DESC LIMIT 10000，10;

這種分頁方式會導致大量的io，因為MySQL使用的是提前讀取策略。
推薦分頁方式:

SELECT * FROM table WHERE TIME<last_TIME ORDER BY TIME DESC LIMIT 10.SELECT * FROM table inner JOIN (SELECT id FROM table ORDER BY TIME LIMIT 10000，10) as tUSING(id)

13.SELECT只擷取必要的欄位，禁⽌止使用SELECT *。
減少網路頻寬消耗；
能有效利用覆蓋索引；
表結構變更對程式基本無影響。

14.SQL中避免出現now()、rand()、sysdate()、current_user()等不確定結果的函數。
語句級複製情境下，引起主從資料不一致；不確定值的函數，產⽣生的SQL語句無法利用QUERY CACHE。

15.採用合適的分庫分表策略。例如千庫十表、十庫百表等。
採用合適的分庫分表策略，有利於業務發展後期快速對資料庫進行水平分割，同時分庫可以有效利⽤用MySQL
的多線程複製特性。

16.減少與資料庫互動次數，盡量採用批量SQL語句。
使用下面的語句來減少和db的互動次數:

a)INSERT ... ON DUPLICATE KEY UPDATEb)REPLACE INTOc)INSERT IGNOREd)INSERT INTO VALUES() 

17.拆分複雜SQL為多個小SQL，避免大事務。
簡單的SQL容易使⽤用到MySQL的QUERY CACHE；減少鎖表時間特別是MyISAM；可以使用多核 CPU。

18.對同一個表的多次alter操作必須合并為一次操作。
mysql對錶的修改絕大部分操作都需要鎖表並重建表，而鎖表則會對線上業務造成影響。為減少這種影響，必須把對錶的多次alter操作合并為一次操作。例如，要給表t增加一個欄位b，同時給已有的欄位aa建立索引，
通常的做法分為兩步：

alter table t add column b varchar(10);

然後增加索引：

alter table t add index idx_aa(aa);

正確的做法是：

alter table t add column b varchar(10),add index idx_aa(aa);

19.避免使用預存程序、觸發器、視圖、自訂函數等。
這些進階特性有效能問題，以及未知BUG較多。商務邏輯放到資料庫會造成資料庫的DDL、SCALE OUT、
SHARDING等變得更加困難。

20.禁止有super許可權的應用程式帳號存在。
安全第一。super許可權會導致read only失效，導致較多詭異問題而且很難追蹤。

21.不要在MySQL資料庫中存放商務邏輯。
資料庫是有狀態的服務，變更複雜而且速度慢，如果把商務邏輯放到資料庫中，將會限制業務的快速發展。建議把商務邏輯提前，放到前端或中間邏輯層，而把資料庫作為儲存層，實現邏輯與儲存的分離。

著作權聲明：本文為博主原創文章，未經博主允許不得轉載。