MySQL计数行性能 [英] MySQL count rows performance

问题描述

对于 InnoDB 存储，最好通过记录总数来计算

• 在

  上使用mysql_num_rows

  select * from tbl where pk = 1
  

或通过

• 获取数组并从中获取总计"值

  select count(*) as total from tbl where pk = 1
  

?

解决方案

绝对是后者.它可以直接从PK的索引中获取值，而前者几乎可以肯定需要进行表扫描(除非每个单列是索引的一部分；即使如此，它也必须从所有位置获取值)索引).然后，根据您连接数据库的方式，有大量的数据传输只是为了计数.

explain 可以在这里提供帮助.在这种情况下，它会告诉您选择已被优化.

For an InnoDB storage is it better to count the total number of records by

• Using mysql_num_rows on

  select * from tbl where pk = 1
  

or by

• fetching the array and retrieve the "total" value from

  select count(*) as total from tbl where pk = 1
  

?

解决方案

Absolutely the latter. It can grab the value direct from the PK's index, whereas the former almost certainly requires a table scan (unless every single column is part of an index; and even then, it has to grab values from all over the indexes). Then depending on how you're connecting to the DB, there's a large amount of data transit just to get a count.

explain can help here. In this case, it'll tell you that the select is optimized away.

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