MySQL に最適化されたパーティション テーブル_MySQL

データベースのデータ量が一定量に増加した場合、パフォーマンスをどのように最適化するかが問題になります。 一般的に使用されるメソッドはいくつかのみです:

1. テーブルを分割する、つまり、大きな式データを複数のテーブルに分割し、各テーブルにあまり多くのデータが含まれないようにします。

利点: 同時実行性が向上し、ロックの粒度が減少します
欠点: コードのメンテナンスのコストが高く、関連する SQL を変更する必要があります

2. パーティション、すべてのデータは依然として 1 つのテーブル内にありますが、物理ストレージ データは特定のルールに従って別のファイルに保存されます。ファイルは別のディスクに配置することもできます。

利点: コードのメンテナンスが少なく、基本的に変更が不要で、IO スループットが向上します

欠点: テーブルの同時実行度は増加していません

3. 事業の分割は基本的にテーブルに分割されます。

利点: より良い長期サポート

短所: コードロジックの再構築、多大な作業

もちろん、それぞれの状況には適切な適用シナリオがあり、特定のビジネスに応じて選択する必要があります。テーブルの分割と分割ビジネスは MySQL 自体とはほとんど関係がなく、ビジネス レベルに属するため、データベースに最も密接に関連する方法であるテーブル パーティショニングについてのみ説明します。ただし、テーブル パーティショニングを使用するための前提条件は、データベースがそれをサポートしている必要があることです。では、データベースがテーブル パーティショニングをサポートしているかどうかを確認するにはどうすればよいでしょうか? 以下のコマンドを実行してください

コードは次のとおりです:

プラグインを表示する; ---mysql コンソールで実行します

5.4未満のバージョンは別のコマンドを使用すると言われていますが、私はそれをテストしていません

コードは次のとおりです:

'%part%' のような変数を表示します;

一般に、データベース内のテーブルを分割するには、垂直方向と水平方向の 2 つの方法があります。垂直とは、テーブル内のさまざまなフィールドをさまざまなデータ ファイルに分割することを意味します。水平方向では、テーブル内のデータの最初の部分がファイルに配置され、データの他の部分がファイルに配置されます。 MySQL は後者の水平分割のみをサポートします。

1. パーティションテーブルを作成します

テーブルのパーティション化の利点を利用したい場合は、データベースのバージョンがパーティション化をサポートしている必要があるだけでなく、パーティション化されたテーブルを構築することが重要です。このテーブルは通常のテーブルとは異なり、作成時にパーティションを指定する必要があります。そうしないと、通常のテーブルをパーティション テーブルに変更できません。では、パーティションテーブルを作成したらどうなるでしょうか? 残りは非常に簡単です。以下のテーブル作成ステートメントを参照してください

リーリー

上記のステートメントは、f_id と f_name の 2 つのフィールドを持つ「T_part」テーブルを作成し、RANGE メソッドに従ってテーブルを 2 つの領域 p0 と p1 に分割します。f_id が 10 未満の場合は、p0 パーティションに配置されます。 0 より大きく 20 より小さい場合、パーティション p1 に配置されます。それでは、f_id が 20 より大きいデータはどのパーティションに配置されるべきでしょうか。 ご想像のとおり、insert ステートメントはエラーを報告します。

ほら、パーティション テーブルの作成はとても簡単です。もちろん、パーティションはいつでも追加および削除できますが、パーティションを削除すると、現在のパーティションの下にあるすべてのデータが削除されることに注意してください。

コードは次のとおりです:

alter table T_part addpartition((MAXVALUE) 未満のパーティション p2 値) ---新しいパーティションを追加します
alter table T_part DROP パーティション p2 ----パーティションを削除します

2. テーブルを分割するいくつかの方法 MySQL は、RANGE パーティション、LIST パーティション、HASH パーティション、LINEAR HASH パーティション、KEY パーティションの 5 つのパーティション化方法をサポートしています。各パーティションには独自の使用シナリオがあります。

1) RANGE パーティション:

RANGE パーティション テーブルは、次の方法でパーティション化されます。各パーティションには、パーティション式の値が指定された連続範囲内にある行が含まれます。これらの間隔は連続的である必要があり、互いに重なることはできず、VALUES LESS THAN 演算子を使用して定義されます。

上記の例はRANGEパーティションです

2) LIST パーティション:

MySQL の LIST パーティションは、多くの点で RANGE パーティションに似ています。 RANGE によるパーティション化と同様に、各パーティションを明確に定義する必要があります。これらの主な違いは、LIST パーティションの各パーティションの定義と選択は値リスト セット内の値に属する列の値に基づくのに対し、RANGE パーティションは連続間隔値のセットに属することです。 LIST パーティショニングは、「PARTITION BY LIST(expr)」を使用して実現されます。「expr」は列値または列値に基づく式であり、整数値を返します。その後、「VALUES IN (value_list)」方法によって定義されます。各パーティション。「value_list」はカンマで区切られた整数のリストです。

リーリー

3)ハッシュパーティション:

　　　　HASH分区主要用来确保数据在预先确定数目的分区中平均分布。在RANGE和LIST分区中，必须明确指定一个给定的列值或列值集合应该保存在哪个分区中；而在HASH分区中，MySQL 自动完成这些工作，你所要做的只是基于将要被哈希的列值指定一个列值或表达式，以及指定被分区的表将要被分割成的分区数量。要使用HASH分区来分割一个表，要在CREATE TABLE 语句上添加一个“PARTITION BY HASH (expr)”子句，其中“expr”是一个返回一个整数的表达式。它可以仅仅是字段类型为MySQL 整型的一列的名字。此外，你很可能需要在后面再添加一个“PARTITIONS num”子句，其中num 是一个非负的整数，它表示表将要被分割成分区的数量。

CREATE TABLE `T_hash` (
  `f_id` INT DEFAULT NULL,
  `f_name` VARCHAR (20) DEFAULT NULL,
  PRIMARY KEY (`f_id`)
) ENGINE = myisam DEFAULT CHARSET = utf8 
PARTITION BY HASH(f_id) ---可以指定多列
PARTITIONS 4；---分区个数

“expr”还可以是MySQL 中有效的任何函数或其他表达式，只要它们返回一个既非常数、也非随机数的整数。（换句话说，它既是变化的但又是确定的）。但是应当记住，每当插入或更新（或者可能删除）一行，这个表达式都要计算一次；这意味着非常复杂的表达式可能会引起性能问题，尤其是在执行同时影响大量行的运算（例如批量插入）的时候。最有效率的哈希函数是只对单个表列进行计算，并且它的值随列值进行一致地增大或减小，因为这考虑了在分区范围上的“修剪”。也就是说，表达式值和它所基于的列的值变化越接近，MySQL就可以越有效地使用该表达式来进行HASH分区。

　　4）LINEAR HASH分区：

　　　　MySQL还支持线性哈希功能，它与常规哈希的区别在于，线性哈希功能使用的一个线性的2的幂（powers-oftwo）运算法则，而常规 哈希使用的是求哈希函数值的模数。线性哈希分区和常规哈希分区在语法上的唯一区别在于，在“PARTITION BY” 子句中添加“LINEAR”关键字.

　　5）KEY分区：

　　　　按照KEY进行分区类似于按照HASH分区，除了HASH分区使用的用户定义的表达式，而KEY分区的 哈希函数是由MySQL 服务器提供。MySQL 簇（Cluster）使用函数MD5()来实现KEY分区；对于使用其他存储引擎的表，服务器使用其自己内部的 哈希函数，这些函数是基于与PASSWORD()一样的运算法则。

　　　　KEY分区的语法和HASH语法类似，只是把关键字改成KEY。　　

CREATE TABLE `T_key` (
  `f_id` INT DEFAULT NULL,
  `f_name` VARCHAR (20) DEFAULT NULL,
  PRIMARY KEY (`f_id`)
) ENGINE = myisam DEFAULT CHARSET = utf8 
PARTITION BY LINEAR key(f_id)
PARTITIONS 3；

6）子分区：

　　　　子分区的意思就是在分区的基础上再次分区。且每个分区必须有相同个数的子分区。

CREATE TABLE `T_part` (
  `f_id` INT DEFAULT NULL,
  `f_name` VARCHAR (20) DEFAULT NULL,
  PRIMARY KEY (`f_id`)
) 
PARTITION BY RANGE (f_id)
SUBPARTITION BY HASH(F_ID)
SUBPARTITIONS 2
(
  PARTITION p0   VALUES     less THAN (10),
  PARTITION p1  VALUES    less THAN (20)
)

上面语句的意思是，建立两个range分区，每个分区根据hash有分别有两个子分区，实际上整个表分成2×2=4个分区。当然，要详细定义每个分区属性也是可以的

CREATE TABLE `T_part` (
  `f_id` INT DEFAULT NULL,
  `f_name` VARCHAR (20) DEFAULT NULL,
  PRIMARY KEY (`f_id`)
) 
PARTITION BY RANGE (f_id)
SUBPARTITION BY HASH(F_ID)
(
  PARTITION p0   VALUES less THAN (10)
  (
    SUBPARTITION s0 
      DATA DIRECTORY = '/disk0/data' 
      INDEX DIRECTORY = '/disk0/idx',
    SUBPARTITION s1 
      DATA DIRECTORY = '/disk1/data' 
      INDEX DIRECTORY = '/disk1/idx'
  ),
  PARTITION p1  VALUES less THAN (20)
  (
    SUBPARTITION s2
      DATA DIRECTORY = '/disk0/data' 
      INDEX DIRECTORY = '/disk0/idx',
    SUBPARTITION s3 
      DATA DIRECTORY = '/disk1/data' 
      INDEX DIRECTORY = '/disk1/idx'
  )
)

这样可以对每个分区指定具体存储磁盘。前提磁盘是存在的。　　

　　MySQL 中的分区在禁止空值（NULL）上没有进行处理，无论它是一个列值还是一个用户定义表达式的值。一般而言，在这种情况下MySQL 把NULL视为0。如果你希望回避这种做法，你应该在设计表时不允许空值；最可能的方法是，通过声明列“NOT NULL”来实现这一点。