笙亿网络策划

MySQL 存储引擎有哪些？怎么选择和使用？

约 24 分钟读完 36 阅
文章导读
上一个 测验 下一个 MySQL 存储引擎 正如我们所知，MySQL 数据库用于以行和列的形式存储数据。MySQL 存储引擎 是一个用于处理管理这些数据的 SQL 操作的组件。它负责诸如创建表、重命名表、更新或删除表等简单任务，这些任务对于提升数据库性能至关重要。
📋 目录
  1. Ⅰ MySQL 存储引擎
  2. Ⅱ 常用存储引擎
  3. Ⅲ SHOW ENGINES 语句
  4. Ⅳ 设置存储引擎
  5. Ⅴ 更改默认存储引擎
  6. Ⅵ 更改存储引擎
  7. Ⅶ 使用客户端程序的存储引擎
A A

MySQL - 存储引擎

目录
  • MySQL 存储引擎
  • 常见存储引擎
  • SHOW ENGINES 语句
  • 设置存储引擎
  • 更改默认存储引擎
  • 更改存储引擎
  • 使用客户端程序的存储引擎
上一个
测验
下一个

MySQL 存储引擎

正如我们所知，MySQL 数据库用于以行和列的形式存储数据。MySQL 存储引擎 是一个用于处理管理这些数据的 SQL 操作的组件。它负责诸如创建表、重命名表、更新或删除表等简单任务，这些任务对于提升数据库性能至关重要。

存储引擎分为两类：事务性引擎和非事务性引擎。许多常见的存储引擎都属于这些类别之一。然而，在 MySQL 中，默认的存储引擎是 InnoDB。

常用存储引擎

以下是用于处理 MySQL 的各种常用存储引擎 −

InnoDB Storage Engine

  • ACID 合规 − InnoDB 是 MySQL 5.5 及更高版本的默认存储引擎。它是一个事务性数据库引擎，确保 ACID 合规性，这意味着它支持 commit 和 rollback 等操作。
  • 崩溃恢复 − InnoDB 提供崩溃恢复功能来保护用户数据。
  • 行级锁 − 它支持行级锁，这提高了多用户并发性和性能。
  • 引用完整性 − 它还强制执行 FOREIGN KEY 引用完整性约束。

ISAM Storage Engine

  • 已弃用 − ISAM（Indexed Sequential Access Method，索引顺序访问方法）在早期 MySQL 版本中得到支持，但已在最近版本中被弃用并移除。
  • 大小受限 − ISAM 表的大小限制为 4GB。

MyISAM Storage Engine

  • 可移植性 − MyISAM 设计用于可移植性，解决了 ISAM 的不可移植问题。
  • 性能 − 它比 ISAM 提供更快的性能，并且在 MySQL 5.x 之前是默认存储引擎。
  • 内存效率 − MyISAM 表占用内存较小，适合只读或主要读的工作负载。

MERGE Storage Engine

  • 逻辑组合 − MERGE 表允许 MySQL 开发人员逻辑上组合多个相同的 MyISAM 表，并将它们引用为一个对象。
  • 有限操作 − MERGE 表仅允许 INSERT、SELECT、DELETE 和 UPDATE 操作。如果使用 DROP 查询，只有存储引擎规范会被重置，而表本身保持不变。

MEMORY Storage Engine

  • 内存存储 − MEMORY 表将数据完全存储在 RAM 中，优化快速查找的访问速度。
  • 哈希索引 − 它使用哈希索引来实现更快的数据检索。
  • 使用减少 − 其使用场景正在减少；其他引擎如 InnoDB 的 buffer pool 内存区域提供了更好的内存管理。

CSV Storage Engine

  • CSV 格式 − CSV 表是带有逗号分隔值的文本文件，适用于与脚本和应用程序进行数据交换。
  • 无索引 − 它们没有索引，通常在与 InnoDB 表一起进行数据导入或导出时使用。

NDBCLUSTER Storage Engine

  • 集群 − NDBCLUSTER（也称为 NDB）是一个集群数据库引擎，适用于需要最高正常运行时间和可用性的应用程序。

ARCHIVE Storage Engine

  • 历史数据 − ARCHIVE 表非常适合存储和检索大量历史、归档或安全数据。ARCHIVE 存储引擎支持非索引表

BLACKHOLE Storage Engine

  • 数据丢弃 − BLACKHOLE 表接受数据但不存储它，总是返回空集。
  • 用法 − 用于复制配置中，DML 语句被发送到副本服务器，但源服务器不保留其自己的数据副本。

FEDERATED Storage Engine

  • 分布式数据库 − FEDERATED 允许链接独立的 MySQL 服务器，从多个物理服务器创建逻辑数据库，在分布式环境中非常有用。

EXAMPLE Storage Engine

  • 开发工具 − EXAMPLE 是 MySQL 源代码中的一个工具，作为开发人员开始编写新存储引擎的示例。您可以创建使用此引擎的表，但它不存储或检索数据。

尽管有这么多可以与数据库一起使用的存储引擎，但没有一种被称为完美的存储引擎。在某些情况下，一种存储引擎可能更适合使用，而在其他情况下，其他引擎性能更好。因此，在特定环境中工作时，必须仔细选择要使用的 Storage engine。

要选择引擎，可以使用 SHOW ENGINES 语句。

SHOW ENGINES 语句

MySQL 中的 SHOW ENGINES 语句将列出所有存储引擎。在选择数据库支持且易于使用的引擎时，可以考虑使用它。

语法

以下是 SHOW ENGINES 语句的语法 −

SHOW ENGINES\G

其中，'\G' 分隔符用于垂直对齐执行该语句获得的结果集。

示例

让我们通过以下查询在 MySQL 数据库中执行 SHOW ENGINES 语句，观察获得的结果集 −

SHOW ENGINES\G

输出

以下是获得的结果集。在此，您可以检查 MySQL 数据库支持哪些存储引擎以及它们最适合的使用场景 −

*************************** 1. row ************************
      Engine: MEMORY
     Support: YES
     Comment: 基于哈希，存储在内存中，适用于临时表
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 2. row ************************
      Engine: MRG_MYISAM
     Support: YES
     Comment: 相同 MyISAM 表的集合
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 3. row ************************
      Engine: CSV
     Support: YES
     Comment: CSV 存储引擎
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 4. row ************************
      Engine: FEDERATED
     Support: NO
     Comment: Federated MySQL 存储引擎
Transactions: NULL
          XA: NULL
  Savepoints: NULL
*************************** 5. row ************************
      Engine: PERFORMANCE_SCHEMA
     Support: YES
     Comment: Performance Schema
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 6. row ************************
      Engine: MyISAM
     Support: YES
     Comment: MyISAM 存储引擎
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 7. row ************************
      Engine: InnoDB
     Support: DEFAULT
     Comment: 支持事务、行级锁和外键
Transactions: YES
          XA: YES
  Savepoints: YES
*************************** 8. row ************************
      Engine: ndbinfo
     Support: NO
     Comment: MySQL Cluster 系统信息存储引擎
Transactions: NULL
          XA: NULL
  Savepoints: NULL
*************************** 9. row ************************
      Engine: BLACKHOLE
     Support: YES
     Comment: /dev/null 存储引擎（写入其中的任何内容都会消失）
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 10. row ************************
      Engine: ARCHIVE
     Support: YES
     Comment: Archive 存储引擎
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 11. row ************************
      Engine: ndbcluster
     Support: NO
     Comment: 集群化、高容错表
Transactions: NULL
          XA: NULL
  Savepoints: NULL
11 rows in set (0.00 sec)

设置存储引擎

一旦为表选择了存储引擎，您可能希望在创建数据库表时设置它。这是通过在 CREATE TABLE 语句中添加要使用的引擎名称来指定引擎类型的。

如果您没有指定引擎类型，将自动使用默认引擎（MySQL 中的 InnoDB）。

语法

以下是在 CREATE TABLE 语句中设置存储引擎的语法 −

CREATE TABLE table_name (
   column_name1 datatype,
   column_name2 datatype,
   .
   .
   .
) ENGINE = engine_name;

示例

在本示例中，让我们使用以下查询在 MyISAM 存储引擎上创建一个名为 'TEST' 的新表 −

CREATE TABLE TEST (
   ROLL INT,
   NAME VARCHAR(25),
   MARKS DECIMAL(20, 2)
) ENGINE = MyISAM;

得到的结果如下所示 −

Query OK, 0 rows affected (0.01 sec)

但是，如果我们在 MySQL 不支持的引擎上创建表，例如 FEDERATED，将引发错误 −

CREATE TABLE TEST (
   ROLL INT,
   NAME VARCHAR(25),
   MARKS DECIMAL(20, 2)
) ENGINE = FEDERATED;

我们将得到以下错误 −

ERROR 1286 (42000): Unknown storage engine 'FEDERATED'

更改默认存储引擎

MySQL 还提供了三种方式来更改默认存储引擎选项 −

  • 使用 '--default-storage-engine=name' 服务器启动选项。

  • 在 'my.cnf' 配置文件中设置 'default-storage-engine' 选项。

  • 使用 SET 语句

语法

让我们看看使用 SET 语句更改数据库中默认存储引擎的语法 −

SET default_storage_engine = engine_name;

注意 − 使用 CREATE TEMPORARY TABLE 语句创建的临时表的存储引擎，可以通过设置 'default_tmp_storage_engine'（在启动时或运行时）单独设置。

示例

在本示例中，我们使用以下 SET 语句将默认存储引擎更改为 MyISAM −

SET default_storage_engine = MyISAM;

得到的结果如下 −

Query OK, 0 rows affected (0.00 sec)

现在，让我们使用以下 SHOW ENGINES 语句列出存储引擎。MyISAM 存储引擎的 support 列已更改为 default −

SHOW ENGINES\G

输出

以下是产生的结果集。请注意，我们没有显示整个结果集，仅显示 MyISAM 行以便于理解。实际结果集共有 11 行 −

*************************** 6. row ************************
      Engine: MyISAM
     Support: DEFAULT
     Comment: MyISAM storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
11 rows in set (0.00 sec)

更改存储引擎

您还可以使用 MySQL 中的 ALTER TABLE 命令将现有表的存储引擎更改为另一个存储引擎。但是，存储引擎必须更改为 MySQL 支持的引擎。

语法

以下是将现有存储引擎更改为另一个的基本语法 −

ALTER TABLE table_name ENGINE = engine_name;

示例

考虑之前在 MyISAM 数据库引擎上创建的表 TEST。在本示例中，使用以下 ALTER TABLE 命令，我们将其更改为 InnoDB 引擎。

ALTER TABLE TEST ENGINE = InnoDB;

输出

执行上述查询后，我们得到以下输出 −

Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

验证

要验证存储引擎是否已更改，请使用以下查询 −

SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = 'testDB';

产生的结果表如下所示 −

TABLE_NAME ENGINE
test InnoDB

使用客户端程序的存储引擎

我们也可以使用客户端程序来操作存储引擎。

语法

PHP NodeJS Java Python

通过 PHP 程序显示存储引擎，我们需要使用 mysqli 函数 query() 执行 "SHOW ENGINES" 语句，如下所示 −

$sql = "SHOW ENGINES";
$mysqli->query($sql);

通过 JavaScript 程序显示存储引擎，我们需要使用 mysql2 库的 query() 函数执行 "SHOW ENGINES" 语句，如下所示 −

sql = "SHOW ENGINES";
con.query(sql);

通过 Java 程序显示存储引擎，我们需要使用 JDBC 函数 executeQuery() 执行 "SHOW ENGINES" 语句，如下所示 −

String sql = "SHOW ENGINES";
statement.executeQuery(sql);

通过 Python 程序显示存储引擎，我们需要使用 MySQL Connector/Python 的 execute() 函数执行 "SHOW ENGINES" 语句，如下所示 −

sql = "SHOW ENGINES"
cursorObj.execute(sql)

示例

以下是相应的程序 −

PHP NodeJS Java Python 
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$db = 'TUTORIALS';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $db);
if ($mysqli->connect_errno) {
    printf("Connect failed: %s
", $mysqli->connect_error);
    exit();
}
//printf('Connected successfully.
');
$sql = "SHOW ENGINES";
if($mysqli->query($sql)){
    printf("Show query executed successfully....!\n");
}
printf("Storage engines: \n");
if($result = $mysqli->query($sql)){
   print_r($result);
}
if($mysqli->error){
    printf("Error message: ", $mysqli->error);
}
$mysqli->close();

输出

得到的输出如下所示 −

Show query executed successfully....!
Storage engines:
mysqli_result Object
(
    [current_field] => 0
    [field_count] => 6
    [lengths] =>
    [num_rows] => 11
    [type] => 0
)     

var mysql = require('mysql2');
var con = mysql.createConnection({
host:"localhost",
user:"root",
password:"password"
});

 //Connecting to MySQL
 con.connect(function(err) {
 if (err) throw err;
//   console.log("Connected successfully...!");
//   console.log("--------------------------");
 sql = "USE TUTORIALS";
 con.query(sql);
 //create table
 sql = "SHOW ENGINES";
 con.query(sql, function(err, result){
    console.log("Show query executed successfully....!");
    console.log("Storage engines: ")
    if (err) throw err;
    console.log(result);
    });
});

输出

得到的输出如下所示 −

Show query executed successfully....!
Storage engines: 
[
  {
    Engine: 'MEMORY',
    Support: 'YES',
    Comment: 'Hash based, stored in memory, useful for temporary tables',
    Transactions: 'NO',
    XA: 'NO',
    Savepoints: 'NO'
  },
  {
    Engine: 'MRG_MYISAM',
    Support: 'YES',
    Comment: 'Collection of identical MyISAM tables',
    Transactions: 'NO',
    XA: 'NO',
    Savepoints: 'NO'
  },
  {
    Engine: 'CSV',
    Support: 'YES',
    Comment: 'CSV storage engine',
    Transactions: 'NO',
    XA: 'NO',
    Savepoints: 'NO'
  },
  {
    Engine: 'FEDERATED',
    Support: 'NO',
    Comment: 'Federated MySQL storage engine',
    Transactions: null,
    XA: null,
    Savepoints: null
  }
]  

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class StorageEngine {
  public static void main(String[] args) {
    String url = "jdbc:mysql://localhost:3306/TUTORIALS";
    String user = "root";
    String password = "password";
    ResultSet rs;
    try {
      Class.forName("com.mysql.cj.jdbc.Driver");
            Connection con = DriverManager.getConnection(url, user, password);
            Statement st = con.createStatement();
            //System.out.println("Database connected successfully...!");
            //create table
            String sql = "SHOW ENGINES";
            rs = st.executeQuery(sql);
            System.out.println("Storage engines: ");
            while(rs.next()) {
              String engines = rs.getNString(1);
              System.out.println(engines);
            }
    }catch(Exception e) {
      e.printStackTrace();
    }
  }
}

输出

得到的输出如下所示 −

Storage engines: 
MEMORY
MRG_MYISAM
CSV
FEDERATED
PERFORMANCE_SCHEMA
MyISAM
InnoDB
ndbinfo
BLACKHOLE
ARCHIVE
ndbcluster

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password',
    database='tut'
)
cursorObj = connection.cursor()
# Query to get information about storage engines
storage_engines_query = "SHOW ENGINES"
cursorObj.execute(storage_engines_query)
# Fetching all records about storage engines
all_storage_engines = cursorObj.fetchall()
for row in all_storage_engines:
    print(row)
# Closing the cursor and connection
cursorObj.close()
connection.close()

输出

得到的输出如下所示 −

('MEMORY', 'YES', 'Hash based, stored in memory, useful for temporary tables', 'NO', 'NO', 'NO')
('MRG_MYISAM', 'YES', 'Collection of identical MyISAM tables', 'NO', 'NO', 'NO')
('CSV', 'YES', 'CSV storage engine', 'NO', 'NO', 'NO')
('FEDERATED', 'NO', 'Federated MySQL storage engine', None, None, None)
('PERFORMANCE_SCHEMA', 'YES', 'Performance Schema', 'NO', 'NO', 'NO')
('MyISAM', 'YES', 'MyISAM storage engine', 'NO', 'NO', 'NO')
('InnoDB', 'DEFAULT', 'Supports transactions, row-level locking, and foreign keys', 'YES', 'YES', 'YES')
('BLACKHOLE', 'YES', '/dev/null storage engine (anything you write to it disappears)', 'NO', 'NO', 'NO')
('ARCHIVE', 'YES', 'Archive storage engine', 'NO', 'NO', 'NO')