매우 큰 테이블에서 정확한 수의 행을 계산하는 가장 빠른 방법은 무엇입니까?
SELECT COUNT(*) FROM TABLE_NAME테이블에 많은 행과 많은 열이있을 때 속도가 느린 기사를 보았습니다.
수십억 개의 행 (약 15 개의 열이 있음)을 포함 할 수있는 테이블이 있습니다. 테이블의 행 수에 대한 정확한 개수 를 얻는 더 좋은 방법이 있습니까?
대답하기 전에 다음 사항을 고려하십시오.
데이터베이스 공급 업체 독립 솔루션을 찾고 있습니다. 그것이 MySQL , Oracle , MS SQL Server를 포함한다면 괜찮습니다 . 그러나 실제로 데이터베이스 벤더 독립 솔루션이 없다면 다른 데이터베이스 벤더에 대해 다른 솔루션을 결정합니다.
다른 외부 도구를 사용하여이 작업을 수행 할 수 없습니다. 주로 SQL 기반 솔루션을 찾고 있습니다.
더 이상 데이터베이스 디자인을 정규화 할 수 없습니다. 이미 3NF에 있으며 많은 코드가 이미 작성되었습니다.
간단한 답변 :
- 데이터베이스 벤더 독립 솔루션 = 표준 사용 =
COUNT(*) - 있다 대략 SQL 서버 솔루션은 그러나 범위를 벗어난 = COUNT (*)를 사용하지 않는
노트:
COUNT (1) = COUNT (*) = COUNT (PrimaryKey) 경우에 대비하여
편집하다:
SQL Server 예 (14 억 행, 12 열)
SELECT COUNT(*) FROM MyBigtable WITH (NOLOCK)
-- NOLOCK here is for me only to let me test for this answer: no more, no less
1 회, 5:46 분, 카운트 = 1,401,659,700
--Note, sp_spaceused uses this DMV
SELECT
Total_Rows= SUM(st.row_count)
FROM
sys.dm_db_partition_stats st
WHERE
object_name(object_id) = 'MyBigtable' AND (index_id < 2)
2 초, 1 초 미만, 카운트 = 1,401,659,670
두 번째 행은 행 수가 적습니다. 쓰기에 따라 동일하거나 더 많을 수 있습니다 (삭제는 몇 시간이 지나면 완료 됨)
MySQL에서 가장 빠른 방법은 다음과 같습니다.
SHOW TABLE STATUS;
원하는 경우 많은 추가 정보와 함께 행 수 (전체)와 함께 모든 테이블을 즉시 얻을 수 있습니다.
테이블에 행과 열이 많을 때 SELECT COUNT (*) FROM TABLE_NAME이 느리다는 기사가 나왔습니다.
데이터베이스에 따라 다릅니다. 예를 들어, 인덱스에서 행의 존재 여부를 추적하여 인덱스 수만 스캔하여 행 수를 추출 할 수 있습니다. 다른 사람들은 전체 테이블을 방문하지 않고 라이브 행을 하나씩 계산해야합니다. 거대한 테이블의 경우 속도가 느려집니다.
일반적으로 쿼리 최적화 도구, 테이블 통계 등을 사용하여 적절한 추정치를 추출 할 수 있습니다. 예를 들어 PostgreSQL의 경우 출력을 구문 분석하고 explain count(*) from yourtable행 수를 합리적으로 추정 할 수 있습니다. 두 번째 질문으로 연결됩니다.
수십억 개의 행 (약 15 개의 열이 있음)을 포함 할 수있는 테이블이 있습니다. 테이블의 행 수에 대한 정확한 개수를 얻는 더 좋은 방법이 있습니까?
진심이야? :-) 정말로 수십억 개의 행이있는 테이블에서 정확한 수를 의미 합니까? 정말로 확신합니까? :-)
당신이 경우 정말 할 당신이 할 경우, 당신은 트리거를 사용하여 전체의 추적하지만, 마음의 동시성과 교착 상태를 유지할 수 있습니다.
이 sp_spaceused (Transact-SQL)를 사용해 볼 수 있습니다
현재 데이터베이스의 테이블, 인덱싱 된 뷰 또는 Service Broker 큐에서 사용 된 행 수, 디스크 공간 및 디스크 공간을 표시하거나 전체 데이터베이스에서 예약 및 사용 된 디스크 공간을 표시합니다.
테이블의 행 수에 대한 정확한 개수를 얻는 더 좋은 방법이 있습니까?
귀하의 질문에 간단히 대답하려면 아니오 .
이 작업을 수행하는 DBMS 독립적 방법이 필요한 경우 가장 빠른 방법은 항상 다음과 같습니다.
SELECT COUNT(*) FROM TableName
일부 DBMS 공급 업체는 시스템에서만 작동하는 더 빠른 방법이있을 수 있습니다. 이러한 옵션 중 일부는 이미 다른 답변에 게시되어 있습니다.
COUNT(*) 어쨌든 DBMS (적어도 PROD 가치있는 DB)에 의해 최적화되어야하므로 최적화를 우회하지 마십시오.
참고 사항 :
다른 많은 쿼리도 테이블 크기로 인해 완료하는 데 오랜 시간이 걸릴 것이라고 확신합니다. 스키마 설계에 대한 속도를 염두에두고 생각하면 성능 문제를 해결할 수 있습니다. 변경 옵션이 아니라고 말했지만 10 분 이상 쿼리가 옵션이 아니라는 것을 알 수 있습니다. 3 NF는 속도를 필요로하고, 기록하지 않는 경우 때로는 데이터가 여러 테이블에서 분할 될 수있을 때 항상 가장 좋은 방법은 아니다 이 함께 저장 될 수 있습니다. 생각해 볼 점 ...
SQL Server 버전이 2005/2008 인 경우 DMV를 사용하여 테이블의 행 수를 계산할 수 있습니다.
-- Shows all user tables and row counts for the current database
-- Remove is_ms_shipped = 0 check to include system objects
-- i.index_id < 2 indicates clustered index (1) or hash table (0)
SELECT o.name,
ddps.row_count
FROM sys.indexes AS i
INNER JOIN sys.objects AS o ON i.OBJECT_ID = o.OBJECT_ID
INNER JOIN sys.dm_db_partition_stats AS ddps ON i.OBJECT_ID = ddps.OBJECT_ID
AND i.index_id = ddps.index_id
WHERE i.index_id < 2
AND o.is_ms_shipped = 0
ORDER BY o.NAME
SQL Server 2000 데이터베이스 엔진의 경우 sysindex가 작동하지만 조만간 제거 될 수 있으므로 이후 버전의 SQL Server에서는 사용하지 않는 것이 좋습니다.
샘플 코드 : 테이블 행 수를 신속하고 고통없이 얻는 방법
나는 사용한다
select /*+ parallel(a) */ count(1) from table_name a;
나는 대답 한 다른 사람들만큼 전문가가 아니었지만 테이블에서 임의의 행을 선택하는 데 사용하는 절차에 문제가 있었지만 (과도 관련이없는) 참조 테이블의 행 수를 알아야했습니다. 랜덤 인덱스를 계산합니다. 전통적인 Count (*) 또는 Count (1) 작업을 사용했지만 쿼리가 실행되는 데 때때로 최대 2 초가 걸렸습니다. 대신 ( 'tbl_HighOrder'라는 내 테이블의 경우) 다음을 사용하고 있습니다.
Declare @max int
Select @max = Row_Count
From sys.dm_db_partition_stats
Where Object_Name(Object_Id) = 'tbl_HighOrder'
잘 작동하며 Management Studio의 쿼리 시간이 0입니다.
글쎄, 5 년 후반에 도움이되는지 확실하지 않습니다.
나는 아니오를 세려고 노력했다. SQL Server Management Studio를 사용하여 SQL Server 테이블의 행 수를 초과하고 오버플로 오류가 발생하여 다음을 사용했습니다.
[dbname]. [dbo]. [FactSampleValue] 에서 count_big (1)을 선택하십시오 .
결과 :
24296650578 행
나는이 좋은 문서를 발견 신속하게 테이블에 대한 정확한 행 개수를 검색 : SQL 서버-HOW-TO 에서 martijnh1각 시나리오에 대한 좋은 요점의 반복을주는합니다.
특정 조건을 기반으로 카운트를 제공 해야하는 곳에서 이것을 확장해야 하며이 부분을 파악하면이 답변을 더 업데이트 할 것입니다.
그 동안 기사의 세부 정보는 다음과 같습니다.
방법 1 :
질문:
SELECT COUNT(*) FROM Transactions
코멘트:
전체 테이블 스캔을 수행합니다. 큰 테이블에서는 느립니다.
방법 2 :
질문:
SELECT CONVERT(bigint, rows)
FROM sysindexes
WHERE id = OBJECT_ID('Transactions')
AND indid < 2
코멘트:
행 수를 검색하는 빠른 방법. 통계에 따라 정확하지 않습니다.
COUNT_ROWS로 DBCC UPDATEUSAGE (데이터베이스)를 실행하십시오. 큰 테이블의 경우 시간이 오래 걸릴 수 있습니다.
방법 3 :
질문:
SELECT CAST(p.rows AS float)
FROM sys.tables AS tbl
INNER JOIN sys.indexes AS idx ON idx.object_id = tbl.object_id and
idx.index_id < 2
INNER JOIN sys.partitions AS p ON p.object_id=CAST(tbl.object_id AS int)
AND p.index_id=idx.index_id
WHERE ((tbl.name=N'Transactions'
AND SCHEMA_NAME(tbl.schema_id)='dbo'))
코멘트:
SQL 관리 스튜디오가 행을 계산하는 방법 (테이블 속성, 스토리지, 행 수를보십시오). 매우 빠르지 만 여전히 대략적인 행 수입니다.
방법 4 :
질문:
SELECT SUM (row_count)
FROM sys.dm_db_partition_stats
WHERE object_id=OBJECT_ID('Transactions')
AND (index_id=0 or index_id=1);
코멘트:
신속하고 (방법 2만큼 빠르지는 않지만) 작동하고 마찬가지로 중요하고 신뢰할 수 있습니다.
나는 항상 가장 빠른 솔루션이 있다고 생각하지 않습니다. 일부 RDBMS / 버전은 SELECT COUNT(*)더 빠른 옵션을 사용하는 반면 특정 테이블은 단순히 스캔 하는 특정 최적화 기능 이 있습니다. 두 번째 세트에 대한 문서 / 지원 사이트를 방문해야합니다.이 경우 더 구체적인 쿼리를 작성해야합니다.
편집하다:
다음은 스키마와 데이터 분포에 따라 작동 할 수있는 생각입니다. 증가하는 값, 숫자 증가하는 ID, 말 또는 타임 스탬프 또는 날짜를 참조하는 인덱스 열이 있습니까? 그런 다음 삭제가 발생하지 않는다고 가정하면 최근 값 (어제 날짜, 최근 샘플 포인트에서 가장 높은 ID 값)까지 카운트를 저장하고 그 이상으로 카운트를 추가하면 인덱스에서 매우 빨리 해결됩니다. . 물론 가치와 지수에 크게 의존하지만 거의 모든 버전의 DBMS에 적용 할 수 있습니다.
이 질문에 늦었지만 MySQL을 사용하여 MySQL로 할 수있는 작업은 다음과 같습니다. 나는 나의 관찰을 여기에서 공유하고있다 :
1) SELECT COUNT(*) AS TOTAL_ROWS FROM <TABLE_NAME>
결과
행 수 : 508534
콘솔 출력 : 영향을받는 행 : 0 찾은 행 : 1 경고 : 0 1 쿼리 지속 시간 : 0.125 초.
행 수가 많은 테이블의 경우 시간이 걸리지 만 행 수는 매우 정확합니다.
2) SHOW TABLE STATUS or SHOW TABLE STATUS WHERE NAME="<TABLE_NAME>"
결과
행 수 : 511235
콘솔 출력 : 영향을받는 행 : 0 찾은 행 : 1 경고 : 0 1 회 쿼리 지속 시간 : 0.250 초 요약 : 행 수가 정확하지 않습니다.
3) SELECT * FROM information_schema.tables WHERE table_schema = DATABASE();
결과
행 수 : 507806
콘솔 출력 : 영향을받는 행 : 0 찾은 행 : 48 경고 : 0 1 쿼리 지속 시간 : 1.701 초.
행 개수가 정확하지 않습니다.
나는 MySQL 또는 데이터베이스 전문가는 아니지만 매우 큰 테이블의 경우 옵션 2 또는 3을 사용하고 존재하는 행 수에 대한 '정당한 아이디어'를 얻을 수 있음을 발견했습니다.
UI에 통계를 표시하기 위해 이러한 행 수를 가져와야했습니다. 위의 쿼리를 사용하여 총 행 수가 50 만 개 이상임을 알았으므로 정확한 행 수를 표시하지 않고 "500,000 개 이상의 행"과 같은 통계를 표시했습니다.
아마도 OP의 질문에 실제로 대답하지는 않았지만 그러한 통계가 필요한 상황에서 내가 한 일을 공유하고 있습니다. 필자의 경우 대략적인 행을 표시하는 것이 허용되었으므로 위의 내용이 저에게 효과적이었습니다.
다른 StackOverflow 질문 / 답변 에서이 스크립트를 얻었습니다.
SELECT SUM(p.rows) FROM sys.partitions AS p
INNER JOIN sys.tables AS t
ON p.[object_id] = t.[object_id]
INNER JOIN sys.schemas AS s
ON s.[schema_id] = t.[schema_id]
WHERE t.name = N'YourTableNameHere'
AND s.name = N'dbo'
AND p.index_id IN (0,1);
내 테이블에는 5 억 개의 레코드가 있으며 위의 결과는 1ms 미만입니다. 그 동안에,
SELECT COUNT(id) FROM MyTable
39 분 52 초가 걸렸습니다!
그들은 정확히 같은 수의 행을 산출합니다 (제 경우에는 정확히 519326012).
나는 그것이 항상 그런 것인지 모른다.
정확히 DBMS에 구애받지 않는 솔루션은 아니지만 최소한 클라이언트 코드에는 차이가 없습니다 ...
하나의 행과 하나의 정수 필드 N 1로 다른 테이블 T를 작성하고 다음을 실행하는 INSERT TRIGGER를 작성하십시오.
UPDATE T SET N = N + 1
또한 다음을 실행하는 DELETE TRIGGER를 작성하십시오.
UPDATE T SET N = N - 1
소금의 가치가있는 DBMS는 2 이상의 연산의 원 자성을 보장 하고 N은 항상 정확한 행 수를 포함하므로 간단하게 얻을 수 있습니다.
SELECT N FROM T
트리거는 DBMS에 따라 다르지만 T에서 선택하는 것은 아니며 클라이언트 코드를 지원되는 각 DBMS마다 변경할 필요가 없습니다.
그러나 특히 INSERT / DELETE 직후에 커밋하지 않으면 테이블이 INSERT 또는 DELETE를 많이 사용하는 경우 확장 성 문제가 발생할 수 있습니다.
1 이 이름은 단지 자리 표시 자일뿐입니다. 프로덕션에서보다 의미있는 것을 사용하십시오.
2 읽기와 쓰기가 단일 SQL 문에서 수행되는 한, 읽기와 쓰기 사이의 동시 트랜잭션에 의해 N은 변경 될 수 없습니다.
말 그대로 미친 대답이지만, 어떤 종류의 복제 시스템이 설정되어 있다면 (십억 개의 행을 가진 시스템에 대해 희망합니다) 거친 추정기 (예 :)를 사용하여 MAX(pk)그 값을 슬레이브 수로 나눌 수 있습니다 여러 쿼리를 병렬로 실행하십시오.
대부분의 경우 가장 좋은 키 (또는 내가 추측 한 기본 키)를 기준으로 슬레이브에 쿼리를 분할합니다 (250000000을 행 / 슬레이브로 사용합니다).
-- First slave
SELECT COUNT(pk) FROM t WHERE pk < 250000000
-- Ith slave where 2 <= I <= N - 1
SELECT COUNT(pk) FROM t WHERE pk >= I*250000000 and pk < (I+1)*250000000
-- Last slave
SELECT COUNT(pk) FROM t WHERE pk > (N-1)*250000000
그러나 SQL 만 필요합니다. 무슨 흉상. 자, 당신이 sadomasochist라고 가정 해 봅시다 마스터 (또는 가장 가까운 슬레이브)에서 다음을 위해 테이블을 만들어야 할 것입니다.
CREATE TABLE counter_table (minpk integer, maxpk integer, cnt integer, slaveid integer)
따라서 슬레이브에서 선택을 실행하는 대신 다음과 같은 삽입을 수행해야합니다.
INSERT INTO counter_table VALUES (I*25000000, (I+1)*250000000, (SELECT COUNT(pk) FROM ... ), @@SLAVE_ID)
마스터의 테이블에 쓰는 슬레이브에 문제가 발생할 수 있습니다. 더 창의적이고 창의적인 것을 원할 수도 있습니다.
-- A table per slave!
INSERT INTO counter_table_slave_I VALUES (...)
결국 첫 번째 슬레이브와 관련하여 복제 그래프가 통과 한 경로의 마지막에 슬레이브가 있어야합니다. 해당 슬레이브는 이제 다른 모든 카운터 값을 가져야하며 자체 값을 가져야합니다. 그러나 완료 할 때까지 행이 추가되었을 수 있으므로 counter_table 및 현재 최대 pk에 기록 된 최대 pk를 보상하는 다른 행을 삽입해야합니다.
이 시점에서 총 행 수를 파악하기 위해 집계 함수를 수행해야하지만 최대 "있는 슬레이브 수 및 변경"행에서 실행하기 때문에 더 쉽습니다.
슬레이브에 별도의 테이블이있는 경우 UNION필요한 모든 행을 얻을 수 있습니다 .
SELECT SUM(cnt) FROM (
SELECT * FROM counter_table_slave_1
UNION
SELECT * FROM counter_table_slave_2
UNION
...
)
또는 약간 덜 미쳤고 데이터를 분산 처리 시스템으로 마이그레이션하거나 데이터웨어 하우징 솔루션 (미래에도 멋진 데이터 크 런칭을 제공함)을 사용할 수도 있습니다.
복제가 얼마나 잘 설정되어 있는지에 따라 달라집니다. 기본 병목 현상은 영구 스토리지 일 가능성이 높기 때문에 고르지 않은 스토리지 또는 인접 노이즈가 심한 분리 된 데이터 저장소가있는 경우 단일 대기 시간보다 느리게 실행될 수 있습니다.SELECT COUNT(*) ...
그러나 복제가 양호하면 속도 향상은 숫자 또는 슬레이브와 직접 관련되어야합니다. 실제로 계산 쿼리를 단독으로 실행하는 데 10 분이 걸리고 8 개의 슬레이브가있는 경우 시간을 몇 분 미만으로 줄입니다. 이 솔루션의 세부 사항을 다룰 시간이있을 수도 있습니다.
물론이 분산 해결은 행을 삭제하고 삽입 할 수있는 약간의 시간을 소개하기 때문에 놀랍도록 정확한 답변을 얻지 못하지만 동일한 인스턴스에서 분산 된 행 잠금을 얻고 정확한 수를 얻을 수 있습니다 특정 시점에 대한 테이블 행의
실제로 이것은 기본적으로 SQL 전용 솔루션을 사용하고 있기 때문에 불가능한 것으로 보이며 여러 슬레이브에서 즉시 샤드 및 잠긴 쿼리를 실행하는 메커니즘이 제공되지 않았다고 생각합니다. 어쩌면 복제 로그 파일을 제어 할 수 있다면 ...이 목적을 위해 문자 그대로 슬레이브를 회전시키고 있다는 것을 의미합니다. 어쨌든 단일 컴퓨터에서 카운트 쿼리를 실행하는 것보다 느릴 것입니다.
내 2013 페니 두 개가 있습니다.
경우 삽입 트리거가 사용하기에 너무 고가이지만, 삭제 트리거가 주어져야 수 있으며, 자동 증가가id , 다음 번 전체 테이블을 계산하고, 같은 수를 기억 한 후 last-count와 last-counted-id,
그런 다음 매일id > 을 계산하고 last-counted-id에 추가 last-count하고 새로운을 저장하면 last-counted-id됩니다.
삭제 된 레코드의 ID <= last-counted-id 인 경우 삭제 트리거는 last-count를 감소시킵니다.
행이 삭제되지 않는 자동 증가 기본 키 열이있는 일반적인 테이블 구조가있는 경우 레코드 수를 결정하는 가장 빠른 방법은 다음과 같으며 대부분의 ANSI 호환 데이터베이스에서 비슷하게 작동해야합니다.
SELECT TOP(1) <primarykeyfield> FROM <table> ORDER BY <primarykeyfield> DESC;
레코드 수를 포함하여 데이터에 대한 1 초 미만의 응답 시간이 필요한 수십억 개의 행이 포함 된 MS SQL 테이블로 작업합니다. 비슷한 SELECT COUNT (*)를 비교하여 처리하는 데 몇 분이 걸립니다.
SQL 서버의 경우 이것을 시도하십시오
SELECT T.name,
I.rows AS [ROWCOUNT]
FROM sys.tables AS T
INNER JOIN sys.sysindexes AS I
ON T.object_id = I.id AND I.indid < 2
WHERE T.name = 'Your_Table_Name'
ORDER BY I.rows DESC
id = Object_ID ( 'TableName')이고 indid <2 인 sysindexes에서 행을 선택하십시오.
일부 열에 색인을 넣으십시오. 그러면 옵티마이 저가 테이블의 전체 스캔 대신 인덱스 블록의 전체 스캔을 수행 할 수 있어야합니다. 그러면 IO 비용이 줄어 듭니다. 실행 계획을 전후로보십시오. 그런 다음 벽시계 시간을 양방향으로 측정하십시오.
Oracle을 사용하는 경우 테이블 통계가 업데이트되었다고 가정하면 다음과 같습니다.
select <TABLE_NAME>, num_rows, last_analyzed from user_tables
last_analyzed는 통계가 마지막으로 수집 된 시간을 보여줍니다.
PostgreSQL로 :
SELECT reltuples AS approximate_row_count FROM pg_class WHERE relname = 'table_name'
SQL Server 2016에서는 테이블 속성을 확인한 다음 '스토리지'탭을 선택하면 행 수, 테이블에 사용되는 디스크 공간, 사용 된 인덱스 공간 등이 표시됩니다.
테이블 어딘가에 기본 키 (고유 값)가있는 경우 MAX(yourId)기본적으로 총 행 수를 제공하는 데 사용할 수 있습니다 . 아래는 샘플 스 니펫입니다.
SELECT MAX(yourId)
FROM YourTable
어쩌면 조금 늦었지만 MSSQL에 대한 다른 사람들을 도울 수 있습니다.
; WriteCount AS AS (SELECT_ROW_NUMBER () OVER (ORDER BY COLUMN_NAME) AS [RowNumber] FROM TABLE_NAME) SELECT MAX (RowNumber) FROM RecordCount
'Programing' 카테고리의 다른 글
| 왜 자바 스크립트`atob ()`와`btoa ()`의 이름이 그렇게 되었습니까? (0) | 2020.04.25 |
|---|---|
| 부모 컨테이너의 높이 백분율로 여백 또는 패딩을 설정하는 방법은 무엇입니까? (0) | 2020.04.25 |
| @ Html.DisplayFor 구문은 무엇입니까? (0) | 2020.04.25 |
| iOS 7 흐림보기와 유사한 효과를 만들려면 어떻게해야합니까? (0) | 2020.04.25 |
| 별도의 변수 사전을 만드는 간단한 방법? (0) | 2020.04.25 |