MySQL使用高流量数据库上的过滤器对行进行计数 [英] Mysql count rows using filters on high traffic database

问题描述

假设您有一个包含多个选择字段的搜索表单，假设用户从下拉菜单中选择了一个选项，但是在他提交数据之前，我需要显示数据库中的行数.

Let's say you have a search form, with multiple select fields, let's say a user selects from a dropdown an option, but before he submits the data I need to display the count of the rows in the database .

因此，假设该网站每天至少有300k(300.000)个访问者，并且用户从访问中至少选择40次，从表单中选择选项，这意味着数据库上有1200万个Ajax请求+ 1200万个计数查询，这似乎有点太多.

So let's say the site has at least 300k(300.000) visitors a day, and a user selects options from the form at least 40 times a visit, that would mean 12M ajax requests + 12M count queries on the database, which seems a bit too much .

问题是，如何才能实现快速计数(使用php(Zend Framework)和MySQL)，以便对数据库进行额外的12M查询不会影响网站的负载.

The question is how can one implement a fast count (using php(Zend Framework) and MySQL) so that the additional 12M queries on the database won't affect the load of the site .

一种解决方案是拥有一个表，该表存储选择字段的所有组合及其各自的计数(当从产品表中添加或删除产品时，将更新存储计数的表).虽然这不是一个好主意，但是当43个过滤器中的8个(选择选项)插入的+ 8M行需要管理时.

One solution would be to have a table that stores all combinations of select fields and their respective counts (when a product is added or deleted from the products table the table storing the count would be updated). Although this is not such a good idea when for 8 filters (select options) out of 43 there would be +8M rows inserted that need to be managed.

关于如何实现这一目标的其他想法?

Any other thoughts on how to achieve this?

p.s.我不需要代码示例，但想法本身可以在这种情况下使用.

p.s. I don't need code examples but the idea itself that would work in this scenario.

推荐答案

我建议使用一个单独的表来缓存计数以及触发器.

I would suggest a separate table that caches the counts, combined with triggers.

为了使其快速运行，请使其成为内存表，并使用插入，删除和更新上的触发器对其进行更新.

In order for it to be fast you make it a memory table and you update it using triggers on the inserts, deletes and updates.

伪代码:

CREATE TABLE counts (
  id unsigned integer auto_increment primary key
  option integer indexed using hash key
  user_id integer indexed using hash key
  rowcount unsigned integer
  unique key user_option (user, option)
) engine = memory

DELIMITER $$

CREATE TRIGGER ai_tablex_each AFTER UPDATE ON tablex FOR EACH ROW
BEGIN
  IF (old.option <> new.option) OR (old.user_id <> new.user_id) THEN BEGIN
    UPDATE counts c SET c.rowcount = c.rowcount - 1 
      WHERE c.user_id = old.user_id and c.option = old.option; 
    INSERT INTO counts rowcount, user_id, option  
      VALUES (1, new.user_id, new.option)
      ON DUPLICATE KEY SET c.rowcount = c.rowcount + 1; 
  END; END IF;
END $$

DELIMITER ;

计数的选择将是即时的，并且触发器中的更新也不会花费很长时间，因为您正在使用带有具有O(1)查找时间的哈希索引的内存表.

Selection of the counts will be instant, and the updates in the trigger should not take very long either because you're using a memory table with hash indexes which have O(1) lookup time.

链接:
内存引擎: http://dev.mysql.com/doc/refman/5.5/zh-CN/memory-storage-engine.html
触发器: http://dev.mysql.com/doc/refman/5.5/en/triggers.html

这篇关于MySQL使用高流量数据库上的过滤器对行进行计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！