建站
咨詢(xún)
售后
建站咨詢(xún)
新聞中心
因?yàn)楣ぷ鲘徫坏脑颍?fù)責(zé)制定了關(guān)于后端組數(shù)據(jù)庫(kù)的規(guī)約規(guī)范，作為所有產(chǎn)品線的規(guī)范，歷經(jīng)幾版的修改，最終形成下邊的文本，規(guī)范在整個(gè)后端執(zhí)行也有大半年的時(shí)間，對(duì)于整個(gè)團(tuán)隊(duì)在開(kāi)發(fā)階段就減少不恰當(dāng)?shù)慕ū碚Z(yǔ)句、錯(cuò)誤SQL、錯(cuò)誤的索引有積極的意義，故分享出來(lái)給大家參考。
創(chuàng)新互聯(lián)建站：公司2013年成立為各行業(yè)開(kāi)拓出企業(yè)自己的“網(wǎng)站建設(shè)”服務(wù)，為數(shù)千家公司企業(yè)提供了專(zhuān)業(yè)的成都網(wǎng)站制作、網(wǎng)站建設(shè)、網(wǎng)頁(yè)設(shè)計(jì)和網(wǎng)站推廣服務(wù)， 按需求定制開(kāi)發(fā)由設(shè)計(jì)師親自精心設(shè)計(jì)，設(shè)計(jì)的效果完全按照客戶(hù)的要求，并適當(dāng)?shù)奶岢龊侠淼慕ㄗh，擁有的視覺(jué)效果，策劃師分析客戶(hù)的同行競(jìng)爭(zhēng)對(duì)手，根據(jù)客戶(hù)的實(shí)際情況給出合理的網(wǎng)站構(gòu)架，制作客戶(hù)同行業(yè)具有領(lǐng)先地位的。
下邊分為建表規(guī)約、SQL規(guī)約、索引規(guī)約三個(gè)部分，每部分的每一條都有強(qiáng)制、建議兩個(gè)級(jí)別，大家在參考時(shí)，根據(jù)自己公司的情況來(lái)權(quán)衡。
一、建表規(guī)約
【強(qiáng)制】(1) 存儲(chǔ)引擎必須使用InnoDB
解讀：InnoDB支持事物、行級(jí)鎖、并發(fā)性能更好，CPU及內(nèi)存緩存頁(yè)優(yōu)化使得資源利用率更高。
【強(qiáng)制】(2)每張表必須設(shè)置一個(gè)主鍵ID，且這個(gè)主鍵ID使用自增主鍵(在滿(mǎn)足需要的情況下盡量短)，除非在分庫(kù)分表環(huán)境下。
解讀：由于InnoDB組織數(shù)據(jù)的方式?jīng)Q定了需要有一個(gè)主鍵，而且若是這個(gè)主鍵ID是單調(diào)遞增的可以有效提高插入的性能，避免過(guò)多的頁(yè)分裂、減少表碎片提高空間的使用率。而在分庫(kù)分表環(huán)境下，則需要統(tǒng)一來(lái)分配各個(gè)表中的主鍵值，從而避免整個(gè)邏輯表中主鍵重復(fù)。
【強(qiáng)制】(3)必須使用utf8mb4字符集
解讀：在Mysql中的UTF-8并非“真正的UTF-8”，而utf8mb4”才是真正的“UTF-8”。
【強(qiáng)制】(4) 數(shù)據(jù)庫(kù)表、表字段必須加入中文注釋
解讀：大家都別懶
【強(qiáng)制】(5) 庫(kù)名、表名、字段名均小寫(xiě)，下劃線風(fēng)格，不超過(guò)32個(gè)字符，必須見(jiàn)名知意，禁止拼音英文混用。
解讀：約定
【強(qiáng)制】(6)單表列數(shù)目必須小于30，若超過(guò)則應(yīng)該考慮將表拆分
解讀：?jiǎn)伪砹袛?shù)太多使得Mysql服務(wù)器處理InnoDB返回?cái)?shù)據(jù)之間的映射成本太高
【強(qiáng)制】(7)禁止使用外鍵，如果有外鍵完整性約束，需要應(yīng)用程序控制
解讀：外鍵會(huì)導(dǎo)致表與表之間耦合，UPDATE與DELETE操作都會(huì)涉及相關(guān)聯(lián)的表，十分影響SQL的性能，甚至?xí)斐伤梨i。
【強(qiáng)制】(8)必須把字段定義為NOT NULL并且提供默認(rèn)值
解讀：a、NULL的列使索引/索引統(tǒng)計(jì)/值比較都更加復(fù)雜，對(duì)MySQL來(lái)說(shuō)更難優(yōu)化 b、NULL這種類(lèi)型Msql內(nèi)部需要進(jìn)行特殊處理，增加數(shù)據(jù)庫(kù)處理記錄的復(fù)雜性;同等條件下，表中有較多空字段的時(shí)候，數(shù)據(jù)庫(kù)的處理性能會(huì)降低很多 c、NULL值需要更多的存儲(chǔ)空，無(wú)論是表還是索引中每行中的NULL的列都需要額外的空間來(lái)標(biāo)識(shí)
【強(qiáng)制】(9)禁用保留字，如DESC、RANGE、MARCH等，請(qǐng)參考Mysql官方保留字。
【強(qiáng)制】(10)如果存儲(chǔ)的字符串長(zhǎng)度幾乎相等，使用CHAR定長(zhǎng)字符串類(lèi)型。
解讀：能夠減少空間碎片，節(jié)省存儲(chǔ)空間。更多SQL技巧可搜索公號(hào)：SQL數(shù)據(jù)庫(kù)開(kāi)發(fā)
【建議】(11)在一些場(chǎng)景下，考慮使用TIMESTAMP代替DATETIME。
解讀：
- 這兩種類(lèi)型的都能表達(dá)"yyyy-MM-dd HH:mm:ss"格式的時(shí)間，TIMESTAMP只需要占用4個(gè)字節(jié)的長(zhǎng)度，可以存儲(chǔ)的范圍為(1970-2038)年，在各個(gè)時(shí)區(qū)，所展示的時(shí)間是不一樣的；
- 而DATETIME類(lèi)型占用8個(gè)字節(jié)，對(duì)時(shí)區(qū)不敏感，可以存儲(chǔ)的范圍為(1001-9999)年。
【建議】(12)當(dāng)心自動(dòng)生成的Schema，建議所有的Schema手動(dòng)編寫(xiě)。
解讀：對(duì)于一些數(shù)據(jù)庫(kù)客戶(hù)端不要太過(guò)信任。
二、SQL規(guī)約
【建議】 (1) 為了充分利用緩存，不允許使用自定義函數(shù)、存儲(chǔ)函數(shù)、用戶(hù)變量。
解讀：如果查詢(xún)中包含任何用戶(hù)自定義函數(shù)、存儲(chǔ)函數(shù)、用戶(hù)變量、臨時(shí)表、Mysql庫(kù)中的系統(tǒng)表，其查詢(xún)結(jié)果都不會(huì)被緩存。比如函數(shù)NOW()或者CURRENT_DATE()會(huì)因?yàn)椴煌牟樵?xún)時(shí)間，返回不同的查詢(xún)結(jié)果。
【強(qiáng)制】(2)在查詢(xún)中指定所需的列，而不是直接使用“ *”返回所有的列
解讀：a)讀取不需要的列會(huì)增加CPU、IO、NET消耗 b)不能有效的利用覆蓋索引
【強(qiáng)制】(3)不允許使用屬性隱式轉(zhuǎn)換
解讀：假設(shè)我們?cè)谑謾C(jī)號(hào)列上添加了索引，然后執(zhí)行下面的SQL會(huì)發(fā)生什么?explain SELECT user_name FROM parent WHERE phone=13812345678;很明顯就是索引不生效，會(huì)全表掃描。
【建議】(4)在WHERE條件的屬性上使用函數(shù)或者表達(dá)式
解讀：Mysql無(wú)法自動(dòng)解析這種表達(dá)式，無(wú)法使用到索引。
【強(qiáng)制】(5)禁止使用外鍵與級(jí)聯(lián)，一切外鍵概念必須在應(yīng)用層解決。
解讀：外鍵與級(jí)聯(lián)更新適用于單機(jī)低并發(fā)，不適合分布式、高并發(fā)集群;級(jí)聯(lián)更新是強(qiáng)阻塞，存在數(shù)據(jù)庫(kù)更新風(fēng)暴的風(fēng)險(xiǎn);外鍵影響數(shù)據(jù)庫(kù)的插入速度。
【建議】(6)應(yīng)盡量避免在WHERE子句中使用or作為連接條件
解讀：根據(jù)情況可以選擇使用UNION ALL來(lái)代替OR
【強(qiáng)制】(7)不允許使用%開(kāi)頭的模糊查詢(xún)
解讀：根據(jù)索引的最左前綴原理，%開(kāi)頭的模糊查詢(xún)無(wú)法使用索引，可以使用ES來(lái)做檢索。
三、索引規(guī)約
【建議】(1)避免在更新比較頻繁、區(qū)分度不高的列上單獨(dú)建立索引
解讀：區(qū)分度不高的列單獨(dú)創(chuàng)建索引的優(yōu)化效果很小，但是較為頻繁的更新則會(huì)讓索引的維護(hù)成本更高
【強(qiáng)制】(2) JOIN的表不允許超過(guò)五個(gè)。需要JOIN的字段，數(shù)據(jù)類(lèi)型必須絕對(duì)一致; 多表關(guān)聯(lián)查詢(xún)時(shí)，保證被關(guān)聯(lián)的字段需要有索引。
解讀：太多表的JOIN會(huì)讓Mysql的優(yōu)化器更難權(quán)衡出一個(gè)“最佳”的執(zhí)行計(jì)劃(可能性為表數(shù)量的階乘)，同時(shí)要注意關(guān)聯(lián)字段的類(lèi)型、長(zhǎng)度、字符編碼等等是否一致。
【強(qiáng)制】(3)在一個(gè)聯(lián)合索引中，若第一列索引區(qū)分度等于1，那么則不需要建立聯(lián)合索引。
解讀：索引通過(guò)第一列就能夠完全定位的數(shù)據(jù)，所以聯(lián)合索引的后邊部分是不需要的。
【強(qiáng)制】(4)建立聯(lián)合索引時(shí)，必須將區(qū)分度更高的字段放在左邊
解讀：區(qū)分度更高的列放在左邊，能夠在一開(kāi)始就有效的過(guò)濾掉無(wú)用數(shù)據(jù)。提高索引的效率，相應(yīng)我們?cè)贛apper中編寫(xiě)SQL的WHERE條件中有多個(gè)條件時(shí)，需要先看看當(dāng)前表是否有現(xiàn)成的聯(lián)合索引直接使用，注意各個(gè)條件的順序盡量和索引的順序一致。
【建議】(5)利用覆蓋索引來(lái)進(jìn)行查詢(xún)操作，避免回表
解讀：覆蓋查詢(xún)即是查詢(xún)只需要通過(guò)索引即可拿到所需DATA，而不再需要再次回表查詢(xún)，所以效率相對(duì)很高。我們?cè)谑褂肊XPLAIN的結(jié)果，extra列會(huì)出現(xiàn)："using index"。這里也要強(qiáng)調(diào)一下不要使用“SELECT * ”，否則幾乎不可能使用到覆蓋索引。
【建議】(6)在較長(zhǎng)VARCHAR字段,例如VARCHAR(100)上建立索引時(shí)，應(yīng)指定索引長(zhǎng)度，沒(méi)必要對(duì)全字段建立索引，根據(jù)實(shí)際文本區(qū)分度決定索引長(zhǎng)度即可。
解讀：索引的長(zhǎng)度與區(qū)分度是一對(duì)矛盾體，一般對(duì)字符串類(lèi)型數(shù)據(jù)，若長(zhǎng)度為20的索引，區(qū)分度會(huì)高達(dá)90%以上，則可以考慮創(chuàng)建長(zhǎng)度例為20的索引，而非全字段索引。例如可以使用SELECT COUNT(DISTINCT LEFT(lesson_code, 20)) / COUNT(*) FROM lesson；來(lái)確定lesson_code字段字符長(zhǎng)度為20時(shí)文本區(qū)分度。
【建議】(7)如果有ORDER BY的場(chǎng)景，請(qǐng)注意利用索引的有序性。ORDER BY最后的字段是聯(lián)合索引的一部分，并且放在索引組合順序的最后，避免出現(xiàn)file_sort的情況，影響查詢(xún)性能。
解讀：
- 假設(shè)有查詢(xún)條件為WHERE a=? and b=? ORDER BY c; 存在索引：a_b_c，則此時(shí)可以利用索引排序。
- 反例：在查詢(xún)條件中包含了范圍查詢(xún)，那么索引有序性無(wú)法利用，如:WHERE a>10 ORDER BY b; 索引a_b無(wú)法排序。
【建議】(8)在where中索引的列不能某個(gè)表達(dá)式的一部分，也不能是函數(shù)的參數(shù)。
解讀：即是某列上已經(jīng)添加了索引，但是若此列成為表達(dá)式的一部分、或者是函數(shù)的參數(shù)，Mysql無(wú)法將此列單獨(dú)解析出來(lái)，索引也不會(huì)生效。
【建議】 (9)我們?cè)趙here條件中使用范圍查詢(xún)時(shí)，索引最多用于一個(gè)范圍條件，超過(guò)一個(gè)則后邊的不走索引。
解讀：Mysql能夠使用多個(gè)范圍條件里邊的最左邊的第一個(gè)范圍查詢(xún)，但是后邊的范圍查詢(xún)則無(wú)法使用。
【建議】 (10)在多個(gè)表進(jìn)行外連接時(shí)，表之間的關(guān)聯(lián)字段類(lèi)型必須完全一致。
解讀：當(dāng)兩個(gè)表進(jìn)行Join時(shí)，字段類(lèi)型若沒(méi)有完全一致，則加索引也不會(huì)生效，這里的完全一致包括但不限于字段類(lèi)型、字段長(zhǎng)度、字符集、collection等等。
當(dāng)前文章：超全的數(shù)據(jù)庫(kù)建表/SQL/索引規(guī)范，建議打印
瀏覽路徑：http://www.dlmjj.cn/article/djdcooo.html
