本文主要是對目前工作中使用到的DB相關知識點的總結，應用開發瞭解到以下深度基本足以應對日常需求，再深入下去更偏向於DB本身的理論、調優和運維實踐。
不在本文重點關註討論的內容（可能會提到一些）：

• 具體的DQL、DML、DDL、DCL等語法
• 基礎性的概念，如主鍵、索引、存儲過程（註：阿裡巴巴規範中禁止使用存儲過程）等
• 聯合查詢，我個人不太喜歡在應用中寫過於複雜的SQL，性能和後續維護容易出現問題
• 可能會用到的具體DB特性，如oracle的DATA GUARD
  有一些屬於基礎知識或語法但是常用的信息，也會列一下，如join的用法。

一、基礎

1. ACID

DB的四大特性，這裡簡單概括下不具體展開。

• 原子性（Atomicity）：事務操作中的多條SQL，要麼全部成功要麼全部失敗，失敗後回滾不對原有數據造成任何影響。
• 一致性（Consistency）:事務開始前和結束後，資料庫的完整性沒有被破壞。如觸發器、約束、級聯回滾
• 隔離性（Isolation）：多個事務支持併發讀寫。具體隔離級別見後文。
• 持久性（Durability）：事務結束後，修改是永久的，不丟失。

2. 範式

這裡展開講比較複雜，實踐中很少用到，一般滿足1NF即可。
高一級必滿足低一級。

1. 1NF：每個屬性都不可再分，即表的列是最原子的
2. 2NF：在1NF基礎上，消除非主屬性對鍵的部分依賴。這裡不解釋非主屬性和鍵的含義，可以簡單認為是指不存在列A可以通過列B來獲取，如“學生姓名-學號”這種y=f(x)的函數關係。
3. 3NF：在2NF的基礎之上，消除了非主屬性對於碼的傳遞函數依賴
4. BCNF：對於關係模式R，如果每一個函數依賴的決定因素都包含鍵，則R屬於BCNF範式
   有興趣可以參考：範式通俗理解：1NF、2NF、3NF和BNCF

二、事務

3. 事務的隔離級別

3.1 讀現象

讀現象是伴生於不同的隔離級別出現的。讀現象的場景都是在多個事務併發執行的前提下可能出現的：

• 臟讀 —— 一個事務讀取了另一個未提交事務執行過程中的數據。此時另一個事務可能會由於提交失敗而回滾。
• 不可重覆讀 —— 一個事務執行過程中多次查詢同一條數據但返回了不同查詢結果。這說明在事務執行過程中，數據被其他事務修改並提交了。
• 幻讀 —— 事務1先行查詢了某種數據，在修改或插入提交之前，事務2對此類數據進行了插入或刪除並提交，導致了事務1對預期結果的數量變化。

3.2 隔離級別

• 未提交讀(read uncommited)：允許另外一個事務可以看到這個事務未提交的數據。
• 提交讀(read commited)：保證一個事務提交後才能被另外一個事務讀取，而不能讀取未提交的數據。
• 可重覆讀(repeatable read)：保持讀鎖和寫鎖一直到事務提交，但不提供範圍鎖，因此不能避免幻讀。
• 可序列化(serializable)：代價最高但最可靠的事務隔離級別，事務被處理為順序執行。

3.3 隔離級別與讀現象

不同的隔離級別可以防止讀現象。

註：為什麼提交讀不能避免不可重覆讀？假設A事務需要讀取兩次變數a，第一次讀取時a=10，執行過程中a被事務B修改變成了20，那麼A第二次讀時a與第一次的結果不同。

3.4 查看DB的隔離級別

// 查看當前會話
select @@tx_isolation;
// 查看當前系統
select @@global.tx_isolation;

MySql 5.7.14-ALISQL版預設是提交讀。

4. 事務傳播性(Spring)

在多個含有事務方法的相互調用時，事務如何在這些方法間傳播。
spring支持7種事務傳播行為：

• propagation_requierd：如果當前沒有事務，就新建一個事務；否則加入到這個已有事務中，這是最常見的選擇。
• propagation_supports：支持當前事務，如果沒有當前事務，就以非事務方法執行。
• propagation_mandatory：使用當前事務，如果沒有當前事務，就拋出異常。
• propagation_required_new：新建事務，如果當前存在事務，把當前事務掛起。
• propagation_not_supported：以非事務方式執行操作，如果當前存在事務，就把當前事務掛起。
• propagation_never：以非事務方式執行操作，如果當前事務存在則拋出異常。
• propagation_nested：如果當前存在事務，則在嵌套事務內執行。如果當前沒有事務，則執行與propagation_required類似的操作

Spring預設是propagation_requierd。
為了便於理解，將以上幾種傳播行為分類：

事務掛起

指當前方法不再受所屬的事務控制直到該方法結束。比如A方法起了一個事務，調用B方法時B掛起事務，那麼B的所有DB操作都不再受A方法的事務控制，直到B執行結束。

事務嵌套

嵌套的事務可以獨立於當前事務提交或回滾。

三、性能與優化

5. 執行計劃

確認SQL在實際執行時的執行情況，如是否走上索引、走了哪個索引、掃描行數、執行順序（如多個select級聯查詢）

查看方式

explain XXX

解讀

MySql: MySQL_執行計劃詳細說明

6. 索引相關

6.1 聚集/非聚集索引

• 聚集索引：邏輯上和物理上都是連續的，如主鍵，一般一個表只有一個聚集索引
• 非聚集索引：邏輯上是連續的但物理上不是

以Mysql的InnoDB為例：
主鍵是聚集索引。
唯一索引、普通索引、首碼索引等都是二級索引（輔助索引）。

結合B+樹的知識，對於聚集索引，索引數據和存儲數據是在一起的，比如id-age這個記錄。
對於非聚集索引，只有索引數據，定位具體的記錄需要通過索引來找，也即通過索引找到id，再通過id找到id-age這條記錄。

6.2 覆蓋索引

查詢條件和結果全部在一個索引中，MySql不需要通過二級索引查到主鍵後再查一遍數據就可以返回查詢數據。覆蓋索引可以大大提升查詢效率，舉例

select a, b from table_x where c = XXX order by d;

其中a、b、c、d全部在索引中，那麼這就是覆蓋索引。

對於做不到覆蓋索引的查詢，查到主鍵後還要回到數據表中把數據查詢出來，則稱為__回表__。

6.3 索引有序性

對於聯合索引，建立(a, b, c)相當於建立(a), (a,b), (a,b,c)。
在這個索引下，遵循”最左首碼原理“，即先按a排序，再按b排序，最後按c排序。
如果缺失了前一列，如where b = xxx，則走不上索引。
如果某一列不是等值匹配，如where a>10 and b = 1，則只能部分走上索引，b走不上索引。非等值匹配有<、>、!=、IN、LIKE等。

更完整的可以參考mysql組合索引的有序性

6.4 創建了索引但沒有走上的原因

1. 使用了<、>、!=、IN、LIKE等（非最左的like，也即like 'xxx%'是可以的）
2. 使用or連接查詢子句
3. 預期使用聯合索引，但實際上沒有按照最左首碼原理排序（見上文7.3節）
4. 字元串類型沒有使用引號
5. 全表掃描比走索引快
6. where子句中包含了函數或表達式
   為什麼你創建的資料庫索引沒有生效，索引失效的條件！

7. 行鎖和表鎖

select...for update，走上索引（含主鍵）是行鎖，沒走上就是表鎖。但是如果索引匹配過多，也會變成表鎖。
[轉載&整理&鏈接]mysql 通過測試'for update'，深入瞭解行鎖、表鎖、索引

8. 索引的B+樹

https://www.cnblogs.com/tiancai/p/9024351.html
https://www.jianshu.com/p/9bd572b0a0d4
https://www.jianshu.com/p/23524cc57ca4

簡單概括一下：
B樹的中間節點和葉子節點都有不止一個關鍵字(key)。B樹出現的目的是減少磁碟臂移動的開銷從而，儘量減少讀寫的次數。
B+樹與B樹的不同在於，B+樹的數據都在葉子節點上，中間件節點沒有數據。
應用：由於B樹最左首碼匹配的特性，如果用左模糊查詢(like "%xxx")是走不上索引的。

四、應用開發

9. 分頁查詢

查詢第N頁(下標從1開始)數據，每頁大小PageSize

// 先獲取符合條件的總數
select count(1) from tableA where XXX
// 查詢該頁
// 偏移量,可選 offset = (pageSize-1) * N
// 行數 rows = pageSize
select row1, ..., rowN from tableA where XXX limit offset, rows

10. Join

10.1 語法

SELECT Table1.Row1, Table1.Row2, Table2.Row1
FROM Table1
INNER JOIN Table2
ON Table1.Row2 = Table2.Row2
ORDER BY Table1.Row1

10.2 種類

inner join( = join)，都匹配才返回
left join，左表全返回不管右表有沒有匹配
right join，右表全返回不管左表有沒有匹配
full join，全返回，左表右表無論對方匹配都返回所有行

11. MyBatis緩存

MyBatis緩存分為兩級：一級緩存，SqlSession級別；二級緩存，SqlSessionFactory級別。和通常命名習慣相反，二級緩存的作用範圍大於一級緩存，原因是，SqlSession是由SqlSessionFactory創建的。

MyBatis預設開啟一級緩存，不開啟二級緩存。一級緩存生效於同一個SqlSession，當這個session沒有做任何update操作且查詢完全相同時，會返回一樣的數據。
此時，在併發環境下，很有可能會發生這種情況：在一臺伺服器A上連續查詢兩次，兩次屬於同一個SqlSession；中間另一個伺服器B對錶做了更新，A看到的第二次查詢結果仍然是舊的。

關於緩存的細節，如如何判斷“同一次查詢”、緩存有效期、SqlSession原理，可以自行查閱。推薦mybatis中文官網，有很多原理的介紹。
在實踐中，spring和mybatis整合以後每次查詢都會刷新sqlSession，即一級緩存是無效的。
MyBatis緩存系列
單獨提一下，二級緩存的readOnly預設為false，同一條數據在記憶體中每個對象都是獨立的，可修改相互不影響。可參考如何理解Mybatis二級緩存配置中的readOnly？

12. mybatis和hibernate

我在工作中絕大多數時間都用mybatis+spring/springboot寫持久層，只有一個應用因為使用SpringDataJPA才對hibernate才做了一些瞭解。
看了一些資料，瞭解到二者在寫法以外，性能的差別主要在於多表查詢這個場景，hibernate會比mybatis慢一些，原因是

hibernate為了保證POJO的數據完整性，需要將關聯的數據載入，需要額外地查詢更多的數據。

MyBatis和Hibernate相比，優勢在哪裡？ - 鄭沐興的回答 - 知乎
此外，JPA如果想運行原生sql，可以使用EntityManager。

13. 水平擴展與垂直擴展

13.1 水平擴展——分庫分表一般思路

• 按某一欄位將一張表分片，如userId。分片方式：
  • 第X位到Y位的值
  • 欄位hash值
  • 特殊值特殊處理，如某KA(Key Account關鍵客戶)數據量較大，單獨一個分表

13.2 水平擴展——歷史庫

按日期定時同步遷移及清理線上數據
查詢需要根據日期路由到線上庫或歷史庫

13.3 水平擴展——按業務拆表

按業務，已處理數據及未處理數據拆分。如已受理未申請單和已完結申請單分開保存。

13.4 垂直擴展

提供更多、更強、容量更大的硬體資源。

13.5 FailOver

在電腦術語中，故障轉移（英語：failover），即當活動的服務或應用意外終止時，快速啟用冗餘或備用的伺服器、系統、硬體或者網路接替它們工作。 故障轉移(failover)與交換轉移操作基本相同，只是故障轉移通常是自動完成的，沒有警告提醒手動完成，而交換轉移需要手動進行。 ——wiki

FailOver是從應用層面做的，不是單純DB層面。

13.5.1 背景

單庫架構，一旦庫掛掉整個服務不可用；
主備架構，切換時有時間延遲；
FailOver從分佈上來看仍然是主備架構，但是增加了系統自動切換恢復能力。

13.5.2 思想

和去IOE是一致的，用大量相對廉價的硬體，拆分服務，減少單點，提升整體的可用性。

13.5.3 交互模式

僅舉兩個最典型的例子，具體場景需要結合硬體能力和應用架構綜合分析。

13.5.3.1 記賬型

特點：

• 主備準實時同步，Failover庫平時不做讀寫
• 主備庫表結構一致，Failover庫不一定和主備庫的表一致（可能會少一些不需要用到的表）
• 賬戶型數據保持最終一致性即可

方案：

• 按比列拆表拆庫，降低單個庫掛掉時影響用戶數
• 正常工作時，主備準實時同步，Failover庫不讀寫
• 主庫發生異常時，切換到備庫讀，Failover庫記錄操作信息。同時，業務操作儘量分流到不依賴相關庫到支路上。
• 主庫恢復時，不再寫入Failover，將Failover庫和主庫內容做merge，回寫主庫，主庫再同步備庫

註：可以採取雙寫、基於讀庫（上文中所述，利用oracle的data guard、mysql的replication等）、非同步消息等保證主備一致。

13.5.3.2 交易流水型

特點：

• 數據保證創建，不保證推進。即交易下單失敗，重新下單
• failover庫交易號與主庫通過某些位隔離，不重覆

方案：

• 和“記賬型”類似，Failover庫數據推進業務完成即可
• 可以不回寫failover期間的數據，依賴中間件讀failover庫中數據

13.6 讀寫分離

為瞭解決讀大於多於寫的場景下資料庫瓶頸的一種架構模式。同樣需要結合具體業務不能生搬硬套。
主要是一寫多讀的架構，在主庫掛掉的場景下有可能需要考慮使用paxos演算法來決定新的主庫。
在做讀寫分離前，可以先考慮緩存是否能解決當前場景的問題。

五、運維

14. binlog

記錄DB操作（不含查詢）及其他執行信息的二進位日誌。
可以參考下麵兩篇文章簡單瞭解下。
【原創】研發應該懂的binlog知識(上)
【原創】研發應該懂的binlog知識(下)

六、其他話題

15. 零碎的話題

想起來就補一些。

15.1 列的預設值

對於有預設值的非空列，如果在insert語句中指明瞭這一列且值為null，插入仍然會報錯，此時不會取預設值。讓該列取預設值的方式是，不讓該列出現在insert語句中。

15.2 索引下推

MySql5.6做的優化之一，可以在like查詢中提高性能。利用查詢子句中能確定的查詢條件，減少一次查詢匹配到的索引，從而減少回表查詢的數據。

16. 延伸話題

可以自行研究的話題，限於筆者接觸範圍和篇幅，不展開來寫。

• 索引建立實踐，是否越多越好，應該怎麼選擇索引列
• hibernate和mybaits的區別，最大區別是mybatis需要手寫sql，用一定的工作量更大的靈活性，利於優化和多表聯合查詢
• redo log、undo log，與DB本身的分離
• 以下內容可能被濫用，我在實際工作中幾乎沒有用到，有興趣可以自行瞭解。
  • 觸發器
  • union
  • 視圖
• 全表掃描時發生的filesort原理

附：”點評“ 《阿裡巴巴JAVA開發手冊》之MySql規範部分

開發中遵守一些事先約定好的規範，有助於提升研發效率（無論是個人還是團隊內部或團隊之間），避免犯一些重覆錯誤，也有助於後續的維護。對於《阿裡巴巴JAVA開發手冊》中的規範，限於篇幅並沒有寫明原因，筆者基於自己的開發經驗進行一些點評，供參考。
本來是想針對《阿裡巴巴JAVA開發手冊》MySql規範部分這一部分補一下點評的，但是發現前兩天新出的泰山版已經補上很多說明，沒必要一一點評，直接下載來看就好：https://files.cnblogs.com/files/wuyuegb2312/《Java開發手冊（泰山版）》.pdf.zip

可以看出，前面一部分有很多規範都是和Java OOP相關聯的。對於部分條目，是之前沒註意到的，單獨拉出來點評下。

count(*)和count(1)

【強制】不要使用 count(列名)或 count(常量)來替代 count()，count()是 SQL92 定義的標
準統計行數的語法，跟資料庫無關，跟 NULL 和非 NULL 無關。
說明：count(*)會統計值為 NULL 的行，而 count(列名)不會統計此列為 NULL 值的行。

官方文檔提到，InnoDB下count(*)和count(1)是沒有區別的：

InnoDB handles SELECT COUNT() and SELECT COUNT(1) operations in the same way. There is no performance difference.
但考慮到其他實現對count()有優化（如MyISAM，前提是沒有WHERE和GROUP BY子句，直接取緩存的總數），再考慮到用其他DB的情況，統一起見一直用count(*)就好了。
更詳細的分析可以看 為什麼阿裡巴巴禁止使用 count(列名)或 count(常量)來替代 count(*)

禁用外鍵

【強制】不得使用外鍵與級聯，一切外鍵概念必須在應用層解決。
說明：（概念解釋）學生表中的 student_id 是主鍵，那麼成績表中的student_id 則為外鍵。如果更新學生表中的 student_id，同時觸發成績表中的 student_id 更新，即為級聯更新。外鍵與級聯更新適用於單機低併發，不適合分散式、高併發集群；級聯更新是強阻塞，存在資料庫更新風暴的風險；外鍵影響資料庫的插入速度。

禁止使用外鍵，在本例中並不是不允許在成績表中存放student_id欄位，只是不設置成為外鍵即可，更新由應用層來做。