Mysql は大規模なデータ テーブルをどのように処理しますか?ソリューションの共有

Mysql は大規模なデータ テーブルをどのように処理しますか?次の記事では、Mysql ビッグ データ テーブル処理ソリューションを紹介します。お役に立てば幸いです。

シナリオ:

ビジネス データベース テーブル内のデータがますます増え、あなたと私が次のような状況に遭遇したとします。同様のシナリオで、この問題を一緒に解決しましょう

• データの挿入にはクエリに長い時間がかかります
• その後のビジネス要件の拡大は、新しい分野に大きな影響を与えますテーブル内
• テーブル内のすべてのデータが有効なデータであるわけではありません。クエリが必要なのは、

#評価テーブルのデータ ボリューム #We データ量は、テーブル容量/ディスク容量/インスタンス容量の 3 つの側面から評価できます。次に、

テーブル容量:

テーブルを展開して見てみましょう容量は主に、レコード数、平均長、増加、読み取りおよび書き込みボリューム、テーブルの合計サイズに基づいて評価されます。一般に、OLTP テーブルの場合、1 つのテーブルのデータ行数が 2,000 万行を超えず、合計サイズが 15G 以内であることが推奨されます。アクセス量: 単一テーブルの読み取りおよび書き込み量は 1600/s 以内です

行データのクエリ方法: テーブル内にどれだけのデータがあるかをクエリするときに通常使用する古典的な SQL ステートメントは次のとおりです。

select count(*) from table

• select count(1) from table ただし、データ量が多すぎる場合、このようなクエリはタイムアウトする可能性があるため、クエリ メソッドを変更する必要があります

#ライブラリ名を使用

• show table status like 'table name' ; または show table status like 'table name'\G ;

• 上記のメソッドは、テーブルのデータをクエリするだけではありませんテーブルの詳細については、\G を追加して出力をフォーマットします。テーブル名、ストレージ エンジンのバージョン、行数、1 行あたりのバイト数などが含まれます。自分で試すことができます。

ディスク容量

指定されたデータベース容量を表示します。

select
table_schema as '数据库',
table_name as '表名',
table_rows as '记录数',
truncate(data_length/1024/1024, 2) as '数据容量(MB)',
truncate(index_length/1024/1024, 2) as '索引容量(MB)'
from information_schema.tables
order by data_length desc, index_length desc;

単一データベース内のすべてのテーブルのディスク使用量をクエリします

select
table_schema as '数据库',
table_name as '表名',
table_rows as '记录数',
truncate(data_length/1024/1024, 2) as '数据容量(MB)',
truncate(index_length/1024/1024, 2) as '索引容量(MB)'
from information_schema.tables
where table_schema='mysql'
order by data_length desc, index_length desc;

クエリの結果は次のとおりです:

推奨データディスク使用量に占める量は 70% 以内です。同時に、急速に増大する一部のデータについては、データのアーカイブに大規模で遅いディスクの使用を検討できます (アーカイブについては、プラン 3 を参照してください)

インスタンス容量

MySQL は、スレッドベースのサービス モデルであるため、同時実行性が高い一部のシナリオでは、単一インスタンスではサーバーの CPU リソースを完全に活用できず、スループットが mysql 層でスタックします。ビジネスに基づいて独自のインスタンス モードを検討できます。

問題の原因

データ テーブルのサイズは上記ですでにわかっていますが、単一テーブルのデータ サイズが大きくなる根本原因は何でしょうか。 、ビジネスの実行効率は遅くなりますか？

テーブル内のデータの量が数千万または数億に達すると、インデックスを追加した効果はそれほど明白ではありません。パフォーマンスが低下する理由は、インデックスを維持するための

B

ツリー構造のレベルが高くなり、データをクエリする際に多くのディスク IO が必要となるため、クエリのパフォーマンスが低下するためです。

B ツリーに保存できるデータ量をまだ覚えていますか?

InnoDB ストレージ エンジンの最小ストレージ単位はページであり、ページのサイズは 16k

です。

B ツリーの葉にはデータが格納され、内部ノードにはキー値のポインターが格納されます。索引構成表は、非リーフ・ノードとポインターの二分検索方法によってデータがどのページにあるかを判別し、データ・ページに移動して必要なデータを見つけます。 B ツリーの高さが

2

の場合、1 つのルート ノードと複数のリーフ ノードがあるとします。この B ツリーに格納されるレコードの総数は、= ルート ノード ポインターの数 * 1 つのリーフ ノードに記録される行の数です。

レコードの行のデータ サイズが 1k の場合、1 つのリーフ ノードに格納できるレコードの数 =16k/1k =16.

いくつポインタは非リーフノードに格納されますか?主キー ID は bigint 型で、長さは 8 バイト であると仮定します (

インタビュアーは int 型について質問しました。int は 32 ビット、4 バイトです

)。 InnoDB ソース コードではポインター サイズが 6 バイトに設定されているため、8 6 = 14 バイト、16k/14B = 16*1024B/14B = 1170
• したがって、B ツリーは次のようになります。高さ 2 では 1170 * 16=18720 のようなデータ レコードを保存できます。同様に、高さ 3 の B ツリーは 1170 *1170 *16 =21902400 を保存できます。これは、約 2,000 万件のレコードを保存できることを意味します。 B ツリーの高さは通常 1 ～ 3 レイヤーであり、数千万レベルのデータのストレージ要件を満たすことができます。

B ツリーがより多くのデータを保存したい場合、ツリー構造のレベルが高くなり、データをクエリするときに必要なディスク IO が増えるため、クエリのパフォーマンスが遅くなります。

単一テーブル内のデータが多すぎてクエリが遅いという問題を解決する方法

根本原因を理解した後、データベースを最適化する方法を検討する必要があります。問題を解く＃ ＃＃

这里提供了三种解决方案，包括数据表分区，分库分表，冷热数据归档 了解完这些方案之后大家可以选取适合自己业务的方案

方案一：数据表分区

为什么要分区：表分区可以在区间内查询对应的数据，降低查询范围 并且索引分区 也可以进一步提高命中率，提升查询效率

分区是指将一个表的数据按照条件分布到不同的文件上面，未分区前都是存放在一个文件上面的，但是它还是指向的同一张表，只是把数据分散到了不同文件而已。

我们首先看一下分区有什么优缺点：

表分区有什么好处？

• 与单个磁盘或文件系统分区相比，可以存储更多的数据。

• 对于那些已经失去保存意义的数据，通常可以通过删除与那些数据有关的分区，很容易地删除那些数据。相反地，在某些情况下，添加新数据的过程又可以通过为那些新数据专门增加一个新的分区，来很方便地实现。

• 一些查询可以得到极大的优化，这主要是借助于满足一个给定WHERE语句的数据可以只保存在一个或多个分区内，这样在查找时就不用查找其他剩余的分区。因为分区可以在创建了分区表后进行修改，所以在第一次配置分区方案时还不曾这么做时，可以重新组织数据，来提高那些常用查询的效率。

• 涉及到例如SUM()和COUNT()这样聚合函数的查询，可以很容易地进行并行处理。这种查询的一个简单例子如 “SELECT salesperson_id, COUNT (orders) as order_total FROM sales GROUP BY salesperson_id；”。通过“并行”，这意味着该查询可以在每个分区上同时进行，最终结果只需通过总计所有分区得到的结果。

• 通过跨多个磁盘来分散数据查询，来获得更大的查询吞吐量。

表分区的限制因素

• 一个表最多只能有1024个分区。

• MySQL5.1中，分区表达式必须是整数，或者返回整数的表达式。在MySQL5.5中提供了非整数表达式分区的支持。

• 如果分区字段中有主键或者唯一索引的列，那么多有主键列和唯一索引列都必须包含进来。即：分区字段要么不包含主键或者索引列，要么包含全部主键和索引列。

• 分区表中无法使用外键约束。

• MySQL的分区适用于一个表的所有数据和索引，不能只对表数据分区而不对索引分区，也不能只对索引分区而不对表分区，也不能只对表的一部分数据分区。

在进行分区之前可以用如下方法 看下数据库表是否支持分区哈

mysql> show variables like '%partition%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| have_partitioning | YES   |
+-------------------+-------+
1 row in set (0.00 sec)

方案二：数据库分表

为什么要分表：分表后，显而易见，单表数据量降低，树的高度变低，查询经历的磁盘io变少，则可以提高效率

mysql 分表分为两种 水平分表和垂直分表

分库分表就是为了解决由于数据量过大而导致数据库性能降低的问题，将原来独立的数据库拆分成若干数据库组成 ，将数据大表拆分成若干数据表组成，使得单一数据库、单一数据表的数据量变小，从而达到提升数据库性能的目的。

水平分表

定义：数据表行的拆分，通俗点就是把数据按照某些规则拆分成多张表或者多个库来存放。分为库内分表和分库。 比如一个表有4000万数据，查询很慢，可以分到四个表，每个表有1000万数据

垂直分表

定义：列的拆分，根据表之间的相关性进行拆分。常见的就是一个表把不常用的字段和常用的字段就行拆分，然后利用主键关联。或者一个数据库里面有订单表和用户表，数据量都很大，进行垂直拆分，用户库存用户表的数据，订单库存订单表的数据

缺点：垂直分隔的缺点比较明显，数据不在一张表中，会增加join 或 union之类的操作

知道了两个知识后，我们来看一下分库分表的方案

1. モジュロ スキーム:

分割する前に、データ量を見積もります。たとえば、user テーブルには 4,000 万のデータがあり、データを 4 つのテーブル user1 user2 uesr3 user4 に分割する必要があります。 たとえば、id = 17, 17 modulo 4 は 1 に を加えたものであるため、このデータは user2 テーブルに格納されます。

注: Auto_increment は、水平分割後にテーブルから削除する必要があります。このときのIDはID自己増加テンポラリテーブルを利用するか、redis incrメソッドを利用して取得できます。 ＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃アドバンテージ： データはさまざまなテーブルに均等に分割されており、ホットな問題が発生する可能性は非常に低くなります。

デメリット: 将来的なデータの拡張や移行が難しくなる データ量が多くなると、これまで4テーブルに分割されていたものが8テーブルに分割され、モジュロ値が変化し、再度データ移行を行う必要があります。

2.range range スキーム

データを範囲ごとに分割します。つまり、特定の範囲内の注文が特定のテーブルに格納されます。たとえば、user1 テーブルには id=12 が格納され、user2 テーブルには id=1300 万が格納されます。

利点: 将来のデータ拡張に役立つ

欠点: ホット データが 1 つのテーブルに存在する場合、一方のテーブルにはプレッシャーがかかり、もう一方のテーブルにはプレッシャーがかかります。ゲージに圧力はかかりません。

上記の 2 つのソリューションには欠点もありますが、補完的なものであることがわかりました。では、これら 2 つのソリューションを組み合わせるとどうなるでしょうか?

3. ハッシュ係数と範囲スキームの組み合わせ

下の図に示すように、グループ group には 0 から 4,000 万までの ID を持つデータが格納されていることがわかります。 、DB0 という 3 つのデータベースがあります。 DB1、DB2、DB0 には 4 つのデータベースがあり、DB1 と DB2 には 3 つのデータベースがあります。

ID が 15000 の場合、10 の剰余を取得します (なぜテーブルが 10 個あるため、10 のモジュロ)、0 を取得して DB_0 に入り、範囲に従って Table_0 に入ります。

#要約: ハッシュ係数とレンジスキームを組み合わせると、ホットデータの問題を回避できるだけでなく、将来のデータの拡張も容易になります

すでに知っています mysql のパーティショニングとサブテーブルについての知識が得られたので、2 つのテクノロジーの違いと適用可能なシナリオを見てみましょう

パーティショニングとサブテーブルの違い:

1 , 実装面では

mysql のサブテーブルは実際のサブテーブルであり、1 つのテーブルを複数のテーブルに分割した後、それぞれの小さなテーブルは、3 つのファイル (.MYD データ ファイル、.MYI インデックス ファイル、.frm テーブル構造) に対応する完全なテーブルです。

パーティションは異なります。大きなテーブルはパーティション化された後も、依然として 1 つです。 table と 2 つのテーブルにはなりませんが、データを格納するブロックの数が増えます。

#2. パフォーマンスの向上
• テーブルのサブディビジョンの焦点は、データにアクセスする際の mysql の同時実行性を向上させる方法です。

Asパーティションの場合、mysql のパフォーマンスを向上させるという目的を達成するために、ディスクの読み取りおよび書き込み機能を突破する方法。

3. 実装の難しさ
• 1. テーブルを分割する方法はたくさんありますが、マージを使用してテーブルを分割するのが最も簡単です。この方法はルート分割とほぼ同じ難易度であり、プログラム コードに対して透過的です。他のテーブルパーティショニング方法を使用する場合は、パーティショニングよりも面倒になります。 2. パーティションの実装は比較的単純です。パーティション テーブルの作成と通常のテーブルの構築に違いはなく、コードエンドに対して透過的です。

パーティションとテーブルの関係

1. mysql のパフォーマンスを向上させ、同時実行性が高い状況でも優れたパフォーマンスを発揮します。

2. テーブルのパーティショニングとパーティショニングは矛盾するものではなく、相互に連携することができます。アクセス数が多く、テーブルのデータが大量にあるテーブルの場合は、テーブルのパーティショニングとパーティショニングを組み合わせることができます。訪問数は大きくありませんが、大量のデータを含むテーブルの場合は、テーブルを分割できます。

サブデータベースとサブテーブルの問題

1. トランザクションの問題

サブデータベースとサブテーブルの実行後-table のせいで、データが別のライブラリに保存され、データベースのトランザクション管理が困難になります。データベース自体の分散トランザクション管理機能に依存してトランザクションを実行すると、高いパフォーマンスの代償を払うことになりますが、アプリケーションが制御を補助してプログラムロジックのトランザクションを形成する場合は、プログラミングの負担も発生します。

2. クロスデータベースおよびクロステーブル結合の問題

サブデータベースとテーブルのサブデータベースを実行した後、元々論理的に高度に関連していたものを分割することは避けられません。データを異なるテーブルおよび異なるライブラリに保存すると、この時点でテーブルの関連付け操作が制限されます。異なるサブデータベースにあるテーブルを結合したり、サブテーブルの粒度が異なるテーブルを結合したりすることはできません。その結果、 1 つのクエリで完了できるビジネスは不可能である可能性があり、完了するには複数のクエリが必要です。

3. 余分なデータ管理の負担とデータ計算のプレッシャー

余分なデータ管理の負担、最も明白なのはデータの配置の問題と、データの追加、削除、変更の繰り返しの実行です。と query , これらはアプリケーションを通じて解決できますが、必然的に追加の論理演算が発生します。たとえば、ユーザー スコアを記録するユーザー データ テーブル userTable の場合、ビジネスでは最高 100 のスコアを見つける必要があります。テーブルを分割する前に、 order by ステートメントは実行できますが、テーブルを分割した後、分割された各テーブルの上位 100 人のユーザー データを検索し、データをマージして結果を取得するには、n 回の order by ステートメントが必要になります。

オプション 3: ホット アーカイブとコールド アーカイブ

ホット アーカイブとコールド アーカイブが必要な理由: 実際、理由は 2 番目のオプションと同様で、単一テーブル内のデータ量が減少し、ツリーの高さが低くなると、クエリで発生するディスク IO が減少し、効率が向上します。 たとえば、ビジネス データにホットとコールドの明確な区別がある場合、過去 1 週間または過去 1 か月のデータのみを表示する必要があります。この場合、今週と月間のデータをホットデータ、残りのデータをコールドデータと呼びます。その後、コールド データを他のデータベース テーブルにアーカイブして、ホット データの運用効率を向上させることができます。

次はアーカイブ プロセスについて話しましょう

• アーカイブ テーブルを作成します 原則として、作成したアーカイブテーブルは元のテーブルと一致している必要があります。

• アーカイブテーブルデータの初期化

• ビジネス増分データ処理プロセス

• ##データ取得プロセス

#上記の 3 つのオプションを選択するにはどうすればよいですか

オプションデータ テーブル パーティション数千万を超えるデータテーブルのサブテーブルホットアーカイブとコールドアーカイブのサブライブラリ お客様のビジネス シナリオに応じて、お客様のビジネスに適したソリューションを選択できます。いくつかのアイデアを提供します ~

トライアルシナリオ	利点	欠点
1. 大量のデータ 2 。クエリシナリオは特定の領域のみ 3. 結合クエリシナリオは存在しない	パーティショニングとテーブルパーティショニングとは、データテーブルに対応するファイルを物理的に分割することです。 sql	など、テーブル分割後の以前の業務ロジックに影響を与える業務は、対応するオブジェクトを作成するため、一定のオーバーヘッドが発生し、分割されたデータの集計にも時間がかかり、利用範囲が制限される
データ量が多く、区別できない	ホット領域とコールド領域の境界が明確でないデータに適しており、後続の同様のデータに対してもこの方法を使用できます。クエリ挿入の効率を高めるために、大きなテーブルを小さなテーブルに分割する	大きなデータ テーブルが徐々に増加する場合、対応するデータベース テーブルの数がますます必要になり、各テーブルをテーブルに分割する必要があります。間隔の分割は比較的固定されており、後続の単一テーブルのデータ量が増加すると、パフォーマンスにも影響します。実装の複雑さは 3 番目のソリューションよりも複雑で、全体をテストする必要があります。コーディング層で処理され本来の業務に影響を与える;
データ量が多く、コールドデータのホット パーティションが明らかである; コールド データの使用頻度が非常に低い;	データ移行のプロセスはビジネスへの影響が少なく、開発量もコストも削減されます。	テーブル分割ルールを確認する必要があります

ということで、ここで私が話したいことは終わりに近づいてきましたが、もし何か間違っていることや疑問に思っていることがあれば、ぜひアドバイスをいただければ幸いです。

[関連する推奨事項:

mysql ビデオ チュートリアル

]

以上がMysql は大規模なデータ テーブルをどのように処理しますか?ソリューションの共有の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。