数据库技术支持文档
數據庫技術支持文檔
說明
對平時工作學習遇到的數據庫相關知識和技巧記錄，會對一些優秀知識講解文章的摘錄，包括PostgreSQL、MySQL、Oracle等
| 1.0 | 初稿 | 2021-05-28 | pitt1997 |
完整文件下載
數據庫技術支持文檔.pdf
數據庫技術支持文檔.md
MySQL
MySQL 數據怎么存儲？MySQL 中的數據在磁盤上，它到底是如何進行存儲的？長什么樣？
掃盲：存儲引擎是作用在表上的。
主要命令
查詢當前數據庫支持的存儲引擎
mysql> show engines;查詢當前默認的存儲引擎
mysql> show variables like '%storage_engine%';查詢表的相關信息（先使用具體的數據庫）
use '數據庫名'; show table status like '表名';查看是否開啟了binlog
mysql> show variables like 'log_%';查看指定binlog文件的內容
mysql> show binlog events in 'mysql-bin.000001';只查看第一個binlog文件的內容
mysql> show binlog events;儲存引擎
MySQL 中的數據用各種不同的技術存儲在文件（或者內存）中，這些不同的技術以及配套的相關功能在MySQL 中被稱作存儲引擎。不同的存儲引擎，我們的數據存儲的格式也會不一樣。
MySQL 中常用的存儲引擎有兩種：MyISAM 和 InnoDB。并且存儲引擎是作用在表上的!
MySQL 5.5之前，MyISAM 是默認的存儲引擎。MySQL 5.5開始，InnoDB 是默認的存儲引擎。
主要區別
| 事務 | 不支持 | 支持 |
| 表/行鎖 | 只有表鎖 | 還引入了行鎖 |
| 外鍵 | 不支持 | 支持 |
| 全文索引 | 支持 | 版本5.6 開始支持 |
| 讀寫速度 | 更快 | 更慢 |
相關命令
查詢當前數據庫支持的存儲引擎
mysql> show engines;[外鏈圖片轉存失敗,源站可能有防盜鏈機制,建議將圖片保存下來直接上傳(img-kliRhbUm-1629818634013)(C:\Users\17996\AppData\Roaming\Typora\typora-user-images\1622134580802.png)]
查詢當前默認的存儲引擎
mysql> show variables like '%storage_engine%';[外鏈圖片轉存失敗,源站可能有防盜鏈機制,建議將圖片保存下來直接上傳(img-7L2KTRxp-1629818634015)(C:\Users\17996\AppData\Roaming\Typora\typora-user-images\1622134667543.png)]
查詢表的相關信息（先使用具體的數據庫）
use '數據庫名'; show table status like '表名';[外鏈圖片轉存失敗,源站可能有防盜鏈機制,建議將圖片保存下來直接上傳(img-xp2RZo7F-1629818634016)(C:\Users\17996\AppData\Roaming\Typora\typora-user-images\1622135393028.png)]
查詢說明詳細資料參考鏈接：鏈接
| name | 表名 |
| Engine | 存儲引擎 |
| Version | 版本 |
| Row_format | 行格式 |
| … | … |
MyISAM
每個 MyISAM 表都以3個文件存儲在磁盤上。這些文件的名稱以表名開頭，以擴展名指示文件類型。
.frm 文件（frame）存儲表結構；
.MYD 文件（MY Data）存儲表數據；
.MYI 文件（MY Index）存儲表索引。
MySQL 里的數據默認是存放在安裝目錄下的 data 文件夾中，也可以自己修改。
MyISAM中的B+tree
.MYI 文件組織索引的方式就是 B+tree。葉子節點的 value 處存放的就是索引所在行的磁盤文件地址。
[外鏈圖片轉存失敗,源站可能有防盜鏈機制,建議將圖片保存下來直接上傳(img-FBKi2oXy-1629818634019)(C:\Users\17996\AppData\Roaming\Typora\typora-user-images\1622982461610.png)]
底層查找過程
首先會判斷查找條件 where 中的字段是否是索引字段，如果是就會先拿著這字段去 .MYI 文件里通過 B+tree 快速定位，從根節點開始定位查找；
找到后再把這個索引關鍵字（就是我們的條件）存放的磁盤文件地址拿到 .MYD 文件里面找，從而定位到索引所在行的記錄。
注意：表邏輯上相鄰的記錄行數據在磁盤上并不一定是物理相鄰的。
[外鏈圖片轉存失敗,源站可能有防盜鏈機制,建議將圖片保存下來直接上傳(img-XGnjKLCx-1629818634021)(C:\Users\17996\AppData\Roaming\Typora\typora-user-images\1622982415598.png)]
InnoDB
一張 InnoDB 表底層會對應2個文件在文件夾中進行數據存儲。
.frm 文件（frame）存儲表結構；
.ibd 文件（InnoDB Data）存儲表索引+數據。
下面我創建了以 InnoDB 作為存儲引擎的一張表 t_user_innodb。
很顯然，InnoDB 把索引和數據都放在一個文件里存著了。毫無疑問，InnoDB 表里面的數據也是用 B+tree 數據結構組織起來的。
下面我們來看看它具體是怎么存儲的。
[外鏈圖片轉存失敗,源站可能有防盜鏈機制,建議將圖片保存下來直接上傳(img-ggQ1MkMW-1629818634022)(C:\Users\17996\AppData\Roaming\Typora\typora-user-images\1622982609363.png)]
.ibd 存儲數據的特點就是 B+tree 的葉子節點上包括了我們要的索引和該索引所在行的其它列數據。
底層查找過程：
首先會判斷查找條件 where 中的字段是否是索引字段，如果是就會先拿著這字段去 .ibd 文件里通過 B+tree 快速定位，從根節點開始定位查找；
找到后直接把這個索引關鍵字及其記錄所在行的其它列數據返回。
[外鏈圖片轉存失敗,源站可能有防盜鏈機制,建議將圖片保存下來直接上傳(img-QMwllPlA-1629818634023)(C:\Users\17996\AppData\Roaming\Typora\typora-user-images\1622982673790.png)]
聚集（聚簇）索引
聚集索引：葉子節點包含了完整的數據記錄。
簡單來說就是索引和它所在行的其它列數據全部都在一起了。
很顯然，MyISAM 沒有聚集索引，InnoDB 有，而且 InnoDB 的主鍵索引就是天然的聚集索引。
有聚集索引當然就有非聚集索引（稀疏索引）。對于 MyISAM 來說，它的索引就是非聚集索引。因為它的索引和數據是分開兩個文件存的：一個 .MYI 存索引，一個 .MYD 存數據。
為什么要有主鍵？
為什么 DBA 都建議表中一定要有主鍵，而且推薦使用整型自增？
因為 InnoDB 表里面的數據必須要有一個 B+tree 的索引結構來組織、維護我們的整張表的所有數據，從而形成 .idb 文件。
那和主鍵有什么關系？
如果 InnoDB 創建了一張沒有主鍵的表，那這張表就有可能沒有任何索引，則 MySQL會選擇所有具有唯一性并且不為 null 中的第一個字段的創建聚集索引。
如果沒有唯一性索引的字段就會有一個隱式字段成為表的聚集索引：而這個隱式字段，就是 InnoDB 幫我們創建的一個長度為 6字節 的整數列 ROW_ID，它隨著新行的插入單調增加，InnoDB 就以該列對數據進行聚集。
使用這個 ROW_ID 列的表都共享一個相同的全局序列計數器（這是數據字典的一部分）。為了避免這個 ROW_ID 用完，所以建議表中一定要單獨建立一個主鍵字段。
為什么推薦使用整型自增？
首先整型的占用空間會比字符串小，而且在查找上比大小也會比字符串更快。字符串比大小的時候還要先轉換成 ASCII 碼再去比較。
如果使用自增的話，在插入方面的效率也會提高。
不使用自增，可能時不時會往 B+tree 的中間某一位置插入元素，當這個節點位置放滿了的時候，節點就要進行分裂操作（效率低）再去維護，有可能樹還要進行平衡，又是一個耗性能的操作。
都用自增就會永遠都往后面插入元素，這樣索引節點分裂的概率就會小很多。
MySQL的一級索引和二級索引
一級索引和二級索引即對應聚集索引和非聚集索引，葉子節點存放主索引和數據的樹，稱為聚集索引樹；葉子節點存放輔助索引和主索引的樹，稱為非聚集索引樹。
一級索引（聚集索引）
索引和數據存儲在一起，都存儲在同一個B+tree中的葉子節點。一般主鍵索引都是一級索引
二級索引（非聚集索引）
除聚集索引之外的所有索引都叫做二級索引，也稱輔助索引。
它的葉子節點則不會存儲其它所有列的數據，就只存儲主鍵值。
[外鏈圖片轉存失敗,源站可能有防盜鏈機制,建議將圖片保存下來直接上傳(img-ahqUqbhF-1629818634025)(C:\Users\17996\AppData\Roaming\Typora\typora-user-images\1622982870747.png)]
底層查找過程：
每次要找數據的時候，會根據它找到對應葉子節點的主鍵值，再把它拿到聚集索引的 B+tree 中查找，從而拿到整條記錄。
[外鏈圖片轉存失敗,源站可能有防盜鏈機制,建議將圖片保存下來直接上傳(img-RqilMesM-1629818634025)(C:\Users\17996\AppData\Roaming\Typora\typora-user-images\1622982895238.png)]
優點：保持一致性和節省空間。
SQL執行過程
我們的系統到底是如何和 MySQL 交互的？MySQL 如何幫我們存儲數據、又是如何幫我們管理事務？MySQL 在接受到我們發送的 SQL 語句時又分別做了哪些事情？
MySQL 驅動
我們的系統在和 MySQL 數據庫進行通信的時候，總不可能是平白無故的就能接收和發送請求，就算是你沒有做什么操作，那總該是有其他的“人”幫我們做了一些事情，基本上使用過 MySQL 數據庫的程序員多多少少都會知道 MySQL 驅動這個概念的。就是這個 MySQL 驅動在底層幫我們做了對數據庫的連接，只有建立了連接了，才能夠有后面的交互
[外鏈圖片轉存失敗,源站可能有防盜鏈機制,建議將圖片保存下來直接上傳(img-P3WEIzae-1629818634027)(C:\Users\17996\AppData\Roaming\Typora\typora-user-images\1623083908291.png)]
這樣的話，在系統和 MySQL 進行交互之前，MySQL 驅動會幫我們建立好連接，然后我們只需要發送 SQL 語句就可以執行 CRUD 了。一次 SQL 請求就會建立一個連接，多個請求就會建立多個連接，那么問題來了，我們系統肯定不是一個人在使用的，換句話說肯定是存在多個請求同時去爭搶連接的情況。我們的 web 系統一般都是部署在 tomcat 容器中的，而 tomcat 是可以并發處理多個請求的，這就會導致多個請求會去建立多個連接，然后使用完再都去關閉，這樣會有什么問題呢？
[外鏈圖片轉存失敗,源站可能有防盜鏈機制,建議將圖片保存下來直接上傳(img-cEk7yVmm-1629818634027)(C:\Users\17996\AppData\Roaming\Typora\typora-user-images\1623084123562.png)]
Java系統在通過 MySQL 驅動和 MySQL 數據庫連接的時候是基于 TCP/IP 協議的，所以如果每個請求都是新建連接和銷毀連接，那這樣勢必會造成不必要的浪費和性能的下降，也就說上面的多線程請求的時候頻繁的創建和銷毀連接顯然是不合理的。必然會大大降低我們系統的性能，但是如果給你提供一些固定的用來連接的線程，這樣是不是不需要反復的創建和銷毀連接了呢？相信懂行的朋友會會心一笑，沒錯，說的就是數據庫連接池。
數據庫連接池：維護一定的連接數，方便系統獲取連接，使用就去池子中獲取，用完放回去就可以了，我們不需要關心連接的創建與銷毀，也不需要關心線程池是怎么去維護這些連接的。[外鏈圖片轉存失敗,源站可能有防盜鏈機制,建議將圖片保存下來直接上傳(img-2wvUHpYK-1629818634029)(C:\Users\17996\AppData\Roaming\Typora\typora-user-images\1623084190998.png)]
常見的數據庫連接池有 Druid、C3P0、DBCP，連接池實現原理在這里就不深入討論了，采用連接池大大節省了不斷創建與銷毀線程的開銷，這就是有名的「池化」思想，不管是線程池還是 HTTP 連接池，都能看到它的身影。數據庫連接池
到這里，我們已經知道的是我們的系統在訪問 MySQL 數據庫的時候，建立的連接并不是每次請求都會去創建的，而是從數據庫連接池中去獲取，這樣就解決了因為反復的創建和銷毀連接而帶來的性能損耗問題了。不過這里有個小問題，業務系統是并發的，而 MySQL 接受請求的線程呢，只有一個？
其實MySQL 的架構體系中也已經提供了這樣的一個池子，也是數據庫連池。雙方都是通過數據庫連接池來管理各個連接的，這樣一方面線程之前不需要是爭搶連接，更重要的是不需要反復的創建的銷毀連接。
[外鏈圖片轉存失敗,源站可能有防盜鏈機制,建議將圖片保存下來直接上傳(img-tBHsB7VA-1629818634030)(C:\Users\17996\AppData\Roaming\Typora\typora-user-images\1623084436096.png)]
至此系統和 MySQL 數據庫之間的連接問題已經說明清楚了。那么 MySQL 數據庫中的這些連接是怎么來處理的，又是誰來處理呢？
網絡連接必須由線程來處理
對計算基礎稍微有一點了解的的同學都是知道的，網絡中的連接都是由線程來處理的，所謂網絡連接說白了就是一次請求，每次請求都會有相應的線程去處理的。也就是說對于 SQL 語句的請求在 MySQL 中是由一個個的線程去處理的。
[外鏈圖片轉存失敗,源站可能有防盜鏈機制,建議將圖片保存下來直接上傳(img-HyshhPSC-1629818634031)(C:\Users\17996\AppData\Roaming\Typora\typora-user-images\1623084473459.png)]
那這些線程會怎么去處理這些請求？會做哪些事情？
SQL 接口
MySQL 中處理請求的線程在獲取到請求以后獲取 SQL 語句去交給 SQL 接口去處理。
查詢解析器
假如現在有這樣的一個 SQL
SELECT stuName,age,sex FROM students WHERE id = 1但是這個 SQL 是寫給我們人看的，機器哪里知道你在說什么？這個時候解析器就上場了。他會將 SQL 接口傳遞過來的 SQL 語句進行解析，翻譯成 MySQL 自己能認識的語言，至于怎么解析的就不需要在深究了，無非是自己一套相關的規則。
[外鏈圖片轉存失敗,源站可能有防盜鏈機制,建議將圖片保存下來直接上傳(img-3E0og3jl-1629818634032)(C:\Users\17996\AppData\Roaming\Typora\typora-user-images\1625379739838.png)]
現在 SQL 已經被解析成 MySQL 認識的樣子的，那下一步是不是就是執行嗎？理論上是這樣子的，但是 MySQL 的強大遠不止于此，他還會幫我們選擇最優的查詢路徑。
什么叫最優查詢路徑？就是 MySQL 會按照自己認為的效率最高的方式去執行查詢
具體是怎么做到的呢？這就要說到 MySQL 的查詢優化器了
MySQL 查詢優化器
查詢優化器內部具體怎么實現的我們不需要是關心，我需要知道的是 MySQL 會幫我去使用他自己認為的最好的方式去優化這條 SQL 語句，并生成一條條的執行計劃，比如你創建了多個索引，MySQL 會依據成本最小原則來選擇使用對應的索引，這里的成本主要包括兩個方面, IO 成本和 CPU 成本
IO 成本: 即從磁盤把數據加載到內存的成本，默認情況下，讀取數據頁的 IO 成本是 1，MySQL 是以頁的形式讀取數據的，即當用到某個數據時，并不會只讀取這個數據，而會把這個數據相鄰的數據也一起讀到內存中，這就是有名的程序局部性原理，所以 MySQL 每次會讀取一整頁，一頁的成本就是 1。所以 IO 的成本主要和頁的大小有關
CPU 成本：將數據讀入內存后，還要檢測數據是否滿足條件和排序等 CPU 操作的成本，顯然它與行數有關，默認情況下，檢測記錄的成本是 0.2。
MySQL 優化器 會計算 「IO 成本 + CPU」 成本最小的那個索引來執行
[外鏈圖片轉存失敗,源站可能有防盜鏈機制,建議將圖片保存下來直接上傳(img-em8KBQZm-1629818634034)(C:\Users\17996\AppData\Roaming\Typora\typora-user-images\1625379842659.png)]
優化器執行選出最優索引等步驟后，會去調用【存儲引擎接口】，【存儲引擎接口】需要由【執行器】進行調用，開始去執行被 MySQL 解析過和優化過的 SQL 語句
執行器
執行器是一個非常重要的組件，因為前面那些組件的操作最終必須通過執行器去調用存儲引擎接口才能被執行。執行器最終最根據一系列的執行計劃去調用存儲引擎的接口去完成 SQL 的執行
[外鏈圖片轉存失敗,源站可能有防盜鏈機制,建議將圖片保存下來直接上傳(img-GuIshQeo-1629818634035)(C:\Users\17996\AppData\Roaming\Typora\typora-user-images\1625379965648.png)]
存儲引擎
查詢優化器會調用存儲引擎的接口，去執行 SQL，也就是說真正執行 SQL 的動作是在存儲引擎中完成的。數據是被存放在內存或者是磁盤中的（存儲引擎是一個非常重要的組件，開頭也已經介紹）
我們以一個更新的SQL語句來說明，SQL 如下
UPDATE students SET stuName = '小強' WHERE id = 1當我們系統發出這樣的查詢去交給 MySQL 的時候，MySQL 會按照我們上面介紹的一系列的流程最終通過執行器調用存儲引擎去執行，流程圖就是上面那個。在執行這個 SQL 的時候 SQL 語句對應的數據要么是在內存中，要么是在磁盤中，如果直接在磁盤中操作，那這樣的隨機IO讀寫的速度肯定讓人無法接受的，所以每次在執行 SQL 的時候都會將其數據加載到內存中，這塊內存就是**【 InnoDB 】**中一個非常重要的組件：緩沖池 Buffer Pool
Buffer Pool （緩沖池）是 InnoDB 存儲引擎中非常重要的內存結構，顧名思義，緩沖池其實就是類似 Redis 一樣的作用，起到一個緩存的作用，因為我們都知道 MySQL 的數據最終是存儲在磁盤中的，如果沒有這個 Buffer Pool 那么我們每次的數據庫請求都會磁盤中查找，這樣必然會存在 IO 操作，這肯定是無法接受的。但是有了 Buffer Pool 就是我們第一次在查詢的時候會將查詢的結果存到 Buffer Pool 中，這樣后面再有請求的時候就會先從緩沖池中去查詢，如果沒有再去磁盤中查找，然后在放到 Buffer Pool 中，如下圖
[外鏈圖片轉存失敗,源站可能有防盜鏈機制,建議將圖片保存下來直接上傳(img-vmTzvXH2-1629818634037)(C:\Users\17996\AppData\Roaming\Typora\typora-user-images\1625380312030.png)]
按照上面的那幅圖，這條 SQL 語句的執行步驟大致是這樣子的
undo 日志文件：記錄數據被修改前的樣子
undo 顧名思義，就是沒有做，沒發生的意思。undo log 就是沒有發生事情（原本事情是什么）的一些日志
我們剛剛已經說了，在準備更新一條語句的時候，該條語句已經被加載到 Buffer pool 中了，實際上這里還有這樣的操作，就是在將該條語句加載到 Buffer Pool 中的時候同時會往 undo 日志文件中插入一條日志，也就是將 id=1 的這條記錄的原來的值記錄下來。
這樣做的目的是什么？
Innodb 存儲引擎的最大特點就是支持事務，如果本次更新失敗，也就是事務提交失敗，那么該事務中的所有的操作都必須回滾到執行前的樣子，也就是說當事務失敗的時候，也不會對原始數據有影響，看圖說話
[外鏈圖片轉存失敗,源站可能有防盜鏈機制,建議將圖片保存下來直接上傳(img-9vxD7qWf-1629818634038)(C:\Users\17996\AppData\Roaming\Typora\typora-user-images\1625380409620.png)]
這里說句額外話，其實 MySQL 也是一個系統，就好比我們平時開發的 java 的功能系統一樣，MySQL 使用的是自己相應的語言開發出來的一套系統而已，它根據自己需要的功能去設計對應的功能，它即然能做到哪些事情，那么必然是設計者們當初這么定義或者是根據實際的場景變更演化而來的。所以大家放平心態，把 MySQL 當作一個系統去了解熟悉他。
到這一步，我們的執行的 SQL 語句已經被加載到 Buffer Pool 中了，然后開始更新這條語句，更新的操作實際是在Buffer Pool中執行的，那問題來了，按照我們平時開發的一套理論緩沖池中的數據和數據庫中的數據不一致時候，我們就認為緩存中的數據是臟數據，**那此時 Buffer Pool 中的數據豈不是成了臟數據？**沒錯，目前這條數據就是臟數據，Buffer Pool 中的記錄是小強 數據庫中的記錄是旺財 ，這種情況 MySQL是怎么處理的呢，繼續往下看
redo 日志文件：記錄數據被修改后的樣子
除了從磁盤中加載文件和將操作前的記錄保存到 undo 日志文件中，其他的操作是在內存中完成的，內存中的數據的特點就是：斷電丟失。如果此時 MySQL 所在的服務器宕機了，那么 Buffer Pool 中的數據會全部丟失的。這個時候 redo 日志文件就需要來大顯神通了
注：redo 日志文件是 InnoDB 特有的，他是存儲引擎級別的，不是 MySQL 級別的redo 記錄的是數據修改之后的值，不管事務是否提交都會記錄下來，例如，此時將要做的是update students set stuName='小強' where id=1; 那么這條操作就會被記錄到 redo log buffer 中，啥？怎么又出來一個 redo log buffer ,很簡單，MySQL 為了提高效率，所以將這些操作都先放在內存中去完成，然后會在某個時機將其持久化到磁盤中。
[外鏈圖片轉存失敗,源站可能有防盜鏈機制,建議將圖片保存下來直接上傳(img-if2HOTmj-1629818634040)(C:\Users\17996\AppData\Roaming\Typora\typora-user-images\1625380605315.png)]
截至目前，我們應該都熟悉了 MySQL 的執行器調用存儲引擎是怎么將一條 SQL 加載到緩沖池和記錄哪些日志的，流程如下：
上面說的步驟都是在正常情況下的操作，但是程序的設計和優化并不僅是為了這些正常情況而去做的，也是為了那些臨界區和極端情況下出現的問題去優化設計的
這個時候如果服務器宕機了，那么緩存中的數據還是丟失了。真煩，竟然數據總是丟失，那能不能不要放在內存中，直接保存到磁盤呢？很顯然不行，因為在上面也已經介紹了，在內存中的操作目的是為了提高效率。
此時，如果 MySQL 真的宕機了，那么沒關系的，因為 MySQL 會認為本次事務是失敗的，所以數據依舊是更新前的樣子，并不會有任何的影響。
好了，語句也更新好了那么需要將更新的值提交啊，也就是需要提交本次的事務了，因為只要事務成功提交了，才會將最后的變更保存到數據庫，在提交事務前仍然會具有相關的其他操作
將 redo Log Buffer 中的數據持久化到磁盤中，就是將 redo log buffer 中的數據寫入到 redo log 磁盤文件中，一般情況下，redo log Buffer 數據寫入磁盤的策略是立即刷入磁盤（具體策略情況在下面小總結出會詳細介紹）
[外鏈圖片轉存失敗,源站可能有防盜鏈機制,建議將圖片保存下來直接上傳(img-QBFN7sxV-1629818634042)(C:\Users\17996\AppData\Roaming\Typora\typora-user-images\1625380795909.png)]
如果 redo log Buffer 刷入磁盤后，數據庫服務器宕機了，那我們更新的數據怎么辦？此時數據是在內存中，數據豈不是丟失了？不，這次數據就不會丟失了，因為 redo log buffer 中的數據已經被寫入到磁盤了，已經被持久化了，就算數據庫宕機了，在下次重啟的時候 MySQL 也會將 redo 日志文件內容恢復到 Buffer Pool 中（這邊我的理解是和 Redis 的持久化機制是差不多的，在 Redis 啟動的時候會檢查 rdb 或者是 aof 或者是兩者都檢查，根據持久化的文件來將數據恢復到內存中）
到此為止，從【執行器開始調用存儲引擎接口】做了哪些事情呢？
1.準備更新一條 SQL 語句 2.MySQL（innodb）會先去緩沖池（BufferPool）中去查找這條數據，沒找到就會去磁盤中查找，如果查找到就會將這條數據加載到緩沖池（BufferPool）中 3.在加載到 Buffer Pool 的同時，會將這條數據的原始記錄保存到 undo 日志文件中 4.innodb 會在 Buffer Pool 中執行更新操作 5.更新后的數據會記錄在 redo log buffer 中 6.MySQL 提交事務的時候，會將 redo log buffer 中的數據寫入到 redo 日志文件中 刷磁盤可以通過 innodb_flush_log_at_trx_commit 參數來設置 值為 0 表示不刷入磁盤 值為 1 表示立即刷入磁盤 值為 2 表示先刷到 os cache 7.myslq 重啟的時候會將 redo 日志恢復到緩沖池中截止到目前位置，MySQL 的【執行器】調用【存儲引擎的接口】去執行【執行計劃】提供的 SQL 的時候 InnoDB 做了哪些事情也就基本差不多了，但是這還沒完。下面還需要介紹下 MySQL 級別的日志文件 bin log
bin log 日志文件：記錄整個操作過程
上面介紹到的**redo log是 InnoDB 存儲引擎特有的日志文件，而bin log屬于是 MySQL 級別的日志**。redo log記錄的東西是偏向于物理性質的，如：“對什么數據，做了什么修改”。bin log是偏向于邏輯性質的，類似于：“對 students 表中的 id 為 1 的記錄做了更新操作” 兩者的主要特點總結如下:
| 文件大小 | redo log 的大小是固定的（配置中也可以設置，一般默認的就足夠了） | bin log 可通過配置參數max_bin log_size設置每個bin log文件的大小（但是一般不建議修改）。 |
| 實現方式 | redo log是InnoDB引擎層實現的（也就是說是 Innodb 存儲引起過獨有的） | bin log是 MySQL 層實現的，所有引擎都可以使用 bin log日志 |
| 記錄方式 | redo log 采用循環寫的方式記錄，當寫到結尾時，會回到開頭循環寫日志。 | bin log 通過追加的方式記錄，當文件大小大于給定值后，后續的日志會記錄到新的文件上 |
| 使用場景 | redo log適用于崩潰恢復(crash-safe)（這一點其實非常類似與 Redis 的持久化特征） | bin log適用于主從復制和數據恢復 |
bin log文件是如何刷入磁盤的?
bin log 的刷盤是有相關的策略的，策略可以通過sync_bin log來修改，默認為 0，表示先寫入 os cache，也就是說在提交事務的時候，數據不會直接到磁盤中，這樣如果宕機bin log數據仍然會丟失。所以建議將sync_bin log設置為 1 表示直接將數據寫入到磁盤文件中。
刷入 bin log 有以下幾種【模式】
1、 STATMENT
基于 SQL 語句的復制(statement-based replication, SBR)，每一條會修改數據的 SQL 語句會記錄到 bin log 中
【優點】：不需要記錄每一行的變化，減少了 bin log 日志量，節約了 IO , 從而提高了性能
【缺點】：在某些情況下會導致主從數據不一致，比如執行sysdate()、slepp()等
2、ROW
基于行的復制(row-based replication, RBR)，不記錄每條SQL語句的上下文信息，僅需記錄哪條數據被修改了
【優點】：不會出現某些特定情況下的存儲過程、或 function、或 trigger 的調用和觸發無法被正確復制的問題
【缺點】：會產生大量的日志，尤其是 alter table 的時候會讓日志暴漲
3、MIXED
基于 STATMENT 和 ROW 兩種模式的混合復制( mixed-based replication, MBR )，一般的復制使用 STATEMENT 模式保存 bin log ，對于 STATEMENT 模式無法復制的操作使用 ROW 模式保存 bin log
那既然bin log也是日志文件，那它是在什么記錄數據的呢？
其實 MySQL 在提交事務的時候，不僅僅會將 redo log buffer 中的數據寫入到redo log 文件中，同時也會將本次修改的數據記錄到 bin log文件中，同時會將本次修改的bin log文件名和修改的內容在bin log中的位置記錄到redo log中，最后還會在redo log最后寫入 commit 標記，這樣就表示本次事務被成功的提交了。
[外鏈圖片轉存失敗,源站可能有防盜鏈機制,建議將圖片保存下來直接上傳(img-Qo6PV4lC-1629818634043)(C:\Users\17996\AppData\Roaming\Typora\typora-user-images\1625383328114.png)]
如果在數據被寫入到bin log文件的時候，剛寫完，數據庫宕機了，數據會丟失嗎？
首先可以確定的是，只要redo log最后沒有 commit 標記，說明本次的事務一定是失敗的。但是數據是沒有丟失了，因為已經被記錄到redo log的磁盤文件中了。在 MySQL 重啟的時候，就會將 redo log 中的數據恢復（加載）到Buffer Pool中。
好了，到目前為止，一個更新操作我們基本介紹得差不多，但是你有沒有感覺少了哪件事情還沒有做？是不是你也發現這個時候被更新記錄僅僅是在內存中執行的，哪怕是宕機又恢復了也僅僅是將更新后的記錄加載到Buffer Pool中，這個時候 MySQL 數據庫中的這條記錄依舊是舊值，也就是說內存中的數據在我們看來依舊是臟數據，那這個時候怎么辦呢？
其實 MySQL 會有一個【后臺線程】，它會在某個時機將我們Buffer Pool中的臟數據刷到 MySQL 數據庫中，這樣就將內存和數據庫的數據保持統一了。
[外鏈圖片轉存失敗,源站可能有防盜鏈機制,建議將圖片保存下來直接上傳(img-xaea71qR-1629818634044)(C:\Users\17996\AppData\Roaming\Typora\typora-user-images\1625384652041.png)]
到此，關于Buffer Pool、Redo Log Buffer 和undo log、redo log、bin log 概念以及關系就基本差不多了。
我們再回顧下
1. Buffer Pool 是 MySQL 的一個非常重要的組件，因為針對數據庫的增刪改操作都是在 Buffer Pool 中完成的 2. Undo log 記錄的是數據操作前的樣子 3. redo log 記錄的是數據被操作后的樣子（redo log 是 Innodb 存儲引擎特有） 4. bin log 記錄的是整個操作記錄（這個對于主從復制具有非常重要的意義）從準備更新一條數據到事務的提交的流程描述
1. 首先執行器根據 MySQL 的執行計劃來查詢數據，先是從緩存池中查詢數據，如果沒有就會去數據庫中查詢，如果查詢到了就將其放到緩存池中 2. 在數據被緩存到緩存池的同時，會寫入 undo log 日志文件 3. 更新的動作是在 BufferPool 中完成的，同時會將更新后的數據添加到 redo log buffer 中 4. 完成以后就可以提交事務，在提交的同時會做以下三件事 5. （第一件事）將redo log buffer中的數據刷入到 redo log 文件中 6. （第二件事）將本次操作記錄寫入到 bin log文件中 7. （第三件事）將 bin log 文件名字和更新內容在 bin log 中的位置記錄到redo log中，同時在 redo log 最后添加 commit 標記至此表示整個更新事務已經完成
文章原文：鏈接
binlog
binlog，即二進制日志,它記錄了數據庫上的所有改變，并以二進制的形式保存在磁盤中；它可以用來查看數據庫的變更歷史、數據庫增量備份和恢復、MySQL的復制（主從數據庫的復制）。
binlog有三種格式
1、【Statement】基于SQL語句的復制(statement-based replication,SBR)，每一條會修改數據的sql都會記錄在binlog中。
優點：不需要記錄每一行的變化，減少了binlog日志量，節約了IO，提高性能。 缺點：由于記錄的只是執行語句，為了這些語句能在slave上正確運行，因此還必須記錄每條語句在執行的時候的一些相關信息，以保證所有語句能在slave得到和在master端執行時候相同 的結果。另外mysql 的復制,像一些特定函數功能，slave可與master上要保持一致會有很多相關問題。 ps：相比row能節約多少性能與日志量，這個取決于應用的SQL情況，正常同一條記錄修改或者插入row格式所產生的日志量還小于Statement產生的日志量，但是考慮到如果帶條件的update操作，以及整表刪除，alter表等操作，ROW格式會產生大量日志，因此在考慮是否使用ROW格式日志時應該跟據應用的實際情況，其所產生的日志量會增加多少，以及帶來的IO性能問題。2、【Row】基于行的復制(row-based replication,RBR)，5.1.5版本的MySQL才開始支持row level的復制，它不記錄sql語句上下文相關信息，僅保存哪條記錄被修改。
優點： binlog中可以不記錄執行的sql語句的上下文相關的信息，僅需要記錄那一條記錄被修改成什么了。所以rowlevel的日志內容會非常清楚的記錄下每一行數據修改的細節。而且不會出現某些特定情況下的存儲過程，或function，以及trigger的調用和觸發無法被正確復制的問題. 缺點:所有的執行的語句當記錄到日志中的時候，都將以每行記錄的修改來記錄，這樣可能會產生大量的日志內容。 ps:新版本的MySQL中對row level模式也被做了優化，并不是所有的修改都會以row level來記錄，像遇到表結構變更的時候就會以statement模式來記錄，如果sql語句確實就是update或者delete等修改數據的語句，那么還是會記錄所有行的變更。3、【Mixed】混合模式復制(mixed-based replication,MBR)從5.1.8版本開始，MySQL提供了Mixed格式，實際上就是Statement與Row的結合。
在Mixed模式下，一般的語句修改使用statment格式保存binlog，如一些函數，statement無法完成主從復制的操作，則采用row格式保存binlog，MySQL會根據執行的每一條具體的sql語句來區分對待記錄的日志形式，也就是在Statement和Row之間選擇一種。查看是否開啟了binlog
mysql> show variables like 'log_%'; +---------------------------------+-------------+ | Variable_name | Value | +---------------------------------+-------------+ | log_bin | OFF | | log_bin_trust_function_creators | OFF | | log_error | .\mysql.err | | log_queries_not_using_indexes | OFF | | log_slave_updates | OFF | | log_slow_queries | ON | | log_warnings | 1 | +---------------------------------+-------------+mysql的配置文件my.ini添加如下配置：
# Binary Logging. log-bin=mysql-bin binlog-format=Row重啟MySQL服務之后驗證binlog是否開啟：
show variables like 'log_bin'; show binary logs;binlog文件的位置：如果在修改my.ini的binlog時給的是全路徑，那么生成的日志文件就在指定的目錄下；如果如以上步驟中只給一個名字，那么生成的binlog日志的位置為（windwos為例）：
C:\ProgramData\MySQL\MySQL Server 5.5\data服務重啟之后就會在指定目錄下產生mysql-bin.000001和mysql-bin.index文件。
查看指定binlog文件的內容
mysql> show binlog events in 'mysql-bin.000001';只查看第一個binlog文件的內容
mysql> show binlog events;查看帶有序號的binlog文件內容
mysql> show binlog events in 'mysql-bin.000002'; +------------------+-----+-------------+-----------+-------------+----------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+-----+-------------+-----------+-------------+----------------------+ | mysql-bin.000002 | 4 | Format_desc|1 |107 | Server ver: 5.5.58-log, Binlog ver: 4 | | mysql-bin.000002 |107| Query |1 | 175 | BEGIN | | mysql-bin.000002 |175| Query |1 |268 | use `test`; delete from bean where id = 10| | mysql-bin.000002 |268| Xid |1 |295 | COMMIT /* xid=4 */ | +------------------+-----+-------------+-----------+-------------+----------------------+ 4 rows in set (0.02 sec)1、當停止或重啟服務器時，服務器會把日志文件記入下一個日志文件，Mysql會在重啟時生成一個新的日志文件，文件序號遞增；
2、 如果日志文件超過max_binlog_size（默認值1G）系統變量配置的上限時，也會生成新的日志文件（在這里需要注意的是，如果你正使用大的事務，二進制日志還會超過max_binlog_size，不會生成新的日志文件，事務全寫入一個二進制日志中,這種情況主要是為了保證事務的完整性）
3、 日志被刷新時，新生成一個日志文件。
相關參考
1、https://blog.jcole.us/innodb/
2、MySQL的數據存在磁盤上到底長什么樣
3、阿里二面: 詳解一條 SQL 的執行過程：https://mp.weixin.qq.com/s?__biz=MzU4ODI1MjA3NQ==&mid=2247499508&idx=2&sn=446d53f109c058b4c389cbfe7b0c3c31&chksm=fddd2830caaaa12650240644d91aa32bbc0754c993e0bd236a520c79e41370126d2d4e7e90fb&scene=132#wechat_redirect
PostreSQL
常用命令
linux命令行直接運行pgsql命令
PGPASSWORD=password psql -h 127.0.0.1 -p 5432 -d basicframe -U basicframe -c "SELECT * FROM t_acc_user"shell腳本連接postgresql并操作數據庫
#!/bin/shsql="UPDATE t_acc_user SET status = 'normal'" PGPASSWORD=pgsqlpwd psql -h 127.0.0.1 -p 5432 -d basicframe -U basicframe -c "$sql"切換用戶
su postgres bash-4.2$ psql -d basicframe psql (9.3.6) Type "help" for help. basicframe=# select * from t_acc_master;分組后拼接多行
分組查詢結果做字段拼接
| 1 | 張三 |
| 2 | 李四 |
| 3 | 王五 |
| 1 | 張四 |
| 2 | 李五 |
| 2 | 李一 |
需求是根據name_type分組，李姓的在一組，張姓的分為一組，查詢結果如下：
| 1 | 張三,張四 |
| 2 | 李四,李五,李一 |
| 3 | 王五 |
MySQL可以很方便的利用group_concat函數來實現，但是postgres9.0版本之前沒有這樣的函數，需要進行自定義函數【參考博客】。我們可以用**【array_agg()】,【string_agg()】**等函數來實現。注意string_agg()方法參數都必須為字符串。
array_agg
select name_type,array_to_string(array_agg(name),',') as names from t_acc_user group by name_type;string_agg
select name_type, string_agg(name,',') as names from t_acc_user group by name_type;根據條件修改對應字段
-- t_acc_master_mapping:映射表 name_old：舊 name_new:新 UPDATE t_acc_master t01 SET name = t02.name_new FROM (SELECT * FROM t_acc_master_mapping) t02 WHERE t01.name = t02.name_old AND t01.status = 'normal';鎖表問題解決
在使用pgsql刪除數據庫表（DROP）操作時候出現阻塞的現象，由此懷疑是鎖表導致。
排查數據庫表是否鎖住
SELECT oid FROM pg_class WHERE relname = 't_user'; -- 可能鎖表的表名 SELECT pid FROM pg_locks WHERE relation = '2523'; -- 由上面查出的oid如果上面的SQL查詢到了結果，則表示該表被鎖，執行下面SQL釋放鎖定
SELECT pg_cancel_backend('100045'); -- 上面查到的pidctid的淺談
ctid： 表示數據記錄的物理行當信息，指的是 一條記錄位于哪個數據塊的哪個位移上面。 跟oracle中偽列 rowid 的意義一樣的，只是形式不一樣。例如這有個一表t_org_user，查看每行記錄的ctid情況：
SELECT ctid, * FROM t_org_user;查詢結果，格式(blockid,itemid)，拿其中(0,1)來說，0表示塊id，1表示在這塊第一條記錄。
ctid | id | name | cnname (0,1) 1 data01 測試01 (0,2) 2 data02 測試02 (0,3) 3 data01 測試01 (0,4) 1 data01 測試01ctid去重
我們知道rowid在oracle有個重要的作用；被用作表記錄去重；同理 ctid在postgresql里面同樣可以使用。例如t_org_user表id為1有兩條記錄
DELETE FROM t_org_user WHERE ctid NOT IN ( SELECT min( ctid ) FROM t_org_user GROUP BY id );根據id去重后
ctid | id | name | cnname (0,1) 1 data01 測試01 (0,2) 2 data02 測試02 (0,3) 3 data01 測試01剛剛我們刪除了(0,4)這條記錄，現在我們重新插入一條新的記錄之后查詢
ctid | id | name | cnname (0,1) 1 data01 測試01 (0,2) 2 data02 測試02 (0,3) 3 data01 測試01 (0,5) 1 data01 測試01為什么不是(0,4)，而是(0,5)？這個跟postgresql多版本事務有關，這是postgresql的特性，postgresql里面沒有回滾段的概念，那怎么把(0,5)在顯示呢？想這塊(0,5)的空間再存放數據，postgresql里面有AUTOVACUUM進程，當然我們也可以手動回收這段空間；
刪除（0,5）這條數據之后執行：
vacuum t_org_user;再次插入之后就是從(0,4)開始的，vacuum: 回收未顯示的物理位置；標明可以繼續使用。
select relpages,reltuples from pg_class where relname = 't_org_user';我們可以借助系統視圖pg_class，其中relpages，reltuples分別代表塊數，記錄數
generate_series序列函數
INSERT INTO t_org_user SELECT generate_series ( 1, 1000 ), 'data' || generate_series ( 1, 1000 ), '中文' || generate_series ( 1, 1000 );generate_series為一個序列函數，例如產生1-100就是generate_series(1,100)，產生0-100直接的偶數就是generate_series(0,100,2)，其中的0表示序列開始位置；100代表結束位置；2為偏移量。
未指定字段類型問題（type “unknown”）
SELECT (array_to_string(array_agg(t.cmdaudit),'#!')) as cmdaudit FROM ( SELECT 'test-new-xx' as cmdaudit from t_acc_master ) tpostgres數據庫較低版本（9.3.6）存在以下問題，這里的問題是'' as name實際上并沒有為值指定類型。這是unknown類型，而 PostgreSQL 通常從諸如您將其插入到哪個列或您將其傳遞給哪個函數之類的東西推斷出真正的類型。
Could not determine polymorphic type because input has type "unknown"類型化定義
TEXT '' AS nameCAST
CAST('' AS text) AS namePostgreSQL 簡寫
''::text案例
SELECT (array_to_string(array_agg(t.cmdaudit),'#!')) as cmdaudit FROM ( SELECT 'test-new-xx' ::text as cmdaudit from t_acc_master ) tSELECT (array_to_string(array_agg(t.cmdaudit),'#!')) as cmdaudit FROM ( SELECT VARCHAR 'test-new-xx' as cmdaudit from t_acc_master ) tSELECT (array_to_string(array_agg(t.cmdaudit),'#!')) as cmdaudit FROM ( SELECT TEXT 'test-new-xx' as cmdaudit from t_acc_master ) tSELECT (array_to_string(array_agg(t.cmdaudit),'#!')) as cmdaudit FROM ( SELECT CAST('test-new-xx' AS text) as cmdaudit from t_acc_master ) t自定義函數索引丟失
測試場景: t_auth_r_master_slave授權表 200w 行數據，masterId、resId有索引 ：
CREATE INDEX resid_index ON t_auth_r_master_slave (resid); CREATE INDEX masterid_index ON t_auth_r_master_slave (masterid);EXPLAIN ANALYZE進行分析
EXPLAIN ANALYZE SELECT * FROM t_auth_r_master_slave LIMIT 10 SELECT count(*) FROM t_auth_r_master_slave使用函數get_master_auth耗時11s
EXPLAIN ANALYZEselectt.id,t.ruleid,t.masterid,t.resid from get_master_auth('del') t where t.masterid = '1140131' and t.resid = '38100217' and t.slaveid is null;Function Scan on get_master_auth t (cost=0.25..15.25 rows=1 width=872) (actual time=9719.881..10632.622 rows=2 loops=1)Filter: (((masterid)::text = '1140131'::text) AND ((resid)::text = '38100217'::text))Rows Removed by Filter: 2463409 Total runtime: 10721.853 ms使用原始sql耗時0.01s
EXPLAIN ANALYZE selectt.id,t.ruleid,t.masterid,t.resid from (SELECT t.* FROM t_auth_r_master_slave tJOIN t_acc_master m ON m.id=t.masterid AND m.status !='del'JOIN t_auth_res r ON r.id=t.resid AND r.status !='del'JOIN t_acc_slave s ON s.id=t.slaveid and s.status != 'del'WHERE t.status!='del'UNION ALLSELECT t.* FROM t_auth_r_master_slave tJOIN t_acc_master m ON m.id=t.masterid AND m.status !='del'JOIN t_auth_res r ON r.id=t.resid AND r.status !='del'WHERE t.status!='del' and t.slaveid is null) t where t.masterid = '1' and t.resid = '1' and t.slaveid is null;匹配上索引:Bitmap Index Scan on resid_index…
Append (cost=749.97..1555.83 rows=2 width=35) (actual time=513.408..513.408 rows=0 loops=1)-> Subquery Scan on "*SELECT* 1" (cost=749.97..782.07 rows=1 width=35) (actual time=506.057..506.057 rows=0 loops=1)-> Nested Loop (cost=749.97..782.06 rows=1 width=1398) (actual time=506.055..506.055 rows=0 loops=1)-> Nested Loop (cost=749.68..773.74 rows=1 width=1398) (actual time=506.053..506.053 rows=0 loops=1)-> Nested Loop (cost=749.39..765.43 rows=1 width=1398) (actual time=506.052..506.052 rows=0 loops=1)-> Bitmap Heap Scan on t_auth_r_master_slave t (cost=749.11..757.12 rows=1 width=1398) (actual time=506.050..506.050 rows=0 loops=1)Recheck Cond: (((resid)::text = '38100217'::text) AND ((masterid)::text = '1140131'::text))Rows Removed by Index Recheck: 9Filter: ((slaveid IS NULL) AND ((status)::text <> 'del'::text))Rows Removed by Filter: 2-> BitmapAnd (cost=749.11..749.11 rows=2 width=0) (actual time=505.392..505.392 rows=0 loops=1)-> Bitmap Index Scan on resid_index (cost=0.00..14.54 rows=281 width=0) (actual time=12.929..12.929 rows=70 loops=1)Index Cond: ((resid)::text = '38100217'::text)-> Bitmap Index Scan on masterid_index (cost=0.00..734.32 rows=21586 width=0) (actual time=492.214..492.214 rows=22169 loops=1)Index Cond: ((masterid)::text = '1140131'::text)-> Index Scan using t_acc_master_pkey on t_acc_master m (cost=0.28..8.30 rows=1 width=7) (never executed)Index Cond: ((id)::text = '1140131'::text)Filter: ((status)::text <> 'del'::text)-> Index Scan using t_auth_res_pkey on t_auth_res r (cost=0.29..8.31 rows=1 width=9) (never executed)Index Cond: ((id)::text = '38100217'::text)Filter: ((status)::text <> 'del'::text)-> Index Scan using t_acc_slave_pkey on t_acc_slave s (cost=0.29..8.31 rows=1 width=9) (never executed)Index Cond: ((id)::text = (t.slaveid)::text)Filter: ((status)::text <> 'del'::text)-> Subquery Scan on "*SELECT* 2" (cost=749.68..773.75 rows=1 width=35) (actual time=7.348..7.348 rows=0 loops=1)-> Nested Loop (cost=749.68..773.74 rows=1 width=1398) (actual time=7.346..7.346 rows=0 loops=1)-> Nested Loop (cost=749.39..765.43 rows=1 width=1398) (actual time=7.345..7.345 rows=0 loops=1)-> Bitmap Heap Scan on t_auth_r_master_slave t_1 (cost=749.11..757.12 rows=1 width=1398) (actual time=7.343..7.343 rows=0 loops=1)Recheck Cond: (((resid)::text = '38100217'::text) AND ((masterid)::text = '1140131'::text))Rows Removed by Index Recheck: 9Filter: ((slaveid IS NULL) AND (slaveid IS NULL) AND ((status)::text <> 'del'::text))Rows Removed by Filter: 2-> BitmapAnd (cost=749.11..749.11 rows=2 width=0) (actual time=7.315..7.315 rows=0 loops=1)-> Bitmap Index Scan on resid_index (cost=0.00..14.54 rows=281 width=0) (actual time=0.075..0.075 rows=70 loops=1)Index Cond: ((resid)::text = '38100217'::text)-> Bitmap Index Scan on masterid_index (cost=0.00..734.32 rows=21586 width=0) (actual time=7.122..7.122 rows=22169 loops=1)Index Cond: ((masterid)::text = '1140131'::text)-> Index Scan using t_acc_master_pkey on t_acc_master m_1 (cost=0.28..8.30 rows=1 width=7) (never executed)Index Cond: ((id)::text = '1140131'::text)Filter: ((status)::text <> 'del'::text)-> Index Scan using t_auth_res_pkey on t_auth_res r_1 (cost=0.29..8.31 rows=1 width=9) (never executed)Index Cond: ((id)::text = '38100217'::text)Filter: ((status)::text <> 'del'::text) Total runtime: 514.501 mshttps://www.jb51.cc/postgresql/192456.html
https://blog.csdn.net/u011944141/article/details/98056440
相關參考
pgsql:https://www.cnblogs.com/lottu/category/535826.html
pgsql數據遷移：https://www.cnblogs.com/lottu/category/838299.html
pgsql高可用：https://www.cnblogs.com/lottu/category/841292.html
Oracle
總結
- 上一篇： javax.servlet-api 简介
- 下一篇： 通过java获取抖音用户主页信息（202