【面試必看】MySQL部分

MySQL

1. 基礎

關系型數據庫表關系

1. 什么是關系型數據庫？

一種建立在關系模型的基礎上的數據庫。關系模型表明了數據庫中所存儲的數據之間的聯系（一對一、一對多、多對多）。各種表中（比如用戶表），表中的每一行就存放著一條數據（比如一個用戶的信息）。

2. 什么是SQL？

一種結構化查詢語言，專門用來與數據庫打交道，目的是提供一種從數據庫中讀寫數據的簡單有效的方法。

3. MySQL的優點

一種關系型數據庫，主要用于持久化存儲我們的系統中的一些數據比如用戶信息，端口默認3306。

成熟穩定，功能完善，開源免費。

文檔豐富，既有詳細的官方文檔，又有非常多優質文章可供參考學習。

開箱即用，操作簡單，維護成本低。

兼容性好，支持常見的操作系統，支持多種開發語言。

社區活躍，生態完善。

事務支持優秀， InnoDB 存儲引擎默認使用 REPEATABLE-READ 并不會有任何性能損失，并且，InnoDB 實現的 REPEATABLE-READ 隔離級別其實是可以解決幻讀問題發生的。

支持分庫分表、讀寫分離、高可用。

4. 基礎架構

連接器： 身份認證和權限相關(登錄 MySQL 的時候)。
查詢緩存： 執行查詢語句的時候，會先查詢緩存（MySQL 8.0 版本后移除，因為這個功能不太實用）。
分析器： 沒有命中緩存的話，SQL 語句就會經過分析器，分析器說白了就是要先看你的 SQL 語句要干嘛，再檢查你的 SQL 語句語法是否正確。
優化器： 按照 MySQL 認為最優的方案去執行。
執行器： 執行語句，然后從存儲引擎返回數據。執行語句之前會先判斷是否有權限，如果沒有權限的話，就會報錯。

分層：

Server 層：主要包括連接器、查詢緩存、分析器、優化器、執行器等，所有跨存儲引擎的功能都在這一層實現，比如存儲過程、觸發器、視圖，函數等，還有一個通用的日志模塊 binlog 日志模塊。
存儲引擎：主要負責數據的存儲和讀取，采用可以替換的插件式架構，支持 InnoDB、MyISAM、Memory 等多個存儲引擎，其中 InnoDB 引擎有自有的日志模塊 redolog 模塊。

SQL執行：

查詢：權限校驗（如果命中緩存）--->查詢緩存--->分析器--->優化器--->權限校驗--->執行器--->引擎
更新：分析器---->權限校驗---->執行器--->引擎---redo log(prepare 狀態)--->binlog--->redo log(commit 狀態)

5. MySQL自增主鍵不一定連續

自增主鍵可以讓聚集索引盡量地保持遞增順序插入，避免了隨機查詢，從而提高了查詢效率。

場景：
1. 自增初始值 和自增步長設置不為 1
2. 唯一鍵沖突
3. 事務回滾
4. 批量插入（如 insert...select 語句）：并不知道到底需要申請多少 id，所以就采用了這種批量申請的策略

6. 數據庫設計通常分為幾步？

需求分析 : 分析用戶的需求，包括數據、功能和性能需求。
概念結構設計 : 主要采用 E-R 模型進行設計，包括畫 E-R 圖。
邏輯結構設計 : 通過將 E-R 圖轉換成表，實現從 E-R 模型到關系模型的轉換。
物理結構設計 : 主要是為所設計的數據庫選擇合適的存儲結構和存取路徑。
數據庫實施 : 包括編程、測試和試運行
數據庫的運行和維護 : 系統的運行與數據庫的日常維護。

2. 字段類型

數值（整、浮點、定點）、字符串（CHAR VARCHAR）、日期時間（DATETIME TIMESTAMP）

MySQL 常見字段類型總結

1. 整數類型的 UNSIGNED 屬性有什么用？

表示不允許負值的無符號整數。使用 UNSIGNED 屬性可以將正整數的上限提高一倍，因為它不需要存儲負數值。對于從 0 開始遞增的 ID 列，提供了更多的 ID 值可用。。

2. CHAR 和 VARCHAR 的區別是什么？

CHAR 是定長字符串，VARCHAR 是變長字符串。

CHAR 在存儲時會在右邊填充空格以達到指定的長度，檢索時會去掉空格； VARCHAR 在存儲時需要使用 1 或 2 個額外字節記錄字符串的長度，檢索時不需要處理。

CHAR 更適合存儲長度較短或者長度都差不多的字符串，例如 Bcrypt 算法、MD5 算法加密后的密碼、身份證號碼。VARCHAR 類型適合存儲長度不確定或者差異較大的字符串，例如用戶昵稱、文章標題等。

3. DECIMAL 和 FLOAT/DOUBLE 的區別是什么？

DECIMAL 是定點數，FLOAT/DOUBLE 是浮點數。DECIMAL 可以存儲精確的小數值，FLOAT/DOUBLE 只能存儲近似的小數值。

在 Java 中，MySQL 的 DECIMAL 類型對應的是 Java 類 java.math.BigDecimal。

4. 為什么不推薦使用 TEXT 和 BLOB？

TEXT 類型類似于 CHAR（0-255 字節）和 VARCHAR（0-65,535 字節），但可以存儲更長的字符串，即長文本數據，例如博客內容。

BLOB 類型主要用于存儲二進制大對象，例如圖片、音視頻等文件。

缺陷：

不能有默認值。
檢索效率較低。
不能直接創建索引，需要指定前綴長度。
可能會消耗大量的網絡和 IO 帶寬。
可能導致表上的 DML 操作變慢。

5. DATETIME 和 TIMESTAMP 的區別是什么？

DATETIME 類型沒有時區信息 8字節，TIMESTAMP 和時區有關 4字節。

DATETIME：1000-01-01 00:00:00 ~ 9999-12-31 23:59:59

Timestamp：1970-01-01 00:00:01 ~ 2037-12-31 23:59:59

6. NULL 和 '' 的區別是什么？

NULL 跟 ''(空字符串)是兩個完全不一樣的值：

NULL 代表一個不確定的值,就算是兩個 NULL,它倆也不一定相等。例如，SELECT NULL=NULL的結果為 false，但是在我們使用DISTINCT,GROUP BY,ORDER BY時,NULL又被認為是相等的。
''的長度是 0，是不占用空間的，而NULL 是需要占用空間的。
NULL 會影響聚合函數的結果。例如，SUM、AVG、MIN、MAX 等聚合函數會忽略 NULL 值。 COUNT 的處理方式取決于參數的類型。如果參數是 *(COUNT(*))，則會統計所有的記錄數，包括 NULL 值；如果參數是某個字段名(COUNT(列名))，則會忽略 NULL 值，只統計非空值的個數。
查詢 NULL 值時，必須使用 IS NULL 或 IS NOT NULLl 來判斷，而不能使用 =、!=、 <、> 之類的比較運算符。而''是可以使用這些比較運算符的。

7. Boolean 類型如何表示？

MySQL 中沒有專門的布爾類型，而是用 TINYINT(1) 類型來表示布爾值。TINYINT(1) 類型可以存儲 0 或 1，分別對應 false 或 true。

3. 存儲引擎

MySQL 當前默認的存儲引擎是 InnoDB。并且，所有的存儲引擎中只有 InnoDB 是事務性存儲引擎。

1. 存儲引擎架構是怎么樣的？

插件式架構，支持多種存儲引擎，我們甚至可以為不同的數據庫表設置不同的存儲引擎以適應不同場景的需要。存儲引擎是基于表的，而不是數據庫。

2. MyISAM 和 InnoDB 有什么區別？

InnoDB 支持行級別的鎖粒度，MyISAM 不支持，只支持表級別的鎖粒度。
MyISAM 不提供事務支持。InnoDB 提供事務支持，實現了 SQL 標準定義了四個隔離級別。
MyISAM 不支持外鍵，而 InnoDB 支持。
MyISAM 不支持 MVCC，而 InnoDB 支持。
雖然 MyISAM 引擎和 InnoDB 引擎都是使用 B+Tree 作為索引結構，但是兩者的實現方式不太一樣。
MyISAM 不支持數據庫異常崩潰后的安全恢復，而 InnoDB 支持。
InnoDB 的性能比 MyISAM 更強大。

4. 索引

1. 理解

索引是一種用于快速查詢和檢索數據的數據結構，其本質可以看成是一種排序好的數據結構。（目錄，B+樹）

優點：

使用索引可以大大加快數據的檢索速度（大大減少檢索的數據量）, 減少 IO 次數，這也是創建索引的最主要的原因。
通過創建唯一性索引，可以保證數據庫表中每一行數據的唯一性。

缺點：

創建索引和維護索引需要耗費許多時間。當對表中的數據進行增刪改的時候，如果數據有索引，那么索引也需要動態的修改，會降低 SQL 執行效率。
索引需要使用物理文件存儲，也會耗費一定空間。

大多數情況下，索引查詢都是比全表掃描要快的。但是如果數據庫的數據量不大，那么使用索引也不一定能夠帶來很大提升。

2. 索引底層數據結構選擇

1. Hash表

哈希表是鍵值對的集合，通過鍵(key)即可快速取出對應的值(value)，因此哈希表可以快速檢索數據（接近 O（1））。

key -> hash -> index -> value

hash = hashfunc(key)
index = hash % array_size

Hash 沖突 問題，也就是說多個不同的 key 最后得到的 index 相同。

解決：鏈地址 將哈希沖突數據存放在鏈表中，過長引入紅黑樹。一個好的哈希函數應該“均勻地”將數據分布在整個可能的哈希值集合中。

InnoDB 存儲引擎中存在一種特殊的“自適應哈希索引”，每個哈希桶實際上是一個小型的 B+Tree 結構，可以存儲多個鍵值對。

??不支持順序和范圍查詢。

2. 二叉查找樹 BST

平衡的時候，也就是樹的每個節點的左右子樹深度相差不超過 1 的時候，查詢的時間復雜度為 O(log2(N))，具有比較高的效率；最壞變成線性鏈表，時間復雜退化為 O（N）。

嚴重依賴其平衡程度。

3. 自平衡二叉查找樹 AVL樹

任何節點的左右子樹高度之差不超過 1，查找、插入和刪除在平均和最壞情況下的時間復雜度都是 O(logn)，采用旋轉操作來保持平衡。

AVL 樹

然而，需要頻繁地進行旋轉操作來保持平衡，較大的計算開銷進而降低了數據庫寫操作的性能；每次進行磁盤 IO 時只能讀取一個節點的數據，增加了磁盤 IO 操作的次數。

4. 紅黑樹

一種自平衡二叉查找樹，通過在插入和刪除節點時進行顏色變換和旋轉操作，使得樹始終保持平衡狀態。

紅黑樹

因為紅黑樹的平衡性相對較弱，可能會導致樹的高度較高，這可能會導致一些數據需要進行多次磁盤 IO 操作才能查詢到，這也是 MySQL 沒有選擇紅黑樹的主要原因。

5. B 樹& B+樹

多路平衡查找樹 ，B+樹與 B 樹相比，具備更少的 IO 次數、更穩定的查詢效率（任何查找都是從根節點到葉子節點的過程）和更適于范圍查詢（遍歷鏈表）這些優勢。

3.類型總結

數據結構：BTree 索引、哈希索引、全文索引
底層存儲：聚簇索引（索引結構和數據一起存放，InnoDB主鍵索引）、非聚簇索引（MyISAM）
應用：主鍵、普通、唯一、覆蓋、聯合、全文

1. 不同索引的組織結構

CREATE TABLE `student` (`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主鍵',`name` varchar(32) COLLATE utf8_bin NOT NULL COMMENT '名稱',`age` int(3) unsigned NOT NULL DEFAULT '1' COMMENT '年齡',PRIMARY KEY (`id`),KEY `I_name` (`name`)
) ENGINE=InnoDB;
?
INSERT INTO student (name, age) VALUES("小趙", 10),("小王", 11),("小李", 12),("小陳", 13);

每一個索引在 InnoDB 里面對應一棵B+樹，那么此時就存著兩棵B+樹。

區別在與葉子節點中，主鍵索引存儲了整行數據，而非主鍵索引中存儲的值為主鍵id。

SELECT age FROM student WHERE name = '小李'；

在name索引樹上找到名稱為小李的節點 id為03
從id索引樹上找到id為03的節點獲取所有數據
從數據中獲取字段命為age的值返回 12

從非主鍵索引樹搜索回到主鍵索引樹搜索的過程稱為回表。因為本次查詢中查詢結果只存在主鍵索引樹中，我們必須回表才能查詢到結果。

2. 覆蓋索引

從非主鍵索引中就能查到的記錄，而不需要查詢主鍵索引中的記錄，避免了回表的產生減少了樹的搜索次數，顯著提升性能。按鍵值的順序存儲，把隨機 IO 變成順序 IO 加快查詢效率，減少了IO次數。

ALTER TABLE student DROP INDEX I_name;
ALTER TABLE student ADD INDEX I_name_age(name, age);

流程變為：

在name,age聯合索引樹上找到名稱為小李的節點
此時節點索引里包含信息age 直接返回 12

把單列的非主鍵索引 修改為 多字段的聯合索引，在一棵索引樹上就找到了想要的數據, 不需要去主鍵索引樹上，再檢索一遍。（不用回表）

4. 主鍵索引

數據表的主鍵列使用的就是主鍵索引，Primary Key。

主鍵索引：加速查詢 + 列值唯一（不 NULL）+ 表中只有一個。
普通索引：僅加速查詢。
唯一索引：加速查詢 + 列值唯一（可 NULL）。

沒有顯示的指定表的主鍵時，InnoDB 會自動先檢查表中是否有唯一索引且不允許存在 null 值的字段，如果有，則選擇該字段為默認的主鍵，否則 InnoDB 將會自動創建一個 6Byte 的自增主鍵。

5. 二級索引

二級索引（Secondary Index）的葉子節點存儲的數據是主鍵的值，通過二級索引可以定位主鍵，二級索引又稱為輔助索引/非主鍵索引。（唯一、普通、前綴、全文）

6. 聚簇索引與非聚簇索引

聚簇索引和非聚簇索引

聚簇索引（聚集索引）

索引結構和數據一起存放的索引，并不是一種單獨的索引類型。InnoDB 中的主鍵索引就屬于聚簇索引。

在 MySQL 中，InnoDB 引擎的表的 .ibd文件就包含了該表的索引和數據，對于 InnoDB 引擎表來說，該表的索引(B+樹)的每個非葉子節點存儲索引，葉子節點存儲索引和索引對應的數據。

優點：

查詢速度非常快：聚簇索引的查詢速度非常的快，因為整個 B+樹本身就是一顆多叉平衡樹，葉子節點也都是有序的，定位到索引的節點，就相當于定位到了數據。相比于非聚簇索引，聚簇索引少了一次讀取數據的 IO 操作。
對排序查找和范圍查找優化：聚簇索引對于主鍵的排序查找和范圍查找速度非常快。

缺點：

依賴于有序的數據：因為 B+樹是多路平衡樹，如果索引的數據不是有序的，那么就需要在插入時排序，如果數據是整型還好，否則類似于字符串或 UUID 這種又長又難比較的數據，插入或查找的速度肯定比較慢。
更新代價大：如果對索引列的數據被修改時，那么對應的索引也將會被修改，而且聚簇索引的葉子節點還存放著數據，修改代價肯定是較大的，所以對于主鍵索引來說，主鍵一般都是不可被修改的。

非聚簇索引（非聚集索引）

索引結構和數據分開存放的索引，并不是一種單獨的索引類型。二級索引(輔助索引)就屬于非聚簇索引。MySQL 的 MyISAM 引擎，不管主鍵還是非主鍵，使用的都是非聚簇索引。

優點：

更新代價比聚簇索引要小。非聚簇索引的更新代價就沒有聚簇索引那么大了，非聚簇索引的葉子節點是不存放數據的。

缺點：

依賴于有序的數據:跟聚簇索引一樣，非聚簇索引也依賴于有序的數據
可能會二次查詢(回表):這應該是非聚簇索引最大的缺點了。當查到索引對應的指針或主鍵后，可能還需要根據指針或主鍵再到數據文件或表中查詢。

5. 事務

數據庫中途突然因為某些原因掛掉了。
客戶端突然因為網絡原因連接不上數據庫了。
并發訪問數據庫時，多個線程同時寫入數據庫，覆蓋了彼此的更改。

事務是邏輯上的一組操作，要么都執行，要么都不執行，ACID

1. 并發事務帶來的問題

臟讀 Dirty Read

B讀取A未提交的數據，且1回滾。

丟失修改 Lost to modify

A, B 都讀取并修改數據

丟失修改

不可重復讀 Unrepeatable read

B事務內兩次讀取結果不一樣（修改或者減少，delete update ），因為A修改了。

不可重復讀

幻讀 Phantom read

第一個事務就會發現多（ insert ）了一些原本不存在的記錄，就好像發生了幻覺一樣。

2. 并發事務的控制方法

鎖和 MVCC。鎖可以看作是悲觀控制的模式，多版本并發控制（MVCC，Multiversion concurrency control）可以看作是樂觀控制的模式。

鎖控制方式下會通過鎖來顯式控制共享資源而不是通過調度手段，MySQL 中主要是通過 讀寫鎖 來實現并發控制，只能做到 讀讀并行。根據根據鎖粒度的不同，又被分為 表級鎖(table-level locking) 和 行級鎖(row-level locking) 。

共享鎖（S 鎖）：又稱讀鎖，事務在讀取記錄的時候獲取共享鎖，允許多個事務同時獲取（鎖兼容）。
排他鎖（X 鎖）：又稱寫鎖/獨占鎖，事務在修改記錄的時候獲取排他鎖，不允許多個事務同時獲取。如果一個記錄已經被加了排他鎖，那其他事務不能再對這條記錄加任何類型的鎖（鎖不兼容）。

MVCC 是多版本并發控制方法，即對一份數據會存儲多個版本，通過事務的可見性來保證事務能看到自己應該看到的版本。通常會有一個全局的版本分配器來為每一行數據設置版本號，版本號是唯一的。

undo log : undo log 用于記錄某行數據的多個版本的數據。
read view 和 隱藏字段 : 用來判斷當前版本數據的可見性。

3. SQL 標準定義了哪些事務隔離級別?

READ-UNCOMMITTED(讀取未提交) ：最低的隔離級別，允許讀取尚未提交的數據變更，可能會導致臟讀、幻讀或不可重復讀。
READ-COMMITTED(讀取已提交) ：允許讀取并發事務已經提交的數據，可以阻止臟讀，但是幻讀或不可重復讀仍有可能發生。
REPEATABLE-READ(可重復讀) ：對同一字段的多次讀取結果都是一致的，除非數據是被本身事務自己所修改，可以阻止臟讀和不可重復讀，但幻讀仍有可能發生。
SERIALIZABLE(可串行化) ：最高的隔離級別，完全服從 ACID 的隔離級別。所有的事務依次逐個執行，這樣事務之間就完全不可能產生干擾，也就是說，該級別可以防止臟讀、不可重復讀以及幻讀。

隔離級別	臟讀	不可重復讀	幻讀
READ-UNCOMMITTED	√	√	√
READ-COMMITTED	×	√	√
REPEATABLE-READ	×	×	√
SERIALIZABLE	×	×	×

SERIALIZABLE 隔離級別是通過鎖來實現的。

InnoDB 存儲引擎的默認支持的隔離級別是 REPEATABLE-READ（可重讀）。

6. MySQL鎖

1. 表級鎖和行級鎖了解嗎？

MyISAM 僅僅支持表級鎖(table-level locking)，一鎖就鎖整張表，這在并發寫的情況下性非常差。 InnoDB 不光支持表級鎖(table-level locking)，還支持行級鎖(row-level locking)，默認為行級鎖。

行級鎖的粒度更小，僅對相關的記錄上鎖即可（對一行或者多行記錄加鎖），所以對于并發寫入操作來說， InnoDB 的性能更高。

表級鎖： MySQL 中鎖定粒度最大的一種鎖（全局鎖除外），是針對非索引字段加的鎖，對當前操作的整張表加鎖，實現簡單，資源消耗也比較少，加鎖快，不會出現死鎖。不過，觸發鎖沖突的概率最高，高并發下效率極低。表級鎖和存儲引擎無關，MyISAM 和 InnoDB 引擎都支持表級鎖。
行級鎖： MySQL 中鎖定粒度最小的一種鎖，是 針對索引字段加的鎖 ，只針對當前操作的行記錄進行加鎖。行級鎖能大大減少數據庫操作的沖突。其加鎖粒度最小，并發度高，但加鎖的開銷也最大，加鎖慢，會出現死鎖。行級鎖和存儲引擎有關，是在存儲引擎層面實現的。

2. InnoDB有哪幾類行鎖？

記錄鎖（Record Lock）：也被稱為記錄鎖，屬于單個行記錄上的鎖。
間隙鎖（Gap Lock）：鎖定一個范圍，不包括記錄本身。
臨鍵鎖（Next-Key Lock）：Record Lock+Gap Lock，鎖定一個范圍，包含記錄本身，主要目的是為了解決幻讀問題（MySQL 事務部分提到過）。記錄鎖只能鎖住已經存在的記錄，為了避免插入新記錄，需要依賴間隙鎖。

在 InnoDB 默認的隔離級別 REPEATABLE-READ 下，行鎖默認使用的是 Next-Key Lock。但是，如果操作的索引是唯一索引或主鍵，InnoDB 會對 Next-Key Lock 進行優化，將其降級為 Record Lock，即僅鎖住索引本身，而不是范圍。

3. 意向鎖有什么用？

意向鎖是一個表級鎖，其作用就是指明接下來的事務將會用到哪種鎖。由數據引擎自己維護的，用戶無法手動操作意向鎖，在為數據行加共享/排他鎖之前，InooDB 會先獲取該數據行所在數據表的對應意向鎖。

意向共享鎖（Intention Shared Lock，IS 鎖）：事務有意向對表中的某些記錄加共享鎖（S 鎖），加共享鎖前必須先取得該表的 IS 鎖，表明該事務將對該行進行加鎖操作。
意向排他鎖（Intention Exclusive Lock，IX 鎖）：事務有意向對表中的某些記錄加排他鎖（X 鎖），加排他鎖之前必須先取得該表的 IX 鎖。

4. 當前讀和快照讀有什么區別？

當前讀 （一致性鎖定讀）就是給行記錄加 X 鎖或 S 鎖。

快照讀（一致性非鎖定讀），如果讀取的記錄正在執行 UPDATE/DELETE 操作，讀取操作不會因此去等待記錄上 X 鎖的釋放，而是會去讀取行的一個快照（記錄的歷史版本），每行記錄可能存在多個歷史版本（MVCC 多版本技術）。

7. 性能優化

1. 直接MySQL存儲圖片會怎么樣？

直接存儲文件對應的二進制數據即可，但是會嚴重影響數據庫性能，消耗過多存儲空間。

建議使用云服務廠商提供的開箱即用的文件存儲服務（阿里云OSS），或者自己搭建文件存儲系統。

數據庫只存儲文件地址信息，文件的內容由文件存儲服務負責存儲。

2. 如何存儲IP地址？

將 IP 地址轉換成整形數據存儲，性能更好，占用空間也更小。

INET_ATON()：把 ip 轉為無符號整型 (4-8 位)
INET_NTOA() :把整型的 ip 轉為地址

3. 常見的數據庫優化方法？

索引優化
讀寫分離和分庫分表
數據冷熱分離
SQL 優化
深度分頁優化
適當冗余數據（通過預存儲某些計算結果或常用的數據組合，減少運行時的計算量，從而更快地響應用戶查詢）
使用更高的硬件配置

4. 讀寫分離

1. 理解

將對數據庫的讀寫操作分散到不同的數據庫節點上。這樣的話，就能夠小幅提升寫性能，大幅提升讀性能。（小寫大讀）處理的是 數據庫讀并發 問題。

讀寫分離示意圖

一主多從，也就是一臺主數據庫負責寫，其他的從數據庫負責讀。主庫和從庫之間會進行數據同步，以保證從庫中數據的準確性。這樣的架構實現起來比較簡單，并且也符合系統的寫少讀多的特點。

2. 實現

要求：

部署多臺數據庫，選擇其中的一臺作為主數據庫，其他的一臺或者多臺作為從數據庫。
保證主數據庫和從數據庫之間的數據實時同步的，這個過程也就是我們常說的主從復制。
系統將寫請求交給主數據庫處理，讀請求交給從數據庫處理。

軟件層面：

代理：在應用和數據中間加了一個代理層。應用程序所有的數據請求都交給代理層處理，代理層負責分離讀寫請求，將它們路由到對應的數據庫中。（例如 MySQL Router）

代理方式實現讀寫分離

組件： sharding-jdbc ，直接引入 jar 包即可使用，

3. 主從復制原理

MySQL binlog(binary log 即二進制日志文件) 主要記錄了 MySQL 數據庫中數據的所有變化(數據庫執行的所有 DDL 和 DML 語句)。因此，我們根據主庫的 MySQL binlog 日志就能夠將主庫的數據同步到從庫中。（還能幫助我們實現數據恢復。）

過程：

主庫將數據庫中數據的變化寫入到 binlog
從庫連接主庫
從庫會創建一個 I/O 線程向主庫請求更新的 binlog
主庫會創建一個 binlog dump 線程來發送 binlog ，從庫中的 I/O 線程負責接收
從庫的 I/O 線程將接收的 binlog 寫入到 relay log 中。
從庫的 SQL 線程讀取 relay log 同步數據到本地（也就是再執行一遍 SQL ）。

主寫從連，從主線程，從寫從同。

MySQL主從復制

4. 如何避免主從延遲？

主庫和從庫的數據存在延遲，比如你寫完主庫之后，主庫的數據同步到從庫是需要時間的，這個時間差就導致了主庫和從庫的數據不一致性問題。

解決：強制將讀請求路由到主庫處理。

Sharding-JDBC 的 HintManager 分片鍵值管理器，我們可以強制使用主庫。

HintManager hintManager = HintManager.getInstance();
hintManager.setMasterRouteOnly();
// 繼續JDBC操作

將那些必須獲取最新數據的讀請求都交給主庫處理。

5. 什么情況下會出現？如何盡量減少延遲？

MySQL 主從同步延時是指從庫的數據落后于主庫的數據，這種情況可能由以下兩個原因造成：
1. 從庫 I/O 線程接收 binlog 的速度跟不上主庫寫入 binlog 的速度，導致從庫 relay log 的數據滯后于主庫 binlog 的數據；(主寫從收)
2. 從庫 SQL 線程執行 relay log 的速度跟不上從庫 I/O 線程接收 binlog 的速度，導致從庫的數據滯后于從庫 relay log 的數據。（從收從行）

與主從同步有關的時間點主要有 3 個：
1. 主庫執行完一個事務，寫入 binlog，將這個時刻記為 T1；
2. 從庫 I/O 線程接收到 binlog 并寫入 relay log 的時刻記為 T2；
3. 從庫 SQL 線程讀取 relay log 同步數據本地的時刻記為 T3。

可以得出：
1. T2 和 T1 的差值反映了從庫 I/O 線程的性能和網絡傳輸的效率，這個差值越小說明從庫 I/O 線程的性能和網絡傳輸效率越高。
2. T3 和 T2 的差值反映了從庫 SQL 線程執行的速度，這個差值越小，說明從庫 SQL 線程執行速度越快。

什么情況下會發生？
1. 從庫機器性能比主庫差：從庫進行性能優化（或者換一個更好的），比如調整參數、增加緩存、使用 SSD 等。
2. 從庫處理的讀請求過多：引入緩存（推薦）、使用一主多從的架構。
3. 大事務：避免大批量修改數據，盡量分批進行。
4. 從庫太多：減少從庫的數量，或者將從庫分為不同的層級（分級）。
5. 網絡延遲：優化網絡環境，比如提升帶寬、降低延遲、增加穩定性等。
6. 單線程復制：多線程復制。
7. 復制模式：MySQL默認的復制是異步的，使用半同步復制。

5. 分庫分表

1. 理解

解決 MySQL 的存儲壓力。（如果 MySQL 一張表的數據量過大怎么辦?）

2. 分庫

將數據庫中的數據分散到不同的數據庫上。

垂直：單一數據庫按照業務進行劃分，不同的業務使用不同的數據庫，進而將一個數據庫的壓力分擔到多個數據庫。

水平：同一個表按一定規則拆分到不同的數據庫中，每個庫可以位于不同的服務器上，這樣就實現了水平擴展，解決了單表的存儲和性能瓶頸的問題。

水平分庫

3. 分表

對單表的數據進行拆分。

垂直：把一張列比較多的表拆分為多張表。
水平：把一張行比較多的表拆分為多張表，可以解決單一表數據量過大的問題。水平分表通常和水平分庫同時出現。

4. 什么情況下？

單表的數據達到千萬級別以上，數據庫讀寫速度比較緩慢（體量）。
數據庫中的數據占用的空間越來越大，備份時間越來越長（增長）。
應用的并發量太大（應該優先考慮其他性能優化方法，而非分庫分表）。

但是，分庫分表的成本太高，如非必要盡量不要采用。

5. 常見的分片算法？

數據被水平分片之后，數據究竟該存放在哪個表的問題。

哈希：使每個表的數據分布相對均勻，適合隨機讀寫的場景。
一致性哈希：將哈希空間組織成一個環形結構，解決了傳統哈希對動態伸縮不友好的問題。
范圍：進行范圍查找且數據分布均勻。
映射表：存儲分片鍵和分片位置的對應關系，維護額外的表。
地理位置
融合算法：靈活組合多種分片算法，比如將哈希分片和范圍分片組合。

6. 分片鍵如何選擇？

數據分片的關鍵字段，影響數據的分布和查詢效率。分片鍵可以是表中多個字段的組合。例如將訂單表中的訂單主鍵的尾數取模分片，則訂單主鍵為分片鍵。

共性，即能夠覆蓋絕大多數的查詢場景，盡量減少單次查詢所涉及的分片數量，降低數據庫壓力；
離散性，即能夠將數據均勻地分散到各個分片上，避免數據傾斜和熱點問題；
穩定性，即分片鍵的值不變，避免數據遷移和一致性問題；
擴展性，即能夠支持分片的動態增加和減少，避免數據重新分片的開銷。

7. 帶來什么問題？

join：同一個數據庫中的表分布在了不同的數據庫中，導致無法使用 join 操作，需要多次查詢業務層進行數據組裝的方法。（join效率低，且影響分庫分表操作，不推薦）
分布式事務：單個操作涉及到多個數據庫，那么數據庫自帶的事務就無法滿足我們的要求了，引入分布式事務。
分布式ID：數據遍布在不同服務器上的數據庫，數據庫的自增主鍵已經沒辦法滿足生成的主鍵唯一了。
跨庫聚合查詢：導致常規聚合查詢操作，如 group by，order by 等變得異常復雜，在多個分片上進行數據匯總和排序。使用中間件來協調分片間的通信和數據傳輸。

8. 推薦方案？

Apache ShardingSphere 是一款分布式的數據庫生態系統，可以將任意數據庫轉換為分布式數據庫，并通過數據分片、彈性伸縮、加密等能力對原有數據庫進行增強。除了支持讀寫分離和分庫分表，還提供分布式事務、數據庫治理、影子庫、數據加密和脫敏等功能。

9. 怎么數據遷移？

如何將老庫（單庫單表）的數據遷移到新庫（分庫分表后的數據庫系統）呢？

停機遷移：寫一個腳本將老庫的數據都同步到新庫中。
雙寫方案：老庫更新寫入新庫，比對數據少插多刪，重復直到新老數據一致。

我們對老庫的更新操作（增刪改），同時也要寫入新庫（雙寫）。如果操作的數據不存在于新庫的話，需要插入到新庫中。這樣就能保證，咱們新庫里的數據是最新的。

我們還需要自己寫腳本將老庫中的數據和新庫的數據做比對。如果新庫中沒有，那咱們就把數據插入到新庫。如果新庫有，舊庫沒有，就把新庫對應的數據刪除（冗余數據清理）。

重復上一步的操作，直到老庫和新庫的數據一致為止。

10. 總結

讀寫分離主要是為了將對數據庫的讀寫操作分散到不同的數據庫節點上。這樣的話，就能夠小幅提升寫性能，大幅提升讀性能。
讀寫分離基于主從復制，MySQL 主從復制是依賴于 binlog 。
分庫就是將數據庫中的數據分散到不同的數據庫上。分表就是對單表的數據進行拆分，可以是垂直拆分，也可以是水平拆分。
引入分庫分表之后，需要系統解決事務、分布式 id、無法 join 操作問題。
現在很多公司都是用的類似于 TiDB 這種分布式關系型數據庫，不需要我們手動進行分庫分表（數據庫層面已經幫我們做了），也不需要解決手動分庫分表引入的各種問題，直接一步到位，內置很多實用的功能（如無感擴容和縮容、冷熱存儲分離）！如果公司條件允許的話，個人也是比較推薦這種方式！
如果必須要手動分庫分表的話，ShardingSphere 是首選！ShardingSphere 的功能完善，除了支持讀寫分離和分庫分表，還提供分布式事務、數據庫治理等功能。另外，ShardingSphere 的生態體系完善，社區活躍，文檔完善，更新和發布比較頻繁。

6. 深度分頁如何優化？

查詢偏移量過大的場景我們稱為深度分頁，這會導致查詢性能較低。

# MySQL 在無法利用索引的情況下跳過1000000條記錄后，再獲取10條記錄
SELECT * FROM t_order ORDER BY id LIMIT 1000000, 10

范圍查詢：保證 ID 的連續性時，根據 ID 范圍進行分頁。

# 查詢指定 ID 范圍的數據
SELECT * FROM t_order WHERE id > 100000 AND id <= 100010 ORDER BY id
# 也可以通過記錄上次查詢結果的最后一條記錄的ID進行下一頁的查詢：
SELECT * FROM t_order WHERE id > 100000 LIMIT 10

子查詢：先查詢出 limit 第一個參數對應的主鍵值，再根據這個主鍵值再去過濾并 limit。

# 通過子查詢來獲取 id 的起始值，把 limit 1000000 的條件轉移到子查詢
SELECT * FROM t_order WHERE id >= (SELECT id FROM t_order limit 1000000, 1) LIMIT 10;

子查詢的結果會產生一張新表，會影響性能，應該盡量避免大量使用子查詢。

延遲關聯：通過使用覆蓋索引查詢返回需要的主鍵，再根據主鍵關聯原表獲得需要的數據。

SELECT t1.* FROM t_order t1
INNER JOIN (SELECT id FROM t_order limit 1000000, 10) t2
ON t1.id = t2.id;

減少大量的回表查詢，大大的提升了sql查詢效率。

7. 數據冷熱分離

1. 理解

指根據數據的訪問頻率和業務重要性，將數據分為冷數據和熱數據，冷數據一般存儲在存儲在低成本、低性能的介質中，熱數據高性能存儲介質中。對數據進行分類，然后分開存儲。

劃分：
1. 時間維度：按照數據的創建時間、更新時間、過期時間等，將一定時間段內的數據視為熱數據，超過該時間段的數據視為冷數據。例如，訂單系統可以將 1 年前的訂單數據作為冷數據，1 年內的訂單數據作為熱數據。這種方法適用于數據的訪問頻率和時間有較強的相關性的場景。
2. 訪問頻率：將高頻訪問的數據視為熱數據，低頻訪問的數據視為冷數據。例如，內容系統可以將瀏覽量非常低的文章作為冷數據，瀏覽量較高的文章作為熱數據。這種方法需要記錄數據的訪問頻率，成本較高，適合訪問頻率和數據本身有較強的相關性的場景。

優點：熱數據的查詢性能得到優化（用戶的絕大部分操作體驗會更好）、節約成本（可以冷熱數據的不同存儲需求，選擇對應的數據庫類型和硬件配置，比如將熱數據放在 SSD 上，將冷數據放在 HDD 上）。

缺點：系統復雜性和風險增加（需要分離冷熱數據，數據錯誤的風險增加）、統計效率低（統計的時候可能需要用到冷庫的數據）。

2. 冷數據如何遷移？

業務層代碼實現：當有對數據進行寫操作時，觸發冷熱分離的邏輯，判斷數據是冷數據還是熱數據，冷數據就入冷庫，熱數據就入熱庫。這種方案會影響性能且冷熱數據的判斷邏輯不太好確定，還需要修改業務層代碼，因此一般不會使用。（不推薦）
任務調度：可以利用 xxl-job 或者其他分布式任務調度平臺定時去掃描數據庫，找出滿足冷數據條件的數據，然后批量地將其復制到冷庫中，并從熱庫中刪除。這種方法修改的代碼非常少，非常適合按照時間區分冷熱數據的場景。
監聽數據庫的變更日志 binlog ：將滿足冷數據條件的數據從 binlog 中提取出來，然后復制到冷庫中，并從熱庫中刪除。這種方法可以不用修改代碼，但不適合時間維度區分冷熱數據的場景。
讓 DBA 進行冷數據的人工遷移，一次遷移完成冷數據到冷庫。

3. 冷數據如何存儲？

容量大，成本低，可靠性高，訪問速度可以適當犧牲。

Hbase（常用）、RocksDB、Doris、Cassandra

使用 TiDB 6.0 的數據放置功能，可以在同一個集群實現海量數據的冷熱存儲，將新的熱數據存入 SSD，歷史冷數據存入 HDD。

8. 三大日志

主要包括錯誤日志、查詢日志、慢查詢日志、事務日志、二進制日志幾大類。

重要：二進制日志 binlog（歸檔日志）、事務日志 redolog（重做日志）、undolog（回滾日志）。

redo log 是 InnoDB 引擎特有的。

1. redo log 重做日志

作用：確保事務的持久性。防止在發生故障的時間點，尚有臟頁未寫入磁盤，在重啟 mysql 服務的時候，根據 redo log 進行重做，從而達到事務的持久性這一特性。
內容：物理日志，記錄的是物理數據頁面的修改的信息，其 redo log 是順序寫入 redo log file 的物理文件中去的。

2. bin log 歸檔日志（二進制日志）

作用：用于復制，在主從復制中，從庫利用主庫上的 binlog 進行重播，實現主從同步。用于數據庫的基于時間點的還原。只要發生了表數據更新，都會產生 binlog 日志，順序寫入。保證數據的一致性。
內容：邏輯日志，可以簡單認為就是執行過的事務中的 sql 語句。但又不完全是 sql 語句這么簡單，而是包括了執行的 sql 語句（增刪改）反向的信息，也就意味著 delete 對應著 delete 本身和其反向的 insert；update 對應著 update 執行前后的版本的信息；insert 對應著 delete 和 insert 本身的信息。

　　binlog 有三種模式：Statement（基于 SQL 語句的復制）、Row（基于行的復制）以及 Mixed（混合模式）

3. undo log 回滾日志

作用：保存了事務發生之前的數據的一個版本，可以用于回滾版本，同時可以提供多版本并發控制下的讀（MVCC），也即非鎖定讀。
內容：邏輯日志，在執行 undo 的時候，僅僅是將數據從邏輯上恢復至事務之前的狀態，而不是從物理頁面上操作實現的，這一點是不同于 redo log 的。

MVCC 的實現依賴于：隱藏字段、Read View、undo log

4. 兩段提交

同時使用了 redo log 和 binlog，那么就需要保證這兩種日志之間的一致性。否則，在數據庫發生異常重啟或者主從切換時，可能會出現數據不一致（ 數據庫的狀態就有可能和用它的日志恢復出來的庫的狀態不一致）的情況。

先寫 redo log 直接提交，然后寫 binlog，假設寫完 redo log 后，機器掛了，binlog 日志沒有被寫入，那么機器重啟后，這臺機器會通過 redo log 恢復數據，但是這個時候 binlog 并沒有記錄該數據，后續進行機器備份的時候，就會丟失這一條數據，同時主從同步也會丟失這一條數據。
先寫 binlog，然后寫 redo log，假設寫完了 binlog，機器異常重啟了，由于沒有 redo log，本機是無法恢復這一條記錄的，但是 binlog 又有記錄，那么和上面同樣的道理，就會產生數據不一致的情況。

以基本的事務為單位，redo log 在事務執行過程中可以不斷寫入，而 binlog 只有在提交時才寫入。

所以，將 redo log 的寫入拆成了兩個步驟prepare和commit。

在準備階段，MySQL先將數據修改寫入redo log，并將其標記為prepare狀態（事務還未提交）。然后將對應的SQL語句寫入bin log。
在提交階段，MySQL將redo log標記為commit狀態（事務已經提交）。然后根據sync_binlog參數的設置，決定是否將bin log刷入磁盤。