MySQL存儲引擎簡介

目錄

1 存儲引擎的概念

2 MySQL存儲引擎分類及適用場景

4 怎樣查看和設(shè)置存儲引擎

4.1查看MySQL支持的存儲引擎

4.2 查看表的存儲引擎

4.3 設(shè)置存儲引擎

4 存儲引擎的比較

4.1 InnoDB vs MyISAM

4.2 InnoDB vs Memory

1 存儲引擎的概念

• 存儲引擎是數(shù)據(jù)庫管理系統(tǒng)用來存儲、處理和保護(hù)數(shù)據(jù)的核心組件。
• MySQL的靈活性之一在于，它支持多種存儲引擎，每種存儲引擎都有其獨特的特點。

2 MySQL存儲引擎分類及適用場景

• InnoDB: The default storage engine in MySQL 5.7. InnoDB is a transaction-safe (ACID compliant) storage engine for MySQL that has commit, rollback, and crash-recovery capabilities to protect user data. InnoDB row-level locking (without escalation to coarser granularity locks) and Oracle-style consistent nonlocking reads increase multi-user concurrency and performance. InnoDB stores user data in clustered indexes to reduce I/O for common queries based on primary keys. To maintain data integrity, InnoDB also supports FOREIGN KEY referential-integrity constraints. For more information about InnoDB, see Chapter 14, The InnoDB Storage Engine.
• MyISAM: These tables have a small footprint. Table-level locking limits the performance in read/write workloads, so it is often used in read-only or read-mostly workloads in Web and data warehousing configurations.
• Memory: Stores all data in RAM, for fast access in environments that require quick lookups of non-critical data. This engine was formerly known as the HEAP engine. Its use cases are decreasing; InnoDB with its buffer pool memory area provides a general-purpose and durable way to keep most or all data in memory, and NDBCLUSTER provides fast key-value lookups for huge distributed data sets.
• CSV: Its tables are really text files with comma-separated values. CSV tables let you import or dump data in CSV format, to exchange data with scripts and applications that read and write that same format. Because CSV tables are not indexed, you typically keep the data in InnoDB tables during normal operation, and only use CSV tables during the import or export stage.
• Archive: These compact, unindexed tables are intended for storing and retrieving large amounts of seldom-referenced historical, archived, or security audit information.
• Blackhole: The Blackhole storage engine accepts but does not store data, similar to the Unix /dev/null device. Queries always return an empty set. These tables can be used in replication configurations where DML statements are sent to replica servers, but the source server does not keep its own copy of the data.
• NDB (also known as NDBCLUSTER): This clustered database engine is particularly suited for applications that require the highest possible degree of uptime and availability.
• Merge(also known as the MRG_MyISAM ): Enables a MySQL DBA or developer to logically group a series of identical MyISAM tables and reference them as one object. Good for VLDB environments such as data warehousing.
• Federated: Offers the ability to link separate MySQL servers to create one logical database from many physical servers. Very good for distributed or data mart environments.
• Example: This engine serves as an example in the MySQL source code that illustrates how to begin writing new storage engines. It is primarily of interest to developers. The storage engine is a “stub” that does nothing. You can create tables with this engine, but no data can be stored in them or retrieved from them.

You are not restricted to using the same storage engine for an entire server or schema. You can specify the storage engine for any table. For example, an application might use mostly InnoDB tables, with one CSV table for exporting data to a spreadsheet and a few MEMORY tables for temporary workspaces.

?

4 怎樣查看和設(shè)置存儲引擎

4.1查看MySQL支持的存儲引擎

查看自己的MySQL版本支持哪些引擎：

mysql> show engines;

4.2 查看表的存儲引擎

show create table 表名;

結(jié)果：

CREATE TABLE `employees` (
 ?`id` int(11) NOT NULL,
 ?`name` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL,
 ?`department` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL,
 ?`salary` decimal(10,2) DEFAULT NULL,
 ?`hire_date` date DEFAULT NULL,
 ?PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

4.3 設(shè)置存儲引擎

在創(chuàng)建表的時候在create table 時指定 ENGINE=存儲引擎名字;

create table test_engine (a int) ENGINE = MyISAM;

show create table test_engine;

4 存儲引擎的比較

4.1 InnoDB vs MyISAM

• 事務(wù)支持： InnoDB提供事務(wù)支持，而MyISAM則不支持。如果你的系統(tǒng)需要執(zhí)行復(fù)雜的事務(wù)操作，那么你應(yīng)該選擇InnoDB。
• 鎖定級別： MyISAM只支持表級鎖，而InnoDB則支持行級鎖和表級鎖。這意味著在并發(fā)環(huán)境下，InnoDB通常能提供更高的吞吐量。
• 全文搜索： MyISAM支持全文搜索，而InnoDB在MySQL 5.6之前的版本中不支持。但是從MySQL 5.6開始，InnoDB也開始支持全文搜索。
• 數(shù)據(jù)完整性： InnoDB支持外鍵和完整性約束，這有助于確保數(shù)據(jù)的完整性和準(zhǔn)確性。而MyISAM則不支持。
• 崩潰恢復(fù)： InnoDB有更好的崩潰恢復(fù)能力。如果MySQL服務(wù)器意外停止，InnoDB存儲引擎可以利用日志文件自動恢復(fù)到一致的狀態(tài)，而MyISAM可能需要手動修復(fù)。

4.2 InnoDB vs Memory

• 數(shù)據(jù)持久性： InnoDB存儲的數(shù)據(jù)是持久化的，服務(wù)器重啟后數(shù)據(jù)不會丟失。而Memory存儲引擎的數(shù)據(jù)存儲在內(nèi)存中，服務(wù)器重啟后數(shù)據(jù)會丟失。
• 數(shù)據(jù)容量： 由于Memory存儲引擎的數(shù)據(jù)存儲在內(nèi)存中，因此其數(shù)據(jù)容量受到內(nèi)存大小的限制，通常不能存儲大量數(shù)據(jù)。而InnoDB存儲的數(shù)據(jù)存儲在磁盤中，可以存儲更多的數(shù)據(jù)。
• 查詢速度： Memory存儲引擎的數(shù)據(jù)存儲在內(nèi)存中，查詢速度通常比InnoDB快。但是如果數(shù)據(jù)量過大，超過了內(nèi)存容量，Memory的性能會大大下降。