Quel est le principe d'indexation et la stratégie d'optimisation de la base de données MySQL ?

1 Index

Concept d'index

Un index est un fichier spécial qui contient des pointeurs de référence vers tous les enregistrements de la table de données. Vous pouvez créer un index sur une ou plusieurs colonnes de la table et spécifier le type d'index. Chaque type d'index a sa propre implémentation de structure de données.

Le rôle de l'index

La relation entre les tables, les données et les index dans une base de données est similaire à la relation entre les livres, le contenu des livres et les catalogues de livres sur une étagère. L'index joue un rôle similaire à celui d'un catalogue de livres et peut. être utilisé pour localiser et récupérer rapidement des données. Les index peuvent grandement améliorer les performances des bases de données.

Scénarios d'utilisation d'index

Pour envisager de créer un index sur une ou plusieurs colonnes d'une table de base de données, vous devez prendre en compte les points suivants :

• La quantité de données est importante et des requêtes conditionnelles sont souvent effectuées sur celles-ci. colonnes.

• La fréquence des opérations d'insertion et de modification sur ces colonnes dans cette table de base de données est faible.

• Les index occupent de l'espace disque supplémentaire.

2 Classification de l'index

• Divisé de la structure de stockage de l'index : index BTree, index de hachage, index de texte intégral FULLTEXT, index RTree

• Divisé du niveau d'application : index ordinaire, index unique, clé primaire index, index composé

• Divisé du type de valeur de clé d'index, index de clé primaire, index auxiliaire (index secondaire)

• Divisé de la relation logique entre le stockage des données et la valeur de la clé d'index : index clusterisé (index clusterisé) non -index clusterisé (index non clusterisé) Index)

• Divisé du nombre de colonnes d'index : index à colonne unique, index composé

La différence entre l'index B-tree et l'index arbre B+

Différence :

L'emplacement de stockage des données est différent : stockage de l'arbre B+ Au niveau du nœud feuille, le B-tree est stocké dans tous les nœuds, ce qui reflète les avantages de l'arbre B+ : le nœud ne stocke pas de données, donc un le nœud peut stocker plus de clés. Cela peut rendre l'arborescence plus courte, ce qui réduit le nombre d'opérations d'E/S. Les performances des requêtes sont stables : chaque requête passe du nœud racine au nœud feuille, la longueur du chemin de la requête est la même, c'est-à-dire que chaque requête est également efficace et la complexité temporelle est fixée à O(log(n))

Pointage des nœuds feuilles : arbre B+ Les nœuds feuilles adjacents sont connectés via des pointeurs. L'arbre B n'a pas de

reflétant les avantages de l'arbre B+ : tous les nœuds feuilles forment une liste chaînée ordonnée, ce qui facilite la recherche de plage

3 Opération d'indexation

Créer un index de clé primaire

-- 在创建表的时候，直接在字段名后指定 primary key
create table user1(id int primary key, name varchar(30));
-- 在创建表的最后，指定某列或某几列为主键索引
create table user2(id int, name varchar(30), primary key(id));
-- 创建表以后再添加主键
create table user3(id int, name varchar(30));
alter table user3 add primary key(id);

Caractéristiques de l'index de clé primaire :

Il y a au plus un index de clé primaire dans une table. Bien sûr, cela peut rendre l'index de clé primaire très efficace (la clé primaire ne peut pas être répétée)
• .

Créez la colonne de l'index de clé primaire, sa valeur ne peut pas être nulle et ne peut pas être répétée
Les colonnes de l'index de clé primaire sont essentiellement int
Création d'un index unique

• -- 在表定义时，在某列后直接指定unique唯一属性。
  create table user4(id int primary key, name varchar(30) unique);
  -- 创建表时，在表的后面指定某列或某几列为unique
  create table user5(id int primary key, name varchar(30), unique(name));
  -- 创建表以后再添加unique
  create table user6(id int primary key, name varchar(30)）；
  alter table user6 add unique(name);

  Caractéristiques de l'index unique index :

Dans une table, il peut y avoir plusieurs index uniques

Efficacité des requêtes Élevée
Si vous créez un index unique sur une certaine colonne, vous devez vous assurer que cette colonne ne peut pas contenir de données en double
Si vous spécifiez non null sur un index unique, cela équivaut à la création d'un index de clé primaire
index ordinaire

• --在表的定义最后，指定某列为索引
  create table user8(id int primary key,
  name varchar(20),
  email varchar(30),
  index(name)
  );
  --创建完表以后指定某列为普通索引
  create table user9(id int primary key, name varchar(20), email varchar(30));
  alter table user9 add index(name); 
  -- 创建一个索引名为 idx_name 的索引
  create table user10(id int primary key, name varchar(20), email varchar(30));
  create index idx_name on user10(name)；

  Caractéristiques des index ordinaires :

Il peut y avoir plusieurs index ordinaires dans une table . Les index ordinaires sont plus couramment utilisés dans le développement réel

Si une colonne doit être indexée, mais que la colonne a des valeurs en double, alors nous devons utiliser des index ordinaires
index de requête
afficher les clés de la table. nom

• mysql> afficher les clés de marchandisesG

  *********** 1. ligne **** *******

  Table : marchandises Non_unique : 0 Key_name : PRIMARY Seq_in_index : 1

  Column_name : goods_id Collation : A
  Cardinalité : 0
  Sous_partie : NULL
  Emballé : NULL
  Null :
  Index_type : BTREE Commentaire :
  1 ligne dans l'ensemble (0,00 sec)
  
  
  
  
  afficher l'index du nom de la table ;
  

  desc nom de la table
  Supprimer l'index
  Supprimer l'index de clé primaire : modifier le nom de la table, supprimer la clé primaire ;
  Supprimer les autres index : modifier le nom de la table, supprimer le nom de l'index ; le nom de l'index est le champ Key_name dans afficher les clés du nom de la table

  • mysql> alter table user10 drop index idx_name;

  • déposer le nom de l'index sur le nom de la table

  mysql> drop index name on user8

  Principe de création d'index
  Les champs qui sont plus fréquemment utilisés comme conditions de requête doivent être indexés
  Les champs peu uniques ne conviennent pas à la création d'un index seuls, même s'ils sont fréquemment utilisés comme conditions de requête
  Les champs mis à jour très fréquemment ne conviennent pas à la création d'un index
  Les champs qui n'apparaissent pas dans la clause Where ne doivent pas créer d'index

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!