mysql - 如何使用GROUP BY(exec_datetime)优化COUNT(DISTINCT ip_address)的性能

我有以下查询，它显示了每天发出请求的不同ip地址。

SELECT COUNT(DISTINCT ip_address) as ip_address, DATE(exec_datetime) as day
FROM requests
GROUP BY MONTH(exec_datetime), DAY(exec_datetime);

EXPLAIN的输出如下

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  requests    ALL NULL    NULL    NULL    NULL    472043  Using filesort

我对覆盖索引没有清晰的理解，因为当我创建一个索引时，查询的完成时间与

ALTER TABLE requests ADD INDEX unique_ip_per_time(ip_address, exec_datetime);

这里是EXPLAIN的输出

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  requests    index   NULL    unique_ip_per_time  268 NULL    472043  Using index; Using filesort

如何通过创建索引或重新编写索引来优化此查询？
编辑
这两个语句的执行时间约为15秒（包括和不包括覆盖索引）。此表中唯一的其他键是aUNIQUE代理项和aINDEXonip_address
show indexes from requests

Table   Non_unique  Key_name    Seq_in_index    Column_name Collation   Cardinality Sub_part    Packed  Null    Index_type  Comment Index_comment
requests    0   PRIMARY 1   request_id  A   386577  NULL    NULL        BTREE
requests    1   ip_address  1   ip_address  A   193288  NULL    NULL    YES BTREE
requests    1   unique_ip_per_time  1   ip_address  A   163 NULL    NULL    YES BTREE
requests    1   unique_ip_per_time  2   exec_datetime   A   163 NULL    NULL    YES BTREE

编辑2
我遵循了eisberg的指示，但是这个查询大约需要1.1秒。。。

EXPLAIN SELECT
  A.request_day,
  (
    SELECT COUNT(DISTINCT B.ip_address)
    FROM requests B
    WHERE B.exec_date = A.request_day
  ) as num_ip_addr
FROM request_days A
ORDER BY A.request_day ASC;

比这个查询稍慢，大约需要0.9秒

SELECT COUNT(DISTINCT ip_address) as ip_address, exec_date
FROM requests
GROUP BY exec_date;

我想我不需要创建带有日期的附加表。对于DISTINCT ip_address（这似乎是瓶颈）语句的一部分，是否可以应用任何优化？

最佳答案

我已经为这类问题创造了一些解决办法。但你需要做些工作。
首先，应请求创建一个附加列，以避免在选择期间进行额外计算：

ALTER TABLE requests ADD COLUMN (request_day DATE);

ALTER TABLE requests ADD INDEX i1(request_day);

UPDATE requests SET request_day = DATE(exec_datetime);

您需要一个额外的表格来记忆您可以/想要选择的日期：

CREATE TABLE request_days (
  request_day DATE
);

ALTER TABLE request_days ADD UNIQUE INDEX i1(request_day);

INSERT IGNORE INTO request_days SELECT DATE(exec_datetime) FROM requests;

最后你可以：

EXPLAIN
SELECT
  A.request_day,
  (
    SELECT COUNT(DISTINCT B.ip_address)
    FROM requests B
    WHERE B.request_day = A.request_day
  )
FROM request_days A
ORDER BY A.request_day DESC

它给出：

ID  SELECT_TYPE         TABLE   TYPE    POSSIBLE_KEYS   KEY KEY_LEN REF                         ROWS    EXTRA
1   PRIMARY             A       index   (null)          i1  4       (null)                      1       Using index
2   DEPENDENT SUBQUERY  B       ref     i1              i1  4       db_2_95a42.A.request_day    1       Using where

我希望这对你有帮助！
SQL小提琴示例：http://sqlfiddle.com/#!2/95a42/2

关于mysql - 如何使用GROUP BY(exec_datetime)优化COUNT(DISTINCT ip_address)的性能，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/13525389/