我在用MYSQL。
我有三张桌子。people包含两列的表：
id-表中的主键
姓名-人员姓名income包含人民表中人民收入的表。
此表中的每条记录代表一个人的收入。
在这张表中，一个人的收入可以是零，也可以是多。
表格结构为：
人员id（“人员”表的外键）
金额（十进制-金额）
金额的小时数（整数类型-赚取此收入所需的小时数）expenses包含人员费用的表。
此表中的每条记录都表示一个人的支出，
他花了多少钱买的东西。
一个人在这个表中可以有零个或多个费用记录。
表格结构为：
人员id（“人员”表的外键）
金额（十进制金额）
购买的物品数量（整数类型-此费用中购买的物品数量）
我要做的是创建一个查询，它将给我一个所有人的列表
（每人一张唱片）每排我都有
那个人的名字，
他所有收入的总和，
他工作的总小时数，
他所有开支的总和，
他买的东西的总数。
我尝试的第一个天真的方法逻辑上很好，但性能很差，
它看起来像这样：
SELECT name, income_sum, work_hours_sum, expenses_sum, items_count
FROM (people
LEFT JOIN
(SELECT person_id, sum(amount) as income_sum,
sum(number_of_hours_for_amount) as work_hours_sum
FROM income
GROUP BY person_id) as income_subquery
ON people.id = income_subquery.person_id)
LEFT JOIN
(SELECT person_id, sum(amount) as expenses_sum,
sum(number_of_items_bought) as items_count
FROM expenses
GROUP BY person_id) as income_subquery
ON people.id = income_subquery.person_id
据我所知，这个查询的问题是，一旦我从子查询中获取数据，连接就会非常低效地完成
因为索引在这些表上没有很好的使用，因为它们是临时子查询表。
充分利用现有索引的最佳方法是直接在三个表之间进行连接。
而不是通过子查询。
但这不是一个正确的解决方案，因为它将创建一个笛卡尔积，该积将向聚合求和中添加重复的值
从那些比他们应该出现的更多的记录中。
（我尝试的另一个选择是将每个人的收入和支出值计算为一个选择表达式
在选择部分（依赖子查询）。这也不够快）
我正在寻找一个有效的查询并给出这些结果。
最佳答案
你说得对，这里有一个不可避免的笛卡尔积。可以将此问题分解为两个子查询：
一个代表收入：
SELECT p.id, p.name, SUM(i.amount) AS income_sum, SUM(number_of_hours_for_amount) AS work_hours_sum
FROM people p
LEFT JOIN income i ON p.id = i.person_id
GROUP BY p.id;
+----+---------+------------+----------------+
| id | name | income_sum | work_hours_sum |
+----+---------+------------+----------------+
| 1 | Groucho | 20.00 | 20 |
| 2 | Harpo | 40.00 | 40 |
| 3 | Chico | 60.00 | 60 |
+----+---------+------------+----------------+
下面是这个问题的解释：
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+
| 1 | SIMPLE | p | ALL | PRIMARY | NULL | NULL | NULL | 3 | Using temporary; Using filesort |
| 1 | SIMPLE | i | ALL | NULL | NULL | NULL | NULL | 6 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+
费用一份：
SELECT p.id, SUM(e.amount) AS expenses_sum, SUM(number_of_items_bought) AS items_count
FROM people p
LEFT JOIN expenses e ON p.id = e.person_id
GROUP BY p.id;
+----+--------------+-------------+
| id | expenses_sum | items_count |
+----+--------------+-------------+
| 1 | 30.00 | 4 |
| 2 | 30.00 | 4 |
| 3 | 30.00 | 4 |
+----+--------------+-------------+
下面是解释：
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+
| 1 | SIMPLE | p | ALL | PRIMARY | NULL | NULL | NULL | 3 | Using temporary; Using filesort |
| 1 | SIMPLE | e | ALL | NULL | NULL | NULL | NULL | 6 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+
我们在上面的解释报告中看到，查询对收入和支出表使用表扫描（键入“ALL”），而不使用索引连接（使用连接缓冲区）。红色标志是，在一个join中有两个表都使用了访问类型“ALL”。如果这些表中的行数不多，那么代价将非常高昂它通常与“使用连接缓冲区”一起使用，这是另一个代价高昂的查询的危险标志。
最后，它通过使用临时表和文件排序来高效地进行分组。这是另一个性能杀手。
Block Nested Loop是MySQL的5.6版本。如果使用早期版本的MySQL，就看不到这一点。
以下索引应该有助于使这些查询更好：
ALTER TABLE income ADD KEY (person_id, amount, number_of_hours_for_amount);
ALTER TABLE expenses ADD KEY (person_id, amount, number_of_items_bought);
现在，EXPLAIN报告不再显示低效访问。连接是用索引（类型“ref”）完成的，临时表和文件排序都不见了“使用索引”表示它只通过索引中的列访问联接表，根本不需要接触表行。
+----+-------------+-------+-------+---------------+-----------+---------+-----------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+-----------+---------+-----------+------+-------------+
| 1 | SIMPLE | p | index | PRIMARY | PRIMARY | 4 | NULL | 3 | NULL |
| 1 | SIMPLE | i | ref | person_id | person_id | 5 | test.p.id | 1 | Using index |
+----+-------------+-------+-------+---------------+-----------+---------+-----------+------+-------------+
+----+-------------+-------+-------+---------------+-----------+---------+-----------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+-----------+---------+-----------+------+-------------+
| 1 | SIMPLE | p | index | PRIMARY | PRIMARY | 4 | NULL | 3 | NULL |
| 1 | SIMPLE | e | ref | person_id | person_id | 5 | test.p.id | 1 | Using index |
+----+-------------+-------+-------+---------------+-----------+---------+-----------+------+-------------+
你说你想在一个查询中完成这项工作，下面是如何做到的：
我们可以将这两个单独的查询合并到一个查询中，以每人获得一行结果：
SELECT name, income_sum, work_hours_sum, expenses_sum, items_count
FROM
(SELECT p.id, p.name, SUM(i.amount) AS income_sum, SUM(number_of_hours_for_amount) AS work_hours_sum
FROM people p
LEFT OUTER JOIN income i ON p.id = i.person_id
GROUP BY p.id) AS subq_i
INNER JOIN
(SELECT p.id, SUM(e.amount) AS expenses_sum, SUM(number_of_items_bought) AS items_count
FROM people p
LEFT OUTER JOIN expenses e ON p.id = e.person_id
GROUP BY p.id) AS subq_e
USING (id);
+---------+------------+----------------+--------------+-------------+
| name | income_sum | work_hours_sum | expenses_sum | items_count |
+---------+------------+----------------+--------------+-------------+
| Groucho | 20.00 | 20 | 30.00 | 4 |
| Harpo | 40.00 | 40 | 30.00 | 4 |
| Chico | 60.00 | 60 | 30.00 | 4 |
+---------+------------+----------------+--------------+-------------+
即使对于这个连接的查询，解释看起来也没有那么糟糕。没有临时表或文件队列或连接缓冲区，并且很好地使用了覆盖索引。
+----+-------------+------------+-------+---------------+-------------+---------+-----------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+-------+---------------+-------------+---------+-----------+------+-------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 3 | NULL |
| 1 | PRIMARY | <derived3> | ref | <auto_key0> | <auto_key0> | 4 | subq_i.id | 2 | NULL |
| 3 | DERIVED | p | index | PRIMARY | PRIMARY | 4 | NULL | 3 | Using index |
| 3 | DERIVED | e | ref | person_id | person_id | 5 | test.p.id | 1 | Using index |
| 2 | DERIVED | p | index | PRIMARY | PRIMARY | 4 | NULL | 3 | NULL |
| 2 | DERIVED | i | ref | person_id | person_id | 5 | test.p.id | 1 | Using index |
+----+-------------+------------+-------+---------------+-------------+---------+-----------+------+-------------+