매우 큰 테이블에서 Mysql 개수 성능

매우 큰 테이블에서 Mysql 개수 성능

이노브에 1억 줄이 넘는 테이블이 있습니다.

외부 키가 = 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초의 과부하가 될 수 있습니다!

다른 방도가 있습니까?

감사해요.

편집 : [ OP 관련 의견 추가]

테이블에서 SELECT SQL_NO_CACH COUNT(fk)를 시도했는데 fk = 1입니다. 하지만 25초가 걸렸습니다.

Mysql은 Mysql 튜너와 함께 Innodb에 맞게 조정되었습니다.

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: 지금까지 동일한 방법을 사용하여 매일 6억 개의 행과 64만 개의 새 행을 사용했습니다.아직 잘 작동하고 있습니다.

실제 카운트에 관심이 없는 것 같으니 다음을 시도해 보십시오.

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

행이 반환되면 5000개 이상의 레코드가 있습니다. 마아일 입니다.fk열이 인덱스됩니다.

카운터 테이블 또는 기타 캐싱 메커니즘이 해결책입니다.

동시 트랜잭션은 동시에 서로 다른 행 수를 "확인"할 수 있기 때문에 InnoDB는 테이블의 내부 행 수를 유지하지 않습니다.SELECT COUNT(*) FROMt 문을 처리하기 위해 InnoDB는 테이블의 인덱스를 검색합니다. 인덱스가 완전히 버퍼 풀에 있지 않으면 시간이 걸립니다.테이블이 자주 변경되지 않으면 MySQL 쿼리 캐시를 사용하는 것이 좋습니다.빠른 카운트를 얻으려면 사용자가 직접 만든 카운터 테이블을 사용하고 응용 프로그램이 삽입 및 삭제에 따라 업데이트하도록 해야 합니다.대략적인 행 수가 충분하면 SHOW TABLE STATUS를 사용할 수 있습니다.섹션 14.3.14.1, "InnoDB 성능 조정 팁"을 참조하십시오.

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

다른 답변을 추가해야 합니다. 지금까지 댓글과 답변에 많은 수정/추가 사항이 있습니다.

MyISAM 위해서을.SELECT COUNT(*) 없이WHERE죽은 채로 복제되었습니다. 매우 빠릅니다.다른 모든 상황(질문에 InnoDB 포함)은 데이터의 BTree 또는 인덱스의 BTree를 통해 답을 얻어야 합니다.그래서 우리는 얼마나 세어볼 필요가 있습니다.

InnoDB는 데이터 및 인덱스 블록(각각 16KB)을 캐시합니다.그러나 테이블의 데이터 또는 인덱스 BTree가 다음보다 클 때innodb_buffer_pool_size당신은 디스크를 칠 것이 확실합니다.디스크를 치는 것은 거의 항상 SQL에서 가장 느린 부분입니다.

쿼리 캐시를 사용하면 대개 약 1밀리초의 쿼리 시간이 발생합니다. 이는 인용된 시간에 문제가 없는 것으로 보입니다.그래서 나는 그것에 연연하지 않을 것입니다.

그러나... 동일한 쿼리를 연속으로 두 번 실행하면 다음과 같이 표시되는 경우가 많습니다.

• 첫 번째 주행: 10초
• 2차 주행: 1초

이는 첫 번째 실행이 디스크에서 대부분의 블록을 가져와야 하는 반면 두 번째 실행은 RAM(buffer_pool)에서 모두 찾아야 한다는 것을 나타냅니다.이 캐싱 문제를 인식하지 못했기 때문에 나열된 일부 타이밍이 잘못된 것 같습니다.(16초 대 0.6초로 설명할 수 있습니다.)

SQL이 더 빠른 실제 측정 기준으로 "디스크 히트" 또는 "터치해야 하는 블록"을 강조할 것입니다.

COUNT(x)x위해서IS NOT NULL적중 .이렇게 하면 소량의 처리가 추가되지만 디스크 적중 횟수는 변경되지 않습니다.

제안된 테이블에는 PK와 두 번째 열이 있습니다.그게 진짜 테이블인지 궁금합니다.그것은 변화를 만듭니다.

• Optimizer가 데이터를 읽기로 결정한 경우 즉, 스캔인PRIMARY KEY순서 - 데이터 BTree를 읽습니다. 일반적으로 2차 인덱스 BTree보다 훨씬 넓습니다.
• Optimizer가 2차 인덱스를 읽기로 결정한 경우(단, 정렬할 필요는 없음) 터치할 블록이 더 적어집니다.그러므로, 더 빨리.

원본 쿼리에 대한 주석:

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)따라서 세 번째 쿼리는 데이터를 건드리지 않고 이를 "적용"으로 사용할 수 있습니다.

표가 실제로 두 개의 열만 있는 경우 보조 인덱스 BTree가 데이터 BTree보다 더 뚱뚱해질 수 있습니다.하지만 현실적인 표에서는 2차 지수가 더 작을 것입니다.따라서 인덱스 검색은 테이블 검색보다 더 빠릅니다(터치할 블록 수가 적음).

세 번째 쿼리는 또한 큰 결과 집합을 제공합니다. 이로 인해 쿼리에 시간이 오래 걸릴 수 있지만 따옴표로 묶인 "시간"에는 포함되지 않습니다. 쿼리 시간이 아니라 네트워크 시간입니다.

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 이상"입니다. 이것이 가장 좋은 방법입니다.테이블의 총 행 수에 관계없이 일관되게 속도가 빠릅니다(블록 수는 12개에 불과함).(이는 여전히 시스템의 buffer_pool_size 및 캐시 특성에 영향을 받습니다.그러나 콜드 캐쉬를 사용하더라도 12개 블록은 1초 미만으로 소요됩니다.)

마리아 DB는 조사할 가치가 있을 것입니다.그게 없다면, 당신은 할 수 있습니다.

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

클라이언트에 행을 전달하는 것보다 빠를 수 있습니다. tmp 테이블에서 내부적으로 행을 수집해야 하지만 다음과 같은 작업만 수행해야 합니다.COUNT.

행 -- 행에 대한 합니다: 하에참 640K 개삽행. 이것은 단일 행의 제한에 근접합니다.INSERTsMySQL(SDD가 아닌 HDD의 현재 설정)에서 사용할 수 있습니다.잠재적인 재해에 대해 논의해야 하는 경우 다른 질문을 엽니다.

결론:

• 쿼리 캐시를 피해야 합니다.(사용)SQL_NO_CACHE를 선택합니다.
• 타이밍 쿼리를 두 번 실행하고 두 번째 시간을 사용합니다.
• 관련 BTree의 구조와 크기를 이해합니다.
• 사용하지 COUNT(x)Null 체크가 필요하지 않은 경우.
• PHP를 mysql_* 인페이스로 ; 전환으로 전환mysqli_*또는PDO.

오래된 질문이지만 저도 같은 문제가 있었고 아마도 이것이 누군가에게 도움이 될 것입니다. 4백만 개의 레코드로 COUNT 쿼리는 20초 이상 걸립니다.따라서 기본 키로 간단한 필터링을 추가한 후에는 훨씬 빨라지고 4초밖에 걸리지 않습니다.마지막 질문은 다음과 같습니다.

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

제 경우 PK는 INT였습니다.

만약 당신이 PHP를 사용하고 있다면 당신은 할 수 있습니다.mysql_num_rows당신이 얻은 결과에 의하면SELECT primary FROM table WHERE fk = 1 => 0.6 seconds그게 효율적일 것 같습니다.

그러나 사용 중인 서버 측 언어에 따라 다릅니다.

행 수를 알고 싶지 않고 COUNT를 일부 값과 비교하여 테스트하려는 경우 아래의 표준 스크립트를 사용할 수 있습니다.

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

조건이 충족되는지 여부에 따라 단일 행을 반환하거나 행을 반환하지 않습니다.

이 스크립트는 ANSI를 준수하며 COUNT(*)의 전체 값을 평가하지 않고 완전히 실행할 수 있습니다.MySQL이 어떤 조건이 충족된 후에 행 평가를 중지하는 최적화를 구현했다면(정말 그러길 바랍니다), 성능이 향상될 것입니다.사용할 수 있는 큰 MySQL 데이터베이스가 없기 때문에 안타깝게도 이 동작을 직접 테스트할 수 없습니다.이 테스트를 하시면 여기에 결과를 공유해주세요 :)

마지막으로 가장 빠른 것은 C#을 사용하여 행 번호를 세는 첫 번째 X 행을 쿼리하는 것이었습니다.

제 애플리케이션은 데이터를 일괄 처리하는 것입니다.두 배치 사이의 시간은 처리해야 하는 행 수에 따라 달라집니다.

SELECT pk FROM table WHERE fk = 1 LIMIT X

저는 0.9초 만에 결과를 얻었습니다.

당신의 아이디어에 모두 감사드립니다!

언급URL : https://stackoverflow.com/questions/10976328/mysql-count-performance-on-very-big-tables