使用JOIN优化sql查询的顺序和位置

Question

我目前正在一个在线电子商务平台的后台工作。我现时有约7万种产品，我希望能加快数据的显示速度，使雇员能更有效率地工作。

我使用的是MySQL "Ver 14.14远端5.7.28“。

基本上，对于我的后台办公室(我不会明确列出专栏的细节，因为我认为这并不重要)，我有：

• 主表node_node，包含所有数据的基本信息，例如creation_date、last_modification_date (日期字段)
• 主表staff_node_staffnode，包含员工创建的所有数据(如产品、品牌等)的基本信息。它主要包含字段owner_id ( staff表的外键，我在这里不详细介绍)和is_verified (布尔字段)和一个外键staffnode_ptr_id (指向node_node )。
• 数据结构表，如product_merchandise、product_brand，它们包含自己的字段，以及一个外键staffnode_ptr_id定位到staff_node_staffnode

我首先运行一个查询来检索我想要显示的产品的所有I(考虑到我喜欢的大量数据，首先只检索我的列表中产品的I，它将限制在每页30个，然后在这个子集上检索更多的数据，并在其他表上添加更多的联接)。

SELECT id from product_merchandise pm 
INNER JOIN staff_node_staffnode sns ON sns.node_ptr_id = pm.staffnode_ptr_id 
INNER JOIN node_node nn ON nn.id = sns.node_ptr_id 
ORDER BY creation_date DESC LIMIT 30;

有一个关于product_merchandise(staffnode_ptr_id)、staff_node_staffnode(node_ptr_id)和node_node(id)的索引。运行这个查询平均需要2到3秒，这太长了。

编辑:正如注释中所建议的，以下是解释查询的输出。“解释分析”并不适用于我的Mysql版本。

+----+-------------+-------+------------+--------+---------------+------------------------------+---------+------------------------+-------+----------+----------------------------------------------+
| id | select_type | table | partitions | type   | possible_keys | key                          | key_len | ref                    | rows  | filtered | Extra                                        |
+----+-------------+-------+------------+--------+---------------+------------------------------+---------+------------------------+-------+----------+----------------------------------------------+
|  1 | SIMPLE      | pm    | NULL       | index  | PRIMARY       | product_merchandise_447d3092 | 5       | NULL                   | 69623 |   100.00 | Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | sns   | NULL       | eq_ref | PRIMARY       | PRIMARY                      | 4       | db.pm.staffnode_ptr_id |     1 |   100.00 | Using index                                  |
|  1 | SIMPLE      | nn    | NULL       | eq_ref | PRIMARY       | PRIMARY                      | 4       | db.pm.staffnode_ptr_id |     1 |   100.00 | NULL                                         |
+----+-------------+-------+------------+--------+---------------+------------------------------+---------+------------------------+-------+----------+----------------------------------------------+

我决定在node_node(creation_date)上添加一个索引node_node(creation_date)，当我强制使用它时，我得到了0.10s到0.15s之间的值，这是非常完美的：

SELECT id from product_merchandise pm 
INNER JOIN staff_node_staffnode sns ON sns.node_ptr_id = pm.staffnode_ptr_id 
INNER JOIN node_node nn FORCE INDEX(creation_date_idx) ON nn.id = sns.node_ptr_id 
ORDER BY creation_date DESC LIMIT 30;

现在的问题是，产品的工作人员应该能够根据不同的参数进行过滤，例如owner_id。

SELECT id from product_merchandise pm 
INNER JOIN staff_node_staffnode sns ON sns.node_ptr_id = pm.staffnode_ptr_id 
INNER JOIN node_node nn FORCE INDEX(creation_date_idx) ON nn.id = sns.node_ptr_id 
WHERE sns.owner_id = [NUMBER]
ORDER BY creation_date DESC LIMIT 30;

结果很糟糕(我在30岁左右停止了查询，但我认为这可能会花费更多的时间)，这是有意义的，因为我强制使用索引creation_date_index，这在这里不相关。

如果去掉这个索引的使用，就会得到更好的结果(1-2s.)但我回到第一个问题，那就是:计算时间太长。

编辑:正如建议的，这是解释的输出

SELECT id from product_merchandise pm 
INNER JOIN staff_node_staffnode sns ON sns.node_ptr_id = pm.staffnode_ptr_id 
INNER JOIN node_node nn ON nn.id = sns.node_ptr_id 
WHERE sns.owner_id = [NUMBER]
ORDER BY creation_date DESC LIMIT 30;

+----+-------------+-------+------------+--------+---------------------------------------+------------------------------+---------+------------------------+-------+----------+----------------------------------------------+
| id | select_type | table | partitions | type   | possible_keys                         | key                          | key_len | ref                    | rows  | filtered | Extra                                        |
+----+-------------+-------+------------+--------+---------------------------------------+------------------------------+---------+------------------------+-------+----------+----------------------------------------------+
|  1 | SIMPLE      | pm    | NULL       | index  | PRIMARY                               | product_merchandise_447d3092 | 5       | NULL                   | 69220 |   100.00 | Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | sns   | NULL       | eq_ref | PRIMARY,staff_node_staffnode_5e7b1936 | PRIMARY                      | 4       | db.pm.staffnode_ptr_id |     1 |    19.00 | Using where                                  |
|  1 | SIMPLE      | nn    | NULL       | eq_ref | PRIMARY                               | PRIMARY                      | 4       | db.pm.staffnode_ptr_id |     1 |   100.00 | NULL                                         |
+----+-------------+-------+------------+--------+---------------------------------------+------------------------------+---------+------------------------+-------+----------+----------------------------------------------+

我想我应该创建另一个索引，但我不知道什么列。此外，工作人员应该能够在5个不同的字段(假设它们都是VARCHAR或外键或布尔)上进行筛选，并根据这些不同的字段进行排序。这些字段可以来自表product_merchandise (例如product_name)、staff_node_staffnode (创建者或is_verified)或事件node_node (例如creation_date)。

我希望我说得够清楚了。谢谢您的时间，我会感谢您的帮助！

祝你今天过得愉快。

Answer 1

我把它放在这里，因为它不适合注释，下面列出了提高性能所需的索引列表：

• product_merchandise(id，staffnode_ptr_id)
• staff_node_staffnode(node_ptr_id，owner_id)
• node_node(id，creation_date DESC)

更改/将索引添加到上面的列表中，让我们看看它如何改变性能

Answer 2

谢谢你的建议。我发布了一个答案，而不是编辑我原来的问题，因为我的测试结果是相当长的。我希望这不会是一个问题。

首先，我忘记提到staffnode_ptr_id是product_merchandise的主键，node_ptr_id是staff_node_staffnode的主键。

下面是除了主要索引之外的索引：

CREATE INDEX node_creationdate_idx ON node_node(creation_date);
CREATE INDEX node_id_creationdate_idx ON node_node(id,creation_date);
CREATE INDEX staffnode_nodeptrid_ownerid_idx ON staff_node_staffnode(node_ptr_id,owner_id);

我没有为索引node_id_creationdate_idx指定DESC，因为排序可以是ASC，也可以是DESC (视情况而定)。

这里是我运行的速度测试的结果(我对每种情况执行了10次查询)：

详细信息可在此链接上找到。

No index forced, ordering by 'creation_date' only
average: 2.4473010037094354 fastest: 2.0254166573286057 slowest: 2.891202986240387

Forcing index 'node_creationdate_idx', ordering by 'creation_date' only
average: 0.045951709523797034 fastest: 0.03917844220995903 slowest: 0.06625311821699142

No index forced, ordering by 'creation_date' and filtering on 'owner_id'
average: 1.7595138054341077 fastest: 1.08128846809268 slowest: 2.858897101134062

Forcing index 'node_creationdate_idx', ordering by 'creation_date' and filtering on 'owner_id'
average: infinity

上面的结果与我在最初的文章中所说的相吻合。

如果我尝试按sku排序(这是product_merchandise表的VARCHAR列)，无论发生什么，计算都非常快。

No index forced, ordering by 'sku' only
average: 0.0022248398512601853 fastest: 0.0017771385610103607 slowest: 0.0032510906457901

No index forced, ordering by 'sku' and filtering on 'owner_id'
average: 0.00639396645128727 fastest: 0.0025643371045589447 slowest: 0.0197000615298748

在下面的结果中，我试图强制使用新的索引staffnode_nodeptrid_ownerid_idx和node_id_creationdate_idx

Forcing index 'staffnode_nodeptrid_ownerid_idx', ordering by 'creation_date' only
average: 2.1846631478518246 fastest: 1.665839608758688 slowest: 2.5894345454871655

Forcing index 'staffnode_nodeptrid_ownerid_idx', ordering by 'creation_date' and filtering on 'owner_id'
average: 0.9459988728165627 fastest: 0.726978026330471 slowest: 1.1611059792339802

Forcing index 'node_id_creationdate_idx', ordering by 'creation_date' only
average: 1.7628929097205401 fastest: 1.5384734570980072 slowest: 1.9222845435142517

Forcing index 'node_id_creationdate_idx', ordering by 'creation_date' and filtering on 'owner_id'
average: 1.2311949148774146 fastest: 0.9017647355794907 slowest: 1.4749027229845524

Forcing indexes 'node_id_creationdate_idx' and 'staffnode_nodeptrid_ownerid_idx', ordering by 'creation_date' only
average: 1.5638799782842399 fastest: 1.3537045568227768 slowest: 1.8629941195249557

Forcing indexes 'node_id_creationdate_idx' and 'staffnode_nodeptrid_ownerid_idx', ordering by 'creation_date' and filtering on 'owner_id'
average: 1.6410113696008921 fastest: 1.2819141708314419 slowest: 2.3169863671064377

最后：

• 我用这些指数得到的结果略好一些，尽管在我看来它仍然太长了
• 问题似乎在于creation_date不属于表product_merchandise，因此对其进行索引并不是真正有效的。

你有什么建议？我应该改变我的桌子的结构吗？

谢谢你的帮助！