SQL Server에서 수천만 개의 레코드를 처리하는 방법 알아보기
- coldplay.xixi앞으로
- 2020-11-27 16:42:299470검색
sqltutorial 칼럼에서는 수천만 건의 레코드를 처리하는 방법을 소개합니다.
추천: sql tutorial
프로젝트 배경
데이터센터를 위한 프로젝트입니다. 프로젝트의 난이도가 정말 어마어마합니다. 전장에는 전술이 너무 많고, 고위 관료들 사이의 경쟁이 너무 많고, 내부 이야기가 너무 많습니다. 이번 프로젝트의 구체적인 상황에 대해서는 시간이 나면 관련 블로그 포스팅을 작성해보겠습니다.
이 프로젝트에는 환경 모니터링이 필요합니다. 당분간 모니터링되는 장비를 수집 장치라고 하며, 수집 장비의 속성을 모니터링 지표라고 합니다. 프로젝트 요구 사항: 시스템은 10w 모니터링 지표를 지원하고, 각 모니터링 지표의 데이터 업데이트는 20초를 초과할 수 없으며, 저장 지연은 120초을 초과할 수 없습니다. 그런 다음 간단한 계산을 통해 이상적인 상태를 얻을 수 있습니다. 저장되는 데이터는 분당 30w, 시간당 1800w, 하루 4억 3200만입니다. 실제 데이터의 양은 이보다 5% 정도 더 많을 것이다. (사실 대부분은 데이터 압축을 통해 처리할 수 있는 정보 쓰레기인데, 다른 사람들이 당신을 엉망으로 만들고 싶다면 어떻게 할 수 있습니까?)
위는 프로젝트에서 요구하는 지표입니다. 빅데이터 처리 경험이 많으신 분들은 스니핑만 하면 알겠죠? 글쎄요, 저도 빅데이터 처리에 대해 많이 읽었지만 한번도 다루어본 적이 없습니다. 다른 분들의 명쾌한 설명을 보면 무엇이 분산되어 있고 무엇이 읽기와 쓰기를 분리하는지 해결하기가 정말 쉬운 것 같습니다. 그러나 문제는 그렇게 간단하지 않습니다. 위에서 말했듯이 이것은 매우 나쁜 프로젝트이며 업계의 전형적인 악순환 프로젝트입니다.
- 더 이상 서버는 없지만, 이 서버는 데이터베이스와 중앙집중식 수집기(즉, 데이터 분석, 알람, 저장을 위한 프로그램) 외에 30만 포인트 노스바운드 인터페이스(SNMP)도 지원합니다. 프로그램이 최적화되어 있으면 CPU가 일년 내내 80% 이상을 점유합니다. 이 프로젝트는 이중 머신 상시 대기를 사용해야 하기 때문에 시간을 절약하고 불필요한 문제를 줄이기 위해 HA(외부 구매 HA 시스템)의 특성을 최대한 활용할 수 있도록 관련 서비스를 통합했습니다
- 시스템 데이터 정확성 요구 사항은 매우 비정상적이므로 하위 수집 시스템에서 최상위 모니터링 시스템까지 모든 데이터에 차이가 없어야 합니다. 우리의 시스템 아키텍처는 다음과 같습니다. 특히 LevelA 노드에서:
하드웨어 구성은 다음과 같습니다: -
CPU: Intel® 500GB 7200 RPM 3.5'' SATA3 하드 드라이브, Raid5.
데이터베이스 버전은 SQLServer2012 표준 버전을 사용하며 정품 소프트웨어가 제공됩니다. 엔터프라이즈 버전의 NB 기능이 많이 부족한 HP의 제품입니다.
당신만의 linuxC/C++ 커뮤니케이션 그룹을 추천해주세요: 973961276! 개인적으로 더 좋다고 생각되는 학습서, 영상자료, 주요 제조사 인터뷰 등을 모아서 그룹파일로 공유해두었는데, 필요한 친구들이 직접 추가할 수 있어요! ~
우리가 직면한 첫 번째 장애물은 SQL Server가 기존 프로그램에서 그렇게 많은 양의 데이터를 처리할 수 없다는 점이었습니다. 구체적인 상황은 무엇입니까?
우리의 저장소 구조
일반적으로 대량의 과거 데이터를 저장하기 위해 물리적인 테이블을 생성하게 됩니다. 그렇지 않으면 매일 수백만 건의 기록이 1년에 수억 건이 될 것입니다. 따라서 원래 테이블 구조는CREATE TABLE [dbo].[His20140822](
[No] [bigint] IDENTITY(1,1) NOT NULL,
[Dtime] [datetime] NOT NULL,
[MgrObjId] [varchar](36) NOT NULL,
[Id] [varchar](50) NOT NULL,
[Value] [varchar](50) NOT NULL,
CONSTRAINT [PK_His20140822] PRIMARY KEY CLUSTERED (
[No] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]No 고유 식별자, 수집 장치 Id(Guid), 모니터링 표시기 ID(varchar(50)), 녹화 시간, 녹화 값입니다. 그리고 빠른 검색을 위해 수집장치 ID와 모니터링 지표 ID를 지표로 활용하세요. Batch writingBulKCopy를 쓰기에 사용했습니다. 네, 그렇습니다. 수백만 개의 레코드를 몇 초 만에 쓴다고 합니다.
public static int BatchInert(string connectionString, string desTable, DataTable dt, int batchSize = 500)
{
using (var sbc = new SqlBulkCopy(connectionString, SqlBulkCopyOptions.UseInternalTransaction)
{
BulkCopyTimeout = 300,
NotifyAfter = dt.Rows.Count,
BatchSize = batchSize,
DestinationTableName = desTable })
{
foreach (DataColumn column in dt.Columns)
sbc.ColumnMappings.Add(column.ColumnName, column.ColumnName);
sbc.WriteToServer(dt);
}
return dt.Rows.Count;
}
무엇이 문제인가요?
위 아키텍처는 하루 4천만 개의 데이터에 적합합니다. 그러나 위의 배경에서 구성을 조정해 보니 중앙 집중식 모니터링 프로그램에서 메모리가 오버플로되는 현상이 발생했습니다. 분석 결과 너무 많은 데이터를 수신하여 메모리에 넣었으나 데이터베이스에 쓸 시간이 부족하여 결국 문제가 발생한 것으로 나타났습니다. 생성된 데이터 소비된 것보다 큰 데이터는 메모리 오버플로를 유발하고 프로그램이 작동하지 않습니다.병목 현상은 어디에 있나요?
RAID 디스크 문제 때문인가요? 데이터 구조 문제인가요? 하드웨어 문제인가요? SQL Server 버전에 문제가 있나요? 파티션 테이블에 문제가 있나요? 아니면 프로그램 문제인가요?그때는 일주일 밖에 안 남았는데, 프로젝트 감독님이 나가라고 하더군요. 그래서 48시간 동안 계속해서 일을 해야 했고, 여기저기에 전화해서 도움을 요청해야 했어요.. .
하지만 이때는 또 침착하고 침착해야 하는데...SQLServer 버전? 하드웨어? 현재로서는 교체될 가능성이 없습니다. RAID 디스크 어레이는 아닐 수도 있습니다. 그래서 정말 진정할 수가 없어요.
현장의 긴장된 분위기가 이해가 안 되실 수도 있겠지만, 사실 너무 오랜 시간이 흐른 뒤에도 그 상황으로 돌아가기는 힘들 것 같아요. 하지만 지금은 다양한 방법이 있을 수도 있고, 아웃사이더로서 생각이 많아졌을 수도 있지만, 프로젝트가 포기할 정도로 압박을 가할 때 현장의 환경적 요인에 의해 그때의 생각과 배려가 제약을 받는다고 할 수 있습니다. .큰 편차가 발생할 수 있습니다. 그것은 당신을 빠르게 생각하게 만들 수도 있고, 당신의 생각을 정체하게 만들 수도 있습니다. 이러한 압박감이 심한 환경에서 일부 동료들은 더 낮은 수준의 실수까지 저질렀고, 그들의 사고는 완전히 엉망이 되었으며, 효율성은 더욱 떨어졌습니다... 그들은 36시간 동안 한잠도 자지 않았거나, 그냥 가만히 있었습니다. 공사장 (비 오는 날 여기저기 진흙이 있었고, 말랐다면 그때쯤이면 다 진흙이 됐을 거에요) 두세 시간 정도 눈을 가늘게 뜨고 일주일 정도 계속하세요! 아니면 계속하세요!
많은 분들이 많은 아이디어를 주셨는데 유용한 것 같기도 하고 쓸모없는 것 같기도 합니다. 잠깐, 왜 "작동하는 것 같지만 작동하지 않는 것"입니까? 막연하게 방향의 힌트를 얻은 것 같습니다. 그것은 무엇입니까? 그런데 검증을 해보니 지금은 라이브 환경에서 실행되고 있는데, 이전에는 문제가 없었지만, 현재의 압박 하에서 그런 작은 기능을 분석하는 것은 너무 큰 영향을 미친다는 뜻입니다. , 그래서 우리는 그것을 분해해야 합니다. 예, 단일 방법을 테스트하는 "단위 테스트"입니다. 각 기능을 검증하려면 각 단계에 시간이 걸리나요?
시스템 병목 현상을 검증하기 위한 단계별 테스트
BulkCopy의 매개변수 수정
우선 내가 생각한 것은 BulkCopy의 매개변수, BulkCopyTimeout, 를 수정하는 것이었다. BatchSize , 지속적인 테스트 및 조정으로 인해 결과는 항상 특정 범위 내에서 변동되지만 실제 영향은 없습니다. 일부 CPU 수에 영향을 미칠 수 있지만 쓰기 속도는 여전히 5초에 10,000~2,000회 사이를 오가며 20초에 20,000,000개의 레코드를 작성해야 하는 요구 사항과는 거리가 멀습니다. BulkCopyTimeout、BatchSize，不断的测试调整，结果总是在某个范围波动，实际并没有影响。或许会影响一些CPU计数，但是远远没有达到我的期望，写入的速度还是在5秒1w~2w波动，远远达不到要求20秒内要写20w的记录。
按采集设备存储
是的，上述结构按每个指标每个值为一条记录，是不是太多的浪费？那么按采集设备+采集时间作为一条记录是否可行?问题是，怎么解决不同采集设备属性不一样的问题？这时，一个同事发挥才能了，监控指标+监控值可以按XML格式存储。哇，还能这样？查询呢，可以用for XML这种形式。
于是有了这种结构：No、MgrObjId、Dtime、XMLData
수집 장치별 보관
네, 위의 구조는 각 지표의 값을 기록하는 것이 너무 낭비인가요? 그렇다면 수집 장치 + 수집 시간을 기록으로 사용하는 것이 가능한가? 문제는 수집 장치마다 다른 속성이 있는 문제를 어떻게 해결하느냐 하는 것입니다. 이때 동료의 재능을 발휘한 모니터링 지표 + 모니터링 값을 XML 형식으로 저장할 수 있습니다. 와, 이런 일이 일어날 수 있나요? 쿼리의 경우 XML에 사용할 수 있습니다. 그래서 다음과 같은 구조가 있습니다: No, MgrObjId, Dtime, XMLData
데이터 테이블 분할???
다른 프로그램 중지
이것이 SQL Server의 병목 현상인가요? 아니, 이것이 SQL Server의 병목 현상인가요? 관련 정보를 온라인으로 확인해보니 IO의 병목 현상일 수도 있겠군요. 젠장, 서버를 업그레이드하고 데이터베이스를 교체해야 하는데 프로젝트 측에서 제공해줄까요?
잠깐만요 또 뭔가 있는 것 같아요 인덱스 맞죠 인덱스! 인덱스의 존재는 삽입 및 업데이트에 영향을 미칩니다
인덱스 제거예, 인덱스를 제거한 후에는 확실히 쿼리 속도가 느려지지만 먼저 인덱스를 제거하면 쓰기 속도가 빨라지는지 확인해야 합니다. MgrObjId 및 Id 필드의 인덱스를 결정적으로 제거하는 경우.
실행하면 기적이 일어났습니다. 매번 100,000개의 레코드가 기록되고, 7~9초 내에 기록될 수 있어 시스템 요구 사항을 충족합니다.
질의를 해결하는 방법은?
🎜테이블에는 하루에 4억 개가 넘는 레코드가 필요하며, 이는 인덱스 없이는 질의가 불가능합니다. 무엇을 해야 하는가! ? 나는 우리의 예전 방식인 물리적 하위 테이블을 다시 생각했습니다. 네, 원래는 일 단위로 일정을 나누었는데 이제는 시간 단위로 일정을 나눕니다. 그리고 24개의 테이블이 있으며 각 테이블은 약 1,800만 개의 레코드만 저장하면 됩니다. 🎜🎜그런 다음 한 시간 또는 몇 시간 내에 속성 기록을 쿼리합니다. 결과는 다음과 같습니다: 천천히! 느린! ! 느린! ! ! 인덱싱 없이 천만 개가 넘는 레코드를 쿼리하는 것은 상상할 수 없습니다. 또 무엇을 할 수 있나요? 🎜🎜테이블을 계속 분할하면 기본 수집기에 따라 테이블을 계속 분할할 수도 있다고 생각했습니다. 왜냐하면 수집 장비는 수집자마다 다르기 때문입니다. 그런 다음 과거 곡선을 쿼리하면 과거 곡선만 확인할 수 있습니다. 단일 지표의 경우 여러 테이블에 분산될 수 있습니다. 🎜说干就干，结果，通过按10个采集嵌入式并按24小时分表，每天生成240张表(历史表名类似这样：His_001_2014112615)，终于把一天写入4亿多条记录并支持简单的查询这个问题给解决掉了！！！
查询优化
在上述问题解决之后，这个项目的难点已经解决了一半，项目监管也不好意思过来找茬，不知道是出于什么样的战术安排吧。
过了很长一段时间，到现在快年底了，问题又来了，就是要拖死你让你在年底不能验收其他项目。
这次要求是这样的：因为上述是模拟10w个监控指标，而现在实际上线了，却只有5w个左右的设备。那么这个明显是不能达到标书要求的，不能验收。那么怎么办呢？这些聪明的人就想，既然监控指标减半，那么我们把时间也减半，不就达到了吗：就是说按现在5w的设备，那你要10s之内入库存储。我勒个去啊，按你这个逻辑，我们如果只有500个监控指标，岂不是要在0.1秒内入库？你不考虑下那些受监控设备的感想吗？
但是别人要玩你，你能怎么办？接招呗。结果把时间降到10秒之后，问题来了，大家仔细分析上面逻辑可以知道，分表是按采集器分的，现在采集器减少，但是数量增加了，发生什么事情呢，写入可以支持，但是，每张表的记录接近了400w，有些采集设备监控指标多的，要接近600w，怎么破？
于是技术相关人员开会讨论相关的举措。
在不加索引的情况下怎么优化查询？
有同事提出了，where子句的顺序，会影响查询的结果，因为按你刷选之后的结果再处理，可以先刷选出一部分数据，然后继续进行下一个条件的过滤。听起来好像很有道理，但是SQLServer查询分析器不会自动优化吗？原谅我是个小白，我也是感觉而已，感觉应该跟VS的编译器一样，应该会自动优化吧。
具体怎样，还是要用事实来说话：
结果同事修改了客户端之后，测试反馈，有较大的改善。我查看了代码：
难道真的有这么大的影响？等等，是不是忘记清空缓存，造成了假象？
于是让同事执行下述语句以便得出更多的信息：
--优化之前DBCC FREEPROCCACHEDBCC DROPCLEANBUFFERSSET STATISTICS IO ONselect Dtime,Value from dbo.his20140825 WHERE Dtime>='' AND Dtime<='' AND MgrObjId='' AND Id=''SET STATISTICS IO OFF--优化之后DBCC FREEPROCCACHEDBCC DROPCLEANBUFFERSSET STATISTICS IO ONselect Dtime,Value from dbo.his20140825 WHERE MgrObjId='' AND Id='' AND Dtime>='' AND Dtime<=''SET STATISTICS IO OFF
结果如下：
优化之前反而更好了？
仔细查看IO数据，发现，预读是一样的，就是说我们要查询的数据记录都是一致的，物理读、表扫描也是一直的。而逻辑读取稍有区别，应该是缓存命中数导致的。也就是说，在不建立索引的情况下，where子句的条件顺序，对查询结果优化作用不明显。
那么，就只能通过索引的办法了。
建立索引的尝试
建立索引不是简单的事情，是需要了解一些基本的知识的，在这个过程中，我走了不少弯路，最终才把索引建立起来。
下面的实验基于以下记录总数做的验证：
按单个字段建立索引
这个想法，主要是受我建立数据结构影响的，我内存中的数据结构为：Dictionary44c8bf3c168555d7e46180150f40c496>。我以为先建立MgrObjId的索引，再建立Id的索引，SQLServer查询时，就会更快。
先按MgrObjId建立索引，索引大小为550M，耗时5分25秒。结果，如上图的预估计划一样，根本没有起作用，反而更慢了。
按多个条件建立索引
OK，既然上面的不行，那么我们按多个条件建立索引又如何？CREATE NONCLUSTERED INDEX Idx_His20141008 ON dbo.his20141008(MgrObjId,Id,Dtime)
结果，查询速度确实提高了一倍：
等等，难道这就是索引的好处？花费7分25秒，用1.1G的空间换取来的就是这些？肯定是有什么地方不对了，于是开始翻查资料，查看一些相关书籍，最终，有了较大的进展。
正确的建立索引
首先，我们需要明白几个索引的要点：
- 인덱싱 후 가장 적게 반복되는 인덱스 필드를 기준으로 정렬하면 최적의 효과를 얻을 수 있습니다. 우리 테이블의 경우 No라는 클러스터형 인덱스가 설정된 경우 where 절에 No를 먼저 입력하고 Id, MgrObjId, 마지막으로 시간 인덱스를 입력하는 것이 가장 좋습니다.
-
where 절의 순서에 따라 쿼리 분석기가 쿼리 에 인덱스를 사용할지 여부가 결정됩니다. 예를 들어 MgrObjId 및 Id의 인덱스가 설정된 경우
where MgrObjId='' and Id='' and Dtime=''는 인덱스 검색을 사용하고where Dtime='' MgrObjId= '' 및 Id=''는 반드시 색인 검색을 사용하는 것은 아닙니다.where MgrObjId='' and Id='' and Dtime=''就会采用索引查找，而where Dtime='' and MgrObjId='' and Id=''则不一定会采用索引查找。 - 把非索引列的结果列放在包含列中。因为我们条件是MgrObjId和Id以及Dtime，因此返回结果中只需包含Dtime和Value即可，因此把Dtime和Value放在包含列中，返回的索引结果就有这个值，不用再查物理表，可以达到最优的速度。
跟上述几点原则，我们建立以下的索引：CREATE NONCLUSTERED INDEX Idx_His20141008 ON dbo.his20141008(MgrObjId,Id) INCLUDE(Value,Dtime)
인덱싱되지 않은 열의 결과 열을 포함 열
에 넣습니다. 우리의 조건은 MgrObjId, Id 및 Dtime이므로 반환 결과에 Dtime과 Value만 포함하면 됩니다. 따라서 포함된 열에 Dtime과 Value를 넣으면 반환된 인덱스 결과에 이 값이 포함되므로 확인할 필요가 없습니다. 더 이상 물리적 테이블이 최적의 속도를 달성하지 못합니다.
위 원칙에 따라 다음 인덱스를 생성합니다: CREATE NONCLUSTERED INDEX Idx_His20141008 ON dbo.his20141008(MgrObjId,Id) INCLUDE(Value,Dtime)
소요 시간은 6 이상입니다. 분 시계, 인덱스 크기는 903M입니다.
예상 계획을 살펴보겠습니다.
보시다시피 여기서는 추가 소모 없이 인덱스가 완전히 사용됩니다. 실제 실행 결과는 1초도 채 안 걸렸고, 1초도 안 되어 1,100만 건의 레코드에서 결과가 필터링되었습니다! ! 정말 잘생겼어요! !
인덱스를 적용하는 방법은?
쓰기가 완료되고 읽기가 완료되었으니 어떻게 조합해야 할까요? 한 시간 전의 데이터는 인덱싱할 수 있지만 현재 시간의 데이터는 인덱싱할 수 없습니다. 즉, 테이블을 생성할 때 인덱스를 생성하지 마세요! !
어떻게 최적화할 수 있나요? 읽기와 쓰기를 분리하고 두 개의 라이브러리를 작성할 수 있습니다. 하나는 실시간 라이브러리이고 다른 하나는 읽기 전용 라이브러리입니다. 1시간 이내의 데이터는 실시간 데이터베이스에서 조회하고, 1시간 전의 데이터는 읽기 전용 데이터베이스에서 조회하고, 읽기 전용 데이터베이스는 정기적으로 저장한 후 일주일 동안의 데이터를 분석, 처리 및 인덱싱합니다. 그런 다음 저장됩니다. 이렇게 하면 어떤 기간에 데이터를 쿼리하더라도 정확하게 처리할 수 있습니다. 실시간 데이터베이스는 1시간 내에 쿼리하고, 읽기 전용 데이터베이스는 1시간~1주일 내에 쿼리하며, 리포트 데이터베이스는 일주일 내에 쿼리합니다. 전에. 물리적 테이블 샤딩이 필요하지 않은 경우 읽기 전용 라이브러리에서 정기적으로 인덱스를 재구축할 수 있습니다.
- 요약
- SQL Server에서 수십억 개의 데이터(기록 데이터)를 처리하는 방법은 다음과 같이 진행할 수 있습니다.
- 테이블의 모든 인덱스 제거
- SqlBulkCopy를 사용하여 삽입
- 테이블 또는 파티션을 분할하여 크기 줄이기 각 테이블의 데이터 총량
- 테이블 작성이 완료된 후 인덱스 생성
- 인덱스 필드를 올바르게 지정하세요
위 내용은 SQL Server에서 수천만 개의 레코드를 처리하는 방법 알아보기의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!