[TOC] > # 事務特征和隔離級別 - 數據庫事務的四個特征 ACID：原子性、一致性、隔離性、持久性 - 原子性 (Atomicity): 事務中的所有操作是一個不可分割的整體，要么全部成功，要么全部失敗并回滾 - 一致性 (Consistency): 事務執行前后，數據庫必須保持一致的狀態，所有數據應滿足約束和規則（如外鍵、唯一性等。 示例：如果下訂單時庫存不足，訂單創建失敗，數據庫中的庫存數據不會被破壞) - 隔離性 (Isolation):事務的中間操作在提交前，對其他事務是不可見的，不同事務之間互不干擾(通過隔離級別設置) - 持久性 (Durability): 一旦事務提交，其對數據庫的修改將永久保存，即使系統崩潰或斷電也不會丟失。 | 隔離級別 | 臟讀 | 不可重復讀 | 幻讀 | | --- | --- | --- | --- | | 未提交讀 | 會 | 會 | 會 | | 已提交讀 | 不會 | 會 | 會 | | 可重復讀 | 不會 | 不會 | 會 | | 串行化 | 不會 | 不會 | 不會 | > # 臟讀 - 事務讀取了另一個事務未提交的數據 ``` -- 測試表 CREATE TABLE `s_user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '姓名', `age` tinyint(4) NULL DEFAULT NULL COMMENT '年齡', PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic; INSERT INTO `s_user` VALUES (1, '張三', 11); INSERT INTO `s_user` VALUES (2, '李四', 22); INSERT INTO `s_user` VALUES (3, '王五', 33); ``` ``` -- 事務1 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -- 設置隔離級別 未提交讀 -- SET TRANSACTION ISOLATION LEVEL READ COMMITTED; -- 設置隔離級別 提交讀 -- SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; -- 設置隔離級別 可重復讀 -- SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; -- 設置隔離級別 串行化 START TRANSACTION; SELECT age FROM s_user WHERE name = '張三'; SELECT SLEEP(10); SELECT age FROM s_user WHERE name = '張三'; COMMIT; ``` ``` -- 事務2 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -- 設置隔離級別 未提交讀 -- SET TRANSACTION ISOLATION LEVEL READ COMMITTED; -- 設置隔離級別 提交讀 -- SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; -- 設置隔離級別 可重復讀 -- SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; -- 設置隔離級別 串行化 START TRANSACTION; UPDATE s_user SET age = 44 WHERE name = '張三'; SELECT SLEEP(10); COMMIT; ``` > # 不可重復讀 - 事務多次讀取同一條數據時，其它事務修改了這條數據，導致兩次讀取結果不一致 - READ COMMITTED級別, 一個事務可以讀到另一個事務提交的數據 ``` -- 事務1 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -- 設置隔離級別 未提交讀 -- SET TRANSACTION ISOLATION LEVEL READ COMMITTED; -- 設置隔離級別 提交讀 -- SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; -- 設置隔離級別 可重復讀 -- SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; -- 設置隔離級別 串行化 START TRANSACTION; SELECT age FROM s_user WHERE name = '李四'; SELECT SLEEP(10); SELECT age FROM s_user WHERE name = '李四'; COMMIT; ``` ``` -- 事務2 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -- 設置隔離級別 未提交讀 -- SET TRANSACTION ISOLATION LEVEL READ COMMITTED; -- 設置隔離級別 提交讀 -- SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; -- 設置隔離級別 可重復讀 -- SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; -- 設置隔離級別 串行化 START TRANSACTION; UPDATE s_user SET age = 55 WHERE name = '李四'; COMMIT; ``` > # 幻讀 - 事務多次范圍內查詢時，其它事務對范圍內的數據插入、刪除或更新, 導致查詢結果發生了變化 - 可重復讀級別下 InnoDB 通過 多版本并發控制 MVCC 和 臨鍵鎖 Next-Key Lock (間隙鎖 Gap Lock + 行鎖 Record Lock)解決了幻讀問題 ``` -- 事務1 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -- 設置隔離級別 未提交讀 -- SET TRANSACTION ISOLATION LEVEL READ COMMITTED; -- 設置隔離級別 提交讀 -- SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; -- 設置隔離級別 可重復讀 -- SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; -- 設置隔離級別 串行化 START TRANSACTION; SELECT `name`, age FROM s_user WHERE age < 100; SELECT SLEEP(10); SELECT `name`, age FROM s_user WHERE age < 100; COMMIT; ``` ``` -- 事務2 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -- 設置隔離級別 未提交讀 -- SET TRANSACTION ISOLATION LEVEL READ COMMITTED; -- 設置隔離級別 提交讀 -- SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; -- 設置隔離級別 可重復讀 -- SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; -- 設置隔離級別 串行化 START TRANSACTION; INSERT INTO s_user (`name`, `age`) VALUES ("趙六",66); COMMIT; ``` > # 數據結構和索引類型 | 數據結構 | 索引類型 | 適用場景 | | --- | --- | --- | | B+樹 | 主鍵索引、唯一索引、普通索引、聯合索引 | OLTP 場景（范圍查詢、排序） | | 倒排索引 | 全文索引 | 文本模糊搜索(分詞、模糊匹配) | | R-Tree | 空間索引 | 地理空間查詢 | > # 查詢優化 ``` - 架構上擴展 - 垂直擴展（向上擴展）: 提升單機性能, 提高單臺服務器處理能力(增加 CPU、內存或存儲等資源) - 水平擴展（向外擴展）: 增加更多服務器節點來分擔負載,提高系統的吞吐能力(分庫分表分區等) - 分庫分表 - 垂直拆分(按業務拆分):根據業務邏輯（如 用戶表、訂單表）把不同的數據表拆分到不同的數據庫或表中 - 水平拆分（按數據拆分，分片)：把同一張大表的數據按照一定規則（哈希、范圍）拆分到不同的數據庫或表中 - 向內擴展：不增加硬件資源，而是優化數據、代碼、緩存策略，提高系統效率(數據歸檔、冷熱數據分離、sql優化等) - 維護與監控 - 定期優化表: 使用 OPTIMIZE TABLE/ALTER TABLE ... FORCE 命令定期優化表，重新組織表的存儲結構，減少碎片，提高查詢效率 (會對表加鎖, 在負載低的時候執行) - 更新統計信息: 使用 ANALYZE TABLE 命令更新表的統計信息，幫助優化器生成更優的執行計劃 - 監控慢查詢: 開啟慢查詢日志，分析慢查詢，找出性能瓶頸，進行針對性優化 - 索引優化 - 哈希索引: 哈希索引需要特定的引擎, 某些字符串的列加上索引查詢還是很慢, 可以通過 CRC32(字段列) 做哈希去查詢(select * from test_table where name_hash=3413111798 and `name` ='Name_1') - 壓縮(前綴)索引: 設置索引長度 - ``` -- 選著合適的長度 select count(DISTINCT LEFT(name_hash,4))/count(*) as name4, count(DISTINCT LEFT(name_hash,5))/count(*) as name5, count(DISTINCT LEFT(name_hash,6))/count(*) as name6, count(DISTINCT LEFT(name_hash,7))/count(*) as name7 from test_table ``` - 組合索引: 要符合最左前綴, 比如a,b,c 對a, ab, abc生效, 單獨的b和c無法使用到索引(基數（Distinct 值數量）高的列優先，減少查詢掃描的行數;訪問頻率高的列優先，避免索引無效) - 索引覆蓋: 索引包含了查詢所需的所有列，查詢可直接從索引中讀取數據，無需回表（減少 IO） - 延遲關聯: 通過先篩選數據后再關聯來減少計算量 - ``` -- 分頁優化: -- 查詢時使用較大的 OFFSET，MySQL 必須掃描從第 1 條記錄到指定 OFFSET 的所有記錄 select * from test_table order by id limit 5000000, 10 --改成 select * from test_table a INNER JOIN (select id from test_table order by id limit 5000000, 10) b on a.id=b.id; ``` - or - 多列 or（如a=1 or b=2）: 對每列單獨創建索引 -> 觸發索引合并（Index Merge） - 多列 or 且無法索引合并,單列有索引: UNION ALL（避免去重開銷）-> SELECT * FROM t WHERE a=1 UNION ALL SELECT * FROM t WHERE b=2; - join - 嵌套循環連接是 MySQL 中最常用的一種連接方式。在這種連接方式中，對于每一行來自第一個表（稱為 驅動表），數據庫會在第二個表中查找匹配的行。MySQL 會逐行掃描第一個表，對于每一行，它都會遍歷第二個表來查找匹配的行 - 選擇行數較少或者能夠利用索引的表作為驅動表（即最先掃描的表), 減少第二個表的掃描次數 - 子查詢: - exists: (外層表數據量大,內層表數據量小)對于外層查詢中的每一行，exists子查詢只需檢查是否存在至少一條滿足條件的記錄，一旦找到匹配記錄就立即返回true，不必掃描整個子查詢結果集(一對多的時候, EXISTS是在多的里面找到一條符合, 就執行外部的下一條) - in:(子查詢結果集小) 子查詢會先生成一個值列表，然后外層查詢將某個列的值與該列表中的所有值進行比較。如果列表較大，可能會帶來額外的開銷 - 子查詢優化: 物化表/物化視圖, 但是Mysql不支持, 手動模擬創建一張表, 定期使用INSERT/REPLACE或CREATE TABLE ... AS SELECT刷新數據) - 負向查詢: !=, <>, NOT IN, NOT LIKE... : 不走索引 - count(*): 用統計信息里的模糊數量 / 放到緩存計數里 - 字段類型不同(隱式類型轉換),表字符集不同(隱式字符編碼轉換),字段使用了函數或表達式,like '%XXX' 會使索引失效 - 當索引掃描行數超過全表的 10%-30% 時，可能放棄索引.索引列的取值重復率太高,統計信息可能不準確或過期，導致MySQL錯誤估算索引的選擇性，進而不使用索引 - USE INDEX (建議使用索引): 建議優化器優先考慮使用指定的索引，但如果優化器認為索引效果不好，它仍可能選擇不用 - FORCE INDEX (強制使用索引): 強制優化器必須使用指定的索引，即使優化器認為它不如全表掃描快，也必須使用索引 - explain - possible_keys:可能使用的索引, key:實際使用的索引 - type: 訪問方式: all(全表掃描) < index(索引掃描) < range(范圍掃描) < ref(索引匹配) < const(單行匹配) < system(只有一行數據) - rows: 預計掃描的行數, filtered: 查詢條件過濾后的數據比例 - extra: using index 覆蓋索引, using filesort 需要排序, using where 需要額外過濾 ``` ># JOIN 的主要類型 - 內連接（INNER JOIN / JOIN）: 返回兩個表中滿足連接條件的記錄(兩個表的交集) - 外連接（OUTER JOIN） - 左外連接（LEFT OUTER JOIN / LEFT JOIN）：返回左表的所有記錄以及右表中與之匹配的記錄。如果右表中沒有匹配，返回的右表列為 NULL - 右外連接（RIGHT OUTER JOIN / RIGHT JOIN）：與左外連接相反，返回右表的所有記錄以及左表中與之匹配的記錄。如果左表中沒有匹配，返回的左表列為 NULL - 全外連接（FULL OUTER JOIN）：返回左右兩個表中所有記錄，對于沒有匹配的部分顯示為 NULL（MySQL 不直接支持全外連接，但可以通過 UNION 實現） - 交叉連接（CROSS JOIN）: 返回兩個表的笛卡爾積，即所有可能的組合，不需要指定連接條件 --- - 自連接: 同一張表與自身進行連接，常用于比較同一表中不同記錄之間的關系。 - 反連接: 返回在一個表中存在而在另一個表中不存在的記錄，通常通過 NOT EXISTS 或 NOT IN 實現。 - 半連接: 返回一側的數據：只返回主表中存在匹配記錄的行，常用于存在性檢查（例如使用 EXISTS 或 IN），不會將關聯表的列合并到結果中。 > # 主備數據同步 - 主備數據如何保持一致: - 業務層策略: 對于需要強一致性的場景，關鍵的讀操作可以直接指向主庫，以確保讀到的數據是最新的 - 半同步復制：半同步復制通過配置，在主庫提交事務時，至少等待一個從庫確認接收數據后再返回成功，確保數據同步的可靠性，減少延遲 - 通過對比主庫和從庫的 binlog 位置: 確保從庫的 `Master_Log_File` 和 `Read_Master_Log_Pos` 與主庫一致，即從庫已經讀取并應用了主庫的所有 binlog - 延遲監控: Seconds_Behind_Master 字段顯示從庫延遲的秒數，可以通過該字段監控從庫與主庫的同步延遲 - 使用 GTID（全局事務 ID): 通過使用全局事務 ID來跟蹤主從復制進度，GTID 提供了更高的一致性保證，減少了主備數據同步的延遲誤差。GTID 的使用能夠確保在復制過程中事務的一致性，避免丟失或重復應用事務 > # 數據一致性 - 強一致性: 任何時刻，所有節點看到的數據都是相同的，讀操作能立即看到最新寫入的數據(eg:XA) - 有序執行（線性一致性) - 無序執行 (順序一致性) - 弱一致性: 讀操作可能看到舊數據，并不能保證所有節點的數據完全同步(eg:通過Kafka去處理) - 有序執行 (因果一致性) - 無序執行(最終一致性) - Kafka > # 死鎖 - 手動解決: 找到sql對應的id, kill掉 - 自動處理死鎖 ``` innodb_deadlock_detect = ON //是否開啟死鎖檢測(**實時檢測死鎖**，一旦發現死鎖，會主動**回滾**其中一個事務，另一個事務繼續執行) innodb_lock_wait_timeout = 5 //調整超時時間 ``` > # 主鍵id不連續 | **原因** | **描述** | **示例** | | --- | --- | --- | | **刪除操作** | 刪除行后，自增值不回退。 | 刪除 ID=2 后，下次插入 ID=4 | | **插入失敗(單體/多條)** | 插入失敗后，自增值不回退。 | 自增 ID 一旦分配不會回滾，即使事務失敗 | | **事務回滾** | 事務回滾后，自增值不回退。 | 與插入失敗同理，自增 ID 的分配獨立于事務提交 | | **手動插入 ID** | 手動指定 ID 后，自增值更新為指定值。 | 若手動插入的 ID 大于當前自增值，后續插入會從該值遞增 | | **復制或導入數據** | 導入數據時手動指定 ID，導致不連續。 | 導入 ID=1000-2000 后，下次插入 ID=2001 | | **自增值緩存** | 自增值緩存機制導致未使用的值丟失。 | 服務器崩潰后，未使用的自增值丟失。 | > # 批量數據更新 - ON DUPLICATE KEY UPDATE: 當插入數據遇到唯一鍵沖突時，執行 UPDATE 操作 - REPLACE INTO重復時刪除舊數據并插入新數據。需要覆蓋舊數據的場景。(先刪除再插入) - INSERT IGNORE: 忽略插入重復數據 > # XA: 兩階段提交(用于分布式事務處理，確保在多個資源上的事務能夠作為一個單一的工作單元提交或回滾) - 設置 sync_binlog - 內部XA:用于同一 MySQL 實例內部的事務一致性，比如 InnoDB 存儲引擎在提交時，同時需要保證重做日志（redo log）與二進制日志（binlog）的寫入一致 - 外部XA: 用于跨多個 MySQL 實例或其他資源管理器的分布式事務，必須由應用層或外部事務管理器主動調用 XA 命令 ``` -- 開啟 XA 事務 XA START '唯一id'; -- 執行 SQL 操作 INSERT XXX; UPDATE XXX; -- 結束 XA 事務 XA END '唯一id'; -- 準備提交 XA PREPARE '唯一id'; ( xa recover 查看所有PREPARE狀態的XA) -- 回滾事務 XA ROLLBACK '唯一id'; -- 提交事務 XA COMMIT '唯一id'; ``` > # Savepoints：用于在單個事務內部創建標記點，以便可以回滾到這些特定的標記點，提供細粒度的事務控制