多表查询与子查询的性能比较

发表于 | 更新于: | 分类于 | | 字数: 2.4k

使用MySQL官方提供的测试数据库来测试多表关联查询与子查询的性能到底谁更胜一筹


写在前面

在程序员的世界里，从来都不缺争论，从世界上最好的语言到世界上最好的IDE再到世界上最好的编辑器，这场没有硝烟的战争从未停止过，当然我自己从来都不会参与其中，也许我是个假程序员，在我看来，最好的东西，从来都不存在，没有最好只有最适合。咳咳~~~好像都点跑题了，是这样，最近跟朋友吃饭的时候，聊到了关于MySQL数据库的多表查询与子查询的性能问题，我的朋友认为多表查询的性能要优于子查询的性能，而我自己认为子查询的性能要优于多表查询，大家都各执一词，谁也没能说服谁，与其这样争论不休、倒不如让数据说话，使用模拟数据进行测试，到底多表查询与子查询谁的性能谁更胜一筹。

导入SQL测试数据

官方文档：https://dev.mysql.com/doc/employee/en/
测试数据库github地址：https://github.com/datacharmer/test_db

1、安装

测试数据使用的是MySQL官方提供的测试数据库，此数据库包含了大约30万的员工数据，280多万条记录，当然所有的数据都是测试数据并非真实数据。数据量并不算多，但用于测试应该是足够了。导入数据也比较简单，首先在github上将test_db-master下载到本地，解压，切换到test_db-master目录，运行mysql -u yourusername -p -t < employees.sql，回车，输入密码即可，类似于下图

test_db

图片并没有截全，完整的信息如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
+-----------------------------+
| INFO                        |
+-----------------------------+
| CREATING DATABASE STRUCTURE |
+-----------------------------+
+------------------------+
| INFO                   |
+------------------------+
| storage engine: InnoDB |
+------------------------+
+---------------------+
| INFO                |
+---------------------+
| LOADING departments |
+---------------------+
+-------------------+
| INFO              |
+-------------------+
| LOADING employees |
+-------------------+
+------------------+
| INFO             |
+------------------+
| LOADING dept_emp |
+------------------+
+----------------------+
| INFO                 |
+----------------------+
| LOADING dept_manager |
+----------------------+
+----------------+
| INFO           |
+----------------+
| LOADING titles |
+----------------+
+------------------+
| INFO             |
+------------------+
| LOADING salaries |
+------------------+
+---------------------+
| data_load_time_diff |
+---------------------+
| 00:01:03            |
+---------------------+

2、测试安装

测试命令如下:

两种测试安装的方法
1
2
time mysql -u yourusername -p -t < test_employees_sha.sql
time mysql -u yourusername -p -t < test_employees_md5.sql

以test_employees_sha.sql为例，执行time mysql -u mackvord -p -t < test_employees_sha.sql，显示的信息与官方提供的信息吻合则表示安装没有问题。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
+----------------------+
| INFO                 |
+----------------------+
| TESTING INSTALLATION |
+----------------------+
+--------------+------------------+------------------------------------------+
| table_name   | expected_records | expected_crc                             |
+--------------+------------------+------------------------------------------+
| employees    |           300024 | 4d4aa689914d8fd41db7e45c2168e7dcb9697359 |
| departments  |                9 | 4b315afa0e35ca6649df897b958345bcb3d2b764 |
| dept_manager |               24 | 9687a7d6f93ca8847388a42a6d8d93982a841c6c |
| dept_emp     |           331603 | d95ab9fe07df0865f592574b3b33b9c741d9fd1b |
| titles       |           443308 | d12d5f746b88f07e69b9e36675b6067abb01b60e |
| salaries     |          2844047 | b5a1785c27d75e33a4173aaa22ccf41ebd7d4a9f |
+--------------+------------------+------------------------------------------+
+--------------+------------------+------------------------------------------+
| table_name   | found_records    | found_crc                                |
+--------------+------------------+------------------------------------------+
| employees    |           300024 | 4d4aa689914d8fd41db7e45c2168e7dcb9697359 |
| departments  |                9 | 4b315afa0e35ca6649df897b958345bcb3d2b764 |
| dept_manager |               24 | 9687a7d6f93ca8847388a42a6d8d93982a841c6c |
| dept_emp     |           331603 | d95ab9fe07df0865f592574b3b33b9c741d9fd1b |
| titles       |           443308 | d12d5f746b88f07e69b9e36675b6067abb01b60e |
| salaries     |          2844047 | b5a1785c27d75e33a4173aaa22ccf41ebd7d4a9f |
+--------------+------------------+------------------------------------------+
+--------------+---------------+-----------+
| table_name   | records_match | crc_match |
+--------------+---------------+-----------+
| employees    | OK            | ok        |
| departments  | OK            | ok        |
| dept_manager | OK            | ok        |
| dept_emp     | OK            | ok        |
| titles       | OK            | ok        |
| salaries     | OK            | ok        |
+--------------+---------------+-----------+
+------------------+
| computation_time |
+------------------+
| 00:00:10         |
+------------------+
+---------+--------+
| summary | result |
+---------+--------+
| CRC     | OK     |
| count   | OK     |
+---------+--------+

real	0m13.565s
user	0m0.003s
sys	0m0.003s

在shell中进入MySQL模式，查看employees数据库是否安装成功:

employees

查看数据库的基本信息:

information

查看employees表中所有的字段:

1
2
3
4
5
6
7
8
9
10
11
12
mysql> select column_name from information_schema.columns where table_name = "employees";
+-------------+
| column_name |
+-------------+
| emp_no      |
| birth_date  |
| first_name  |
| last_name   |
| gender      |
| hire_date   |
+-------------+
6 rows in set (0.00 sec)

测试

在测试之前，我们先看一下employees数据库的表结构图:

structure

通过观察发现，employees表与departments表之间通过dept_emp这张表连接，那么我们通过一个简单的例子来看一下，同样的需求，使用多表关联查询与子查询所耗费的时间。需求:查询每个部门的编号、名称以及部门的人数

多表查询:

1
2
3
4
5
mysql> select d.dept_no,d.dept_name,count(e.emp_no) count
    -> from departments d
    -> left join dept_emp de on d.dept_no = de.dept_no
    -> right join employees e on e.emp_no = de.emp_no
    -> group by d.dept_no,d.dept_name;

结果:

多表查询]

子查询:

1
2
3
4
5
6
7
8
9
10
mysql> select d.dept_no,d.dept_name,temp.count
    -> from departments d
    -> left join (
    -> select de.dept_no,count(e.emp_no) count
    -> from employees e
    -> left join dept_emp de
    -> on e.emp_no = de.emp_no
    -> group by de.dept_no
    -> ) temp
    -> on d.dept_no = temp.dept_no;

结果:

子查询

以上只是执行单条查询语句所花费的时间，并没有什么说服力，所以接下来，我们换一种测试的方式。

使用mysqlslap测试

mysqlslap是mysql官方提供的一个用于模拟服务器负载的工具，使用这个工具可以模拟客户端访问服务器，并输出所花费的时间信息，下面我们就以这个工具为例，再次测试上面的sql语句的执行效果。

1、查看帮助信息，在shell中执行: mysqlslap --help 或者 mysqlslap -?

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
--print-defaults        输出参数列表
--no-defaults           除登录文件外，不读取任何选项文件的默认选项
--defaults-file=#       仅读取给定文件中的默认选项
--defaults-extra-file=# 读取全局文件后读取此文件
--defaults-group-suffix=# 读取后缀
--login-path=#          从登录文件中读取路径
  -?, --help          显示帮助信息
  -a, --auto-generate-sql 自动生成sql语句
  --auto-generate-sql-add-autoincrement 将AUTO_INCREMENT列添加到自动生成的表中
  --auto-generate-sql-execute-number=# 指定自动生成的查询数
  --auto-generate-sql-guid-primary 将基于GUID的主键添加到自动生成的表中
  --auto-generate-sql-load-type=name 指定测试负载类型：mixed，update，write，key或read; 默认是mixed
  --auto-generate-sql-secondary-indexes=# 在自动生成表时增加二级索引的个数
  --auto-generate-sql-unique-query-number=# 为自动测试生成的唯一查询数
  --auto-generate-sql-unique-write-number=# 为auto generateql-write-number生成的唯一查询数
  --auto-generate-sql-write-number=# 每个线程要执行的插入行的数量（默认值为100）
  --commit=#          指定在提交前要执行的语句的个数
  -C, --compress      压缩客户端和服务器之间发送的所有信息
  -c, --concurrency=name 指定模拟查询的客户端数量
  --create=name       用于创建数据表的sql语句或包含sql的文件
  --create-schema=name 指定运行测试的模式
  --csv[=name]        以逗号分割的格式生成输出信息
  -#, --debug[=#]     将调试信息写入到日志中
  --debug-check       程序退出时打印调试信息
  -T, --debug-info    程序退出时打印调试信息，包括内存和CPU统计信息
  --default-auth=name 指定默认的身份验证插件
  -F, --delimiter=name 指定在sql语句中使用的分隔符
  --detach=#          在指定个数的请求之后，关闭并重新打开连接
  --enable-cleartext-plugin 启用明文身份验证插件
  -e, --engine=name   使用指定的数据库引擎创建数据表
  -h, --host=name     连接的主机
  -i, --iterations=#  执行测试的次数
  --no-drop           测试完后不删除测试表
  -x, --number-char-cols=name 自动生成的测试表中包含多少个字符类型的列，默认1
  -y, --number-int-cols=name 自动生成的测试表中包含多少个数字类型的列，默认1
  --number-of-queries=# 限制执行查询的客户端数量（大约）
  --only-print        不连接数据库，只打印已执行的相关信息
  -p, --password[=name] 连接服务器的密码
  --plugin-dir=name   客户端插件的所在目录
  -P, --port=#        指定连接数据库的端口
  --post-query=name   在运行测试之后，执行查询语句或者包含查询语句的文件
  --post-system=name  运行测试之后，使用system()方法输出的字符串
  --pre-query=name    在运行测试之前，执行查询语句或者包含查询语句的文件
  --pre-system=name   在运行测试之前使用system()方法输出的字符串
  --protocol=name     指定使用的连接协议(tcp, socket, pipe,memory)
  -q, --query=name    执行查询语句或者包含查询语句的文件
  --secure-auth       如果客户端使用4.1.1版本之前的协议将被拒绝连接到服务器，已废弃，始终为true
  -s, --silent        以静默模式运行程序 - 无输出
  -S, --socket=name   用于连接的套接字文件
  --sql-mode=name     指定运行mysqlslap工具的sql模式
  --ssl-mode=name     SSL 连接模式
  --ssl               已废弃，可以使用 --ssl-mode 代替，注意此选项默认是开启的，可以使用 --skip-ssl 关闭
  --ssl-verify-server-cert 已废弃，使用 --ssl-mode=VERIFY_IDENTITY 代替
    
  --ssl-ca=name       包含受信任的SSL证书颁发机构证书文件的目录
  --ssl-capath=name   CA（受信任的证书颁发机构）目录
  --ssl-cert=name     PEM格式的X509证书
  --ssl-cipher=name   SSL密码
  --ssl-key=name      PEM格式的X.509密钥
  --ssl-crl=name      包含证书吊销列表的文件
  --ssl-crlpath=name  包含证书吊销列表文件的目录
  --tls-version=name  指定TLS协议的版本，可选值: TLSv1, TLSv1.1
  -u, --user=name     指定登录用户
  -v, --verbose       输出详细信息
  -V, --version       输出版本信息并退出

2、模拟50个客户端，500次并发连接请求

多表查询:

执行以下语句:

1
2
3
4
5
6
7
8
mysqlslap -h localhost -P 3306 --concurrency=50 --number-of-queries=500 --create-schema='employees' 
--query='
select d.dept_no,d.dept_name,count(e.emp_no) count 
from departments d 
left join dept_emp de on d.dept_no = de.dept_no 
right join employees e on e.emp_no = de.emp_no 
group by d.dept_no,d.dept_name;' 
-u mackvord -p

多表

子查询:

执行以下语句:

1
2
3
4
5
6
7
8
9
10
11
mysqlslap -h localhost -P 3306 --concurrency=50 --number-of-queries=500 --create-schema='employees' 
--query='
select d.dept_no,d.dept_name,temp.count 
from departments d left join (
  select de.dept_no,count(e.emp_no) count 
  from employees e 
  left join dept_emp de on e.emp_no = de.emp_no 
  group by de.dept_no
  ) temp 
  on d.dept_no = temp.dept_no;' 
  -u mackvord -p

子查询

通过两次执行的结果来看，多表查询用了将近5分钟，而子查询用了3分钟多一点，虽然两次查询的速度都有点慢joy，但是貌似子查询的速度要快一点？这样下结论好像太草率了，毕竟这只是查询每个部门的编号、名称以及部门的人数，对于其他的不同需求，可能编写的sql语句就不一样，那么执行的性能也会有所差异，所以还得看具体问题。OK，文章到此就告一段落了，有时间再继续补充吧。gif

如果您觉得我的文章对您有帮助，请随意赞赏，您的支持将鼓励我继续创作！
0%