问题描述

在 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(*) 时，它接受 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:计算行数的最快方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！