MySQL はパーティションテーブルとは何かを理解していますか?一時テーブルとは何ですか?-mysql チュートリアル-php.cn

パーティションテーブルとは何ですか?一時テーブルとは何ですか?次の記事では、MySQL のパーティションテーブルと一時テーブルについて説明します。お役に立てば幸いです。

MySQL はパーティションテーブルとは何かを理解していますか?一時テーブルとは何ですか?

一時テーブル

MySQL には一時テーブルと呼ばれる一種のテーブルがあり、CREATE の使用を指します。 TEMPORARY TABLE ステートメントによって作成され、一時的な結果を保存できる特別なタイプのテーブルであり、単一セッションで複数回再利用でき、他の接続からは見えません。接続が中断されると、データはテーブルは失われますが、必要がない場合は、DROP TABLE を使用して明示的に削除することもできます。 [関連する推奨事項: mysql ビデオチュートリアル ]

CREATE TEMPORARY TABLE table_name( 
column_1_definition, 
column_2_definition,
....
);

ログイン後にコピー

既存のテーブルと同じ構造を持つ一時テーブルを作成する場合は、CREATE TEMPORARY TABLE ステートメントを使用します。問題がある場合は、次のステートメントを使用できます

CREATE TEMPORARY TABLE temp_table_name SELECT * FROM table_name LIMIT 0;

ログイン後にコピー

もう 1 つの機能は、一時テーブルに他のテーブルと同じ名前を付けることができることです。たとえば、ユーザーのテーブルがデータベースに存在する場合でも、ユーザーの一時テーブルをデータベースに作成することもできます。

一時テーブルの作成例

tblemployee という名前の新しい一時テーブルを作成します。このテーブルは、SHOW TABLES を使用して表示することはできません。

create temporary table tblemployee
(
id int auto_increment Primary key,
emp_name varchar(500),
emp_address varchar(500),
emp_dept_id int
)

ログイン後にコピー

データを挿入します。

mysql> insert into tblemployee values(1,'张三','北京',2);
Query OK, 1 row affected (0.00 sec)

mysql> select * from tblemployee;
+----+----------+-------------+-------------+
| id | emp_name | emp_address | emp_dept_id |
+----+----------+-------------+-------------+
|  1 | 张三     | 北京        |           2 |
+----+----------+-------------+-------------+
1 row in set (0.01 sec)

mysql>

ログイン後にコピー

既存のテーブル構造に基づいて作成

最初に 2 つのテーブルを作成します。

create table tb_user(user_name varchar(255),user_id int(11));

insert tb_user values("张三",1);
insert tb_user values("李四",2);
insert tb_user values("王五",3);

create table balance(user_id int(11),balance decimal(5,2));
insert balance values(1,200);
insert balance values(2,150);
insert balance values(3,100);

ログイン後にコピー

名前と残高を指定して一時テーブルを作成します

create temporary table temp_user_balance select user_name,balance from tb_user left join balance on tb_user.user_id=balance.user_id;

ログイン後にコピー

一時テーブルのデータを表示します。

mysql> select * from temp_user_balance;
+-----------+---------+
| user_name | balance |
+-----------+---------+
| 张三      |  200.00 |
| 李四      |  150.00 |
| 王五      |  100.00 |
+-----------+---------+
3 rows in set (0.00 sec)

ログイン後にコピー

しかし、他のセッションがこのテーブルを表示すると、エラーが報告されます。

mysql> select * from temp_user_balance;
ERROR 1146 (42S02): Table 'test.temp_user_balance' doesn't exist
mysql>

ログイン後にコピー

一時テーブルの削除

DROP TEMPORARY TABLE table_name;

ログイン後にコピー

パーティションテーブル

MySQL は 5.1 でサポートを開始しましたパーティショニング機能、パーティショニングとは、同じテーブル内の異なる行のレコードを、特定のルールに従って異なる物理ファイルに割り当てることを指します。各領域は独立しており、独立して処理することも、テーブルの一部として処理することもできます。パーティショニングは、透過的であり、透過的であると言われます。ビジネスには影響しません。

MySQL は水平パーティショニングのみをサポートしており、垂直パーティショニングはサポートしていません。水平パーティショニングは、同じテーブルの異なる行のレコードを異なる物理ファイルに割り当てますが、垂直パーティショニングは、同じテーブルの異なる列のレコードを異なる物理ファイルに割り当てることを指します。ファイル。

SHOW PLUGINS コマンドを使用すると、MySQL でパーティショニング機能が有効になっているかどうかを確認できます。

MySQL は、パーティションの作成時に、partition by ステートメントを使用して、各パーティションに保存されるデータを定義します。クエリを実行すると、オプティマイザは、パーティション定義に基づいて、必要なデータを持たないパーティションをフィルタリングします。クエリを実行する必要はなく、効率を向上させるためにすべてのパーティションをスキャンします。

パーティションタイプ

RANGE パーティション

これは、連続間隔の範囲に基づいており、分散しています。データを異なるパーティションに分割するのが最も一般的に使用されるタイプのパーティションです。ID 列間隔でパーティションテーブルを作成してみましょう。

create table user(id int(11),user_name varchar(255))
partition by range(id)(
partition user0 values less than (100),
partition user1 values less than (200));

ログイン後にコピー

このテーブルを作成すると、テーブルは 1 つの ibd で構成されなくなりますが、作成時の各パーティションの ibd で構成されます。最初に次のステートメントでデータディレクトリの場所を確認し、次に確認できます。パーティション分割後に作成された IBD。

show global variables like "%datadir%"
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| datadir       | /var/lib/mysql/ |
+---------------+-----------------+
1 row in set (0.00 sec)

ログイン後にコピー

root@hxl-PC:/var/lib/mysql/test# ls
user#p#user0.ibd  user#p#user1.ibd
root@hxl-PC:/var/lib/mysql/test#

ログイン後にコピー

次に、3 つのデータを挿入しましたが、ID 250 の 3 番目のデータがエラーを報告したことがわかります。その理由は、パーティションに定義されていない値が挿入されるためです。 MySQL は例外をスローします。

mysql> insert user values(50,"张三");
Query OK, 1 row affected (0.01 sec)

mysql> insert user values(150,"张三");
Query OK, 1 row affected (0.01 sec)

mysql> insert user values(250,"张三");
ERROR 1526 (HY000): Table has no partition for value 250
mysql>

ログイン後にコピー

解決策は、MAXXXVALUE 値を持つパーティションを追加して、200 を超えるすべての値がそこに格納されるようにすることです。これで、200 を超える値を挿入できるようになります。

alter table user add partition (partition user3 values less than maxvalue);

mysql> insert user values(250,"张三");
Query OK, 1 row affected (0.02 sec)

ログイン後にコピー

次のステートメントを使用して、PARTITIONS テーブルをクエリして、各パーティションの特定の情報を取得できます。

select * from information_schema.partitions where table_schema=database() and table_name='user'\G;

ログイン後にコピー

現在、パーティションが 3 つあるため、行は 3 つになります。各行の TABLE_ROWS はストレージの数を示しているため、すべて 1 になり、PARTITION_METHOD はパーティションのタイプを示します。

.....
*************************** 1. row ***************************

             PARTITION_METHOD: RANGE
          SUBPARTITION_METHOD: NULL
         PARTITION_EXPRESSION: `id`
      SUBPARTITION_EXPRESSION: NULL
        PARTITION_DESCRIPTION: 100
                   TABLE_ROWS: 1
.....

ログイン後にコピー

Explain を使用して、クエリでどのパーティションが使用されているかを確認することもできます。

LIST パーティション

LIST パーティションは RANGE に似ていますが、パーティション列の値には値のコレクションである特定の値のみを格納できる点が異なります。列挙リスト内。 RANGE は連続間隔値のコレクションです

create table user (id int(11)) 
partition by list(id)(
partition user0 values in(1,3,5,7,9),
partition user1 values in(0,2,4,6,8)
);

ログイン後にコピー

また、いくつかのデータを挿入します。10 を挿入すると例外がスローされることがわかります。その理由は、挿入されたデータがパーティション定義に存在しないためです。

mysql> insert user values(1);
Query OK, 1 row affected (0.02 sec)

mysql> insert user values(2);
Query OK, 1 row affected (0.01 sec)

mysql> insert user values(6);
Query OK, 1 row affected (0.02 sec)

mysql> insert user values(9);
Query OK, 1 row affected (0.01 sec)

mysql> insert user values(10);
ERROR 1526 (HY000): Table has no partition for value 10
mysql>

ログイン後にコピー

残りの 1、2、6、および 9 には、user0 および user1 パーティションにそれぞれ 2 つのエントリがあります。

HASH パーティション

HASH の目的は、定義された各パーティションにデータを均等に分散して、各パーティションのデータ量がほぼ同じになるようにすることです。 RANGE や LIST のように特定の値をどのパーティションに保存するかを指定する必要はなく、パーティション数を指定するだけで自動的に HASH 分割が行われます。

create table user (id int(11)) partition by hash(id) partitions 4;

ログイン後にコピー

このデータがどのパーティションに保存されているかを確認するにはどうすればよいですか? 例として 500 を取り上げますが、これは mod (500, パーティション数) によって取得されるため、500 は最初のパーティションにあります。

mysql> select mod(500,4)
    -> ;
+------------+
| mod(500,4) |
+------------+
|          0 |
+------------+

ログイン後にコピー

たとえば、31 の場合、mod(31,4) は 3 となるため、4 番目のパーティションにこれら 2 つの数値を挿入すると、information_schema.partitions で確認し、次に TABLE_ROWS になります。パーティション 1 と 4 は両方とも 1 です。

通过条件查找数据时，使用到的分区也不一样，比如查找相等的数，那么首先计算这个值应该在哪个分区，然后在进行查找，如果使用<、>来范围查找，则会使用所有分区。

还有HASH可以使用一些函数或其他有效表达式，比如创建时可以使用partition by hash(abs(id))，但并不是所有函数都可以使用，可使用的函数可以参考官网；

KEY分区

Key分区和HASH类似，不同的是， HASH 分区允许使用用户自定义的表达式，KEY 分区不允许使用用户自定义的表达式，需要使用 HASH 函数

KEY分区允许多列，而HASH分区只允许一列，另外在有主键或者唯一键的情况下，key中分区列可不指定，默认为主键或者唯一键，如果没有，则必须显性指定列。

create table user(id int(11)) partition by key(id) partitions 4;

ログイン後にコピー

但是我并没有找到关于详细分区介绍的算法，而是看到一些说通过PASSWORD的运算，没有搞懂。

Columns分区

Columns分区是5.5引入的分区类型，在此之前，RANGE分区和LIST分区只能支持整数分区，从而需要额外的函数来计算，Columns分区解决了这个问题。

Columns分区可以细分为RANGE Columns 和LIST Columns分区，支持的类型如下：

TINYINT、 SMALLINT、 MEDIUMINT、 INT ( INTEGER) 和 BIGINT，但是不支持DECIMAL或 FLOAT。
DATE和 DATETIME。
CHAR， VARCHAR， BINARY，和 VARBINARY，TEXT和 BLOB列不支持。

create table user (
    a int,
    b int
)
partition by range columns(a, b) (
    partition p0 values less than (5, 12),
    partition p1 values less than (maxvalue, maxvalue)
);

ログイン後にコピー

现在插入一些数据

insert into user (a,b) values (4,11);
insert into user (a,b) values (6,13);

ログイン後にコピー

第一条由于(4,11) < (5,12) 所以在p0分区，而(6,13) < (5,12) ，超出预期，在p1分区。

子分区

子分区也称为复合分区，可以对分区表RANGE和LIST上分区再进分区。

create table user (id int, purchased date)
    partition by range( year(purchased) )
    subpartition by hash( to_days(purchased) )
    subpartitions 2 (
        partition p0 values less than (1990),
        partition p1 values less than (2000),
        partition p2 values less than maxvalue
);

ログイン後にコピー