加速sql内部连接和左连接组合语句

13z8s7eq 于 2021-06-20 发布在 Mysql

关注(0)|答案(3)|浏览(311)

希望提高以下sql语句的性能，因为搜索850000条记录需要7秒以上的时间。

select c.productTitle
     , c.catalogue_id
     , s1.catalogue_id
     , s1.cpc 
  FROM store s1 
  JOIN catalogue c 
    ON s1.catalogue_id = c.catalogue_id
  LEFT 
  JOIN store s2 
    ON (s1.catalogue_id = s2.catalogue_id AND s1.cpc < s2.cpc)
 WHERE s2.cpc IS NULL 
  AND c.productTitle LIKE '%user-query-here%'
GROUP 
   BY c1.catalogue_id

我有一个包含850000种产品（非重复）的目录表和一个包含每个产品销售价格的存储表，存储表可以有重复，因为多个卖家可以销售同一产品。
目录表

| catalogue_id | productTitle      | barcode |
| 1            | washing machine   | abc     |
| 2            | dish washer       | def     |
| 3            | toaster           | ghi     |
| 4            | kettle            | jkl     |
| 5            | mobile phone 8gb  | mno     |

存储表

| product_id | catalogue_id      | cpc     |
| 1001       | 1                 | 0.01     |
| 2001       | 1                 | 0.02     |
| 3001       | 2                 | 0.05     |
| 4001       | 3                 | 0.01     |
| 5001       | 1                 | 0.05     |

预期结果。。。
（洗衣机按目录编号分组，仅按每次点击的最高成本（cpc）显示产品）

washing machine | 5001 | 0.05 
dish washer     | 3001 | 0.05
toaster         | 4001 | 0.01

当做

sql mysql

来源：https://stackoverflow.com/questions/51581545/speed-up-sql-inner-join-and-left-join-combination-statement

3条答案

按热度按时间

b4wnujal1#

你应该重新定义你的sql。第一个连接可能是一个内部连接请使用它来明确的意图。嵌套查询。

select c.productTitle
     , c.catalogue_id
     , s1.catalogue_id
     , s1.cpc 
  FROM store s1 
  JOIN catalogue c 
    ON s1.catalogue_id = c.catalogue_id
  LEFT 
  JOIN store s2 
    ON (s1.catalogue_id = s2.catalogue_id AND s1.cpc < s2.cpc)
 WHERE s2.cpc IS NULL

分为一个查询和其余的查询之外，还要考虑避免is null语句和有一个为空的代码。
还有一个问题是查询中表的顺序。首先从联接中选取最小的表或提供最小乘积的表，然后再选取其余的表。
下一个问题是表上有什么索引。如果查询需要7秒，那么你应该围绕什么样的功能进行实验。
另外一个问题是i/o单元有多忙，如果这不是问题的话，那么重新设计应该会起作用。

赞(0）回复(0）举报 2021-06-20

fykwrbwg2#

我最好的猜测是大部分时间都花在 AND c.productTitle LIKE '%user-query-here%' .
坏消息是，在这种情况下无法生成普通索引，因此数据库必须遍历所有记录并对每个记录执行子字符串匹配。
如果你想不出任何其他的方法（即更换 LIKE 与 = )，则需要全文搜索。好消息是mysql有一个内置的fts支持。

赞(0）回复(0）举报 2021-06-20

hvvq6cgz3#

在研究了可能的原因后，结果是 Innodb buffer pool size 太低了。
我换了游泳池的大小，换了游泳池的大小 like 至 MATCH() AGAINST() 在表中重新定义索引。
谢谢你的回复。

赞(0）回复(0）举报 2021-06-20