【轉(zhuǎn)載】一份值得收藏的的 MySQL 規(guī)范

碼農(nóng)有道?5天前

作者：聽風(fēng)

編輯：http://www.reibang.com/p/f584799f1c77

碼農(nóng)有道

碼農(nóng)有道高質(zhì)量技術(shù)文章目錄整理（請戳我）

關(guān)于碼農(nóng)有道（請戳我）

數(shù)據(jù)命名規(guī)范

所有數(shù)據(jù)庫對象名稱必須使用小寫字母并用下劃線分割确封。

所有數(shù)據(jù)庫對象名稱禁止使用 MySQL 保留關(guān)鍵字（如果表名中包含關(guān)鍵字查詢時，需要將其用單引號括起來）。

數(shù)據(jù)庫對象的命名要能做到見名識意哟旗，并且最后不要超過32個字符寂呛。

臨時庫表必須以 tmp_ 為前綴并以日期為后綴该面，備份表必須以 bak_ 為前綴并以日期 ( 時間戳 ) 為后綴。

所有存儲相同數(shù)據(jù)的列名和列類型必須一致（一般作為關(guān)聯(lián)列缀遍，如果查詢時關(guān)聯(lián)列類型不一致會自動進行數(shù)據(jù)類型隱式轉(zhuǎn)換赡矢，會造成列上的索引失效匙睹，導(dǎo)致查詢效率降低）愚屁。

數(shù)據(jù)庫基本設(shè)計規(guī)范

1、所有表必須使用 InnoDB 存儲引擎

沒有特殊要求（即 InnoDB 無法滿足的功能如：列存儲痕檬，存儲空間數(shù)據(jù)等）的情況下霎槐，所有表必須使用 InnoDB 存儲引擎（MySQL 5.5 之前默認(rèn)使用 Myisam，5.6 以后默認(rèn)的為 InnoDB）InnoDB 支持事務(wù)梦谜，支持行級鎖丘跌，更好的恢復(fù)性，高并發(fā)下性能更好唁桩。

2闭树、數(shù)據(jù)庫和表的字符集統(tǒng)一使用 UTF8

兼容性更好，統(tǒng)一字符集可以避免由于字符集轉(zhuǎn)換產(chǎn)生的亂碼荒澡，不同的字符集進行比較前需要進行轉(zhuǎn)換會造成索引失效报辱。

3、所有表和字段都需要添加注釋

使用 comment 從句添加表和列的備注從一開始就進行數(shù)據(jù)字典的維護单山。

4碍现、盡量控制單表數(shù)據(jù)量的大小，建議控制在 500 萬以內(nèi)

500 萬并不是 MySQL 數(shù)據(jù)庫的限制米奸，過大會造成修改表結(jié)構(gòu)昼接、備份、恢復(fù)都會有很大的問題悴晰，可以用歷史數(shù)據(jù)歸檔（應(yīng)用于日志數(shù)據(jù)）慢睡，分庫分表（應(yīng)用于業(yè)務(wù)數(shù)據(jù)）等手段來控制數(shù)據(jù)量大小。

5铡溪、謹(jǐn)慎使用 MySQL 分區(qū)表

分區(qū)表在物理上表現(xiàn)為多個文件漂辐，在邏輯上表現(xiàn)為一個表謹(jǐn)慎選擇分區(qū)鍵，跨分區(qū)查詢效率可能更低建議采用物理分表的方式管理大數(shù)據(jù)棕硫。

6髓涯、盡量做到冷熱數(shù)據(jù)分離，減小表的寬度

MySQL 限制每個表最多存儲 4096 列饲帅，并且每一行數(shù)據(jù)的大小不能超過 65535 字節(jié) 減少磁盤 IO，保證熱數(shù)據(jù)的內(nèi)存緩存命中率（表越寬瘤泪，把表裝載進內(nèi)存緩沖池時所占用的內(nèi)存也就越大,也會消耗更多的 IO）更有效的利用緩存灶泵，避免讀入無用的冷數(shù)據(jù) 經(jīng)常一起使用的列放到一個表中（避免更多的關(guān)聯(lián)操作）

7、禁止在表中建立預(yù)留字段

預(yù)留字段的命名很難做到見名識義預(yù)留字段無法確認(rèn)存儲的數(shù)據(jù)類型对途，所以無法選擇合適的類型對預(yù)留字段類型的修改赦邻，會對表進行鎖定

8、禁止在數(shù)據(jù)庫中存儲圖片实檀，文件等大的二進制數(shù)據(jù)

通常文件很大惶洲，會短時間內(nèi)造成數(shù)據(jù)量快速增長按声，數(shù)據(jù)庫進行數(shù)據(jù)庫讀取時，通常會進行大量的隨機 IO 操作恬吕，文件很大時签则，IO 操作很耗時通常存儲于文件服務(wù)器，數(shù)據(jù)庫只存儲文件地址信息铐料。

9渐裂、禁止在線上做數(shù)據(jù)庫壓力測試

10、禁止從開發(fā)環(huán)境钠惩，測試環(huán)境直接連接生成環(huán)境數(shù)據(jù)庫

數(shù)據(jù)庫字段設(shè)計規(guī)范

1. 優(yōu)先選擇符合存儲需要的最小的數(shù)據(jù)類型

原因

列的字段越大柒凉，建立索引時所需要的空間也就越大，這樣一頁中所能存儲的索引節(jié)點的數(shù)量也就越少也越少篓跛，在遍歷時所需要的IO次數(shù)也就越多膝捞，索引的性能也就越差

方法

1、將字符串轉(zhuǎn)換成數(shù)字類型存儲愧沟，如：將IP地址轉(zhuǎn)換成整形數(shù)據(jù)蔬咬。

MySQL 提供了兩個方法來處理 IP 地址

inet_aton把ip轉(zhuǎn)為無符號整型(4-8位)

inet_ntoa?把整型的ip轉(zhuǎn)為地址

插入數(shù)據(jù)前，先用 inet_aton 把 IP 地址轉(zhuǎn)為整型央渣，可以節(jié)省空間计盒。顯示數(shù)據(jù)時，使用 inet_ntoa 把整型的 IP 地址轉(zhuǎn)為地址顯示即可芽丹。

2北启、對于非負型的數(shù)據(jù)（如自增 ID、整型 IP）來說拔第，要優(yōu)先使用無符號整型來存儲,因為無符號相對于有符號可以多出一倍的存儲空間咕村。

SIGNEDINT-2147483648~2147483647

UNSIGNEDINT0~4294967295

VARCHAR(N) 中的 N 代表的是字符數(shù)，而不是字節(jié)數(shù)蚊俺。使用 UTF8 存儲 255 個漢字 Varchar(255)=765 個字節(jié)懈涛。過大的長度會消耗更多的內(nèi)存

2. 避免使用 TEXT、BLOB 數(shù)據(jù)類型泳猬，最常見的TEXT類型可以存儲64k的數(shù)據(jù)

建議把 BLOB 或是TEXT列分離到單獨的擴展表中

MySQL 內(nèi)存臨時表不支持 TEXT批钠、BLOB 這樣的大數(shù)據(jù)類型，如果查詢中包含這樣的數(shù)據(jù)得封，在排序等操作時埋心，就不能使用內(nèi)存臨時表，必須使用磁盤臨時表進行忙上。

而且對于這種數(shù)據(jù)拷呆，MySQL 還是要進行二次查詢，會使 SQL 性能變得很差，但是不是說一定不能使用這樣的數(shù)據(jù)類型茬斧。

如果一定要使用腰懂，建議把 BLOB 或是 TEXT 列分離到單獨的擴展表中，查詢時一定不要使用?select *?而只需要取出必要的列项秉，不需要 TEXT 列的數(shù)據(jù)時不要對該列進行查詢绣溜。

TEXT 或 BLOB 類型只能使用前綴索引

因為 MySQL 對索引字段長度是有限制的，所以 TEXT 類型只能使用前綴索引伙狐，并且 TEXT 列上是不能有默認(rèn)值的涮毫。

3. 避免使用 ENUM 類型

修改 ENUM 值需要使用 ALTER 語句

ENUM 類型的 ORDER BY 操作效率低，需要額外操作

禁止使用數(shù)值作為 ENUM 的枚舉值

4. 盡可能把所有列定義為 NOT NULL

原因：

索引 NULL 列需要額外的空間來保存贷屎，所以要占用更多的空間罢防。

進行比較和計算時要對 NULL 值做特別的處理。

5. 使用 TIMESTAMP（4 個字節(jié)）或 DATETIME 類型（8 個字節(jié)）存儲時間

TIMESTAMP 存儲的時間范圍 1970-01-01 00:00:01 ~ 2038-01-19-03:14:07唉侄。

TIMESTAMP 占用 4 字節(jié)和 INT 相同咒吐，但比 INT 可讀性高，超出 TIMESTAMP 取值范圍的使用 DATETIME 類型存儲属划。

經(jīng)常會有人用字符串存儲日期型的數(shù)據(jù)（不正確的做法）：

缺點 1：無法用日期函數(shù)進行計算和比較恬叹。

缺點 2：用字符串存儲日期要占用更多的空間。

6. 同財務(wù)相關(guān)的金額類數(shù)據(jù)必須使用 decimal 類型

非精準(zhǔn)浮點：float同眯，double

精準(zhǔn)浮點：decimal

Decimal 類型為精準(zhǔn)浮點數(shù)绽昼，在計算時不會丟失精度。占用空間由定義的寬度決定须蜗，每 4 個字節(jié)可以存儲 9 位數(shù)字硅确，并且小數(shù)點要占用一個字節(jié)∶靼梗可用于存儲比 bigint 更大的整型數(shù)據(jù)菱农。

索引設(shè)計規(guī)范

1. 限制每張表上的索引數(shù)量，建議單張表索引不超過 5 個

索引并不是越多越好柿估！索引可以提高效率同樣也可以降低效率循未；索引可以增加查詢效率，但同樣也會降低插入和更新的效率秫舌，甚至有些情況下會降低查詢效率的妖。

因為 MySQL 優(yōu)化器在選擇如何優(yōu)化查詢時，會根據(jù)統(tǒng)一信息足陨，對每一個可以用到的索引來進行評估嫂粟，以生成出一個最好的執(zhí)行計劃，如果同時有很多個索引都可以用于查詢钠右，就會增加 MySQL 優(yōu)化器生成執(zhí)行計劃的時間赋元，同樣會降低查詢性能忘蟹。

2. 禁止給表中的每一列都建立單獨的索引

5.6 版本之前飒房，一個 SQL 只能使用到一個表中的一個索引搁凸，5.6 以后，雖然有了合并索引的優(yōu)化方式狠毯，但是還是遠遠沒有使用一個聯(lián)合索引的查詢方式好

3. 每個 InnoDB 表必須有個主鍵

InnoDB 是一種索引組織表：數(shù)據(jù)的存儲的邏輯順序和索引的順序是相同的护糖。每個表都可以有多個索引，但是表的存儲順序只能有一種 InnoDB是按照主鍵索引的順序來組織表的嚼松。

不要使用更新頻繁的列作為主鍵嫡良，不適用多列主鍵（相當(dāng)于聯(lián)合索引）不要使用 UUID、MD5献酗、HASH寝受、字符串列作為主鍵（無法保證數(shù)據(jù)的順序增長）。主鍵建議使用自增 ID 值罕偎。

常見索引列建議

出現(xiàn)在 SELECT很澄、UPDATE、DELETE 語句的 WHERE 從句中的列颜及。

包含在 ORDER BY甩苛、GROUP BY、DISTINCT 中的字段俏站。

并不要將符合 1 和 2 中的字段的列都建立一個索引讯蒲，通常將 1、2 中的字段建立聯(lián)合索引效果更好肄扎。

多表 JOIN 的關(guān)聯(lián)列墨林。

如何選擇索引列的順序

建立索引的目的是：希望通過索引進行數(shù)據(jù)查找，減少隨機 IO反浓，增加查詢性能萌丈，索引能過濾出越少的數(shù)據(jù)，則從磁盤中讀入的數(shù)據(jù)也就越少雷则。

區(qū)分度最高的放在聯(lián)合索引的最左側(cè)（區(qū)分度 = 列中不同值的數(shù)量 / 列的總行數(shù)）辆雾。

盡量把字段長度小的列放在聯(lián)合索引的最左側(cè)（因為字段長度越小，一頁能存儲的數(shù)據(jù)量越大月劈，IO 性能也就越好）度迂。

使用最頻繁的列放到聯(lián)合索引的左側(cè)（這樣可以比較少的建立一些索引）。

避免建立冗余索引和重復(fù)索引

因為這樣會增加查詢優(yōu)化器生成執(zhí)行計劃的時間猜揪。

重復(fù)索引示例：primary key(id)惭墓、index(id)、unique index(id)

冗余索引示例：index(a,b,c)而姐、index(a,b)腊凶、index(a）

優(yōu)先考慮覆蓋索引

對于頻繁的查詢優(yōu)先考慮使用覆蓋索引。

覆蓋索引：就是包含了所有查詢字段(where,select,ordery by,group by包含的字段)的索引

覆蓋索引的好處：

避免 InnoDB 表進行索引的二次查詢

InnoDB 是以聚集索引的順序來存儲的，對于 InnoDB 來說钧萍，二級索引在葉子節(jié)點中所保存的是行的主鍵信息褐缠，如果是用二級索引查詢數(shù)據(jù)的話，在查找到相應(yīng)的鍵值后风瘦，還要通過主鍵進行二次查詢才能獲取我們真實所需要的數(shù)據(jù)队魏。而在覆蓋索引中，二級索引的鍵值中可以獲取所有的數(shù)據(jù)万搔，避免了對主鍵的二次查詢胡桨，減少了 IO 操作，提升了查詢效率瞬雹。

可以把隨機 IO 變成順序 IO 加快查詢效率

由于覆蓋索引是按鍵值的順序存儲的昧谊，對于 IO 密集型的范圍查找來說，對比隨機從磁盤讀取每一行的數(shù)據(jù) IO 要少的多酗捌，因此利用覆蓋索引在訪問時也可以把磁盤的隨機讀取的 IO 轉(zhuǎn)變成索引查找的順序 IO揽浙。

索引SET規(guī)范

盡量避免使用外鍵約束。

不建議使用外鍵約束（foreign key）意敛，但一定要在表與表之間的關(guān)聯(lián)鍵上建立索引馅巷。

外鍵可用于保證數(shù)據(jù)的參照完整性，但建議在業(yè)務(wù)端實現(xiàn)草姻。

外鍵會影響父表和子表的寫操作從而降低性能钓猬。

數(shù)據(jù)庫SQL開發(fā)規(guī)范

1. 建議使用預(yù)編譯語句進行數(shù)據(jù)庫操作

預(yù)編譯語句可以重復(fù)使用這些計劃，減少 SQL 編譯所需要的時間撩独，還可以解決動態(tài) SQL 所帶來的 SQL 注入的問題只傳參數(shù)敞曹，比傳遞 SQL 語句更高效相同語句可以一次解析，多次使用综膀，提高處理效率澳迫。

2. 避免數(shù)據(jù)類型的隱式轉(zhuǎn)換

隱式轉(zhuǎn)換會導(dǎo)致索引失效。如：

selectname,phonefromcustomerwhereid='111';

3. 充分利用表上已經(jīng)存在的索引

避免使用雙 % 號的查詢條件剧劝。

如a like '%123%'橄登，（如果無前置 %，只有后置 %讥此，是可以用到列上的索引的）

一個 SQL 只能利用到復(fù)合索引中的一列進行范圍查詢

如：有 a,b,c 列的聯(lián)合索引拢锹，在查詢條件中有 a 列的范圍查詢，則在 b,c 列上的索引將不會被用到萄喳，在定義聯(lián)合索引時卒稳，如果a列要用到范圍查找的話，就要把 a 列放到聯(lián)合索引的右側(cè)他巨。

使用 left join 或 not exists 來優(yōu)化 not in 操作

因為 not in 也通常會使用索引失效充坑。

4. 數(shù)據(jù)庫設(shè)計時减江，應(yīng)該要對以后擴展進行考慮

5. 程序連接不同的數(shù)據(jù)庫使用不同的賬號，進制跨庫查詢

為數(shù)據(jù)庫遷移和分庫分表留出余地

降低業(yè)務(wù)耦合度

避免權(quán)限過大而產(chǎn)生的安全風(fēng)險

6. 禁止使用?SELECT *?必須使用 SELECT <字段列表> 查詢

原因：

消耗更多的 CPU 和 IO 以網(wǎng)絡(luò)帶寬資源

無法使用覆蓋索引

可減少表結(jié)構(gòu)變更帶來的影響

7. 禁止使用不含字段列表的 INSERT 語句

如：

insertintovalues('a','b','c');

應(yīng)使用：

insertintot(c1,c2,c3)values('a','b','c');

8. 避免使用子查詢捻爷，可以把子查詢優(yōu)化為 JOIN 操作

通常子查詢在 in 子句中您市，且子查詢中為簡單 SQL ( 不包含 union、group by役衡、order by、limit 從句 ) 時薪棒，才可以把子查詢轉(zhuǎn)化為關(guān)聯(lián)查詢進行優(yōu)化手蝎。

子查詢性能差的原因：

子查詢的結(jié)果集無法使用索引，通常子查詢的結(jié)果集會被存儲到臨時表中俐芯，不論是內(nèi)存臨時表還是磁盤臨時表都不會存在索引棵介，所以查詢性能會受到一定的影響。

特別是對于返回結(jié)果集比較大的子查詢吧史，其對查詢性能的影響也就越大邮辽。

由于子查詢會產(chǎn)生大量的臨時表也沒有索引，所以會消耗過多的 CPU 和 IO 資源贸营，產(chǎn)生大量的慢查詢吨述。

9. 避免使用 JOIN 關(guān)聯(lián)太多的表

對于 MySQL 來說，是存在關(guān)聯(lián)緩存的钞脂，緩存的大小可以由 join_buffer_size 參數(shù)進行設(shè)置揣云。

在 MySQL 中，對于同一個 SQL 多關(guān)聯(lián)（join）一個表冰啃，就會多分配一個關(guān)聯(lián)緩存邓夕，如果在一個 SQL 中關(guān)聯(lián)的表越多，所占用的內(nèi)存也就越大阎毅。

如果程序中大量的使用了多表關(guān)聯(lián)的操作焚刚，同時 join_buffer_size 設(shè)置的也不合理的情況下，就容易造成服務(wù)器內(nèi)存溢出的情況扇调，就會影響到服務(wù)器數(shù)據(jù)庫性能的穩(wěn)定性矿咕。

同時對于關(guān)聯(lián)操作來說，會產(chǎn)生臨時表操作狼钮，影響查詢效率 MySQL 最多允許關(guān)聯(lián) 61 個表痴腌，建議不超過 5 個。

10. 減少同數(shù)據(jù)庫的交互次數(shù)

數(shù)據(jù)庫更適合處理批量操作合并多個相同的操作到一起燃领，可以提高處理效率

11. 對應(yīng)同一列進行 or 判斷時士聪，使用 in 代替 or

In 的值不要超過 500 個， in 操作可以更有效的利用索引猛蔽，or 大多數(shù)情況下很少能利用到索引剥悟。

12. 禁止使用 order by rand() 進行隨機排序

會把表中所有符合條件的數(shù)據(jù)裝載到內(nèi)存中灵寺，然后在內(nèi)存中對所有數(shù)據(jù)根據(jù)隨機生成的值進行排序，并且可能會對每一行都生成一個隨機值区岗，如果滿足條件的數(shù)據(jù)集非常大略板，就會消耗大量的 CPU 和 IO 及內(nèi)存資源。

推薦在程序中獲取一個隨機值慈缔，然后從數(shù)據(jù)庫中獲取數(shù)據(jù)的方式叮称。

13. WHERE從句中禁止對列進行函數(shù)轉(zhuǎn)換和計算

對列進行函數(shù)轉(zhuǎn)換或計算時會導(dǎo)致無法使用索引。

不推薦：

wheredate(create_time)='20190101'

【轉(zhuǎn)載】一份值得收藏的的 MySQL 規(guī)范

推薦閱讀更多精彩內(nèi)容