首页 > 数据库 >Mysql--运维篇--库表分离（垂直分库，水平分库，垂直分表，水平分表）

Mysql--运维篇--库表分离（垂直分库，水平分库，垂直分表，水平分表）

时间：2025-01-12 22:32:56浏览次数：3  
标签：分库 -- 数据库 user 分片 分表 id

在处理大规模数据和高并发访问时，数据库的分库和分表是两种常见的优化策略。它们通过将数据分散到多个数据库或表中，来提高性能、可扩展性和管理效率。为了更精细地应对不同的场景，分库和分表可以进一步细分为垂直分库/分表和水平分库/分表。

一、分库（Database Sharding）

分库是指将一个大的数据库拆分成多个独立的数据库实例，每个数据库实例负责存储一部分数据。这些数据库实例可以分布在不同的服务器上，以实现负载均衡和高可用性。

目的：

  • 提高性能：通过将数据分散到多个数据库实例中，减少了单个数据库的压力，提升了查询和写入的性能。
  • 提升可扩展性：随着数据量的增长，可以通过增加更多的数据库实例来扩展系统。
  • 增强容错能力：即使某个数据库实例发生故障，其他实例仍然可以继续提供服务。

适用场景：

  • 当单个数据库实例无法承受巨大的数据量或高并发访问时。
  • 当需要支持分布式架构，确保系统的高可用性和容错能力。

二、分表（Table Partitioning）

分表是指将一个大表拆分成多个小表，每个小表存储原表的一部分数据。分表可以在同一个数据库内进行，也可以跨多个数据库实例进行。

目的：

  • 减少单表的行数或列数，降低查询时的扫描范围，提升查询性能。
  • 优化索引和缓存命中率，减少不必要的I/O操作。
  • 简化维护，例如备份、恢复和清理操作。

适用场景：

  • 当单个表的行数或列数过多，导致查询性能下降时。
  • 当某些字段的访问频率远高于其他字段，或者某些字段占用大量空间但不经常被查询时。

三、垂直分库与垂直分表

1、垂直分库（Vertical Database Sharding）

垂直分库是指将不同的表或表中的不同字段分配到不同的数据库实例中。每个数据库实例负责存储特定类型的表或字段。

原理：

  • 按功能或业务模块划分：将不同的业务模块（如用户信息、订单信息、商品信息等）存储在不同的数据库中。
  • 按字段划分：将一个表中的不同字段拆分到不同的数据库中，通常通过主键或其他唯一标识符关联。

优点：

  • 减少单个数据库的复杂性：每个数据库只负责特定的业务逻辑，降低了复杂度。
  • 优化查询性能：减少了每次查询时需要读取的数据量，提升了查询速度。
  • 简化索引管理：可以为每个数据库创建更合适的索引，避免不必要的索引维护开销。

缺点：

  • 增加了应用程序的复杂性：需要在应用层面对多个数据库进行管理和路由。
  • JOIN操作复杂：当需要从多个数据库中获取完整的信息时，必须执行跨库JOIN操作，这可能会增加查询的复杂性和执行时间。

示例：
假设你有一个电子商务平台，可以将用户信息、订单信息和商品信息分别存储在三个不同的数据库中。

  • db_users：存储用户信息表users。
  • db_orders：存储订单信息表orders。
  • db_products：存储商品信息表products。

2、垂直分表（Vertical Table Partitioning）

垂直分表是指将一个大表按照列（字段）进行拆分，将不同类型的字段分配到不同的表中。每个新表包含原表的一部分列，并且这些表之间通常通过主键或其他唯一标识符关联。

原理：

  • 按字段使用频率划分：将常用的字段和不常用的字段分开，减少每次查询时需要读取的数据量。
  • 按字段类型划分：将不同类型的数据（如字符串、数值、BLOB等）分开存储，优化存储和查询性能。

优点：

  • 提升查询性能：减少了每次查询时需要扫描的列数，降低了I/O开销。
  • 优化缓存命中率：较小的表更容易适应内存缓存，减少了磁盘I/O操作。
  • 简化索引管理：可以为每个表创建更合适的索引，避免不必要的索引维护开销。

缺点：

  • 增加了应用程序的复杂性：需要在应用层面对多个表进行管理和路由。
  • JOIN操作复杂：当需要从多个表中获取完整的信息时，必须执行JOIN操作，这可能会增加查询的复杂性和执行时间。

示例：
假设有一个用户表 users，包含以下字段：

- user_id
- username
- password
- email
- profile_picture (BLOB)
- last_login
- created_at

垂直分表后，拆分为两个表：

-- 用户基本信息表
CREATE TABLE users_basic (
    user_id INT PRIMARY KEY,
    username VARCHAR(50),
    password VARCHAR(255),
    email VARCHAR(100)
);

-- 用户附加信息表
CREATE TABLE users_extra (
    user_id INT PRIMARY KEY,
    profile_picture BLOB,
    last_login DATETIME,
    created_at DATETIME
);

四、水平分库与水平分表

1、水平分库（Horizontal Database Sharding）

水平分库是指将一个大的数据库拆分成多个数据库实例，每个数据库实例存储原数据库中的一部分行（记录）。通常根据某个字段（如用户ID、订单ID等）进行分片，确保相同类型的数据分散到不同的数据库中。

原理：

  • 按行划分：将表中的行根据某个字段（称为分片键或Shard Key）分配到不同的数据库实例中。
  • 分布式架构：每个数据库实例可以分布在不同的服务器上，实现负载均衡和高可用性。

优点：

  • 提升查询性能：减少了单个数据库的行数，降低了查询时的扫描范围，提升了查询速度。
  • 提高可扩展性：随着数据量的增长，可以通过增加更多的数据库实例来扩展系统。
  • 增强容错能力：即使某个数据库实例发生故障，其他实例仍然可以继续提供服务。

缺点：

  • 复杂的查询逻辑：跨分片的查询（如全局查询）可能会变得复杂，因为需要从多个数据库中获取数据并合并结果。
  • 数据一致性挑战：确保所有分片之间的数据一致性和同步可能是一个挑战，特别是在分布式环境中。
  • 分片键选择困难：选择合适的分片键非常重要，不恰当的选择可能导致数据分布不均或查询性能下降。

示例：
假设你有一个订单表orders，可以根据user_id将订单分散到多个数据库中。假设我们有4个数据库实例，可以使用哈希分片策略：
shard_id = HASH(user_id) % 4
然后，订单数据会根据user_id的哈希值分配到不同的数据库实例中：

  • db_orders_0：存储user_id的哈希值为0的订单。
  • db_orders_1：存储user_id的哈希值为1的订单。
  • db_orders_2：存储user_id的哈希值为2的订单。
  • db_orders_3：存储user_id的哈希值为3的订单。

2、水平分表（Horizontal Table Partitioning）

水平分表是指将一个大表按照行（记录）进行拆分，将不同的行分配到不同的表中。每个新表包含原表的全部列，但只包含部分行。水平分表可以通过多种方式进行划分，常见的有基于范围、哈希、列表等方式。

原理：

  • 按行划分：将表中的行根据某个字段（称为分片键或Shard Key）分配到不同的表中。
  • 分布式架构：每个表可以分布在不同的数据库实例中，实现负载均衡和高可用性。

优点：

  • 提升查询性能：减少了单个表的行数，降低了查询时的扫描范围，提升了查询速度。
  • 提高可扩展性：随着数据量的增长，可以通过增加更多的表来扩展系统。
  • 增强容错能力：即使某个表发生故障，其他表仍然可以继续提供服务。

缺点：

  • 复杂的查询逻辑：跨分片的查询（如全局查询）可能会变得复杂，因为需要从多个表中获取数据并合并结果。
  • 数据一致性挑战：确保所有分片之间的数据一致性和同步可能是一个挑战，特别是在分布式环境中。
  • 分片键选择困难：选择合适的分片键非常重要，不恰当的选择可能导致数据分布不均或查询性能下降。

示例：
假设我们有一个订单表orders，可以根据user_id对订单进行水平分片。假设我们有4个分片表，可以使用哈希分片策略：
shard_id = HASH(user_id) % 4
水平分表，可以创建4个分片表：
– 分片 0

CREATE TABLE orders_shard_0 (
    order_id INT PRIMARY KEY,
    user_id INT,
    product_id INT,
    order_date DATETIME,
    total_amount DECIMAL(10, 2)
);

– 分片 1

CREATE TABLE orders_shard_1 (
    order_id INT PRIMARY KEY,
    user_id INT,
    product_id INT,
    order_date DATETIME,
    total_amount DECIMAL(10, 2)
);

– 分片 2

CREATE TABLE orders_shard_2 (
    order_id INT PRIMARY KEY,
    user_id INT,
    product_id INT,
    order_date DATETIME,
    total_amount DECIMAL(10, 2)
);

– 分片 3

CREATE TABLE orders_shard_3 (
    order_id INT PRIMARY KEY,
    user_id INT,
    product_id INT,
    order_date DATETIME,
    total_amount DECIMAL(10, 2)
);

在插入或查询订单时，应用程序需要根据user_id计算出对应的shard_id，并将操作路由到相应的分片表中。

五、垂直分库与水平分库、垂直分表与水平分表的区别

在这里插入图片描述

六、垂直分库/分表与水平分库/分表的结合

在实际应用中，垂直分库/分表和水平分库/分表并不是互斥的，而是可以结合使用。例如，你可以先对表进行垂直分库，将不同的字段拆分到不同的表中，然后再对每个表进行水平分表，以进一步提高性能和可扩展性。

示例：
假设我们有一个电子商务平台，用户表users和订单表orders都非常大。我们可以先对users表进行垂直分表，将用户的基本信息和附加信息分开，然后再对orders表进行水平分表，根据user_id将订单分散到多个分片中。
垂直分表：

-- 用户基本信息表
CREATE TABLE users_basic (
    user_id INT PRIMARY KEY,
    username VARCHAR(50),
    password VARCHAR(255),
    email VARCHAR(100)
);
-- 用户附加信息表
CREATE TABLE users_extra (
    user_id INT PRIMARY KEY,
    profile_picture BLOB,
    last_login DATETIME,
    created_at DATETIME
);

水平分表：

CREATE TABLE orders_shard_0 (
    order_id INT PRIMARY KEY,
    user_id INT,
    product_id INT,
    order_date DATETIME,
    total_amount DECIMAL(10, 2)
);

CREATE TABLE orders_shard_1 (
    order_id INT PRIMARY KEY,
    user_id INT,
    product_id INT,
    order_date DATETIME,
    total_amount DECIMAL(10, 2)
);

– 其他分片…
通过这种方式，我们可以同时利用垂直分表和水平分表的优势，既减少了单表的宽度，又分散了数据的行数，从而提高了整体性能和可扩展性。

七、总结

  • 分库是指将一个大的数据库拆分成多个独立的数据库实例，适用于需要分布式架构和高可用性的场景。
  • 分表是指将一个大表拆分成多个小表，适用于需要减少单表行数或列数的场景。
  • 垂直分库/分表是按列或表的功能进行划分，适用于字段较多且某些字段不常用的情况。
  • 水平分库/分表是按行进行划分，适用于数据量大、并发访问高的场景。
  • 两者结合 可以在更大规模的应用中提供更好的性能和可扩展性。

快速记忆：
分库是把表或者数据分布到不同的数据库上，减轻数据库压力。
分表是把表结构或者表数据拆分到不同的表上，减轻数据库压力。
垂直，一定是改变了结构（如数据库表量减少，或表的字段减少）。
水平，把数据分开（如根据分片分到不同数据库上，或分布到同一个库的不同分片表上）。

乘风破浪会有时，直挂云帆济沧海！！！

标签：分库,--,数据库,user,分片,分表,id
From： https://blog.csdn.net/qq_34207422/article/details/145100631

相关文章

  • 解决 Git SSL 连接错误：OpenSSL SSL_read: SSL_ERROR_SYSCALL, errno
    问题描述在执行gitpull命令时遇到以下错误：>gitpull--tagsoriginmainfatal:unabletoaccess'对应github仓库':OpenSSLSSL_read:SSL_ERROR_SYSCALL,errno0这个错误通常表示Git在尝试通过HTTPS连接到GitHub时遇到了SSL连接问题。解决方案1.检查网络......
  • C#中的设计模式：构建更加优雅的代码
    C#在面向对象编程（OOP）方面的强大支持，我们可以探讨“C#中的设计模式”。这不仅有助于理解如何更好地组织代码，还能提高代码的可维护性和可扩展性。引言设计模式是软件工程中经过实践验证的解决方案模板，它们提供了一种标准化的方法来解决常见的开发问题。对于使用C#进行开发......
  • G1原理—5.G1垃圾回收过程之Mixed GC
    大纲1.MixedGC混合回收是什么2.YGC可作为MixedGC的初始标记阶段3.MixedGC并发标记算法详解(一)4.MixedGC并发标记算法详解(二)5.MixedGC并发标记算法详解(三)6.并发标记的三色标记法7.三色标记法如何解决错标漏标问题8.SATB如何解决错标漏标问题9.重新梳理MixedGC......
  • Linux电源管理——Suspend-to-Idle(s2idle) 流程
    目录一、常见的Suspend方式1、Suspend-to-idle2、Standby 3、Suspend-to-RAM4、Hibernation 二、代码分析1、state_store2、suspend_enter3、s2idle_loop4、idleloop5、psci_enter_idle_state6、cpu_suspend7、开始resume流程8、开启IRQ中断9、pm_syst......
  • 汽车电子相关的协议UDS、DOIP、CAN
    UDS协议UDS（UnifiedDiagnosticServices，统一诊断服务协议）是一种标准化的通信协议，广泛应用于汽车电子系统中，尤其是在车辆诊断和服务过程中。它用于车辆的诊断、维护、控制和测试，支持通过车载诊断接口（如OBD接口）与汽车的电子控制单元（ECU）进行通信。UDS协议基于ISO14229标......
  • 书生大模型实战营L2G4000 - InternVL 部署微调实践
    1.多模态大模型的工作原理和设计模式1.1工作原理多模态大模型的工作原理基于深度学习技术，旨在通过处理和融合多种模态数据（如文本、图像、音频、视频等）来实现更全面的理解和生成能力。以下是其工作原理的核心要点：1.数据预处理与编码数据预处理：不同模态的数据需要特定的预......
  • 基于SpringBoot+Vue农场投入品运营线上管理系统的设计与实现
    博主主页：一季春秋博主简介：专注Java技术领域和毕业设计项目实战、Java微信小程序、安卓等技术开发，远程调试部署、代码讲解、文档指导、ppt制作等技术指导。技术范围：SpringBoot、Vue、SSM、HLMT、Jsp、PHP、Nodejs、Python、爬虫、数据可视化、小程序、安卓app、大数据、物联......
  • 基于SpringBoot+Vue在线项目管理与任务分配中的应用的设计与实现
    博主主页：一季春秋博主简介：专注Java技术领域和毕业设计项目实战、Java微信小程序、安卓等技术开发，远程调试部署、代码讲解、文档指导、ppt制作等技术指导。技术范围：SpringBoot、Vue、SSM、HLMT、Jsp、PHP、Nodejs、Python、爬虫、数据可视化、小程序、安卓app、大数据、物联......
  • Allegro 如何快速划分电源铜皮
    电源网络特别多的情况下，使用这个方法快速划分铜皮。 直接上图： 1、选择正确的层，选择划线指令 2、划线，规划铜皮区域，这一步最好把不同的电源网络点亮颜色，免得看不清楚，细致一点，规划好在划线，不然删除线是整体删除 3、画好的线完整展示  4、选择以下操作，分割平面，......
  • Python在生成式AI驱动的电影特效中的作用
    文章目录引言一、生成式AI在电影特效中的应用1.1生成式AI简介1.2生成式AI在电影特效中的角色二、Python的优势及其在生成式AI中的应用2.1Python的生态系统2.2Python在生成式AI中的应用三、虚拟角色生成3.1虚拟角色生成的挑战3.2利用GAN生成虚拟角色四、场景重建......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99