一、MySQL 体系结构

MySQL 拥有分层的体系结构，各层次协同工作以满足不同的数据存储和处理需求。

  • 连接层：处于最上层，包含客户端和链接服务。主要负责连接处理、授权认证以及相关安全方案。服务器会为每个安全接入的客户端验证其操作权限。

  • 服务层：这一层主要实现大多数核心服务功能。包括 SQL 接口、缓存查询、SQL 分析和优化，以及部分内置函数的执行。所有跨存储引擎的功能也在此层实现，比如存储过程、函数等。

  • 引擎层：存储引擎负责 MySQL 中数据的存储和提取。服务器通过 API 与存储引擎进行通信。不同的存储引擎具有不同的功能，用户可根据实际需求选取合适的存储引擎。

  • 存储层：将数据存储在文件系统之上，并与存储引擎进行交互。

二、存储引擎简介

存储引擎是存储数据、建立索引、更新 / 查询数据等技术的实现方式，也被称为表类型，因为它是基于表而非库的。MySQL 支持多种存储引擎，以适应不同的应用场景和数据存储需求。

  • InnoDB：自 MySQL 5.5.5 版本后成为默认存储引擎。以强大的事务支持和高并发性能著称。在电商系统中，如订单处理、库存管理等涉及大量事务操作且需保证数据一致性的场景广泛应用。它通过行级锁和 MVCC（多版本并发控制）机制减少锁冲突，提高并发效率，能很好地处理多个用户同时对数据库进行读写操作。

  • MyISAM：在早期 MySQL 版本中应用广泛。结构相对简单，数据存储和索引构建方式较为直接。适用于一些以读为主的应用场景，如数据仓库中的报表查询、博客系统中的文章读取等。但不支持事务和外键约束，在数据更新频繁且需保证数据完整性的场景下不太适用。

  • Memory：数据存储在内存中，读写速度极快。适合用于缓存数据或者临时表等场景。例如在高流量网站中，可将热门商品信息存储在 Memory 引擎的表中，快速响应用户查询请求。但由于数据存储在内存，一旦服务器断电或 MySQL 服务停止，数据将会丢失，所以通常用于数据量较小且能容忍数据丢失风险的情况。

创建表时指定存储引擎的语法如下：

CREATE TABLE 表名(
    字段1 字段1类型 [COMMENT 字段1注释],
    ......
    字段n 字段n类型 [COMMENT 字段n注释]
) ENGINE = INNODB [COMMENT 表注释];

查看当前数据库支持的存储引擎可使用以下语句：

SHOW ENGINES;

三、存储引擎特点

  • InnoDB 特点：

    • 事务特性：严格遵循 ACID（原子性、一致性、隔离性、持久性）原则，确保事务的完整性。例如在银行转账业务中，从一个账户扣除金额和在另一个账户增加金额这两个操作必须作为一个原子事务执行，要么全部成功，要么全部失败，InnoDB 能够可靠地实现这一要求。

    • 行级锁与 MVCC：行级锁允许在并发环境下不同事务对不同行进行操作，减少锁竞争。MVCC 则进一步提升了并发读性能，不同事务可以读取同一数据行的不同历史版本，避免了读操作的阻塞等待。比如在社交网络系统中，多个用户同时查询和更新自己的个人信息，InnoDB 能够高效地处理这些并发操作。

    • 外键约束：能够建立表之间的外键关系，维护数据的参照完整性。在企业资源管理系统中，订单表与客户表、产品表之间通过外键关联，InnoDB 可以防止非法的关联数据插入或更新，保证数据的准确性和一致性。

  • MyISAM 特点：

    • 高效的读性能：简单的存储结构使得在进行简单查询时速度较快。对于一些内容管理系统中文章内容的读取，MyISAM 可以快速定位并返回数据。

    • 全文索引支持：擅长对文本数据进行全文搜索。在新闻网站或者文档管理系统中，如果需要根据关键词搜索文章或文档内容，MyISAM 的全文索引功能能够高效地完成任务。

    • 表级锁限制：由于采用表级锁，在并发写操作较多时性能会受到较大影响。例如在多用户同时更新商品库存的电商系统中，如果使用 MyISAM 存储商品表，可能会出现大量锁等待，降低系统的响应速度。

  • Memory 特点：

    • 内存存储优势：数据读写几乎没有磁盘 I/O 延迟，速度极快。在一些实时性要求极高的场景，如高频交易系统中的部分数据缓存，Memory 引擎能够迅速提供数据服务。

    • 哈希索引特性：默认的哈希索引对于等值查询非常高效。比如在根据用户 ID 快速查询用户信息的登录验证系统中，Memory 引擎可以快速定位到数据。

    • 数据易失性风险：数据完全存储在内存中，一旦系统故障或断电，数据将不复存在。因此在使用时需要考虑数据备份和持久化策略，或者与其他持久化存储引擎配合使用。

四、存储引擎选择

在选择存储引擎时，应根据应用系统的特点进行选择。对于复杂的应用系统，还可以根据实际情况选择多种存储引擎进行组合。

  • InnoDB：作为 MySQL 的默认存储引擎，支持事务、外键。如果应用对事务的完整性有较高要求，在并发条件下要求数据的一致性，且数据操作除了插入和查询之外，还包含很多更新、删除操作，那么 InnoDB 存储引擎是比较合适的选择。

  • MyISAM：如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎是非常合适的。

  • MEMORY：将所有数据保存在内存中，访问速度快，通常用于临时表及缓存。但它对表的大小有限制，且数据易失，无法保障数据的安全性。

多说一句： 在业务系统中使用这三种存储引擎时，绝大部分场景选择的都是 InnoDB。并且如果选择 InnoDB 存储引擎，在创建表时无需指定，因为它是默认存储引擎。对于后两种存储引擎，在实际业务开发中使用相对较少。这是因为在使用 MyISAM 存储引擎的场景中，现在通常会被另一个 NoSQL 系列的数据库 MongoDB 所替代；而在使用 Memory 存储引擎的场景中，又会被当前比较流行的 Redis 取代。