Quels sont les types d'index dans la base de données MySQL ? Méthodes d'établissement et avantages et inconvénients

Cet article se concentrera sur la description des quatre types d'index de base de données masql. Comment créer un index de base de données ? Les colonnes apparaissant dans WHERE et JOIN doivent être indexées, mais pas entièrement, car MySQL n'utilise que <, <=, =, >, >=, BETWEEN, IN et parfois LIKE. J'espère que cet article pourra aider tout le monde. Tout d'abord, comprenons ce qu'est un index. Pour le résumer en une phrase : l'index est la clé d'une recherche rapide.

L'établissement de l'index MySQL est très important pour le fonctionnement efficace de MySQL. Ce qui suit présente plusieurs types d'index MySQL courants

Dans les tables de base de données, les champs d'indexation peuvent considérablement améliorer la vitesse des requêtes. Supposons que nous créions une table mytable :

Le code est le suivant :

CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL );

Nous aléatoirement Il y a 10 000 enregistrements insérés, dont un : 5555, admin.

Lors de la recherche de l'enregistrement de username="admin" SELECT * FROM mytable WHERE username='admin';, si un index a été établi sur le nom d'utilisateur, MySQL peut trouver avec précision l'enregistrement sans aucune analyse. Au contraire, MySQL analysera tous les enregistrements, c'est-à-dire que 10 000 enregistrements seront interrogés.

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 Index ordinaire

Il s'agit de l'index le plus basique, il n'a aucune restriction. Il dispose des méthodes de création suivantes :

1. Créer un index

Le code est le suivant :

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

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

2. Modifier la structure de la table

Le code est le suivant :
ALTER mytable ADD INDEX [indexName] ON (username(length)) -- Spécifiez directement

CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, INDEX [indexName] (username(length)) );

-- Syntaxe pour supprimer l'index :

DROP INDEX [indexName] ON mytable;

2. Index unique

Il est similaire à l'index ordinaire précédent, sauf que la valeur de la colonne d'index doit être unique, mais c'est autorisé. Il y a une valeur nulle. 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 :

Le code est le suivant :

CREATE UNIQUE INDEX indexName ON mytable(username(length))

--Modifier la structure de la table
ALTER mytable ADD UNIQUE [indexName] ON (username(length))
-- Spécifiez directement
CREATE TABLE mytable lors de la création de la table (ID INT NOT NULL, username VARCHAR(16) NOT NULL, UNIQUE [indexName] (username (longueur) ) );

3. Index de clé primaire

C'est un index unique spécial qui n'autorise pas les valeurs nulles. Généralement, l'index de clé primaire est créé en même temps lors de la création de la table :

Le code est le suivant :

CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, PRIMARY KEY(ID) ) ;

Bien sûr, vous pouvez également utiliser la commande ALTER. N'oubliez pas : une table ne peut avoir qu'une seule clé primaire.

4. Index combiné

Afin de comparer de manière vivante l'index à colonne unique et l'index combiné, ajoutez plusieurs champs au tableau :

Le code est comme suit :

CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, city VARCHAR(50) NOT NULL, age INT NOT NULL

Afin d'aller plus loin); Pour extraire l'efficacité de MySQL, vous devez envisager de créer un index composite. Construisez simplement le nom, la ville et l'âge dans un index :

Le code est le suivant :

ALTER TABLE mytable ADD INDEX name_city_age (name(10),city,age);[code]

Lors de la création du tableau, la longueur du nom d'utilisateur est de 16 et 10 est utilisé ici. En effet, la longueur du nom ne dépassera généralement pas 10, ce qui accélérera la requête d'index, réduira la taille du fichier d'index et améliorera la vitesse de mise à jour d'INSERT.

Si vous créez des index à colonne unique sur le nom d'utilisateur, la ville et l'âge respectivement, de sorte que la table ait trois index à colonne unique, l'efficacité de la requête sera très différente de l'index combiné ci-dessus, qui est bien inférieur que notre indice combiné. Bien qu'il existe actuellement trois index, MySQL ne peut utiliser que l'index à une seule colonne qui lui semble le plus efficace.

Établir un tel index combiné équivaut en fait à établir les trois ensembles d'index combinés suivants :

nom d'utilisateur, ville, âge nom d'utilisateur, ville nom d'utilisateur Pourquoi n'y a-t-il pas d'index combiné comme ville, âge ? Ceci est le résultat du « préfixe le plus à gauche » de l'index composite MySQL. La compréhension simple est de commencer la combinaison uniquement à partir de celle la plus à gauche. Non seulement les requêtes contenant ces trois colonnes utiliseront cet index combiné, mais le SQL suivant utilisera cet index combiné :
[code]
SELECT * FROM mytable WHREE username="admin" AND city ="Zhengzhou" SELECT * FROM mytable WHREE username="admin"

Les suivants ne seront pas utilisés :

Le code est le suivant :
SELECT * FROM mytable WHREE age=20 AND city="Zhengzhou" SELECT * FROM mytable WHREE city="Zhengzhou"

5. Comment créer un index

Jusqu'à présent, nous avons appris comment créer un index, puis nous Dans quelles circonstances faut-il créer un index ? De manière générale, les colonnes apparaissant dans WHERE et JOIN doivent être indexées, mais ce n'est pas tout à fait vrai car MySQL indexe uniquement <, <=, =, >, >=, BETWEEN, IN et parfois LIKE utilisera le indice. Par exemple :

Le code est le suivant :

SELECT t.Name FROM mytable t LEFT JOIN mytable m ON t.Name=m.username WHERE m.age=20 AND m.city ='Zhengzhou'

À ce stade, il est nécessaire d'indexer la ville et l'âge. Puisque le nom d'utilisateur de la table mytable apparaît également dans la clause JOIN, il est également nécessaire de l'indexer.

Je viens de mentionner que LIKE ne doit être indexé qu'à certains moments. Parce que MySQL n'utilisera pas l'index lors des requêtes commençant par les caractères génériques % et _. Par exemple, la phrase suivante utilisera l'index :

Le code est le suivant :
SELECT * FROM mytable WHERE username like'admin%'
La phrase suivante n'utilisera pas l'index :

Le code est le suivant :

SELECT * FROM mytable WHEREt Name like'%admin'

Par conséquent, vous devez faire attention aux différences ci-dessus lorsque vous utilisez LIKE.

6. Inconvénients des index

Ce qui précède parle des avantages de l'utilisation des index, mais une utilisation excessive des index entraînera des abus. Par conséquent, l'index aura également ses inconvénients :

1. Bien que l'index améliore considérablement la vitesse des requêtes, il réduira é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.

2. 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.

Les index ne sont qu'un facteur d'amélioration de l'efficacité. Si votre MySQL dispose d'une grande table de données, vous devez passer du temps à rechercher et à créer les meilleurs index ou à optimiser les instructions de requête.

7. Choses à noter lors de l'utilisation des index :

Lors de l'utilisation des index, il y a les conseils et précautions suivants :

1. ne contient pas Les colonnes avec des valeurs NULL

ne seront pas incluses dans l'index tant que la colonne contient des valeurs NULL. Tant qu'il y a une colonne dans l'index composite qui contient des valeurs NULL, cette colonne le sera. être invalide pour l'index composite. Par conséquent, lors de la conception de la base de données, nous ne devons pas laisser la valeur par défaut du champ être NULL.

2. Utilisez un index court

pour indexer la chaîne, et une longueur de préfixe doit être spécifiée si possible. Par exemple, si vous avez une colonne CHAR(255), si la plupart des valeurs sont uniques dans les 10 ou 20 premiers caractères, n'indexez pas la colonne entière. Les index courts améliorent non seulement la vitesse des requêtes, mais permettent également d'économiser de l'espace disque et des opérations d'E/S.

3. Tri des colonnes d'index

La requête MySQL n'utilise qu'un seul index, donc si l'index a été utilisé dans la clause Where, les colonnes classées par n'utiliseront pas l'index. Par conséquent, n'utilisez pas d'opérations de tri lorsque le tri par défaut de la base de données peut répondre aux exigences ; essayez de ne pas inclure le tri de plusieurs colonnes. Si nécessaire, il est préférable de créer des index composites pour ces colonnes.

4. Opération d'instruction similaire

Généralement, l'utilisation de l'opération similaire n'est pas encouragée. Si elle doit être utilisée, comment l'utiliser est également un problème. Comme « %aaa% » n'utilisera pas l'index mais comme « aaa% » utilisera l'index.

5. N'effectuez pas d'opérations sur les colonnes

Le code est le suivant :
sélectionnez * parmi les utilisateurs où YEAR(adddate)<2007;

sera dans chaque ligne Cela entraînera l'échec de l'index et effectuera une analyse complète de la table, nous pouvons donc le changer en :

Le code est le suivant :

sélectionnez * parmi les utilisateurs où adddate<' 2007-01-01' ;

6. N'utilisez pas les opérations NOT IN et <>

Ci-dessus, les types d'index MySQL sont présentés. J'espère que cela aide tout le monde.

Deux méthodes d'indexation :

Indice B-tree

Remarque : Il est appelé index btree, qui est un gros aspect Regardez, ils utilisent tous des arbres équilibrés, mais en termes d'implémentation spécifique, chaque moteur de stockage est légèrement différent. Par exemple, à proprement parler, le moteur NDB utilise T-tree
Myisam, et dans innodb, l'index B-tree. est utilisé par défaut. , la complexité théorique du temps de requête de B-tree est O(log2 (N-1)), N est le nombre de lignes dans la table de données

index de hachage
Pour les tables utilisant moteur de stockage en mémoire, la valeur par défaut est hash La complexité théorique du temps de requête de l'index et du hachage est O(1)

Recommandations associées :

Explication détaillée de l'utilisation de l'indexation et de l'analyse MySQL d'avantages et d'inconvénients

Avantages et inconvénients de l'indexation page 1/2

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!