温故知新MySQL篇

MySQL是每个后端人学习过程中最重要的内容之一了，不过可能随着工作时间的增长，一些曾经学过但常常用不到的知识也都慢慢流逝了～～

本文希望能在一篇文章中，把MySQL中的基础知识尽量都串联起来。对于具体的知识点，希望能做到精炼且生动的诠释。希望不管是社畜人还是后端新人，看完这篇文章，都能有所收获。

对于具体的知识点，本文可能不会讲的很深入。另外，文章中有遗漏或者错误之处，也欢迎大家在评论区讨论，一起完善补充。

1. 什么是MySQL

MySQL是现在业界最主流的关系型数据库，简单讲，关系型数据库就是通过表格形式进行数据统计的数据库。

2. 索引-为查询“赋能”

2.1. B+树

数据存储是为了进行查询，既然提到查询，那必然就会涉及到效率问题，MySQL可以靠增加索引来提升搜索效率。那么，索引为什么能提升搜索效率呢？解释这个问题，就需要了解MySQL的数据结构“B+树”。

B+树分为根节点、索引节点与叶子节点，其中，根节点与叶子节点都维护了一组带有“排序”的指针（以上图为例，根节点维护了五个指针，标号分别为1，20，20，30，40）。在进行数据查询时，搜索线程会从根节点出发，根据排序所在的区间，层层定位，最终找到存储数据的“叶子节点”。

在B+树之前，MySQL使用的数据结构是B树，B+树与B树的区别，也是常见的面试问题：

• B+树与B树的本质区别是，B+树只在叶子节点存储数据，B树在中间节点也会存储数据。
• 索引节点不存储数据，就可以储存更大范围的索引，MySQL在读取索引时会有磁盘IO，是很耗时的操作，B+树的这种设计能减少磁盘IO的数量，查询的效率会更高。

上面提到了很重要的一点，磁盘IO，因此在设置MySQL中B+树的节点大小时（默认大小大约为16KB），最好为磁盘页的倍数，这样能使磁盘IO更高效。

2.2. 联合索引

联合索引就是使用多个属性一起当作索引建立B+树：

2.3. 聚簇索引

在Innodb引擎中，数据本身就是存储在一个索引B+树中的，而这个索引就是聚簇索引，聚簇索引的叶子节点就是数据库元数据，其他的所有索引都是非聚簇索引，非聚簇索引的叶子节点维护一个指向聚簇索引叶子节点的指针。

非聚簇索引的叶子节点只有索引使用的属性，比如我们以班级+性别建立索引，那么这个索引中就只有班级和性别信息，而当我们需要根据班级和性别查询学生的名字时，就需要根据非聚簇索引的叶子节点维护的指针回到聚簇索引查看名字的属性，这个过程就是常说的回表查询。

回表查询虽然会用到索引，但是会降低查询的效率，因此，我们在建立索引时，需要尽量把需要的属性都涵盖上，这样只需要查询非聚簇索引就能获得我们需要的所有信息了，满足这个条件，就是索引覆盖。

2.4. 索引失效

查询语句不走索引的情况就是索引失效，而所有的索引失效，本质上都是没有符合最左匹配原则。

以上面的联合索引为例子：

如果我们直接以性别为条件查询，那么上面的结构自然而言是失效的，我们只能全量走一遍所有节点，这种情况下索引就失效了。

可能导致最左匹配失效的几种情况：

• 联合索引查询条件没有携带靠前的条件。
• LIKE，NOT，<>，BETWEEN符号导致索引失效。
• 查询条件中有函数、计算或者隐式转型。
• 某些索引未覆盖或者索引区分度很低的情况下，优化器判断走索引效率低。

索引区分度低是指数据很多行具有相同的值，例如性别。

3. 事务

MySQL中的事务主要用于解决数据一致性的问题，确保在多个数据库操作之间保持数据的完整性和可靠性。

3.1. 事务的4大特性

MySQL分别通过如下机制保障ACID特性：

3.2. 事务的4大隔离级别

在数据库事务处理中，脏读（Dirty Read）、不可重复读（Non-Repeatable Read）和幻读（Phantom Read）是事务隔离级别中可能出现的几种现象，它们描述的是一个事务读取其他事务未提交或已提交数据的不同情况，具体如下：

3.3. 锁

当一个事务开始执行需要加锁的操作时，如UPDATE、DELETE或SELECT ... FOR UPDATE/SHARE等语句，InnoDB会根据语句的具体类型和设置的事务隔离级别来决定加锁的类型和范围。

什么情况下会加哪种锁：

• 对于等值查询，如果查询条件匹配唯一索引，InnoDB通常会加行锁（Record Lock），锁定匹配的行。
• 对于等值查询，如果索引不是唯一的，InnoDB会加下一个记录上的间隙锁（Gap Lock）以防止插入，同时也会加行锁到找到的记录上，这种组合称为Next-Key Lock，以防止幻读。
• 范围查询时，InnoDB会在查询的范围两端加上间隙锁，以防止在这个范围内插入新的记录，造成幻读。对于范围内的每一行匹配记录，同样会加行锁。

索引加锁的位置：

• 当操作涉及到索引时，无论是主键索引还是非主键（二级）索引，InnoDB都是通过对索引来加锁实现行锁的。如果查询使用了二级索引，InnoDB除了在二级索引记录上加锁外，还会对相应的聚簇索引记录加锁，以确保数据的一致性。

上面说的都是互斥锁（悲观锁），即直接限制并发操作的锁，MySQL还有乐观锁机制，MySQL中的每一行都有事务ID（版本号），这个是自增的，可以通过对版本号的校验，判断更新操作是否被其他修改操作插入，进而判断是否成功。

3.4. MVCC版本控制机制

MVCC是一种多版本并发控制机制，通过事务的可见性看到自己预期的数据，能降低其系统开销（RC和RR级别）。简单的select不会加锁，删改及select for update等需要当前读的场景会加锁。

所谓的MVCC机制，本质上是事务会根据版本号在Undo日志中生成一组“快照”（同时用来做回滚日志，保障原子性），事务在读取时会优先读取快照中的数据，避免其他事务线程的影响，解决脏读与不可重复读的问题。

4. 单机->集群

4.1. 分库分表

随着业务量的增长，单机性能就会遇到瓶颈，单机不行，那就只能加机器了。然而，数据库服务与业务服务又不同，业务服务提供的是逻辑处理，是轻数据的，而数据库提供的是重数据的数据查询服务，增加机器的时候，如何进行数据拆分，就是老生常谈的分库分表问题。

分库一般是根据业务场景进行的，相同业务场景的表会归拢到一个库中，对于这部分，一般感知和涉及的都比较少。

分表是指对于一个独立的表，对于其中的数据进行切割，存储在不同的机器上。在进行分表时，有垂直分表与水平分表两种方式：

• 垂直拆分：垂直分表是将一个表根据列的特点分割成多个独立的小表，每个表包含原表的一部分列。例如，一个包含用户基本信息和用户详细行为记录的表可以分为用户基本信息表和用户行为表。
• 水平拆分：水平分表是将一个大表按照某种规则（如ID范围、取模算法等）分割成多个小表，这些小表结构相同，包含相同的列，但每张表存储的是原表中不同部分的数据行，在查询时路由到对应的表。

4.2. 水平分表的哈希倾斜

水平拆分一般都是通过哈希算法进行流量路由的，往往使用用户ID等信息，这时其实可能会遇到哈希倾斜的问题，例如有一个超级商家，订单很多，那么在查询中，这个超级买家所在的表的流量就会很大，如果多个超级商家哈希到了同一个表，那么流量就会更大了。

但是在数据库场景下，一致性哈希算法不一定是最优解。举一个例子，我们在进行水平分表时，分表键往往是查询的关键条件，比如我们查询一个公司下有哪些订单，按照公司ID进行分表，我们只需要在一张表进行查找就能找到所有数据，而采用一致性哈希时，数据就可能被打散到了多张表中，其查询效率就成了未知数。（个人看法，不一定对，欢迎讨论）。

对于上面说的类似于多个超级商家哈希到了同一个表的问题，有两个思路：

• 结合业务场景，采用复合分区键，如果单一字段无法均匀分布数据，可以考虑使用复合分区键，结合两个或多个字段进行分区，以达到更好的均衡。比如在订单场景，一般查询是根据商家ID+订单类型查询的，就可以用商家ID+订单类型作为复合分区键，将大卖的数据离散到各个表中，分散压力。
• 特殊情况下，可以进行哈希微调。一般场景下，超级用户的个数都是有限的，可以对超级用户采取单独的哈希策略，保障超级用户的数据尽量均匀分布。

4.3. 数据分身-主从复制

数据的冗余存储核心是为了保证高可用性，一方面可以避免单点故障，一方面可以进行读写分离提升效率。

MySQL的主从复制是将主服务器的binlog日志复制到从服务器上执行一遍，达到主从数据的一致状态。

MySQL读写分离是指数据修改操作在主库，通过binlog日志同步到分库，数据读取在分库。读写分离能提升数据读取的效率，但是，既然存在数据同步的过程，就会有数据一致性的问题（主库和分库数据是否一致），MySQL提供了三种级别的一致性方案：

• 异步复制（最低） ：当主库执行完事务后，写入binlog日志后，就视为事务成功，不管从库是否更新。
• 半同步复制（中等）： 当主库执行完事务后，不会立即返回，而是等待其中一个从库接收到Binlog并成功写入Relay-log中才视为事务成功
• 全同步复制（最高）： 当主库执行完事务后，所有的从库都执行了该事务才视为事务成功。

越高的数据一致性就代表越低的执行效率，MySQL默认的策略是异步复制。

另外，也是因为数据同步的延迟性，当采用异步复制的策略时，主库的更好不能即时的同步到从库（尤其是在主从库物理距离很远的时候，比如跨境），因此要尽量避免插入数据后即刻进行搜索的操作。

4.4. 容灾方案

上面说到了，数据冗余存储还能避免单点故障的问题，当主库故障时，会基于选举算法选出一个从库晋升为主库：

• • 每个节点都会持续监控其他节点的心跳信息。如果一个节点没有收到主节点的心跳超过预设的超时时间，它将认为主节点已失效，并开始选举过程。

• • 任何认为主节点失效的节点都可以发起选举。这个节点会向其他节点发送请求，宣告自己想要成为新的主节点。

• • 接收到选举请求的节点会评估自己的状态（数据同步程度、优先级等），并可能响应支持该请求或者提出自己的候选资格。这一步骤确保只有最新的节点参与选举，避免数据不一致。

• • 所有参与选举的节点通过一系列的消息交换来达成共识。每个节点会投给自己认为合适的一方（一般是看版本号，谁的数据更新，谁更适合当主节点），并收集投票。一旦某个节点获得大多数节点的支持（即超过半数），它就被选为新的主节点。

• • 当一个节点获得了足够的票数成为主节点后，它会广播这一消息给所有其他节点，其他节点确认并调整自己的状态为从节点，开始从新的主节点接收更新。

• • 在新的主节点开始接受写操作之前，可能会执行一些一致性检查，确保数据的一致性。