blocks|key|1008021|text|在postgreSQL中，将执行表扫描。我认为它依赖于实现。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1008022|编辑:请参阅this+link|offset|length|1008023|entityMap|0|LINK|mutability|MUTABLE|url|http://www.oracledba.co.uk/tips/count_speed.htm^0|0|6|9|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|O|8|@]|9|@$D|P|E|Q|1|R]]|A|$]]|$1|F|3|-4|5|6|7|S|8|@]|9|@]|A|$]]]|G|$H|$5|I|J|K|A|$L|M]]]]

In postgreSQL a table scan is performed.
I think it's implementation dependant.

Edit:
See <a href="http://www.oracledba.co.uk/tips/count_speed.htm" rel="nofollow noreferrer">this link</a>

blocks|key|1008080|text|YSE+COUNT函数剂量表扫描，而不是使用count+on+TABLE来获取您可以使用的总行数：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1008081|SELECT
Total_Rows=+SUM(st.row_count)
FROM
sys.dm_db_partition_stats+st
WHERE
object_name(object_id)+=+'TABLENAME'+|code-block|syntax|javascript|1008082|或|1008083|SELECT+sysobjects.[name],+max(sysindexes.[rows])+AS+TableRows
FROM+sysindexes+INNER+JOIN+sysobjects+ON+sysindexes.[id]+=+sysobjects.[id]
WHERE+sysobjects.xtype+=+'U'+and+sysobjects.[name]='tablename'
GROUP+BY+sysobjects.[name]
ORDER+BY+max(rows)+DESC|1008084|获取总计数的另一种方式：http://www.codeproject.com/Tips/58796/Number-of-different-way-to-get-total-no-of-row-fro.aspx|offset|length|1008085|entityMap|0|LINK|mutability|MUTABLE|url|http://www.codeproject.com/Tips/58796/Number-of-different-way-to-get-total-no-of-row-fro.aspx^0|0|0|0|0|C|2L|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|X|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Y|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|Z|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|10|8|@]|9|@$M|11|N|12|1|13]]|A|$]]|$1|O|3|-4|5|6|7|14|8|@]|9|@]|A|$]]]|P|$Q|$5|R|S|T|A|$U|V]]]]

YSE COUNT FUNCTION DOSE TABLE SCAN, rather than using count on table to get total number of row you can use :

<pre><code>SELECT
Total_Rows= SUM(st.row_count)
FROM
sys.dm_db_partition_stats st
WHERE
object_name(object_id) = 'TABLENAME' 
</code></pre>

or 

<pre><code>SELECT sysobjects.[name], max(sysindexes.[rows]) AS TableRows
FROM sysindexes INNER JOIN sysobjects ON sysindexes.[id] = sysobjects.[id]
WHERE sysobjects.xtype = 'U' and sysobjects.[name]='tablename'
GROUP BY sysobjects.[name]
ORDER BY max(rows) DESC
</code></pre>

OTHER WAY TO GET TOTAL COUNT : <a href="http://www.codeproject.com/Tips/58796/Number-of-different-way-to-get-total-no-of-row-fro.aspx" rel="nofollow noreferrer">http://www.codeproject.com/Tips/58796/Number-of-different-way-to-get-total-no-of-row-fro.aspx</a>

blocks|key|1222529|text|这取决于所使用的DBMS。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1222530|如果有索引，则每个表行应该有一个索引行。智能DBMS可能会选择最小的索引并计算索引行数。|1222531|最后，如果表足够小，它可能会计算表行数并绕过索引。|1222532|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|H|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|I|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|J|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|G|$]]

It depends on the DBMS used.

If there is an index, there should be one index row for each table row. A smart DBMS will likely choose the smallest index and count the index rows.

Finally, if the table is small enough, it may count the table rows and bypass the index.

blocks|key|1222576|text|它通常是某种索引扫描，除非表上没有唯一索引。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1222577|奇怪的是，大多数数据库引擎只能通过扫描来计数。它们甚至提供了使用表元数据进行计数的替代解决方案。例如，SQL+Server支持SELECT+rowcnt+FROM+sysindexes+...。然而，这些通常不是100%25准确的。|offset|length|style|CODE|1222578|entityMap^0|0|1R|X|0^^$0|@$1|2|3|4|5|6|7|J|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|K|8|@$D|L|E|M|F|G]]|9|@]|A|$]]|$1|H|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|I|$]]

It is usually some sort of index scan, unless there is no unique index on the table.

Strangely enough, most database engines can only count by scanning. They even provide alternate solutions to count using table metadata. For instance SQL Server supports <code>SELECT rowcnt FROM sysindexes ...</code>. However, these are usually not 100% accurate.

blocks|key|1222631|text|通常，要么执行表扫描，要么执行索引扫描。这主要是因为在支持MVCC的引擎中，不同的事务可以看到不同的行，因此不存在同时对每个人都正确的单个“行数”。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1222632|同样，如果您有一个WHERE子句，那么where条件对于不同的客户端可能是不同的，因此它们会看到不同的数字。|1222633|如果您需要对大型表进行大量计数，请考虑将您自己的计数器存储在不同的表中。具体如何做到这一点完全取决于应用程序。|1222634|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|H|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|I|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|J|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|G|$]]

In general either a table or index scan is performed. This is chiefly because in a MVCC-supporting engine, different transactions could see different rows, so there is no single "row count" which is simultaneously correct for everyone.

Likewise, if you have a WHERE clause, then the where condition could be different for different clients, so they see different numbers.

If you need to do a lot of counts of large tables, consider storing your own counters in a different table. Exactly how you do this is entirely application specific.

blocks|key|1008323|text|这将在很大程度上取决于您正在使用的SQL实现(MS+SQL+Server、MySQL、Oracle、PostgreSQL等)，以及它的优化器有多聪明。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1008324|它也可能取决于查询。例如，使用类似于|1008325|SELECT+COUNT(primary_key)+FROM+table;|code-block|syntax|javascript|1008326|优化器可能意识到不需要扫描表(因为没有使用WHERE进行过滤，也不可能有任何值为NULL)，只需返回表的大小即可。对于更复杂的查询(其中有过滤，或者可能出现NULL)，数据库可能必须扫描表，或者它可以使用索引进行一些优化。|offset|length|style|CODE|1008327|entityMap^0|0|0|0|L|5|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|R|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|S|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|T|8|@$K|U|L|V|M|N]]|9|@]|A|$]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

This will depend very much on which SQL implementation you are using (MS SQL Server, MySQL, Oracle, PostgreSQL etc), and how clever its optimiser is.

It may also depend on the query. For example, with something like

<pre><code>SELECT COUNT(primary_key) FROM table;
</code></pre>

the optimiser may realise that there is no need to scan the table (since there is no filtering with <code>WHERE</code> and no possibility that any values are NULL) and just return the size of the table. With a more complicated query (where there is filtering, or the possibility of NULLs), the database may have to scan the table, or it may be able to do some optimisation with the use of an index.

blocks|key|1008407|text|这显然是依赖于实现的(即不同的关系数据库可能采用不同的策略)和使用依赖(即select+count(*)+from+mytable和select+count(*)+from+mytable+where+myfield+<+somevalue)可能使用不同的方法，即使在同一个DB中。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1008408|如果您正在尝试基于某个已由索引表示的分区来获取计数，则智能数据库将尝试单独使用索引。或者像Foxbase中使用的旧的"rushmore“。|1008409|因此，“这取决于”，但最终，如果没有更好的方法可用，是的，数据库将执行表扫描。|1008410|entityMap^0|11|S|1U|1I|0|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@$9|M|A|N|B|C]|$9|O|A|P|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|Q|8|@]|D|@]|E|$]]|$1|H|3|I|5|6|7|R|8|@]|D|@]|E|$]]|$1|J|3|-4|5|6|7|S|8|@]|D|@]|E|$]]]|K|$]]

This is obviously implementation dependant (i.e. different RDBMS may employ different strategies) and usage dependant (i.e. <code>select count(*) from mytable</code> and <code>select count(*) from mytable where myfield &lt; somevalue</code>) may use different methods even in the same DB.

If you are trying to get the count based on some partitioning that is already expressed by an Index, smart DBs will try to use the index alone. Or something like the old "rushmore" used in Foxbase.

So, "it depends", but at the end of the day, if no better methods are available, yes, the DB will perform a table scan.

I would like to understand how exactly does sql count work. Is it a whole table scan that happens or is it some property of the table that is read. However I feel a table scan would be an overhead in case of huge tables with lots of records.

how does sql count work?

SQL Server

PostgreSQL

数据库

MySQL

我想了解sql count到底是如何工作的。发生的是整个表扫描，还是正在读取的表的某些属性。然而，我觉得在包含大量记录的大型表的情况下，表扫描将是一种开销。

问sql count是如何工作的？
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问sql count是如何工作的？EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问sql count是如何工作的？
EN