Mysql使用高流量数据库上的过滤器计算行数
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 .
假设您有一个搜索表单,有多个选择字段,假设用户从下拉列表中选择一个选项,但在提交数据之前,我需要显示数据库中的行数。
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 .
所以假设该网站每天至少有300k(300.000)个访问者,并且用户从访问表中选择至少40次访问的选项,这意味着12M ajax请求+ 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 .
问题是如何实现快速计数(使用php(Zend Framework)和MySQL),以便数据库上额外的12M查询不会影响站点的负载。
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.
一种解决方案是具有存储所选字段的所有组合及其各自计数的表(当从产品表中添加或删除产品时,将更新存储计数的表)。虽然这对于43个中的8个过滤器(选择选项)来说并不是一个好主意,但是会插入需要管理的+ 8M行。
Any other thoughts on how to achieve this?
关于如何实现这一点的任何其他想法?
p.s. I don't need code examples but the idea itself that would work in this scenario.
附:我不需要代码示例,但想法本身可以在这种情况下工作。
10 个解决方案
#1
2
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.
为了使它快速,你可以使它成为一个内存表,并使用插入,删除和更新的触发器来更新它。
pseudo code:
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 ;
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.
计数的选择将是即时的,并且触发器中的更新不会花费很长时间,因为您正在使用具有O(1)查找时间的哈希索引的内存表。
Links:
Memory engine: http://dev.mysql.com/doc/refman/5.5/en/memory-storage-engine.html
Triggers: http://dev.mysql.com/doc/refman/5.5/en/triggers.html
链接:内存引擎:http://dev.mysql.com/doc/refman/5.5/en/memory-storage-engine.html触发器:http://dev.mysql.com/doc/refman/5.5/en/ triggers.html
更多相关文章
- MYSQL如何导出存储过程和触发器?
- mysql 触发器 自动补全字段
- php怎么读取MYSQL数据到radio选项
- 在我的sql中调用触发器中的函数
- MYSQL在触发器中怎样实现‘根据条件来确定是否插入一条记录’?急!
- MySql_数据库触发器的使用
- 如何在HTML选择选项列表中保留空间hi
- 如何让react-native Picker保持新选择的选项?
- 如何为select中的选项创建?