跳至主要內容

MySQL常见知识点总结

JavaJuice数据库MySQL大厂面试约 30558 字大约 102 分钟

MySQL 基础

什么是关系型数据库？

关系型数据库（RDB，Relational Database）是一种基于关系模型的数据库，它通过表格（关系）来组织数据。每个表由若干行和列组成，每行代表一条记录，每列代表记录的一个字段或属性。数据通过主键、外键等关联起来，表与表之间可以建立一对一、一对多或多对多的关系。

例如，在一个电商平台中，可能有一个“用户表”和一个“订单表”。“用户表”中的每一行代表一个用户，而“订单表”中的每一行代表一条订单记录。两个表之间可以通过用户ID建立关联，形成“用户与订单”之间的一对多关系。

关系型数据库的特点

  1. 表结构：数据被存储在表格中，每个表由行（记录）和列（字段）组成。
  2. 关系模型：数据之间通过主键（唯一标识记录）和外键（表之间的关联）建立关系。
  3. SQL查询：大部分关系型数据库使用结构化查询语言（SQL）来执行增、删、改、查等操作。
  4. 事务特性（ACID）：
    • 原子性（Atomicity）：事务内的操作要么全部完成，要么全部不做。
    • 一致性（Consistency）：事务执行前后，数据库的状态必须保持一致。
    • 隔离性（Isolation）：事务的执行不受其他事务的干扰。
    • 持久性（Durability）：一旦事务提交，其结果是永久保存的，即使发生系统崩溃。

常见的关系型数据库

  • MySQL：开源、广泛使用，适合Web应用。
  • PostgreSQL：功能强大，支持更多高级功能。
  • Oracle：企业级数据库，常用于大型企业应用。
  • SQL Server：微软的关系型数据库，常用于Windows平台。
  • SQLite：轻量级数据库，通常用于嵌入式应用和桌面应用。

关系型数据库的结构化存储方式和事务支持使得它在管理复杂、结构化数据时非常有效。

什么是 SQL？

SQL（Structured Query Language，结构化查询语言）是一种专门用于与关系型数据库交互的语言。SQL 提供了一套标准化的命令和语法，用来执行数据库的基本操作，比如数据查询、数据更新、数据库管理等。SQL 的设计目的是让用户能以一种简单且高效的方式与数据库进行沟通，无论是查询数据、修改数据，还是管理数据库对象（如表、视图、存储过程等）。

SQL 的主要功能

  1. 数据库和表的管理：

    • 创建数据库：CREATE DATABASE
    • 创建表：CREATE TABLE
    • 删除数据库/表：DROP DATABASE / DROP TABLE
    • 修改表结构：ALTER TABLE

  2. 数据操作：

    • 插入数据：INSERT INTO
    • 查询数据：SELECT
    • 更新数据：UPDATE
    • 删除数据：DELETE

  3. 数据查询：
    SQL 允许用户对数据进行复杂的查询操作，支持多表连接、聚合函数、排序、分组等。例如：

    • SELECT * FROM table WHERE condition
    • SELECT COUNT(*) FROM table
    • SELECT column1, column2 FROM table JOIN another_table ON condition

  4. 视图、索引、存储过程等数据库对象管理：

    • 视图：CREATE VIEW，创建虚拟表，方便查询。
    • 索引：CREATE INDEX，提高查询效率。
    • 存储过程：CREATE PROCEDURE，封装一组 SQL 操作为一个可调用的过程。

  5. 数据分析：
    SQL 不仅用于简单的数据查询，也可以与数据分析结合，常见的功能包括：

    • 聚合函数：如 SUM(), AVG(), COUNT()
    • 分组查询：GROUP BY
    • 排序：ORDER BY

  6. 兼容性与扩展性：
    尽管 SQL 是为关系型数据库设计的，但许多非关系型数据库（如一些 NoSQL 系统）也开始支持 SQL 或类似 SQL 的查询语言。比如 Hadoop 的 Hive 和 Spark SQL 就允许使用类似 SQL 的语法进行大数据分析。

SQL 语言的简要分类

  • DML（数据操作语言）：用于操作数据，如 SELECT、INSERT、UPDATE、DELETE。
  • DDL（数据定义语言）：用于定义数据库结构，如 CREATE、ALTER、DROP。
  • DCL（数据控制语言）：用于控制访问权限，如 GRANT、REVOKE。
  • TCL（事务控制语言）：用于事务控制，如 COMMIT、ROLLBACK。

SQL 是数据库管理和操作的核心语言，几乎所有主流的关系型数据库系统都支持 SQL。

什么是 MySQL？

MySQL 是一种关系型数据库管理系统（RDBMS），用于存储和管理数据，支持使用结构化查询语言（SQL）来操作数据库。

MySQL 的特点

  1. 关系型数据库：

    • 数据以表的形式存储，每个表由行和列组成。
    • 支持复杂的关系查询，通过外键实现表间关联。

  2. 开源免费：

    • 在 GPL（通用公共许可证）下，任何人都可以免费下载和使用 MySQL。
    • 提供企业版供高性能、高安全性的应用选择。

  3. 跨平台支持：

    • 支持多种操作系统，包括 Windows、Linux 和 macOS。

  4. 高性能：

    • 优化了查询执行性能，适合高并发读写场景。

  5. 广泛应用：

    • 广泛应用于 Web 开发、内容管理系统、电商平台、数据分析等领域。

  6. 插件式存储引擎：

    • 提供多种存储引擎（如 InnoDB、MyISAM、Memory 等），根据具体需求灵活选择。

  7. 默认端口：

    • MySQL 的默认通信端口号是 3306。

MySQL 的用途

  1. 数据持久化存储：

    • 存储系统中的关键业务数据，例如用户信息、订单信息、商品数据等。

  2. 数据查询和分析：

    • 支持复杂的 SQL 查询，用于实时数据分析和报表生成。

  3. 支持高并发：

    • 适用于多用户同时访问的数据服务，例如社交平台、电商平台。

  4. 备份与恢复：

    • 提供数据备份与恢复功能，确保数据的安全性和可用性。

MySQL 的组成

  1. 数据库：

    • 存储有组织的数据集合。

  2. 表：

    • 存储具体的数据内容，每张表记录一类信息。

  3. 存储引擎：

    • 负责数据的存储和索引，常见引擎有 InnoDB（支持事务）、MyISAM（高性能查询）。

  4. SQL：

    • MySQL 支持标准 SQL 语法，用于数据操作和管理。

MySQL 的优点

  1. 简单易用：
    • 安装和使用便捷，适合初学者和开发者。
  2. 性能高：
    • 支持高并发和快速查询。
  3. 安全性好：
    • 提供用户认证、权限管理和数据加密等功能。
  4. 扩展性强：
    • 支持多种存储引擎，灵活配置满足不同场景需求。
  5. 社区支持广泛：
    • 拥有庞大的开发者社区和丰富的文档资源。

通过 MySQL，我们可以高效管理和操作数据，是现代 Web 开发和应用系统中不可或缺的数据库工具。

什么是 MySQL？

MySQL 是一种关系型数据库管理系统（RDBMS），用于存储和管理数据，支持使用结构化查询语言（SQL）来操作数据库。

MySQL 的特点

  1. 关系型数据库：

    • 数据以表的形式存储，每个表由行和列组成。
    • 支持复杂的关系查询，通过外键实现表间关联。

  2. 开源免费：

    • 在 GPL（通用公共许可证）下，任何人都可以免费下载和使用 MySQL。
    • 提供企业版供高性能、高安全性的应用选择。

  3. 跨平台支持：

    • 支持多种操作系统，包括 Windows、Linux 和 macOS。

  4. 高性能：

    • 优化了查询执行性能，适合高并发读写场景。

  5. 广泛应用：

    • 广泛应用于 Web 开发、内容管理系统、电商平台、数据分析等领域。

  6. 插件式存储引擎：

    • 提供多种存储引擎（如 InnoDB、MyISAM、Memory 等），根据具体需求灵活选择。

  7. 默认端口：

    • MySQL 的默认通信端口号是 3306。

MySQL 的用途

  1. 数据持久化存储：

    • 存储系统中的关键业务数据，例如用户信息、订单信息、商品数据等。

  2. 数据查询和分析：

    • 支持复杂的 SQL 查询，用于实时数据分析和报表生成。

  3. 支持高并发：

    • 适用于多用户同时访问的数据服务，例如社交平台、电商平台。

  4. 备份与恢复：

    • 提供数据备份与恢复功能，确保数据的安全性和可用性。

MySQL 的组成

  1. 数据库：

    • 存储有组织的数据集合。

  2. 表：

    • 存储具体的数据内容，每张表记录一类信息。

  3. 存储引擎：

    • 负责数据的存储和索引，常见引擎有 InnoDB（支持事务）、MyISAM（高性能查询）。

  4. SQL：

    • MySQL 支持标准 SQL 语法，用于数据操作和管理。

MySQL 的优点

  1. 简单易用：
    • 安装和使用便捷，适合初学者和开发者。
  2. 性能高：
    • 支持高并发和快速查询。
  3. 安全性好：
    • 提供用户认证、权限管理和数据加密等功能。
  4. 扩展性强：
    • 支持多种存储引擎，灵活配置满足不同场景需求。
  5. 社区支持广泛：
    • 拥有庞大的开发者社区和丰富的文档资源。

通过 MySQL，我们可以高效管理和操作数据，是现代 Web 开发和应用系统中不可或缺的数据库工具。

MySQL 的优点

MySQL 作为一种流行的关系型数据库管理系统，具备许多使其广泛应用的优点。以下是 MySQL 受欢迎的主要原因：

  1. 成熟稳定，功能完善：

    • MySQL 已经过多年的发展，具备了高度的稳定性，能够支持大规模应用和高并发场景。它提供了丰富的功能，满足大部分业务需求。

  2. 开源免费：

    • MySQL 是开源软件，遵循 GPL 许可证，任何人都可以免费使用、修改和分发。这为广大开发者和企业提供了低成本的数据库解决方案。

  3. 文档丰富：

    • MySQL 拥有详尽的官方文档，此外，社区中也有大量优质的技术文章和教程，方便开发者学习和解决问题。

  4. 开箱即用，操作简单，维护成本低：

    • MySQL 安装简便，操作直观，配置和维护相对容易。适合快速部署，减少了开发和运维的复杂性。

  5. 兼容性好：

    • MySQL 支持常见的操作系统（如 Windows、Linux、macOS），并与多种编程语言（如 Java、Python、PHP、C++ 等）兼容，方便与各种技术栈结合。

  6. 社区活跃，生态完善：

    • MySQL 拥有一个庞大的开发者社区，提供技术支持、插件、工具和第三方库，生态系统十分丰富。

  7. 事务支持优秀：

    • MySQL 的 InnoDB 存储引擎支持事务，并且其默认隔离级别是 REPEATABLE-READ，能够避免脏读、不可重复读等问题，并且在性能上没有显著影响。InnoDB 还通过多版本并发控制（MVCC）解决了幻读问题。

  8. 支持分库分表、读写分离、高可用：

    • MySQL 支持分库分表和读写分离等技术，能够实现数据库的水平扩展和负载均衡。通过 Master-Slave 复制或 Cluster 等技术，可以实现高可用性，保证系统的可靠性。

这些优点使得 MySQL 成为 Web 开发、数据存储和企业级应用的常见选择，尤其适用于要求高性能、易维护和低成本的项目。

MySQL 字段类型

MySQL 字段类型

MySQL 中的字段类型可以简单分为三大类：数值类型、字符串类型和日期时间类型。每一类字段类型都适用于不同的存储和查询需求。常用的字段类型在开发过程中频繁使用，并且在面试中也常常被提及。

1. 数值类型

  • 整型：

    • TINYINT：非常小的整数，取值范围为 -128 到 127（有符号）或 0 到 255（无符号）。
    • SMALLINT：小范围的整数，取值范围为 -32,768 到 32,767（有符号）或 0 到 65,535（无符号）。
    • MEDIUMINT：中等范围的整数，取值范围为 -8,388,608 到 8,388,607（有符号）或 0 到 16,777,215（无符号）。
    • INT：常见的整数类型，取值范围为 -2,147,483,648 到 2,147,483,647（有符号）或 0 到 4,294,967,295（无符号）。
    • BIGINT：大范围的整数，取值范围为 -9,223,372,036,854,775,808 到 9,223,372,036,854,775,807（有符号）或 0 到 18,446,744,073,709,551,615（无符号）。

  • 浮点型：

    • FLOAT：单精度浮点数，精度较低，适用于存储大范围的小数值。
    • DOUBLE：双精度浮点数，精度较高，适用于存储更精确的小数值。

  • 定点型：

    • DECIMAL：用于存储精确的小数值，尤其是需要高精度的场合，如货币存储。可以指定总位数和小数位数，如 DECIMAL(10,2) 表示总共有 10 位数字，其中 2 位为小数部分。

2. 字符串类型

  • 定长字符串：

    • CHAR(N)：定长字符串，最多可以存储 255 字符，空余部分会用空格填充。适用于长度固定的字符串（例如国家代码、性别等）。

  • 变长字符串：

    • VARCHAR(N)：变长字符串，最多可以存储 65,535 字符。适用于长度不固定的字符串，通常比 CHAR 更节省空间。

  • 文本类型：

    • TINYTEXT：最多存储 255 字符的文本。
    • TEXT：最多存储 65,535 字符的文本。
    • MEDIUMTEXT：最多存储 16,777,215 字符的文本。
    • LONGTEXT：最多存储 4,294,967,295 字符的文本。

  • BLOB 类型（用于存储二进制数据）：

    • TINYBLOB：最多存储 255 字节的二进制数据。
    • BLOB：最多存储 65,535 字节的二进制数据。
    • MEDIUMBLOB：最多存储 16,777,215 字节的二进制数据。
    • LONGBLOB：最多存储 4,294,967,295 字节的二进制数据。

3. 日期时间类型

  • YEAR：用于表示年份，范围从 1901 到 2155。
  • TIME：表示时间，格式为 HH:MM:SS，存储时间值（无日期）。
  • DATE：表示日期，格式为 YYYY-MM-DD，不包含时间部分。
  • DATETIME：表示日期和时间，格式为 YYYY-MM-DD HH:MM:SS，存储完整的时间戳。
  • TIMESTAMP：表示时间戳，格式为 YYYY-MM-DD HH:MM:SS，但存储的是自 1970 年 1 月 1 日以来的秒数。TIMESTAMP 会根据时区变化自动调整，而 DATETIME 则不会。

这些字段类型根据不同的业务需求和存储要求来选择。例如，如果你需要存储一个人的姓名，通常会使用 VARCHAR；如果存储金额，可能会选择 DECIMAL 类型；而存储图像数据则会使用 BLOB 类型。

关于字段类型的优化，《高性能 MySQL（第三版）》第四章，这一章节详细讨论了如何根据业务需求和数据量选择最合适的字段类型，从而优化数据库性能。

整数类型的 UNSIGNED 属性

MySQL 中的整数类型可以使用 UNSIGNED 属性来指定该字段为无符号整数，表示不允许存储负数值。通过使用 UNSIGNED 属性，可以将该字段的正整数范围扩展一倍，因为没有负数值需要占用存储空间。

作用

  • 增加最大值的上限：使用 UNSIGNED 属性时，字段的最大值可以增加一倍，因为不再需要存储负数。

    • 例如，TINYINT UNSIGNED 的取值范围为 0 ~ 255，而普通的 TINYINT 取值范围是 -128 ~ 127。
    • INT UNSIGNED 的取值范围为 0 ~ 4,294,967,295，而普通的 INT 类型取值范围是 -2,147,483,648 ~ 2,147,483,647。

  • 适用于递增的 ID 字段：对于自增字段，通常是 ID 列，通常我们不需要负数，因此使用 UNSIGNED 类型更为合适。这样不仅避免了负值的出现，而且能够增加可用的 ID 值上限，尤其是在高并发、大数据量的应用场景下，这个扩展是非常重要的。

示例

  • 普通的 INT 类型：

    CREATE TABLE users (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(100),
    PRIMARY KEY (id)
);
    • id 字段的范围是 -2,147,483,648 ~ 2,147,483,647。

  • 使用 UNSIGNED 的 INT 类型：

    CREATE TABLE users (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(100),
    PRIMARY KEY (id)
);
    • id 字段的范围变为 0 ~ 4,294,967,295，即可以存储更多的正整数 ID。
总结

通过在整数类型上使用 UNSIGNED 属性，可以最大化利用字段存储空间，适用于那些只需要存储正整数的场景，尤其是自增的 ID 字段。这种优化有助于提高数据存储效率，避免负值带来的额外存储开销。

CHAR 和 VARCHAR 的区别

CHAR 和 VARCHAR 是 MySQL 中两种常用的字符串类型，它们的主要区别在于存储方式和使用场景。

1. 存储方式

  • CHAR 是定长字符串，存储时会填充空格以达到指定的长度。即使实际存储的字符串长度小于定义的长度，系统仍会占用固定的存储空间。例如，如果定义了 CHAR(10)，存储 5 个字符时，系统会自动在后面补充 5 个空格，直到达到 10 个字符的长度。

  • VARCHAR 是变长字符串，仅存储实际的字符串长度，并且需要额外的 1 或 2 个字节来存储字符串的长度信息。VARCHAR 不会填充空格，存储的是实际的字符数。

2. 性能

  • CHAR 的固定长度特性使得它在存取时的性能较为一致，尤其适用于长度固定的字段。由于不需要在存储时计算长度，它的检索速度通常会更快。
  • VARCHAR 由于是变长的字符串，它在存储时需要额外的字节来记录长度信息，且对于长度差异较大的数据，可能会导致存储碎片，影响性能。

3. 适用场景

  • CHAR 更适合存储长度相同或接近的字符串，比如：
    • 密码哈希（如 MD5、Bcrypt）
    • 身份证号、电话号码、邮政编码等固定长度的数据。
  • VARCHAR 更适合存储长度不定的字符串，比如：
    • 用户名、文章标题、评论内容等。
    • VARCHAR 可以有效节省存储空间，尤其是当字段值长度差异较大时。

4. 存储空间

  • CHAR 在存储时总是占用定义长度的空间，无论实际存储的字符数是多少。比如，CHAR(10) 字段会始终占用 10 字符的空间。
  • VARCHAR 只占用实际字符的长度，并加上额外的 1 或 2 字节存储长度信息，适用于存储变长数据。

5. 示例

  • CHAR(10)：无论存储什么内容，都会占用 10 字符的空间，短于 10 字符的字符串会填充空格。

    CREATE TABLE example (
    code CHAR(10)
);

    存储 "123" 后，会占用 10 个字符的空间，其中包括 7 个空格。

  • VARCHAR(10)：只存储实际的字符，不会填充空格。

    CREATE TABLE example (
    code VARCHAR(10)
);

    存储 "123" 后，只会占用 3 个字符的空间，不会多余存储空格。

总结
  • 使用 CHAR 时，如果字段长度固定且数据量不大，性能和存储都较为高效。
  • 使用 VARCHAR 时，适用于字段长度变化较大的数据，可以节省存储空间，适合长度不确定的字符串。

选择使用哪种类型主要依赖于实际存储数据的特性以及性能需求。

VARCHAR(100) 和 VARCHAR(10) 的区别

VARCHAR(100) 和 VARCHAR(10) 都是变长字符串类型，主要区别在于最大存储字符数的不同。

1. 最大存储长度

  • VARCHAR(100)：表示最多能存储 100 个字符。
  • VARCHAR(10)：表示最多能存储 10 个字符。

这意味着，VARCHAR(100) 适用于存储字符长度可能较长的数据，而 VARCHAR(10) 适用于较短的字符串。

2. 存储空间

虽然 VARCHAR(100) 和 VARCHAR(10) 能存储的最大字符数不同，但 它们存储相同字符串时，占用的存储空间是一样的，因为 VARCHAR 类型是变长的，它只存储实际的字符数据并加上额外的字节来记录字符串的长度。

例如，如果你存储一个 5 个字符的字符串，无论是 VARCHAR(100) 还是 VARCHAR(10)，它们都会占用相同的空间，只会占用 5 个字符的存储空间加上 1 或 2 个字节来存储字符串的长度。

3. 内存占用和性能

  • VARCHAR(100)：尽管在磁盘上，它只会存储实际的数据，但在 内存操作 时，通常会为每个字段分配固定大小的内存块，这意味着 VARCHAR(100) 会在内存中分配更多的空间（即为 100 个字符分配空间），即使存储的字符少于 100 个。

  • VARCHAR(10)：相对于 VARCHAR(100)，它在内存中的分配会小一些，因为它只为 10 个字符分配空间。

4. 业务拓展性

  • VARCHAR(100) 提供了更大的字符存储范围，适用于需要处理较长字符串的场景，比如长文本描述、文章标题等。它在未来的业务拓展中更具灵活性，因为可以存储更长的字符串，减少了未来修改表结构的可能性。
  • VARCHAR(10) 则适用于长度较短且较为固定的数据，比如手机号、简短的标识符等。如果实际数据超过了 10 个字符，必须通过修改表结构来增加字段的长度。

5. 排序与索引

  • 排序时的内存使用：如果在查询中对 VARCHAR(100) 字段进行排序，会消耗更多的内存，因为它会分配最多 100 字符的空间用于比较。即使实际数据的长度远小于 100 字符，排序时仍然会按照 VARCHAR(100) 的长度来处理。
  • 索引优化：如果字段长度较大，索引的效率也可能受影响。在一些查询中，较大的 VARCHAR 字段可能导致性能问题，尤其是在大数据量时。
总结
  • VARCHAR(100) 适用于需要存储较长字符数据的场景，具有更好的业务拓展性。
  • VARCHAR(10) 适用于存储较短且长度相对固定的字符串。
  • 在 存储空间 上，二者没有太大区别，但 内存占用 和 性能 可能会受到影响，VARCHAR(100) 在内存操作时消耗更多空间，尤其是进行排序时。

DECIMAL 和 FLOAT/DOUBLE 的区别

DECIMAL 和 FLOAT/DOUBLE 都用于存储小数值，但它们有显著的区别，特别是在精度和存储方式上。

1. 定点数与浮点数

  • DECIMAL 是定点数类型，表示数字的小数部分有固定的精度，适用于需要精确计算的场景，例如金融、货币等领域。

    • 它不使用二进制浮点数，而是采用字符串的方式存储数字，确保精度不会丢失。
    • 例如，DECIMAL(10,2) 表示最多 10 位数字，其中 2 位是小数部分。

  • FLOAT 和 DOUBLE 是浮点数类型，用于存储近似的小数值。它们使用二进制格式存储数字，可能会出现精度损失，特别是在进行连续计算时。

    • FLOAT 是单精度浮点数，DOUBLE 是双精度浮点数。DOUBLE 提供更高的精度和更大的存储范围。

2. 精度

  • DECIMAL 类型能够精确存储小数部分，不会丢失精度。它非常适合用于财务计算或任何需要精确表示小数的场景。
    • 例如，计算货币金额时，使用 DECIMAL 可以避免由于浮点数存储方式而产生的精度问题。
  • FLOAT 和 DOUBLE 存储的是近似值，它们的表示方式会有舍入误差。这在科学计算或工程计算中通常是可以接受的，但在金融或需要高精度的计算中，可能会导致问题。

3. 存储方式

  • DECIMAL 是基于字符串存储的，因此对于小数位的存储非常精确，但相对来说，它的存储空间更大。
  • FLOAT 和 DOUBLE 是基于二进制浮点表示存储的，虽然它们在存储和计算速度上通常较快，但由于采用浮点表示，可能会遇到一些精度丢失的问题。

4. 适用场景

  • DECIMAL：
    • 适用于需要精确存储小数的场景，特别是与财务、货币、税收等相关的应用。
    • 例如，存储银行账户余额、支付金额等。
  • FLOAT 和 DOUBLE：
    • 适用于对精度要求较低的场景，主要用于科学计算、工程计算等场景。
    • 例如，存储物理实验的数据、GPS 坐标等。

5. Java 对应类型

  • DECIMAL 在 Java 中对应的是 java.math.BigDecimal，该类能够提供任意精度的浮动小数计算，保证数值的精确性。
  • FLOAT 和 DOUBLE 在 Java 中对应的是 float 和 double 类型，虽然它们能提供快速计算，但由于浮点运算的精度限制，可能会出现不准确的结果。

6. 性能

  • DECIMAL 类型的计算速度通常比 FLOAT 和 DOUBLE 慢，因为它需要处理精确的小数计算。
  • FLOAT 和 DOUBLE 类型的计算速度较快，因为它们是基于硬件浮点运算的，但精度可能不如 DECIMAL。
总结
  • DECIMAL：定点数，适用于需要精确存储小数的场景（如金融、货币），保证计算精度。
  • FLOAT/DOUBLE：浮点数，适用于对精度要求不高的科学计算、工程计算等，存储和计算速度较快，但可能会丢失精度。

选择哪种类型取决于具体的业务需求，若需要精确的计算结果，尤其是涉及财务数据时，推荐使用 DECIMAL。若是需要处理大量近似的小数值且计算速度更为重要，可以选择 FLOAT 或 DOUBLE。

为什么不推荐使用 TEXT 和 BLOB？

尽管 TEXT 和 BLOB 类型在存储大数据时非常有用，但在实际开发中，它们通常不被推荐使用，主要因为它们在性能、管理以及一些功能上的限制。以下是一些原因：

1. 不能有默认值

  • TEXT 和 BLOB 类型不能有默认值。这意味着在插入数据时，必须显式指定这些字段的值。如果没有合适的默认值，会使插入操作变得更加麻烦。

2. 不能使用内存临时表

  • 对于 TEXT 和 BLOB 类型的数据，MySQL 在处理临时表时会使用磁盘而非内存。内存临时表是快速查询和处理的关键，而将大数据存储在磁盘上会极大影响性能。
  • 如果字段是 TEXT 或 BLOB 类型，临时表只能存储在磁盘上，导致性能开销增加。

3. 检索效率低

  • TEXT 和 BLOB 类型数据较大时，读取和写入这些数据会导致更高的磁盘 I/O 开销。在检索大量数据时，性能显著下降。
  • 在读取包含大量 TEXT 或 BLOB 字段的记录时，数据库可能需要更多的时间和资源。

4. 不能直接创建索引

  • 默认情况下，TEXT 和 BLOB 字段不能直接创建索引，只有在指定了字段的前缀长度（例如 TEXT(255)）时才能进行索引。
  • 即使创建了索引，也无法对整个字段进行索引，只能对指定的前缀长度创建索引，这影响了查询效率，尤其是在需要完全匹配或范围查询时。

5. 消耗大量的网络和 I/O 带宽

  • 当 TEXT 或 BLOB 类型的字段较大时，传输这些数据会占用大量的网络带宽和数据库的 I/O 带宽。在大规模数据传输时，可能会导致性能瓶颈，影响其他正常的数据库操作。

6. 可能导致 DML 操作变慢

  • 对于大字段（如 TEXT 或 BLOB 类型的字段）的修改，可能会导致表上的 DML（数据操作语言）操作变得更慢。这是因为每次更新或插入时都涉及到大数据量的传输和存储处理。

7. 存储效率问题

  • 如果你预期某些字段的长度在一定范围内（例如用户简介、评论等），使用 VARCHAR 会更高效，因为它可以动态调整存储空间。而 TEXT 类型始终会占用较大的空间，并且可能会浪费空间。

8. 数据碎片化问题

  • TEXT 和 BLOB 类型的数据如果经常被更新、删除，可能导致存储空间碎片化，影响数据库的性能。
总结

如果字段的大小可以通过 VARCHAR 来满足，尽量避免使用 TEXT 和 BLOB 类型。这些类型通常会影响数据库的性能，尤其是在查询、更新和存储大量数据时。适当选择字段类型不仅能提高性能，还能避免不必要的资源浪费。

DATETIME 和 TIMESTAMP 的区别

虽然 DATETIME 和 TIMESTAMP 都用于存储日期和时间数据，但它们在存储、时间范围、时区处理等方面存在显著的差异。以下是它们的主要区别：

1. 存储方式与字节数

  • DATETIME 类型需要 8 个字节来存储日期和时间。
  • TIMESTAMP 类型只需要 4 个字节来存储日期和时间。

2. 时间范围

  • DATETIME 的时间范围从 1000-01-01 00:00:00 到 9999-12-31 23:59:59，适合存储较为广泛的时间数据。
  • TIMESTAMP 的时间范围较小，表示从 1970-01-01 00:00:01 到 2037-12-31 23:59:59，即基于 Unix 时间戳（自 1970 年 1 月 1 日以来的秒数）。

3. 时区处理

  • DATETIME 不包含时区信息，表示的是一个固定的日期和时间，不受时区影响。它在不同的时区下显示相同的时间。
  • TIMESTAMP 是与时区相关的，存储的是 UTC 时间（世界协调时间），并会根据数据库的时区设置自动转换为本地时间。因此，当数据库的时区发生变化时，TIMESTAMP 字段的值也会相应地调整，而 DATETIME 不会受影响。

4. 默认值与自动更新

  • 在 MySQL 中，如果字段类型是 TIMESTAMP，通常它会在插入数据时自动填充为当前时间（CURRENT_TIMESTAMP），并且在记录被更新时也能自动更新时间。
  • DATETIME 字段需要显式地设置时间，并且不会像 TIMESTAMP 一样自动更新。

5. 使用场景

  • DATETIME 适用于需要记录某一固定时间（无时区偏差）的场景。例如，某个固定事件的时间戳。
  • TIMESTAMP 更适用于需要记录按 UTC 时间存储的数据，并且该数据会根据时区设置动态变化的场景。例如，日志记录、创建时间等。
总结
  • DATETIME 适合存储跨时区的时间，且不会受时区影响，存储的是一个固定的日期时间。
  • TIMESTAMP 则适合需要考虑时区的场景，它基于 UTC 时间存储，并根据时区设置进行自动转换。

关于两者的详细对比，请参考我写的MySQL 时间类型数据存储建议。

NULL 和 ''（空字符串）的区别

虽然 NULL 和 ''（空字符串）看起来都代表着“空”，但它们在数据库中的含义和表现是完全不同的。以下是它们的主要区别：

1. 定义和语义

  • NULL：表示“没有值”或“未知值”。它并不是一个空值，而是指缺少有效数据。可以理解为数据库不知道该存储什么值。
  • ''：表示“空字符串”，是一个有效的字符串类型，只不过它的长度是 0。也就是说，它表示的是一个没有字符的字符串，而不是“没有值”。

2. 比较操作

  • NULL 不能直接与任何值进行比较，因为 NULL 本身就是“未知”的。比如，NULL = NULL 的结果是 false，即使两个 NULL 值看起来相同，它们在比较时并不被认为相等。
  • ''（空字符串）可以和其他字符串进行直接比较。例如，'' = '' 返回 true，因为它是一个有效的、长度为 0 的字符串。

3. 存储

  • NULL 需要占用空间（在 MySQL 中，NULL 会占用 1 个字节的额外空间，表示该值是未知的）。
  • ''（空字符串）不占用任何字符的存储空间，因为它只是一个没有字符的字符串。

4. 聚合函数的处理

  • NULL 值会被聚合函数忽略。例如，SUM、AVG 等聚合函数会忽略 NULL，仅对非 NULL 值进行计算。
  • ''（空字符串）不会被忽略，它被当作有效值处理。例如，COUNT(列名) 会统计空字符串，但会忽略 NULL。

5. 查询判断

  • NULL 值查询时，必须使用 IS NULL 或 IS NOT NULL 来判断，不能用 =、!= 等常规的比较运算符。例如：
    • SELECT * FROM table WHERE column IS NULL;
  • ''（空字符串）可以用常规的比较运算符进行判断。例如：
    • SELECT * FROM table WHERE column = '';

6. 在数据库设计中的使用

  • NULL 作为列的默认值并不推荐，因为它可能导致一些难以发现的潜在问题，如不确定的值或复杂的查询逻辑。一般来说，应该明确为字段提供默认值（如 0、'' 或其他有意义的默认值），避免使用 NULL。
  • ''（空字符串）通常用于表示空文本或缺失的字符数据。它可以作为字段的默认值，但要确保它的使用符合业务逻辑。
总结
  • NULL 代表的是“无值”或“未知值”，不能直接进行比较，并且需要特殊的查询方法和处理。
  • ''（空字符串）是一个有效的、长度为 0 的字符串，可以直接使用比较运算符，并且不会被聚合函数忽略。

在设计数据库时，NULL 和 '' 需要根据实际的业务需求来选择使用，避免混淆和潜在的逻辑错误。

Boolean 类型如何表示？

在 MySQL 中，没有专门的布尔类型，但是可以使用 TINYINT(1) 类型来表示布尔值。具体来说：

  • TINYINT(1) 类型可以存储两个值：0 和 1。
    • 0 表示 false（假）。
    • 1 表示 true（真）。

MySQL 对于 TINYINT(1) 类型的处理，是通过数值的方式来模拟布尔类型。虽然从语义上理解为布尔值，但实际上它是一个整数类型。

示例

CREATE TABLE example (
    is_active TINYINT(1) NOT NULL
);

-- 插入布尔值
INSERT INTO example (is_active) VALUES (1);  -- true
INSERT INTO example (is_active) VALUES (0);  -- false

在查询时，你可以将 0 和 1 分别映射为 false 和 true：

SELECT is_active FROM example WHERE is_active = 1;  -- 查询所有 active 状态的记录

虽然 MySQL 并没有提供布尔类型，但是在开发过程中可以根据约定使用 TINYINT(1) 来代表布尔值。

MySQL 基础架构

建议配合 SQL 语句在 MySQL 中的执行过程 这篇文章来理解 MySQL 基础架构。另外，“一个 SQL 语句在 MySQL 中的执行流程”也是面试中比较常问的一个问题。

MySQL 基础架构

MySQL 的架构设计是一个分层结构，每一层负责不同的功能。客户端提交的 SQL 语句在 MySQL 内部经过多个组件的处理，直到最终执行并返回结果。以下是 MySQL 的主要架构组成部分：

1. 连接器（Connection Layer）

  • 负责管理与客户端的连接，包括身份认证和权限控制。
  • 当客户端连接到 MySQL 时，连接器会验证用户名、密码及相关权限，确保客户端的合法性。

2. 查询缓存（Query Cache）

  • 这是一个缓存层，用于缓存查询结果以加速重复查询的响应。
  • 但需要注意的是，MySQL 8.0 之后移除了查询缓存，因为这个功能在很多场景下并不高效。

3. 分析器（Parser）

  • 当查询没有命中缓存时，SQL 语句会经过分析器。
  • 分析器负责解析 SQL 语句，检查其语法是否正确，并将 SQL 转换为内部的解析树或解析计划。

4. 优化器（Optimizer）

  • 优化器负责生成查询的执行计划，即决定查询如何执行（例如选择合适的索引、如何连接表等）。
  • MySQL 会根据多种因素（如表的大小、索引的可用性等）来选择最优的执行策略。

5. 执行器（Executor）

  • 执行器负责实际执行 SQL 语句，处理数据的增删改查操作。
  • 在执行之前，执行器会检查用户是否有足够的权限，如果没有权限，则会报错。
  • 执行器执行的操作通常会与存储引擎交互，存储引擎负责具体的数据读取和写入。

6. 插件式存储引擎（Storage Engine Layer）

  • 存储引擎是 MySQL 最底层的组件，负责数据的物理存储、检索、修改等。
  • MySQL 支持多种存储引擎，最常用的包括：
    • InnoDB（默认存储引擎）：支持事务、行级锁、外键等，适合高并发、高事务的场景。
    • MyISAM：不支持事务，适合读多写少的场景。
    • Memory：将数据存储在内存中，适用于对性能要求极高且数据量不大的场景。
总结

通过上面的架构，每一个 SQL 语句会从连接器到存储引擎经历多个层次的处理。理解这个架构有助于我们更好地优化 SQL 查询，提高数据库的性能和稳定性。在面试中，理解 MySQL 的执行流程也是一个常见的考察点。

MySQL 存储引擎

MySQL 核心在于存储引擎，想要深入学习 MySQL，必定要深入研究 MySQL 存储引擎。

MySQL 支持的存储引擎及默认存储引擎

MySQL 支持多种存储引擎，每个存储引擎都有不同的特性，适用于不同的应用场景。可以通过 SHOW ENGINES 命令查看当前 MySQL 实例支持的所有存储引擎。

1. InnoDB

  • 默认存储引擎：从 MySQL 5.5.5 版本开始，InnoDB 成为默认的存储引擎。
  • 特点：
    • 支持事务，遵循 ACID 特性。
    • 支持外键（foreign key）。
    • 使用行级锁，适合高并发、高事务的应用。
    • 性能较优，尤其在读写操作较多的情况下。

2. MyISAM

  • 特点：
    • 不支持事务，不支持外键。
    • 使用表级锁，适合读多写少的应用。
    • 性能上在纯读取的场景下较好，但并发性较差。
  • 在 MySQL 5.5.5 之前，MyISAM 是默认的存储引擎。

3. Memory

  • 特点：
    • 将数据存储在内存中，查询速度非常快。
    • 不支持持久化，服务器重启后数据丢失。
    • 适用于缓存类应用。

4. CSV

  • 特点：
    • 数据以 CSV 格式存储在文件中。
    • 不支持索引，适用于一些临时性的数据存储。

5. NDB (Cluster)

  • 特点：
    • 主要用于 MySQL 集群架构，支持分布式数据库。
    • 数据存储在内存中，支持高可用性和高并发。

6. BLACKHOLE

  • 特点：
    • 类似于一个“黑洞”，所有写入的数据都会被丢弃，但可以用于复制数据流。
    • 适用于日志记录或分布式复制的场景。

7. 其他引擎

  • MySQL 还支持其他一些存储引擎，例如 FEDERATED、ARCHIVE、EXAMPLE 等，但这些通常不常用。

8. 查看默认存储引擎

你可以使用以下命令查看当前 MySQL 实例的默认存储引擎：

SHOW VARIABLES LIKE '%storage_engine%';

输出结果通常如下：

+---------------------------------+-----------+
| Variable_name                   | Value     |
+---------------------------------+-----------+
| default_storage_engine          | InnoDB    |
| default_tmp_storage_engine      | InnoDB    |
| disabled_storage_engines        |           |
| internal_tmp_mem_storage_engine | TempTable |
+---------------------------------+-----------+
总结
  • 默认存储引擎：InnoDB
  • InnoDB 提供事务支持、行级锁、外键约束，是最常用的存储引擎。
  • 根据具体业务需求，可以选择不同的存储引擎，例如读多写少的场景可以使用 MyISAM，内存存储场景可以使用 Memory。

MySQL 存储引擎架构

MySQL 采用 插件式存储引擎架构，这意味着用户可以根据不同的应用需求为不同的表选择不同的存储引擎。每个存储引擎提供不同的功能特性，支持不同的数据存储方式，用户可以在保证性能和功能的前提下选择合适的存储引擎。

架构概述

  1. 连接层（Connection Layer）：

    • MySQL 通过连接层与客户端通信，负责用户的身份认证、权限控制和连接管理。

  2. 查询处理层（Query Processing Layer）：

    • 查询处理层负责接收客户端发来的 SQL 语句，进行语法解析、优化和执行计划生成。如果缓存未命中，查询将传递到存储引擎进行实际数据的读取或写入操作。

  3. 存储引擎层（Storage Engine Layer）：

    • 存储引擎负责实际的数据存储和检索工作，MySQL 支持多种存储引擎，每个引擎有不同的特性，能够根据具体场景进行选择。
    • 每个存储引擎提供自己的接口实现，MySQL 将这些引擎与查询处理层解耦，支持通过插件的方式扩展或替换存储引擎。

  4. 文件系统（File System）：

    • 存储引擎与文件系统交互，将数据持久化到磁盘。不同的存储引擎使用不同的文件格式和存储机制。

存储引擎的种类

MySQL 提供了多种存储引擎，每种存储引擎适用于不同的场景：

  • InnoDB：

    • 默认的事务性存储引擎，支持 ACID 特性，适合高并发、事务要求严格的应用。
    • 提供行级锁、外键约束、支持事务的回滚和提交。

  • MyISAM：

    • 适用于只读或读写比重偏向读取的场景。
    • 不支持事务和外键，提供表级锁，性能相对较高，但缺乏事务的可靠性。

  • Memory：

    • 数据存储在内存中，适用于对性能要求极高的临时性数据存储，如缓存、会话数据等。
    • 表数据丢失问题较为严重，适用于临时表和高速数据处理。

  • CSV：

    • 使用逗号分隔值（CSV）格式存储数据，适用于需要与其他应用进行数据交换的场景。

  • NDB：

    • 适用于分布式数据库系统，通常用在集群环境中，支持数据的高可用性。

  • Archive：

    • 用于存储归档数据，数据以高压缩格式存储，适用于日志记录和历史数据存储。

  • Blackhole：

    • 一个虚拟的存储引擎，用于测试或将所有写入的数据丢弃。

插件式架构

MySQL 的存储引擎架构非常灵活，用户可以根据需求选择合适的存储引擎，甚至在同一个数据库中对不同的表使用不同的存储引擎。比如，某些表可能需要事务支持和高并发处理，那么可以选择 InnoDB，而一些只需要存储大量静态数据的表则可以选择 MyISAM。

通过插件式架构，MySQL 的存储引擎可以随时扩展或更替。例如，如果需要自定义存储引擎来满足特殊需求，开发者可以实现 MySQL 提供的接口，编写并部署自定义引擎。这种灵活性使得 MySQL 成为一个高度可定制的数据库管理系统。

存储引擎接口与扩展

MySQL 提供了存储引擎接口（Storage Engine Interface），允许开发者实现自己的存储引擎。这些存储引擎可以完全按照应用的特定需求进行设计，比如使用特定的缓存策略、数据加密方式或特殊的并发控制策略。自定义存储引擎可以非常好地适应特定行业的需求，提供专门化的优化。

总结

MySQL 存储引擎采用插件式架构，可以灵活地为不同的表选择不同的存储引擎，以适应不同的应用场景。存储引擎如 InnoDB、MyISAM 等各有特点，用户可以根据性能、事务需求、存储需求等方面进行选择。同时，MySQL 也支持开发自定义存储引擎，以便在极端定制需求的情况下使用。

MyISAM 和 InnoDB 的区别

MyISAM 和 InnoDB 是 MySQL 最常见的两种存储引擎，各有不同的特性和应用场景。以下是两者的详细对比：

1. 行级锁 vs 表级锁

  • MyISAM: 只支持表级锁。当多个用户同时对表进行写操作时，MyISAM 会锁住整张表，导致并发性能较差。
  • InnoDB: 支持行级锁和表级锁。默认情况下使用行级锁，能够提高并发性能，尤其是在写操作较多的场景下。

2. 事务支持

  • MyISAM: 不支持事务。操作无法进行回滚或提交，适合对事务要求不高的应用场景。
  • InnoDB: 完全支持事务，支持 ACID 特性（原子性、一致性、隔离性、持久性）。通过支持 commit 和 rollback，保证数据一致性。

关于 MySQL 事务的详细介绍，可以看看这篇文章：MySQL 事务隔离级别详解。

3. 外键支持

  • MyISAM: 不支持外键约束。
  • InnoDB: 支持外键约束，能够确保数据的完整性和一致性，尤其在进行关联表操作时非常有用。

4. 崩溃恢复

  • MyISAM: 不支持崩溃恢复。数据库崩溃后可能会导致数据丢失。
  • InnoDB: 支持崩溃恢复。使用 redo log 和 undo log，能够在数据库崩溃后保证数据一致性，自动恢复到崩溃前的状态。

5. MVCC（多版本并发控制）

  • MyISAM: 不支持 MVCC。每次操作都可能需要加锁，影响并发性能。
  • InnoDB: 支持 MVCC，允许多个事务并发执行，避免了大量的锁竞争，提高了并发性。

6. 索引实现

  • MyISAM: 索引和数据文件是分开的。数据存储在磁盘的独立文件中，索引文件则存储在另一个文件中。
  • InnoDB: 数据和索引存储在一个文件中。InnoDB 使用 聚簇索引，即表数据本身按索引排序，叶子节点保存完整数据。相较于 MyISAM，数据访问更高效。

详细区别，推荐你看看这篇文章：MySQL 索引详解。

7. 性能

  • MyISAM: 在读操作频繁的场景下性能较好，因为 MyISAM 的表级锁比较简单，读操作开销小。然而，写操作的并发性能较差。
  • InnoDB: 在读写混合或者并发写操作较多的场景中表现优越。InnoDB 采用行级锁和更高效的缓存机制，能够随着 CPU 核数的增加而线性提升性能。

8. 数据缓存机制

  • MyISAM: 使用键缓存（Key Cache）来缓存索引数据，不缓存实际的数据行。适合只读或以读为主的场景。
  • InnoDB: 使用缓冲池（Buffer Pool）缓存数据和索引。通过缓存机制，InnoDB 提高了数据访问的速度，尤其适用于读写操作频繁的情况。

9. 表空间管理

  • MyISAM: 每个表使用独立的文件来存储数据和索引。
  • InnoDB: 支持表空间管理，多个表的数据可以存储在一个共享表空间中，也支持每个表使用独立的表空间。

10. 适用场景

  • MyISAM: 适合静态数据的存储，例如日志表、搜索引擎索引等对数据一致性和事务要求不高的应用。
  • InnoDB: 适合需要事务支持、高并发读写和数据一致性的应用，比如电商网站、银行系统等需要确保数据完整性和高可靠性的场景。
总结对比
特性MyISAMInnoDB
锁机制表级锁行级锁（默认）
事务支持不支持支持事务（ACID）
外键支持不支持支持外键
崩溃恢复不支持支持崩溃恢复
MVCC不支持支持MVCC
索引结构索引和数据分开数据文件就是索引文件（聚簇索引）
性能适合读多写少的场景适合读写混合的高并发场景
数据缓存键缓存，仅缓存索引缓冲池，缓存数据和索引
表空间每个表一个文件支持共享表空间和独立表空间

总的来说，InnoDB 是现代 MySQL 应用的首选存储引擎，适用于大部分需要事务支持和高并发读写的场景。而 MyISAM 由于不支持事务和外键等功能，适合那些对数据一致性要求不高且以读取为主的应用。

MyISAM 和 InnoDB 如何选择？

在选择 MyISAM 和 InnoDB 存储引擎时，我们需要根据具体的应用场景和需求来决定。虽然 InnoDB 是现代应用的默认选择，但某些情况下 MyISAM 也可以适用。下面是一些关键点，帮助你做出选择：

1. 数据一致性和事务支持

  • InnoDB: 如果你的应用需要确保数据一致性、完整性，并且要支持 事务（包括 COMMIT 和 ROLLBACK），InnoDB 是必选的。它支持 ACID 特性，适合需要事务的系统，如电商、银行、财务系统等。
  • MyISAM: 不支持事务。如果你的应用不需要事务，也没有高要求的数据一致性（比如缓存、日志记录等），MyISAM 可能会合适，但这通常是不推荐的，尤其是在需要持久性和恢复能力的应用场景中。

2. 并发性能

  • InnoDB: 支持 行级锁 和 MVCC（多版本并发控制），能够有效地处理高并发写操作。它的行级锁机制使得多个事务可以同时对不同的数据行进行操作而不互相干扰，适合并发写入操作较多的场景。
  • MyISAM: 只支持 表级锁，意味着每次写操作都会锁住整张表，这会极大影响并发性能，尤其是在多个并发写操作的情况下。

3. 数据恢复

  • InnoDB: 支持 崩溃恢复，即使在数据库崩溃后，它也能够通过 redo log 恢复数据。对于重要的数据系统，这是一个非常重要的特性。
  • MyISAM: 不支持崩溃恢复。MyISAM 数据表可能在数据库崩溃后损坏，数据恢复困难。

4. 外键和数据完整性

  • InnoDB: 支持 外键，能够确保不同表之间的引用完整性。对于复杂的数据库结构，使用外键可以确保数据之间的一致性。
  • MyISAM: 不支持外键，因此如果需要确保数据完整性和关系的约束，InnoDB 是唯一的选择。

5. 读写密集型 vs 只读型

  • InnoDB: 适合 读写混合型 或者 写密集型 应用，尤其是在有多个用户或线程同时进行读写操作时。它的性能随着硬件（尤其是 CPU 核数）的增加而提升。
  • MyISAM: 更适合 只读型 或 读密集型 应用。在这种情况下，MyISAM 的性能可能略优于 InnoDB，尤其是查询操作较多且没有频繁写入的场景。

6. 性能

  • InnoDB: 在大多数现代应用中，InnoDB 的性能通常优于 MyISAM，特别是在需要 事务支持、行级锁 和高并发操作时。
  • MyISAM: 在某些 只读操作 或 查询密集型 应用中，MyISAM 可以提供稍好的性能，但随着应用规模扩大，InnoDB 通常会在读写混合的场景下表现得更好。

7. 空间管理

  • InnoDB: 默认使用 共享表空间，也支持每个表使用独立的表空间。表的存储结构更加灵活，适应各种业务需求。
  • MyISAM: 每个表使用独立的文件，管理上相对简单，但对于大型系统，管理多个表的文件会增加复杂度。
总结建议
  • 对于大多数现代的业务系统，尤其是那些需要 事务、高并发 和 数据一致性 的场景，选择 InnoDB 是最好的选择。
  • MyISAM 适合 读密集型、简单查询 和 不需要事务支持 的场景，例如日志记录、临时数据存储等。但即便如此，很多情况下，使用 InnoDB 会更可靠，尤其在应用复杂性增大时。

总体来说，除非有特殊需求，否则建议默认使用 InnoDB，它具备更强的事务支持、更高的并发性能和更好的数据恢复能力。

MySQL 索引

MySQL 索引相关的问题比较多，对于面试和工作都比较重要，于是，我单独抽了一篇文章专门来总结 MySQL 索引相关的知识点和问题：MySQL 索引详解 。

MySQL 查询缓存

MySQL 查询缓存

MySQL 查询缓存是一种用于提高查询性能的机制。它会将查询结果缓存起来，当后续有相同查询请求时，如果缓存未过期，直接返回缓存中的结果，避免重复的查询操作，从而减少数据库的负载和提高响应速度。

开启查询缓存

你可以通过以下步骤开启 MySQL 查询缓存：

  1. 在 my.cnf 配置文件中设置：
query_cache_type=1
query_cache_size=600000
  1. 在 MySQL 中动态配置查询缓存：
set global query_cache_type=1;
set global query_cache_size=600000;

参数解释：

  • query_cache_type=1：开启查询缓存。
  • query_cache_size=600000：设置查询缓存的大小。单位为字节，600000 字节大约为 600KB。

查询缓存的工作原理

查询缓存根据查询语句和数据的变化来判断是否命中缓存。只有当查询语句、数据和相关条件完全一致时，缓存才会命中。

  1. 查询缓存的命中条件：
    • 查询语句相同。
    • 查询的数据库相同。
    • 查询中的其他因素（例如客户端协议版本号）也相同。
  2. 查询缓存不命中的情况：
    • 查询语句或查询条件发生变化。
    • 查询中包含用户自定义函数、存储过程、用户变量、临时表，或者查询 MySQL 系统表。
    • 相关表的数据发生变化（如插入、更新、删除操作）。

查询缓存的影响

查询缓存可以提高读密集型应用的性能，但也有一些需要注意的地方：

  1. 额外开销：每次查询都会检查缓存状态，缓存失效后需要重新计算，这会带来一定的性能开销，尤其在频繁写入数据的应用中。
  2. 缓存失效：在涉及数据变化时（如增删改操作），缓存会失效，导致缓存需要重新生成，这对写密集型应用来说可能带来性能瓶颈。
  3. 大小控制：查询缓存的大小需要合理配置，一般几十 MB 是比较适合的大小。如果缓存过大，可能会占用过多内存；如果缓存过小，可能无法带来明显的性能提升。

控制查询缓存

在某些情况下，你可能不希望某个查询结果被缓存。你可以使用 sql_no_cache 来显式禁用缓存，例如：

SELECT sql_no_cache COUNT(*) FROM usr;

这样，查询结果就不会被缓存，即使查询条件相同，也不会使用缓存中的结果。

MySQL 8.0 及以后版本

  • MySQL 5.6 开始，查询缓存默认被禁用。
  • MySQL 8.0 及以后版本，查询缓存已彻底移除，不再支持查询缓存。

对于现代版本的 MySQL，建议更多地使用 应用层缓存 或 代理缓存（如 Redis）来替代 MySQL 查询缓存。

结论
  • 对于写密集型应用：尽量避免启用查询缓存，因为缓存的维护开销较大。
  • 对于读密集型应用：可以考虑启用查询缓存，但要注意合理配置缓存大小。
  • MySQL 8.0 及以后版本：已经不支持查询缓存，因此需要依赖其他缓存方案，如 Redis、Memcached 等。

MySQL 日志

MySQL 中常见的日志

MySQL 提供了多种日志来帮助管理员监控数据库的运行状态、查询性能和故障排除。以下是 MySQL 中常见的日志类型：

1. 错误日志（Error Log）

作用：记录 MySQL 启动、停止、运行过程中出现的错误信息，以及一些警告、系统状态等重要信息。

  • 日志内容：

    • MySQL 启动、停止时的状态信息。
    • 错误、警告、重大事件信息。
    • 如某些系统配置错误或运行时遇到的故障。

  • 默认路径：一般为 /var/log/mysql/error.log 或 /var/log/mysqld.log。

  • 配置：

    [mysqld]
log_error = /var/log/mysql/error.log

2. 查询日志（General Query Log）

作用：记录所有的 SQL 查询（包括成功和失败的查询）。它对调试和审计有帮助，但会影响性能，通常在生产环境中不建议开启。

  • 日志内容：

    • 所有执行的 SQL 语句，包括查询、更新、删除等操作。

  • 默认路径：未开启时不会记录。

  • 开启方法：

    [mysqld]
general_log = 1
general_log_file = /var/log/mysql/general.log

  • 注意：开启该日志会对性能产生一定影响，尤其在高并发情况下。

3. 慢查询日志（Slow Query Log）

作用：记录执行时间超过一定阈值的 SQL 查询，主要用于分析数据库性能瓶颈，找出慢查询。

  • 日志内容：

    • 执行时间超过阈值的 SQL 查询。
    • 记录查询的执行时间和相关的执行计划。

  • 默认路径：默认未启用。

  • 开启方法：

    [mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow_query.log
long_query_time = 2  # 记录执行时间超过 2 秒的查询

  • 注意：开启慢查询日志可以帮助优化查询性能，但也可能对性能有一定影响。

4. 二进制日志（Binary Log）

作用：记录所有的更改数据库的操作（如插入、更新、删除等），二进制日志对数据库的复制和恢复至关重要。

  • 日志内容：

    • 记录所有更新数据库的操作。
    • 用于主从复制的同步机制。
    • 用于数据库恢复的增量备份。

  • 默认路径：一般为 /var/lib/mysql/mysql-bin.000001 等文件。

  • 开启方法：

    [mysqld]
log_bin = /var/log/mysql/mysql-bin.log

  • 注意：开启二进制日志会增加磁盘空间的消耗，尤其是在大规模操作下。也需要定期清理旧的二进制日志文件。

5. 中继日志（Relay Log）

作用：中继日志是 MySQL 主从复制中的从库使用的日志。主库的二进制日志被从库读取并写入中继日志，然后从库根据中继日志执行更新操作。

  • 日志内容：

    • 记录从主库接收到的更新操作。

  • 默认路径：一般为 /var/lib/mysql/mysql-relay-bin.000001 等文件。

  • 开启方法：由 MySQL 复制机制自动开启，无需手动配置。

6. 事务日志（InnoDB Log）

作用：InnoDB 存储引擎通过事务日志（ib_logfile0 和 ib_logfile1）来保证数据的一致性，支持事务回滚和崩溃恢复。

  • 日志内容：

    • 记录对 InnoDB 表的所有更改操作，包括插入、更新、删除等。

  • 默认路径：一般为 /var/lib/mysql/ib_logfile0 和 /var/lib/mysql/ib_logfile1。

  • 配置方法：

    [mysqld]
innodb_log_file_size = 128M
innodb_log_files_in_group = 2

7. 复制日志（Replication Log）

作用：复制日志用于数据库的主从复制配置。主库会将所有更新操作记录在二进制日志中，从库会读取这些操作并在自己的数据库中执行。

  • 日志内容：

    • 记录主库的所有更新操作。

  • 默认路径：使用二进制日志（Binary Log）来实现。

8. 审计日志（Audit Log）

作用：审计日志用于记录用户的登录、操作等审计信息，通常需要安装 MySQL 企业版或第三方插件（如 Percona 或 MariaDB 提供的插件）。

  • 日志内容：
    • 用户登录、查询、更新、删除等操作的详细信息。
  • 开启方法：
    • 需要安装审计插件，如 MySQL Enterprise Audit 插件或 Percona Audit 插件。
总结

MySQL 提供的日志功能非常强大，帮助开发者和运维人员诊断问题、优化性能并进行数据恢复。常见的日志类型包括错误日志、查询日志、慢查询日志、二进制日志等。在生产环境中，要根据实际需求合理选择和开启日志，以避免不必要的性能开销。

慢查询日志的作用

慢查询日志是 MySQL 中用于记录执行时间超过一定阈值的查询语句的日志。其主要作用是帮助开发人员和数据库管理员分析和优化数据库性能，尤其是查询性能。慢查询日志可以提供关于哪些查询操作执行缓慢、执行时间较长的详细信息。

1. 识别性能瓶颈

慢查询日志可以帮助你识别数据库中哪些查询语句执行得比较慢，进而找到可能存在的性能瓶颈。如果有查询语句的执行时间长于设定的阈值，这些查询会被记录下来，方便后续的性能分析和优化。

2. 优化查询语句

通过分析慢查询日志中的信息，可以发现哪些查询语句可能没有使用索引、哪些查询语句的执行计划不够高效。对这些慢查询进行优化（例如，添加索引、调整查询语句、优化数据模型等）可以有效提升系统的整体性能。

3. 监控数据库负载

慢查询日志记录了执行时间较长的查询，能够帮助你了解哪些查询操作对数据库负载的影响较大。这有助于在高并发场景下，减少对数据库性能的影响，尤其是减少阻塞和延迟。

4. 调试和故障排查

在系统遇到性能问题时，慢查询日志提供了有效的调试和故障排查线索。可以通过查看慢查询日志，确认是否存在某些特定的查询导致数据库的性能下降。

5. 数据库容量规划

通过分析慢查询日志，可以判断是否有查询因为数据量过大而变得慢，这能够帮助你进行容量规划。例如，如果某些查询在数据量增大后变得很慢，可以提前采取措施（如分表、分区等）来避免性能问题。

6. 避免重复查询和不必要的查询

慢查询日志可以帮助开发者发现一些重复的查询，或者那些可以被优化的查询操作。例如，某些查询可能可以通过缓存、批量处理等方法优化，以减少数据库的压力。

慢查询日志的配置

  1. 开启慢查询日志：
    可以通过以下配置启用慢查询日志：

    [mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow_query.log
long_query_time = 2  # 记录执行时间超过 2 秒的查询

    这里 long_query_time 用于设置慢查询的阈值（单位：秒），即记录执行时间超过这个值的查询。

  2. 检查慢查询日志是否启用：
    使用以下命令检查是否启用了慢查询日志：

    SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'long_query_time';

  3. 查看慢查询日志：
    慢查询日志文件会记录下执行时间较长的查询。查看日志时，通常会看到以下信息：

    • 查询执行的时间。
    • 查询的 SQL 语句。
    • 查询执行的锁定时间（如果有）。
    • 查询是否使用了索引等信息。

    例如，日志文件中的内容可能是这样的：

    # Time: 2025-02-07T10:53:04.789233Z
# User@Host: root[root] @ localhost []  Id: 12345
# Query_time: 5.123456  Lock_time: 0.000123 Rows_sent: 100 Rows_examined: 5000
SET timestamp=1633983364;
SELECT * FROM large_table WHERE some_column = 'value';
总结

慢查询日志是 MySQL 中一个非常有用的工具，它帮助数据库管理员和开发者监控和优化查询性能。通过启用慢查询日志，可以找出那些执行时间过长的查询语句，进而对它们进行优化，从而提升数据库的响应速度和整体性能。在高负载、高并发的环境中，合理配置和使用慢查询日志至关重要。

binlog 主要记录了什么？

binlog（binary log）是 MySQL 中的一种日志文件，主要用于记录数据库的所有更改操作。它的作用包括数据恢复、主从复制以及数据同步等。具体来说，binlog 记录以下内容：

1. 数据库的增、删、改操作

  • 主要记录对数据库表结构或数据的所有修改，如 INSERT、UPDATE、DELETE 操作。
  • 每次执行这些操作时，binlog 会记录下更改的具体信息，比如哪些表被修改、具体的修改内容（如插入了什么数据、更新了哪些字段等）。

2. DDL 操作

  • 例如 CREATE, ALTER, DROP 等数据库结构修改操作也会被记录在 binlog 中。
  • 这些操作影响的是数据库结构，而非数据内容，但 MySQL 依然会在 binlog 中记录，以便在主从复制时保持一致性。

3. 事务信息

  • 如果开启了事务（比如使用了 BEGIN, COMMIT, ROLLBACK），binlog 会记录事务的开始、提交和回滚事件。
  • 在复制时，确保所有的从库和主库保持数据一致性。

4. 时间戳

  • binlog 会包含每个事件的时间戳，帮助后续的事件重放、审计或恢复操作。

5. 事件类型

  • 每条日志记录会包括事件类型（如 INSERT, UPDATE, DELETE 等），并且会以二进制格式存储操作的数据内容，以便更高效地存储和传输。

6. 行级或语句级的记录（取决于 binlog_format）

  • MySQL 支持三种 binlog_format：STATEMENT（语句格式）、ROW（行格式）和 MIXED（混合格式）。
    • STATEMENT 格式：记录 SQL 语句（如 INSERT INTO table VALUES ...）。
    • ROW 格式：记录行级的变化，具体到每一行数据。
    • MIXED 格式：在 STATEMENT 和 ROW 之间切换，根据具体的操作选择最合适的记录方式。
总结

binlog 主要记录了数据库中的数据变更操作（如增、删、改、DDL 语句），是 MySQL 实现数据复制、恢复以及审计的重要工具。

redo log 如何保证事务的持久性？

在 MySQL 中，redo log（重做日志）是用于保证事务持久性的重要机制。持久性（Durability）是事务的四大特性之一，意味着一旦事务提交，它对数据库的修改应该是永久性的，即使发生系统崩溃，修改的数据也不能丢失。redo log 在保证这一点方面发挥了关键作用。

1. 事务提交与日志写入的顺序

在 MySQL 中，事务的持久性是通过日志先写磁盘、再提交的方式来保证的。具体步骤如下：

  • 当事务执行 COMMIT 时，MySQL 不会直接将数据写入磁盘，而是先将事务的修改记录到 redo log 中。
  • 只有当 redo log 写入完成后，系统才会返回事务提交成功的确认。这意味着即使在事务提交后发生了崩溃，只要 redo log 已经持久化到磁盘，就可以确保事务的修改不会丢失。

2. 日志的持久化和磁盘

  • redo log 存储在硬盘上，并且是顺序写入的。写入日志的操作比直接修改数据库数据文件要高效得多。
  • redo log 的作用是记录事务对数据页的修改，它并不直接存储数据，而是记录修改的操作和数据的物理位置。即便数据库崩溃，redo log 仍能帮助系统重做（redo）这些修改，确保数据的持久性。

3. 两阶段提交（2PC）与 redo log

  • 在 MySQL 的 InnoDB 存储引擎中，事务提交过程遵循两阶段提交协议（2PC）。这两个阶段分别是：
    1. 准备阶段：事务将修改的内容写入 redo log，但还没有更新数据库数据文件。此时事务已提交，但数据还未真正持久化。
    2. 提交阶段：只有在 redo log 写入磁盘之后，系统才会通知事务提交成功。
  • 通过这种方式，即使在数据库崩溃后，redo log 仍然存在，可以在恢复过程中重做事务，从而确保事务的持久性。

4. 日志缓冲和刷盘机制

  • redo log 是通过 log buffer 来缓存的，log buffer 是存储在内存中的。当事务提交时，log buffer 会将日志写入 redo log 文件（由 flush 操作触发）。
  • 通过 innodb_flush_log_at_trx_commit 配置参数控制何时将 redo log 刷写到磁盘：
    • 1：每次事务提交时都会将 redo log 写入磁盘，保证事务的持久性。
    • 2：每次事务提交时只将日志写入到操作系统的缓存中，而在每秒钟时会刷盘一次。
    • 0：每次事务提交时不立即将日志写入磁盘，而是由操作系统自行管理。

5. 崩溃恢复

  • 当数据库发生崩溃时，MySQL 会通过 redo log 来恢复未提交的事务。具体做法是读取 redo log 中的已提交事务，并根据记录的操作重做这些事务的修改。
  • 恢复过程是顺序进行的，因此即使崩溃发生在事务提交后，数据库也可以保证修改会被完整应用，从而保持事务的持久性。
总结

redo log 通过记录事务对数据的物理修改，确保在系统崩溃或重启时能够恢复这些修改，保证事务提交后的数据不会丢失。它与 commit 操作的顺序保证了事务的持久性，避免了数据的丢失，即使发生崩溃也能从日志中恢复数据。

为什么页修改之后不直接刷盘？

在数据库中，特别是 MySQL 的 InnoDB 存储引擎，修改的数据页（如表数据）并不是每次都立即刷盘的，原因主要与性能、IO 操作的优化以及事务日志（如 redo log）的管理策略相关。下面详细解释为何 InnoDB 选择不在每次修改后立即刷盘。

1. 减少磁盘 I/O 操作，提升性能

  • 磁盘 I/O 是数据库操作中最为耗时的部分。每次修改数据时，如果都立即将修改后的数据页写入磁盘，会导致大量的磁盘操作，严重影响性能。
  • 通过延迟将数据页刷盘，InnoDB 能够减少不必要的磁盘写入，提升系统的响应速度。InnoDB 会将修改后的数据页保存在内存中的缓存池（buffer pool）中，并定期将修改过的数据页刷入磁盘。
  • 写操作通常是顺序写入，而磁盘上的随机写操作会大大增加 I/O 时间。因此，数据库通过批量、延迟地写入数据页来优化性能。

2. 日志机制：先写日志，再写数据页

  • 事务的持久性是通过 redo log 机制来保证的。即使数据页的修改没有立即写入磁盘，只要相关操作被记录在 redo log 中，并且 redo log 已经刷盘，系统就可以在崩溃恢复时通过日志来重做操作，保证数据的持久性。
  • 这意味着，只要事务的修改已经被记录在 redo log 中，数据页可以在内存中保持一段时间，直到被刷入磁盘。
  • redo log 的写入频率通常比数据页刷盘频率要高得多，这减少了对磁盘的写入负担。

3. 写时复制（Write-Ahead Logging，WAL）

  • InnoDB 遵循写前日志（WAL）协议，即修改数据之前，必须先把日志写入 redo log。通过这种方式，即使数据页的修改还没有刷盘，数据库也能够保证事务的持久性。
  • WAL 协议要求修改操作记录先写入日志，而数据页的更新操作在日志写入后才会进行。这使得即使系统崩溃，日志中的操作可以帮助数据库恢复最新的修改。

4. 刷盘策略：延迟刷盘

  • InnoDB 使用了基于时间间隔和内存容量的刷盘策略。系统会定期（而不是每次修改后）将内存中修改过的数据页刷盘，这样可以利用内存缓存来优化性能。
  • 在刷盘时，InnoDB 会按以下方式控制：
    • 后台刷盘线程：InnoDB 有一个后台线程负责定期将内存中修改过的数据页写入磁盘，这种做法提高了系统的吞吐量。
    • 页面刷新阈值：系统会监控内存中脏页（即修改过的未刷盘的数据页）的数量，并在达到一定阈值时触发刷盘。

5. 事务和持久性保证

  • 数据库中的事务模型保证了持久性，即使数据页没有立即写入磁盘，只要事务已经提交，相关修改会被记录在 redo log 中，并且可以通过日志恢复。InnoDB 的持久性依赖于日志系统的可靠性，而不是每次写操作后都立即刷盘。
  • 只有在特定情况下，比如数据库关闭、崩溃恢复或者后台刷盘线程达到某个阈值时，修改的数据页才会被刷入磁盘。

6. 减少磁盘碎片

  • 如果每次修改数据后都立即写入磁盘，会导致大量的小数据写入操作，从而产生磁盘碎片。延迟写入可以让多个修改合并成一个较大的写入操作，从而降低磁盘碎片的产生，提升磁盘的性能。
总结

InnoDB 采用延迟刷盘策略的原因主要是为了提高性能，减少磁盘 I/O 操作，并通过日志（如 redo log）保证事务的持久性。通过先记录日志再修改数据页的方式，确保了即使数据页没有立刻刷盘，事务的修改也不会丢失。而后台定期刷盘和减少不必要的写入操作，有助于提高系统的吞吐量和响应速度。

binlog 和 redolog 的区别

binlog 和 redolog 都是 MySQL 中的日志文件，主要用于数据恢复、复制等目的，但它们在用途、内容、工作方式等方面有很大的区别。以下是它们的主要区别：

1. 日志的类型和目的

  • binlog（Binary Log）：主要用于记录数据库的语句级或者行级操作。它的主要用途是：

    • 数据复制：用于 MySQL 主从复制，记录主库的所有数据修改操作，然后通过复制将这些操作应用到从库。
    • 数据恢复：在数据库崩溃后，通过 binlog 重放操作来恢复丢失的操作。
    • 审计和备份：可以用来追踪数据库操作，做审计日志。

  • redolog（Redo Log）：主要用于事务的持久性和崩溃恢复，确保数据修改不会因为崩溃而丢失。它的主要用途是：

    • 事务的持久性：记录事务修改的数据页，为事务提交提供持久化保证。即使系统崩溃，只要 redolog 已经写入磁盘，事务也不会丢失。
    • 崩溃恢复：在数据库崩溃或重启后，redolog 可以帮助 MySQL 恢复未刷盘的修改，保证数据的完整性。

2. 记录内容的不同

  • binlog：

    • 记录了 SQL 语句或者数据行的变更操作，内容通常是逻辑层面的，具体描述了对数据库执行的操作（如 INSERT、UPDATE、DELETE 等）。
    • 根据 binlog_format 的不同，可以分为：
      • STATEMENT 格式：记录 SQL 语句。
      • ROW 格式：记录数据行的变化（更详细）。
      • MIXED 格式：结合使用 STATEMENT 和 ROW。

  • redolog：

    • 记录的是事务对数据页的物理修改（如修改的具体页面和数据内容），并以二进制格式保存。它不关心 SQL 语句，而是记录底层数据的物理变更。
    • redolog 是通过记录 "redo"（重做）信息来支持数据库的崩溃恢复，确保所有已经提交的事务修改能够被恢复。

3. 刷盘的时机

  • binlog：

    • binlog 通常是在事务提交后进行刷盘的。这意味着，数据库操作会在事务提交后将相应的变更写入 binlog，并将 binlog 刷盘。
    • 由于 binlog 是用于复制和备份的，所以它必须被同步写入磁盘，以确保数据一致性。

  • redolog：

    • redolog 的写入是为了确保事务的持久性，事务的每个操作都会先被记录到 redolog 中，然后才会被写入数据页。在事务提交时，redolog 必须先写入磁盘，才能确认事务的提交，保证即使发生崩溃，事务的修改不会丢失。

4. 工作机制

  • binlog：

    • binlog 是基于事件的日志，记录的是数据库修改操作的逻辑语句或数据变化，支持语句级别的复制。
    • 在主从复制中，主库会将操作记录到 binlog 中，然后从库通过读取 binlog 来执行相同的操作。
    • 由于是逻辑记录，它支持数据恢复时可以更精确地控制恢复的时间点和操作。

  • redolog：

    • redolog 是基于物理日志的，它记录的是对数据页的物理操作，支持事务的恢复。
    • redolog 确保在事务提交后，数据页的更改会被持久化，即使系统崩溃，可以通过重做日志恢复操作。
    • 它不是为了复制而设计的，而是为事务的持久性和崩溃恢复提供保障。

5. 日志的使用场景

  • binlog：

    • 主从复制：通过复制 binlog，从库可以实时地同步主库的变更。
    • 数据备份：binlog 可以用于增量备份，记录所有的变更操作。
    • 审计：binlog 可以追踪所有的修改操作，作为审计的依据。

  • redolog：

    • 事务持久性：确保即使系统崩溃，已经提交的事务也能够恢复。
    • 崩溃恢复：在数据库重启或崩溃后，redolog 可以帮助恢复那些已经提交但未刷盘的数据变更。

6. 存储位置

  • binlog：binlog 文件通常存储在 MySQL 数据目录中，文件名以 mysql-bin 开头。
  • redolog：redolog 文件存储在 MySQL 数据目录中的 ib_logfile 文件中。
总结
特性binlogredolog
主要用途数据复制、备份、审计事务持久性、崩溃恢复
记录内容逻辑操作（如 INSERT, UPDATE 等）物理数据页修改
写入时机事务提交后，按需刷盘事务提交前，必须先写入磁盘
复制支持支持（主从复制）不用于复制
数据恢复可以通过重放 binlog 恢复数据通过重做 redolog 恢复崩溃前的操作
存储位置存储在 mysql-bin 文件中存储在 ib_logfile 文件中

简而言之，binlog 是逻辑层面的日志，主要用于数据复制和备份，而 redolog 是物理层面的日志，主要用于保证事务的持久性和数据库崩溃恢复。两者在工作机制和应用场景上有着显著的区别。

undo log 如何保证事务的原子性？

undo log 是 MySQL 中用于支持事务原子性的关键日志机制之一。原子性是事务的四大特性之一，它确保事务中的操作要么全部执行成功，要么全部回滚，不会出现部分成功、部分失败的情况。undo log 通过记录事务修改前的数据状态，保证了事务的原子性。

1. 原子性的定义

原子性（Atomicity）要求一个事务中的所有操作要么全部成功，要么全部失败。即使在事务执行过程中发生错误或系统崩溃，系统也能确保事务的修改被完全回滚，恢复到事务开始之前的状态。

2. undo log 的作用

  • undo log 记录了事务对数据进行修改前的原始状态，即记录了操作前的数据。这些信息用于在事务失败或需要回滚时恢复数据。
  • 当事务执行某个操作（如 INSERT、UPDATE、DELETE）时，undo log 会记录该操作执行之前的数据快照，确保在事务回滚时可以恢复这些修改。

3. 事务回滚的过程

  • 执行修改前写入 undo log：在事务执行某个修改操作时，InnoDB 会将修改前的数据（即旧值）保存到 undo log 中。
  • 事务失败或回滚时恢复原始数据：如果事务执行过程中出现错误，或者用户显式地执行了 ROLLBACK 操作，undo log 中记录的旧数据将会被用来恢复被修改的数据页。通过回滚操作，数据库会“撤销”事务中的修改，恢复到事务开始前的状态。

例如，对于 UPDATE 操作，undo log 会保存修改前的数据值。如果该事务回滚，undo log 会使用这些原始数据恢复表中的数据，确保操作不会被部分提交。

4. 事务的操作与 undo log

  • INSERT 操作：在执行 INSERT 操作时，undo log 会记录插入前的数据状态。回滚时，插入的数据会被删除，恢复到事务之前的状态。
  • UPDATE 操作：在执行 UPDATE 操作时，undo log 会记录更新前的旧值。如果事务回滚，undo log 会把数据恢复到原来的状态。
  • DELETE 操作：在执行 DELETE 操作时，undo log 会记录删除前的行数据。回滚时，数据会从 undo log 中恢复回来。

5. 保证原子性的工作机制

  • 操作前写日志：每当事务修改数据时，InnoDB 会先将修改前的数据写入 undo log。这样，如果事务提交前发生崩溃或回滚，undo log 中的记录可以帮助系统恢复到事务开始前的状态。
  • 事务回滚：当事务回滚时，undo log 中的记录会被用来撤销事务中执行的所有操作。系统通过读取 undo log 来恢复数据，确保数据库状态恢复到事务开始前的状态。
  • 保证数据一致性：undo log 确保了数据的一致性和完整性，即使在系统崩溃或用户显式回滚的情况下，数据库的状态也不会部分更新，确保事务的原子性。

6. 例子：事务回滚过程

假设有一个事务，包含如下操作：

  1. UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  2. UPDATE accounts SET balance = balance + 100 WHERE id = 2;
  • 操作 1：在执行 UPDATE accounts SET balance = balance - 100 WHERE id = 1; 时，undo log 会记录该行的旧值（例如，balance = 500），以便如果后续回滚操作时能恢复到该值。
  • 操作 2：在执行 UPDATE accounts SET balance = balance + 100 WHERE id = 2; 时，undo log 会记录该行的旧值（例如，balance = 300）。
  • 如果事务提交之前发生错误或系统崩溃，回滚时：
    • undo log 会读取并恢复 id = 1 的账户的余额到原始值（500）。
    • undo log 会读取并恢复 id = 2 的账户的余额到原始值（300）。

通过这个过程，undo log 确保了所有修改要么完全执行，要么完全撤销，从而保证事务的原子性。

总结

undo log 通过记录事务执行前的数据快照，确保了在事务执行失败或回滚时，能够恢复原始数据，避免部分操作被提交。它通过对每个操作记录撤销信息，保证了事务的原子性，即确保事务的操作要么全部生效，要么完全不生效。

MySQL 事务

何为事务？

事务（Transaction）是一个逻辑上的操作单元，它包含了一系列的操作，这些操作要么全部成功，要么全部失败。事务的核心目的就是保证数据的一致性和完整性，确保在面对异常情况时，不会出现部分成功、部分失败的情形。

事务的四大特性

为了确保事务的正确性，数据库事务遵循 ACID 原则，这四个特性分别是：

  1. 原子性（Atomicity）：

    • 事务中的操作要么全部成功，要么全部失败。如果事务中的任何一部分操作失败，则所有的操作都会回滚，恢复到事务开始前的状态。
    • 例如，在转账操作中，如果从小明账户中扣款成功，但向小红账户存款失败，那么整个事务都会回滚，保证数据的一致性。

  2. 一致性（Consistency）：

    • 事务必须使数据库从一个一致的状态变到另一个一致的状态。无论事务成功或失败，数据库的状态必须是有效的。
    • 例如，在上述转账例子中，数据库的余额总数应该始终保持一致。事务开始时，数据库总余额是 X，事务结束时，它应该还是 X，尽管是不同账户之间的余额发生了变化。

  3. 隔离性（Isolation）：

    • 事务的执行不会被其他事务所干扰。即使多个事务并发执行，每个事务的操作都应该是独立的，其他事务无法看到当前事务未提交的中间结果。
    • 比如，如果两个用户同时在转账，一个用户扣款、另一个用户存款，他们的操作不应该互相影响，最终每个事务的结果都应该是独立的。

  4. 持久性（Durability）：

    • 一旦事务提交，事务对数据库的修改就是永久性的，即使系统崩溃也不会丢失。
    • 如果转账操作成功提交，即使数据库崩溃，事务修改的数据依然会被恢复到持久存储中。

事务中的常见问题

在实际操作中，事务的执行可能会面临一些挑战，这也是事务机制应运而生的原因。常见问题包括：

  • 数据库崩溃：系统崩溃可能导致部分事务的修改丢失，导致数据不一致。
  • 网络中断：客户端与数据库的连接突然断开，导致事务无法完成。
  • 并发冲突：多个客户端或线程并发操作同一数据，可能导致数据的竞争和不一致。

这些问题会影响数据库的正确性和可靠性，而事务则通过保证 原子性、一致性、隔离性 和 持久性 来解决这些问题。

事务的例子：转账

以转账为例，假设小明给小红转账 1000 元。这个操作涉及到两个关键步骤：

  1. 扣款：从小明账户中减去 1000 元。
  2. 存款：向小红账户中增加 1000 元。

事务的关键是确保这两个操作要么都成功，要么都失败。例如，如果系统在扣款成功后发生崩溃，导致存款操作无法执行，那么整个事务应该回滚，确保小明的余额没有减少，小红的余额没有增加，保证系统的一致性。

通过事务管理，数据库可以处理这些操作，确保即使在异常情况下（如崩溃、网络中断等），数据仍然保持一致。

何谓数据库事务？

数据库事务（Database Transaction）是数据库管理系统（DBMS）中管理多个数据库操作的机制，它将多个操作组合成一个原子操作单元，确保这些操作要么都成功，要么都不执行。事务在确保数据一致性、完整性和安全性方面起着至关重要的作用。

在日常开发中，特别是在单体架构项目中，我们接触到的数据库事务大多数就是指数据库事务。事务可以有效地解决并发访问、系统崩溃、数据不一致等问题。

数据库事务的作用

数据库事务可以确保一系列相关的 SQL 操作（如插入、更新、删除等）构成一个逻辑上的整体，并且要么全部执行成功，要么全部回滚。这样可以确保在执行过程中，数据库的状态始终保持一致。

典型的事务操作过程：

# 开始事务
START TRANSACTION;

# 执行多条 SQL 语句
SQL1, SQL2, ...

# 提交事务，保存修改
COMMIT;

如果事务中的任何一条 SQL 语句执行失败，可以使用 ROLLBACK 回滚事务，撤销之前所有操作，恢复到事务开始前的状态。

数据库事务的 ACID 特性

关系型数据库的事务遵循 ACID 特性，确保事务的可靠性和一致性。ACID 是四个英文单词的首字母缩写，分别代表：

  1. 原子性（Atomicity）：

    • 事务是一个不可分割的单位。事务中的所有操作要么全部成功，要么全部失败。如果事务中任何一部分操作失败，则整个事务回滚，所有已执行的操作都会被撤销。
    • 例如，在转账操作中，若从小明账户扣款成功，但向小红账户存款失败，那么整个事务会被回滚，避免出现小明账户扣款了但小红账户未存款的情况。

  2. 一致性（Consistency）：

    • 事务执行前后，数据库必须从一个一致的状态变到另一个一致的状态。无论事务是否成功，数据库的完整性约束和业务规则都必须得到保持。
    • 例如，转账操作前后，转账者和收款人的总余额应该保持一致，即使事务失败，数据的一致性也必须保证。

  3. 隔离性（Isolation）：

    • 并发事务之间彼此隔离，一个事务的执行不应被其他事务的执行干扰。在事务提交之前，其他事务不能看到当前事务的中间状态。
    • 例如，在两个并发事务同时修改同一账户余额时，数据库需要保证事务之间互不干扰，避免出现数据竞争和不一致。

  4. 持久性（Durability）：

    • 一旦事务提交，对数据库的更改是永久性的，即使系统崩溃，也不会丢失已提交的事务数据。事务一旦提交，所有的操作都被永久写入数据库。
    • 例如，一旦转账事务提交，即使系统崩溃，已完成的转账操作的修改仍然会保留，数据库能够恢复到事务提交时的状态。

ACID 与一致性的关系

在传统的 ACID 模型中，通常认为一致性（Consistency）是数据库的一个核心特性，但实际上，一致性并不完全是数据库的责任。一致性往往依赖于应用程序层面的设计，而数据库的作用是提供 原子性、隔离性和持久性 来确保一致性得以实现。

正如《Designing Data-Intensive Application（数据密集型应用系统设计）》一书中所指出的：

"Atomicity, isolation, and durability are properties of the database, whereas consistency (in the ACID sense) is a property of the application."

这意味着，原子性、隔离性和持久性是数据库的责任，而一致性是应用程序根据业务逻辑确保的。数据库确保事务中的操作要么全成功，要么全失败，应用程序则通过这些机制来保证最终的数据一致性。

总结
  • 数据库事务是一种管理数据库操作的机制，可以确保多个数据库操作要么全部成功，要么全部失败，保证数据的一致性和完整性。
  • ACID 特性（原子性、一致性、隔离性、持久性）是事务的核心原则，确保事务在执行过程中不会出现数据不一致、系统崩溃导致数据丢失等问题。
  • 事务不仅仅是数据库功能的一部分，它与应用程序逻辑紧密相关。一致性（Consistency）虽然是 ACID 特性的一部分，但实现一致性往往还需要结合应用层的设计。

如果你对数据库事务有更深入的兴趣，书籍《Designing Data-Intensive Applications》是一本非常推荐的读物，可以帮助你更好地理解分布式系统和数据库设计的核心概念。

并发事务带来了哪些问题?

在典型的应用程序中，多个事务并发运行，经常会操作相同的数据来完成各自的任务（多个用户对同一数据进行操作）。并发虽然是必须的，但可能会导致以下的问题。

脏读（Dirty read）

一个事务读取数据并且对数据进行了修改，这个修改对其他事务来说是可见的，即使当前事务没有提交。这时另外一个事务读取了这个还未提交的数据，但第一个事务突然回滚，导致数据并没有被提交到数据库，那第二个事务读取到的就是脏数据，这也就是脏读的由来。

例如：事务 1 读取某表中的数据 A=20，事务 1 修改 A=A-1，事务 2 读取到 A = 19,事务 1 回滚导致对 A 的修改并未提交到数据库， A 的值还是 20。

脏读

丢失修改（Lost to modify）

在一个事务读取一个数据时，另外一个事务也访问了该数据，那么在第一个事务中修改了这个数据后，第二个事务也修改了这个数据。这样第一个事务内的修改结果就被丢失，因此称为丢失修改。

例如：事务 1 读取某表中的数据 A=20，事务 2 也读取 A=20，事务 1 先修改 A=A-1，事务 2 后来也修改 A=A-1，最终结果 A=19，事务 1 的修改被丢失。

丢失修改

不可重复读（Unrepeatable read）

指在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读。

例如：事务 1 读取某表中的数据 A=20，事务 2 也读取 A=20，事务 1 修改 A=A-1，事务 2 再次读取 A =19，此时读取的结果和第一次读取的结果不同。

不可重复读

幻读（Phantom read）

幻读与不可重复读类似。它发生在一个事务读取了几行数据，接着另一个并发事务插入了一些数据时。在随后的查询中，第一个事务就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。

例如：事务 2 读取某个范围的数据，事务 1 在这个范围插入了新的数据，事务 2 再次读取这个范围的数据发现相比于第一次读取的结果多了新的数据。

幻读

不可重复读和幻读有什么区别？

  • 不可重复读的重点是内容修改或者记录减少比如多次读取一条记录发现其中某些记录的值被修改；
  • 幻读的重点在于记录新增比如多次执行同一条查询语句（DQL）时，发现查到的记录增加了。

幻读其实可以看作是不可重复读的一种特殊情况，单独把幻读区分出来的原因主要是解决幻读和不可重复读的方案不一样。

举个例子：执行 delete 和 update 操作的时候，可以直接对记录加锁，保证事务安全。而执行 insert 操作的时候，由于记录锁（Record Lock）只能锁住已经存在的记录，为了避免插入新记录，需要依赖间隙锁（Gap Lock）。也就是说执行 insert 操作的时候需要依赖 Next-Key Lock（Record Lock+Gap Lock） 进行加锁来保证不出现幻读。

并发事务的控制方式有哪些？

MySQL 中并发事务的控制方式无非就两种：锁 和 MVCC。锁可以看作是悲观控制的模式，多版本并发控制（MVCC，Multiversion concurrency control）可以看作是乐观控制的模式。

锁 控制方式下会通过锁来显式控制共享资源而不是通过调度手段，MySQL 中主要是通过 读写锁 来实现并发控制。

  • 共享锁（S 锁）：又称读锁，事务在读取记录的时候获取共享锁，允许多个事务同时获取（锁兼容）。
  • 排他锁（X 锁）：又称写锁/独占锁，事务在修改记录的时候获取排他锁，不允许多个事务同时获取。如果一个记录已经被加了排他锁，那其他事务不能再对这条记录加任何类型的锁（锁不兼容）。

读写锁可以做到读读并行，但是无法做到写读、写写并行。另外，根据根据锁粒度的不同，又被分为 表级锁(table-level locking) 和 行级锁(row-level locking) 。InnoDB 不光支持表级锁，还支持行级锁，默认为行级锁。行级锁的粒度更小，仅对相关的记录上锁即可（对一行或者多行记录加锁），所以对于并发写入操作来说， InnoDB 的性能更高。不论是表级锁还是行级锁，都存在共享锁（Share Lock，S 锁）和排他锁（Exclusive Lock，X 锁）这两类。

MVCC 是多版本并发控制方法，即对一份数据会存储多个版本，通过事务的可见性来保证事务能看到自己应该看到的版本。通常会有一个全局的版本分配器来为每一行数据设置版本号，版本号是唯一的。

MVCC 在 MySQL 中实现所依赖的手段主要是: 隐藏字段、read view、undo log。

  • undo log : undo log 用于记录某行数据的多个版本的数据。
  • read view 和 隐藏字段 : 用来判断当前版本数据的可见性。

关于 InnoDB 对 MVCC 的具体实现可以看这篇文章：InnoDB 存储引擎对 MVCC 的实现 。

SQL 标准定义了哪些事务隔离级别?

SQL 标准定义了四个隔离级别：

  • READ-UNCOMMITTED(读取未提交) ：最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
  • READ-COMMITTED(读取已提交) ：允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。
  • REPEATABLE-READ(可重复读) ：对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。
  • SERIALIZABLE(可串行化) ：最高的隔离级别，完全服从 ACID 的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。
隔离级别脏读不可重复读幻读
READ-UNCOMMITTED√√√
READ-COMMITTED×√√
REPEATABLE-READ××√
SERIALIZABLE×××

MySQL 的隔离级别是基于锁实现的吗？

MySQL 的隔离级别基于锁和 MVCC 机制共同实现的。

SERIALIZABLE 隔离级别是通过锁来实现的，READ-COMMITTED 和 REPEATABLE-READ 隔离级别是基于 MVCC 实现的。不过， SERIALIZABLE 之外的其他隔离级别可能也需要用到锁机制，就比如 REPEATABLE-READ 在当前读情况下需要使用加锁读来保证不会出现幻读。

MySQL 的默认隔离级别是什么?

MySQL InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ（可重读）。我们可以通过SELECT @@tx_isolation;命令来查看，MySQL 8.0 该命令改为SELECT @@transaction_isolation;

mysql> SELECT @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+

关于 MySQL 事务隔离级别的详细介绍，可以看看我写的这篇文章：MySQL 事务隔离级别详解。

MySQL 锁

锁是一种常见的并发事务的控制方式。

表级锁和行级锁了解吗？有什么区别？

MyISAM 仅仅支持表级锁(table-level locking)，一锁就锁整张表，这在并发写的情况下性非常差。InnoDB 不光支持表级锁(table-level locking)，还支持行级锁(row-level locking)，默认为行级锁。

行级锁的粒度更小，仅对相关的记录上锁即可（对一行或者多行记录加锁），所以对于并发写入操作来说， InnoDB 的性能更高。

表级锁和行级锁对比：

  • 表级锁： MySQL 中锁定粒度最大的一种锁（全局锁除外），是针对非索引字段加的锁，对当前操作的整张表加锁，实现简单，资源消耗也比较少，加锁快，不会出现死锁。不过，触发锁冲突的概率最高，高并发下效率极低。表级锁和存储引擎无关，MyISAM 和 InnoDB 引擎都支持表级锁。
  • 行级锁： MySQL 中锁定粒度最小的一种锁，是 针对索引字段加的锁 ，只针对当前操作的行记录进行加锁。 行级锁能大大减少数据库操作的冲突。其加锁粒度最小，并发度高，但加锁的开销也最大，加锁慢，会出现死锁。行级锁和存储引擎有关，是在存储引擎层面实现的。

行级锁的使用有什么注意事项？

InnoDB 的行锁是针对索引字段加的锁，表级锁是针对非索引字段加的锁。当我们执行 UPDATE、DELETE 语句时，如果 WHERE条件中字段没有命中唯一索引或者索引失效的话，就会导致扫描全表对表中的所有行记录进行加锁。这个在我们日常工作开发中经常会遇到，一定要多多注意！！！

不过，很多时候即使用了索引也有可能会走全表扫描，这是因为 MySQL 优化器的原因。

InnoDB 有哪几类行锁？

InnoDB 行锁是通过对索引数据页上的记录加锁实现的，MySQL InnoDB 支持三种行锁定方式：

  • 记录锁（Record Lock）：属于单个行记录上的锁。
  • 间隙锁（Gap Lock）：锁定一个范围，不包括记录本身。
  • 临键锁（Next-Key Lock）：Record Lock+Gap Lock，锁定一个范围，包含记录本身，主要目的是为了解决幻读问题（MySQL 事务部分提到过）。记录锁只能锁住已经存在的记录，为了避免插入新记录，需要依赖间隙锁。

在 InnoDB 默认的隔离级别 REPEATABLE-READ 下，行锁默认使用的是 Next-Key Lock。但是，如果操作的索引是唯一索引或主键，InnoDB 会对 Next-Key Lock 进行优化，将其降级为 Record Lock，即仅锁住索引本身，而不是范围。

一些大厂面试中可能会问到 Next-Key Lock 的加锁范围，这里推荐一篇文章：MySQL next-key lock 加锁范围是什么？ - 程序员小航 - 2021 。

共享锁和排他锁呢？

不论是表级锁还是行级锁，都存在共享锁（Share Lock，S 锁）和排他锁（Exclusive Lock，X 锁）这两类：

  • 共享锁（S 锁）：又称读锁，事务在读取记录的时候获取共享锁，允许多个事务同时获取（锁兼容）。
  • 排他锁（X 锁）：又称写锁/独占锁，事务在修改记录的时候获取排他锁，不允许多个事务同时获取。如果一个记录已经被加了排他锁，那其他事务不能再对这条事务加任何类型的锁（锁不兼容）。

排他锁与任何的锁都不兼容，共享锁仅和共享锁兼容。

S 锁X 锁
S 锁不冲突冲突
X 锁冲突冲突

由于 MVCC 的存在，对于一般的 SELECT 语句，InnoDB 不会加任何锁。不过， 你可以通过以下语句显式加共享锁或排他锁。

# 共享锁 可以在 MySQL 5.7 和 MySQL 8.0 中使用
SELECT ... LOCK IN SHARE MODE;
# 共享锁 可以在 MySQL 8.0 中使用
SELECT ... FOR SHARE;
# 排他锁
SELECT ... FOR UPDATE;

意向锁有什么作用？

如果需要用到表锁的话，如何判断表中的记录没有行锁呢，一行一行遍历肯定是不行，性能太差。我们需要用到一个叫做意向锁的东东来快速判断是否可以对某个表使用表锁。

意向锁是表级锁，共有两种：

  • 意向共享锁（Intention Shared Lock，IS 锁）：事务有意向对表中的某些记录加共享锁（S 锁），加共享锁前必须先取得该表的 IS 锁。
  • 意向排他锁（Intention Exclusive Lock，IX 锁）：事务有意向对表中的某些记录加排他锁（X 锁），加排他锁之前必须先取得该表的 IX 锁。

意向锁是由数据引擎自己维护的，用户无法手动操作意向锁，在为数据行加共享/排他锁之前，InnoDB 会先获取该数据行所在在数据表的对应意向锁。

意向锁之间是互相兼容的。

IS 锁IX 锁
IS 锁兼容兼容
IX 锁兼容兼容

意向锁和共享锁和排它锁互斥（这里指的是表级别的共享锁和排他锁，意向锁不会与行级的共享锁和排他锁互斥）。

IS 锁IX 锁
S 锁兼容互斥
X 锁互斥互斥

《MySQL 技术内幕 InnoDB 存储引擎》这本书对应的描述应该是笔误了。

当前读和快照读有什么区别？

快照读（一致性非锁定读）就是单纯的 SELECT 语句，但不包括下面这两类 SELECT 语句：

SELECT ... FOR UPDATE
# 共享锁 可以在 MySQL 5.7 和 MySQL 8.0 中使用
SELECT ... LOCK IN SHARE MODE;
# 共享锁 可以在 MySQL 8.0 中使用
SELECT ... FOR SHARE;

快照即记录的历史版本，每行记录可能存在多个历史版本（多版本技术）。

快照读的情况下，如果读取的记录正在执行 UPDATE/DELETE 操作，读取操作不会因此去等待记录上 X 锁的释放，而是会去读取行的一个快照。

只有在事务隔离级别 RC(读取已提交) 和 RR（可重读）下，InnoDB 才会使用一致性非锁定读：

  • 在 RC 级别下，对于快照数据，一致性非锁定读总是读取被锁定行的最新一份快照数据。
  • 在 RR 级别下，对于快照数据，一致性非锁定读总是读取本事务开始时的行数据版本。

快照读比较适合对于数据一致性要求不是特别高且追求极致性能的业务场景。

当前读 （一致性锁定读）就是给行记录加 X 锁或 S 锁。

当前读的一些常见 SQL 语句类型如下：

# 对读的记录加一个X锁
SELECT...FOR UPDATE
# 对读的记录加一个S锁
SELECT...LOCK IN SHARE MODE
# 对读的记录加一个S锁
SELECT...FOR SHARE
# 对修改的记录加一个X锁
INSERT...
UPDATE...
DELETE...

自增锁有了解吗？

不太重要的一个知识点，简单了解即可。

关系型数据库设计表的时候，通常会有一列作为自增主键。InnoDB 中的自增主键会涉及一种比较特殊的表级锁— 自增锁（AUTO-INC Locks） 。

CREATE TABLE `sequence_id` (
  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `stub` CHAR(10) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  UNIQUE KEY `stub` (`stub`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

更准确点来说，不仅仅是自增主键，AUTO_INCREMENT的列都会涉及到自增锁，毕竟非主键也可以设置自增长。

如果一个事务正在插入数据到有自增列的表时，会先获取自增锁，拿不到就可能会被阻塞住。这里的阻塞行为只是自增锁行为的其中一种，可以理解为自增锁就是一个接口，其具体的实现有多种。具体的配置项为 innodb_autoinc_lock_mode （MySQL 5.1.22 引入），可以选择的值如下：

innodb_autoinc_lock_mode介绍
0传统模式
1连续模式（MySQL 8.0 之前默认）
2交错模式(MySQL 8.0 之后默认)

交错模式下，所有的“INSERT-LIKE”语句（所有的插入语句，包括：INSERT、REPLACE、INSERT…SELECT、REPLACE…SELECT、LOAD DATA等）都不使用表级锁，使用的是轻量级互斥锁实现，多条插入语句可以并发执行，速度更快，扩展性也更好。

不过，如果你的 MySQL 数据库有主从同步需求并且 Binlog 存储格式为 Statement 的话，不要将 InnoDB 自增锁模式设置为交叉模式，不然会有数据不一致性问题。这是因为并发情况下插入语句的执行顺序就无法得到保障。

如果 MySQL 采用的格式为 Statement ，那么 MySQL 的主从同步实际上同步的就是一条一条的 SQL 语句。

最后，再推荐一篇文章：为什么 MySQL 的自增主键不单调也不连续 。

MySQL 性能优化

关于 MySQL 性能优化的建议总结，请看这篇文章：MySQL 高性能优化规范建议总结 。

能用 MySQL 直接存储文件（比如图片）吗？

可以是可以，直接存储文件对应的二进制数据即可。不过，还是建议不要在数据库中存储文件，会严重影响数据库性能，消耗过多存储空间。

可以选择使用云服务厂商提供的开箱即用的文件存储服务，成熟稳定，价格也比较低。

也可以选择自建文件存储服务，实现起来也不难，基于 FastDFS、MinIO（推荐） 等开源项目就可以实现分布式文件服务。

数据库只存储文件地址信息，文件由文件存储服务负责存储。

相关阅读：Spring Boot 整合 MinIO 实现分布式文件服务 。

MySQL 如何存储 IP 地址？

可以将 IP 地址转换成整形数据存储，性能更好，占用空间也更小。

MySQL 提供了两个方法来处理 ip 地址

  • INET_ATON()：把 ip 转为无符号整型 (4-8 位)
  • INET_NTOA() :把整型的 ip 转为地址

插入数据前，先用 INET_ATON() 把 ip 地址转为整型，显示数据时，使用 INET_NTOA() 把整型的 ip 地址转为地址显示即可。

常见的 SQL 优化手段

1. 索引优化

  • 创建合适的索引：通过创建合适的索引可以大大提升查询效率。一般来说，选择在常用于 WHERE、JOIN、ORDER BY 和 GROUP BY 的字段上创建索引。
  • 避免过多的索引：索引虽然能提高查询效率，但会影响写入性能，特别是更新、删除操作。所以要根据查询需求合理选择索引。
  • 使用复合索引：对于多列条件查询，使用复合索引比多个单列索引效率更高。

2. 查询优化

  • 避免 SELECT * 查询：尽量避免使用 SELECT *，只查询需要的字段，减少不必要的数据传输。
  • 避免不必要的子查询：有些时候将子查询改为 JOIN 或 IN 查询可以提升性能。
  • 使用 LIMIT：对于只需要部分数据的查询，可以使用 LIMIT 限制返回的记录数，减少计算量。
  • WHERE 子句优化：确保 WHERE 子句中的条件是可索引的，并避免在条件中使用函数，这会导致索引失效。

3. 数据表设计优化

  • 规范化设计：合理的数据库设计可以避免数据冗余，提高查询效率。
  • 分表分库：对于数据量较大的表，可以考虑分表分库，避免单个表数据过多影响查询性能。常用的分表策略有水平分表和垂直分表。
  • 选择合适的数据类型：选择合适的数据类型可以减少存储空间，提高查询效率。

4. 避免锁的争用

  • 减少锁的持有时间：操作数据库时要尽量减少事务的执行时间，避免长时间持有锁，造成其他操作的阻塞。
  • 合理使用事务：尽量缩小事务的范围，避免大事务对性能的影响。

5. 批量操作优化

  • 批量插入或更新：对于大量数据的插入或更新，使用批量操作能减少数据库的开销。
  • 批量删除：在删除大量数据时，分批次删除而不是一次性删除。

6. 查询计划和执行计划分析

  • 使用 EXPLAIN 分析查询：使用 EXPLAIN 或 EXPLAIN ANALYZE 语句分析查询的执行计划，查看是否使用了索引，是否有不合理的全表扫描等，针对性地进行优化。

7. 避免使用不必要的临时表

  • 避免临时表：不必要的临时表会增加 I/O 操作，尽量避免或减少临时表的使用。

8. 避免重复的计算

  • 缓存计算结果：对于频繁执行的计算结果，可以通过缓存或者计算提前保存结果，避免重复计算。

这些优化手段是常见的 SQL 查询优化策略，实际应用时要根据具体的业务场景和数据库的性能瓶颈来选择合适的优化方法。

如何分析 SQL 的性能？

我们可以使用 EXPLAIN 命令来分析 SQL 的 执行计划 。执行计划是指一条 SQL 语句在经过 MySQL 查询优化器的优化会后，具体的执行方式。

EXPLAIN 并不会真的去执行相关的语句，而是通过 查询优化器 对语句进行分析，找出最优的查询方案，并显示对应的信息。

EXPLAIN 适用于 SELECT, DELETE, INSERT, REPLACE, 和 UPDATE语句，我们一般分析 SELECT 查询较多。

我们这里简单来演示一下 EXPLAIN 的使用。

EXPLAIN 的输出格式如下：

mysql> EXPLAIN SELECT `score`,`name` FROM `cus_order` ORDER BY `score` DESC;
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+----------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra          |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+----------------+
|  1 | SIMPLE      | cus_order | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 997572 |   100.00 | Using filesort |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+----------------+
1 row in set, 1 warning (0.00 sec)

各个字段的含义如下：

列名含义
idSELECT 查询的序列标识符
select_typeSELECT 关键字对应的查询类型
table用到的表名
partitions匹配的分区，对于未分区的表，值为 NULL
type表的访问方法
possible_keys可能用到的索引
key实际用到的索引
key_len所选索引的长度
ref当使用索引等值查询时，与索引作比较的列或常量
rows预计要读取的行数
filtered按表条件过滤后，留存的记录数的百分比
Extra附加信息

篇幅问题，我这里只是简单介绍了一下 MySQL 执行计划，详细介绍请看：SQL 的执行计划这篇文章。

读写分离和分库分表了解吗？

读写分离和分库分表相关的问题比较多，于是，我单独写了一篇文章来介绍：读写分离和分库分表详解。

深度分页如何优化？

深度分页介绍及优化建议

数据冷热分离如何做？

数据冷热分离详解

MySQL 性能怎么优化？

MySQL 性能优化是一个系统性工程，涉及多个方面，在面试中不可能面面俱到。因此，建议按照“点-线-面”的思路展开，从核心问题入手，再逐步扩展，展示出你对问题的思考深度和解决能力。

1. 抓住核心：慢 SQL 定位与分析

性能优化的第一步永远是找到瓶颈。面试时，建议先从 慢 SQL 定位和分析 入手，这不仅能展示你解决问题的思路，还能体现你对数据库性能监控的熟练掌握：

  • 监控工具： 介绍常用的慢 SQL 监控工具，如 MySQL 慢查询日志、Performance Schema 等，说明你对这些工具的熟悉程度以及如何通过它们定位问题。
  • EXPLAIN 命令： 详细说明 EXPLAIN 命令的使用，分析查询计划、索引使用情况，可以结合实际案例展示如何解读分析结果，比如执行顺序、索引使用情况、全表扫描等。

2. 由点及面：索引、表结构和 SQL 优化

定位到慢 SQL 后，接下来就要针对具体问题进行优化。 这里可以重点介绍索引、表结构和 SQL 编写规范等方面的优化技巧：

  • 索引优化： 这是 MySQL 性能优化的重点，可以介绍索引的创建原则、覆盖索引、最左前缀匹配原则等。如果能结合你项目的实际应用来说明如何选择合适的索引，会更加分一些。
  • 表结构优化： 优化表结构设计，包括选择合适的字段类型、避免冗余字段、合理使用范式和反范式设计等等。
  • SQL 优化： 避免使用 SELECT *、尽量使用具体字段、使用连接查询代替子查询、合理使用分页查询、批量操作等，都是 SQL 编写过程中需要注意的细节。

3. 进阶方案：架构优化

当面试官对基础优化知识比较满意时，可能会深入探讨一些架构层面的优化方案。以下是一些常见的架构优化策略：

  • 读写分离： 将读操作和写操作分离到不同的数据库实例，提升数据库的并发处理能力。
  • 分库分表： 将数据分散到多个数据库实例或数据表中，降低单表数据量，提升查询效率。但要权衡其带来的复杂性和维护成本，谨慎使用。
  • 数据冷热分离：根据数据的访问频率和业务重要性，将数据分为冷数据和热数据，冷数据一般存储在存储在低成本、低性能的介质中，热数据高性能存储介质中。
  • 缓存机制： 使用 Redis 等缓存中间件，将热点数据缓存到内存中，减轻数据库压力。这个非常常用，提升效果非常明显，性价比极高！

4. 其他优化手段

除了慢 SQL 定位、索引优化和架构优化，还可以提及一些其他优化手段，展示你对 MySQL 性能调优的全面理解：

  • 连接池配置： 配置合理的数据库连接池（如 连接池大小、超时时间 等），能够有效提升数据库连接的效率，避免频繁的连接开销。
  • 硬件配置： 提升硬件性能也是优化的重要手段之一。使用高性能服务器、增加内存、使用 SSD 硬盘等硬件升级，都可以有效提升数据库的整体性能。

5.总结

在面试中，建议按优先级依次介绍慢 SQL 定位、索引优化、表结构设计和 SQL 优化等内容。架构层面的优化，如读写分离和分库分表、数据冷热分离 应作为最后的手段，除非在特定场景下有明显的性能瓶颈，否则不应轻易使用，因其引入的复杂性会带来额外的维护成本。

MySQL 学习资料推荐

MySQL 学习书籍推荐 。

文章推荐 :

上次编辑于: 2025/2/7 22:35:02
贡献者: jiang