Mysql大师之路：存储引擎详解

最新推荐文章于 2024-11-06 11:25:59 发布

饭叮叮的学习日记

最新推荐文章于 2024-11-06 11:25:59 发布

阅读量902

点赞数 19

文章标签： mysql 数据库

本文链接：https://blog.csdn.net/m0_55839103/article/details/141704296

版权

MySQL 执行查询 SQL 语句的全流程

连接器：
- 作用：客户端与 MySQL 服务器之间建立连接。
- 过程：客户端发送连接请求，连接器负责与客户端建立连接，验证用户的身份和权限，连接成功后进入下一步。
查询缓存：
- 作用：检查查询语句是否已经在缓存中存在。
- 过程：如果查询缓存中有结果，直接返回结果；如果没有缓存，则继续到解析器阶段。
- 注意：MySQL 8.0 版本之后，查询缓存被废弃，因为查询缓存命中率低，反而会拖累性能。
解析器：
- 词法分析：将输入的 SQL 语句拆解为多个“词”，例如关键字、表名、列名等。
- 语法分析：根据 SQL 语法规则生成语法树，判断 SQL 语句的语法是否合法。
优化器：
- 作用：决定使用何种索引及查询顺序，生成最优的执行计划。
- 过程：优化器根据语法树生成一个或多个执行计划，选择代价最小的计划作为最终的执行计划。
执行器：
- 作用：根据优化器生成的执行计划，调用存储引擎的 API 逐步执行 SQL 语句。
- 过程：
  - 获取表的引擎实例。
  - 执行前权限检查，确保用户有相应权限。
  - 根据执行计划，逐步调用存储引擎接口，获取结果并返回给客户端。
存储引擎：
- 作用：处理来自执行器的请求，执行底层数据操作。
- 常见引擎：InnoDB、MyISAM 等，不同的存储引擎有不同的数据管理方式。
- 过程：根据表的存储引擎（如 InnoDB、MyISAM 等），通过存储引擎执行底层数据操作并返回结果。

MySQL 存储引擎概述

MySQL 的架构分为两层：Server 层和存储引擎层。Server 层负责处理连接管理、查询解析、优化以及执行等功能，而存储引擎层负责具体的数据存储和检索操作。MySQL 提供了多种存储引擎，可以根据具体业务需求选择合适的引擎。

常见的 MySQL 存储引擎

InnoDB:
- 特点:
  - 事务支持: InnoDB 是 MySQL 的默认存储引擎，支持 ACID 事务，提供了事务的提交、回滚和崩溃恢复能力。
  - 行级锁: 使用行级锁，有效提高了多用户并发操作的性能，适合高并发场景。
  - 外键支持: InnoDB 支持外键约束，确保数据的完整性。
  - MVCC（多版本并发控制）: 提供了更好的并发性。
- 适用场景:
  - 对事务有要求的应用，如金融系统、订单系统等。
  - 需要高并发读写操作的应用。
MyISAM:
- 特点:
  - 不支持事务: MyISAM 不支持事务和行级锁，只支持表级锁，锁的粒度较大。
  - 高效的读性能: 在读多写少的场景下，MyISAM 具有较高的读取性能。
  - 全文索引支持: 支持全文索引，对于需要全文检索的应用有优势。
  - 表级锁: 适合批量插入、查询密集的操作。
- 适用场景:
  - 以读操作为主的应用，如报表系统、日志系统等。
  - 对事务要求不高的场景。

InnoDB 和 MyISAM 存储引擎的对比分析

从数据存储、B+树结构、锁粒度、事务四个角度分析 InnoDB 和 MyISAM 两种 MySQL 存储引擎的区别：

1. 数据存储

InnoDB 引擎:
- 存储方式: InnoDB 采用 索引组织表 (Index Organized Table, IOT)。在这种结构中，数据按主键顺序存储在同一个 B+ 树中。每个叶子节点既包含主键索引，也包含行数据。因此，主键索引和数据存储在同一个文件中。
- 优点:
  - 聚簇索引: 数据和索引保存在同一个 B+ 树中，从主键（聚簇索引）中获取数据非常快速，无需二次查找。
  - 二级索引维护简单: 由于二级索引（非主键索引）只存储主键的引用，当记录发生修改时，如果不是修改主键，其他二级索引无需维护，性能更高。
MyISAM 引擎:
- 存储方式: MyISAM 使用堆表（Heap Table）存储结构，数据和索引分开存储。主键和二级索引都存储在一个单独的 B+ 树中，叶子节点存储数据的地址指针而不是实际数据。因此，表数据和索引数据存储在不同的文件中。
- 缺点:
  - 非聚簇索引: 所有索引，包括主键索引，都是二级索引（非聚簇索引），每次通过索引查询都需要通过指针回表查找数据，导致查询效率相对较低。
  - 更新成本高: 如果数据发生变化且存储位置变更，所有索引的地址指针都需要更新，维护成本高。

2. B+树结构

InnoDB 引擎:
- B+树叶子节点: 存储 索引 + 数据，即聚簇索引中每个叶子节点保存完整的行数据。这种设计减少了主键查询的时间。
MyISAM 引擎:
- B+树叶子节点: 存储 索引 + 数据地址，即所有索引（包括主键）都是二级索引，叶子节点仅存储数据的地址。每次通过索引查询数据时，需要先找到地址再读取数据文件，效率较低。

3. 锁粒度

InnoDB 引擎:
- 支持行级锁（Row-Level Locking）: 可以对表中的单行数据加锁，适用于高并发场景，避免了锁争用问题，提高了数据的并发处理能力。
MyISAM 引擎:
- 仅支持表级锁（Table-Level Locking）: 只能对整张表加锁，锁的粒度较大，导致在高并发情况下容易出现锁争用，性能较低，适合读多写少的场景。

4. 事务支持

InnoDB 引擎:
- 支持事务（Transaction Support）: 提供 ACID（原子性、一致性、隔离性、持久性）事务支持，支持事务回滚和崩溃恢复功能，适合对数据一致性和完整性有要求的场景。
MyISAM 引擎:
- 不支持事务: 不提供事务支持，也不支持回滚和崩溃恢复，不适合需要事务管理的应用。

为什么 MySQL 选择 InnoDB 作为默认存储引擎？

事务支持: InnoDB 引擎支持事务，能够确保数据库操作的 ACID 属性（原子性、一致性、隔离性、持久性）。这是 MyISAM 等其他存储引擎所不具备的。在需要数据一致性和事务控制的应用中，InnoDB 能够提供更强的保障。
并发性能: InnoDB 采用行级锁（Row-Level Locking）而不是 MyISAM 的表级锁（Table-Level Locking）。行级锁可以支持更高的并发量，因为它允许多个事务在同一时间对不同的行进行操作，而不需要等到表被解锁。因此，对于高并发写操作的场景，InnoDB 性能更优。
崩溃恢复能力: InnoDB 提供了崩溃恢复功能，通过使用 redo log（重做日志）来实现。在发生意外崩溃（如断电、系统崩溃等）时，InnoDB 可以通过重做日志恢复未完成的事务，确保数据的持久性和一致性。相反，MyISAM 不支持自动的崩溃恢复，如果发生崩溃可能导致数据不一致。
外键支持: InnoDB 支持外键约束，这对于确保数据的完整性和关系型数据库设计非常重要。MyISAM 不支持外键，无法在数据库层面上强制执行外键约束。

使用 `COUNT(*)` 查询哪个存储引擎更快？

MyISAM 引擎:
- MyISAM 存储引擎对 COUNT(*) 查询优化做得非常好。MyISAM 在存储表数据时，会在一个 meta 信息中维护一个 row_count 变量，记录了表中数据行的总数。因此，执行 COUNT(*) 查询时，MyISAM 直接读取这个 row_count 变量即可，查询复杂度为 O(1)，速度非常快。
- 由于 MyISAM 使用的是表级锁，所以当表数据在进行更新或插入操作时，表会被锁住，row_count 的一致性得以保证。
InnoDB 引擎:
- InnoDB 引擎在执行 COUNT(*) 时，无法直接读取类似于 row_count 这样的变量。因为 InnoDB 支持事务和多版本并发控制（MVCC），表数据可能在事务期间被多次修改。COUNT(*) 的结果可能会因事务隔离级别的不同而不同，因此 InnoDB 需要逐行遍历表数据来计算总数，查询复杂度为 O(n)，性能相对较差。

CHAR 和 VARCHAR 的区别

CHAR 和 VARCHAR 是 MySQL 中两种常用的字符串数据类型，主要区别在于它们的存储方式、语义和性能。

1. 语义上的区别

CHAR:
- 是固定长度的字符串类型。
- 在创建表时定义了固定的长度（如 CHAR(10)），无论实际存储的数据长度是多少，都会占用定义时指定的固定长度。
- 如果实际存储的数据长度小于定义的长度，系统会自动用空格填充到指定的长度。例如，CHAR(10) 类型的字段，如果存储一个长度为 5 的字符串，会自动填充 5 个空格，总共占用 10 个字符的存储空间。
VARCHAR:
- 是可变长度的字符串类型。
- 实际存储时只占用实际字符串长度的空间，不会进行空格填充。
- 例如，VARCHAR(10) 类型的字段，如果存储一个长度为 5 的字符串，它只会占用 5 个字符的存储空间。此外，还会额外用 1-2 个字节来存储字符串的长度（如果最大长度超过 255 字节，则需要 2 字节，否则 1 字节）。

2. 存储上的区别

CHAR:
- 使用固定长度的存储方式。
- 在存储时总是使用定义的最大长度。例如，CHAR(10) 总是使用 10 个字节的空间，即使存储的实际数据小于 10 字节。
- 适合存储长度固定的数据，如哈希值、身份证号等。
VARCHAR:
- 使用可变长度的存储方式。
- 在存储时只占用实际数据的长度，外加 1-2 个字节来存储数据长度的元信息。例如，VARCHAR(10) 存储一个 5 字符长度的字符串，会占用 5 + 1 = 6 个字节。
- 适合存储长度不固定的数据，如姓名、地址等。

3. 性能上的区别

CHAR:
- 理论上，CHAR 类型的字段在读取时更快，因为它是固定长度的，读取时可以直接根据固定偏移量读取数据，无需额外计算长度。
- CHAR 类型在写入时，数据库不需要记录字符串的实际长度，写入时性能稍优。
- 但是在实际应用中，CHAR 的优势仅在特定场景下体现，比如数据量非常大、字段长度固定并且固定为短字符的场景。
VARCHAR:
- 虽然 VARCHAR 需要额外存储字符串长度（1-2 字节），读取时需要多一次长度的计算，但它在磁盘 I/O 方面通常性能更优。
- VARCHAR 在数据长度可变的情况下占用更少的存储空间，这对于需要频繁访问磁盘的大量数据来说是一个优势。
- 当 InnoDB Buffer Pool（内存缓存）足够大，能容纳整个表的数据时，CHAR 和 VARCHAR 的性能差异不大。但是当 Buffer Pool 较小，无法容纳所有数据，需要频繁从磁盘读取时，VARCHAR 更省空间，因此总体性能可能会更好。

MySQL 缓存（Buffer Pool）替代 Redis？

MySQL 的 Buffer Pool 无限大，能够将所有数据放入内存，MySQL 仍然无法替代 Redis，主要原因包括：

设计目标不同：MySQL 缓存的设计目标是减少磁盘 I/O 的代价，优化磁盘读写操作；而 Redis 是专为内存操作优化的数据库，提供极高的读写速度。
性能差异：MySQL 由于使用 B+ 树索引查找、事务管理和日志记录，内存访问速度不如 Redis 快。Redis 针对内存优化，提供了接近 O(1)O(1)O(1) 的数据访问速度。
操作灵活性和并发控制：Redis 提供了更多的数据类型和操作方法，并且天然没有并发问题，MySQL 由于事务管理的需要，需要进行锁控制，影响并发性能。