【MySQL 系列】MySQL 引擎篇

存储引擎是 MySQL 的组件，用于处理不同表类型的 SQL 操作。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎，还可以获得特定的功能。

1、MySQL 引擎介绍

1.1、MySQL 引擎介绍

存储引擎是 MySQL 的组件，用于处理不同表类型的 SQL 操作。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎，还可以获得特定的功能。

MySQL引擎是数据库的核心组件之一，决定了数据库的性能、可靠性和功能特性。

MySQL引擎的作用包括但不限于以下几个方面：

存储数据：MySQL 引擎负责将数据存储在物理存储介质上，包括硬盘或固态硬盘等。它将数据组织成表、索引等结构，并提供对这些数据的高效访问方式。
管理数据：MySQL 引擎管理数据的增删改查操作，包括插入新数据、更新已有数据、删除数据以及查询数据等操作。它负责确保数据的完整性、一致性和持久性。
提供事务支持：MySQL 引擎支持事务，可以确保一组操作要么全部成功执行，要么全部失败回滚，保证数据的一致性和可靠性。
提供并发控制：MySQL 引擎提供并发控制机制，确保多个用户可以同时访问数据库而不会相互干扰，保证数据的正确性和可靠性。
优化查询性能：MySQL 引擎负责优化查询语句，提高查询性能，包括选择合适的索引、执行查询计划优化等。

1.2、常见引擎及其区别和特点

MySQL支持多种不同类型的存储引擎，每种引擎都有其自身的特点和适用场景。以下是 MySQL 支持的一些常见引擎及其区别和特点：

① InnoDB：

InnoDB 是 MySQL 默认的事务安全引擎，提供了对事务的支持，包括 ACID（原子性、一致性、隔离性、持久性）特性。
支持行级锁定和外键约束，适合于需要高并发和数据完整性的应用场景。
InnoDB 引擎使用聚簇索引，将数据和索引存储在同一个B树结构中，提高了查询性能。

② MyISAM：

MyISAM 是 MySQL 的另一种常见引擎，不支持事务和行级锁定。
MyISAM 引擎适合于读操作频繁、写操作较少的应用场景，如数据仓库、日志分析等。
支持全文索引和压缩表格，对于特定类型的查询有一定的优势。

③ MEMORY（也称为HEAP）：

MEMORY 引擎将表格存储在内存中，适合于临时表格或者数据存储量较小的场景。
内存引擎不支持持久化，数据库重启时数据会丢失，因此不适合用于持久性数据存储。
内存引擎的查询速度非常快，但受限于内存大小。

④ NDB Cluster：

NDB Cluster 引擎是 MySQL 的集群存储引擎，适用于分布式环境和高可用性需求。
NDB Cluster 提供了分布式存储和事务支持，支持自动分区和数据复制，保证了数据库的可扩展性和可靠性。

⑤ 其他引擎：

还有其他一些不太常见的 MySQL 引擎，如 ARCHIVE、CSV、BLACKHOLE 等，它们各自有特定的用途和特点。

这些引擎之间的主要区别和特点包括：

事务支持：InnoDB 和 NDB Cluster 支持事务，而 MyISAM 和 MEMORY等引擎不支持。
锁定机制：InnoDB 支持行级锁定，而 MyISAM 通常使用表级锁定。
存储引擎特性：不同引擎支持的特性不同，如全文索引、压缩表格、分布式存储等。
性能和可靠性：不同引擎在性能、可靠性和适用场景上有所差异，根据具体需求选择合适的引擎。

在选择 MySQL 引擎时，需要根据应用场景和需求综合考虑各种因素，包括事务需求、性能要求、可靠性需求等，以选择最适合的引擎。

1.3、常用引擎相关命令

1.3.1、查询存储引擎

-- 查看支持的存储引擎
SHOW ENGINES
-- 查看默认存储引擎
SHOW VARIABLES LIKE '%storage_engine%'
--查看具体某一个表所使用的存储引擎，这个默认存储引擎被修改了！
show create table tablename
--准确查看某个数据库中的某一表所使用的存储引擎
show table status like 'tablename'
show table status from database where name="tablename"

1.3.2、设置存储引擎

-- 建表时指定存储引擎。默认的就是INNODB，不需要设置
CREATE TABLE t1 (i INT) ENGINE = INNODB;
CREATE TABLE t2 (i INT) ENGINE = CSV;
CREATE TABLE t3 (i INT) ENGINE = MEMORY;
-- 修改存储引擎
ALTER TABLE t ENGINE = InnoDB;
-- 修改默认存储引擎，也可以在配置文件my.cnf中修改默认引擎
SET default_storage_engine=NDBCLUSTER;

2、InnoDB 与 MyISAM 存储引擎之间的比较

2.1、事务的支持

InnoDB 支持 ACID 的事务处理，MyISAM 并不支持事务，这里需要注意，如果我们在程序需要进行事务处理，所有的数据都要使用 Innodb 存储引擎，如果中间有 MyISAM 存储引擎的数据表，如果进行事务回滚，MyISAM 并不会回滚到之前的状态，因此 MyISAM 不支持事务。

2.2、索引与主键处理

InnoDB 存储引擎使用的是聚集索引，InnoDB 主键的叶子节点是该行的数据，而其他索引则指向主键，而 MyISAM 存储引擎使用的是非聚集索引，主键与其他索引的叶子节点都存储了指向数据的指针。

另外一个是 MyISAM 数据表允许没有主键和其他索引，而 InnoDB 数据表如果没有主键的话，而会生成一个用户不可见6字节的主键。

2.3、外键

MyISAM 不支持外键，而 Innodb 则支持建立数据表之间的外键关联。

2.4、存储文件的不同

Innodb 存储文件有 frm、ibd，而 MyISAM 是 frm、MYD、MYI，Innodb 存储文件中 frm 是数据表结构定义文件，ibd 是数据文件，MyISAM 中 frm 是数据表结构定义文件，MYD 是数据的文件，MYI 则是存储索引的文件。

2.5、select count(*)

使用 MyISAM 存储引擎的数据表会记录一个数据表的总行数，所以对使用 MyISAM 存储引擎的数据表进行 select count(*)，可以很快得到一个数据表的总行数，而对于 InnoDB 存储引擎的数据表，想要查询总行数需要进行全表扫描才能得到。

2.6、锁的级别

InnoDB 支持行级锁，而 MyISAM 只支持表级锁，因此 InnoDB 更能支持高并发。