在非常大的表mysql的计数性能(Mysql count performance on very b

我有InnoDB的超过100个百万行的表。

我知道，如果有超过5000行，其中外键= 1。我不需要确切的数字。

我做了一些测试：

=>16秒
=>16秒
=>0.6秒

我将有一个更大的网络和处理时间，但它可以是15.4秒的过载！

你有一个更好的主意吗？

谢谢

编辑：增加了OP的相关评论]

我想SELECT COUNT SQL_NO_CACHE（FK）FROM表WHERE FK = 1，但它采取25秒

Mysql的进行了调整，对于InnoDB与MySQL调谐器。

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

DB的东西：

'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：我用同样的方法到现在拥有600个百万行和每天640万个新行。它仍然工作正常。

Answer 1:

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

InnoDB的不守行的内部计数的表，因为并发事务可能会“看到”的行不同数量在同一时间。为了处理一个SELECT COUNT（*）FROM吨语句时，InnoDB扫描表，这需要一些时间，如果索引不是完全在缓冲池的索引。如果您的表不经常改变，使用MySQL查询缓存是一个很好的解决方案。要获得快速计数，你必须使用你自己创建一个计数表，让您的应用程序根据插入更新和删除它。如果一个近似的行数是足够的，可用于SHOW TABLE STATUS。见第14.3.14.1，“InnoDB的性能优化技巧” 。

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

Answer 2:

你似乎并不感兴趣的实际数量，使得试试这个：

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

如果返回的行，你有5000多条记录。我相信在fk列索引。

Answer 3:

我要补充另外一个答案 - 我有很多更正/添加到迄今为止的意见和解答。

对于MyISAM， SELECT COUNT(*)没有WHERE是航位推算-速度非常快。所有其他情况下（包括在问题InnoDB的）必须通过两种数据的B树或索引的B树指望得到答案。因此，我们需要看到多少通过计数。

InnoDB的缓存的数据和索引块（16KB每个）。但是，当表的数据或索引B树比我的大innodb_buffer_pool_size ，保证您打盘。击中盘几乎总是任意SQL最慢的部分。

查询缓存，涉及的情况下，通常会导致在大约1毫秒的查询时间; 这似乎并不符合任何引用了定时的问题。所以，我不会纠缠于它。

但是...... 连续办刊同一查询两次会经常表现出：

首先运行：10秒
第二轮：1秒

这是对症具有抓取最从磁盘中块的第一次运行的，而第二个发现这一切在RAM（在BUFFER_POOL）。我怀疑，一些上市的时机，因为没有意识到这个缓存的问题都是伪造的。（16秒VS 0.6秒可能被此进行说明。）

我会喋喋不休“磁盘命中”或真正的指标，其中SQL更快“需要被感动块”。

COUNT(x)检查， x为IS NOT NULL清点之前。这增加了处理的微小量，但不会改变磁盘命中数。

递上表具有PK和第二列。我不知道这是否是真正的表？它的确与众不同 -

如果优化决定来读取数据 -即，扫描PRIMARY KEY顺序-它会被读取数据B树，这通常是（但不是在这个例子跛脚）比仲索引B树宽得多。
如果优化确定需要读取一个辅助索引（但不是需要做一个排序），将会有更少的块触摸。因此，速度更快。

原始查询的评论：

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中，每个二级指标包含了PK的副本。即， INDEX(fk)实际上是INDEX(fk, primary) 。因此，第三查询可以使用它作为“覆盖”，而不是需要触摸的数据。

如果表是真正公正的两列则可能是二次B树索引会比数据B树胖。但在现实表，次级索引会更小。因此索引扫描会更快（更少的块到触摸），比表扫描。

第三个查询还提供了大量的ResultSet; 这可能导致查询需要很长的时间- 但它不会被包含在所列出的“时间”; 这是网络时代，而不是查询的时间。

innodb_buffer_pool_size = 25,769,803,776我猜想，表和它的二级索引（从FK）分别大约3-4GB。因此，任何时间可能首先需要加载很多东西。然后， 第二次运行将完全缓存。（当然，我不知道有多少行已fk=1 ;大概少于所有行？）

但是 ......在600M行，表和它的指数均接近25GB BUFFER_POOL。因此，也许有一天很快，它成为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 );

它可能比提供行给客户更快; 它必须在内部收集在一个tmp目录表中的行，但只提供了COUNT 。

旁注：每天插入640K行-这个方法对于单行限制INSERTs在MySQL上一个HDD（未SDD）当前设置。如果您需要讨论潜在的灾难，打开另外一个问题。

底线：

一定要避免查询缓存。（通过使用SQL_NO_CACHE或转动QC关闭）
运行的任何定时查询两次; 使用第二次。
理解所涉及的B树（一个或多个）的结构和尺寸。
不要使用COUNT(x)除非你需要空校验。
不要使用PHP的mysql_*接口; 切换到mysqli_*或PDO 。

Answer 4:

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

但取决于你使用的是什么服务器端语言

Answer 5:

最后，最快的是查询使用C＃和计数的行数第X行。

我的应用程序被分批处理数据。两批之间的时间量取决于谁需要处理的行数

SELECT pk FROM table WHERE fk = 1 LIMIT X

我得到了0.9秒的结果。

感谢所有为您的想法！

Answer 6:

如果你没兴趣知道的行数，你只是想测试对一些价值计数，可以波纹管使用标准的脚本：

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

这将返回一个单行或没有行可言，如果条件得到满足不同。

这个脚本是ANSI标准，可以不计算COUNT（*）的完整的价值得到充分执行。如果MySQL实现优化停止评估行满足某些条件后（我真的希望它），然后你会得到一个性能改进。不幸的是，因为我不会有大的MySQL数据库可我不能对此进行测试自己的行为。如果你做这个测试，请在这里分享的结果:)

文章来源: Mysql count performance on very big tables