表姐告訴我學習數據庫這一篇就差不離了

大家好，我是captain，歡迎大家關注

?

正如大家所預料，接下來我們針對數據庫的知識點進行全方位的分析，其實數據庫應該是程序員的必備技能了，也是大家最最常用的了，大家不可能在開發(fā)中不會用到數據庫吧，那我有必要懷疑你是個假程序員

這一系列其實我很早之前就想寫了，但是也只是處于一個想的階段，一個構思，思考如何才能更好的把這一系列寫好，其實吧，本來吧我這人寫作就不太擅長，不過感覺現在比之前好多了，最起碼也可以和大家瞎聊聊了

?

我也會堅持寫下去，雖然現在的粉絲并不多，但是我寫這個的初衷也是為了記錄自己的學習知識點，我相信每一個程序員對于龐大的后端知識體系都不可能是手到擒來，都需要進行一段時間的回憶和思考，所以呢，正是由于這個，我就產生了寫文章的這個想法，把自己對于知識點的匯總和理解記錄下來，如果后續(xù)遇到一些不清楚的，可以利用自己的文章迅速的撿起來

?

所以呢，大家也可以在之前的rocketmq系列文章也可以看出來，我會自己記錄并且轉發(fā)一些自己認為比較好的文章，為的就是以后用到該知識點可以手到擒來！

?

數據庫呢，大家用的最多的肯定就是mysql了，當然，我們接下來要分析的知識點也是圍繞mysql來分析

1、數據庫三大范式

?

2、mysql常用存儲引擎

?

3、樹結構和索引類型

?

4、查詢性能優(yōu)化、explain、索引優(yōu)化

?

5、事務、鎖類型

?

6、常用日志類型

?

7、MVCC多版本并發(fā)控制

?

8、分庫分表策略

?

9、JSON

?

10、主從復制、讀寫分離

?01?數據庫三大范式

數據庫三大范式

?

第一范式：確保每一列的原子性，也就是表中的每個字段都是不可分解的原子值，這個屬于最基本的范式

?

第一范式的合理遵循需要根據系統(tǒng)來設定，比如有些系統(tǒng)中的地址屬性，在系統(tǒng)中的出現永遠都是整個地址屬性，那就屬于滿足原子性，滿足第一范式。

?

但是，如果如果我們系統(tǒng)中可能會經常用到地址中的省、市、區(qū)，而且還可能經常單獨出現其中某一個屬性，那這就屬于不滿足第一范式了

?

所以，是否滿足第一范式不能單純的按照我們的日常習俗去斷定，需要根據系統(tǒng)的設定來決定是否真正滿足第一范式

?

第二范式：確保表中的每一列都和主鍵相關，而不能只和主鍵中的其中一部分相關，主要是針對聯(lián)合主鍵

?

也就是一個數據庫表中，只能保存一種數據，不可能把多種數據保存到同一張數據表中，比如要統(tǒng)計一個地區(qū)的多個學校的學生，因為地區(qū)有多個學校，每個學校中的學生有相應的編號，所以要把學校ID和學生編號設計成一個聯(lián)合主鍵

?

第三范式：確保每一列和主鍵都是直接相關，而不是間接相關

?

再說上面的例子，我們可以通過學校ID作為外鍵和學校教師表建立相應的關系，而不能直接把學校教師相關信息存儲到學生表，這樣就屬于違反了第三范式了

?02?mysql常用存儲引擎

存儲引擎

?

存儲引擎其實就是屬于對數據庫文件的一種存取的方式，如何實現底層的存儲數據，以及如何為存儲的數據建立索引

?

存儲引擎屬于表級別，這一點可千萬要知道，難道你沒見過在建表的時候讓你選擇該表的存儲引擎的嗎

?

mysql常用存儲引擎有四種：MyISAM存儲引擎、innoDB存儲引擎、MEMORY存儲引擎、ARCHIVE存儲引擎

// 查看mysql所支持的存儲引擎，以及從中得到mysql默認的存儲引擎show?engines;?// 查看mysql 默認的存儲引擎show?variables?like?'%?storage_engine';? // 查看具體某一個表所使用的存儲引擎，這個默認存儲引擎被修改了！show create table tablename ;//準確查看某個數據庫中的某一表所使用的存儲引擎show?table?status?from?database?where?name="tablename"?

MyISAM存儲引擎

?

不支持事務，不支持行級鎖，只能對整張表進行加鎖，只支持并發(fā)插入的表鎖，主要用于高負載的select，讀取的時候對需要讀的表加共享鎖，寫入時則對表加排他鎖

?

在表有讀取操作的同時，也支持往表中插入新的記錄，這是并發(fā)插入

?

底層結構也是B+樹，但是屬于非聚簇索引，也就是說只要查詢的數據不止主鍵列，就需要記性回表查詢，這一特性和innodb引擎還是不一樣的，innodb采用的是聚簇索引，這兩個概念會在下面介紹，大家先簡單了解下

?

強調了快速讀取操作，占用空間小，處理速度快，表支持三種不同類型的存儲結構：靜態(tài)型、動態(tài)型和壓縮型

?

靜態(tài)型：表的列的大小固定，自動使用靜態(tài)的存儲，性能比較高，因為在維護和訪問一預定格式存儲數據時需要的開銷低，但是這種高性能是用空間為代價換來的，因為定義的時候固定，不管列中的值有多大，都會以最大值為準，占據整個空間

?

動態(tài)型：如果列定義是動態(tài)的，就會自動使用動態(tài)存儲，雖然動態(tài)型的表占用了較少的空間，但是會帶來性能的降低，因為某個字段內容發(fā)生改變其位置就需要移動，可能導致內存碎片的產生

?

對于內存碎片一般兩種解決方案：

?

1、盡量使用靜態(tài)數據類型

?

2、經常使用optimize table table_name語句整理表的碎片，恢復由于表數據的更新和刪除導致的空間丟失

?

壓縮型：如果在數據庫中創(chuàng)建在整個生命周期內只讀的表，則應該使用MyISAM的壓縮型表來減少空間的占用。

?

innoDB存儲引擎

?

mysql的默認存儲引擎，支持事務，回滾以及系統(tǒng)崩潰的修復能力

?

底層存儲索引也是使用B+樹，和上面不同的是使用的聚簇索引，減少了回表這一操作，可以直接通過主鍵找到整條數據，因此性能較高

?

innodb內部優(yōu)化，在磁盤讀取數據的時候采用的可預測性讀，能夠在內存中創(chuàng)建hash索引用來加速讀操作，以及能夠加速插入操作的插入緩沖區(qū)

?

支持自增長列，自增長列的值不能為空，支持外鍵

?

采用MVCC多版本并發(fā)控制支持高并發(fā)，而且實現了四種隔離級別，每一種隔離級別對應著該等級存在的問題，可以根據業(yè)務場景選擇合適的隔離級別

MEMORY存儲引擎

?

在內存中創(chuàng)建表，所有數據也是存在在內存中

?

每個基于內存存儲引擎的表都實際對應一個磁盤文件，文件名和表名是相同的，類型為.frm，該文件只存儲表的結構，而數據文件都是存儲在內存中，有利于對數據的快速處理，提高整個表的處理能力

默認使用hash索引，速度比B+樹快，缺點就是對于范圍查詢不友好咯，這個下面會對數據結構分析一波，大家就會明白B+樹索引和hash索引的區(qū)別

?

如果進程發(fā)生異常重啟或者關閉機器，這些數據都會消失，所以該存儲引擎中的表的數據的生命周期都很短，一般也只使用一次

?

ARCHIVE存儲引擎

?

該存儲引擎非常適合存儲大量獨立的、作為歷史記錄的數據。區(qū)別于InnoDB和MyISAM這兩種引擎，ARCHIVE提供了壓縮功能，擁有高效的插入速度，但是這種引擎不支持索引，所以查詢性能較差一些

?03?樹結構和索引類型

二叉樹

?

N個有限元素的集合組成，集合為空的時候，則是一個空的二叉樹

?

樹中的節(jié)點的度不大于2的有序樹，二叉樹的每個節(jié)點最多有兩顆字數，且有左右之分

左子樹元素小于父節(jié)點元素，右子樹節(jié)點元素大于父節(jié)點元素，次序不能顛倒

?

即使某個節(jié)點只有一個子樹，也要區(qū)分左子樹和右子樹

?

優(yōu)點：有序，可以更快的查詢到相應元素

?

缺點：如果選取根節(jié)點不準確，則容易出現左右子樹的高度偏差情況，極限情況下，可能會出現鏈式的結構，下面AVL樹也是為了解決這個情況而出現的

? ? ? ? ? ? ? ? ? ? ? ? ? ??

AVL二叉平衡樹

?

是一個空樹或者左右子樹的高度差的絕對值不超1，并且左右兩個子樹也都是一顆平衡二叉樹，為的就是解決上述說的退化成鏈表的情況

?

插入、查找和刪除的時間復雜度的最好情況和最壞情況都維持在O(logN)，但是最好不要用于修改數據比較多的情況下，一般用于修改少查詢多的場景

?

紅黑樹

它一種特殊的二叉查找樹。

紅黑樹的每個節(jié)點上都有存儲位表示節(jié)點的顏色，可以是紅(Red)或黑(Black)。

?

相較于嚴格要求平衡的AVL樹，它的旋轉的保持平衡次數較少，適合查找少和修改次數較多的場景

?

紅黑樹的特性:

?

（1）每個節(jié)點或者是黑色，或者是紅色。

（2）根節(jié)點是黑色。

（3）每個葉子節(jié)點（NIL）是黑色。這里葉子節(jié)點，是指為空(NIL或NULL)的葉子節(jié)點

（4）如果一個節(jié)點是紅色的，則它的子節(jié)點必須是黑色的。

（5）從一個節(jié)點到該節(jié)點的子孫節(jié)點的所有路徑上包含相同數目的黑節(jié)點。

?

葉子節(jié)點，是只為空(NIL或null)的節(jié)點

確保沒有一條路徑會比其他路徑長出倆倍，因而，紅黑樹是相對是接近平衡的二叉樹

?

B樹

?

上面說到的都是屬于兩個子節(jié)點，這種結構如果用于文件系統(tǒng)有一個很大的缺點，就是樹的高度會比較高，會使文件系統(tǒng)多次的進行磁盤IO，效率極其低

?

多路查找樹，體態(tài)矮胖，可以更少的進行磁盤IO，想象一下，樹的每一層代表一次磁盤IO

?

描述一棵B樹時需要指定它的階數，階數表示了一個節(jié)點最多有多少的孩子節(jié)點，一般使用字母m表示階數。

當m取2時，就是我們常見的二叉搜索樹。

?

一棵m階的B數定義如下：

?

（1）每個節(jié)點最多有m-1個關鍵字

（2）根節(jié)點最少可以只有一個關鍵字

（3）非根節(jié)點至少有Math.ceil(m/2)-1個關鍵字

（4）每個節(jié)點的關鍵字都按照從小到大的順序排列，每個關鍵字的左子樹中的所有關鍵字都小于它，而右子樹中的所有關鍵字都大于它

（5）所有葉子節(jié)點都位于同一層，或者說根節(jié)點到每個葉子節(jié)點的路徑長度都相同

?

B+樹

?

B+樹是對B樹的變形，最大的區(qū)別是非葉子節(jié)點不保存數據，而只用于存儲索引，所有的數據都保存到葉子節(jié)點中

?

B樹是所有節(jié)點（包含葉子節(jié)點）組成了所有的數據，而B+樹是所有數據均存儲到葉子節(jié)點上

?

同時B+樹的所有葉子節(jié)點都有相鄰葉子節(jié)點的指針，也就是所有葉子節(jié)點組成了一個鏈表

關于數據結構，這里我給大家推薦一個網站，可以學習下各種數據結構，觀看數據組成原理：https://www.cs.usfca.edu/~galles/visualization/Algorithms.html

B樹和B+樹的對比

?

B+樹的磁盤IO更低，查詢效率更高

?

B+樹的非葉子節(jié)點并不會存儲整條數據，而是存儲數據的索引，這句話是針對于MySQL表來說，因此非葉子節(jié)點可以用同樣的存儲空間，存儲更多的索引數據，也就使得B+樹更加的矮胖，可以一次性讀入內存中的關鍵字也就越多，磁盤的IO次數也就降低了，查詢效率也就更高

?

查詢效率更加穩(wěn)定

?

非葉子節(jié)點并不是最終指向文件內容的節(jié)點，也就意味著如果需要獲取更多數據，都需要通過非葉子節(jié)點索引找到葉子節(jié)點，也就是任何關鍵字的查找都需要走一條從根節(jié)點到葉子節(jié)點的路徑，所以查詢效率也更加穩(wěn)定

?

B+樹遍歷效率更高

?

由于B+樹特有的結構，只需要遍歷所有葉子節(jié)點的數據便可以實現整棵樹的遍歷

?

B+樹更好的支持范圍查詢

?

范圍查詢在現在系統(tǒng)中是必不可少的，B+樹的葉子節(jié)點都有相應的指針指向前后節(jié)點，組成鏈表，所以更好的支持范圍查詢。而B樹效率則會很低

上面說了這么多種樹，為的就是給大家理解mysql底層索引和數據的存儲結構跟上Captain的步伐，繼續(xù)沖

MySQL索引

?

主鍵索引

?

主鍵索引，不允許null，這個是底層構建B+樹的依據，可以提高查詢效率，并提供唯一性約束，一張表中只能有一個主鍵，被標志為自動增長的字段一定是主鍵，但是主鍵并不一定自動增長，一般把主鍵定義在無意義的字段上，主鍵的數據類型也最好是數值

?

B+樹的構建就是根據主鍵索引來構建，如果我們未指定主鍵，MySQL會自動創(chuàng)建一個列來作為主鍵索引

?

普通索引

?

最普通的索引，沒有任何限制，該唯一索引指向的是主鍵索引，通過唯一索引找到主鍵索引，然后去主鍵索引構建的B+樹中回表查詢具體數據，如果只需要主鍵字段，則不需要回表即可滿足條件

?

唯一索引

?

特性就是唯一，可以為null，可以把唯一性約束放在一個或者多個列上，這些列或列的組合必須有唯一的。但是，唯一性約束所在的列并不是表的主鍵列。

?

唯一性約束強制在指定的列上創(chuàng)建一個唯一性索引。在默認情況下，創(chuàng)建唯一性的非聚簇索引，但是，也可以指定所創(chuàng)建的索引是聚簇索引。

?

存在唯一鍵沖突的時候的避免策略

?

insert ignore

?

會忽略數據庫中已經存在的數據(根據主鍵或者唯一索引判斷)，如果數據庫沒有數據，就插入新的數據，如果有數據的話就跳過這條數據.

?

replace into

?

首先嘗試插入數據到表中。如果發(fā)現表中已經有此行數據(根據主鍵或者唯一索引判斷)則先刪除此行數據，然后插入新的數據，否則，直接插入新數據。使用replace into，你必須具有delete和insert權限

?

insert on duplicate key update

?

如果在insert into 語句末尾指定了on duplicate key update，并且插入行后會導致在一個UNIQUE索引或PRIMARY KEY中出現重復值，則在出現重復值的行執(zhí)行UPDATE；如果不會導致重復的問題，則插入新行，跟普通的insert into一樣。使用insert into，你必須具有insert和update權限

?

如果有新記錄被插入，則受影響行的值顯示1；如果原有的記錄被更新，則受影響行的值顯示2；如果記錄被更新前后值是一樣的，則受影響行數的值顯示0

?

全文索引

?

fulltext索引跟其它索引大不相同，它更像是一個搜索引擎，而不是簡單的where語句的參數匹配。fulltext索引配合match against操作使用，而不是一般的where語句加like

?

MySQL可以通過建立全文索引，利用查詢關鍵字和查詢列內容之間的相關度進行檢索，可以利用全文索引來提高匹配的速度。比如實現全匹配模糊查詢。

?

mysql的全文索引性能非常不穩(wěn)定，不建議生產環(huán)境使用。需要使用全文檢索的地方，建議使用ES

?

空間索引

?

MyISAM 存儲引擎支持空間數據索引R樹，可以用于地理數據存儲。空間數據索引會從所有維度來索引數據，可以有效地使用任意維度來進行組合查詢。

?

組合索引

多個字段上創(chuàng)建的索引，只有在查詢條件中使用了創(chuàng)建索引時的第一個字段，索引才會被使用。使用組合索引時遵循最左前綴集合

?

Hash索引

?

hash結構存儲數據，查詢速度很快，也只能用于精確查找。缺點是范圍查詢效率很低，無法用于排序和分組

?

?InnoDB 存儲引擎有一個特殊的功能叫“自適應哈希索引”，當某個索引值被使用的非常頻繁時，會在 B+Tree 索引之上再創(chuàng)建一個哈希索引，這樣就讓 B+Tree 索引具有哈希索引的一些優(yōu)點，比如快速的哈希查找。所以自適應哈希是起到一個輔助作用

?

索引的優(yōu)缺點

?

大大加速數據的檢索速度

?

減少磁盤IO，最根本也是提高了速度

?

創(chuàng)建和維護索引需要耗費時間，表中的數據進行增加、修改和刪除的時候，索引也需要動態(tài)維護

?

索引需要占據額外的存儲空間，所以這并不意味著索引越多越好，有些小表可能進行全表掃描速度更快，因為使用索引需要進行回表查詢，就是先通過索引找到主鍵索引，再通過主鍵索引去構建的B+樹中去查找整條數據

?04?查詢性能優(yōu)化、索引優(yōu)化、explain

查詢流程

（1）客服端發(fā)送一條查詢給服務器

?

（2）服務器先檢查查詢緩存，如果命中緩存，則立刻返回存儲在緩存中的結果。否則進入下一個階段。

?

（3）服務器端進行SQL解析、預處理，在由優(yōu)化器生成對應的執(zhí)行計劃。

?

（4）MySQL根據優(yōu)化器生成的執(zhí)行計劃，調用存儲引擎的API來執(zhí)行查詢

?

（5）將結果返回給客戶端

?

優(yōu)化數據訪問

?

查看是否請求了不需要的數據，是否請求了過量的數據或者重復請求數據

?

查詢到不需要的數據，可以用limit進行分割，我們需要對select?* 敏感，只返回自己需要用到的數據，在關聯(lián)表的數據的時候，盡量避免使用select?* 來查詢數據

?

重構查詢方式

?

在傳統(tǒng)實現中，總是強調需要數據庫完成盡可能多的工作。但是對于MySQL而言，這并不適用，MySQL從設計上讓連接和斷開連接都非常輕量級，在返回一個小的查詢結果方面很高效。

?

如果將一個復雜的查詢拆分成多個簡單查詢對應用更友好，能減少更多的工作，就不要害怕這樣做。

?

我們可以將一個大的查詢“分而治之”，將大查詢分成小查詢，每個小查詢 功能相同，只完成一小部分，每次只返回一小部分查詢結果。這樣可以減輕服務器負擔，避免一次性鎖住很多數據，占用過多事務日志等。

?

優(yōu)化特定類型查詢

?

Count()查詢

?

Count(*)查詢在沒有where條件的時候是非?？斓?，如果我們想要查詢某一列不為空的數據量，可以使用count(col)，而不是使用where條件過濾

?

使用近似值

如果某些業(yè)務場景不要求完全精確的COUNT值，此時可以用近似值來代替。EXPLAIN出來的優(yōu)化器估算的行數就是一個不錯的近似值，執(zhí)行EXPLAIN并不需要真正地去執(zhí)行查詢，所以成本很低

explain優(yōu)化分析

?

一、 id，SELECT識別符，這是SELECT的查詢序列號，我的理解是SQL執(zhí)行的順序的標識，SQL從大到小的執(zhí)行

?

id相同時，執(zhí)行順序由上至下。如果是子查詢，id的序號會遞增，id值越大優(yōu)先級越高，越先被執(zhí)行。

id如果相同，可以認為是一組，從上往下順序執(zhí)行；在所有組中，id值越大，優(yōu)先級越高，越先執(zhí)行

?

二、select_type，查詢中每個select子句的類型

?

(1) SIMPLE(簡單SELECT，不使用UNION或子查詢等)

?

(2) PRIMARY(子查詢中最外層查詢，查詢中若包含任何復雜的子部分，最外層的select被標記為PRIMARY)

?

(3) UNION(UNION中的第二個或后面的SELECT語句)

?

(4) DEPENDENT UNION(UNION中的第二個或后面的SELECT語句，取決于外面的查詢)

?

(5) UNION RESULT(UNION的結果，union語句中第二個select開始后面所有select)

?

(6) SUBQUERY(子查詢中的第一個SELECT，結果不依賴于外部查詢)

?

(7) DEPENDENT SUBQUERY(子查詢中的第一個SELECT，依賴于外部查詢)

?

(8) DERIVED(派生表的SELECT, FROM子句的子查詢)

?

(9) UNCACHEABLE SUBQUERY(一個子查詢的結果不能被緩存，必須重新評估外鏈接的第一行)

?

三、table，顯示這一步所訪問數據庫中表名稱（顯示這一行的數據是關于哪張表的），有時不是真實的表名字，可能是簡稱，例如上面的e，d，也可能是第幾步執(zhí)行的結果的簡稱

?

四、type，對表訪問方式，表示MySQL在表中找到所需行的方式，又稱“訪問類型”。

?

常用的類型有：ALL、index、range、 ref、eq_ref、const、system、NULL（從左到右，性能從差到好）

?

ALL：Full Table Scan， MySQL將遍歷全表以找到匹配的行

?

index: Full Index Scan，index與ALL區(qū)別為index類型只遍歷索引樹

?

range:只檢索給定范圍的行，使用一個索引來選擇行

?

ref: 表示上述表的連接匹配條件，即哪些列或常量被用于查找索引列上的值

?

eq_ref: 類似ref，區(qū)別就在使用的索引是唯一索引，對于每個索引鍵值，表中只有一條記錄匹配，簡單來說，就是多表連接中使用primary key或者 unique key作為關聯(lián)條件

?

const、system: 當MySQL對查詢某部分進行優(yōu)化，并轉換為一個常量時，使用這些類型訪問。如將主鍵置于where列表中，MySQL就能將該查詢轉換為一個常量，system是const類型的特例，當查詢的表只有一行的情況下，使用system

?

NULL: MySQL在優(yōu)化過程中分解語句，執(zhí)行時甚至不用訪問表或索引，例如從一個索引列里選取最小值可以通過單獨索引查找完成

?

五、possible_keys，指出MySQL能使用哪個索引在表中找到記錄，查詢涉及到的字段若存在索引，則該索引將被列出，但不一定被查詢使用（該查詢可以利用的索引，如果沒有任何索引顯示 null）

?

該列完全獨立于EXPLAIN輸出所示的表的次序。這意味著在possible_keys中的某些鍵實際上不能按生成的表次序使用

如果該列是NULL，則沒有相關的索引。在這種情況下，可以通過檢查WHERE子句看是否它引用某些列或適合索引的列來提高你的查詢性能。如果是這樣，創(chuàng)造一個適當的索引并且再次用EXPLAIN檢查查詢

?

六、Key，key列顯示MySQL實際決定使用的鍵（索引），必然包含在possible_keys中

?

如果沒有選擇索引，鍵是NULL。要想強制MySQL使用或忽視possible_keys列中的索引，在查詢中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

?

七、key_len，表示索引中使用的字節(jié)數，可通過該列計算查詢中使用的索引的長度（key_len顯示的值為索引字段的最大可能長度，并非實際使用長度，即key_len是根據表定義計算而得，不是通過表內檢索出的）

?

不損失精確性的情況下，長度越短越好

?

八、ref，列與索引的比較，表示上述表的連接匹配條件，即哪些列或常量被用于查找索引列上的值

?

九、rows，估算出結果集行數，表示MySQL根據表統(tǒng)計信息及索引選用情況，估算的找到所需的記錄所需要讀取的行數

?

十、Extra，該列包含MySQL解決查詢的詳細信息。有以下幾種情況：

?

Using where:不用讀取表中所有信息，僅通過索引就可以獲取所需數據，這發(fā)生在對表的全部的請求列都是同一個索引的部分的時候，表示mysql服務器將在存儲引擎檢索行后再進行過濾

?

Using temporary：表示MySQL需要使用臨時表來存儲結果集，常見于排序和分組查詢，常見 group by ，order by

?

Using filesort：當Query中包含 order by 操作，而且無法利用索引完成的排序操作稱為“文件排序”

?

Using join buffer：改值強調了在獲取連接條件時沒有使用索引，并且需要連接緩沖區(qū)來存儲中間結果。如果出現了這個值，那應該注意，根據查詢的具體情況可能需要添加索引來改進能。

?

Impossible where：這個值強調了where語句會導致沒有符合條件的行（通過收集統(tǒng)計信息不可能存在結果）。

?

Select tables optimized away：這個值意味著僅通過使用索引，優(yōu)化器可能僅從聚合函數結果中返回一行

?

No tables used：Query語句中使用from dual 或不含任何from子句

?

再說索引

?

使用聯(lián)合索引的時候，只有滿足最左匹配原則，才能夠使用索引

解釋一下，也就是把A、B、C作為組合索引的時候，則必須按照ABC的順序來使用索引，底層才可以使用索引

select * from table where A = 'a' and B = 'b' and C = 'c'

索引必須按照上面的順序使用，當然可以只使用A或者使用AB，但是一定要按照這個順序使用，這就是最左匹配原則，這里是因為底層B+樹存儲的時候就是按照ABC依次排序的，所以查找的時候也就只能按照這個順序使用才可以正確的使用索引

索引下推ICP，接著上面的聯(lián)合索引，我們看個語句select * froom table where A like 'java%' and B = 1，這個語句在未使用索引下推的情況下會通過索引A然后進行l(wèi)ike的查詢，查出數據來，然后根據主鍵回表查出數據看是否符合B=1的條件

而使用了索引下推下，則在判斷A的條件的時候就會直接把B的條件比對并且把不符合條件的過濾掉，而不需要回表查出數據再比對，減少了回表的次數，增加了效率

聚簇索引，指的就是索引和全部數據存在于一顆樹上的這種結構，切記，這是一種數據存儲方式，并不是一種索引，只是叫法是這樣而已，主鍵就是聚簇索引，默認一個表一個聚簇索引，也就是通過索引可以直接找到全部數據

非聚簇索引，指的就是索引和數據不在一起的這種存儲結構，普通索引都是非聚簇索引，這種索引查找到的是主鍵，也就是這種索引的B+樹的葉子節(jié)點存儲的都是主鍵索引的數據，然后如果需要查找主鍵之外的數據，則需要拿到主鍵之后再去主鍵構建的B+樹中進行回表查詢

回表，我們上面已經多次提到回表這個詞了，這個其實就是普通索引構建的數據無法滿足要求，這個時候就需要去主鍵構建的B+樹中去查詢所有數據，這就叫做回表

前綴索引，對于 BLOB、TEXT?和?VARCHAR?類型的列，必須使用前綴索引，只索引開始的部分字符。前綴長度的選取需要根據索引選擇性來確定。

覺得寫得還可以的，可以點贊關注走一波

?05?鎖類型和事務

先說一下mysql中的幾種語言，SQL語言共分為四大類

數據查詢語言DQL，數據操縱語言DML，數據定義語言DDL，數據控制語言DCL。

?

1. 數據查詢語言DQL：數據查詢語言DQL基本結構是由SELECT子句，FROM子句，WHERE

?

2 .數據操縱語言DML：數據操縱語言DML主要有三種形式，插入，更新，刪除。

?

3. 數據定義語言DDL：數據定義語言DDL用來創(chuàng)建數據庫中的各種對象如：表 視圖 索引 同義詞 簇。DDL操作是隱性提交的，不能rollback

?

4. 數據控制語言DCL：數據控制語言DCL用來授予或回收訪問數據庫的某種特權，并控制數據庫操縱事務發(fā)生的時間及效果，對數據庫實行監(jiān)視等。

重點來了

鎖

?

鎖是并發(fā)訪問同一個共享資源時的同步機制，Synchronized、ReentrantLock和ReentrantReadWriteLock都用過的吧，都是一樣的道理

?

MySQL的鎖是在服務器層或者存儲引擎層實現的，保證數據訪問的一致性和有序性

?

?

按模式分類為：樂觀鎖與悲觀鎖。

?

按粒度分可以分為全局鎖、表級鎖、頁級鎖、行級鎖。

?

按屬性可以分為：共享鎖、排它鎖。

?

按狀態(tài)分為：意向共享鎖、意向排它鎖。

?

按算法分為：間隙鎖、臨鍵鎖、記錄鎖。

樂觀鎖?

?

一種思想，樂觀鎖建設數據一般情況下不會產生沖突，在數據的操作過程中不會對數據做任何鎖定，只有當數據進行提交跟新的時候，才會正式對數據的沖突與否進行檢測

?

如果發(fā)生沖突了，則返回錯誤，調用者決定如何操作，是回滾還是重試

?

比較適用于讀多寫少的情況，如果寫場景比較多，寫沖突的可能性比較高，可能需要不斷重試，這樣會大大降低系統(tǒng)性能

?

這種可以通過增加一個數據版本字段Version來實現，讀取數據的時候把這個字段一起讀出來，數據每更新一次，對Version字段加一，當我們提交更新數據的時候，判斷數據庫表中的對應記錄的版本信息和第一次取出來的Version是否一致，一致則可以更新，不一致則過期

?

悲觀鎖

?

這個也是一種思想，悲觀的看法，認為每次去取數據的時候都會有別人去修改，所以在整個數據處理過程中，數據處于鎖定狀態(tài)

?

適用于并發(fā)量不大，寫入操作比較頻繁，數據一致性比較高的場景，MySQL中，共享鎖和排他鎖都是屬于悲觀鎖的不同實現

?

全局鎖

?

對整個數據庫實例進行加鎖，一般用于全庫的邏輯備份

?

MySQL 提供了一個加全局讀鎖的方法，命令是Flush tables with read lock (FTWRL)。使用這個命令之后，整個庫處于只讀狀態(tài)，其它線程的更新語句都會被阻塞

?

主庫備份，需要考慮影響業(yè)務系統(tǒng)，從庫備份，在備份期間不能執(zhí)行主庫同步過來的binlog，主從同步會有延遲

?

解決辦法，mysqldump使用參數--single-transaction，啟動一個事務，確保拿到一致性視圖。而由于MVCC的支持，這個過程中數據是可以正常更新的。

?

表級鎖

?

對操作的數據表加鎖，MyISAM和InnoDB引擎都支持表級鎖定，這里分為兩種，一種是表鎖，一種是元數據鎖，即meat?data lock，MDL鎖

lock?tables?表名?read?#該表可以讀，不能ddl?和?dml?中增刪改，只能讀取表數據lock tables 表名 write # 既不能讀，也不能寫

?

表鎖的語法是 lock tables … read/write。與 FTWRL 類似，可以用 unlock tables 主動釋放鎖，也可以在客戶端斷開的時候自動釋放。需要注意，lock tables 語法除了會限制別的線程的讀寫外，也限定了本線程接下來的操作對象。

?

MDL鎖

?

防止DDL和DML并發(fā)的沖突，你想啊，一個查詢正在遍歷表中數據，而執(zhí)行期間另一個線程對這個表結構進行了變更，刪除了一列，查詢線程拿到的結果跟表結構對不上，就亂套了

?

MDL鎖不是顯示的，MDL鎖是在5.5版本引入的

?

對一個表做增刪改查操作的時候，加MDL讀鎖，讀鎖之間不沖突，所以多個線程可以同時對一個表進行增刪改查

?

當要對表結構變更操作的時候，加MDL寫鎖，讀鎖和寫鎖、寫鎖和寫鎖都是沖突的，用來保證變更結構操作的安全性

?

兩個線程同時對一個表中加字段，其中一個要等另一個執(zhí)行完才可以開始。一個線程A先在查詢數據，另一個線程B想要加一列數據，需要等A線程執(zhí)行完才可以執(zhí)行線程B，就解決了上面的問題

?

MDL鎖是系統(tǒng)默認加的，我們理解了上面的機制之后，一定要注意MDL寫鎖之后的讀鎖和寫鎖都會阻塞，所以在給一些表加字段的時候一定要注意，盡量避開業(yè)務系統(tǒng)比較繁忙的時候

?

即使小表，操作不慎，如果一個表的查詢語句頻繁，而且客戶端有重試機制，也就是超時之后還會再起一個新session，庫的線程很容易就慢了，這時系統(tǒng)就崩了

?

千萬不要在長事務中對表結構進行修改，事務不提交會一直占用MDL寫鎖，那后面的語句就需要一直等待

?

頁級鎖

?

頁級鎖是 MySQL 中鎖定粒度介于行級鎖和表級鎖中間的一種鎖。表級鎖速度快，但沖突多，行級沖突少，但速度慢。因此，采取了折衷的頁級鎖，一次鎖定相鄰的一組記錄。BDB 引擎支持頁級鎖。

?

行級鎖

?

MySQL中只有InnoDB支持行級鎖，行級鎖分為共享鎖和排他鎖。

?

行級鎖是粒度最低的鎖，鎖沖突概率最低。但加鎖慢、開銷大，容易發(fā)生死鎖現象。

?

行鎖并不是直接鎖記錄，而是鎖索引

?

索引分為主鍵索引和非主鍵索引，一條sql語句操作了主鍵索引，MySQL就會鎖定這條主鍵索引；如果一條語句操作了非主鍵索引，MySQL會先鎖定該非主鍵索引，再鎖定相關的主鍵索引

?

共享鎖

?

共享鎖，也就是我們常說的讀鎖，一個事務對數據加上讀鎖之后，其它事務只能對該數據加讀鎖，不能做任何修改，不能加寫鎖

?

這樣可以更好的支持并發(fā)中的讀取數據，讀取數據的時候，不允許其他事物對當前數據進行修改操作，從而避免不可重復讀的問題的出現

select … lock in share mode

排它鎖

排它鎖，也就是寫鎖，當對數據加上寫鎖之后，其它事務不能對該數據讀寫，這個時候讀鎖和寫鎖都不可以加了，也就是全部阻塞了

?

寫鎖就是為了解決在數據修改的時候，不允許其它事務對當前數據進行修改和讀取操作，從而可以避免臟讀問題的產生

?

共享鎖可以避免不可重復讀的問題，排它鎖可以避免臟讀問題的產生

?

意向共享鎖和意向排它鎖

?

意向鎖的出現就是為了協(xié)調表鎖和行鎖，支持多粒度的并存

?

事務A有行鎖的時候，MySQL會自動給該表加上意向鎖，事務B如果想申請整個表的寫鎖，就不用遍歷去每一行判斷是否存在行鎖，只需要判斷是否存在意向鎖，即可決定是否可以加表的寫鎖

?

意向鎖的互斥性

當然，表格中的共享鎖和排他鎖都是表鎖，即表鎖和意向鎖的關系

?

意向鎖是不會和行級的共享排他鎖互斥的

?

給大家再解釋一下，就是有行級共享鎖，那就加上意向共享鎖，當需要加表級的共享鎖的時候，兼容，即行讀表讀共存；相反，表級的排他鎖加不上，也就是行讀表寫不共存

?

相應的行級的排他鎖，也就是寫鎖加上之后，表級的讀鎖和寫鎖都是不能加上的了，也就是行寫表既不可讀也不可寫

?

總結

?

行讀表讀共存

行讀表寫不共存

行寫既不可讀也不可寫????

?

記錄鎖

記錄鎖是封鎖記錄，記錄鎖也叫行鎖

間隙鎖

間隙鎖基于非唯一索引，它鎖定一段范圍內的索引記錄。使用間隙鎖鎖住的是一個區(qū)間，而不僅僅是這個區(qū)間中的每一條數據

?

臨鍵鎖

?

臨鍵鎖，是記錄鎖與間隙鎖的組合，它的封鎖范圍，既包含索引記錄，又包含索引區(qū)間，是一個左開右閉區(qū)間。臨鍵鎖的主要目的，也是為了避免幻讀(Phantom Read)。如果把事務的隔離級別降級為RC，臨鍵鎖則也會失效。

?

每個數據行上的非唯一索引列上都會存在一把臨鍵鎖，當某個事務持有該數據行的臨鍵鎖時，會鎖住一段左開右閉區(qū)間的數據。

需要強調的一點是，InnoDB 中行級鎖是基于索引實現的，臨鍵鎖只與非唯一索引列有關，在唯一索引列（包括主鍵列）上不存在臨鍵鎖。

事務

事務指的是一組SQL語句，要么全部執(zhí)行成功，要么全部執(zhí)行失敗，要么提交，要么回滾，這句話大家聽得耳朵都長繭子了吧

事務特性ACID

原子性：事務是最小單元，不可再分，要么全部執(zhí)行成功，要么全部失敗回滾。

?

一致性：一致性是指事務必須使數據庫從一個一致的狀態(tài)變到另外一個一致的狀態(tài)，也就是執(zhí)行事務之前和之后的狀態(tài)都必須處于一致的狀態(tài)。不一致性包含三點：臟讀，不可重復讀，幻讀

?

隔離性：隔離性是指當多個用戶并發(fā)訪問數據庫時，比如操作同一張表時，數據庫為每一個用戶開啟的事務，不能被其他事務的操作所干擾，多個并發(fā)事務之間要相互隔離

?

持久性：一旦事務提交，則其所做的修改將會永遠保存到數據庫中。即使系統(tǒng)發(fā)生崩潰，事務執(zhí)行的結果也不能丟。

事務隔離級別

未提交讀：即能夠讀取到沒有被提交的數據，所以很明顯這個級別的隔離機制無法解決臟讀、不可重復讀、幻讀中的任何一種。

?

已提交讀：即能夠讀到那些已經提交的數據，自然能夠防止臟讀，但是無法限制不可重復讀和幻讀

?

可重復讀：讀取了一條數據，這個事務不結束，別的事務就不可以改這條記錄，這樣就解決了臟讀、不可重復讀的問題，

?

串行化：多個事務時，只有運行完一個事務之后，才能運行其他事務。

隔離級別問題詳解

臟讀：一個事務處理過程里讀取了另一個未提交的事務中的數據

不可重復讀：一個事務在它運行期間，兩次查找相同的表，出現了不同的數據

幻讀：在一個事務中讀取到了別的事務插入的數據，導致前后不一致

和不可重復讀的區(qū)別，這里是新增，不可重復讀是更改（或刪除）。

這兩種情況對策是不一樣的，對于不可重復讀，只需要采取行級鎖防止該記錄數據被更改或刪除，然而對于幻讀必須加表級鎖，防止在這個表中新增一條數據。

再議鎖和事務問題

相信大家讀到這里，應該也大致對鎖和事務的關系有了更進一步的理解了吧，來，給大家捋一捋

共享鎖，也就是讀鎖，對一行數據加上共享鎖之后，別的事務就無法獲得該行數據的排他鎖了，別的事務也就暫時無法對這個數據進行修改操作了，也就避免了不可重復讀這個問題

排他鎖，也就是寫鎖，一個事務對數據進行修改的時候，就獲得相應數據的寫鎖，這時候別的事務也就無法獲得該數據的讀鎖和寫鎖了，也就避免了臟讀問題

臨鍵鎖的主要目的，也是為了避免幻讀(Phantom Read)。如果把事務的隔離級別降級為RC，臨鍵鎖則也會失效。

?06?日志類型

?

日志，就是記錄行為的文件，這個大家應該都很熟悉，無論在開發(fā)什么系統(tǒng)中，我們都離不了日志來排查我們的bug，如果沒了日志這一關鍵要素，那我們排查問題都無從下手了

mysql中有多種日志類型，有幾種日志屬于我們開發(fā)中需要熟悉的日志，無論是服務崩潰恢復數據，還是用來支持mysql的innodb的事務的原子性和持久性，這些都需要日志的支持，還有innodb所特有的MVCC多版本并發(fā)控制機制，來提高數據的并發(fā)性能

redo log重做日志

這一日志保證的是innodb引擎的事務的持久性，數據的寫入都是先寫入到緩存區(qū)中，并不是直接寫入到磁盤中，然后會順序存儲到redo log日志文件中，寫入的是會修改數據的增刪改語句，并不會寫入select語句，順序寫入速度很快

redo log的落盤并不是隨著事務的提交才寫入到文件中的，而是在事務的執(zhí)行過程中便開始寫入到redo log文件中了

redo log記錄的是物理級別上的頁修改，比如頁號1、偏移量20寫入了數據520

保證的即使如果系統(tǒng)發(fā)生故障，可以根據redo log的記錄事務的執(zhí)行狀態(tài)來恢復未寫入磁盤的數據，保證事務的持久性，當對應事務的臟頁寫入到磁盤之后，redo log的使命也就完成了，redo log占用的空間就可以被覆蓋了

很重要一點，redo log是什么時候寫盤的？前面說了是在事物開始之后逐步寫盤的。

之所以說重做日志是在事務開始之后逐步寫入重做日志文件，而不一定是事務提交才寫入重做日志緩存，原因就是，重做日志有一個緩存區(qū)Innodb_log_buffer，Innodb_log_buffer的默認大小為8M(這里設置的16M)，Innodb存儲引擎先將重做日志寫入innodb_log_buffer中

然后會通過以下三種方式將innodb日志緩沖區(qū)的日志刷新到磁盤

• Master Thread 每秒一次執(zhí)行刷新Innodb_log_buffer到重做日志文件。

• 每個事務提交時會將重做日志刷新到重做日志文件。

• 當重做日志緩存可用空間少于一半時，重做日志緩存被刷新到重做日志文件

由此可以看出，重做日志通過不止一種方式寫入到磁盤，尤其是對于第一種方式，Innodb_log_buffer到重做日志文件是Master Thread線程的定時任務。

因此重做日志的寫盤，并不一定是隨著事務的提交才寫入重做日志文件的，而是隨著事務的開始，逐步開始的。這一點是必須要知道的，因為這可以很好地解釋再大的事務的提交（commit）的時間也是很短暫的。

undo log回滾日志

保證事務的原子性，保存了事務發(fā)生之前的數據的一個版本，可以用于回滾，同時可以提高MVCC，下面會詳細說這個MVCC

存儲的是邏輯格式的日志，在執(zhí)行undo的時候，僅僅是把數據從弄個邏輯上恢復到事務之前的狀態(tài)，而不是從物理數據上實現的，這一點不同于redo log的存儲方式，存儲的是相反的SQL語句，比如執(zhí)行一個insert，我就存儲一個delete。執(zhí)行一個update，我就存儲一個相反的update，用于回滾數據

當事務提交之后，undo log并不能立馬被刪除，而是放入待清理的鏈表，由purge線程來判斷是否由其它事務在使用這個版本以及之前版本的信息，決定是否可以清理undo log中的日志空間，這塊版本鏈和MVCC機制有關

binlog二進制日志

這是屬于邏輯格式上的日志，可以簡單的理解為就是執(zhí)行過的事務中的SQL語句，但又不完全是SQL語句那么簡單，而是包括了執(zhí)行的增刪改的SQL語句和反向的信息，即delete對應著delete本身和反向的insert語句，update對應著執(zhí)行前后的版本信息，insert對應著delete和insert的語句

因此binlog可以做到類似閃回的功能，就是依賴于binlog日志文件的

事務提交的時候，一次性將事務中的SQL語句按照一定的格式記錄到binlog中，這里和redo log明顯的差異就是redo log并不是在事務提交的時候刷新到磁盤，redo log是在事務開始之后就已經逐步寫入到磁盤中了

因此開啟了binlog的情況下，對于比較大的事務的提交，可能會變得慢一些

可以用于數據庫的基于時間點的還原，還會用在主從復制中，從庫利用主庫的binlog進行重播，實現主從同步

二進制日志的作用之一是還原數據庫的，這與redo log很類似，很多人混淆過，但是兩者有本質的不同

作用不同：redo log是保證事務的持久性的，是事務層面的，binlog作為還原的功能，是數據庫層面的（當然也可以精確到事務層面的），雖然都有還原的意思，但是其保護數據的層次是不一樣的。

內容不同：redo log是物理日志，是數據頁面的修改之后的物理記錄，binlog是邏輯日志，可以簡單認為記錄的就是sql語句

機制不同：兩者日志產生的時間，可以釋放的時間，在可釋放的情況下清理機制，都是完全不同的。

效率不同：恢復數據時候的效率，基于物理日志的redo log恢復數據的效率要高于語句邏輯日志的binlog

關于事務提交時，redo log和binlog的寫入順序，為了保證主從復制時候的主從一致（當然也包括使用binlog進行基于時間點還原的情況），是要嚴格一致的，MySQL通過兩階段提交過程來完成事務的一致性的，也即redo log和binlog的一致性的，理論上是先寫redo log，再寫binlog，兩個日志都提交成功（刷入磁盤），事務才算真正的完成。

errorlog錯誤日志

錯誤日志記錄著mysqld啟動和停止,以及服務器在運行過程中發(fā)生的錯誤的相關信息。在默認情況下，系統(tǒng)記錄錯誤日志的功能是關閉的，錯誤信息被輸出到標準錯誤輸出。

slow query log慢查詢日志

慢日志記錄執(zhí)行時間過長和沒有使用索引的查詢語句，報錯select、update、delete以及insert語句，慢日志只會記錄執(zhí)行成功的語句。

1. 查看慢查詢時間：show variables like “l(fā)ong_query_time”;默認10s
2. 查看慢查詢配置情況：show?status?like?“%slow_queries%”;
3. 查看慢查詢日志路徑：show?variables?like?“%slow%”
4.?開啟慢日志set global slow_query_log=1
查看已經開啟Show?variables?‘slow_query_log%’

general log一般查詢日志

記錄了服務器接收到的每一個查詢或是命令，無論這些查詢或是命令是否正確甚至是否包含語法錯誤，general log 都會將其記錄下來 ，記錄的格式為 {Time ，Id ，Command，Argument }

也正因為mysql服務器需要不斷地記錄日志，開啟General log會產生不小的系統(tǒng)開銷

因此，Mysql默認是把General log關閉的

查看日志的存放方式：show?variables?like?'log_output'

relay log中繼日志

從服務器I/O線程將主服務器的二進制日志（也就是上面說的binlog）讀取過來記錄到從服務器本地文件，然后從服務器SQL線程會讀取relay-log日志的內容并應用到從服務器，從而使從服務器和主服務器的數據保持一致

說到底其實就是輔助完成主從復制的一個文件，master的binlog文件復制寫到slave中的中繼日志文件中，然后用來執(zhí)行，很好理解，具體細節(jié)我會單開一篇主從復制來說

?07?MVCC多版本并發(fā)控制

什么是MVCC

全稱Multi-Version Concurrency Control，多版本并發(fā)控制，屬于一種并發(fā)控制的手段，一般在數據庫管理系統(tǒng)中，實現對數據庫的并發(fā)訪問

數據庫就必然涉及到讀和寫的存在，讀寫就必然涉及到讀寫沖突，MVCC在mysql中的innodb引擎實現就是為了更好的解決讀寫沖突，提高數據庫的性能，做到即使有讀寫沖突的時候，也可以不用加鎖的方式，非阻塞方式來實現并發(fā)讀

最早的數據庫系統(tǒng)，只有讀讀之間可以并發(fā)，讀寫，寫讀，寫寫都要阻塞。引入多版本之后，只有寫寫之間相互阻塞，其他三種操作都可以并行，這樣大幅度提高了InnoDB的并發(fā)度

MVCC只在 READ COMMITTED 和 REPEATABLE READ 兩個隔離級別下工作。其他兩個隔離級別夠和MVCC不兼容, 因為 READ UNCOMMITTED 總是讀取最新的數據行, 而不是符合當前事務版本的數據行。而 SERIALIZABLE 則會對所有讀取的行都加鎖

MVCC屬于一種悲觀鎖的實現

當前讀和快照讀

當前讀：像select lock in share mode這是共享鎖，select for update , update , insert , delete都是屬于排他鎖，上面說的采用共享鎖和排他鎖的這種方式，都是屬于當前讀，當前讀就是讀取的記錄的最新版本，讀取的時候還會保證其他并發(fā)事務不會修改當前的記錄，會對當前的記錄進行加鎖，防止修改

快照讀：不加鎖的正常的select查詢都是屬于快照讀，也就是不加鎖的非阻塞讀。

當然，快照讀的前提是隔離級別不是串行級別，此時便會退化成當前讀，之所以出現快照讀的情況，是mysql中的innodb引擎基于提高并發(fā)性能的考慮，快照讀也就是基本多版本的并發(fā)控制，來更高效的解決讀和寫之間的沖突問題

根據業(yè)務場景來考慮可以接受的問題，避免了加鎖的操作，降低了開銷，既然是多版本并發(fā)控制，那么就要接受讀取到的并不一定是最新版本的歷史數據這一場景

實現

MVCC只是一個抽象概念，innodb實現這個靠的是三個隱式字段、undo log日志、Read View來實現的

三個隱式字段

?

數據庫在每行記錄中除了記錄我們自定義的那些字段之外，還有數據庫的隱藏的定義字段，DB_TRX_ID、DB_ROLL_PTR、DB_ROW_ID

?

DB_TRX_ID：最近修改事務ID，也會記錄創(chuàng)建這條記錄和最后一次修改這個記錄的事務ID

?

DB_ROLL_PTR：回滾指針，指向這條記錄的上一個版本，存儲在undo log日志中的Rollback?segment回滾段中

?

DB_ROW_ID：這個不是一定有，如果表沒有創(chuàng)建主鍵，innodb會自動以這列為主鍵，以這一列來創(chuàng)建B+樹，產生一個聚簇索引，也就是創(chuàng)建的其余索引的B+樹的葉子節(jié)點存儲的是這個主鍵

?

實際還有一個刪除 flag 隱藏字段, 既記錄被更新或刪除并不代表真的刪除，而是刪除 flag 變了

?

再說undo log日志

?

Undo?log日志分為兩種insert?undo log和update?undo log

?

Insert?undo log：這種是事務在insert新數據的時候產生的日志，只有在事務回滾的時候需要，所以在事務commit之后可以立即丟棄該日志

?

Update undo log：這個是在進行update或者delete而產生的日志，這個不僅是事務回滾的時候需要，在快照讀的時候也是需要的，也就是innodb的MVCC機制會用到歷史的數據，所以不能隨便刪除，需要等快照讀和事務回滾都不涉及到該日志的時候，這個日志才會被相應的線程統(tǒng)一清楚

?

Read View

?

這哥們的作用可以理解為生成的一個鏡像數據，記錄當時的情況

?

事務快照是用來存儲數據庫的事務運行情況。一個事務快照ReadView的創(chuàng)建過程可以概括為：

?

m_ids：一個數值列表，用于維護 Read View 生成時刻系統(tǒng)正活躍的事務ID列表

up_limit_id：是m_ids活躍事務ID中的最小的事務ID

low_limit_id：ReadView 生成時刻系統(tǒng)尚未分配的下一個事務ID ，也就是目前已出現過的事務ID 的最大值 + 1

?

可見性比較算法

?

當事務執(zhí)行快照讀的時候，對該記錄創(chuàng)建一個Read?View讀視圖，用于記錄此時的情景，把它比做條件用來判斷當前事務可以看到哪個版本的數據，到底是看到最新版本，還是看到指向undo?log日志中的歷史版本呢

?

我們來一起看可見性算法，來決定該版本是否可見

此圖來源于知乎，侵刪

https://www.zhihu.com/question/66320138/answer/241418502

算法的流程

?

1. 當行記錄的事務ID小于當前系統(tǒng)的最小活動id，就是可見的。

if?(trx_id?up_limit_id)?{return(TRUE);}

2. 當行記錄的事務ID大于當前系統(tǒng)的最大活動id，就是不可見的。

if?(trx_id?>=?view->low_limit_id)?{return(FALSE);}

3. 當行記錄的事務ID在活動范圍之中時，判斷是否在活動鏈表中，如果在就不可見，如果不在就是可見的。

?

這里我也別用那些官方語言給大家解釋了，我就舉個簡單的例子給大家解釋

滴滴滴，跟上思路，加油，就快結束了

?

M_ids：一個數值列表，用于維護 Read View 生成時刻系統(tǒng)正活躍的事務ID列表

up_limit_id：是m_ids活躍事務ID中的最小的事務ID

low_limit_id：ReadView 生成時刻系統(tǒng)尚未分配的下一個事務ID ，也就是目前已出現過的事務ID 的最大值 + 1

?

插入一個記錄，事務ID是10，此時版本鏈是10

執(zhí)行一個update操作，事務ID是20，此時版本鏈是20-10，commit

執(zhí)行一個update操作，事務ID是30，此時版本連是30-20-10，未Commit

執(zhí)行select，事務ID是40，生成一個ReadView，這是一個鏡像，此時可能已經有更多事務操作這條數據了，活躍列表是m_ids是[30]，最小事務up_limit_id也是30，最大事務low_limit_id是41

?

比較過程

?

按照這個ReadView的事務鏈30-20-10進行上述算法的比較，30不合適，因為在活躍事務中，20滿足條件，所以此時事務ID為40的讀取的就是ID為20更新的數據

?

事務ID30Commit，事務ID50執(zhí)行update，鏈變成了50-30-20-10，未提交

?

關鍵

此時事務ID為40的再次執(zhí)行了select操作，查詢了該記錄

?

如果事務隔離級別是已提交讀隔離級別，這時候會重新生成一個新的ReadView，那此時ReadView已經變了，活躍列表m_ids是[50]，最小事務up_limit_id也是50，最大事務low_limit_id是51，于是按照上述比較，30便符合條件了，所以此時讀出來的版本就是事務ID30的update數據了

?

如果事務隔離級別是可重復讀，此時不會生成新的ReadView，用的還是開始時候生成的，所以還是20符合條件

?

兩種隔離級別

?

我們上面說了MVCC只在READ COMMITTED 和 REPEATABLE READ 兩個隔離級別下工作，已提交讀和可重復讀的區(qū)別在于他們生成ReadView的策略不同

?

也就是說已提交讀隔離級別下的事務在每次查詢的開始都會生成一個獨立的ReadView,而可重復讀隔離級別則在第一次讀的時候生成一個ReadView，之后的讀都復用之前的ReadView。

?

我們根據名字也可以推斷，可重復讀，如果每次讀取的時候生成新的ReadView了，那符合條件的版本很可能就不一樣了，所以查出來的也就不一樣了，就不符合條件了，于是用的就是同一個ReadView

?08?分庫分表策略

?

當業(yè)務體量很大的時候，一個庫無法滿足數據量的需求，此時就需要多個庫來協(xié)同工作，此時如何將數據庫拆分便是一個值得思考的問題

垂直拆分

垂直拆分值得是把一張表的數據按照列分成多個表，通常是按照列的關系緊密程度來進行切分，也可以按照列的變動頻率來進行切分

比如把更新頻率低的列放到同一張表，把更新頻率高的列放到同一張表中，這樣也可以根據不同的更新頻率來采用不同的規(guī)則

水平切分

水平切分，這個其實也很好理解，就是等同于是把同一個表的數據的記錄拆分到多個結構相同的表中，其實就是把數據進行平均分散

這個大家應該很熟悉，在開發(fā)中經常遇到這類問題，基本就是屬于平均分配的原理，比如負載均衡，再比如哈希，就那意思吧

當一個數據不斷增加時，水平切分是必然的選擇，這樣可以將數據分布到不同的節(jié)點上，可以減輕單個數據庫的壓力

哈希取模是最基本的策略，理解起來也是比較簡單的，熟悉HashMap的這些應該都很好理解，可以使用一個單獨的數據庫來存儲映射關系，然后對這個數據庫做好備份策略

作為一個程序員，跑不了hash，還不懂哈希算法的小伙伴，趕緊去復習一波哈希

切分的問題

系統(tǒng)中的事務問題一定要用分布式事務的思維來解決，而不能用普通的事務的思維來解決了，因為一旦事務操作了多張表，這多張表大概率不在同一個庫中，我們必須做好萬全之策，分布式事務便可以解決

ID的唯一性問題，這種就是即使分庫分表，每個表中也會存在一個全局唯一的ID，可以為每一個分片指定一個ID范圍，如果后續(xù)ID范圍不夠了，可以通過擴容來解決。也可以使用分布式的ID生成器來生成全局唯一的ID

?09?JSON

?

json是一種常用的數據類型，這個大家應該都很熟悉

?

在MySQL數據庫中，5.7版本之前是沒有JSON這一數據類型的，需要依靠varchar或者text之類的數據類型來存儲，而在5.7版本之后，MySQL則直接支持了JSON這一數據類型的存儲結構

?

JSON列存儲的要么是NULL，要么必須是JSON格式數據，否則會報錯。JSON數據類型是沒有默認值的。

?

JSON格式優(yōu)點

?

保證了JSON數據類型的強校驗，JSON數據列會自動校驗存入此列的內容是否符合JSON格式，非正常格式則報錯，而varchar類型和text等類型本身是不存在這種機制的。

?

MySQL同時提供了一組操作JSON類型數據的內置函數。where條件過濾可以直接基本json中的部分字段

?

更優(yōu)化的存儲格式，存儲在JSON列中的JSON數據會被轉成內部特定的存儲格式，允許快速讀取。

這是JSON內置函數連接：https://dev.mysql.com/doc/refman/5.7/en/json-function-reference.html

?10?讀寫分離、主從復制

主從復制

?

MySQL主從復制涉及到三個線程，一個運行在主節(jié)點（log dump thread），其余兩個(I/O thread，?SQL thread)運行在從節(jié)點

?

主節(jié)點 binary log dump 線程

當從節(jié)點連接主節(jié)點時，主節(jié)點會創(chuàng)建一個log dump 線程，用于發(fā)送bin-log的內容。在讀取bin-log中的操作時，此線程會對主節(jié)點上的bin-log加鎖，當讀取完成，甚至在發(fā)動給從節(jié)點之前，鎖會被釋放。

?

?

從節(jié)點I/O線程

當從節(jié)點上執(zhí)行start slave命令之后，從節(jié)點會創(chuàng)建一個I/O線程用來連接主節(jié)點，請求主庫中更新的bin-log。I/O線程接收到主節(jié)點binlog dump 進程發(fā)來的更新之后，保存在本地relay-log中

?

?

從節(jié)點SQL線程

SQL線程負責讀取relay log中的內容，解析成具體的操作并執(zhí)行，最終保證主從數據的一致性

讀寫分離???

一般應用對數據庫而言都是讀多寫少，也就說對數據庫讀取數據的壓力比較大，有一個思路就是說采用數據庫集群的方案，基本的原理是讓主數據庫處理事務性查詢，而從數據庫處理SELECT查詢

?

?

?

優(yōu)點

?

1.主從復制，讀寫分離，增加冗余，提高可用性，當一臺數據庫服務器宕機后能通過調整另外一臺從庫來以最快的速度恢復服務。

?

2.對于讀操作為主的應用，使用讀寫分離是最好的場景，因為可以確保寫的服務器壓力更小，而讀又可以接受點時間上的延遲。

?

3.主從只負責各自的寫和讀，極大程度的緩解X鎖和S鎖爭用，從庫可配置myisam引擎，提升查詢性能以及節(jié)約系統(tǒng)開銷

?

4.從庫同步主庫的數據和主庫直接寫還是有區(qū)別的，通過主庫發(fā)送來的binlog恢復數據，但是，最重要區(qū)別在于主庫向從庫發(fā)送binlog是異步的，從庫恢復數據也是異步的

?

5.讀寫分離適用與讀遠大于寫的場景，如果只有一臺服務器，當select很多時，update和delete會被這些select訪問中的數據堵塞，等待select結束，并發(fā)性能不高。對于寫和讀比例相近的應用，應該部署雙主相互復制

?11?結束語

? ? ?? 如果覺得Captain寫的還不錯，對你有幫助，點波關注吧

Captain希望有一天能夠靠寫作養(yǎng)活自己，現在還在磨練，這個時間可能會持續(xù)很久，但是，請看我漂亮的堅持

感謝大家能夠做我最初的讀者和傳播者，請大家相信，只要你給我一份愛，我終究會還你們一頁情的。

Captain會持續(xù)更新技術文章，和生活中的暴躁文章，歡迎大家關注【Java賊船】，成為船長的學習小伙伴，和船長一起乘千里風、破萬里浪

哦對了，后續(xù)所有的文章都會更新到這里

https://github.com/DayuMM2021/Java