MySQL常用存储引擎解析：特性与适用场景指南

-- 查看当前MySQL支持的存储引擎
SHOW ENGINES\G
 
-- 查看表的存储引擎
SHOW TABLE STATUS LIKE 'your_table'\G
 
-- 修改表的存储引擎
ALTER TABLE your_table ENGINE=InnoDB;

// InnoDB缓冲池管理的核心结构（简化版）
struct buf_pool_t {
    hash_table_t* page_hash;      // 页面哈希表
    UT_LIST_BASE_NODE_T(buf_page_t) free;   // 空闲页面列表
    UT_LIST_BASE_NODE_T(buf_page_t) LRU;    // LRU链表
    ulint curr_size;               // 当前缓冲池大小
    ulint n_chunks;                // 内存块数量
};

-- 观察InnoDB的行结构（需要开启innodb_monitor）
SHOW ENGINE INNODB STATUS\G
 
-- 查看当前事务
SELECT * FROM information_schema.INNODB_TRX;

-- 缓冲池大小（通常设置为物理内存的70-80%）
innodb_buffer_pool_size = 8G
 
-- 日志文件大小（影响恢复速度）
innodb_log_file_size = 512M
 
-- 刷新日志频率（平衡性能与安全性）
innodb_flush_log_at_trx_commit = 1

// MyISAM数据文件格式（.MYD文件）
struct MYD_DATA_FILE {
    uchar file_header[4];           // 文件魔数
    ulonglong rec_per_key_part;     // 每个键的记录数
    ulonglong data_file_length;     // 数据文件长度
    ulonglong max_data_file_length; // 最大文件长度
    ulonglong index_file_length;    // 索引文件长度
    ulonglong max_index_file_length;// 最大索引长度
    ulonglong create_time;          // 创建时间
    ulonglong recover_time;         // 恢复时间
    ulonglong check_time;           // 检查时间
};

-- 创建全文索引（MyISAM）
CREATE TABLE articles (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200),
    body TEXT,
    FULLTEXT(title, body)
) ENGINE=MyISAM;
 
-- 使用全文搜索
SELECT * FROM articles 
WHERE MATCH(title, body) AGAINST('MySQL存储引擎' IN NATURAL LANGUAGE MODE);

# 使用myisampack压缩表
myisampack -b /var/lib/mysql/mydb/history_table.MYI
 
# 压缩后的表变为只读，但空间节省可达70%

-- 创建内存表
CREATE TABLE session_cache (
    session_id VARCHAR(128) PRIMARY KEY,
    user_data JSON,
    expire_time TIMESTAMP,
    INDEX idx_expire(expire_time)
) ENGINE=MEMORY;
 
-- 查看内存使用情况
SHOW TABLE STATUS LIKE 'session_cache'\G

// Memory引擎的哈希索引结构
struct hash_info {
    uint key_length;        // 键长度
    uint blength;           // 桶数量
    uint records;           // 记录数
    uint deleted;           // 删除记录数
    uchar *key;             // 键指针
    void *next;             // 下一个指针
};

-- 查看临时表的使用
EXPLAIN FORMAT=JSON 
SELECT * FROM large_table 
ORDER BY non_indexed_column;
 
-- 在JSON输出中查找"using_temporary_table": true

-- 会话管理实战示例
CREATE TABLE user_sessions (
    session_id VARCHAR(128) PRIMARY KEY,
    user_id INT NOT NULL,
    login_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_active TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    ip_address VARCHAR(45),
    user_agent TEXT,
    INDEX idx_user(user_id),
    INDEX idx_active(last_active)
) ENGINE=MEMORY;
 
-- 定期清理过期会话
DELETE FROM user_sessions 
WHERE last_active < DATE_SUB(NOW(), INTERVAL 30 MINUTE);

-- 用户核心数据：InnoDB（事务安全）
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    balance DECIMAL(10,2) DEFAULT 0.00,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_username(username),
    INDEX idx_email(email)
) ENGINE=InnoDB;
 
-- 用户行为日志：Archive（高压缩比）
CREATE TABLE user_logs (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    action VARCHAR(100),
    details JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=ARCHIVE;
 
-- 实时排行榜：Memory（极速查询）
CREATE TABLE leaderboard (
    user_id INT PRIMARY KEY,
    score INT NOT NULL,
    rank INT NOT NULL,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_score(score DESC)
) ENGINE=MEMORY;
 
-- 商品搜索：MyISAM（全文索引）
CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255),
    description TEXT,
    price DECIMAL(10,2),
    FULLTEXT KEY ft_name_desc (name, description)
) ENGINE=MyISAM;

-- InnoDB监控查询
SELECT 
    ENGINE,
    SUM(DATA_LENGTH) as data_size,
    SUM(INDEX_LENGTH) as index_size,
    COUNT(*) as table_count
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA NOT IN ('mysql','information_schema','performance_schema')
GROUP BY ENGINE;
 
-- 锁等待监控
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
JOIN information_schema.INNODB_TRX b ON b.trx_id = w.blocking_trx_id
JOIN information_schema.INNODB_TRX r ON r.trx_id = w.requesting_trx_id;

# TRAE IDE AI引擎的推荐逻辑（简化版）
def recommend_engine(table_schema, query_patterns):
    features = extract_features(table_schema, query_patterns)
    
    # 基于历史项目训练的推荐模型
    if features['transaction_required']:
        return 'InnoDB'
    elif features['read_heavy'] and features['fulltext_needed']:
        return 'MyISAM'
    elif features['temporary_data']:
        return 'Memory'
    elif features['archive_data']:
        return 'Archive'
    else:
        return 'InnoDB'  # 默认推荐