SQL Server で数千万のレコードを処理する方法を学ぶ
- coldplay.xixi転載
- 2020-11-27 16:42:299445ブラウズ
sqlチュートリアル 列では、数千万のレコードを処理する方法を紹介します。
推奨: sql チュートリアル
プロジェクトの背景
これはデータセンターのプロジェクトをやりました。プロジェクトの難易度は桁外れでした。このプロジェクトを見て、ショッピングモールは戦場のようなものだと実感しました。私はそこにいるただの兵士です。戦術が多すぎて、対立が多すぎます。高官間の争い、内輪話が多すぎる。このプロジェクトの具体的な状況については、時間があるときに関連するブログ記事を書きたいと思います。
このプロジェクトでは環境モニタリングが必要となるため、当面、監視対象機器を収集装置と呼び、収集機器の属性をモニタリング指標と呼びます。プロジェクト要件: システムは 10w 以上のモニタリング インジケーターをサポートし、各モニタリング インジケーターのデータ更新 は 20 秒 を超えてはならず、ストレージ遅延 を超えてはなりません。 120秒。次に、簡単な計算によって理想的な状態を得ることができます。保存されるデータは、30w/分、1800w/時間、つまり 1 日あたり 4 億 3,200 万 です。実際にはこれより5%程度データ量が多くなります。 (実際には、そのほとんどは情報のゴミであり、データ圧縮によって処理できますが、他の人があなたに干渉したい場合はどうすればよいでしょうか)
上記は、プロジェクトに必要な指標です。ビッグデータ処理がたくさんあると思います 経験豊富な学生はそれを笑うでしょう、それだけですか？さて、私もビッグデータ処理についてはよく読んだことがありますが、これまで扱ったことはありませんでした。他の人の明確な説明を見ると、何が分散されているのか、何が読み書きを分けているのかを解決するのは非常に簡単に思えます。しかし、問題はそれほど単純ではなく、上で述べたように、これは非常に悪質なプロジェクトであり、業界における悪質な競争の典型的なプロジェクトです。
- これ以上サーバーはありませんが、データベースと集中コレクター (つまり、データ分析、アラーム、ストレージ プログラム) に加えて、このサーバーは 300,000 ポイントのノースバウンド インターフェイス (SNMP) もサポートしています。 ). プログラムが最適化される前は、年間を通じて CPU が 80% 以上を占めていました。このプロジェクトではデュアルマシンのホットスタンバイを使用する必要があるため、時間を節約し、不要なトラブルを軽減するために、HA (外部購入の HA システム) の特性を最大限に活用できるように関連サービスをまとめました
- システムデータ 精度要件は非常に異常であり、下位収集システムから上位レベルの監視システムまで、いかなるデータも異なるものであってはなりません。
当社のシステム アーキテクチャは次のとおりです。データベースへの負荷が非常に高く、特に LevelA ノードで高くなります:
- ハードウェア構成は次のとおりです:
CPU: インテル® Xeon® プロセッサー E5 -2609 (4 コア、2.40 GHz、10 MB、6.4 GT/秒)
メモリ: 4GB (2x2GB) DDR3 RDIMM メモリ、1333 MHz、ECC
ハードディスク: 500GB 7200 RPM 3.5 '' SATA3 ハードディスク、Raid5。 - データベース バージョン
は、SQLServer2012 標準バージョンを使用します。HP が提供する純正ソフトウェアには、エンタープライズ バージョンの多くの NB 機能がありません。
独自の linuxC/C コミュニケーション グループを推薦してください: 973961276!私が良いと思う学習書や動画教材、大手メーカーのインタビューなどをグループファイルにまとめて共有しているので、必要な友達は自分で追加してください！ ~
書き込みのボトルネック
最初に直面した障害は、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 をインデックスとして使用すると、迅速な検索が容易になります。
バッチ書き込み
書き込みは BulKCopy を使用して行われました。はい、それだけです。数秒で数百万のレコードを書き込むと主張しています
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;
} ## ＃＃＃＃どうしたの？ 上記のアーキテクチャは、1 日あたり 4,000 万のデータを処理できます。しかし、上記の背景で構成を調整したところ、集中監視プログラムがメモリオーバーフローを起こし、分析の結果、大量のデータを受信してメモリに置いたものの、データベースに書き込む時間がなくなり、最終的にはエラーが発生したことが判明しました。生成されたデータ 消費されたデータよりも大きいデータはメモリ オーバーフローを引き起こし、プログラムが動作しなくなります。
ボトルネックはどこですか? RAID ディスクの問題が原因でしょうか?データ構造の問題でしょうか？ハードウェアの問題ですか? SQL Serverのバージョンの問題でしょうか？パーティションテーブルがないのが問題でしょうか？それともプログラムの問題でしょうか？
当時は1週間しか時間がなかったので、そうでないとプロジェクト責任者に辞めてもらうということで、48時間働き続けるという偉業を達成し、あちこちの人に電話して助けを求めなければなりませんでした。 ..
しかし、この時に必要なのは、一度落ち着いて、また落ち着くことです...SQLServerのバージョンは？ハードウェア？現時点では交換される可能性は低いです。 RAID ディスク アレイ、おそらくそうではありません。それで何だろう?本当に落ち着かない。
現場の緊迫した雰囲気は理解できないかもしれませんが、実際、私も長い時間が経つと、あの状況に戻るのは難しいです。しかし、今はいろいろな方法があるのかもしれないし、部外者としての考えが増えているのかもしれませんが、プロジェクトで断念寸前まで追い込まれたとき、そのときの思考や考慮事項は現場の環境要因によって制限されてしまいます。 . 大きな差異が発生する可能性があります。思考が速くなることもあれば、思考が停滞してしまうこともあります。このプレッシャーのかかる環境では、一部の同僚はさらにレベルの低いミスを犯し、思考が完全に混乱し、効率がさらに低下しました... 彼らは 36 時間一睡もしなかったか、ただ仕事をし続けただけでした。工事現場（雨の日はどこもかしこも泥だらけだったけど乾いてた）終わったらその頃には全部泥になってる） 2～3時間目を細めて、それを1週間続ける！あるいは続けてください！
多くの人がたくさんのアイデアを出してくれていますが、役に立つようで役に立たないようです。ちょっと待って、なぜ「機能しているように見えるのに、機能していない」のでしょうか?なんとなく方向性のヒントが掴めたような気がするのですが、どうでしょうか？ちなみに検証ですが、現在本番環境で動作させています。以前は問題なかったのですが、現状では問題がないわけではありません。大きなシステムでこのような小さな機能を解析するのは影響が大きすぎます。 、だからそれを分解する必要があります。はい、それは単一メソッドのテストである「単体テスト」です。各機能を検証する必要があります。それぞれの独立したステップはどこに行くのでしょうか?
段階的にテストしてシステムのボトルネックを確認する
BulkCopy のパラメーターを変更する
まず、私が考えていることは次のとおりです。 BulkCopy 、 BulkCopyTimeout 、 BatchSize のパラメータを変更し、継続的なテストと調整を行うと、結果は常に一定の範囲内で変動しますが、実際の影響はありません。一部の CPU 数に影響する可能性がありますが、私の期待には程遠く、書き込み速度は 5 秒間に 10,000 回から 2,000 回の間で変動しており、20 秒で 20,000,000 件のレコードを書き込むという要件には程遠いです。
収集装置による保存
はい、上記の構造は各指標の値ごとの記録ですが、無駄が多すぎませんか？では、収集装置の収集時間を記録として使用することは可能でしょうか? 問題は、収集装置ごとに異なる属性の問題をどのように解決するかということです。このとき、同僚が才能を発揮し、監視指標と監視値をXML形式で保存できました。うわー、こんなことが起こり得るのか？クエリの場合は、XML を使用できます。
したがって、次の構造になります: No、MgrObjId、Dtime、XMLData
結果の検証は上記よりもわずかに優れていますが、あまり明らかではありません。
データ テーブルのパーティション???
当時、私はこのスキルを学んでいませんでした。インターネットの記事を読んだ後、非常に複雑に思えました。私にはあまり知識がありませんでした。時間があったので、あえて試しませんでした。
他のプログラムを停止します
ソフトウェアとハードウェアのアーキテクチャは一時的に変更できないため、これは間違いなく機能しないことはわかっています。しかし、これらの要因が影響しているかどうかを検証したいと思います。プロンプトは確かに明らかですが、それでも要件を満たしていないことが判明しました。
これは SQL Server のボトルネックですか?
まったくわかりませんが、これは SQL Server のボトルネックですか?関連情報をオンラインで確認したところ、それが IO のボトルネックである可能性があることがわかりました。くそー、他に何ができるでしょうか? サーバーをアップグレードしてデータベースを置き換える必要があります。しかし、プロジェクト パーティはそれを提供してくれるでしょうか?
待って、別のものがあるようです、インデックス、インデックス！インデックスの存在は挿入と更新に影響します
インデックスを削除します
はい、インデックスを削除するとクエリは確実に遅くなりますが、最初に次のことを確認する必要があります。インデックスを削除すると書き込み速度が向上します。 MgrObjId フィールドと Id フィールドのインデックスを思い切って削除した場合。
実行すると奇跡が起こりました。100,000 件のレコードが書き込まれるたびに 7 ～ 9 秒以内に書き込むことができ、システム要件を満たします。
クエリを解決するにはどうすればよいですか?
テーブルには 1 日に 4 億を超えるレコードが必要ですが、インデックスなしではクエリを実行することは不可能です。何をするか！ ?私は、物理的なサブテーブルという古い方法をもう一度考えました。はい、当初はスケジュールを日ごとに分割していましたが、現在は時間ごとにスケジュールを分割しています。次に、24 のテーブルがあり、各テーブルには約 1,800 万件のレコードのみを保存する必要があります。
次に、1 時間または数時間以内に属性の履歴レコードをクエリします。結果は「遅い！」遅い！ ！遅い！ ！ ！インデックスを作成せずに 1,000 万を超えるレコードをクエリすることはまったく想像できません。他に何ができるでしょうか?
テーブルの分割を続けて、基礎となるコレクターに従ってテーブルの分割を続けることもできると思いました。コレクターごとに収集装置が異なるため、履歴曲線をクエリするときにできるのは、単一のインジケーターをチェックする 履歴曲線はさまざまなテーブルに分散している場合があります。
说干就干，结果，通过按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、最後に time を入れるのが最善です。時間インデックスが 1 時間の場合は、入れないのが最善です。 use
-
where 句の順序によって、クエリ アナライザーが のクエリにインデックスを使用するかどうかが決まります。たとえば、MgrObjId と Id のインデックスが確立されている場合、
where MgrObjId='' および Id='' および Dtime=''はインデックス検索を使用し、where Dtime='' およびMgrObjId=' ' および Id=''は、必ずしもインデックス検索を使用するわけではありません。 - インデックスのない列の結果列を、含まれる列に入れます。条件は MgrObjId、Id、および Dtime であるため、返される結果に Dtime と Value を含める必要があるだけです。したがって、含まれる列に Dtime と Value を入力すると、返されるインデックス結果にはこの値が含まれます。チェックする必要はありません。もはや物理テーブルではなく、最適な速度を実現します。
上記の原則に従って、次のインデックスを作成します。 CREATE NONCLUSTERED INDEX Idx_His20141008 ON dbo.his20141008(MgrObjId,Id) INCLUDE(Value,Dtime)
インデックスの適用方法
書き込みと読み込みが完了したところで、これらをどのように結合するのでしょうか。 1 時間前のデータにはインデックスを付けることができますが、現在の時間のデータにはインデックスを付けることはできません。つまり、テーブルを作成するときにインデックスを作成しないでください。 ！他にどのように最適化できますか?
読み取りと書き込みを分離し、2 つのライブラリを作成してみることができます。1 つはリアルタイム ライブラリで、もう 1 つは読み取りライブラリです。唯一の図書館。 1 時間以内のデータはリアルタイム データベースでクエリされ、1 時間前のデータは読み取り専用データベースでクエリされます。読み取り専用データベースは定期的に保存され、インデックスが付けられます。1 週間にわたるデータは分析、処理され、その後保管されます。このようにして、データのクエリが行われる期間に関係なく、リアルタイム データベースのクエリは 1 時間以内、読み取り専用データベースのクエリは 1 時間から 1 週間以内、レポート データベースのクエリは 1 週間以内に実行されるなど、データを正しく処理できます。前。 物理テーブルのシャーディングが必要ない場合は、読み取り専用データベースでインデックスを定期的に再構築できます。概要
SQL Server で数十億のデータ (履歴データ) を処理する方法は、次のとおりです。- すべて削除テーブルのインデックス
- SqlBulkCopy を使用して挿入
- テーブルまたはパーティションを分割して各テーブルのデータの総量を削減します
- テーブルの書き込みが完了した後にインデックスを作成します
- Indexフィールドを正しく指定してください
以上がSQL Server で数千万のレコードを処理する方法を学ぶの詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。