MySQL這一塊的知識(shí)還是挺多的，問深度的話， 一般都是如何調(diào)優(yōu)的，當(dāng)然少不了MySQL的基礎(chǔ)等知識(shí)。
數(shù)據(jù)庫引擎innodb與myisam的區(qū)別
InnoDB
是 MySQL 默認(rèn)的「事務(wù)型」存儲(chǔ)引擎，只有在需要它不支持的特性時(shí)，才考慮使用其它存儲(chǔ)引擎。
實(shí)現(xiàn)了四個(gè)標(biāo)準(zhǔn)的隔離級別，默認(rèn)級別是「可重復(fù)讀」(REPEATABLE READ)。在可重復(fù)讀隔離級別下，「通過多版本并發(fā)控制(MVCC)+ 間隙鎖(Next-Key Locking)防止幻影讀?！?/strong>
主索引是「聚簇索引」，在索引中保存了數(shù)據(jù)，從而避免直接讀取磁盤，因此對查詢性能有很大的提升。
內(nèi)部做了很多優(yōu)化，包括從磁盤讀取數(shù)據(jù)時(shí)采用的「可預(yù)測性讀」、能夠加快讀操作并且自動(dòng)創(chuàng)建的「自適應(yīng)哈希索引」、能夠加速插入操作的插入緩沖區(qū)等。
支持真正的「在線熱備份」。其它存儲(chǔ)引擎不支持在線熱備份，要獲取一致性視圖需要停止對所有表的寫入，而在讀寫混合場景中，停止寫入可能也意味著停止讀取。
MyISAM
設(shè)計(jì)簡單，數(shù)據(jù)以「緊密格式存儲(chǔ)」。對于只讀數(shù)據(jù)，或者表比較小、可以容忍修復(fù)操作，則依然可以使用它。
提供了大量的特性，包括「壓縮表」、「空間數(shù)據(jù)索引」等。
「不支持事務(wù)」。
「不支持行級鎖」，只能對整張表加鎖，讀取時(shí)會(huì)對需要讀到的所有表加共享鎖，寫入時(shí)則對表加排它鎖。但在表有讀取操作的同時(shí)，也可以往表中插入新的記錄，這被稱為并發(fā)插入(CONCURRENT INSERT)。
可以手工或者自動(dòng)執(zhí)行檢查和修復(fù)操作，但是和事務(wù)恢復(fù)以及崩潰恢復(fù)不同，可能導(dǎo)致一些數(shù)據(jù)丟失，而且修復(fù)操作是非常慢的。
如果指定了 DELAY_KEY_WRITE 選項(xiàng)，在每次修改執(zhí)行完成時(shí)，不會(huì)立即將修改的索引數(shù)據(jù)寫入磁盤，而是會(huì)寫到內(nèi)存中的鍵緩沖區(qū)，只有在清理鍵緩沖區(qū)或者關(guān)閉表的時(shí)候才會(huì)將對應(yīng)的索引塊寫入磁盤。這種方式可以極大的提升寫入性能，但是在數(shù)據(jù)庫或者主機(jī)崩潰時(shí)會(huì)造成索引損壞，需要執(zhí)行修復(fù)操作。
比較
- 「事務(wù)」: InnoDB 是事務(wù)型的，可以使用 Commit 和 Rollback 語句。
- 「并發(fā)」: MyISAM 只支持表級鎖，而 InnoDB 還支持行級鎖。
- 「外鍵」: InnoDB 支持外鍵。
- 「備份」: InnoDB 支持在線熱備份。
- 「崩潰恢復(fù)」: MyISAM 崩潰后發(fā)生損壞的概率比 InnoDB 高很多，而且恢復(fù)的速度也更慢。
- 「其它特性」: MyISAM 支持壓縮表和空間數(shù)據(jù)索引。
MySQL是如何執(zhí)行一條SQL的
「MySQL內(nèi)部可以分為服務(wù)層和存儲(chǔ)引擎層兩部分：」
- 「服務(wù)層包括連接器、查詢緩存、分析器、優(yōu)化器、執(zhí)行器等」，涵蓋MySQL的大多數(shù)核心服務(wù)功能，以及所有的內(nèi)置函數(shù)（如日期、時(shí)間、數(shù)學(xué)和加密函數(shù)等），所有跨存儲(chǔ)引擎的功能都在這一層實(shí)現(xiàn)，比如存儲(chǔ)過程、觸發(fā)器、視圖等。
- 存儲(chǔ)引擎層負(fù)責(zé)數(shù)據(jù)的存儲(chǔ)和提取。其架構(gòu)模式是插件式的，支持InnoDB、MyISAM、Memory等多個(gè)存儲(chǔ)引擎?，F(xiàn)在最常用的存儲(chǔ)引擎是InnoDB，它從MySQL 5.5.5版本開始成為了默認(rèn)的存儲(chǔ)引擎。
「Server層按順序執(zhí)行sql的步驟為：」
客戶端請求->連接器（驗(yàn)證用戶身份，給予權(quán)限） -> 查詢緩存（存在緩存則直接返回，不存在則執(zhí)行后續(xù)操作）->分析器（對SQL進(jìn)行詞法分析和語法分析操作） -> 優(yōu)化器（主要對執(zhí)行的sql優(yōu)化選擇最優(yōu)的執(zhí)行方案方法） -> 執(zhí)行器（執(zhí)行時(shí)會(huì)先看用戶是否有執(zhí)行權(quán)限，有才去使用這個(gè)引擎提供的接口）->去引擎層獲取數(shù)據(jù)返回（如果開啟查詢緩存則會(huì)緩存查詢結(jié)果）
「簡單概括」：
- 「連接器」：管理連接、權(quán)限驗(yàn)證；
- 「查詢緩存」：命中緩存則直接返回結(jié)果；
- 「分析器」：對SQL進(jìn)行詞法分析、語法分析；（判斷查詢的SQL字段是否存在也是在這步）
- 「優(yōu)化器」：執(zhí)行計(jì)劃生成、選擇索引；
- 「執(zhí)行器」：操作引擎、返回結(jié)果；
- 「存儲(chǔ)引擎」：存儲(chǔ)數(shù)據(jù)、提供讀寫接口。
mysql的acid原理
「ACID嘛，原子性(Atomicity)、一致性(Consistency)、隔離性(Isolation)、持久性(Durability)！」
我們以從A賬戶轉(zhuǎn)賬50元到B賬戶為例進(jìn)行說明一下ACID，四大特性。
原子性
根據(jù)定義，原子性是指一個(gè)事務(wù)是一個(gè)不可分割的工作單位，其中的操作要么都做，要么都不做。即要么轉(zhuǎn)賬成功，要么轉(zhuǎn)賬失敗，是不存在中間的狀態(tài)！
「如果無法保證原子性會(huì)怎么樣？」
OK，就會(huì)出現(xiàn)「數(shù)據(jù)不一致」的情形，A賬戶減去50元，而B賬戶增加50元操作失敗。系統(tǒng)將無故丟失50元~
隔離性
根據(jù)定義，隔離性是指多個(gè)事務(wù)并發(fā)執(zhí)行的時(shí)候，「事務(wù)內(nèi)部的操作與其他事務(wù)是隔離的」，并發(fā)執(zhí)行的各個(gè)事務(wù)之間不能互相干擾。
「如果無法保證隔離性會(huì)怎么樣？」
OK，假設(shè)A賬戶有200元，B賬戶0元。A賬戶往B賬戶轉(zhuǎn)賬兩次，金額為50元，分別在兩個(gè)事務(wù)中執(zhí)行。如果無法保證隔離性，會(huì)出現(xiàn)下面的情形：
如圖所示，如果不保證隔離性，A扣款兩次，而B只加款一次，憑空消失了50元，依然出現(xiàn)了「數(shù)據(jù)不一致」的情形！
持久性
根據(jù)定義，「持久性是指事務(wù)一旦提交，它對數(shù)據(jù)庫的改變就應(yīng)該是永久性的」。接下來的其他操作或故障不應(yīng)該對其有任何影響。
「如果無法保證持久性會(huì)怎么樣？」
在MySQL中，為了解決CPU和磁盤速度不一致問題，MySQL是將磁盤上的數(shù)據(jù)加載到內(nèi)存，對內(nèi)存進(jìn)行操作，然后再回寫磁盤。好，假設(shè)此時(shí)宕機(jī)了，在內(nèi)存中修改的數(shù)據(jù)全部丟失了，持久性就無法保證。
設(shè)想一下，系統(tǒng)提示你轉(zhuǎn)賬成功。但是你發(fā)現(xiàn)金額沒有發(fā)生任何改變，此時(shí)數(shù)據(jù)出現(xiàn)了不合法的數(shù)據(jù)狀態(tài)，我們將這種狀態(tài)認(rèn)為是「數(shù)據(jù)不一致」的情形。
一致性
根據(jù)定義，一致性是指事務(wù)執(zhí)行前后，數(shù)據(jù)處于一種合法的狀態(tài)，這種狀態(tài)是語義上的而不是語法上的。 那什么是合法的數(shù)據(jù)狀態(tài)呢？ oK，這個(gè)狀態(tài)是滿足預(yù)定的約束就叫做合法的狀態(tài)，再通俗一點(diǎn)，這狀態(tài)是由你自己來定義的。「滿足這個(gè)狀態(tài)，數(shù)據(jù)就是一致的，不滿足這個(gè)狀態(tài)，數(shù)據(jù)就是不一致的」！
「如果無法保證一致性會(huì)怎么樣？」
- 例一:A賬戶有200元，轉(zhuǎn)賬300元出去，此時(shí)A賬戶余額為-100元。你自然就發(fā)現(xiàn)了此時(shí)數(shù)據(jù)是不一致的，為什么呢？因?yàn)槟愣x了一個(gè)狀態(tài)，余額這列必須大于0。
- 例二:A賬戶200元，轉(zhuǎn)賬50元給B賬戶，A賬戶的錢扣了，但是B賬戶因?yàn)楦鞣N意外，余額并沒有增加。你也知道此時(shí)數(shù)據(jù)是不一致的，為什么呢？因?yàn)槟愣x了一個(gè)狀態(tài)，要求A+B的余額必須不變。
mysql怎么保證一致性？
OK，這個(gè)問題分為兩個(gè)層面來說。 「從數(shù)據(jù)庫層面」，數(shù)據(jù)庫通過原子性、隔離性、持久性來保證一致性。也就是說ACID四大特性之中，C(一致性)是目的，A(原子性)、I(隔離性)、D(持久性)是手段，是為了保證一致性，數(shù)據(jù)庫提供的手段。數(shù)據(jù)庫必須要實(shí)現(xiàn)AID三大特性，才有可能實(shí)現(xiàn)一致性。例如，原子性無法保證，顯然一致性也無法保證。
但是，如果你在事務(wù)里故意寫出違反約束的代碼，一致性還是無法保證的。例如，你在轉(zhuǎn)賬的例子中，你的代碼里故意不給B賬戶加錢，那一致性還是無法保證。因此，還必須從應(yīng)用層角度考慮。
「從應(yīng)用層面」，通過代碼判斷數(shù)據(jù)庫數(shù)據(jù)是否有效，然后決定回滾還是提交數(shù)據(jù)！
mysql怎么保證原子性
OK，是利用Innodb的undo log。 undo log名為回滾日志，是實(shí)現(xiàn)原子性的關(guān)鍵，當(dāng)事務(wù)回滾時(shí)能夠「撤銷所有已經(jīng)成功執(zhí)行的sql語句」，他需要記錄你要回滾的相應(yīng)日志信息。 例如
- (1)當(dāng)你delete一條數(shù)據(jù)的時(shí)候，就需要記錄這條數(shù)據(jù)的信息，回滾的時(shí)候，insert這條舊數(shù)據(jù)
- (2)當(dāng)你update一條數(shù)據(jù)的時(shí)候，就需要記錄之前的舊值，回滾的時(shí)候，根據(jù)舊值執(zhí)行update操作
- (3)當(dāng)年insert一條數(shù)據(jù)的時(shí)候，就需要這條記錄的主鍵，回滾的時(shí)候，根據(jù)主鍵執(zhí)行delete操作
undo log記錄了這些回滾需要的信息，當(dāng)事務(wù)執(zhí)行失敗或調(diào)用了rollback，導(dǎo)致事務(wù)需要回滾，便可以利用undo log中的信息將數(shù)據(jù)回滾到修改之前的樣子。
mysql怎么保證持久性的
OK，是利用Innodb的redo log。 正如之前說的，Mysql是先把磁盤上的數(shù)據(jù)加載到內(nèi)存中，在內(nèi)存中對數(shù)據(jù)進(jìn)行修改，再刷回磁盤上。如果此時(shí)突然宕機(jī)，內(nèi)存中的數(shù)據(jù)就會(huì)丟失。 怎么解決這個(gè)問題？ 簡單啊，事務(wù)提交前直接把數(shù)據(jù)寫入磁盤就行啊。 這么做有什么問題？
- 只修改一個(gè)頁面里的一個(gè)字節(jié)，就要將整個(gè)頁面刷入磁盤，太浪費(fèi)資源了。畢竟一個(gè)頁面16kb大小，你只改其中一點(diǎn)點(diǎn)東西，就要將16kb的內(nèi)容刷入磁盤，聽著也不合理。
- 畢竟一個(gè)事務(wù)里的SQL可能牽涉到多個(gè)數(shù)據(jù)頁的修改，而這些數(shù)據(jù)頁可能不是相鄰的，也就是屬于隨機(jī)IO。顯然操作隨機(jī)IO，速度會(huì)比較慢。
于是，決定采用redo log解決上面的問題。當(dāng)做數(shù)據(jù)修改的時(shí)候，不僅在內(nèi)存中操作，還會(huì)在redo log中記錄這次操作。當(dāng)事務(wù)提交的時(shí)候，會(huì)將redo log日志進(jìn)行刷盤(redo log一部分在內(nèi)存中，一部分在磁盤上)。當(dāng)數(shù)據(jù)庫宕機(jī)重啟的時(shí)候，會(huì)將redo log中的內(nèi)容恢復(fù)到數(shù)據(jù)庫中，再根據(jù)undo log和binlog內(nèi)容決定回滾數(shù)據(jù)還是提交數(shù)據(jù)。
「采用redo log的好處？」
其實(shí)好處就是將redo log進(jìn)行刷盤比對數(shù)據(jù)頁刷盤效率高，具體表現(xiàn)如下
-
redo log體積小，畢竟只記錄了哪一頁修改了啥，因此體積小，刷盤快。 -
redo log是一直往末尾進(jìn)行追加，屬于順序IO。效率顯然比隨機(jī)IO來的快。
mysql怎么保證隔離性
利用的是鎖和MVCC機(jī)制。
并發(fā)事務(wù)帶來的問題
臟讀
丟棄修改
T1 和 T2 兩個(gè)事務(wù)都對一個(gè)數(shù)據(jù)進(jìn)行修改，「T1 先修改，T2 隨后修改，T2 的修改覆蓋了 T1 的修改」。例如：事務(wù)1讀取某表中的數(shù)據(jù)A=20，事務(wù)2也讀取A=20，事務(wù)1修改A=A-1，事務(wù)2也修改A=A-1，最終結(jié)果A=19，事務(wù)1的修改被丟失。
不可重復(fù)讀
「T2 讀取一個(gè)數(shù)據(jù)，T1 對該數(shù)據(jù)做了修改。如果 T2 再次讀取這個(gè)數(shù)據(jù)，此時(shí)讀取的結(jié)果和第一次讀取的結(jié)果不同」。
幻讀
「T1 讀取某個(gè)范圍的數(shù)據(jù)，T2 在這個(gè)范圍內(nèi)插入新的數(shù)據(jù)，T1 再次讀取這個(gè)范圍的數(shù)據(jù)，此時(shí)讀取的結(jié)果和和第一次讀取的結(jié)果不同」。
不可重復(fù)讀和幻讀區(qū)別
「不可重復(fù)讀的重點(diǎn)是修改，幻讀的重點(diǎn)在于新增或者刪除」。
例1（同樣的條件, 你讀取過的數(shù)據(jù), 再次讀取出來發(fā)現(xiàn)值不一樣了 ）：事務(wù)1中的A先生讀取自己的工資為 1000的操 作還沒完成，事務(wù)2中的B先生就修改了A的工資為2000，導(dǎo) 致A再讀自己的工資時(shí)工資變?yōu)?2000；這就是不可重復(fù)讀。
例2（同樣的條件, 第1次和第2次讀出來的記錄數(shù)不一樣 ）：假某工資單表中工資大于3000的有4人，事務(wù)1讀取了所 有工資大于3000的人，共查到4條記錄，這時(shí)事務(wù)2 又插入了一條工資大于3000的記錄，事務(wù)1再次讀取時(shí)查到的記 錄就變?yōu)榱?條，這樣就導(dǎo)致了幻讀。
數(shù)據(jù)庫的隔離級別
- 未提交讀，事務(wù)中發(fā)生了修改，即使沒有提交，其他事務(wù)也是可見的，比如對于一個(gè)數(shù)A原來50修改為100，但是我還沒有提交修改，另一個(gè)事務(wù)看到這個(gè)修改，而這個(gè)時(shí)候原事務(wù)發(fā)生了回滾，這時(shí)候A還是50，但是另一個(gè)事務(wù)看到的A是100.「可能會(huì)導(dǎo)致臟讀、幻讀或不可重復(fù)讀」
- 提交讀，對于一個(gè)事務(wù)從開始直到提交之前，所做的任何修改是其他事務(wù)不可見的，舉例就是對于一個(gè)數(shù)A原來是50，然后提交修改成100，這個(gè)時(shí)候另一個(gè)事務(wù)在A提交修改之前，讀取的A是50，剛讀取完，A就被修改成100，這個(gè)時(shí)候另一個(gè)事務(wù)再進(jìn)行讀取發(fā)現(xiàn)A就突然變成100了；「可以阻止臟讀，但是幻讀或不可重復(fù)讀仍有可能發(fā)生」
- 可重復(fù)讀，就是對一個(gè)記錄讀取多次的記錄是相同的，比如對于一個(gè)數(shù)A讀取的話一直是A，前后兩次讀取的A是一致的；「可以阻止臟讀和不可重復(fù)讀，但幻讀仍有可能發(fā)生?！?/strong>
- 可串行化讀，在并發(fā)情況下，和串行化的讀取的結(jié)果是一致的，沒有什么不同，比如不會(huì)發(fā)生臟讀和幻讀；「該級別可以防止臟讀、不可重復(fù)讀以及幻讀?！?/strong>
| 隔離級別 | 臟讀 | 不可重復(fù)讀 | 幻影讀 |
|---|---|---|---|
| READ-UNCOMMITTED | √ | √ | √ |
| READ-COMMITTED | × | √ | √ |
| REPEATABLE-READ | × | × | √ |
| SERIALIZABLE | × | × | × |
MySQL InnoDB 存儲(chǔ)引擎的默認(rèn)支持的隔離級別是 REPEATABLE-READ（可重讀）。
「這里需要注意的是」：與 SQL 標(biāo)準(zhǔn)不同的地方在于InnoDB 存儲(chǔ)引擎在 REPEATABLE-READ（可重讀）事務(wù)隔離級別 下使用的是「Next-Key Lock 鎖」算法，因此可以避免幻讀的產(chǎn)生，這與其他數(shù)據(jù)庫系統(tǒng)(如 SQL Server)是不同的。所以 說InnoDB 存儲(chǔ)引擎的默認(rèn)支持的隔離級別是 REPEATABLE-READ（可重讀） 已經(jīng)可以完全保證事務(wù)的隔離性要 求，即達(dá)到了 SQL標(biāo)準(zhǔn)的SERIALIZABLE(可串行化)隔離級別。
因?yàn)楦綦x級別越低，事務(wù)請求的鎖越少，所以大部分?jǐn)?shù)據(jù)庫系統(tǒng)的隔離級別都是READ-COMMITTED(讀取提交內(nèi) 容):，但是你要知道的是InnoDB 存儲(chǔ)引擎默認(rèn)使用 「REPEATABLE-READ（可重讀）并不會(huì)有任何性能損失」。
InnoDB 存儲(chǔ)引擎在分布式事務(wù) 的情況下一般會(huì)用到SERIALIZABLE(可串行化)隔離級別。
為什么使用索引
- 通過創(chuàng)建「唯一性索引」，可以保證數(shù)據(jù)庫表中每一行數(shù)據(jù)的唯一性。
- 可以大大「加快數(shù)據(jù)的檢索速度」，這也是創(chuàng)建索引的最主要的原因。
- 幫助服務(wù)器「避免排序和臨時(shí)表」
- 將「隨機(jī)IO變?yōu)轫樞騃O」。
- 可以加速「表和表之間的連接」，特別是在實(shí)現(xiàn)數(shù)據(jù)的參考完整性方面特別有意義。
索引這么多優(yōu)點(diǎn)，為什么不對表總的每一列創(chuàng)建一個(gè)索引
- 當(dāng)對表中的數(shù)據(jù)進(jìn)行增加、刪除和修改的時(shí)候，「索引也要?jiǎng)討B(tài)的維護(hù)」，這樣就降低了數(shù)據(jù)的維護(hù)速度。
- 「索引需要占物理空間」，除了數(shù)據(jù)表占數(shù)據(jù)空間之外，每一個(gè)索引還要占一定的物理空間，如果要建立簇索引，那么需要的空間就會(huì)更大。
- 「創(chuàng)建索引和維護(hù)索引要耗費(fèi)時(shí)間」，這種時(shí)間隨著數(shù)據(jù)量的增加而增加
索引如何提高查詢速度的
將無序的數(shù)據(jù)變成相對有序的數(shù)據(jù)（就像查有目的一樣）
使用索引的注意事項(xiàng)
- 在經(jīng)常需要搜索的列上，可以加快搜索的速度；
- 在經(jīng)常使用在where子句中的列上面創(chuàng)建索引，加快條件的判斷速度。
- 在經(jīng)常需要排序的列上創(chuàng)建索引，因?yàn)樗饕呀?jīng)排序，這樣查詢可以利用索引的排序，加快排序查詢時(shí)間
- 在中到大型表索引都是非常有效的，但是特大型表的維護(hù)開銷會(huì)很大，不適合建索引
- 在經(jīng)常用到連續(xù)的列上，這些列主要是由一些外鍵，可以加快連接的速度
- 避免where子句中對字段施加函數(shù)，這會(huì)造成無法命中索引
- 在使用InnoDB時(shí)使用與業(yè)務(wù)無關(guān)的自增主鍵作為主鍵，即使用邏輯主鍵，而不要使用業(yè)務(wù)主鍵。
- 「將打算加索引的列設(shè)置為NOT NULL，否則將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描」
- 刪除長期未使用的索引，不用的索引的存在會(huì)造成不必要的性能損耗
- 在使用limit offset查詢緩存時(shí)，可以借助索引來提高性能。
MySQL索引主要使用的兩種數(shù)據(jù)結(jié)構(gòu)
- 「哈希索引」，對于哈希索引來說，底層的數(shù)據(jù)結(jié)構(gòu)肯定是哈希表，因此在絕大多數(shù)需求為單條記錄查詢的時(shí)候，可以選擇哈希索引，查詢性能最快；其余大部分場景，建議選擇BTree索引
- 「BTree索引」，Mysql的BTree索引使用的是B樹中的B+Tree但對于主要的兩種存儲(chǔ)引擎（MyISAM和InnoDB）的實(shí)現(xiàn)方式是不同的。
myisam和innodb實(shí)現(xiàn)btree索引方式的區(qū)別
- MyISAM，「B+Tree葉節(jié)點(diǎn)的data域存放的是數(shù)據(jù)記錄的地址」，在索引檢索的時(shí)候，首先按照B+Tree搜索算法搜索索引，如果指定的key存在，則取出其data域的值，然后以data域的值為地址讀區(qū)相應(yīng)的數(shù)據(jù)記錄，這被稱為“非聚簇索引”
- InnoDB，其數(shù)據(jù)文件本身就是索引文件，相比MyISAM，「索引文件和數(shù)據(jù)文件是分離的」，「其表數(shù)據(jù)文件本身就是按B+Tree組織的一個(gè)索引結(jié)構(gòu)，樹的節(jié)點(diǎn)data域保存了完整的數(shù)據(jù)記錄」，這個(gè)索引的key是數(shù)據(jù)表的主鍵，因此InnoDB表數(shù)據(jù)文件本身就是主索引。這被稱為“聚簇索引”或者聚集索引，而其余的索引都作為輔助索引，輔助索引的data域存儲(chǔ)相應(yīng)記錄主鍵的值而不是地址，這也是和MyISAM不同的地方，在根據(jù)主索引搜索時(shí)，直接找到key所在的節(jié)點(diǎn)即可取出數(shù)據(jù)；在根據(jù)輔助索引查找時(shí)，則需要先取出主鍵的值，在走一遍主索引。因此，在設(shè)計(jì)表的時(shí)候，不建議使用過長的字段為主鍵，也不建議使用非單調(diào)的字段作為主鍵，這樣會(huì)造成主索引頻繁分裂。
數(shù)據(jù)庫結(jié)構(gòu)優(yōu)化
- 「范式優(yōu)化」： 比如消除冗余（節(jié)省空間。。）
- 「反范式優(yōu)化」：比如適當(dāng)加冗余等（減少join）
- 「限定數(shù)據(jù)的范圍」： 務(wù)必禁止不帶任何限制數(shù)據(jù)范圍條件的查詢語句。比如：我們當(dāng)用戶在查詢訂單歷史的時(shí)候，我們可以控制在一個(gè)月的范圍內(nèi)。
- 「讀/寫分離」： 經(jīng)典的數(shù)據(jù)庫拆分方案，主庫負(fù)責(zé)寫，從庫負(fù)責(zé)讀；
- 「拆分表」：分區(qū)將數(shù)據(jù)在物理上分隔開，不同分區(qū)的數(shù)據(jù)可以制定保存在處于不同磁盤上的數(shù)據(jù)文件里。這樣，當(dāng)對這個(gè)表進(jìn)行查詢時(shí)，只需要在表分區(qū)中進(jìn)行掃描，而不必進(jìn)行全表掃描，明顯縮短了查詢時(shí)間，另外處于不同磁盤的分區(qū)也將對這個(gè)表的數(shù)據(jù)傳輸分散在不同的磁盤I/O，一個(gè)精心設(shè)置的分區(qū)可以將數(shù)據(jù)傳輸對磁盤I/O競爭均勻地分散開。對數(shù)據(jù)量大的時(shí)時(shí)表可采取此方法。可按月自動(dòng)建表分區(qū)。
「拆分其實(shí)又分垂直拆分和水平拆分：」
案例： 簡單購物系統(tǒng)暫設(shè)涉及如下表：
1.產(chǎn)品表（數(shù)據(jù)量10w，穩(wěn)定）
2.訂單表（數(shù)據(jù)量200w，且有增長趨勢）
3.用戶表 （數(shù)據(jù)量100w，且有增長趨勢）
以mysql為例講述下水平拆分和垂直拆分，mysql能容忍的數(shù)量級在百萬靜態(tài)數(shù)據(jù)可以到千萬
-
「垂直拆分：」
- 解決問題：表與表之間的io競爭
- 不解決問題：單表中數(shù)據(jù)量增長出現(xiàn)的壓力
- 方案： 把產(chǎn)品表和用戶表放到一個(gè)server上 訂單表單獨(dú)放到一個(gè)server上
-
「水平拆分：」
- 解決問題：單表中數(shù)據(jù)量增長出現(xiàn)的壓力
- 不解決問題：表與表之間的io爭奪
方案：「用戶表」 通過性別拆分為男用戶表和女用戶表，「訂單表」 通過已完成和完成中拆分為已完成訂單和未完成訂單，「產(chǎn)品表」 未完成訂單放一個(gè)server上，已完成訂單表盒男用戶表放一個(gè)server上，女用戶表放一個(gè)server上(女的愛購物 哈哈)。
主鍵超鍵候選鍵外鍵是什么
「超鍵」：在關(guān)系中能唯一標(biāo)識(shí)「元組的屬性集」稱為關(guān)系模式的超鍵
「候選鍵」：不含有「多余屬性的超鍵」稱為候選鍵。也就是在候選鍵中，若再刪除屬性，就不是鍵了！
「主鍵」：「用戶選作元組標(biāo)識(shí)的一個(gè)候選鍵程序主鍵」
「外鍵」：如果關(guān)系模式「R中屬性K是其它模式的主鍵」，那么「k在模式R中稱為外鍵」。
「舉例」：
| 學(xué)號(hào) | 姓名 | 性別 | 年齡 | 系別 | 專業(yè) |
|---|---|---|---|---|---|
| 20020612 | 李輝 | 男 | 20 | 計(jì)算機(jī) | 軟件開發(fā) |
| 20060613 | 張明 | 男 | 18 | 計(jì)算機(jī) | 軟件開發(fā) |
| 20060614 | 王小玉 | 女 | 19 | 物理 | 力學(xué) |
| 20060615 | 李淑華 | 女 | 17 | 生物 | 動(dòng)物學(xué) |
| 20060616 | 趙靜 | 男 | 21 | 化學(xué) | 食品化學(xué) |
| 20060617 | 趙靜 | 女 | 20 | 生物 | 植物學(xué) |
- 超鍵：于是我們從例子中可以發(fā)現(xiàn) 學(xué)號(hào)是標(biāo)識(shí)學(xué)生實(shí)體的唯一標(biāo)識(shí)。那么該元組的超鍵就為學(xué)號(hào)。除此之外我們還可以把它跟其他屬性組合起來，比如：(
學(xué)號(hào)，性別)，(學(xué)號(hào)，年齡) - 候選鍵：根據(jù)例子可知，學(xué)號(hào)是一個(gè)可以唯一標(biāo)識(shí)元組的唯一標(biāo)識(shí)，因此學(xué)號(hào)是一個(gè)候選鍵，實(shí)際上，候選鍵是超鍵的子集，比如 （學(xué)號(hào)，年齡）是超鍵，但是它不是候選鍵。因?yàn)樗€有了額外的屬性。
- 主鍵：簡單的說，例子中的元組的候選鍵為學(xué)號(hào)，但是我們選定他作為該元組的唯一標(biāo)識(shí)，那么學(xué)號(hào)就為主鍵。
- 外鍵是相對于主鍵的，比如在學(xué)生記錄里，主鍵為學(xué)號(hào)，在成績單表中也有學(xué)號(hào)字段，因此學(xué)號(hào)為成績單表的外鍵，為學(xué)生表的主鍵。
「主鍵為候選鍵的子集，候選鍵為超鍵的子集，而外鍵的確定是相對于主鍵的?！?/strong>
drop,delete與truncate的區(qū)別
- drop直接刪掉表;
- truncate刪除表中數(shù)據(jù)，再插入時(shí)自增長id又從1開始 ;
- delete刪除表中數(shù)據(jù)，可以加where字句。
- DELETE語句執(zhí)行刪除的過程是每次從表中刪除一行，并且同時(shí)將該行的刪除操作作為事務(wù)記錄在日志中保存以便進(jìn)行進(jìn)行回滾操作。TRUNCATE TABLE 則一次性地從表中刪除所有的數(shù)據(jù)并不把單獨(dú)的刪除操作記錄記入日志保存，刪除行是不能恢復(fù)的。并且在刪除的過程中不會(huì)激活與表有關(guān)的刪除觸發(fā)器。執(zhí)行速度快。
- 表和索引所占空間。當(dāng)表被TRUNCATE 后，這個(gè)表和索引所占用的空間會(huì)恢復(fù)到初始大小，而DELETE操作不會(huì)減少表或索引所占用的空間。drop語句將表所占用的空間全釋放掉。
- 一般而言，drop > truncate > delete
- 應(yīng)用范圍。TRUNCATE 只能對TABLE；DELETE可以是table和view
- TRUNCATE 和DELETE只刪除數(shù)據(jù)，而DROP則刪除整個(gè)表（結(jié)構(gòu)和數(shù)據(jù)）。
- truncate與不帶where的delete ：只刪除數(shù)據(jù)，而不刪除表的結(jié)構(gòu)（定義）drop語句將刪除表的結(jié)構(gòu)被依賴的約束（constrain),觸發(fā)器（trigger)索引（index);依賴于該表的存儲(chǔ)過程/函數(shù)將被保留，但其狀態(tài)會(huì)變?yōu)椋篿nvalid。
- delete語句為DML（Data Manipulation Language),這個(gè)操作會(huì)被放到 rollback segment中,事務(wù)提交后才生效。如果有相應(yīng)的 tigger,執(zhí)行的時(shí)候?qū)⒈挥|發(fā)。
- truncate、drop是DDL（Data Define Language),操作立即生效，原數(shù)據(jù)不放到 rollback segment中，不能回滾
- 在沒有備份情況下，謹(jǐn)慎使用 drop 與 truncate。要?jiǎng)h除部分?jǐn)?shù)據(jù)行采用delete且注意結(jié)合where來約束影響范圍?；貪L段要足夠大。要?jiǎng)h除表用drop;若想保留表而將表中數(shù)據(jù)刪除，如果于事務(wù)無關(guān)，用truncate即可實(shí)現(xiàn)。如果和事務(wù)有關(guān)，或老是想觸發(fā)trigger,還是用delete。
- Truncate table 表名 速度快,而且效率高,因?yàn)? truncate table 在功能上與不帶 WHERE 子句的 DELETE 語句相同：二者均刪除表中的全部行。但 TRUNCATE TABLE 比 DELETE 速度快，且使用的系統(tǒng)和事務(wù)日志資源少。DELETE 語句每次刪除一行，并在事務(wù)日志中為所刪除的每行記錄一項(xiàng)。TRUNCATE TABLE 通過釋放存儲(chǔ)表數(shù)據(jù)所用的數(shù)據(jù)頁來刪除數(shù)據(jù)，并且只在事務(wù)日志中記錄頁的釋放。
- TRUNCATE TABLE 刪除表中的所有行，但表結(jié)構(gòu)及其列、約束、索引等保持不變。新行標(biāo)識(shí)所用的計(jì)數(shù)值重置為該列的種子。如果想保留標(biāo)識(shí)計(jì)數(shù)值，請改用 DELETE。如果要?jiǎng)h除表定義及其數(shù)據(jù)，請使用 DROP TABLE 語句。
- 對于由 FOREIGN KEY 約束引用的表，不能使用 TRUNCATE TABLE，而應(yīng)使用不帶 WHERE 子句的 DELETE 語句。由于 TRUNCATE TABLE 不記錄在日志中，所以它不能激活觸發(fā)器。
視圖的作用，視圖可以更改嗎
視圖是虛擬的表，與包含數(shù)據(jù)的表不一樣，視圖只包含使用時(shí)動(dòng)態(tài)檢索數(shù)據(jù)的查詢；不包含任何列或數(shù)據(jù)。使用視圖可以簡化復(fù)雜的sql操作，隱藏具體的細(xì)節(jié)，保護(hù)數(shù)據(jù)；視圖創(chuàng)建后，可以使用與表相同的方式利用它們。
視圖不能被索引，也不能有關(guān)聯(lián)的觸發(fā)器或默認(rèn)值，如果視圖本身內(nèi)有order by 則對視圖再次order by將被覆蓋。
創(chuàng)建視圖：create view xxx as xxxx
對于某些視圖比如未使用聯(lián)結(jié)子查詢分組聚集函數(shù)Distinct Union等，是可以對其更新的，對視圖的更新將對基表進(jìn)行更新；但是視圖主要用于簡化檢索，保護(hù)數(shù)據(jù)，并不用于更新，而且大部分視圖都不可以更新。
數(shù)據(jù)庫范式
第一范式
在任何一個(gè)關(guān)系數(shù)據(jù)庫中，第一范式（1NF）是對關(guān)系模式的基本要求，不滿足第一范式（1NF）的數(shù)據(jù)庫就不是關(guān)系數(shù)據(jù)庫。 所謂第一范式（1NF）是指數(shù)據(jù)庫表的每一列都是不可分割的基本數(shù)據(jù)項(xiàng)，同一列中不能有多個(gè)值，即實(shí)體中的某個(gè)屬性不能有多個(gè)值或者不能有重復(fù)的屬性。如果出現(xiàn)重復(fù)的屬性，就可能需要定義一個(gè)新的實(shí)體，新的實(shí)體由重復(fù)的屬性構(gòu)成，新實(shí)體與原實(shí)體之間為一對多關(guān)系。在第一范式（1NF）中表的每一行只包含一個(gè)實(shí)例的信息。簡而言之，「第一范式就是無重復(fù)的列」。
第二范式
第二范式（2NF）是在第一范式（1NF）的基礎(chǔ)上建立起來的，即滿足第二范式（2NF）必須先滿足第一范式（1NF）。第二范式（2NF）要求數(shù)據(jù)庫表中的每個(gè)實(shí)例或行必須可以被惟一地區(qū)分。為實(shí)現(xiàn)區(qū)分通常需要為表加上一個(gè)列，以存儲(chǔ)各個(gè)實(shí)例的惟一標(biāo)識(shí)。這個(gè)惟一屬性列被稱為主關(guān)鍵字或主鍵、主碼。 第二范式（2NF）要求實(shí)體的屬性完全依賴于主關(guān)鍵字。所謂完全依賴是指不能存在僅依賴主關(guān)鍵字一部分的屬性，如果存在，那么這個(gè)屬性和主關(guān)鍵字的這一部分應(yīng)該分離出來形成一個(gè)新的實(shí)體，新實(shí)體與原實(shí)體之間是一對多的關(guān)系。為實(shí)現(xiàn)區(qū)分通常需要為表加上一個(gè)列，以存儲(chǔ)各個(gè)實(shí)例的惟一標(biāo)識(shí)。簡而言之，「第二范式就是非主屬性非部分依賴于主關(guān)鍵字」。
第三范式
滿足第三范式（3NF）必須先滿足第二范式（2NF）。簡而言之，第三范式（3NF）要求一個(gè)數(shù)據(jù)庫表中不包含已在其它表中已包含的非主關(guān)鍵字信息。例如，存在一個(gè)部門信息表，其中每個(gè)部門有部門編號(hào)（dept_id）、部門名稱、部門簡介等信息。那么在員工信息表中列出部門編號(hào)后就不能再將部門名稱、部門簡介等與部門有關(guān)的信息再加入員工信息表中。如果不存在部門信息表，則根據(jù)第三范式（3NF）也應(yīng)該構(gòu)建它，否則就會(huì)有大量的數(shù)據(jù)冗余。簡而言之，第三范式就是屬性不依賴于其它非主屬性。（我的理解是消除冗余）
什么是覆蓋索引
如果一個(gè)索引包含（或者說覆蓋）所有需要查詢的字段的值，我們就稱 之為“覆蓋索引”。我們知道在InnoDB存儲(chǔ)引 擎中，如果不是主鍵索引，葉子節(jié)點(diǎn)存儲(chǔ)的是主鍵+列值。最終還是要“回表”，也就是要通過主鍵再查找一次,這樣就 會(huì)比較慢。覆蓋索引就是把要查詢出的列和索引是對應(yīng)的，不做回表操作！
作者：Dreamcat
鏈接：https://juejin.im/post/5e94116551882573b86f970f
來源：掘金
