浏览跟踪器的MySQL查询优化 [英] mySQL query optimisation for browse tracker

问题描述

一段时间以来，我一直在阅读许多针对不同问题的出色答案，但这是我第一次发布.因此，预先感谢您的帮助.

I have been reading lots of great answers to different problems over the time on this site but this is the first time I am posting. So in advance thanks for your help.

这是我的问题:

我有一个 MySQL 表，该表跟踪对我们拥有的不同网站的访问.这是表结构:

I have a MySQL table that tracks visits to different websites we have. This is the table structure:

    create table navigation_base (
          uid int(11) NOT NULL,
          date datetime not null,
          dia date not null,
          ip int(4) unsigned not null default 0,
          session_id int unsigned not null,
          cliente smallint unsigned not null default 0,
          campaign mediumint unsigned not null default 0,
          trackcookie int unsigned not null,
          adgroup int unsigned not null default 0,
          PRIMARY KEY (uid)
     ) ENGINE=MyISAM;

此表带有一个近似值. 7000万行(平均每天110,000行).

This table has aprox. 70 million rows (an average of 110,000 per day).

在该表上，我们使用以下命令创建了索引:

On that table we have created indexes with following commands:

alter table navigation_base add index dia_cliente_campaign_ip (dia,cliente,campaign,ip);
alter table navigation_base add index dia_cliente_campaign_ip_session (dia,cliente,campaign,ip,session_id);
alter table navigation_base add index dia_cliente_campaign_ip_session_trackcookie (dia,cliente,campaign,ip,session_id,trackcookie);

然后，我们使用此表通过以下查询获取按客户，天数和广告系列分组的访问者统计信息:

We then use this table to get visitor statistics grouped by clients, days and campaigns with the following query:

select 
  dia,
  navigation_base.campaign,
  navigation_base.cliente,
  count(distinct ip) as visitas,
  count(ip) as paginas_vistas,
  count(distinct session_id) as sesiones,
  count(distinct trackcookie) as cookies 
from navigation_base where 
  (dia between '2017-01-01' and '2017-01-31') 
  group by dia,cliente,campaign order by NULL

即使创建了这些索引，一个月的响应时间也相对较慢；在我们的服务器上大约3秒钟.

Even having those indexes created, the response times for periods of one month are relatively slow; On our server about 3 seconds.

是否有一些方法可以加快这些查询的速度?

Are there some ways of speeding up these queries?

谢谢.

推荐答案

如果只在一天的间隔内执行COUNT(DISTINCT ...)，则构建并逐步维护汇总表.它将在每晚增加几乎与您的SELECT相同的查询，但只会获取昨天的数据.

If you only do COUNT(DISTINCT ...) at the granularity of a day, then build and incrementally maintain a summary table. It would augmented each night by a query nearly identical to your SELECT, but only fetching yesterday's data.

然后将此摘要表用于每月的报告".

Then use this Summary Table for the monthly "report".

汇总表的更多信息

这篇关于浏览跟踪器的MySQL查询优化的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！