当前位置： 首页 > news >正文

MySQL 分库分表

news 2024/11/4 21:04:32

一、简介

在MySQL中，分库分表是一种常用的数据库优化策略，特别是在数据量巨大时，可以有效提高查询性能和系统的可扩展性。以下简单介绍下分库分表的概念：

  • 分库：将数据分散到多个数据库中，每个数据库可以放在不同的物理服务器上。
  • 分表：将一个大表拆分成多个小表，这些小表可以在同一个数据库中，也可以分布在不同的数据库中。

二、分库分表的策略

分库分表的策略主要有以下两种：

  1. 垂直分库分表：

    • 垂直分库：根据业务模块将不同的表分配到不同的数据库中。例如，将用户信息放在一个数据库，将订单信息放在另一个数据库。
    • 垂直分表：将一个表的列拆分成多个表。例如，将用户表拆分为用户基本信息表和用户扩展信息表。

    优点：

    • 结构清晰：垂直分库分表是根据业务模块或功能将数据拆分到不同的数据库中，结构更加清晰，便于管理。
    • 易于维护：不同的业务模块可以独立进行数据库优化和维护，减少了相互之间的影响。
    • 安全性高：不同模块的数据分开存储，可以提高数据的安全性和隔离性。
    • 适合复杂业务：对于业务逻辑复杂且模块化明显的系统，垂直分库分表可以有效地组织数据。

    缺点：

    • 跨库查询复杂：由于数据分布在不同的数据库中，涉及多个模块的数据查询会变得复杂，可能需要分布式事务支持。
    • 扩展性有限：垂直分库分表主要解决的是业务模块的隔离问题，对于单个模块内的数据量增长，扩展性有限。
    • 开发成本高：需要对系统进行详细的业务分析和设计，开发和维护成本较高。

  2. 水平分库分表：

    • 水平分库：将同一个表的数据按某种规则分配到不同的数据库中。
    • 水平分表：将同一个表的数据按某种规则分配到多个表中。

    优点：

    • 高扩展性：通过将同一张表的数据分布到多个数据库中，可以轻松应对数据量的增长，扩展性强。
    • 负载均衡：数据分布在多个数据库中，可以有效地分散读写压力，提高系统的整体性能。
    • 单库压力小：每个数据库只存储部分数据，单个数据库的压力较小，性能更好。

    缺点：

    • 分片策略复杂：需要设计合理的分片策略，以确保数据的均匀分布和查询效率。
    • 事务处理复杂：跨分片的事务处理复杂，可能需要引入分布式事务管理。
    • 数据一致性问题：在分布式环境下，数据的一致性维护变得更加复杂。
    • 开发难度大：需要对系统进行详细的设计和实现，开发和维护成本较高。

三、分库分表常用的中间件

虽然分库分表的策略已经很明确，但是要如何才能在开发中实现分库分表的效果呢？目前已经有很多成熟的中间件，只需要根据业务需求选择合适的中间件引入到项目中，就可以轻松的实现分库分表的目的。以下是现在比较常用的中间件：

  1. MyCat：
    • MyCat是一个开源的数据库中间件，支持MySQL的分库分表功能。它可以将SQL请求路由到不同的数据库实例中，并支持读写分离、分片、分布式事务等功能。
  2. ShardingSphere：
    • Apache ShardingSphere是一个开源的分布式数据库中间件解决方案，支持分库分表、读写分离、数据加密等功能。它提供了JDBC、Proxy和Sidecar三种模式，适用于不同的应用场景。
  3. Cobar：
    • Cobar是阿里巴巴开源的一个分布式数据库中间件，主要用于MySQL的分库分表。它支持SQL解析、路由、执行等功能，适合大规模数据的分布式处理。
  4. Vitess：
    • Vitess是一个开源的数据库集群系统，最初由YouTube开发。它可以将MySQL水平扩展到数千个节点，支持分库分表、自动分片、负载均衡等功能。
  5. Atlas：
    • Atlas是由Qihoo 360开发的一个MySQL协议的中间层代理，支持读写分离、分库分表等功能。它可以作为MySQL的前端代理，处理SQL请求的路由和负载均衡。

四、SpringBoot整合MyCat实现分库分表

在Spring Boot项目中整合MyCAT实现分库分表是一个复杂的过程，涉及到数据库配置、MyCAT配置以及Spring Boot的相关设置。

4.1、准备工作
  • 安装MyCAT：确保你已经安装并配置好MyCAT。
  • 数据库准备：准备好需要分库分表的数据库实例。
4.2、配置MyCAT

Mycat中，实现分库分表需要配置三个主要的XML文件：server.xml、schema.xml和rule.xml。下面是一个简单的配置示例：

1.在server.xml配置Mycat服务器的基本信息，包括用户认证、端口等。
<?xml version="1.0" encoding="UTF-8"?>
<server><system><property name="serverPort">8066</property><property name="managerPort">9066</property></system><user name="root"><property name="password">root</property></user><user name="test"><property name="password">test</property></user>
</server>2.在schema.xml文件定义逻辑库和物理库的映射关系。
<?xml version="1.0" encoding="UTF-8"?>
<schema><schema name="test_db" checkSQLschema="false" sqlMaxLimit="100"><table name="user" primaryKey="id" dataNode="dn1,dn2" rule="sharding-by-int"><childTable name="order" primaryKey="order_id" joinKey="user_id" parentKey="id" /></table></schema><dataNode name="dn1" dataHost="localhost1" database="test_db1" /><dataNode name="dn2" dataHost="localhost2" database="test_db2" /><dataHost name="localhost1" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native"><heartbeat>select 1</heartbeat><writeHost host="hostM1" url="localhost:3306" user="root" password="password"><readHost host="hostS1" url="localhost:3307" user="root" password="password" /></writeHost></dataHost><dataHost name="localhost2" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native"><heartbeat>select 1</heartbeat><writeHost host="hostM2" url="localhost:3308" user="root" password="password"><readHost host="hostS2" url="localhost:3309" user="root" password="password" /></writeHost></dataHost>
</schema>3.在rule.xml文件用于定义分片规则。
<?xml version="1.0" encoding="UTF-8"?>
<rule><tableRule name="sharding-by-int"><rule><columns>id</columns><algorithm>hash-int</algorithm></rule></tableRule><function name="hash-int" class="io.mycat.route.function.PartitionByMod"><property name="count">2</property></function>
</rule>
4.3、配置Spring Boot

  1. 添加依赖
    在pom.xml中添加MySQL驱动依赖。

    <dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId><version>8.0.33</version>
</dependency>

  2. 配置数据源
    在Spring Boot中配置数据源，指向Mycat的地址和端口。常用的方式有以下两种：

    • 在src/main/resources目录下的application.properties中配置数据源。

      spring.datasource.url=jdbc:mysql://mycat-server:8066/testDB
spring.datasource.username=root
spring.datasource.password=123456
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

    • 通过Java配置类来配置数据源，这种方式可以让你在代码中更灵活地控制数据源的配置。

      import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import javax.sql.DataSource;
import org.springframework.boot.jdbc.DataSourceBuilder;@Configuration
public class DataSourceConfig {@Beanpublic DataSource dataSource() {return DataSourceBuilder.create().url("jdbc:mysql://mycat-server:8066/testDB").username("root").password("123456").driverClassName("com.mysql.cj.jdbc.Driver").build();}
}
4.4、启动测试
  • 启动MyCAT服务。
  • 启动Spring Boot应用。
  • 测试数据库的分库分表功能，确保数据能够正确地路由到不同的数据库实例。

五、配置文件解析

  1. server.xml
    在MyCAT的conf目录下，找到server.xml文件，主要用于配置 Mycat 服务器的全局参数、用户权限、数据节点等。

    <?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://io.mycat/"><!-- Mycat 服务器的基本配置 --><system><!-- Mycat 服务器的名称 --><property name="serverName">MyCat-Server</property><!-- Mycat 服务器的监听端口 --><property name="bindPort">8066</property><!-- Mycat 服务器的字符集 --><property name="charset">utf8mb4</property><!-- Mycat 服务器的默认SQL模式 --><property name="sqlMode">STRICT_TRANS_TABLES</property></system><!-- 用户权限配置 --><user name="root"><!-- 用户密码 --><property name="password">root</property><!-- 用户可以访问的schema --><property name="schemas">schema1,schema2</property></user><!-- 数据节点配置 --><dataNode name="dn1" dataHost="localhost1" database="db1" /><dataNode name="dn2" dataHost="localhost2" database="db2" /><!-- 数据主机配置 --><dataHost name="localhost1" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native"><heartbeat>select 1</heartbeat><writeHost host="hostM1" url="localhost:3306" user="user1" password="password1"><readHost host="hostS1" url="localhost:3307" user="user1" password="password1" /></writeHost></dataHost><dataHost name="localhost2" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native"><heartbeat>select 1</heartbeat><writeHost host="hostM2" url="localhost:3308" user="user2" password="password2"><readHost host="hostS2" url="localhost:3309" user="user2" password="password2" /></writeHost></dataHost><!-- 读写分离和负载均衡策略 --><balance><readBalance>1</readBalance></balance><!-- 其他高级配置 --><property name="processorBufferPool">2048</property><property name="processorExecutor">2048</property>
</mycat:server>配置说明：
系统配置 (<system>)：serverName：Mycat 服务器的名称。bindPort：Mycat 监听的端口号，默认是 8066。charset：Mycat 使用的字符集。sqlMode：SQL 模式，影响 SQL 语句的解析和执行。用户配置 (<user>)：name：用户名。password：用户密码。schemas：用户可以访问的数据库 schema 列表。数据节点 (<dataNode>)：name：数据节点的名称。dataHost：数据节点对应的数据主机。database：数据节点对应的数据库名称，这个数据库名是在目标数据库服务器上已经存在的数据库。数据主机 (<dataHost>)：name：数据主机的名称。maxCon 和 minCon：最大和最小连接数。balance：负载均衡策略。writeType：写入类型。dbType 和 dbDriver：数据库类型和驱动。heartbeat：心跳检测 SQL。writeHost 和 readHost：主从数据库的配置，包括连接 URL、用户名和密码。负载均衡 (<balance>)：readBalance：读负载均衡策略。高级配置：processorBufferPool 和 processorExecutor：处理器的缓冲池和执行器配置。

  2. schema.xml
    在schema.xml中定义逻辑库、物理库、数据节点、分片规则等。

    <schema xmlns="http://io.mycat/"><schema name="testdb" checkSQLschema="false" sqlMaxLimit="100"><table name="user" primaryKey="id" dataNode="dn1" rule="sharding-by-id"/></schema><dataNode name="dn1" dataHost="localhost" database="testdb1"/><dataHost name="localhost" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"><heartbeat>select 1</heartbeat><writeHost host="127.0.0.1" url="jdbc:mysql://127.0.0.1:3306" user="root" password="password"/></dataHost><rule name="sharding-by-id"><columns>id</columns><algorithm>hash</algorithm></rule>
</schema>详细配置说明
schema: 定义逻辑库。name: 逻辑库的名称。checkSQLschema: 是否检查 SQL 中的 schema。sqlMaxLimit: SQL 查询的最大限制。table: 定义逻辑表。name: 表名。primaryKey: 主键字段。dataNode: 数据节点名称。rule: 分片规则名称。dataNode: 定义数据节点。name: 数据节点的名称。dataHost: 数据主机名称。database: 物理数据库名称，这个数据库名是在目标数据库服务器上已经存在的数据库。dataHost: 定义数据主机。name: 数据主机的名称。maxCon: 最大连接数。minCon: 最小连接数。balance: 负载均衡策略。writeType: 写入类型。dbType: 数据库类型（如 MySQL）。dbDriver: 数据库驱动。switchType: 切换类型。slaveThreshold: 从库阈值。heartbeat: 心跳检测 SQL。writeHost/readHost: 定义主从数据库连接。host: 主机地址。url: 数据库连接 URL。user: 数据库用户名。password: 数据库密码。rule: 定义分片规则。name: 分片规则名称。columns: 分片字段。algorithm: 分片算法。

  3. rule.xml
    在rule.xml中配置分片规则。

    <?xml version="1.0" encoding="UTF-8"?>
<rule><!-- 定义表的分片规则列表 --><tableRule><!-- 定义表的分片规则 --><table name="your_table_name"><rule><columns>your_sharding_column</columns><algorithm>your_algorithm_name</algorithm></rule></table></tableRule><!-- 定义分片算法列表 --><function><!-- 定义分片算法 --><algorithm name="your_algorithm_name" class="your.algorithm.class"><property name="property_name">property_value</property></algorithm></function>
</rule>关键部分说明：
<tableRule>: 定义表的分片规则。<table name="your_table_name">: 指定需要分片的表名。<columns>: 指定用于分片的列名。<algorithm>: 指定使用的分片算法名称。<function>: 定义分片算法。<algorithm name="your_algorithm_name" class="your.algorithm.class">: 定义算法的名称和实现类。<property>: 定义算法的属性，可以根据需要添加多个属性。

六、分片规则解析

MyCAT支持多种分片规则，主要包括以下几种：

  1. 范围分片（Range Sharding）：

    • 根据某个字段的值范围进行分片。
    • 适用于有序数据，例如时间戳、ID等。
    <?xml version="1.0" encoding="UTF-8"?>
<rule><!-- 定义范围分片算法 --><functions><!-- 使用了PartitionByRange类来实现范围分片 --><function name="rangePartition" class="io.mycat.route.function.PartitionByRange"><!-- 定义分片的范围，指定了一个文件，该文件定义了具体的分片范围 --><property name="mapFile">autopartition-long.txt</property></function></functions><!-- 定义表的分片规则 --><tableRule name="exampleRangeRule"><rule><!-- 定义分片的列 --><columns>id</columns><!-- 使用上面定义的范围分片算法 --><algorithm>rangePartition</algorithm></rule></tableRule><!-- 将分片规则应用到具体的表 --><table name="example_table" rule="exampleRangeRule" />
</rule>注意：
1.autopartition-long.txt是一个文本文件，定义了具体的分片范围，
每一行定义一个分片，格式为分片编号=起始值-结束值。文件内容可能如下：
0=0-10000
1=10001-20000
2=20001-300002.确保autopartition-long.txt文件路径正确，并且MyCAT能够访问到该文件。
3.在修改rule.xml文件后，需要重启MyCAT服务以使配置生效。

  2. 哈希分片（Hash Sharding）：

    • 使用哈希函数对某个字段的值进行分片。
    • 适用于数据分布均匀的场景。
    <?xml version="1.0" encoding="UTF-8"?>
<rule><!-- 定义具体的哈希分片算法，使用了io.mycat.route.function.PartitionByMod，这是MyCAT内置的一个哈希分片算法 --><function name="hashAlgorithm" class="io.mycat.route.function.PartitionByMod"><!-- 指定实际分片的数量，数据将被分成4个分片 --><property name="count">4</property></function><!-- 定义表的分片规则 --><tableRule name="exampleHashRule"><rule><!-- 指定用于分片的列名，user_id是用于哈希分片的列 --><columns>user_id</columns><!-- 指定使用上面定义的哈希分片算法 --><algorithm>hashAlgorithm</algorithm></rule></tableRule><!-- 将分片规则应用到具体的表 --><table name="example_table" rule="exampleHashRule" />
</rule>注意：
1.确保count的值与实际数据库中分片的数量一致。
2.user_id列应该是一个适合哈希运算的字段，通常是主键或唯一标识符。
3.根据实际需求，可能需要调整分片算法和分片数量。

  3. 枚举分片（Enum Sharding）：

    • 根据字段的枚举值进行分片。
    • 适用于字段值有限且固定的情况。
    <?xml version="1.0" encoding="UTF-8"?>
<rule><!-- 定义具体的枚举分片算法，使用了io.mycat.route.function.PartitionByEnum --><function name="enumAlgorithm" class="io.mycat.route.function.PartitionByEnum"><!-- enum-mapping.properties 文件中定义枚举值与分片的映射关系 --><property name="mapFile">enum-mapping.properties</property></function><!-- 定义表的分片规则 --><tableRule name="exampleEnumRule"><rule><!-- 指定用于分片的列名，status是用于枚举分片的列 --><columns>status</columns><!-- 指定使用上面定义的枚举分片算法 --><algorithm>enumAlgorithm</algorithm></rule></tableRule><!-- 将分片规则应用到具体的表 --><table name="example_table" rule="exampleEnumRule" />
</rule>注意：
1.假设我们有三个状态：NEW、PROCESSING、COMPLETED，并且我们希望将它们映射到不同的分片：
NEW=0
PROCESSING=1
COMPLETED=2
在这个映射文件中，NEW 状态的数据将被路由到分片 0，PROCESSING 状态的数据将被路由到分片 1，COMPLETED 状态的数据将被路由到分片 2。
2.文件路径：确保 enum-mapping.properties 文件的路径正确，并且 MyCAT 能够访问到该文件。
3.分片数量：确保分片数量与实际的数据库分片配置一致。
4.默认分片：可以在 enum-mapping.properties 中设置一个默认分片，以处理未定义的枚举值。

  4. 自定义分片（Custom Sharding）：

    • 用户可以根据自己的需求编写Java类，实现自定义的分片逻辑。
    • 适用于复杂的分片需求。
    <?xml version="1.0" encoding="UTF-8"?>
<rule><!-- 定义表的自定义分片规则 --><tableRule name="my_custom_rule"><rule><columns>column_name</columns><algorithm><function>com.example.MyCustomPartitionAlgorithm</function></algorithm></rule></tableRule><!-- 将分片规则应用到具体的表 --><table name="example_table" rule="my_custom_rule" />
</rule>自定义分片算法实现：
1.编写一个MyCustomPartitionAlgorithm的Java类，实现io.mycat.route.function.PartitionByCRC32或RuleAlgorithm接口。
2.实现calculate方法，定义具体的分片逻辑。
3.将编译后的类放入MyCAT的类路径中。

  5. 模数分片（Mod Sharding）：

    • 通过对某个字段的值取模进行分片。
    • 适用于ID等数值型字段。
    <?xml version="1.0" encoding="UTF-8"?>
<rule><!-- 定义具体的取模分片算法，使用了io.mycat.route.function.PartitionByMod --><function name="modShardAlgorithm" class="io.mycat.route.function.PartitionByMod"><!-- 指定分片的数量 --><property name="count">4</property> </function><!-- 定义表的取模分片规则 --><tableRule name="modShardRule"><rule><!-- 指定用于分片的列名，user_id是用于枚举分片的列 --><columns>user_id</columns><!-- 指定使用上面定义的取模分片算法 --><algorithm>modShardAlgorithm</algorithm></rule></tableRule><!-- 将分片规则应用到具体的表 --><table name="example_table" rule="modShardRule" />
</rule>

  6. 日期分片（Date Sharding）：

    • 根据日期进行分片。
    • 适用于按时间周期分片的场景。
    <?xml version="1.0" encoding="UTF-8"?>
<rule><!-- 定义具体的日期分片算法，使用了io.mycat.route.function.PartitionByDate --><function name="dateMonthPartition" class="io.mycat.route.function.PartitionByDate"><!-- 定义日期格式，这里使用yyyy-MM表示按月分片 --><property name="dateFormat">yyyy-MM</property><!-- 分片的起始日期 --><property name="sBeginDate">2020-01</property><!-- 每个分片的天数，这里设置为30天 --><property name="sPartionDay">30</property><!-- 分片的数量，这里设置为12，表示一年12个月 --><property name="sPartionNum">12</property></function><!-- 定义表的取模分片规则 --><tableRule name="order_date_rule"><rule><!-- 指定用于分片的列名，order_date是用于枚举分片的列 --><columns>order_date</columns><!-- 指定使用上面定义的日期分片算法 --><algorithm>dateMonthPartition</algorithm></rule></tableRule><!-- 将分片规则应用到具体的表 --><table name="example_table" rule="order_date_rule" />
</rule>注意：
1.日期格式：确保dateFormat与数据库中日期字段的格式一致。
2.起始日期：sBeginDate应根据实际数据的最早日期设置。
3.分片数量：sPartionNum应根据业务需求和数据量进行调整。

七、使用 MyCAT 分库分表后查询操作

在使用MyCAT进行分库分表后，查询语句的编写需要考虑MyCAT的分片规则和路由机制。以下是一些编写查询语句的建议和注意事项：

  1. 使用逻辑表名：在MyCAT中，应用程序只需关注逻辑表名，而不需要关心物理表名。MyCAT会根据配置的分片规则自动将查询路由到正确的物理表。
  2. 分片键的使用：在查询语句中，尽量使用分片键作为查询条件。这有助于MyCAT快速定位到具体的分片，减少全局扫描，提高查询效率。
  3. 避免全表扫描：如果查询条件中不包含分片键，MyCAT可能需要扫描所有分片，导致性能下降。尽量避免这种情况，或者在业务上允许的情况下，使用MyCAT的全局表功能。
  4. 聚合查询和排序：对于需要跨分片进行聚合或排序的查询，MyCAT会在各个分片上执行子查询，然后在中间件层面进行合并。这种操作可能会比较耗时，建议在业务上尽量减少此类查询，或者在应用层进行处理。
  5. 分页查询：分页查询在分库分表环境下可能会比较复杂，尤其是当需要跨多个分片进行分页时。可以考虑在应用层进行分页逻辑的处理，或者使用MyCAT的分页支持功能。
  6. 读写分离：如果配置了读写分离，确保查询语句根据业务需求正确路由到主库或从库。通常，SELECT语句会被路由到从库，而INSERT、UPDATE、DELETE语句会被路由到主库。
  7. 全局表和ER表：如果有一些数据需要在所有分片中都存在，可以使用MyCAT的全局表功能。对于有主从关系的表，可以使用ER表功能来确保数据一致性。

http://www.mrgr.cn/news/65543.html

相关文章：

  • 第二十八章 Vue之自定义指令
  • 从数据提取到管理：TextIn平台的全面解析与产品体验
  • word mathml 创建粗体字母快捷键
  • 开源OCR免费助力法律文档数字化，提升文档管理效率
  • 将分类标签转换为模型可以处理的数值格式
  • 15. 文件操作
  • 手把手教你轻松掌握~Air780E软件UDP应用示例！快来看！
  • 论负载均衡技术在Web系统中的应用
  • Manjaro Linux安装过程简介
  • 华为HarmonyOS打造开放、合规的广告生态 - 开屏广告
  • 华为HarmonyOS打造开放、合规的广告生态 - 插屏广告
  • 工时管理系统怎么选？用对工具助你快速提升效率
  • Google推出MusicFX DJ，用AI实时混合文本提示生成独特音乐
  • 刷题计划 day15 二叉树（四）【完全二叉树的节点个数】【平衡二叉树】【二叉树的所有路径】
  • ONLYOFFICE：数字化办公的创新解决方案与高效协作平台
  • awvs Acunetix-v24.9.241015145 win/linux版本
  • 基于java+SpringBoot+Vue的大学生就业招聘系统设计与实现
  • 使用ffmpeg和mediamtx模拟多通道rtsp相机
  • 递归 搜索 回溯 算法专题
  • 链接分析与反向链接的重要性及最佳实践解析
  • 新160个crackme - 091-DOSKEY-CRACKME2
  • 【Java 8】方法引用
  • RT-Thread PIN设备 UART设备
  • 2023年SCRM系统排名分析及市场趋势解读
  • 7. 触发模式
  • C++中，如何找到一个vector中最大的元素