MySQL学习---分库和分表

        在当今互联网应用快速发展的情况下，各种数据量呈现爆炸式增长。当 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: db1db1:type: com.zaxxer.hikari.HikariDataSourcedriver-class-name: com.mysql.cj.jdbc.Driverurl: jdbc:mysql://localhost:3306/db1username: rootpassword: rootrules:sharding:tables:order:actual-data-nodes: db1.order_${1..2}table-strategy:standard:sharding-column: idsharding-algorithm-name: order_inlinesharding-algorithms:order_inline:type: INLINEprops: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 大数据量场景的重要技术方案，通过数据分片的方式将数据分散存储，有效提升了数据库的查询性能和并发处理能力，同时提高了系统的可用性和可扩展性。​但是它们也有各自的优缺点，只有深入理解分库分表的原理和优缺点，结合实际业务需求进行合理设计和实施，才能充分发挥其优势，构建高性能、高可用的数据库架构。​