[[374227]]

本文轉(zhuǎn)載自微信公眾號(hào)「小姐姐味道」，作者小姐姐養(yǎng)的狗 。轉(zhuǎn)載本文請(qǐng)聯(lián)系小姐姐味道公眾號(hào)。  

晚上，我被叫進(jìn)寬大的辦公室，總監(jiān)正在煮茶。高壓鍋煮著長(zhǎng)嘴茶壺，水蒸氣繚繞。領(lǐng)導(dǎo)舉手之間，淡黃茶水奔涌而出，倒立而下澆上茶葉，漏出兩杯茶水。

“喝茶?”領(lǐng)導(dǎo)推給我一杯，然后自己抿了一口。沉默良久，把顯示器轉(zhuǎn)到我這邊：“最近數(shù)據(jù)庫(kù)表出現(xiàn)了些有意思的東西，你來(lái)看看”。

我探著腦袋一瞧，心涼了半截。

時(shí)隔五年，又在項(xiàng)目里見(jiàn)到哥哥表和妹妹字段，著實(shí)讓我坐立不安。所謂哥哥表，就是名稱(chēng)叫做gg的數(shù)據(jù)庫(kù)表，意為公共;所謂妹妹字段，就是名稱(chēng)叫做mm的表子段，意為密碼。比起shit mountain來(lái)，這些命名更讓人浮想聯(lián)翩，實(shí)為不規(guī)范之典范。

這么魔幻的事情，不止一次出現(xiàn)，任何領(lǐng)導(dǎo)都會(huì)坐不住?？上У氖?，一次次的會(huì)議，專(zhuān)項(xiàng)討論某一個(gè)SQL禁止條例，到最后還是大開(kāi)方便之門(mén)，過(guò)往的規(guī)范承諾皆?huà)佒X外。

數(shù)據(jù)庫(kù)命名規(guī)范是最基礎(chǔ)的規(guī)范，連這個(gè)都沒(méi)做好，證明監(jiān)管工作確實(shí)出現(xiàn)了紕漏。我趕緊掏出自己的手機(jī)，翻到xjjdog的文章，打算把數(shù)據(jù)庫(kù)要注意的點(diǎn)，給領(lǐng)導(dǎo)匯報(bào)一下。

也順便向大家匯報(bào)。

我把規(guī)范分成了統(tǒng)一的規(guī)范、索引規(guī)范、SQL規(guī)范、命名規(guī)范、安全規(guī)范、性能小Case等6個(gè)部分。

請(qǐng)聽(tīng)我慢慢道來(lái)。

1. 統(tǒng)一的規(guī)范

首先，我們來(lái)一些通用的規(guī)范。這里有很多是經(jīng)驗(yàn)值，如果你的數(shù)據(jù)庫(kù)所在的宿主機(jī)硬件，并不是十分的牛X，可以考慮再降低一下標(biāo)準(zhǔn)。

存儲(chǔ)引擎： 請(qǐng)統(tǒng)一使用innodb存儲(chǔ)引擎，特殊的數(shù)據(jù)庫(kù)引擎必須通過(guò)DBA的評(píng)審。

字符集：統(tǒng)一使用utf8字符集。這個(gè)要從應(yīng)用程序、服務(wù)器、數(shù)據(jù)庫(kù)的表、字段等全部統(tǒng)一起來(lái)。注意：MySQL中的utf8mb4字符集，才是真正的utf8，請(qǐng)用這個(gè)。

作用范圍：不要在MySQL存儲(chǔ)大對(duì)象，比如圖片、音樂(lè)等;不要用MySQL做Gis運(yùn)算、全文檢索;不使用存儲(chǔ)過(guò)程、觸發(fā)器、函數(shù)、外鍵，避免破壞數(shù)據(jù)庫(kù)的性能和擴(kuò)展性。

使用上限：

• 每個(gè)MySQL實(shí)例，數(shù)據(jù)庫(kù)不要超過(guò)50個(gè);
• 單數(shù)據(jù)庫(kù)容量，不要超過(guò)500GB，否則分庫(kù);
• 單表記錄數(shù)量，不要超過(guò)5000W，否則分表;
• 單表子段數(shù)量，不要超過(guò)30個(gè)，否則拆表;
• 單張表中索引數(shù)量不超過(guò)5個(gè)，單個(gè)索引中的字段數(shù)不超過(guò)5個(gè);
• varchar字段最大值不超過(guò)1024;注意：VARCHAR(N)中的N表示字符數(shù)而非字節(jié)數(shù)

2. 索引規(guī)范

索引是數(shù)據(jù)庫(kù)中非常重要的結(jié)構(gòu)，可以加速數(shù)據(jù)的檢索。但索引是要占用大量空間的，如果你的數(shù)據(jù)表里面沒(méi)幾條記錄，就不必創(chuàng)建索引。比如2000條以下。

選擇性很小的字段(低基數(shù)列)，不要加索引。比如一些state，type，布爾判斷等。因?yàn)榧恿艘矝](méi)用。

盡量讓索引的內(nèi)容盡量的短!比較長(zhǎng)的子段，要使用前綴索引。比如：title varchar (64) ，可以創(chuàng)建前綴索引 idx_title (title(16))。

合理利用索引的最左原則，合并相似的索引。比如 (a) (ab) (abc)三種索引需求，我們只需要?jiǎng)?chuàng)建abc這一個(gè)索引就ok了。

避免在索引列做計(jì)算(這將造成索引失效)，比如 data_format(created_date)，substring(short_name,0,6) = 'xjjdog'。

不能使用%前綴模糊查詢(xún)，因?yàn)闊o(wú)法使用索引，例如：WHERE name LIKE '%味道'。

不能使用數(shù)據(jù)庫(kù)端做全文檢索操作。雖然它支持，也不要這么做。

索引的命名要有章可循：idx_前綴表明是普通索引，而 uk_前綴表明的是唯一索引。

3. SQL規(guī)范

建議在每個(gè)表中，添加下面三個(gè)字段。其實(shí)，SpringBoot JPA，也建議你添加上這三個(gè)字段。根據(jù)時(shí)間字段，除了審計(jì)，還能夠做一些非常nice的遷移操作;version字段是高并發(fā)下的樂(lè)觀鎖實(shí)現(xiàn)，UPDATE語(yǔ)句可以結(jié)合version字段，避免并發(fā)操作造成的不一致情況。

• created：記錄創(chuàng)建時(shí)間，時(shí)間類(lèi)型
• modified：記錄修改時(shí)間，時(shí)間類(lèi)型
• version：“樂(lè)觀鎖”的版本標(biāo)記，long型，默認(rèn)為0

大多數(shù)字段應(yīng)該定義成not null的，并分配默認(rèn)值，但是不要default null，因?yàn)閿?shù)據(jù)庫(kù)無(wú)法索引null值。

復(fù)雜的SQL查詢(xún)語(yǔ)句，是絕對(duì)要避免的。我們所說(shuō)的，就是慢查詢(xún)。慢查詢(xún)會(huì)占用大量資源，并阻塞線(xiàn)程，應(yīng)該見(jiàn)諒將大SQL拆分成多條簡(jiǎn)單的SQL，減少數(shù)據(jù)的鎖定時(shí)間。

另外，不要在不同數(shù)據(jù)類(lèi)型的字段上進(jìn)行比較，避免字段類(lèi)型轉(zhuǎn)換造成性能損失，這就要求我們?cè)赟QL語(yǔ)句中傳入的參數(shù)類(lèi)型，和數(shù)據(jù)庫(kù)中所定義的類(lèi)型是相同的。

禁止使用select *進(jìn)行輸出，應(yīng)該選擇具體的字段進(jìn)行輸出。除了避免無(wú)用的字段造成傳輸上的性能損耗，還能在一定程度上避免敏感信息的泄漏。

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

禁止使用order by rand()。

插入語(yǔ)句，不要直接使用 nsert into table values()，而應(yīng)該加入具體的字段，否則無(wú)法適應(yīng)數(shù)據(jù)庫(kù)變更情況。在做批量插入時(shí)，一次性操作100-200條就可以，沒(méi)必要把batch數(shù)量設(shè)置成上千上萬(wàn)。

禁止非框架類(lèi)業(yè)務(wù)代碼，直接調(diào)用set sql_mode或者set tx_isolation，禁止使用SELECT … FOR UPDAT，優(yōu)先采用樂(lè)觀鎖實(shí)現(xiàn)。

多表關(guān)聯(lián)不要超過(guò)3個(gè)，盡量拆分成簡(jiǎn)單的SQL處理。

大多數(shù)開(kāi)發(fā)人員會(huì)在需要時(shí)寫(xiě)UNION，這往往會(huì)導(dǎo)致執(zhí)行一個(gè)排序來(lái)消除重復(fù)。應(yīng)該盡量使用UNION ALL來(lái)代替UNION。

注意OR語(yǔ)句的一些改善情況。比如WHERE id=1 OR id=2可以 改寫(xiě)為WHERE id IN(1,2)。在不同的字段，可以將OR改寫(xiě)為UNION ALL。

4. 命名規(guī)范

數(shù)據(jù)庫(kù)表和字段的命名，不要使用駝峰命名方式。比如，不能叫saleOrder，而應(yīng)該叫做sale_order。因?yàn)榇蠖鄶?shù)數(shù)據(jù)庫(kù)，都不區(qū)分大小寫(xiě)，下劃線(xiàn)命名會(huì)更安全。

這些命名，只能使用英文小寫(xiě)字母、數(shù)字和下劃線(xiàn)，長(zhǎng)度不超過(guò)17個(gè)字符。

命名應(yīng)該有確切的含義。和代碼規(guī)范一樣，不允許使用a，b等無(wú)意義的字符串。不允許中文拼音縮寫(xiě)、中英文混用等。

嚴(yán)禁出現(xiàn)哥哥表和妹妹字段。

5. 安全安全安全圖片

(1) 服務(wù)器隔離 如果你的公司有多個(gè)環(huán)境，比如dev環(huán)境，測(cè)試環(huán)境等，就要做好相應(yīng)的隔離。比如，不允許在線(xiàn)上環(huán)境直接進(jìn)行開(kāi)發(fā)和測(cè)試、禁止在線(xiàn)上做數(shù)據(jù)庫(kù)壓?力測(cè)試。這是非常重要的，避免了無(wú)謂的數(shù)據(jù)錯(cuò)亂。如果條件允許，甚至可以做物理隔離，用不同的IP段進(jìn)行區(qū)分。不長(zhǎng)腦子的程序員有很多，你永遠(yuǎn)不知道他們連的是哪個(gè)環(huán)境的數(shù)據(jù)庫(kù)。

(2)賬戶(hù)的權(quán)限 永遠(yuǎn)不要在生產(chǎn)上，讓root賬號(hào)遠(yuǎn)程可連。對(duì)不同的應(yīng)用，應(yīng)該分配不同的database，并建立相互隔離的賬號(hào)。

賬號(hào)默認(rèn)開(kāi)啟select/insert/update/delete/execute的權(quán)限就可以。create都不能放開(kāi)，用根本上杜絕程序員們刪庫(kù)跑路的機(jī)會(huì)。

針對(duì)安全級(jí)別高的應(yīng)用，應(yīng)分配讀寫(xiě)賬號(hào)。讀賬號(hào)去掉各種更新權(quán)限，只能做一些sql查詢(xún)。賬號(hào)命名方式上，可以加入_w或者_(dá)r后綴，表明它們的意圖。

對(duì)于SQL的傳入?yún)?shù)(數(shù)字，字符和混用)必須進(jìn)行合法性檢查，防止SQL注入。業(yè)務(wù)應(yīng)該提前準(zhǔn)備好風(fēng)險(xiǎn)SQL語(yǔ)句，進(jìn)行集中審核，負(fù)責(zé)后果自負(fù)。

6. 性能小case

如有自增字段，請(qǐng)使用無(wú)符號(hào)型(unsigned)int或bigint 。優(yōu)先使用更小的數(shù)據(jù)類(lèi)型，比如：

數(shù)字用tinyint、smallint、mediumint、int、bigint類(lèi)型;

日期用date、datetime類(lèi)型;

時(shí)間用timestamp、int類(lèi)型;

不使用char、varchar存儲(chǔ)日期和時(shí)間;

使用更小的數(shù)據(jù)類(lèi)型，能用tinyint的就不用smallint，能用timestamp的就不用datetime類(lèi)型;

不能使用tinyblob、mediumblob、blob和longblob類(lèi)型字段，對(duì)于表存在大字段類(lèi)型，應(yīng)當(dāng)考慮單獨(dú)拆分。

OLTP數(shù)據(jù)庫(kù)絕對(duì)要避免大事務(wù)和數(shù)據(jù)庫(kù)端運(yùn)算，可以考慮使用NoSQL或者大數(shù)據(jù)計(jì)算平臺(tái)。

End

可以看到，我們規(guī)范里，有些禁止的東西，其實(shí)最后還是用了。比如分區(qū)表、大字段存儲(chǔ)、GIS操作。但這是和規(guī)范不沖突的。

規(guī)范，只定義了一些常見(jiàn)的可能會(huì)引起嚴(yán)重后果的操作禁止，然后將風(fēng)險(xiǎn)的事情，交給專(zhuān)業(yè)的人去做，并評(píng)估、控制風(fēng)險(xiǎn)點(diǎn)的規(guī)模。

規(guī)范定了，要執(zhí)行才行。不論是人工的review，還是工具的檢測(cè)。如此，系統(tǒng)才能健康成長(zhǎng)，程序員才能不加班，領(lǐng)導(dǎo)才能開(kāi)上保時(shí)捷。

這時(shí)候，我匯報(bào)完畢，抬頭向領(lǐng)導(dǎo)望去。他的頭倚在真皮座椅后背上，已經(jīng)沉沉的的睡了過(guò)去。我把外套輕輕脫下來(lái)，披在他身上，這才捧過(guò)自己的茶杯，咕咚一口喝了下去。雖然茶已經(jīng)涼了，但醇香一直在嘴中繚繞。

作者簡(jiǎn)介：小姐姐味道 (xjjdog)，一個(gè)不允許程序員走彎路的公眾號(hào)。聚焦基礎(chǔ)架構(gòu)和Linux。十年架構(gòu)，日百億流量，與你探討高并發(fā)世界，給你不一樣的味道。我的個(gè)人微信xjjdog0，歡迎添加好友，進(jìn)一步交流。