作者：白程序員的自習(xí)室
鏈接：https://www.studytime.xin/article/mysql-internal-specifications.html
最近涉及數(shù)據(jù)庫相關(guān)操作較多，公司現(xiàn)有規(guī)范也不是太全面，就根據(jù)網(wǎng)上各路大神的相關(guān)規(guī)范，整理了一些自用的規(guī)范用法，萬望指正。
開發(fā)可讀寫，可修改表結(jié)構(gòu)。開發(fā)人員可以修改表結(jié)構(gòu)，可以隨意修改其中的數(shù)據(jù)但是需要保證不影響其他開發(fā)同事。
開發(fā)可讀寫，開發(fā)人員可以通過工具修改表結(jié)構(gòu)。
開發(fā)人員不允許直接在線上環(huán)境進(jìn)行數(shù)據(jù)庫操作，如果需要操作必須找DBA進(jìn)行操作并進(jìn)行相應(yīng)記錄，禁止進(jìn)行壓力測試。
重點的問題，各個環(huán)境的mysql服務(wù)器對應(yīng)的用戶權(quán)限，一定要做到權(quán)限劃分明確，有辨識度，能具體區(qū)分業(yè)務(wù)場景等。
在 MySQL 中，數(shù)據(jù)庫和表對就于那些目錄下的目錄和文件。因而，操作系統(tǒng)的敏感性決定數(shù)據(jù)庫和表命名的大小寫敏感。
同一個模塊的表盡可能使用相同的前綴，表名稱盡可能表達(dá)含義。所有日志表均以 log_ 開頭
表引擎取決于實際應(yīng)用場景；日志及報表類表建議用myisam，與交易，審核，金額相關(guān)的表建議用innodb引擎。如無說明，建表時一律采用innodb引擎
默認(rèn)使用utf8mb4字符集，數(shù)據(jù)庫排序規(guī)則使用utf8mb4_general_ci，（由于數(shù)據(jù)庫定義使用了默認(rèn)，數(shù)據(jù)表可以不再定義，但為保險起見，建議都寫上
采用utf8編碼的MySQL無法保存占位是4個字節(jié)的Emoji表情。為了使后端的項目，全面支持客戶端輸入的Emoji表情，升級編碼為utf8mb4是最佳解決方案。對于JDBC連接串設(shè)置了characterEncoding為utf8或者做了上述配置仍舊無法正常插入emoji數(shù)據(jù)的情況，需要在代碼中指定連接的字符集為utf8mb4。
所有表、字段均應(yīng)用 comment 列屬性來描述此表、字段所代表的真正含義，如枚舉值則建議將該字段中使用的內(nèi)容都定義出來。
如無說明，表中的第一個id字段一定是主鍵且為自動增長，禁止在非事務(wù)內(nèi)作為上下文作為條件進(jìn)行數(shù)據(jù)傳遞。禁止使用varchar類型作為主鍵語句設(shè)計。
如無說明，表必須包含create_time和modify_time字段，即表必須包含記錄創(chuàng)建時間和修改時間的字段
如無說明，表必須包含is_del，用來標(biāo)示數(shù)據(jù)是否被刪除，原則上數(shù)據(jù)庫數(shù)據(jù)不允許物理刪除。
相同點：
TIMESTAMP列的顯示格式與DATETIME列相同。顯示寬度固定在19字符，并且格式為YYYY-MM-DD HH:MM:SS。
不同點：
TIMESTAMP
將當(dāng)前時間作為ts的默認(rèn)值：ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP。當(dāng)行更新時，更新ts的值：ts TIMESTAMP DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP。
可以將1和2結(jié)合起來：ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP。
使用INT UNSIGNED而不是char(15)來存儲ipv4地址，通過MySQL函數(shù)inet_ntoa和inet_aton來進(jìn)行轉(zhuǎn)化。Ipv6地址目前沒有轉(zhuǎn)化函數(shù)，需要使用DECIMAL或者兩個bigINT來存儲。
INT[M]，M值代表什么含義？
注意數(shù)值類型括號后面的數(shù)字只是表示寬度而跟存儲范圍沒有關(guān)系。很多人他們認(rèn)為INT(4)和INT(10)其取值范圍分別是 (-9999到9999)和(-9999999999到9999999999)，這種理解是錯誤的。其實對整型中的 M值與 ZEROFILL 屬性結(jié)合使用時可以實現(xiàn)列值等寬。不管INT[M]中M值是多少，其取值范圍還是 (-2147483648到2147483647 有符號時)，(0到4294967295無符號時)。
顯示寬度并不限制可以在列內(nèi)保存的值的范圍，也不限制超過列的指定寬度的值的顯示。當(dāng)結(jié)合可選擴展屬性ZEROFILL使用時默認(rèn)補充的空格用零代替。例如：對于聲明為INT(5) ZEROFILL的列，值4檢索為00004。請注意如果在整數(shù)列保存超過顯示寬度的一個值，當(dāng)MySQL為復(fù)雜聯(lián)接生成臨時表時會遇到問題，因為在這些情況下MySQL相信數(shù)據(jù)適合原列寬度，如果為一個數(shù)值列指定ZEROFILL, MySQL自動為該列添加UNSIGNED屬性。
什么時候用CHAR，什么時候用VARCHAR？
CHAR和VARCHAR類型類似，但它們保存和檢索的方式不同。它們的最大長度和是否尾部空格被保留等方面也不同。CHAR和VARCHAR類型聲明的長度表示你想要保存的最大字符數(shù)。例如，CHAR(30)可以占用30個字符。
CHAR列的長度固定為創(chuàng)建表時聲明的長度。長度可以為從0到255的任何值。當(dāng)保存CHAR值時，在它們的右邊填充空格以達(dá)到指定的長度。當(dāng)檢索到CHAR值時，尾部的空格被刪除掉。在存儲或檢索過程中不進(jìn)行大小寫轉(zhuǎn)換。
VARCHAR列中的值為可變長字符串。長度可以指定為0到65,535之間的值。(VARCHAR的最大有效長度由最大行大小和使用的字符集確定。整體最大長度是65,532字節(jié)）。同CHAR對比，VARCHAR值保存時只保存需要的字符數(shù)，另加一個字節(jié)來記錄長度(如果列聲明的長度超過255，則使用兩個字節(jié))。VARCHAR值保存時不進(jìn)行填充。當(dāng)值保存和檢索時尾部的空格仍保留，符合標(biāo)準(zhǔn)SQL。
char適合存儲用戶密碼的MD5哈希值，它的長度總是一樣的。對于經(jīng)常改變的值，char也好于varchar,因為固定長度的行不容易產(chǎn)生碎片，對于很短的列，char的效率也高于varchar。char(1)字符串對于單字節(jié)字符集只會占用一個字節(jié)，但是varchar(1)則會占用2個字節(jié)，因為1個字節(jié)用來存儲長度信息。
MySQL的查詢速度依賴良好的索引設(shè)計，因此索引對于高性能至關(guān)重要。合理的索引會加快查詢速度（包括UPDATE和DELETE的速度，MySQL會將包含該行的page加載到內(nèi)存中，然后進(jìn)行UPDATE或者DELETE操作），不合理的索引會降低速度。MySQL索引查找類似于新華字典的拼音和部首查找，當(dāng)拼音和部首索引不存在時，只能通過一頁一頁的翻頁來查找。當(dāng)MySQL查詢不能使用索引時，MySQL會進(jìn)行全表掃描，會消耗大量的IO。索引的用途：去重、加速定位、避免排序、覆蓋索引。
InnoDB存儲引擎中，secondary index（非主鍵索引）中沒有直接存儲行地址，存儲主鍵值。如果用戶需要查詢secondary index中所不包含的數(shù)據(jù)列時，需要先通過secondary index查找到主鍵值，然后再通過主鍵查詢到其他數(shù)據(jù)列，因此需要查詢兩次。覆蓋索引的概念就是查詢可以通過在一個索引中完成，覆蓋索引效率會比較高，主鍵查詢是天然的覆蓋索引。合理的創(chuàng)建索引以及合理的使用查詢語句，當(dāng)使用到覆蓋索引時可以獲得性能提升。比如SELECT email,uid FROM user_email WHERE uid=xx，如果uid不是主鍵，適當(dāng)時候可以將索引添加為index(uid,email)，以獲得性能提升。
InnoDB的secondary index使用b+tree來存儲，因此在UPDATE、DELETE、INSERT的時候需要對b+tree進(jìn)行調(diào)整，過多的索引會減慢更新的速度。
對字符串使用前綴索引，前綴索引長度不超過8個字符，建議優(yōu)先考慮前綴索引，必要時可添加偽列并建立索引。
不要索引blob/text等字段,不要索引大型字段,這樣做會讓索引占用太多的存儲空間
前綴索引說白了就是對文本的前幾個字符（具體是幾個字符在建立索引時指定）建立索引，這樣建立起來的索引更小，所以查詢更快。前綴索引能有效減小索引文件的大小，提高索引的速度。但是前綴索引也有它的壞處：MySQL 不能在 ORDER BY 或 GROUP BY 中使用前綴索引，也不能把它們用作覆蓋索引(Covering Index)。
建立前綴索引的語法：ALTER TABLE table_name ADD KEY(column_name(prefix_length));
限制分頁展示的頁數(shù)只能點擊上一頁、下一頁采用延遲關(guān)聯(lián)
假如有類似下面分頁語句：SELECT * FROM table ORDER BY id LIMIT 10000, 10由于MySQL里對LIMIT OFFSET的處理方式是取出OFFSET+LIMIT的所有數(shù)據(jù)，然后去掉OFFSET，返回底部的LIMIT。所以，在OFFSET數(shù)值較大時，MySQL的查詢性能會非常低。可以使用id > n 的方式進(jìn)行解決：
使用id > n 的方式有局限性，對于id不連續(xù)的問題，可以通過翻頁的時候同時傳入最后一個id方式來解決。
http://example.com/page.php?last=100
select * from table where id<100 order by id desc limit 10
//上一頁
http://example.com/page.php?first=110
select * from table where id>110 order by id desc limit 10這種方式比較大的缺點是，如果在瀏覽中有插入/刪除操作，翻頁不會更新，而總頁數(shù)可能仍然是根據(jù)新的count(*) 來計算，最終可能會產(chǎn)生某些記錄訪問不到。為了修補這個問題，可以繼續(xù)引入當(dāng)前頁碼以及在上次翻頁以后是否有插入/刪除等影響總記錄數(shù)的操作并進(jìn)行緩存
select * from table where id >= (select id from table order by id limit #offset#, 1)EXPLAIN語句（在MySQL客戶端中執(zhí)行）可以獲得MySQL如何執(zhí)行SELECT語句的信息。通過對SELECT語句執(zhí)行EXPLAIN，可以知曉MySQL執(zhí)行該SELECT語句時是否使用了索引、全表掃描、臨時表、排序等信息。盡量避免MySQL進(jìn)行全表掃描、使用臨時表、排序等。詳見官方文檔。
union all與 union有什么區(qū)別？
union和union all關(guān)鍵字都是將兩個結(jié)果集合并為一個，但這兩者從使用和效率上來說都有所不同。
union在進(jìn)行表鏈接后會篩選掉重復(fù)的記錄，所以在表鏈接后會對所產(chǎn)生的結(jié)果集進(jìn)行排序運算，刪除重復(fù)的記錄再返回結(jié)果。如：
select * from test_union1
union select * from test_union2這個SQL在運行時先取出兩個表的結(jié)果，再用排序空間進(jìn)行排序刪除重復(fù)的記錄，最后返回結(jié)果集，如果表數(shù)據(jù)量大的話可能會導(dǎo)致用磁盤進(jìn)行排序。
而union all只是簡單的將兩個結(jié)果合并后就返回。這樣，如果返回的兩個結(jié)果集中有重復(fù)的數(shù)據(jù)，那么返回的結(jié)果集就會包含重復(fù)的數(shù)據(jù)了。
從效率上說，union all要比union快很多，所以，如果可以確認(rèn)合并的兩個結(jié)果集中不包含重復(fù)的數(shù)據(jù)的話，那么就使用union all，如下：
select * from test_union1 union all select * from test_union2單表一到兩年內(nèi)數(shù)據(jù)量超過500w或數(shù)據(jù)容量超過10G考慮分表，需提前考慮歷史數(shù)據(jù)遷移或應(yīng)用自行刪除歷史數(shù)據(jù)，采用等量均衡分表或根據(jù)業(yè)務(wù)規(guī)則分表均可。要分表的數(shù)據(jù)表必須與DBA商量分表策略
日志類數(shù)據(jù)不建議存儲在MySQL上，優(yōu)先考慮Hbase或OceanBase，如需要存儲請找DBA評估使用壓縮表存儲。
●輸入m獲取到文章目錄
聯(lián)系客服
