tutoriel simple sur l'index mysql advanced (2)

Tutoriel simple sur l'index MySQL

Concepts de base

L'indexation fait référence au stockage du contenu du champ A que vous définissez comme index dans un intervalle indépendant S, qui contient uniquement le contenu de ce champ . Lors de la recherche du contenu de ce champ A, il sera recherché directement à partir de cet intervalle indépendant au lieu de rechercher dans la table de données. Après avoir trouvé ces champs qualifiés, lisez l'adresse physique de l'enregistrement de données réel pointé par le champ A, puis affichez le contenu des données correspondant. Si vous recherchez un champ qui n'est pas indexé, il sera recherché à partir de la table de données. Étant donné que la table de données contient de nombreux champs non pertinents, le programme de base de données ne les omettra pas et ne les recherchera pas. Il faut une certaine quantité de ressources pour déterminer ces champs non pertinents et accéder à l'enregistrement plusieurs fois. Bien entendu, plus vous définissez d’index, mieux c’est. Du fait que les index sont placés dans cet intervalle indépendant S, plus l'intervalle indépendant S est grand, plus les ressources occupées par la recherche sont importantes. Si vous n’avez qu’un seul champ indexé, alors votre recherche sur ce champ est très rapide.

Le but de l'établissement d'un index est d'accélérer la recherche ou le tri des enregistrements dans le tableau. Définir un index pour une table a un prix : premièrement, cela augmente l'espace de stockage de la base de données, et deuxièmement, cela prend plus de temps pour insérer et modifier les données (car l'index change également en conséquence).

L'avantage de l'index est qu'il peut trier les colonnes spécifiées et améliorer la vitesse de récupération.

Un exemple simple :

Les données dans une certaine colonne sont

nom d'identification

12 Xiao Li

10 Xiaolong

5 Xiaoqing

99 Xiaohong

Une fois la colonne id indexée, une table d'index sera générée

id index

5 3

10 2

12 1

99 4

Lors de l'interrogation où id =10, la table d'index est utilisée. Parce qu'en dessous de 10, c'est 5. Par conséquent, l’opération d’analyse de table n’est plus effectuée. Renvoie la deuxième donnée, correspondant à la deuxième ligne de la table principale. Cela améliore la vitesse des requêtes. Si aucun index n'est ajouté, la table principale entière sera analysée.

Pour le type d'index, les colonnes qui doivent être indexées et d'autres informations connexes, vous devez effectuer une recherche sur Baidu. Ce que je vous dis, voici quelques concepts de base.

Le rôle, les avantages et les inconvénients des index de bases de données

Pourquoi créer un index ? En effet, la création d'index peut améliorer considérablement les performances du système.

Premièrement, en créant un index unique, l'unicité de chaque ligne de données dans la table de la base de données peut être garantie.

Deuxièmement, cela peut considérablement accélérer la récupération des données, ce qui est également la principale raison de la création d'un index.

Troisièmement, cela peut accélérer la connexion entre les tables, ce qui est particulièrement important pour garantir l'intégrité référentielle des données.

Quatrièmement, lors de l'utilisation de clauses de regroupement et de tri pour la récupération de données, le temps de regroupement et de tri dans la requête peut également être considérablement réduit.

Cinquièmement, en utilisant des index, vous pouvez utiliser des caches d'optimisation pendant le processus de requête pour améliorer les performances du système.

Certaines personnes peuvent se demander : il y a tellement d'avantages à ajouter un index, pourquoi ne pas créer un index pour chaque colonne du tableau ? Bien que cette idée soit raisonnable, elle est également unilatérale. Bien que les index présentent de nombreux avantages, il est très imprudent d’ajouter un index à chaque colonne du tableau. En effet, l'ajout d'un index présente de nombreux inconvénients.

Premièrement, la création et la maintenance d'index prennent du temps, et ce temps augmente à mesure que la quantité de données augmente.

Deuxièmement, les index doivent occuper de l'espace physique en plus de l'espace de données occupé par la table de données, chaque index occupe également une certaine quantité d'espace physique. Si vous souhaitez créer un index clusterisé, l'espace requis. sera plus grand.

Troisièmement, lors de l'ajout, de la suppression et de la modification de données dans la table, l'index doit être maintenu dynamiquement, ce qui réduit la vitesse de maintenance des données.

Les indices sont construits sur certaines colonnes de la table de la base de données. Par conséquent, lors de la création d’un index, vous devez soigneusement déterminer quelles colonnes peuvent être indexées et lesquelles ne peuvent pas l’être. De manière générale, il faut créer des index sur ces colonnes, par exemple :

sur les colonnes fréquemment recherchées peut accélérer les recherches

sur les colonnes qui servent de clés primaires, forcer la colonne L'unicité ; et la structure de disposition des données dans la table d'organisation ;

est souvent utilisée dans les colonnes de connexion. Ces colonnes sont principalement des clés étrangères, ce qui peut accélérer la connexion ; dans la connexion. Créez un index sur la colonne qui doit être recherchée en fonction de la plage, car l'index a été trié et sa plage spécifiée est continue

;

Créez des index sur les colonnes qui doivent souvent être triées, car l'index a été trié, afin que les requêtes puissent utiliser le tri de l'index pour accélérer le temps de tri des requêtes

Créez des index sur les colonnes qui le sont ; souvent utilisé dans les clauses WHERE , accélérant le jugement des conditions.

De plus, certaines colonnes ne doivent pas être indexées. De manière générale, ces colonnes qui ne doivent pas être indexées présentent les caractéristiques suivantes :

Premièrement, il ne faut pas créer d'index pour des colonnes rarement utilisées ou référencées dans les requêtes. En effet, ces colonnes étant rarement utilisées, l’indexation ou non n’améliore pas la vitesse des requêtes. Au contraire, du fait de l’ajout d’index, la vitesse de maintenance du système est réduite et les besoins en espace sont augmentés.

Deuxièmement, les index ne doivent pas être ajoutés aux colonnes contenant peu de valeurs de données. En effet, comme ces colonnes ont très peu de valeurs, comme la colonne sexe de la table personnel, dans les résultats de la requête, les lignes de données du jeu de résultats représentent une grande proportion des lignes de données de la table, c'est-à-dire : les données qui doivent être recherchées dans le tableau La proportion de lignes est énorme. L'augmentation de l'index n'accélère pas significativement la récupération.

Troisièmement, les index ne doivent pas être ajoutés aux colonnes définies comme types de données texte, image et bits. En effet, le volume de données de ces colonnes est soit assez important, soit très peu de valeurs.

Quatrièmement, lorsque les performances de modification sont bien supérieures aux performances de récupération, l'index ne doit pas être créé. En effet, les performances de modification et les performances de récupération sont contradictoires. Lors de l'ajout d'index, les performances de récupération seront améliorées, mais les performances de modification seront réduites. Lors de la réduction des index, les performances de modification augmenteront et les performances de récupération diminueront. Par conséquent, lorsque les performances de modification sont bien supérieures aux performances de récupération, les index ne doivent pas être créés.

Méthodes pour créer des index et caractéristiques des index

Méthodes pour créer des index

Il existe de nombreuses façons de créer des index, ces méthodes incluent les méthodes de création d'index directes et les méthodes de création d'index indirectes méthode. Créez un index directement, par exemple en utilisant l'instruction CREATE INDEX ou utilisez l'assistant de création d'index, ou créez un index indirectement, par exemple en définissant une contrainte de clé primaire ou une contrainte de clé unique dans la table, et l'index est également créé en même temps. temps.

Bien que les deux méthodes puissent créer des index, le contenu spécifique de la création d'index est différent.

Utilisez l'instruction CREATE INDEX ou utilisez l'assistant de création d'index pour créer un index. Il s'agit de la manière la plus simple de créer un index, et cette méthode est la plus flexible. Vous pouvez personnaliser l'index pour répondre à vos besoins. .

Lors de la création d'un index de cette manière, vous pouvez utiliser de nombreuses options, telles que la spécification de la plénitude de la page de données, le tri, le tri des statistiques, etc., afin d'optimiser l'index. En utilisant cette méthode, vous pouvez spécifier le type, l'unicité et la composition de l'index, c'est-à-dire que vous pouvez créer soit un index clusterisé, soit un index non clusterisé, vous pouvez créer un index sur une colonne ou sur deux ou créer un index sur plus de deux colonnes.

Les index peuvent également être créés indirectement en définissant des contraintes de clé primaire ou des contraintes de clé unique. Une contrainte de clé primaire est une logique qui maintient l'intégrité des données en limitant les enregistrements d'une table pour qu'ils aient le même enregistrement de clé primaire. Lors de la création d'une contrainte de clé primaire, le système crée automatiquement un index clusterisé unique.

Bien que, logiquement, la contrainte de clé primaire soit une structure importante, mais en termes de structure physique, la structure correspondant à la contrainte de clé primaire est un index clusterisé unique. En d’autres termes, dans l’implémentation physique, il n’y a pas de contrainte de clé primaire, mais seulement un index clusterisé unique.

De même, lors de la création d'une contrainte de clé unique, un index est également créé, qui est un index unique non clusterisé. Par conséquent, lors de l'utilisation de contraintes pour créer un index, le type et les caractéristiques de l'index ont été essentiellement déterminés et il y a moins de place pour la personnalisation de l'utilisateur.

Lors de la définition d'une clé primaire ou d'une contrainte de clé unique sur une table, si la table a déjà un index standard créé à l'aide de l'instruction CREATE INDEX, l'index créé par la contrainte de clé primaire ou la contrainte de clé unique écrasera l'index précédent. index créé. En d'autres termes, les index créés par des contraintes de clé primaire ou des contraintes de clé unique ont une priorité plus élevée que les index créés à l'aide de l'instruction CREATE INDEX.

Caractéristiques de l'index

L'index a deux caractéristiques, à savoir l'index unique et l'index composite.

L'index unique garantit que toutes les données de la colonne d'index sont uniques et ne contiennent pas de données redondantes. Si la table possède déjà une contrainte de clé primaire ou une contrainte de clé unique, SQL Server crée automatiquement un index unique lorsque la table est créée ou modifiée.

Cependant, si l'unicité doit être garantie, alors une contrainte de clé primaire ou une contrainte de clé unique doit être créée au lieu d'un index unique. Lors de la création d'un index unique, vous devez soigneusement considérer ces règles : Lors de la création d'une contrainte de clé primaire ou d'une contrainte de clé unique dans une table, SQL Server crée automatiquement un index unique

Si la table contient déjà des données, alors ; lors de la création d'un index, SQL Server vérifie la redondance des données déjà dans la table ; chaque fois qu'une instruction insert est utilisée pour insérer des données ou qu'une instruction modifier est utilisée pour modifier des données, SQL Server vérifie la redondance des données : S'il existe des valeurs redondantes. , alors SQL Server annule l'exécution de l'instruction et renvoie un message d'erreur ;

garantit que chaque ligne de données dans la table a une valeur unique, ce qui garantit que chaque entité ne peut être confirmée de manière unique que lorsque l'entité ; peut être garanti Créez un index unique sur la colonne complète Par exemple, vous ne pouvez pas créer un index unique sur la colonne nom de la table personnel car les personnes peuvent avoir le même nom.

Un index composite est un index créé sur deux colonnes ou plus. Lors d'une recherche, lorsque deux colonnes ou plus servent de valeur clé, il est préférable de créer un index composite sur ces colonnes. Lors de la création d'un index composite, vous devez tenir compte de ces règles : Jusqu'à 16 colonnes peuvent être combinées en un seul index composite, et la longueur totale des colonnes qui composent l'index composite ne peut pas dépasser 900 octets, ce qui signifie que la longueur de l'index composite. la colonne composite ne peut pas être trop longue ;

Dans un index composite, toutes les colonnes doivent provenir de la même table, et les colonnes composites ne peuvent pas être créées entre les tables ; dans un index composite, l'ordre des colonnes est très important, l'ordre des colonnes doit donc être soigneusement organisé. En principe, la colonne la plus unique doit être définie en premier. Par exemple, l'index sur (COL1, COL2) n'est pas le même que l'index sur (COL2, COL1) car l'ordre. des colonnes des deux index est différente ;

Pour que l'optimiseur de requête utilise un index composite, la clause WHERE dans l'instruction de requête doit faire référence à la première colonne de l'index composite ; utile lorsqu'il y a plusieurs colonnes clés dans la table ; l'utilisation d'index composites peut améliorer les performances des requêtes et réduire le nombre d'index créés sur une table.

Type d'index

Un index non unique signifie que les valeurs de cet index peuvent être répétées. Par rapport à l'index unique, les valeurs de cet index ne peuvent pas être répétées.

Un exemple simple est comme notre carte d’identité. Si stocké dans la base de données. Si vous créez un index sur le nom, il s'agit d'un index non unique car il existe une personne portant le même nom. Si vous créez un index sur le numéro d'identification, ce sera un index unique. Parce que les numéros sont répétés, ce sera gênant.

Ce qui précède est le contenu du didacticiel simple d'index mysql advanced (2). Pour plus de contenu connexe, veuillez faire attention au site Web PHP chinois (www.php.cn) !