【MySQL 系列】MySQL 引擎篇

谁践踏了优雅 2024-04-06 15:43 77阅读 0赞

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

--------------------

#### 文章目录 ####

*   *   *   *  1、MySQL 引擎介绍
             *   *  1.1、MySQL 引擎介绍
                 *  1.2、常见引擎及其区别和特点
                 *  1.3、常用引擎相关命令
                 *   *  1.3.1、查询存储引擎
                     *  1.3.2、设置存储引擎
             *  2、InnoDB 与 MyISAM 存储引擎之间的比较
             *   *  2.1、事务的支持
                 *  2.2、索引与主键处理
                 *  2.3、外键
                 *  2.4、存储文件的不同
                 *  2.5、select count(\\\*)
                 *  2.6、锁的级别

--------------------

##### 1、MySQL 引擎介绍 #####

###### 1.1、MySQL 引擎介绍 ######

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

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

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

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