Mysql実現パーティション機能(二)

6901 ワード
mysql パーティションテーブル
多くの人がよく同じ質問をすると信じています:MySQLが
の総記録数が100万を超えると、性能の大幅な低下はありますか?答えは肯定的ですが、パフォーマンスの低下率は異なり、システムのアーキテクチャ、アプリケーション、インデックス、サーバハードウェアなど多くの要因によって異なります.あるネットユーザーが私にこの質問をしたとき、私の最もよくある答えは:表を分けて、id区間や時間の前後順などによって多くのことができます.
種の規則で表を分ける.表分けは簡単ですが、将来の拡張性など、アプリケーションやアーキテクチャの変更作業は侮れません.
以前は、MERGEタイプを使っていましたが、とても便利な料理でした.アーキテクチャとプログラムは基本的に変更する必要はありませんが、その欠点は明らかです.
  • 同じ構造のMyISAMテーブルでのみ
    • を使用できます.
  • MyISAMのすべての機能を享受できない、例えばMERGEタイプでFULLTEXT検索
    • を実行できない
  • より多くのファイル記述子
    • を使用する必要があります.
  • インデックスの読み取りが遅い
    • このとき、MySQL 5.1に追加されたパーティション機能のメリットも明らかになりました.
  • は、単一のディスクまたはファイルシステムパーティションよりも多くのデータを格納することができる
    • .
  • は、不要または古いデータ
    • を簡単に削除できます.
  • いくつかのクエリは、
    • を極めて最適化することができます.
  • SUM()/COUNT()などの集約関数に関しては、
    • を並列に行うことができる.
  • IOスループットより大きい
    • パーティションを使用すると、任意のサイズのルールに設定でき、ファイルシステムにわたって単一のテーブルの複数の部分を割り当てることができます.実際には、テーブルの異なる部分は、異なる場所で個別のテーブルとして格納される.
    二、パーティションのタイプ
  • RANGEパーティション:指定された連続区間に属する列値に基づいて、複数行がパーティションに割り当てられる.
  • LISTパーティション:RANGEによるパーティションと同様に、LISTパーティションは、カラム値に基づいて離散値セットの値を一致させることによって選択される.
  • HASHパーティション:テーブルに挿入する行の列値を使用して計算される、ユーザー定義式の戻り値に基づいて選択されるパーティション.この関数は、MySQLで有効な非負の整数値を生成する任意の式をパッケージ>含むことができます.
  • KEYパーティション:HASHによるパーティションと同様に、KEYパーティションは1つ以上のカラムの計算のみをサポートし、MySQLサーバは独自のハッシュ関数を提供します.>整数値を含む列または複数の列が必要です.

    • 三、区分例:
  • RANGEタイプ
    CREATE TABLE users (
    uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(30) NOT NULL DEFAULT '',
    email VARCHAR(30) NOT NULL DEFAULT ''
)
PARTITION BY RANGE (uid) (
    PARTITION p0 VALUES LESS THAN (3000000)
    DATA DIRECTORY = '/data0/data'
    INDEX DIRECTORY = '/data1/idx',

    PARTITION p1 VALUES LESS THAN (6000000)
    DATA DIRECTORY = '/data2/data'
    INDEX DIRECTORY = '/data3/idx',

    PARTITION p2 VALUES LESS THAN (9000000)
    DATA DIRECTORY = '/data4/data'
    INDEX DIRECTORY = '/data5/idx',

    PARTITION p3 VALUES LESS THAN MAXVALUE    DATA DIRECTORY = '/data6/data'
    INDEX DIRECTORY = '/data7/idx'
);
    ここでは、ユーザテーブルを4つのパーティションに分割し、300万件ごとのレコードを限界とし、各パーティションには独自のデータ、インデックスファイルの格納ディレクトリがあり、同時に、これらのディレクトリが存在する物理ディスクパーティションも完全に独立している可能性があり、ディスクIOのスループットを大幅に向上させることができる.
  • LISTタイプ
    CREATE TABLE category (
    cid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(30) NOT NULL DEFAULT ''
)
PARTITION BY LIST (cid) (
    PARTITION p0 VALUES IN (0,4,8,12)
    DATA DIRECTORY = '/data0/data'
    INDEX DIRECTORY = '/data1/idx',

    PARTITION p1 VALUES IN (1,5,9,13)
    DATA DIRECTORY = '/data2/data'
    INDEX DIRECTORY = '/data3/idx',

    PARTITION p2 VALUES IN (2,6,10,14)
    DATA DIRECTORY = '/data4/data'
    INDEX DIRECTORY = '/data5/idx',

    PARTITION p3 VALUES IN (3,7,11,15)
    DATA DIRECTORY = '/data6/data'
    INDEX DIRECTORY = '/data7/idx'
);
    は4つの領域に分かれており、データファイルとインデックスファイルは別々に格納されている.
  • HASHタイプ
    CREATE TABLE users (
    uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(30) NOT NULL DEFAULT '',
    email VARCHAR(30) NOT NULL DEFAULT ''
)
PARTITION BY HASH (uid) PARTITIONS 4 (
    PARTITION p0
    DATA DIRECTORY = '/data0/data'
    INDEX DIRECTORY = '/data1/idx',

    PARTITION p1
    DATA DIRECTORY = '/data2/data'
    INDEX DIRECTORY = '/data3/idx',

    PARTITION p2
    DATA DIRECTORY = '/data4/data'
    INDEX DIRECTORY = '/data5/idx',

    PARTITION p3
    DATA DIRECTORY = '/data6/data'
    INDEX DIRECTORY = '/data7/idx'
);
    は4つの領域に分かれており、データファイルとインデックスファイルは別々に格納されている.
  • KEYタイプ
    REATE TABLE users (
    uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(30) NOT NULL DEFAULT '',
    email VARCHAR(30) NOT NULL DEFAULT ''
)
PARTITION BY KEY (uid) PARTITIONS 4 (
    PARTITION p0
    DATA DIRECTORY = '/data0/data'
    INDEX DIRECTORY = '/data1/idx',

    PARTITION p1
    DATA DIRECTORY = '/data2/data'
    INDEX DIRECTORY = '/data3/idx',

    PARTITION p2
    DATA DIRECTORY = '/data4/data'
    INDEX DIRECTORY = '/data5/idx',

    PARTITION p3
    DATA DIRECTORY = '/data6/data'
    INDEX DIRECTORY = '/data7/idx'
);
    は4つのゾーンに分かれており、データファイルとインデックスファイルは別々に格納されている.
  • サブパーティションサブパーティションは、RANGE/LISTタイプのパーティションテーブルにおける各パーティションの再分割である.再分割はHASH/KEYなどのタイプでよい.例えば、
    CREATE TABLE users (
    uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(30) NOT NULL DEFAULT '',
    email VARCHAR(30) NOT NULL DEFAULT ''
)
PARTITION BY RANGE (uid) SUBPARTITION BY HASH (uid % 4) SUBPARTITIONS 2(
    PARTITION p0 VALUES LESS THAN (3000000)
    DATA DIRECTORY = '/data0/data'
    INDEX DIRECTORY = '/data1/idx',

    PARTITION p1 VALUES LESS THAN (6000000)
    DATA DIRECTORY = '/data2/data'
    INDEX DIRECTORY = '/data3/idx'
);
    はRANGEパーティションを再びサブパーティション分割し、サブパーティションはHASHタイプを採用する.または、
    CREATE TABLE users (
    uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(30) NOT NULL DEFAULT '',
    email VARCHAR(30) NOT NULL DEFAULT ''
)
PARTITION BY RANGE (uid) SUBPARTITION BY KEY(uid) SUBPARTITIONS 2(
    PARTITION p0 VALUES LESS THAN (3000000)
    DATA DIRECTORY = '/data0/data'
    INDEX DIRECTORY = '/data1/idx',

    PARTITION p1 VALUES LESS THAN (6000000)
    DATA DIRECTORY = '/data2/data'
    INDEX DIRECTORY = '/data3/idx'
);
    はRANGEパーティションを再度サブパーティション分割し、サブパーティションはKEYタイプを採用する.

    • 四、パーティション管理
    削除パーティション
    ALERT TABLE users DROP PARTITION p0;
    削除パーティションp 0.
    パーティションの再構築
  • RANGEパーティション再構築
    ALTER TABLE users REORGANIZE PARTITION p0,p1 INTO (PARTITION p0 VALUES LESS THAN (6000000));
    元のp 0,p 1パーティションを統合し、新しいp 0パーティションに格納する.
  • LISTパーティション再構築
    ALTER TABLE users REORGANIZE PARTITION p0,p1 INTO (PARTITION p0 VALUES IN(0,1,4,5,8,9,12,13));
    元のp 0,p 1パーティションを統合し、新しいp 0パーティションに格納します.
  • HASH/KEYパーティションリビルド
    ALTER TABLE users REORGANIZE PARTITION COALESCE PARTITION 2;
    REORGANIZE方式でリビルドされたパーティションの数は2となり、ここでは数を減らすしか増えない.ADD PARTITIONメソッドを追加したい.

  • パーティションの追加
    新規RANGEパーティション
    ALTER TABLE category ADD PARTITION (PARTITION p4 VALUES IN (16,17,18,19)
DATA DIRECTORY = '/data8/data'
INDEX DIRECTORY = '/data9/idx');
    新規RANGEパーティション.
  • HASH/KEYパーティション
    ALTER TABLE users ADD PARTITION PARTITIONS 8;
    が追加され、パーティションの合計数が8に拡張されました.

  • HASH/KEYパーティション
    ALTER TABLE users ADD PARTITION PARTITIONS 8;
    が追加され、パーティションの合計数が8に拡張されました.
    • 自動的にあなたのプロフィールにブログ記事を加えるGitTubアクション
    Elasticsearch:負荷-スコア-query Plugin開発履歴