`
willko
  • 浏览: 386333 次
  • 性别: Icon_minigender_1
  • 来自: 深圳
社区版块
存档分类
最新评论

再实验mysql中批量查询使用in还是n+1?

阅读更多
延续上次的实验,http://willko.iteye.com/blog/407964

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代码太恶心了..语句太长了.

希望能出现第三篇总结的文章.

相关推荐

    关系型数据库+Mysql+查询用户连续登陆天数+数据统计

    关系型数据库+Mysql+查询用户连续登陆天数+数据统计 关系型数据库+Mysql+查询用户连续登陆天数+数据统计 关系型数据库+Mysql+查询用户连续登陆天数+数据统计 关系型数据库+Mysql+查询用户连续登陆天数+数据统计 关系...

    MySQL查询语句汇总+编程知识+开发技术

    MySQL查询语句汇总+编程知识+开发技术; MySQL查询语句汇总+编程知识+开发技术; MySQL查询语句汇总+编程知识+开发技术; MySQL查询语句汇总+编程知识+开发技术; MySQL查询语句汇总+编程知识+开发技术; MySQL查询...

    MySQL++使用手册

    #### 七、在多线程程序中使用 MySQL++ **7.1 构建问题** - **目的**:解决在多线程环境中构建 MySQL++ 的常见问题。 - **代码**:例如,配置编译选项。 **7.2 连接管理** - **目的**:说明如何在多线程环境下管理...

    MySQL 索引:索引为什么使用 B+树? · .pdf

    B+ 树是 MySQL 中使用的索引结构。B+ 树的每个节点可以有多个子树,且每个节点的子树可以有多个叶节点。B+ 树的高度远远小于 AVL 树和红黑树,磁盘 IO 次数大大减少。B+ 树的定义中最重要的概念是阶数(Order),...

    数据库查询指令:mysql查询语句汇总+编程知识+技术开发

    mysql查询语句汇总,数据库查询指令:mysql查询语句汇总+编程知识+技术开发;mysql查询语句汇总,数据库查询指令:mysql查询语句汇总+编程知识+技术开发;mysql查询语句汇总,数据库查询指令:mysql查询语句汇总+...

    mfc中使用mysql++

    "mfc中使用mysql++"这个主题就是关于如何在MFC应用中集成MySQL++库,以便能方便地操作MySQL数据库。MySQL++是一个C++封装库,它为MySQL数据库提供了易于使用的接口。 首先,你需要确保已经安装了Visual Studio (VS)...

    mysql++使用及封装示例文档

    首先,我们来看一下如何在C++中使用MySQL++ API来连接和查询数据库。基本步骤包括初始化库,创建数据库连接,执行SQL语句,处理结果集,以及关闭连接。以下是一个简单的示例: ```cpp #include <mysql++.h> using ...

    php+mysql学生成绩查询(系统+源码)

    php+mysql学生成绩查询(系统+源码) php+mysql学生成绩查询(系统+源码) php+mysql学生成绩查询(系统+源码) ...vphp+mysql学生成绩查询(系统+源码) php+mysql学生成绩查询(系统+源码) php+mysql学生成绩查询

    Mysql解决数据库N+1查询问题

    在数据库管理中,N+1查询问题是一种常见的性能瓶颈,特别是在使用ORM(对象关系映射)框架如Hibernate或MyBatis时。这个问题出现在当我们需要获取一个对象及其关联对象时,通常会先执行一次主查询获取主对象,然后对...

    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实例php+mysql+html实例

    apache+mysql+php+snort+base实现snort

    【Apache+Mysql+Php+Snort+Base 实现 Snort 知识点详解】 Apache、MySQL、PHP、Snort 和 BASE 是构建一个强大的入侵检测系统(IDS)的基础组件。这个组合使得网络管理员能够实时监控网络流量,检测潜在的攻击,并...

    基于SSM框架+JSP+Ajax请求 mysql数据 关于员工信息的新增+修改+查询+删除

    基于SSM框架+JSP+Ajax请求 mysql数据 关于员工信息的新增+修改+查询+删除基于SSM框架+JSP+Ajax请求 mysql数据 关于员工信息的新增+修改+查询+删除基于SSM框架+JSP+Ajax请求 mysql数据 关于员工信息的新增+修改+查询+...

    mysql++安装及使用文档

    MySQL++ 是一个C++库,它为MySQL数据库提供了一种方便的接口,使得开发者能够使用C++语言编写MySQL数据库相关的应用程序。这篇文档将详细介绍如何在Windows环境下,利用Visual Studio 2008来安装和使用MySQL++。 ...

    Apache+mysql+php

    在IT领域,"Apache+MySQL+PHP"是一个经典的Web开发环境组合,被称为LAMP(Linux, Apache, MySQL, PHP)的Windows版本,这里为WAMP(Windows, Apache, MySQL, PHP)。这个组合提供了强大的功能,使得开发者能够在本地...

    mysql++ v3.2.0 文档

    在文档中,我们可以看到它详细介绍了 MySQL++ 的历史、如何使用、如何处理常见问题、连接对象、查询对象、结果集、异常处理、模板查询、特殊 SQL 结构以及如何在多线程程序中使用 MySQL++ 等内容。接下来我们将详细...

    Django + python + mysql的在线考试系统源码.zip

    Django + python + mysql的在线考试系统源码 Django + python + mysql的在线考试系统源码 Django + python + mysql的在线考试系统源码 Django + python + mysql的在线考试系统源码 Django + python + mysql的...

    课程实验 使用QT+Mysql 实现学生选课管理系统.zip

    课程实验 使用QT+Mysql 实现学生选课管理系统.zip 课程实验 使用QT+Mysql 实现学生选课管理系统.zip 课程实验 使用QT+Mysql 实现学生选课管理系统.zip 课程实验 使用QT+Mysql 实现学生选课管理系统.zip 课程实验 ...

    springboot+mybatis+mysql最简单demo

    springboot+mybatis+mysql最简单demospringboot+mybatis+mysql最简单demospringboot+mybatis+mysql最简单demospringboot+mybatis+mysql最简单demospringboot+mybatis+mysql最简单demospringboot+mybatis+mysql最简单...

    Springboot+mybatis+mysql+redis

    这是一个java WEB小项目,用 Maven Springboot+mybatis+mysql+redis集成的小项目例子,可供新手进行参考学习,数据库是mysql 5.5 ,只是一个简单的小参考例子,测试过可以正常运行

    使用 Python+Flask+MySQL+Redis 开发简单接口实例

    使用 Python+Flask+MySQL+Redis 开发简单接口实例 flaskDemo 本接口项目的技术选型:Python+Flask+MySQL+Redis,通过 Python+Falsk 来开发接口,使用 MySQL 来存储用户信息,使用 Redis 用于存储token,目前为纯后端...

Global site tag (gtag.js) - Google Analytics