深入探讨MySQL

MySQL是一个开源的关系型数据库管理系统，广泛地应用于网站和应用程序的数据存储和管理。它最初由瑞典的公司MySQL AB开发，后来被甲骨文公司收购。尽管如此MySQL仍然是全球使用最广泛的数据库系统之一。

由于InnoDB是MySQL当前的默认存储引擎，本文的所有内容都将基于此进行讨论。

MySQL索引

索引相当于“数据库的书签“，能够帮助我们快速定位数据。

MySQL索引应用场景

索引作为优化数据库查询性能的重要工具，其作用毋庸置疑，但是过多或者不合理的索引反而会导致性能下降和存储空间的浪费。因此如何在合适的时机添加索引就显得尤其重要。

索引的主要作用就是加速查询，当某个字段经常被用作查询条件或者经常用于排序或分组操作时，如WHERE子句后、或者在ORDER BY、GROUP BY或DISTINCT中频繁使用时，就可以考虑为该字段添加索引。此外，当两张表或者多张表进行JOIN操作时，也可以在连接条件的字段上添加索引，以加速数据的匹配过程。

MySQL索引失效

索引能够显著提升查询的性能，但在一些情况下，尽管我们已经对列设定了索引，但是MySQL的查询优化器会放弃使用索引，转而使用全表扫描，此时就失去了设定索引的意义。

• 范围查询没有使用最左前缀：复合查询需要遵循最左前缀原则，否则可能会导致索引失效。

• 使用了函数或表达式：在查询条件中对索引列使用函数或者表达式。

• 类型不一致：查询条件的类型与索引列类型不匹配，例如索引串是字符串类型，但是条件为数字类型。

• 使用通配符匹配：在模糊查询中，如果通配符位于开头，索引会失效。

• OR条件，如果WHERE条件中有OR，且没有为所有的列添加索引，可能会导致索引失效。

• 查询量太少或者全表扫描更优：查询优化器会根据数据分布选择更快的查询方式，如果表的数据量较小，全表扫描可能更快。

• 隐式转换：如果列为字符串类型，而查询条件未使用引号包裹，可能会触发隐式类型转换，从而导致索引失效。

当然实际时间当中我们可以通过EXPLAIN语句来查看执行计划，从而判断其是否使用了索引，如果使用了索引，则索引字段会显示在key列中，而type列则显示range表示索引范围扫描。

再见B+树

在数据库架构原理 | async已经简要介绍了B+树，使用B+树索引还可进一步细分为聚簇索引和非聚簇索引。

聚簇索引就是将数据存储和索引放在了一起，如果找到了索引，也就找到了数据，一般情况下主键会默认创建聚簇索引，且一张表仅允许存在一个聚簇索引。InnoDB中每张表都有一个聚簇索引，通常由主键列实现，如果没有定义主键，InnoDB会选择一个唯一的非空的索引列作为聚簇索引，如果没有合适的列，InnoDB会隐式创建一个内部主键列。

非聚簇索引叶子节点存储的是指向数据行的指针，而不是实际的数据行。再通过非聚簇索引查询的时候，需要进行回表操作，即通过主键从聚簇索引中再找到相应的数据行。设计非聚簇索引叶子节点仅存储指针而非实际的数据行的原因是一旦数据发生了迁移，不需要重新组织维护所有的索引。

此外，如果查询的数据列只需要通过索引就能够获取，就不再需要回表，这也称为覆盖索引。

如图所示：

非聚簇索引查询过程

假设要查找name=C的数据，那么会先在非聚簇索引下中通过C查找到主键id=9，然后再在聚簇索引的叶子节点中找到真正的数据。因此通过非聚簇索引进行检索，需要检索两次索引。

MySQL锁机制

锁机制是数据库管理系统中并发控制的重要手段，主要用于解决数据并发访问冲突，保障数据的一致性和完整性。MySQL中不同的存储引擎实现了不同的锁机制，而在实际应用中，InnoDB存储引擎的锁机制是重点讨论对象。

按照锁的粒度进行划分，可以划分为以下两种：

• 表级锁：MySQL中锁定粒度最大的一种锁，是针对非索引字段加的锁，对当前操作的整张表加锁，实现简单且资源消耗较少，加锁较快且不会出现死锁。但是这种方式触发锁的概率很高，在高并发下的效率较低。MyISAM和InnoDB引擎都支持。
• 行级锁：MySQL中锁定粒度最小的一种锁，是针对索引字段加的锁，只针对当前操作的行记录进行加锁。行级锁能够极大减少数据库操作的冲突，并发度高，但是其开销也较大，加锁较慢，会出现死锁。InnoDB中的行级锁当索引失效时会导致全表扫描，并且对所有行记录进行加锁。

如果按照锁的行为进行划分，则又可以分为以下两种：

• 共享锁：又称为读锁。多个事务可以同时读取同一个资源，而不会阻塞其他事务的读，但是会阻塞写操作。
  • 如：SELECT * FROM table_name LOCK IN SHARE MODE;
• 排他锁：又称为写锁/独占锁，在加锁后，其他食物无法读取或者修改该资源。
  • 如：SELECT * FROM table_name FOR UPDATE;

对于行级锁，在InnoDB中支持三种行锁定方式：

• 记录锁：属于单个记录上面的锁。
• 间隙锁：锁定一个范围，但是不包括记录本身。
• 临键锁：锁定一个范围，包括记录本身，是记录锁和间隙锁的组合。主要是为了防止幻影读问题。记录锁仅能锁住已经存在的记录，为了避免插入新的记录，需要依赖间隙锁。

此外还有意向锁，这是一个表级锁，由存储引擎自己维护，用户无法手动操作。这个锁是在为数据行添加共享锁/排他锁之前存储引擎要先取得的，也一样分为意向共享锁和意向排他锁：

• 意向共享锁：事务有意向对表中的某些记录加共享锁，在加锁之前先要取得的锁。
• 意向排他锁：事务有意向对表中的某些记录加上排他锁，加上排他锁之前所先要取得的锁。（感觉自己在说废话）

MySQL事务

事务就是满足ACID的一组操作，可以通过Commit提交一个事务，可以通过Rollback进行回滚。

ACID简要来说就是以下四点：

• A（原子性，Atomicity）：一个事务中的操作要么全部成功要么全部失败。在MySQL中主要由Undo Log和事务管理器负责保障，通过记录事务中的每一步操作，可以在事务失败时撤销这些操作，确保原子性。
• C（一致性，Consistency）：数据库总是从一个一致性的状态切换到另外一个一致性的状态。一般而言由代码层面保证。
• I（隔离性，Isolation）：并发事务之间互不干扰，一个事务的中间状态对其他事务不可见。通过锁机制（行锁和间隙锁）和MVCC（多版本并发控制）实现。不同隔离级别的并发控制，从而避免脏读、不可重复读和幻影读问题。
• D（持久性，Durability）：事务提交之后，结果会被永久保存，即使发生系统故障。主要通过Redo Log进行保障，通过事务的更改记录到持久化的日志文件中，在系统恢复时重放日志从而确保数据的持久化。

并发一致性问题

在高并发的情况下，事务的隔离性是很难保证的，可能会出现很多并发一致性的问题：

• 丢失修改：两个事务同时对一个数据进行修改，但是其中一个事务的修改被另一个事务的修改覆盖。
• 读脏数据：事务1修改了一个数据，事务2随后读取了这个数据，然而事务1撤销了这次修改，此时事务2读取的数据就是脏数据。
• 不可重复读：事务2读取一个数据，事务1对该数据进行了修改，此时事务2再次读取这个数据，此时读取的结果和第一次读取的结果不同。
• 幻影读：事务1读取某个范围的数据，事务2在这个范围内插入了新的数据，事务1再次此读取这个范围的数据，此时读取的结果和第一次读取的结果不一样。

MySQL中提供了四种事务隔离级别来解决这些问题。

• 未提交读：事务中的修改，即使没有提交，对于其他事务也是可见的。即会直接读取最新的数据行，不会使用一致性视图。这个级别几乎不会使用，因为其隔离性非常弱，很有可能导致脏读、不可重复读和幻影读。
• 已提交读：事务只能读取到其他事务已经提交的修改，每一次读取操作都会创建一个新的快照，读取的是已经提交的事务的最新版本，而对于修改的操作，则会进行加锁避免其他事务读取修改中的数据。大多数数据库默认都会采用此隔离级别，避免了脏读但是仍然有可能出现不可重复读和幻影读。
• 可重复读：事务在开始的时候会创建一个一致性视图，确保同一个事务中读取的结果一致。在修改数据的时候，会使用行锁和间隙锁避免其他事务插入或修改可能影响当前查询的数据行。这是MYSQL的默认隔离级别，有效避免了脏读和不可重复读，但是还是有可能出现幻影读。
• 串行化：事务按照严格的顺序执行，确保完全隔离。每个读取操作都会对相关表或范围加上共享锁，对写操作加上排他锁，确保严格的串行执行顺序，在当前的事务完成之前，其他的事务无法对相关范围进行修改或者插入。这避免了所有并发一致性的问题，但是由于需要频繁加锁，事务之间等待的时间较长。

MVCC

MVCC (Multi-Version Concurrency Control)，即多版本并发控制。MVCC是一种实现并发控制的方法，一般在数据库管理系统中，实现对数据库的并发访问，在编程语言中实现事务内存。

MySQL的InnoDB引擎中的MVCC实现主要针对已提交读和可重复读这两种隔离级别下的事务，因为未提交读会直接读取最新的未提交的版本，串行化通过加锁实现严格隔离，不依赖于MVCC，这两种隔离级别下的MVCC作用有限。

MySQL的InnoDB引擎对MVCC的实现：

每行数据在InnoDB中会存储两个隐藏的系统列，用于实现MVCC的多版本支持：

DB_TRX_ID：用于记录最后一次修改该行的事务ID。

DB_ROLL_PTR：指向Undo Log的指针，用于访问改行的旧版本。

这两列在用户表中是不可见的，但是在内部主要用于维护版本链。

隐藏字段

在不同事务或者相同事务对于同一记录的修改，会导致该记录的Undo Log成为一条记录版本的线性表，即链表，Undo Log的链首就是最新的旧记录，链表尾就是最早的旧记录：

Undo Log

已提交读和可重复读的区别就在于其生成一致性视图的策略不同。一致性视图在事务启动时生成，用于定义哪些版本对当前事务可见。一般包含有以下内容：

• m_ids：当前系统中活跃事务的ID列表。
• min_trx_id：m_ids中最小的事务ID。
• max_trx_id：当前系统分配的下一个事务ID。
• 当前事务的ID。

需要注意的是，对于事务 ID 位于 min_trx_id 和 max_trx_id 之间的情况，min_trx_id 和 max_trx_id 无法区分事务是已提交还是未提交，因为这个范围内包含了所有活跃事务（未提交事务）和刚刚提交的事务。

假设当前的min_trx_id为80，而max_trx_id为100，如果要访问的记录版本的事务ID为50，比min_trx_id小，则说明这个事务在之前就已经提交，因此对当前活动的事务而言是可以访问的。如果访问的记录版本的事务Id为90，在max_trx_id与min_trx_id之间，就再判断一下该事务是否在列表内，如果在就说明该事务还没有被提交，此时不能够访问，如果不在就说明事务已经提交，可以访问。如果要访问的的记录版本的事务Id为110，就说明其是在此一致性视图生成之后才发生的，因此不能够被访问。

所有的记录都是从Undo Log链中查找的，先查找最近的记录，如果最近这一条记录的事务Id不符合条件，不可见的话就再找上一个版本，比较当前事务Id和上一个版本的事务Id来进行比较，直到返回可见的版本。

已提交读隔离界别下的事务在每一次查询的开始都会生成一个独立的一致性视图，而可重复读隔离级别则是在第一次读的时候生成一个一致性视图，之后的读都会复用之前的一致性视图。

MySQL分库分表

随着业务发展和数据量的快速增长，MySQL单库单表的架构难以满足高并发和大数据量的场景需求。这个时候就需要进行分库分表。分库分表的好处主要有减少单表的数据量，提升索引的效率，减少全表扫描。

分库的方法有垂直分库，分表有垂直分表和水平分表。

• 垂直分库就是将数据库按照业务模块或者功能划分进行拆分的方法，具体而言就是将一个数据库中的不同表，按照业务逻辑和功能进行拆分，分布到多个独立的数据库中。一般而言现在的微服务都已经做到了垂直分库。
• 垂直分表就是将一张表切分为多个表，通常按照列的关系密集程度进行切分，也可以利用垂直切分将经常被使用的列和不经常被使用的列切分到不同的表中。
• 水平分表就是将数据库表的数据按照行记录的维度进行拆分的策略，即将同一张表的数据按照某种规则拆分到多个表中，每个子表的结构完全相同，但是存储的数据不同。水平分表的ID仍然需要保持唯一性，此时就需要不同的ID生成策略：
  • 自增ID+步长法：每个子表或者数据库配置不同的自增起始值和步长，保证生成的ID唯一，例如有三个子表，则可以设定表的自增起始值分别为0、1、2，步长为3，这样生成的ID就能够达到保证唯一性的效果。但是缺点就是扩容的时候需要重新分配起始值和步长，维护较为复杂。
  • UUID法：使用数据库函数或者应用程序生成128位的唯一标识符字符串，优点是生成简单，天然保证唯一性，但是由于其是字符串，存储空间大，索引性能较差，且不适合用作主键。
  • 雪花算法：一种分布式ID生成算法，会分为一个64位的长整型ID，时间戳占41位，保证时间顺序，机器ID占用10位，用于区分不同的服务器或者节点。序列号占用12位，同一时间内生成的序列号，保证唯一性。

水平分表的时候如果查询时使用的条件不是分表键（用于决定数据分布规则的字段），会导致查询无法直接定位到某个具体的子表或者分库，这个时候我们可以采用冗余数据的方式，将非分表键的数据或者索引冗余存储到一个独立的全局表或者分库的其他表中，查询的时候先通过冗余表获取分表键，再根据分表键查询具体的分表。

MySQL集群

主从复制

主从复制主要有以下几种类型：

• 异步复制：默认复制方式，主库将二进制日志发送给从库，但是不会等待从库确认，而是继续执行后续的操作。这种方式性能高，主库不会因为从库的延迟而受到影响，但是当主库宕机时，从库可能会丢失部分数据。
• 半同步复制：主库在提交事务之后，至少会等待一个从库确认收到二进制日志才算提交成功。一定程度上保证了数据的一致性，减少了数据丢失的风险，但是性能比异步复制略低，因为主库还需要等待从库的响应。
• 同步复制：主库在提交事务之后，必须等待所有的从库确认完成数据同步，才算提交成功。这种方式的数据一致性最高，但是性能的开销较大，主库的写操作会被从库的同步速度影响，从而影响写入性能。

MySQL的主从复制主要涉及到三个线程：binlog线程、I/O线程以及SQL线程。

• binlog线程主要负责将主服务器上的数据更改写入到二进制日志中。
• I/O线程主要负责从主服务器上读取二进制日志，并且写入从服务器的中继日志中。
• SQL线程负责读取中继日志并且重放其中的SQL语句。

MySQL主从复制

读写分离

读写分离是MySQL在主从复制的基础上实现的一种数据库架构优化方案，核心的思想是写操作交由主库处理，而读操作分散到从库处理。这样可以减轻主库的压力，提高系统的并发处理能力，特别适合读多写少的业务场景。

读写分离能够提高性能的原因主要在于主从服务器负责各自的读和写，极大程度缓解了锁的争用，从服务器可以使用MyISAM存储引擎，从而提升查询性能，节约系统的开销。

读写分离通常使用代理的方式来实现，代理服务器接收应用层传来的读写请求，然后再决定转发到哪一个服务器。

在实际应用中，随着数据量和业务的持续增长，合理设计 MySQL 的架构、优化查询性能、保障数据一致性和系统可用性，都是我们需要不断深入和实践的领域。结合业务特点与需求，灵活运用索引优化、分库分表、读写分离等技术，将为系统性能和稳定性带来显著提升。

MySQL 的强大不仅在于其功能和性能，更在于它的灵活性与可扩展性。深入理解 MySQL 的底层原理与实现机制，将帮助我们在实际项目中更好地解决问题、优化系统，为业务的发展提供坚实的数据支撑。

参考资料：

MySQL :: MySQL 8.4 参考手册 :: 17.6.2.1 聚集索引和二级索引 --- MySQL :: MySQL 8.4 Reference Manual :: 17.6.2.1 Clustered and Secondary Indexes

Java 全栈知识点问题汇总（上） | Java 全栈知识体系

了解 MySQL 复制坐标 |吉尔伯特·马图塞维奇乌斯 --- Understanding MySQL replication coordinates | gilbertas matusevičius