[[280514]]

命名規(guī)范

 1.庫(kù)名、表名、字段名必須使用小寫(xiě)字母，并采用下劃線分割。

a)MySQL有配置參數(shù)lower_case_table_names，不可動(dòng)態(tài)更改，Linux系統(tǒng)默認(rèn)為 0，即庫(kù)表名以實(shí)際情況存儲(chǔ)，大小寫(xiě)敏感。如果是1，以小寫(xiě)存儲(chǔ)，大小寫(xiě)不敏感。如果是2，以實(shí)際情況存儲(chǔ)，但以小寫(xiě)比較。

b)如果大小寫(xiě)混合使用，可能存在abc，Abc，ABC等多個(gè)表共存，容易導(dǎo)致混亂。

c)字段名顯示區(qū)分大小寫(xiě)，但實(shí)際使⽤用不區(qū)分，即不可以建立兩個(gè)名字一樣但大小寫(xiě)不一樣的字段。

d)為了統(tǒng)一規(guī)范， 庫(kù)名、表名、字段名使用小寫(xiě)字母。

2.庫(kù)名、表名、字段名禁止超過(guò)32個(gè)字符。

庫(kù)名、表名、字段名支持最多64個(gè)字符，但為了統(tǒng)一規(guī)范、易于辨識(shí)以及減少傳輸量，禁止超過(guò)32個(gè)字符。

3.使用INNODB存儲(chǔ)引擎。

INNODB引擎是MySQL5.5版本以后的默認(rèn)引擘，支持事務(wù)、行級(jí)鎖，有更好的數(shù)據(jù)恢復(fù)能力、更好的并發(fā)性能，同時(shí)對(duì)多核、大內(nèi)存、SSD等硬件支持更好，支持?jǐn)?shù)據(jù)熱備份等，因此INNODB相比MyISAM有明顯優(yōu)勢(shì)。

4.庫(kù)名、表名、字段名禁止使用MySQL保留字。

當(dāng)庫(kù)名、表名、字段名等屬性含有保留字時(shí)，SQL語(yǔ)句必須用反引號(hào)引用屬性名稱，這將使得SQL語(yǔ)句書(shū)寫(xiě)、SHELL腳本中變量的轉(zhuǎn)義等變得⾮非常復(fù)雜。

5.禁止使用分區(qū)表。

分區(qū)表對(duì)分區(qū)鍵有嚴(yán)格要求;分區(qū)表在表變大后，執(zhí)⾏行DDL、SHARDING、單表恢復(fù)等都變得更加困難。因此禁止使用分區(qū)表，并建議業(yè)務(wù)端手動(dòng)SHARDING。

6.建議使用UNSIGNED存儲(chǔ)非負(fù)數(shù)值。

同樣的字節(jié)數(shù)，非負(fù)存儲(chǔ)的數(shù)值范圍更大。如TINYINT有符號(hào)為 -128-127，無(wú)符號(hào)為0-255。

7.建議使用INT UNSIGNED存儲(chǔ)IPV4。

用UNSINGED INT存儲(chǔ)IP地址占用4字節(jié)，CHAR(15)則占用15字節(jié)。另外，計(jì)算機(jī)處理整數(shù)類型比字符串類型快。使用INT UNSIGNED而不是CHAR(15)來(lái)存儲(chǔ)IPV4地址，通過(guò)MySQL函數(shù)inet_ntoa和inet_aton來(lái)進(jìn)行轉(zhuǎn)化。IPv6地址目前沒(méi)有轉(zhuǎn)化函數(shù)，需要使用DECIMAL或兩個(gè)BIGINT來(lái)存儲(chǔ)。

例如：

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

8.強(qiáng)烈建議使用TINYINT來(lái)代替ENUM類型。

ENUM類型在需要修改或增加枚舉值時(shí)，需要在線DDL，成本較高;ENUM列值如果含有數(shù)字類型，可能會(huì)引起默認(rèn)值混淆。

9.使用VARBINARY存儲(chǔ)大小寫(xiě)敏感的變長(zhǎng)字符串或二進(jìn)制內(nèi)容。

VARBINARY默認(rèn)區(qū)分大小寫(xiě)，沒(méi)有字符集概念，速度快。

10.INT類型固定占用4字節(jié)存儲(chǔ)

例如INT(4)僅代表顯示字符寬度為4位，不代表存儲(chǔ)長(zhǎng)度。數(shù)值類型括號(hào)后面的數(shù)字只是表示寬度而跟存儲(chǔ)范圍沒(méi)有關(guān)系，比如INT(3)默認(rèn)顯示3位，空格補(bǔ)齊，超出時(shí)正常顯示，Python、Java客戶端等不具備這個(gè)功能。

11.區(qū)分使用DATETIME和TIMESTAMP。

存儲(chǔ)年使用YEAR類型。存儲(chǔ)日期使用DATE類型。存儲(chǔ)時(shí)間(精確到秒)建議使用TIMESTAMP類型。

DATETIME和TIMESTAMP都是精確到秒，優(yōu)先選擇TIMESTAMP，因?yàn)門IMESTAMP只有4個(gè)字節(jié)，而DATETIME8個(gè)字節(jié)。同時(shí)TIMESTAMP具有自動(dòng)賦值以及⾃自動(dòng)更新的特性。注意：在5.5和之前的版本中，如果一個(gè)表中有多個(gè)timestamp列，那么最多只能有一列能具有自動(dòng)更新功能。

如何使用TIMESTAMP的自動(dòng)賦值屬性?

a)自動(dòng)初始化，而且自動(dòng)更新： 
column1 TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATECURRENT_TIMESTAMP 
 
b)只是自動(dòng)初始化： 
column1 TIMESTAMP DEFAULT CURRENT_TIMESTAMP 
 
c)自動(dòng)更新，初始化的值為0： 
column1 TIMESTAMP DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP 
 
d)初始化的值為0： 
column1 TIMESTAMP DEFAULT 0 

12.所有字段均定義為NOT NULL。

a)對(duì)表的每一行，每個(gè)為NULL的列都需要額外的空間來(lái)標(biāo)識(shí)。

b)B樹(shù)索引時(shí)不會(huì)存儲(chǔ)NULL值，所以如果索引字段可以為NULL，索引效率會(huì)下降。

c)建議用0、特殊值或空串代替NULL值。

MySQL使用技巧

1.將大字段、訪問(wèn)頻率低的字段拆分到單獨(dú)的表中存儲(chǔ)，分離冷熱數(shù)據(jù)。

有利于有效利用緩存，防⽌止讀入無(wú)用的冷數(shù)據(jù)，較少磁盤(pán)IO，同時(shí)保證熱數(shù)據(jù)常駐內(nèi)存提⾼高緩存命中率。

2.禁止在數(shù)據(jù)庫(kù)中存儲(chǔ)明文密碼。

采用加密字符串存儲(chǔ)密碼，并保證密碼不可解密，同時(shí)采用隨機(jī)字符串加鹽保證密碼安全。

3.表必須有主鍵，推薦使用UNSIGNED自增列作為主鍵。

表沒(méi)有主鍵，INNODB會(huì)默認(rèn)設(shè)置隱藏的主鍵列;沒(méi)有主鍵的表在定位數(shù)據(jù)行的時(shí)候非常困難，也會(huì)降低基于行復(fù)制的效率。

4.禁止冗余索引。

索引是雙刃劍，會(huì)增加維護(hù)負(fù)擔(dān)，增⼤大IO壓力。(a,b,c)、(a,b)，后者為冗余索引。可以利用前綴索引來(lái)達(dá)到加速目的，減輕維護(hù)負(fù)擔(dān)。

5.禁止重復(fù)索引。

primary key a;uniq index a;重復(fù)索引增加維護(hù)負(fù)擔(dān)、占用磁盤(pán)空間，同時(shí)沒(méi)有任何益處。

6.不在低基數(shù)列上建立索引，例如“性別”。

大部分場(chǎng)景下，低基數(shù)列上建立索引的精確查找，相對(duì)于不建立索引的全表掃描沒(méi)有任何優(yōu)勢(shì)，而且增大了IO負(fù)擔(dān)。

7.合理使用覆蓋索引減少IO，避免排序。

覆蓋索引能從索引中獲取需要的所有字段，從⽽而避免回表進(jìn)行二次查找，節(jié)省IO。

INNODB存儲(chǔ)引擎中，secondary index(非主鍵索引，又稱為輔助索引、二級(jí)索引)沒(méi)有直接存儲(chǔ)行地址，而是存儲(chǔ)主鍵值。

如果用戶需要查詢secondary index中所不包含的數(shù)據(jù)列，則需要先通過(guò)secondary index查找到主鍵值，然后再通過(guò)主鍵查詢到其他數(shù)據(jù)列，因此需要查詢兩次。覆蓋索引則可以在⼀一個(gè)索引中獲取所有需要的數(shù)據(jù)，因此效率較高。

例如SELECT email，uid FROM user_email WHERE uid=xx，如果uid不是主鍵，適當(dāng)時(shí)候可以將索引添加為index(uid，email)，以獲得性能提升。

8.用IN代替OR。SQL語(yǔ)句中IN包含的值不應(yīng)過(guò)多，應(yīng)少于1000個(gè)。

IN是范圍查找，MySQL內(nèi)部會(huì)對(duì)IN的列表值進(jìn)行排序后查找，比OR效率更高。

9.表字符集使用UTF8，必要時(shí)可申請(qǐng)使用UTF8MB4字符集。

a)UTF8字符集存儲(chǔ)漢字占用3個(gè)字節(jié)，存儲(chǔ)英文字符占用一個(gè)字節(jié)。

b)UTF8統(tǒng)一而且通用，不會(huì)出現(xiàn)轉(zhuǎn)碼出現(xiàn)亂碼風(fēng)險(xiǎn)。

c)如果遇到EMOJ等表情符號(hào)的存儲(chǔ)需求，可申請(qǐng)使用UTF8MB4字符集。

10.用UNION ALL代替UNION。

UNION ALL不需要對(duì)結(jié)果集再進(jìn)行排序。

11.禁止使用order by rand()。

order by rand()會(huì)為表增加一個(gè)偽列，然后用rand()函數(shù)為每一行數(shù)據(jù)計(jì)算出rand()值，然后基于該行排序，這通常都會(huì)生成磁盤(pán)上的臨時(shí)表，因此效率非常低。建議先使用rand()函數(shù)獲得隨機(jī)的主鍵值，然后通過(guò)主鍵

獲取數(shù)據(jù)。

12.建議使用合理的分頁(yè)方式以提高分頁(yè)效率。

假如有類似下面分頁(yè)語(yǔ)句:

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

這種分頁(yè)方式會(huì)導(dǎo)致大量的io，因?yàn)镸ySQL使用的是提前讀取策略。

推薦分頁(yè)方式:

SELECT * FROM table WHERE TIME

13.SELECT只獲取必要的字段，禁⽌止使用SELECT *。

減少網(wǎng)絡(luò)帶寬消耗;

能有效利用覆蓋索引;

表結(jié)構(gòu)變更對(duì)程序基本無(wú)影響。

14.SQL中避免出現(xiàn)now()、rand()、sysdate()、current_user()等不確定結(jié)果的函數(shù)。

語(yǔ)句級(jí)復(fù)制場(chǎng)景下，引起主從數(shù)據(jù)不一致;不確定值的函數(shù)，產(chǎn)⽣生的SQL語(yǔ)句無(wú)法利用QUERY CACHE。

15.采用合適的分庫(kù)分表策略。例如千庫(kù)十表、十庫(kù)百表等。

采用合適的分庫(kù)分表策略，有利于業(yè)務(wù)發(fā)展后期快速對(duì)數(shù)據(jù)庫(kù)進(jìn)行水平拆分，同時(shí)分庫(kù)可以有效利⽤用MySQL

的多線程復(fù)制特性。

16.減少與數(shù)據(jù)庫(kù)交互次數(shù)，盡量采用批量SQL語(yǔ)句。

使用下面的語(yǔ)句來(lái)減少和db的交互次數(shù):

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

17.拆分復(fù)雜SQL為多個(gè)小SQL，避免大事務(wù)。

簡(jiǎn)單的SQL容易使⽤用到MySQL的QUERY CACHE;減少鎖表時(shí)間特別是MyISAM;可以使用多核 CPU。

18.對(duì)同一個(gè)表的多次alter操作必須合并為一次操作。

mysql對(duì)表的修改絕大部分操作都需要鎖表并重建表，而鎖表則會(huì)對(duì)線上業(yè)務(wù)造成影響。為減少這種影響，必須把對(duì)表的多次alter操作合并為一次操作。例如，要給表t增加一個(gè)字段b，同時(shí)給已有的字段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.避免使用存儲(chǔ)過(guò)程、觸發(fā)器、視圖、自定義函數(shù)等。

這些高級(jí)特性有性能問(wèn)題，以及未知BUG較多。業(yè)務(wù)邏輯放到數(shù)據(jù)庫(kù)會(huì)造成數(shù)據(jù)庫(kù)的DDL、SCALE OUT、

SHARDING等變得更加困難。

20.禁止有super權(quán)限的應(yīng)用程序賬號(hào)存在。

安全第一。super權(quán)限會(huì)導(dǎo)致read only失效，導(dǎo)致較多詭異問(wèn)題而且很難追蹤。

21.不要在MySQL數(shù)據(jù)庫(kù)中存放業(yè)務(wù)邏輯。

數(shù)據(jù)庫(kù)是有狀態(tài)的服務(wù)，變更復(fù)雜而且速度慢，如果把業(yè)務(wù)邏輯放到數(shù)據(jù)庫(kù)中，將會(huì)限制業(yè)務(wù)的快速發(fā)展。建議把業(yè)務(wù)邏輯提前，放到前端或中間邏輯層，而把數(shù)據(jù)庫(kù)作為存儲(chǔ)層，實(shí)現(xiàn)邏輯與存儲(chǔ)的分離。