MySQL prend-il en charge le partitionnement ?

mysql prend en charge la fonction de partitionnement à partir de la version 5.1. Dans MySQL 5.1, l'expression de partition doit être un entier ou une expression qui renvoie un entier tandis que MySQL 5.5 prend en charge le partitionnement d'expressions non entières. La partition de la base de données MySQL est un index de partition local. Une partition stocke à la fois les données et les index ; c'est-à-dire que l'index clusterisé et l'index non clusterisé de chaque zone sont placés dans leurs zones respectives (fichiers physiques différents). MySQL prend en charge 4 types de partitions : partition RANGE, partition LIST, partition HASH et partition KEY.

L'environnement d'exploitation de ce tutoriel : système windows7, version mysql8, ordinateur Dell G3.

mysql prend en charge le partitionnement.

Présentation du partitionnement MySQL

MySQL a ajouté la prise en charge du partitionnement horizontal dans la version 5.1. Le partitionnement consiste à décomposer une table ou un index en parties plus petites et plus faciles à gérer. Chaque zone est indépendante et peut être traitée indépendamment ou dans le cadre d'un objet plus grand. Il s'agit d'une fonction prise en charge par MySQL et le code métier n'a pas besoin d'être modifié. Il faut savoir que MySQL est orienté vers les données OLTP. Ce n'est pas comme les autres bases de données comme TIDB. Ensuite, vous devez être très prudent lorsque vous utilisez des partitions. Si vous ne savez pas comment utiliser les partitions, cela peut avoir un impact négatif sur les performances.

La partition de la base de données MySQL est un index de partition local. Une partition stocke à la fois les données et l'index. Autrement dit, l'index clusterisé et l'index non clusterisé de chaque zone sont placés dans leurs zones respectives (fichiers physiques différents). Actuellement, la base de données MySQL ne prend pas en charge le partitionnement global.

Quel que soit le type de partitionnement, s'il y a une clé primaire ou un index unique dans la table, la colonne de partitionnement doit être un composant de l'index unique.

Facteurs limitants de la table de partition

(1). Une table ne peut avoir qu'un maximum de 1024 partitions.

(2). Dans MySQL5.1, l'expression de partition doit être un entier ou une expression qui renvoie un entier. La prise en charge du partitionnement d'expressions non entières est fournie dans MySQL 5.5.

(3) S'il y a des colonnes de clé primaire ou d'index unique dans le champ de partition, alors de nombreuses colonnes de clé primaire et colonnes d'index uniques doivent être incluses. Autrement dit : le champ de partition soit ne contient pas la clé primaire ou la colonne d'index, soit contient toutes les colonnes de clé primaire et d'index.

(4). Les contraintes de clé étrangère ne peuvent pas être utilisées dans les tables partitionnées.

(5) Le partitionnement MySQL s'applique à toutes les données et index d'une table. Vous ne pouvez pas partitionner les données de la table mais pas l'index, vous ne pouvez pas partitionner l'index mais pas la table, et vous ne pouvez pas partitionner seulement une partie des données de la table. tableau. .

Type de partition

Actuellement, MySQL prend en charge plusieurs types de partitions, la partition RANGE, la partition LIST, la partition HASH et la partition KEY. Si la table possède une clé primaire ou un index unique, la colonne de partition doit être un composant de l'index unique. En combat réel, le partitionnement RANGE est très probablement utilisé.

Partitionnement RANGE

Le partitionnement RANGE est le type de partitionnement le plus couramment utilisé dans la pratique. Les données de ligne sont placées dans des partitions en fonction des valeurs de colonne appartenant à un intervalle continu donné. Mais rappelez-vous que lorsque les données insérées n'ont pas de valeur définie dans une partition, une exception sera levée.

La partition RANGE est principalement utilisée pour le partitionnement des colonnes de dates, telles que les tables de transactions, les tables de ventes, etc. Les données peuvent être stockées selon l'année et le mois. Si vous partitionnez des données de type date dans l'index unique, veuillez noter que l'optimiseur ne peut optimiser que des fonctions telles que YEAR(), TO_DAYS(), TO_SECONDS() et UNIX_TIMESTAMP(). En combat réel, le type int peut être utilisé, il suffit donc de stocker aaaaMM. Vous n'avez plus à vous soucier des fonctions.

CREATE TABLE `m_test_db`.`Order` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `partition_key` INT NOT NULL,
    `amt` DECIMAL(5) NULL,
    PRIMARY KEY (`id` , `partition_key`)
) PARTITION BY RANGE (partition_key) PARTITIONS 5 (
PARTITION part0 VALUES LESS THAN (201901) , 
PARTITION part1 VALUES LESS THAN (201902) , 
PARTITION part2 VALUES LESS THAN (201903) , 
PARTITION part3 VALUES LESS THAN (201904) , 
PARTITION part4 VALUES LESS THAN (201905));

À ce stade, nous insérons d'abord quelques données

INSERT INTO `m_test_db`.`Order` (`id`, `partition_key`, `amt`) VALUES ('1', '201901', '1000');
INSERT INTO `m_test_db`.`Order` (`id`, `partition_key`, `amt`) VALUES ('2', '201902', '800');
INSERT INTO `m_test_db`.`Order` (`id`, `partition_key`, `amt`) VALUES ('3', '201903', '1200');

Maintenant, nous interrogeons et trouvons via la commande EXPLAIN PARTITION que l'optimiseur SQL recherche uniquement la zone correspondante et ne recherchera pas toutes les partitions

S'il y a un problème avec l'instruction SQL, puis peut visiter toutes les zones. Ce serait dangereux. Par conséquent, après avoir partitionné la table, l'instruction select doit utiliser la clé de partition.

Les 3 types suivants ne sont pas très couramment utilisés, je vais donc simplement les mentionner ici.

Partitionnement LIST

Le partitionnement LIST est très similaire au partitionnement RANGE, sauf que les valeurs de la colonne de partitionnement sont discrètes et non continues. Le partitionnement LIST utilise VALUES IN car la valeur de chaque partition est discrète, donc seules les valeurs peuvent être définies.

Partition HASH

En parlant de hachage, le but est évident : répartir uniformément les données dans des partitions prédéfinies afin de garantir que le numéro de chaque partition est à peu près le même.

Partition KEY

La partition KEY est similaire à la partition HASH. La différence est que la partition HASH utilise des fonctions définies par l'utilisateur pour le partitionnement et que la partition KEY utilise les fonctions fournies par la base de données pour le partitionnement.

分区和性能

一项技术，不是用了就一定带来益处。比如显式锁功能比内置锁强大，你没玩好可能导致很不好的情况。分区也是一样，不是启动了分区数据库就会运行的更快，分区可能会给某些sql语句性能提高，但是分区主要用于数据库高可用性的管理。

数据库应用分为2类，一类是OLTP（在线事务处理），一类是OLAP（在线分析处理）。对于OLAP应用分区的确可以很好的提高查询性能，因为一般分析都需要返回大量的数据，如果按时间分区，比如一个月用户行为等数据，则只需扫描响应的分区即可。在OLTP应用中，分区更加要小心，通常不会获取一张大表的10%的数据，大部分是通过索引返回几条数据即可。

比如一张表1000w数据量，如果一句select语句走辅助索引，但是没有走分区键。那么结果会很尴尬。如果1000w的B+树的高度是3，现在有10个分区。那么不是要(3+3)*10次的逻辑IO？（3次聚集索引，3次辅助索引，10个分区）。所以在OLTP应用中请小心使用分区表。

在日常开发中，如果想查看sql语句的分区查询结果可以使用explain partitions + select sql来获取，partitions标识走了哪几个分区。

mysql> explain partitions select * from TxnList where startTime>&#39;2016-08-25 00:00:00&#39; and startTime<&#39;2016-08-25 23:59:00&#39;;  
+----+-------------+-------------------+------------+------+---------------+------+---------+------+-------+-------------+  
| id | select_type | table             | partitions | type | possible_keys | key  | key_len | ref  | rows  | Extra       |  
+----+-------------+-------------------+------------+------+---------------+------+---------+------+-------+-------------+  
|  1 | SIMPLE      | ClientActionTrack | p20160825  | ALL  | NULL          | NULL | NULL    | NULL | 33868 | Using where |  
+----+-------------+-------------------+------------+------+---------------+------+---------+------+-------+-------------+  
1 row in set (0.00 sec)

注：

1.MySQL Workbench下添加分区的截图

2. Table has no partition for value 12

在12月的某一天，我查看了生产的日志文件，忽然发现系统一直在报错：Table has no partition for value 12。仔细检查分区sql发现分区的时候用的是less than

也就是说我在注释1截图里面的分区是不包括12月的区的。执行以下命令增加分区：

ALTER TABLE table_name ADD PARTITION (PARTITION p_12 VALUES LESS THAN (13));

如果没有进行适当的处理，将会报错。所以在进行 RANGE 分区时，要思考这种情况。一般情况下，就时在最后添加一个 MAXVALUE 分区，如下：

PARTITION p_max VALUES LESS THAN MAXVALUE

【相关推荐：mysql视频教程】

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!