Optimiser la recopie d'une table !
Salut à tous.
Mon but est de recopier une table le plus rapidement possible, mais en conservant la même volumétrie sur le disque.
Je me suis dis qu'en partant d'un :
Code:
1 2
| create `new_charniere` like `charniere`;
insert info `new_charniere` select * from `charniere`; |
suffisait largement. Sauf que je mets 22 secondes pour la charger !
Et en plus, j'ai deux foreign key qui sont encore présentes dans la table `new_charniere`.
Pour la volumétrie, ce n'est pas ça non plus, car elle est légèrement supérieure à la table d'origine.
Autrement dit, ce n'est la bonne méthode pour recopier une table.
Ma table charnière à 1 million de lignes.
Elle possède deux clefs étrangères et la volumétie est :
Code:
1 2 3 4 5 6 7 8 9
| --------------
call `outils`.`display_volume` ('base', 'charniere')
--------------
+-----------------+-----------+----------------+-----------------+-----------------+
| Base de données | Table | Data_Length Ko | Index_Length Ko | Total_Length Ko |
+-----------------+-----------+----------------+-----------------+-----------------+
| base | charniere | 20776 | 15904 | 36680 |
+-----------------+-----------+----------------+-----------------+-----------------+ |
En consultant le net, j'ai trouvé un sujet qui dit qu'en recopiant une table sans les index, il suffit de faire ceci :
Code:
create `new_charniere` select * from `charnière`;
Sauf qu'en consultant la volumétrie, j'ai environ sur le disque 80Mo, soit le double de la table `charnière`.
Pour recréer une table sans les index, il suffit de reprendre la déclaration de cette table et ne pas mettre la déclaration des index. Ce qui donne dans mon cas ;
Code:
1 2 3 4 5 6 7 8
| CREATE TABLE `new_charniere`
( `id` integer unsigned auto_increment NOT NULL primary key,
`medecin` integer unsigned NOT NULL,
`patient` integer unsigned NOT NULL)
ENGINE=InnoDB
DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
ROW_FORMAT=COMPRESSED
SELECT `id`,`medecin`,`patient` FROM `charniere`; |
Comme on peut le constater, ce que j'ai mis en rouge permet lors de la création de la table d'effectuer à sa suite la recopie des lignes.
Puis ensuite de recréer les deux clefs étrangères. Voici le résultat que j'obtiens à l'exécution :
Code:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125
| --------------
START TRANSACTION
--------------
--------------
ALTER INSTANCE DISABLE INNODB REDO_LOG
--------------
--------------
SET SESSION autocommit = 0
--------------
--------------
SET SESSION unique_checks = 0
--------------
--------------
SET SESSION foreign_key_checks = 0
--------------
--------------
DROP TABLE IF EXISTS `new_charniere`
--------------
--------------
set profiling=1
--------------
--------------
CREATE TABLE `new_charniere` ( `id` integer unsigned auto_increment NOT NULL primary key,`medecin` integer unsigned NOT NULL, `patient` integer unsigned NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci` ROW_FORMAT=COMPRESSED SELECT `id`,`medecin`,`patient` FROM `charniere`
--------------
--------------
ALTER TABLE `new_charniere` ADD CONSTRAINT `FK_03` FOREIGN KEY (`medecin`) REFERENCES `medecin` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
--------------
--------------
ALTER TABLE `new_charniere` ADD CONSTRAINT `FK_04` FOREIGN KEY (`patient`) REFERENCES `patient` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
--------------
--------------
set profiling=0
--------------
--------------
show profiles
--------------
+----------+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 1 | 10.06795950 | CREATE TABLE `new_charniere` ( `id` integer unsigned auto_increment NOT NULL primary key,`medecin` integer unsigned NOT NULL, `patient` integer unsigned NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci` ROW_FORMAT=COMPRESSED SELECT `id`,`medecin`,`patient` FROM `charniere |
| 2 | 2.15856375 | ALTER TABLE `new_charniere` ADD CONSTRAINT `FK_03` FOREIGN KEY (`medecin`) REFERENCES `medecin` (`id`) ON DELETE CASCADE ON UPDATE CASCADE |
| 3 | 1.67187975 | ALTER TABLE `new_charniere` ADD CONSTRAINT `FK_04` FOREIGN KEY (`patient`) REFERENCES `patient` (`id`) ON DELETE CASCADE ON UPDATE CASCADE |
+----------+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
--------------
SET SESSION foreign_key_checks = 1
--------------
--------------
SET SESSION unique_checks = 1
--------------
--------------
SET SESSION autocommit = 1
--------------
--------------
ALTER INSTANCE ENABLE INNODB REDO_LOG
--------------
--------------
optimize table `new_charniere`
--------------
+--------------------+----------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+--------------------+----------+----------+----------+
| base.new_charniere | optimize | status | OK |
+--------------------+----------+----------+----------+
--------------
analyze table `new_charniere`
--------------
+--------------------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+--------------------+---------+----------+----------+
| base.new_charniere | analyze | status | OK |
+--------------------+---------+----------+----------+
--------------
describe `new_charniere`
--------------
+---------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+----------------+
| id | int unsigned | NO | PRI | NULL | auto_increment |
| medecin | int unsigned | NO | MUL | NULL | |
| patient | int unsigned | NO | MUL | NULL | |
+---------+--------------+------+-----+---------+----------------+
--------------
call `outils`.`display_volume` ('base', 'charniere')
--------------
+-----------------+-----------+----------------+-----------------+-----------------+
| Base de données | Table | Data_Length Ko | Index_Length Ko | Total_Length Ko |
+-----------------+-----------+----------------+-----------------+-----------------+
| base | charniere | 20776 | 15904 | 36680 |
+-----------------+-----------+----------------+-----------------+-----------------+
--------------
call `outils`.`display_volume` ('base','new_charniere')
--------------
+-----------------+---------------+----------------+-----------------+-----------------+
| Base de données | Table | Data_Length Ko | Index_Length Ko | Total_Length Ko |
+-----------------+---------------+----------------+-----------------+-----------------+
| base | new_charniere | 20776 | 15904 | 36680 |
+-----------------+---------------+----------------+-----------------+-----------------+
--------------
COMMIT
--------------
Début : 12:04:59,91
Fin ..: 12:05:13,99
Durée : 00:00:14,08 |
Environ 10 secondes pour la recopie de la table, et j'ai exactement la même volumétrie sur le disque.
Alors que le chargement par la procédure stockée me donne le résultat suivant :
Code:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113
| --------------
START TRANSACTION
--------------
--------------
DROP TABLE IF EXISTS `charniere`
--------------
--------------
CREATE TABLE `charniere`
( `id` integer unsigned auto_increment NOT NULL primary key,
`medecin` integer unsigned NOT NULL,
`patient` integer unsigned NOT NULL
) ENGINE=InnoDB
DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
ROW_FORMAT=COMPRESSED
--------------
--------------
DROP PROCEDURE IF EXISTS ajout_char
--------------
--------------
CREATE PROCEDURE ajout_char
(
IN In_Qte INTEGER UNSIGNED,
IN In_Start INTEGER UNSIGNED,
IN In_End INTEGER UNSIGNED
)
DETERMINISTIC
NO SQL
BEGIN
DECLARE _max INTEGER UNSIGNED DEFAULT 100;
DECLARE _ind INTEGER UNSIGNED DEFAULT 0;
DECLARE _2nd INTEGER UNSIGNED DEFAULT 0;
DECLARE _nbr INTEGER UNSIGNED DEFAULT 0;
DECLARE _med INTEGER UNSIGNED DEFAULT 0;
SET _nbr = 1;
SET _ind = floor(In_Qte / _max);
ALTER TABLE `charniere` DISABLE KEYS;
WHILE _ind > 0
DO
START TRANSACTION;
SET _2nd = _max;
SET @REQ = 'INSERT INTO `charniere` (`medecin`,`patient`) VALUES ';
WHILE _2nd > 1
DO
SET _med = round(cast(rand() * (In_End - In_Start) as unsigned) + In_Start);
SET @REQ = concat(@REQ,'(',_med,',',_nbr,'),');
SET _nbr = _nbr + 1;
SET _2nd = _2nd - 1;
END WHILE;
SET _med = round(cast(rand() * (In_End - In_Start) as unsigned) + In_Start);
SET @REQ = concat(@REQ,'(',_med,',',_nbr,');');
SET _nbr = _nbr + 1;
PREPARE stmt FROM @REQ;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
COMMIT;
SET _ind = _ind - 1;
END WHILE;
ALTER TABLE `charniere` ENABLE KEYS;
END
--------------
--------------
call ajout_char(1000000, 1, 4000)
--------------
--------------
select count(*) from charniere
--------------
+----------+
| count(*) |
+----------+
| 1000000 |
+----------+
--------------
select * from charniere limit 10
--------------
+----+---------+---------+
| id | medecin | patient |
+----+---------+---------+
| 1 | 2920 | 1 |
| 2 | 640 | 2 |
| 3 | 2437 | 3 |
| 4 | 2267 | 4 |
| 5 | 22 | 5 |
| 6 | 1306 | 6 |
| 7 | 2463 | 7 |
| 8 | 398 | 8 |
| 9 | 2597 | 9 |
| 10 | 3795 | 10 |
+----+---------+---------+
--------------
COMMIT
--------------
Début : 10:59:37,47
Fin ..: 11:00:00,99
Durée : 00:00:23,52 |
Soit 23 secondes, le double du temps nécessaire pour une simple recopie.
Pas mal pour un SGBDR soi-disant merdique !
Cordialement.
Artemus24.
@+