MySQL 3 表交叉/左连接优化

Question

我有3张 table Table1 有 id 单元格Table2 有 id 单元格Table3 有 id1 单元格 -> 对应于 Table1.idTable3 有 id2 单元格 -> 对应于 Table2.idTable3 有一个时间戳，用于仅查看最后一天的数据

Table1和Table2还有更多数据需要返回。

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 小时内购买次数最少的产品这就是整个过程:)

Answer 1

我会在加入之前进行汇总。毕竟您想知道每个 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);

因此甚至不必读取表格来查找名称。这应该是最快的。