MySql - 索引优化

Question

我们有一个分析产品。我们为每个客户提供一个 JavaScript 代码，他们将其放在自己的网站上。如果用户访问我们的客户站点，java 脚本代码会访问我们的服务器，以便我们代表我们的客户存储此页面访问。我们的每个客户都包含唯一的域名，这意味着客户由域名决定

数据库服务器:MySql 5.6表格行数:4 亿

以下是我们的表架构。

+---------------+------------------+------+-----+---------+----------------+
| Field         | Type             | Null | Key | Default | Extra          |
+---------------+------------------+------+-----+---------+----------------+
| id            | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| domain        | varchar(50)      | NO   | MUL | NULL    |                |
| guid          | binary(16)       | YES  |     | NULL    |                |
| sid           | binary(16)       | YES  |     | NULL    |                |
| url           | varchar(2500)    | YES  |     | NULL    |                |
| ip            | varbinary(16)    | YES  |     | NULL    |                |
| is_new        | tinyint(1)       | YES  |     | NULL    |                |
| ref           | varchar(2500)    | YES  |     | NULL    |                |
| user_agent    | varchar(255)     | YES  |     | NULL    |                |
| stats_time    | datetime         | YES  |     | NULL    |                |
| country       | char(2)          | YES  |     | NULL    |                |
| region        | char(3)          | YES  |     | NULL    |                |
| city          | varchar(80)      | YES  |     | NULL    |                |
| city_lat_long | varchar(50)      | YES  |     | NULL    |                |
| email         | varchar(100)     | YES  |     | NULL    |                |
+---------------+------------------+------+-----+---------+----------------+

在上表中，guid 代表我们客户站点的访问者，sid 代表我们客户站点的访问者 session 。这意味着每个 sid 都应该有关联的 guid。

我们需要如下查询

查询 1:查找唯一身份访问者总数

SELECT count(DISTINCT guid) AS count,count(guid) AS total FROM page_views WHERE domain = 'abc' AND stats_time BETWEEN '2015-10-05 00:00:00' AND '2015-10-04 23:59:59'

综合索引规划:domain,stats_time,sid

查询 2:查找唯一的总 session 数

SELECT count(DISTINCT sid) AS count,count(sid) AS total FROM page_views WHERE domain = 'abc' AND stats_time BETWEEN '2015-10-05 00:00:00' AND '2015-10-04 23:59:59'

综合索引规划:domain,stats_time,guid

查询 3:按国家、地区、城市查找访客、 session

综合指数规划:领域，国家

综合索引规划:domain,region

每个组合都需要新的复合索引。这意味着巨大的索引文件，我们无法将其保存在内存中，因此查询性能很低。

有什么方法可以优化这种索引组合以减少索引大小并提高性能。

Answer 1

只是为了笑，运行这个看看你有什么类型的点差......

select 
      country, region, city, 
      DATE_FORMAT(colName, '%Y-%m-%d') DATEONLY, count(*)
   from 
      yourTable 
   group by
      country, region, city, 
      DATE_FORMAT(colName, '%Y-%m-%d')
   order by
      count(*) desc

然后查看它返回了多少行。此外，COUNT 列生成什么样的范围。在您尝试提供数据挖掘的关键元素上创建一个单独的聚合表是否有意义，而不只是一个索引。

如果是这样，我建议您查看类似的帖子 also on the stack here .这显示了一个关于如何做的样本，但在进一步建议之前我会先看看计数。但是，如果您每天对其进行分解，那么它可能会减少到什么程度。

此外，您可能希望创建一次预聚合表以开始使用，然后有一个夜间程序，根据刚刚完成的一天构建任何新记录。这样它就永远不会遍历所有 400M 记录。

如果您的预聚合表仅基于日期(仅 y、m、d)存储，则每天汇总的查询会缩短查询要求。 COUNT(*) 只是示例基础，但您可以根据需要添加 count(distinct whateverColumn)。然后，您可以根据域、日期范围等查询 SUM( aggregateColumn )。如果您的 400M 记录减少到 7M 记录，我也会在(域、dateOnlyField，也许还有国家)上有一个最小索引来优化您的域、日期范围查询。一旦您将某些内容缩小到任何有意义的级别，您就可以始终深入研究原始数据以获得更细粒度的级别。