如何加速InnoDB计数（*）查询？ [英] How to speed up InnoDB count(*) query?

问题描述

这里有很多类似的问题，但很多答案说要强制使用一个索引，似乎不会为我加速任何东西。

我想在我的网站上显示一个活计数器，显示表中的行数。类似某些网站如何显示实时（即使用ajax或websockets频繁更新）的注册用户数量或其他统计信息。

我的表有大约5M行。它的增长相当快，并且有大量的插入和删除。运行

 从my_table中选择count（*）
  

需要1.367秒，这是不可接受的，因为我需要我的应用程序来获取每秒一次的新行计数。

我试过这里的许多答案建议并将查询更改为：

 从my_table使用索引（my_index）选择计数（*）
  

$ c> my_index 在 bigint上的正常， BTREE 字段。但是时间实际上增加到1.414秒。

为什么不使用索引加速查询，因为这里有很多答案？

另一个选项，一些答案建议是在表上放置一个触发器，增加另一个表中的列。所以我可以创建一个 stats 表，每当在中插入或删除一行时，my_table 有一个触发器递增或递减列在 stats 表中。这是唯一的其他选项，因为使用索引似乎不工作？

编辑：这是一个完美的例子，我想完成的事情：请注意，该博客倡导在一对临时表之间进行翻转，以便最小化对它们的锁定，并允许多个客户端共存。

There are a number of similar questions on here, but a lot of the answers say to force the use of an index and that doesn't seem to speed anything up for me.

I am wanting to show a "live" counter on my website showing the number of rows in a table. Kind of like how some websites show the number of registered users, or some other statistic, in "real time" (i.e. updated frequently using ajax or websockets).

My table has about 5M rows. It's growing fairly quickly and there is a high volume of inserts and deletes on it. Running

select count(*) from my_table

Takes 1.367 seconds, which is unacceptable because I need my application to get the new row count about once per second.

I tried what many of the answers on here suggest and changed the query to:

select count(*) from my_table use index(my_index)

Where my_index is Normal, BTREE on a bigint field. But the time actually increased to 1.414 seconds.

Why doesn't using an index speed up the query as many answers on here said it would?

Another option some answers suggest is to put a trigger on the table that increments a column in another table. So I could create a stats table and whenever a row is inserted or deleted in my_table have a trigger increment or decrement a column in the stats table. Is this the only other option, since using an index doesn't seem to work?

EDIT: Here's a perfect example of the type of thing I'm trying to accomplish: https://www.freelancer.com. Scroll to the bottom of the page and you'll see:

Those numbers update every second or so.

解决方案

If your system is so busy that the counting is having too much impact, then probably the INSERTing/DELETEing is also having impact. One way to improve INSERT/DELETE is to do them in 'batches' instead of one at a time.

Gather the INSERTs, preferably in the app, but optionally in a 'staging' table. Then, once a second (or whatever) copy them into the real table using an INSERT..SELECT, or (if needed) INSERT..ON DUPLICATE KEY UPDATE. DELETEs can go into the same table (with a flag) or a separate table.

The COUNT(*) can be done at the end of the batch. Or it could be dead reckoned (at much lower cost) by knowing what the count was, then adjusting by what the staging table(s) will change it by.

This is a major upheaval to you app code, so don't embark on it unless you have spikes of, say, >100 INSERTs/DELETEs per second. (A steady 100 INSERTs/sec = 3 billion rows per year.)

For more details on "staging table", see http://mysql.rjweb.org/doc.php/staging_table Note that that blog advocates flip-flopping between a pair of staging tables, so as to minimize locks on them, and to allow multiple clients to coexist.

这篇关于如何加速InnoDB计数（*）查询？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！