交通分析系统的MySQL优化

Question

背景:

我开发了一个URL缩短器系统，像Bitly一样，具有相同的功能，因此该系统还可以跟踪点击信息，并将其表示为图表给那些将链接缩短为分析数据的人。目前，我正在使用MySQL，并有一个表来存储此模式的单击信息：

visit_id (int)
ip (int)
date (datetime)
country
browser
device
os
referrer (varchar)
url_id (int)  //as foreign key to the shortened URL

现在，只有url_id字段有索引

系统应该表示用户想要的时间段中的点击分析，例如，过去的一小时，过去的24小时，过去的一个月，.

例如，为了生成上个月的图表，我执行以下查询：

SELECT all DAY(date) AS period, COUNT( * ) 
                        FROM (

                        SELECT * 
                        FROM visits
                        WHERE url_id =  '$url_id'
                        ) AS URL
                        WHERE DATE > DATE_SUB( CURRENT_TIMESTAMP( ) , INTERVAL 1 MONTH ) 
                        GROUP BY DAY( DATE )

//another query to display clicker browsers in this period
//another query to display clicker countries in this period
// ...

问题：

• 对于一个简短的链接，点击约50万次，只需3-4秒就能计算出第一个查询，所以对于总查询( 10-12秒)来说，这太糟糕了。
• 运行这些查询需要大量的内存和CPU。

问题:

如何改进和优化高流量链路的结构，以便在不到1秒的时间内显示出高流量链路的分析结果(就像小型和类似的web应用程序)，并且使用较少的CPU和RAM？我应该在字段date、country、browser、device、os、referrer上建立索引吗？如果是，如何为字段date这样做，因为我应该按DAY(date)，有时按HOUR(date)，有时按MINUTE(date)和.

2- MySQL适合这个应用程序吗？假设我的应用程序最多应该处理1亿个链接和100亿次点击。例如，我应该考虑切换到NoSQL解决方案吗？

如果MySQL还好，我的数据库设计和表结构是否适合我的应用程序需要？还是你有更好的建议和建议？

更新:我在列referrer上做了一个索引，但是这一点也没有帮助，而且还破坏了性能，我认为这是因为这个专栏的基数很低(也包括其他)，以及与我的服务器的内存相关的大索引大小。

我认为在这些专栏上建立索引无助于解决我的问题，我的想法是其中之一：

1-如果使用MySQL，可能使用高流量链路的背景处理来生成统计数据，而不是在用户请求时进行生动的计算。

2-使用像memcached这样的缓存解决方案来帮助MySQL实现高流量链接。

3-使用NoSQL (如MongoDB )和解决方案(如)，我对此非常熟悉，而且从未使用过。

你认为如何？

Answer 1

您可以将查询简化为类似的内容。我预计MySQL会产生一个更简单的执行计划。

SELECT date(date) period, count(*) clicks
FROM visits
WHERE url_id = 3
  AND DATE > DATE_SUB( CURRENT_TIMESTAMP( ) , INTERVAL 1 MONTH ) 
GROUP BY period;

如果在一秒钟内返回高流量链接的结果是一个困难的要求，您可能需要升级您的硬件。

在WHERE子句中使用的每个列都需要索引。您可能受益于某些多个索引；{url_id，date}是候选索引。

试一试，如果有可能的话。(这通常是可能的，尽管可能需要一些时间。)使用EXPLAIN查看MySQL对查询所做的操作。

您不必同时查询浏览器、国家和其他所有内容。当我经营网络开发的时候，我很少关注那些国家--它们与我所工作的利基没有关系。还可以考虑其他异步UI技术。

PostgreSQL有一个更好的优化器。它可能比MySQL更有效。试试看。