记录些MySQL题集（6）

MySQL 单表为什么不要超过 2000W 行？

数据持久化在磁盘中，磁盘的最小单元是扇区，一个扇区 0.5 KB，而由 8 个扇区可以构成一个文件系统块（4K），以 InnoDB 存储引擎为例，一个数据页的大小是 4 个 文件系统块（16K）。

假设一个数据行是 1KB，一个数据页存放 16 条数据。MySQL 的 InnoDB 引擎，底层是 B+ 树。

B + 树数据查找流程

1）从根节点开始：首先，从 B+ 树的根节点开始搜索。如果树为空，则搜索结束，未找到目标数据。

2）节点内部搜索：对于每个非叶子节点，根据节点内的键值进行搜索。根据搜索的键值大小，确定下一步搜索应该进行的分支。

3）叶子节点搜索：当搜索到叶子节点时，根据叶子节点内的键值和指针信息，进行目标数据的查找。如果在叶子节点中找到了目标数据，则搜索结束；否则，转到下一步。

4）范围查询处理：如果进行范围查询，即需要找到大于或等于某个值的所有数据，或者小于或等于某个值的所有数据，则可以根据 B+ 树的特性，在叶子节点中进行顺序遍历或者反向遍历。

5）返回结果：如果找到了目标数据或者范围内的数据，则返回查询结果；否则，返回空结果，表示未找到目标数据。

B + 树存的数据量大小

B+树的存储总数据数 = 根节点指针数 * 单个叶子节点记录条数。假设主键 ID 为 bigint 类型，长度为 8 字节，而 InnoDB 源码中设置的指针大小为 6 字节，总共占用 14 字节。因此，在一个页中可以存放 16384 个字节，即 1170 个这样的组合。因此，对于一棵高度为 2 的 B+ 树，可以存放的数据记录数量为18720 条。而对于高度为 3 的 B+ 树，则可以存放的数据记录数量为 21902400 条。

实际项目中，每个表的结构设计都不一样，占用的存储空间大小也各不相等。假如一个数据行为 5K，那么数据记录量就在 500W 左右，因此需要根据数据行大小去进行一个数据量的判断。

MySQL单表不建议超过2000万行的主要原因有以下几点：

1. 性能问题：随着表中数据量的增加，查询、插入、更新和删除操作的性能可能会受到影响。特别是在没有正确索引支持的情况下，随着数据量的增加，查询性能可能会线性下降。

2. 内存消耗：MySQL在内存中会缓存部分数据和索引，用于加速查询。当单表数据量过大时，会增加内存消耗，可能导致性能下降或者频繁的磁盘IO操作，影响系统整体性能。

3. 锁冲突：大表上的更新操作可能需要锁定较多的行，导致锁冲突和并发性能下降。特别是在使用InnoDB引擎时，行级锁会增加锁管理的开销。

4. 备份与恢复：大表的备份和恢复操作可能会更加耗时，增加系统维护的复杂性。尤其是在磁盘空间有限或者网络带宽受限的情况下，备份和恢复可能需要更长的时间。

可以创建一个包含大量数据的表，并进行一系列查询、插入、更新和删除操作，观察操作的响应时间和系统资源的使用情况。可以对比在不同数据量下的性能表现，从而验证单表数据量过大对MySQL性能的影响。

在 InnoDB 中 B+ 树高度一般为1-3层，它就能满足千万级的数据存储。查询数据时，每加载一页（page）代表一次IO，所以通过主键索引查询通常只需要1~3次 I/O 操作即可查找到数据。

MySQL 都有缓存， B+ 树高度为 3 时，第 1 层和第 2 层的数据都在缓存中，查询只需要一次 I/O 操作，速度很快，但是当数据超出 2KW 时， B+ 树层高会增加，需要再多一次 I/O 操作，查询效率就急速下降了。所以 MySQL 数据库单表建议最大 2KW 数据。

MySQL 死锁怎么发生的？怎么解决？

死锁产生的原因

可重复读隔离级别，会有当前读的幻读问题。所以 InnoDB 是采用了 MVCC + NextKey 锁，解决当前读的幻读问题。

NextKey（临键锁） = RecordLock(记录锁)+GapLock(间隙锁)。

记录锁：锁行记录本身，一条。

间隙锁：除锁记录本身，锁定一个范围，多条。

普通 Select 语句是属于当前读，不加行锁。

加行锁方式： share mode 共享锁（多个读可以，修改不行），或者 for update 直接加排他锁（独占）。

行锁的释放时机是在事务提交之后，也就是写入 binlog 日志，并且  redolog 是 Commit 状态，不是语句执行结束就释放行锁了，关键是事务什么时候提交。

锁是针对索引进行加锁，如果没有使用到索引，会进行全表扫描，就会产生业务宕机情况。

两个事务，都采用加行锁，并且由于加锁范围产生了交集，因此

就会产生等待，要注意 X 和 X，X 和 S，S 和 S，三个只有 S 和 S 可以共享，其他都会冲突阻塞。

死锁解决方案

1）设置事务等待锁的超时时间，超时后进行事务回滚，锁被释放，跟 Spring 的 @Transactional 的 timeOut 可以类比。

2）开启主动死锁检测，参数 InnoDB_deadlock_detect 设置为 on 开启，开启死锁检测。

最好在业务场景，直接杜绝死锁，比如接口幂等性，直接用分布式 id、唯一性索引、双重 token 等。

Insert 插入

在 MySQL 中，INSERT 语句并不会直接加行级锁。行级锁是在对数据进行读取或更新时加上的，以保护数据的一致性和完整性。然而，INSERT 语句可以触发行级锁的加锁行为，具体取决于数据库的隔离级别以及并发访问情况。

当执行 INSERT 语句时，如果插入的数据涉及到已经被其他事务锁定的行，则可能会发生行级锁定。这种情况下，MySQL 会根据当前事务的隔离级别和已经存在的锁情况来决定是否对相关行加锁，以确保数据的一致性和完整性。

在默认的隔离级别（REPEATABLE READ）下，当执行 INSERT 语句时，MySQL 会根据已有的锁情况来判断是否需要对相关行加锁。如果其他事务已经对插入的数据所在的行加了锁，MySQL 可能会等待该锁被释放后再执行插入操作，或者根据需要自动加锁。

其他补充

MySQL InnoDB 引擎下死锁产生的原因和解决方案

死锁产生的原因：

在 MySQL 的 InnoDB 引擎下，死锁（Deadlock）通常是由于多个事务相互竞争资源（例如行级锁）而产生的。当多个事务同时持有某些资源的锁，并且每个事务都在等待其他事务释放它所需的锁时，就会出现死锁。

死锁的解决方案：

1. 超时重试： 当检测到死锁时，InnoDB 会选择其中一个事务作为死锁牺牲者，将其回滚并释放资源。其他事务会收到一个错误，可以在这种情况下重新尝试。

2. 优化事务和锁定顺序： 通过优化事务的设计和锁定资源的顺序，可以减少死锁的发生。例如，尽量减少事务中涉及的数据量，避免长时间持有锁。

3. 使用死锁检测工具： MySQL 提供了一些死锁检测工具，可以帮助识别和解决死锁问题。例如，通过设置 innodb_print_all_deadlocks=1 可以在日志中打印死锁信息。

数据库锁的种类和作用

在数据库中，锁是用来管理对共享资源的访问的机制。不同的锁具有不同的粒度和作用，常见的数据库锁包括：

1. 行级锁（Row-level Lock）： 行级锁用于锁定单个数据行，防止其他事务对该行进行修改。行级锁可以提高并发性，但可能会导致死锁问题。

2. 表级锁（Table-level Lock）： 表级锁用于锁定整个表，防止其他事务对整个表进行修改。表级锁会影响并发性，因为只有等到整个表解锁后，其他事务才能继续操作。

3. 页级锁（Page-level Lock）： 页级锁用于锁定数据页，即一组连续的数据行。它介于行级锁和表级锁之间，既提高了并发性，又减少了锁的粒度。

4. 意向锁（Intention Lock）： 意向锁是用来表示一个事务打算对某个资源（如表或页）加什么类型的锁。它不会阻止其他事务对资源加锁，只是为了协调其他事务对资源的锁定行为。

5. 共享锁（Shared Lock）和排他锁（Exclusive Lock）： 共享锁用于防止其他事务对资源进行写操作，但允许其他事务同时持有共享锁进行读操作；排他锁用于防止其他事务对资源进行读或写操作。

这些锁的作用是保护数据的一致性和完整性，确保在并发访问时数据不会被破坏或丢失。不同的锁适用于不同的场景，需要根据实际情况进行选择和使用。

介绍下InnoDB的锁机制？

在InnoDB中，锁可以分为两种级别，一种是共享锁（S锁），另一种是排他锁（X锁）。

共享锁&排他锁

共享锁又称为读锁，由读取操作创建。其他用户可以并发读取数据，但直到所有共享锁都被释放之前，任何事务都无法对数据进行修改（获得数据上的排他锁）。

如果事务T对数据A加上共享锁后，其他事务只能对A再加共享锁，而不能加排他锁。获得共享锁的事务只能读取数据，而不能修改数据。

SELECT ... LOCK IN SHARE MODE;

在查询语句后添加LOCK IN SHARE MODE，MySQL会为查询结果中的每行加上共享锁。只有当没有其他线程对查询结果集中的任意行使用排他锁时，才能成功获取共享锁；否则将被阻塞。其他线程可以读取已经被加了共享锁的表，且这些线程将读取相同版本的数据。

排他锁又称为写锁，一旦事务T对数据A加上排他锁，其他事务就无法再对A加任何类型的锁。获得排他锁的事务既可读取数据，又可修改数据。

SELECT ... FOR UPDATE;

除了S锁和X锁之外，InnoDB还有另外两种锁，分别是IX锁和IS锁，这里的"I"代表着"Intention"，即意向锁。IX即意向排他锁，IS即意向共享锁。

在查询语句后添加FOR UPDATE，MySQL会对查询命中的每条记录都加排他锁（如果有索引，则通过索引加锁；如果没有索引，则会锁定整个表）。只有当没有其他线程对查询结果集中的任何一行使用排他锁时，才能成功申请排他锁；否则将被阻塞。

意向锁

在MySQL的InnoDB引擎中，支持多种锁级别，包括行级锁和表级锁。当多个事务需要访问共享资源时，如果每个事务都直接请求锁，可能会导致彼此相互阻塞，甚至引发死锁。

举个例子：

事务A对表Table1中的某一行加上了行级锁，这导致该行只能读取而不能修改。与此同时，事务B试图申请对Table1的表级锁。如果事务B成功获取表级锁，那么它就能修改表中的任意一行记录，从而引发冲突。

为解决这一问题，事务B在申请Table1的表级锁时，需要先检查是否有其他事务已经加了行级锁。然而，事务B无法简单地遍历表中所有数据逐行判断是否已被锁定，这样效率太低了。

为了解决这一问题，MySQL引入了意向锁机制。意向锁作为一种锁机制，在数据库管理系统中旨在协调不同锁粒度（如行级锁和表级锁）之间的并发问题。（对于同一锁粒度内的并发问题，如多个行级锁之间的冲突，则通过行级互斥锁来解决。）

注意：

1. 意向锁并非直接锁定资源，而是用于通知其他事务，以防止它们在相同资源上设置不兼容的锁。

2. 意向锁不是由用户直接请求的，而是由MySQL系统管理的。

当一个事务请求获取行级锁或表级锁时，MySQL会自动获取相应表的意向锁。这样一来，其他事务在请求表锁时，可以先通过该意向锁探知是否有已经加锁，并根据意向锁的类型（意向共享锁/意向排它锁）判断自身是否可获取锁。这种方式在不阻塞其他事务的情况下，为当前事务锁定资源。

意向锁有两种类型：意向共享锁和意向排它锁。

1. 意向共享锁：代表事务打算对资源设置共享锁（读锁）。通常用于暗示事务打算读取资源，不希望在读取时有其他事务设置排它锁。

2. 意向排它锁：代表事务打算对资源设置排它锁（写锁）。这表明事务计划修改资源，不希望其他事务同时设置共享或排它锁。

意向锁是表级锁，在触发意向锁的事务提交或回滚后会释放。

以下是MySQL官网上给出的这几种锁之间的冲突关系：

https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html

记录锁

记录锁（Record Lock）是一种加在索引记录上的锁，用于保护特定行数据的完整性。例如，对于语句 SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE;，将会对满足条件 c1=10 的记录加锁，以防止其他事务对该行进行插入、更新或删除操作。

尽管记录锁通常被称为行级锁，但需要特别注意的是，它实际上锁定的是索引记录而非数据行本身。此外，记录锁仅限于锁定索引。

当表中不存在索引时该如何处理？InnoDB 引擎会自动创建一个隐藏的聚簇索引，并使用该索引进行记录锁定。

★

若表中未定义主键，MySQL会默认选择一个唯一的非空索引作为聚簇索引。若不存在适用的非空唯一索引，则会创建一个隐藏的主键（row_id）作为聚簇索引。

关于记录锁的加锁原则。感兴趣的小伙伴一键三连。后续可以出一片文章。

插入记录锁

插入意向锁是一种由插入操作在行插入之前设置的间隙锁。这种锁表明了插入的意图，以这样一种方式，如果多个事务尝试插入到同一索引间隙但不在间隙内的相同位置，则它们不需要相互等待。

举例来说，假设存在索引记录的值为4和7。当不同事务分别尝试插入值为5和6时，它们会在获取插入行的独占锁之前，各自使用插入意向锁锁定4和7之间的间隙。由于它们插入的行并不冲突，因此它们不会相互阻塞。然而，如果它们都试图插入6，那么就会发生阻塞情况。

AUTO-INC 锁

AUTO-INC 锁是一种特殊的表级锁，由向包含 AUTO_INCREMENT 列的表插入数据的事务所获取。在最简单的情况下，如果一个事务正在向表中插入值，其他任何事务都必须等待，以便执行它们自己的插入操作，这样第一个事务插入的行就会接收到连续的主键值。

innodb_autoinc_lock_mode 变量控制用于自增锁定的算法。它允许你在可预测的自增值序列和插入操作的最大并发性之间进行权衡。

在MySQL 5.1之前，AUTO-INC锁是一种表级锁。

百亿级数据存储架构

咱们的生产需求上，百亿级数据存储架构， 一般来说，需要具备以下多种能力/多种形式的异构存储架构：

• 高并发的在线ACID事务 （分库分表）

• 高并发的在线搜索 （倒排表副本）

• 海量数据的离线处理 （高可用+全量副本）

• 冗余表双写能力 （不同业务维度的副本）

其中，上面的冗余表双写能力， 也就是 高并发的 多业务维度 在线ACID 事务处理能力，比如在海量订单场景：

• 用户维度的在线ACID 事务订单处理能力，需要进行用户维度的分库分表。

• 商家维度的在线ACID 事务订单处理能力，需要进行商家维度的分库分表。

如果不需要 不同业务维度的 在线ACID 事务订单处理能力，那么冗余表双写能力 这个是可选项。

这是引入这么多的副本，有好处，也有坏处：

• 好处是满足各种各样的处理要求

• 坏处是我们要维护多个副本之间的数据一致。

百亿级数据存储架构，多副本之间的数据一致如何实现？

便于商品的聚合搜索，高速搜索，采用两大优化方案：

• 把商品数据冗余存储在Elasticsearch中，实现高速搜索

• 把商品数据冗余存储在redis 中，实现高速缓存

既然有了多个副本，那么，如何保持很高的数据一致性？

比如：

• 要求 mysql 与 es 做到秒级别的数据同步。

• 要求 mysql 与 redis 做到秒级别的数据同步。

• 要求 mysql 与 hbase 做到秒级别的数据同步。

接下来，以 mysql 与 es 的数据一致，作为业务场景进行分析， 其他的场景比如mysql 与 redis 的数据一致性方案，都是差不多的。

方案一：同步双写

同步双写是一种最为简单的方式，在将数据写到 MySQL 时，同时将数据写到 ES。

同步双写优点：

这种方式简单粗暴，实时写入能做到秒级。

同步双写缺点：

• 业务耦合，这种方式代码侵入性强，商品的管理中耦合大量数据同步代码，要在之前写 mysql 的地方加写 es 的代码。以后写 mysql 的地方也要加写 es 的代码。

• 影响性能，写入两个存储，响应时间变长，本来 MySQL 的性能不是很高，再加一个 ES，系统的性能必然会下降。

• 不便扩展：搜索可能有一些个性化需求，需要对数据进行聚合，这种方式不便实现

• 高风险：存在双写失败丢数据风险

方案2：异步双写

同步操作性能低，异步性能高。异步双写，分为两种：

• 使用内存队列（如阻塞队列）异步

• 使用消息队列进行异步

方案2.1 使用内存队列（如阻塞队列）异步

先把商品数据写入DB后，然后把 数据写入 BlockingQueue 阻塞队列。消费线程异步从 drain 数据，batch 写入 ElasticSearch, 保证数据一致性。

方案2.2 使用消息队列（如阻塞队列）异步

如果内存队列里边数据丢失，那么es 当中的数据和DB就不一致了，如何解决呢？

• 方式1：定期同步 db数据到 es ，同步周期一般比较长，这里有比较长时间的不一致

• 方式2：保证队列的可靠性，使用高可靠消息队列

生产场景中，一般会有一个搜索服务，由搜索服务去订阅商品变动的消息，来完成同步。

异步双写优点：

• 性能高；

• 不易出现数据丢失问题，主要基于 MQ 消息的消费保障机制，比如 ES 宕机或者写入失败，还能重新消费 MQ 消息；

• 多源写入之间相互隔离，便于扩展更多的数据源写入。

异步双写缺点：

• 硬编码问题，接入新的数据源需要实现新的消费者代码；

• 系统复杂度增加，引入了消息中间件；

• MQ是异步消费模型，用户写入的数据不一定可以马上看到，造成延时。

方案三：定期同步

为了保证 DB和ES /HBase 数据一致性，包括两个方面：

• 增量数据一致性

• 全量数据一致性

为了保证 DB和ES /HBase 的全量数据一致性， 往往需要进行定期的全量数据同步。

数据增量数据，很少，并且，一致性要求不高，那么可以把增量数据一致性行的 同步双写、异步双写去掉。

定期同步优点：

实现比较简单

定期同步缺点：

• 实时性难以保证

• 对存储压力较大

当然，增量数据，可以考虑用定时任务来处理：

1. 数据库的相关表中增加一个字段为 timestamp 的字段，任何 CURD 操作都会导致该字段的时间发生变化；

2. 原来程序中的 CURD 操作不做任何变化；

3. 增加一个定时器程序，让该程序按一定的时间周期扫描指定的表，把该时间段内发生变化的数据提取出来；

4. 逐条写入到 ES 中。

方案四：数据订阅

如果要提高实时性，又要低入侵, 可以利用 MySQL 的 Binlog 来进行同步。

MySQL通过binlog订阅实现主从同步，canal Server 是一个伪装的slave节点，接收到binlog日志后，发送到MQ, 其他的 存储消费 MQ里边 的binlog日志，实现数据订阅。

这种方式和异步双写比较像，但是有两个优点：

• 第一降低了商品服务的入侵性，

• 第二数据的实时性更好。

所以使用数据订阅：

• 优点：

  • 业务入侵较少

  • 实时性较好

至于数据订阅框架的选型，主流的大体上是这些：

	Cancal	Maxwell	Python-Mysql-Rplication
开源方	阿里巴巴	Zendesk	社区
开发语言	Java	Java	Python
活跃度	活跃	活跃	活跃
高可用	支持	支持	不支持
客户端	Java/Go/PHP/Python/Rust	无	Python
消息落地	Kafka/RocketMQ 等	Kafka/RabbitNQ/Redis 等	自定义
消息格式	自定义	JSON	自定义
文档详略	详细	详细	详细
Boostrap	不支持	支持	不支持

如果不允许有秒级延迟的场景，不能使用这种架构。

方案五：冗余表的同步双写/异步双写

为什么要有冗余表？

当t_order表达到500万条或2GB时需要考虑水平分表，进行水平分表需要根据某个列进行分割，假设根据userId分割。用户查询自己的订单携带着userId，因此能够定位到具体哪张表。

而商家查询者自己店铺的订单，没办法确定userId，只能访问一遍所有的分表再合并结果，效率非常低。为了加快商家端的查询，可以冗余一份订单表，这份冗余表根据merchantId切分，商家访问冗余表，效率就很好。

这是引入冗余表的好处，坏处是我们要维护普通表和冗余表的数据一致。

冗余表的同步双写实现方案

更新t_order的操作要执行两次，一次更新普通表，一次更新冗余表，写两次。优点：

• 实现简单，由一次写变为两次写

• 容易维护数据的一致性

缺点：

• 代码冗余，第二次写跟第一次写的代码类似，而且每个更新的地方都要写两次

• 请求处理时间变长

冗余表的异步双写实现方案：

更新请求过来，写一次数据库，再发送一条消息到消息中间件，返回响应。消费者拉取消息进行写操作。优点：

• 处理时间是单次写

缺点

• 较复杂，引入了消息中间件

• 不容易维护数据的一致性

方案六：ETL数据同步

一致性分为两种：

• 增量一致性: 前面的的双写方案，主要是保持增量数据的一致性。

• 全量一致性: ETL数据同步主要用于同步全量数据。

MySQL数据全量同步到Redis、MySQL同步到hbase、MySQL同步到es、或机房同步、主从同步等，都可以考虑使用etl工具。

什么是 etl 工具呢？

ETL，是英文 Extract-Transform-Load 的缩写，用来描述将数据从来源端经过抽取（extract）、转换（transform）、加载（load）至目的端的过程。ETL一词较常用在数据仓库，但其对象并不限于数据仓库。ETL是构建数据仓库的重要一环，用户从数据源抽取出所需的数据，经过数据清洗,最终按照预先定义好的数据仓库模型，将数据加载到数据仓库中去。

常用的etl工具有：databus、canal （有etl 的部分功能）、otter 、kettle 等。

下面以 databus 为例，介绍一下。

Databus 是一个低延迟、可靠的、支持事务的、保持一致性的数据变更抓取系统。由 LinkedIn 于 2013 年开源。Databus 通过挖掘数据库日志的方式，将数据库变更实时、可靠的从数据库拉取出来，业务可以通过定制化 client 实时获取变更并进行其他业务逻辑。

特点：

• 多数据源：Databus 支持多种数据来源的变更抓取，包括 Oracle 和 MySQL。

• 可扩展、高度可用：Databus 能扩展到支持数千消费者和事务数据来源，同时保持高度可用性。

• 事务按序提交：Databus 能保持来源数据库中的事务完整性，并按照事务分组和来源的提交顺寻交付变更事件。

• 低延迟、支持多种订阅机制：数据源变更完成后，Databus 能在毫秒级内将事务提交给消费者。同时，消费者使用D atabus 中的服务器端过滤功能，可以只获取自己需要的特定数据。

• 无限回溯：对消费者支持无限回溯能力，例如当消费者需要产生数据的完整拷贝时，它不会对数据库产生任何额外负担。当消费者的数据大大落后于来源数据库时，也可以使用该功能。

再看看 Databus 的系统架构。

Databus 由 Relays、bootstrap 服务和 Client lib 等组成，Bootstrap 服务中包括 Bootstrap Producer 和 Bootstrap Server。

• 快速变化的消费者直接从 Relay 中取事件；

• 如果一个消费者的数据更新大幅落后，它要的数据就不在 Relay 的日志中，而是需要请求 Bootstrap 服务，返回的将会是自消费者上次处理变更之后的所有数据变更快照。

开源地址：https://github.com/linkedin/databus

100亿级任务调度篇：从0到1, 从入门到 XXLJOB 工业级使用

什么是索引下推？

索引下推也被称为索引条件下推 （Index Condition Pushdown）ICP。

MySQL服务层负责SQL语法解析、生成执行计划等，并调用存储引擎层去执行数据的存储和检索。

索引下推的下推其实就是指将部分上层（服务层）负责的事情（条件过滤），交给了下层（引擎层）去处理。

在没有使用ICP的情况下，MySQL的查询：

1. 存储引擎读取索引记录；

2. 根据索引中的主键值，定位并读取完整的行记录；

3. 存储引擎把记录交给Server层去检测该记录是否满足WHERE条件。

使用ICP的情况下，查询过程：

1. 存储引擎读取索引记录

2. 判断条件部分能否用索引中的列来做检查，条件不满足，则处理下一行索引记录；

3. 条件满足，使用索引中的主键去定位并读取完整的行记录（就是所谓的回表）；

4. 存储引擎把记录交给层，层检测该记录是否满足条件的其余部分。

有了索引下推的优化，在满足一定条件下，存储 引擎层会在回表查询之前对数据进行过滤，可以减少存储引擎回表查询的次数。

实战：

假如有一张表user, 表有四个字段 id,name,level,tool

建立联合索引（name,level）

匹配姓名第一个字为“大”，并且level为1的用户，sql语句为:

select * from user where name like "大%" and level = 1；

在5.6之前，执行流程是如下图

根据前面说的“最左前缀原则”，该语句在搜索索引树的时候，只能匹配到名字第一个字是‘大的记录，接下来是怎么处理的呢？当然是ID 1 、ID4开始，逐个回表，到主键索引上找出相应的记录，再比对level这个字段的值是否符合。

图 1 中，在 (name,level) 索引里，只是按顺序把“name 第一个字是’大’”的记录一条条取出来回表。因此，需要回表 2次。

MySQL 5.6引入了索引下推优化，可以在索引遍历过程中，对索引中包含的字段先做判断，过滤掉不符合条件的记录，减少回表字数。

下面图1、图2分别展示这两种情况。

5.6及之后，执行流程图如下

图 2 跟图 1 的区别是，InnoDB 在 (name,level) 索引内部就判断了 level是否等于1，对于不等于1 的记录，直接判断并跳过。在我们的这个例子中，只需要对ID 1 、ID4 这两条记录回表取数据判断，就只需要回表 1 次。使用索引下推后由两次回表变为一次，提高了查询效率。

总结

如果没有索引下推优化（或称ICP优化），当进行索引查询时，首先根据索引来查找记录，然后再根据where条件来过滤记录；在支持ICP优化后，MySQL会在取出索引的同时，判断是否可以进行where条件过滤再进行索引查询。也就是说提前执行where的部分过滤操作，在某些场景下，可以大大减少回表次数，从而提升整体性能

InnoDB如何解决脏读、不可重复读和幻读的？

在InnoDB中，采用MVCC解决了脏读和不可重复读的问题，而结合MVCC和间隙锁则解决了幻读。

脏读的解决

脏读是指一个事务可以读取另一个事务未提交的数据，造成数据不一致。在读已提交（Read Committed）隔离级别下，事务只能读取到其他事务已经提交的数据版本。因此，如果一个事务在读取数据时，另一个事务已经修改了这些数据但尚未提交，那么读取事务将不会看到这些未提交的更改。

在执行读操作时，当事务处于“读已提交”隔离级别下，InnoDB会获取当前最新的全局事务ID，代表当前时刻所有已提交事务的最新状态。InnoDB会检查每个数据行的版本，如果该版本由一个小于或等于当前事务ID的事务修改，并且该事务已提交，则这个版本是可见的。这确保了事务只能看到在其开始之前已经提交的数据版本。

不可重复读的解决

不可重复读指一个事务读取同一行数据两次，但在两次读取之间另一个事务修改了该行数据，导致两次读取结果不同。InnoDB通过MVCC来解决不可重复读的问题。在Repeatable Read（重复读）隔离级别下，采用快照读进行数据读取时，仅在第一次读取时生成一个Read View，后续的所有快照读都使用相同快照，因此不会发生不可重复读的问题。

幻读的解决

对于幻读问题，在InnoDB的Repeatable Read（重复读）隔离级别中，基于MVCC和间隙锁在一定程度上可以避免幻读，但无法完全避免。当一个事务执行当前读时，可能会导致幻读的发生。

分库分表

在大规模数据应用系统中，随着数据量的增长，单一数据库可能无法满足高并发、高可用、高扩展性等需求。分库分表作为一种数据库水平扩展的解决方案，逐渐成为了解决这类问题的主流方案之一。

随着用户量的激增和时间的堆砌，存在数据库里面的数据越来越多，此时的数据库就会产生瓶颈，出现资源报警、查询慢等场景。首先单机数据库所能承载的连接数、I/O及网络的吞吐等都是有限的，所以当并发量上来了之后，数据库就渐渐顶不住了。再则，如果单表的数据量过大，查询的性能也会下降。因为数据越多 B+ 树就越高，树越高则查询 I/O 的次数就越多，那么性能也就越差。

因为上述的原因，不得已就得上分库分表了。把以前存在一个数据库实例里的数据拆分成多个数据库实例，部署在不同的服务器中，这是分库。把以前存在一张表里面的数据拆分成多张表，这是分表。一般而言：

• 分表：是为了解决由于单张表数据量多大，而导致查询慢的问题。大致三、四千万行数据就得拆分，不过具体还是得看每一行的数据量大小，有些字段都很小的可能支持更多行数，有些字段大的可能一千万就顶不住了。

• 分库：是为了解决服务器资源受单机限制，顶不住高并发访问的问题，把请求分配到多台服务器上，降低服务器压力。

分库分表分为四个类型，垂直分表、垂直分库、水平分表、水平分库。

垂直分表

1）简单来说就是将原本的一张表切割成多张表。举个例子：有张 student 表存储着学生家庭背景、学生入学宣言（500字）、学生信息，现在将其进行垂直分表，可以分为 学生基本信息表、学生入学宣言表、学生家庭背景表，将其进行切割。

2）垂直分表一般是将不常用的字段单独放在一张表、将大字段分一张表、把经常需要同时查出来的信息放一张表。这样做可以冷数据和热数据分开提高查询效率。

垂直分库

1）把一个数据库里面的多个表，按照功能，分成多个数据库存放。举个例子：一个数据库有很多张表，用户表、商品表、订单表等，那么可以根据功能属性进行垂直分库，将用户表等信息放到存放用户信息的数据库，将商品表、订单表存放到与商品订单有关的商品订单库。

2）这样做的好处就是将数据负载分散到不同的数据库上，从而提高系统的性能和扩展性、降低单一数据库的复杂度。

水平分表

1）在同一个数据库中，有几个相同表，里面的数据是不一样的，但表结构是一样的，数据按照固定的规则选择数据表存放，如：商品表1、商品表2。

2）提高了读写性能，减少了单表的压力、可弹性增加存储容量只需要增加一个表就行。

水平分库

1）相同的表结构复制一份分到另一个库中，每个库的表结构是一样的，但是数据是不一样的。

2）这样做能在大数量的情况下提高读写性能，因为减少了单一数据库的读写压力。

3）能提高存储容量。可以通过增加或减少数据库进行弹性伸缩。

4）提高容错性。当一个数据库故障了，别的数据库还能正常运行，只影响小部分数据查询。

怎么分库，怎么分表的

分库

一般分库都是按照业务划分的，比如订单库、用户库等等。有时候会针对一些特殊的库再作切分，比如一些活动相关的库都做了拆分。因为做活动的时候并发可能会比较高，怕影响现有的核心业务，所以即使有关联，也会单独做拆分。

分表

对于分表分为了两种情况，垂直分表、水平分表：

1）垂直分表就是把一些不常用的大字段剥离出去。

用户名是很常见的搜索结果，性别和年龄占用的空间又不大，而地址和个人简介占用的空间相对而言就较大，我们都知道一个数据页的空间是有限的，把一些无用的数据拆分出去，一页就能存放更多行的数据。内存存放更多有用的数据，就减少了磁盘的访问次数，性能就得到提升。

2）水平分表，则是因为一张表内的数据太多了，上文也提到了数据越多 B+ 树就越高，访问的性能就差，所以进行水平拆分。

其他补充

分库分表是一种数据库分片（Sharding）的实现方式，用于解决单一数据库在数据量增大时性能瓶颈的问题。在分库分表中，数据会被分散存储到多个数据库实例（分库）和表中（分表），以达到分布式存储和查询的目的。

通常，分库分表的策略会根据业务需求和数据库性能做出选择。例如，可以按照用户 ID、地理位置、时间等关键字段将数据分散存储到不同的数据库实例或表中。这样做的好处包括：

1. 水平扩展: 可以通过增加数据库实例和表来扩展系统的存储容量和处理能力，从而应对数据量和并发访问的增长。

2. 提高性能: 分散数据存储和查询操作可以减轻单一数据库的压力，提高系统的并发处理能力和响应速度。

3. 容灾备份: 分库分表可以使得系统在某个数据库实例或表发生故障时，不会影响整个系统的运行，提高系统的容灾性。

然而，分库分表也带来了一些挑战，包括跨库事务处理、数据一致性、查询跨库等问题，需要在设计和实现时加以考虑和解决。

浅聊MVCC

MVCC，即多版本并发控制（Multiversion Concurrency Control），类似于数据库锁，是一种优雅的并发控制方案。在数据库环境中，数据操作主要包括读取和写入两种操作，在并发情境下，可能出现以下三种情况：

• 读-读并发

• 读-写并发

• 写-写并发

众所周知，在读取操作时没有写入操作的情况下，并发读取不会引发问题；而写入操作并发时，常常会通过加锁的方式来处理。而针对读取-写入并发的场景，则可通过MVCC机制来解决。

快照读和当前读

要深入了解MVCC机制，其中最关键的一个概念就是快照读。所谓快照读，即读取快照数据，即在生成快照时刻的数据。比如我们常用的普通SELECT语句在无锁情况下就属于快照读。例如：

SELECT * FROM xx_table WHERE ...

与快照读相对应的另一个概念是当前读，当前读即获取最新的数据。因此，加锁的SELECT操作或进行数据的增删改都属于当前读。例如：

SELECT * FROM xx_table LOCK IN SHARE MODE;

SELECT * FROM xx_table FOR UPDATE;

INSERT INTO xx_table ...

DELETE FROM xx_table ...

UPDATE xx_table ...

可以理解为：快照读是MVCC实现的基础，而当前读则是悲观锁实现的基础。

快照读所读取的快照数据来自于何处？换言之，这些快照数据存储在何处？

UndoLog

undo log是 MySQL 中一种重要的事务日志之一。顾名思义，undo log 是用于回滚操作的日志。在事务提交之前，MySQL会将更新前的数据记录到 undo log 日志文件中。当需要回滚事务或者发生数据库崩溃时，可以通过 undo log 进行数据回退。

在这个过程中提到的 "更新前的数据" 存储在undo log中，即我们之前提及的快照。因此，这正是许多人认为 Undo Log 是实现 MVCC 的重要工具的原因之一。

在同一时刻，一条记录可能会被多个事务操作。因此，undo log 可能会包含一条记录的多个快照。当需要进行快照读取时，就要考虑应该读取哪个快照。这时候就需要利用其他相关信息来做出决定。

行记录的隐式字段

实际上，在数据库的每一行记录中，除了保存我们自定义的字段之外，还包含一些重要的隐式字段：

• db_row_id：隐式主键。如果表没有创建主键，将使用该字段创建聚簇索引。

• db_trx_id：最后一次修改该记录的事务ID。

• db_roll_ptr：回滚指针，指向记录的上一个版本，在本质上指向Undo Log中的前一个版本的快照地址。

由于每次记录更改之前都会先将一个快照存储到undo log中，这些隐式字段也会与记录一起保存在undo log中。因此，每个快照中都包含一个db_trx_id字段，表示最后一次修改该记录的事务ID，以及一个db_roll_ptr字段，指向前一个快照的地址。（db_trx_id和db_roll_ptr是重点，将在后续中用到）

因此，这样就形成了一个快照链表：

有了undo log，又有了几个隐式字段，我们好像还是不知道具体应该读取哪个快照，那怎么办呢？

Read View

此时，Read View 登场，它的主要作用是解决可见性问题，即确定当前事务应该查看哪个快照，而不应查看哪个快照。在 Read View 中具有几个重要属性：

• trx_ids：系统当前未提交的事务ID列表。

• low_limit_id：应分配给下一个事务的ID值。

• up_limit_id：未提交事务中最小的事务ID。

• creator_trx_id：创建该 Read View 的事务ID。

每次启动一个事务，都会获得一个递增的事务ID。通过ID的大小，我们可以确定事务的时间顺序。

其实原则比较简单，那就是事务ID大的事务应该能看到事务ID小的事务的变更结果，反之则不能！举个例子：

假设当前存在一个事务3想要进行快照读取某条记录，它会首先创建一个Read View，并记录所有当前未提交事务的信息。例如，up_limit_id = 2，low_limit_id = 5，trx_ids= [2,4,5]，creator_trx_id= 3

前文提到，每条记录都包含一个隐式字段db_trx_id，记录对该记录进行最新修改的事务ID，例如db_trx_id = 3；

接下来，数据库将检查此记录的db_trx_id与Read View进行可见性比较。

• 若db_trx_id < up_limit_id，则意味着在Read View中所有未提交事务创建之前，事务ID为3的操作已经提交，并在此期间没有新的提交。因此，对当前事务而言，此记录应该是可见的。

• 若db_trx_id > low_limit_id，则表示事务ID为3的操作是在Read View中所有未提交事务创建之后才提交的，也就是在当前事务开启之后，有其他事务修改了数据并提交。因此，这条记录对当前事务来说是不可见的。（不可见时的处理将在后文讨论）

另一种情况是，up_limit_id < db_trx_id < low_limit_id。在此情况下，将db_trx_id与Read View中的trx_ids逐一比较。

• 若db_trx_id在trx_ids列表中，表示在当前事务开启时，某些未提交事务对数据进行了更改并提交，因此，对当前事务来说，此记录应该是不可见的。

• 若db_trx_id不在trx_ids列表中，表示在当前事务开启之前，其他事务对数据进行了修改并提交，所以对当前事务来说，该记录是可见的。

因此，在读取记录时，经过上述判断，若记录对当前事务可见，则直接返回。若不可见，则需要利用undo log。

当数据的事务ID与Read View规则不符时，需要从undo log中获取数据的历史快照，然后使用数据快照的事务ID与Read View进行可见性比较。如果找到一条快照，则返回数据；否则，返回空。

因此，在InnoDB中，MVCC机制通过Read View和Undo Log相结合来实现。Undo Log保存了历史快照，而Read View则确定了哪一个具体的快照对当前操作是可见的。

MVCC和可重复读

根据不同的事务隔离级别，在InnoDB中，获取Read View的时机有所不同。在可重复读隔离级别下，每次查询都会重新获取一次Read View，而在读已提交隔离级别下，只有在事务的第一次查询时获取一次Read View。

因此，在可重复读隔离级别下，由于MVCC机制的存在，能够有效解决不可重复读的问题。因为在可重复读隔离级别中，只在第一次查询时获取一次Read View，从而天然消除了可能导致重读问题的可能性。

为什么不使用外键吗？

速度比一切都重要

首先，速度，这是生命。在互联网海洋的激流中，哪怕是一秒钟的延迟都可能会被竞争对手赶超。外键约束在保证数据一致性的同时，也带来了额外的开销。在高并发场景下，这种开销可能会成为性能瓶颈。

解耦，一种生存之道

其次，阿里巴巴的系统之间高度解耦，为了保持独立性和灵活性，他们更喜欢在服务层面控制依赖关系，而不是在数据库层面。这样一来，系统解耦度更高，更易于扩展。

不用外键，那该咋办？

那么，阿里巴巴是如何在没有外键的情况下保证数据完整性的呢？答案是，他们在应用层面实现数据完整性的校验，通过代码逻辑来实现数据的一致性，而不是依赖数据库层面的外键约束。

还有一种方法是通过数据治理来保证数据一致性，例如使用事件驱动的方式来处理数据关联和完整性问题。

外键，用还是不用？

那么，对于我们普通的开发者来说，外键到底该用还是不用呢？这其实没有绝对的答案，因为这取决于你的具体需求和场景。在一些小规模的系统或者对数据一致性要求极高的场景下，外键仍然是一个有用的工具。但在大规模、高并发、需要高可扩展性的系统中，我们可能需要像阿里巴巴一样，寻找替代方案。

SQL实战：分组求和、累加求和、滑动求和

分组求和

问题1：统计各区域累计销售额。

解答：用聚合函数SUM+GROUP BY 即可实现。

问题2：统计各区域每月销售额占累计销售额百分比。

方式一：可以用开窗函数SUM() OVER()实现。

数据分析工作中常用的3类SQL开窗函数详解

SELECT                          -- 各大区当月销售额占总销售额比例                amount/SUM(amount) OVER(partition by region) as amount_ratio            FROM                data_learning.sales_amount            ;

问题3：统计各区域累计销售额占其占总销售额百分比。

SELECT  DISTINCT  -- 各大区当月销售额占总销售额比例  SUM(amount) OVER(partition by region)/SUM(amount) OVER() as amount_ratio FROM  data_learning.sales_amount ;          

累加求和

问题1：统计各区域在每个月的累加销售额。

解答：这里累加求和需要按月份升序，比如2023-01的累加销售额为当月销售额33780、2023-02月累加销售额=2023-01销售额33780+2023-02销售额14500=48280，依此类推。SQL上可以用开窗函数SUM() OVER()实现。

SELECT  -- 各大区每月累加销售额  ,SUM(amount) OVER(partition by region ORDER BY year_month_id) as month_acc_amount FROM  data_learning.sales_amount ;     

滑动求和

滑动求和在日常业务数据分析和数据挖掘建模时构造特征时很有用。

问题1：统计各区域在每个月的近6个月累计销售额。

解答：滑动求和仍然使用开窗函数SUM() OVER()实现。前文中的分组求和、累加求和已经为这个问题做了铺垫，比如“问题：统计各区域在每个月的累加销售额。”是按区域分组，按月份升序后，统计每个月及其之前行数的销售额之和。这里要限制每个月及其之前行数一直保持6行，可以通过OVER函数中的range between and指定窗口大小。

• N PRECEDING：PARTITION BY分组ORDER BY后 前n行

• N FOLLOWING：PARTITION BY分组ORDER BY 后n行  

-- 含统计月：between 5 preceding and 0 following/between 5 preceding and current row            
SELECT            
    region            
    ,year_month_id            
    -- 各大区当月销售额            
    ,amount            
    -- 各大区每月累加销售额            
    ,SUM(amount) OVER(partition by region ORDER BY month_id range between 5 preceding and 0 following) as acc_amount_6m            
FROM            
(            
SELECT            
    region            
    ,year_month_id            
    ,EXTRACT(YEAR FROM DATE(CONCAT(year_month_id,'-01'))) AS year_id            
    ,EXTRACT(MONTH FROM DATE(CONCAT(year_month_id,'-01'))) AS month_id            
    ,amount            
FROM            
    data_learning.sales_amount            
)a            
;            
           
-- 不含统计月：between 6 preceding and 1 preceding 或者 between 6 preceding and -1 following            
SELECT            
    region            
    ,year_month_id            
    -- 各大区当月销售额            
    ,amount            
    -- 各大区每月累加销售额            
    ,SUM(amount) OVER(partition by region ORDER BY month_id range between 6 preceding and 1 preceding) as acc_amount_6m            
FROM            
(            
SELECT            
    region            
    ,year_month_id            
    ,EXTRACT(YEAR FROM DATE(CONCAT(year_month_id,'-01'))) AS year_id            
    ,EXTRACT(MONTH FROM DATE(CONCAT(year_month_id,'-01'))) AS month_id            
    ,amount            
FROM            
    data_learning.sales_amount            
)a            
;                       

不含统计月：

当往前累加求和，且不包含统计月时，首月会出现null值。这里开窗函数中的排序字段为什么不使用year_month_id，而是使用处理后的month_id？

用year_month_id进行排序运行SQL，会发现SQL运行报错。ORDER BY 后面的字段数据类型需要是数字类型或者日期时间等类型的，而year_month_id是字符串类型的字段，不符合语法要求，因此我们对year_month_id字段进行了处理，提取出了其中的月份id形成数字类型字段。

当然也可以用日期类型的，如果开窗函数内ORDER BY 的字段是日期类型，但仍使用range between 6 preceding and 1 preceding限制窗口大小，会报错。所以当ORDER BY的字段是日期类型时，窗口范围限制需要加上INTERVAL关键字，解答如下：

-- 含统计月：range between INTERVAL 6 MONTH PRECEDING AND CURRENT ROW            
SELECT            
    region            
    ,year_month_id            
    -- 各大区当月销售额            
    ,amount            
    -- 各大区每月累加销售额            
    ,SUM(amount) OVER(partition by region ORDER BY month_date range between INTERVAL 6 MONTH PRECEDING AND CURRENT ROW ) as acc_amount_6m            
FROM            
(            
SELECT            
    region            
    ,year_month_id            
    ,EXTRACT(YEAR FROM DATE(CONCAT(year_month_id,'-01'))) AS year_id            
    ,EXTRACT(MONTH FROM DATE(CONCAT(year_month_id,'-01'))) AS month_id            
    ,DATE(CONCAT(year_month_id,'-01')) as month_date            
    ,amount            
FROM            
    data_learning.sales_amount            
)a            
;            
           
-- 不含统计月：range between INTERVAL 6 MONTH PRECEDING AND INTERVAL 1 MONTH PRECEDING            
SELECT            
    region            
    ,year_month_id            
    -- 各大区当月销售额            
    ,amount            
    -- 各大区每月累加销售额            
    ,SUM(amount) OVER(partition by region ORDER BY month_date range between INTERVAL 6 MONTH PRECEDING AND INTERVAL 1 MONTH PRECEDING ) as acc_amount_6m            
FROM            
(            
SELECT            
    region            
    ,year_month_id            
    ,EXTRACT(YEAR FROM DATE(CONCAT(year_month_id,'-01'))) AS year_id            
    ,EXTRACT(MONTH FROM DATE(CONCAT(year_month_id,'-01'))) AS month_id            
    ,DATE(CONCAT(year_month_id,'-01')) as month_date            
    ,amount            
FROM            
    data_learning.sales_amount            
)a            

问题2：统计各区域在每个月未来3个月的累计销售额，比如2023-03月统计2023-04、05、06三个月的累计销售额。

-- 不含统计月：between 6 preceding and 1 preceding 或者 between 6 preceding and -1 following            SELECT                region                ,year_month_id                -- 各大区当月销售额                ,amount                -- 各大区每月累加销售额                ,SUM(amount) OVER(partition by region ORDER BY month_id range between 1 following and 3 following) as acc_amount_f3m            FROM            (            SELECT                region                ,year_month_id                ,EXTRACT(YEAR FROM DATE(CONCAT(year_month_id,'-01'))) AS year_id                ,EXTRACT(MONTH FROM DATE(CONCAT(year_month_id,'-01'))) AS month_id                ,amount            FROM                data_learning.sales_amount            )a            ;