mysql-テーブルパーティション

今日のビッグデータストレージに直面して、mysqlの1つのテーブルの総記録が1000 Wを超えると、性能の大幅な低下が予想されますか?
答えは肯定的で、1つのテーブルの総記録は1000 Wを超え、オペレーティングシステムのレベルで検索するのも非常に効率的ではありません.
 
ソリューション:
現在、大量データの最適化には2つの方法があります.
 
1、大きい表は小さい表を分解する方式(主に分表と区分の両方の技術がある)
(1)分表技術
垂直分割
利点:高同時性の場合、テーブルのロックを低減します.
不足:単一テーブルでは、データベースの記録が増えるにつれて、読み書き圧力がさらに増大します.
 
 
すいへいぶんかつ
 
単一テーブルのIO圧力が大きい場合は,hashアルゴリズムにより1枚のテーブルをN複数ページに分割し,新しいテーブル(総テーブル)により各ページの位置を記録することを原理として水平分割が考えられる.ポータルサイトでデータベース・テーブルが1000万件に達している場合、selectで検索すると効率が低下します(インデックスを作らない前提).単一テーブルの読み書きIO圧力を低減するために、水平分割により、このテーブルを10ページに分割し、同時に1つの総テーブルを生成し、各ページの情報を記録すると、id=100の記録を検索すると、全テーブルスキャンを必要とせず、総テーブルを通じてどの対応するページに記録されているかを見つけ、対応するページを検索し、IO圧力を低減することができる.
 
水平表分割技術は、1つのテーブルを複数のテーブルに分割することであり、比較的一般的な方法は、テーブルのレコードをあるHASHアルゴリズムに従って分割することである.また、このパーティション化方法は、フロントエンドのアプリケーションのSQLを修正しなければ使用できない.また、1つのSQL文については、2つのテーブルを修正する可能性がある.では、2つのSQL文を修正して論理的なトランザクションを完了する必要があります.論理的な判断がますます複雑になり、プログラムのメンテナンスコストが増加するため、このような状況を回避する必要があります.
 
2、SQL文の最適化(索引)
SQL文の最適化:インデックスを増やすなどして調整できますが、データ量の増加によりインデックスのメンテナンスコストが増加します.
 
パーティションの利点:
1、IOを減らす
2、読み書きの向上
3、データ管理を便利にする
 
パーティションとサブテーブルの違い:
パーティションは論理的に水平に分割され、アプリケーションにとってはテーブルです.
パーティションとは、1枚のテーブルのデータをN個以上のブロックに分割することであり、これらのブロックは同じディスク上でも、異なるディスク上でもよい.
 
1.実現方式上
(1)mysqlの分表は本当の分表で、1枚の表が多くの表に分かれた後、すべての小さな表はすべて完全な1枚の表で、すべて3つのファイルに対応して、1つ.MYDデータファイル,.MYIインデックスファイル,.frmテーブル構造ファイル.

[root@BlackGhost test]# ls |grep user
alluser.MRG
alluser.frm
user1.MYD
user1.MYI
user1.frm
user2.MYD
user2.MYI
user2.frm

 
 
簡単に説明すると、上のサブテーブルはmergeストレージエンジン(サブテーブルの一種)を利用しており、alluserは総テーブルであり、下には2つのサブテーブル、user 1、user 2がある.彼ら二人とも独立したテーブルで、データを取るときは、総テーブルで取ることができます.ここの総時計はありません.MYD,.MYIという2つのファイル、つまり、総表は彼が1枚の表ではなく、データがなく、データはすべて分表の中に置かれています.見てみましょう.MRGとは何か

[root@BlackGhost test]# cat alluser.MRG |more
user1
user2

#INSERT_METHOD=LAST

 
 
上から分かるようにalluser.MRGにはいくつかの分表関係とデータの挿入方法が保存されています.総表はハウジング、または接続プールとして理解できます.
 
(2)パーティションが異なり、大きなテーブルをパーティション化した後も、2つのテーブルにはならないが、データを格納するブロックが多くなった.

[root@BlackGhost test]# ls |grep aa
aa#P#p1.MYD
aa#P#p1.MYI
aa#P#p2.MYD
aa#P#p2.MYI
aa#P#p3.MYD
aa#P#p3.MYI
aa.frm
aa.par

 
 
上からaaという表は、3つのエリアに分かれていることがわかります.私たちは1枚の表が3つのファイルに対応していることを知っています.MYD,.MYI,.frm.パーティションは、一定のルールに従ってデータファイルとインデックスファイルを分割する、さらに1つ多くなった.parファイル、開く.parファイルの後、彼が記録したことがわかります.このテーブルのパーティション情報は、サブテーブルのものです.MRGは少し似ています.パーティション化後も、複数のテーブルではなく1枚です.
 
2.データ処理上
(1)分表後,データはすべて分表に格納され,総表は1つのハウジングにすぎず,アクセスデータは1つの分表に発生する.次の例を見てください.
 
select*from user 1 user 2 where id='12'表面的には、テーブルalluserを操作しているが、実際にはそうではない.alluserの中の分表を操作しました.
 
(2)パーティションは,分割テーブルの概念は存在せず,パーティションはデータを格納するファイルを多くの小さなブロックに分けたにすぎず,パーティション化後のテーブルは,まだ1枚のテーブルである.データ処理はやはり自分で行います.
select * from alluser where id='12'
 
3.性能向上
(1)分表後,単表の同時能力が向上し,ディスクI/O性能も向上した.一度にクエリにかかる時間が短くなったため、高同時性が現れると、総テーブルは異なるクエリに基づいて、同時性圧力を異なる小さなテーブルに分けることができます.もともととても大きいです.MYDファイルは現在も各小表に割り当てられている.MYDで行ったので、ディスクIOに対する圧力も低下しました.
 
(2)mysqlはパーティション化の概念を提案しており、ディスクI/Oのボトルネックを突破し、ディスクの読み書き能力を高め、mysqlの性能を高めたいと思っています.
この点では、パーティションとテーブルの側面の重点が異なり、テーブルの重点はデータへのアクセス時にmysqlの同時能力をどのように向上させるかである.パーティションは、ディスクの読み書き能力をどのように突破し、mysqlのパフォーマンスを向上させるかです.
 
4.実現の難易度上
(1)表を分ける方法はいろいろありますが、mergeで表を分けるのが一番簡単な方法です.この方式はルートパーティションの難易度が少なく,プログラムコードにとって透明である.他の分表方式であればパーティションより面倒です.
 
(2)パーティション実装は比較的簡単であり,パーティションテーブルを構築することは,通常のテーブルを構築することと変わらず,オープンコード側にとって透明である.
 
パーティションのタイプ
hash、range、list、key

RANGEパーティション:指定された連続区間の列値に基づいて、複数行がパーティションに割り当てられる.

LISTパーティション:RANGEによるパーティションと同様に、LISTパーティションは、カラム値に基づいて離散値セットの値を一致させることによって選択される.

HASHパーティション:テーブルに挿入する行の列値を使用して計算される、ユーザー定義式の戻り値に基づいて選択されるパーティション.この関数には、MySQLで有効な非負の整数値を生成する式が含まれます.

hashは、データが比較的ランダムな場合に用いられる.テーブル内のコンテンツに基づいてhash演算を行った後、ランダムに平均的に割り当てられます.このカラムが性別であると仮定すると、hashパーティションは適切ではありません.コンテンツは男性か女性か、ランダム性がないからです.

KEYパーティション:HASHによるパーティションと同様に、KEYパーティションは1つ以上のカラムの計算のみをサポートし、MySQLサーバは独自のハッシュ関数を提供します.整数値を含む列または複数の列が必要です.----

はめったに使いません
 
データベースがパーティション化テクノロジーをサポートしているかどうかを確認する方法
 
パーティションの作成:

mysql> create table t1(id int)partition by hash(id)partitions 3;
Query OK, 0 rows affected (0.03 sec)

 
 
【実験】
パーティション化されたテーブルと非パーティション化されたテーブルをそれぞれ作成し、パフォーマンステストを行います.
 
パーティション表の作成

mysql> create table part_tab ( c1 int default NULL, c2 varchar(30) default null, c3 date default null) engine=myisam
-> partition by range(year(c3))(
-> partition p0 values less than (1995),
-> partition p1 values less than (1996),
-> partition p2 values less than (1997),
-> partition p3 values less than (1998),
-> partition p4 values less than (1999),
-> partition p5 values less than (2000),
-> partition p6 values less than (2001),
-> partition p7 values less than (2002),
-> partition p8 values less than (2003),
-> partition p9 values less than (2004),
-> partition p10 values less than (2010),
-> partition p11 values less than MAXVALUE);
Query OK, 0 rows affected (0.14 sec)

 
 
非パーティション表の作成

mysql> create table no_part_tab ( c1 int default NULL, c2 varchar(30) default null, c3 date default null) engine=myisam;
Query OK, 0 rows affected (0.11 sec)

mysql> \d // #           ，          “//”。              sql     。
mysql> create procedure load_part_tab()
-> begin
-> declare v int default 0;
-> while v < 8000000
-> do
-> insert into part_tab
-> values (v,'testing partitions',adddate('1995-01-01',(rand(v)*36520)mod 3652));
-> set v = v+1;
-> end while;
-> end
-> //
Query OK, 0 rows affected (0.04 sec)

mysql> \d ; //           ，          

 
 
上記のストレージ・プロシージャは、実際には大量のデータ(800万件)を作成するためです.

mysql> call load_part_tab(); //   load_part_tab      
Query OK, 1 row affected (9 min 18.95 sec)

 
 
すばやくpart_tabのデータをno_に挿入part_tabの中

mysql> insert no_part_tab select * from part_tab;
Query OK, 8000000 rows affected (8.97 sec)
Records: 8000000 Duplicates: 0 Warnings: 0

 
 
テスト1:
実験前に2つのテーブルのデータが一致していることを確認します!実験の比較可能性を保証する

mysql> select count(*) from part_tab where c3 > date '1995-01-01' and c3 < date '1995-12-31';
+----------+
| count(*) |
+----------+
| 795181 |
+----------+
1 row in set (0.49 sec)

mysql> select count(*) from no_part_tab where c3 > date '1995-01-01' and c3 < date '1995-12-31';
+----------+
| count(*) |
+----------+
| 795181 |
+----------+
1 row in set (3.94 sec)

mysql> desc select count(*) from part_tab where c3 > date '1995-01-01' and c3 < date '1995-12-31'\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: part_tab
type: ALL //    
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 798458
Extra: Using where
1 row in set (0.09 sec)

ERROR:
No query specified

mysql> desc select count(*) from no_part_tab where c3 > date '1995-01-01' and c3 < date '1995-12-31'\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: no_part_tab
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 8000000
Extra: Using where
1 row in set (0.00 sec)

ERROR:
No query specified

 
 
結論:パーティション化を行った後、79万個の文をスキャンするだけで、パーティション化をしない場合は、全表スキャンが必要であることがわかるので、パーティション化技術をした後、読み書き効率を高めることができる.
テスト2:インデックスの作成、文の実行状況の表示

mysql> create index idx_c3 on no_part_tab(c3)；
Query OK, 8000000 rows affected (32.68 sec)
Records: 8000000 Duplicates: 0 Warnings: 0

 
 
結果分析:
結論:パーティション化されていないテーブルにインデックスを作成した後、同じ文を再度実行すると、テーブル全体でスキャンするのではなくrangeインデックスに基づいてSQL文が取得されることがわかります.明らかに効率も向上した.

mysql> desc select count(*) from no_part_tab where c3 > date '1995-01-01' and c3 < date '1995-12-31'\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: NO_part_tab
type: range
possible_keys: idx_c3
key: idx_c3
key_len: 4
ref: NULL
rows: 785678
Extra: Using where; Using index
1 row in set (0.16 sec)

ERROR:
No query specified

 
テスト3:
インデックスとインデックスなしの読み書き効率をテストします.

mysql> create index idx_c3 on part_tab(c3);
Query OK, 8000000 rows affected (31.85 sec)
Records: 8000000 Duplicates: 0 Warnings: 0

mysql> desc select count(*) from part_tab where c3 > date '1995-01-01' and c3 < date '1995-12-31'\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: part_tab
type: index
possible_keys: idx_c3
key: idx_c3
key_len: 4
ref: NULL
rows: 798458
Extra: Using where; Using index
1 row in set (0.14 sec)

ERROR:
No query specified

 
インデックスフィールドが作成されていません

mysql> select count(*) from no_part_tab where c3 > date '1995-01-01' and c3 < date '1995-12-31' and c2='hello';
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (4.90 sec)

 
結論:インデックスによる検索が行われていない場合、インデックスによる検索よりも時間がかかることがわかります.
テスト4:削除

mysql> delete from part_tab where c3 > date '1995-01-01' and c3 < date '1995-12-31';
Query OK, 795181 rows affected (14.02 sec)

mysql> delete from no_part_tab where c3 > date '1995-01-01' and c3 < date '1995-12-31';
Query OK, 795181 rows affected (15.21 sec)

 
結論:削除の面では、パーティションがあるのはパーティションがないより速いことがわかります.これにより、データ管理が容易であるという特徴を体現している.例えば、データベースの表t 1には、今年1年間(12カ月)の会社の売上高が記録されている.パーティション化されていない場合、つまりデータファイルは同じファイルに保存されている.第1四半期の記録を削除すると、結果を出すには、全テーブルスキャンが必要です.しかし、t 1というテーブルが事前にパーティション化されている場合は、1,2,3の3つのファイルを別々に削除するだけです.だからある程度、管理が便利になりました.
 
原文:http://pmghong.blog.51cto.com/3221425/1301945
転送元:mysql-テーブルパーティション