optimisation mysql (1) optimisation des tables et sélection du type de colonne

Optimisation de la table :

1 : Séparation de longueur fixe et de longueur variable

Par exemple, id int, occupe 4 octets, char(4) occupe 4 caractères et a également une longueur fixe ,
time signifie que les octets occupés par chaque valeur d'unité sont fixes

les champs de base et couramment utilisés doivent être construits à une longueur fixe et placés dans un tableau. , Ce type de champ de longueur variable convient pour être placé dans une seule table et utiliser la clé primaire pour l'associer à la table principale

SQL sautera très rapidement 100 000 éléments de données car tous sont constants <.>
2 : Les champs couramment utilisés et les champs peu courants doivent être séparés.

Il doit être analysé en fonction de l'activité spécifique du site Web, et les scénarios de requête des champs doivent être analysés avec. la faible fréquence de requêtes doit être séparée individuellement.

3 : Ajoutez des champs redondants aux champs 1 à plusieurs qui nécessitent des statistiques associées
Réduisez les requêtes associées

Consultez les statistiques d'effet BBS suivantes. . Ne ​​comptez pas le nombre de posts, mais ajoutez-les sous la colonne Champs redondants, mettez à jour le nombre d'articles par 1 à chaque fois qu'un article est publié, ce qui réduira l'intensité des requêtes

Principe de sélection des colonnes :

1 : Type de champ priorité entier> date, heure >enum ,char>varchar > blob,text

Analyse des caractéristiques des colonnes : type entier : longueur fixe, pas de distinction pays/région, non différence de jeu de caractères

Par exemple, tinyint 1,2, 3,4,5 <--> char(1) a, b, c, d, e, en termes d'espace, occupent tous 1 octet, mais commander
par tri, le premier est plus rapide

Raison : ce dernier doit prendre en compte le jeu de caractères et le jeu de classement (c'est-à-dire les règles de tri)

le temps est fixe longueur, fonctionnement rapide et gain de place Compte tenu du fuseau horaire, il n'est pas pratique d'écrire du SQL où
> '2005-10-12'; L'heure est stockée dans le type int ; dans le but de contraindre les valeurs. Il est stocké en interne avec un type entier, mais lorsqu'il est combiné avec char, la conversion interne de chaîne et de valeur est requise
Char Longueur fixe, considérez le jeu de caractères et le jeu de classement (tri)
varchar, longueur variable. doit prendre en compte la conversion du jeu de caractères et le classement lors du tri, ce qui est lent
text/Blob ne peut pas utiliser la table temporaire en mémoire (tri et autres opérations) Ne peut être effectué que sur le disque)

Sexe : prendre utf8 comme un exemple

char(1), 3 mots longs octets

enum('male','female '); // Converti en interne en nombres pour le stockage, il existe un processus de conversion supplémentaire

tinyint(), // 0 1 2 // Longueur fixe de 1 octet.

Livre d'optimisation SQL "MYSQL High Performance Optimization"

Concernant la sélection de la date/heure , l'opinion claire du maître est de choisir directement int unsgined not null pour stocker l'horodatage http://www.xaprb.com/blog/2014/01/ 30/timestamps-in-mysql/

Time-- ->Enregistrer sous un entier

2 : Juste assez, ne soyez pas généreux (comme smallint, varchar(N))

Raison : Les grands champs gaspillent de la mémoire et affectent la vitesse.

Prenons l'exemple de l'âge. tinyint unsigned not null peut stocker 255 ans, ce qui est suffisant. Utiliser int gaspille 3 octets

Le contenu stocké dans varchar(10) et varchar(300) est le. pareil, mais lors d'une requête de jointure de table, varchar(300) prend plus de mémoire

3 : essayez d'éviter d'utiliser NULL()

Raison : NULL n'est pas propice à l'indexation et doit être marqué avec octets spéciaux.

prend en fait plus d'espace sur le disque (mysql5.7 a amélioré null, mais les requêtes sont toujours peu pratiques)

Expérience :

Vous pouvez en créer deux. tables avec les mêmes champs, l'une peut être nulle et l'autre ne peut pas être nulle, ajoutez 10 000 entrées à chacune et vérifiez la taille du fichier d'index. Vous pouvez constater que l'index pour null est plus grand. (Dans mysql5.5, l'optimisation a été effectuée pour null, et la différence de taille n'est plus évidente)

De plus : null n'est pas pratique pour les requêtes,

où nom de colonne =null ;

où le nom de la colonne !=null ; aucune valeur ne peut être trouvée,

où le nom de la colonne est nul ou n'est pas nul peut être interrogé.

create table dictnn (
id int,
word varchar(14) not null default '',
key(word)
)engine myisam charset utf8;

create table dictyn (
id int,
word varchar(14),
key(word)
)engine myisam charset utf8;

alter table dictnn disable keys;
alter table dictyn disable keys;

insert into dictnn select id,if(id%2,word,'') from dict limit 10000;
insert into dictyn select id,if(id%2,word,null) from dict limit 10000;

alert table dictnn enable keys;
alter table dictyn enable keys;

Description de la colonne Enum

1 : La colonne enum est stockée en interne sous forme d'entier

2 : La colonne enum est la plus rapide à associer à la colonne enum

3 : La colonne enum est plus faible que (var) char --- lorsqu'elle rencontre l'association avec char, elle doit être convertie. .

4 : L'avantage est que lorsque le caractère est très long, l'énumération est toujours une longueur entière fixe.

Lorsque la quantité de données interrogées est plus grande, l'avantage de l'énumération devient plus évident.

5 : enum est lié à char/varchar, car il doit être converti, la vitesse est plus lente que enum->enum, char->char,

mais parfois c'est le cas également utilisé comme ceci ----- c'est-à-dire Lorsque la quantité de données est particulièrement importante, les IO peuvent être enregistrées

Test :




<🎜. >

create table t2 (
id int,
gender enum('man','woman'),
key(gender)
)engine myisam charset utf8;

create table t3 (
id int,
gender char(5) not null default '',
key(gender)
)engine myisam charset utf8;

Colonne<- --->Colonne

alter table t2 disable keys;
alter table t3 disable keys;

insert into t2 select id,if(id%2,'man','woman') from dict limit 10000;
insert into t3 select id,if(id%2,'man','woman') from dict limit 10000;

Heure

alter table t2 enable keys;
alter table t3 enable keys;
mysql> select count(*) from t2 as ta,t2 as tb where ta.gender=tb.gender
mysql> select count(*) from t3 as ta,t3 as tb where ta.gender=tb.gender

Énumération<--->énum

10.53

Char<---->char

24.65

Enum<----> ;char

18.22

Si les avantages de la table t2 ne sont pas évidents, augmentez la colonne genre de t3, char(15),
char(20) ...

Avec l'augmentation de la colonne de genre t3 Au fur et à mesure qu'elle s'agrandit, les avantages de la table t2 deviennent progressivement apparents

La raison est que peu importe comment. long les caractères énumérés par enum('manmaman','womanwomanwoman') sont,

En interne, ils sont tous représentés par des entiers, et la taille des données générées en mémoire reste inchangée,

, tandis que le type char génère de plus en plus de données dans la mémoire



Résumé : les types enum et enum sont associés plus rapidement

Le type Enum enregistre les IO

Ce qui précède est le contenu de l'optimisation mysql (1) optimisation des tables et sélection du type de colonne, plus de contenu connexe Veuillez faire attention au site Web PHP chinois (www.php.cn) !