likes
comments
collection
share

mysql的in查询参数限制，多少数据量会造成性能下降？什么时候创建临时表合适？

作者站长头像
站长
· 阅读数 8

文章目录

一、前言

      如题所示，博主对这个问题一直模模糊糊的，网上也没一个具体的测试，都是零零散散的提一下，缺少系统的测试。前两天看到csdn的论坛，看到两个大版主，有的说超过300性能会下降，有的说超过999性能会下降？？？行吧，咱们不如自己测测。

论坛链接：https://bbs.csdn.net/topics/392474752

二、本地配置以及生成测试数据

测试前首先是明确博主这边的各种环境，按照环境来测是最准的。

1、本地mysql配置

（1）mysql版本
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.5.53    |
+-----------+
1 row in set (0.00 sec)
（2）本地sql长度限制
mysql> show variables like '%max_allowed_packet%';
+--------------------------+------------+
| Variable_name            | Value      |
+--------------------------+------------+
| max_allowed_packet       | 52428800   |
| slave_max_allowed_packet | 1073741824 |
+--------------------------+------------+
2 rows in set (0.00 sec)

      in查询的数量多少，和sql长度有关的，我们本地sql长度长一些，这样in查询数量多的时候也不至于报错。博主本地sql限制长度是50M，一次插5W条数据没问题，相应的in查询5W也不是问题哈哈。

mysql批量插入数据，一次插入多少行数据效率最高？

2、创建表和测试数据（50W）

（1）创建表语句
CREATE TABLE `student` (
  `id` int(10) NOT NULL AUTO_INCREMENT COMMENT 'id主键',
  `sno` int(10) NOT NULL DEFAULT '0' COMMENT '学号',
  `name` varchar(20) NOT NULL DEFAULT '' COMMENT '姓名',
  `age` int(10) NOT NULL DEFAULT '1' COMMENT '年龄',
  `test_id` int(10) NOT NULL DEFAULT '0' COMMENT '和id一样的值，方便测试',
  PRIMARY KEY (`id`),
  UNIQUE KEY `sno` (`sno`),
  KEY `test_id` (`test_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='学生表(test)'

      这里的test_id表的数据和id的值是一样的，方便咱们测试。索引什么的都建好，50W数据也不少了，可以用到索引的。

（2）循环插入50W条数据
<?php
$link = mysqli_connect('localhost','root','root');
if(!$link){
  exit('mysql数据链接失败');
}
mysqli_select_db($link,'test');
mysqli_set_charset($link,'utf-8');
// 循环插50W条，每次插入5W条数据
for($i=0;$i<10;$i++){
  $sqls = "";
  //接下来循环拼接sql到下面
  $j = 50000*$i+1;
  $k = $j+50000;
  for($j;$j<$k;$j++){
      $sno = intval('2019'.sprintf("%06d", $j)); //学号是前面补0
      $name = 'name_'.$j;
      $age = 20;
      $test_id = $j;
      $sqls .= "({$sno},'{$name}',{$age},{$test_id})".",";
  }
  $sqls = substr($sqls,0,-1);
  $sql = "INSERT INTO `student` (`sno`,`name`,`age`,`test_id`) VALUES {$sqls}";
  $rs = mysqli_query($link,$sql);
  if(! $rs ) {
    die('无法插入数据: ' . mysqli_error($link));
  }else{
    echo "数据插入成功\n：返回值是:".$rs;
  }
  unset($sqls);
  unset($sql);
  unset($rs);
}

mysqli_close($link);

3、测试in查询是否用到了索引

mysql> explain select * from student where 1 and test_id in (100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118,119,120,121,122,123,124,125,126,127,128,129,130,131,132,133,134,135,136,137,138,139,140,141,142);
+----+-------------+---------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table   | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+---------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | student | range | test_id       | test_id | 4       | NULL |   43 | Using where |
+----+-------------+---------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)

      这里看是能用到索引的，如果大家对这块有疑问可以参考我的其他博客：mysql的in查询是可以用到索引吗？亲测详解

三、开始测试

      这里的测试主要分为三部分，主要是有顺序的in查询字符串，和无顺序的查询字符串。以及in查询与创建临时表的性能比较。

代码大致如下：

function test_in()
{
  $start = rand(1, 5000) * 100;
  $nums = 100;
//开始测试
  $link = mysqlTest();
//获取要查询的随机数
  $strs_arr = randomStr($nums, $start, true);
  $str = "(" . implode(',', $strs_arr) . ")";
//调用获取毫秒时间的函数，t1
  $sql = "select * from student where 1 and test_id in {$str}";
//var_dump($sql);exit;
  $t1 = msectime();
  $rs = mysqli_query($link, $sql);
  $t2 = msectime();
  $diff = $t2 - $t1;
  if (!$rs) {
    die('查询失败: ' . mysqli_error($link));
  } else {
    $arr = mysqli_fetch_all($rs);
    echo "数据查询成功：查询数据量是:" . count($arr) . "\n";
  }
  echo "查询开始值是:" . $start . "查询数量是" . $nums . "耗费时间是:" . $diff . "毫秒";
  mysqli_close($link);
}

1、查询顺序字符串

这里的结果都是博主查询10次，求的平均值大小，大概是如下：

查询开始值是:32800查询数量是100耗费时间是:7毫秒
查询开始值是:44300查询数量是100耗费时间是:5毫秒
查询开始值是:33300查询数量是100耗费时间是:6毫秒
查询开始值是:49300查询数量是100耗费时间是:4毫秒

下面是正式结果：

100条平均“5ms”
200条平均：10.2ms
299条平均：16.4ms
400条平均：19.4ms
600条平均：21.67ms
800条平均：30.8ms
999条平均：32.8ms
1200条平均：39.2ms
3000条平均：67ms
5000条平均：101.5ms
10000条平均：189ms
20000条平均：336ms

      其实这里可以看到，in查询中的数量增加，性能下降并没有想象中那么可怕。这也是mysql5.5之后优化了in查询的结果。

2、打乱查询字符串比较

100条平均：7.5ms
300条平均：13ms
600条平均：23.8ms
999条平均：31ms
1200条平均：39ms
3000条平均：76ms
10000条平均：199ms

      这里对比上面的顺序查询，可以看出来性能是有细微差异的。也是，我们顺序查询的时候，经过索引会进行顺序IO，消耗的性能会少一些。不过我们平时进行In查询的数据基本都是散乱的，所以这个还是比较有参考意义的。

3、创建临时表测试

这块主要是要创建表，写入数据，通过inner join进行连接查询等。下面列出大致步骤和代码。

（1）创建临时表
function createTem($link,$table_name)
{
  $sql= "create temporary table {$table_name} (
  id int(10) NOT NULL AUTO_INCREMENT,
  test_id int(10) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `test_id` (`test_id`)
) ENGINE=MEMORY DEFAULT CHARSET=utf8";
  $rs = mysqli_query($link,$sql);
  if(!$rs){
    die('创建临时表失败: ' . mysqli_error($link));
  }
}
（2）插入数据，进行测试
function test_create_tem()
{
  $start = rand(1, 5000) * 100;
  $nums = 20000;
//开始测试
  $link = mysqlTest();
  $table_name = "test_tem";
//获取要查询的随机数
  $strs_arr = randomStr($nums, $start, true);
  //$str = "(" . implode(',', $strs_arr) . ")";
  $sqls = "";
  foreach($strs_arr as $v){
      $sqls .= "({$v})".",";
  }
  $sqls = substr($sqls,0,-1);
//调用获取毫秒时间的函数，t1
  $t1 = msectime();
  //创建临时表
  createTem($link,$table_name);
  //插入数据到临时表
  $insert_sql = "insert into `{$table_name}` (test_id) VALUES {$sqls}";
  $rs1 = mysqli_query($link, $insert_sql);
  if($rs1){
    $sql = "select a.* from student as a join {$table_name} as b on a.test_id=b.test_id";
    $rs = mysqli_query($link, $sql);
  }
  $t2 = msectime();
  $diff = $t2 - $t1;
  if (!$rs) {
    die('查询失败: ' . mysqli_error($link));
  } else {
    $arr = mysqli_fetch_all($rs);
    echo "数据查询成功：查询数据量是:" . count($arr) . "\n";
  }
  echo  "数据查询成功：查询数据量是:" . count($arr)."连表耗费时间是:" . $diff . "毫秒";
  mysqli_close($link);
}

打印结果形如如下：

数据查询成功：查询数据量是:20000连表耗费时间是:138毫秒
xxxxx
（3）临时表的耗时展示
300条数据:13ms
600条数据:17ms
999条数据:22.6ms
1500条数据：25ms
3000条数据：34.4ms
5000条数据：55ms
10000条数据：89ms
20000条数据：124ms

这部分我们没有测试较少数据的查询，因为小数据的查询，耗时肯定是比in查询长的。

这里能看出来：

1）当数据量大于300的时候,临时表的查询性能一直是优于in查询的。
2）随着数据量的增加，临时表的性能越来越出色

四、总结

      这个测试说实话，对博主来说也是推翻了一些东西，在以前博主总是不愿意建临时表的，总觉得麻烦，创建表还得插入数据，这速度能快嘛，，结果打脸了。

1、in查询数据量多少会性能下降？

答： 根据测试来说，in查询的数据量在1W以内都是可以接受的，这里没看到有性能剧变，猜测是mysql5.5之后的版本优化原因。就像in查询使用索引一样，网上很多资料都说用不到索引，但是实际上5.5之后都是可以用到的。不能说前辈们总结的是错的，只能说mysql一直在发展进步。

2、数据量多少创建临时表比较好

答： 通过测试我们可以看到，数据量大于300建临时表都是不错的选择。这块是仁者见仁智者见智了，如果in查询的条件，其他部分也有用到的话，建临时表是最好的，可以多次使用。如果你只是想一锤子买卖，那么博主觉得1000条以内直接用in查询就好了。

3、提升性能的建议

答： 博主这里只是大致测试下，代码写的勉强能用，测试精度也有限。如果想要继续提升性能的话，建议是创建临时表的时候选用内存表，性能还能提升30%左右。其他部分能提升的不多了，这里的in查询已经用到了索引。。。

      以上就是关于in查询的总结了，博主耗费了一天周末时间测试，不过结果是好的，最起码搞懂了一些东西，下次碰到类似的情况也知道该如何选择了哈哈，一起加油！

需要源码的，评论留邮箱，博主可以把测试demo发过去。

end