AI 助理
备案控制台
开发者社区 数据库 文章 正文

MySQL分库分表

2023-12-29 73
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
云数据库 RDS MySQL，集群系列 2核4GB
推荐场景：
搭建个人博客
RDS MySQL Serverless 基础系列，0.5-2RCU 50GB
推荐场景：
学生管理系统数据库设计 搭建个人博客
RDS MySQL Serverless 高可用系列，价值2615元额度，1个月
简介： MySQL分库分表

MySQL分库分表

https://blog.csdn.net/csdn_heshangzhou/article/details/82963237

https://mp.weixin.qq.com/s/BDXrSFG5KwIi9OjOZcOaYw

 

一、水平分表

这里做的是我的一个笔记。

水平分表比较简单, 理解就是:

  • 合并的表使用的必须是MyISAM引擎
  • 表的结构必须一致，包括索引、字段类型、引擎和字符集

数据表

user1

CREATE TABLE `user1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  `sex` int(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

user2

create table user2 like user1;

user

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  `sex` int(1) NOT NULL DEFAULT '0',
  KEY `id` (`id`)
) ENGINE=MRG_MyISAM DEFAULT CHARSET=utf8 INSERT_METHOD=LAST UNION=(`user1`,`user2`);

 

1) ENGINE = MERGE 和 ENGINE = MRG_MyISAM是一样的意思，都是代表使用的存储引擎是 Merge。

2) INSERT_METHOD，表示插入方式，取值可以是：0 和 1，0代表不允许插入，1代表可以插入；

3) FIRST插入到UNION中的第一个表，LAST插入到UNION中的最后一个表。

触发器

create table tb_ids(id int);
insert into tb_ids values(1);

如果user1和user2中有数据的话先清除

delete from user1;
delete from user2;

然后在user1和user2表中分别建立一个触发器(tr_seq和tr_seq2)，触发器的功能是 当在user1或者user2表中增加一条记录时，取出tb_ids中的id值，赋给user1和user2的id，然后将tb_ids的id值加1.

user1的触发器:tr_seq

DELIMITER $$
   CREATE TRIGGER tr_seq
   BEFORE INSERT on user1
   FOR EACH ROW BEGIN
      select id  into @testid from tb_ids limit 1;
      update tb_ids set id = @testid + 1;
   set new.id =  @testid;
   END$$
   DELIMITER;

user2的触发器:tr_seq2

DELIMITER $$
   CREATE TRIGGER tr_seq2
   BEFORE INSERT on user2
   FOR EACH ROW BEGIN
      select id  into @testid from tb_ids limit 1;
      update tb_ids set id = @testid + 1;
   set new.id =  @testid;
   END$$
   DELIMITER;

我是直接扔进一个sql文件source执行的, 效果都一样.

然后查询一下触发器

select * from information_schema.triggers where TRIGGER_NAME='tr_seq' \G;

此时已经分表成功, 下面插入数据

mysql>  insert into user1(name,sex) values('王五',1);
Query OK, 1 row affected (0.02 sec)
mysql>  insert into user2(name,sex) values('张飞',2);
Query OK, 1 row affected (0.01 sec)
mysql>  insert into user2(name,sex) values('张飞',2);
Query OK, 1 row affected (0.01 sec)
mysql>  insert into user2(name,sex) values('张飞',2);
Query OK, 1 row affected (0.01 sec)
mysql>  insert into user2(name,sex) values('张飞',2);
Query OK, 1 row affected (0.01 sec)
mysql>  insert into user2(name,sex) values('张飞',2);
Query OK, 1 row affected (0.01 sec)
mysql>  insert into user2(name,sex) values('张飞',2);
Query OK, 1 row affected (0.00 sec)
mysql>  insert into user2(name,sex) values('张飞',2);
Query OK, 1 row affected (0.01 sec)
mysql>  insert into user2(name,sex) values('张飞',2);
Query OK, 1 row affected (0.01 sec)
mysql>  insert into user2(name,sex) values('张飞',2);
Query OK, 1 row affected (0.00 sec)
mysql>  insert into user1(name,sex) values('王五',1);
Query OK, 1 row affected (0.01 sec)
mysql>  insert into user1(name,sex) values('王五',1);
Query OK, 1 row affected (0.01 sec)
mysql>  insert into user1(name,sex) values('王五',1);
Query OK, 1 row affected (0.10 sec)
mysql>  insert into user1(name,sex) values('王五',1);
Query OK, 1 row affected (0.01 sec)
mysql>  insert into user1(name,sex) values('王五',1);
Query OK, 1 row affected (0.01 sec)
mysql>  insert into user1(name,sex) values('王五',1);
Query OK, 1 row affected (0.01 sec)
mysql>  insert into user1(name,sex) values('王五',1);
Query OK, 1 row affected (0.01 sec)

查询一下所有数据库

1

show tables;

查询user1

mysql> select * from user1;
+----+--------+-----+
| id | name   | sex |
+----+--------+-----+
|  1 | 王五   |   1 |
| 11 | 王五   |   1 |
| 12 | 王五   |   1 |
| 13 | 王五   |   1 |
| 14 | 王五   |   1 |
| 15 | 王五   |   1 |
| 16 | 王五   |   1 |
| 17 | 王五   |   1 |
+----+--------+-----+
8 rows in set (0.00 sec)

查询user2

mysql> select * from user2;
+----+--------+-----+
| id | name   | sex |
+----+--------+-----+
|  2 | 张飞   |   2 |
|  3 | 张飞   |   2 |
|  4 | 张飞   |   2 |
|  5 | 张飞   |   2 |
|  6 | 张飞   |   2 |
|  7 | 张飞   |   2 |
|  8 | 张飞   |   2 |
|  9 | 张飞   |   2 |
| 10 | 张飞   |   2 |
+----+--------+-----+
9 rows in set (0.00 sec)

再插入几条数据, 前面插入的太偏向了

mysql>  insert into user1(name,sex) values('王五',1);
Query OK, 1 row affected (0.01 sec)
mysql>  insert into user2(name,sex) values('王五',1);
Query OK, 1 row affected (0.01 sec)
mysql>  insert into user2(name,sex) values('王五',4);
Query OK, 1 row affected (0.04 sec)
mysql>  insert into user1(name,sex) values('王五',4);
Query OK, 1 row affected (0.01 sec)

此时查看user

mysql> select * from user order by id asc;
+----+--------+-----+
| id | name   | sex |
+----+--------+-----+
|  1 | 王五   |   1 |
|  2 | 张飞   |   2 |
|  3 | 张飞   |   2 |
|  4 | 张飞   |   2 |
|  5 | 张飞   |   2 |
|  6 | 张飞   |   2 |
|  7 | 张飞   |   2 |
|  8 | 张飞   |   2 |
|  9 | 张飞   |   2 |
| 10 | 张飞   |   2 |
| 11 | 王五   |   1 |
| 12 | 王五   |   1 |
| 13 | 王五   |   1 |
| 14 | 王五   |   1 |
| 15 | 王五   |   1 |
| 16 | 王五   |   1 |
| 17 | 王五   |   1 |
| 18 | 王五   |   1 |
| 19 | 王五   |   1 |
| 20 | 王五   |   4 |
| 21 | 王五   |   4 |
+----+--------+-----+
21 rows in set (0.00 sec)

user1

mysql> select * from user1 order by id asc;
+----+--------+-----+
| id | name   | sex |
+----+--------+-----+
|  1 | 王五   |   1 |
| 11 | 王五   |   1 |
| 12 | 王五   |   1 |
| 13 | 王五   |   1 |
| 14 | 王五   |   1 |
| 15 | 王五   |   1 |
| 16 | 王五   |   1 |
| 17 | 王五   |   1 |
| 18 | 王五   |   1 |
| 21 | 王五   |   4 |
+----+--------+-----+
10 rows in set (0.00 sec)

user2

mysql> select * from user2 order by id asc;
+----+--------+-----+
| id | name   | sex |
+----+--------+-----+
|  2 | 张飞   |   2 |
|  3 | 张飞   |   2 |
|  4 | 张飞   |   2 |
|  5 | 张飞   |   2 |
|  6 | 张飞   |   2 |
|  7 | 张飞   |   2 |
|  8 | 张飞   |   2 |
|  9 | 张飞   |   2 |
| 10 | 张飞   |   2 |
| 19 | 王五   |   1 |
| 20 | 王五   |   4 |
+----+--------+-----+
11 rows in set (0.00 sec)

下面我们update一下

mysql> update user set name='刘备' where id ='9';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

再看一下user

mysql> select * from user order by id asc;
+----+--------+-----+
| id | name   | sex |
+----+--------+-----+
|  1 | 王五   |   1 |
|  2 | 张飞   |   2 |
|  3 | 张飞   |   2 |
|  4 | 张飞   |   2 |
|  5 | 张飞   |   2 |
|  6 | 张飞   |   2 |
|  7 | 张飞   |   2 |
|  8 | 张飞   |   2 |
|  9 | 刘备   |   2 |
| 10 | 张飞   |   2 |
| 11 | 王五   |   1 |
| 12 | 王五   |   1 |
| 13 | 王五   |   1 |
| 14 | 王五   |   1 |
| 15 | 王五   |   1 |
| 16 | 王五   |   1 |
| 17 | 王五   |   1 |
| 18 | 王五   |   1 |
| 19 | 王五   |   1 |
| 20 | 王五   |   4 |
| 21 | 王五   |   4 |
+----+--------+-----+
21 rows in set (0.00 sec)

user1

mysql> select * from user1;
+----+--------+-----+
| id | name   | sex |
+----+--------+-----+
|  1 | 王五   |   1 |
| 11 | 王五   |   1 |
| 12 | 王五   |   1 |
| 13 | 王五   |   1 |
| 14 | 王五   |   1 |
| 15 | 王五   |   1 |
| 16 | 王五   |   1 |
| 17 | 王五   |   1 |
| 18 | 王五   |   1 |
| 21 | 王五   |   4 |
+----+--------+-----+
10 rows in set (0.00 sec)

user2

mysql> select * from user2;
+----+--------+-----+
| id | name   | sex |
+----+--------+-----+
|  2 | 张飞   |   2 |
|  3 | 张飞   |   2 |
|  4 | 张飞   |   2 |
|  5 | 张飞   |   2 |
|  6 | 张飞   |   2 |
|  7 | 张飞   |   2 |
|  8 | 张飞   |   2 |
|  9 | 刘备   |   2 |
| 10 | 张飞   |   2 |
| 19 | 王五   |   1 |
| 20 | 王五   |   4 |
+----+--------+-----+
11 rows in set (0.00 sec)

 

水平分表完成!


文章标签：
云数据库 RDS MySQL 版
宜搭
关系型数据库
MySQL
SQL
数据库
存储
关键词：
云数据库 RDS MySQL 版分库分表
相关实践学习
如何在云端创建MySQL数据库
开始实验后，系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心，利用清洁计算为万物互联的新世界提供源源不断的能源动力，目前开服的区域包括中国（华北、华东、华南、香港）、新加坡、美国（美东、美西）、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程，来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品：云服务器ECS 云服务器 ECS（Elastic Compute Service）是一种弹性可伸缩的计算服务，助您降低 IT 成本，提升运维效率，使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
诸葛子房
目录
相关文章
流烟默
|
7月前
|
关系型数据库 MySQL Java
MySQL单表膨胀优化之MyCat分库分表
MySQL单表膨胀优化之MyCat分库分表
流烟默
144 0
.29.
|
7月前
|
SQL 关系型数据库 MySQL
②⑩① 【MySQL】什么是分库分表？拆分策略有什么？什么是MyCat？
②⑩① 【MySQL】什么是分库分表？拆分策略有什么？什么是MyCat？
.29.
107 0
倪虹升
|
7月前
|
SQL 存储 关系型数据库
Mysql系列-5.Mysql分库分表(中)
Mysql系列-5.Mysql分库分表
倪虹升
68 0
终有救赎
|
7月前
|
中间件 关系型数据库 Java
MySQL数据库分库分表方案
MySQL数据库分库分表方案
终有救赎
290 0
MySQL数据库分库分表方案
竹子爱熊猫
|
4月前
|
存储 算法 关系型数据库
(二十二)全解MySQL之分库分表后带来的“副作用”一站式解决方案！
上篇《分库分表的正确姿势》中已经将分库分表的方法论全面阐述清楚了，总体看下来用一个字形容，那就是爽！尤其是分库分表技术能够让数据存储层真正成为三高架构，但前面爽是爽了，接着一起来看看分库分表后产生一系列的后患问题，注意我这里的用词，是一系列而不是几个，也就是分库分表虽然好，但你要解决的问题是海量的。
竹子爱熊猫
432 3
程序员小海绵
|
3月前
|
存储 SQL 关系型数据库
一篇文章搞懂MySQL的分库分表，从拆分场景、目标评估、拆分方案、不停机迁移、一致性补偿等方面详细阐述MySQL数据库的分库分表方案
MySQL如何进行分库分表、数据迁移？从相关概念、使用场景、拆分方式、分表字段选择、数据一致性校验等角度阐述MySQL数据库的分库分表方案。
程序员小海绵
490 15
一篇文章搞懂MySQL的分库分表，从拆分场景、目标评估、拆分方案、不停机迁移、一致性补偿等方面详细阐述MySQL数据库的分库分表方案
嘟嘟嘟嘟嘟嘟
|
7月前
|
NoSQL 关系型数据库 MySQL
实时计算 Flink版操作报错之同步MySQL分库分表500张表报连接超时，是什么原因
在使用实时计算Flink版过程中，可能会遇到各种错误，了解这些错误的原因及解决方法对于高效排错至关重要。针对具体问题，查看Flink的日志是关键，它们通常会提供更详细的错误信息和堆栈跟踪，有助于定位问题。此外，Flink社区文档和官方论坛也是寻求帮助的好去处。以下是一些常见的操作报错及其可能的原因与解决策略。
嘟嘟嘟嘟嘟嘟
104 1
竹子爱熊猫
|
4月前
|
SQL 算法 Java
(二十六)MySQL分库篇：Sharding-Sphere分库分表框架的保姆级教学！
前面《MySQL主从原理篇》、《MySQL主从实践篇》两章中聊明白了MySQL主备读写分离、多主多写热备等方案，但如果这些高可用架构依旧无法满足业务规模，或业务增长的需要，此时就需要考虑选用分库分表架构。
竹子爱熊猫
2679 4
竹子爱熊猫
|
4月前
|
存储 SQL 关系型数据库
(二十一)MySQL之高并发大流量情况下海量数据分库分表的正确姿势
从最初开设《全解MySQL专栏》到现在，共计撰写了二十个大章节详细讲到了MySQL各方面的进阶技术点，从最初的数据库架构开始，到SQL执行流程、库表设计范式、索引机制与原理、事务与锁机制剖析、日志与内存详解、常用命令与高级特性、线上调优与故障排查.....，似乎涉及到了MySQL的方方面面。但到此为止就黔驴技穷了吗？答案并非如此，以《MySQL特性篇》为分割线，整个MySQL专栏从此会进入“高可用”阶段的分析，即从上篇之后会开启MySQL的新内容，主要讲述分布式、高可用、高性能方面的讲解。
竹子爱熊猫
315 1
不吃核桃
|
5月前
|
算法 搜索推荐 NoSQL
面试题MySQL问题之分库分表后的富查询问题处理如何解决
面试题MySQL问题之分库分表后的富查询问题处理如何解决
不吃核桃
52 3

热门文章

最新文章

  • 1
    RDS入门——Excel文件转存到RDS数据库实践
  • 2
    PostgreSQL SQL HINT的使用
  • 3
    mysql安装过程
  • 4
    Mysql数据库初始化后更改数据存储目录
  • 5
    MySQL学习笔记_2_MySQL创建数据表（上）
  • 6
    MySQL服务器 IO 100%的案例分析
  • 7
    MySQL和Oracle的添加字段的处理差别
  • 8
    [MySQL]命令行工具和基本操作
  • 9
    PostgreSQL:系统表pg_attribute膨胀解决方案
  • 10
    mysql中的时间函数---运维常用
  • 1
    Java医院绩效考核系统源码B/S+avue+MySQL助力医院实现精细化管理
    76
  • 2
    Linux对MySQL语法校验
    89
  • 3
    【MySQL系列笔记】分库分表
    203
  • 4
    mysql 设置环境变量与未设置环境变量连接数据库的区别
    161
  • 5
    服务器Linux系统配置mysql数据库主从自动备份
    214
  • 6
    mysqlTools 一分钟部署安装本mysql多个版本，解锁繁琐部署过程
    419
  • 7
    golang使用gorm操作mysql3，数据查询
    97
  • 8
    golang使用gorm操作mysql2
    61
  • 9
    golang使用gorm操作mysql1
    112
  • 10
    Hbase与MySQL对比，区别是什么？
    300

    • 相关课程

    更多
  • MySQL企业常见架构与调优经验分享
  • 云数据库MySQL版快速上手教程
  • 阿里云云原生数据仓库AnalyticDB MySQL版 使用教程
  • MySQL实战进阶
  • 数据库及SQL/MySQL基础
  • 云数据库MySQL快速入门

    • 相关电子书

    更多
  • One Box： 解读事务与分析一体化数据库 HybridDB for MySQL
  • One Box：解读事务与分析一体化数据库HybridDB for MySQL
  • 如何支撑HTAP场景-HybridDB for MySQL系统架构和技术演进

    • 相关实验场景

    更多
  • MySQL引擎及架构优化
  • 基于EBS部署高性能的MySQL服务
  • 5分钟上手Flink MySQL连接器
  • 云端MySQL的安装及应用
  • 手动部署MySQL数据库（Alibaba Cloud Linux 2）
  • 手动部署MySQL数据库（CentOS 7）

    • 推荐镜像

    更多
  • mysql
  • postgresql
  • mongodb
    • 下一篇
    fs.oss.accessKeyId和fs.oss.accessKeySecret。