MySQL - 存储引擎
MySQL 存储引擎引擎
众所周知，MySQL 数据库用于以行和列的形式存储数据。MySQL 存储引擎是一个组件，用于处理管理这些数据所需的 SQL 操作。它们可以执行诸如创建表、重命名表、更新表或删除表之类的简单任务;这对于提高数据库性能至关重要。
存储引擎分为两类:事务性引擎和非事务性引擎。许多常见的存储引擎都属于这两类。然而，MySQL 的默认存储引擎是 InnoDB。
常用存储引擎
MySQL 常用的存储引擎如下:-
InnoDB 存储引擎
- 符合 ACID 标准 - InnoDB 是 MySQL 5.5 及更高版本中的默认存储引擎。它是一个事务型数据库引擎，确保符合 ACID 规范，这意味着它支持提交和回滚等操作。
- 崩溃恢复 − InnoDB 提供崩溃恢复功能来保护用户数据。
- 行级锁定 − 它支持行级锁定，从而增强了多用户并发性和性能。
- 参照完整性 − 它还强制执行外键参照完整性约束。
ISAM 存储引擎
- 已弃用 − ISAM(索引顺序访问方法)在早期 MySQL 版本中受支持，但在最新版本中已被弃用和删除。
- 大小受限 − ISAM 表的大小限制为4GB。
MyISAM 存储引擎
- 可移植性 − MyISAM 专为可移植性而设计，解决了 ISAM 的不可移植性。
- 性能 − 与 ISAM 相比，它提供了更快的性能，并且是 MySQL 5.x 之前的默认存储引擎。
- 内存效率 − MyISAM 表占用内存较少，非常适合只读或以读为主的工作负载。
MERGE 存储引擎
- 逻辑组合 − MERGE 表使 MySQL 开发人员能够逻辑地组合多个相同的 MyISAM 表并将它们作为一个对象引用。
- 受限操作 − 仅允许在合并表。如果使用 DROP 查询，则仅重置存储引擎规范，而表保持不变。
MEMORY 存储引擎
- 内存存储 − MEMORY 表将数据完全存储在 RAM 中，从而优化了访问速度，以实现快速查找。
- 哈希索引 − 它使用哈希索引来加快数据检索速度。
- 使用减少 − 其用例正在减少;其他引擎，例如 InnoDB 的缓冲池内存区域，提供了更好的内存管理。
CSV 存储引擎
- CSV 格式 − CSV 表是使用逗号分隔值的文本文件，可用于与脚本和应用程序进行数据交换。
- 无索引 − 它们没有索引，通常在数据导入或导出过程中与 InnoDB 表一起使用。
NDBCLUSTER 存储引擎
- 集群 − NDBCLUSTER，也称为 NDB，是一种集群数据库引擎，适用于需要最高正常运行时间和可用性的应用程序。
ARCHIVE 存储引擎
- 历史数据 - ARCHIVE 表非常适合存储和检索大量历史数据、存档数据或安全数据。 ARCHIVE 存储引擎支持非索引表
BLACKHOLE 存储引擎
- 数据丢弃 - BLACKHOLE 表接受数据但不存储，始终返回空集。
- 用途 - 用于复制配置，其中 DML 语句发送到副本服务器，但主服务器不保留其自己的数据副本。
FEDERATED 存储引擎
- 分布式数据库 - FEDERATED 允许链接单独的 MySQL 服务器，以从多个物理服务器创建逻辑数据库，这在分布式环境中非常有用。
EXAMPLE 存储引擎
- 开发工具 - EXAMPLE 是 MySQL 源代码中的一个工具，可作为开发人员开始编写新存储引擎。您可以使用此引擎创建表，但它不存储或检索数据。
尽管有很多存储引擎可用于数据库，但不存在所谓的完美存储引擎。在某些情况下，某种存储引擎可能更适合使用，而在其他情况下，其他引擎的性能会更好。因此，在特定环境下工作时，必须谨慎选择要使用的存储引擎。
要选择引擎，您可以使用 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: Hash based, stored in memory, useful for temporary tables
Transactions: NO
XA: NO
Savepoints: NO
*************************** 2. row ************************
Engine: MRG_MYISAM
Support: YES
Comment: Collection of identical MyISAM tables
Transactions: NO
XA: NO
Savepoints: NO
*************************** 3. row ************************
Engine: CSV
Support: YES
Comment: CSV storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 4. row ************************
Engine: FEDERATED
Support: NO
Comment: Federated MySQL storage engine
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 storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 7. row ************************
Engine: InnoDB
Support: DEFAULT
Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
XA: YES
Savepoints: YES
*************************** 8. row ************************
Engine: ndbinfo
Support: NO
Comment: MySQL Cluster system information storage engine
Transactions: NULL
XA: NULL
Savepoints: NULL
*************************** 9. row ************************
Engine: BLACKHOLE
Support: YES
Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
XA: NO
Savepoints: NO
*************************** 10. row ************************
Engine: ARCHIVE
Support: YES
Comment: Archive storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 11. row ************************
Engine: ndbcluster
Support: NO
Comment: Clustered, fault-tolerant tables
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 存储引擎的支持列已更改为默认列 -
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 程序显示存储引擎，我们需要使用 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)
示例
以下是程序 -
$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....!
");
}
printf("Storage engines:
");
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"
});
//连接到 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
#建立连接
connection = mysql.connector.connect(
host='localhost',
user='root',
password='password',
database='tut'
)
cursorObj = connection.cursor()
# 查询存储引擎信息
storage_engines_query = "SHOW ENGINES"
cursorObj.execute(storage_engines_query)
# 获取所有存储引擎记录
all_storage_engines = cursorObj.fetchall()
for row in all_storage_engines:
print(row)
# 关闭游标和连接
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')
