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

1. 數(shù)據(jù)庫命名規(guī)范 采用小寫字母、數(shù)字（通常不需要）和下劃線組成厦坛。禁止使用’-’五垮，命名簡潔、含義明確杜秸。
2. 表命名

• 根據(jù)業(yè)務(wù)類型不同拼余，采用不同的前綴，小寫字母亩歹、下劃線組成
• 長度控制在30個(gè)字符以內(nèi) 推薦的命名規(guī)則

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

引擎

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

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

字符集 -- 拔劍起蒿萊

• 數(shù)據(jù)庫和表的字符集統(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)致索引失效

• 除非特殊情況，禁止建立指定字符集（采用庫默認(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頁，主要取決于行大小蕉扮，他會(huì)把行中最長的一列放到off-page整胃，直到數(shù)據(jù)頁能存放下兩行。TEXT或BLOB列<=40bytes時(shí)總是存在于數(shù)據(jù)頁喳钟。這種方式可以避免compact那樣把太多的大列值放到B-tree Node（數(shù)據(jù)頁中只存放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這類大長度數(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ù)庫的限制，過大會(huì)造成修改表結(jié)構(gòu)匕垫，備份僧鲁，恢復(fù)都會(huì)有很大的問題，可以用歷史數(shù)據(jù)歸檔（應(yīng)用于日志數(shù)據(jù)）年缎，分庫分表（應(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è)長度占用3個(gè)字節(jié)凳厢，utf8mb4字符集下账胧，每一個(gè)長度占用4個(gè)字節(jié)

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

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

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

• 關(guān)于數(shù)據(jù)長度 夠用前提下变屁，越短越好，這樣能夠消耗更少的存儲(chǔ)空間意狠；因排序申請(qǐng)的內(nèi)存大小和字段長度有關(guān)粟关，需要進(jìn)行排序時(shí)，長度小的字段消耗更少的內(nèi)存空間环戈；優(yōu)先選擇符合存儲(chǔ)需要的最小的數(shù)據(jù)類型
• 禁止使用TEXT/BLOB類型闷板，禁止在數(shù)據(jù)庫中存儲(chǔ)圖片，文件等大的二進(jìn)制數(shù)據(jù) 通常文件很大院塞，會(huì)短時(shí)間內(nèi)造成數(shù)據(jù)量快速增長遮晚，數(shù)據(jù)庫進(jìn)行數(shù)據(jù)庫讀取時(shí)，通常會(huì)進(jìn)行大量的隨機(jī)IO操作拦止，文件很大時(shí)县遣，IO操作很耗時(shí)。通常存儲(chǔ)于文件服務(wù)器汹族，數(shù)據(jù)庫只存儲(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ì)丟失精度。* 同一意義的字段定義必須相同* 同一意義的字段定義包括字段類型和長度范圍必須相同* 增加字段時(shí)禁止指定after* VARCHAR(N)卷玉，N盡可能小 如果N<256時(shí)會(huì)使用一個(gè)字節(jié)來存儲(chǔ)長度哨颂，如果N>=256時(shí)則使用兩個(gè)字節(jié)來存儲(chǔ)長度。* 數(shù)值型字段相种，default值建議選用0

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

• 創(chuàng)建表一定要有主鍵（PRIMARY KEY）威恼，推薦使用雪花或梨花。
• 不要使用UUID寝并、MD5箫措、HASH、字符串列作為主鍵（無法保證數(shù)據(jù)的順序增長）衬潦。
• 限制每張表上的索引數(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ù)）；
• 盡量把字段長度小的列放在聯(lián)合索引的最左側(cè)（因?yàn)樽侄伍L度越小匆赃，一頁能存儲(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ù)庫操作 預(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ù)庫無關(guān)善榛，跟 NULL 和非 NULL 無關(guān)辩蛋。