mysql - 计算唯一行性能

Question

我有包含用户统计信息的表格。生成请求 URL 的统计信息。在带有 GET 参数 (event_id) 的 URL 下是 PHP 脚本。是用透明 [1x1] GIF 进行响应。事件是子项，插入和插入是从事件等到帐户的子项。基于 statistic_type 的两种统计类型在哪里。一个帐户有多个空格 (space_id)。

这是表创建:

CREATE TABLE `statistic` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`statistic_type` TINYINT(3) UNSIGNED NOT NULL,
`account_id` TINYINT(3) UNSIGNED NOT NULL,
`advertiser_id` SMALLINT(5) UNSIGNED NOT NULL,
`campaign_id` SMALLINT(5) UNSIGNED NOT NULL,
`insertion_id` MEDIUMINT(8) UNSIGNED NOT NULL,
`event_id` INT(10) UNSIGNED NOT NULL,
`space_id` SMALLINT(5) UNSIGNED NULL DEFAULT NULL,
`date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`stat_platform_id` TINYINT(3) UNSIGNED NULL DEFAULT NULL,
`stat_browser_id` SMALLINT(5) UNSIGNED NULL DEFAULT NULL,
`stat_device_type_id` TINYINT(3) UNSIGNED NULL DEFAULT NULL,
`major_version` TINYINT(4) UNSIGNED NULL DEFAULT NULL,
`uid` CHAR(19) NOT NULL,
`referrer` VARCHAR(255) NULL DEFAULT NULL,
`useragent` VARCHAR(255) NULL DEFAULT NULL,
`ipv4` INT(11) UNSIGNED NULL DEFAULT NULL,
PRIMARY KEY (`id`),
INDEX `IDX_uid` (`uid`),
INDEX `Indeks 2` (`statistic_type`, `account_id`, `advertiser_id`, `campaign_id`, `insertion_id`, `event_id`, `space_id`, `date`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
ROW_FORMAT=COMPRESSED
AUTO_INCREMENT=427891347;

现在我需要获取按 statistic_type、 分组的 campaign_id 的所有唯一(基于 uid 字段)行数event_id，空格。

所有表现在有 428067039 行。它每天仍在增长约 1500000 行。结果最多应有 100 行。过滤和字母聚合(具有一个 campaign_id 的行)行的最大数量应最多为 5000000。

现在我使用这个查询:

SELECT 
`sub`.`account_id`, 
`sub`.`advertiser_id`, 
`sub`.`campaign_id`, 
`sub`.`insertion_id`, 
`sub`.`event_id`, 
`sub`.`space_id`,
SUM(`sub`.actions) as 'actions',
count(1) as 'unique_actions'
FROM (
SELECT 
    `statistic`.`account_id`, 
    `statistic`.`advertiser_id`, 
    `statistic`.`campaign_id`, 
    `statistic`.`insertion_id`, 
    `statistic`.`event_id`, 
    `statistic`.`space_id`,
    count(1) as 'actions'
FROM `statistic`
WHERE 
    (`statistic`.`statistic_type`=1) AND 
    (`statistic`.`account_id`=3) AND 
    (`statistic`.`advertiser_id`=679) AND
    (`statistic`.`campaign_id`=4475) AND
    (`statistic`.`insertion_id`=26841)
GROUP BY 
    `statistic`.`statistic_type`, 
    `statistic`.`account_id`,
    `statistic`.`advertiser_id`,
    `statistic`.`campaign_id`,
    `statistic`.`insertion_id`,
    `statistic`.`event_id`,
    `statistic`.`space_id`,
    `statistic`.`uid`
) sub
GROUP BY 
`sub`.`statistic_type`, 
`sub`.`account_id`,
`sub`.`advertiser_id`,
`sub`.`campaign_id`,
`sub`.`insertion_id`,
`sub`.`event_id`,
`sub`.`space_id`;

当我按 insertion_id 过滤 400000 行时，计算时间超过 7-10 分钟。

解释:

+----+-------------+------------+------+---------------+----------+---------+------+-------+----------------------------------------------+
| id | select_type | table      | type | possible_keys | key      | key_len | ref  | rows  | Extra                                        |
+----+-------------+------------+------+---------------+----------+---------+------+-------+----------------------------------------------+
|  1 | PRIMARY     | <derived2> | ALL  | NULL          | NULL     | NULL    | NULL | 157521 | Using temporary; Using filesort              |
|  2 | DERIVED     | statistic  | ref  | Indeks 2      | Indeks 2 | 9       |      | 627090 | Using where; Using temporary; Using filesort |
+----+-------------+------------+------+---------------+----------+---------+------+-------+----------------------------------------------+
2 rows in set (2.61 sec)

也许我需要更改索引，或者添加新索引？

也许我需要添加新的列并在用户请求 URL 时插入一些值？

Answer 1

通常我建议不要使用长索引，但在这种情况下，我建议使用 8 列索引，其中包含 sub GROUP BY 的所有列，并且完全按照该顺序。您拥有的以 date 结尾的；我不知道它是否对其他事情有用。我的以 uid 结尾。

我希望这会加快子查询的速度，但对外部查询没有影响。

另一个想法...由于其中 5 列是不变的，您可以不将它们传递到外部查询吗？这将减少临时表的大约 157521 行，从而可能加快外部部分的速度。