Mysql在非常大的表上计数性能 [英] Mysql count performance on very big tables

问题描述

我有一个在Innodb中有超过1亿行的表。

我必须知道外键是否超过5000行。
我不需要确切的数字。

我做了一些测试：

SELECT COUNT（*）FROM table WHERE fk = 1 => 16秒
$ db $ b SELECT COUNT（*）FROM table WHERE fk = 1 LIMIT 5000 => 16 seconds

SELECT primary FROM table WHERE fk = 1 => 0.6secondsÚ

我会拥有更大的网络和治疗时间，

感谢

b

编辑：[添加OP的相关评论]

我试过SELECT SQL_NO_CACHE COUNT（fk）FROM table WHERE fk = 1，但需要25秒

Mysql通过Mysql调试器调整为Innodb。

 code> CREATE TABLE table（pk bigint（20）NOT NULL AUTO_INCREMENT，
 fk tinyint（3）unsigned DEFAULT'0'，
 PRIMARY KEY（pk），KEY idx_fk ）
 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_bin_''''innodb_log_group_home_dir'，'。/''innodb_max_dirty_pages_pct'，'''innodb_log_files_in_group'，' '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' 
  

更新'15：
我使用相同的方法到现在有6亿行和640 000新行每天。

解决方案

最后，最快的是使用C＃查询第一个x行并计算行数。

SELECT pk FROM table WHERE fk = 1 LIMIT x

我的结果是0.9秒！非常感谢！

感谢您的建议！

I have a table with more than 100 millions rows in Innodb.

I have to know if there is more than 5000 rows where the foreign key = 1. I don't need the exact number.

I made some testing :

SELECT COUNT(*) FROM table WHERE fk = 1 => 16 seconds
SELECT COUNT(*) FROM table WHERE fk = 1 LIMIT 5000 => 16 seconds
SELECT primary FROM table WHERE fk = 1 => 0.6 seconds

I will have a bigger network and treatment time but it can be an overload of 15.4 seconds !

Do you have a better idea ?

Thanks

Edit: [Added OP's relevant comments]

I tried SELECT SQL_NO_CACHE COUNT(fk) FROM table WHERE fk = 1 but it took 25 seconds

Mysql was tuned for Innodb with Mysql Tuner.

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 Stuff:

'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'

Update '15: I used the same method up to now with 600 millions rows and 640 000 new rows per day. It's still working fine.

解决方案

Finally the fastest was to query the first x rows using C# and counting the rows number.

SELECT pk FROM table WHERE fk = 1 LIMIT x

I had the result in 0.9 seconds ! GREAT !

Thanks all for your ideas !

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