sql 面试 mysql

Mysql进阶技术栈

mengnankkzhou2025-05-212025-07-12

主从同步

1.MySQL 主从架构有什么优缺点？

MySQL Replication 是 MySQL 官方提供的主从同步方案，用于将 MySQL 主库的数据同步到从库中，从库可以供应用程序读取数据。

Replication 是目前 MySQL 使用最多的灾备方案，主要有 3 个作用：

读写分离，写主库读从库。这样大大降低主库的负载，即使主库出现类似锁表之类的情况，也不影响应用读取数据。
实现灾备，当主库发生故障时，可以方便地把从库切换成主库，实现高可用（HA）。
水平扩展，当应用访问量导致数据库 I/O 高时，可以通过水平扩展的方式将降低单机负载，降低磁盘 I/O。

同步原理：

MySQL 通过 binlog 实现同步过程中，会用到 3 个线程：

IO thread: 从库执行 START SLAVE 语句时，会创建一个 IO thread，负责连接主节点，请求更新的 binlog**，接收到 binlog 后写入 relaylog；**
dump thread：主库接收到从库的 binlog 请求后，创建一个 dump thread，把 binlog 同步给从库；
sql thread：读取 relaylog，解析 relaylog 的命令并执行，将数据落库。

在从库上执行 change master 命令，设置要连接主库的用户名、密码、ip、端口以及请求同步的 binlog 中的位置，这个位置包含文件名和binlog offset；

从库执行 start slave 命令，这时会启动上面的 IO thread 和 sql thread，其中 IO thread 负责跟主库建立连接；

主库收到从库的连接请求后，校验用户名密码；

主库校验通过后创建 dump thread，按照从库请求 binlog 的 offset 将 binlog 发给从库；

从库收到主库发送的 binlog 后，将日志写入 relaylog；

sql thread 读取 relaylog，解析出命令后执行。

缺点：

最大的缺点就是主从延迟。

原因：：：：：

从库所在机器性能差，命令执行慢；

从库查询压力大，消耗了大量 CPU 资源，影响了 sql thread 执行；

主库有大事务（比如大表DDL），这个事务里面执行的 sql 比较多，一方面主库需要等待事务执行完成才能写入 binlog，另一方面同步到从库和在从库执行都需要花费很多时间，导致主从延迟；

数据库版本低，在 MySQL 5.6 之前，只支持单线程复制，效率比较低；

表上无主键，主库利用索引更改数据，从库只能用全表扫描。

要解决主备延迟的问题，可以考虑下面方法：

优化业务逻辑，避免使用大事务，或者大事务场景尽量放在业务低峰期执行；
提高从库所在机器的性能；
保障网络性能，避免网络延迟；
引入 semi-sync 半同步复制，配合异步复制。

主从同步的第二个缺点就是数据丢失。

MySQL 有 3 种主从复制方式：

异步复制：主库执行完客户端提交的事务后立即将结果返回给客户端，不关心从库是否同步完成。这种方式很容易发生数据丢失，比如主库的日志还未同步给从库就宕机了，这时需要在从库中选择一个作为新主库，之前未同步完成的数据就丢失了；

全同步复制：主库执行完客户端提交的事务并且等待从库也执行完成数据同步后再把结果返回给客户端。这种方式能够保证不丢失数据，但是数据库的性能会受到影响；

半同步复制：是介于全同步和异步复制的一种方式，主库至少等待一个从库接收 binlog 并成功写入到 relaylog 后给客户端返回结果。主库不需要等待所有从库返回 ACK。

MySQL 中默认采用异步复制，这样很容易导致数据丢失。一个好的方式就是采用 semi-sync 半同步复制插件。不过 semi-sync 存在一个问题，主库写数据到 binlog 后执行 commit，才会给从库同步数据。如果从库还没有返回 ACK，主库发生了宕机，从库还没有写完 relaylog 就被选择为主库，也会发生数据丢失。

MySQL 5.7 引入了增强版半同步复制。主库写入数据到 binlog 后，就给从库进行同步，直到至少一个从库返回给主库 ACK，主库才会进行 commit 操作。

2.MySQL双主架构有什么优缺点？

双主架构是 MySQL 常见的一种架构模式，它的特点是有两个主节点对外提供服务，并且这两个主节点互为主备。今天来学习一下双主架构。

1.双主复制

这种架构的特点配置两个主库，每个主库都提供读写服务，并且这两个主库互为主备。如下图：

在 M1 写入的数据要同步到 M2，在 M2 写入的数据要同步到 M1。这种两个主库同时支持写入，这种架构模式一个明显的优势写入效率高。比如一个应用在不同的城市部署了两个主节点，请求可以就近选择写入数据库。

但这种架构在数据同步时很容易出问题。

案例一：M1 和 M2 同时收到一张表的插入请求，这张表是自增主键，两张表插入后主键相同。这时发生数据同步，这条插入语句在 binlog 里面记录的是 row 格式，同步时发生主键冲突。

MySQL binlog 有三种格式：

STATEMENT：记录的是 SQL 语句本身；

ROW：记录的是数据的变化；

MIXED：STATEMENT 和 ROW 格式的结合，MySQL 会根据 SQL 语句特性选择使用 STATEMENT 还是 ROW 格式。

解决方法：：：

MySQL 5.0 后可以通过设置 auto_increment_increment 和 auto_increment_offset 这两个选项来解决这个问题。

案例二：在 M1 上执行了一条语句，生成 binlog 后发给 M2 进行同步，M2 执行完成后又生成 binlog 同步给 A，导致一条语句循环复制。

这个问题的解决方法是要求 M1 和 M2 的 server id不相同，M1 产生的 binlog 记录 server id 是 M1，M2 执行同步时生成的 binlog 也记录 server id 为 M1。这样同步给 M1 是，M1 判断到 server id 跟自己相同，就丢弃这个日志，不做同步。

案例三：同步过程中会有数据不一致的问题。比如用户 xiaoming 的账户余额是 100。M1 执行了 update 操作把账户余额更新成 150，M2 执行了 update 操作更新成 130。

解决这种数据不一致问题的一个思路是严格划分数据和设置权限，比如案例中小明的所有数据只能在 M1 上操作。

案例四：因为节点发生故障，M1 不能复制了，但是应用可以写数据库，M2 能正常写和复制，这个问题就很难解决了。

解决这个问题，需要给 M1 和 M2 配置从节点，主节点故障后切换到从节点进行工作。

2.主备复制

这个架构模式的特点是双主节点中，同一时刻只有一个主节点提供写服务，另一个主节点只能提供读服务。如下图：

这个架构相当于比单主节点架构多了一个热备，有如下优势：

因为 M1 和 M2 配置对称，切换主备比较容易；
有助于故障转移和恢复；
可以在不影响应用的情况下进行数据库升级和维护；
不用考虑循环复制问题和主备不一致问题。

当然，主备架构也有缺点，那就是写性能不能得到提升。

3.主主架构拥有备库

主主架构中每个主库也可以拥有备库，如下图：

这种配置为每个主库增加了一个备份，可以防止单点故障，同时备库也可以处理读请求，提高数据库整体读效率。

这个架构的缺点是增加了机器成本。

4.环形复制

环形复制架构是 MySQL 集群中拥有多个主库，主库之间形成一个环形，前面一个节点是当前节点的主库，当前节点是前面节点的备库，也是后面一个节点的主库。如下图：

环形复制这种架构其实并不推荐，因为它很难做到故障转移，高可用特性依赖于每个节点不出故障。但是如果一个节点出了故障，去掉这个节点，这个节点产生的 binlog 将一直循环复制下去，因为只有通过这个节点的 server id 才能做出判断停止复制。

3.MySQL主从复制了解吗

MySQL 的主从复制依赖于 binlog ，也就是记录 MySQL 上的所有变化并以二进制形式保存在磁盘上。复制的过程就是将 binlog 中的数据从主库传输到从库上。

这个过程一般是异步的，也就是主库上执行事务操作的线程不会等待复制 binlog 的线程同步完成。

MySQL 集群的主从复制过程梳理成 3 个阶段：

写入 Binlog：主库写 binlog 日志，提交事务，并更新本地存储数据。
同步 Binlog：把 binlog 复制到所有从库上，每个从库把 binlog 写到暂存日志中。
回放 Binlog：回放 binlog，并更新存储引擎中的数据。

具体详细过程如下：

MySQL 主库在收到客户端提交事务的请求之后，会先写入 binlog，再提交事务，更新存储引擎中的数据，事务提交完成后，返回给客户端“操作成功”的响应。
从库会创建一个专门的 I/O 线程，连接主库的 log dump 线程，来接收主库的 binlog 日志，再把 binlog 信息写入 relay log 的中继日志里，再返回给主库“复制成功”的响应。
从库会创建一个用于回放 binlog 的线程，去读 relay log 中继日志，然后回放 binlog 更新存储引擎中的数据，最终实现主从的数据一致性。

在完成主从复制之后，你就可以在写数据时只写主库，在读数据时只读从库，这样即使写请求会锁表或者锁记录，也不会影响读请求的执行。

4.分表和分库是什么？有什么区别？

分库是一种水平扩展数据库的技术，将数据根据一定规则划分到多个独立的数据库中。每个数据库只负责存储部分数据，实现了数据的拆分和分布式存储。分库主要是为了解决并发连接过多，单机 mysql扛不住的问题。
分表指的是将单个数据库中的表拆分成多个表，每个表只负责存储一部分数据。这种数据的垂直划分能够提高查询效率，减轻单个表的压力。分表主要是为了解决单表数据量太大，导致查询性能下降的问题。

分库与分表可以从：垂直（纵向）和水平（横向）两种纬度进行拆分。下边我们以经典的订单业务举例，看看如何拆分。

垂直分库：一般来说按照业务和功能的维度进行拆分，将不同业务数据分别放到不同的数据库中，核心理念专库专用。按业务类型对数据分离，剥离为多个数据库，像订单、支付、会员、积分相关等表放在对应的订单库、支付库、会员库、积分库。垂直分库把一个库的压力分摊到多个库，提升了一些数据库性能，但并没有解决由于单表数据量过大导致的性能问题，所以就需要配合后边的分表来解决。
垂直分表：针对业务上字段比较多的大表进行的，一般是把业务宽表中比较独立的字段，或者不常用的字段拆分到单独的数据表中，是一种大表拆小表的模式。数据库它是以行为单位将数据加载到内存中，这样拆分以后核心表大多是访问频率较高的字段，而且字段长度也都较短，因而可以加载更多数据到内存中，减少磁盘IO，增加索引查询的命中率，进一步提升数据库性能。
水平分库：是把同一个表按一定规则拆分到不同的数据库中，每个库可以位于不同的服务器上，以此实现水平扩展，是一种常见的提升数据库性能的方式。这种方案往往能解决单库存储量及性能瓶颈问题，但由于同一个表被分配在不同的数据库中，数据的访问需要额外的路由工作，因此系统的复杂度也被提升了。
水平分表：是在同一个数据库内，把一张大数据量的表按一定规则，切分成多个结构完全相同表，而每个表只存原表的一部分数据。水平分表尽管拆分了表，但子表都还是在同一个数据库实例中，只是解决了单一表数据量过大的问题，并没有将拆分后的表分散到不同的机器上，还在竞争同一个物理机的CPU、内存、网络IO等。要想进一步提升性能，就需要将拆分后的表分散到不同的数据库中，达到分布式的效果。

5.主从延迟都有什么处理方法？

强制走主库方案：对于大事务或资源密集型操作，直接在主库上执行，避免从库的额外延迟。

索引

1.mysql索引的类型，聚集索引和非聚集索引是什么？非聚集索引一定回表查询吗？索引下推是什么？

MySQL 从 5.6 开始引入的优化技术，用于提升通过索引查询的效率。它通过将部分 WHERE 条件“下推”到存储引擎层，避免回表，提高性能。

先用索引筛选出更符合条件的记录，再决定是否回表取数据。

存储引擎读取索引记录
先在索引字段中判断 WHERE 条件是否成立
满足条件才回表取整行数据

优点：减少不必要的回表次数 → 提升性能

条件：

查询条件中涉及了 联合索引的多个字段

被下推的字段在 索引中存在

WHERE 条件中使用的字段顺序与索引字段顺序 不冲突

仅支持 InnoDB 引擎

Using index condition，说明使用了索引下推

事务

1.事务的“两段提交”

“两阶段提交”本是分布式事务的一种协议，被 InnoDB 借用来保证 事务提交与持久化日志之间的一致性。

在 InnoDB 中的含义：
主要是协调 redo log（InnoDB 层） 与 binlog（Server 层） 的一致性，以防出现一方提交而另一方失败。

举例说明：

若先写入 binlog 成功，redo log 写失败 —— 数据回滚但 binlog 已写，会造成主从不一致
若先写 redo log 成功，binlog 写失败 —— 数据已修改但 binlog 没写，从库无法同步

因此，MySQL 采用 两阶段提交，保证 redo log 和 binlog 要么都成功，要么都失败。

以 事务提交过程为例，发生顺序如下：

准备阶段：

执行 SQL（UPDATE、INSERT等），修改内存中数据页（Buffer Pool）
写入 Undo log，用于回滚支持（记录旧值）
生成 redo log（重做日志），标记为 prepare 状态（表示事务还未提交）
写入 redo log 的 日志缓存（log buffer）
将 redo log buffer 刷盘（调用 fsync），确保 redo log prepare 状态持久化到磁盘

👉 此时，事务还没提交，但 redo log 已持久化，保证崩溃恢复时能找到 prepare 状态。

提交阶段：

写入 binlog（Server 层，记录逻辑 SQL）
binlog 写盘成功后，调用 COMMIT 方法通知 InnoDB
InnoDB 把 redo log 标记为 commit 状态，并刷盘

至此，事务真正提交成功。

✅ 只有当 redo log 和 binlog 都成功，事务才算完成。

当崩溃恢复时：

只写了 prepare 的 redo log，没有写 binlog：事务被判断为未提交，InnoDB 会自动回滚
redo 和 binlog 都成功：InnoDB 看到 redo 有 commit 状态，恢复该事务
binlog 写成功但 redo 没有 commit：不可能发生（写 binlog 前 redo prepare 已持久化）

➡️ 保证了崩溃恢复时不会出现“只提交一半”的事务状态

问题	简要回答
InnoDB 为什么要使用两阶段提交？	保证 binlog 与 redo log 一致性，避免主从不一致
两阶段提交都涉及哪些日志？	redo log（InnoDB）和 binlog（Server）
提交过程中崩溃怎么办？	redo log prepare 状态+binlog 决定是否提交或回滚
redo log 和 binlog 顺序是谁先谁后？	redo prepare → binlog → redo commit
binlog 是逻辑日志，redo 是物理日志，谁更快？	redo 更快，binlog可重放，但不可恢复页

2.buffer pool是什么，了解吗

Buffer Pool（缓冲区池）是一种常见的内存管理技术，广泛应用于数据库系统、操作系统、网络服务器等需要频繁进行数据IO的场景。目的是为了减少IO操作，提高数据读写效率。

Buffer Pool 是一块内存区域，用于缓存磁盘上的数据块（也称为页，Page）。当应用程序需要读取或写入数据时，首先在 Buffer Pool 中查找，如果找到，则直接访问内存中的数据，避免了 disk IO。如果没找到，则需要从磁盘读取数据并放入 Buffer Pool，然后再进行访问。

减少 I/O 操作： 通过缓存常用的数据块，大幅减少了从磁盘读取数据的次数，从而提高性能。
提升响应速度： 直接从内存读取数据比从磁盘读取数据快得多，缩短了应用程序的响应时间。
提高并发性： 多个应用程序可以同时访问 Buffer Pool 中的数据，而无需频繁进行磁盘 I/O，从而提高系统的并发性。

数据库设计

1.冗余数据

冗余数据的问题

数据不一致性：当冗余数据中的一份被修改而另一份没有被修改时，就会出现数据不一致。这是最严重的问题。
存储空间浪费：同一份数据被存储多次，增加了存储成本。
更新异常：修改一份数据需要同时修改所有冗余副本，增加了更新操作的复杂性和出错概率。
删除异常：删除一个实体的数据时，可能无意中删除掉与其冗余存储的其他数据。
插入异常：插入新数据时，可能需要额外插入冗余数据，或者在某些情况下，无法插入新数据直到相关联的冗余数据也存在。

构建数据库1-范式化

建表时主要通过范式化（Normalization）来处理冗余数据。范式化的目标是消除数据依赖，减少数据冗余，并提高数据的一致性。

第一范式 (1NF)：确保表的每一列都是原子性的，不可再分。
第二范式 (2NF)：在 1NF 的基础上，要求非主键列完全依赖于整个主键，而不是主键的一部分。主要针对联合主键。
第三范式 (3NF)：在 2NF 的基础上，要求非主键列之间不存在传递依赖。即，非主键列不依赖于其他非主键列。
BC 范式 (BCNF)：比 3NF 更严格，要求所有的非主属性都完全函数依赖于每一个候选键，并且所有的主属性也都完全函数依赖于每一个候选键。
更高范式 (4NF, 5NF)：主要处理多值依赖和连接依赖，在实际应用中较少达到。

实践中：通常我们会设计到 3NF 或 BCNF，因为它能在保持数据一致性的同时，也兼顾一定的查询性能。过度范式化可能导致更多的表连接，从而降低查询性能。

处理方式：通过将一个大表分解为多个小表，并使用主键和外键来建立表之间的关联关系。例如，将客户信息和订单信息分离到 Customers 表和 Orders 表，通过 customer_id 进行关联。

构建数据库-2分表

分表（Sharding）\或*分库（Database Sharding）*时，情况会变得复杂。分表分库是为了解决单机数据库的性能瓶颈（如存储容量、IO 吞吐、CPU 利用率等）。在这个阶段，为了提高查询效率，有时会*主动引入少量冗余数据*，这被称为反范式化

垂直分表：

将一个表按照列进行拆分。例如，将一个大表中的不常用或字段较多的列拆分到另一个表中，通过相同的主键关联。但主键可能会在多个垂直分表中重复出现，这是正常的关联键。其目的不是为了消除冗余，而是为了优化单行数据的大小，提高缓存命中率。

把热点key和非热点的key分开，把大字段分开。构建一个主键的表

水平分表：

将一个表的数据按照某种规则（如哈希、范围、列表等）分散到多个物理表甚至多个数据库实例中。

一般都是使用hash分，hash分的化，一致性hash比较适合

水平分表本身不直接产生冗余数据，每条记录只存在于一个分片中。但如果涉及到跨分片的复杂查询或报表需求，可能需要：

数据汇总/聚合：通过数据同步（ETL）将各个分片的数据汇总到中心库或数据仓库进行分析，这过程中可能出现部分冗余（但通常是维度数据或预计算结果）。
引入缓存或搜索中间件：比如使用 Redis 缓存热点数据，或使用 Elasticsearch 进行复杂查询，这些外部系统也会存储一份数据，但它们是原始数据的副本，通常用于提升读性能，而非核心数据库层的冗余。

构建数据库-3，冗余策略

双写/多写：在写入主数据时，同时将冗余数据写入到需要它的其他库中。

异步消息队列：通过消息队列（如 Kafka、RabbitMQ）来解耦数据更新和冗余数据同步。当一个数据被修改时，发送一条消息，其他需要这份冗余数据的服务订阅并更新自己的副本。这可以避免同步更新的性能瓶颈，但会引入最终一致性问题。

ETL (Extract-Transform-Load)：定期将数据从源库抽取、转换、加载到目标库（如数仓或报表库），用于离线分析或非实时查询。

数据冗余的选择：通常冗余的是那些不经常变化、但查询频率高且跨库查询代价大的数据。对于频繁变化的数据，冗余的维护成本会非常高。

重点是范式化和反范式化

2.分库分表

分库分表是解决海量数据存储和高并发访问的有效手段，但其设计并非一蹴而就，需要深入思考以下几个核心原则和复杂性。

分片键—-重要

分片键是数据分布的依据，其选择直接决定了分片策略的有效性和未来的可维护性。

业务相关性: 分片键应与核心业务逻辑紧密相关。例如，用户相关数据以 user_id 分片，订单相关数据可能也以 user_id 分片以保证用户维度的数据局部性。
数据均匀性: 理想的分片键能够将数据均匀地分散到各个分片中，避免出现“热点”（某个分片数据量过大或访问压力过高）。通常采用哈希（如 MD5(user_id) % N 或更复杂的哈希算法）或自定义算法来确保均匀分布。

可以通过历史数据分析来模拟不同分片键策略下的数据分布情况，评估其均匀性。对于哈希取模，可以考虑使用一致性哈希，它在节点增减时能更好地保持数据分布的均衡性，减少数据迁移量。

稳定性: 分片键一旦确定，通常不应改变。如果分片键的值会频繁更新，会带来数据迁移和维护的巨大挑战。

对于那些不含分片键的查询，需要特别关注。这类查询通常会触发全表扫描（或全分片扫描），性能极低。在设计业务功能时，应尽量引导业务方在查询条件中包含分片键。如果无法避免，则需要考虑其他方案，例如：

数据冗余： 将少量高频查询但无分片键的数据冗余到所有分片或单独的热点库中。

ES/Solr等搜索引擎： 对于复杂查询、模糊查询或多维度查询，可以考虑将部分数据同步到Elasticsearch或Solr等搜索引擎中，利用其强大的查询能力，将查询压力从关系型数据库中剥离。

数仓或OLAP系统： 对于报表、统计等分析类查询，应将其导入到数仓或OLAP系统中进行处理，避免直接冲击在线事务数据库。

查询命中率: 绝大部分查询应该能够通过分片键直接路由到唯一的分片，避免跨分片查询，从而提高效率。
数据类型: 通常选择整数类型（如 bigint）作为分片键，因为它们计算和比较效率高。

分片策略

水平分片 (Horizontal Sharding/Row-based Sharding): 将表中行数据分散到不同的物理表或数据库中。这是最常见的模式，也是您文件中的 _0, _1 后缀所体现的。

好处: 扩展性强，可以根据数据增长动态增加分片。查询通常只涉及少量数据，性能高。

常见实现:

哈希取模: sharding_key % N（N为分片总数），数据分布最均匀，但扩容时需要数据迁移（弹性伸缩性差）。
范围分片: 根据分片键的值范围划分，例如用户ID 1-100万在一个分片，100万-200万在另一个。优点是扩容方便（直接增加新范围分片），缺点是容易出现热点。
一致性哈希: 一种更高级的哈希算法，在增加或减少分片时，只需要迁移少量数据，弹性伸缩性更好。
时间分片: 例如按年或月创建新表，用于日志、流水等时间序列数据。

对于用户数据，哈希取模通常是首选，因为它能带来更好的均匀性。而对于按时间产生的日志或流水数据，时间分片则更为合适。一致性哈希： 这是一种更高级且更灵活的策略，值得深入研究和应用，尤其是在需要频繁扩容或缩容的场景。它能有效降低数据迁移的成本。

垂直分片 (Vertical Sharding/Column-based Sharding):

按业务功能分库: 将不同业务模块的数据（例如用户服务、订单服务、商品服务）分别存储在独立的数据库中。这通常是微服务架构中的常见实践。

好处: 职责分离，团队独立开发维护，数据库之间互不影响，方便根据业务特点进行优化。
例子: damai_base_data, damai_customize, damai_order_0/1, damai_pay_0/1, damai_program_0/1, damai_user_0/1 等数据库的划分，明显是按业务领域（基础数据、定制、订单、支付、节目、用户）进行的垂直分库。

按字段分表: 将一张表中不同业务含义或访问频率差异大的列拆分到不同的表中，以减少单表宽度，提高查询效率。解决大宽表问题上非常有效。过多的垂直分表可能会增加数据管理的复杂性。

好处: 减少I/O开销，热点数据和非热点数据分离存储，提高查询效率。

在决定按字段分表时，应仔细分析字段的访问频率、更新频率以及是否包含大文本或二进制数据。通常将不常用的大字段、或者单独查询频率高的字段拆分出去。

数据路由

分片后，应用程序如何知道数据存在哪个分片上？这需要一个路由层。

应用层路由: 应用程序代码根据分片键和分片算法直接计算出目标分片，然后连接到对应的数据库。实现简单，但应用逻辑与分片规则耦合。
中间件路由: 使用专门的数据库中间件（如MyCAT, ShardingSphere, Vitess）作为代理层，应用程序连接中间件，中间件负责解析SQL、计算分片、路由请求、聚合结果。这是最推荐的方式，实现了分片逻辑与应用解耦，提供了更丰富的功能（如分布式事务、读写分离）。

ShardingSphere作为一套成熟的开源分布式数据库解决方案，提供了丰富的特性，包括数据分片、分布式事务、数据加密、影子库压测等，且支持多种部署模式，是目前非常受欢迎的选择。

MyCAT更偏向于一个数据库代理层，配置相对简单。

Vitess源自YouTube，更侧重于自动化运维和大规模部署，适合超大规模的场景。

全局唯一ID生成

在分库分表后，数据库的自增ID不能再使用，因为它们只能保证单库内的唯一性。需要引入全局唯一ID生成策略。

UUID/GUID: 优点是全局唯一，生成简单；缺点是无序，作为主键索引性能差，存储空间大。

虽然生成简单且全局唯一，但作为主键索引性能差是其致命缺点，因为其无序性导致B+树索引频繁分裂，影响写入性能和查询效率。应尽量避免作为主键。

基于时间戳+机器ID+序列号: 如Snowflake算法，生成趋势递增的ID，有助于索引性能。

这是目前最推荐的方案。它生成的ID趋势递增，有利于数据库索引性能。同时，通过机器ID和序列号保证了全局唯一性。引入时钟回拨问题的应对策略。Snowflake算法依赖于系统时钟，如果系统时钟发生回拨，可能会生成重复ID或阻塞。需要有机制检测并处理这种情况（例如，等待时钟恢复或暂停ID生成服务）。

独立ID生成服务: 单独部署一个服务来生成和分配ID。

部署一个独立的ID生成服务，可以提供更强的可控性和定制性，但增加了系统复杂度和维护成本。在没有特殊需求的情况下，Snowflake算法通常足以满足需求。

分布式事务

当一个业务操作涉及多个分片的数据修改时，需要保证这些操作的原子性（要么都成功，要么都失败）。

2PC (Two-Phase Commit): 传统的关系型数据库分布式事务协议，但性能差，有阻塞风险。

理论上能保证强一致性，但由于其同步阻塞、性能低、易死锁等缺点，在线业务中极少使用，尤其是在高并发场景下。

TCC (Try-Confirm-Cancel): 业务层面的分布式事务解决方案，需要侵入业务代码，但性能较好。

是一种业务侵入性较强的柔性事务方案，需要业务开发人员在每个参与者服务中实现Try、Confirm、Cancel三个操作。 TCC适用于对实时性要求较高，且业务逻辑相对独立的场景。它的优点是性能比2PC好，但开发成本和维护成本较高，需要仔细设计补偿逻辑。

消息最终一致性: 通过消息队列（MQ）实现异步补偿，保证最终一致性，适用于对实时性要求不高的场景。

通过可靠消息队列（如Kafka, RocketMQ）实现，将业务操作拆分为多个本地事务，通过消息通知其他服务进行后续操作。即使某个操作失败，也可以通过重试或补偿机制最终达到一致。

最终一致性，可能存在短暂的数据不一致窗口期；需要引入消息队列，增加系统复杂度。

在设计消息最终一致性方案时，需要考虑消息的幂等性消费、消息的可靠投递（本地消息表/事务消息）、死信队列和补偿机制。

跨分片查询与Join

分片的主要目的是避免跨分片操作。如果业务逻辑不可避免地需要跨分片Join或聚合查询，会非常复杂和低效。

尽量避免: 优化业务逻辑，将需要Join的数据放在同一个分片上（如关联表也使用相同的分片键）。
应用层Join: 从各个分片查询数据，然后在应用层进行内存Join。
数据冗余: 少量关键数据在不同分片上进行冗余存储，以避免跨分片Join。
数据同步/ETL: 将需要Join的数据通过ETL同步到数仓或专门的分析数据库中进行分析。

尽量避免： 优化业务逻辑，使关联数据位于同一分片，这是最好的策略。

应用层Join： 适用于结果集较小的场景。如果涉及大量数据的跨分片Join，在应用层进行会消耗大量内存和CPU，导致性能瓶颈。

数据冗余： 对于读多写少、数据量不大且更新频率不高的关键关联数据，冗余是一种有效的优化手段。但需要考虑数据一致性问题，需要有机制（如消息队列异步同步）来保证冗余数据的一致性。

数据同步/ETL到数仓： 这是处理复杂跨分片查询和分析查询的标准方案。将事务性数据和分析性数据分离，可以避免分析查询对在线业务的影响，提高系统整体性能。

搭建一套完善的数据同步链路（例如基于Binlog的CDC工具，如Canal），将数据实时或准实时地同步到数仓（如Hive, Flink, StarRocks, ClickHouse等），并在数仓中进行复杂的Join和聚合分析。

数据迁移和扩容

当业务增长，分片容量不足时，需要进行扩容，这涉及数据迁移。这是一个复杂且风险高的操作，通常需要专门的工具和详细的预案。

预案先行： 必须制定详细的数据迁移预案，包括迁移策略、回滚方案、风险评估、影响范围、时间窗口等。

灰度发布/双写： 在数据迁移过程中，可以采用双写（新旧库同时写入）和灰度发布的方式，逐步将流量切换到新分片，以降低风险。

自动化工具： 尽量使用或开发自动化迁移工具，减少人工操作，提高效率和准确性。

监控与告警： 在迁移过程中，必须有完善的监控和告警机制，实时关注数据一致性、服务可用性和性能指标。

充分测试： 在生产环境迁移前，必须在类生产环境进行充分的全链路压测和功能测试，确保迁移方案的可靠性。

问题

用户服务分库分表与读扩散问题：

用户服务中用户邮箱、用户手机号多种方式登录，并且要避免读扩散，这是一个非常典型的应用场景。读扩散（Read Amplification）指的是为了获取一条数据，不得不查询多个甚至所有分片，从而导致数据库压力倍增和查询效率低下。

通过集中式映射服务，将多维度登录凭证映射到统一的分片键

为了避免读扩散，我们不会在用户数据分片中直接通过邮箱或手机号进行查询，而是会增加一个独立的用户身份映射层。

独立的身份认证服务/库：

该服务专门负责用户身份的认证和登录凭证（邮箱、手机号）到核心用户ID (user_id) 的映射。

d_user_main (主用户表，在用户数据分片中)

-- 在 damai_user_0, damai_user_1 等分片中
CREATE TABLE `d_user_0` (
  `user_id` bigint(20) NOT NULL COMMENT '用户ID，作为主分片键',
  -- 其他用户属性，如昵称、头像、注册时间等
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户主表';

此表是用户核心数据表，以 user_id 作为分片键

d_ticket_user_X

-- 在 damai_user_0 分片中
CREATE TABLE `d_ticket_user_0` (
  `id` bigint(20) NOT NULL COMMENT '主键id',
  `user_id` bigint(20) NOT NULL COMMENT '用户id', -- 关联到 d_user_0 的 user_id，确保购票人信息和用户主信息在同一分片
  `rel_name` varchar(256) NOT NULL COMMENT '用户真实名字',
  `id_type` int(11) NOT NULL DEFAULT '1' COMMENT '证件类型',
  `id_number` varchar(512) NOT NULL COMMENT '证件号码',
  -- ... 其他字段
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='购票人表';

独立的映射数据库/表

为了解决通过手机号/邮箱查找 user_id 的问题，我们需要额外的映射表。这些表通常存储在一个独立的、不分片的数据库中，或者在一个专用的、读写性能极高的存储系统（如Redis、Memcached）中，或者通过特定的哈希策略进行独立分片。

-- 在一个独立的 Identity Database 或集中式数据库中
CREATE TABLE `user_email_mapping` (
  `email_hash` varchar(64) NOT NULL COMMENT '邮箱的哈希值', -- 用于存储邮箱哈希，避免直接存储明文邮箱
  `email` varchar(256) NOT NULL UNIQUE COMMENT '用户邮箱',
  `user_id` bigint(20) NOT NULL COMMENT '对应的用户ID',
  PRIMARY KEY (`email`),
  KEY `idx_email_hash` (`email_hash`) -- 可选，如果查询以哈希为主
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='邮箱-用户ID映射表';

CREATE TABLE `user_mobile_mapping` (
  `mobile_hash` varchar(64) NOT NULL COMMENT '手机号的哈希值', -- 存储手机号哈希
  `mobile` varchar(20) NOT NULL UNIQUE COMMENT '用户手机号',
  `user_id` bigint(20) NOT NULL COMMENT '对应的用户ID',
  PRIMARY KEY (`mobile`),
  KEY `idx_mobile_hash` (`mobile_hash`) -- 可选
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='手机号-用户ID映射表';

用户登录:

用户输入手机号/邮箱和密码。
身份认证服务接收请求。
身份认证服务首先查询 user_mobile_mapping 或 user_email_mapping 表（在独立的身份数据库中），通过手机号/邮箱快速查找对应的 user_id。这是一个单点查询，不会有读扩散。
获取 user_id 后，验证密码（密码哈希可以存储在身份数据库中，或与 user_id 关联存储在用户数据分片中的一个小表中）。
认证成功，生成登录凭证（如Token）。