mysql - SQL - 连接查询运行缓慢

Question

我正在尝试从 3 个不同的表中获取数据，如下所示:

SELECT urs.id
    ,urs.username
    ,urs.b_avg_update
    ,urs.b_set_min_avg
    ,urs.b_set_max_avg
    ,urs.b_min_avg
    ,urs.b_max_avg
    ,trs.TIME
    ,trs.user
    ,sum(trs.amount)
    ,trs.type
    ,ur.user_by
FROM (
    SELECT DISTINCT user_by
    FROM xeon_users_rented
    ) AS ur
LEFT JOIN users AS urs ON ur.user_by = urs.username
LEFT JOIN transactions AS trs ON urs.id = trs.user
    AND trs.type = 'Recycle'
    AND trs.TIME >= UNIX_TIMESTAMP(CURDATE())
GROUP BY ur.user_by

但是，该查询运行得相当慢:

Showing rows 0 - 29 (812 in total, request took 15.0095 sec)

在上述查询中运行 EXPLAIN 时，我得到的是:

id  select_type table   type    possible_keys   key key_len ref rows    Extra   
1   PRIMARY <derived2>  ALL NULL    NULL    NULL    NULL    1516    Using temporary; Using filesort
1   PRIMARY urs ALL NULL    NULL    NULL    NULL    14553   Using where; Using join buffer (flat, BNL join)
1   PRIMARY trs ref user,type   user    8   littlebu_maindb.urs.id  6   Using where
2   DERIVED xeon_users_rented   range   NULL    idx_user_by 62  NULL    1516    Using index for group-by

我试图在某些列上添加 INDEXES，希望能加快速度:

在表 xeon_users_rented 上:

Table   Non_unique  Key_name    Seq_in_index    Column_name Collation   Cardinality Sub_part    Packed  Null    Index_type  
xeon_users_rented   0   PRIMARY 1   id  A   37897   NULL    NULL        BTREE       
xeon_users_rented   0   user    1   user    A   37897   NULL    NULL        BTREE       
xeon_users_rented   1   user_by 1   user_by A   1579    NULL    NULL        BTREE       
xeon_users_rented   1   since   1   since   A   4210    NULL    NULL        BTREE       
xeon_users_rented   1   expire  1   expire  A   7579    NULL    NULL        BTREE       
xeon_users_rented   1   clicks  1   clicks  A   176 NULL    NULL        BTREE       
xeon_users_rented   1   clicks_last 1   clicks_last A   5413    NULL    NULL        BTREE       
xeon_users_rented   1   avg 1   avg A   3445    NULL    NULL        BTREE       
xeon_users_rented   1   click_hour  1   click_hour  A   46  NULL    NULL        BTREE       
xeon_users_rented   1   click_minute    1   click_minute    A   102 NULL    NULL        BTREE       
xeon_users_rented   1   idx_click_hour  1   click_hour  A   46  NULL    NULL        BTREE       
xeon_users_rented   1   idx_click_minute    1   click_minute    A   112 NULL    NULL        BTREE       
xeon_users_rented   1   idx_id  1   id  A   37897   NULL    NULL        BTREE       
xeon_users_rented   1   idx_user    1   user    A   37897   NULL    NULL        BTREE       
xeon_users_rented   1   idx_user_by 1   user_by A   1515    NULL    NULL        BTREE       
xeon_users_rented   1   idx_expire  1   expire  A   6316    NULL    NULL        BTREE       
xeon_users_rented   1   idx_since   1   since   A   4210    NULL    NULL        BTREE       
xeon_users_rented   1   idx_clicks  1   clicks  A   176 NULL    NULL        BTREE       
xeon_users_rented   1   idx_clicks_last 1   clicks_last A   5413    NULL    NULL        BTREE       
xeon_users_rented   1   idx_avg 1   avg A   3445    NULL    NULL        BTREE       

在表 users 上:

Table   Non_unique  Key_name    Seq_in_index    Column_name Collation   Cardinality Sub_part    Packed  Null    Index_type  
users   0   PRIMARY 1   id  A   14554   NULL    NULL        BTREE       
users   1   idx_username    1   username    A   14554   NULL    NULL        BTREE       
users   1   upline  1   upline  A   856 NULL    NULL        BTREE       
users   1   upline  2   upline_expire   A   856 NULL    NULL        BTREE

最后但同样重要的是，transactions 表:

Table   Non_unique  Key_name    Seq_in_index    Column_name Collation   Cardinality Sub_part    Packed  Null    Index_type  
transactions    0   PRIMARY 1   id  A   5293    NULL    NULL        BTREE       
transactions    1   user    1   user    A   882 NULL    NULL        BTREE       
transactions    1   type    1   type    A   9   NULL    NULL        BTREE       
transactions    1   amount  1   amount  A   378 NULL    NULL        BTREE

我看不出是什么导致了缓慢。总共连1000条记录都没有。如何改进我的原始查询和/或数据库设计？

Answer 1

这样试试看。我认为 users 应该是 JOIN 的 LEFT 一侧。但如果这不是您要查找的查询，我会尝试其他方法。

SELECT urs.username
    ,sum(trs.amount)
FROM users AS urs 
LEFT JOIN xeon_users_rented AS ur
       ON urs.username = ur.user_by 
LEFT JOIN transactions AS trs 
       ON urs.id = trs.user
      AND trs.type = 'Recycle'
      AND trs.TIME >= UNIX_TIMESTAMP(CURDATE())
WHERE ur.user_by IS NOT NULL
GROUP BY ur.user_by