mysql 水平分表技术

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

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

合并的表使用的必须是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)

水平分表完成!