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

2023-11-30 11:15| 来源: 网络整理| 查看: 265

文章目录一、前言二、本地配置以及生成测试数据1、本地mysql配置（1）mysql版本（2）本地sql长度限制 2、创建表和测试数据（50W）（1）创建表语句（2）循环插入50W条数据 3、测试in查询是否用到了索引三、开始测试1、查询顺序字符串2、打乱查询字符串比较3、创建临时表测试（1）创建临时表（2）插入数据，进行测试（3）临时表的耗时展示四、总结1、in查询数据量多少会性能下降？2、数据量多少创建临时表比较好3、提升性能的建议

一、前言

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

论坛链接：https://bbs.csdn.net/topics/392474752 二、本地配置以及生成测试数据

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

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条数据 $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

【本文地址】

公司简介

联系我们