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

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

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

引擎

使用默認Innodb引擎（5.5以后默認）

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

字符集 -- 拔劍起蒿萊

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

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

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

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

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

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

  • COLLATE會影響到ORDER BY語句的順序，會影響到WHERE條件中大于小于號篩選出來的結(jié)果，會影響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 及以后版本，默認行格式由innodb_default_row_format變量決定，它的默認值是DYNAMIC

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

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

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

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

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

  • 盡量控制單表數(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ù)量大小

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

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

  • 建議將大字段，訪問頻度低的字段拆分到單獨的表中存儲，分離冷熱數(shù)據(jù)，盡量做到冷熱數(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)操作）。對于非常用的字段，建議采用擴展表的方式進行分表。

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

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

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

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

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

1. 計算 longlong ymd
   year*13 + month = 9999*13 + 12 = 129999
   將 129999 左移 5 位，再與 31 進行或運算
       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. 計算 longlong hms
    將 hour 左移 12 位，與 minute 左移 6 位，再與 second 進行或運算
    0001 0111 [0000 0000 0000]   ---   23 左移 12 位 （時）
              1110 11[00 0000]   ---   59 左移 6 位 （分）
                      11 1011    ---   59 （秒）
   = 0001 0111 1110 1111 1011    ---   得出 longlong hms 的低位，極限有 17 位

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

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

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

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

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

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

  • 創(chuàng)建表一定要有主鍵（PRIMARY KEY），推薦使用雪花或梨花。
  • 不要使用UUID、MD5、HASH、字符串列作為主鍵（無法保證數(shù)據(jù)的順序增長）。
  • 限制每張表上的索引數(shù)量 索引并不是越多越好！索引可以提高效率同樣可以降低效率。索引可以增加查詢效率，但同樣也會降低插入和更新的效率，甚至有些情況下會降低查詢效率。因為mysql優(yōu)化器在選擇如何優(yōu)化查詢時，會根據(jù)統(tǒng)一信息，對每一個可以用到的索引來進行評估，以生成出一個最好的執(zhí)行計劃，如果同時有很多個索引都可以用于查詢，就會增加mysql優(yōu)化器生成執(zhí)行計劃的時間，同樣會降低查詢性能。
  • 區(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包含的字段)的索引 覆蓋索引的好處：1.可以把隨機IO變成順序IO加快查詢效率；2.能夠避免回表查詢，提升查詢效率
  • 一定要在表與表之間的關(guān)聯(lián)鍵上建立索引

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

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

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

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