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

1. 采用小寫字母、數(shù)字（通常不需要）和下劃線組成。禁止使用’-’，命名簡潔、含義明確。

表命名

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

引擎

字符集 --拔劍起蒿萊

• 數(shù)據(jù)庫和表的字符集統(tǒng)一,盡量使用UTF8（根據(jù)業(yè)務(wù)需求）
• 兼容性更好，統(tǒng)一字符集可以避免由于字符集轉(zhuǎn)換產(chǎn)生的亂碼，不同的字符集進(jìn)行比較前需要進(jìn)行轉(zhuǎn)換會造成索引失效
• UTF8和UTF8MB4字段進(jìn)行關(guān)聯(lián)，會導(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會影響到ORDER BY語句的順序，會影響到WHERE條件中大于小于號篩選出來的結(jié)果，會影響DISTINCT、GROUP BY、HAVING語句的查詢結(jié)果。比如：select * from test where name like 'A%',在 utf8_bin字符集下，是無法檢索出 'abc’字段的，并且排序的情況下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)測試最大也就 1/2，但讀取和寫入會有額外cpu開銷，并且申請內(nèi)存是按照解壓后的原大小申請，在高并發(fā)情況下容易導(dǎo)致性能問題。
• Dynamic行格式，列存儲是否放到off-page頁，主要取決于行大小，他會把行中最長的一列放到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 兩種格式會存放768個(gè)字前綴字節(jié)）。
• Compressed物理結(jié)構(gòu)上與Dynamic類似，Compressed行記錄格式的另一個(gè)功能就是存儲在其中的行數(shù)據(jù)會以zlib的算法進(jìn)行壓縮，因此對于BLOB、TEXT、VARCHAR這類大長度數(shù)據(jù)能夠進(jìn)行有效的存儲（減少40%，但對CPU要求更高）。

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

• 所有表和字段都需要添加注釋，使用comment從句添加表和列的備注 從一開始就進(jìn)行數(shù)據(jù)字典的維護(hù)
• 盡量控制單表數(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ù)量大小
• 謹(jǐn)慎使用MySQL分區(qū)表
• 分區(qū)表在物理上表現(xiàn)為多個(gè)文件，在邏輯上表現(xiàn)為一個(gè)表。謹(jǐn)慎選擇分區(qū)鍵，跨分區(qū)查詢效率可能更低，另外，對于表結(jié)構(gòu)維護(hù)，分區(qū)表的維護(hù)造成的開銷更集中，建議采用物理分表的方式管理大數(shù)據(jù)
• 建議將大字段，訪問頻度低的字段拆分到單獨(dú)的表中存儲，分離冷熱數(shù)據(jù)，盡量做到冷熱數(shù)據(jù)分離，減小表的寬度
• MySQL限制每個(gè)表最多存儲4096列，并且每一行數(shù)據(jù)的大小不能超過65535字節(jié)。為減少磁盤IO,保證熱數(shù)據(jù)的內(nèi)存緩存命中率（表越寬，把表裝載進(jìn)內(nèi)存緩沖池時(shí)所占用的內(nèi)存也就越大,也會消耗更多的IO），更有效的利用緩存，避免讀入無用的冷數(shù)據(jù)，經(jīng)常一起使用的列放到一個(gè)表中（避免更多的關(guān)聯(lián)操作）。對于非常用字段，建議采用擴(kuò)展表的方式進(jìn)行分表。
• 注意：每一行數(shù)據(jù)的65535字節(jié)中，utf8字符集下，varchar每一個(gè)長度占用3個(gè)字節(jié)，utf8mb4字符集下，每一個(gè)長度占用4個(gè)字節(jié)
• 盡量不在表中建立預(yù)留字段
• 預(yù)留字段的命名很難做到見名識義，預(yù)留字段無法確認(rèn)存儲的數(shù)據(jù)類型，所以無法選擇合適的類型。對預(yù)留字段類型的修改，會對表進(jìn)行鎖
• 禁止使用外鍵約束
• 外鍵使得表之間相互耦合，影響update/delete等SQL性能，有可能造成死鎖，高并發(fā)情況下容易成為數(shù)據(jù)庫瓶頸。建議在業(yè)務(wù)端實(shí)現(xiàn)。

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

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

• TIMESTAMP存儲的時(shí)間范圍：1970-01-01 00:00:01 ~ 2038-01-19-03:14:07。
• TIMESTAMP占用4字節(jié)和INT相同，但比INT可讀性高
• 超出TIMESTAMP取值范圍的使用DATETIME類型存儲。
• 同財(cái)務(wù)相關(guān)的金額類數(shù)據(jù){設(shè)計(jì)使用小數(shù)}必須使用decimal類型
• Decimal類型為精準(zhǔn)浮點(diǎn)數(shù)，在計(jì)算時(shí)不會丟失精度。
• 同一意義的字段定義必須相同
• 同一意義的字段定義包括字段類型和長度范圍必須相同
• 增加字段時(shí)禁止指定after
• VARCHAR(N)，N盡可能小
• 如果N<256時(shí)會使用一個(gè)字節(jié)來存儲長度，如果N>=256則使用兩個(gè)字節(jié)來存儲長度。
• 數(shù)值型字段，default值建議選用0

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

• 創(chuàng)建表一定要有主鍵（PRIMARY KEY），推薦使用雪花或梨花。
• 不要使用UUID、MD5、HASH、字符串列作為主鍵（無法保證數(shù)據(jù)的順序增長）。
• 限制每張表上的索引數(shù)量
• 索引并不是越多越好！索引可以提高效率同樣可以降低效率。索引可以增加查詢效率，但同樣也會降低插入和更新的效率，甚至有些情況下會降低查詢效率。因?yàn)閙ysql優(yōu)化器在選擇如何優(yōu)化查詢時(shí)，會根據(jù)統(tǒng)一信息，對每一個(gè)可以用到的索引來進(jìn)行評估，以生成出一個(gè)最好的執(zhí)行計(jì)劃，如果同時(shí)有很多個(gè)索引都可以用于查詢，就會增加mysql優(yōu)化器生成執(zhí)行計(jì)劃的時(shí)間，同樣會降低查詢性能。
• 區(qū)分度最高的放在聯(lián)合索引的最左側(cè)（區(qū)分度=列中不同值的數(shù)量/列的總行數(shù)）；
• 盡量把字段長度小的列放在聯(lián)合索引的最左側(cè)（因?yàn)樽侄伍L度越小，一頁能存儲的數(shù)據(jù)量越大，IO性能也就越好）；
• 使用最頻繁的列放到聯(lián)合索引的左側(cè)（這樣可以比較少的建立一些索引）。
• 避免建立冗余索引和重復(fù)索引---因?yàn)檫@樣會增加查詢優(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)先考慮覆蓋索引
• 對于頻繁的查詢優(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í)間，還可以解決動態(tài)SQL所帶來的SQL注入的問題；只傳參數(shù)，比傳遞SQL語句更高效；相同語句可以一次解析，多次使用，提高處理效率。
• 在實(shí)際生產(chǎn)環(huán)境中，如MyBatis等ORM框架大量使用了預(yù)編譯語句，最終底層調(diào)用都會走到MySQL驅(qū)動里，從驅(qū)動中了解相關(guān)實(shí)現(xiàn)細(xì)節(jié)有助于更好地理解預(yù)編譯語句
• 就像我們熟悉的#{}是經(jīng)過預(yù)編譯的，是安全的；${}是未經(jīng)過預(yù)編譯的，僅僅是取變量的值，是非安全的，存在SQL注入
• MySQL驅(qū)動里對于server預(yù)編譯的情況維護(hù)了兩個(gè)基于LinkedHashMap使用LRU策略的cache，分別是serverSideStatementCheckCache用于緩存sql語句是否可以由服務(wù)端來緩存以及serverSideStatementCache用于緩存服務(wù)端預(yù)編譯sql語句，這兩個(gè)緩存的大小由prepStmtCacheSize參數(shù)控制。
• 避免數(shù)據(jù)類型的隱式轉(zhuǎn)換
• 隱式轉(zhuǎn)換會導(dǎo)致索引失效。如：select name,phone from customer where id = '111';
• 充分利用表上已經(jīng)存在的索引
• 避免使用雙%號的查詢條件
• 如a like '%123%'，（如果無前置%,只有后置%，是可以用到列上的索引的）。
• 一個(gè)SQL只能利用到復(fù)合索引中的一列進(jìn)行范圍查詢
• 如：有 a,b,c列的聯(lián)合索引，在查詢條件中有a列的范圍查詢，則在b,c列上的索引將不會被用到，在定義聯(lián)合索引時(shí)，如果a列要用到范圍查找的話，就要把a(bǔ)列放到聯(lián)合索引的右側(cè)。
• WHERE從句中禁止對列進(jìn)行函數(shù)轉(zhuǎn)換和計(jì)算
• 不推薦：where date(create_time)=20190101
• 推薦：where create_time >= 20190101 and create_time < 20190102
• 在明顯不會有重復(fù)值時(shí)使用UNION ALL而不是UNION
• UNION會把兩個(gè)結(jié)果集的所有數(shù)據(jù)放到臨時(shí)表中后再進(jìn)行去重和排序操作
• UNION ALL不會再對結(jié)果集進(jìn)行去重和排序操作
• 拆分復(fù)雜的大SQL為多個(gè)小SQL
• SQL 性能優(yōu)化的目標(biāo):至少要達(dá)到 range 級別，要求是 ref 級別，如果可以是 consts 最好。
• 不要使用 count(列名)或 count(常量)來替代 count()，count()就是 SQL92 定義 的標(biāo)準(zhǔn)統(tǒng)計(jì)行數(shù)的語法，跟數(shù)據(jù)庫無關(guān)，跟 NULL 和非 NULL 無關(guān)。