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 :
suffisait largement. Sauf que je mets 22 secondes pour la charger !
Code mysql : Sélectionner tout - Visualiser dans une fenêtre à part
1
2 create `new_charniere` like `charniere`; insert info `new_charniere` select * from `charniere`;
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 :
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 : Sélectionner tout - Visualiser dans une fenêtre à part
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 | +-----------------+-----------+----------------+-----------------+-----------------+
Sauf qu'en consultant la volumétrie, j'ai environ sur le disque 80Mo, soit le double de la table `charnière`.
Code mysql : Sélectionner tout - Visualiser dans une fenêtre à part create `new_charniere` select * from `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 ;
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.
Code mysql : Sélectionner tout - Visualiser dans une fenêtre à part
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`;
Puis ensuite de recréer les deux clefs étrangères. Voici le résultat que j'obtiens à l'exécution :
Environ 10 secondes pour la recopie de la table, et j'ai exactement la même volumétrie sur le disque.
Code mysql : Sélectionner tout - Visualiser dans une fenêtre à part
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
Alors que le chargement par la procédure stockée me donne le résultat suivant :
Soit 23 secondes, le double du temps nécessaire pour une simple recopie.
Code mysql : Sélectionner tout - Visualiser dans une fenêtre à part
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
Pas mal pour un SGBDR soi-disant merdique !
Cordialement.
Artemus24.
@+
Partager