前言 | 筆記歸檔
這周公司開(kāi)發(fā)工作比較悠閑，工作幾乎壓在設(shè)計(jì)上游逗载，于是整理了下公司開(kāi)發(fā)的文檔哆窿，包括項(xiàng)目架構(gòu)、服務(wù)器運(yùn)維厉斟、規(guī)范更耻、api對(duì)接、基本依賴信息等捏膨。如下是包含其中的MySQL開(kāi)發(fā)規(guī)范秧均，根據(jù)社區(qū)很多的博文參考以及結(jié)合自身小團(tuán)隊(duì)開(kāi)發(fā)情況總結(jié)。
命名規(guī)范
對(duì)象名稱必須使用小寫号涯，多單詞統(tǒng)一使用下劃線分割
命名的單詞必須做到顧名思義目胡、簡(jiǎn)潔，表名長(zhǎng)度不要超過(guò)16個(gè)字符链快，字段名稱長(zhǎng)度不要超過(guò)32個(gè)字符
禁止使用保留字并且盡量少用含有關(guān)鍵詞來(lái)命名
臨時(shí)表必須以
tmp_開(kāi)頭誉己、以日期結(jié)尾，備份表必須以bak_開(kāi)頭域蜗、以日期結(jié)尾
基礎(chǔ)規(guī)范
-
盡可能地使用
InnoDB作為表的存儲(chǔ)引擎在
MySQL 5.6以后巨双，InnoDB被設(shè)置成默認(rèn)的存儲(chǔ)引擎，支持事務(wù)和行級(jí)鎖霉祸。 -
數(shù)據(jù)庫(kù)和數(shù)據(jù)表統(tǒng)一使用
UTF8MB4字符編碼UTF8MB4字符編碼支持中文儲(chǔ)存以及表情存儲(chǔ)筑累，兼容性杠杠的。 -
所有的表和字段必須添加注釋
這個(gè)是好習(xí)慣的問(wèn)題丝蹭，即使做到了顧名思義慢宗，以防萬(wàn)一哪天健忘或理解錯(cuò)誤，同時(shí)給后人留下后路奔穿，提高維護(hù)性镜沽。使用
comment設(shè)定注釋。 -
盡量控制表行數(shù)在500萬(wàn)以內(nèi)
數(shù)據(jù)量越多贱田，則查詢的效率越低缅茉，同時(shí)會(huì)導(dǎo)致長(zhǎng)時(shí)間占用高內(nèi)存以及磁盤
IO過(guò)高。數(shù)據(jù)量膨大建議采用分表男摧、合理分區(qū)等方案蔬墩。 -
盡可能采用冷熱數(shù)據(jù)分離策略
在
MySQL中统阿，數(shù)據(jù)表列數(shù)最大限制為4096列 ，每條元祖數(shù)據(jù)總和大小不能超過(guò)65535字節(jié)筹我，常用的字段與基本不常用的字段、細(xì)分不同業(yè)務(wù)的數(shù)據(jù)分開(kāi)表設(shè)計(jì)存儲(chǔ)帆离，減小表寬度蔬蕊，保證熱數(shù)據(jù)的內(nèi)存緩存命中率，降低CPU使用率以及降低IO流哥谷。 -
禁止以圖片岸夯、文件等二進(jìn)制數(shù)據(jù)
MySQL雖然支持對(duì)文件對(duì)象的存儲(chǔ)，但是開(kāi)發(fā)人員是不允許们妥、不推薦這樣做的猜扮。文件通常是很大的，轉(zhuǎn)成二進(jìn)制數(shù)據(jù)將是一串很長(zhǎng)的字符串监婶，無(wú)疑占用數(shù)據(jù)庫(kù)很大的存儲(chǔ)空間旅赢，在數(shù)據(jù)庫(kù)讀寫更是消耗內(nèi)存和占用大量的IO流，最終導(dǎo)致查詢的效率低下惑惶。一般文件是存放于文件服務(wù)器煮盼，將文件服務(wù)器的路徑存儲(chǔ)于數(shù)據(jù)庫(kù)中。
行為與流程規(guī)范
禁止在線上做數(shù)據(jù)庫(kù)的壓力測(cè)試
對(duì)應(yīng)的環(huán)境使用對(duì)應(yīng)的數(shù)據(jù)庫(kù)比如測(cè)試環(huán)境一定要使用測(cè)試環(huán)境的數(shù)據(jù)庫(kù)
super權(quán)限只能屬于DBA带污，不能賦予項(xiàng)目程序-
養(yǎng)成查看
SQL運(yùn)行性能的習(xí)慣僵控，可以借用性能分析工具譬如：
EXPLAIN語(yǔ)句 |showprofile|mySQLsla等。 -
禁止在業(yè)務(wù)高峰期批量更新鱼冀、查詢數(shù)據(jù)
可以在流量比較低的凌晨跑批操作报破。
-
活動(dòng)推廣、系統(tǒng)上線以及平臺(tái)上新務(wù)必對(duì)流量進(jìn)行評(píng)估
防患于未然千绪、否則可能造成數(shù)據(jù)庫(kù)服務(wù)器流量瓶頸進(jìn)而導(dǎo)致影響業(yè)務(wù)充易。
-
所有建表前都要確定字段的類型、長(zhǎng)度以及索引方可建表
確保表結(jié)構(gòu)設(shè)計(jì)為最優(yōu)是前期數(shù)據(jù)庫(kù)最大的優(yōu)化
所有對(duì)表的結(jié)構(gòu)荸型、數(shù)據(jù)的修改務(wù)必經(jīng)過(guò)
DBA的審閱和同意
表設(shè)計(jì)規(guī)范
-
盡可能每張表的索引數(shù)量控制在5個(gè)以內(nèi)
索引具有提高查詢的效率的好處也有降低寫操作效率的壞處蔽氨，甚至?xí)档筒樵兊降男省Ｍ瑫r(shí)索引也是占用內(nèi)存空間的帆疟，因而應(yīng)該合理控制索引的數(shù)量鹉究。
-
每一張
InnoDB表都必須含有一個(gè)主鍵InnoDB 是一種索引組織表：數(shù)據(jù)的存儲(chǔ)的邏輯順序和索引的順序是相同的。每個(gè)表都可以有多個(gè)索引踪宠，但是表的存儲(chǔ)順序只能有一種 InnoDB是按照主鍵索引的順序來(lái)組織表的自赔。不要使用可能會(huì)更新的列作為主鍵，同時(shí)盡量不要使用
UUID柳琢、MD5绍妨、HASH等無(wú)序的字符串作為主鍵润脸。在沒(méi)有特別的情況下，要使用自增的整型或發(fā)號(hào)器作為主鍵他去。 -
盡可能避免使用外鍵約束
外鍵可以保證數(shù)據(jù)的準(zhǔn)確性毙驯、參照完整性，每次進(jìn)行寫操作時(shí)都會(huì)走校驗(yàn)數(shù)據(jù)知否正確的流程灾测，將會(huì)有損寫操作的性能爆价，數(shù)據(jù)的參照完整性建議在業(yè)務(wù)層實(shí)現(xiàn)。倘若字表的寫操作很少的情況下務(wù)必使用外鍵約束媳搪。
-
設(shè)置數(shù)據(jù)表架構(gòu)應(yīng)考慮后期擴(kuò)展型
體驗(yàn)產(chǎn)品和架構(gòu)師的交流和能力铭段、對(duì)業(yè)務(wù)的熟悉度。
-
遵循范式與冗余平衡原則
第一范式：具有原子性
第二范式：主鍵列與非主鍵列遵循完全函數(shù)依賴關(guān)系
第三范式：非主鍵列之間沒(méi)有傳遞函數(shù)依賴關(guān)系
合理的原則能夠體驗(yàn)出數(shù)據(jù)庫(kù)的可操作性秦爆、穩(wěn)定性以及性能
nice序愚。范式設(shè)計(jì)是數(shù)據(jù)結(jié)構(gòu)的一種思想，但是我們應(yīng)當(dāng)靈活使用等限，一味追求三范式無(wú)疑會(huì)影響程序的性能爸吮，適當(dāng)?shù)娜哂嗍强梢蕴岣卟樵兊男实模疤嵋ＷC是主鍵的冗余望门。 -
控制每張表的字段在20以內(nèi)拗胜，否則業(yè)務(wù)分表
數(shù)據(jù)表的寬度與內(nèi)存占用的大小成正比，在進(jìn)行讀寫操作時(shí)怒允，數(shù)據(jù)庫(kù)程序?qū)⒈斫Y(jié)構(gòu)與數(shù)據(jù)載入內(nèi)存埂软，當(dāng)表寬度越長(zhǎng)消耗的內(nèi)存越多、越占
IO流纫事，導(dǎo)致操作的效率下降勘畔。將可能將字段按照業(yè)務(wù)細(xì)分、冷熱的條件進(jìn)行分表設(shè)計(jì)丽惶。
字段設(shè)計(jì)規(guī)范
-
盡可能不要在表中建立顧名思義的擴(kuò)展字段
比如
ext炫七、ext_1、extend_n钾唬，時(shí)間一長(zhǎng)万哪，好幾個(gè)這樣的字段，即使每一個(gè)都有comment抡秆，也會(huì)造成SQL的可讀性奕巍，特別是在構(gòu)建SQL語(yǔ)句的時(shí)候。 -
優(yōu)先設(shè)置占存儲(chǔ)空間最小的類型和長(zhǎng)度
合理設(shè)置字段的類型和長(zhǎng)度儒士，可以節(jié)省
MySQL的表空間的止，是性能優(yōu)化的姿勢(shì)之一。同時(shí)着撩，索引列定義空間越大也會(huì)導(dǎo)致建立索引的所需空間也越大诅福。應(yīng)當(dāng)嚴(yán)禁定義字段匾委，譬如IP應(yīng)使用UNSUGNED或者INT結(jié)構(gòu)類型，在PHP中可以使用long2ip與ip2long函數(shù)進(jìn)行互轉(zhuǎn)性別應(yīng)使用
CHAR(1)氓润，即定長(zhǎng)的字符串類型... ...
-
盡可能避免使用
TEXT赂乐、BLOB、ENUM數(shù)據(jù)類型MySQL 內(nèi)存臨時(shí)表不支持
TEXT咖气、BLOB這樣的大數(shù)據(jù)類型挨措，如果查詢中包含這樣的數(shù)據(jù)，在排序等操作時(shí)采章，就不能使用內(nèi)存臨時(shí)表，必須使用磁盤臨時(shí)表進(jìn)行壶辜，毋庸置疑會(huì)降低查詢的效率悯舟。MySQL對(duì)索引字段長(zhǎng)度是有限制的，TEXT或BLOB類型只能使用前綴索引砸民。 -
避免
ENUM數(shù)據(jù)類型在
MySQL中抵怎，存儲(chǔ)枚舉類型的數(shù)據(jù)在庫(kù)中，字段列中保存的值實(shí)際為整數(shù)岭参，特別容易導(dǎo)致開(kāi)發(fā)者混亂反惕，同時(shí)在查詢使用排序是基于數(shù)值整型的，雖然可以使用ORDER BY FIELD(),但是會(huì)導(dǎo)致索引失效演侯，盡量避免這么做姿染。 -
盡可能將所有的數(shù)據(jù)列定義為
NOT NULL類型NULL列比較特殊，需要額外的空間來(lái)保存秒际，同時(shí)會(huì)造成索引失效悬赏。 -
使用
TIMESTAMP與INT替換DATETIME存儲(chǔ)時(shí)間很明顯，
TIMESTAMP與INT占4位字節(jié)娄徊，而DATETIME占8位字節(jié)闽颇。那么存儲(chǔ)時(shí)間應(yīng)該如何選擇TIMESTAMP與INT呢？TIMESTAMP的可讀性高而INT的靈活性高寄锐，因而經(jīng)常需要使用計(jì)算操作的應(yīng)當(dāng)使用INT存儲(chǔ)兵多，否則使用TIMESTAMP。 -
金額相關(guān)的數(shù)據(jù)必須使用
DECIMAL數(shù)據(jù)類型談到錢這個(gè)東西呢橄仆，精確是非常重要的剩膘，即便要浪費(fèi)存儲(chǔ)空間、笑?~
DECIMAL類型為精準(zhǔn)浮點(diǎn)數(shù)盆顾，在計(jì)算時(shí)不會(huì)丟失精度援雇，可以自定義其長(zhǎng)度，可用于存儲(chǔ)比 bigint 更大的整型數(shù)據(jù)椎扬。 -
表與表關(guān)聯(lián)的鍵名保持一致或以關(guān)聯(lián)表名的縮寫為前綴
規(guī)范事項(xiàng)惫搏，保持規(guī)范具温、養(yǎng)成習(xí)慣，提高程序的可讀性筐赔。
-
固定長(zhǎng)度的字符串字段務(wù)必使用
CHAR節(jié)省存空間铣猩、降低內(nèi)存使用率、提高讀寫性能茴丰。
-
使用
UNSIGNEG存儲(chǔ)非負(fù)整數(shù)節(jié)省存空間达皿、降低內(nèi)存使用率、提高讀寫性能贿肩。
-
禁止敏感數(shù)據(jù)以明文形式存儲(chǔ)
確保信息的安全性峦椰，比如密碼、隱秘?cái)?shù)據(jù)等汰规。
索引規(guī)范
重要的
SQL語(yǔ)句必須帶上索引作為條件-
避免冗余和重復(fù)索引
重復(fù)索引： 在相同的列上按照相同的順序創(chuàng)建的相同類型的索引汤功。
冗余索引： 兩個(gè)索引按照相同的順序覆蓋了相同的列。
在一張用戶表里面溜哮，將用戶
id設(shè)置成主鍵的同時(shí)再設(shè)置成唯一索引滔金，那就是重復(fù)索引，如果創(chuàng)建了索引(a,b)茂嗓，再設(shè)置a索引餐茵，則a為冗余索引，這兩種錯(cuò)誤的操作都會(huì)降低讀寫的性能述吸。 -
務(wù)必不要在作為查詢條件很少忿族、或者沒(méi)有關(guān)聯(lián)的字段下建立索引
索引本身占用存儲(chǔ)空間，過(guò)多設(shè)置會(huì)導(dǎo)致查詢效率降低蝌矛。比如在成績(jī)表中將分?jǐn)?shù)設(shè)置為索引肠阱，這是一種錯(cuò)誤的做法。
-
禁止在索引列進(jìn)行數(shù)學(xué)運(yùn)算和函數(shù)運(yùn)算
MySQL不擅長(zhǎng)于運(yùn)算朴读，需要計(jì)算的應(yīng)該移至代碼業(yè)務(wù)層屹徘。總而言之衅金，凡是計(jì)算都要移至代碼業(yè)務(wù)層(MySQL不擅長(zhǎng)于運(yùn)算)噪伊。 -
符合索引將區(qū)分度高的置前
將區(qū)分度高的索引置前可以縮短查詢的范圍，以至提高查詢的效率氮唯，特別是在
JOIN連表查詢鉴吹，提高效率特別明顯。
SQL使用規(guī)范
-
危險(xiǎn)的
SQL語(yǔ)句必須帶上索引作為條件惩琉，謹(jǐn)記謹(jǐn)記哪些是危險(xiǎn)的
SQL語(yǔ)句呢豆励，刪、改皆為危險(xiǎn)的語(yǔ)句，一定要記住帶上WHERE良蒸。 -
建議使用預(yù)編譯語(yǔ)句操作數(shù)據(jù)庫(kù)
先簡(jiǎn)單了解下
SQL執(zhí)行的流程技扼，SQL先解析、預(yù)編譯處理再生成執(zhí)行計(jì)劃嫩痰，最后調(diào)用引擎的api方法返回執(zhí)行的結(jié)果剿吻，使用預(yù)編譯的操作姿勢(shì)，在讀寫的時(shí)候可以省去預(yù)編譯的時(shí)間串纺，終而提高執(zhí)行效率丽旅。 -
嚴(yán)禁使用
SELECT *查詢字段要什么
SELECT什么，不能多纺棺，否則可能導(dǎo)致覆蓋索引失效榄笙，消耗更多的CPU和IO以網(wǎng)絡(luò)帶寬資源。 查詢語(yǔ)句務(wù)必帶上索引以提高查詢效率
-
必須避免數(shù)據(jù)類型隱式轉(zhuǎn)換
在
MySQL中祷蝌，數(shù)據(jù)會(huì)存在隱式轉(zhuǎn)換茅撞，當(dāng)該字段發(fā)生轉(zhuǎn)換時(shí)，索引會(huì)造成失效杆逗。 -
充分利用利用索引優(yōu)勢(shì)
既然設(shè)置了索引就好好充分利用好索引乡翅，將查詢的效率提至最高鳞疲。
-
禁止使用相同的賬號(hào)跨庫(kù)操作
各執(zhí)其職罪郊，互不越權(quán)。
-
禁止使用帶有數(shù)據(jù)值卻不帶有字段鍵名的
INSERT操作這是一種錯(cuò)誤的做法尚洽，對(duì)于表的改動(dòng)后會(huì)造成比較大的影響悔橄。
INSERT INTO user VALUES ('alicfeng',23); # 應(yīng)該這樣操作 INSERT INTO user (`username`,`age`) VALUES ('alicfeng',23); -
盡可能使用
JOIN替代子查詢操作子查詢的結(jié)果集無(wú)法使用索引，通常子查詢的結(jié)果集會(huì)被存儲(chǔ)到臨時(shí)表中腺毫，不論是內(nèi)存臨時(shí)表還是磁盤臨時(shí)表都不會(huì)存在索引癣疟，所以查詢性能會(huì)受到一定的影響。 特別是對(duì)于返回結(jié)果集比較大的子查詢潮酒，其對(duì)查詢性能的影響也就越大睛挚。 由于子查詢會(huì)產(chǎn)生大量的臨時(shí)表也沒(méi)有索引，所以會(huì)消耗過(guò)多的
CPU和IO資源急黎，產(chǎn)生大量的慢查詢扎狱。 -
盡可能避免使用
JOIN關(guān)聯(lián)過(guò)多的表一般情況下，建議
JOIN的表不要超過(guò)5個(gè)勃教，JOIN多表查詢比較耗時(shí)時(shí)間淤击，關(guān)聯(lián)的表越多越耗時(shí)間，防止執(zhí)行超時(shí)或死鎖故源。 -
合并操作污抬、減少數(shù)據(jù)庫(kù)的交互
可以靈活地合并
SQL操作，降低IO消耗的同時(shí)也提高了執(zhí)行效率绳军，譬如UPDATE user SET username='alicfeng' FROM id=1995; UPDATE user SET age=23 FROM id=1995; # 合并操作成一條SQL UPDATE user SET username='alicfeng',age=23 FROM id=1995; 盡可能使用
IN代替OR語(yǔ)句-
禁止使用
ORDER BY RAND()隨機(jī)排序語(yǔ)句會(huì)把表中所有符合條件的數(shù)據(jù)裝載到內(nèi)存中印机，然后在內(nèi)存中對(duì)所有數(shù)據(jù)根據(jù)隨機(jī)生成的值進(jìn)行排序矢腻，并且可能會(huì)對(duì)每一行都生成一個(gè)隨機(jī)值，如果滿足條件的數(shù)據(jù)集非常大耳贬，就會(huì)消耗大量的
CPU和IO及內(nèi)存資源踏堡。 -
禁止在
WHERE語(yǔ)句中進(jìn)行計(jì)算對(duì)列進(jìn)行函數(shù)轉(zhuǎn)換或計(jì)算時(shí)會(huì)導(dǎo)致無(wú)法使用索引。
# 索引會(huì)失效 WHERE DATE(create_date)='20190308'; # 靈活使用[推薦] WHERE create_date>='20190308' AND create_date<'20190309'; -
使用
UNION ALL而不是使用UNION在已知數(shù)據(jù)沒(méi)有重復(fù)或無(wú)須刪除重復(fù)行的前提下咒劲，因?yàn)?code>UNION需要重復(fù)值掃描顷蟆，降低效率。
-
大批量寫操作盡可能合理地分批次處理
大批量的操作應(yīng)當(dāng)合理平均分批次處理腐魂，防止死鎖影響業(yè)務(wù)帐偎，同時(shí)盡量將跑批這種大操作至于凌晨操作。
-
不在數(shù)據(jù)庫(kù)做運(yùn)算蛔屹，務(wù)必將運(yùn)算置于業(yè)務(wù)層
MySQL不擅長(zhǎng)數(shù)學(xué)運(yùn)算和邏輯判斷削樊。 -
禁止使用索引做運(yùn)算
索引會(huì)失效。
SQL語(yǔ)句簡(jiǎn)單化-
使用事務(wù)盡量簡(jiǎn)單化兔毒，同時(shí)控制事務(wù)執(zhí)行的時(shí)間
時(shí)間長(zhǎng)會(huì)導(dǎo)致長(zhǎng)時(shí)間鎖表漫贞，造成死鎖，進(jìn)而影響業(yè)務(wù)育叁。
IN語(yǔ)句參數(shù)的個(gè)數(shù)盡量控制在1000以內(nèi)-
注意
LIMIT分頁(yè)查詢效率迅脐，LIMIT越大效率越低在使用
LIMIT做分頁(yè)時(shí)，更改巧妙地處理查詢豪嗽，譬如使用S1替換成S2谴蔑，將有效地提高查詢的效率。# S1 SELECT `username` FROM `user` LIMIT 10000,20; # S2 SELECT `username` FROM `user` WHERE id>10000 LIMIT 20; -
編寫
SQL語(yǔ)句必須全部為大寫龟梦，每個(gè)詞必只允許只有一個(gè)空格符編寫規(guī)范隐锭，必須統(tǒng)一并遵循。
盡可能使用
EXIST|NOT EXIST替代IN | NOT IN-
禁止使用
LIKE添加%前綴進(jìn)行模糊查詢%前置會(huì)導(dǎo)致索引失效 禁止一條語(yǔ)句同時(shí)對(duì)多個(gè)表進(jìn)行寫操作
