MYSQL实现水平分表

最新推荐文章于 2026-03-17 12:02:55 发布

原创最新推荐文章于 2026-03-17 12:02:55 发布 · 4.2k 阅读

19 ·

本内容遵循CC 4.0 BY-SA版权协议

标签

#数据库 #触发器 #mysql

本文介绍了MySQL水平分表的原理和实现，包括为何进行分库分表以提升性能和增加可用性。通过Navicat展示了如何创建数据表和使用触发器进行分表操作，详细解释了触发器的作用，并提供了查询和更新数据的示例。

mysql水平分表

本人主要以Navicat实现，具体想要高端点，可以用Sharing JDBC、Mycat去实现

水平分表

mysql水平分表
分库分表原理是什么？
为什么要分库分表？
二、水平分表
- 1.数据表
- 2.触发器

分库分表原理是什么？

按照字面意思：

分库：从单个数据库拆分成多个数据库的过程，将数据散落在多个数据库中。
分表：从单张表拆分成多张表的过程，将数据散落在多张表内。

为什么要分库分表？

提升性能、增加可用性。
1. 从性能上看

随着单库中的数据量越来越大、数据库的查询QPS越来越高，相应的，对数据库的读写所需要的时间也越来越多。数据库的读写性能可能会成为业务发展的瓶颈。对应的，就需要做数据库性能方面的优化。本文中我们只讨论数据库层面的优化，不讨论缓存等应用层优化的手段。
如果数据库的查询QPS过高，就需要考虑拆库，通过分库来分担单个数据库的连接压力。比如，如果查询QPS为3500，假设单库可以支撑1000个连接数的话，那么就可以考虑拆分成4个库，来分散查询连接压力。
如果单表数据量过大，当数据量超过一定量级后，无论是对于数据查询还是数据更新，在经过索引优化等纯数据库层面的传统优化手段之后，还是可能存在性能问题。这是量变产生了质变，这时候就需要去换个思路来解决问题，比如：从数据生产源头、数据处理源头来解决问题，既然数据量很大，那我们就来个分而治之，化整为零。这就产生了分表，把数据按照一定的规则拆分成多张表，来解决单表环境下无法解决的存取性能问题。

2. 从可用性上看

单个数据库如果发生意外，很可能会丢失所有数据。尤其是云时代，很多数据库都跑在虚拟机上，如果虚拟机/宿主机发生意外，则可能造成无法挽回的损失。因此，除了传统的
Master-Slave、Master-Master 等部署层面解决可靠性问题外，我们也可以考虑从数据拆分层面解决此问题。

分库分表实现方案：
在这里插入图片描述

二、水平分表

1.数据表

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`);

在这里插入图片描述

ENGINE = MERGE 和 ENGINE = MRG_MyISAM是一样的意思，都是代表使用的存储引擎是 Merge。
INSERT_METHOD，表示插入方式，取值可以是：0 和 1，0代表不允许插入，1代表可以插入；
FIRST插入到UNION中的第一个表，LAST插入到UNION中的最后一个表。

2.触发器

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’

此时已经分表成功, 查询一下所有数据库
在这里插入图片描述
插入数据,多插几下（这是什么虎狼之词emmm）

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)

查询user1
在这里插入图片描述
查询user2 （刘备那条是我修改的，下面会说，不要在意这些细节）

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

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表

select * from user order by id asc;

在这里插入图片描述
查询user1 / user2

select * from user1 order by id asc;
select * from user2 order by id asc;

在这里插入图片描述

下面我们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 , 刘备就出来了

select * from user order by id asc;

到这里，水平分表就完成啦！！实现了的话别忘了回来给弟弟一个赞吧（求求了）