mysql千万级测试1亿数据的分页分析测试 - 战斗机 - ITeye博客

`

zpball

浏览: 930879 次
性别:
来自: 北京

最近访客更多访客>>

lng87108

feilafei123

qq_731139982

u012363178

博主相关

博客

微博

相册

收藏

留言

关于我

文章分类

社区版块

存档分类

最新评论

M_drm：请问要怎么设置浏览器才不报没权限呢？
用JS在页面调用本地可执行文件的方法(ACTIVEX)
Alexniver：官方文档。When importing data into I ...
mysql导入数据过慢解决方法
camelwoo：我记得 Criteria 可以做连接查询与子查询，也可以做分页 ...
Hibernate总结篇二
zhenglongfei：楼主如果SubKeyName 这个节点不存在，怎么办？？怎么用 ...
Java操作注册表
yxx676229549：用log4j 2 了
logback

mysql千万级测试1亿数据的分页分析测试

博客分类：

Mysql

阅读更多

本文为本人最近利用几个小时才分析总结出的原创文章,希望大家转载,但是要注明出处
http://blog.sina.com.cn/s/blog_438308750100im0e.html
有什么问题可以互相讨论:yubaojian0616@163.com 于堡舰

上一篇文章我们测试一些order by查询和分页查询的一些基准性能,现在我们来分析一下条件索引查询的结果集的测试

现在我们继续进行一个测试相同的表结构插入1亿条数据这次用到的是Innodb表引擎,表名有些变化,这里为甚要新建一个表的很重要元素是原来的那张表是每个uid=1来做的索引,这次uid是1...10不等的数每种1千万条记录
CREATE TABLE `ipdata` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `uid` int(8) NOT NULL DEFAULT '0',
   `ipaddress` varchar(50) NOT NULL,
   `source` varchar(255) DEFAULT NULL,
   `track` varchar(255) DEFAULT NULL,
   `entrance` varchar(255) DEFAULT NULL,
   `createdtime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
   `createddate` date NOT NULL DEFAULT '0000-00-00',
   PRIMARY KEY (`id`),
   KEY `uid` (`uid`)
} ENGINE=InnoDB AUTO_INCREMENT=100004857 DEFAULT CHARSET=utf8
我开启了Innodb的线程数为128,因为innodb是行级别锁定，并发处理能力很强我开启100线程每个线程大小为100万记录插入时间如下
JDBC插入100w条数据此线程用时:9300984ms
JDBC插入100w条数据此线程用时:9381203ms
JDBC插入100w条数据此线程用时:9412343ms
JDBC插入100w条数据此线程用时:9442046ms
JDBC插入100w条数据此线程用时:9449828ms
JDBC插入100w条数据此线程用时:9484703ms
JDBC插入100w条数据此线程用时:9528093ms
JDBC插入100w条数据此线程用时:9533359ms
JDBC插入100w条数据此线程用时:9534296ms
JDBC插入100w条数据此线程用时:9539718ms
JDBC插入100w条数据此线程用时:9541750ms
JDBC插入100w条数据此线程用时:9636406ms
JDBC插入100w条数据此线程用时:9695093ms
JDBC插入100w条数据此线程用时:9806890ms
JDBC插入100w条数据此线程用时:9895500ms
JDBC插入100w条数据此线程用时:9989750ms
JDBC插入100w条数据此线程用时:10012312ms
JDBC插入100w条数据此线程用时:10037250ms
JDBC插入100w条数据此线程用时:10092796ms
JDBC插入100w条数据此线程用时:11993187ms
JDBC插入100w条数据此线程用时:12033203ms
JDBC插入100w条数据此线程用时:12068453ms
JDBC插入100w条数据此线程用时:12133625ms
JDBC插入100w条数据此线程用时:12212953ms
JDBC插入100w条数据此线程用时:12253421ms
JDBC插入100w条数据此线程用时:12284968ms
JDBC插入100w条数据此线程用时:12296421ms
JDBC插入100w条数据此线程用时:12366828ms
JDBC插入100w条数据此线程用时:12388093ms
JDBC插入100w条数据此线程用时:12389656ms
JDBC插入100w条数据此线程用时:12396625ms
JDBC插入100w条数据此线程用时:12417921ms
JDBC插入100w条数据此线程用时:12431000ms
JDBC插入100w条数据此线程用时:12432875ms
JDBC插入100w条数据此线程用时:12434703ms
JDBC插入100w条数据此线程用时:12455218ms
JDBC插入100w条数据此线程用时:12457109ms
JDBC插入100w条数据此线程用时:12484218ms
JDBC插入100w条数据此线程用时:12518375ms
JDBC插入100w条数据此线程用时:12519015ms
JDBC插入100w条数据此线程用时:12521109ms
JDBC插入100w条数据此线程用时:12521515ms
JDBC插入100w条数据此线程用时:12537343ms
JDBC插入100w条数据此线程用时:12539421ms
JDBC插入100w条数据此线程用时:12544250ms
JDBC插入100w条数据此线程用时:12559234ms
JDBC插入100w条数据此线程用时:12567484ms
JDBC插入100w条数据此线程用时:12574109ms
JDBC插入100w条数据此线程用时:12579156ms
JDBC插入100w条数据此线程用时:12638046ms
JDBC插入100w条数据此线程用时:12693047ms
JDBC插入100w条数据此线程用时:12722906ms
JDBC插入100w条数据此线程用时:12728781ms
JDBC插入100w条数据此线程用时:12732546ms
JDBC插入100w条数据此线程用时:12748265ms
JDBC插入100w条数据此线程用时:12757421ms
JDBC插入100w条数据此线程用时:12761375ms
JDBC插入100w条数据此线程用时:12765312ms
JDBC插入100w条数据此线程用时:12788359ms
JDBC插入100w条数据此线程用时:12802765ms
JDBC插入100w条数据此线程用时:12810484ms
JDBC插入100w条数据此线程用时:12811062ms
JDBC插入100w条数据此线程用时:12811796ms
JDBC插入100w条数据此线程用时:12812843ms
JDBC插入100w条数据此线程用时:12829671ms
JDBC插入100w条数据此线程用时:12830296ms
JDBC插入100w条数据此线程用时:12840000ms
JDBC插入100w条数据此线程用时:12840890ms
JDBC插入100w条数据此线程用时:12850312ms
JDBC插入100w条数据此线程用时:12856671ms
JDBC插入100w条数据此线程用时:12858609ms
JDBC插入100w条数据此线程用时:12860125ms
JDBC插入100w条数据此线程用时:12861750ms
JDBC插入100w条数据此线程用时:12864125ms
JDBC插入100w条数据此线程用时:12875609ms
JDBC插入100w条数据此线程用时:12875781ms
JDBC插入100w条数据此线程用时:12900859ms
JDBC插入100w条数据此线程用时:12906812ms
JDBC插入100w条数据此线程用时:12909656ms
JDBC插入100w条数据此线程用时:12913375ms
JDBC插入100w条数据此线程用时:12915609ms
JDBC插入100w条数据此线程用时:12917562ms
JDBC插入100w条数据此线程用时:12918000ms
JDBC插入100w条数据此线程用时:12919468ms
JDBC插入100w条数据此线程用时:12922093ms
JDBC插入100w条数据此线程用时:12922843ms
JDBC插入100w条数据此线程用时:12924375ms
JDBC插入100w条数据此线程用时:12925734ms
JDBC插入100w条数据此线程用时:12925781ms
JDBC插入100w条数据此线程用时:12931140ms
JDBC插入100w条数据此线程用时:12934562ms
JDBC插入100w条数据此线程用时:12934828ms
JDBC插入100w条数据此线程用时:12935281ms
JDBC插入100w条数据此线程用时:12936953ms
JDBC插入100w条数据此线程用时:12937218ms
JDBC插入100w条数据此线程用时:12937406ms
JDBC插入100w条数据此线程用时:12937765ms
JDBC插入100w条数据此线程用时:12939125ms
JDBC插入100w条数据此线程用时:12940281ms
JDBC插入100w条数据此线程用时:12941828ms
大概一共用了2个多小时内容为1亿条数据mysql的innodb中文件大小为 11.7 GB (12,660,506,624 字节);
首先来看看in查询
SELECT * FROM ipdata WHERE id IN(112358,201023,100020,100001,10000,100000,1000000,10000000,100000000); 141ms
SELECT * FROM ipdata WHERE id IN(12345,123456,1234567,12345678,987654,789654,1236985,852963,9745621,78965412); 141ms
看来in的查询还算理想,
然后我们进行分页必要查询不排序
SELECT id FROM ipdata WHERE uid=1 LIMIT 1,10; 31ms;
SELECT id FROM ipdata WHERE uid=1 LIMIT 10,10; 0ms;
SELECT id FROM ipdata WHERE uid=1 LIMIT 100,10; 0ms;
SELECT id FROM ipdata WHERE uid=1 LIMIT 1000,10; 0ms;
SELECT id FROM ipdata WHERE uid=1 LIMIT 10000,10; 47ms;
SELECT id FROM ipdata WHERE uid=1 LIMIT 100000,10; 235ms;
SELECT id FROM ipdata WHERE uid=1 LIMIT 1000000,10; 1.438s;
SELECT id FROM ipdata WHERE uid=1 LIMIT 5000000,10; 5.422s;
SELECT id FROM ipdata WHERE uid=1 LIMIT 10000000,10; 9.562s; 无返回结果
SELECT id FROM ipdata WHERE uid=1 LIMIT 9999990,10; 10.953s;
符合上一篇的结论mysql越向后越慢,但是整体来说是可以接受的,毕竟分页到最后一页虽然用到了10秒钟,但是后台人员不可能到最后去看,第二呢,10秒后台人员也算可以接受级别;

分页排序查询
SELECT id FROM ipdata WHERE uid=1 ORDER BY id ASC LIMIT 1,10; 0ms;
SELECT id FROM ipdata WHERE uid=1 ORDER BY id ASC LIMIT 10,10; 0ms;
SELECT id FROM ipdata WHERE uid=1 ORDER BY id ASC LIMIT 100,10; 0ms;
SELECT id FROM ipdata WHERE uid=1 ORDER BY id ASC LIMIT 1000,10; 0ms;
SELECT id FROM ipdata WHERE uid=1 ORDER BY id ASC LIMIT 10000,10; 47ms;
SELECT id FROM ipdata WHERE uid=1 ORDER BY id ASC LIMIT 100000,10; 266ms;
SELECT id FROM ipdata WHERE uid=1 ORDER BY id ASC LIMIT 1000000,10; 1.594s;
SELECT id FROM ipdata WHERE uid=1 ORDER BY id ASC LIMIT 5000000,10; 5.625s;
SELECT id FROM ipdata WHERE uid=1 ORDER BY id DESC LIMIT 5000000,10; 11.235s;
SELECT id FROM ipdata WHERE uid=1 ORDER BY id ASC LIMIT 10000000,10; 11.562s 无返回结果
SELECT id FROM ipdata WHERE uid=1 ORDER BY ID ASC LIMIT 9999990,10; 11.719s;
SELECT id FROM ipdata WHERE uid=1 ORDER BY ID DESC LIMIT 9999990,10; 18.719s;
结论是如果单查找id,order by的时间比较可观,但是可见正序和倒序时间不同.

返回全部结果查询"*"
SELECT * FROM ipdata WHERE uid=1 ORDER BY id ASC LIMIT 1,10; 109ms;
SELECT * FROM ipdata WHERE uid=1 ORDER BY id ASC LIMIT 10,10; 0ms;
SELECT * FROM ipdata WHERE uid=1 ORDER BY id ASC LIMIT 100,10; 16ms;
SELECT * FROM ipdata WHERE uid=1 ORDER BY id ASC LIMIT 1000,10; 63ms;
SELECT * FROM ipdata WHERE uid=1 ORDER BY id ASC LIMIT 10000,10; 356ms;
SELECT * FROM ipdata WHERE uid=1 ORDER BY id ASC LIMIT 100000,10; 2.969s;
SELECT * FROM ipdata WHERE uid=1 ORDER BY id ASC LIMIT 1000000,10; 30.766s;
select id,uid,ipaddress,source,track,entrance,createdtime,createddate from ipdata WHERE uid=1 ORDER BY id ASC LIMIT 1000000,10; 29.953s;
...下面的就不测试了,已经难以接受了
结论SELECT id 要比SELECT *快了不少至少在大的结果面前;

结果count测试
SELECT COUNT(*) FROM ipdata WHERE uid=1; 12.281s;
SELECT COUNT(*) FROM ipdata WHERE uid=2; 12.250s;
....
SELECT COUNT(*) FROM ipdata WHERE uid=10; 11.453s;
count级别大概是10多秒左右返回都是1000万;
Count(id)测试
SELECT COUNT(id) FROM ipdata WHERE uid=1; 10.281s;
SELECT COUNT(id) FROM ipdata WHERE uid=2; 10.531s;
....
SELECT COUNT(id) FROM ipdata WHERE uid=10; 12.531s;
Count(id)这里我不知道是机器原因可能测试不是十分准确,总之相差不大,不知道是否mysql默认通过唯一主键来count,如果*和id差不多都方便我还是推荐id,呵呵

   这样我们可以通过SELECT id 来得到id列表,然后通过in来得到相应的记录,可见是可行的;还有在这次测试中我们通过uid这个属性来过滤掉了90%的结果集,如果根据95%过滤理想化可能还有点欠缺,但是根据80%过滤原则就不同了,至少这个索引还是理想的，过滤掉的内容看来mysql就可以算到千万级别的用时了。其实这里面的时间不代表真实时间,毕竟机器也是我们办公室一台pc电脑,数据也比较小,这里我只是有时间来测试一下千万条乃至上亿条数据的处理能力,到服务器上应该要比这个快很多,毕竟磁盘io差距大,而且cpu也有差距,

总结
1.mysql千万级别数据肯定是没问题的,毕竟现在的流向web2.0网站大部分是mysql的
2.合理分表也是必须的,主要涉及横向分表与纵向分表,如把大小字段分开,或者每100万条记录在一张表中等等,像上面的这个表可以考虑通过uid的范围分表,或者通过只建立索引表,去掉相对大的字段来处理.
3.count()时间比较长,但是本身是可以缓存在数据库中或者缓存在程序中的,因为我们当时使用在后台所以第一页比较慢但是后面比较理想
4.SELECT id 相对SELECT * 差距还是比较大的,可以通过上面的方法来使用SELECT id + SELECT * ... IN 查询来提高性能
5.必要的索引是必须的,还是要尽量返回5%-20%的结果级别其中小于5%最理想;
6.mysql分页的前面几页速度很快,越向后性能越差,可以考虑只带上一页,下一页不带页面跳转的方法,呵呵这个比较垃圾但是也算是个方案,只要在前后多查一条就能解决了.比如100,10 你就差99,12呵呵，这样看看前后是否有结果.
7.前台还是要通过其他手段来处理,比如lucene/Solr+mysql结合返回翻页结果集,或者上面的分表
8. 1亿的数据还在我们这里大家可以充分考虑搜索条件我帮大家测试哈哈。

接下来我将要测试一些关于1亿+的用户数据表的解决方案,及大数据的搜索方案通过lucene/solr+mysql

分享到：

(原)CentOS_linux5.5、JDK1.6、tomcat7、 ... | mysql数据库千万级别数据的查询优化和分页 ...

2013-11-23 14:15
浏览 1251
评论(0)
分类:数据库
查看更多

评论

发表评论

您还没有登录,请您登录后再发表评论

相关推荐

Mysql千万级别数据优化方案: "Mysql千万级别数据优化方案" 一、目的与意义在 MySql 单表中数据达到千万级别时，数据的分页查询结果时间过长，对此进行优达到最优效果，也就是时间最短。为了解决这个问题，我们需要了解 MySQL 数据库的分页...

海量数据库查询优化及分页算法方案: 对于拥有千万级乃至亿级记录的大型数据库，高效的查询和分页策略能够显著提升系统的响应速度和用户体验。以下将详细介绍如何针对大规模数据库进行查询优化和实现高效分页。首先，查询优化主要涉及以下几个方面： ...

win7修复本地系统工具: win7修复本地系统工具

《自动化专业英语》04-Automatic-Detection-Block(自动检测模块).ppt: 《自动化专业英语》04-Automatic-Detection-Block(自动检测模块).ppt

《计算机专业英语》chapter12-Intelligent-Transportation.ppt: 《计算机专业英语》chapter12-Intelligent-Transportation.ppt

西门子S7-1200博图平台下3轴伺服螺丝机程序解析与应用: 内容概要：本文详细介绍了基于西门子S7-1200博图平台的3轴伺服螺丝机程序。该程序使用SCL语言编写，结合KTP700组态和TIA V14及以上版本，实现了对X、Y、Z三个轴的精密控制。文章首先概述了程序的整体架构，强调了其在自动化控制领域的高参考价值。接着深入探讨了关键代码片段，如轴初始化、运动控制以及主程序的设计思路。此外，还展示了如何通过KTP700组态实现人机交互，并分享了一些实用的操作技巧和技术细节，如状态机设计、HMI交互、异常处理等。适用人群：从事自动化控制系统开发的技术人员，尤其是对西门子PLC编程感兴趣的工程师。使用场景及目标：适用于希望深入了解西门子S7-1200博图平台及其SCL语言编程特点的学习者；旨在帮助读者掌握3轴伺服系统的具体实现方法，提高实际项目中的编程能力。其他说明：文中提供的代码示例和设计理念不仅有助于理解和学习，还能直接应用于类似的实际工程项目中。

MATLAB仿真：非线性滤波器在水下长基线定位(LBL)系统的应用与比较: 内容概要：本文详细探讨了五种非线性滤波器（卡尔曼滤波(KF)、扩展卡尔曼滤波(EKF)、无迹卡尔曼滤波(UKF)、粒子滤波(PF)和变维卡尔曼滤波(VDKF)）在水下长基线定位（LBL）系统中的应用。通过对每种滤波器的具体实现进行MATLAB代码展示，分析了它们在不同条件下的优缺点。例如，KF适用于线性系统但在非线性环境中失效；EKF通过雅可比矩阵线性化处理非线性问题，但在剧烈机动时表现不佳；UKF利用sigma点处理非线性，精度较高但计算量大；PF采用蒙特卡罗方法，鲁棒性强但计算耗时；VDKF能够动态调整状态维度，适合信标数量变化的场景。适合人群：从事水下机器人(AUV)导航研究的技术人员、研究生以及对非线性滤波感兴趣的科研工作者。使用场景及目标：①理解各种非线性滤波器的工作原理及其在水下定位中的具体应用；②评估不同滤波器在特定条件下的性能，以便为实际项目选择合适的滤波器；③掌握MATLAB实现非线性滤波器的方法和技术。其他说明：文中提供了详细的MATLAB代码片段，帮助读者更好地理解和实现这些滤波器。此外，还讨论了数值稳定性问题和一些实用技巧，如Cholesky分解失败的处理方法。

VMware-workstation-full-14.1.3-9474260: VMware-workstation-full-14.1.3-9474260

DeepSeek系列-提示词工程和落地场景.pdf: DeepSeek系列-提示词工程和落地场景.pdf

javaSE阶段面试题: javaSE阶段面试题

《综合布线施工技术》第5章-综合布线工程测试.ppt: 《综合布线施工技术》第5章-综合布线工程测试.ppt

安川机器人NX100使用说明书.pdf: 安川机器人NX100使用说明书.pdf

S7-1200 PLC改造M7120平面磨床电气控制系统：IO分配、梯形图设计及组态画面实现: 内容概要：本文详细介绍了将M7120型平面磨床的传统继电器控制系统升级为基于西门子S7-1200 PLC的自动化控制系统的过程。主要内容涵盖IO分配、梯形图设计和组态画面实现。通过合理的IO分配，确保了系统的可靠性和可维护性；梯形图设计实现了主控制逻辑、砂轮升降控制和报警逻辑等功能；组态画面则提供了友好的人机交互界面，便于操作和监控。此次改造显著提高了设备的自动化水平、运行效率和可靠性，降低了维护成本。适合人群：从事工业自动化领域的工程师和技术人员，尤其是熟悉PLC编程和控制系统设计的专业人士。使用场景及目标：适用于需要进行老旧设备升级改造的企业，旨在提高生产设备的自动化水平和可靠性，降低故障率和维护成本。具体应用场景包括但不限于金属加工行业中的平面磨床等设备的控制系统改造。其他说明：文中还分享了一些实际调试中的经验和技巧，如急停逻辑的设计、信号抖动的处理方法等，有助于读者在类似项目中借鉴和应用。

chromedriver-linux64-136.0.7103.48.zip: chromedriver-linux64-136.0.7103.48.zip

IMG_20250421_180507.jpg: IMG_20250421_180507.jpg

《网络营销策划实务》项目一-网络营销策划认知.ppt: 《网络营销策划实务》项目一-网络营销策划认知.ppt

Lianantech_Security-Vulnerabil_1744433229.zip: Lianantech_Security-Vulnerabil_1744433229

MybatisCodeHelperNew2019.1-2023.1-3.4.1.zip: MybatisCodeHelperNew2019.1-2023.1-3.4.1

《Approaching(Almost)any machine learning problem》中文版第13章（最后一章）: 【深度学习部署】基于Docker的BERT模型训练与API服务部署：实现代码复用与模型共享

火车票订票系统设计与实现(代码+数据库+LW): 摘要传统办法管理信息首先需要花费的时间比较多，其次数据出错率比较高，而且对错误的数据进行更改也比较困难，最后，检索数据费事费力。因此，在计算机上安装火车票订票系统软件来发挥其高效地信息处理的作用，可以规范信息管理流程，让管理工作可以系统化和程序化，同时，火车票订票系统的有效运用可以帮助管理人员准确快速地处理信息。火车票订票系统在对开发工具的选择上也很慎重，为了便于开发实现，选择的开发工具为Eclipse，选择的数据库工具为Mysql。以此搭建开发环境实现火车票订票系统的功能。其中管理员管理用户，新闻公告。火车票订票系统是一款运用软件开发技术设计实现的应用系统，在信息处理上可以达到快速的目的，不管是针对数据添加，数据维护和统计，以及数据查询等处理要求，火车票订票系统都可以轻松应对。关键词：火车票订票系统；SpringBoot框架，系统分析，数据库设计

Global site tag (gtag.js) - Google Analytics