第5章：存储引擎深度解析

-- Buffer Pool 配置
innodb_buffer_pool_size = 8G              -- 设置为内存的70-80%
innodb_buffer_pool_instances = 8          -- 提高并发性能
innodb_buffer_pool_chunk_size = 128M      -- 8.0+ 新增，动态调整

-- I/O 配置
innodb_io_capacity = 2000                 -- SSD推荐值
innodb_io_capacity_max = 4000             -- 最大I/O容量
innodb_read_io_threads = 4                -- 读线程数
innodb_write_io_threads = 4               -- 写线程数

-- 日志配置
innodb_log_file_size = 256M               -- Redo Log大小
innodb_log_files_in_group = 2             -- Redo Log文件数
innodb_flush_log_at_trx_commit = 2        -- 平衡性能与安全

-- 并发配置
innodb_thread_concurrency = 0             -- 0=自动调整
innodb_read_io_threads = 4                -- 读线程数
innodb_write_io_threads = 4               -- 写线程数

-- Buffer Pool 命中率
SELECT 
    (1 - VARIABLE_VALUE / (
        SELECT VARIABLE_VALUE 
        FROM information_schema.GLOBAL_STATUS 
        WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests'
    )) * 100 AS hit_rate_percent
FROM information_schema.GLOBAL_STATUS 
WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads';

-- 查看存储引擎状态
SHOW ENGINE INNODB STATUS\G

-- 查看表空间使用情况
SELECT 
    TABLESPACE_NAME,
    FILE_NAME,
    ROUND((TOTAL_EXTENTS * EXTENT_SIZE) / 1024 / 1024, 2) AS 'Size_MB',
    ROUND((INITIAL_SIZE / 1024 / 1024), 2) AS 'Initial_MB',
    ROUND(((TOTAL_EXTENTS * EXTENT_SIZE) / INITIAL_SIZE) * 100, 2) AS 'Usage_%'
FROM information_schema.FILES 
WHERE TABLESPACE_NAME LIKE '%innodb%';

-- 查看存储引擎统计信息
SELECT * FROM information_schema.INNODB_METRICS 
WHERE NAME LIKE '%buffer%' OR NAME LIKE '%log%';

-- 查看页分裂统计
SHOW STATUS LIKE 'Innodb_page_splits';

-- 查看锁等待统计
SHOW STATUS LIKE 'Innodb_row_lock%';

-- 检查表空间碎片
SELECT 
    TABLE_SCHEMA,
    TABLE_NAME,
    ROUND((DATA_FREE / (DATA_LENGTH + INDEX_LENGTH)) * 100, 2) AS fragmentation_pct
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA NOT IN ('information_schema', 'performance_schema', 'mysql', 'sys')
AND DATA_FREE > 0
ORDER BY fragmentation_pct DESC;

-- 检查未使用的索引
SELECT 
    OBJECT_SCHEMA,
    OBJECT_NAME,
    INDEX_NAME
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE OBJECT_SCHEMA = 'mysql_learning'
AND COUNT_FETCH = 0
AND COUNT_INSERT = 0
AND COUNT_UPDATE = 0
AND COUNT_DELETE = 0;

-- 检查MyISAM表
SELECT 
    TABLE_SCHEMA,
    TABLE_NAME,
    ENGINE,
    TABLE_ROWS,
    ROUND(((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024), 2) AS 'Size_MB'
FROM information_schema.TABLES 
WHERE ENGINE = 'MyISAM'
AND TABLE_SCHEMA NOT IN ('information_schema', 'performance_schema', 'mysql', 'sys');

-- 步骤1：备份数据
mysqldump --single-transaction --routines --triggers database_name > backup.sql

-- 步骤2：修改表引擎
ALTER TABLE table_name ENGINE=InnoDB;

-- 步骤3：验证数据完整性
CHECK TABLE table_name;

-- 步骤4：优化表
OPTIMIZE TABLE table_name;

-- 调整InnoDB参数
SET GLOBAL innodb_buffer_pool_size = 8G;
SET GLOBAL innodb_log_file_size = 256M;
SET GLOBAL innodb_flush_log_at_trx_commit = 2;

-- 重建索引
ALTER TABLE table_name ENGINE=InnoDB;

-- T1:
BEGIN;
UPDATE user SET age=20 WHERE id=1;
UPDATE user SET age=21 WHERE id=2;

-- T2:
BEGIN;
UPDATE user SET age=30 WHERE id=2;
UPDATE user SET age=31 WHERE id=1;

SHOW ENGINE INNODB STATUS\G

innodb_lock_wait_timeout = 50   # 默认 50 秒
innodb_deadlock_detect = ON     # 启用死锁检测

-- 创建测试表
USE mysql_learning;

CREATE TABLE test_storage (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    data TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- 插入测试数据
INSERT INTO test_storage (name, data) VALUES 
('测试1', REPEAT('A', 1000)),
('测试2', REPEAT('B', 1000)),
('测试3', REPEAT('C', 1000));

-- 查看表空间信息
SELECT 
    TABLE_NAME,
    ENGINE,
    ROUND(((DATA_LENGTH + INDEX_LENGTH) / 1024), 2) AS 'Size in KB'
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = 'mysql_learning' 
AND TABLE_NAME = 'test_storage';

-- 查看 Buffer Pool 状态
SHOW ENGINE INNODB STATUS\G

-- 查看 Buffer Pool 统计信息
SELECT 
    POOL_ID,
    POOL_SIZE,
    FREE_BUFFERS,
    DATABASE_PAGES,
    OLD_DATABASE_PAGES,
    MODIFIED_DATABASE_PAGES
FROM information_schema.INNODB_BUFFER_POOL_STATS;

-- 查看 Buffer Pool 命中率
SHOW STATUS LIKE 'Innodb_buffer_pool%';

-- 计算命中率
SELECT 
    (1 - VARIABLE_VALUE / (
        SELECT VARIABLE_VALUE 
        FROM information_schema.GLOBAL_STATUS 
        WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests'
    )) * 100 AS hit_rate_percent
FROM information_schema.GLOBAL_STATUS 
WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads';

-- 创建测试表
CREATE TABLE lock_test (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    value INT
) ENGINE=InnoDB;

INSERT INTO lock_test (name, value) VALUES 
('记录1', 100),
('记录2', 200),
('记录3', 300);

-- 会话1：加排他锁
START TRANSACTION;
SELECT * FROM lock_test WHERE id = 1 FOR UPDATE;

-- 会话2：尝试读取（会被阻塞）
SELECT * FROM lock_test WHERE id = 1 FOR UPDATE;
-- 会等待，直到会话1提交或超时

-- 准备测试数据
INSERT INTO lock_test (name, value) VALUES 
('记录4', 400),
('记录5', 500);

-- 会话1：按顺序锁定
START TRANSACTION;
SELECT * FROM lock_test WHERE id = 1 FOR UPDATE;
-- 等待几秒
SELECT SLEEP(2);
SELECT * FROM lock_test WHERE id = 2 FOR UPDATE;

-- 会话2：反向顺序锁定（同时执行）
START TRANSACTION;
SELECT * FROM lock_test WHERE id = 2 FOR UPDATE;
SELECT * FROM lock_test WHERE id = 1 FOR UPDATE;

-- 查看死锁信息
SHOW ENGINE INNODB STATUS\G

-- 查看当前锁信息
SELECT * FROM information_schema.innodb_locks;

-- 查看锁等待情况
SELECT * FROM information_schema.innodb_lock_waits;

-- 查看事务信息
SELECT * FROM information_schema.innodb_trx;

-- 查看当前进程
SHOW PROCESSLIST;

-- 创建测试表
CREATE TABLE gap_test (
    id INT PRIMARY KEY,
    name VARCHAR(50)
) ENGINE=InnoDB;

INSERT INTO gap_test VALUES 
(1, 'A'),
(3, 'C'),
(5, 'E'),
(7, 'G');

-- 会话1：锁定范围
START TRANSACTION;
SELECT * FROM gap_test WHERE id > 2 AND id < 6 FOR UPDATE;

-- 会话2：尝试插入（会被间隙锁阻塞）
INSERT INTO gap_test VALUES (4, 'D');
-- 会等待，因为 id=4 在间隙 (3,5) 中

-- 设置锁等待超时时间
SET innodb_lock_wait_timeout = 10;

-- 测试超时
-- 会话1：锁定记录
START TRANSACTION;
SELECT * FROM lock_test WHERE id = 1 FOR UPDATE;

-- 会话2：尝试锁定（会超时）
SELECT * FROM lock_test WHERE id = 1 FOR UPDATE;
-- 10秒后会超时并报错

-- 查看锁相关统计信息
SHOW STATUS LIKE 'Innodb_row_lock%';

-- 查看死锁统计
SHOW STATUS LIKE 'Innodb_deadlocks';

-- 查看锁等待统计
SHOW STATUS LIKE 'Innodb_lock_wait%';

-- 查看当前锁等待时间
SELECT 
    r.trx_id waiting_trx_id,
    r.trx_mysql_thread_id waiting_thread,
    r.trx_query waiting_query,
    b.trx_id blocking_trx_id,
    b.trx_mysql_thread_id blocking_thread,
    b.trx_query blocking_query
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;

-- 问题代码
START TRANSACTION;
SELECT stock FROM products WHERE id = 1 FOR UPDATE;
UPDATE products SET stock = stock - 1 WHERE id = 1 AND stock > 0;
COMMIT;

-- 方案1：原子操作
UPDATE products 
SET stock = GREATEST(stock - 1, 0) 
WHERE id = 1 AND stock > 0;

-- 方案2：乐观锁
UPDATE products 
SET stock = stock - 1, version = version + 1 
WHERE id = 1 AND stock > 0 AND version = @current_version;

-- 方案3：分布式锁（Redis）
-- 先获取分布式锁，再执行扣减

-- 问题代码
START TRANSACTION;
UPDATE users SET status = 'inactive' WHERE id IN (1,2,3,4,5);
COMMIT;

-- 方案1：分批处理
UPDATE users SET status = 'inactive' WHERE id IN (1,2,3);
UPDATE users SET status = 'inactive' WHERE id IN (4,5);

-- 方案2：使用临时表
CREATE TEMPORARY TABLE temp_user_ids (id INT PRIMARY KEY);
INSERT INTO temp_user_ids VALUES (1),(2),(3),(4),(5);

UPDATE users u 
JOIN temp_user_ids t ON u.id = t.id 
SET u.status = 'inactive';

-- 方案3：异步处理
-- 将更新任务放入消息队列，异步处理

-- 1. 使用读写分离
-- 读操作路由到从库
SELECT * FROM users WHERE id = 1;

-- 写操作路由到主库
UPDATE users SET name = '新名字' WHERE id = 1;

-- 2. 使用缓存减少数据库压力
-- 热点数据放入 Redis
-- 读操作先查缓存，缓存未命中再查数据库

-- 3. 优化锁粒度
-- 使用行锁而不是表锁
-- 避免长时间持有锁

-- 查看当前锁等待情况
SELECT 
    r.trx_id waiting_trx_id,
    r.trx_mysql_thread_id waiting_thread,
    r.trx_query waiting_query,
    b.trx_id blocking_trx_id,
    b.trx_mysql_thread_id blocking_thread,
    b.trx_query blocking_query
FROM information_schema.innodb_lock_waits w
INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id
INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id;

-- 查看死锁历史
SHOW ENGINE INNODB STATUS\G
-- 关注：LATEST DETECTED DEADLOCK 部分

-- 查看锁统计信息
SHOW STATUS LIKE 'Innodb_row_lock%';

-- 关键指标说明
-- Innodb_row_lock_current_waits: 当前等待锁数量
-- Innodb_row_lock_time_avg: 平均等待时间
-- Innodb_row_lock_time_max: 最大等待时间
-- Innodb_deadlocks: 死锁次数

-- 查看锁详细信息
SELECT 
    OBJECT_SCHEMA,
    OBJECT_NAME,
    INDEX_NAME,
    LOCK_TYPE,
    LOCK_MODE,
    LOCK_STATUS,
    LOCK_DATA
FROM performance_schema.data_locks;

-- 查看锁等待情况
SELECT 
    OBJECT_SCHEMA,
    OBJECT_NAME,
    INDEX_NAME,
    LOCK_TYPE,
    LOCK_MODE,
    LOCK_STATUS,
    LOCK_DATA
FROM performance_schema.data_lock_waits;