如何通过组( ip_address)优化计数(DISTINCT Ip_address)的性能

Question

我有以下查询，它显示了每天发出请求的不同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秒(有和没有覆盖索引)。这个表中唯一的其他键是UNIQUE代理项和ip_address上的INDEX

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;

，这比这个查询稍微慢一些，该查询大约需要.9秒。

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

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

Answer 1

我为这类问题创造了一个小小的解决办法。但你需要在里面做些工作。

首先，应请求创建一个额外的列，以避免在选择期间进行额外的计算：

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

Answer 2

由于在exec_datetime上使用日期函数，引擎将扫描表的所有行。你应该试试partitioning the table on exec_datetime http://dev.mysql.com/doc/refman/5.1/en/partitioning.html

Answer 3

理想情况下，只需添加如下复合功能索引：

CREATE INDEX month_day_idx
   ON requests (MONTH(exec_datetime), DAY(exec_datetime));

不幸的是，MySQL不支持函数索引。相反，您有两个选择：

1. 为月和日创建额外的列，并使用这2个新字段创建复合索引。
2. 或者，如果可以的话，可以将组更改为不使用函数。