MySQL每次都查询一次效率_MySQL 查询单条数据多次与一次查询多条数据的比较

一 引子

总体上来说，一次查询 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","GuangZhou","DBA"),\

-> ("robin09",19,"M