추천: sql tutorial
데이터센터를 위한 프로젝트입니다. 프로젝트의 난이도가 정말 어마어마합니다. 전장에는 전술이 너무 많고, 고위 관료들 사이의 경쟁이 너무 많고, 내부 이야기가 너무 많습니다. 이번 프로젝트의 구체적인 상황에 대해서는 시간이 나면 관련 블로그 포스팅을 작성해보겠습니다.
이 프로젝트에는 환경 모니터링이 필요합니다. 당분간 모니터링되는 장비를 수집 장치라고 하며, 수집 장비의 속성을 모니터링 지표라고 합니다. 프로젝트 요구 사항: 시스템은 10w 모니터링 지표를 지원하고, 각 모니터링 지표의 데이터 업데이트는 20초를 초과할 수 없으며, 저장 지연은 120초을 초과할 수 없습니다. 그런 다음 간단한 계산을 통해 이상적인 상태를 얻을 수 있습니다. 저장되는 데이터는 분당 30w, 시간당 1800w, 하루 4억 3200만입니다. 실제 데이터의 양은 이보다 5% 정도 더 많을 것이다. (사실 대부분은 데이터 압축을 통해 처리할 수 있는 정보 쓰레기인데, 다른 사람들이 당신을 엉망으로 만들고 싶다면 어떻게 할 수 있습니까?)
위는 프로젝트에서 요구하는 지표입니다. 빅데이터 처리 경험이 많으신 분들은 스니핑만 하면 알겠죠? 글쎄요, 저도 빅데이터 처리에 대해 많이 읽었지만 한번도 다루어본 적이 없습니다. 다른 분들의 명쾌한 설명을 보면 무엇이 분산되어 있고 무엇이 읽기와 쓰기를 분리하는지 해결하기가 정말 쉬운 것 같습니다. 그러나 문제는 그렇게 간단하지 않습니다. 위에서 말했듯이 이것은 매우 나쁜 프로젝트이며 업계의 전형적인 악순환 프로젝트입니다.
우리가 직면한 첫 번째 장애물은 SQL Server가 기존 프로그램에서 그렇게 많은 양의 데이터를 처리할 수 없다는 점이었습니다. 구체적인 상황은 무엇입니까?
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를 지표로 활용하세요.
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; }
그때는 일주일 밖에 안 남았는데, 프로젝트 감독님이 나가라고 하더군요. 그래서 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
데이터 테이블 분할???
다른 프로그램 중지
잠깐만요 또 뭔가 있는 것 같아요 인덱스 맞죠 인덱스! 인덱스의 존재는 삽입 및 업데이트에 영향을 미칩니다
실행하면 기적이 일어났습니다. 매번 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的空间换取来的就是这些?肯定是有什么地方不对了,于是开始翻查资料,查看一些相关书籍,最终,有了较大的进展。
首先,我们需要明白几个索引的要点:
where MgrObjId='' and Id='' and Dtime=''
는 인덱스 검색을 사용하고 where Dtime='' MgrObjId= '' 및 Id=''
는 반드시 색인 검색을 사용하는 것은 아닙니다. where MgrObjId='' and Id='' and Dtime=''
就会采用索引查找,而where Dtime='' and MgrObjId='' and Id=''
则不一定会采用索引查找。跟上述几点原则,我们建立以下的索引: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만 건의 레코드에서 결과가 필터링되었습니다! ! 정말 잘생겼어요! !
쓰기가 완료되고 읽기가 완료되었으니 어떻게 조합해야 할까요? 한 시간 전의 데이터는 인덱싱할 수 있지만 현재 시간의 데이터는 인덱싱할 수 없습니다. 즉, 테이블을 생성할 때 인덱스를 생성하지 마세요! !
위 내용은 SQL Server에서 수천만 개의 레코드를 처리하는 방법 알아보기의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!