第1章：MySQL 架构与存储引擎

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

-- 计算命中率（关键指标）
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';

-- 查看Redo Log配置
SHOW VARIABLES LIKE 'innodb_log%';

-- 查看Redo Log状态
SHOW ENGINE INNODB STATUS\G
-- 关注：LOG部分
-- Log sequence number: 当前LSN
-- Log flushed up to: 已刷盘的LSN
-- Pages flushed up to: 已刷盘的页LSN
-- Last checkpoint at: 最后检查点LSN

-- 查看Undo表空间
SELECT 
    TABLESPACE_NAME,
    FILE_NAME,
    TOTAL_EXTENTS,
    EXTENT_SIZE,
    INITIAL_SIZE,
    MAXIMUM_SIZE,
    AUTOEXTEND_SIZE,
    STATUS
FROM information_schema.FILES 
WHERE TABLESPACE_NAME LIKE '%undo%';

-- 查看Undo段信息
SELECT 
    SEGMENT_ID,
    SEGMENT_NAME,
    SPACE_ID,
    PAGE_NO,
    MAX_SIZE,
    HISTORY_SIZE,
    CURRENT_SIZE,
    LAST_UPDATE_TIME
FROM information_schema.INNODB_SYS_TABLESPACES 
WHERE NAME LIKE '%undo%';

-- 查看Double Write状态
SHOW STATUS LIKE 'Innodb_dblwr%';

-- 关键指标
-- Innodb_dblwr_pages_written: 写入的页数
-- Innodb_dblwr_writes: 写入次数

-- 查看Change Buffer状态
SHOW ENGINE INNODB STATUS\G
-- 关注：INSERT BUFFER AND ADAPTIVE HASH INDEX部分

-- 关键指标
-- size: Change Buffer大小
-- free list len: 空闲列表长度
-- seg size: 段大小
-- merges: 合并次数

-- 查看线程状态
SHOW ENGINE INNODB STATUS\G
-- 关注：BACKGROUND THREAD 部分

-- 查看IO线程统计
SELECT 
    THREAD_NAME,
    COUNT_READ,
    COUNT_WRITE,
    COUNT_READ / (COUNT_READ + COUNT_WRITE) * 100 AS read_ratio
FROM performance_schema.file_summary_by_instance
WHERE THREAD_NAME LIKE '%io%';

-- 查看AHI状态
SHOW ENGINE INNODB STATUS\G
-- 关注：INSERT BUFFER AND ADAPTIVE HASH INDEX部分

-- 关键指标
-- hash searches: 哈希查找次数
-- hash search hits: 哈希命中次数
-- hash search misses: 哈希未命中次数

-- 计算命中率
SELECT 
    (hash_search_hits / (hash_search_hits + hash_search_misses)) * 100 
    AS ahi_hit_rate
FROM performance_schema.global_status 
WHERE VARIABLE_NAME = 'Innodb_adaptive_hash_hits';

-- 查看表空间信息
SELECT 
    TABLESPACE_NAME,
    FILE_NAME,
    TOTAL_EXTENTS,
    EXTENT_SIZE,
    INITIAL_SIZE,
    MAXIMUM_SIZE,
    AUTOEXTEND_SIZE,
    STATUS
FROM information_schema.FILES 
WHERE TABLESPACE_NAME LIKE '%innodb%';

-- 查看表空间使用率
SELECT 
    TABLESPACE_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%';

-- Redo Log 配置
SHOW VARIABLES LIKE 'innodb_log%';
-- innodb_log_file_size: Redo Log文件大小
-- innodb_log_files_in_group: Redo Log文件数量
-- innodb_flush_log_at_trx_commit: 刷盘策略

-- Undo Log 配置  
SHOW VARIABLES LIKE 'innodb_undo%';
-- innodb_undo_tablespaces: Undo表空间数量
-- innodb_undo_log_truncate: 是否启用Undo截断

-- Binlog 配置
SHOW VARIABLES LIKE 'log_bin%';
-- log_bin: 是否启用Binlog
-- binlog_format: Binlog格式(ROW/STATEMENT/MIXED)
-- sync_binlog: Binlog刷盘策略

-- 查看Redo Log状态
SHOW ENGINE INNODB STATUS\G
-- 关注：LOG部分
-- Log sequence number: 当前LSN
-- Log flushed up to: 已刷盘的LSN  
-- Pages flushed up to: 已刷盘的页LSN
-- Last checkpoint at: 最后检查点LSN

-- 查看Binlog状态
SHOW MASTER STATUS;
SHOW BINARY LOGS;

-- 查看Undo使用情况
SELECT 
    TABLESPACE_NAME,
    FILE_NAME,
    TOTAL_EXTENTS,
    EXTENT_SIZE,
    INITIAL_SIZE,
    MAXIMUM_SIZE,
    AUTOEXTEND_SIZE,
    STATUS
FROM information_schema.FILES 
WHERE TABLESPACE_NAME LIKE '%undo%';

-- 核心参数
innodb_buffer_pool_size = 8G          -- 设置为内存的70-80%
innodb_log_file_size = 256M           -- Redo Log大小
innodb_flush_log_at_trx_commit = 2    -- 平衡性能和安全
innodb_file_per_table = ON            -- 每表独立表空间
innodb_buffer_pool_instances = 8      -- 提高并发性能

-- 核心参数
key_buffer_size = 256M                -- 索引缓存大小
myisam_sort_buffer_size = 64M         -- 排序缓冲区
myisam_max_sort_file_size = 10G       -- 排序文件大小限制
myisam_repair_threads = 1             -- 修复线程数

-- 创建测试表
CREATE TABLE test_innodb (
    id INT PRIMARY KEY AUTO_INCREMENT,
    data VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE test_myisam (
    id INT PRIMARY KEY AUTO_INCREMENT,
    data VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=MyISAM;

-- 插入性能测试
-- InnoDB: 约10000行/秒
-- MyISAM: 约15000行/秒

-- 查询性能测试
-- InnoDB: 支持MVCC，读不阻塞写
-- MyISAM: 表锁，读阻塞写

-- 在线迁移（MySQL 5.6+）
ALTER TABLE table_name ENGINE=InnoDB;

-- 检查迁移结果
SHOW TABLE STATUS LIKE 'table_name';

-- 1. 查看MySQL版本
SELECT VERSION();

-- 2. 查看支持的存储引擎
SHOW ENGINES;

-- 3. 查看当前数据库的存储引擎使用情况
SELECT 
    TABLE_SCHEMA,
    TABLE_NAME,
    ENGINE,
    TABLE_ROWS,
    ROUND(((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024), 2) AS 'Size_MB'
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA NOT IN ('information_schema', 'performance_schema', 'mysql', 'sys')
ORDER BY TABLE_SCHEMA, TABLE_NAME;

-- 1. 创建测试数据库
CREATE DATABASE IF NOT EXISTS mysql_learning;
USE mysql_learning;

-- 2. 创建InnoDB测试表
CREATE TABLE test_innodb (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    age INT,
    email VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_name (name)
) ENGINE=InnoDB;

-- 3. 创建MyISAM测试表（注意：MySQL 8.0已移除MyISAM支持）
-- 如果使用MySQL 8.0，可以跳过此步骤
CREATE TABLE test_myisam (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    age INT,
    email VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_name (name)
) ENGINE=MyISAM;

-- 查看当前 Buffer Pool 配置
SELECT 
    VARIABLE_NAME,
    VARIABLE_VALUE,
    CASE 
        WHEN VARIABLE_NAME = 'innodb_buffer_pool_size' 
        THEN CONCAT(ROUND(VARIABLE_VALUE/1024/1024/1024, 2), ' GB')
        WHEN VARIABLE_NAME = 'innodb_buffer_pool_instances'
        THEN CONCAT(VARIABLE_VALUE, ' instances')
        ELSE VARIABLE_VALUE
    END AS 'Formatted_Value'
FROM information_schema.GLOBAL_VARIABLES 
WHERE VARIABLE_NAME LIKE 'innodb_buffer_pool%'
ORDER BY VARIABLE_NAME;

-- 计算 Buffer Pool 命中率
SELECT 
    'Buffer Pool Hit Rate' AS 'Metric',
    CONCAT(
        ROUND(
            (1 - (
                SELECT VARIABLE_VALUE 
                FROM information_schema.GLOBAL_STATUS 
                WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads'
            ) / (
                SELECT VARIABLE_VALUE 
                FROM information_schema.GLOBAL_STATUS 
                WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests'
            )) * 100, 2
        ), '%'
    ) AS 'Value',
    CASE 
        WHEN (1 - (
            SELECT VARIABLE_VALUE 
            FROM information_schema.GLOBAL_STATUS 
            WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads'
        ) / (
            SELECT VARIABLE_VALUE 
            FROM information_schema.GLOBAL_STATUS 
            WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests'
        )) * 100 > 95 THEN '✅ Excellent'
        WHEN (1 - (
            SELECT VARIABLE_VALUE 
            FROM information_schema.GLOBAL_STATUS 
            WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads'
        ) / (
            SELECT VARIABLE_VALUE 
            FROM information_schema.GLOBAL_STATUS 
            WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests'
        )) * 100 > 90 THEN '⚠️ Good'
        ELSE '❌ Needs Optimization'
    END AS 'Status';

-- 查看 Buffer Pool 使用情况
SELECT 
    'Buffer Pool Usage' AS 'Metric',
    CONCAT(
        ROUND(
            (DATABASE_PAGES * 16384) / 1024 / 1024 / 1024, 2
        ), ' GB'
    ) AS 'Used',
    CONCAT(
        ROUND(
            (POOL_SIZE * 16384) / 1024 / 1024 / 1024, 2
        ), ' GB'
    ) AS 'Total',
    CONCAT(
        ROUND(
            (DATABASE_PAGES * 100.0 / POOL_SIZE), 2
        ), '%'
    ) AS 'Usage_Percentage'
FROM information_schema.INNODB_BUFFER_POOL_STATS
WHERE POOL_ID = 0;

-- 创建性能测试表
CREATE TABLE perf_test_innodb (
    id INT PRIMARY KEY AUTO_INCREMENT,
    data VARCHAR(1000),
    random_num INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_random (random_num)
) ENGINE=InnoDB;

CREATE TABLE perf_test_myisam (
    id INT PRIMARY KEY AUTO_INCREMENT,
    data VARCHAR(1000),
    random_num INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_random (random_num)
) ENGINE=MyISAM;

-- 插入测试数据（注意：实际环境中请谨慎执行）
-- 这里演示少量数据
INSERT INTO perf_test_innodb (data, random_num) VALUES 
('测试数据1', FLOOR(RAND() * 1000)),
('测试数据2', FLOOR(RAND() * 1000)),
('测试数据3', FLOOR(RAND() * 1000)),
('测试数据4', FLOOR(RAND() * 1000)),
('测试数据5', FLOOR(RAND() * 1000));

INSERT INTO perf_test_myisam (data, random_num) VALUES 
('测试数据1', FLOOR(RAND() * 1000)),
('测试数据2', FLOOR(RAND() * 1000)),
('测试数据3', FLOOR(RAND() * 1000)),
('测试数据4', FLOOR(RAND() * 1000)),
('测试数据5', FLOOR(RAND() * 1000));

-- 查看表大小和统计信息对比
SELECT 
    TABLE_NAME,
    ENGINE,
    TABLE_ROWS,
    ROUND(((DATA_LENGTH + INDEX_LENGTH) / 1024), 2) AS 'Size_KB',
    ROUND((DATA_LENGTH / 1024), 2) AS 'Data_KB',
    ROUND((INDEX_LENGTH / 1024), 2) AS 'Index_KB',
    CREATE_TIME,
    UPDATE_TIME
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = 'mysql_learning' 
AND TABLE_NAME LIKE 'perf_test_%'
ORDER BY TABLE_NAME;

-- 性能测试查询
-- 测试1：主键查询
SELECT 'Primary Key Query Test' AS 'Test_Type';
SELECT COUNT(*) FROM perf_test_innodb WHERE id = 1;
SELECT COUNT(*) FROM perf_test_myisam WHERE id = 1;

-- 测试2：索引查询
SELECT 'Index Query Test' AS 'Test_Type';
SELECT COUNT(*) FROM perf_test_innodb WHERE random_num = 500;
SELECT COUNT(*) FROM perf_test_myisam WHERE random_num = 500;

-- 测试3：全表扫描
SELECT 'Full Table Scan Test' AS 'Test_Type';
SELECT COUNT(*) FROM perf_test_innodb;
SELECT COUNT(*) FROM perf_test_myisam;

-- 测试 InnoDB 事务支持
START TRANSACTION;
INSERT INTO test_innodb (name, age, email) VALUES ('测试用户', 25, 'test@example.com');
SELECT * FROM test_innodb WHERE name = '测试用户';
-- 不提交，在另一个会话中查看
-- ROLLBACK; -- 回滚事务

-- 测试 MyISAM 表锁
-- 在会话1中
LOCK TABLE test_myisam WRITE;
INSERT INTO test_myisam (name, age, email) VALUES ('锁测试', 30, 'lock@example.com');
-- 在会话2中尝试查询（会被阻塞）
-- UNLOCK TABLES; -- 释放锁

-- 查看当前锁状态
SELECT 
    r.trx_id AS 'Transaction_ID',
    r.trx_state AS 'State',
    r.trx_started AS 'Started',
    r.trx_requested_lock_id AS 'Requested_Lock',
    r.trx_wait_started AS 'Wait_Started',
    r.trx_weight AS 'Weight',
    r.trx_mysql_thread_id AS 'Thread_ID',
    r.trx_query AS 'Query'
FROM information_schema.INNODB_TRX r;

-- 商品表：读多写少，需要事务支持
CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(200) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    stock INT NOT NULL,
    category_id INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_category (category_id),
    INDEX idx_name (name)
) ENGINE=InnoDB;  -- 选择 InnoDB：支持事务、外键、行锁

-- 订单表：写多读多，强事务要求
CREATE TABLE orders (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    total_amount DECIMAL(10,2) NOT NULL,
    status ENUM('pending', 'paid', 'shipped', 'delivered', 'cancelled') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user_id (user_id),
    INDEX idx_status (status),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB;  -- 选择 InnoDB：支持事务、并发控制

-- 日志表：只写不读，不需要事务
CREATE TABLE access_logs (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    action VARCHAR(100),
    ip_address VARCHAR(45),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_created_at (created_at)
) ENGINE=MyISAM;  -- 选择 MyISAM：写入性能好，空间占用小

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

-- 2. 计算命中率
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';

-- 3. 查看当前 Buffer Pool 大小
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

-- 4. 查看系统内存使用情况
SHOW VARIABLES LIKE 'innodb_buffer_pool_instances';

-- 如果命中率 < 95%，建议增加 Buffer Pool 大小
-- 在 my.cnf 中设置（需要重启）
innodb_buffer_pool_size = 8G  -- 设置为系统内存的 70%
innodb_buffer_pool_instances = 8  -- 提高并发性能