매우 큰 테이블에서 정확한 행 수를 계산하는 가장 빠른 방법은 무엇입니까?
나는 다음과 같은 기사를 접했습니다.SELECT COUNT(*) FROM TABLE_NAME테이블에 행이 많고 열이 많은 경우 속도가 느려집니다.
저는 수십억 개의 행을 포함할 수 있는 테이블을 가지고 있습니다[약 15개의 열이 있습니다].테이블의 행 수를 정확하게 계산할 수 있는 더 좋은 방법이 있습니까?
답변하기 전에 다음 사항을 고려하십시오.
데이터베이스 공급업체 독립 솔루션을 찾고 있습니다.MySQL, Oracle, MS SQL Server를 포함하면 됩니다.하지만 데이터베이스 공급업체에 독립적인 솔루션이 없다면 데이터베이스 공급업체마다 다른 솔루션을 선택할 것입니다.
다른 외부 도구를 사용하여 이 작업을 수행할 수 없습니다.저는 주로 SQL 기반 솔루션을 찾고 있습니다.
데이터베이스 설계를 더 이상 정상화할 수 없습니다.그것은 이미 3NF에 있고, 게다가 많은 코드가 이미 그것 주위에 쓰여져 있습니다.
간단한 대답:
- 솔루션 = 데베이스공체독솔 = 표준사용 = 사용
COUNT(*) - 대략적인 SQL Server 솔루션이 있지만 COUNT(*) = 범위를 벗어남
주의:
COUNT(1) = COUNT(*) = COUNT(기본 키)
편집:
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;
원하는 경우 추가 정보와 함께 행 수(총)가 포함된 모든 테이블이 즉시 표시됩니다.
다른 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).
나는 그것이 항상 그럴지는 모르겠습니다.
이 sp_space를 사용해 볼 수 있습니다(Transact-SQL).
현재 데이터베이스에서 테이블, 인덱싱된 보기 또는 Service Broker 대기열에서 사용하는 행, 예약된 디스크 공간 및 디스크 공간을 표시하거나 전체 데이터베이스에서 예약하여 사용하는 디스크 공간을 표시합니다.
테이블에 행과 열이 많을 때 TABLE_NAME에서 SELECT COUNT(*)가 느려진다는 기사를 본 적이 있습니다.
그것은 데이터베이스에 따라 다릅니다.예를 들어 인덱스에서 행이 활성 상태인지 비활성 상태인지 추적하여 인덱스만 검색하여 행 수를 추출할 수 있도록 함으로써 카운트를 가속화합니다.다른 사람들은 그렇지 않기 때문에 전체 테이블을 방문하여 라이브 행을 하나씩 세야 합니다.어느 쪽이든 큰 테이블치고는 느릴 것입니다.
일반적으로 쿼리 최적화 도구, 표 통계량 등을 사용하여 좋은 추정치를 추출할 수 있습니다.를 들어 Postgre Postgre의 SQL의 에는 ""의 분석할 수 .explain count(*) from yourtable행 수에 대한 적절한 추정치를 얻을 수 있습니다.그래서 두 번째 질문을 하겠습니다.
저는 수십억 개의 행을 포함할 수 있는 테이블을 가지고 있습니다[약 15개의 열이 있습니다].테이블의 행 수를 정확하게 계산할 수 있는 더 좋은 방법이 있습니까?
정말요? :-) 수십억 줄의 테이블에서 정확한 카운트를 말하는 건가요?정말 확실해요? :-)
만약 정말로 그렇게 한다면, 트리거를 사용하여 총계를 추적할 수 있지만, 만약 그렇다면 동시성과 교착 상태를 염두에 둘 수 있습니다.
테이블의 행 수를 정확하게 계산할 수 있는 더 좋은 방법이 있습니까?
질문에 간단히 대답하자면, 아니요.
DBMS 독립적인 방법이 필요한 경우 가장 빠른 방법은 항상 다음과 같습니다.
SELECT COUNT(*) FROM TableName
일부 DBMS 공급업체는 시스템에서만 작동하는 더 빠른 방법을 사용할 수 있습니다.이러한 옵션 중 일부는 이미 다른 답변에 게시되어 있습니다.
COUNT(*)DBMS(적어도 PROD 가치가 있는 DB)에 의해 최적화되어야 하므로 이러한 최적화를 우회하려고 하지 마십시오.
참고 사항:
테이블 크기 때문에 다른 많은 쿼리도 완료하는 데 시간이 오래 걸릴 것입니다.성능 문제는 속도를 염두에 두고 스키마 설계를 고려하여 해결해야 합니다.당신이 변경할 수 있는 옵션이 아니라고 말한 것은 알고 있지만 10분 이상의 쿼리도 옵션이 아닐 수 있습니다.속도가 필요할 때 3차 NF가 항상 최선의 방법은 아니며, 레코드를 함께 저장할 필요가 없는 경우 데이터를 여러 테이블로 분할할 수도 있습니다.생각해 볼 일이...
SQL Server에 대한 좋은 기사를 찾았습니다.방법: 테이블에 대한 정확한 행 수를 신속하게 검색합니다.martijnh1각 시나리오에 대한 요약 정보를 제공합니다.
특정 조건을 기준으로 카운트를 제공해야 하는 곳으로 확장이 필요하며, 이 부분을 파악하면 이 답변을 추가로 업데이트하겠습니다.
한편, 다음은 기사의 세부 정보입니다.
방법 1:
쿼리:
SELECT COUNT(*) FROM Transactions
댓글:
전체 테이블 검색을 수행합니다.큰 테이블에서는 느립니다.
방법 2:.
쿼리:
SELECT CONVERT(bigint, rows)
FROM sysindexes
WHERE id = OBJECT_ID('Transactions')
AND indid < 2
댓글:
행 수를 빠르게 검색할 수 있습니다.통계에 따라 다르며 부정확합니다.
COUNT_ROWS로 DBCCUPDATEUSE(데이터베이스)를 실행합니다. 이 경우 큰 테이블에 상당한 시간이 소요될 수 있습니다.
방법 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만큼 빠르지는 않지만)과 마찬가지로 중요하고 신뢰할 수 있습니다.
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에서는 sysindex를 사용하지 않는 것이 좋습니다.
다음에서 가져온 샘플 코드:테이블 행 수를 빠르고 쉽게 계산하는 방법
사용합니다
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년이나 늦었지만 도움이 될지 확신할 수 없습니다.
MS SQL Server Management Studio를 사용하여 SQL Server 테이블의 행 수를 계산하려다가 오버플로 오류가 발생하여 다음을 사용했습니다.
[dbname]에서 count_big(1) FROM을 선택합니다.[dbo].[팩트 표본 값];
결과:
24296650578 행
DBMS에 구애받지 않는 솔루션은 아니지만 적어도 고객 코드에는 차이가 없습니다.
하나의 행과 하나의 정수 필드1 N으로 다른 테이블 T를 만들고 다음을 실행하는 INSERT 트리거를 만듭니다.
UPDATE T SET N = N + 1
또한 다음을 실행하는 DELETE 트리거를 만듭니다.
UPDATE T SET N = N - 1
그 가치가 있는 DBMS는 위의 작업의2 원자성을 보장하며, N은 항상 정확한 행 수를 포함하며, 이는 간단히 다음과 같이 매우 빠르게 얻을 수 있습니다.
SELECT N FROM T
트리거는 DBMS마다 다르나 T에서 선택하면 지원되는 각 DBMS에 대해 클라이언트 코드를 변경할 필요가 없습니다.
그러나 테이블이 INSERT 또는 DELETE 집약적인 경우, 특히 INSERT/DELETE 직후 커밋하지 않으면 확장성 문제가 발생할 수 있습니다.
1 이러한 이름은 자리 표시자일 뿐입니다. 프로덕션에서 더 의미 있는 이름을 사용하십시오.
2 즉, 읽기와 쓰기가 모두 단일 SQL 문에서 수행되는 한 N에 대한 읽기와 쓰기 사이의 동시 트랜잭션으로 N을 변경할 수 없습니다.
일반적으로 항상 가장 빠른 솔루션은 없다고 생각합니다. 일부 RDBMS/버전은 다음을 위해 특정 최적화되어 있습니다.SELECT COUNT(*)더 빠른 옵션을 사용하는 반면 다른 옵션은 테이블 스캔만 사용합니다.두 번째 세트의 설명서/지원 사이트로 이동해야 합니다. 일반적으로 어떤 식으로든 인덱스에 맞는 좀 더 구체적인 쿼리를 작성해야 할 것입니다.
편집:
스키마와 데이터 분포에 따라 효과적일 수 있는 아이디어가 있습니다. 증가하는 값, 숫자 증가 ID, 예를 들어 타임스탬프 또는 날짜를 참조하는 인덱스된 열이 있습니까?그런 다음 삭제가 발생하지 않는다고 가정하면, 일부 최근 값(어제 날짜, 일부 최근 샘플 포인트에서 가장 높은 ID 값)까지 카운트를 저장하고 그 이상의 카운트를 추가할 수 있으며, 이는 인덱스에서 매우 빠르게 해결될 것입니다.물론 값과 인덱스에 매우 의존하지만 거의 모든 DBMS 버전에 적용할 수 있습니다.
이 질문에 늦었지만 MySQL을 사용하여 수행할 수 있는 작업은 다음과 같습니다(MySQL 사용 시).저는 여기서 제 의견을 공유합니다.
1) SELECT COUNT(*) AS TOTAL_ROWS FROM <TABLE_NAME>
결과
행 수: 508534
콘솔 출력: 영향을 받는 행: 0 발견된 행: 1 경고: 0 쿼리 기간: 0.125초
행 수가 많은 테이블의 경우 시간이 걸리지만 행 수는 매우 정확합니다.
2) SHOW TABLE STATUS or SHOW TABLE STATUS WHERE NAME="<TABLE_NAME>"
결과
행 수: 511235
콘솔 출력: 영향을 받는 행: 0개 발견된 행: 1개 경고: 0개 쿼리 기간: 0.250초 요약: 행 수가 정확하지 않습니다.
3) SELECT * FROM information_schema.tables WHERE table_schema = DATABASE();
결과
행 수: 507806
콘솔 출력: 영향을 받는 행: 0개 발견 행: 48개 경고: 0개 쿼리 기간: 1.701초
행 수가 정확하지 않습니다.
MySQL이나 데이터베이스 전문가는 아니지만 매우 큰 테이블의 경우 옵션 2 또는 3을 사용하여 얼마나 많은 행이 있는지 '공정한 아이디어'를 얻을 수 있다는 것을 알게 되었습니다.
UI에 일부 통계를 표시하기 위해 이 행 수를 가져와야 했습니다.위의 쿼리를 통해 총 행 수가 500,000개 이상이라는 것을 알고 정확한 행 수를 표시하지 않고 "500,000개 이상의 행"과 같은 통계를 표시했습니다.
OP의 질문에 제대로 답변하지 못했을 수도 있지만, 그러한 통계가 필요한 상황에서 제가 한 일을 공유하고 있습니다.저의 경우, 대략적인 행을 보여주는 것은 허용 가능했고 위의 것들이 저에게 효과가 있었습니다.
이지만, 종류의 , (행의 할 수 . (예: 말억개예의말안만지대이, 답복이설어정되있경다우템시면대도제사수, 략있스템스시용할습적인니다추를는있정대치그행로이는되10억▁a,▁(예▁alike경▁literally▁answerim):MAX(pk) 여러 합니다.), 이 값 슬 레 을 여 쿼 를 리 병 로 렬 러 실 합 행 니 다 음 다 눈 나 이 로 수 브 ▁), ▁you 합 ▁divide
대부분의 경우 최상의 키(또는 기본 키)를 기준으로 슬레이브 간에 쿼리를 분할합니다(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만 필요합니다.흉상이네요.자, 그럼 당신이 사도마조히스트라고 치자.마스터(또는 가장 가까운 슬레이브)에서 이에 대한 테이블을 만들어야 합니다.
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에 기록된 max pk와 현재 max pk를 보상하는 다른 행을 삽입해야 합니다.
이 시점에서 총 행 수를 파악하려면 집계 함수를 수행해야 하지만, 최대 "슬레이브 수 및 변경" 행에서 실행하는 것이 더 쉽습니다.
있는 예들의테따이로있상면, 는▁if▁can▁in▁you다▁where,있니습▁tables▁situation▁the수▁separate▁slaves▁you할당,▁you'▁the노▁have신예.UNION필요한 모든 행을 얻을 수 있습니다.
SELECT SUM(cnt) FROM (
SELECT * FROM counter_table_slave_1
UNION
SELECT * FROM counter_table_slave_2
UNION
...
)
또는 데이터를 분산 처리 시스템으로 마이그레이션하거나 데이터 웨어하우징 솔루션을 사용할 수도 있습니다(미래에는 데이터 처리 능력도 향상될 것입니다).
이는 복제 설정 수준에 따라 달라집니다. 현상은이 높기 를 제대로 분리하지 못한 보다 속도가 수 .SELECT COUNT(*) ...
그러나 복제가 우수한 경우 속도 향상은 수 또는 슬레이브와 직접적으로 관련되어야 합니다.실제로 계산 쿼리를 실행하는 데만 10분이 걸리고 노예가 8명인 경우 시간을 2분 미만으로 단축할 수 있습니다.이 해결책의 세부사항을 해결하는 데 한 시간 정도 걸립니다.
물론, 이 분산 솔루션은 행을 삭제하고 삽입할 수 있는 약간의 시간을 제공하기 때문에 놀라운 정확한 답을 얻을 수는 없지만, 동일한 인스턴스에서 행의 분산 잠금을 얻고 특정 시간 동안 테이블의 행 수를 정확하게 계산할 수 있습니다.
사실, 이것은 불가능해 보입니다. 기본적으로 SQL 전용 솔루션을 사용해야 하기 때문입니다. 그리고 여러 슬레이브에서 즉시 샤드 및 잠금 쿼리를 실행할 수 있는 메커니즘이 제공되지 않는다고 생각합니다.복제 로그 파일을 제어할 수 있다면...이는 문자 그대로 이 목적을 위해 노예를 만들어내는 것을 의미합니다. 어쨌든 하나의 기계에서 카운트 쿼리를 실행하는 것보다 느리다는 것은 의심할 여지가 없습니다.
이것이 제 2013년 페니 두 개입니다.
삽입 트리거가 너무 비싸서 사용할 수 없지만 삭제 트리거를 사용할 수 있고 자동 증분이 있는 경우 전체 테이블을 한 번 카운트한 후 카운트를 다음과 같이 기억합니다.last-count 리고그고.last-counted-id,
그러면 매일매일이 그저 세어지기만 하면 됩니다.id>last-counted-id에 그것을 더하다.last-count그리고 새로운 것을 저장합니다.last-counted-id.
삭제된 레코드 <= last-count-id의 ID인 경우 삭제 트리거는 last-count를 감소시킵니다.
나를 위한 아주 큰 테이블에서,
SELECT COUNT(1) FROM TableLarge
37초가 소요되는 반면
SELECT COUNT_BIG(1) FROM TableLarge
4초 걸립니다.
행이 삭제되지 않는 자동 증분 기본 키 열이 있는 일반적인 테이블 구조인 경우, 다음은 레코드 수를 결정하는 가장 빠른 방법이며 대부분의 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
select rows from sysindexes
where id = Object_ID('TableName') and indid <2
사용하다COUNT_BIG()매우 큰 크기의 파일에서 레코드 수를 가져오는 데 사용됩니다.
SELECT COUNT_BIG(*) FROM TABLENAME;
어떤 열에 색인을 붙이세요.이렇게 하면 최적화 도구는 테이블의 전체 검색 대신 인덱스 블록에 대한 전체 검색을 수행할 수 있습니다.그러면 IO 비용이 크게 절감됩니다.실행 계획을 전후로 살펴봅니다.그런 다음 벽 시계 시간을 양방향으로 측정합니다.
Oracle을 사용하는 경우 테이블 통계가 업데이트되었다고 가정하면 다음과 같습니다.
select <TABLE_NAME>, num_rows, last_analyzed from user_tables
last_message는 통계가 마지막으로 수집된 시간을 표시합니다.
Postgre 포함SQL:
SELECT reltuples AS approximate_row_count FROM pg_class WHERE relname = 'table_name'
SQL Server 2019에서는 다음과 같은 APTU_COUNT_DISTINCT를 사용할 수 있습니다.
그룹에서 고유하지 않은 값의 대략적인 수를 반환합니다.
그리고 문서에서:
ACTUL_COUNT_DISTINCT는 빅데이터 시나리오에서 사용하도록 설계되었으며 다음과 같은 조건에 최적화되어 있습니다.
- 수백만 행 이상의 데이터 세트에 대한 액세스 및
- 여러 개의 고유한 값을 가진 열의 집합
또한, 그 함수는
- 구현은 97% 확률 내에서 최대 2%의 오류율을 보장합니다.
- 철저한 Count DISTINCT 작업보다 적은 메모리 필요
- 메모리 설치 공간이 작기 때문에 정확한 Count DISTINCT 작업에 비해 메모리가 디스크에 유출될 가능성이 적습니다.
구현 뒤에 있는 알고리즘은 HyperLogLog입니다.
SQL Server 2016에서는 테이블 속성을 확인한 다음 'Storage' 탭을 선택하면 행 수, 테이블에서 사용하는 디스크 공간, 인덱스 공간 등이 표시됩니다.
조금 늦었을 수도 있지만 이것은 MSSQL에 대해 다른 사람들에게 도움이 될 수 있습니다.
;WITH RecordCount AS ( SELECT ROW_NUMBER() OVER (ORDER BY
COLUMN_NAME) AS [RowNumber] FROM TABLE_NAME ) SELECT
MAX(RowNumber) FROM RecordCount
언급URL : https://stackoverflow.com/questions/6069237/fastest-way-to-count-exact-number-of-rows-in-a-very-large-table
'codememo' 카테고리의 다른 글
| 'DOMAIN\MACHINE$' 사용자에 대한 로그인 실패 (0) | 2023.05.03 |
|---|---|
| 자동 배치 - 절반의 수퍼뷰 높이를 기준으로 높이 설정 (0) | 2023.05.03 |
| PostgreSQL에서 보기에 대한 CREATE VIEW 코드를 보는 방법은 무엇입니까? (0) | 2023.05.03 |
| WPF에 "All Children loaded" 이벤트가 있습니까? (0) | 2023.05.03 |
| OS X에서 환경 변수 설정 (0) | 2023.05.03 |