mysql分表+分页查询

川长思鸟来 2022-12-22 15:23 165阅读 0赞

**随着项目运行的时间越来越长，有些数据的存储也会越来越大，比如一些点击量，浏览量量，单表的数据可以到达上千万条数据，这时候会存在单表数据过大，查询效率低的问题，为了提高查询效率这时候需要对单表进行拆分，比如一张1000万条数据的表，我们需要把它拆分为10张表，一张表就需要100万，mysql中单表都有一个最大存储的阈值，数据量不能超过这个值；**  
分表之间，我们需要去生产一个上万条的数据的表，这里我生产了如下的数据：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2pveV90b20_size_16_color_FFFFFF_t_70_pic_center]  
我需要对这张表进行拆分多张，我这里拆分了2张表；  
**第一步：**  
表一：

DROP table IF EXISTS tb_member1;
    create table tb_member1(
        id bigint primary key auto_increment ,
        name varchar(20),
        age tinyint not null default '0'
    )ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

表二：

DROP table IF EXISTS tb_member2;
    create table tb_member2(
        id bigint primary key auto_increment ,
        name varchar(20),
        age tinyint not null default '0'
    )ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

接下来开始进行拆分：

insert into tb_member1(id,name,sex) select id,name,sex from dd_user where id%2=0;
    insert into tb_member2(id,name,sex) select id,name,sex from dd_user where id%2=1;

数据被分为：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2pveV90b20_size_16_color_FFFFFF_t_70_pic_center 1]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2pveV90b20_size_16_color_FFFFFF_t_70_pic_center 2]  
这时候表基本是已经拆分完成。

**接下来，我们需要考虑的是一张user表被拆分成2张表，那分页如何实现呢？**  
第一步：  
创建一个主表：

DROP table IF EXISTS tb_member_all;
    create table tb_member_all(
    id bigint primary key auto_increment ,
    name varchar(20),
    age tinyint not null default '0'
    )ENGINE=MERGE UNION=(tb_member1,tb_member2) INSERT_METHOD=LAST CHARSET=utf8 AUTO_INCREMENT=1 ;

执行上述出现如下问题：

ERROR 1168 (HY000): Unable to open underlying table which is differently defined
    or of non-MyISAM type or doesn't exist

**需要检查：  
1：查看上面的分表数据库引擎是不是MyISAM.  
2：查看分表与指标的字段定义是否一致。**  
上述的都成功以后，我们会发现，你在member1或者member2中创建数据member\_all表中也会出现同样的数据  
所以：tb\_member\_all表就是tb\_member1，tb\_member2的并集，刚刚实现到这里，我也没理解，后来看了一些文档，了解了一下：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2pveV90b20_size_16_color_FFFFFF_t_70_pic_center 3]  
\*\*其实tb\_member\_all表里面是没有存储数据，它就是一个外壳，里面的数据是tb\_member1，tb\_member2的并集，数据的存储是放在分表中；  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2pveV90b20_size_16_color_FFFFFF_t_70_pic_center 4]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2pveV90b20_size_16_color_FFFFFF_t_70_pic_center 5]  
![在这里插入图片描述][20201120183521249.png_pic_center]

这时候，我们就豁然开朗了，利用这个tb\_member\_all，我们就可以实现数据查询的分页；\*\*  
java代码实现：

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2pveV90b20_size_16_color_FFFFFF_t_70_pic_center 6]

**id%2这是取模处理，分配数据进入哪个数据；**

我们在做分页的时候就利用tb\_member\_all表去做分页查询实现  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2pveV90b20_size_16_color_FFFFFF_t_70_pic_center 7]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2pveV90b20_size_16_color_FFFFFF_t_70_pic_center 8]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2pveV90b20_size_16_color_FFFFFF_t_70_pic_center 9]  
测试结果：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2pveV90b20_size_16_color_FFFFFF_t_70_pic_center 10]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2pveV90b20_size_16_color_FFFFFF_t_70_pic_center]: /images/20221120/9378b48619c047ce8281e9088f133c7f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2pveV90b20_size_16_color_FFFFFF_t_70_pic_center 1]: https://img-blog.csdnimg.cn/20201120181033518.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2pveV90b20=,size_16,color_FFFFFF,t_70#pic_center
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2pveV90b20_size_16_color_FFFFFF_t_70_pic_center 2]: https://img-blog.csdnimg.cn/20201120181057608.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2pveV90b20=,size_16,color_FFFFFF,t_70#pic_center
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2pveV90b20_size_16_color_FFFFFF_t_70_pic_center 3]: https://img-blog.csdnimg.cn/20201120181857843.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2pveV90b20=,size_16,color_FFFFFF,t_70#pic_center
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2pveV90b20_size_16_color_FFFFFF_t_70_pic_center 4]: https://img-blog.csdnimg.cn/20201120183446953.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2pveV90b20=,size_16,color_FFFFFF,t_70#pic_center
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2pveV90b20_size_16_color_FFFFFF_t_70_pic_center 5]: https://img-blog.csdnimg.cn/20201120183503779.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2pveV90b20=,size_16,color_FFFFFF,t_70#pic_center
[20201120183521249.png_pic_center]: https://img-blog.csdnimg.cn/20201120183521249.png#pic_center
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2pveV90b20_size_16_color_FFFFFF_t_70_pic_center 6]: https://img-blog.csdnimg.cn/20201120182330899.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2pveV90b20=,size_16,color_FFFFFF,t_70#pic_center
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2pveV90b20_size_16_color_FFFFFF_t_70_pic_center 7]: https://img-blog.csdnimg.cn/20201120183835597.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2pveV90b20=,size_16,color_FFFFFF,t_70#pic_center
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2pveV90b20_size_16_color_FFFFFF_t_70_pic_center 8]: https://img-blog.csdnimg.cn/20201120183904418.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2pveV90b20=,size_16,color_FFFFFF,t_70#pic_center
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2pveV90b20_size_16_color_FFFFFF_t_70_pic_center 9]: https://img-blog.csdnimg.cn/20201120183928669.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2pveV90b20=,size_16,color_FFFFFF,t_70#pic_center
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2pveV90b20_size_16_color_FFFFFF_t_70_pic_center 10]: https://img-blog.csdnimg.cn/20201120183953521.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2pveV90b20=,size_16,color_FFFFFF,t_70#pic_center