MySQL:计算行数的最快方法 [英] MySQL: Fastest way to count number of rows

问题描述

在MySQL中哪种计数行数的方法应该更快?

Which way to count a number of rows should be faster in MySQL?

此:

SELECT COUNT(*) FROM ... WHERE ...

或者，替代方法:

SELECT 1 FROM ... WHERE ...

// and then count the results with a built-in function, e.g. in PHP mysql_num_rows()

人们会认为第一种方法应该更快，因为在内部确定类似情况时，这显然是数据库领域，而数据库引擎应该比其他任何人都要快.

One would think that the first method should be faster, as this is clearly database territory and the database engine should be faster than anybody else when determining things like this internally.

推荐答案

COUNT(*)时，它接受计数列索引，因此它将是最佳结果.使用 MyISAM 引擎的Mysql实际上存储行数，每次尝试对所有行进行计数时，它都不会对所有行进行计数. (基于主键的列)

When you COUNT(*) it takes in count column indexes, so it will be the best result. Mysql with MyISAM engine actually stores row count, it doensn't count all rows each time you try to count all rows. (based on primary key's column)

使用PHP计数行不是很聪明，因为您必须将数据从mysql发送到php.当您可以在mysql端实现相同的目的时，为什么要这样做呢?

Using PHP to count rows is not very smart, because you have to send data from mysql to php. Why do it when you can achieve the same on the mysql side?

如果COUNT(*)缓慢，则应在查询上运行EXPLAIN，并检查是否确实使用了索引，以及应将索引添加到何处.

If the COUNT(*) is slow, you should run EXPLAIN on the query, and check if indexes are really used, and where should they be added.

以下不是最快的方法，但是在某些情况下，COUNT(*)并不适合-在开始对结果进行分组时，您可能会遇到问题，其中COUNT并没有真正计算所有行.

The following is not the fastest way, but there is a case, where COUNT(*) doesn't really fit - when you start grouping results, you can run into problem, where COUNT doesn't really count all rows.

解决方案是SQL_CALC_FOUND_ROWS.通常在选择行但仍需要知道总行数(例如，用于分页)时使用. 选择数据行时，只需在SELECT之后附加SQL_CALC_FOUND_ROWS关键字:

The solution is SQL_CALC_FOUND_ROWS. This is usually used when you are selecting rows but still need to know the total row count (for example, for paging). When you select data rows, just append the SQL_CALC_FOUND_ROWS keyword after SELECT:

SELECT SQL_CALC_FOUND_ROWS [needed fields or *] FROM table LIMIT 20 OFFSET 0;

选择所需的行后，可以通过以下单个查询获取计数:

After you have selected needed rows, you can get the count with this single query:

SELECT FOUND_ROWS();

FOUND_ROWS()必须在数据选择查询之后立即调用.

FOUND_ROWS() has to be called immediately after the data selecting query.

最后，实际上所有内容都取决于您有多少个条目以及WHERE语句中的内容.当有很多行(数万，数百万甚至更多)时，您应该真正注意索引的使用方式.

In conclusion, everything actually comes down to how many entries you have and what is in the WHERE statement. You should really pay attention on how indexes are being used, when there are lots of rows (tens of thousands, millions, and up).

这篇关于MySQL:计算行数的最快方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！