MySQL versteht, was eine Partitionstabelle ist? Was ist eine temporäre Tabelle?

Was ist eine Partitionstabelle? Was ist eine temporäre Tabelle? Der folgende Artikel führt Sie durch die Partitionstabellen und temporären Tabellen in MySQL. Ich hoffe, er ist hilfreich für Sie!

Temporäre Tabelle

Es gibt eine Art Tabelle in MySQL, die als temporäre Tabelle bezeichnet wird und sich auf einen speziellen Tabellentyp bezieht, der mit der Anweisung CREATE TEMPORARY TABLE erstellt wird Ergebnisse können in einer einzigen Sitzung mehrmals wiederverwendet werden und sind für andere Verbindungen unsichtbar. Wenn die Verbindung unterbrochen wird, geht die Datentabelle verloren. Sie können jedoch auch DROP TABLE verwenden, wenn sie nicht benötigt wird. Fall explizit gelöscht. [Verwandte Empfehlungen: MySQL-Video-TutorialCREATE 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;

还有一个特点是，临时表可以与其他表具有相同的名称，例如即使数据库中存在user的表，但也可以在数据库中创建user的临时表。

创建临时表示例

创建一个名为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>

基于现有表结构创建

首先创建两个表。

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));

创建这个表后，表不再由一个ibd组成，而是由建立时候各个分区的ibd组成，可以先通过下面语句查看data目录位置，然后查看被分区后创建的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的却报错了，原因是要插入一个在分区没有定义的值，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;

由于现在三个分区，所以会出现三个row，每个row中的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两个分区中各两条。

HASH分区

HASH的目的是将数据均匀的分布到定义的各个分区中，保证各个分区的数据量大致都是一样的， HASH分区没有向RANGE和LIST一样必须规定某个值在哪个分区中保存，HASH分区是自动完成的，我们只需要指定分区数量即可。

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

那如何得知这个数据在哪个分区中存储呢，拿500来说，就是通过mod(500,分区数量)来得到，所以500在第1个分区中。

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

在比如31，那么mod(31,4)就是3，所以在第4个分区中，如果把这两个数插入进去，通过information_schema.partitions]

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

🎜Wenn Sie Wenn Sie eine temporäre Tabelle mit der gleichen Struktur wie eine vorhandene Tabelle erstellen möchten, ist die Verwendung der Anweisung CREATE TEMPORARY TABLE zu mühsam. Sie können die folgende Anweisung verwenden🎜

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)
);

🎜Eine weitere Funktion ist die temporäre Tabelle Kann mit anderen Tabellen mit demselben Namen kombiniert werden. Selbst wenn beispielsweise die Tabelle des Benutzers in der Datenbank vorhanden ist, kann eine temporäre Tabelle für den Benutzer in der Datenbank erstellt werden. 🎜🎜🎜Beispiel für die Erstellung einer temporären Tabelle🎜🎜🎜Erstellen Sie eine neue temporäre Tabelle mit dem Namen tblemployee. Diese Tabelle kann derzeit nicht mit SHOW TABLES angezeigt werden. 🎜

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

🎜Daten hineingeben. 🎜

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
);

🎜🎜Erstellen Sie basierend auf der vorhandenen Tabellenstruktur🎜🎜🎜Erstellen Sie zunächst zwei Tabellen. 🎜rrreee🎜Erstellen Sie eine temporäre Tabelle mit Namen und Kontostand. 🎜rrreee🎜Sehen Sie sich die Daten in der temporären Tabelle an. 🎜rrreee🎜Aber wenn andere Sitzungen diese Tabelle anzeigen, wird ein Fehler gemeldet. 🎜rrreee🎜🎜Temporäre Tabelle löschen🎜🎜rrreee🎜🎜Partitionstabelle🎜🎜🎜MySQL unterstützt seit 5.1 die Partitionierungsfunktion. Partitionierung bezieht sich auf die entsprechende Partitionierungsfunktion Gemäß bestimmten Regeln werden Datensätze verschiedener Zeilen in derselben Tabelle verschiedenen physischen Dateien zugewiesen. Die Partition ist für die Anwendung transparent und hat keine Auswirkungen. 🎜🎜MySQL unterstützt nur die horizontale Partitionierung und keine vertikale Partitionierung, bei der Datensätze in verschiedenen Zeilen derselben Tabelle verschiedenen physischen Dateien zugewiesen werden, während sich bei der vertikalen Partitionierung die Zuordnung von Datensätzen in verschiedenen Spalten derselben Tabelle zu verschiedenen physischen Dateien bezieht . 🎜🎜Sie können mit dem Befehl SHOW PLUGINS überprüfen, ob MySQL die Partitionierungsfunktion aktiviert hat. 🎜🎜MySQL verwendet die Partition-by-Anweisung, um beim Erstellen einer Partition die in jeder Partition gespeicherten Daten zu definieren. Bei der Abfrage filtert der Optimierer die Partitionen, die nicht über die von uns benötigten Daten verfügen, basierend auf der Partitionsdefinition, sodass kein Bedarf besteht um bei der Abfrage alle Partitionen zu scannen. 🎜🎜🎜Partitionstyp🎜🎜🎜🎜RANGE-Partition🎜🎜🎜Es ist eine Methode zum Zuweisen von Daten zu verschiedenen Partitionen basierend auf einem kontinuierlichen Bereich Verwendeter Partitionierungstyp, erstellen Sie eine partitionierte Tabelle mit einem ID-Spaltenintervall. 🎜rrreee🎜Nach dem Erstellen dieser Tabelle besteht die Tabelle nicht mehr aus einer IBD, sondern aus der IBD jeder Partition zum Zeitpunkt der Erstellung. Sie können zunächst den Speicherort des Datenverzeichnisses mit der folgenden Anweisung überprüfen und dann die danach erstellte IBD überprüfen wird partitioniert. 🎜rrreeerrreee🎜 Dann haben wir 3 Datenelemente eingefügt, aber wir können sehen, dass das dritte Element mit der ID 250 einen Fehler gemeldet hat. Der Grund dafür ist, dass ein Wert eingefügt werden soll, der nicht in der Partition definiert ist, und MySQL einen Fehler auslöst Ausnahme. 🎜rrreee🎜Die Lösung besteht darin, eine Partition mit einem MAXXXVALUE-Wert hinzuzufügen, sodass alle Werte größer als 200 darin gespeichert werden. Jetzt können Sie Werte größer als 200 einfügen. 🎜rrreee🎜Mit der folgenden Anweisung können Sie die PARTITIONS-Tabelle abfragen, um die spezifischen Informationen jeder Partition zu erhalten. 🎜rrreee🎜Da es jetzt drei Partitionen gibt, gibt es in jeder Zeile drei Zeilen, die die Anzahl der Speicher angeben, sodass sie jetzt alle 1 sind, und PARTITION_METHOD gibt den Partitionstyp an. 🎜rrreee🎜Sie können auch EXPLAIN verwenden, um zu sehen, welche Partition in der Abfrage verwendet wird. 🎜🎜🎜LIST-Partition 🎜🎜🎜LIST-Partition ähnelt RANGE, mit der Ausnahme, dass die Werte der Partitionsspalte nur bestimmte Werte speichern können, bei denen es sich um eine Sammlung von Werten in einer Aufzählungsliste handelt. RANGE ist eine Sammlung kontinuierlicher Intervallwerte 🎜rrreee🎜 Fügen Sie auch einige Daten ein. Sie können sehen, dass beim Einfügen von 10 eine Ausnahme ausgelöst wird. Der Grund dafür ist, dass die eingefügten Daten nicht mehr in der Partitionsdefinition enthalten sind. 🎜rrreee🎜Die restlichen 1, 2, 6 und 9 sind jeweils zwei in den Partitionen Benutzer0 und Benutzer1. 🎜🎜🎜HASH-Partition🎜🎜🎜Der Zweck von HASH besteht darin, Daten gleichmäßig auf jede definierte Partition zu verteilen und sicherzustellen, dass die Datenmenge in jeder Partition ungefähr gleich ist. HASH-Partitionen müssen keinen bestimmten Wert wie RANGE und LIST angeben . In welcher Partition gespeichert werden soll, die HASH-Partitionierung erfolgt automatisch, wir müssen nur die Anzahl der Partitionen angeben. 🎜rrreee🎜Woher weiß ich, in welcher Partition diese Daten gespeichert sind? Nehmen wir als Beispiel 500, sie werden über Mod (500, Anzahl der Partitionen) abgerufen, sodass sich 500 in der ersten Partition befindet. 🎜rrreee🎜Zum Beispiel 31, dann ist mod(31,4) 3. Wenn Sie also in der vierten Partition diese beiden Zahlen einfügen und sie über information_schema.partitions anzeigen, dann 1. Die TABLE_ROWS Von den 4 Partitionen sind alle 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
);

对NULL的处理

MySQL可以在分区键上使用NULL，会把他当做最小分区来处理，也就是会存放到第一个分区，但是在List分区中，NULL值必须定义在列表中，否则不能被插入。

更多编程相关知识，请访问：编程视频！！

Das obige ist der detaillierte Inhalt vonMySQL versteht, was eine Partitionstabelle ist? Was ist eine temporäre Tabelle?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!