Créer un index pour Myql

Afin d'améliorer les performances de Mysql, nous pouvons créer des index pour améliorer la vitesse de recherche de Mysql et soulager la pression sur la base de données Mysql. Parlons des index Mysql et de certaines utilisations avancées.

Tous les types de colonnes MySQL peuvent être indexés. Définissez le nombre maximum d'index et la longueur maximale de l'index pour chaque table en fonction du moteur de stockage.
Tous les moteurs de stockage prennent en charge au moins 16 index par table, avec une longueur totale d'index d'au moins 256 octets. La plupart des moteurs de stockage ont des limites plus élevées.

Il n'existe actuellement que deux types d'index de stockage (btree et hash), qui sont spécifiquement liés au mode moteur de stockage :
MyISAM btree
InnoDB btree
Hash MEMORY/Heap, btree

Par défaut, le moteur de stockage MEMORY/Heap utilise l'index de hachage

La différence entre l'index btree de MySQL et l'index de hachage
La particularité de la structure de l'index de hachage, son efficacité de récupération est très élevée, la récupération de l'index peut être localisée en une seule fois, contrairement L'index btree (B-Tree) nécessite plusieurs accès IO du nœud racine au nœud de branche, et enfin au nœud de page, de sorte que l'efficacité des requêtes de l'index de hachage est beaucoup supérieure à celle de l'index btree (B-Tree).

Bien que l'index de hachage soit très efficace, l'index de hachage lui-même présente également de nombreuses limitations et inconvénients en raison de sa particularité, principalement les suivants.
(1) L'index de hachage ne peut satisfaire que les requêtes =, <=>, IN, IS NULL ou IS NOT NULL, et les requêtes par plage ne peuvent pas être utilisées.
Étant donné que l'index de hachage compare la valeur de hachage après l'opération de hachage, il ne peut être utilisé que pour le filtrage de valeurs égales et ne peut pas être utilisé pour le filtrage basé sur la plage, car la valeur de hachage après traitement par l'algorithme de hachage correspondant La taille Il n’est pas garanti que la relation soit exactement la même qu’avant l’opération de hachage.

(2) L'index de hachage ne peut pas être utilisé pour éviter les opérations de tri des données.
Étant donné que l'index de hachage stocke la valeur de hachage après le calcul du hachage et que la relation de taille de la valeur de hachage n'est pas nécessairement exactement la même que la valeur clé avant l'opération de hachage, la base de données ne peut donc pas utiliser les données d'index pour éviter toute opération de tri ;

(3) L'index de hachage ne peut pas être interrogé à l'aide de clés d'index partiel.
Pour l'index combiné, lors du calcul de la valeur de hachage de l'index de hachage, les clés d'index combinées sont fusionnées, puis la valeur de hachage est calculée ensemble, au lieu de calculer la valeur de hachage séparément, elle est donc calculée via le d'abord une ou plusieurs clés d'index de l'index combiné lors de l'interrogation, l'index de hachage ne peut pas être utilisé.

(4) L'index de hachage ne peut à aucun moment éviter l'analyse de la table.
Comme nous le savons auparavant, l'index de hachage consiste à stocker la valeur de hachage du résultat de l'opération de hachage et les informations de pointeur de ligne correspondantes dans une table de hachage après le hachage de la clé d'index, puisque différentes clés d'index ont la même valeur de hachage. Ainsi, même si vous obtenez le nombre d'enregistrements qui satisfont à une certaine valeur de clé de hachage, vous ne pouvez pas compléter directement la requête à partir de l'index de hachage. Vous devez toujours effectuer les comparaisons correspondantes en accédant aux données réelles de la table et obtenir les résultats correspondants.

(5) Lorsqu'un index de hachage rencontre un grand nombre de valeurs de hachage égales, ses performances ne seront pas nécessairement supérieures à celles de l'index B-Tree.
Pour les clés d'index à faible sélectivité, si un index de hachage est créé, il y aura un grand nombre d'informations de pointeur d'enregistrement associées à la même valeur de hachage. De cette façon, il sera très difficile de localiser un certain enregistrement et cela gaspillera plusieurs accès aux données de la table, ce qui entraînera de faibles performances globales


L'index B-Tree est l'index le plus fréquemment utilisé tapez dans la base de données MySQL. Tous les moteurs de stockage, à l'exception du moteur de stockage Archive, prennent en charge les index B-Tree. Ce n'est pas seulement le cas dans MySQL, mais en fait, dans de nombreux autres systèmes de gestion de bases de données, l'index B-Tree est également le type d'index principal. Cela est principalement dû au fait que la structure de stockage de l'index B-Tree a certaines fonctions dans la récupération des données de la base de données. .Très bonne prestation.
De manière générale, la plupart des fichiers physiques de l'index B-Tree dans MySQL sont stockés dans la structure Balance Tree, c'est-à-dire que toutes les données réelles requises sont stockées dans le nœud feuille de l'arbre et sont accessibles depuis n'importe quel nœud feuille La longueur du chemin le plus court est exactement la même, c'est pourquoi nous l'appelons tous un index B-Tree. Bien sûr, diverses bases de données (ou divers moteurs de stockage de MySQL) peuvent stocker leurs propres index B-Tree. légèrement modifié.
Par exemple, la structure de stockage réelle utilisée par l'index B-Tree du moteur de stockage Innodb est en fait B+Tree, ce qui signifie qu'une petite modification est apportée sur la base de la structure de données B-Tree, et elle est ajoutée vers chaque nœud feuille. En plus de stocker les informations pertinentes de la clé d'index, les informations du pointeur pointant vers le nœud feuille suivant adjacent au nœud feuille sont également stockées. Ceci est principalement pour accélérer l'efficacité de la récupération de plusieurs nœuds feuilles adjacents.
Dans le moteur de stockage Innodb, il existe deux formes différentes d'index, l'une est l'index de clé primaire (Primary Key) sous forme de cluster, et l'autre est fondamentalement la même forme de stockage que les autres moteurs de stockage (tels que le Moteur de stockage MyISAM) Index B-Tree ordinaire, cet index est appelé index secondaire dans le moteur de stockage Innodb.
Dans Innodb, si vous accédez aux données via la clé primaire, c'est très efficace. Si vous accédez aux données via l'index secondaire, Innodb récupère d'abord le nœud feuille via les informations pertinentes de l'index secondaire et la clé d'index correspondante. Obtenez ensuite la ligne de données correspondante via la valeur de clé primaire stockée dans le nœud feuille, puis via l'index de clé primaire.
La différence entre l'index de clé primaire et l'index de clé non primaire du moteur de stockage MyISAM est très faible, sauf que la clé d'index de l'index de clé primaire est une clé unique et non vide. De plus, la structure de stockage de l'index du moteur de stockage MyISAM est fondamentalement la même que celle de l'index secondaire d'Innodb. La principale différence est que le moteur de stockage MyISAM stocke les informations de clé d'index sur les nœuds Leaf
Le fichier de données MyISAM. peut être localisé directement après le stockage. Les informations de la ligne de données correspondante dans les données (telles que le numéro de ligne), mais les informations sur la valeur de clé de la clé primaire ne seront pas stockées.

Les index sont divisés en index à colonne unique et en index combinés. Un index à une seule colonne signifie qu'un index ne contient qu'une seule colonne. Une table peut avoir plusieurs index à une seule colonne, mais il ne s'agit pas d'un index combiné. Index combiné, c'est-à-dire qu'un index contient plusieurs colonnes.
Les types d'index MySQL incluent :
(1) L'index ordinaire, qui est l'index le plus basique et n'a aucune restriction. Il dispose des méthodes de création suivantes :

-- Create index

CREATE INDEX indexName ON mytable(username(10));

-- CREATE INDEX indexName ON mytable(username(10),city(10)); -- Index combiné

-- indexName est le nom de l'index, le nom de la table mytable, le nom d'utilisateur et la ville ​​est le nom de la colonne, 10 est la longueur du préfixe, c'est-à-dire la longueur des informations stockées dans l'index à partir du caractère le plus à gauche de la colonne, octet unité

-- s'il s'agit de CHAR, VARCHAR type, la longueur du préfixe peut être inférieure à la longueur réelle du champ ; S'il s'agit de types BLOB et TEXT, la longueur du préfixe doit être spécifiée, la même ci-dessous.

-- Modifier la structure de la table pour créer un index

ALTER TABLE mytable ADD INDEX indexName (username(10));

-- ALTER TABLE mytable ADD INDEX indexName (username(10),city(10));

-- Le nom de l'index ici n'a pas besoin d'être écrit, le système attribuera automatiquement les noms username, username_2, username_3,.. .

-- Spécifiez directement

CREATE TABLE mytable(

id INT,

username VARCHAR(16),

ville VARCHAR(16),

age INT,

INDEX indexName (nom d'utilisateur(10))-- INDEX indexName (nom d'utilisateur(10),ville(10))

);

-- Ici, le nom de l'index indexName peut également être omis

(2) Index unique, qui est similaire à l'index ordinaire précédent, la différence est : la valeur de la colonne d'index doit être unique, mais les valeurs nulles sont autorisées. Dans le cas d'un index composite, la combinaison des valeurs des colonnes doit être unique. Il dispose des méthodes de création suivantes (ajoutez uniquement UNIQUE avant le mot-clé INDEX lors de la création d'un index normal) :

-- Créer un index

CREATE UNIQUE INDEX indexName ON mytable(username(10));

-- Modifier la structure de la table pour créer un index

ALTER TABLE mytable ADD UNIQUE INDEX indexName (username( 10) ));-- Il peut également être abrégé en ALTER TABLE mytable ADD UNIQUE indexName (username(10));

-- Spécifiez directement

CREATE TABLE lors de la création de la table mytable(

id INT,

username VARCHAR(16),

city VARCHAR(16),

age INT,

UNIQUE INDEX indexName (username(10)) -- peut également être abrégé en UNIQUE indexName (username(10))

);

(3) Clé primaire index, qui est un index unique spécial qui n'autorise pas les valeurs nulles. La clé primaire créée en même temps lors de la création de la table est l'index de clé primaire
L'index de clé primaire n'a pas besoin d'être nommé, et une table ne peut avoir qu'une seule clé primaire. L'index de clé primaire peut être à la fois un index unique ou un index de texte intégral, mais l'index unique ou l'index de texte intégral ne peuvent pas coexister dans le même index :

- - Modifier la structure de la table pour créer un index ALTER TABLE mytable ADD PRIMARY KEY (id );

-- Spécifiez directement CREATE TABLE mytable lors de la création d'une table (
id INT,
username VARCHAR(16 ),
city VARCHAR(16),
age INT, PRIMARY KEY(id)
);

(4) Index de texte intégral, moteur de stockage InnoDB ne prend pas en charge l'index en texte intégral :

-- Créer un index CREATE FULLTEXT INDEX indexName ON mytable(username(10));

-- Modifier la structure de la table pour créer un index ALTER TABLE mytable ADD FULLTEXT INDEX indexName (username(10));

- - Il peut également être abrégé en ALTER TABLE mytable ADD FULLTEXT indexName (username(10));

-- Spécifiez directement CREATE TABLE mytable(
id INT,
username VARCHAR(16) lors de la création de la table,
city VARCHAR(16),
age INT,
FULLTEXT INDEX indexName ( username(10))

-- peut également être abrégé en FULLTEXT indexName (username(10)))ENGINE =MYISAM;

-- Créez un index de texte intégral lors de la création d'une table. Définissez le moteur de stockage de la table sur MYISAM. Le moteur de stockage InnoDB par défaut de la nouvelle version de MySQL ne prend pas en charge les index de texte intégral

-- Supprimez l'index DROP INDEX indexName ON mytable;

Bien que l'index améliore considérablement la vitesse des requêtes, il réduit également la vitesse de mise à jour de la table, comme INSERT, UPDATE et DELETE sur la table. Parce que lors de la mise à jour de la table, MySQL doit non seulement enregistrer les données, mais également enregistrer le fichier d'index.
La création de fichiers d'index occupera de l'espace disque. Généralement, ce problème n'est pas grave, mais si vous créez plusieurs index combinés sur une grande table, le fichier d'index se développera rapidement.

Ce qui précède est ce que j'ai résumé sur la création d'un index dans Mysql. J'espère que cela sera utile à tout le monde à l'avenir.

Articles associés :

L'insertion de données dans myql en PHP affiche des caractères tronqués

Configuration optimisée de Myql5.7.7 paramètres _MySQL

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!