MySQL每次都查询一次效率_MySQL 每次查询一条数据查询十次与一次查询十条数据之间的区别...

一 引子

有个知友邀请我回答问题，问道「MySQL 每次查询一条数据查 10 次和一次查询 10 条数据效率有多少差距？」

总体上来说，一次查询 10 条数据效率是高于每次查询一条数据查 10 次的。但究竟差距多少，具体的数据很难说。这本来是一个很简单的问题，但我还是想亲身实践下，给以后碰到这个问题的朋友一点参考。我先做一个模拟，然后在文末给出一个分析。

说明：本文中的测试只是一个参考。实际的差距跟硬件配置、系统负载等因素相关。

二 模拟

在做模拟之前，得有数据。所以我创建了一组测试数据，如下：

mysql> USE test;

Database changed

mysql> CREATE TABLE user

-> (id INT PRIMARY KEY AUTO_INCREMENT,

-> name VARCHAR(20),

-> age INT,

-> sex CHAR(2),

-> city VARCHAR(20),

-> work VARCHAR(10)

-> ) DEFAULT CHARSET utf8 ENGINE = INNODB;

Query OK, 0 rows affected (0.10 sec)

mysql> INSERT INTO user(name, age, sex, city, work) \

-> VALUES("robin01",19,"M","GuangZhou","DBA"),\

-> ("robin02",19,"M","GuangZhou","DBA"),\

-> ("robin03",19,"M","GuangZhou","DBA"),\

-> ("robin04",19,"M","GuangZhou","DBA"),\

-> ("robin05",19,"M","GuangZhou","DBA"),\

-> ("robin06",19,"M","GuangZhou","DBA"),\

-> ("robin07",19,"M","GuangZhou","DBA"),\

-> ("robin08",19,"M","GuangZh