L'index MySQL est sous contrôle

Cet article vous apporte des connaissances pertinentes sur mysql, qui présente principalement des problèmes liés à l'index mysql, y compris des problèmes de type d'index tels que la division de l'index selon des fonctions logiques, la division selon l'implémentation physique, la division selon le nombre de champs, etc. , j'espère que cela aidera tout le monde.

Apprentissage recommandé : Tutoriel MySQL

Dans l'optimisation SQL, les index sont un élément crucial, qui peut apporter un saut qualitatif à l'efficacité des requêtes, mais les index ne sont pas une conception d'index omnipotente et déraisonnable. Cela peut même ralentir l'efficacité des requêtes. .

Définition de l'index

Un index est une structure de données spécialement conçue pour aider SQL à obtenir des données efficacement. Un exemple courant est que l'index est similaire à la table des matières d'un livre, qui peut rapidement localiser et trouver des valeurs spécifiques, donc grandement. accélérer l'efficacité des requêtes de données. En fait, l'index est également une table. Cette table enregistre les champs de clé primaire et d'index et pointe vers les enregistrements de la table d'entité (semblable à un pointeur).

Avantages et inconvénients de l'index

Avantages

• L'index réduit considérablement la quantité de données que le serveur doit analyser
• L'index peut aider le serveur à éviter le tri et les tables temporaires
• L'index peut transformer les E/S aléatoires en E/S séquentielles
• Index est pour InnoDB (prenant en charge les verrous au niveau des lignes pour les index) est très important. InnoDB verrouille uniquement les tuples auxquels il faut accéder, et les index peuvent réduire le nombre de tuples auxquels InnoDB accède. Si la requête ne peut pas utiliser l'index, MySQL effectuera une analyse complète de la table et verrouillera chaque tuple, qu'il soit réellement nécessaire ou non.

Inconvénients

• Bien que l'index améliore considérablement la vitesse de requête, il réduit également la vitesse de mise à jour de 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. Par conséquent, il n’est généralement pas recommandé d’utiliser des index pour des champs mis à jour très fréquemment.
• L'indexation occupera de l'espace disque.
• Si une colonne de données contient beaucoup de contenu répété, l'effet d'indexation sera très faible. Cette propriété est appelée la sélectivité de l'index : le rapport entre les valeurs d'index uniques et le nombre total d'enregistrements dans les données. tableau. Plus la sélectivité de l'index est élevée, plus l'efficacité des requêtes est élevée. Par exemple, si vous créez un index sur le champ genre, il n'y a que deux possibilités pour un homme et une femme pour un million de données. La sélectivité de l'index est de une sur 500 000 et l'effet d'indexation est très faible
• Pour les très petites tables. , l'index a peu d'importance. Dans ce cas, une simple analyse complète de la table est plus efficace.

Ainsi, seules les colonnes de données les plus fréquemment interrogées et les plus fréquemment triées doivent être indexées. Le nombre total d'index dans la même table de données dans MySQL est limité à 16.

Type d'index

Divisé par la logique fonctionnelle

Divisé de la logique fonctionnelle, l'index est principalement divisé en index ordinaire, index unique, index de clé primaire et index de texte intégral

Index ordinaire

L'index le plus basique, il n'a aucune restriction. La seule tâche d'un index ordinaire (un index défini par le mot-clé KEY ou INDEX) est d'accélérer l'accès aux données. Par conséquent, les index ne doivent être créés que pour les colonnes de données qui apparaissent le plus fréquemment dans les conditions de requête (colonne WHERE = ...) ou les conditions de tri (colonne ORDER BY).

Il existe trois façons de créer un index normal.

# 创建索引CREATE INDEX idx_username ON user_tbl(username);# 对于字符串字段，可以手动指定长度，如 user_tbl(username(5))，表示只用前五个字符来做索引，可以进一步加快查询效率，索引长度要小于字段长度# 修改表结构ALTER TABLE user_tbl ADD INDEX idx_username (username)# 创建表的时候直接指定，如CREATE TABLE user_tbl( 
	ID INT NOT NULL, 
	username VARCHAR(16) NOT NULL, 
	INDEX idx_username (username) );

Supprimer l'index

DROP INDEX idx_username ON user_tbl;

Afficher l'index

SHOW INDEX FROM user_tbl;

Index unique

Il est similaire à l'index ordinaire précédent, sauf que l'index ordinaire permet à la colonne de données indexée de contenir des valeurs en double. La valeur de la colonne d'index unique 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.

La création d'un index unique est similaire à un index normal :

#创建索引
CREATE UNIQUE INDEX idx_username ON user_tbl(username);

# 修改表结构
ALTER TABLE user_tbl ADD UNIQUE idx_username (username)

# 创建表的时候直接指定
CREATE TABLE user_tbl( 
	ID INT NOT NULL, 
	username VARCHAR(16) NOT NULL, 
	UNIQUE idx_username (username) 
);

Index de clé primaire

C'est un index unique spécial qui n'autorise pas les valeurs nulles. Une table ne peut avoir qu’une seule clé primaire, qui est généralement créée en même temps que la table.

CREATE TABLE user_tbl( 
	ID INT NOT NULL, 
	username VARCHAR(16) NOT NULL, 
	PRIMARY KEY(ID) 
);

Un index de clé étrangère est similaire. Si une contrainte de clé étrangère est définie pour un champ de clé étrangère, MySQL définira un index interne pour l'aider à gérer et à utiliser les contraintes de clé étrangère de la manière la plus efficace.

Index de texte intégral

Dans l'article précédent sur la syntaxe de base de MySQL, nous avons dit que si vous utilisez LIKE + % au début, l'index sera invalide, puis lorsque nous aurons besoin de répondre aux exigences de recherche floues avant et après (telles que comme LIKE '%hello %'), vous devez utiliser l'index de texte intégral. Il convient de noter qu'Innodb ne prend en charge l'index de texte intégral qu'après la version 5.6.

Création et suppression d'un index de texte intégral :

# 创建的两种方法
CREATE FULLTEXT INDEX idx_name ON tbl_name(field_name);
ALTER TABLE tbl_name ADD FULLTEXT INDEX idx_name(field_name);

# 删除的两种方法
DROP INDEX idx_name ON tbl_name;
ALTER TABLE tbl_name DROP INDEX idx_name;

La syntaxe d'utilisation de l'index de texte intégral pour une correspondance floue complète est :

SELECT XXX FROM tbl_name WHERE match(field_name) against('xxx');
# 比如对 user_tbl 的 user_name 字段加了全文索引
# 查询结果等效于 SELECT user_name, user_id FROM user_tbl WHERE user_name LIKE '%hello%';
SELECT user_name, user_id FROM user_tbl WHERE match(user_name) against('hello');

Utilisez expliquer pour vérifier et vous constaterez que l'index de texte intégral est efficace.

Divisé par implémentation physique

Divisé par implémentation physique, il peut généralement être divisé en index clusterisés et index non clusterisés.

聚集索引（clustered index）

存储内容是按照聚集索引排序的，聚集索引的顺序和行记录的顺序一致,一张表只能有一个聚集索引。聚集索引的叶子节点直接储存聚集索引指向的内容，因此查询的时候只需要进行一次查找。

聚集索引在创建主键时自动生成，如果没有主键，则根据第一个不为空的唯一索引自动生成，如果还没有，则自动生成一个隐式的聚集索引。

需要注意的是，在进行查询操作的时候，聚集索引的效率更高，因为少了一次查找；但是进行修改操作的时候，效率比非聚集索引低，因为直接修改了数据内容，为了标准数据内容的顺序和聚集索引顺序一致，会对数据页重新排序。

非聚集索引（non-clustered index）

非聚集索引虽然索引项是顺序存储的，但是索引项对应的内容是随机存储的，系统会维护单独的索引表来存储索引。

非聚集索引的叶子节点存储的是数据的地址，查询非聚集索引的时候，系统会进行两次查找，先查找索引，再查找索引对应位置的数据。因此非聚集索引也叫二级索引或者辅助索引。

按字段个数划分

按字段个数可以把索引分为单一索引和联合索引。

单一索引

索引字段只有一列时为单一索引，上述所有索引都是单一索引。

联合索引

将多个字段组合在一起创建的索引叫联合索引。如下：

ALTER TABLE user_tbl ADD INDEX idx_name_city_age (username,city,age);

最左匹配原则

建立这样的联合索引，其实是相当于分别建立了下面三组联合索引：

usernname,city,age
usernname,city
usernname

为什么没有 city，age 这样的联合索引呢？这是因为MySQL联合索引的最左匹配原则，只会按照最左优先的顺序进行索引匹配，也就是说，(x,y,z) 和 (z,y,x) 是不同的索引，即使是使用联合索引中的字段查询，联合索引也有可能失效。

对于 (x,y,z)，只有在以下查询条件联合索引会生效：

WHERE x = 1WHERE x = 1 AND y = 1WHERE x = 1 AND y = 1 AND z = 1

对于其他情况，比如 WHERE y = 1 、WHERE y = 1 AND z = 1 等，就不会匹配联合索引，索引失效，注意对于 WHERE x = 1 AND z = 1，联合索引会对 x 生效，但是对 z 不生效。

可以扩展了解一下，理论上最左匹配原则中索引对 where 中子句的顺序也是敏感的，但是由于MySQL的查询优化器会自动调整 where 子句的条件顺序以使用适合的索引，所以实际上 where 子句顺序不影响索引的效果。

要注意的是，如果联合索引查询过程中有范围查询，就会停止匹配，比如下面的语句中， z 字段不能使用到索引：

WHERE x = 1 AND y > 2 AND z = 3

顺便提一下，可以用 explain 命令来查看在某个查询语句中索引是否生效，具体用法请参考官网文档。

如果分别在 x, y, z 上建立单列索引，让该表有3个单列索引，索引效率也会大不一样，在联合索引生效的情况下，单个索引的效率远远低于联合索引。这是由 MySQL 查询优化器的执行顺序决定的，在执行一条查询 sql 时，针对索引的选择大致有如下步骤：

1. MySQL 优化器根据搜索条件，找出所有可能使用的索引
2. 计算全表扫描的代价
3. 计算使用不同索引执行查询的代价
4. 对比各种执行方案的代价，找出成本最低的那一个

因此，虽然有多个单列索引，但 MySQL 只能用到其中的那个系统认为似乎是最有效率的，其他的就会失效。

按索引结构划分

不同的 mysql 数据引擎支持不同结构的索引，按结构划分，常用的索引为 B+树索引、Hash 索引、FULLTEXT索引 等，将在下一篇文章 MySQL 索引结构 中介绍。

使用总结

接下来我们来简单总结一下在什么场景下推荐使用索引。

推荐使用

• WHERE, GROUP BY, ORDER BY 子句中的字段

• 多个单列索引在多条件查询是只会有一个最优的索引生效，因此多条件查询中最好创建联合索引。

  联合索引的时候必须满足最左匹配原则，并且最好考虑到 sql 语句的执行顺序，比如 WHERE a = 1 GROUP BY b ORDER BY c, 那么联合索引应该设计为 (a,b,c)，因为在上一篇文章 MySQL 基础语法 中我们介绍过，mysql 查询语句的执行顺序 WHERE > GROUP BY > ORDER BY。

• 多张表 JOIN 的时候，对表连接字段创建索引。

• Lorsqu'il y a des champs dans SELECT qui ne sont pas dans l'index, la valeur de clé primaire qui remplit les conditions sera d'abord interrogée via l'index, puis tous les champs dans SELECT seront interrogés via la clé primaire Retour à la table, affectant l'efficacité des requêtes. Par conséquent, s'il y a très peu de contenu dans le SELECT, afin d'éviter de renvoyer la table, vous pouvez ajouter tous les champs du SELECT à l'index conjoint. C'est le concept d'un index large. Cependant, il convient de noter que s'il y a trop de champs d'index, le coût de stockage et de maintenance de l'index augmentera également. 回表查询出所有的 SELECT 中的字段，影响查询效率。因此如果 SELECT 中的内容很少，为了避免回表，可以把 SELECT 中的字段都加到联合索引中，这也就是宽索引的概念。但是需要注意，如果索引字段过多，存储和维护索引的成本也会增加。

不推荐使用或索引失效情况

• 数据量很小的表

• 有大量重复数据的字段

• 频繁更新的字段

• 如果对索引字段使用了函数或者表达式计算，索引失效

• innodb OR 条件没有对所有条件创建索引，索引失效

• 大于小于条件 <code>>，索引是否生效取决于命中的数量比例，如果命中数量很多，索引生效，命中数量很小，索引失效

• 不等于条件 != ，索引失效

• LIKE 值以 %

Non recommandé ou index invalide

Tableaux avec un petit volume de données

🎜Champs avec une grande quantité de données en double🎜🎜🎜🎜Champs fréquemment mis à jour🎜🎜🎜🎜Si des fonctions ou des calculs d'expression sont utilisés pour les champs d'index, l'index échouera🎜🎜🎜🎜innodb OR condition Index ne sont pas créés pour toutes les conditions. L'index n'est pas valide 🎜🎜🎜🎜 est supérieur ou inférieur à la condition <code>>. le nombre de hits. S'il y a beaucoup de hits, L'index prend effet, le nombre de hits est très petit, l'index échoue 🎜🎜🎜🎜 n'est pas égal à la condition != &lt ;>, l'index échoue 🎜🎜🎜🎜La valeur LIKE commence par Commençant par %, l'index n'est pas valide🎜🎜🎜🎜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!