MariaDB存储引擎 #

存储引擎概念
├── 数据存储和检索的底层实现
├── 决定表的特性
│   ├── 事务支持
│   ├── 锁粒度
│   ├── 索引类型
│   └── 存储格式
└── 可以为不同表选择不同引擎

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

-- 查看默认存储引擎
SELECT @@default_storage_engine;

-- 查看表的存储引擎
SHOW TABLE STATUS LIKE 'users';
SELECT ENGINE FROM information_schema.tables WHERE table_name = 'users';

InnoDB特点
├── 事务支持
│   ├── ACID特性
│   └── 行级锁
├── 外键约束
├── 崩溃恢复
├── MVCC（多版本并发控制）
├── 聚簇索引
└── 缓冲池

-- 创建InnoDB表（默认）
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    email VARCHAR(100)
) ENGINE=InnoDB;

-- 显式指定
CREATE TABLE orders (
    id INT PRIMARY KEY,
    user_id INT,
    amount DECIMAL(10,2),
    FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB;

[mysqld]
# 缓冲池大小（物理内存的50-70%）
innodb_buffer_pool_size = 4G

# 缓冲池实例数
innodb_buffer_pool_instances = 4

# 日志文件大小
innodb_log_file_size = 512M

# 日志缓冲区
innodb_log_buffer_size = 16M

# 刷新策略
innodb_flush_log_at_trx_commit = 1

# 刷新方法
innodb_flush_method = O_DIRECT

# 文件格式
innodb_file_per_table = 1

-- 查看InnoDB状态
SHOW ENGINE INNODB STATUS;

-- 查看缓冲池状态
SHOW STATUS LIKE 'Innodb_buffer_pool%';

-- 查看行锁
SHOW STATUS LIKE 'Innodb_row_lock%';

Aria特点
├── MariaDB增强的MyISAM
├── 支持崩溃恢复
├── 页级缓存
├── 支持事务（部分）
├── 更好的并发性能
└── 内部临时表默认引擎

-- 创建Aria表
CREATE TABLE logs (
    id INT PRIMARY KEY AUTO_INCREMENT,
    message TEXT,
    created_at DATETIME
) ENGINE=Aria;

-- 创建带事务支持的Aria表
CREATE TABLE temp_data (
    id INT PRIMARY KEY,
    data VARCHAR(100)
) ENGINE=Aria TRANSACTIONAL=1;

[mysqld]
# Aria缓存大小
aria_pagecache_buffer_size = 128M

# 日志文件大小
aria_log_file_size = 1G

# 日志级别
aria_log_level = 1

ColumnStore特点
├── 列式存储
├── 数据压缩
├── 大数据分析
├── 并行查询
├── 适合OLAP场景
└── 不适合OLTP场景

-- 创建ColumnStore表
CREATE TABLE analytics (
    id INT,
    event_date DATE,
    user_id INT,
    event_type VARCHAR(50),
    amount DECIMAL(10,2)
) ENGINE=ColumnStore;

-- 列式存储优势
-- 1. 只读取需要的列
-- 2. 更高的压缩比
-- 3. 适合聚合查询

-- 适合：聚合分析
SELECT 
    event_date,
    event_type,
    COUNT(*) AS event_count,
    SUM(amount) AS total_amount
FROM analytics
WHERE event_date BETWEEN '2024-01-01' AND '2024-12-31'
GROUP BY event_date, event_type;

-- 不适合：频繁更新
-- ColumnStore适合批量导入，不适合频繁的单行更新

Spider特点
├── 分布式存储
├── 透明访问远程表
├── 支持分片
├── 支持XA事务
└── 适合水平扩展

-- 创建远程服务器
CREATE SERVER remote_server
FOREIGN DATA WRAPPER mysql
OPTIONS (
    HOST '192.168.1.100',
    DATABASE 'mydb',
    USER 'spider',
    PASSWORD 'password'
);

-- 创建Spider表
CREATE TABLE remote_users (
    id INT PRIMARY KEY,
    name VARCHAR(50)
) ENGINE=Spider
COMMENT='wrapper "mysql", srv "remote_server", table "users"';

-- 查询远程表
SELECT * FROM remote_users;

-- 创建分片表
CREATE TABLE sharded_orders (
    id INT PRIMARY KEY,
    user_id INT,
    amount DECIMAL(10,2)
) ENGINE=Spider
COMMENT='wrapper "mysql", table "orders"'
PARTITION BY HASH (user_id) (
    PARTITION p1 COMMENT='srv "server1"',
    PARTITION p2 COMMENT='srv "server2"',
    PARTITION p3 COMMENT='srv "server3"'
);

Memory特点
├── 数据存储在内存中
├── 极快的访问速度
├── 支持Hash索引
├── 不支持事务
├── 不支持BLOB/TEXT
└── 重启后数据丢失

-- 创建Memory表
CREATE TABLE session_data (
    session_id VARCHAR(32) PRIMARY KEY,
    user_id INT,
    data TEXT,
    created_at DATETIME
) ENGINE=Memory;

-- 使用Hash索引
CREATE TABLE lookup (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    KEY USING HASH (name)
) ENGINE=Memory;

-- 适合：临时数据、缓存
-- 会话存储
CREATE TABLE sessions (
    id VARCHAR(32) PRIMARY KEY,
    data TEXT,
    expires INT
) ENGINE=Memory;

-- 查询缓存
CREATE TABLE query_cache (
    query_hash VARCHAR(32) PRIMARY KEY,
    result TEXT,
    expires INT
) ENGINE=Memory;

MyISAM特点
├── 不支持事务
├── 表级锁
├── 全文索引
├── 空间索引
├── 崩溃恢复能力弱
└── 适合只读场景

-- 创建MyISAM表
CREATE TABLE articles (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(200),
    content TEXT,
    FULLTEXT INDEX ft_content (content)
) ENGINE=MyISAM;

-- 全文搜索
SELECT * FROM articles 
WHERE MATCH(content) AGAINST('database');

-- CSV引擎：存储为CSV文件
CREATE TABLE csv_data (
    id INT,
    name VARCHAR(50),
    email VARCHAR(100)
) ENGINE=CSV;

INSERT INTO csv_data VALUES (1, 'John', 'john@example.com');

-- 数据存储为CSV文件
-- /var/lib/mysql/mydb/csv_data.CSV

-- Archive引擎：归档存储
CREATE TABLE archived_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    message TEXT,
    created_at DATETIME
) ENGINE=Archive;

-- 特点：
-- 1. 高压缩比
-- 2. 只支持INSERT和SELECT
-- 3. 适合日志归档

-- Blackhole引擎：黑洞，数据不存储
CREATE TABLE blackhole_data (
    id INT PRIMARY KEY,
    data VARCHAR(100)
) ENGINE=BLACKHOLE;

-- 用途：
-- 1. 复制过滤
-- 2. 性能测试
-- 3. 审计日志

-- 转换表引擎
ALTER TABLE users ENGINE=InnoDB;

-- 注意：转换过程会锁表
-- 大表转换建议使用pt-online-schema-change