MySQL - 儲存引擎

MySQL 儲存引擎

如我們所知，MySQL 資料庫用於以行和列的形式儲存資料。MySQL 儲存引擎是一個用於處理執行以管理此資料的 SQL 操作的元件。它們處理建立表、重命名錶、更新或刪除表等簡單任務；這對於提高資料庫效能是必要的。

使用的儲存引擎分為兩類：事務引擎和非事務引擎。許多常見的儲存引擎都屬於這兩種型別。但是，在 MySQL 中，預設儲存引擎是 InnoDB。

常用儲存引擎

用於與 MySQL 協作的各種常用儲存引擎如下所示：

InnoDB 儲存引擎

• ACID 相容 - InnoDB 是 MySQL 5.5 及更高版本中的預設儲存引擎。它是一個事務資料庫引擎，確保 ACID 相容性，這意味著它支援提交和回滾等操作。
• 崩潰恢復 - InnoDB 提供崩潰恢復功能來保護使用者資料。
• 行級鎖定 - 它支援行級鎖定，從而增強多使用者併發性和效能。
• 引用完整性 - 它還強制執行 FOREIGN KEY 引用完整性約束。

ISAM 儲存引擎

• 已棄用 - ISAM（Indexed Sequential Access Method 的縮寫）在早期 MySQL 版本中受支援，但已棄用並從最新版本中刪除。
• 大小受限 - ISAM 表的大小限制為 4GB。

MyISAM 儲存引擎

• 可移植性 - MyISAM 旨在實現可移植性，解決 ISAM 的不可移植性問題。
• 效能 - 與 ISAM 相比，它提供了更快的效能，並且是 MySQL 5.x 之前的預設儲存引擎。
• 記憶體效率 - MyISAM 表的記憶體佔用空間小，使其適用於只讀或主要讀取的工作負載。

MERGE 儲存引擎

• 邏輯組合 - MERGE 表使 MySQL 開發人員能夠邏輯地組合多個相同的 MyISAM 表並將它們作為單個物件進行引用。
• 操作受限 - 僅允許在 MERGE 表上執行 INSERT、SELECT、DELETE 和 UPDATE 操作。如果使用 DROP 查詢，則只有儲存引擎規範會被重置，而表保持不變。

MEMORY 儲存引擎

• 記憶體儲存 - MEMORY 表將資料完全儲存在 RAM 中，從而最佳化訪問速度以進行快速查詢。
• 雜湊索引 - 它使用雜湊索引來更快地檢索資料。
• 使用減少 - 其用例正在減少；其他引擎（如 InnoDB 的緩衝池記憶體區域）提供了更好的記憶體管理。

CSV 儲存引擎

• 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';

生成的表如下所示 -

使用客戶端程式的儲存引擎

我們也可以使用客戶端程式執行儲存引擎操作。

語法

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

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

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

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....!\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://: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')