MySQL分表实现上百万上千万记录分布存储的批量查询设计模式

最新推荐文章于 2024-10-28 10:56:10 发布
最新推荐文章于 2024-10-28 10:56:10 发布
阅读量5.8k 收藏 2
点赞数
分类专栏： mysql分表 文章标签： mysql 设计模式 存储 thread html 任务
本文介绍了一种针对MySQL中上百万乃至上千万条记录的分表存储和批量查询的设计模式。探讨了通过应用程序级别的实现和JDBC层实现的解决方案，以及MySQL 5.1 partition功能的局限性。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

原帖:

MySQL分表实现上百万上千万记录分布存储的批量查询设计模式
2006-11-15 23:55

MySQL分表实现上百万上千万记录分布存储的批量查询设计模式

Tim http://hi.baidu.com/jabber/blog/category/Mysql

我们知道可以将一个海量记录的 MySQL 大表根据主键、时间字段，条件字段等分成若干个表甚至保存在若干服务器中。
唯一的问题就是跨服务器批量查询麻烦，只能通过应用程序来解决。谈谈在Java中的解决思路。其他语言原理类似。

这里说的分表不是 MySQL 5.1 的 partition，而是人为把一个表分开存在若干表或不同的服务器。

1. 应用程序级别实现

见示意图


SelectThreadManager 分表数据查询管理器
它为分表的每个database or server 建立一个 thread pool

addTask() - 添加任务
stopTask() - 停止任务
getResult() - 获取执行结果

最快的执行时间 = 最慢的 MySQL 节点查询消耗时间
最慢的执行时间 = 超时时间

某个 ThreadPool 忙时候处理流程
1. 假如 ThreadPoolN 非常忙，(也意味 DB N 非常忙)；
2. 新的查询任务到来，addTask(), 新的任务的一个thread加到ThreadPoolN任务排队中
3. 外层应用已经获得其他 thread 返回结果，继续等待
4. 外层应用等待超时的时间到，调用 stopTask() 设置该任务全部 thread 中的停止标志, 外层应用返回。
5. 若干时间后，ThreadPoolN取到该排队 Thread, 因为设置了停止位，线程直接运行完成。


2. JDBC 层实现
做一个 JDBC Driver 的包装，拦截 PreparedStatement, Statement 的 executeQuery()
然后调用 SelectThreadManager 完成

3. MySQL partition
MySQL 5.1 的 partition 功能由于单张表的数据跨文件，批量查询时候同样存在上述问题，不过它是在 MySQL 内部实现的，不需要外部调用者关心。其查询实现的原理应该大致类似。
但 partition 只解决了 IO 的瓶颈，并不能解决 CPU 计算的瓶颈，因此无法代替传统的手工分表方式。

类别：Mysql | 添加到搜藏 | 浏览( 1099)
 
最近读者：

 		 <script type="text/javascript">document.write("<a href='http://passport.baidu.com/?login&tpl=sp&tpl_reg=sp&u="+myref+"' target='_self'>登录</a>后，您就出现在这里。");</script> 登录后，您就出现在这里。
好羊就好样 		 
 
<script language="JavaScript" type="text/javascript"> allkey=allkey+"3c8bf3eddc140b4f78f05584_adc442ed647adad4b31cb11e_"; </script>
网友评论：
<script type="text/javascript"> function writecmt(type,id,cmtname,cmturl,portraitId){ var html1=""; if(type==1){ html1="
"+cmtname+""; }else{ if(cmtname=="" || cmtname=="匿名网友"){ if(cmturl==""){ html1=" 匿名网友"; }else{ html1=" "+cmtname+""; } }else{ if(cmturl==""){ html1="
网友: "+cmtname+"
"; }else{ html1="
网友: "+cmtname+"
"; } } } document.write(html1); } </script>
1
<script language="javascript" type="text/javascript"> writecmt(2,"3b78660661b3257b020881f3","Yimin","http://bantu.cn/blog","000059696d696e0000"); </script>
网友: Yimin
2006-11-16 11:41
受教了。 :)
 
2
<script language="javascript" type="text/javascript"> writecmt(2,"67485b43d4900d119313c6b7","rei","","00007265690000"); </script>
网友: rei
2006-12-12 21:41
仰慕啊～～
继续深入下去，你会发现你要做的其实是一个基于MySQL单点的分布式数据库中间件。
加油把～
 
3
<script type="text/javascript"> writecmt(1,"7e87985266e2b90e0df3e356","iso1600","http://hi.baidu.com/jabber","c8d769736f313630301801"); </script>
iso1600
2006-12-22 21:59
以上设计模式的程序其实我已经实现了，在10个数据库的环境下运行稳定。需要补充的是：int addTask()要返回一个taskID, getResult(int taskID)要传入一个taskID。90%以上的情况所有线程不会超时。每个线程执行完之后会用nofity()通知 getResult的主线程，所以当所有线程执行完之后主线程会立即被唤醒并返回结果，否则主线程会wait()到超时(设置为1s)。只不过现在是基于Java语言的，如果实现了跨语言，那就是一个分布式的中间件了。不过现在开发完成的功能已经满足了项目的需要，所以目前还缺少继续深入的动力。:)
 
5
<script language="javascript" type="text/javascript"> writecmt(2,"8c82ba114004ad7fca80c4ed","今晚打老虎","","0000bdf1cdedb4f2c0cfbba20000"); </script>
网友: 今晚打老虎
2007-02-06 23:09
请问有没有php的代码可以借来参考一下呢？最近在开发一个广告统计系统，由于数据量太大，所以需要分表，分表以后总统计有点困难，对数据库中间件不是很在行，请高手指教
 
6
<script language="javascript" type="text/javascript"> writecmt(2,"a6b8af86e00f623f67096eed","今晚打老虎","mailto:huzhuolei@gmail.com","0000bdf1cdedb4f2c0cfbba20000"); </script>
网友: 今晚打老虎
2007-02-06 23:10
忘了留邮箱了，呵呵
MySQL 如何同时查询多个相同分表的数据
省赚客开发者博客
08-14 717
在大型系统中，随着数据量的增加，单表存储数据会变得非常慢，查询效率也会下降。本文将详细介绍在MySQL中如何同时查询多个相同分表的数据，并且会通过一些具体的例子来演示。通过本文的介绍，我们了解了在MySQL中如何同时查询多个相同分表的数据，并且演示了几种常用的方法。在上述 Java 代码中，我们动态构建了查询语句，将所有分表的查询结果合并，然后按。如果分表的结构固定，可以创建一个视图，将多个分表的数据合并在一起。，因为订单量非常大，所以我们将其按月份进行了分表，形成了多个表，如。将多个查询结果合并在一起。
MySQL百万级/千万级数据存储解决方案
最近学习目标：MySQL，Redis
12-06 4990
MySQL 百万级/千万级数据存储解决方案 百万级、千万级数据处理，个人认为核心关键在于数据存储方案设计，存储方案设计的是否合理，直接影响到数据CRUD操作。总体设计可以考虑一下三个方面进行设计考虑： 数据存储结构设计 索引设计 数据主键设计 查询方案设计 百万级数据处理方案： 数据存储结构设计 表字段设计 表字段 not null，因为 null 值很难查询优化且占用额外的索...
MySQL分表实现上百万上千万记录分布存储的批量查询设计模式详解
12-15
我们知道可以将一个海量记录的 MySQL 大表根据主键、时间字段，条件字段等分成若干个表甚至保存在若干服务器中。 唯一的问题就是跨服务器批量查询麻烦，只能通过应用程序来解决。谈谈在Java中的解决思路。其他语言原理类似。这里说的分表不是 MySQL 5.1 的 partition，而是人为把一个表分开存在若干表或不同的服务器。1. 应用程序级别实现见示意图 electThreadManager 分表数据查询管理器它为分表的每个database or server 建立一个 thread pooladdTask() – 添加任务stopTask() – 停止任务getResult() – 获取
【演讲实录】分布式数据库海量数据存储和实时查询实现与应用
weixin_34216036的博客
05-18 357
2019独角兽企业重金招聘Python工程师标准>>> ...
分库分表多表查询 不再复制粘贴
wangbaosongmsn的博客
04-04 309
/** * 生成分表查询语句 * sqls：原始sql，带有占位符 * tableNums: 分表个数 * skipType: 跳过分表类型 0.不跳过 1.跳过奇数表 2.跳过偶数表 * replaceStr: 替换占位符 * * @date 2021-11-08 * @author fdh */ CREATE DEFINER=`root`@`%` FUNCTION `buildSql`(sqls varchar(1000), tableNums int(3), skipType in.
MySQL高频基本面试问题整理
热门推荐
张彦峰的博客
04-07 173万+
总结大厂MySQL高频基本面试问题整理，用于快速查缺补漏
MySQL数据库之分库分表方案
weixin_71682097的博客
06-10 2839
MySQL数据库之分库分表方案
转载：mysql 对于百万 千万级数据的分表实现方法
dewine0070的博客
12-15 455
一般来说，当我们的数据库的数据超过了100w记录的时候就应该考虑分表或者分区了，这次我来详细说说分表的一些方法。目前我所知道的方法都是MYISAM的，INNODB如何做分表并且保留事务和外键，我还不是很了解。首先，我们需要想好到底分多少个表，前提当然是满足应用。这里我使用了一个比较简单的分表方法，就是根据自增id的尾数来分，也就是说分0-9一共10个表，其取值也很好做，就是对10进行取模...
写一个通用的对一个表的增删查改的操作
有上进心的阿龙
12-06 340
写一个通用的对一个表的增删查改的操作 1、在数据库中创建一张customer表 2、在idea中创建一个文件，存储连接数据库的参数 3、在idea中写一个实体类，类名就叫做Customer 其中的属性和mysql的customer表的字段名一样 实体类代码： package myself_test.entity; import java.util.Date; public class Customer { private Integer id; private String nam
记一次mysql百万级数据分表经历
cslover的博客
03-11 867
平时开发，虽然每天都要和数据库打交道，但是并没有涉及到过分库分表这项业务，对分表一直是停留在了解概念程度，没有真正实践过。因为之前的数据基本用不到了，可以转移走，所以这次也只是简单的手动分了一下表，应急用，不是从数据库开始的设计和程序开发的角度进行分表。 这个表的数据超过了500万，之前没有出问题，是因为围绕这个表的业务很少用，估计当时是侥幸过关了。这次程序在查询时报了超时，很简单的一条sql，也没有联表查询，但是条件字段没有加索引，而且还是左右全模糊的like查询，不超时才怪。 因为比较着急，就直接手
笔记：MYSQL 大数据查询效率优化，分表，分库，读写分离（主从数据库）
qq_36470908的博客
03-15 2002
1.分表（一个数据库）：解决单表数据量过大，数据查询的效率问题（一张表大约能支持1000w条数据，字段越多就越少） 1.1 5年数据预算，计算出大概需要 x 张表  1.2 为了可扩展性考虑，我们计划生成 2x 张表 1.3 利用一致性hash值，进行数据插入，一般用用户主键id操作，hash(id)%2x -----尽量保证同一用户数据在一张表里面       2.分库（多个数据库）：解决单台数...
订单表mysql_PHP 结合 MySQL 千万级数据处理
weixin_39650994的博客
11-21 170
mysql 分表思路一张一亿的订单表，可以分成五张表，这样每张表就只有两千万数据，分担了原来一张表的压力，分表需要根据某个条件进行分，这里可以根据地区来分表，需要一个中间件来控制到底是去哪张表去找到自己想要的数据。中间件：根据主表的自增 id 作为中间件（什么样的字段适合做中间件？要具备唯一性） 怎么分发？主表插入之后返回一个 id，根据这个 id 和表的数量进行取模，余数是几就往哪张表中插...
mysql 对于百万 千万级数据的分表实现方法
csdn924618338的博客
05-18 4548
首先，我们需要想好到底分多少个表，前提当然是满足应用。这里我使用了一个比较简单的分表方法，就是根据自增id的尾数来分，也就是说分0-9一共10个表，其取值也很好做，就是对10进行取模。另外，还可以根据某一字段的md5值取其中几位进行分表，这样的话，可以分的表就很多了。 CREATE TABLE `test`.`article_0` ( `id` BIGINT( 20 ) N
【Mysql】给你100万条数据的一张表，你将如何查询优化？
咔咔博客
01-28 1万+
author：咔咔 wechat：fangkangfk 1.两种查询引擎查询速度（myIsam 引擎） InnoDB 中不保存表的具体行数，也就是说，执行select count(*) from table时，InnoDB要扫描一遍整个表来计算有多少行。 MyISAM只要简单的读出保存好的行数即可。 注意的是，当count(*)语句包含 where条件时，两种表的操作有些不同，Inno...
Web站点数据库分布存储浅谈
半道出家的和尚
06-25 2219
【 前言 】   网 站在Web 2.0时代，时常面临迅速增加的访问量（这是好事情），但是我们的应用如何满足用户的访问需求，而且基本上我们看到的情况都是性能瓶颈都是在数据库上，这 个不怪数据库，毕竟要满足很大访问量确实对于任何一款数据库都是很大的压力，不论是商业数据库Oracle、MS SQL Server、DB2之类，还是开源的MySQL、PostgreSQL，都是很大的挑战，解决的方法很简单，
mysql 分表 跨表汇总_数据库分库分表解决方案汇总
weixin_39884492的博客
02-07 984
转自：数据库分库分表解决方案汇总 - 猿圈-程序猿的知识社区-学习猿地​www.lmonkey.com一. 数据切分关系型数据库本身比较容易成为系统瓶颈，单机存储容量、连接数、处理能力都有限。当单表的数据量达到1000W或100G以后，由于查询维度较多，即使添加从库、优化索引，做很多操作时性能仍下降严重。此时就要考虑对其进行切分了，切分的目的就在于减少数据库的负担，缩短查询时间。数据库分布式核心内...
数据库分表及分页查询策略
最新发布
疯狂的星光的专栏
10-28 774
并不是的，在一些对顺序、精度要求不严格的场景下，还是可以用的。所以在Proxy形态的分库分表中间件里，分页查询一多，就容易把中间件的内存耗尽，引发OOM，又或是CPU 100%。返回的结果是5,7,8,9,11，其中7在第一次查询里的偏移量是2，所以5的偏移量是1。比如说第一次查询的时候。这种方案并没有彻底解决分库分表查询中的分页问题，但是控制了偏移量，极大的减少了网络通信的消耗和磁盘扫描的消耗。在开发的时候先用SQL在不同的表上执行一下，看看同样的WHERE条件下各自返回了多少数据，就可以推断出来了。
评论 3
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值