mysql - MySql 中的大表和分析

Question

对于我的初创公司，我自己跟踪一切，而不是依赖谷歌分析。这很好，因为我实际上可以拥有 ips 和用户 ID 以及所有内容。

在我的跟踪表增加了大约 200 万行之前，这一直很有效。该表称为 acts，记录:

• IP
• 网址
• 注意
• 帐号编号

...如果可用。

现在，尝试做这样的事情:

SELECT COUNT(distinct ip) 
  FROM acts
  JOIN users ON(users.ip = acts.ip) 
 WHERE acts.url LIKE '%some_marketing_page%';

基本上永远不会完成。我切换到这个:

SELECT COUNT(distinct ip) 
  FROM acts
  JOIN users ON(users.ip = acts.ip) 
 WHERE acts.note = 'some_marketing_page';

但它仍然很慢，尽管有一个索引。

我显然不是 mysql 专家。我的问题是:

拥有大量数据的公司如何跟踪漏斗转化率等信息？有可能在 mysql 中做，我只是缺少一些知识吗？如果没有，我可以阅读哪些有关网站如何执行此操作的书籍/博客？

Answer 1

虽然要达到“体面”，但 200 万行对于一个表来说仍然是一个相对较小的尺寸。 (因此通常可以实现更快的性能)

正如您所发现的，前端通配符效率特别低，如果该用例在您的应用程序中很常见，我们将不得不为此找到解决方案。

可能只是您没有正确的索引集。然而，在我继续之前，我想强调的是，虽然索引通常会通过各种 SELECT 语句提高 DBMS 性能，但它系统地对“CUD”操作的性能产生负面影响(即使用 SQL CREATE/INSERT、UPDATE , DELETE 动词，即 写入 数据库而不是读取 数据库的查询)。在某些情况下，索引对“写入”查询的负面影响可能非常显着。

我特别强调索引的矛盾性质的原因是，您的应用程序似乎将大量数据收集作为其正常操作的一部分，并且您需要注意在 INSERT 查询到达时可能出现的降级放慢速度。一种可能的替代方法是将数据收集到一个相对较小的表/数据库中，没有或只有很少的索引，并定期将数据从该输入数据库导入到实际进行数据挖掘的数据库中。 (导入后，行可能会从“输入数据库”中删除，以保持它的 INSERT 功能小而快。)

另一个关注点/问题是关于转换表中一行的宽度(列数和这些列的宽度之和)。性能不佳可能与行太宽有关，导致表的叶节点中的行太少，因此树结构比需要的更深。

回到索引...
鉴于问题中的几个查询，您似乎可以从 ip + note 索引中受益(至少按此顺序使用这两个键创建的索引)。对索引情况的全面分析，坦率地说，无法在这里完成对数据库模式的可能审查(没有足够的信息……)，但这样做的一般过程是列出最常见的用例和查看哪些数据库索引可以帮助处理这些情况。可以使用 mySQL 命令 EXPLAIN 深入了解特定查询在最初或添加索引后是如何处理的。

规范化或非规范化(或者实际上是两者的结合!)，通常也是提高采矿作业性能的可行想法。