探讨select in 在postgresql的效率问题

忘是亡心i 2022-05-26 07:59 306阅读 0赞

这篇文章主要介绍了探讨select in 在postgresql的效率问题 的相关资料,需要的朋友可以参考下

在知乎上看到这样一个问题：

MySQL 查询 select \* from table where id in (几百或几千个 id) 如何提高效率？修改

电商网站，一个商品属性表，几十万条记录，80M，索引只有主键id，做这样的查询如何提高效率？

select \* from table where id in (几百或几千个id)

这些id没啥规律，分散的。。。。

看了一下答案，感觉有好多不靠谱的，但是口说无凭，所以在我的电脑上写了几个查询测试一下。我用的是Postgresql9.4，但感觉mysql应该也差不多，首先创建一个简单表，只有简单的3列，在这个问题的下面好多人提到了需要看表的大小，其实这个问题和表大小无关，只和index的大小有关，因为是index是建立在int上的，所以只和纪录数目有关。

[?][Link 1]

<table style="border-spacing:0px;border:1px solid rgb(204,204,204);margin-top:.54em;text-align:center;width:969px;clear:both;background:none;float:none;height:auto;line-height:1.1em;margin-right:0px;margin-bottom:0px;margin-left:0px;padding:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-size:1em;min-height:auto;"> 
 <tbody style="background:none;float:none;height:auto;line-height:1.1em;margin:0px;padding:0px;text-align:left;vertical-align:baseline;width:auto;font-size:1em;min-height:auto;"> 
  <tr style="background:none;border-top:0px;float:none;height:auto;line-height:1.1em;margin:0px;padding:0px;vertical-align:baseline;width:auto;font-size:1em;min-height:auto;"> 
   <td style="padding:.2em .46em;border-color:rgb(204,204,204);margin:0px;background:none;float:none;height:auto;line-height:1.1em;vertical-align:baseline;width:auto;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-size:1em;min-height:auto;color:rgb(175,175,175);"> 
    <div style="line-height:15.4px;margin:0px;padding:0px .5em 0px 1em;background-image:none;border-width:0px 3px 0px 0px;border-right-style:solid;border-right-color:rgb(108,226,108);float:none;height:auto;text-align:right;vertical-align:baseline;width:auto;min-height:auto;white-space:pre;">
      1 
    </div> 
    <div style="line-height:15.4px;margin:0px;padding:0px .5em 0px 1em;background-image:none;border-width:0px 3px 0px 0px;border-right-style:solid;border-right-color:rgb(108,226,108);float:none;height:auto;text-align:right;vertical-align:baseline;width:auto;min-height:auto;white-space:pre;">
      2 
    </div> 
    <div style="line-height:15.4px;margin:0px;padding:0px .5em 0px 1em;background-image:none;border-width:0px 3px 0px 0px;border-right-style:solid;border-right-color:rgb(108,226,108);float:none;height:auto;text-align:right;vertical-align:baseline;width:auto;min-height:auto;white-space:pre;">
      3 
    </div> 
    <div style="line-height:15.4px;margin:0px;padding:0px .5em 0px 1em;background-image:none;border-width:0px 3px 0px 0px;border-right-style:solid;border-right-color:rgb(108,226,108);float:none;height:auto;text-align:right;vertical-align:baseline;width:auto;min-height:auto;white-space:pre;">
      4 
    </div> 
    <div style="line-height:15.4px;margin:0px;padding:0px .5em 0px 1em;background-image:none;border-width:0px 3px 0px 0px;border-right-style:solid;border-right-color:rgb(108,226,108);float:none;height:auto;text-align:right;vertical-align:baseline;width:auto;min-height:auto;white-space:pre;">
      5 
    </div> 
    <div style="line-height:15.4px;margin:0px;padding:0px .5em 0px 1em;background-image:none;border-width:0px 3px 0px 0px;border-right-style:solid;border-right-color:rgb(108,226,108);float:none;height:auto;text-align:right;vertical-align:baseline;width:auto;min-height:auto;white-space:pre;">
      6 
    </div> 
    <div style="line-height:15.4px;margin:0px;padding:0px .5em 0px 1em;background-image:none;border-width:0px 3px 0px 0px;border-right-style:solid;border-right-color:rgb(108,226,108);float:none;height:auto;text-align:right;vertical-align:baseline;width:auto;min-height:auto;white-space:pre;">
      7 
    </div> 
    <div style="line-height:15.4px;margin:0px;padding:0px .5em 0px 1em;background-image:none;border-width:0px 3px 0px 0px;border-right-style:solid;border-right-color:rgb(108,226,108);float:none;height:auto;text-align:right;vertical-align:baseline;width:auto;min-height:auto;white-space:pre;">
      8 
    </div></td> 
   <td style="padding:.2em .46em;border-color:rgb(204,204,204);width:936px;margin:0px;background:none;float:none;height:auto;line-height:1.1em;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-size:1em;min-height:auto;"> 
    <div style="line-height:15.4px;margin:0px;padding:0px;background:none;border:0px;float:none;height:auto;vertical-align:baseline;width:auto;min-height:auto;"> 
     <div style="line-height:15.4px;margin:0px;padding:0px 1em;background-image:none;border:0px;float:none;height:auto;vertical-align:baseline;width:auto;min-height:auto;white-space:pre;"> 
      <code style="margin:3px auto 0px;padding:0px 0px 0px 5px;background:rgb(255,255,255);border-left:3px solid rgb(108,226,108);width:640px;font-size:14px;clear:both;font-weight:bold;border-top:0px;border-right:0px;border-bottom:0px;float:none;height:auto;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:rgb(0,102,153);">Table</code> 
      <code style="margin:3px auto 0px;padding:0px 0px 0px 5px;background:rgb(255,255,255);border-left:3px solid rgb(108,226,108);width:640px;font-size:14px;clear:both;border-top:0px;border-right:0px;border-bottom:0px;float:none;height:auto;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:#0000FF;">"public.t9"</code> 
     </div> 
     <div style="line-height:15.4px;margin:0px;padding:0px 1em;background-image:none;border:0px;float:none;height:auto;vertical-align:baseline;width:auto;min-height:auto;white-space:pre;"> 
      <code style="margin:3px auto 0px;padding:0px 0px 0px 5px;background:rgb(255,255,255);border-left:3px solid rgb(108,226,108);width:640px;font-size:14px;clear:both;font-weight:bold;border-top:0px;border-right:0px;border-bottom:0px;float:none;height:auto;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:rgb(0,102,153);">Column</code> 
      <code style="margin:3px auto 0px;padding:0px 0px 0px 5px;background:rgb(255,255,255);border-left:3px solid rgb(108,226,108);width:640px;font-size:14px;clear:both;border-top:0px;border-right:0px;border-bottom:0px;float:none;height:auto;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:#000000;">| Type | Modifiers</code> 
     </div> 
     <div style="line-height:15.4px;margin:0px;padding:0px 1em;background-image:none;border:0px;float:none;height:auto;vertical-align:baseline;width:auto;min-height:auto;white-space:pre;"> 
      <code style="margin:3px auto 0px;padding:0px 0px 0px 5px;background:rgb(255,255,255);border-left:3px solid rgb(108,226,108);width:640px;font-size:14px;clear:both;border-top:0px;border-right:0px;border-bottom:0px;float:none;height:auto;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:rgb(0,130,0);">--------+----------------+-----------</code> 
     </div> 
     <div style="line-height:15.4px;margin:0px;padding:0px 1em;background-image:none;border:0px;float:none;height:auto;vertical-align:baseline;width:auto;min-height:auto;white-space:pre;"> 
      <code style="margin:3px auto 0px;padding:0px 0px 0px 5px;background:rgb(255,255,255);border-left:3px solid rgb(108,226,108);width:640px;font-size:14px;clear:both;border-top:0px;border-right:0px;border-bottom:0px;float:none;height:auto;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:#000000;">c1 | </code> 
      <code style="margin:3px auto 0px;padding:0px 0px 0px 5px;background:rgb(255,255,255);border-left:3px solid rgb(108,226,108);width:640px;font-size:14px;clear:both;font-weight:bold;border-top:0px;border-right:0px;border-bottom:0px;float:none;height:auto;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:rgb(0,102,153);">integer</code> 
      <code style="margin:3px auto 0px;padding:0px 0px 0px 5px;background:rgb(255,255,255);border-left:3px solid rgb(108,226,108);width:640px;font-size:14px;clear:both;border-top:0px;border-right:0px;border-bottom:0px;float:none;height:auto;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:#000000;">|</code> 
     </div> 
     <div style="line-height:15.4px;margin:0px;padding:0px 1em;background-image:none;border:0px;float:none;height:auto;vertical-align:baseline;width:auto;min-height:auto;white-space:pre;"> 
      <code style="margin:3px auto 0px;padding:0px 0px 0px 5px;background:rgb(255,255,255);border-left:3px solid rgb(108,226,108);width:640px;font-size:14px;clear:both;border-top:0px;border-right:0px;border-bottom:0px;float:none;height:auto;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:#000000;">c2 | </code> 
      <code style="margin:3px auto 0px;padding:0px 0px 0px 5px;background:rgb(255,255,255);border-left:3px solid rgb(108,226,108);width:640px;font-size:14px;clear:both;font-weight:bold;border-top:0px;border-right:0px;border-bottom:0px;float:none;height:auto;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:rgb(0,102,153);">character</code> 
      <code style="margin:3px auto 0px;padding:0px 0px 0px 5px;background:rgb(255,255,255);border-left:3px solid rgb(108,226,108);width:640px;font-size:14px;clear:both;border-top:0px;border-right:0px;border-bottom:0px;float:none;height:auto;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:#000000;">(100) |</code> 
     </div> 
     <div style="line-height:15.4px;margin:0px;padding:0px 1em;background-image:none;border:0px;float:none;height:auto;vertical-align:baseline;width:auto;min-height:auto;white-space:pre;"> 
      <code style="margin:3px auto 0px;padding:0px 0px 0px 5px;background:rgb(255,255,255);border-left:3px solid rgb(108,226,108);width:640px;font-size:14px;clear:both;border-top:0px;border-right:0px;border-bottom:0px;float:none;height:auto;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:#000000;">c3 | </code> 
      <code style="margin:3px auto 0px;padding:0px 0px 0px 5px;background:rgb(255,255,255);border-left:3px solid rgb(108,226,108);width:640px;font-size:14px;clear:both;font-weight:bold;border-top:0px;border-right:0px;border-bottom:0px;float:none;height:auto;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:rgb(0,102,153);">character</code> 
      <code style="margin:3px auto 0px;padding:0px 0px 0px 5px;background:rgb(255,255,255);border-left:3px solid rgb(108,226,108);width:640px;font-size:14px;clear:both;border-top:0px;border-right:0px;border-bottom:0px;float:none;height:auto;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:#000000;">(200) |</code> 
     </div> 
     <div style="line-height:15.4px;margin:0px;padding:0px 1em;background-image:none;border:0px;float:none;height:auto;vertical-align:baseline;width:auto;min-height:auto;white-space:pre;"> 
      <code style="margin:3px auto 0px;padding:0px 0px 0px 5px;background:rgb(255,255,255);border-left:3px solid rgb(108,226,108);width:640px;font-size:14px;clear:both;border-top:0px;border-right:0px;border-bottom:0px;float:none;height:auto;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:#000000;">Indexes:</code> 
     </div> 
     <div style="line-height:15.4px;margin:0px;padding:0px 1em;background-image:none;border:0px;float:none;height:auto;vertical-align:baseline;width:auto;min-height:auto;white-space:pre;"> 
      <code style="margin:3px auto 0px;padding:0px 0px 0px 5px;background:rgb(255,255,255);border-left:3px solid rgb(108,226,108);width:640px;font-size:14px;clear:both;border-top:0px;border-right:0px;border-bottom:0px;float:none;height:auto;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:#0000FF;">"i1"</code> 
      <code style="margin:3px auto 0px;padding:0px 0px 0px 5px;background:rgb(255,255,255);border-left:3px solid rgb(108,226,108);width:640px;font-size:14px;clear:both;font-weight:bold;border-top:0px;border-right:0px;border-bottom:0px;float:none;height:auto;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:rgb(0,102,153);">UNIQUE</code> 
      <code style="margin:3px auto 0px;padding:0px 0px 0px 5px;background:rgb(255,255,255);border-left:3px solid rgb(108,226,108);width:640px;font-size:14px;clear:both;border-top:0px;border-right:0px;border-bottom:0px;float:none;height:auto;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:#000000;">, btree (c1)</code> 
      <code style="margin:3px auto 0px;padding:0px 0px 0px 5px;background:rgb(255,255,255);border-left:3px solid rgb(108,226,108);width:640px;font-size:14px;clear:both;font-weight:bold;border-top:0px;border-right:0px;border-bottom:0px;float:none;height:auto;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:rgb(0,102,153);">insert</code> 
      <code style="margin:3px auto 0px;padding:0px 0px 0px 5px;background:rgb(255,255,255);border-left:3px solid rgb(108,226,108);width:640px;font-size:14px;clear:both;font-weight:bold;border-top:0px;border-right:0px;border-bottom:0px;float:none;height:auto;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:rgb(0,102,153);">into</code> 
      <code style="margin:3px auto 0px;padding:0px 0px 0px 5px;background:rgb(255,255,255);border-left:3px solid rgb(108,226,108);width:640px;font-size:14px;clear:both;border-top:0px;border-right:0px;border-bottom:0px;float:none;height:auto;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:#000000;">t9 </code> 
      <code style="margin:3px auto 0px;padding:0px 0px 0px 5px;background:rgb(255,255,255);border-left:3px solid rgb(108,226,108);width:640px;font-size:14px;clear:both;font-weight:bold;border-top:0px;border-right:0px;border-bottom:0px;float:none;height:auto;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:rgb(0,102,153);">values</code> 
      <code style="margin:3px auto 0px;padding:0px 0px 0px 5px;background:rgb(255,255,255);border-left:3px solid rgb(108,226,108);width:640px;font-size:14px;clear:both;border-top:0px;border-right:0px;border-bottom:0px;float:none;height:auto;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:#000000;">(generate_series(1000,500000,1),repeat(</code> 
      <code style="margin:3px auto 0px;padding:0px 0px 0px 5px;background:rgb(255,255,255);border-left:3px solid rgb(108,226,108);width:640px;font-size:14px;clear:both;border-top:0px;border-right:0px;border-bottom:0px;float:none;height:auto;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:#0000FF;">'a'</code> 
      <code style="margin:3px auto 0px;padding:0px 0px 0px 5px;background:rgb(255,255,255);border-left:3px solid rgb(108,226,108);width:640px;font-size:14px;clear:both;border-top:0px;border-right:0px;border-bottom:0px;float:none;height:auto;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:#000000;">,90),repeat(</code> 
      <code style="margin:3px auto 0px;padding:0px 0px 0px 5px;background:rgb(255,255,255);border-left:3px solid rgb(108,226,108);width:640px;font-size:14px;clear:both;border-top:0px;border-right:0px;border-bottom:0px;float:none;height:auto;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:#0000FF;">'b'</code> 
      <code style="margin:3px auto 0px;padding:0px 0px 0px 5px;background:rgb(255,255,255);border-left:3px solid rgb(108,226,108);width:640px;font-size:14px;clear:both;border-top:0px;border-right:0px;border-bottom:0px;float:none;height:auto;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:#000000;">,180));</code> 
     </div> 
    </div></td> 
  </tr> 
 </tbody> 
</table>

之后生成一些随机数，Mac上用jot，Linux上用shuf

[?][Link 1]

然后根据rand.file 生成查询语句：

[?][Link 1]

<table style="border-spacing:0px;border:1px solid rgb(204,204,204);margin-top:.54em;text-align:center;width:660px;clear:both;background:none;float:none;height:auto;line-height:1.1em;margin-right:0px;margin-bottom:0px;margin-left:0px;padding:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-size:1em;min-height:auto;"> 
 <tbody style="background:none;float:none;height:auto;line-height:1.1em;margin:0px;padding:0px;text-align:left;vertical-align:baseline;width:auto;font-size:1em;min-height:auto;"> 
  <tr style="background:none;border-top:0px;float:none;height:auto;line-height:1.1em;margin:0px;padding:0px;vertical-align:baseline;width:auto;font-size:1em;min-height:auto;"> 
   <td style="padding:.2em .46em;border-color:rgb(204,204,204);margin:0px;background:none;float:none;height:auto;line-height:1.1em;vertical-align:baseline;width:auto;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-size:1em;min-height:auto;color:rgb(175,175,175);"> 
    <div style="line-height:15.4px;margin:0px;padding:0px .5em 0px 1em;background-image:none;border-width:0px 3px 0px 0px;border-right-style:solid;border-right-color:rgb(108,226,108);float:none;height:auto;text-align:right;vertical-align:baseline;width:auto;min-height:auto;white-space:pre;">
      1 
    </div> 
    <div style="line-height:15.4px;margin:0px;padding:0px .5em 0px 1em;background-image:none;border-width:0px 3px 0px 0px;border-right-style:solid;border-right-color:rgb(108,226,108);float:none;height:auto;text-align:right;vertical-align:baseline;width:auto;min-height:auto;white-space:pre;">
      2 
    </div> 
    <div style="line-height:15.4px;margin:0px;padding:0px .5em 0px 1em;background-image:none;border-width:0px 3px 0px 0px;border-right-style:solid;border-right-color:rgb(108,226,108);float:none;height:auto;text-align:right;vertical-align:baseline;width:auto;min-height:auto;white-space:pre;">
      3 
    </div> 
    <div style="line-height:15.4px;margin:0px;padding:0px .5em 0px 1em;background-image:none;border-width:0px 3px 0px 0px;border-right-style:solid;border-right-color:rgb(108,226,108);float:none;height:auto;text-align:right;vertical-align:baseline;width:auto;min-height:auto;white-space:pre;">
      4 
    </div> 
    <div style="line-height:15.4px;margin:0px;padding:0px .5em 0px 1em;background-image:none;border-width:0px 3px 0px 0px;border-right-style:solid;border-right-color:rgb(108,226,108);float:none;height:auto;text-align:right;vertical-align:baseline;width:auto;min-height:auto;white-space:pre;">
      5 
    </div> 
    <div style="line-height:15.4px;margin:0px;padding:0px .5em 0px 1em;background-image:none;border-width:0px 3px 0px 0px;border-right-style:solid;border-right-color:rgb(108,226,108);float:none;height:auto;text-align:right;vertical-align:baseline;width:auto;min-height:auto;white-space:pre;">
      6 
    </div> 
    <div style="line-height:15.4px;margin:0px;padding:0px .5em 0px 1em;background-image:none;border-width:0px 3px 0px 0px;border-right-style:solid;border-right-color:rgb(108,226,108);float:none;height:auto;text-align:right;vertical-align:baseline;width:auto;min-height:auto;white-space:pre;">
      7 
    </div> 
    <div style="line-height:15.4px;margin:0px;padding:0px .5em 0px 1em;background-image:none;border-width:0px 3px 0px 0px;border-right-style:solid;border-right-color:rgb(108,226,108);float:none;height:auto;text-align:right;vertical-align:baseline;width:auto;min-height:auto;white-space:pre;">
      8 
    </div> 
    <div style="line-height:15.4px;margin:0px;padding:0px .5em 0px 1em;background-image:none;border-width:0px 3px 0px 0px;border-right-style:solid;border-right-color:rgb(108,226,108);float:none;height:auto;text-align:right;vertical-align:baseline;width:auto;min-height:auto;white-space:pre;">
      9 
    </div> 
    <div style="line-height:15.4px;margin:0px;padding:0px .5em 0px 1em;background-image:none;border-width:0px 3px 0px 0px;border-right-style:solid;border-right-color:rgb(108,226,108);float:none;height:auto;text-align:right;vertical-align:baseline;width:auto;min-height:auto;white-space:pre;">
      10 
    </div> 
    <div style="line-height:15.4px;margin:0px;padding:0px .5em 0px 1em;background-image:none;border-width:0px 3px 0px 0px;border-right-style:solid;border-right-color:rgb(108,226,108);float:none;height:auto;text-align:right;vertical-align:baseline;width:auto;min-height:auto;white-space:pre;">
      11 
    </div> 
    <div style="line-height:15.4px;margin:0px;padding:0px .5em 0px 1em;background-image:none;border-width:0px 3px 0px 0px;border-right-style:solid;border-right-color:rgb(108,226,108);float:none;height:auto;text-align:right;vertical-align:baseline;width:auto;min-height:auto;white-space:pre;">
      12 
    </div> 
    <div style="line-height:15.4px;margin:0px;padding:0px .5em 0px 1em;background-image:none;border-width:0px 3px 0px 0px;border-right-style:solid;border-right-color:rgb(108,226,108);float:none;height:auto;text-align:right;vertical-align:baseline;width:auto;min-height:auto;white-space:pre;">
      13 
    </div></td> 
   <td style="padding:.2em .46em;border-color:rgb(204,204,204);width:619px;margin:0px;background:none;float:none;height:auto;line-height:1.1em;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-size:1em;min-height:auto;"> 
    <div style="line-height:15.4px;margin:0px;padding:0px;background:none;border:0px;float:none;height:auto;vertical-align:baseline;width:auto;min-height:auto;"> 
     <div style="line-height:15.4px;margin:0px;padding:0px 1em;background-image:none;border:0px;float:none;height:auto;vertical-align:baseline;width:auto;min-height:auto;white-space:pre;"> 
      <code style="margin:3px auto 0px;padding:0px 0px 0px 5px;background:rgb(255,255,255);border-left:3px solid rgb(108,226,108);width:640px;font-size:14px;clear:both;font-weight:bold;border-top:0px;border-right:0px;border-bottom:0px;float:none;height:auto;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:rgb(0,102,153);">select</code> 
      <code style="margin:3px auto 0px;padding:0px 0px 0px 5px;background:rgb(255,255,255);border-left:3px solid rgb(108,226,108);width:640px;font-size:14px;clear:both;border-top:0px;border-right:0px;border-bottom:0px;float:none;height:auto;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:#000000;">* </code> 
      <code style="margin:3px auto 0px;padding:0px 0px 0px 5px;background:rgb(255,255,255);border-left:3px solid rgb(108,226,108);width:640px;font-size:14px;clear:both;font-weight:bold;border-top:0px;border-right:0px;border-bottom:0px;float:none;height:auto;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:rgb(0,102,153);">from</code> 
      <code style="margin:3px auto 0px;padding:0px 0px 0px 5px;background:rgb(255,255,255);border-left:3px solid rgb(108,226,108);width:640px;font-size:14px;clear:both;border-top:0px;border-right:0px;border-bottom:0px;float:none;height:auto;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:#000000;">t9 </code> 
      <code style="margin:3px auto 0px;padding:0px 0px 0px 5px;background:rgb(255,255,255);border-left:3px solid rgb(108,226,108);width:640px;font-size:14px;clear:both;font-weight:bold;border-top:0px;border-right:0px;border-bottom:0px;float:none;height:auto;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:rgb(0,102,153);">where</code> 
      <code style="margin:3px auto 0px;padding:0px 0px 0px 5px;background:rgb(255,255,255);border-left:3px solid rgb(108,226,108);width:640px;font-size:14px;clear:both;border-top:0px;border-right:0px;border-bottom:0px;float:none;height:auto;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:#000000;">c1 </code> 
      <code style="margin:3px auto 0px;padding:0px 0px 0px 5px;background:rgb(255,255,255);border-left:3px solid rgb(108,226,108);width:640px;font-size:14px;clear:both;border-top:0px;border-right:0px;border-bottom:0px;float:none;height:auto;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:#808080;">in</code> 
      <code style="margin:3px auto 0px;padding:0px 0px 0px 5px;background:rgb(255,255,255);border-left:3px solid rgb(108,226,108);width:640px;font-size:14px;clear:both;border-top:0px;border-right:0px;border-bottom:0px;float:none;height:auto;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:#000000;">(</code> 
     </div> 
     <div style="line-height:15.4px;margin:0px;padding:0px 1em;background-image:none;border:0px;float:none;height:auto;vertical-align:baseline;width:auto;min-height:auto;white-space:pre;"> 
      <code style="margin:3px auto 0px;padding:0px 0px 0px 5px;background:rgb(255,255,255);border-left:3px solid rgb(108,226,108);width:640px;font-size:14px;clear:both;border-top:0px;border-right:0px;border-bottom:0px;float:none;height:auto;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:#000000;">494613,</code> 
     </div> 
     <div style="line-height:15.4px;margin:0px;padding:0px 1em;background-image:none;border:0px;float:none;height:auto;vertical-align:baseline;width:auto;min-height:auto;white-space:pre;"> 
      <code style="margin:3px auto 0px;padding:0px 0px 0px 5px;background:rgb(255,255,255);border-left:3px solid rgb(108,226,108);width:640px;font-size:14px;clear:both;border-top:0px;border-right:0px;border-bottom:0px;float:none;height:auto;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:#000000;">575087,</code> 
     </div> 
     <div style="line-height:15.4px;margin:0px;padding:0px 1em;background-image:none;border:0px;float:none;height:auto;vertical-align:baseline;width:auto;min-height:auto;white-space:pre;"> 
      <code style="margin:3px auto 0px;padding:0px 0px 0px 5px;background:rgb(255,255,255);border-left:3px solid rgb(108,226,108);width:640px;font-size:14px;clear:both;border-top:0px;border-right:0px;border-bottom:0px;float:none;height:auto;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:#000000;">363588,</code> 
     </div> 
     <div style="line-height:15.4px;margin:0px;padding:0px 1em;background-image:none;border:0px;float:none;height:auto;vertical-align:baseline;width:auto;min-height:auto;white-space:pre;"> 
      <code style="margin:3px auto 0px;padding:0px 0px 0px 5px;background:rgb(255,255,255);border-left:3px solid rgb(108,226,108);width:640px;font-size:14px;clear:both;border-top:0px;border-right:0px;border-bottom:0px;float:none;height:auto;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:#000000;">527650,</code> 
     </div> 
     <div style="line-height:15.4px;margin:0px;padding:0px 1em;background-image:none;border:0px;float:none;height:auto;vertical-align:baseline;width:auto;min-height:auto;white-space:pre;"> 
      <code style="margin:3px auto 0px;padding:0px 0px 0px 5px;background:rgb(255,255,255);border-left:3px solid rgb(108,226,108);width:640px;font-size:14px;clear:both;border-top:0px;border-right:0px;border-bottom:0px;float:none;height:auto;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:#000000;">251670,</code> 
     </div> 
     <div style="line-height:15.4px;margin:0px;padding:0px 1em;background-image:none;border:0px;float:none;height:auto;vertical-align:baseline;width:auto;min-height:auto;white-space:pre;"> 
      <code style="margin:3px auto 0px;padding:0px 0px 0px 5px;background:rgb(255,255,255);border-left:3px solid rgb(108,226,108);width:640px;font-size:14px;clear:both;border-top:0px;border-right:0px;border-bottom:0px;float:none;height:auto;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:#000000;">343456,</code> 
     </div> 
     <div style="line-height:15.4px;margin:0px;padding:0px 1em;background-image:none;border:0px;float:none;height:auto;vertical-align:baseline;width:auto;min-height:auto;white-space:pre;"> 
      <code style="margin:3px auto 0px;padding:0px 0px 0px 5px;background:rgb(255,255,255);border-left:3px solid rgb(108,226,108);width:640px;font-size:14px;clear:both;border-top:0px;border-right:0px;border-bottom:0px;float:none;height:auto;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:#000000;">426858,</code> 
     </div> 
     <div style="line-height:15.4px;margin:0px;padding:0px 1em;background-image:none;border:0px;float:none;height:auto;vertical-align:baseline;width:auto;min-height:auto;white-space:pre;"> 
      <code style="margin:3px auto 0px;padding:0px 0px 0px 5px;background:rgb(255,255,255);border-left:3px solid rgb(108,226,108);width:640px;font-size:14px;clear:both;border-top:0px;border-right:0px;border-bottom:0px;float:none;height:auto;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:#000000;">202886,</code> 
     </div> 
     <div style="line-height:15.4px;margin:0px;padding:0px 1em;background-image:none;border:0px;float:none;height:auto;vertical-align:baseline;width:auto;min-height:auto;white-space:pre;"> 
      <code style="margin:3px auto 0px;padding:0px 0px 0px 5px;background:rgb(255,255,255);border-left:3px solid rgb(108,226,108);width:640px;font-size:14px;clear:both;border-top:0px;border-right:0px;border-bottom:0px;float:none;height:auto;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:#000000;">254037,</code> 
     </div> 
     <div style="line-height:15.4px;margin:0px;padding:0px 1em;background-image:none;border:0px;float:none;height:auto;vertical-align:baseline;width:auto;min-height:auto;white-space:pre;"> 
      <code style="margin:3px auto 0px;padding:0px 0px 0px 5px;background:rgb(255,255,255);border-left:3px solid rgb(108,226,108);width:640px;font-size:14px;clear:both;border-top:0px;border-right:0px;border-bottom:0px;float:none;height:auto;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:#000000;">...</code> 
     </div> 
     <div style="line-height:15.4px;margin:0px;padding:0px 1em;background-image:none;border:0px;float:none;height:auto;vertical-align:baseline;width:auto;min-height:auto;white-space:pre;"> 
      <code style="margin:3px auto 0px;padding:0px 0px 0px 5px;background:rgb(255,255,255);border-left:3px solid rgb(108,226,108);width:640px;font-size:14px;clear:both;border-top:0px;border-right:0px;border-bottom:0px;float:none;height:auto;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:#000000;">1</code> 
     </div> 
     <div style="line-height:15.4px;margin:0px;padding:0px 1em;background-image:none;border:0px;float:none;height:auto;vertical-align:baseline;width:auto;min-height:auto;white-space:pre;"> 
      <code style="margin:3px auto 0px;padding:0px 0px 0px 5px;background:rgb(255,255,255);border-left:3px solid rgb(108,226,108);width:640px;font-size:14px;clear:both;border-top:0px;border-right:0px;border-bottom:0px;float:none;height:auto;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:#000000;">);</code> 
     </div> 
    </div></td> 
  </tr> 
 </tbody> 
</table>

分别生成3个sql文件，in内变量的数目分别是100，1000和10000个，执行这3个sql文件，看看时间

[?][Link 1]

可以看到只有在in内数据到了10，000个的时候数据时间会有比较大的变化，但也不过是在300多ms内完成。

那如果按照有些回答那样，先建一个临时表，然后用in subquery，并且希望这时候可以两表join呢？为了简单我直接用两表join了

[?][Link 1]

<table style="border-spacing:0px;border:1px solid rgb(204,204,204);margin-top:.54em;text-align:center;width:660px;clear:both;background:none;float:none;height:auto;line-height:1.1em;margin-right:0px;margin-bottom:0px;margin-left:0px;padding:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-size:1em;min-height:auto;"> 
 <tbody style="background:none;float:none;height:auto;line-height:1.1em;margin:0px;padding:0px;text-align:left;vertical-align:baseline;width:auto;font-size:1em;min-height:auto;"> 
  <tr style="background:none;border-top:0px;float:none;height:auto;line-height:1.1em;margin:0px;padding:0px;vertical-align:baseline;width:auto;font-size:1em;min-height:auto;"> 
   <td style="padding:.2em .46em;border-color:rgb(204,204,204);margin:0px;background:none;float:none;height:auto;line-height:1.1em;vertical-align:baseline;width:auto;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-size:1em;min-height:auto;color:rgb(175,175,175);"> 
    <div style="line-height:15.4px;margin:0px;padding:0px .5em 0px 1em;background-image:none;border-width:0px 3px 0px 0px;border-right-style:solid;border-right-color:rgb(108,226,108);float:none;height:auto;text-align:right;vertical-align:baseline;width:auto;min-height:auto;white-space:pre;">
      1 
    </div> 
    <div style="line-height:15.4px;margin:0px;padding:0px .5em 0px 1em;background-image:none;border-width:0px 3px 0px 0px;border-right-style:solid;border-right-color:rgb(108,226,108);float:none;height:auto;text-align:right;vertical-align:baseline;width:auto;min-height:auto;white-space:pre;">
      2 
    </div> 
    <div style="line-height:15.4px;margin:0px;padding:0px .5em 0px 1em;background-image:none;border-width:0px 3px 0px 0px;border-right-style:solid;border-right-color:rgb(108,226,108);float:none;height:auto;text-align:right;vertical-align:baseline;width:auto;min-height:auto;white-space:pre;">
      3 
    </div> 
    <div style="line-height:15.4px;margin:0px;padding:0px .5em 0px 1em;background-image:none;border-width:0px 3px 0px 0px;border-right-style:solid;border-right-color:rgb(108,226,108);float:none;height:auto;text-align:right;vertical-align:baseline;width:auto;min-height:auto;white-space:pre;">
      4 
    </div> 
    <div style="line-height:15.4px;margin:0px;padding:0px .5em 0px 1em;background-image:none;border-width:0px 3px 0px 0px;border-right-style:solid;border-right-color:rgb(108,226,108);float:none;height:auto;text-align:right;vertical-align:baseline;width:auto;min-height:auto;white-space:pre;">
      5 
    </div> 
    <div style="line-height:15.4px;margin:0px;padding:0px .5em 0px 1em;background-image:none;border-width:0px 3px 0px 0px;border-right-style:solid;border-right-color:rgb(108,226,108);float:none;height:auto;text-align:right;vertical-align:baseline;width:auto;min-height:auto;white-space:pre;">
      6 
    </div> 
    <div style="line-height:15.4px;margin:0px;padding:0px .5em 0px 1em;background-image:none;border-width:0px 3px 0px 0px;border-right-style:solid;border-right-color:rgb(108,226,108);float:none;height:auto;text-align:right;vertical-align:baseline;width:auto;min-height:auto;white-space:pre;">
      7 
    </div> 
    <div style="line-height:15.4px;margin:0px;padding:0px .5em 0px 1em;background-image:none;border-width:0px 3px 0px 0px;border-right-style:solid;border-right-color:rgb(108,226,108);float:none;height:auto;text-align:right;vertical-align:baseline;width:auto;min-height:auto;white-space:pre;">
      8 
    </div> 
    <div style="line-height:15.4px;margin:0px;padding:0px .5em 0px 1em;background-image:none;border-width:0px 3px 0px 0px;border-right-style:solid;border-right-color:rgb(108,226,108);float:none;height:auto;text-align:right;vertical-align:baseline;width:auto;min-height:auto;white-space:pre;">
      9 
    </div> 
    <div style="line-height:15.4px;margin:0px;padding:0px .5em 0px 1em;background-image:none;border-width:0px 3px 0px 0px;border-right-style:solid;border-right-color:rgb(108,226,108);float:none;height:auto;text-align:right;vertical-align:baseline;width:auto;min-height:auto;white-space:pre;">
      10 
    </div></td> 
   <td style="padding:.2em .46em;border-color:rgb(204,204,204);width:619px;margin:0px;background:none;float:none;height:auto;line-height:1.1em;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-size:1em;min-height:auto;"> 
    <div style="line-height:15.4px;margin:0px;padding:0px;background:none;border:0px;float:none;height:auto;vertical-align:baseline;width:auto;min-height:auto;"> 
     <div style="line-height:15.4px;margin:0px;padding:0px 1em;background-image:none;border:0px;float:none;height:auto;vertical-align:baseline;width:auto;min-height:auto;white-space:pre;"> 
      <code style="margin:3px auto 0px;padding:0px 0px 0px 5px;background:rgb(255,255,255);border-left:3px solid rgb(108,226,108);width:640px;font-size:14px;clear:both;font-weight:bold;border-top:0px;border-right:0px;border-bottom:0px;float:none;height:auto;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:rgb(0,102,153);">drop</code> 
      <code style="margin:3px auto 0px;padding:0px 0px 0px 5px;background:rgb(255,255,255);border-left:3px solid rgb(108,226,108);width:640px;font-size:14px;clear:both;font-weight:bold;border-top:0px;border-right:0px;border-bottom:0px;float:none;height:auto;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:rgb(0,102,153);">table</code> 
      <code style="margin:3px auto 0px;padding:0px 0px 0px 5px;background:rgb(255,255,255);border-left:3px solid rgb(108,226,108);width:640px;font-size:14px;clear:both;border-top:0px;border-right:0px;border-bottom:0px;float:none;height:auto;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:#000000;">t_tmp;</code> 
     </div> 
     <div style="line-height:15.4px;margin:0px;padding:0px 1em;background-image:none;border:0px;float:none;height:auto;vertical-align:baseline;width:auto;min-height:auto;white-space:pre;"> 
      <code style="margin:3px auto 0px;padding:0px 0px 0px 5px;background:rgb(255,255,255);border-left:3px solid rgb(108,226,108);width:640px;font-size:14px;clear:both;font-weight:bold;border-top:0px;border-right:0px;border-bottom:0px;float:none;height:auto;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:rgb(0,102,153);">create</code> 
      <code style="margin:3px auto 0px;padding:0px 0px 0px 5px;background:rgb(255,255,255);border-left:3px solid rgb(108,226,108);width:640px;font-size:14px;clear:both;font-weight:bold;border-top:0px;border-right:0px;border-bottom:0px;float:none;height:auto;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:rgb(0,102,153);">table</code> 
      <code style="margin:3px auto 0px;padding:0px 0px 0px 5px;background:rgb(255,255,255);border-left:3px solid rgb(108,226,108);width:640px;font-size:14px;clear:both;border-top:0px;border-right:0px;border-bottom:0px;float:none;height:auto;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:#000000;">t_tmp(id </code> 
      <code style="margin:3px auto 0px;padding:0px 0px 0px 5px;background:rgb(255,255,255);border-left:3px solid rgb(108,226,108);width:640px;font-size:14px;clear:both;font-weight:bold;border-top:0px;border-right:0px;border-bottom:0px;float:none;height:auto;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:rgb(0,102,153);">int</code> 
      <code style="margin:3px auto 0px;padding:0px 0px 0px 5px;background:rgb(255,255,255);border-left:3px solid rgb(108,226,108);width:640px;font-size:14px;clear:both;border-top:0px;border-right:0px;border-bottom:0px;float:none;height:auto;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:#000000;">);</code> 
     </div> 
     <div style="line-height:15.4px;margin:0px;padding:0px 1em;background-image:none;border:0px;float:none;height:auto;vertical-align:baseline;width:auto;min-height:auto;white-space:pre;"> 
      <code style="margin:3px auto 0px;padding:0px 0px 0px 5px;background:rgb(255,255,255);border-left:3px solid rgb(108,226,108);width:640px;font-size:14px;clear:both;font-weight:bold;border-top:0px;border-right:0px;border-bottom:0px;float:none;height:auto;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:rgb(0,102,153);">insert</code> 
      <code style="margin:3px auto 0px;padding:0px 0px 0px 5px;background:rgb(255,255,255);border-left:3px solid rgb(108,226,108);width:640px;font-size:14px;clear:both;font-weight:bold;border-top:0px;border-right:0px;border-bottom:0px;float:none;height:auto;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:rgb(0,102,153);">into</code> 
      <code style="margin:3px auto 0px;padding:0px 0px 0px 5px;background:rgb(255,255,255);border-left:3px solid rgb(108,226,108);width:640px;font-size:14px;clear:both;border-top:0px;border-right:0px;border-bottom:0px;float:none;height:auto;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:#000000;">t_tmp (id) </code> 
      <code style="margin:3px auto 0px;padding:0px 0px 0px 5px;background:rgb(255,255,255);border-left:3px solid rgb(108,226,108);width:640px;font-size:14px;clear:both;font-weight:bold;border-top:0px;border-right:0px;border-bottom:0px;float:none;height:auto;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:rgb(0,102,153);">values</code> 
     </div> 
     <div style="line-height:15.4px;margin:0px;padding:0px 1em;background-image:none;border:0px;float:none;height:auto;vertical-align:baseline;width:auto;min-height:auto;white-space:pre;"> 
      <code style="margin:3px auto 0px;padding:0px 0px 0px 5px;background:rgb(255,255,255);border-left:3px solid rgb(108,226,108);width:640px;font-size:14px;clear:both;border-top:0px;border-right:0px;border-bottom:0px;float:none;height:auto;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:#000000;">(494613),</code> 
     </div> 
     <div style="line-height:15.4px;margin:0px;padding:0px 1em;background-image:none;border:0px;float:none;height:auto;vertical-align:baseline;width:auto;min-height:auto;white-space:pre;"> 
      <code style="margin:3px auto 0px;padding:0px 0px 0px 5px;background:rgb(255,255,255);border-left:3px solid rgb(108,226,108);width:640px;font-size:14px;clear:both;border-top:0px;border-right:0px;border-bottom:0px;float:none;height:auto;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:#000000;">(575087),</code> 
     </div> 
     <div style="line-height:15.4px;margin:0px;padding:0px 1em;background-image:none;border:0px;float:none;height:auto;vertical-align:baseline;width:auto;min-height:auto;white-space:pre;"> 
      <code style="margin:3px auto 0px;padding:0px 0px 0px 5px;background:rgb(255,255,255);border-left:3px solid rgb(108,226,108);width:640px;font-size:14px;clear:both;border-top:0px;border-right:0px;border-bottom:0px;float:none;height:auto;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:#000000;">(363588),</code> 
     </div> 
     <div style="line-height:15.4px;margin:0px;padding:0px 1em;background-image:none;border:0px;float:none;height:auto;vertical-align:baseline;width:auto;min-height:auto;white-space:pre;"> 
      <code style="margin:3px auto 0px;padding:0px 0px 0px 5px;background:rgb(255,255,255);border-left:3px solid rgb(108,226,108);width:640px;font-size:14px;clear:both;border-top:0px;border-right:0px;border-bottom:0px;float:none;height:auto;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:#000000;">(345980),...</code> 
     </div> 
     <div style="line-height:15.4px;margin:0px;padding:0px 1em;background-image:none;border:0px;float:none;height:auto;vertical-align:baseline;width:auto;min-height:auto;white-space:pre;"> 
      <code style="margin:3px auto 0px;padding:0px 0px 0px 5px;background:rgb(255,255,255);border-left:3px solid rgb(108,226,108);width:640px;font-size:14px;clear:both;border-top:0px;border-right:0px;border-bottom:0px;float:none;height:auto;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:#000000;">(1);</code> 
     </div> 
     <div style="line-height:15.4px;margin:0px;padding:0px 1em;background-image:none;border:0px;float:none;height:auto;vertical-align:baseline;width:auto;min-height:auto;white-space:pre;"> 
      <code style="margin:3px auto 0px;padding:0px 0px 0px 5px;background:rgb(255,255,255);border-left:3px solid rgb(108,226,108);width:640px;font-size:14px;clear:both;font-weight:bold;border-top:0px;border-right:0px;border-bottom:0px;float:none;height:auto;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:rgb(0,102,153);">select</code> 
      <code style="margin:3px auto 0px;padding:0px 0px 0px 5px;background:rgb(255,255,255);border-left:3px solid rgb(108,226,108);width:640px;font-size:14px;clear:both;border-top:0px;border-right:0px;border-bottom:0px;float:none;height:auto;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:#000000;">t9.* </code> 
      <code style="margin:3px auto 0px;padding:0px 0px 0px 5px;background:rgb(255,255,255);border-left:3px solid rgb(108,226,108);width:640px;font-size:14px;clear:both;font-weight:bold;border-top:0px;border-right:0px;border-bottom:0px;float:none;height:auto;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:rgb(0,102,153);">from</code> 
      <code style="margin:3px auto 0px;padding:0px 0px 0px 5px;background:rgb(255,255,255);border-left:3px solid rgb(108,226,108);width:640px;font-size:14px;clear:both;border-top:0px;border-right:0px;border-bottom:0px;float:none;height:auto;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:#000000;">t9, t_tmp</code> 
     </div> 
     <div style="line-height:15.4px;margin:0px;padding:0px 1em;background-image:none;border:0px;float:none;height:auto;vertical-align:baseline;width:auto;min-height:auto;white-space:pre;"> 
      <code style="margin:3px auto 0px;padding:0px 0px 0px 5px;background:rgb(255,255,255);border-left:3px solid rgb(108,226,108);width:640px;font-size:14px;clear:both;font-weight:bold;border-top:0px;border-right:0px;border-bottom:0px;float:none;height:auto;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:rgb(0,102,153);">where</code> 
      <code style="margin:3px auto 0px;padding:0px 0px 0px 5px;background:rgb(255,255,255);border-left:3px solid rgb(108,226,108);width:640px;font-size:14px;clear:both;border-top:0px;border-right:0px;border-bottom:0px;float:none;height:auto;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:#000000;">t9.c1 = t_tmp.id;</code> 
     </div> 
    </div></td> 
  </tr> 
 </tbody> 
</table>

时间如何呢？

[?][Link 1]

除去drop和create的时间，依然花费了500+的时间，这里的前提还是我用的ssd盘，所以写LOG的时间会快很多。为什么会这么慢呢？用explain看一下，这时候数据量较大，直接走Merge join 了

那1000行数据的效率如何呢？

[?][Link 1]

100行的数据如下：

[?][Link 1]

可以看到在100个值和1000个值的情况下create table的方式不会比直接在in里面写所有的变量好多少，explain看的话是在用NLJ了。但在数据量更大（按照原问题，这里in的数量其实无法预知）的情况下效率只会更低，再加上额外的表维护成本和多余的SQL语句，DBA肯定不喜欢的，还是相信数据库，放心大胆直接用in list来搞定这些问题吧。

以上内容是针对select in 在postgresql的效率问题，希望对大家有所帮助！

转自: http://www.jb51.net/article/81980.htm

[Link 1]: http://www.jb51.net/article/81980.htm#