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

最新推荐文章于 2024-06-05 12:15:55 发布
最新推荐文章于 2024-06-05 12:15:55 发布
阅读量4.6k 收藏
点赞数
分类专栏： @MySQL/Oracle/SQL 文章标签： mysql statistics query system logging java
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/phphot/article/details/4325656
版权

延续上次的实验，http://willko.javaeye.com/blog/407964

200m记录，innodb

先从小量查询开始实验，只考虑随机情况，毕竟生产环境比较少出现顺序.

20个值的情况

Java代码 复制代码
  1. |        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)                                                                                                               |    
  2. |        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 |  
|        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个值的情况

Java代码 复制代码
  1. |        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)                                                                                                               |    
  2. |        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 |  
|        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个值的情况

Java代码 复制代码
  1. |       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 |    
  2. |       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 |   
|       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记录的表上实验

Java代码 复制代码
  1. |       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)                                                                                                                                          |    
  2. |       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 =  |    
  3. |       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 |    
  4. |       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 =  |   
|       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的情况

Java代码 复制代码
  1. +--------------------------------+----------+----------+------------+--------------+---------------+   
  2. | Status                         | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |   
  3. +--------------------------------+----------+----------+------------+--------------+---------------+   
  4. | starting                       | 0.000014 | 0.000000 |   0.000000 |            0 |             0 |    
  5. | checking query cache for query | 0.000048 | 0.000000 |   0.000000 |            0 |             0 |    
  6. | Opening tables                 | 0.000011 | 0.000000 |   0.000000 |            0 |             0 |    
  7. | System lock                    | 0.000003 | 0.000000 |   0.000000 |            0 |             0 |    
  8. | Table lock                     | 0.000019 | 0.000000 |   0.000000 |            0 |             0 |    
  9. | init                           | 0.000039 | 0.000000 |   0.000000 |            0 |             0 |    
  10. | optimizing                     | 0.000007 | 0.000000 |   0.000000 |            0 |             0 |    
  11. | statistics                     | 0.000047 | 0.000000 |   0.000000 |            0 |             0 |    
  12. | preparing                      | 0.000009 | 0.000000 |   0.000000 |            0 |             0 |    
  13. | executing                      | 0.000002 | 0.000000 |   0.000000 |            0 |             0 |    
  14. | Sending data                   | 0.234442 | 0.002000 |   0.001000 |            0 |             0 |    
  15. | end                            | 0.000007 | 0.000000 |   0.000000 |            0 |             0 |    
  16. | query end                      | 0.000003 | 0.000000 |   0.000000 |            0 |             0 |    
  17. | freeing items                  | 0.000031 | 0.000000 |   0.000000 |            0 |             0 |    
  18. | storing result in query cache  | 0.000005 | 0.000000 |   0.000000 |            0 |             0 |    
  19. | logging slow query             | 0.000001 | 0.000000 |   0.000000 |            0 |             0 |    
  20. | cleaning up                    | 0.000004 | 0.000000 |   0.000000 |            0 |             0 |    
  21. +--------------------------------+----------+----------+------------+--------------+---------------+  
+--------------------------------+----------+----------+------------+--------------+---------------+
| 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情况

Java代码 复制代码
  1. +--------------------------------+----------+----------+------------+--------------+---------------+   
  2. | Status                         | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |   
  3. +--------------------------------+----------+----------+------------+--------------+---------------+   
  4. | starting                       | 0.000015 | 0.000000 |   0.000000 |            0 |             0 |    
  5. | checking query cache for query | 0.000123 | 0.000000 |   0.000000 |            0 |             0 |    
  6. | Opening tables                 | 0.000573 | 0.000000 |   0.000000 |            0 |             0 |    
  7. | System lock                    | 0.000004 | 0.000000 |   0.000000 |            0 |             0 |    
  8. | Table lock                     | 0.000607 | 0.001000 |   0.000000 |            0 |             0 |    
  9. | optimizing                     | 0.000012 | 0.000000 |   0.000000 |            0 |             0 |    
  10. | statistics                     | 0.000057 | 0.000000 |   0.000000 |            0 |             0 |    
  11. | preparing                      | 0.000007 | 0.000000 |   0.000000 |            0 |             0 |    
  12. | executing                      | 0.000002 | 0.000000 |   0.000000 |            0 |             0 |    
  13. | Sending data                   | 0.000033 | 0.000000 |   0.000000 |            0 |             0 |    
  14. | optimizing                     | 0.000003 | 0.000000 |   0.000000 |            0 |             0 |    
  15. | statistics                     | 0.000031 | 0.000000 |   0.000000 |            0 |             0 |    
  16. | preparing                      | 0.000003 | 0.000000 |   0.000000 |            0 |             0 |    
  17. | executing                      | 0.000002 | 0.000000 |   0.000000 |            0 |             0 |    
  18. | Sending data                   | 0.000015 | 0.000000 |   0.000000 |            0 |             0 |    
  19. | optimizing                     | 0.000003 | 0.000000 |   0.000000 |            0 |             0 |    
  20. | statistics                     | 0.000026 | 0.000000 |   0.000000 |            0 |             0 |    
  21. | preparing                      | 0.000002 | 0.000000 |   0.000000 |            0 |             0 |    
  22. | executing                      | 0.000001 | 0.000000 |   0.000000 |            0 |             0 |    
  23. | Sending data                   | 0.000017 | 0.000000 |   0.000000 |            0 |             0 |    
  24. | optimizing                     | 0.000003 | 0.000000 |   0.000000 |            0 |             0 |    
  25. | statistics                     | 0.000021 | 0.000000 |   0.000000 |            0 |             0 |    
  26. | preparing                      | 0.000002 | 0.000000 |   0.000000 |            0 |             0 |    
  27. | executing                      | 0.000001 | 0.000000 |   0.000000 |            0 |             0 |    
  28. | optimizing                     | 0.000003 | 0.000000 |   0.000000 |            0 |             0 |    
  29. | statistics                     | 0.000028 | 0.000000 |   0.000000 |            0 |             0 |    
  30. | preparing                      | 0.000003 | 0.000000 |   0.000000 |            0 |             0 |    
  31. | executing                      | 0.000002 | 0.000000 |   0.000000 |            0 |             0 |    
  32. | Sending data                   | 0.000024 | 0.000000 |   0.000000 |            0 |             0 |    
  33. | optimizing                     | 0.000003 | 0.000000 |   0.000000 |            0 |             0 |    
  34. | statistics                     | 0.000030 | 0.000000 |   0.000000 |            0 |             0 |    
  35. | preparing                      | 0.000003 | 0.000000 |   0.000000 |            0 |             0 |    
  36. | executing                      | 0.000001 | 0.000000 |   0.000000 |            0 |             0 |    
  37. | Sending data                   | 0.000016 | 0.000000 |   0.000000 |            0 |             0 |    
  38. | optimizing                     | 0.000003 | 0.000000 |   0.000000 |            0 |             0 |    
  39. | statistics                     | 0.000029 | 0.000000 |   0.000000 |            0 |             0 |    
  40. | preparing                      | 0.000002 | 0.000000 |   0.000000 |            0 |             0 |    
  41. | executing                      | 0.000002 | 0.000000 |   0.000000 |            0 |             0 |    
  42. | Sending data                   | 0.000022 | 0.000000 |   0.000000 |            0 |             0 |    
  43. | optimizing                     | 0.000003 | 0.000000 |   0.000000 |            0 |             0 |    
  44. | statistics                     | 0.000024 | 0.000000 |   0.000000 |            0 |             0 |    
  45. | preparing                      | 0.000002 | 0.000000 |   0.000000 |            0 |             0 |    
  46. | executing                      | 0.000002 | 0.000000 |   0.000000 |            0 |             0 |    
  47. | Sending data                   | 0.000016 | 0.000000 |   0.000000 |            0 |             0 |    
  48. | optimizing                     | 0.000003 | 0.000000 |   0.000000 |            0 |             0 |    
  49. | statistics                     | 0.000032 | 0.000000 |   0.000000 |            0 |             0 |    
  50. | preparing                      | 0.000003 | 0.000000 |   0.000000 |            0 |             0 |    
  51. | executing                      | 0.000001 | 0.000000 |   0.000000 |            0 |             0 |    
  52. | Sending data                   | 0.000016 | 0.000000 |   0.000000 |            0 |             0 |    
  53. | optimizing                     | 0.000003 | 0.000000 |   0.000000 |            0 |             0 |    
  54. | statistics                     | 0.000025 | 0.000000 |   0.000000 |            0 |             0 |    
  55. | preparing                      | 0.000003 | 0.000000 |   0.000000 |            0 |             0 |    
  56. | executing                      | 0.000001 | 0.000000 |   0.000000 |            0 |             0 |    
  57. | Sending data                   | 0.000021 | 0.000000 |   0.000000 |            0 |             0 |    
  58. | optimizing                     | 0.000003 | 0.000000 |   0.000000 |            0 |             0 |    
  59. | statistics                     | 0.000033 | 0.000000 |   0.000000 |            0 |             0 |    
  60. | preparing                      | 0.000002 | 0.000000 |   0.000000 |            0 |             0 |    
  61. | executing                      | 0.000002 | 0.000000 |   0.000000 |            0 |             0 |    
  62. | Sending data                   | 0.000022 | 0.000000 |   0.000000 |            0 |             0 |    
  63. | optimizing                     | 0.000003 | 0.000000 |   0.000000 |            0 |             0 |    
  64. | statistics                     | 0.000027 | 0.000999 |   0.000000 |            0 |             0 |    
  65. | preparing                      | 0.000003 | 0.000000 |   0.000000 |            0 |             0 |    
  66. | executing                      | 0.000001 | 0.000000 |   0.000000 |            0 |             0 |    
  67. | Sending data                   | 0.000016 | 0.000000 |   0.000000 |            0 |             0 |    
  68. | optimizing                     | 0.000003 | 0.000000 |   0.000000 |            0 |             0 |    
  69. | statistics                     | 0.000031 | 0.000000 |   0.000000 |            0 |             0 |    
  70. | preparing                      | 0.000003 | 0.000000 |   0.000000 |            0 |             0 |    
  71. | executing                      | 0.000002 | 0.000000 |   0.000000 |            0 |             0 |    
  72. | Sending data                   | 0.000022 | 0.000000 |   0.000000 |            0 |             0 |    
  73. | optimizing                     | 0.000003 | 0.000000 |   0.000000 |            0 |             0 |    
  74. | statistics                     | 0.000029 | 0.000000 |   0.000000 |            0 |             0 |    
  75. | preparing                      | 0.000003 | 0.000000 |   0.000000 |            0 |             0 |    
  76. | executing                      | 0.000001 | 0.000000 |   0.000000 |            0 |             0 |    
  77. | Sending data                   | 0.000017 | 0.000000 |   0.000000 |            0 |             0 |    
  78. | optimizing                     | 0.000003 | 0.000000 |   0.000000 |            0 |             0 |    
  79. | statistics                     | 0.000028 | 0.000000 |   0.000000 |            0 |             0 |    
  80. | preparing                      | 0.000003 | 0.000000 |   0.000000 |            0 |             0 |    
  81. | executing                      | 0.000001 | 0.000000 |   0.000000 |            0 |             0 |    
  82. | Sending data                   | 0.000018 | 0.000000 |   0.000000 |            0 |             0 |    
  83. | optimizing                     | 0.000002 | 0.000000 |   0.000000 |            0 |             0 |    
  84. | statistics                     | 0.000034 | 0.000000 |   0.000000 |            0 |             0 |    
  85. | preparing                      | 0.000003 | 0.000000 |   0.000000 |            0 |             0 |    
  86. | executing                      | 0.000002 | 0.000000 |   0.000000 |            0 |             0 |    
  87. | Sending data                   | 0.000019 | 0.000000 |   0.000000 |            0 |             0 |    
  88. | optimizing                     | 0.000003 | 0.000000 |   0.000000 |            0 |             0 |    
  89. | statistics                     | 0.000032 | 0.000000 |   0.000000 |            0 |             0 |    
  90. | preparing                      | 0.000003 | 0.000000 |   0.000000 |            0 |             0 |    
  91. | executing                      | 0.000002 | 0.000000 |   0.000000 |            0 |             0 |    
  92. | Sending data                   | 0.000016 | 0.000000 |   0.000000 |            0 |             0 |    
  93. | optimizing                     | 0.000003 | 0.000000 |   0.000000 |            0 |             0 |    
  94. | statistics                     | 0.000028 | 0.000000 |   0.000000 |            0 |             0 |    
  95. | preparing                      | 0.000002 | 0.000000 |   0.000000 |            0 |             0 |    
  96. | executing                      | 0.000002 | 0.000000 |   0.000000 |            0 |             0 |    
  97. | Sending data                   | 0.000016 | 0.000000 |   0.000000 |            0 |             0 |    
  98. | optimizing                     | 0.000003 | 0.000000 |   0.000000 |            0 |             0 |    
  99. | statistics                     | 0.000028 | 0.000000 |   0.000000 |            0 |             0 |    
  100. | preparing                      | 0.000003 | 0.000000 |   0.000000 |            0 |             0 |    
  101. | executing                      | 0.000001 | 0.000000 |   0.000000 |            0 |             0 |    
  102. | Sending data                   | 0.000024 | 0.000000 |   0.000000 |            0 |             0 |    
  103. | optimizing                     | 0.000003 | 0.000000 |   0.000000 |            0 |             0 |    
  104. | statistics                     | 0.000024 | 0.000000 |   0.000000 |            0 |             0 |    
  105. | preparing                      | 0.000002 | 0.000000 |   0.000000 |            0 |             0 |    
  106. | executing                      | 0.000010 | 0.000000 |   0.000000 |            0 |             0 |    
  107. | optimizing                     | 0.000002 | 0.000000 |   0.000000 |            0 |             0 |    
  108. | statistics                     | 0.000005 | 0.000000 |   0.000000 |            0 |             0 |    
  109. | preparing                      | 0.000004 | 0.000000 |   0.000000 |            0 |             0 |    
  110. | executing                      | 0.000001 | 0.000000 |   0.000000 |            0 |             0 |    
  111. | Sending data                   | 0.000143 | 0.000000 |   0.000000 |            0 |             0 |    
  112. | removing tmp table             | 0.000004 | 0.000000 |   0.000000 |            0 |             0 |    
  113. | Sending data                   | 0.000007 | 0.000000 |   0.000000 |            0 |             0 |    
  114. | query end                      | 0.000003 | 0.000000 |   0.000000 |            0 |             0 |    
  115. | freeing items                  | 0.000048 | 0.000000 |   0.000000 |            0 |             0 |    
  116. | storing result in query cache  | 0.000018 | 0.000000 |   0.000000 |            0 |             0 |    
  117. | logging slow query             | 0.000001 | 0.000000 |   0.000000 |            0 |             0 |    
  118. | logging slow query             | 0.000043 | 0.000000 |   0.000000 |            0 |             0 |    
  119. | cleaning up                    | 0.000004 | 0.000000 |   0.000000 |            0 |             0 |    
  120. +--------------------------------+----------+----------+------------+--------------+---------------+  
+--------------------------------+----------+----------+------------+--------------+---------------+
| 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是怎么实现查找的，这个就不清楚了。

郁闷

phphot
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 12
    评论
专栏目录
mysql关于in大量数据的解决办法
迷失的鱼的博客
07-22 4685
1.创建临时表 2.join 表 3.EXISTS
mysql 批量查找 in_在数据库查询时解决大量in 关键字的方法
weixin_30689519的博客
02-07 3208
有时候在前台界面需要批量处理一些数据时，经常要用到update table set fields=value where keyid in ('1','2',....)但如果这个数据量如果超过1000,在oracle里是不被支持的，这样就只能写程序循环，或者用代码以1000为单位进行or连接，效率非常低下。一个朋友给我介绍了另一种方法，用临时表，一直没有时间测试性能，只做个记录吧CREATE TA...
深入浅出mysql海量数据批量更新插入、批量查询
最新发布
小伍的程序之路
06-05 1425
这个参数后的抓包截图，jdbc客户端重写了sql语句，它把多个sql语句用分号分隔连接在一起，形成一个大sql脚本， 然后将这个sql脚本一次性发送到server端，最后接收到了大量的response响应。mysql提供了批量写入的方法，将大批量的sql脚本一批次发送到服务端，减少IO次数，然后统一一次执行sql。理论上，上面的sql是批量提交到mysql server统一执行的，但在默认情况下实际上它还是一条条执行命令，要真正的批量执行sql，需要在jdbc连接url加上。，重启项目再执行接口。
mysql如何优化in太多_如何优化MySQL in查询
weixin_34580441的博客
01-19 9348
提供几个方案：1) 如果内存足够用的话(这个表都没有特别大)，可以使用mysql的memory engine，即把查询都放到内存里就行了。memory engine可以使用hash index。2)使用memcache或者redis作为cache，相当于每次查询时都要multi_get一次，没有命中的再回mysql查，可以大大的降低mysql的in后面跟的数量。查询回来之后，再multi_set一...
MySQL优化
qq_40250529的博客
03-15 301
一、EXPLAIN做MySQL优化，我们要善用 EXPLAIN 查看SQL执行计划。下面来个简单的示例，标注(1,2,3,4,5)我们要重点关注的数据type列，连接类型。一个好的sql语句至少要达到range级别。杜绝出现all级别key列，使用到的索引名。如果没有选择索引，值是NULL。可以采取强制索引方式key_len列，索引长度rows列，扫描行数。该值是个预估值extra列，详细说明。注...
MySql中 where IN 中多个参数查询
热门推荐
weixin_40725027的博客
10-11 3万+
mysql查询语句where条件in 正常情况需要查询的语句： select *from temp where id in ('1','2','3','4','5') 在实际过程中发现一直查不出数据，实际上能够查出数据，弄得一直很郁闷，找不出原因。 通过各终尝试，最后在项目中通过断点找出原因。 在进行查询时in中自动添加了单引号，如： select *from temp where id in ...
Mysql解决数据库N+1查询问题
09-09
在数据库管理中，N+1查询问题是一种常见的性能瓶颈，特别是在使用ORM（对象关系映射）框架如Hibernate或MyBatis时。这个问题出现在当我们需要获取一个对象及其关联对象时，通常会先执行一次主查询获取主对象，然后对...
为什么MySQL数据库索引选择使用B+树?
09-09
MySQL数据库在设计索引时选择使用B+树作为底层数据结构，这主要基于以下几个原因： ...在实际应用中，B+树不仅在MySQL中使用广泛，也是许多其他关系型数据库系统如Oracle、PostgreSQL等的首选索引结构。
MySQL 索引：索引为什么使用 B+树？ · .pdf
02-22
B+ 树是 MySQL 中使用的索引结构。B+ 树的每个节点可以有多个子树，且每个节点的子树可以有多个叶节点。B+ 树的高度远远小于 AVL 树和红黑树，磁盘 IO 次数大大减少。B+ 树的定义中最重要的概念是阶数（Order），...
关系型数据库+Mysql+查询用户连续登陆天数+数据统计
06-25
关系型数据库+Mysql+查询用户连续登陆天数+数据统计 关系型数据库+Mysql+查询用户连续登陆天数+数据统计 关系型数据库+Mysql+查询用户连续登陆天数+数据统计 关系型数据库+Mysql+查询用户连续登陆天数+数据统计 关系...
一次Mysql使用IN大数据量的优化记录
01-19
mysql版本号是5.7.28，表A有390W条记录，使用InnoDB引擎，其中varchar类型字段mac已建立索引，索引方法为B-tree。B表仅有5000+条记录。 有一条SQL指令是这样写的： SELECT * FROM A WHERE mac IN(aa:aa:aa:aa:aa:aa,bb:bb:bb:bb:bb:b,...此外省略900+条) 通过查询出来的结果耗时294.428s。没错，将近5分钟。 使用EXPLAIN分析下： 访问类型type是range，且已命中索引，rows行也只有587776，可为什么查询耗时要这么久？ mac的索引方法使用了B-tree，那
MySQL入门笔记 —— 022 新手1+N查询
烟敛寒林的博客
04-01 226
查询价格大于2000元的商品及其栏目id及其商品价格 先把商品取出 那取出的商品没有栏目名称怎么办？ 所谓的新手1+N查询
mysql 批量查找 in_dbutils使用---QueryRunner实现in批量查询
weixin_39925098的博客
02-07 495
sql.append("AND a.").append(MchStore.STORE_PROVINCE_COL).append(" IN (");for (int i = 0; i < size; i++) {if (i == size - 1) {sql.append("?");} else {sql.append("?,");}params.put(MchStore.STORE_PROV...
MyBatis 实现消息页面，批量查询用户信息
weixin_34221332的博客
02-19 367
本文的原文连接是: http://blog.csdn.net/freewebsys/article/details/50699141 未经博主允许不得转载。 博主地址是：http://blog.csdn.net/freewebsys 1，背景 开发了java这么久，有的时候会偷懒写一些代码，影响性能。 比如 消息列表里面，...
n点不能开设mysql数据库_Mysql解决数据库N+1查询问题
weixin_30705133的博客
01-18 121
简介在orm框架中，比如hibernate和mybatis都可以设置关联对象，比如user对象关联dept假如查询出n个user，那么需要做n次查询dept，查询user是一次select，查询user关联的dept，是n次，所以是n+1问题，其实叫1+n更为合理一些。mybatis配置UserMapper.xml数据表如下：department表|id|name|user表|id|name|de...
mysql的索引使用B树还是B+树？
04-08
MySQL的索引使用的是B+树[^1][^2]。 B+树是B树的改进版，它在B树的基础上进行了优化。B+树的特点是： - 所有的数据都存储在叶子节点上，而非叶子节点只存储索引信息。 - 叶子节点之间通过指针连接，形成一个有序...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 12
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值