命名規範 1.庫名、表名、欄位名必須使用小寫字母,並採用下劃線分割。 a)MySQL有配置參數lower_case_table_names,不可動態更改,Linux系統預設為 0,即庫表名以實際情況存儲,大小寫敏感。如果是1,以小寫存儲,大小寫不敏感。如果是2,以實際情況存儲,但以小寫比較。 b)如 ...
命名規範
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預設區分大小寫,沒有字元集概念,速度快。
http://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:
1 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等表情符號的存儲需求,可申請使用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 UPDATE b)REPLACE INTO c)INSERT IGNORE d)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資料庫中存放業務邏輯。
資料庫是有狀態的服務,變更複雜而且速度慢,如果把業務邏輯放到資料庫中,將會限制業務的快速發展。建議把業務邏輯提前,放到前端或中間邏輯層,而把資料庫作為存儲層,實現邏輯與存儲的分離。