熬夜肝了一篇數(shù)據(jù)庫(kù)規(guī)范，你應(yīng)該用得上

熬夜打卡】相信大多數(shù)的同學(xué)都非常了解這些條條款款了，之前我也認(rèn)為是這樣的，但是寫出來才發(fā)現(xiàn)有好些點(diǎn)之前都沒有深刻理解，比如覆蓋索引、預(yù)編譯、mysql驅(qū)動(dòng)那塊、還有那些行記錄格式，COLLATE 這些，收獲滿滿。

  1. 數(shù)據(jù)庫(kù)命名規(guī)范 采用小寫字母、數(shù)字（通常不需要）和下劃線組成。禁止使用’-’，命名簡(jiǎn)潔、含義明確。
  2. 表命名
  • 根據(jù)業(yè)務(wù)類型不同，采用不同的前綴，小寫字母、下劃線組成
  • 長(zhǎng)度控制在30個(gè)字符以內(nèi) 推薦的命名規(guī)則
熬夜肝了一篇數(shù)據(jù)庫(kù)規(guī)范，你應(yīng)該用得上

引擎

使用默認(rèn)Innodb引擎（5.5以后默認(rèn)）

支持事務(wù)、支持行級(jí)鎖、更好的恢復(fù)性、高并發(fā)下性能更好。

字符集 -- 拔劍起蒿萊

  • 數(shù)據(jù)庫(kù)和表的字符集統(tǒng)一,盡量使用UTF8（根據(jù)業(yè)務(wù)需求）

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

  • UTF8和UTF8MB4字段進(jìn)行關(guān)聯(lián)，會(huì)導(dǎo)致索引失效

  • 除非特殊情況，禁止建立指定字符集（采用庫(kù)默認(rèn)字符集），降低出現(xiàn)字符集不統(tǒng)一導(dǎo)致性能問題的風(fēng)險(xiǎn)。

  • 無特殊要求，禁止指定表COLLATE -----

  • COLLATE主要的作用是排序的規(guī)則以及檢索的規(guī)則，utf8字符集默認(rèn)的是 utf8_general_ci ，utf8mb4字符集默認(rèn)的是utf8mb4_general_ci，結(jié)尾的ci意思是不區(qū)分大小寫。

  • COLLATE會(huì)影響到ORDER BY語句的順序，會(huì)影響到WHERE條件中大于小于號(hào)篩選出來的結(jié)果，會(huì)影響DISTINCT、GROUP BY、HAVING語句的查詢結(jié)果。比如：select * from test where name like 'A%',在 utf8_bin字符集下，是無法檢索出 ‘a(chǎn)bc’字段的，并且排序的情況下Abc和abc所在的順序是不一致的。

  • 慎重選擇row_format（行記錄格式）

  • Barracuda: 新的文件格式。它支持InnoDB的所有行格式，包括新的行格式：COMPRESSED 和 DYNAMIC

  • 在 msyql 5.7.9 及以后版本，默認(rèn)行格式由innodb_default_row_format變量決定，它的默認(rèn)值是DYNAMIC

  • db默認(rèn)的innodb_file_format 為 Barracuda，默認(rèn)的innodb_default_row_format為 dynamic；其中COMPRESSED 壓縮比經(jīng)測(cè)試最大也就 1/2，但讀取和寫入會(huì)有額外cpu開銷，并且申請(qǐng)內(nèi)存是按照解壓后的原大小申請(qǐng)，在高并發(fā)情況下容易導(dǎo)致性能問題。

  • Dynamic行格式，列存儲(chǔ)是否放到off-page頁(yè)，主要取決于行大小，他會(huì)把行中最長(zhǎng)的一列放到off-page，直到數(shù)據(jù)頁(yè)能存放下兩行。TEXT或BLOB列<=40bytes時(shí)總是存在于數(shù)據(jù)頁(yè)。這種方式可以避免compact那樣把太多的大列值放到B-tree Node（數(shù)據(jù)頁(yè)中只存放20個(gè)字節(jié)的指針，實(shí)際的數(shù)據(jù)存放在Off Page中，之前的Compact 和 Redundant 兩種格式會(huì)存放768個(gè)前綴字節(jié)）。

  • Compressed物理結(jié)構(gòu)上與Dynamic類似，Compressed行記錄格式的另一個(gè)功能就是存儲(chǔ)在其中的行數(shù)據(jù)會(huì)以zlib的算法進(jìn)行壓縮，因此對(duì)于BLOB、TEXT、VARCHAR這類大長(zhǎng)度數(shù)據(jù)能夠進(jìn)行有效的存儲(chǔ)（減少40%，但對(duì)CPU要求更高）。

字段設(shè)計(jì) -- 人生感意氣 功名誰復(fù)論

  • 所有表和字段都需要添加注釋，使用comment從句添加表和列的備注 從一開始就進(jìn)行數(shù)據(jù)字典的維護(hù)

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

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

  • 謹(jǐn)慎使用MySQL分區(qū)表

  • 分區(qū)表在物理上表現(xiàn)為多個(gè)文件，在邏輯上表現(xiàn)為一個(gè)表。謹(jǐn)慎選擇分區(qū)鍵，跨分區(qū)查詢效率可能更低，另外，對(duì)于表結(jié)構(gòu)維護(hù)，分區(qū)表的維護(hù)造成的開銷更集中，建議采用物理分表的方式管理大數(shù)據(jù)

  • 建議將大字段，訪問頻度低的字段拆分到單獨(dú)的表中存儲(chǔ)，分離冷熱數(shù)據(jù)，盡量做到冷熱數(shù)據(jù)分離，減小表的寬度

  • MySQL限制每個(gè)表最多存儲(chǔ)4096列，并且每一行數(shù)據(jù)的大小不能超過65535字節(jié)。為減少磁盤IO,保證熱數(shù)據(jù)的內(nèi)存緩存命中率（表越寬，把表裝載進(jìn)內(nèi)存緩沖池時(shí)所占用的內(nèi)存也就越大,也會(huì)消耗更多的IO），更有效的利用緩存，避免讀入無用的冷數(shù)據(jù)，經(jīng)常一起使用的列放到一個(gè)表中（避免更多的關(guān)聯(lián)操作）。對(duì)于非常用的字段，建議采用擴(kuò)展表的方式進(jìn)行分表。

  • 注意：每一行數(shù)據(jù)的65535字節(jié)中，utf8字符集下，varchar每一個(gè)長(zhǎng)度占用3個(gè)字節(jié)，utf8mb4字符集下，每一個(gè)長(zhǎng)度占用4個(gè)字節(jié)

  • 盡量不在表中建立預(yù)留字段 預(yù)留字段的命名很難做到見名識(shí)義，預(yù)留字段無法確認(rèn)存儲(chǔ)的數(shù)據(jù)類型，所以無法選擇合適的類型。對(duì)預(yù)留字段類型的修改，會(huì)對(duì)表進(jìn)行鎖定

  • 禁止使用外鍵約束 外鍵使得表之間相互耦合，影響update/delete等SQL性能，有可能造成死鎖，高并發(fā)情況下容易成為數(shù)據(jù)庫(kù)瓶頸。建議在業(yè)務(wù)端實(shí)現(xiàn)。

數(shù)據(jù)庫(kù)字段設(shè)計(jì)規(guī)范---愿君學(xué)長(zhǎng)松 慎勿作桃李

  • 關(guān)于數(shù)據(jù)長(zhǎng)度 夠用前提下，越短越好，這樣能夠消耗更少的存儲(chǔ)空間；因排序申請(qǐng)的內(nèi)存大小和字段長(zhǎng)度有關(guān)，需要進(jìn)行排序時(shí)，長(zhǎng)度小的字段消耗更少的內(nèi)存空間；優(yōu)先選擇符合存儲(chǔ)需要的最小的數(shù)據(jù)類型
  • 禁止使用TEXT/BLOB類型，禁止在數(shù)據(jù)庫(kù)中存儲(chǔ)圖片，文件等大的二進(jìn)制數(shù)據(jù) 通常文件很大，會(huì)短時(shí)間內(nèi)造成數(shù)據(jù)量快速增長(zhǎng)，數(shù)據(jù)庫(kù)進(jìn)行數(shù)據(jù)庫(kù)讀取時(shí)，通常會(huì)進(jìn)行大量的隨機(jī)IO操作，文件很大時(shí)，IO操作很耗時(shí)。通常存儲(chǔ)于文件服務(wù)器，數(shù)據(jù)庫(kù)只存儲(chǔ)文件地址信息
  • 避免使用ENUM(枚舉)類型 修改ENUM只需要使用ALTER語句;ENUM類型的ORDER BY操作效率低，需要額外操作；禁止使用數(shù)值作為ENUM的枚舉值
  • 盡可能把所有列定義為NOT NULL 索引NULL列需要額外的空間來保存，所以要占用更多的空間 進(jìn)行比較和計(jì)算時(shí)要對(duì)NULL值做特別的處理 NULL只能采用IS NULL或者IS NOT NULL，而在=/!=/in/not in時(shí)很容易造成查詢結(jié)果與設(shè)計(jì)邏輯不符
  • 使用TIMESTAMP（4個(gè)字節(jié)）或DATETIME類型（5個(gè)字節(jié)）存儲(chǔ)時(shí)間 網(wǎng)上很多博客都說DATETIME是8個(gè)字節(jié)，其實(shí)在5.6.4版本一上就減少到5個(gè)字節(jié) mysql 源碼 github 地址

longlong TIME_to_longlong_datetime_packed(const MYSQL_TIME &my_time) {
 longlong ymd = ((my_time.year * 13 + my_time.month) << 5) | my_time.day;
 longlong hms = (my_time.hour << 12) | (my_time.minute << 6) | my_time.second;
 longlong tmp = my_packed_time_make(((ymd << 17) | hms), my_time.second_part);
 assert(!check_datetime_range(my_time)); /* Make sure no overflow */
 return my_time.neg ? -tmp : tmp;
}
根據(jù)上述算法，計(jì)算極限時(shí)間 9999-12-31 23:59:59
       時(shí)間各部分依次是 year-month-day hour:minute:second

1. 計(jì)算 longlong ymd
   year*13 + month = 9999*13 + 12 = 129999
   將 129999 左移 5 位，再與 31 進(jìn)行或運(yùn)算
       0000 0000 0011 1111 0111 1001 111[0 0000]   --- 129999 左移 5 位 （年*13 + 月）
       0000 0000 0000 0000 0000 0000 0001 1111     ---  31 （日）
     = 0000 0000 0011 1111 0111 1001 1111 1111     ---  得出 longlong ymd 低位，極限有         22 位

2. 計(jì)算 longlong hms
    將 hour 左移 12 位，與 minute 左移 6 位，再與 second 進(jìn)行或運(yùn)算
    0001 0111 [0000 0000 0000]   ---   23 左移 12 位 （時(shí)）
              1110 11[00 0000]   ---   59 左移 6 位 （分）
                      11 1011    ---   59 （秒）
   = 0001 0111 1110 1111 1011    ---   得出 longlong hms 的低位，極限有 17 位

3. 計(jì)算 longlong tmp
     ymd 右移 17 位，與 hms 進(jìn)行或運(yùn)算，這樣剛好存到 39 位。（至此，再加上 1 位標(biāo)識(shí)位，也           就剛好 40 位，為 5 字節(jié)了）
     再使用 my_packed_time_make(）函數(shù)，將 ymd 與 小數(shù)秒部分 連起來。

TIMESTAMP存儲(chǔ)的時(shí)間范圍：1970-01-01 00:00:01 ~ 2038-01-19-03:14:07。

TIMESTAMP占用4字節(jié)和INT相同，但比INT可讀性高

超出TIMESTAMP取值范圍的使用DATETIME類型存儲(chǔ)。

  • 同財(cái)務(wù)相關(guān)的金額類數(shù)據(jù){設(shè)計(jì)使用小數(shù)}必須使用decimal類型 Decimal類型為精準(zhǔn)浮點(diǎn)數(shù)，在計(jì)算時(shí)不會(huì)丟失精度。* 同一意義的字段定義必須相同* 同一意義的字段定義包括字段類型和長(zhǎng)度范圍必須相同* 增加字段時(shí)禁止指定after* VARCHAR(N)，N盡可能小 如果N<256時(shí)會(huì)使用一個(gè)字節(jié)來存儲(chǔ)長(zhǎng)度，如果N>=256時(shí)則使用兩個(gè)字節(jié)來存儲(chǔ)長(zhǎng)度。* 數(shù)值型字段，default值建議選用0

索引設(shè)計(jì)規(guī)范 ---共矜然諾心 各負(fù)縱橫志????

  • 創(chuàng)建表一定要有主鍵（PRIMARY KEY），推薦使用雪花或梨花。
  • 不要使用UUID、MD5、HASH、字符串列作為主鍵（無法保證數(shù)據(jù)的順序增長(zhǎng)）。
  • 限制每張表上的索引數(shù)量 索引并不是越多越好！索引可以提高效率同樣可以降低效率。索引可以增加查詢效率，但同樣也會(huì)降低插入和更新的效率，甚至有些情況下會(huì)降低查詢效率。因?yàn)閙ysql優(yōu)化器在選擇如何優(yōu)化查詢時(shí)，會(huì)根據(jù)統(tǒng)一信息，對(duì)每一個(gè)可以用到的索引來進(jìn)行評(píng)估，以生成出一個(gè)最好的執(zhí)行計(jì)劃，如果同時(shí)有很多個(gè)索引都可以用于查詢，就會(huì)增加mysql優(yōu)化器生成執(zhí)行計(jì)劃的時(shí)間，同樣會(huì)降低查詢性能。
  • 區(qū)分度最高的放在聯(lián)合索引的最左側(cè)（區(qū)分度=列中不同值的數(shù)量/列的總行數(shù)）；
  • 盡量把字段長(zhǎng)度小的列放在聯(lián)合索引的最左側(cè)（因?yàn)樽侄伍L(zhǎng)度越小，一頁(yè)能存儲(chǔ)的數(shù)據(jù)量越大，IO性能也就越好）；
  • 使用最頻繁的列放到聯(lián)合索引的左側(cè)（這樣可以比較少的建立一些索引）。
  • 避免建立冗余索引和重復(fù)索引---因?yàn)檫@樣會(huì)增加查詢優(yōu)化器生成執(zhí)行計(jì)劃的時(shí)間。 重復(fù)索引示例：primary key(id)、index(id)、unique index(id) 冗余索引示例：index(a,b,c)、index(a,b)、index(a)
  • 優(yōu)先考慮覆蓋索引 對(duì)于頻繁的查詢優(yōu)先考慮使用覆蓋索引。覆蓋索引就是包含了所有查詢字段(where,select,ordery by,group by包含的字段)的索引 覆蓋索引的好處：1.可以把隨機(jī)IO變成順序IO加快查詢效率；2.能夠避免回表查詢，提升查詢效率
  • 一定要在表與表之間的關(guān)聯(lián)鍵上建立索引

sql開發(fā)規(guī)劃 --- 月缺不改光 劍折不改剛????????

  • 建議使用預(yù)編譯語句進(jìn)行數(shù)據(jù)庫(kù)操作 預(yù)編譯語句可以重復(fù)使用這些計(jì)劃，減少SQL編譯所需要的時(shí)間，還可以解決動(dòng)態(tài)SQL所帶來的SQL注入的問題；只傳參數(shù)，比傳遞SQL語句更高效；相同語句可以一次解析，多次使用，提高處理效率。 在實(shí)際生產(chǎn)環(huán)境中，如MyBatis等ORM框架大量使用了預(yù)編譯語句，最終底層調(diào)用都會(huì)走到MySQL驅(qū)動(dòng)里，從驅(qū)動(dòng)中了解相關(guān)實(shí)現(xiàn)細(xì)節(jié)有助于更好地理解預(yù)編譯語句 就像我們熟悉的#{}是經(jīng)過預(yù)編譯的，是安全的；${}是未經(jīng)過預(yù)編譯的，僅僅是取變量的值，是非安全的，存在SQL注入 MySQL驅(qū)動(dòng)里對(duì)于server預(yù)編譯的情況維護(hù)了兩個(gè)基于LinkedHashMap使用LRU策略的cache，分別是serverSideStatementCheckCache用于緩存sql語句是否可以由服務(wù)端來緩存以及serverSideStatementCache用于緩存服務(wù)端預(yù)編譯sql語句，這兩個(gè)緩存的大小由prepStmtCacheSize參數(shù)控制。
  • 避免數(shù)據(jù)類型的隱式轉(zhuǎn)換 隱式轉(zhuǎn)換會(huì)導(dǎo)致索引失效。如：select name,phone from customer where id = '111';
  • 充分利用表上已經(jīng)存在的索引
  • 避免使用雙%號(hào)的查詢條件 如a like '%123%'，（如果無前置%,只有后置%，是可以用到列上的索引的）。
  • 一個(gè)SQL只能利用到復(fù)合索引中的一列進(jìn)行范圍查詢 如：有 a,b,c列的聯(lián)合索引，在查詢條件中有a列的范圍查詢，則在b,c列上的索引將不會(huì)被用到，在定義聯(lián)合索引時(shí)，如果a列要用到范圍查找的話，就要把a(bǔ)列放到聯(lián)合索引的右側(cè)。
  • WHERE從句中禁止對(duì)列進(jìn)行函數(shù)轉(zhuǎn)換和計(jì)算 不推薦：where date(create_time)=20190101 推薦：where create_time >= 20190101 and create_time < 20190102
  • 在明顯不會(huì)有重復(fù)值時(shí)使用UNION ALL而不是UNION UNION會(huì)把兩個(gè)結(jié)果集的所有數(shù)據(jù)放到臨時(shí)表中后再進(jìn)行去重和排序操作 UNION ALL不會(huì)再對(duì)結(jié)果集進(jìn)行去重和排序操作
  • 拆分復(fù)雜的大SQL為多個(gè)小SQL
  • SQL 性能優(yōu)化的目標(biāo):至少要達(dá)到 range 級(jí)別，要求是 ref 級(jí)別，如果可以是 consts 最好。
  • 不要使用 count(列名)或 count(常量)來替代 count()，count()就是 SQL92 定義 的標(biāo)準(zhǔn)統(tǒng)計(jì)行數(shù)的語法，跟數(shù)據(jù)庫(kù)無關(guān)，跟 NULL 和非 NULL 無關(guān)。
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時(shí)請(qǐng)結(jié)合常識(shí)與多方信息審慎甄別。
平臺(tái)聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡(jiǎn)書系信息發(fā)布平臺(tái)，僅提供信息存儲(chǔ)服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容