-- ACID事务示例
START TRANSACTION;
-- 转账操作：从账户A向账户B转账100元
UPDATE accounts SET balance = balance - 100 WHERE account_id = 'A';
UPDATE accounts SET balance = balance + 100 WHERE account_id = 'B';
-- 检查业务规则
SELECT SUM(balance) INTO @total FROM accounts;
IF @total != 2000 THEN
    ROLLBACK;
    SELECT '转账失败：总额不匹配';
ELSE
    COMMIT;
    SELECT '转账成功';
END IF;

-- 行级锁示例
-- 会话1
START TRANSACTION;
UPDATE products SET stock = stock - 1 WHERE product_id = 100;
-- 此时只锁定product_id=100的行
-- 会话2（可以并发执行）
START TRANSACTION;
UPDATE products SET stock = stock - 1 WHERE product_id = 200;
-- 不会阻塞，因为锁定不同的行
-- 查看锁信息
SELECT * FROM information_schema.INNODB_LOCKS;
SELECT * FROM information_schema.INNODB_LOCK_WAITS;

-- 外键约束示例
CREATE TABLE departments (
    dept_id INT PRIMARY KEY AUTO_INCREMENT,
    dept_name VARCHAR(100) NOT NULL
) ENGINE=InnoDB;
CREATE TABLE employees (
    emp_id INT PRIMARY KEY AUTO_INCREMENT,
    emp_name VARCHAR(100) NOT NULL,
    dept_id INT,
    FOREIGN KEY (dept_id) 
        REFERENCES departments(dept_id)
        ON DELETE SET NULL
        ON UPDATE CASCADE
) ENGINE=InnoDB;
-- 违反外键约束的操作会被拒绝
INSERT INTO employees (emp_name, dept_id) VALUES ('John', 999);
-- 错误：Cannot add or update a child row: a foreign key constraint fails

-- 查看InnoDB配置
SHOW VARIABLES LIKE 'innodb_%';
-- 关键配置参数
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';    -- 缓冲池大小
SHOW VARIABLES LIKE 'innodb_log_file_size';       -- 重做日志大小
SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit'; -- 刷盘策略
SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';   -- 锁等待超时
-- 查看InnoDB状态
SHOW ENGINE INNODB STATUS\G

MyISAM文件结构：
├── table_name.frm    # 表结构定义
├── table_name.MYD    # 数据文件
└── table_name.MYI    # 索引文件

-- MyISAM表锁示例
-- 会话1
LOCK TABLE myisam_table WRITE;
UPDATE myisam_table SET counter = counter + 1;
-- 此时整个表被锁定
-- 会话2（会被阻塞）
SELECT * FROM myisam_table;
-- 需要等待会话1释放锁
-- 查看表锁状态
SHOW STATUS LIKE 'Table_locks_%';

-- MyISAM全文索引示例
CREATE TABLE articles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200),
    content TEXT,
    FULLTEXT(title, content)
) ENGINE=MyISAM;
-- 使用全文搜索
SELECT * FROM articles 
WHERE MATCH(title, content) AGAINST('MySQL database' IN NATURAL LANGUAGE MODE);
-- 查看全文索引相关配置
SHOW VARIABLES LIKE 'ft_%';

-- 创建MyISAM表
CREATE TABLE log_data (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    log_time DATETIME,
    message TEXT,
    INDEX idx_time (log_time)
) ENGINE=MyISAM;
-- 批量插入性能测试
-- MyISAM在批量插入时表现更好，因为锁开销小
INSERT INTO log_data (log_time, message) VALUES
(NOW(), 'Log message 1'),
(NOW(), 'Log message 2'),
(NOW(), 'Log message 3');

-- 创建Memory表
CREATE TABLE session_data (
    session_id VARCHAR(128) PRIMARY KEY,
    user_id INT,
    data TEXT,
    created_time DATETIME,
    INDEX idx_user (user_id)
) ENGINE=MEMORY;
-- 查看内存表状态
SHOW TABLE STATUS LIKE 'session_data';

-- 适合Memory引擎的场景
CREATE TABLE temporary_config (
    config_key VARCHAR(100) PRIMARY KEY,
    config_value VARCHAR(500),
    updated_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=MEMORY;
-- 注意事项：服务器重启后数据丢失
-- 解决方案：通过触发器持久化到磁盘表
CREATE TABLE config_backup (
    config_key VARCHAR(100) PRIMARY KEY,
    config_value VARCHAR(500),
    updated_time TIMESTAMP
) ENGINE=InnoDB;
DELIMITER $$
CREATE TRIGGER backup_config 
    BEFORE INSERT ON temporary_config
    FOR EACH ROW
BEGIN
    INSERT INTO config_backup VALUES (NEW.config_key, NEW.config_value, NEW.updated_time)
    ON DUPLICATE KEY UPDATE config_value = NEW.config_value, updated_time = NEW.updated_time;
END$$
DELIMITER ;

-- 创建Archive表
CREATE TABLE audit_log_archive (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    log_time DATETIME,
    user_action VARCHAR(100),
    details TEXT
) ENGINE=ARCHIVE;
-- Archive表只支持INSERT和SELECT
-- 不支持UPDATE、DELETE、索引
INSERT INTO audit_log_archive (log_time, user_action, details) 
SELECT log_time, user_action, details FROM audit_log 
WHERE log_time < '2023-01-01';
-- 查看压缩效果
SELECT table_name, engine, data_length, index_length 
FROM information_schema.tables 
WHERE table_name = 'audit_log_archive';

-- 创建CSV表
CREATE TABLE data_export (
    id INT,
    name VARCHAR(100),
    value DECIMAL(10,2),
    created_date DATE
) ENGINE=CSV;
-- 数据直接存储在CSV文件中
-- 对应的文件：data_export.CSV
-- 可以从外部CSV文件加载数据
-- 或者将数据导出到CSV文件

-- 创建Blackhole表
CREATE TABLE blackhole_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    event_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    event_type VARCHAR(50),
    event_data JSON
) ENGINE=BLACKHOLE;
-- 数据写入后立即丢弃，但会写二进制日志
-- 适用于复制架构中的数据过滤

-- 性能测试示例：创建测试表
CREATE TABLE test_innodb (
    id INT AUTO_INCREMENT PRIMARY KEY,
    data VARCHAR(500),
    created_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_created (created_time)
) ENGINE=InnoDB;
CREATE TABLE test_myisam (
    id INT AUTO_INCREMENT PRIMARY KEY,
    data VARCHAR(500),
    created_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_created (created_time)
) ENGINE=MyISAM;
-- 性能对比维度
-- 1. 并发读性能
-- 2. 并发写性能  
-- 3. 存储空间占用
-- 4. 崩溃恢复能力

-- 查看各引擎表的大小
SELECT 
    table_name,
    engine,
    round((data_length + index_length) / 1024 / 1024, 2) as total_size_mb,
    round(data_length / 1024 / 1024, 2) as data_size_mb,
    round(index_length / 1024 / 1024, 2) as index_size_mb,
    table_rows
FROM information_schema.tables 
WHERE table_schema = 'your_database'
ORDER BY total_size_mb DESC;

-- 必须使用InnoDB
CREATE TABLE orders (
    order_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    status ENUM('pending', 'paid', 'shipped', 'completed'),
    created_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(user_id)
) ENGINE=InnoDB;

-- 根据需求选择
-- 选项1：需要实时查询
CREATE TABLE access_log (
    log_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    ip_address VARCHAR(45),
    request_time DATETIME,
    user_agent TEXT,
    INDEX idx_time (request_time)
) ENGINE=InnoDB;
-- 选项2：历史数据归档
CREATE TABLE access_log_archive (
    log_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    ip_address VARCHAR(45),
    request_time DATETIME,
    user_agent TEXT
) ENGINE=ARCHIVE;

-- Memory引擎适合场景
CREATE TABLE user_sessions (
    session_id VARCHAR(128) PRIMARY KEY,
    user_data JSON,
    last_activity TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_activity (last_activity)
) ENGINE=MEMORY;
-- 配合持久化机制
CREATE TABLE sessions_backup (
    session_id VARCHAR(128) PRIMARY KEY,
    user_data JSON,
    last_activity TIMESTAMP
) ENGINE=InnoDB;

-- 查看支持的存储引擎
SHOW ENGINES;
-- 查看表的存储引擎
SHOW TABLE STATUS LIKE 'table_name';
-- 修改表的存储引擎
ALTER TABLE table_name ENGINE = InnoDB;
-- 创建表时指定引擎
CREATE TABLE example (
    id INT PRIMARY KEY,
    data VARCHAR(100)
) ENGINE=MyISAM;
-- 批量修改引擎（谨慎操作）
SELECT CONCAT('ALTER TABLE ', table_name, ' ENGINE=InnoDB;') 
FROM information_schema.tables 
WHERE table_schema = 'your_database' 
  AND engine = 'MyISAM';

-- 1. 备份数据
-- 2. 检查外键约束
SELECT TABLE_NAME, CONSTRAINT_NAME, CONSTRAINT_TYPE
FROM information_schema.TABLE_CONSTRAINTS 
WHERE TABLE_SCHEMA = 'your_database';
-- 3. 转换MyISAM到InnoDB的完整流程
SET FOREIGN_KEY_CHECKS = 0;
-- 逐个表转换，避免锁表时间过长
ALTER TABLE large_table ENGINE=InnoDB;
-- 重新创建外键约束
ALTER TABLE child_table 
ADD FOREIGN KEY (parent_id) REFERENCES parent_table(id);
SET FOREIGN_KEY_CHECKS = 1;
-- 4. 更新统计信息
ANALYZE TABLE table_name;

-- InnoDB关键优化参数
-- 在my.cnf中配置
[mysqld]
# 缓冲池大小（物理内存的70-80%）
innodb_buffer_pool_size = 4G
# 日志文件大小
innodb_log_file_size = 1G
innodb_log_files_in_group = 2
# 刷盘策略（1=安全，2=性能，0=折中）
innodb_flush_log_at_trx_commit = 1
# IO设置
innodb_flush_method = O_DIRECT
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000
# 并发设置
innodb_thread_concurrency = 0
innodb_read_io_threads = 8
innodb_write_io_threads = 8

-- MyISAM优化配置
[mysqld]
# 键缓存大小
key_buffer_size = 512M
# 并发插入设置
concurrent_insert = 2
# 修复设置
myisam_recover_options = BACKUP,FORCE
-- 维护操作
REPAIR TABLE myisam_table;
OPTIMIZE TABLE myisam_table;
CHECK TABLE myisam_table;

-- InnoDB状态监控
SHOW ENGINE INNODB STATUS\G
-- 关键指标监控
SHOW STATUS LIKE 'Innodb_buffer_pool%';
SHOW STATUS LIKE 'Innodb_rows_%';
SHOW STATUS LIKE 'Innodb_log_%';
-- MyISAM状态监控  
SHOW STATUS LIKE 'Key_%';
SHOW STATUS LIKE 'Table_locks_%';
-- 性能Schema监控
SELECT * FROM performance_schema.file_summary_by_instance;
SELECT * FROM performance_schema.table_io_waits_summary_by_table;

-- 1. InnoDB锁等待
SELECT * FROM information_schema.INNODB_LOCKS;
SELECT * FROM information_schema.INNODB_LOCK_WAITS;
-- 2. MyISAM表损坏
REPAIR TABLE damaged_table;
-- 3. Memory引擎内存不足
SHOW VARIABLES LIKE 'max_heap_table_size';
SHOW VARIABLES LIKE 'tmp_table_size';
-- 4. 引擎转换失败处理
-- 检查错误日志，确认具体错误原因
-- 常见原因：外键约束、全文索引、空间索引等