[SQL] 매우 큰 테이블의 성능을 계산 MYSQL

매우 큰 테이블의 성능을 계산 MYSQL

나는 이노 디비에 100 개 이상의 수백만 행이있는 테이블이있다.

나는 5000 개 이상의 행이 있는지 알고있는 경우 외래 키 = 1. 나는 정확한 번호가 필요하지 않습니다.

나는 몇 가지 테스트를했다 :

테이블 SELECT COUNT (*) WHERE FK = 1 => 16초 테이블 SELECT COUNT (*) WHERE FK = 1 LIMIT 5000 => 16초 테이블 기본 SELECT WHERE FK = 1 => 0.6 초

나는 더 큰 네트워크와 처리 시간이있을 것이다 그러나 15.4 초 과부하 될 수 있습니다!

당신은 더 좋은 생각을 가지고 있습니까?

감사

편집 : [추가 OP의 관련 코멘트]

나는 테이블에 FK = 1 SELECT SQL_NO_CACHE의 COUNT (FK)을 시도했지만 25 초 걸렸습니다

MySQL은 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 000 새 행으로 지금까지 같은 방법을 사용했다. 그것은 여전히 ​​잘 작동합니다.

해결법

1. ==============================

   1.당신은 지금이 시도해 실제 계산에 관심이하지 않는 것 :

   당신은 지금이 시도해 실제 계산에 관심이하지 않는 것 :

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

   행이 반환되는 경우에는 5000 개 더 많은 기록을 가지고있다. I는 FK 컬럼 인덱싱 추정.

2. ==============================

   2.카운터 테이블 또는 다른 캐싱 메커니즘은 솔루션입니다 :

   카운터 테이블 또는 다른 캐싱 메커니즘은 솔루션입니다 :

3. ==============================

   3.내가 다른 대답을 추가 해 - 나는 지금까지의 의견과 답변에 많은 수정 / 추가가 있습니다.

   내가 다른 대답을 추가 해 - 나는 지금까지의 의견과 답변에 많은 수정 / 추가가 있습니다.

   의 MyISAM를 들면, WHERE없이 SELECT COUNT (*) 죽은 - 간주 - 매우 빠르게. 다른 모든 상황은 답을 얻기 위해 두 데이터의 BTREE 또는 인덱스의 BTREE을 통해 계산해야한다 (질문에 이노 포함). 그래서 우리는을 통해 계산하는 방법을 많이 볼 필요가있다.

   이노 데이터 및 인덱스 블록 (16킬로바이트 각) 캐시. 테이블의 데이터 또는 인덱스가 BTREE는 innodb_buffer_pool_size보다 클 때, 당신은 디스크를 칠 보장됩니다. 디스크를 명중 거의 항상 SQL의 가장 느린 부분이다.

   쿼리 캐시가 포함 된 경우, 일반적으로 밀리 초 1 약의 쿼리 시간에 결과; 이 인용 한 타이밍의에 문제가 될 것 같지 않습니다. 나는 그것에 연연하지 않습니다 그래서.

   구입 ... 두 번 연속 같은 쿼리를 실행하는 것입니다 종종 전시 :

   두 번째는 모든 RAM합니다 (BUFFER_POOL)에서 발견하면서는 디스크에서 블록의 대부분을 가져 갖는 첫 번째 실행의 증상이다. 나는 나와 타이밍 중 일부는이 때문에 캐싱 문제를 실현하지 않는 가짜가 있다고 생각한다. (0.6 초 16 대 초의이 설명 될 수있다.)

   나는 "디스크 히트"또는 SQL 빠르게로되어있는 실제 통계로 "감동 될 필요가 블록"에 하프 것입니다.

   COUNT (x)의 검사 전에 집계 IS NOT NULL위한 X. 이 처리의 작은 금액을 추가하지만 디스크 히트 수를 변경하지 않습니다.

   proffered 테이블은 PK와 두 번째 열을 갖는다. 그게 진짜 테이블 궁금해? 그것은 차이를 만드는 -

   원래의 질의에 대한 의견 :

   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 개 돌려서 (FK, ...), 바람직하게는 단지 INDEX (FK). 이노에서, 각 보조 인덱스는 PK의 복사본이 들어 있습니다. 즉 INDEX (FK)을 효과적으로 INDEX (FK 차)된다. 따라서, 제 3 쿼리는 "커버"와 데이터를 터치 할 필요가 없습니다로 그것을 사용할 수 있습니다.

   테이블이 진정 경우 단지 두 개의 열은 아마도 보조 인덱스는 BTREE 데이터 BTREE보다 오르게 될 것입니다. 그러나 실제 테이블에서 보조 인덱스는 작아집니다. 따라서 인덱스 스캔은 테이블 스캔보다 빨리 (적은 블록 만지는) 일 것이다.

   세 번째 쿼리는 큰 결과 집합을 제공한다; 시간이 오래 걸릴 수있는 쿼리가 발생할 수 있습니다 -하지만 그것은 인용 "시간"에 포함되지 않습니다; 그것은 네트워크 시간이 아닌 쿼리 시간입니다.

   innodb_buffer_pool_size = 25,769,803,776 나는 테이블합니다 (FK)에서의 보조 인덱스가 3~4기가바이트에 대한 각 있다고 생각합니다. 그래서, 어떤 타이밍 먼저 물건을 많이로드해야 할 수도 있습니다. 그런 다음 두 번째 실행은 완전히 캐시 될 것이다. (물론, 나는 FK = 1 얼마나 많은 행 모른다;? 아마도 덜 모두보다 행)

   하지만 ... 600m의 행에서 테이블 및 인덱스는 25기가바이트의 BUFFER_POOL 접근 각각. 그래서 하루는 내가이되는 것이 곧 올 수 / O 바인딩 - 이것은 당신이 16 (또는 25) 초에 돌아 가야 할 것; 아직 당신은 할 수 없습니다. 우리는 다음 COUNT을하고 대안에 대해 이야기 할 수 있습니다.

   하자이 분석 - FK = 1 LIMIT 5000,1은 TBL에서 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 행 -이 HDD (안 SDD)에 대한 현재 설정으로 MySQL은 단일 행 INSERT들에 대한 제한에 접근한다. 당신이 잠재적 인 재앙을 논의해야하는 경우, 다른 질문을 엽니 다.

   결론 :

4. ==============================

   4.당신이 PHP를 사용하는 경우는 FK = 1 => 0.6 초, 나는 그 효율적입니다 생각 당신은 테이블에서 차 SELECT에서 얻은 결과에 mysql_num_rows도 할 수 있었다.

   당신이 PHP를 사용하는 경우는 FK = 1 => 0.6 초, 나는 그 효율적입니다 생각 당신은 테이블에서 차 SELECT에서 얻은 결과에 mysql_num_rows도 할 수 있었다.

   그러나 당신이 사용하고있는 서버 측 언어에 따라 달라집니다

5. ==============================

   5.마지막으로 가장 빠른 C #을 사용하고, 행 번호를 카운트 최초의 X 행을 조회하는 것이 었습니다.

   마지막으로 가장 빠른 C #을 사용하고, 행 번호를 카운트 최초의 X 행을 조회하는 것이 었습니다.

   내 응용 프로그램 배치로 데이터를 처리한다. 두 배치 사이의 시간은 처리 될 필요가 행수를 따라되고

   SELECT pk FROM table WHERE fk = 1 LIMIT X
   

   나는 0.9 초에 결과를 얻었다.

   감사합니다 당신의 아이디어에 대한 모든!

6. ==============================

   6.당신이 행의 수를 알고 관심이있어, 당신은 단지 몇 가지 값에 대한 COUNT를 테스트하려면 표준 스크립트 울부 짖는 소리를 사용할 수 있습니다 :

   당신이 행의 수를 알고 관심이있어, 당신은 단지 몇 가지 값에 대한 COUNT를 테스트하려면 표준 스크립트 울부 짖는 소리를 사용할 수 있습니다 :

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

   이 조건이 충족되는 경우 따라, 모든 하나 하나의 행 또는 전혀 행을 반환합니다.

   이 스크립트는 ANSI의 준수와 완전히 COUNT (*)의 전체 가치를 평가하지 않고 실행할 수 있습니다. 몇 가지 조건이 충족 된 후에 MySQL은 (내가 정말 않는 희망) 행을 평가 중지 최적화를 구현한다면, 당신은 성능 향상을 얻을 수 있습니다. 나는 큰 MySQL 데이터베이스를 사용할 수 없기 때문에 불행히도 나는이 문제를 자신을 테스트 할 수 없습니다. 이 테스트를 할 경우, 그 결과를 여기에 공유하시기 바랍니다 :)

from https://stackoverflow.com/questions/10976328/mysql-count-performance-on-very-big-tables by cc-by-sa and MIT license