mysql 水平分表技术

最新推荐文章于 2024-04-30 10:57:37 发布
最新推荐文章于 2024-04-30 10:57:37 发布
阅读量224 收藏 1
点赞数
分类专栏： MySQL 文章标签： mysql水平分表
原文链接：https://www.cnblogs.com/chenglee/p/9401384.html
版权

mysql 水平分表技术

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

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

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

     

数据表

user1

1

2

3

4

5

6

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

1

create table user2 like user1;

user

1

2

3

4

5

6

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中的最后一个表。

触发器

1

2

3

create table tb_ids(id int);

 

insert into tb_ids values(1);

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

1

2

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

1

2

3

4

5

6

7

8

9

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

1

2

3

4

5

6

7

8

9

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执行的, 效果都一样.

然后查询一下触发器

1

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

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

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

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

1

2

3

4

5

6

7

8

9

10

11

12

13

14

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

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

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)

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

1

2

3

4

5

6

7

8

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

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

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

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

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

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

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一下

1

2

3

mysql> update user set name='刘备' where id ='9';

Query OK, 1 row affected (0.01 sec)

Rows matched: 1  Changed: 1  Warnings: 0

 再看一下user

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

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

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

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

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

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)

 

水平分表完成!

 

ClearLoveQ
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
mysql笔记，mysql优化图解
05-17
Mysql数据库的优化技术 对mysql优化时一个综合性的技术，主要包括 a: 表的设计合理化(符合3NF) b: 添加适当索引(index) [四种: 普通索引、主键索引、唯一索引unique、全文索引] c: 分表技术(水平分割、垂直分割) d: 读写[写: update/delete/add]分离 e: 存储过程 [模块化编程，可以提高速度] f: 对mysql配置优化 [配置最大并发数my.ini, 调整缓存大小 ] g: mysql服务器硬件升级 h: 定时的去清除不需要的数据,定时进行碎片整理(MyISAM) 什么样的表才是符合3NF (范式) 表的范式，是首先符合1NF, 才能满足2NF , 进一步满足3NF 1NF: 即表的列的具有原子性,不可再分解，即列的信息，不能分解, 只有数据库是关系型数据库(mysql/oracle/db2/informix/sysbase/sql server)，就自动的满足1NF ☞ 数据库的分类 关系型数据库: mysql/oracle/db2/informix/sysbase/sql server 非关系型数据库: (特点: 面向对象或者集合) NoSql数据库: MongoDB(特点是面向文档) 2NF: 表中的记录是唯一的, 就满足2NF, 通常我们设计一个主键来实现 3NF: 即表中不要有冗余数据, 就是说，表的信息，如果能够被推导出来，就不应该单独的设计一个字段来存放. 比如下面的设计就是不满足3NF: 。。。。。。。。。。。。。。
MySql 分库分表：垂直分表、垂直分库、水平分表、水平分库
书香水墨
12-15 2585
读写分离解决的是数据库读写操作的压力，但是没有分散数据库的存储压力，利用分库分表可以解决数据库的储存瓶颈，并提升数据库的查询效率。 12.1、垂直拆分： （1）垂直分表：将一个表按照字段分成多个表，每个表存储其中一部分字段。一般会将常用的字段放到一个表中，将不常用的字段放到另一个表中。 优点： （1）避免IO竞争减少锁表的概率。因为大的字段效率更低，第一，大字段占用的空间更大，单页内存储的行数变少，会使得IO操作增多；第二数据量大，需要的读取时间长。 （2）可以更好地提升热门数据的查询效率。 （2）
一文彻底搞懂 MYSQL分库分表方案
weixin_42273066的博客
05-26 1862
把一部分表字段放入一张表，另一部分放入其他的表。按照表字段的使用频次分门别类的划分。例如：在商品列表查询时，列表中只是展示部分字段，同时这个列表查询比详情信息查询更加高频，并不需要把所有字段都展示，我们可以单独把列表信息单独拿出来当一张表。当表数据量很大时，可以将表按字段切开，将热门字段、冷门字段分开放置在不同表中，这些库可以放在不同的存储设备上，避免IO争抢。垂直切分带来的性能提升主要集中在热门数据的操作效率上，而且磁盘争用情况减少。同理也可以大字段进行拆分扩展表想法。
mysql水平分表方案 mysql水平分表查询方法
JmuziA的博客
04-09 932
原文地址：mysql水平分表方案 mysql水平分表查询方法。
Docker安装Mycat和Mysql进行水平分库分表实战【图文教学】.doc
07-10
Docker安装Mycat和Mysql进行水平分库分表实战【图文教学】.doc
MySQL数据库优化技术概述
12-14
对于一个以数据库为中心的应用，数据库的优化直接影响到程序的性能，因此数据库性能至关重要。一般来说，要保证数据库的效率，要做好以下几个方面的工作： 　　1、 数据库表设计： 　　表的设计合理化(符合3NF)； 　　2、添加适当索引(index)： 　　普通索引: 　　主键索引: primary 效率高，但是只能有一个 　　索引：unique 　　全文索引：fulltext 对文章中的词进行索引 　　空间索引：SPATIAL 很少使用 　　3、分表技术： 　　水平分割 　　垂直分割 　　4、读写分离： 　　写：update/delete/insert 　　5、存储过程
ShardingSphere分库分表教程 Spring Boot 34(1)
最新发布
2401_83977696的博客
04-30 553
为了确保数据库产品的稳定性，很多数据库拥有双机热备功能。第一台数据库服务器对外提供增删改业务的生产数据；第二台数据库服务器，主要进行读的操作；Sharding-JDBC通过sql语句语义分析，实现读写分离过程，不会做数据同步。看完上述知识点如果你深感Java基础不够扎实，或者刷题刷的不够、知识不全面小编专门为你量身定制了一套针对知识面不够，也莫慌！
Sharding-JDBC实战(水平分表、水平分库、垂直分表、公共表、读写分离)
JolyouLu的博客
08-29 2491
Sharding-JDBC实战 基本概述 Sharding-JDBC早期是由当当网开源的分布式数据库中间件，后期该项目被放入到ShardingSphere项目下，Sharding-JDBC是一个轻量级 Java 框架，在 Java 的 JDBC 层提供的额外服务， 它使用客户端直连数据库，以 jar 包形式提供服务，无需额外部署和依赖，可理解为增强版的 JDBC 驱动，完全兼容 JDBC 和各种 ORM 框架 在使用Sharding-JDBC首先要清楚以下几点 它是一个轻量级的Java框架，可以理解是增
从表拆分到数据装填，再到分表查询。手把手一步步实现水平分表业务
硬核编程乐乐的博客
03-30 1032
技术框架 Mybatis 背景 要处理一张亿级数据量的表，它有两个字段，分别为userId和PhoneNumber，两个字段数据都为数字，类型为varchar。而一个userId可以有多个PhoneNumber，因此注定了limit1,1这种语句优化是无效的。 在当前一亿数据量的情况下，一个WHERE查询要花费5分钟，并且加索引也是无效的。 此时想到了分表，并且显而易见的要采取水平分表方案。 可是分表到底要怎么分？在这之前只是听到过，但具体不知道如何实现。 经过建议，知道了可以用sharding-jdbc框
mysql水平分库_sharding-jdbc实现水平分库 + 水平分表
weixin_36318506的博客
02-28 400
1、搭建环境(1)SpringBoot2.2.1+MyBatisPlus+Sharding-JDBC+Druid连接池(2)创建SpringBoot工程，版本2,2,1(3)引入相关依赖org.springframework.bootspring-boot-starterorg.springframework.bootspring-boot-starter-testcom.alibabadruid...
Mysql千万级别水平分表优化
12-14
需求：随着数据量的增加单表已经不能很好的支持业务，千万级别数据查询缓慢 Mysql数据优化方案： 方案一：使用myisam进行水平分表优化 方案二：使用mysql分区优化 一：Myisam水平分区 1、创建水平分表 user_1: -- 创建水平分表 create table user_1( id varchar(50) PRIMARY key COMMENT '主键id', user_name varchar(50) DEFAULT null COMMENT '用户名称', creator varchar(50) DEFAULT null COMMENT '创建者', crea
Mysql数据表分区技术PARTITION浅析
12-15
在这一章节里， 我们来了解下 Mysql 中的分区技术 (RANGE, LIST, HASH)   Mysql 的分区技术与水平分表有点类似， 但是它是在逻辑层进行的水平分表， 对于应用而言它还是一张表， 换句话说: 分区不是实际真正的对一张表进行拆分，分区之后表还是一个表，它是把存储文件进行拆分。 在 Mysql 5.1(后) 有了几种分区类型:   RANGE分区: 基于属于一个给定连续区间的列值， 把多行分配给分区 LIST分区: 类似于按 RANGE 分区， 区别在于 LIST 分区是基于列值匹配一个离散值集合中的某个值来进行选择 HASH分区: 基于用户定义的表达式的返回值来进行选择
MySQL分库分表技术
12-11
这个PPT由浅入深，从很少的用户到千万级别的用户，告诉你为什么要使用分库分表，包括垂直和水平切分，偏入门的理论，代码基本无
mysql 分区分表_mysql分库分区分表
小马哥的博客
10-18 1960
mysql 分区分表_mysql分库分区分表
mysql分库分表
rui28899的博客
01-20 4240
一、如何进行分库分表 1、概念： 将原本存储在单个数据库上的数据拆分到多个数据库中（分库），把原来存储在单张表的数据拆分到多张数据表中，实现数据切分，从而提生数据库操作性能。分库分表的实现方式分为两种：垂直切分，水平切分。 2、水平拆分： 将数据分散到多张表，涉及到分区键。 分库：每个库结构一样，数据不一样，没有交集。库多了可以缓解io和cpu压力。 分表：每个表结构一样，数据不一样，没有交集。表数量减少，可以提高sql执行效率，减轻cpu压力。 优缺点： 优点： 单库（表）的数据保持在一定的量（减
你分库分表的姿势对么？——详谈水平分库分表
nilm61的专栏
04-06 288
前期业务数据量级不大，流量较低的时候，我们无需分库分表，也不建议分库分表。但是一旦我们要对业务进行分库分表设计时，就一定要考虑到分库分表方案的可持续性。那何为可持续性？其实就是：业务数据量级和业务流量未来进一步升高达到新的量级的时候，我们的分库分表方案可以持续使用。一个通俗的案例，假定当前我们分库分表的方案为10库100表，那么未来某个时间点，若10个库仍然无法应对用户的流量压力，或者10个库的磁盘使用即将达到物理上限时，我们的方案能够进行平滑扩容。
SpringBoot整合Sharding-JDBC实现水平分表
桐花思雨
01-12 1306
目录数据库的水平分表需求说明 数据库的水平分表 关于水平分表可以参考文章：https://blog.csdn.net/weixin_38192427/article/details/122441366 需求说明 创建两张表 t_order_1 和 t_order_2，这两张表是订单表水平拆分后的表，通过 Sharding-JDBC 向订单表插入数据， 按照一定的分片规则，主键为偶数的进入 t_order_1，主键为奇数进入 t_order_2，通过 Sharding-JDBC 查询数据，根据 SQL 语句的
ShardingSphere中间件实现数据库分库分表操作
weixin_46088323的博客
08-29 938
ShardingSphere中间件ShardingJDBC和Sharding-proxy实现数据库分库分表以及读写分离操作
mysql分库分表技术
07-20
MySQL分库分表是一种常用的数据库分布式架构设计技术，用于解决大规模数据存储和查询的性能瓶颈问题。它将一个大的数据库拆分成多个小的数据库（分库），每个小数据库再将数据表按照某种规则拆分成多个小表（分表）。 分库分表的优势包括： 1. 横向扩展性：通过分库分表，可以将数据存储在多个物理服务器上，提高系统的并发处理能力和数据存储容量。 2. 提高查询性能：将数据拆分成多个小表后，每个小表的数据量减少，查询效率提高。 3. 负载均衡：通过对数据进行分散存储，可以将负载均衡到多个数据库节点上，避免单一节点的性能瓶颈。 4. 容灾备份：多个数据库节点可以实现数据的冗余备份，提高系统的可用性和可靠性。 在实际应用中，可以采用水平切分和垂直切分两种方式进行分库分表。水平切分是指按照某种规则将数据行划分到不同的数据库中，例如按照用户ID或地理位置进行划分；垂直切分是指按照数据列进行划分，将不同的列存储在不同的数据库中，例如将用户基本信息和用户关注信息存储在不同的数据库中。 分库分表也带来了一些挑战，例如跨库事务的处理、数据一致性、分布式索引等问题需要额外的处理。因此，在设计和实施分库分表方案时，需要综合考虑系统的需求、数据规模、负载情况以及应用程序的复杂度等因素。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值