Evolution de MySql hautes performances (1) : Optimisation des types de données_Partie 1

De nombreuses connaissances sont impliquées dans le processus de réglage des performances de la base de données, notamment si les paramètres des attributs de champ sont appropriés, si l'établissement de l'index est approprié, si la structure de la table est raisonnable, si les paramètres de la base de données/du système d'exploitation sont corrects. .. Chaque sujet peut être un champ.

À mon avis, parmi les technologies clés pour améliorer les performances des bases de données, l'optimisation des champs est relativement difficile et a un impact très important sur les performances. Étant donné que MySQL prend en charge de nombreux types de données et que chaque type a ses propres caractéristiques, parfois lors du choix d'un type de données spécifique, on choisit souvent un type utilisable à volonté sans se demander si le type est optimal. Avant de décrire les types spécifiques, examinons d'abord quelques grands principes de sélection des types de données :

a) Essayez de choisir des types qui prennent moins de place
car les petits types sont soit sur le disque, soit en mémoire. occupé est petit et l'espace requis par la table temporaire pour l'interrogation ou le tri sera relativement petit. Vous ne le ressentirez peut-être pas lorsque la quantité de données est relativement petite, mais lorsque la quantité de données est relativement importante, l'importance de ce principe peut devenir évidente.

Par exemple, il existe une table "informations sur le produit" avec 20 millions d'enregistrements. Cette table a un champ "quantité de produit restante" (COUNT). De manière générale, SMALLINT (len :16). range:0-65535) est suffisant pour exprimer ce champ, mais si vous utilisez BIGINT (len:64 range:0-18446744073709551615) pour l'exprimer pendant le processus de conception, bien que le programme puisse fonctionner correctement, ce champ ajoutera environ 95 Mo. d'espace de stockage disque supplémentaire (64-16)/8*20 000 000 octets). De plus, lors de la sélection et du tri des données, ce seul champ augmentera votre consommation de mémoire de 95 Mo. En fonction de l'impact du comportement ci-dessus, les performances de. la base de données sera inévitablement affectée

Le principe de la garder aussi petite que possible est de garantir que le type que vous allez choisir peut répondre aux besoins du développement commercial futur, car la structure de la table doit être mise à jour lorsque la quantité de données est relativement importante. C'est une chose très lente et gênante.

b) Essayez de choisir des types simples/appropriés

Lors de la sélection et du tri des tables, les types simples consomment souvent moins de cycle d'horloge CPU. Par exemple, pour le serveur MySql, la comparaison des valeurs de type entier est souvent plus simple et plus rapide que la comparaison des valeurs de type chaîne, donc lorsque vous devez trier une table spécifique, vous devriez essayer de choisir le type entier comme base de tri

c) Essayez de définir le champ sur NOTNULL
En général, si vous ne spécifiez pas explicitement un champ comme NULL, alors ce champ sera considéré comme NULLABLE par le système de base de données. Le comportement par défaut. entraînera les trois problèmes suivants
(1) La propre fonction d'optimisation des requêtes du serveur Mysql sera affectée
(2) Mysql nécessite un espace de stockage supplémentaire et un traitement pour les champs de valeur nulle
(3) Si une valeur nulle fait partie de l'index, l'effet d'index sera également affecté

Comme ce principe n'a pas un grand effet sur l'amélioration des performances de la base de données, il existe un champ NULLABLE pour le schéma DB existant. Ou si l'index est NULLABLE, il y a. Il n'est pas nécessaire de le modifier spécifiquement, mais pour les bases de données ou les index nouvellement conçus, ce principe doit être respecté autant que possible.

Après avoir présenté les principes de sélection des types de données, ce qui suit présentera les types de données courants dans Mysql et ce à quoi il faut prêter attention en termes d'optimisation des performances.

· Entier
Les membres de la famille entière de Mysql incluent principalement TINYINT(8bit), SMALLINT(16bit), MEDIUMINT(24bit), INT(32bit) ou BIGINT(64bit).

Pour les entiers signés, la plage de stockage de ces types est (-2(n-1), 2(n-1)-1), et pour les nombres non signés, la plage d'expression est (0, 2n- 1), pour la base de données, les numéros signés et les numéros non signés occupent le même espace de stockage, donc lors de la sélection du type, vous ne pouvez considérer que la plage du numéro, sans considérer s'il est signé ou non signé

Mysql permet Vous spécifiez la largeur lors de la définition d'un type entier, tel que INT(10). Il existe une différence dans la sortie de INT(10) pour la ligne Client/CMD, mais du point de vue de Mysql Server, il n'y a aucune différence entre INT(10) et INT(32) en termes d'espace de stockage réel/consommation de calcul/ plage de numéros.

· Décimal
Dans Mysql, les types de données de la famille décimale comprennent principalement FLOAT (4Bytes), DOUBLE (8Bytes). De l'espace de stockage de ces deux types, on constate que l'accès de. Les décimales sont plus chères que les entiers. Plus d'espace, donc sauf si nécessaire, vous devriez essayer d'éviter d'utiliser le type décimal

Lors de la création d'un champ de type décimal, vous pouvez utiliser FLOAT(10,3) pour spécifier la précision de la décimale, >= La précision maximale dans la version Mysql 5.0 prend en charge jusqu'à 65 décimales.

Étant donné que la base de données utilise une chaîne de tableau binaire pour stocker les nombres après la virgule décimale, plus la précision dont vous avez besoin est élevée, plus l'espace de stockage/l'horloge du processeur de calcul peut être consommé.

Bien que l'utilisation de décimales puisse consommer plus d'espace de stockage et de ressources CPU, et pour les premières versions de Mysql, la précision sera perdue lorsque deux décimales sont impliquées dans les calculs, mais dans de nombreux cas, cela est nécessaire. stockage des sommes. Dans de nombreux cas, afin de réduire les frais de stockage et de garantir l'exactitude, les décimales sont souvent transformées en nombres entiers et stockées dans la base de données, et les décimales sont converties et calculées dans l'application. Par exemple, le solde du compte d'un utilisateur reste de 999,35 yuans, puis le solde du compte d'un utilisateur reste de 999,35 yuans. Le montant stocké dans les données est de 99935 points. Une fois que le programme de traitement de la banque a obtenu 99935 points, il le convertira d'abord en 999,35 yuans, puis effectuera le traitement correspondant

· Chaîne de caractères

Quel que soit le langage dont il s'agit, la chaîne est un type relativement important et complexe. Cette règle s'applique également à MYSQL
Dans MYSQL, il existe deux types de chaînes principaux : VARCHAR et CHAR La méthode de stockage de ces deux types de chaînes. l'espace disque et la mémoire sont déterminés par le moteur de stockage, et différents moteurs de stockage peuvent avoir différentes méthodes de stockage. De manière générale, pour un moteur de stockage, les méthodes de stockage en disque et en mémoire sont également différentes Lorsque les données sont transférées entre le disque et la mémoire, le moteur de stockage se chargera de convertir les données
VARCHAR
Tout d'abord, il. Il convient de souligner que Mysql utilise une longueur variable pour stocker VARCHAR. Par rapport à une longueur fixe, cette méthode adopte une stratégie d'espace de stockage consistant à « utiliser autant que vous en avez besoin », qui est une solution de stockage relativement peu encombrante. comme type par défaut sans exigences particulières

La raison pour laquelle VARCHAR peut atteindre une longueur fixe est que chaque valeur VARCHAR sera ajoutée à un indicateur de longueur de 1 à 2 octets, par exemple, lorsqu'elle doit stocker " " J'aime Java", le contenu de stockage sous-jacent est "11I Love Java", où 11 (1 octet) représente la longueur. Lorsque la longueur du contenu à stocker est de 1000, l'indicateur de longueur nécessite deux octets. Étant donné que la valeur maximale de 2 octets est de 216, lorsque la chaîne stockée dépasse cette longueur, des exceptions imprévisibles se produiront. Dans ce cas, CLOB doit être utilisé pour stocker de telles chaînes ultra longues.

Dans différentes versions de MYSQL, le traitement des espaces de fin pour les champs VARCHAR est également différent
Version>=5.0 Conserver les espaces de fin
Version
par rapport à MYSQL 5.6 est un exemple :

La surcharge d'espace liée à l'utilisation de VARCHAR(5) et VARCHAR(200) pour stocker « bonjour » est la même. Y a-t-il donc des avantages à utiliser des colonnes plus courtes ?

Cela s'avère être un gros avantage. Les colonnes plus grandes consomment plus de mémoire car MySQL alloue généralement des blocs de mémoire de taille fixe pour contenir les valeurs internes. Ceci est particulièrement problématique lors de l'utilisation de tables temporaires en mémoire pour le tri ou les opérations. C'est tout aussi mauvais lors du tri à l'aide de tables temporaires de disque.

La meilleure stratégie est donc d’allouer uniquement l’espace dont vous avez réellement besoin.

CHAR
La plus grande différence entre le type CHAR et le type VARCHAR est qu'il est de longueur fixe. En même temps, par rapport à VARCHAR, il présente principalement les caractéristiques suivantes
1) Dans toutes les versions MYSQL, les espaces de fin seront tronqués

2) Pour certains Les champs courts et de même longueur sont un bon choix, comme MD5, numéro d'identification
3) Pour les champs qui doivent souvent être modifiés, le type CHAR sera plus efficace
4) Pour certains ultra-courts champs, il est également très peu encombrant. Par exemple, si vous enregistrez "Y" ou "N", l'utilisation de CHAR ne nécessite qu'un octet, tandis que l'utilisation de VARCHAR nécessite deux octets (longueur de 1 octet, valeur de 1 octet)

Pour CHAR de longueur fixe, serveur Mysql Suffisamment grand l'espace de stockage sera alloué par des espaces de remplissage en fonction de sa longueur définie. Une chose à noter est que les opérations de "remplissage des espaces" et de "suppression des espaces de fin" pour VARCHAR/CHAR sont implémentées par le serveur Mysql et n'ont rien à voir avec le moteur de stockage

Ce qui précède est la théorie de l'évolution de MySql haute performance (1) :Optimisation des types de données_Contents sur, veuillez faire attention au site Web PHP chinois (www.php.cn) pour plus de contenu connexe !