# 查看Redo Log配置
SHOW VARIABLES LIKE 'innodb_log%';
# 重要參數(shù)：
# innodb_log_file_size = 每個(gè)文件大?。J(rèn)48M-2G）
# innodb_log_files_in_group = 文件數(shù)量（默認(rèn)2）
# innodb_log_buffer_size = 緩沖區(qū)大小（默認(rèn)16M）

# Checkpoint過程示意
def checkpoint_process():
    # 1. 找到最老的臟頁LSN（Log Sequence Number）
    oldest_dirty_lsn = find_oldest_dirty_page()
    
    # 2. 將這個(gè)LSN寫入Redo Log頭
    write_checkpoint_to_redo(oldest_dirty_lsn)
    
    # 3. 刷新臟頁到磁盤
    flush_dirty_pages_to_disk()
    
    # 4. 清理Redo Log空間
    # 從checkpoint前的Redo Log可以安全覆蓋

-- Undo Log段管理
CREATE TABLE t (
    id INT PRIMARY KEY,
    name VARCHAR(20),
    balance DECIMAL(10,2)
) ENGINE=InnoDB;

-- Undo Log包含：
-- 1. 回滾指針：DB_ROLL_PTR（指向舊版本）
-- 2. 事務(wù)ID：DB_TRX_ID（最近修改的事務(wù)ID）
-- 3. 刪除標(biāo)記：DELETED_BIT

-- 讀已提交（Read Committed）隔離級別下的可見性判斷
SELECT * FROM users WHERE id=1;

-- InnoDB判斷流程：
-- 1. 獲取當(dāng)前事務(wù)ID: current_trx_id
-- 2. 獲取當(dāng)前活躍事務(wù)列表: active_trx_list
-- 3. 從當(dāng)前行開始遍歷版本鏈：
--    - 如果 trx_id < current_trx_id 且 trx_id不在活躍列表中
--      且 trx_id <= up_limit_id（快照上限）
--    - 則該版本對當(dāng)前事務(wù)可見
-- 4. 否則繼續(xù)查找更早版本

START TRANSACTION;
-- 步驟1：A賬戶扣款
UPDATE accounts SET balance=balance-100 WHERE id=1;
-- Undo Log記錄: (balance=原始值, trx_id=當(dāng)前事務(wù))
-- Redo Log記錄: 頁面修改信息

-- 步驟2：B賬戶入賬  
UPDATE accounts SET balance=balance+100 WHERE id=2;
-- Undo Log記錄: (balance=原始值, trx_id=當(dāng)前事務(wù))
-- Redo Log記錄: 頁面修改信息

COMMIT;
-- Binlog寫入 → Redo Log狀態(tài)改為commit

-- 危險(xiǎn)的長時(shí)間查詢
START TRANSACTION;
SELECT * FROM large_table WHERE ... FOR UPDATE;
-- 執(zhí)行復(fù)雜業(yè)務(wù)邏輯（耗時(shí)10分鐘）
-- ... 
COMMIT;

-- 問題：
-- 1. Undo Log無法清理，導(dǎo)致undo表空間膨脹
-- 2. 阻塞Purge線程
-- 3. 可能觸發(fā)“事務(wù)ID耗盡”問題

-- 監(jiān)控長時(shí)間運(yùn)行的事務(wù)
SELECT 
    trx_id,
    trx_started,
    TIMEDIFF(NOW(), trx_started) AS duration,
    trx_state,
    trx_operation_state
FROM information_schema.INNODB_TRX
WHERE TIMEDIFF(NOW(), trx_started) > '00:05:00'
ORDER BY trx_started;

-- 監(jiān)控Undo Log使用
SHOW ENGINE INNODB STATUS\G
-- 查看"TRANSACTIONS"部分

-- 錯(cuò)誤的批量更新（產(chǎn)生大量Undo）
START TRANSACTION;
UPDATE large_table SET status=1 WHERE create_date < '2024-01-01';
-- 影響100萬行，產(chǎn)生大量Undo Log
COMMIT;

-- 優(yōu)化的批量更新
SET AUTOCOMMIT=0;
SET UNIQUE_CHECKS=0;
SET FOREIGN_KEY_CHECKS=0;

-- 分批次處理
DECLARE done INT DEFAULT FALSE;
DECLARE batch_size INT DEFAULT 1000;
DECLARE last_id INT DEFAULT 0;

REPEAT
    START TRANSACTION;
    
    UPDATE large_table 
    SET status=1 
    WHERE id > last_id 
    AND create_date < '2024-01-01'
    LIMIT batch_size;
    
    SET last_id = LAST_INSERT_ID();
    
    COMMIT;  -- 每個(gè)批次提交，釋放Undo
    
    SELECT SLEEP(0.1);  -- 避免過度消耗資源
    
UNTIL ROW_COUNT() = 0 END REPEAT;

SET AUTOCOMMIT=1;
SET UNIQUE_CHECKS=1;
SET FOREIGN_KEY_CHECKS=1;

# my.cnf配置示例
[mysqld]
# Redo Log文件大小，建議設(shè)置為緩沖池的1/4到1/2
# 對于8G內(nèi)存，Buffer Pool通常6G，Redo Log設(shè)為2G
innodb_log_file_size = 2G
innodb_log_files_in_group = 4  # 總大小=2G*4=8G

# Log Buffer大小，大事務(wù)可適當(dāng)調(diào)大
innodb_log_buffer_size = 64M

# 刷盤策略，根據(jù)數(shù)據(jù)安全需求選擇
# 1-最安全，每次提交都刷盤（默認(rèn)）
# 2-折中，每次提交寫OS緩存，每秒刷盤
# 0-性能最好，每秒刷盤，可能丟失1秒數(shù)據(jù)
innodb_flush_log_at_trx_commit = 1

-- MySQL 8.0+ 獨(dú)立Undo表空間
-- 查看Undo配置
SELECT * FROM information_schema.INNODB_TABLESPACES 
WHERE SPACE_TYPE = 'Undo';

-- 監(jiān)控Undo空間使用
SELECT 
    tablespace_name,
    file_size / 1024 / 1024 AS file_size_mb,
    allocated_size / 1024 / 1024 AS allocated_mb
FROM information_schema.FILES
WHERE file_name LIKE '%undo%';

-- 設(shè)置Undo表空間自動(dòng)清理
SET GLOBAL innodb_undo_log_truncate = ON;
SET GLOBAL innodb_max_undo_log_size = 1 * 1024 * 1024 * 1024;  -- 1GB
SET GLOBAL innodb_purge_rseg_truncate_frequency = 128;

-- 場景：秒殺活動(dòng)，大量并發(fā)寫入
-- 問題：Redo Log成為瓶頸

-- 解決方案1：臨時(shí)調(diào)整刷盤策略（活動(dòng)期間）
SET GLOBAL innodb_flush_log_at_trx_commit = 2;
-- 活動(dòng)結(jié)束后恢復(fù)為1

-- 解決方案2：組提交優(yōu)化
-- MySQL已自動(dòng)支持，確保參數(shù)合理
SHOW VARIABLES LIKE 'binlog_group%';
SHOW VARIABLES LIKE 'innodb_flush_log_at_timeout';

-- 解決方案3：拆分熱點(diǎn)數(shù)據(jù)
-- 將熱點(diǎn)賬戶分散到不同數(shù)據(jù)頁
UPDATE account_001 SET ... WHERE user_id=1;  -- 分表

# 1. 檢查Redo Log狀態(tài)
mysql> SHOW ENGINE INNODB STATUS\G

# 2. 如果有備份，優(yōu)先使用備份恢復(fù)
# 使用Percona XtraBackup或mysqldump備份

# 3. 強(qiáng)制恢復(fù)模式（謹(jǐn)慎使用）
# 在my.cnf中添加
[mysqld]
innodb_force_recovery = 1  # 1-6，數(shù)字越大越激進(jìn)

# 4. 恢復(fù)步驟
# 4.1 停止MySQL
sudo systemctl stop mysql

# 4.2 備份原數(shù)據(jù)文件
cp -r /var/lib/mysql /var/lib/mysql_backup

# 4.3 移除損壞的Redo Log
mv /var/lib/mysql/ib_logfile* /tmp/

# 4.4 啟動(dòng)MySQL（會(huì)自動(dòng)創(chuàng)建新的Redo Log）
sudo systemctl start mysql

# 4.5 使用mysqlbinlog恢復(fù)未同步的數(shù)據(jù)
mysqlbinlog mysql-bin.000001 | mysql -u root -p

-- 1. Redo Log監(jiān)控
SELECT 
    'Redo Log' AS metric,
    CONCAT(ROUND(SUM(LENGTH)/1024/1024, 2), ' MB') AS current_size,
    CONCAT(ROUND(variable_value/1024/1024, 2), ' MB') AS configured_size,
    ROUND(SUM(LENGTH)*100/variable_value, 2) AS usage_percent
FROM information_schema.INNODB_BUFFER_PAGE
JOIN information_schema.GLOBAL_VARIABLES 
    ON variable_name = 'innodb_log_file_size'
WHERE PAGE_TYPE LIKE 'IBUF%'
GROUP BY variable_value;

-- 2. Undo空間監(jiān)控
SELECT 
    FORMAT_BYTES(SUM(current_size)) AS active_undo_size,
    FORMAT_BYTES(SUM(undo_size)) AS total_undo_size,
    COUNT(*) AS undo_segments,
    ROUND(SUM(current_size)*100/SUM(undo_size), 2) AS usage_pct
FROM information_schema.INNODB_METRICS
WHERE NAME LIKE '%undo%';

-- 3. 長事務(wù)和Undo關(guān)聯(lián)查詢
SELECT 
    r.trx_id AS waiting_trx_id,
    r.trx_mysql_thread_id AS waiting_thread,
    TIMEDIFF(NOW(), r.trx_started) AS wait_time,
    b.trx_id AS blocking_trx_id,
    b.trx_mysql_thread_id AS blocking_thread,
    bl.lock_table AS locked_table
FROM information_schema.INNODB_LOCK_WAITS w
INNER JOIN information_schema.INNODB_TRX b 
    ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.INNODB_TRX r 
    ON r.trx_id = w.requesting_trx_id
INNER JOIN information_schema.INNODB_LOCKS bl 
    ON bl.lock_id = w.blocking_lock_id;