Discussion :

[user41]

Bonjour à tous,

Utilisateur de MySQL depuis des années et MariaDB depuis peu, pour la première fois, je me pose la question de la place que prend une base de données sur mon disque dur... Et là, c'est le choc !

Je crée une base toute simple, une table à 2 colonnes : id (auto increment) et data (varchar 256, mais j'ai essayé char 4, varbinary, et autres...)

Lorsque je remplis 5000 lignes en écrivant dans data soit un string de 8 caractères en hexadecimal (ex: 5b435431), soit un string de 4 caractères (ex: [CT1) correspondant au pack(H*, $string_hexadecimal) , la taille de la table reste invariable, soit 149.7 ko ...

Le problème c'est que 149700 / 5000 , ça fait ~30 ... 30 octets pour stocker seulement 4 caractères d'un octet (en théorie) ou 8 caractères sur 4 bits (hexadecimal)... c'est trop ! Evidemment, il faut des séparateurs, et je ne sais pas si les id auto incrémentés sont vraiment stockés, mais même si c'est le cas, on arrive vraiment à 30 octets... ?

J'ai donc fait des tests en changeant les interclassements pour la base (ascii divers, binary, latin1 divers, utf divers...), mais rien n'y fait, je n'ai pas obtenu mieux...

J'ai ensuite refait les mêmes tests en écrivant 5000 lignes, avec dans data des string de 32 caractères (ex: [CT1[CT1[CT1[CT1[CT1[CT1[CT1[CT1) correspondant au pack(H*, $string_hexadecimal) , et dans ce cas là, la taille de la table n'est pas proportionnelle et change de plus en plus selon l'interclassement : utf8mb4_general_ci : 355 ko , latin1_swedish_ci : 266 ko , binary : 266 ko

Il y a visiblement des pertes avec l'utf8mb4 mais je ne sais pas pourquoi (ce n'est pas sur 8 bits ?!) , et avec binary ou swedish, 266 ko, ça fait 53 octets par ligne (pour 32 caractères sur 8 bits chacun, soit 19 octets de "perdus")... J'imagine que cela devient négligeable dès lors qu'on stocke plus d'une centaine d'octets par ligne.

Quelqu'un aurait il malgré tout une idée pour faire plus économique en place ou c'est le mieux qu'on puisse faire ?

Merci à tous.

[Artemus24]

Salut User41.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
CREATE TABLE `Trav`
( ...
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_cs`
  ROW_FORMAT=COMPRESSED;

Le jeu de caractères "latin1" codifie 1 caractère sur 1 octet.
Le format "compressed" permet de gagner un peu de place.

Cordialement.
Artemus24.
@+

[SQLpro]

Effectivement MySQL gère très mal le stockage du fait de l'UTF8 qui est une vaste merde hélas plébiscité par les développeurs qui n'ont aucune vision du problème.

L'UTF8 suppose de coder :

• les caractères latin non diacritiques (pas d'accent, cédille, ligature...) sur 1 septet, réservant le 8e bit pour indiquer que dans la chaine, ce caractères est codé sur 1 seul octet.
• Puis les caractères diacritiques latin sur un "quinzet" c'est à dire sur 15 bits, réservant le 16e bit pour indique que le caractère est codé sur 2 octets.
• Puis les caractères des langues non indo européennes (hebreu, arabe, chinois, thaï...) sur 23 bits, réservant le 24e bit pour indiquer que le caractère est codé sur 3 octets.

Ceci est censé compresser les chaines de caractères envoyées sur internet, pour "aller plus vite".

Sauf que cela pose de multiples inconvénients :
1) la langue anglaise est favorisé car dénuée d'accents, au détriment de toutes les autres langues latines...
2) la comparaison de chaines de caractères en fonctions de spécificité de collation (confusion des accents notamment) devient très complexe)
3) le calcul de la longueur d'une chaine en nombre de caractères devient problématique...
4) le tri sémantique des données est cauchemardesque et très lent
etc.

Ceci pose bien évidemment le problème de l'hégémonie des USA sur l'informatique au détriment de tous les autres pays n'ayant pas la langue anglaise comme moyen de communication... Mais passons
Il est a remarquer que Microsoft avait proposé d'utiliser l'UTF16 bien plus "smart" pour les pays d’Europe, d’Afrique et d'Amérique du sud, et à refusé d'intégrer l'UTF8 pendant longtemps... Mais hélas la bêtise l'a emportée...

MySQmerde a préféré jouer à fond la carte de l'américanisme en prônant à outrance le type UTF8... Sauf que pour que cela fonctionne dans une BD ou les comparaisons (jointures, clause de filtrage WHERE et HAVING, tris avec ORDER BY, groupage avec GROUP BY, dédoublonnement avec DISTINCT...) sont légions, il n'est pas possible de travailler directement avec l'encodage UTF8 tel que le standard l'impose...
Alors, MySQmerde a bricolé un système dans lequel tout caractère stocké en soit-disant UTF8 est en fait stocké sur 3 octets !!!!

Et voila la raison pour laquelle vous perdez tant de place pour vos maigres caractères, qu'un autre SGBD, SQL Server pour ne pas le nommer, stocke systématiquement sur un seul octet en CHAR ou VARCHAR...

De plus les index MySQmerde étant limité à 767 octets, cela fait qu'en UTF8 vous ne pourrez avoir que 255 caractères dans un index... Là ou SQL Server permet d'avoir jusqu'à 1600 caractères...

Bienvenu dans le monde de merde de MYSQL !
A lire en complément : https://blog.developpez.com/sqlpro/p...oudre_aux_yeux

A +

[SQLpro]

Le format "compressed" permet de gagner un peu de place.

Oui, mais c'est au détriment des performances !

A +

[Artemus24]

Salut SQLPRO.

L'UTF8 suppose de coder :
les caractères latin non diacritiques (pas d'accent, cédille, ligature...) sur 1 septet, réservant le 8e bit pour indiquer que dans la chaine, ce caractères est codé sur 1 seul octet.

Ce jeu de caractères correspond à la table ASCII, codé sur sept bits.
C'est commun à toutes les codifications des jeux de caractères existantes.

Cordialement.
Artemus24.
@+

[SQLpro]

Salut SQLPRO.

Ce jeu de caractères correspond à la table ASCII, codé sur sept bits.
C'est commun à toutes les codifications des jeux de caractères existantes.

Absolument pas !!!!

Cela ne concerne que les caractères encodées en ASCII et en UNICODE pour les 128 premiers.

Mais il existe de nombreux autres encodages....

Par exemple la famille EBCDIC inventée par IBM et déclinée en 10 déclinaisons...
https://fr.wikipedia.org/wiki/Extend...terchange_Code

C'est encore très largement utilisé dans le monde IBM des grands systèmes, mais aussi de l'AS 400 que de nombreuses PME utilisent encore de nos jours.

Microsoft SQL Server permet d'utiliser le jeu EBCDIC et les collations sui vont avec :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
SQL_EBCDIC037_CP1_CS_AS	Latin1-General, case-sensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 210 on Code Page 1252 for non-Unicode Data
SQL_EBCDIC1141_CP1_CS_AS	German-PhoneBook, case-sensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 219 on Code Page 1252 for non-Unicode Data
SQL_EBCDIC273_CP1_CS_AS	German-PhoneBook, case-sensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 211 on Code Page 1252 for non-Unicode Data
SQL_EBCDIC277_2_CP1_CS_AS	Danish-Norwegian, case-sensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 218 on Code Page 1252 for non-Unicode Data
SQL_EBCDIC277_CP1_CS_AS	Danish-Norwegian, case-sensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 212 on Code Page 1252 for non-Unicode Data
SQL_EBCDIC278_CP1_CS_AS	Finnish-Swedish, case-sensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 213 on Code Page 1252 for non-Unicode Data
SQL_EBCDIC280_CP1_CS_AS	Latin1-General, case-sensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 214 on Code Page 1252 for non-Unicode Data
SQL_EBCDIC284_CP1_CS_AS	Modern-Spanish, case-sensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 215 on Code Page 1252 for non-Unicode Data
SQL_EBCDIC285_CP1_CS_AS	Latin1-General, case-sensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 216 on Code Page 1252 for non-Unicode Data
SQL_EBCDIC297_CP1_CS_AS	French, case-sensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 217 on Code Page 1252 for non-Unicode Data

A +

[user41]

Salut User41.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
CREATE TABLE `Trav`
( ...
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_cs`
  ROW_FORMAT=COMPRESSED;

Le jeu de caractères "latin1" codifie 1 caractère sur 1 octet.
Le format "compressed" permet de gagner un peu de place.

Cordialement.
Artemus24.
@+

Bonjour Artemus, merci pour l'astuce, je ne connaissais pas "compressed" ! Cela dit, dans le cas présent, ce sera difficile à compresser...

Il me semblait que latin1 était sur 7 bits plus 1 bit servant à autre chose... (ou alors je confonds avec ascii) je n'avais pas bien saisi, mais tant mieux.

J'ai testé InnoDB mais c'était incroyablement plus lent que MyIsam pour insérer... donc revenu à MyIsam

[user41]

Merci à SQLpro pour ces explications ! J'ai essayé de m'intéresser à cette problématique mais effectivement, elle semble sans limites

Mon problème va être le suivant : créer plus d'un milliard de lignes sur plus d'un téra octets... Pensez vous qu'il soit plus judicieux de diviser ma table en plusieurs tables ou de tout faire passer en une seule ? Savez vous notamment si ma colonne id (auto increment) stocke effectivement des numéros pour chaque ligne ? (l'id "546 052 377" par exemple prenant déjà une certaine place) Quelle place réelle prendrait cette seule colonne et y'a t il moyen de rogner quelque chose là dessus ?

Merci encore.

[escartefigue]

Le type integer est le plus économe en espace occupé.
Le plus large, le bigint, n'occupe que 8 octets pour 2⁶⁴ valeurs possibles (2⁶³ si signé) !
L'integer "normal" occupe 4 octets pour plus de 4 milliards de valeurs possibles en non signé.
Ce n'est donc pas la colonne de l'identifiant qui encombre l'espace disque ni le réseau (sauf si on a choisi un autre type que de l'integer, ce qui est le plus souvent une erreur).
Donc l'id "546 052 377" n'occupe pas "une certaine place", mais seulement les 4 octets du type integer ou les 8 du type bigint, rien de plus

Il n'y a aucun intérêt à multiplier les tables à cause du volume, par contre, il y a intérêt à les partitionner.

[Artemus24]

Salut à tous.

Il me semblait que latin1 était sur 7 bits plus 1 bit servant à autre chose... (ou alors je confonds avec ascii) je n'avais pas bien saisi, mais tant mieux.

Latin1 est codé sur huit bits et donc 1 caractère est stocké dans 1 octet.
Les 128 premiers caractères sont ceux de l'ASCII.
Les autres 128 caractères sont ceux de la norme de l'Europe occidentale.
Une extension de ce ISO-8859-1 est l'ISO-8859-15 avec en particulier le symbole monétaire de l'euro (€).

Si tu travailles uniquement en français et en anglais, le mieux est d'utiliser ISO-8859-1 ou ISO-8859-15.
Le problème avec l'UTF-8 est la variabilité de la taille du caractère stockée.
Si tu prends par exemple UTF8MB4 cela va de 1 octet à 4 octets.
Il est pourtant conseillé de choisir ce jeu de caractères (charset) mais cela pose quelques problèmes au stockage.

J'ai testé InnoDB mais c'était incroyablement plus lent que MyIsam pour insérer... donc revenu à MyIsam

C'est normal car il y a une gestion des transactions qui ne se fait pas dans MyIsam.
Si tu as presque jamais des mises à jour mais beaucoup de lectures, MyIsam est l'idéal.
Inversement, si tu as beaucoup d'insertions et de mises à jour, le mieux est InnoDB.
Il y a une gestion de l'intégrité des données qui ne se fait pas dans MyIsam.
Ce sont les "commit" et "rollback" que l'on fait entre deux points de sauvegardes.
Il y a aussi la gestion des clefs étrangères, ...

Mon problème va être le suivant : créer plus d'un milliard de lignes sur plus d'un téra octets...

Avant de faire quoi que ce soit, il faut commencer par modéliser votre base de données.
Il s'agit de définir les relations entre les différentes entités qui va constituer votre modèle d'information.
Les aspects techniques viennent plus tard.

Pensez vous qu'il soit plus judicieux de diviser ma table en plusieurs tables ou de tout faire passer en une seule ?

En admettant que tout rentre dans un disque, pour répondre à votre question, cela dépend de ce que vous faites sur vos tables.
Admettons qu'une partie de vos données concerne de l'archivage.
Le mieux est alors de créer des tables partitionnées, c'est-à-dire un stockage ayant comme critère le mois ou l'année.
Je pense à la gestion des chèques que l'on photocopie recto et verso.
Les relevés des comptes bancaires doivent être conservé au moins cinq ans.
Cela fait un paquet de chèques à stocker sachant qu'il y en a plusieurs milions par jour.

Savez vous notamment si ma colonne id (auto increment) stocke effectivement des numéros pour chaque ligne ?

Ce sont bien des nuuméros, mais dans la représentation binaire.
La taille de cette colonne "ID" dépend du type que vous utilisez. Par exemple :
--> tinyint : 1 octet
--> smallint : 2 octets
--> medium : 3 octets
--> integer : 4 octets
--> bigint : 8 octets

Le type integer est le plus économe en espace occupé.

Je reconnais que c'est le plus usuel mais cela dépend de ce que l'on y met dedans.
Si la table concerne des références limités en nombres, ne dépasant pas les 255 lignes, le mieux est d'utiliser tinyint.

Quelle place réelle prendrait cette seule colonne et y'a t il moyen de rogner quelque chose là dessus ?

Chaque type peut s'écrire de deux façons différentes, signée ou non signée.
Comme la colonne est auto incrémentée, le mieux est de définir la colonne non signée.
Ainsi le comptage commence à 1 et se termine au maximum de la capacité de stockage du type utilisé.

--> tinyint : -128 à +127 / 0 à 255 (non signé)
--> smallint : -32 768 to + 32 767 / 0 to 65 535 (non signé)
--> mediumint : -8,388,608 à 8,388,607 / 0 à 16,777,215 (non signé)
--> int / integer : -2 147 483 648 à +2,147,483,647 / 0 à 4,294,967,295 (non signé)
--> bigint : -9 223 372 036 854 775 808 à 9 223 372 036 854 775 807 / 0 à 18 446 744 073 709 551 615 (non signé)

Pour ce qui est de l'espace de stockage, il est de plus en plus recommandé de chiffrer ses données.
Si un hacker avait accès au disque où se trouve votre base de données, le mieux est qu'il ne puisse pas les lire.

Il faut savoir qu'une ligne dans une table contient d'autres informations que les colonnes.
De ce fait, une ligne occupe un peu plus que la taille cumulée de chaque colonne.

Cordialement.
Artemus24.
@+