MySQL学习---分库和分表-CSDN博客

本文链接：https://blog.csdn.net/shouxifeiwu/article/details/149780508

在当今互联网应用快速发展的情况下，各种数据量呈现爆炸式增长。当 MySQL 数据库中的数据量达到一定规模后，单库单表的架构会面临性能瓶颈，此时分库分表技术成为解决这一问题的重要手段。

分库分表是在大规模数据场景下，将原本存储在单一数据库和数据表中的数据，按照一定规则分散存储到多个数据库和数据表中的技术方案。其中，二者分别是：

（1）分库：将一个数据库按照某种规则拆分到多个不同的数据库服务器上，每个数据库只存储一部分数据。

（2）分表：将一个数据表按照某种规则拆分到多个不同的数据表中，这些表可以在同一个数据库，也可以在不同的数据库。

例如，一个电商平台的订单表，当订单量达到数亿条时，单表查询会非常缓慢，此时就可以将订单表按照时间或用户 ID 进行分表，同时将不同的分表部署到不同的数据库服务器上，实现分库分表。

分库分表的核心原理是 "数据分片"，即按照预设的规则将数据分散存储，从而降低单库单表的数据量，提高数据库的操作性能。

分库主要有两种方式：

（1）水平分库：按照数据的某种特征（如用户 ID 哈希），将同一类业务的数据分散到多个数据库中，每个数据库的表结构相同。

（2）垂直分库：按照业务模块将数据分散到不同的数据库，如将用户数据、商品数据、订单数据分别存储在不同的数据库。

同样的，分表也分为两种方式：

（1）水平分表：将一个表中的数据按照某种规则（如时间范围、ID 范围）拆分到多个结构相同的表中，每个表只包含一部分数据。

（2）垂直分表：将一个表中字段较多的表，按照字段的冷热程度或业务关联性拆分成多个表，每个表包含一部分字段。

分库分表通常需要借助中间件（如 Sharding-JDBC、MyCat 等）实现，中间件负责解析 SQL，根据分片规则路由到对应的库和表，并将结果汇总返回。其作用主要有：

（1）提升查询性能：当数据量庞大时，单库单表的查询会导致索引失效、磁盘 IO 频繁等问题。分库分表后，每个库和表的数据量减少，查询时扫描的数据范围缩小，索引效率提高，从而提升查询速度。

（2）提高系统可用性：单库架构下，数据库一旦出现故障，整个系统都会受影响。分库后，不同的业务数据在不同的数据库，一个数据库故障不会导致所有业务瘫痪，提高了系统的可用性。

（3）便于水平扩展：分库分表采用分布式架构，当数据量继续增长时，可以通过增加数据库服务器和数据表的方式进行水平扩展，而无需对现有架构进行大规模改造。

（4）优化资源利用：垂直分库可以将不同业务的数据分离，便于针对不同业务的特点分配不同的硬件资源。例如，对读写频繁的订单库配置更高性能的服务器，对查询较少的历史数据存储库使用普通服务器。

这些方法可以带来许多优点，如

（1）解决性能瓶颈：有效降低单库单表的数据量，避免因数据量过大导致的查询缓慢问题。

（2）支持高并发：分散了数据库的访问压力，多个库表可以同时处理请求，提高了系统的并发处理能力。

（3）便于维护：单个库表的数据量小，备份、恢复、索引重建等操作更加高效。

但它们也有不少缺点，例如：

（1）增加系统复杂度：分库分表需要设计分片规则，引入中间件，使系统架构变得复杂，开发和维护成本提高。

（2）分布式事务问题：跨库操作时，事务的一致性难以保证，需要采用分布式事务解决方案（如 TCC、SAGA 等），增加了实现难度。

（3）跨库查询困难：原本简单的联表查询在分库分表后变得复杂，需要中间件进行二次处理，可能影响性能。

（4）数据迁移复杂：当分片规则需要调整时，数据迁移过程复杂，容易出现数据不一致的问题。

下面用几个案例来演示一下这两种方法

（1）水平分表示例（按 ID 范围）

假设我们有一个订单表order，当数据量过大时，按 ID 范围进行水平分表：

-- 创建分表1：存储ID 1-1000000的订单
CREATE TABLE `order_1` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  `create_time` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 创建分表2：存储ID 1000001-2000000的订单
CREATE TABLE `order_2` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  `create_time` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

spring:
  shardingsphere:
    datasource:
      names: db1
      db1:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://localhost:3306/db1
        username: root
        password: root
    rules:
      sharding:
        tables:
          order:
            actual-data-nodes: db1.order_${1..2}
            table-strategy:
              standard:
                sharding-column: id
                sharding-algorithm-name: order_inline
        sharding-algorithms:
          order_inline:
            type: INLINE
            props:
              algorithm-expression: order_${id % 2 + 1}
    props:
      sql-show: true

上述配置的主要内容为，当插入或查询订单数据时，Sharding-JDBC 会根据订单 ID 对 2 取模，决定数据存储在order_1还是order_2表中。

（2）垂直分表示例

对于包含多个字段的用户表user，进行垂直分表：

-- 基本信息表：存储常用字段
CREATE TABLE `user_base` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) NOT NULL,
  `password` varchar(100) NOT NULL,
  `phone` varchar(20) NOT NULL,
  `create_time` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 详细信息表：存储不常用字段
CREATE TABLE `user_detail` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) NOT NULL,
  `address` varchar(200) DEFAULT NULL,
  `birthday` date DEFAULT NULL,
  `education` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

垂直分表后，查询用户基本信息时只需访问user_base表，减少了数据传输量，提高了查询效率。

分库分表是应对 MySQL 大数据量场景的重要技术方案，通过数据分片的方式将数据分散存储，有效提升了数据库的查询性能和并发处理能力，同时提高了系统的可用性和可扩展性。但是它们也有各自的优缺点，只有深入理解分库分表的原理和优缺点，结合实际业务需求进行合理设计和实施，才能充分发挥其优势，构建高性能、高可用的数据库架构。