MySQL - 存储引擎

MySQL - 存储引擎

存储引擎（Storage Engine）是数据库管理系统（如 MySQL）用于在数据库中创建、读取、更新和删除（CRUD）数据的底层软件组件。MySQL 具有可插拔的存储引擎架构，允许您根据特定需求选择最合适的引擎。每个引擎都有独特的功能、性能特点和权衡。

存储引擎大致可分为事务型（支持 ACID 特性）和非事务型。自 MySQL 5.5 以来，InnoDB 已成为通用用途的默认和最推荐的存储引擎。

主要存储引擎

以下是现代 MySQL 版本中一些最重要的存储引擎：

InnoDB

• 默认和通用： 之所以成为默认引擎是有原因的。它是绝大多数应用程序的最佳选择。
• ACID 兼容： 全面支持事务（COMMIT、ROLLBACK、SAVEPOINT），确保数据完整性。
• 行级锁： 通过仅锁定正在修改的行（而不是整个表）来允许高并发。这对于有许多并发写入操作的应用程序至关重要。
• 崩溃恢复： 具有自动崩溃恢复机制，可保护用户数据免受服务器故障的影响。
• 外键约束： 强制执行表之间的参照完整性。

MyISAM

• 传统引擎： 在 MySQL 5.5 之前是默认引擎。现在在新应用程序中很少使用。
• 非事务型： 不支持事务，因此不适用于需要数据完整性保证的应用程序。
• 表级锁： 在写入操作期间锁定整个表，这在高并发环境中可能导致显著的性能瓶颈。
• 特定用例： 仍可能在传统系统或非常特定的只读场景中找到。对于全文搜索，InnoDB 的功能现在已与其持平或更优。

MEMORY (HEAP)

• 内存存储： 将所有数据存储在 RAM 中以实现极快的访问。非常适合临时表、缓存或会话管理。
• 易失性： 当 MySQL 服务器重启时，所有数据都会丢失。不适用于永久数据存储。
• 表级锁： 与 MyISAM 类似，它使用表级锁。

CSV

• 数据交换： 将数据存储在逗号分隔值 (.csv) 文本文件中。便于导入或导出数据以供其他应用程序（如电子表格）使用。
• 无索引： 不支持索引，因此不适合通用数据库使用。

ARCHIVE

• 高压缩存储： 设计用于存储大量未索引的历史或存档数据，占用空间非常小。它是一个仅追加（append-only）引擎（支持 INSERT 和 SELECT，但不支持 DELETE 或 UPDATE）。

BLACKHOLE

• 数据黑洞： BLACKHOLE 引擎接受数据但不存储它。它就像 /dev/null。其主要用途是在复制设置中，将 SQL 语句转发到副本，而不在源服务器上存储数据。

查看可用存储引擎

您可以使用 SHOW ENGINES 语句列出所有可用的存储引擎并检查它们的状态。

SHOW ENGINES;

输出将显示引擎列表。Support 列指示引擎的状态：DEFAULT 表示默认引擎，YES 表示可用，NO 表示不可用。Transactions 列对于识别 ACID 兼容引擎至关重要。

管理表的存储引擎

表创建时设置引擎

您可以使用 ENGINE 子句为新表指定存储引擎。如果省略，将使用服务器的默认引擎 (InnoDB)。

-- 使用 MEMORY 引擎创建用于缓存的临时表
CREATE TABLE session_data (
    session_id VARCHAR(128) PRIMARY KEY,
    session_data TEXT,
    last_access TIMESTAMP
) ENGINE = MEMORY;

更改表的存储引擎

您可以使用 ALTER TABLE 将现有表转换为不同的存储引擎。此操作将重建表及其索引，对于大型表来说可能非常耗时。

-- 将旧的 MyISAM 表转换为 InnoDB
ALTER TABLE legacy_table ENGINE = InnoDB;

验证表的引擎

要检查特定表的存储引擎，您可以查询 information_schema。

SELECT TABLE_NAME, ENGINE
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'your_database_name' AND TABLE_NAME = 'your_table_name';

在客户端程序中使用存储引擎

虽然存储引擎管理通常是通过 SQL 客户端完成的管理任务，但您可以从任何编程语言执行这些命令。以下是如何验证表引擎的示例。

Python 示例

import mysql.connector

def get_table_engine(db_config, db_name, table_name):
    query = """
        SELECT ENGINE FROM information_schema.TABLES
        WHERE TABLE_SCHEMA = %s AND TABLE_NAME = %s
    """
    try:
        with mysql.connector.connect(**db_config) as cnx:
            with cnx.cursor() as cursor:
                cursor.execute(query, (db_name, table_name))
                result = cursor.fetchone()
                if result:
                    print(f"The engine for table '{table_name}' is: {result[0]}")
                else:
                    print(f"Table '{table_name}' not found.")
    except mysql.connector.Error as err:
        print(f"Database Error: {err}")

# 示例用法
db_config = {'user': 'root', 'password': 'password', 'host': '127.0.0.1'}
get_table_engine(db_config, 'your_database_name', 'users')

Node.js (使用 mysql2) 示例

const mysql = require('mysql2/promise');

async function getTableEngine(dbConfig, dbName, tableName) {
    let connection;
    try {
        connection = await mysql.createConnection(dbConfig);
        const query = `
            SELECT ENGINE FROM information_schema.TABLES
            WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?
        `;
        const [rows, fields] = await connection.execute(query, [dbName, tableName]);

        if (rows.length > 0) {
            console.log(`The engine for table '${tableName}' is: ${rows[0].ENGINE}`);
        } else {
            console.log(`Table '${tableName}' not found.`);
        }
    } catch (err) {
        console.error(`Database Error: ${err.message}`);
    } finally {
        if (connection) await connection.end();
    }
}

// 示例用法
const dbConfig = { host: 'localhost', user: 'root', password: 'password' };
getTableEngine(dbConfig, 'your_database_name', 'users');