MySQL在处理非常大的表时的计数性能

Question

我有一个包含一亿多行记录的Innodb表格。

我需要知道外键等于1的行是否超过5000行。并不需要知道确切数量。

我进行了一些测试： SELECT COUNT(*) FROM table WHERE fk = 1 => 16秒
SELECT COUNT(*) FROM table WHERE fk = 1 LIMIT 5000 => 16秒
SELECT primary FROM table WHERE fk = 1 => 0.6秒
即使处理时间更长，也可以承受15.4秒的负载！

请问是否有更好的方法？

谢谢。

CREATE TABLE table ( pk bigint(20) NOT NULL AUTO_INCREMENT,
fk tinyint(3) unsigned DEFAULT '0', 
PRIMARY KEY (pk), KEY idx_fk (fk) USING BTREE ) 
ENGINE=InnoDB AUTO_INCREMENT=100380914 DEFAULT CHARSET=latin1

数据库相关：

'have_innodb', 'YES' 'ignore_builtin_innodb', 'OFF' 'innodb_adaptive_hash_index', 'ON'    
'innodb_additional_mem_pool_size', '20971520' 'innodb_autoextend_increment', '8' 
'innodb_autoinc_lock_mode', '1' 'innodb_buffer_pool_size', '25769803776' 
'innodb_checksums', 'ON' 'innodb_commit_concurrency', '0',
'innodb_concurrency_tickets', '500' 'innodb_data_file_path',
'ibdata1:10M:autoextend' 'innodb_data_home_dir', '', 'innodb_doublewrite', 'ON'     
'innodb_fast_shutdown', '1' 'innodb_file_io_threads', '4' 
'innodb_file_per_table', 'OFF', 'innodb_flush_log_at_trx_commit', '1' 
'innodb_flush_method', '' 'innodb_force_recovery', '0' 'innodb_lock_wait_timeout', '50' 
'innodb_locks_unsafe_for_binlog', 'OFF' 'innodb_log_buffer_size', '8388608' 
'innodb_log_file_size', '26214400' 'innodb_log_files_in_group', '2' 
'innodb_log_group_home_dir', './' 'innodb_max_dirty_pages_pct', '90'     
'innodb_max_purge_lag', '0' 'innodb_mirrored_log_groups', '1' 'innodb_open_files', 
'300' 'innodb_rollback_on_timeout', 'OFF' 'innodb_stats_on_metadata', 'ON' 
'innodb_support_xa', 'ON' 'innodb_sync_spin_loops', '20' 'innodb_table_locks', 'ON' 
'innodb_thread_concurrency', '8' 'innodb_thread_sleep_delay', '10000'      
'innodb_use_legacy_cardinality_algorithm', 'ON'

更新 '15： 我一直使用同样的方法，现在处理的是6亿行数据和每天640,000行新数据。它仍然运行良好。

- hotips

---

如果您在COUNT()中选择了一列，计数将更快，例如：SELECT COUNT(fk) FROM table WHERE fk = 1。 - ClydeFrog

根据手册，@ClydeFrog：真的吗？如果SELECT从一个表中检索数据，没有检索其他列，并且没有WHERE子句，*COUNT(*)被优化为返回非常快*。确实，您提供链接的博客表明COUNT(*)比COUNT(column)更快。 - eggyal

如果现在测试并且我花费的时间相同：16秒 - hotips

SELECT COUNT(*) FROM table WHERE fk = 1 需要 16 秒？你在 fk 上有索引吗？ - ypercubeᵀᴹ

你是否进行过任何InnoDB配置优化，还是直接使用默认设置运行？ - N.B.

Answer 1

你似乎对实际计数不感兴趣，那就试试这个：

SELECT 1 FROM table WHERE fk = 1 LIMIT 5000, 1

如果返回一行记录，则表示你有5000条或更多的记录。我假设 fk 列被索引了。

- Salman A

---

这很有趣。你测试过这种解决方案吗？它的性能如何？ - ypercubeᵀᴹ

@ypercube：我在有300万行的虚拟数据上进行了测试，没有在fk上建立索引，并且始终在<1秒内得到结果（第一次运行约为3秒）。这个查询非常依赖于数据的分布，所以你的情况可能会有所不同。 - Salman A

以下是查询的工作原理：从包含fk的索引（或数据，如果没有索引）中读取5001个“行”。如果这些行恰好在RAM中，则这是一个相当快的CPU任务。如果这些行在磁盘上，则可能需要更长时间；但是，它们将被带入缓存，准备好供您的下一个查询使用。在后一种情况下，检查的成本是“免费”的。 - Rick James

Answer 2

计数器表或其他缓存机制是解决方案：

InnoDB不会在表中保留行的内部计数，因为并发事务可能同时“看到”不同数量的行。要处理SELECT COUNT(*) FROM t语句，InnoDB会扫描表的索引，如果索引没有完全在缓冲池中，则需要一些时间。如果您的表不经常更改，则使用MySQL查询缓存是一个很好的解决方案。要获得快速计数，您必须使用自己创建的计数器表，并让应用程序根据其所做的插入和删除更新它。如果近似行计数足够，可以使用SHOW TABLE STATUS。请参见第14.3.14.1节，“InnoDB性能调整技巧”。

• http://dev.mysql.com/doc/refman/5.5/en/innodb-restrictions.html

- scriptin

---

我有一个where条件 => 显示表状态没有帮助。我每天有40万个新行...我很幸运！ - hotips

@si2w 我的意思不是建议你使用 SHOW TABLE STATUS。我是在谈论表计数和缓存。 - scriptin

Answer 3

我还需要再添加一个回答——到目前为止，我对评论和回答有很多更正/补充。

对于MyISAM，没有WHERE子句的SELECT COUNT(*)是直接计算的，速度非常快。其他情况（包括问题中提到的InnoDB）必须通过数据BTree或索引BTree计数以获得答案。因此，我们需要看一下要计数的量。

InnoDB缓存数据和索引块（每个16KB）。但是，当表的数据或索引BTree大于innodb_buffer_pool_size时，您保证会命中磁盘。命中磁盘几乎始终是任何SQL的最慢部分。

涉及查询缓存时，通常会导致查询时间约为1毫秒；这似乎对所引用的任何时间都不是问题。因此，我不会过多强调它。

但是……连续运行相同的查询通常会出现：

第一次运行：10秒

第二次运行：1秒

这表明第一次运行需要从磁盘获取大部分块，而第二次在RAM（buffer_pool）中找到了所有块。我怀疑某些所引用的时间可能是假的，因为没有意识到这个缓存问题（16秒与0.6秒之间的差异可能可以解释为这个原因）。

我将坚持“磁盘命中”或“需要触摸的块”作为衡量SQL速度的真正指标。

COUNT（x）在计数之前会检查x是否为IS NOT NULL。这添加了一点点处理，但不会改变磁盘命中次数。

提供的表具有PK和第二个列。我想知道那是真实的表吗？这很重要——

如果优化器决定读取数据-即按主键顺序扫描-它将读取数据BTree，该BTree通常比辅助索引BTree宽得多（但在这个无聊的例子中不是这样）。

如果优化器决定读取二级索引（但不需要排序），将需要触摸的块较少。因此更快。

关于原始查询的评论：

SELECT COUNT(*) FROM table WHERE fk = 1 => 16 seconds
    -- INDEX(fk) is optimal, but see below
SELECT COUNT(*) FROM table WHERE fk = 1 LIMIT 5000 => 16 seconds
    -- the LIMIT does nothing, since there is only one row in the result
SELECT primary FROM table WHERE fk = 1 => 0.6 seconds
    -- Again INDEX(fk), but see below

WHERE fk = 1条件的查询最好使用INDEX(fk)索引，如果可能的话，尽量避免使用INDEX(fk, ...)索引。 需要注意的是，在InnoDB中，每个辅助索引都包含主键的副本，因此INDEX(fk)实际上等价于INDEX(fk, primary)。 因此，第三个查询可以使用该索引作为"覆盖索引"而不需要触及数据。

如果表确实只有两列，那么次要索引B-Tree可能比数据B-Tree更宽，但在现实情况下，辅助索引将会更小。 因此，索引扫描将比表扫描更快（需要触及更少的块）。

第三个查询还返回了大量结果；这可能会导致查询需要很长时间 - 但是不会计入报告的“时间”；这是网络时间，不是查询时间。

innodb_buffer_pool_size = 25,769,803,776我猜想表及其从FK提取的辅助索引各自占用约3-4GB的空间。 因此，任何计时首先可能需要加载很多内容。 然后，第二次运行将完全缓存。(当然，我不知道有多少行具有fk=1; 可能比所有行都少?)

但是...对于600M行，表及其索引每个都接近25GB的缓冲池。 因此，这一天可能很快就会到来，它将成为I/O限制 - 这将让您希望回到16（或25）秒; 然而，您将无法这样做。然后我们可以考虑其他替代方案来执行COUNT查询。

SELECT 1 FROM tbl WHERE fk = 1 LIMIT 5000,1 - 让我们分析一下。 它将扫描索引，但在扫描5000行后就会停止。 如果您只需要“超过5K”，那么这是最好的方法。 它将始终保持快速（仅触及十几个块），无论表中的总行数如何。（它仍然受系统的buffer_pool_size和缓存特性的影响。 但是，十几个块不需要很长时间，即使是在一个冷缓存环境下。）

MariaDB的LIMIT ROWS_EXAMINED也值得研究。 如果没有这个，您可以执行

SELECT COUNT(*) AS count_if_less_than_5K
    FROM ( SELECT 1 FROM tbl WHERE fk = 1 LIMIT 5000 );

它可能比将行交付给客户端更快；它将在临时表中内部收集行，但仅交付COUNT。

一件事情：每天插入640K行数据 - 这接近使用HDD（而不是SDD）上的当前设置进行MySQL单行INSERTs的限制。如果您需要讨论潜在的灾难，请开启另一个问题。

底线：

• 确保避免查询缓存。（通过使用SQL_NO_CACHE或关闭QC）
• 运行任何计时查询两次；使用第二个时间。
• 了解涉及的BTree（s）的结构和大小。
• 除非需要进行空值检查，否则不要使用COUNT(x)。
• 不要使用PHP的mysql_*接口；切换到mysqli_*或PDO。

- Rick James

Answer 4

这是一个老问题，但我遇到了相同的问题，也许这能帮助某些人：对于400万条记录，COUNT查询需要超过20秒的时间。因此，在我的情况下，当我添加了一个简单的主键过滤条件后，它变得更快，只需要4秒钟。因此最终查询如下：

SELECT COUNT(*) FROM Table
WHERE PK > 0;

而在我的情况下，PK 是 INT 类型。

- O. V.

---

实际上，这个答案让我想到了一个类似的问题的解决方案。而简单的原因是：在InnoDB中，没有WHERE的SELECT COUNT(*)必须进行索引扫描。优化器会选择“最小”的索引，而在您的情况下是主键。 - kcm

Answer 5

如果您正在使用PHP，您可以在从SELECT primary FROM table WHERE fk = 1 => 0.6 seconds获得的结果上执行mysql_num_rows，我认为这将是有效的。

但这取决于您使用的服务器端语言。

- nischayn22

---

使用最新的官方驅動程式進行C#編程。我認為該驅動程式可以提供一個指針來訪問數據，因此我可以在不檢索整個數據集的情況下獲取行數。 - hotips

@si2w 从服务器获取数据有两种方式：mysql_store_result()将整个结果集发送到客户端，您可以对其进行计数；mysql_use_result()仅在需要时发送数据，但必须在发出其他命令之前获取所有数据。 - glglgl

无法确认这个答案。在我的情况下，COUNT() 持续了 1.6 秒，并且使用通常的 SELECT mysql_num_rows 不检索数据时为 1.8 秒。 - mgutt

这是一个非常糟糕的想法，如果你有一个大表格，比如 GB 级别的数据，标准的 PHP 设置会立即耗尽内存！ - Ben Osborne

Answer 6

如果您不想知道行数，只是想测试COUNT是否与某个值相等，您可以使用以下标准脚本：

SELECT 'X'
FROM mytable
WHERE myfield='A'
HAVING COUNT(*) >5

这将根据条件是否满足返回一行或者不返回任何行。

此脚本符合ANSI标准，可以完全运行而无需评估COUNT(*)的完整值。如果MySQL实现了在满足某些条件后停止评估行的优化（我真的希望它这样做），那么您将获得性能提升。不幸的是，我无法测试此行为，因为我没有可用的大型MySQL数据库。如果您进行此测试，请在此处分享结果 :)

- Gerardo Lima

---

比正常计数慢... 感谢您提供的想法！ - hotips

感谢您发布结果，@si2w！然而，令人失望的是，MySQL引擎没有实现这个（简单？）优化。 - Gerardo Lima

Answer 7

最快的使用C#查询前X行的方法是通过计算行数。

我的应用程序按批处理数据。两个批次之间的时间取决于需要处理的行数。

SELECT pk FROM table WHERE fk = 1 LIMIT X

我在0.9秒内得到了结果。

感谢大家的思路！

- hotips

---

我不明白你是如何计算行数的，请加上代码吧？ - nischayn22

我的应用程序正在批处理数据。两个批次之间的时间取决于需要处理的行数。 - hotips

这种方法的潜在问题是必须将所有 X 主键传输到客户端。在某些情况下，网络时间是相当显著的。 - Rick James

例如：如果限制是5000万。在您的情况下，将发送5000万个主键ID到代码中。我们需要将其存储在变量中，这将消耗RAM内存。然后，如果此过程同时发生在多个线程中，则可能会耗尽内存。有任何更新的新解决方案吗？ - vinieth