Explication détaillée de la fonction de partition MySQL et exemple d'analyse de code-tutoriel mysql-php.cn

L'éditeur ci-dessous vous apportera une explication détaillée de la fonction de partition mysql, ainsi qu'un exemple d'analyse. L'éditeur pense que c'est plutôt bien, alors je vais le partager avec vous maintenant et le donner comme référence. Suivons l'éditeur et jetons un œil.

1. Qu'est-ce qu'une partition de base de données

J'ai écrit un article sur les sous-tables MySQL ? Il y a quelque temps, parlons de ce qu'est le partitionnement de base de données, en prenant MySQL comme exemple. Les données de la base de données mysql sont stockées sur le disque sous forme de fichiers. Elles sont placées par défaut sous /mysql/data (consultables via le datadir dans my.cnf. Une table correspond principalement à trois fichiers, un est). frm storage Dans la structure de table, l'un est myd qui stocke les données de la table et l'autre est myi qui stocke les index de table. Si la quantité de données dans une table est trop grande, alors myd et myi deviendront très volumineux et la recherche de données deviendra très lente. À ce stade, nous pouvons utiliser la fonction de partition de mysql pour correspondre physiquement à cette table. trois fichiers sont divisés en plusieurs petits blocs. De cette façon, lorsque nous recherchons une donnée, nous n'avons pas besoin de les rechercher tous. Il nous suffit de savoir dans quel bloc se trouvent les données, puis de rechercher dans celui-ci. bloc. Si les données du tableau sont trop volumineuses, elles risquent de ne pas tenir sur un seul disque. À ce stade, nous pouvons allouer les données à différents disques.

Deux façons de partitionner

1. à propos des cloisons horizontales ? Cela signifie un partitionnement horizontal. Par exemple, s'il y a 1 million de données, elles sont divisées en dix parties. Les 100 000 premières données sont placées dans la première partition, les 100 000 secondes sont placées dans la seconde. partition, et ainsi de suite. C'est-à-dire que le tableau est divisé en dix parties, et la fusion sert à diviser le tableau, ce qui est un peu comme ça. Lorsqu'une donnée est récupérée, ces données contiennent tous les champs de la structure de la table, ce qui signifie que le partitionnement horizontal ne modifie pas la structure de la table.

2. Le cloisonnement vertical

Qu'est-ce que le cloisonnement vertical ? Il est partitionné verticalement. Par exemple, lors de la conception de la table utilisateur, je ne l'ai pas bien considéré au début, mais j'ai mis toutes les informations personnelles dans une seule table, de sorte qu'il y ait des champs relativement grands dans cette table, comme personnels. profil

, et ces profils peuvent ne pas être consultés par beaucoup de personnes, donc quand quelqu'un veut les lire, lors de la recherche et de la division de tableaux, des champs aussi grands peuvent être séparés Viens.

On a l'impression que le partitionnement de la base de données est comme couper une pomme. Doit-elle être coupée horizontalement ou verticalement ? Les partitions fournies par MySQL appartiennent au premier type, le partitionnement horizontal, et sont. subdivisé en plusieurs manières. Un exemple sera donné ci-dessous.

2. Partitionnement MySQL

Je pense qu'il n'y a qu'une seule façon de partitionner MySQL, mais elle utilise des algorithmes et des règles différents. Il suffit de distribuer les données. en différents blocs.

1, mysql5.1 et supérieur prennent en charge la fonction de partition

Installation

Lors de l'installation, nous pouvons vérifier

Vérifiez-le. Si vous trouvez ce qui précède, cela signifie qu'il prend en charge le partitionnement et qu'il est activé par défaut. Si vous avez déjà installé MySQL,

[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

Copier après la connexion

vérifiez les

variables

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

Copier après la connexion

S'il est pris en charge, l'invite ci-dessus s'affichera.

range

partition La table partitionnée selon RANGE est partitionnée de la manière suivante, chaque partition contient les lignes où la valeur de l'expression de partition se situe dans un intervalle continu donné

//创建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)

Copier après la connexion

list

partition Le la définition et la sélection de chaque partition dans la partition LIST sont basées sur la valeur d'une colonne appartenant à une valeur dans un ensemble de listes de valeurs, tandis que la partition RANGE appartient à un ensemble de valeurs d'intervalle continu.

Lors de la création de la partition de liste ci-dessus, s'il y a une clé primaire, la clé primaire doit s'y trouver lors du partitionnement, sinon une erreur sera signalée. Si je n'utilise pas la clé primaire, la partition sera créée avec succès. Dans des circonstances normales, une table aura certainement une clé primaire. Ceci est considéré comme une limitation d'une partition.

//这种方式失败 
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)

Copier après la connexion

Si vous testez les données, veuillez vous référer au test de la partition de plage pour faire fonctionner

hachage

partition Le partitionnement HASH est principalement utilisé pour garantir que les données sont réparties uniformément entre un nombre prédéterminé de partitions. Tout ce que vous avez à faire est de spécifier une valeur de colonne ou une expression basée sur la valeur de colonne qui sera hachée, et de la spécifier. la table partitionnée sera hachée. Le nombre de partitions à diviser.

Pour les tests, veuillez vous référer au fonctionnement du partitionnement de plage

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)

Copier après la connexion

5, partitionnement par clé

Partitionnement selon to KEY est similaire à Selon le partitionnement HASH, en plus de l'expression définie par l'utilisateur utilisée par le partitionnement HASH, la fonction de hachage

du partitionnement KEY est fournie par le serveur MySQL.

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)

Copier après la connexion

测试请参考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)

Copier après la connexion

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

ERROR 1517 (HY000): Duplicate partition name s1

三，分区管理

1，删除分区

1.mysql> alter table user drop partition p4;

Copier après la connexion

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)

Copier après la connexion

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

Copier après la connexion

四，分区优点

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

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

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

4，跨多个磁盘来分散数据查询，来获得更大的查询吞吐量
Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!