Types d'index MySQL et avantages et inconvénients

1 Sélectionnez le type de données de l'index

MySQL prend en charge de nombreux types de données. Le choix du type de données approprié pour stocker les données a un grand impact sur les performances. De manière générale, certaines directives peuvent être suivies :

(1) Les types de données plus petits sont généralement meilleurs : les types de données plus petits nécessitent généralement moins d'espace sur le disque, la mémoire et le cache du processeur. Le traitement est plus rapide.

(2) Les types de données simples sont meilleurs : les données entières ont moins de surcharge de traitement que les caractères, car la comparaison des chaînes est plus complexe. Dans MySQL, vous devez utiliser les types de données date et heure intégrés au lieu de chaînes pour stocker l'heure et utiliser des types de données entiers pour stocker les adresses IP.

(3) Essayez d'éviter NULL : les colonnes doivent être spécifiées comme NOT NULL, sauf si vous souhaitez stocker NULL. Dans MySQL, les colonnes contenant des valeurs nulles sont difficiles à optimiser dans les requêtes car elles compliquent les index, les statistiques d'index et les opérations de comparaison. Vous devez remplacer les valeurs nulles par 0, une valeur spéciale ou une chaîne vide.

2
Type d'index

Les index sont implémentés dans le moteur de stockage, pas dans la couche serveur. Par conséquent, les index de chaque moteur de stockage ne sont pas nécessairement exactement les mêmes, et tous les moteurs de stockage ne prennent pas en charge tous les types d'index

(1) Index ordinaire

C'est l'index le plus basique dont il dispose. aucune restriction. Il dispose des méthodes de création suivantes :

Create index

CREATE INDEX indexName ON mytable(username(length)); S'il est de type CHAR, VARCHAR, la longueur peut être inférieure à la longueur réelle de le champ ; s'il s'agit de types BLOB et TEXTE, la longueur doit être spécifiée, la même ci-dessous.

Modifier la structure de la table

ALTER mytable ADD INDEX [indexName] ON (username(length)) ◆创建表的时候直接指定
CREATE TABLE mytable(   ID INT NOT NULL,    username VARCHAR(16) NOT NULL,   INDEX [indexName] (username(length))   );  删除索引的语法：
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 :

Créer un index

CREATE UNIQUE INDEX indexName ON mytable(username(length)) ◆修改表结构
ALTER mytable ADD UNIQUE [indexName] ON (username(length)) ◆创建表的时候直接指定
CREATE TABLE mytable(   ID INT NOT NULL,    username VARCHAR(16) NOT NULL,   UNIQUE [indexName] (username(length))   );

(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 :

CREATE TABLE mytable(   ID INT NOT NULL,    username VARCHAR(16) NOT NULL,   PRIMARY KEY(ID)   );  
当然也可以用 ALTER 命令。记住：一个表只能有一个主键。

(4) Index combiné

Afin de comparer de manière vivante les index à colonne unique et les index combinés index, ajoutez plusieurs index à la table Champ :

CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, city VARCHAR(50) NOT NULL, age INT NOT NULL ); pour extraire davantage l'efficacité de MySQL,

Envisagez simplement de créer un index combiné. Construisez simplement le nom, la ville et l'âge dans un index :

ALTER TABLE mytable ADD INDEX name_city_age (name(10),city,age); Lors de la création de la table, 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.

Créer 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. (C'est une des questions de l'entretien, j'aurais dû y répondre correctement à l'époque) Non seulement les requêtes contenant ces trois colonnes utiliseront cet index combiné, mais le SQL suivant utilisera cet index combiné :

SELECT FROM mytable WHREE username="admin" AND city="郑州"  SELECT FROM mytable WHREE username="admin" 而下面几个则不会用到：
SELECT FROM mytable WHREE age=20 AND city="郑州"  SELECT FROM mytable WHREE city="郑州"

3
Il est temps de créer un index

À ce stade, nous avons appris comment créer un index, alors dans quelles circonstances devons-nous créer un index ? D'une 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 l'indice. Par exemple :

SELECT t.Name  FROM mytable t LEFT JOIN mytable m    
ON t.Name=m.username WHERE m.age=20 AND m.city='郑州' 此时就需要对city和age建立索引，由于mytable表的userame也出现在了JOIN子句中，也有对它建立索引的必要。

Je viens de mentionner que seuls certains LIKE doivent être indexés. 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 des index :

SELECT * FROM mytable WHERE username like'admin%' 而下句就不会使用：
SELECT * FROM mytable WHEREt Name like'%admin' 因此，在使用LIKE时应注意以上的区别。

4
Les défauts des index

Ce qui précède L'utilisation des index mentionnée présente des avantages, mais une utilisation excessive des index peut conduire à des abus. Par conséquent, l'index aura également ses défauts :

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.

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 quantité de tables de données, vous devez passer du temps à rechercher et à créer les meilleurs index, ou à optimiser les instructions de requête

<🎜. >

Remarques sur l'utilisation des index

只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。

使用短索引

对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

索引列排序

MySQL查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。

like语句操作

一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。

不要在列上进行运算

select * from users where YEAR(adddate)<2015; 将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成
select * from users where adddate<‘2015-01-01’;

不使用NOT IN和<>操作

以上就是MySQL索引类型与优缺点的内容，更多相关内容请关注PHP中文网（m.sbmmt.com）！