應用開發實踐之關係型資料庫(以MySql為例)小結

五嶽發表於2020-05-04
資料庫MySql

本文主要是對目前工作中使用到的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。
為了便於理解，將以上幾種傳播行為分類：

傳播性的型別 當前不在事務中 當前在事務中 備註
propagation_requierd 新建一個事務 加入到當前事務 最常見的選擇
propagation_supports 非事務執行 加入當前事務
propagation_mandatory 拋異常 加入當前事務
propagation_required_new 新建事務 掛起當前事務
propagation_not_supported 非事務執行 掛起當前事務
propagation_never 非事務執行 拋異常
propagation_nested 新建事務 巢狀事務內執行

事務掛起

指當前方法不再受所屬的事務控制直到該方法結束。比如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欄位，只是不設定成為外來鍵即可，更新由應用層來做。

相關文章