MySQL分库分表-白红宇

MySQL分库分表

阅读量：4630 次

发布时间：2019-06-09

本文共 7131 字，大约阅读时间需要 23 分钟。

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)
        
      

查询一下所有数据库

                         
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)
        
      