MySQL：计算行数的最快方法

MySQL: Fastest way to count number of rows

提问by Franz

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

在 MySQL 中，哪种计算行数的方法应该更快？

This:

这个：

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

Or, the alternative:

或者，替代方案：

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.

有人会认为第一种方法应该更快，因为这显然是数据库领域，并且在内部确定此类事情时，数据库引擎应该比其他任何人都快。

回答by Mārti?? Briedis

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

当您COUNT(*)采用计数列索引时，这将是最好的结果。带有MyISAM引擎的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?

使用 PHP 来计算行数不是很聪明，因为你必须从 mysql 向 php 发送数据。当你可以在 mysql 端实现相同的时候，为什么要这样做？

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

如果COUNT(*)速度很慢，您应该运行EXPLAIN查询，并检查是否真正使用了索引，以及应该在哪里添加索引。

---

---

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

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

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_ROWSkeyword after SELECT:

解决办法是SQL_CALC_FOUND_ROWS。这通常在您选择行但仍需要知道总行数（例如，用于分页）时使用。选择数据行时，只需SQL_CALC_FOUND_ROWS在 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()has to be called immediately after the data selecting query.

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

---

---

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).

总之，一切实际上都归结为您拥有多少条目以及 WHERE 语句中的内容。当有很多行（数万、数百万甚至更多）时，您应该真正注意索引的使用方式。

回答by MagMax

After speaking with my team-mates, Ricardo told us that the faster way is:

在和我的队友交谈后，Ricardo 告诉我们更快的方法是：

show table status like '<TABLE NAME>' \G

But you have to remember that the result may not be exact.

但您必须记住，结果可能并不准确。

You can use it from command line too:

您也可以从命令行使用它：

$ mysqlshow --status <DATABASE> <TABLE NAME>

More information: http://dev.mysql.com/doc/refman/5.7/en/show-table-status.html

更多信息：http: //dev.mysql.com/doc/refman/5.7/en/show-table-status.html

And you can find a complete discussion at mysqlperformanceblog

您可以在mysqlperformanceblog 上找到完整的讨论

回答by Dan Horvat

Great question, great answers. Here's a quick way to echo the results if anyone is reading this page and missing that part:

很好的问题，很好的答案。如果有人正在阅读此页面并遗漏了该部分，这里有一种快速回显结果的方法：

$counter = mysql_query("SELECT COUNT(*) AS id FROM table");
$num = mysql_fetch_array($counter);
$count = $num["id"];
echo("$count");

回答by lepe

This query (which is similar to what bayuah posted) shows a nice summary of all tables count inside a database: (simplified version of stored procedure by Ivan Cachicatariwhich I highly recommend).

这个查询（类似于bayuah 发布的内容）显示了数据库中所有表计数的很好的摘要：（我强烈推荐的 Ivan Cachicatari的存储过程的简化版本）。

SELECT TABLE_NAME AS 'Table Name', TABLE_ROWS AS 'Rows' FROM information_schema.TABLES WHERE TABLES.TABLE_SCHEMA = '`YOURDBNAME`' AND TABLES.TABLE_TYPE = 'BASE TABLE'; 

Example:

例子：

+-----------------+---------+
| Table Name      | Rows    |
+-----------------+---------+
| some_table      |   10278 |
| other_table     |     995 |

回答by adarshr

I've always understood that the below will give me the fastest response times.

我一直都明白，下面的内容会给我最快的响应时间。

SELECT COUNT(1) FROM ... WHERE ...

回答by SamuelCarreira

I did some benchmarksto compare the execution time of COUNT(*)vs COUNT(id)(id is the primary key of the table - indexed).

我做了一些基准测试来比较COUNT(*)vs的执行时间COUNT(id)（id 是表的主键 - 索引）。

Number of trials: 10 * 1000 queries

试验次数：10 * 1000 次查询

Results: COUNT(*)is faster 7%

结果： COUNT(*)快了 7%

VIEW GRAPH: benchmarkgraph

查看图表：基准图

My advice is to use: SELECT COUNT(*) FROM table

我的建议是使用： SELECT COUNT(*) FROM table

回答by Alex Rashkov

If you need to get the count of the entire result set you can take following approach:

如果需要获取整个结果集的计数，可以采用以下方法：

SELECT SQL_CALC_FOUND_ROWS * FROM table_name LIMIT 5;
SELECT FOUND_ROWS();

This isn't normally faster than using COUNTalbeit one might think the opposite is the case because it's doing the calculation internally and doesn't send the data back to the user thus the performance improvement is suspected.

这通常不会比使用更快，COUNT尽管人们可能认为情况正好相反，因为它在内部进行计算并且不会将数据发送回用户，因此怀疑性能改进。

Doing these two queries is good for pagination for getting totals but not particularly for using WHEREclauses.

执行这两个查询有利于分页以获取总数，但不适用于使用WHERE子句。

回答by bayuah

Try this:

尝试这个：

SELECT
    table_rows "Rows Count"
FROM
    information_schema.tables
WHERE
    table_name="Table_Name"
AND
    table_schema="Database_Name";

回答by sky-dev

Perhaps you may want to consider doing a SELECT max(Id) - min(Id) + 1. This will only work if your Ids are sequential and rows are not deleted. It is however very fast.

也许您可能要考虑做一个SELECT max(Id) - min(Id) + 1. 这仅在您的 Id 是连续的且行未被删除时才有效。然而，它非常快。

回答by ssrp

EXPLAIN SELECT id FROM ....did the trick for me. and I could see the number of rows under rowscolumn of the result.

EXPLAIN SELECT id FROM ....帮我解决了这个问题。我可以看到rows结果列下的行数。