Comment utiliser l'archivage des données dans MySQL pour optimiser l'espace de stockage ?

Comment utiliser l'archivage des données dans MySQL pour optimiser l'espace de stockage ?

Introduction :
À mesure que la quantité de données continue de croître, les besoins de stockage de la base de données augmentent également. Afin d'optimiser l'espace de stockage, nous pouvons déplacer les données rarement consultées vers des tables d'archives via l'archivage des données et utiliser la fonction de partitionnement de MySQL pour améliorer encore les performances des requêtes. Cet article expliquera comment utiliser l'archivage des données dans MySQL pour optimiser l'espace de stockage et fournira des exemples de code pertinents pour référence aux lecteurs.

1. Qu'est-ce que l'archivage des données ?
L'archivage des données fait référence au déplacement des données rarement consultées vers des tables d'archives indépendantes, réduisant ainsi la quantité de données dans la table principale. Les tables d'archives ne participent pas aux opérations de requête régulières, mais elles conservent la possibilité d'interroger et d'analyser les données historiques. Cela peut libérer un espace de stockage précieux sans affecter les opérations commerciales normales.

2. Étapes d'archivage des données et exemple de code :
Ce qui suit est une étape et un exemple de code pour utiliser l'archivage des données afin d'optimiser l'espace de stockage :

Étape 1 : Créer la table principale et la table d'archive

--Créer la table principale
CREATE TABLE main_table (main_table (
id INT(11) NOT NULL AUTO_INCREMENT,
col1 VARCHAR(255) NOT NULL,
col2 INT(11) NOT NULL,
created_at DATETIME NOT NULL,
PRIMARY KEY (id),
KEY idx_col1 (col1)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 创建归档表
CREATE TABLE archive_table (
id INT(11) NOT NULL AUTO_INCREMENT,
col1 VARCHAR(255) NOT NULL,
col2 INT(11) NOT NULL,
created_at DATETIME NOT NULL,
PRIMARY KEY (id),
KEY idx_col1 (col1)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

步骤二：创建分区表

-- 创建主表的分区表
ALTER TABLE main_table PARTITION BY RANGE (YEAR(created_at))
(
PARTITION p2020 VALUES LESS THAN (2021),
PARTITION p2021 VALUES LESS THAN MAXVALUE
);

步骤三：创建触发器

-- 创建触发器，将数据插入到归档表中
DELIMITER //
CREATE TRIGGER archive_trigger AFTER DELETE ON main_table
FOR EACH ROW
BEGIN
INSERT INTO archive_table VALUES (OLD.id, OLD.col1, OLD.col2, OLD.created_at);
END//

DELIMITER ;

步骤四：测试数据插入和查询效果

-- 向主表中插入测试数据
INSERT INTO main_table (col1, col2, created_at) VALUES ('data1', 1, '2020-01-01');
INSERT INTO main_table (col1, col2, created_at) VALUES ('data2', 2, '2021-01-01');

-- 查询主表数据
SELECT * FROM main_table;

-- 查询归档表数据
SELECT * FROM archive_table;

步骤五：定期归档数据

-- 定期将旧数据归档到归档表中
DELIMITER //
CREATE EVENT archive_event
ON SCHEDULE EVERY 1 WEEK STARTS NOW()
DO
BEGIN
INSERT INTO archive_table SELECT * FROM main_table WHERE created_at < DATE_SUB(NOW(), INTERVAL 1 MONTH);
DELETE FROM main_table id INT(11) NON NULL AUTO_INCREMENT,
col1 VARCHAR(255) NON NULL,

col2 INT(11) NON NULL,<p> <code>created_at DATETIME NON NULL,

CLÉ PRIMAIRE (id),

CLÉ idx_col1 (col1 )

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Créer une table d'archive

CREATE TABLE archive_table (🎜 id INT(11) NOT NULL AUTO_INCREMENT ,🎜 col1 VARCHAR(255) NON NULL,🎜 col2 INT(11) NON NULL,🎜 created_at DATETIME NON NULL,🎜 PRIMARY KEY ( id),🎜 KEY idx_col1 (col1)🎜) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;🎜🎜Étape 2 : Créer une partition table🎜🎜 -- Créer une table de partition de la table principale🎜ALTER TABLE main_table PARTITION BY RANGE (YEAR(created_at))🎜(🎜 PARTITION p2020 VALUES MOINS QUE (2021),🎜 PARTITION p2021 VALUES MOINS THAN MAXVALUE🎜); 🎜🎜Étape 3 : Créer un déclencheur🎜🎜--Créer un déclencheur et insérer des données dans la table d'archive🎜DELIMITER //🎜CREATE TRIGGER archive_trigger AFTER DELETE ON main_table 🎜 POUR CHAQUE LIGNE 🎜BEGIN 🎜 INSÉRER DANS LES VALEURS <code>archive_table (OLD.id, OLD.col1, OLD.col2, OLD.created_at//🎜🎜DELIMITER ;🎜🎜Step); 4 : Insertion des données de test et effets de requête🎜🎜--Insérer les données de test dans la table principale🎜INSERT INTO main_table (col1, col2, create_at) VALUES ('data1', 1, '2020-01- 01'); 🎜INSERT INTO main_table (col1, col2, create_at) VALUES ('data2', 2, '2021-01-01');🎜🎜-- Interroger les données de la table principale🎜SELECT * FROM main_table;🎜🎜--Interroger les données de la table d'archives🎜SELECT * FROM archive_table;🎜🎜Étape 5 : Archiver régulièrement les données🎜🎜--Archiver les anciennes données dans la table d'archives régulièrement🎜DELIMITER //🎜CREATE EVENT archive_event 🎜ON SCHEDULE CHAQUE 1 SEMAINE COMMENCE MAINTENANT() 🎜DO 🎜BEGIN 🎜 INSERT INTO archive_table SELECT * FROM main_table code> OÙ créé_à < DATE_SUB(MAINTENANT(), INTERVALLE 1 MOIS);🎜 DELETE FROM <code>main_table OÙ créé_à < ;🎜🎜 Grâce aux étapes ci-dessus et aux exemples de code, nous avons mis en œuvre l'opération d'archivage des données. Les données de la table principale sont partitionnées en fonction de l'heure de création et les données supprimées sont insérées dans la table d'archive via des déclencheurs. Dans le même temps, nous archivons également les données expirées de la table principale vers la table d'archives en archivant régulièrement les données. 🎜🎜3. Conclusion🎜En utilisant la technologie d'archivage des données, nous pouvons réduire la quantité de données dans la table principale et optimiser l'espace de stockage. Dans le même temps, en utilisant la fonction de partitionnement de MySQL, nous pouvons également améliorer les performances des requêtes. Nous espérons que l'introduction et l'exemple de code de cet article pourront aider les lecteurs à mieux comprendre et appliquer la technologie d'archivage des données. 🎜

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!