mysql 3表交叉/左连接优化

rt4zxlrg 于 2021-06-18 发布在 Mysql

关注(0)|答案(1)|浏览(264)

我有3个表table1有id单元格table2有id单元格table3有id1单元格->对应table1.id table3有id2单元格->对应table2.id table3有时间戳，仅用于查找数据的最后一天
表1和表2有更多需要返回的数据。

SELECT
    t1.name AS t1name,
    t1.id AS t1id,
    t2.name AS t2name,
    t2.id AS t2id,
    t2.surname AS t2surname,
    t2.served AS t2served,
    t2.reported AS t2reported,
    COUNT(CASE WHEN t3.id1 IS NOT NULL AND t3.id2 IS NOT NULL THEN 1 END) AS t3hits
FROM t1
CROSS JOIN t2
LEFT JOIN t3 ON t1.id = t3.id1 AND t2.id = t3.id2 AND t3.time > SUBDATE(NOW(),1)
GROUP BY t1.id, t2.id
ORDER BY t3hits,t2served,t2reported ASC LIMIT 10

这需要12.45秒与我目前的表。

t1 is small, 20 records or so
t2 is 100k records or so
t3 is 100k records and growing

使用php通过http提供服务。。。
我把索引放得到处都是，但仍然很慢：）
任何帮助都将不胜感激。
谢谢！
这里是解释和索引

作为文本：

id  select_type table   type    possible_keys   key key_len ref rows    Extra

1   SIMPLE  t1  index   NULL    id1unique   50  NULL    13  Using index; Using temporary; Using filesort

1   SIMPLE  t2  ALL NULL    NULL    NULL    NULL    11652   Using join buffer (flat, BNL join)

1   SIMPLE  t3  ref ids_index   ids_index   8   id1,id2 1   Using where

Indexes 
id1unique   t1.id                               
ids_index   id1,id2

更多解释为什么使用它

t1 is a set of customers

t2 is a set of products

t3 has id of a customer and product and timestamp when it was purchased

我想为客户提供过去24小时内没有购买的产品，或过去24小时内购买最少的产品，这就是整个过程：）

mysql

来源：https://stackoverflow.com/questions/53103857/mysql-3-table-cross-left-join-optimization

1条答案

按热度按时间

xxls0lw81#

我想在加入之前先聚合一下。毕竟你想知道每个id1/id2的t3行数，所以加入“行数”吧：

SELECT
  t1.name AS t1name,
  t1.id AS t1id,
  t2.name AS t2name,
  t2.id AS t2id,
  t2.surname AS t2surname,
  t2.served AS t2served,
  t2.reported AS t2reported,
  COALESCE(t3agg.cnt, 0) AS t3hits
FROM t1
CROSS JOIN t2
LEFT JOIN 
(
  select id1, id2, count(*) as cnt
  from t3
  where t3.time > subdate(now(), 1)
  group by id1, id2
) t3agg ON t1.id = t3agg.id1 AND t2.id = t3agg.id2
ORDER BY t3hits, t2served, t2reported
LIMIT 10;

您应该有以下索引：

create index idx3 on t3(time, id1, id2);

该索引使dbms能够快速找到过去24小时内相对较少的行，并立即使用id1和id2，而无需查找表中的行。
你甚至可以做这个

create index idx3 on t3(time, id1, id2, name);

因此，甚至不必读取表中的名称。这应该是最快的。

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

我来回答

mysql 3表交叉/左连接优化

1条答案

相关问题

热门标签

最新问答