MySQL逻辑架构

结构作用
客户端连接器提供与MySQL服务器建立的支持。
连接池负责存储和管理客户端与数据库的连接，一个线程负责管理一个连接
系统管理和控制工具例如备份恢复、安全管理、集群管理等
SQL接口用于接受客户端发送的各种SQL命令，并且返回用户需要查询的结果。比如DML、DDL、存储过程、视图、触发器等。
连接器处理连接、认证授权、安全管理
查询缓存缓存查询结果，MySQL8.0 已废弃
解析器词法分析、语法分析
优化器优化SQL语句、规定执行流程
执行器执行查询语句，获取数据
存储引擎负责数据的存储和检索，与底层系统文件进行交互
系统文件层配置文件、数据文件、日志文件等

MySQL运行机制

连接器→查询缓存→解析器→优化器→执行器→存储引擎

  • 连接器建立连接：当客户端请求连接时，连接器会进行身份验证，并建立与服务器的连接。
  • 查询缓存：如果查询被缓存，那么直接返回缓存结果集，避免了后面的解析、优化和执行步骤。
  • 解析器生成执行计划：如果查询没有被缓存或者缓存被清空，那么解析器会对查询进行语法和语义分析，确定查询要执行的操作和涉及的表和列，根据表的结构信息生成一个初始的执行计划，包含哪些表需要连接、如何连接、使用哪些索引等。
  • 优化器优化执行计划：执行计划是根据查询语句生成的，可能并不是最优的执行方式，优化器的目标是找到最优的执行计划，并生成一个优化后的执行计划。
  • 执行器执行执行计划：执行器根据优化器生成的执行计划，调用存储引擎对数据进行操作。
  • 存储引擎处理返回结果：存储引擎将结果返回给客户端

存储引擎

存储引擎简介

（1）存储引擎是数据库管理系统中负责存储数据、建立索引、更新和查询数据等技术的实现方式。不同的存储引擎采用不同的数据结构、算法和优化策略，以满足不同的应用需求。

（2）存储引擎是基于表的，而不是基于库的。每个表在MySQL中都可以使用不同的存储引擎。这意味着同一个数据库中的不同表可以使用不同的存储引擎，根据具体的需求来选择合适的存储引擎。

（3）MySQL默认存储引擎的变化

MySQL版本变化
MySQL 5.5 前默认存储引擎是 MyISAM，它不支持事务和外键等高级特性
MySQL 5.5 后默认存储引擎变为了 InnoDB，它支持事务、外键等高级特性，并且提供了更好的性能和可靠性

查看存储引擎

查看默认存储引擎

1
SHOW VARIABLES LIKE 'default_storage_engine';

查看正在使用的存储引擎

1
SELECT @@default_storage_engine;

查看可用的存储引擎

1
SHOW ENGINES;
引擎名称支持情况描述事务支持情况XA支持情况Savepoints支持情况
InnoDBDEFAULT支持事务、行级锁和外键YESYESYES
MyISAMYESMyISAM存储引擎NONONO
MemoryYES基于hash算法、存储在内存中，用于临时表NONONO
CSVYESCSV存储引擎NONONO
ARCHIVEYES归档存储引擎NONONO
BLACKHOLEYES/dev/null存储引擎（任何写入均消失）NONONO
FEDERATEDNO远程MySQL存储引擎NULLNULLNULL
PERFORMANCE_SCHEMAYES性能架构NONONO
MRG_MYISAMYES相同MyISAM表的集合NONONO

设置存储引擎

Windows操作系统

进入mysql安装目录找到my.ini，在[mysqld]后添加配置，修改后需要重新启动MySQL服务

1
2
3
[mysqld]
# 指定默认的存储引擎
default-storage-engine=存储引擎名称

Linux操作系统

使用vim编辑器打开/etc/mysql/my.cnf，在[mysqld]后添加配置，修改后需要重新启动MySQL服务

1
2
3
[mysqld]
# 指定默认的存储引擎
default-storage-engine=存储引擎名称

命令设置存储引擎

只会改变当前会话中的设置，不会修改 MySQL 的全局配置

1
set default_storage_engine=存储引擎名称;

修改存储引擎

创建表时修改存储引擎

1
create table [if not exists]表名(字段信息) ENGINE = 存储引擎名称;

已有表修改存储引擎

1
alter table 表名 ENGINE = 存储引擎名称

常见存储引擎

InnoDB存储引擎

特点简介
支持事务DML支持事务的 ACID 特征，并且可以对数据行进行细粒度的锁定，从而提高了并发性能。
支持外键InnoDB 存储引擎支持外键约束，可以保证数据的完整性和一致性。
支持表级锁InnoDB 存储引擎支持表级锁，在执行高并发的大量读操作时 表级锁可以防止写操作对读操作的干扰，提高系统的稳定性和性能。
支持行级锁InnoDB 存储引擎可以对数据行进行细粒度的锁定，避免了全表锁的情况，提高了并发性能。
磁盘文件InnoDB的磁盘文件包括*.frm和*.ibd两个文件，其中*.frm文件存放表的结构定义信息，*.ibd文件存放表中的数据和索引信息。

MyISAM存储引擎

特点简介
不支持事务MyISAM 存储引擎是 MySQL 中较早的一种存储引擎，最大的缺陷是不支持事务。 如果发生系统故障或其他错误，可能会导致数据丢失或损坏。
不支持外键在 MyISAM 中，虽然可以使用关键字来定义外键（FOREIGN KEY），但是 MySQL 并不会对其进行任何约束检查。 因此，MyISAM 存储引擎不支持外键。
支持表锁MyISAM 存储引擎采用表锁定方式来实现并发控制， 即在对 MyISAM 表进行操作时，锁定整个表， 从而避免多个用户同时对同一行记录进行更新操作，保证了数据的一致性与完整性。
不支持行锁MyISAM 存储引擎不支持行锁定，这意味着当多个用户同时对同一张表进行操作时， 可能会出现死锁等并发控制问题。
磁盘文件MyISAM的磁盘文件包括*.frm、*.MYD和*.MYI三个文件，其中*.frm文件存放表的结构定义信息，*.MYD文件存放表中的数据，*.MYI文件存放表的索引信息。

Memory存储引擎

Memory引擎的表数据时存储在内存中的，为了数据安全，只能将这些表作为临时表或缓存使用

特点简介
基于内存Memory 存储引擎是 MySQL 中一种基于内存的存储引擎，所有数据都存储在内存中，因此其读写速度非常快。但是，由于数据存储在内存中，当 MySQL Server 发生异常关闭或崩溃时，所有数据都会丢失。
不支持事务与 MyISAM 存储引擎相似，Memory 存储引擎也不支持事务，因此不能保证数据的一致性和完整性。
不支持外键Memory 存储引擎同样不支持外键约束，因此需要在应用层进行外键约束的处理。
支持锁定机制Memory 存储引擎通过锁定机制来实现并发控制，同时支持表锁定和行锁定两种锁定方式，使得多个用户可以同时对同一张表进行读取和写入操作。
表结构简单Memory 存储引擎的表结构比较简单，不支持 BLOB 和 TEXT 类型的列，而且对 CHAR 和 VARCHAR 类型的列进行存储时，只会存储实际值而不会存储列定义中的长度信息。

CSV存储引擎

特点简介
基于文本文件CSV（Comma Separated Values）存储引擎是 MySQL 中一种基于文本文件的存储引擎 使用逗号分隔不同的字段，每行记录表示一个数据记录。 由于其基于文本文件，因此可以使用文本编辑器或电子表格程序来进行数据的编辑和维护。
不支持索引与其他存储引擎不同，CSV 存储引擎不支持索引功能， 因此对于大量数据的查询操作较为缓慢，建议仅用于较小的数据集合或备份等非常规用途。
不支持事务与 MyISAM 和 Memory 存储引擎类似，CSV 存储引擎也不支持事务，因此不能保证数据的一致性和完整性。
不支持表级锁定CSV 存储引擎不支持表级锁定，而是采用操作系统级别的文件锁定机制来实现并发控制。 这也意味着在高并发环境下 CSV 存储引擎可能会遭受锁定冲突带来的性能损失。
适合临时存储由于其特点，CSV 存储引擎适合用于临时存储数据，如数据导入和导出、数据备份等操作。

InnoDB与MyISAM对比

特性InnoDBMyISAM
存储引擎类型事务型存储引擎非事务型存储引擎
锁定级别支持行级锁定、表级锁定支持表级锁定，不支持行级锁定
外键约束支持外键约束不支持外键约束
索引类型支持聚簇索引以及非聚簇索引只支持非聚簇索引
全文本索引支持全文本索引支持全文本索引
表空间占用占用空间大，需要更多的物理内存占用空间小，相比InnoDB更省空间
性能对比复杂查询性能较高，并发插入性能一般复杂查询性能较低，但并发插入性能较高
崩溃恢复支持崩溃恢复，可以自动恢复未提交的事务不支持崩溃恢复，一旦断电或者崩溃，则数据无法恢复
应用场景适用于高并发、高数据一致性要求的场景适用于读操作较多、对数据一致性要求不高的场景