MySQL进阶-存储引擎

1.MySQL体系结构

体系结构图

各层的作用

2.存储引擎简介

2.1查看当前表的存储引擎

2.2 查询mysql支持的存储引擎

2.3 InnoDB简介

2.4 MyISAM简介

2.5 Memory简介

3.存储引擎的选择

1.MySQL体系结构

mysql体系结构主要有四层结构，从上到下依次是：连接层-> 服务层-> 引擎层-> 存储层

体系结构图

各层的作用

1、连接层：主要完成一些类似于连接处理、授权认证、及相关的安全方案。服务器也会为安全接入的每个客户端验证它所具有的操作权限。

2、服务层：第二层架构主要完成大多数的核心服务功能，如SQL接口，并完成缓存的查询，SQL的分析和优化，部分内置函数的执行。所有跨存储引擎的功能也在这一层实现，如过程、函数等。

3、引擎层：存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API和存储引擎进行通信。不同的存储引擎具有不同的功能，这样我们可以根据自己的需要，来选取合适的存储引擎。

4、存储层：主要是将数据存储在文件系统之上，并完成与存储引擎的交互。

2.存储引擎简介

存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式。存储引擎是基于表的，而不是基于库的，所以存储引擎也可被称为表结构。

在之前我们建表时都没有指定存储引擎，但是在mysql中的默认存储引擎就是 Innodb。

2.1查看当前表的存储引擎

可以查看某张表的建表语句

show create table account;

ENGINE字段就是指定的存储引擎，所以在建表中我们就可以在这个字段指定对应的存储引擎。

例如：

create table sm(id int auto_increment primary key,name varchar(10)
)engine = 存储引擎名称;

2.2 查询mysql支持的存储引擎

查询mysql所支持的存储引擎可以用这个语句

show engines;

Engine:存储引擎名称

Support：当前数据库是否支持

Comment：注释说明

Transactions：是否支持事务

2.3 InnoDB简介

InnoDB是MySQL数据库管理系统中最常用的存储引擎之一，它提供了许多高级功能和性能优化，特别适合于处理事务处理和高并发负载的应用程序。

特点：

事务支持：InnoDB是一个支持ACID（原子性、一致性、隔离性、持久性）事务的存储引擎。这意味着它可以确保数据的完整性和一致性，特别适用于需要处理金融交易或其他重要数据的应用程序。
行级锁定：InnoDB使用行级锁定（Row-Level Locking）来实现并发控制，允许多个事务同时访问同一张表的不同行，而不会导致锁定整个表。这提高了并发性能，减少了锁定冲突。
外键约束：InnoDB支持外键约束，可以在表之间建立引用关系，以维护数据的完整性。这允许数据库执行引用完整性检查，并确保父表和子表之间的关系是一致的。
数据缓存和索引缓存：InnoDB使用数据缓存（Data Buffer Cache）来存储表中的数据和索引缓存（Index Buffer Cache）来存储索引数据。这些缓存提高了查询性能，减少了对磁盘的访问。
自动崩溃恢复：InnoDB支持自动崩溃恢复功能，可以在数据库异常关闭后自动将数据库恢复到一致的状态，而无需手动干预。
支持热备份：InnoDB支持在线备份，允许在数据库运行时备份数据而不需要停机。这有助于提高系统的可用性。
多版本并发控制（MVCC）：InnoDB使用MVCC机制，允许事务并发运行而不会阻塞彼此。每个事务可以看到数据库的一致性快照，这有助于避免脏读和不可重复读问题。
自适应哈希索引：InnoDB可以自动创建和管理哈希索引，以加速特定类型的查询操作，例如等值查找。
支持大容量数据：InnoDB支持大容量的数据存储，适用于需要存储大量数据的应用程序。

涉及的文件结构：

InnoDB引擎的每张表都会对应一个表空间文件，类似于：xxx.ibd，存储表的结构（frm,sdi）、数据和索引。

2.4 MyISAM简介

MyISAM引擎是MySQL早期的默认存储引擎

特点：

不支持事务：MyISAM不支持事务，因此不适用于需要严格的ACID（原子性、一致性、隔离性、持久性）事务支持的应用程序。这意味着如果在MyISAM表上执行INSERT、UPDATE或DELETE操作时发生错误，无法回滚到之前的状态。
表级锁定：MyISAM使用表级锁定（Table-Level Locking）来控制并发访问。这意味着当一个事务修改表中的某一行时，其他事务无法同时修改表的其他行，可能会导致锁定冲突和性能问题。
支持全文搜索：MyISAM存储引擎支持全文搜索功能，使其在需要进行文本搜索的应用中表现出色。MySQL的全文索引可以加速文本搜索操作。
不支持外键：MyISAM不支持外键约束，因此无法维护表之间的引用关系。这使得数据的完整性在一定程度上受到限制。
支持压缩表：MyISAM支持表级别的数据压缩，可以减小数据文件的尺寸，从而节省磁盘空间。这对于存储大量静态数据的表格非常有用。
支持表级别的锁定：MyISAM允许表级别的读锁定和写锁定，这使得在一些特殊情况下，如备份和优化表时，可以更容易地进行锁定和解锁。
不支持崩溃恢复：MyISAM不具备自动崩溃恢复机制，如果数据库崩溃或发生硬件故障，可能需要手动修复表格。
适用于只读或很少修改的数据：由于不支持事务和表级锁定，MyISAM通常适用于只读或很少修改的数据，例如存档数据、日志数据等。

涉及的文件结构：

xxx.sdi：存储表结构信息

xxx.MYD：存储数据

xxx.MYI：存储索引

2.5 Memory简介

Memory存储引擎是MySQL数据库管理系统中的一种特殊存储引擎，也被称为 "HEAP" 存储引擎。与其他常见的存储引擎（如InnoDB、MyISAM等）不同，Memory 存储引擎将数据完全存储在内存中，而不是存储在磁盘上,这使得 Memory 存储引擎非常快速。

Memory引擎的表数据时存储在内存中的，由于受到硬件问题、或断电问题的影响，只能将这些表作为临时表或缓存使用。

特点：

数据存储在内存中：Memory 存储引擎将表中的数据存储在内存中，这意味着对表的读取和写入操作非常快速。它特别适用于需要快速访问临时数据的情况，例如会话缓存或临时表。
不支持事务：Memory 存储引擎不支持事务，因此不适合需要 ACID 特性的应用程序。如果数据库崩溃或发生错误，数据可能会丢失。
表级锁定：Memory 存储引擎使用表级锁定，这意味着当一个连接修改表的一部分时，其他连接无法同时修改同一表的其他部分。这可能导致锁定冲突。
不支持索引类型：Memory 存储引擎支持基本索引，但不支持某些高级索引类型，如全文索引。这会限制某些查询的效率。
不支持外键约束：Memory 存储引擎不支持外键约束，因此无法维护表之间的引用关系。
数据不持久化：由于数据存储在内存中，如果数据库服务器关闭或崩溃，数据将丢失。因此，Memory 存储引擎通常用于存储临时数据或缓存，而不是持久存储。
适用于临时数据和高速缓存：Memory 存储引擎适合用于需要快速读取和写入数据的应用程序，例如缓存存储或需要频繁重建的临时数据表。

涉及的文件结构：

xxx.sdi：存储表结构信息

3.存储引擎的选择

存储引擎之前的特点都是各不相同的，只需要在实际情况选择最合适的存储引擎即可。

InnoDB存储引擎：
- 适用场景：适用于大多数应用程序，特别是那些需要事务支持和数据完整性的应用。
- 优点：支持ACID事务、行级锁定、外键约束，适用于高并发、高事务处理的应用。
- 注意事项：相对于其他存储引擎，InnoDB通常需要更多的内存，因此需要配置适当的系统资源。
MyISAM存储引擎：
- 适用场景：适用于只读或很少修改的数据，例如静态数据或日志数据。
- 优点：读取性能较高，不支持事务的应用可以受益于它的速度。
- 注意事项：不支持事务、表级锁定，不适用于高并发写入和需要数据完整性的应用。
Memory存储引擎：
- 适用场景：适用于需要快速读取和写入数据的缓存或临时数据存储。
- 优点：数据存储在内存中，读取和写入非常快速，适用于缓存和临时数据表。
- 注意事项：不支持事务、表级锁定，数据不持久化，不适用于需要长期存储数据的应用。