數(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 存儲引擎（MySQL5.5 之前默認使用 Myisam奉芦，5.6 以后默認的為 Innodb）。

Innodb 支持事務(wù)瘫想，支持行級鎖仗阅，更好的恢復(fù)性昌讲，高并發(fā)下性能更好国夜。

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

兼容性更好，統(tǒng)一字符集可以避免由于字符集轉(zhuǎn)換產(chǎn)生的亂碼短绸，不同的字符集進行比較前需要進行轉(zhuǎn)換會造成索引失效车吹，如果數(shù)據(jù)庫中有存儲 emoji 表情的需要，字符集需要采用 utf8mb4 字符集醋闭。

參考文章：MySQL 字符集不一致導(dǎo)致索引失效的一個真實案例open in new window

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. 謹慎使用 MySQL 分區(qū)表

分區(qū)表在物理上表現(xiàn)為多個文件，在邏輯上表現(xià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ù)留字段無法確認存儲的數(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)境直接連接生產(chǎn)環(huán)境數(shù)據(jù)庫

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

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

原因：

列的字段越大氓轰，建立索引時所需要的空間也就越大，這樣一頁中所能存儲的索引節(jié)點的數(shù)量也就越少也越少浸卦，在遍歷時所需要的 IO 次數(shù)也就越多署鸡，索引的性能也就越差。

方法：

a.將字符串轉(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)為地址顯示即可炉抒。

b.對于非負型的數(shù)據(jù) (如自增 ID,整型 IP) 來說,要優(yōu)先使用無符號整型來存儲

原因：

無符號相對于有符號可以多出一倍的存儲空間

SIGNED INT -2147483648~2147483647
UNSIGNED INT 0~4294967295

1
2

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

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

a. 建議把 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ù)時不要對該列進行查詢娜睛。

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

因為MySQLopen in new window 對索引字段長度是有限制的卦睹，所以 TEXT 類型只能使用前綴索引畦戒，并且 TEXT 列上是不能有默認值的

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 類型

• 非精準浮點：float,double
• 精準浮點：decimal

Decimal 類型為精準浮點數(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 是按照主鍵索引的順序來組織表的

• 不要使用更新頻繁的列作為主鍵，不適用多列主鍵（相當于聯(lián)合索引）
• 不要使用 UUID,MD5,HASH,字符串列作為主鍵（無法保證數(shù)據(jù)的順序增長）
• 主鍵建議使用自增 ID 值

4. 常見索引列建議

• 出現(xiàn)在 SELECT嗤无、UPDATE震束、DELETE 語句的 WHERE 從句中的列
• 包含在 ORDER BY怜庸、GROUP BY、DISTINCT 中的字段
• 并不要將符合 1 和 2 中的字段的列都建立一個索引垢村， 通常將 1割疾、2 中的字段建立聯(lián)合索引效果更好
• 多表 join 的關(guān)聯(lián)列

5.如何選擇索引列的順序

建立索引的目的是：希望通過索引進行數(shù)據(jù)查找，減少隨機 IO嘉栓，增加查詢性能 宏榕，索引能過濾出越少的數(shù)據(jù)，則從磁盤中讀入的數(shù)據(jù)也就越少侵佃。

• 區(qū)分度最高的放在聯(lián)合索引的最左側(cè)（區(qū)分度=列中不同值的數(shù)量/列的總行數(shù)）
• 盡量把字段長度小的列放在聯(lián)合索引的最左側(cè)（因為字段長度越小麻昼，一頁能存儲的數(shù)據(jù)量越大，IO 性能也就越好）
• 使用最頻繁的列放到聯(lián)合索引的左側(cè)（這樣可以比較少的建立一些索引）

6. 避免建立冗余索引和重復(fù)索引（增加了查詢優(yōu)化器生成執(zhí)行計劃的時間）

• 重復(fù)索引示例：primary key(id)馋辈、index(id)涌献、unique index(id)
• 冗余索引示例：index(a,b,c)、index(a,b)首有、index(a)

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

覆蓋索引：就是包含了所有查詢字段 (where,select,order 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挣棕。

8.索引 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)致索引失效如:

select name,phone from customer where id = '111';

1

詳細解讀可以看：MySQL中的隱式轉(zhuǎn)換造成的索引失效 這篇文章户辫。

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 語句

如：

insert into t values ('a','b','c');

1

應(yīng)使用：

insert into t(c1,c2,c3) values ('a','b','c');

1

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() 進行隨機排序

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)致無法使用索引

不推薦：

where date(create_time)='20190101'

1

推薦：

where create_time >= '20190101' and create_time < '20190102'

1

14. 在明顯不會有重復(fù)值時使用 UNION ALL 而不是 UNION

• UNION 會把兩個結(jié)果集的所有數(shù)據(jù)放到臨時表中后再進行去重操作
• UNION ALL 不會再對結(jié)果集進行去重操作

15. 拆分復(fù)雜的大 SQL 為多個小 SQL

• 大 SQL 邏輯上比較復(fù)雜，需要占用大量 CPU 進行計算的 SQL
• MySQL 中翼抠，一個 SQL 只能使用一個 CPU 進行計算
• SQL 拆分后可以通過并行執(zhí)行來提高處理效率

數(shù)據(jù)庫操作行為規(guī)范

1. 超 100 萬行的批量寫 (UPDATE,DELETE,INSERT) 操作,要分批多次進行操作

大批量操作可能會造成嚴重的主從延遲

主從環(huán)境中,大批量操作可能會造成嚴重的主從延遲咙轩，大批量的寫操作一般都需要執(zhí)行一定長的時間， 而只有當主庫上執(zhí)行完成后阴颖，才會在其他從庫上執(zhí)行活喊，所以會造成主庫與從庫長時間的延遲情況

binlog 日志為 row 格式時會產(chǎn)生大量的日志

大批量寫操作會產(chǎn)生大量日志，特別是對于 row 格式二進制數(shù)據(jù)而言量愧，由于在 row 格式中會記錄每一行數(shù)據(jù)的修改钾菊，我們一次修改的數(shù)據(jù)越多，產(chǎn)生的日志量也就會越多偎肃，日志的傳輸和恢復(fù)所需要的時間也就越長煞烫，這也是造成主從延遲的一個原因

避免產(chǎn)生大事務(wù)操作

大批量修改數(shù)據(jù)，一定是在一個事務(wù)中進行的累颂，這就會造成表中大批量數(shù)據(jù)進行鎖定滞详，從而導(dǎo)致大量的阻塞凛俱，阻塞會對 MySQL 的性能產(chǎn)生非常大的影響。

特別是長時間的阻塞會占滿所有數(shù)據(jù)庫的可用連接料饥，這會使生產(chǎn)環(huán)境中的其他應(yīng)用無法連接到數(shù)據(jù)庫蒲犬，因此一定要注意大批量寫操作要進行分批

2. 對于大表使用 pt-online-schema-change 修改表結(jié)構(gòu)

• 避免大表修改產(chǎn)生的主從延遲
• 避免在對表字段進行修改時進行鎖表

對大表數(shù)據(jù)結(jié)構(gòu)的修改一定要謹慎，會造成嚴重的鎖表操作岸啡，尤其是生產(chǎn)環(huán)境原叮，是不能容忍的。

pt-online-schema-change 它會首先建立一個與原表結(jié)構(gòu)相同的新表巡蘸，并且在新表上進行表結(jié)構(gòu)的修改奋隶，然后再把原表中的數(shù)據(jù)復(fù)制到新表中，并在原表中增加一些觸發(fā)器赡若。把原表中新增的數(shù)據(jù)也復(fù)制到新表中达布，在行所有數(shù)據(jù)復(fù)制完成之后，把新表命名成原表逾冬，并把原來的表刪除掉黍聂。把原來一個 DDL 操作，分解成多個小的批次進行身腻。

3. 禁止為程序使用的賬號賦予 super 權(quán)限

• 當達到最大連接數(shù)限制時产还，還運行 1 個有 super 權(quán)限的用戶連接
• super 權(quán)限只能留給 DBA 處理問題的賬號使用

4. 對于程序連接數(shù)據(jù)庫賬號,遵循權(quán)限最小原則

• 程序使用數(shù)據(jù)庫賬號只能在一個 DB 下使用，不準跨庫
• 程序使用的賬號原則上不準有 drop 權(quán)限