MySQL

三范式

数据库的三范式是关系型数据库设计中的一组规范，用于确保数据库中的数据结构合理、有效、减少冗余。这三个范式是：

  1. 第一范式（1NF）： 数据表中的每一列都是不可再分的原子数据项。换句话说，表中的每个字段必须是单一值，而不能是一组值或是可再分的值。确保每列的原子性。

    例如，一个包含学生姓名和其课程的表：

    学生姓名 所修课程
    小明 数学, 物理, 化学

    不符合第一范式，应该拆分为两个表。

  2. 第二范式（2NF）： 在满足第一范式的基础上，表中的非主键列（非主键属性）完全依赖于整个主键。换句话说，每个非主键列都必须完全依赖于表的主键。

    例如，一个包含学生课程成绩的表：

    学生ID 课程 教师 分数
    1 数学 张老师 90
    1 物理 王老师 85

    这个表不符合第二范式，应该拆分成两个表，分别存储学生课程信息和成绩信息。

  3. 第三范式（3NF）： 在满足第二范式的基础上，任何非主键列都不传递依赖于主键。换句话说，表中的每个非主键列都直接依赖于主键，而不是依赖于其他非主键列。

    例如，一个包含学生地址和学生所在城市的表：

    学生ID 学生姓名 学生地址 所在城市
    1 小明 123 Main St Beijing

    这个表不符合第三范式，应该将学生地址和所在城市拆分成两个表，以避免传递依赖。

遵循三范式有助于设计更规范、减少冗余、易于维护的数据库结构。然而，有时为了性能或其他需求，可能会选择违反三范式的原则。在设计数据库时，需要权衡三范式的优势和具体应用场景的需求。

InnoDB和MyISAM

InnoDB和MyISAM是MySQL数据库两种常见的存储引擎，它们在设计、性能特性和适用场景上有一些区别：

🙶The default storage engine for new tables is InnoDB rather than MyISAM.🙷 InnoDB 从 MySQL 5.5 版本开始成为默认的存储引擎。在此之前的版本中，MyISAM 通常是默认的存储引擎。¹

https://dev.mysql.com/doc/refman/9.6/en/myisam-storage-engine.html

MyISAM Storage Engine Features

InnoDB Storage Engine Features

功能 MyISAM InnoDB
B-Tree 索引 支持 支持
备份/时间点恢复（由 MySQL Server 实现，而非存储引擎实现） 支持 支持
集群数据库(Cluster database )支持 不支持 不支持
聚簇索引（Clustered Index） 不支持 支持
数据压缩 支持（仅压缩行格式的 MyISAM 表支持，且为只读） 支持
数据缓存 不支持 支持
数据加密 支持（通过服务器加密函数实现） 支持（通过服务器加密函数实现；MySQL 5.7 及以上支持静态数据加密）
外键支持 不支持 支持
全文索引（FULLTEXT） 支持 支持（MySQL 5.6 及以上版本）
地理空间数据类型支持 支持 支持
地理空间索引支持 支持 支持（MySQL 5.7 及以上版本）
Hash 索引 不支持 不支持（但 InnoDB 内部使用自适应 Hash 索引 Adaptive Hash Index）
索引缓存 支持 支持
锁粒度 表级锁（Table Lock） 行级锁（Row Lock）
MVCC（多版本并发控制） 不支持 支持
主从复制支持（由 MySQL Server 实现，而非存储引擎实现） 支持 支持
存储容量上限 256 TB 64 TB
T-Tree 索引 不支持 不支持
事务（Transaction） 不支持 支持
数据字典统计信息更新 支持 支持

MyISAM vs InnoDB 总结

核心差异

1. 事务与并发控制

2. 锁粒度

3. 索引特性

4. 缓存机制

5. 外键支持

6. 存储限制

共同点

两者都支持:

选择建议

InnoDB 更适合:

MyISAM 更适合:

从表格可以看出,InnoDB 功能更全面,是现代 MySQL 应用的首选引擎。

事务的特征

事务是数据库管理系统中用来管理对数据库的访问和更新的一个操作单元。事务应该具备四个基本特性，通常被称为ACID特性：

  1. 原子性（Atomicity）： 原子性要求事务是一个不可分割的最小工作单元，要么完全执行，要么完全不执行。如果事务的所有操作都成功完成，则事务被认为是原子的；如果任何一个操作失败，则整个事务应该被回滚到初始状态，以确保数据的一致性。

  2. 一致性（Consistency）： 一致性确保事务将数据库从一种一致性状态转移到另一种一致性状态。在事务执行前后，数据库应该保持一致性，即事务的执行不会破坏数据库的完整性约束。如果一个事务执行完毕后，数据库不再保持一致性，系统会回滚事务，使数据恢复到事务开始前的状态。

  3. 隔离性（Isolation）： 隔离性要求一个事务的执行不能被其他事务干扰。即使多个事务并发执行，每个事务都应该认为它在独立地操作数据。这可以通过使用锁或其他并发控制机制来实现，以防止不同事务之间的相互影响。

  4. 持久性（Durability）： 持久性确保一旦事务被提交，其结果就是永久性的，即使系统发生故障也不会丢失。一旦事务成功提交，对数据库的修改应该永久保存在数据库中，以便在系统故障后能够恢复。

这些ACID特性确保了数据库事务的可靠性和一致性。在设计和执行数据库操作时，开发人员和数据库管理员需要特别关注这些特性，以确保数据的正确性和系统的稳定性。

数据库事务隔离级别

InnoDB 提供 SQL:1992 标准描述的四种事务隔离级别:READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。InnoDB 的默认隔离级别是 REPEATABLE READ。

InnoDB 使用不同的锁定策略来支持此处描述的每种事务隔离级别。您可以使用默认的 REPEATABLE READ 级别强制执行高度一致性,这对于 ACID 合规性至关重要的关键数据操作非常适用。或者,您可以在批量报告等情况下放宽一致性规则,使用 READ COMMITTED 甚至 READ UNCOMMITTED,在这些情况下,精确的一致性和可重复结果不如最小化锁定开销重要。SERIALIZABLE 强制执行比 REPEATABLE READ 更严格的规则,主要用于特殊情况,例如与 XA 事务一起使用,以及用于排查并发和死锁问题。

以下列表描述了 MySQL 如何支持不同的事务隔离级别。列表按从最常用到最少用的顺序排列。

数据库事务隔离级别相关的并发问题

与数据库事务隔离级别相关的并发问题主要包括脏读、不可重复读和幻读。这些问题是由于多个事务同时访问和修改数据库时可能发生的情况，而不同的隔离级别会在处理这些问题上有不同的策略。

  1. 脏读（Dirty Read）：

    • 定义： 一个事务读取了另一个事务未提交的数据。
    • 影响隔离级别： 读未提交（Read Uncommitted）隔离级别可能导致脏读。
    • 解决方案： 提高隔离级别至读已提交（Read Committed）及以上，以避免脏读问题。

  2. 不可重复读（Non-repeatable Read）：

    • 定义： 在一个事务中，两次读取同一行数据时得到了不同的结果，因为在两次读取之间有另一个事务修改了该行数据。
    • 影响隔离级别： 读已提交（Read Committed）隔离级别可能导致不可重复读。
    • 解决方案： 提高隔离级别至可重复读（Repeatable Read）及以上，通过锁定读取的数据来防止其他事务的修改。

  3. 幻读（Phantom Read）：

    • 定义： 一个事务在读取了一组数据后，又发现了另一个事务插入了一些新的数据，导致第一次读取和第二次读取得到的数据集不一致。
    • 影响隔离级别： 可重复读（Repeatable Read）隔离级别可能导致幻读。
    • 解决方案： 提高隔离级别至串行化（Serializable），通过更强的锁定机制来防止其他事务的插入或删除操作。

    不同的隔离级别提供了不同的权衡方案，高隔离级别通常可以解决并发问题，但可能会带来性能的降低。在选择隔离级别时，需要根据应用的特定需求和对并发问题的容忍程度做出权衡。

MVCC

InnoDB 是一个多版本存储引擎。它保存已更改行的旧版本信息,以支持并发和回滚等事务特性。这些信息存储在称为回滚段(return segment)的数据结构中,位于 undo 表空间内。参见第 17.6.3.4 节"Undo 表空间"。InnoDB 使用回滚段中的信息来执行事务回滚所需的 undo 操作。它还使用这些信息为一致性读取构建行的早期版本。参见第 17.7.2.3 节"一致性非锁定读"。

在内部,InnoDB 为数据库中存储的每一行添加三个字段:

回滚段中的 undo 日志分为插入 undo 日志和更新 undo 日志。插入 undo 日志仅在事务回滚时需要,一旦事务提交就可以丢弃。更新 undo 日志也用于一致性读取,但只有在不存在这样的事务时才能丢弃:InnoDB 已为其分配了一个快照,该快照在一致性读取中可能需要更新 undo 日志中的信息来构建数据库行的早期版本。有关 undo 日志的更多信息,请参见第 17.6.6 节"Undo 日志"。

建议您定期提交事务,包括仅发出一致性读取的事务。否则,InnoDB 无法从更新 undo 日志中丢弃数据,回滚段可能会变得太大,填满其所在的 undo 表空间。有关管理 undo 表空间的信息,请参见第 17.6.3.4 节"Undo 表空间"。

回滚段中 undo 日志记录的物理大小通常小于相应的插入或更新的行。您可以使用此信息来计算回滚段所需的空间。

在 InnoDB 的多版本方案中,当您使用 SQL 语句删除行时,该行不会立即从数据库中物理删除。InnoDB 仅在丢弃为删除操作编写的更新 undo 日志记录时,才会物理删除相应的行及其索引记录。此删除操作称为清除(purge),速度非常快,通常与执行删除的 SQL 语句花费相同数量级的时间。

MySQL 有哪些锁？

本节讨论内部锁定;即,在 MySQL 服务器内部执行的锁定,用于管理多个会话对表内容的争用。这种类型的锁定是内部的,因为它完全由服务器执行,不涉及其他程序。有关由其他程序对 MySQL 文件执行的锁定,请参见第 10.11.5 节"外部锁定"。

行级锁定

MySQL 对 InnoDB 表使用行级锁定,以支持多个会话同时写入访问,使其适用于多用户、高并发和 OLTP 应用程序。

为避免在单个 InnoDB 表上执行多个并发写操作时出现死锁,请在事务开始时通过为每个预期要修改的行组发出 SELECT ... FOR UPDATE 语句来获取必要的锁,即使数据更改语句在事务中稍后出现。如果事务修改或锁定多个表,请在每个事务中以相同的顺序发出适用的语句。死锁影响性能而不是代表严重错误,因为 InnoDB 默认会自动检测死锁条件并回滚其中一个受影响的事务。

在高并发系统上,当大量线程等待同一锁时,死锁检测会导致速度变慢。有时,禁用死锁检测并依靠 innodb_lock_wait_timeout 设置在发生死锁时进行事务回滚可能更高效。可以使用 innodb_deadlock_detect 配置选项禁用死锁检测。

行级锁定的优势:

表级锁定

MySQL 对 MyISAM、MEMORY 和 MERGE 表使用表级锁定,每次只允许一个会话更新这些表。此锁定级别使这些存储引擎更适合只读、读多写少或单用户应用程序。

这些存储引擎通过在查询开始时一次性请求所有需要的锁并始终以相同的顺序锁定表来避免死锁。这种策略的权衡是降低了并发性;想要修改表的其他会话必须等待当前数据更改语句完成。

表级锁定的优势:

MySQL索引

大多数 MySQL 索引(PRIMARY KEY、UNIQUE、INDEX 和 FULLTEXT)都存储在 B 树中。例外情况:空间数据类型的索引使用 R 树;MEMORY 表还支持哈希索引;InnoDB 对 FULLTEXT 索引使用倒排列表。

B-Tree 索引特性

B-Tree 索引可以用于使用 =、>、>=、<、<= 或 BETWEEN 运算符的表达式中的列比较。如果 LIKE 的参数是不以通配符开头的常量字符串,索引也可以用于 LIKE 比较。例如,以下 SELECT 语句使用索引:

SELECT * FROM tbl_name WHERE key_col LIKE 'Patrick%';
SELECT * FROM tbl_name WHERE key_col LIKE 'Pat%_ck%';

有时 MySQL 不使用索引,即使索引可用。发生这种情况的一种情况是,当优化器估计使用索引需要 MySQL 访问表中很大比例的行时。(在这种情况下,表扫描可能会快得多,因为它需要更少的查找。)但是,如果此类查询使用 LIMIT 仅检索部分行,MySQL 仍然会使用索引,因为它可以更快地找到要返回的少量行。

复合索引

MySQL 可以创建复合索引(即多列索引)。一个索引最多可以由 16 列组成。对于某些数据类型,您可以对列的前缀进行索引(请参见第 10.3.5 节"列索引")。

MySQL 可以将多列索引用于测试索引中所有列的查询,或仅测试第一列、前两列、前三列等的查询。如果在索引定义中以正确的顺序指定列,则单个复合索引可以加速同一表上的多种查询。

多列索引可以被视为一个排序数组,其行包含通过连接索引列的值而创建的值。

假设您发出以下 SELECT 语句:

SELECT * FROM tbl_name
  WHERE col1=val1 AND col2=val2;

如果 col1 和 col2 上存在多列索引,则可以直接获取适当的行。如果 col1 和 col2 上存在单独的单列索引,优化器会尝试使用索引合并优化(请参见第 10.2.1.3 节"索引合并优化"),或者通过确定哪个索引排除更多行并使用该索引获取行来尝试找到限制最严格的索引。

如果表有多列索引,优化器可以使用索引的任何最左前缀来查找行。例如,如果您在 (col1, col2, col3) 上有一个三列索引,则您在 (col1)、(col1, col2) 和 (col1, col2, col3) 上具有索引搜索能力。

如果列不构成索引的最左前缀,MySQL 无法使用索引执行查找。假设您有以下 SELECT 语句:

SELECT * FROM tbl_name WHERE col1=val1;
SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;

SELECT * FROM tbl_name WHERE col2=val2;
SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3;

如果 (col1, col2, col3) 上存在索引,则只有前两个查询使用该索引。第三和第四个查询确实涉及索引列,但不使用索引执行查找,因为 (col2) 和 (col2, col3) 不是 (col1, col2, col3) 的最左前缀。

聚簇索引

聚簇索引（Clustered Index） 是指：

索引和数据存储在一起，叶子节点直接保存完整的数据行。

每个 InnoDB 表都有一个称为聚簇索引的特殊索引，用于存储行数据。通常，聚簇索引与主键同义。为了从查询、插入和其他数据库操作中获得最佳性能，了解 InnoDB 如何使用聚簇索引来优化常见的查找和 DML 操作非常重要。

一句话概括：

InnoDB 中主键索引就是聚簇索引，叶子节点保存完整数据；二级索引的叶子节点只保存索引列和主键值，查询完整记录时需要通过主键再次访问聚簇索引，这个过程称为回表。

如何排查MySQL中的慢查询

排查MySQL中的慢SQL通常需要使用一系列工具和方法来定位问题。以下是一些建议和步骤：

  1. 启用慢查询日志：

    • 在MySQL配置文件中启用慢查询日志，设置合适的long_query_time参数，以定义执行时间超过多少秒的查询被认为是慢查询。慢查询日志记录了执行时间超过设定阈值的SQL语句。
    slow_query_log = 1
long_query_time = 1
slow_query_log_file = /path/to/slow-query.log

  2. 分析慢查询日志：

    • 使用工具如mysqldumpslow、pt-query-digest等来分析慢查询日志，识别执行时间较长的SQL语句。
    mysqldumpslow /path/to/slow-query.log

  3. [使用EXPLAIN分析查询计划]：

    • 使用EXPLAIN语句分析慢查询的查询计划，以了解MySQL是如何执行查询的。这可以帮助识别潜在的性能问题，例如是否使用了索引。
    EXPLAIN SELECT * FROM your_table WHERE your_condition;

  4. 检查索引：

    • 确保查询涉及的字段上有合适的索引。使用SHOW INDEX FROM your_table查看表的索引情况，确保索引被正确使用。
    SHOW INDEX FROM your_table;

  5. 使用MySQL性能工具：

    • 使用MySQL提供的性能工具，如SHOW PROCESSLIST、SHOW ENGINE INNODB STATUS等，来查看当前运行的SQL语句和系统状态。
    SHOW PROCESSLIST;

  6. 分析表结构和查询语句：

    • 审查表结构，确保数据类型、字段长度等设计合理。同时检查查询语句，尽量避免使用SELECT *，只选择需要的字段。

  7. 考虑数据库缓存：

    • MySQL有一个查询缓存机制，但在某些情况下可能会导致性能问题。通过检查query_cache_size和query_cache_type等相关参数来了解缓存的使用情况。

  8. 定期优化表：

    • 使用OPTIMIZE TABLE语句来优化表，重新组织表的物理存储结构。
    OPTIMIZE TABLE your_table;

  9. 数据库服务器硬件和资源：

    • 确保数据库服务器的硬件资源足够，例如内存、磁盘和CPU。监控系统资源使用情况，确保不会出现资源瓶颈。

  10. 使用数据库性能分析工具：

    • 使用第三方性能分析工具，如Percona Toolkit、pt-query-digest等，进行更深入的性能分析和优化。

    通过以上步骤，可以逐步定位慢查询的原因，并采取相应的优化策略。

MySQL索引原理及慢查询优化

使用 EXPLAIN 优化查询

使用 EXPLAIN 分析查询计划的更多细节，请参考文章：MySQL EXPLAIN 输出格式详解与性能优化思路。

死锁

死锁（Deadlock）是指两个或多个事务在执行过程中，因争夺锁资源而造成的一种互相等待的现象。

在 InnoDB 存储引擎中，死锁是行级锁场景下常见的问题，因为 InnoDB 支持行级锁和多种锁类型（如记录锁、间隙锁、Next-Key 锁），多个事务以不同顺序加锁时很容易产生死锁。

死锁产生的原因

  1. 加锁顺序不一致：多个事务以相反的顺序对相同的资源加锁，是最常见的原因。
  2. 锁冲突：事务持有的锁被其他事务请求，同时自己也在请求其他事务持有的锁。
  3. 大事务：事务执行时间过长，持有锁的时间过久，增加与其他事务冲突的概率。
  4. 索引使用不当：查询未走索引时，InnoDB 会对全表行加锁（或间隙锁），大幅扩大锁范围，提升死锁概率。

死锁示例

创建测试表并插入数据：

CREATE TABLE deadlock_test (
  id INT PRIMARY KEY,
  value INT
) ENGINE=InnoDB;

INSERT INTO deadlock_test VALUES (1, 10), (2, 20);
COMMIT;

两个会话按以下顺序执行，就会产生死锁：

-- 会话 A
START TRANSACTION;
UPDATE deadlock_test SET value = 11 WHERE id = 1; -- 持有 id=1 的行锁

-- 会话 B
START TRANSACTION;
UPDATE deadlock_test SET value = 21 WHERE id = 2; -- 持有 id=2 的行锁

-- 会话 A 继续执行，请求 id=2 的行锁，被会话 B 阻塞
UPDATE deadlock_test SET value = 12 WHERE id = 2;

-- 会话 B 继续执行，请求 id=1 的行锁，被会话 A 阻塞，此时形成死锁
UPDATE deadlock_test SET value = 22 WHERE id = 1;

此时 InnoDB 的死锁检测机制会触发，自动回滚其中一个事务，并抛出死锁错误：Deadlock found when trying to get lock; try restarting transaction。

InnoDB 死锁检测机制

InnoDB 默认开启死锁检测功能（由 innodb_deadlock_detect 参数控制，默认值为 ON）。

当检测到死锁时，InnoDB 会选择回滚回滚代价最小的事务（通常是修改行数最少的事务），让其他事务可以继续执行。

你可以通过以下命令查看最近的死锁信息：

SHOW ENGINE INNODB STATUS;

在输出的 LATEST DETECTED DEADLOCK 部分可以看到死锁的详细过程。

死锁预防建议

  1. 固定加锁顺序：所有事务都按照相同的顺序访问表和行，避免交叉加锁。
  2. 缩小事务范围：尽量缩短事务的执行时间，尽快提交或回滚，减少锁的持有时间。
  3. 使用合适的隔离级别：如果业务允许，使用 READ COMMITTED 隔离级别，该级别下 InnoDB 会禁用间隙锁（除了外键约束和重复键检查），减少锁冲突的概率。
  4. 优化索引：确保查询走合适的索引，避免全表扫描导致的大量行锁/间隙锁。
  5. 避免交互操作在事务中：不要让用户在事务执行过程中进行输入等交互操作，减少事务持锁时间。
  6. 设置合理的锁等待超时：调整 innodb_lock_wait_timeout 参数（默认50秒），避免事务长时间等待锁。

死锁发生后的处理

  1. 自动回滚重试：InnoDB 会自动回滚死锁中的一个事务，应用程序捕获到死锁错误后，可以重试该事务。
  2. 临时关闭死锁检测：在高并发场景下，频繁的死锁检测可能会带来性能开销，可以通过设置 innodb_deadlock_detect = OFF 关闭死锁检测，此时事务会等待 innodb_lock_wait_timeout 后超时回滚，需要业务层处理重试逻辑（谨慎使用）。
  3. 分析死锁日志：通过 SHOW ENGINE INNODB STATUS; 查看死锁详情，定位加锁逻辑的问题，优化业务代码。

更多关于 InnoDB 死锁的内容，请参考 MySQL 官方文档：死锁。

延伸阅读