MySQL Avancé 12 - Index

L'indexation est la clé d'une recherche rapide. L'établissement d'un 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 y insérons aléatoirement 10 000 enregistrements, 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 ou VARCHAR, la longueur peut être inférieure à la longueur réelle du champ ; s'il s'agit de types BLOB et TEXT, la longueur doit être spécifiée, comme ci-dessous.

2. Modifier la structure de la table

Le code est le suivant :

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

Supprimer l'index :

DROP INDEX [indexName] ON mytable;

2. 🎜>
Il est similaire à l'index ordinaire précédent, sauf que la valeur de la colonne d'index doit être unique, mais les valeurs nulles sont autorisées. 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 du tableau

ALTER mytable ADD UNIQUE [indexName] ON (username(length)) ;

A préciser directement lors de la création du tableau

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 :

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 d'une seule colonne et l'index combiné, ajoutez plusieurs champs au tableau :

Le code est le suivant :

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

Afin d'extraire davantage l'efficacité de MySQL, il est nécessaire d'envisager d'établir un index combiné. C'est-à-dire construire le nom, la ville, l'âge dans un index :

Le code est le suivant :

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.

É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, nom d'utilisateur de la ville Pourquoi n'y a-t-il pas d'index combiné comme la ville et l'â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é, le SQL suivant utilisera cet index combiné :

SELECT * FROM mytable WHREE username="admin" AND city="郑州"  SELECT * FROM mytable WHREE username="admin";

Les quelques suivants ne l'utiliseront pas :

Le code est le suivant :

SELECT * FROM mytable WHREE age=20 AND city="郑州"  SELECT * FROM mytable WHREE city="郑州";

5. Moment pour créer un index

Jusqu'à présent, nous avons appris à créer un index, alors dans quelles circonstances devons-nous 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='郑州';

A ce moment, 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 d'être indexé.

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 comme suit :

SELECT * FROM mytable WHEREt Name like'%admin';

因此，在使用LIKE时应注意以上的区别。

六、索引的不足之处

上面都在说使用索引的好处，但过多的使用索引将会造成滥用。因此索引也会有它的缺点：

1.虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。

2.建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快。

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

七、使用索引的注意事项

使用索引时，有以下一些技巧和注意事项：

1.索引不会包含有NULL值的列

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

2.使用短索引

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

3.索引列排序

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

4.like语句操作

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

5.不要在列上进行运算

代码如下:

select * from users where YEAR(adddate)<2007;

将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成:

代码如下:

select * from users where adddate<‘2007-01-01&#39;;

6.不使用NOT IN和<>操作

 以上就是MySQL高级十二——索引的内容，更多相关内容请关注PHP中文网（www.php.cn）！