Detaillierte Erläuterung der MySQL-Partitionsfunktion und Beispielcode-Analyse-MySQL-Tutorial-php.cn

Der folgende Editor bietet Ihnen eine detaillierte Erklärung der MySQL-Partitionsfunktion sowie eine Beispielanalyse. Der Herausgeber findet es ziemlich gut, deshalb werde ich es jetzt mit Ihnen teilen und es allen als Referenz geben. Folgen wir dem Editor und werfen wir einen Blick darauf.

1. Was ist eine Datenbankpartition? Vor einiger Zeit Artikel, lassen Sie uns am Beispiel von MySQL darüber sprechen, was Datenbankpartitionierung ist. Die Daten in der MySQL-Datenbank werden standardmäßig in Form von Dateien auf der Festplatte gespeichert (kann über das Datenverzeichnis in my.cnf angezeigt werden). Eine Tabelle entspricht hauptsächlich drei Dateien frm-Speicher In der Tabellenstruktur ist einer myd, der Tabellendaten speichert, und der andere ist myi, der Tabellenindizes speichert. Wenn die Datenmenge in einer Tabelle zu groß ist, werden myd und myi sehr groß und die Suche nach Daten wird sehr langsam. Zu diesem Zeitpunkt können wir die Partitionsfunktion von MySQL verwenden, um dieser Tabelle zu entsprechen Drei Dateien sind in viele kleine Blöcke unterteilt. Wenn wir nach Daten suchen, müssen wir nicht alle durchsuchen. Wir müssen nur wissen, in welchem Block sich die Daten befinden, und dann darin suchen Block. Wenn die Daten in der Tabelle zu groß sind, passen sie möglicherweise nicht auf eine Festplatte. Zu diesem Zeitpunkt können wir die Daten verschiedenen Festplatten zuweisen.

Zwei Arten der Partitionierung

1. Horizontale Partitionierung

Was ist was über horizontale Trennwände? Dies bedeutet eine horizontale Partitionierung. Wenn beispielsweise 1 Million Daten vorhanden sind, werden diese in zehn Teile unterteilt. Die ersten 100.000 Daten werden in der ersten Partition platziert, die zweiten 100.000 Daten werden in der zweiten platziert Partition usw. Das heißt, die Tabelle ist in zehn Teile unterteilt und wird zum Teilen der Tabelle verwendet. Wenn ein Datenelement abgerufen wird, enthalten diese Daten alle Felder in der Tabellenstruktur, was bedeutet, dass die horizontale Partitionierung die Struktur der Tabelle nicht verändert.

2. Vertikale Partitionierung

Was ist vertikale Partitionierung? Beim Entwerfen der Benutzertabelle habe ich beispielsweise nicht darauf geachtet, alle persönlichen Informationen in einer Tabelle zusammenzufassen, sodass diese Tabelle relativ große Felder enthält, z Profil , und diese Profile werden möglicherweise nicht von vielen Menschen angezeigt. Wenn also jemand sie lesen möchte, können beim Suchen und Teilen von Tabellen so große Felder getrennt werden Kommen.

Es fühlt sich an, als ob die Partitionierung der Datenbank wie das Schneiden eines Apfels ist. Sollte sie horizontal oder vertikal geschnitten werden? Die von MySQL bereitgestellten Partitionen gehören zum ersten Typ, der horizontalen Partitionierung in viele Arten unterteilt. Nachfolgend wird ein Beispiel gegeben.

2. MySQL-Partitionierung

Ich denke, es gibt nur eine Möglichkeit, MySQL zu partitionieren, aber es werden unterschiedliche Algorithmen und Regeln verwendet. Verteilen Sie einfach die Daten in verschiedene Blöcke aufteilen.

1, MySQL5.1 und höher unterstützen die Partitionsfunktion

InstallationBei der Installation können wir

überprüfen Überprüfen Sie es. Wenn Sie das oben Gesagte finden, bedeutet dies, dass es die Partitionierung unterstützt und standardmäßig aktiviert ist. Wenn Sie MySQL bereits installiert haben,

[root@BlackGhost mysql-5.1.50]# ./configure --help |grep -A 3 Partition 
=== Partition Support === 
Plugin Name:   partition 
Description:   MySQL Partitioning Support 
Supports build:  static 
Configurations:  max, max-no-ndb

Nach dem Login kopieren

überprüfen Sie die

Variablen

. Wenn es unterstützt wird, wird die obige Eingabeaufforderung angezeigt.

mysql> show variables like "%part%"; 
+-------------------+-------+ 
| Variable_name   | Value | 
+-------------------+-------+ 
| have_partitioning | YES  | 
+-------------------+-------+ 
1 row in set (0.00 sec)

Nach dem Login kopieren

2, Bereich

Partition

Die nach RANGE partitionierte Tabelle ist wie folgt partitioniert. Jede Partition enthält die Zeilen, in denen der Wert des Partitionsausdrucks liegt innerhalb eines gegebenen kontinuierlichen Intervalls

list

//创建range分区表 
mysql> CREATE TABLE IF NOT EXISTS `user` ( 
 ->  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT &#39;用户ID&#39;, 
 ->  `name` varchar(50) NOT NULL DEFAULT &#39;&#39; COMMENT &#39;名称&#39;, 
 ->  `sex` int(1) NOT NULL DEFAULT &#39;0&#39; COMMENT &#39;0为男，1为女&#39;, 
 ->  PRIMARY KEY (`id`) 
 -> ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 
 -> PARTITION BY RANGE (id) ( 
 ->   PARTITION p0 VALUES LESS THAN (3), 
 ->   PARTITION p1 VALUES LESS THAN (6), 
 ->   PARTITION p2 VALUES LESS THAN (9), 
 ->   PARTITION p3 VALUES LESS THAN (12), 
 ->   PARTITION p4 VALUES LESS THAN MAXVALUE 
 -> ); 
Query OK, 0 rows affected (0.13 sec) 
 
//插入一些数据 
mysql> INSERT INTO `test`.`user` (`name` ,`sex`)VALUES (&#39;tank&#39;, &#39;0&#39;) 
 -> ,(&#39;zhang&#39;,1),(&#39;ying&#39;,1),(&#39;张&#39;,1),(&#39;映&#39;,0),(&#39;test1&#39;,1),(&#39;tank2&#39;,1) 
 -> ,(&#39;tank1&#39;,1),(&#39;test2&#39;,1),(&#39;test3&#39;,1),(&#39;test4&#39;,1),(&#39;test5&#39;,1),(&#39;tank3&#39;,1) 
 -> ,(&#39;tank4&#39;,1),(&#39;tank5&#39;,1),(&#39;tank6&#39;,1),(&#39;tank7&#39;,1),(&#39;tank8&#39;,1),(&#39;tank9&#39;,1) 
 -> ,(&#39;tank10&#39;,1),(&#39;tank11&#39;,1),(&#39;tank12&#39;,1),(&#39;tank13&#39;,1),(&#39;tank21&#39;,1),(&#39;tank42&#39;,1); 
Query OK, 25 rows affected (0.05 sec) 
Records: 25 Duplicates: 0 Warnings: 0 
 
//到存放数据库表文件的地方看一下，my.cnf里面有配置，datadir后面就是 
[root@BlackGhost test]# ls |grep user |xargs du -sh 
4.0K  user#P#p0.MYD 
4.0K  user#P#p0.MYI 
4.0K  user#P#p1.MYD 
4.0K  user#P#p1.MYI 
4.0K  user#P#p2.MYD 
4.0K  user#P#p2.MYI 
4.0K  user#P#p3.MYD 
4.0K  user#P#p3.MYI 
4.0K  user#P#p4.MYD 
4.0K  user#P#p4.MYI 
12K  user.frm 
4.0K  user.par 
 
//取出数据 
mysql> select count(id) as count from user; 
+-------+ 
| count | 
+-------+ 
|  25 | 
+-------+ 
1 row in set (0.00 sec) 
 
//删除第四个分区 
mysql> alter table user drop partition p4; 
Query OK, 0 rows affected (0.11 sec) 
Records: 0 Duplicates: 0 Warnings: 0 
 
/**存放在分区里面的数据丢失了，第四个分区里面有14条数据，剩下的3个分区 
只有11条数据，但是统计出来的文件大小都是4.0K，从这儿我们可以看出分区的 
最小区块是4K 
*/ 
mysql> select count(id) as count from user; 
+-------+ 
| count | 
+-------+ 
|  11 | 
+-------+ 
1 row in set (0.00 sec) 
 
//第四个区块已删除 
[root@BlackGhost test]# ls |grep user |xargs du -sh 
4.0K  user#P#p0.MYD 
4.0K  user#P#p0.MYI 
4.0K  user#P#p1.MYD 
4.0K  user#P#p1.MYI 
4.0K  user#P#p2.MYD 
4.0K  user#P#p2.MYI 
4.0K  user#P#p3.MYD 
4.0K  user#P#p3.MYI 
12K  user.frm 
4.0K  user.par 
 
/*可以对现有表进行分区,并且会按規则自动的将表中的数据分配相应的分区 
中，这样就比较好了，可以省去很多事情，看下面的操作*/ 
mysql> alter table aa partition by RANGE(id) 
 -> (PARTITION p1 VALUES less than (1), 
 -> PARTITION p2 VALUES less than (5), 
 -> PARTITION p3 VALUES less than MAXVALUE); 
Query OK, 15 rows affected (0.21 sec)  //对15数据进行分区 
Records: 15 Duplicates: 0 Warnings: 0 
 
//总共有15条 
mysql> select count(*) from aa; 
+----------+ 
| count(*) | 
+----------+ 
|    15 | 
+----------+ 
1 row in set (0.00 sec) 
 
//删除一个分区 
mysql> alter table aa drop partition p2; 
Query OK, 0 rows affected (0.30 sec) 
Records: 0 Duplicates: 0 Warnings: 0 
 
//只有11条了，说明对现有的表分区成功了 
mysql> select count(*) from aa; 
+----------+ 
| count(*) | 
+----------+ 
|    11 | 
+----------+ 
1 row in set (0.00 sec)

Nach dem Login kopieren

partition

The Die Definition und Auswahl jeder Partition in der LIST-Partition basiert auf dem Wert einer Spalte, die zu einem Wert in einem Wertelistensatz gehört, während die RANGE-Partition zu einem Satz kontinuierlicher Intervallwerte gehört.

Wenn beim Erstellen der obigen Listenpartition ein Primärschlüssel vorhanden ist, muss dieser während der Partitionierung darin enthalten sein, andernfalls wird ein Fehler gemeldet. Wenn ich den Primärschlüssel nicht verwende, wird die Partition erfolgreich erstellt. Unter normalen Umständen verfügt eine Tabelle definitiv über einen Primärschlüssel.

//这种方式失败 
mysql> CREATE TABLE IF NOT EXISTS `list_part` ( 
 ->  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT &#39;用户ID&#39;, 
 ->  `province_id` int(2) NOT NULL DEFAULT 0 COMMENT &#39;省&#39;, 
 ->  `name` varchar(50) NOT NULL DEFAULT &#39;&#39; COMMENT &#39;名称&#39;, 
 ->  `sex` int(1) NOT NULL DEFAULT &#39;0&#39; COMMENT &#39;0为男，1为女&#39;, 
 ->  PRIMARY KEY (`id`) 
 -> ) ENGINE=INNODB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 
 -> PARTITION BY LIST (province_id) ( 
 ->   PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8), 
 ->   PARTITION p1 VALUES IN (9,10,11,12,16,21), 
 ->   PARTITION p2 VALUES IN (13,14,15,19), 
 ->   PARTITION p3 VALUES IN (17,18,20,22,23,24) 
 -> ); 
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table&#39;s partitioning function 
 
//这种方式成功 
mysql> CREATE TABLE IF NOT EXISTS `list_part` ( 
 ->  `id` int(11) NOT NULL COMMENT &#39;用户ID&#39;, 
 ->  `province_id` int(2) NOT NULL DEFAULT 0 COMMENT &#39;省&#39;, 
 ->  `name` varchar(50) NOT NULL DEFAULT &#39;&#39; COMMENT &#39;名称&#39;, 
 ->  `sex` int(1) NOT NULL DEFAULT &#39;0&#39; COMMENT &#39;0为男，1为女&#39; 
 -> ) ENGINE=INNODB DEFAULT CHARSET=utf8 
 -> PARTITION BY LIST (province_id) ( 
 ->   PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8), 
 ->   PARTITION p1 VALUES IN (9,10,11,12,16,21), 
 ->   PARTITION p2 VALUES IN (13,14,15,19), 
 ->   PARTITION p3 VALUES IN (17,18,20,22,23,24) 
 -> ); 
Query OK, 0 rows affected (0.33 sec)

Nach dem Login kopieren

Wenn Sie die Daten testen, beachten Sie bitte den Test der Bereichspartition, um

Hash

Partition

zu betreiben HASH-Partitionierung wird hauptsächlich verwendet, um sicherzustellen, dass Daten gleichmäßig auf eine vorgegebene Anzahl von Partitionen verteilt werden. Sie müssen lediglich einen Spaltenwert oder Ausdruck basierend auf dem Spaltenwert angeben, der gehasht werden soll Die partitionierte Tabelle wird gehasht. Die Anzahl der Partitionen, in die aufgeteilt werden soll.

Zum Testen beachten Sie bitte die Funktionsweise der Bereichspartitionierung

mysql> CREATE TABLE IF NOT EXISTS `hash_part` ( 
 ->  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT &#39;评论ID&#39;, 
 ->  `comment` varchar(1000) NOT NULL DEFAULT &#39;&#39; COMMENT &#39;评论&#39;, 
 ->  `ip` varchar(25) NOT NULL DEFAULT &#39;&#39; COMMENT &#39;来源IP&#39;, 
 ->  PRIMARY KEY (`id`) 
 -> ) ENGINE=INNODB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 
 -> PARTITION BY HASH(id) 
 -> PARTITIONS 3; 
Query OK, 0 rows affected (0.06 sec)

Nach dem Login kopieren

5, Schlüsselpartitionierung

Partitionierung entsprechend to KEY ähnelt der HASH-Partitionierung. Zusätzlich zu dem benutzerdefinierten Ausdruck, der von der HASH-Partitionierung verwendet wird, wird die Hash--Funktion

der KEY-Partitionierung vom MySQL-Server bereitgestellt.

mysql> CREATE TABLE IF NOT EXISTS `key_part` ( 
 ->  `news_id` int(11) NOT NULL COMMENT &#39;新闻ID&#39;, 
 ->  `content` varchar(1000) NOT NULL DEFAULT &#39;&#39; COMMENT &#39;新闻内容&#39;, 
 ->  `u_id` varchar(25) NOT NULL DEFAULT &#39;&#39; COMMENT &#39;来源IP&#39;, 
 ->  `create_time` DATE NOT NULL DEFAULT &#39;0000-00-00 00:00:00&#39; COMMENT &#39;时间&#39; 
 -> ) ENGINE=INNODB DEFAULT CHARSET=utf8 
 -> PARTITION BY LINEAR HASH(YEAR(create_time)) 
 -> PARTITIONS 3; 
Query OK, 0 rows affected (0.07 sec)

Nach dem Login kopieren

测试请参考range分区的操作

6，子分区

子分区是分区表中每个分区的再次分割，子分区既可以使用HASH希分区，也可以使用KEY分区。这也被称为复合分区（composite partitioning）。

1，如果一个分区中创建了子分区，其他分区也要有子分区

2，如果创建了了分区，每个分区中的子分区数必有相同

3，同一分区内的子分区，名字不相同，不同分区内的子分区名子可以相同（5.1.50不适用）

mysql> CREATE TABLE IF NOT EXISTS `sub_part` ( 
 ->  `news_id` int(11) NOT NULL COMMENT &#39;新闻ID&#39;, 
 ->  `content` varchar(1000) NOT NULL DEFAULT &#39;&#39; COMMENT &#39;新闻内容&#39;, 
 ->  `u_id` int(11) NOT NULL DEFAULT 0s COMMENT &#39;来源IP&#39;, 
 ->  `create_time` DATE NOT NULL DEFAULT &#39;0000-00-00 00:00:00&#39; COMMENT &#39;时间&#39; 
 -> ) ENGINE=INNODB DEFAULT CHARSET=utf8 
 -> PARTITION BY RANGE(YEAR(create_time)) 
 -> SUBPARTITION BY HASH(TO_DAYS(create_time))( 
 -> PARTITION p0 VALUES LESS THAN (1990)(SUBPARTITION s0,SUBPARTITION s1,SUBPARTITION s2), 
 -> PARTITION p1 VALUES LESS THAN (2000)(SUBPARTITION s3,SUBPARTITION s4,SUBPARTITION good), 
 -> PARTITION p2 VALUES LESS THAN MAXVALUE(SUBPARTITION tank0,SUBPARTITION tank1,SUBPARTITION tank3) 
 -> ); 
Query OK, 0 rows affected (0.07 sec)

Nach dem Login kopieren

官方网站说不同分区内的子分区可以有相同的名字，但是mysql5.1.50却不行会提示以下错误

ERROR 1517 (HY000): Duplicate partition name s1

三，分区管理

1，删除分区

1.mysql> alter table user drop partition p4;

Nach dem Login kopieren

2，新增分区

//range添加新分区 
mysql> alter table user add partition(partition p4 values less than MAXVALUE); 
Query OK, 0 rows affected (0.06 sec) 
Records: 0 Duplicates: 0 Warnings: 0 
 
//list添加新分区 
mysql> alter table list_part add partition(partition p4 values in (25,26,28)); 
Query OK, 0 rows affected (0.01 sec) 
Records: 0 Duplicates: 0 Warnings: 0 
 
//hash重新分区 
mysql> alter table hash_part add partition partitions 4; 
Query OK, 0 rows affected (0.12 sec) 
Records: 0 Duplicates: 0 Warnings: 0 
 
//key重新分区 
mysql> alter table key_part add partition partitions 4; 
Query OK, 1 row affected (0.06 sec)  //有数据也会被重新分配 
Records: 1 Duplicates: 0 Warnings: 0 
 
//子分区添加新分区，虽然我没有指定子分区，但是系统会给子分区命名的 
mysql> alter table sub1_part add partition(partition p3 values less than MAXVALUE); 
Query OK, 0 rows affected (0.02 sec) 
Records: 0 Duplicates: 0 Warnings: 0 
 
mysql> show create table sub1_part\G; 
*************************** 1. row *************************** 
 Table: sub1_part 
Create Table: CREATE TABLE `sub1_part` ( 
 `news_id` int(11) NOT NULL COMMENT &#39;新闻ID&#39;, 
 `content` varchar(1000) NOT NULL DEFAULT &#39;&#39; COMMENT &#39;新闻内容&#39;, 
 `u_id` varchar(25) NOT NULL DEFAULT &#39;&#39; COMMENT &#39;来源IP&#39;, 
 `create_time` date NOT NULL DEFAULT &#39;0000-00-00&#39; COMMENT &#39;时间&#39; 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 
!50100 PARTITION BY RANGE (YEAR(create_time)) 
SUBPARTITION BY HASH (TO_DAYS(create_time)) 
(PARTITION p0 VALUES LESS THAN (1990) 
 (SUBPARTITION s0 ENGINE = InnoDB, 
 SUBPARTITION s1 ENGINE = InnoDB, 
 SUBPARTITION s2 ENGINE = InnoDB), 
 PARTITION p1 VALUES LESS THAN (2000) 
 (SUBPARTITION s3 ENGINE = InnoDB, 
 SUBPARTITION s4 ENGINE = InnoDB, 
 SUBPARTITION good ENGINE = InnoDB), 
 PARTITION p2 VALUES LESS THAN (3000) 
 (SUBPARTITION tank0 ENGINE = InnoDB, 
 SUBPARTITION tank1 ENGINE = InnoDB, 
 SUBPARTITION tank3 ENGINE = InnoDB), 
 PARTITION p3 VALUES LESS THAN MAXVALUE 
 (SUBPARTITION p3sp0 ENGINE = InnoDB,  //子分区的名子是自动生成的 
 SUBPARTITION p3sp1 ENGINE = InnoDB, 
 SUBPARTITION p3sp2 ENGINE = InnoDB)) 
1 row in set (0.00 sec)

Nach dem Login kopieren

3，重新分区

//range重新分区 
mysql> ALTER TABLE user REORGANIZE PARTITION p0,p1,p2,p3,p4 INTO (PARTITION p0 VALUES LESS THAN MAXVALUE); 
Query OK, 11 rows affected (0.08 sec) 
Records: 11 Duplicates: 0 Warnings: 0 
 
//list重新分区 
mysql> ALTER TABLE list_part REORGANIZE PARTITION p0,p1,p2,p3,p4 INTO (PARTITION p0 VALUES in (1,2,3,4,5)); 
Query OK, 0 rows affected (0.28 sec) 
Records: 0 Duplicates: 0 Warnings: 0 
 
//hash和key分区不能用REORGANIZE，官方网站说的很清楚 
mysql> ALTER TABLE key_part REORGANIZE PARTITION COALESCE PARTITION 9; 
ERROR 1064 (42000): You have an error in your SQL syntax; 
check the manual that corresponds to your MySQL server version for the right syntax to use near &#39;PARTITION 9&#39; at line 1

Nach dem Login kopieren

四，分区优点

1，分区可以分在多个磁盘，存储更大一点

2，根据查找条件，也就是where后面的条件，查找只查找相应的分区不用全部查找了

3，进行大数据搜索时可以进行并行处理。

4，跨多个磁盘来分散数据查询，来获得更大的查询吞吐量
Das obige ist der detaillierte Inhalt vonDetaillierte Erläuterung der MySQL-Partitionsfunktion und Beispielcode-Analyse. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!