willko
- 浏览: 383804 次
- 性别:
- 来自: 深圳
-
文章分类
最新评论
-
zjx20：
...
非阻塞(nonblock)socket接口会否出现EINTR错误 -
mrfeng：
Digest 方式呢?
构建RESTful Web Service - 验证的实现和使用（HTTP 基本认证） -
lck140：
lck140 写道我照着楼主的方法试了一天了，根本就不行。是不 ...
nginx 预压缩(gzip)静态文件 -
lck140：
我照着楼主的方法试了一天了，根本就不行。是不是有哪没弄好，请高 ...
nginx 预压缩(gzip)静态文件 -
iwlk：
chrome 是支持gzip的， 只是添加了sdch~ 大家 ...
nginx 预压缩(gzip)静态文件
延续上次的实验，http://willko.iteye.com/blog/407964
200m记录，innodb
先从小量查询开始实验，只考虑随机情况，毕竟生产环境比较少出现顺序.
20个值的情况
50个值的情况
500个值的情况
在一张只有100k记录的表上实验
注意，即使使用union，query cache还是按照整条sql来算的。
看到这样的结果，我想大家都有结论了，使用n+1比用in快n倍，估计上次实验有问题，，我们来看看他们的执行过程
这是in的情况
n+1情况
in的话主要耗费在sending data上，而n+1比较稳定并且比in多了临时表，sending data包括索引的查找以及数据的传输，我想in慢在查找上，因为是innodb而且是主键，只要找到主键就可以得到数据。
至于，in是怎么实现查找的，这个就不清楚了。
郁闷
这次，我调整了一下配置。。。
200m记录，innodb
先从小量查询开始实验，只考虑随机情况，毕竟生产环境比较少出现顺序.
20个值的情况
| 3 | 0.23469200 | SELECT * FROM Member WHERE MemberID IN (1072317,944960,232144,1221849,1718824,1971030,1634860,219179,1733544,618875,1033368,914264,1657167,687934,2164378,1675400,816727,1072638,56834,798724) | | 4 | 0.00271600 | SELECT * FROM Member WHERE MemberID = 1072317 UNION SELECT * FROM Member WHERE MemberID = 944960 UNION SELECT * FROM Member WHERE MemberID = 232144 UNION SELECT * FROM Member WHERE MemberID = 1221849 UNION SELECT * FROM Member WHERE MemberID = 1718824 UNION SELECT * FROM Member WHERE MemberID = 1971 |
50个值的情况
| 3 | 0.23469200 | SELECT * FROM Member WHERE MemberID IN (1072317,944960,232144,1221849,1718824,1971030,1634860,219179,1733544,618875,1033368,914264,1657167,687934,2164378,1675400,816727,1072638,56834,798724) | | 4 | 0.00271600 | SELECT * FROM Member WHERE MemberID = 1072317 UNION SELECT * FROM Member WHERE MemberID = 944960 UNION SELECT * FROM Member WHERE MemberID = 232144 UNION SELECT * FROM Member WHERE MemberID = 1221849 UNION SELECT * FROM Member WHERE MemberID = 1718824 UNION SELECT * FROM Member WHERE MemberID = 1971 |
500个值的情况
| 11 | 4.89638400 | SELECT * FROM Member WHERE MemberID IN (1940366,1592700,1400564,745603,439521,1782230,1627418,1968030,1173113,1406275,1157786,382329,1252380,2202431,2142859,714044,1178282,1463622,1069076,955140,2071311,647081,619895,154986,1068419,1900229,1792226,1796517,1568490,687304,2059599,912862,1797395,168722 | | 12 | 0.07686600 | SELECT * FROM Member WHERE MemberID = 1940366 UNION SELECT * FROM Member WHERE MemberID = 1592700 UNION SELECT * FROM Member WHERE MemberID = 1400564 UNION SELECT * FROM Member WHERE MemberID = 745603 UNION SELECT * FROM Member WHERE MemberID = 439521 UNION SELECT * FROM Member WHERE MemberID = 1782 |
在一张只有100k记录的表上实验
| 18 | 0.12457700 | SELECT * FROM Product WHERE ProductID IN (11089,108843,80895,6486,91179,109813,97611,49713,90237,56495,114315,773,119650,55401,8965,61268,60379,13692,114931,71883) | | 19 | 0.00348100 | SELECT * FROM Product WHERE ProductID = 11089 UNION SELECT * FROM Product WHERE ProductID = 108843 UNION SELECT * FROM Product WHERE ProductID = 80895 UNION SELECT * FROM Product WHERE ProductID = 6486 UNION SELECT * FROM Product WHERE ProductID = 91179 UNION SELECT * FROM Product WHERE ProductID = | | 20 | 0.35769600 | SELECT * FROM Product WHERE ProductID IN (52447,28980,59590,80193,98487,22829,78756,70810,86308,60046,81279,67714,99244,89245,69998,48611,81038,17256,45283,119693,108364,97453,47837,81514,457,26157,115691,13263,102098,101610,38318,32815,101610,45720,31842,90977,53938,86167,6973,3819,22670,81914,8805 | | 21 | 0.00640500 | SELECT * FROM Product WHERE ProductID = 52447 UNION SELECT * FROM Product WHERE ProductID = 28980 UNION SELECT * FROM Product WHERE ProductID = 59590 UNION SELECT * FROM Product WHERE ProductID = 80193 UNION SELECT * FROM Product WHERE ProductID = 98487 UNION SELECT * FROM Product WHERE ProductID = |
注意，即使使用union，query cache还是按照整条sql来算的。
看到这样的结果，我想大家都有结论了，使用n+1比用in快n倍，估计上次实验有问题，，我们来看看他们的执行过程
这是in的情况
+--------------------------------+----------+----------+------------+--------------+---------------+ | Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out | +--------------------------------+----------+----------+------------+--------------+---------------+ | starting | 0.000014 | 0.000000 | 0.000000 | 0 | 0 | | checking query cache for query | 0.000048 | 0.000000 | 0.000000 | 0 | 0 | | Opening tables | 0.000011 | 0.000000 | 0.000000 | 0 | 0 | | System lock | 0.000003 | 0.000000 | 0.000000 | 0 | 0 | | Table lock | 0.000019 | 0.000000 | 0.000000 | 0 | 0 | | init | 0.000039 | 0.000000 | 0.000000 | 0 | 0 | | optimizing | 0.000007 | 0.000000 | 0.000000 | 0 | 0 | | statistics | 0.000047 | 0.000000 | 0.000000 | 0 | 0 | | preparing | 0.000009 | 0.000000 | 0.000000 | 0 | 0 | | executing | 0.000002 | 0.000000 | 0.000000 | 0 | 0 | | Sending data | 0.234442 | 0.002000 | 0.001000 | 0 | 0 | | end | 0.000007 | 0.000000 | 0.000000 | 0 | 0 | | query end | 0.000003 | 0.000000 | 0.000000 | 0 | 0 | | freeing items | 0.000031 | 0.000000 | 0.000000 | 0 | 0 | | storing result in query cache | 0.000005 | 0.000000 | 0.000000 | 0 | 0 | | logging slow query | 0.000001 | 0.000000 | 0.000000 | 0 | 0 | | cleaning up | 0.000004 | 0.000000 | 0.000000 | 0 | 0 | +--------------------------------+----------+----------+------------+--------------+---------------+
n+1情况
+--------------------------------+----------+----------+------------+--------------+---------------+ | Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out | +--------------------------------+----------+----------+------------+--------------+---------------+ | starting | 0.000015 | 0.000000 | 0.000000 | 0 | 0 | | checking query cache for query | 0.000123 | 0.000000 | 0.000000 | 0 | 0 | | Opening tables | 0.000573 | 0.000000 | 0.000000 | 0 | 0 | | System lock | 0.000004 | 0.000000 | 0.000000 | 0 | 0 | | Table lock | 0.000607 | 0.001000 | 0.000000 | 0 | 0 | | optimizing | 0.000012 | 0.000000 | 0.000000 | 0 | 0 | | statistics | 0.000057 | 0.000000 | 0.000000 | 0 | 0 | | preparing | 0.000007 | 0.000000 | 0.000000 | 0 | 0 | | executing | 0.000002 | 0.000000 | 0.000000 | 0 | 0 | | Sending data | 0.000033 | 0.000000 | 0.000000 | 0 | 0 | | optimizing | 0.000003 | 0.000000 | 0.000000 | 0 | 0 | | statistics | 0.000031 | 0.000000 | 0.000000 | 0 | 0 | | preparing | 0.000003 | 0.000000 | 0.000000 | 0 | 0 | | executing | 0.000002 | 0.000000 | 0.000000 | 0 | 0 | | Sending data | 0.000015 | 0.000000 | 0.000000 | 0 | 0 | | optimizing | 0.000003 | 0.000000 | 0.000000 | 0 | 0 | | statistics | 0.000026 | 0.000000 | 0.000000 | 0 | 0 | | preparing | 0.000002 | 0.000000 | 0.000000 | 0 | 0 | | executing | 0.000001 | 0.000000 | 0.000000 | 0 | 0 | | Sending data | 0.000017 | 0.000000 | 0.000000 | 0 | 0 | | optimizing | 0.000003 | 0.000000 | 0.000000 | 0 | 0 | | statistics | 0.000021 | 0.000000 | 0.000000 | 0 | 0 | | preparing | 0.000002 | 0.000000 | 0.000000 | 0 | 0 | | executing | 0.000001 | 0.000000 | 0.000000 | 0 | 0 | | optimizing | 0.000003 | 0.000000 | 0.000000 | 0 | 0 | | statistics | 0.000028 | 0.000000 | 0.000000 | 0 | 0 | | preparing | 0.000003 | 0.000000 | 0.000000 | 0 | 0 | | executing | 0.000002 | 0.000000 | 0.000000 | 0 | 0 | | Sending data | 0.000024 | 0.000000 | 0.000000 | 0 | 0 | | optimizing | 0.000003 | 0.000000 | 0.000000 | 0 | 0 | | statistics | 0.000030 | 0.000000 | 0.000000 | 0 | 0 | | preparing | 0.000003 | 0.000000 | 0.000000 | 0 | 0 | | executing | 0.000001 | 0.000000 | 0.000000 | 0 | 0 | | Sending data | 0.000016 | 0.000000 | 0.000000 | 0 | 0 | | optimizing | 0.000003 | 0.000000 | 0.000000 | 0 | 0 | | statistics | 0.000029 | 0.000000 | 0.000000 | 0 | 0 | | preparing | 0.000002 | 0.000000 | 0.000000 | 0 | 0 | | executing | 0.000002 | 0.000000 | 0.000000 | 0 | 0 | | Sending data | 0.000022 | 0.000000 | 0.000000 | 0 | 0 | | optimizing | 0.000003 | 0.000000 | 0.000000 | 0 | 0 | | statistics | 0.000024 | 0.000000 | 0.000000 | 0 | 0 | | preparing | 0.000002 | 0.000000 | 0.000000 | 0 | 0 | | executing | 0.000002 | 0.000000 | 0.000000 | 0 | 0 | | Sending data | 0.000016 | 0.000000 | 0.000000 | 0 | 0 | | optimizing | 0.000003 | 0.000000 | 0.000000 | 0 | 0 | | statistics | 0.000032 | 0.000000 | 0.000000 | 0 | 0 | | preparing | 0.000003 | 0.000000 | 0.000000 | 0 | 0 | | executing | 0.000001 | 0.000000 | 0.000000 | 0 | 0 | | Sending data | 0.000016 | 0.000000 | 0.000000 | 0 | 0 | | optimizing | 0.000003 | 0.000000 | 0.000000 | 0 | 0 | | statistics | 0.000025 | 0.000000 | 0.000000 | 0 | 0 | | preparing | 0.000003 | 0.000000 | 0.000000 | 0 | 0 | | executing | 0.000001 | 0.000000 | 0.000000 | 0 | 0 | | Sending data | 0.000021 | 0.000000 | 0.000000 | 0 | 0 | | optimizing | 0.000003 | 0.000000 | 0.000000 | 0 | 0 | | statistics | 0.000033 | 0.000000 | 0.000000 | 0 | 0 | | preparing | 0.000002 | 0.000000 | 0.000000 | 0 | 0 | | executing | 0.000002 | 0.000000 | 0.000000 | 0 | 0 | | Sending data | 0.000022 | 0.000000 | 0.000000 | 0 | 0 | | optimizing | 0.000003 | 0.000000 | 0.000000 | 0 | 0 | | statistics | 0.000027 | 0.000999 | 0.000000 | 0 | 0 | | preparing | 0.000003 | 0.000000 | 0.000000 | 0 | 0 | | executing | 0.000001 | 0.000000 | 0.000000 | 0 | 0 | | Sending data | 0.000016 | 0.000000 | 0.000000 | 0 | 0 | | optimizing | 0.000003 | 0.000000 | 0.000000 | 0 | 0 | | statistics | 0.000031 | 0.000000 | 0.000000 | 0 | 0 | | preparing | 0.000003 | 0.000000 | 0.000000 | 0 | 0 | | executing | 0.000002 | 0.000000 | 0.000000 | 0 | 0 | | Sending data | 0.000022 | 0.000000 | 0.000000 | 0 | 0 | | optimizing | 0.000003 | 0.000000 | 0.000000 | 0 | 0 | | statistics | 0.000029 | 0.000000 | 0.000000 | 0 | 0 | | preparing | 0.000003 | 0.000000 | 0.000000 | 0 | 0 | | executing | 0.000001 | 0.000000 | 0.000000 | 0 | 0 | | Sending data | 0.000017 | 0.000000 | 0.000000 | 0 | 0 | | optimizing | 0.000003 | 0.000000 | 0.000000 | 0 | 0 | | statistics | 0.000028 | 0.000000 | 0.000000 | 0 | 0 | | preparing | 0.000003 | 0.000000 | 0.000000 | 0 | 0 | | executing | 0.000001 | 0.000000 | 0.000000 | 0 | 0 | | Sending data | 0.000018 | 0.000000 | 0.000000 | 0 | 0 | | optimizing | 0.000002 | 0.000000 | 0.000000 | 0 | 0 | | statistics | 0.000034 | 0.000000 | 0.000000 | 0 | 0 | | preparing | 0.000003 | 0.000000 | 0.000000 | 0 | 0 | | executing | 0.000002 | 0.000000 | 0.000000 | 0 | 0 | | Sending data | 0.000019 | 0.000000 | 0.000000 | 0 | 0 | | optimizing | 0.000003 | 0.000000 | 0.000000 | 0 | 0 | | statistics | 0.000032 | 0.000000 | 0.000000 | 0 | 0 | | preparing | 0.000003 | 0.000000 | 0.000000 | 0 | 0 | | executing | 0.000002 | 0.000000 | 0.000000 | 0 | 0 | | Sending data | 0.000016 | 0.000000 | 0.000000 | 0 | 0 | | optimizing | 0.000003 | 0.000000 | 0.000000 | 0 | 0 | | statistics | 0.000028 | 0.000000 | 0.000000 | 0 | 0 | | preparing | 0.000002 | 0.000000 | 0.000000 | 0 | 0 | | executing | 0.000002 | 0.000000 | 0.000000 | 0 | 0 | | Sending data | 0.000016 | 0.000000 | 0.000000 | 0 | 0 | | optimizing | 0.000003 | 0.000000 | 0.000000 | 0 | 0 | | statistics | 0.000028 | 0.000000 | 0.000000 | 0 | 0 | | preparing | 0.000003 | 0.000000 | 0.000000 | 0 | 0 | | executing | 0.000001 | 0.000000 | 0.000000 | 0 | 0 | | Sending data | 0.000024 | 0.000000 | 0.000000 | 0 | 0 | | optimizing | 0.000003 | 0.000000 | 0.000000 | 0 | 0 | | statistics | 0.000024 | 0.000000 | 0.000000 | 0 | 0 | | preparing | 0.000002 | 0.000000 | 0.000000 | 0 | 0 | | executing | 0.000010 | 0.000000 | 0.000000 | 0 | 0 | | optimizing | 0.000002 | 0.000000 | 0.000000 | 0 | 0 | | statistics | 0.000005 | 0.000000 | 0.000000 | 0 | 0 | | preparing | 0.000004 | 0.000000 | 0.000000 | 0 | 0 | | executing | 0.000001 | 0.000000 | 0.000000 | 0 | 0 | | Sending data | 0.000143 | 0.000000 | 0.000000 | 0 | 0 | | removing tmp table | 0.000004 | 0.000000 | 0.000000 | 0 | 0 | | Sending data | 0.000007 | 0.000000 | 0.000000 | 0 | 0 | | query end | 0.000003 | 0.000000 | 0.000000 | 0 | 0 | | freeing items | 0.000048 | 0.000000 | 0.000000 | 0 | 0 | | storing result in query cache | 0.000018 | 0.000000 | 0.000000 | 0 | 0 | | logging slow query | 0.000001 | 0.000000 | 0.000000 | 0 | 0 | | logging slow query | 0.000043 | 0.000000 | 0.000000 | 0 | 0 | | cleaning up | 0.000004 | 0.000000 | 0.000000 | 0 | 0 | +--------------------------------+----------+----------+------------+--------------+---------------+
in的话主要耗费在sending data上，而n+1比较稳定并且比in多了临时表，sending data包括索引的查找以及数据的传输，我想in慢在查找上，因为是innodb而且是主键，只要找到主键就可以得到数据。
至于，in是怎么实现查找的，这个就不清楚了。
郁闷
分享到：
评论
2 楼
willko
2009-07-08
mayongzhan 写道
测试200w的数据.是n+1的方法速度快.不知道你上次的实验是怎么做出来的,不过这n+1代码太恶心了..语句太长了.
希望能出现第三篇总结的文章.
希望能出现第三篇总结的文章.
这次，我调整了一下配置。。。
1 楼
mayongzhan
2009-07-06
测试200w的数据.是n+1的方法速度快.不知道你上次的实验是怎么做出来的,不过这n+1代码太恶心了..语句太长了.
希望能出现第三篇总结的文章.
希望能出现第三篇总结的文章.
发表评论
-
High Performance MySQL 笔录(schema/index 部分)
2010-05-19 18:49 7159主要记录阅读High Performance MySQL后新发 ... -
FYI, MySQL高效分页
2010-03-05 17:23 6320在Percona Performance Conferen ... -
更改MySQL log size导致的问题
2009-09-13 12:59 4054一db对innodb表执行dml操作出现错误： ERROR 1 ... -
改善InnoDB恢复的时间
2009-07-08 22:35 1962InnoDB的恢复速度是一个 ... -
mysql中批量查询使用in还是n+1？
2009-06-14 01:45 4799某日，在一LAMP群里，讨论这个，有些人倾向于in，有些人倾向 ... -
http://www.day32.com/MySQL/
2009-04-15 09:11 0http://www.day32.com/MySQL/ -
启动mysql出现错误，找不到系统表
2009-03-22 02:05 3726之前的服务器被别人拿了，所以自己买了台美国vps，根据笔记安装 ... -
XtraBackup
2009-03-19 09:29 0备份备份备份备份备份备份备份备份备份备份备份 -
mysql 随机获取记录 order by rand 优化
2009-02-28 16:07 25043如果要随机获取记录数，在mysql里最简单的方法肯定是orde ... -
mysql分页limit 优化
2009-02-09 12:55 31001mysql的分页比较简单，只需要limit offset,le ... -
安装mysql5 innodb存储引擎
2009-02-08 20:11 3532今天把mysql5.0升级到5.1.30，发现配置的时候出错了 ... -
整合编译mysql和tcmalloc失败的原因
2009-02-08 10:41 6006google-perftools里的tcmalloc比glib ... -
设置合适的 InnoDB 日志文件大小的计算方法
2009-02-08 10:37 2072做下笔记，简单记录一下，InnoDB日志文件太大，会影响MYS ...
相关推荐
关系型数据库+Mysql+查询用户连续登陆天数+数据统计 关系型数据库+Mysql+查询用户连续登陆天数+数据统计 关系型数据库+Mysql+查询用户连续登陆天数+数据统计 关系型数据库+Mysql+查询用户连续登陆天数+数据统计 关系...
MySQL 索引：索引为什么使用 B+树？ · .pdf
MySQL++使用手册,开发者必备!详细的介绍了mysql++的使用，同时含有丰富的例程。
php+mysql学生成绩查询（系统+源码） php+mysql学生成绩查询（系统+源码） php+mysql学生成绩查询（系统+源码） ...vphp+mysql学生成绩查询（系统+源码） php+mysql学生成绩查询（系统+源码） php+mysql学生成绩查询
php+mysql+html实例php+mysql+html实例php+mysql+html实例php+mysql+html实例php+mysql+html实例php+mysql+html实例php+mysql+html实例php+mysql+html实例php+mysql+html实例
springboot+mybatis+mysql最简单demospringboot+mybatis+mysql最简单demospringboot+mybatis+mysql最简单demospringboot+mybatis+mysql最简单demospringboot+mybatis+mysql最简单demospringboot+mybatis+mysql最简单...
c++中通过mysql++ api访问mysql数据库的使用介绍及一个msql++ api的封装文档，收藏之，用时可以参考
今天小编就为大家分享一篇关于为什么MySQL数据库索引选择使用B+树?，小编觉得内容挺不错的，现在分享给大家，具有很好的参考价值，需要的朋友一起跟随小编来看看吧
基于SSM框架+JSP+Ajax请求 mysql数据 关于员工信息的新增+修改+查询+删除基于SSM框架+JSP+Ajax请求 mysql数据 关于员工信息的新增+修改+查询+删除基于SSM框架+JSP+Ajax请求 mysql数据 关于员工信息的新增+修改+查询+...
php+mysql学生成绩查询系统(源代码+论文) 毕业设计/课程设计
文件中展示了在mfc下如何连接mysql数据库的操作，并附带自己编译的mysql++库和一些必要的头文件
课程实验 使用QT+Mysql 实现学生选课管理系统.zip 课程实验 使用QT+Mysql 实现学生选课管理系统.zip 课程实验 使用QT+Mysql 实现学生选课管理系统.zip 课程实验 使用QT+Mysql 实现学生选课管理系统.zip 课程实验 ...
Django + python + mysql的在线考试系统源码 Django + python + mysql的在线考试系统源码 Django + python + mysql的在线考试系统源码 Django + python + mysql的在线考试系统源码 Django + python + mysql的...
这是一个java WEB小项目，用 Maven Springboot+mybatis+mysql+redis集成的小项目例子，可供新手进行参考学习，数据库是mysql 5.5 ,只是一个简单的小参考例子，测试过可以正常运行
资源名字：基于php+mysql的成绩查询系统设计与实现（源码+文档）_php_mysql_成绩查询系统.zip 资源内容：项目全套源码+完整文档 源码说明： 全部项目源码都是经过测试校正后百分百成功运行。 适合场景：相关项目设计...
使用Eclipse+Jboss+MySql开发EJB
Mysql+haproxy+mycat+pxc+zookeeper实现高可用集群
mysql++ V3.1.0中文用户手册CHM,适用于初使用mysql++的同学
mysql5.7安装教程+mysql5.7镜像安装+mysql学习+mysql5.7镜像包 mysql5.7安装教程+mysql5.7镜像安装+mysql学习+mysql5.7镜像包 mysql5.7安装教程+mysql5.7镜像安装+mysql学习+mysql5.7镜像包 mysql5.7安装教程+mysql...