十四、MySQL高级— 分库分表（7）

最新推荐文章于 2024-09-11 23:33:17 发布
最新推荐文章于 2024-09-11 23:33:17 发布
阅读量708 收藏 8
点赞数 12
分类专栏： # 计算机（Java高级）高级 阶段 文章标签： mysql 数据库 linux
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_42171159/article/details/142109539
版权

🌻🌻 目录

文章大纲 👇👇

在这里插入图片描述

一、分库

1.1 修改配置 schema.xml

① 修改数据库创建完并重启 mycat

在这里插入图片描述

② 修改配置

在这里插入图片描述

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
 <!--逻辑库    name名称,   checkSQLschema         sqlMaxLimit 末尾是否要加 limit xxx-->
        <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1"> 
                <table name="customer" dataNode="dn2" ></table>
         </schema>
            <!--逻辑库    name名称,   dataHost 引用的哪个dataHost      database:对应mysql的database-->
        <dataNode name="dn1" dataHost="host1" database="mydb01" />
        <dataNode name="dn2" dataHost="host2" database="mydb02" />
        <dataHost name="host1" maxCon="1000" minCon="10" balance="0"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <!-- can have multi write hosts -->
                <writeHost host="hostM1" url="192.168.10.100:3306" user="root"
                                   password="root">
                </writeHost>
        </dataHost>

                <dataHost name="host2" maxCon="1000" minCon="10" balance="0"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <!-- can have multi write hosts -->
                <writeHost host="hostM2" url="192.168.10.100:3306" user="root"
                                   password="root">
                </writeHost>
        </dataHost>
</mycat:schema>

③ 在主机与从机上分别创建数据库 mydb01;

  • 主机上创建

在这里插入图片描述

  • 从机上创建

在这里插入图片描述

1.2 如何选择分库表

① 在mycat里面创建表

在这里插入图片描述

#客户表  rows:20万 
CREATE TABLE customer(
    id INT AUTO_INCREMENT,
    NAME VARCHAR(200),
    PRIMARY KEY(id)
);
 
#订单表   rows:600万
CREATE TABLE orders(
    id INT AUTO_INCREMENT,
    order_type INT,
    customer_id INT,
    amount DECIMAL(10,2),
    PRIMARY KEY(id)  
); 
 
#订单详细表     rows:600万
CREATE TABLE orders_detail(
    id INT AUTO_INCREMENT,
    detail VARCHAR(2000),
    order_id INT,
    PRIMARY KEY(id)
);
 
#订单状态字典表   rows:20
CREATE TABLE dict_order_type(
    id INT AUTO_INCREMENT,
    order_type VARCHAR(200),
    PRIMARY KEY(id)
);
 
 select o.*,od.detail,d.order_type 
from orders  o 
inner join orders_detail  od on o.id =od.order_id  
 inner join dict_order_type d on o.order_type=d.id  
where o.customer_id=xxxx

② 最终从机中只有customer表，其余表都到了主机中到库中去了

  • 从机显示

在这里插入图片描述

  • 主机显示

在这里插入图片描述

1.3 SQLyog 连接 mycat

在这里插入图片描述

在这里插入图片描述

二、水平分表

2.1 schema.xml

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
 
        <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
                <table name="customer" dataNode="dn2" ></table>
                <table name="orders" dataNode="dn1,dn2"  rule="mod_rule" ></table>
        </schema>
        <dataNode name="dn1" dataHost="host1" database="atguigu_mc" />
        <dataNode name="dn2" dataHost="host2" database="atguigu_sm" />
        <dataHost name="host1" maxCon="1000" minCon="10" balance="2"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <writeHost host="hostm1" url="192.168.67.1:3306" user="root"
                                   password="123123">
                <!--       <readHost host="hosts1" url="192.168.67.131:3306" user="root"
                                   password="123123">
                       </readHost>-->
                </writeHost>
        </dataHost>
        <dataHost name="host2" maxCon="1000" minCon="10" balance="0"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <writeHost host="hostm2" url="192.168.67.1:3306" user="root"
                                   password="123123">
                </writeHost>
        </dataHost>

2.2 rule.xml

<tableRule name="mod_rule">
             <rule>
                        <columns>customer_id</columns>
                        <algorithm>mod-long</algorithm>
               </rule>
       </tableRule>
 
 
<function name="mod-long" class="io.mycat.route.function.PartitionByMod">
                <!-- how many data nodes -->
                <property name="count">2</property>
        </function>

2.3 跨库join

2.3.1 ER表

为了相关联的表的行尽量分在一个库下
schema.xml

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
 
        <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
                <table name="customer" dataNode="dn2" ></table>
                <table name="orders" dataNode="dn1,dn2"  rule="mod_rule" >
                    <childTable name="orders_detail" primaryKey="id" joinKey="order_id" parentKey="id" />
                </table>
                <table name="dict_status" dataNode="dn1,dn2" type="global" ></table>
        </schema>
        <dataNode name="dn1" dataHost="host1" database="atguigu_mc" />
        <dataNode name="dn2" dataHost="host2" database="atguigu_sm" />
        <dataHost name="host1" maxCon="1000" minCon="10" balance="2"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <writeHost host="hostm1" url="192.168.67.1:3306" user="root"
                                   password="123123">
                <!--       <readHost host="hosts1" url="192.168.67.131:3306" user="root"
                                   password="123123">
                       </readHost>-->
                </writeHost>
        </dataHost>
        <dataHost name="host2" maxCon="1000" minCon="10" balance="0"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <writeHost host="hostm2" url="192.168.67.1:3306" user="root"
                                   password="123123">
                </writeHost>
        </dataHost>
 
</mycat:schema>

建相关表

2.3.2 全局表

设定为全局的表，会直接复制给每个数据库一份，所有写操作也会同步给多个库。
所以全局表一般不能是大数据表或者更新频繁的表
一般是字典表或者系统表为宜。
schema.xml

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
 
        <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
                <table name="customer" dataNode="dn2" ></table>
                <table name="orders" dataNode="dn1,dn2"  rule="mod_rule" ></table>
                 <table name="dict_order_type" dataNode="dn1,dn2" type="global" ></table>
        </schema>
        <dataNode name="dn1" dataHost="host1" database="atguigu_mc" />
        <dataNode name="dn2" dataHost="host2" database="atguigu_sm" />
        <dataHost name="host1" maxCon="1000" minCon="10" balance="2"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <writeHost host="hostm1" url="192.168.67.1:3306" user="root"
                                   password="123123">
                <!--       <readHost host="hosts1" url="192.168.67.131:3306" user="root"
                                   password="123123">
                       </readHost>-->
                </writeHost>
        </dataHost>
        <dataHost name="host2" maxCon="1000" minCon="10" balance="0"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <writeHost host="hostm2" url="192.168.67.1:3306" user="root"
                                   password="123123">
                </writeHost>
        </dataHost>

2.4 全局序列

三种方式

2.4.1 本地文件

不推荐

2.4.2 数据库方式(一般都用这个)

  1. 数据库序列方式原理
  • 利用数据库一个表 来进行计数累加。
  • 但是并不是每次生成序列都读写数据库，这样效率太低
  • mycat会预加载一部分号段到mycat的内存中，这样大部分读写序列都是在内存中完成的。
    如果内存中的号段用完了 mycat会再向数据库要一次。
  • 问：那如果mycat崩溃了 ，那内存中的序列岂不是都没了？
  • 是的。如果是这样，那么mycat启动后会向数据库申请新的号段，原有号段会弃用。
    也就是说如果mycat重启，那么损失是当前的号段没用完的号码，但是不会因此出现主键重复。
  1. 建库序列脚本（官方脚本切勿随意修改）
# 主机下面运行(win10)
 
CREATE TABLE MYCAT_SEQUENCE (NAME VARCHAR(50) NOT NULL,current_value INT NOT
NULL,increment INT NOT NULL DEFAULT 100, PRIMARY KEY(NAME)) ENGINE=INNODB;
 
DELIMITER $$
 
CREATE FUNCTION mycat_seq_currval(seq_name VARCHAR(50)) RETURNS VARCHAR(64)
DETERMINISTIC  
BEGIN
DECLARE retval VARCHAR(64);
SET retval="-999999999,null";
SELECT CONCAT(CAST(current_value AS CHAR),",",CAST(increment AS CHAR)) INTO retval FROM
MYCAT_SEQUENCE WHERE NAME = seq_name;
RETURN retval;
END $$
DELIMITER;
 
DELIMITER $$
CREATE FUNCTION mycat_seq_setval(seq_name VARCHAR(50),VALUE INTEGER) RETURNS VARCHAR(64)
DETERMINISTIC
BEGIN
UPDATE MYCAT_SEQUENCE
SET current_value = VALUE
WHERE NAME = seq_name;
RETURN mycat_seq_currval(seq_name);
END $$
DELIMITER ;
 
DELIMITER $$
CREATE FUNCTION mycat_seq_nextval(seq_name VARCHAR(50)) RETURNS VARCHAR(64) 
DETERMINISTIC
BEGIN
UPDATE MYCAT_SEQUENCE
SET current_value = current_value + increment WHERE NAME = seq_name;
RETURN mycat_seq_currval(seq_name);
END $$
DELIMITER;
 
 
SELECT * FROM MYCAT_SEQUENCE
 
TRUNCATE TABLE MYCAT_SEQUENCE
 
##增加要用的序列
INSERT INTO MYCAT_SEQUENCE(NAME,current_value,increment) VALUES ('ORDERS', 400000,
100);
  1. 修改mycat配置
  • sequence_db_conf.properties
    vim sequence_db_conf.properties

在这里插入图片描述

意思是 ORDERS这个序列在dn1这个节点上，具体dn1节点是哪台机子，请参考schema.xml

  • server.xml
    vim server.xml

在这里插入图片描述

然后重启

  1. 插入语句
insert into `orders`(id,amount,customer_id,order_type) values(next value for MYCATSEQ_ORDERS,1000,101,102);

2.4.3 时间戳方式

比较长

2.4.4 自主生成

  • 根据业务逻辑组合
  • 可以利用 redis的单线程原子性 incr来生成序列

MySQL前面高级总结所有资料

Daniel521-Spark
关注
  • 12
    点赞
  • 踩
  • 8
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
专栏目录
MySQL：互联网公司常用分库分表方案汇总！_数据库分库分表设计方案中,s方案
2401_84103844的博客
04-30 812
看完美团、字节、腾讯这三家的一二三面试问题，是不是感觉问的特别多，可能咱们真的又得开启面试造火箭、工作拧螺丝的模式去准备下一次的面试了。开篇有提及我可是足足背下了Java互联网工程师面试1000题，多少还是有点用的呢，换汤不换药，不管面试官怎么问你，抓住本质即可！能读到此处的都是真爱Java互联网工程师面试1000题。
读书笔记：高性能MySQL之路涉及MySQL高级部分知识分库分表SQL优化等内容.zip
07-29
读书笔记：高性能MySQL之路涉及MySQL高级部分知识分库分表SQL优化等内容
MySQL高级：分库分表是什么？——垂直分表&水平分表等
master_chenchen的博客
06-10 674
在设计分库分表策略时，必须仔细考虑数据的访问模式、业务增长预期以及系统的维护成本。分库分表作为一种高效的解决方案，能够有效地提高系统的可扩展性和性能。本文将深入介绍分库分表的基本概念、实现方式以及在MySQL中的应用。在分库环境中，需要一个全局唯一的ID生成策略以确保不同数据库中的ID不会重复。分库分表是将数据分散存储到多个数据库或表中的技术。分库是把数据分散到多个数据库实例中，而分表则是将数据分散到同一个数据库实例的多个表中。根据一定的规则（如用户ID），将数据访问请求路由到特定的数据库或表中。
MySQL 高级 —— 分库分表是什么？【含代码示例】
一个做过前端开发的产品经理，经历过睿智产品的折磨导致脱发之后，励志要翻身"农奴"把歌唱，一边打入敌人内部一边持续提升自己，为我们广大开发同胞谋福祉，坚决抵制睿智产品折磨我们码农兄弟！
06-29 933
分库分表是将一个大数据库拆分成多个小数据库，将一个大表拆分成多个小表的技术手段。这一策略基于“分而治之”的原则，通过将数据分散到不同的数据库或表中，可以显著提高系统的并发处理能力和数据读写效率，同时也便于数据管理和维护。分库分表是大型互联网应用中不可或缺的数据库架构优化策略，它不仅能有效提升系统的并发处理能力和数据读写效率，还能增强系统的可扩展性和可用性。掌握分库分表的原理与实践，对于MySQL数据库开发人员来说是一项宝贵的技能。希望本文能为你的数据库优化之路提供有价值的参考和启示。
【技术方案选型】MySQL数据库分库分表方案
DreamSun的博客
07-19 564
随着项目不断迭代，使用人数的不断增加。数据库中某些表数据正在逐步膨胀，往单表千万迅速靠拢。所以最近领导也在考虑做一下分库分表，写此文章记录下来。分库：从单个数据库拆分成多个数据库的过程，将数据散落在多个数据库中。分表：从单张表拆分成多张表的过程，将数据散落在多张表内。挑选了两个使用最多的进行了比较，综合来看的话感觉还是Sharding-jdbc更省事一些，无需部署中间件，只通过引入jar包进行分库分表操作，省去一些事情。而且多一个中间件的话系统稳定性也会降低了。
MySQl高级篇-分库分表（为什么需要分表？什么时候分库分表）
ShockChen7的博客
08-10 1408
分库分表是在海量数据下，由于单库、表数据量过大，导致数据库性能持续下降的问题，演变出的技术方案。
MySQL 高级：分库分表之核心 Sharding-JDBC 深入解析【含代码示例】
一个做过前端开发的产品经理，经历过睿智产品的折磨导致脱发之后，励志要翻身"农奴"把歌唱，一边打入敌人内部一边持续提升自己，为我们广大开发同胞谋福祉，坚决抵制睿智产品折磨我们码农兄弟！
06-28 756
Sharding-JDBC 是一款轻量级的Java框架，它以JDBC驱动的形式存在，为应用程序提供了一种透明的方式来实现数据库的分片、读写分离、以及复杂的SQL查询等功能。与传统的中间件或代理相比，Sharding-JDBC的优势在于其低侵入性，几乎不需要修改现有的应用程序代码，即可享受分库分表带来的性能提升。通过本文的深入探讨，我们不仅了解了Sharding-JDBC在MySQL中实现分库分表的原理与步骤，还学习了在实际开发中如何运用这一技术来提升数据库性能和系统稳定性。
Python+MySQL分表分库实战
02-18
综上所述，"Python+MySQL分表分库实战"的学习内容涵盖了数据库设计原则、Python数据库操作技巧、分库分表策略以及性能优化等多个方面。通过学习和实践，开发者可以有效地解决大数据场景下的存储和查询问题，提高系统...
基于mybatis-plus+sharding+mysql的分库分表项目源码.zip
02-03
此外，MySQL的分区功能也可以辅助分库分表，但Sharding-JDBC通常会提供更高级的分片策略和管理。 **项目结构与实现** "mybatis-plus-dynamic-sharding-main"这个文件名可能代表项目的主模块，包含了Mybatis-Plus的...
基于SpringBoot+Vue+MySQL的考研互助交流平台
程序员大金的博客
09-11 403
基于SpringBoot+Vue+MySQL的考研互助交流平台，附源码。
mysql组合键唯一
qq_36321899的博客
09-11 258
这些方法可以帮助您在MySQL数据库中设置多个字段的组合唯一性，确保数据的完整性和准确性。如果您在操作过程中遇到任何问题，可以参考相关的MySQL文档或搜索相应的解决方案。请注意，在添加组合唯一键之前，确保表中的数据已经满足唯一性约束，否则操作将会失败。如果表中已经存在重复的组合数据，您需要先清理这些数据，然后才能成功添加唯一键。在MySQL中，如果您想要确保两个或多个列的组合数据是唯一的，您可以使用组合唯一键（也称为复合唯一键或联合唯一索引）。表添加一个组合唯一键，以确保。例如，如果您有一个名为。
MySQL表操作及约束
2301_77479435的博客
09-06 1087
MySQL表操作及约束
深入解析Go语言中的条件控制与数据处理
一个被知识诅咒的人
09-08 1121
本文详细介绍了Go语言中的switch语句及其在正则表达式匹配中的应用，探讨了如何通过math/big包进行高精度圆周率计算。此外，文章还展示了如何实现一个简易键值对存储系统，并通过encoding/json和encoding/xml包处理常见的JSON和XML数据格式。通过这些示例和知识点，读者将能掌握Go语言中条件控制和数据处理的高级应用，提升编写高效代码的能力。
MySQL的 where 1=1会不会影响性能
ning的博客
09-07 618
通过分析和实验，我们可以得出结论：在现代的MySQL版本中，使用where 1=1并不会对查询性能产生显著影响，因为MySQL的优化器会对其进行常量折叠优化。然而，选择最合适的方法还需要考虑你的具体使用场景和ORM框架。在任何情况下，编写清晰、可维护的代码始终是最重要的。版权声明：本博客内容为原创，转载请保留原文链接及作者信息。参考文章MySQL中where 1=1真的会影响性能么？MySQL的 where 1=1会不会影响性能？看完官方文档就悟了！
基于SpringBoot+Vue+MySQL的瑜伽馆管理系统
最新发布
程序员大金的博客
09-11 798
基于SpringBoot+Vue+MySQL的瑜伽馆管理系统，附源码。
MySQL record 03 part
m0_47505062的博客
09-08 915
注意，百分号和下划线在MySQL中都是被当作通配符来使用的，因此当要匹配的字符是百分号和下划线本身的时候，也就是你要搜索百分号和下划线本身的时候，要将百分号和下划线转义成普通字符，方法是使用右斜线。更灵活，当 DELETE FROM 不指定where时，时删除所有表记录，而指定where时则是删除指定的符合where条件的记录。一般情况下，向表中添加新的记录，应该包含此表的所有字段，也就是应该给表的所有字段添加值，也有2种方式，指定字段和不指定字段，这里使用的是不指定字段的方式，
基于SpringBoot+Vue+MySQL的教学资源共享平台
程序员大金的博客
09-11 522
基于SpringBoot+Vue+MySQL的教学资源共享平台，附源码。
MySQL Workbench 的入门指南
布说在见的博客
09-06 1210
MySQL Workbench 是一个官方的图形化工具，用于开发、管理和设计 MySQL 数据库服务器。它提供了丰富的功能，可以帮助数据库管理员、开发者以及DBA们高效地工作。下面是一个MySQL Workbench的入门指南，介绍如何安装和使用它。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

Daniel521-Spark

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值