MySQL - 复杂性：SELECT COUNT（*）FROM MyTable; [英] MySQL - Complexity of: SELECT COUNT(*) FROM MyTable;

问题描述

这个MySQL查询的复杂性

  SELECT COUNT（*）FROM MyTable 
 

表中条目数的计数存储在某处，并在每次插入行时更新;已删除？如果是这样，那么复杂度应该是O（1）。

解决方案

这取决于存储引擎。对于MyISAM，为每个表存储总行数，因此 SELECT COUNT（*）FROM yourtable

$ b < c>是操作O（1）。它只需要读取此值。


对于InnoDB，不存储总行数，因此需要进行全面扫描。这是一个O（n）操作。

从手动：

InnoDB 不保留表中的行的内部计数。 （在实践中，由于多版本化，这会有些复杂。）要处理 SELECT COUNT（*）FROM t 语句， InnoDB 必须扫描表的索引，如果索引不完全在缓冲池中，则需要一些时间。如果你的表不经常变化，使用MySQL查询缓存是一个很好的解决方案。要获得快速计数，您必须使用您自己创建的计数器表，并让应用程序根据插入和删除它来更新它。 显示表状态 也可以使用，如果近似行计数就足够了。请参见第13.2.13.1节 InnoDB Performance Tuning Tips 。







What is the complexity of this MySQL query

SELECT COUNT(*) FROM MyTable;

Is the count of number of entries in a table stored somewhere and updated every time a row is inserted or deleted? If that is the case, then the complexity should be O(1).

解决方案

It depends on the storage engine.

• For MyISAM the total row count is stored for each table so SELECT COUNT(*) FROM yourtable is an operation O(1). It just needs to read this value.
• For InnoDB the total row count is not stored so a full scan is required. This is an O(n) operation.

From the manual:

InnoDB does not keep an internal count of rows in a table. (In practice, this would be somewhat complicated due to multi-versioning.) To process a SELECT COUNT(*) FROM t statement, InnoDB must scan an index of the table, which takes some time if the index is not entirely in the buffer pool. If your table does not change often, using the MySQL query cache is a good solution. To get a fast count, you have to use a counter table you create yourself and let your application update it according to the inserts and deletes it does. SHOW TABLE STATUS also can be used if an approximate row count is sufficient. See Section 13.2.13.1, "InnoDB Performance Tuning Tips".

这篇关于MySQL - 复杂性：SELECT COUNT（*）FROM MyTable;的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！