如何优化大表的计数SQL查询

Question

我有一个MySQL（InnoDB）上的大表，其中包含产品资产（1300万行）。以下是我的数据库的简要架构：

product <-many2one-- file_item --one2many--> family --many2one--> download_type

*file_item*表是一张有数百万行的大表。我尝试使用以下SQL查询按下载类型计算产品数量：

select t.name as type, 
count(p.product_id) as n 
from file_item p 
inner join family f on f.id = p.family_id 
inner join type t on f.id_type = t.id 
group by t.id order by t.name;

*file_item*表中有3个索引：

• product_family_idx（product_id，family_id）
• family_idx（family_id）
• product_idx（product_id） 解释输出：

+----+-------------+-------+--------+-----------------------------------+---------+---------+-------------------+----------+---------------------------------+
| id | select_type | table | type   | possible_keys                     | key     | key_len | ref               | rows     | Extra                           |
| 1  | SIMPLE      | p     | ALL    | FAMILY_IDX,PRODUCT_FAMILY_IDX     | NULL    | NULL    | NULL              | 13862870 | Using temporary; Using filesort |
| 1  | SIMPLE      | f     | eq_ref | PRIMARY,TYPE_ID                   | PRIMARY | 4       | MEDIA.p.FAMILY_IDX| 1        |                                 |
| 1  | SIMPLE      | t     | eq_ref | PRIMARY                           | PRIMARY | 4       | MEDIA.f.TYPE_ID   | 1        |                                 |
+----+-------------+-------+--------+-----------------------------------+---------+---------+-------------------+----------+---------------------------------+

查询花费超过1小时才返回结果。请问如何优化查询？！

- juliusdev

---

表格是否建立了良好索引？请提供包含索引和该查询的EXPLAIN的模式。谢谢！ - Wiseguy

请提供 "explain <yourquery>;" 的输出结果。 - Spike Gronim

以下是一个简单的找到瓶颈的方法：逐个查询片段地运行查询。只需运行 "select count() from file_item" 并确保它能快速返回。然后再添加 family join，然后是 type join，接着是 group by，最后是 order by。找出导致减慢速度的部分，然后从那里开始解决问题。 - Derek Kromm

如果t.name上没有索引，我认为ORDER BY语句会降低查询性能。如果您仅删除ORDER BY，速度会更快吗？ - Wiseguy

请确保不仅查看您的file_item表。 "familiy"可能需要包括"id_type"以帮助连接，而"type"可能需要在"name"上建立索引以帮助排序。（或者不需要...正如@Derek所说，查看各个部分及其执行计划） - alun

Answer 1

以下是您的原始查询：

select t.name as type,  
count(p.product_id) as n  
from file_item p  
inner join family f on f.id = p.family_id  
inner join type t on f.id_type = t.id  
group by t.id order by t.name; 

你需要进行两个重大改变：

重大改变1：重构查询。

SELECT A.ProductCount,B.name type
FROM
(
    SELECT id_type id,COUNT(1) ProductCount
    FROM
    (
        SELECT p.id_type
        FROM (SELECT family_id,id_type FROM file_item) p
        INNER JOIN (SELECT id FROM family) f on f.id = p.family_id
    ) AA
    GROUP BY id_type
) A
INNER JOIN type B USING (id)
ORDER BY B.name;

主要变更 #2：创建索引以支持重构后的查询

ALTER TABLE file_item ADD INDEX family_type_idx (family_id,id_type);

尝试一下吧！！！

- RolandoMySQLDBA

---

你能解释一下为什么要进行这个重构吗？ - Arnaud Le Blanc

在您的查询中，GROUP BY和ORDER BY子句会在所有连接之后进行评估。窍门是要做两件事：1）强制查询使用仅需要的键具有较小的临时表，2）最后执行JOINs。我从这个视频中学到了这个技术：http://youtu.be/ZVisY-fEoMw。我使用这种技术回答了StackOverflow中涉及成千上万行的另一个非常复杂的问题：https://dev59.com/02025IYBdhLWcg3weV4s#6023217 - RolandoMySQLDBA

Answer 2

让我们将查询分解成几个部分：

1. 首先，获取file_item的每一行 => 13M行
2. 对于每一行返回的结果，获取与f.id = p.family_id匹配的family的一行。=> 13M次获取，13M行
3. 对于每一行返回的结果，获取与f.id_type = t.id匹配的type的一行。=> 13M次获取，13M行
4. 按type.id进行分组 => 10行
5. 按type.name排序 => 10行需要排序

正如您所看到的，您的查询需要从family和type中获取13M行。

您应该开始减少执行查询所需的行获取数量：

假设f.id_type是非空外键，您可以将inner join type t更改为left join type t。然后，将group by t.id更改为group by f.id_type。

将对t表的内连接更改为左连接，并在f表上进行分组，使MySQL能够在从t表中获取行之前执行group by操作。

group by可大大减少行数，因此它也可以大大减少从t中获取的次数：

1. 首先，获取每个file_item行=>13M行
2. 对于每个返回的行，获取一个与f.id=p.family_id匹配的family行。=>13M次获取，13M行
3. 按照type.id进行分组=>10行
4. 对于每个返回的行，获取一个与f.id_type=t.id匹配的type行。=>10次获取，10行
5. 按照type.name进行排序=> 10行需要排序

结果是查询已经减少了1300万行的获取。

您可以通过略微非正规化架构来进一步减少这些内容：

如果在file_item中添加一个family_type_id列，则可以将查询重写如下：

SELECT count(1)
FROM file_item p
JOIN type t ON t.id = p.family_type_id
GROUP BY p.family_type_id
ORDER BY t.name

如果在file_item.family_type_id上建立索引，这个查询应该可以在毫秒级别内执行。

- Arnaud Le Blanc