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 |
|
查询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)
水平分表完成!
