MySQL Introduction avancée aux index (partage de résumé)

Cet article vous apporte des connaissances pertinentes sur mysql, qui présente principalement le contenu pertinent sur les index, y compris les avantages et les inconvénients des index, la classification des index, la structure des index et la création d'index, etc., comme suit Jetons un coup d'œil, je j'espère que cela sera utile à tout le monde.

Apprentissage recommandé : Tutoriel vidéo mysql

Qu'est-ce qu'un index

Définition officielle MySQL de index : Index (Index) est une structure de données qui aide MySQL à obtenir des données efficacement.
L'essence de l'index : L'index est une structure de données.
Objectif de l'index : Améliorer l'efficacité des requêtes, qui peut être comparée à un dictionnaire.

Remarque :L'index peut être simplement compris comme 注：可以将索引简单的理解为 “排好序的快速查找数据结构”。一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上

我们平常所说的索引，如果没有特别指明，都是指B树（多路搜索树，并不一定是二叉的）结构组织的索引。其中聚集索引，复合索引，前缀索引，唯一索引默认都是使用B+树索引，统称索引。

索引的优势和劣势

优势

类似大学图书馆建书目索引，提高数据检索的效率，降低数据库的IO成本

通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗

劣势

索引实际上是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也会占用空间

虽然索引大大提高查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息

索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询

MySQL索引分类

单值索引

定义：一个索引只包含单个列，一个表可以有多个单列索引

注：一般一张表最多而言不要超过5个索引

唯一索引

定义：索引列的值必须唯一，但允许有空值

复合索引

定义：一个索引包含多个列

基本语法

• 创建

CREATE [UNIQUE] INDEX indexName ON mytable(columnnname(length));ALTER mytable ADD [UNIQUE] INDEX [indexName] ON (columnname(length))

• 删除

 DROP INDEX [indexName] ON mytable;

• 查看

SHOW INDEX FROM table_name\G

• 使用ALTER命令

有四种方式添加数据表的索引：

ALTER TABLE tbl_name ADD PRIMARY KEY (column_list):该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。

ALTER TABLE tbl_name ADD UNIQUE index_name(column_list):这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。

ALTER TABLE tbl_name ADD INDEX index_name(column_list):添加普通索引，索引值可出现多次。

ALTER TABLE tbl_name ADD FULLTEXT index_name(column_list):该语句指定了索引为FULLTEXT，用于全文索引。

注："structure de données de recherche rapide triée". D'une manière générale, l'index lui-même est également très volumineux et il est impossible de tout stocker en mémoire. Par conséquent, l'index est souvent stocké sur le disque sous la forme d'un fichier index
Ce que nous appelons habituellement le. index, sauf indication contraire, fait référence à B. Un index organisé selon une structure arborescente (arbre de recherche multidirectionnel, pas nécessairement binaire). Parmi eux, l'index clusterisé, l'index composé, l'index de préfixe et l'index unique utilisent tous l'index arborescent B+ par défaut, collectivement appelés index.

Avantages et inconvénients de l'index

Avantages

• Semblable à la création d'un index bibliographique dans une bibliothèque universitaire, il améliore l'efficacité de la récupération des données et réduit le coût d'E/S de la base de données

• à travers les colonnes d'index Triez les données, réduisez le coût de tri des données et réduisez la consommation du processeur

Inconvénients

• L'index est en fait une table qui enregistre la clé primaire et les champs d'index et pointe vers les enregistrements de la table d'entité, donc les colonnes d'index prendront également de la place

• Bien que les index augmentent considérablement la vitesse des requêtes, ils réduiront é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 le fichier d'index. Chaque fois qu'un champ qui ajoute une colonne d'index est mis à jour, les informations d'index après les changements de valeur de clé provoqués par la mise à jour seront ajustés. ne fait qu'améliorer l'efficacité. Un facteur, si votre MySQL dispose d'une grande quantité de tables de données, vous devez passer du temps à rechercher pour créer le meilleur index ou optimiser la requête

Classification d'index MySQL

Indice à valeur unique

Définition :

Un index ne contient qu'une seule colonne et une table peut avoir plusieurs index à une seule colonne

• Remarque : Généralement, une table ne devrait pas avoir plus de 5 index au maximum

• Index unique

• Définition :

  La valeur de la colonne d'index doit être unique, mais les valeurs nulles sont autorisées

• Indice composé

• 🎜Définition : 🎜 Un index contient plusieurs colonnes🎜🎜🎜🎜Syntaxe de base🎜🎜
  🎜🎜Créer🎜🎜🎜rrreee
  🎜🎜Supprimer🎜🎜🎜rrreee
  🎜🎜View🎜🎜🎜rrreee
  🎜🎜Utilisez ALTER command🎜🎜🎜🎜🎜Il existe quatre façons d'ajouter des tables de données Index : 🎜🎜🎜🎜ALTER TABLE tbl_name ADD PRIMARY KEY (column_list) : Cette instruction ajoute une clé primaire, ce qui signifie que la valeur de l'index doit être unique et ne peut pas être NULL. . 🎜🎜🎜🎜ALTER TABLE tbl_name ADD UNIQUE index_name(column_list) : La valeur de l'index créé par cette instruction doit être unique (sauf NULL, NULL peut apparaître plusieurs fois). 🎜🎜🎜🎜ALTER TABLE tbl_name ADD INDEX index_name(column_list) : Ajoutez un index normal, la valeur de l'index peut apparaître plusieurs fois. 🎜🎜🎜🎜ALTER TABLE tbl_name ADD FULLTEXT index_name(column_list) : Cette instruction spécifie l'index comme FULLTEXT pour l'indexation en texte intégral. 🎜🎜🎜Remarque :[PRIMARY | UNIQUE | FULLTEXT] : Le type d'index, indiquant respectivement l'index unique et l'index de texte intégral. 🎜Lors de la création d'une table de données, le type d'index peut être omis. 🎜🎜 [INDEX KEY] : Indique la spécification de créer un index dans la table de données. Structure de l'index MySQL 🎜 🎜Indice R-Tree🎜🎜🎜🎜🎜🎜🎜Indexé Créer 🎜🎜🎜Dans quels cas il est nécessaire de créer un index🎜🎜🎜🎜🎜La clé primaire crée automatiquement un index unique🎜🎜🎜🎜Les champs fréquemment utilisés comme conditions de requête doivent être indexés🎜🎜🎜🎜Champs associés à d'autres tables dans la requête, les relations de clé étrangère sont indexées 🎜🎜🎜🎜Les champs fréquemment mis à jour ne conviennent pas à la création d'index, car chaque mise à jour met non seulement à jour l'enregistrement mais met également à jour l'index🎜🎜🎜🎜Champs qui ne sont pas utilisés dans la condition Where ne sont pas indexés🎜

• Problème de sélection de clé unique/index combiné (les index combinés ont tendance à être créés dans des conditions de concurrence élevée)

• Les champs de tri dans la requête Si le champ de tri est accessible via l'index, la vitesse de tri sera grandement améliorée

  .

• Statistiques dans les champs de requête ou de regroupement

Quelles situations ne conviennent pas pour créer des index

• Trop peu d'enregistrements de table

• Tables qui sont fréquemment ajoutées, supprimées ou modifiées

• Tableau champs avec des données répétées et uniformément réparties, il ne doit donc être utilisé que pour les colonnes de données les plus fréquemment interrogées et les plus fréquemment triées.
  Notez que si une colonne de données contient beaucoup de contenu répété, son indexation n'aura pas beaucoup d'effet pratique

Apprentissage recommandé : Tutoriel vidéo 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!