mysql架设_服务器架设MySQL开发规范与使用技巧
1.命名規(guī)范
1.庫名、表名、字段名必須使用小寫字母，并采用下劃線分割。
a)MySQL有配置參數(shù)lower_case_table_names，不可動態(tài)更改，linux系統(tǒng)默認(rèn)為 0，即庫表名以實際情況存儲，大小寫敏感。如果是1，以小寫存儲，大小寫不敏感。如果是2，以實際情況存儲，但以小寫比較。
b)如果大小寫混合使用，可能存在abc，Abc，ABC等多個表共存，容易導(dǎo)致混亂。
c)字段名顯示區(qū)分大小寫，但實際使?用不區(qū)分，即不可以建立兩個名字一樣但大小寫不一樣的字段。
d)為了統(tǒng)一規(guī)范， 庫名、表名、字段名使用小寫字母。
2.庫名、表名、字段名禁止超過32個字符。
庫名、表名、字段名支持最多64個字符，但為了統(tǒng)一規(guī)范、易于辨識以及減少傳輸量，禁止超過32個字符。
3.使用INNODB存儲引擎。
INNODB引擎是MySQL5.5版本以后的默認(rèn)引擘，支持事務(wù)、行級鎖，有更好的數(shù)據(jù)恢復(fù)能力、更好的并發(fā)性能，同時對多核、大內(nèi)存、SSD等硬件支持更好，支持?jǐn)?shù)據(jù)熱備份等，因此INNODB相比MyISAM有明顯優(yōu)勢。
4.庫名、表名、字段名禁止使用MySQL保留字。
當(dāng)庫名、表名、字段名等屬性含有保留字時，SQL語句必須用反引號引用屬性名稱，這將使得SQL語句書寫、SHELL腳本中變量的轉(zhuǎn)義等變得?非常復(fù)雜。
5.禁止使用分區(qū)表。
分區(qū)表對分區(qū)鍵有嚴(yán)格要求；分區(qū)表在表變大后，執(zhí)?行DDL、SHARDING、單表恢復(fù)等都變得更加困難。因此禁止使用分區(qū)表，并建議業(yè)務(wù)端手動SHARDING。
6.建議使用UNSIGNED存儲非負(fù)數(shù)值。
同樣的字節(jié)數(shù)，非負(fù)存儲的數(shù)值范圍更大。如TINYINT有符號為 -128-127，無符號為0-255。
7.建議使用INT UNSIGNED存儲IPV4。
用UNSINGED INT存儲IP地址占用4字節(jié)，CHAR(15)則占用15字節(jié)。另外，計算機(jī)處理整數(shù)類型比字符串類型快。使用INT UNSIGNED而不是CHAR(15)來存儲IPV4地址，通過MySQL函數(shù)inet_ntoa和inet_aton來進(jìn)行轉(zhuǎn)化。IPv6地址目前沒有轉(zhuǎn)化函數(shù)，需要使用DECIMAL或兩個BIGINT來存儲。
例如:
?12 SELECT INET_ATON('209.207.224.40'); 3520061480 SELECT INET_NTOA(3520061480); 209.207.224.40
8.強(qiáng)烈建議使用TINYINT來代替ENUM類型。
ENUM類型在需要修改或增加枚舉值時，需要在線DDL，成本較高；ENUM列值如果含有數(shù)字類型，可能會引起默認(rèn)值混淆。
9.使用VARBINARY存儲大小寫敏感的變長字符串或二進(jìn)制內(nèi)容。
VARBINARY默認(rèn)區(qū)分大小寫，沒有字符集概念，速度快。
10.INT類型固定占用4字節(jié)存儲
例如INT(4)僅代表顯示字符寬度為4位，不代表存儲長度。數(shù)值類型括號后面的數(shù)字只是表示寬度而跟存儲范圍沒有關(guān)系，比如INT(3)默認(rèn)顯示3位，空格補(bǔ)齊，超出時正常顯示，python、java客戶端等不具備這個功能。
11.區(qū)分使用DATETIME和TIMESTAMP。
存儲年使用YEAR類型。存儲日期使用DATE類型。 存儲時間(精確到秒)建議使用TIMESTAMP類型。
DATETIME和TIMESTAMP都是精確到秒，優(yōu)先選擇TIMESTAMP，因為TIMESTAMP只有4個字節(jié)，而DATETIME8個字節(jié)。同時TIMESTAMP具有自動賦值以及?自動更新的特性。注意：在5.5和之前的版本中，如果一個表中有多個timestamp列，那么最多只能有一列能具有自動更新功能。
如何使用TIMESTAMP的自動賦值屬性?
a)自動初始化，而且自動更新：
?1 column1 TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATECURRENT_TIMESTAMP
b)只是自動初始化：
?1 column1 TIMESTAMP DEFAULT CURRENT_TIMESTAMP
c)自動更新，初始化的值為0：
?1 column1 TIMESTAMP DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP
d)初始化的值為0：
?1 column1 TIMESTAMP DEFAULT 0
12.所有字段均定義為NOT NULL。
a)對表的每一行，每個為NULL的列都需要額外的空間來標(biāo)識。
b)B樹索引時不會存儲NULL值，所以如果索引字段可以為NULL，索引效率會下降。
c)建議用0、特殊值或空串代替NULL值。
MySQL使用技巧
1.將大字段、訪問頻率低的字段拆分到單獨的表中存儲，分離冷熱數(shù)據(jù)。
有利于有效利用緩存，防?止讀入無用的冷數(shù)據(jù)，較少磁盤IO，同時保證熱數(shù)據(jù)常駐內(nèi)存提?高緩存命中率。
2.禁止在數(shù)據(jù)庫中存儲明文密碼。
采用加密字符串存儲密碼，并保證密碼不可解密，同時采用隨機(jī)字符串加鹽保證密碼安全。
3.表必須有主鍵，推薦使用UNSIGNED自增列作為主鍵。
表沒有主鍵，INNODB會默認(rèn)設(shè)置隱藏的主鍵列；沒有主鍵的表在定位數(shù)據(jù)行的時候非常困難，也會降低基于行復(fù)制的效率。
4.禁止冗余索引。
索引是雙刃劍，會增加維護(hù)負(fù)擔(dān)，增?大IO壓力。(a,b,c)、(a,b)，后者為冗余索引。可以利用前綴索引來達(dá)到加速目的，減輕維護(hù)負(fù)擔(dān)。
5.禁止重復(fù)索引。
primary key a;uniq index a;重復(fù)索引增加維護(hù)負(fù)擔(dān)、占用磁盤空間，同時沒有任何益處。
6.不在低基數(shù)列上建立索引，例如“性別”。
大部分場景下，低基數(shù)列上建立索引的精確查找，相對于不建立索引的全表掃描沒有任何優(yōu)勢，而且增大了IO負(fù)擔(dān)。
7.合理使用覆蓋索引減少IO，避免排序。
覆蓋索引能從索引中獲取需要的所有字段，從?而避免回表進(jìn)行二次查找，節(jié)省IO。
INNODB存儲引擎中，secondary index(非主鍵索引，又稱為輔助索引、二級索引)沒有直接存儲行地址，而是存儲主鍵值。
如果用戶需要查詢secondary index中所不包含的數(shù)據(jù)列，則需要先通過secondary index查找到主鍵值，然后再通過主鍵查詢到其他數(shù)據(jù)列，因此需要查詢兩次。覆蓋索引則可以在?一個索引中獲取所有需要的數(shù)據(jù)，因此效率較高。
例如SELECT email，uid FROM user_email WHERE uid=xx，如果uid不是主鍵，適當(dāng)時候可以將索引添加為index(uid，email)，以獲得性能提升。
8.用IN代替OR。SQL語句中IN包含的值不應(yīng)過多，應(yīng)少于1000個。
IN是范圍查找，MySQL內(nèi)部會對IN的列表值進(jìn)行排序后查找，比OR效率更高。
9.表字符集使用UTF8，必要時可申請使用UTF8MB4字符集。
a)UTF8字符集存儲漢字占用3個字節(jié)，存儲英文字符占用一個字節(jié)。
b)UTF8統(tǒng)一而且通用，不會出現(xiàn)轉(zhuǎn)碼出現(xiàn)亂碼風(fēng)險。
c)如果遇到EMOJ等表情符號的存儲需求，可申請使用UTF8MB4字符集。
10.用UNION ALL代替UNION。
UNION ALL不需要對結(jié)果集再進(jìn)行排序。
11.禁止使用order by rand()。
order by rand()會為表增加一個偽列，然后用rand()函數(shù)為每一行數(shù)據(jù)計算出rand()值，然后基于該行排序，這通常都會生成磁盤上的臨時表，因此效率非常低。建議先使用rand()函數(shù)獲得隨機(jī)的主鍵值，然后通過主鍵獲取數(shù)據(jù)。
12.建議使用合理的分頁方式以提高分頁效率。
假如有類似下面分頁語句:
?1 SELECT * FROM table ORDER BY TIME DESC LIMIT 10000，10;
這種分頁方式會導(dǎo)致大量的io，因為MySQL使用的是提前讀取策略。
推薦分頁方式:
?123 SELECT * FROM table WHERE TIME
13.SELECT只獲取必要的字段，禁?止使用SELECT *。
減少網(wǎng)絡(luò)帶寬消耗；
能有效利用覆蓋索引；
表結(jié)構(gòu)變更對程序基本無影響。
14.SQL中避免出現(xiàn)now()、rand()、sysdate()、current_user()等不確定結(jié)果的函數(shù)。
語句級復(fù)制場景下，引起主從數(shù)據(jù)不一致；不確定值的函數(shù)，產(chǎn)?生的SQL語句無法利用QUERY CACHE。
15.采用合適的分庫分表策略。例如千庫十表、十庫百表等。
采用合適的分庫分表策略，有利于業(yè)務(wù)發(fā)展后期快速對數(shù)據(jù)庫進(jìn)行水平拆分，同時分庫可以有效利?用MySQL的多線程復(fù)制特性。
16.減少與數(shù)據(jù)庫交互次數(shù)，盡量采用批量SQL語句。
使用下面的語句來減少和db的交互次數(shù):
a)INSERT ... ON DUPLICATE KEY UPDATE
b)REPLACE INTO
c)INSERT IGNORE
d)INSERT INTO VALUES()
17.拆分復(fù)雜SQL為多個小SQL，避免大事務(wù)。
簡單的SQL容易使?用到MySQL的QUERY CACHE；減少鎖表時間特別是MyISAM；可以使用多核 CPU。
18.對同一個表的多次alter操作必須合并為一次操作。
mysql對表的修改絕大部分操作都需要鎖表并重建表，而鎖表則會對線上業(yè)務(wù)造成影響。為減少這種影響，必須把對表的多次alter操作合并為一次操作。例如，要給表t增加一個字段b，同時給已有的字段aa建立索引，
通常的做法分為兩步：
?1 alter table t add column b varchar(10);
然后增加索引：
?1 alter table t add index idx_aa(aa);
正確的做法是：
?1 alter table t add column b varchar(10),add index idx_aa(aa);
19.避免使用存儲過程、觸發(fā)器、視圖、自定義函數(shù)等。
這些高級特性有性能問題，以及未知BUG較多。業(yè)務(wù)邏輯放到數(shù)據(jù)庫會造成數(shù)據(jù)庫的DDL、SCALE OUT、SHARDING等變得更加困難。
20.禁止有super權(quán)限的應(yīng)用程序賬號存在。
安全第一。super權(quán)限會導(dǎo)致read only失效，導(dǎo)致較多詭異問題而且很難追蹤。
21.不要在MySQL數(shù)據(jù)庫中存放業(yè)務(wù)邏輯。
數(shù)據(jù)庫是有狀態(tài)的服務(wù)，變更復(fù)雜而且速度慢，如果把業(yè)務(wù)邏輯放到數(shù)據(jù)庫中，將會限制業(yè)務(wù)的快速發(fā)展。建議把業(yè)務(wù)邏輯提前，放到前端或中間邏輯層，而把數(shù)據(jù)庫作為存儲層，實現(xiàn)邏輯與存儲的分離。
與50位技術(shù)專家面對面20年技術(shù)見證，附贈技術(shù)全景圖總結(jié)
以上是生活随笔為你收集整理的mysql架设_服务器架设MySQL开发规范与使用技巧的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： mysql未知数据库_如何处理这个错误(
- 下一篇： mysql语句表_mysql表级sql语