Grosse révélation ! Index de la base de données MySQL

1. Présentation

L'index est une structure de données utilisée par les moteurs de stockage pour trouver rapidement des enregistrements. Une utilisation appropriée des index de base de données peut considérablement améliorer les performances d'accès du système. Ensuite, nous présentons principalement les types d'index dans la base de données

MySql et comment créer des techniques d'index plus raisonnables et efficaces.

Remarque : L'objectif principal ici est la structure de données d'indexation B+Tree du moteur de stockage InnoDB

2 Les avantages de l'indexation

sont excellents Réduit la quantité de données que le serveur doit analyser, augmentant ainsi la vitesse de récupération des données

Aide le serveur à éviter le tri et les tables temporaires

Peut transformer des I/ O dans les E/S séquentielles

3. Création de l'index

3.1. 3.2. Index unique

ALTER TABLE 'table_name' ADD PRIMARY KEY 'index_name' ('column');

3.3, index ordinaire

ALTER TABLE 'table_name' ADD UNIQUE 'index_name' ('column');

3.4, index texte intégral

ALTER TABLE 'table_name' ADD INDEX 'index_name' ('column');

3.5, index combiné

ALTER TABLE 'table_name' ADD FULLTEXT 'index_name' ('column');

4. Règles d'index B+Tree

Créer une table d'utilisateur de test

ALTER TABLE 'table_name' ADD INDEX 'index_name' ('column1', 'column2', ...);

Créer un index de combinaison : ALTER TABLE user_test ADD INDEX idx_user(user_name, city, age);

4.1. Requête avec un index valide

4.1.1. Correspondance de valeur complète

La correspondance de valeur complète fait référence à la correspondance avec toutes les colonnes de l'index. Par exemple : en prenant l'index créé ci-dessus comme exemple, vous pouvez interroger (nom_utilisateur,. ville, âge) en même temps après la condition où ) sont les données de la condition

.

Remarque : cela n'a rien à voir avec l'ordre des conditions de requête après où. C'est un endroit où de nombreux étudiants se méprennent facilement

DROP TABLE IF EXISTS user_test;CREATE TABLE user_test(    id int AUTO_INCREMENT PRIMARY KEY,
    user_name varchar(30) NOT NULL,
    sex bit(1) NOT NULL DEFAULT b'1',
    city varchar(50) NOT NULL,
    age int NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;

4.1.2. Faire correspondre le préfixe le plus à gauche

Faire correspondre le préfixe le plus à gauche signifie faire correspondre en premier la colonne d'index la plus à gauche. Par exemple : l'index créé ci-dessus peut être utilisé pour les conditions de requête : (nom_utilisateur), (nom_utilisateur, ville. ), (nom_utilisateur, ville, âge)

Remarque : L'ordre qui satisfait aux conditions de requête du préfixe le plus à gauche n'a rien à voir avec l'ordre des colonnes d'index, telles que : (ville, nom_utilisateur), (âge, ville, nom d'utilisateur)

4.1.3. Le préfixe de la colonne correspondante

fait référence au début de la valeur de la colonne correspondante. , tels que : interroger tous les utilisateurs dont le nom d'utilisateur commence par feinik

SELECT * FROM user_test WHERE user_name = 'feinik' AND age = 26 AND city = '广州';

4.1.4, valeur de plage correspondante

Par exemple : interrogez tous les utilisateurs dont le nom d'utilisateur commence par feinik , la première colonne de l'index est utilisée ici

SELECT * FROM user_test WHERE user_name LIKE 'feinik%';

4.2 Limitations de l'index

1. La colonne d'index la plus à gauche de la colonne d'index n'est pas incluse dans la condition de requête Where. La requête d'index ne peut pas être utilisée, telle que :

SELECT * FROM user_test WHERE user_name LIKE 'feinik%';

ou

SELECT * FROM user_test WHERE city = '广州';

ou

SELECT * FROM user_test WHERE age= 26;

<🎜. > 2. Même si la condition de requête où est la colonne d'index la plus à gauche, elle ne peut pas utiliser l'index pour interroger les utilisateurs dont les noms d'utilisateur se terminent par feinik

SELECT * FROM user_test WHERE city = '广州' AND age = '26';

3. S'il existe une requête de plage d'un certaine colonne dans la condition de requête Where, toutes les colonnes de droite ne peuvent pas être interrogées à l'aide de l'optimisation de l'index, comme :

SELECT * FROM user_test WHERE user_name like '%feinik';

5. Stratégie d'indexation efficace

<🎜. >5.1. Les colonnes d'index ne peuvent pas faire partie d'une expression ou d'un paramètre d'une fonction, sinon la requête d'index ne peut pas être utilisée.

SELECT * FROM user_test WHERE user_name = 'feinik' AND city LIKE '广州%' AND age = 26;

5.2, Index de préfixe

Parfois il est nécessaire d'indexer des colonnes de caractères très longues, ce qui va augmenter l'espace de stockage de l'index et réduire l'index Pour plus d'efficacité, une stratégie consiste à utiliser un index de hachage, et une autre consiste à utiliser un index de préfixe pour
. L'index de préfixe sélectionne les n premiers caractères de la colonne de caractères comme index, ce qui peut grandement. économiser de l'espace d'indexation, améliorant ainsi l'efficacité de l'indexation.

5.2.1. Sélectivité de l'index de préfixe

L'index de préfixe doit choisir un préfixe suffisamment long pour assurer une sélectivité élevée, mais pas trop long en même temps. , nous pouvons calculer la valeur de longueur de sélection de l'index de préfixe approprié de la manière suivante :

(1)

SELECT * FROM user_test WHERE user_name = concat(user_name, 'fei');

Remarque : calculé par la méthode ci-dessus Le rapport de sélectivité de l'indice de préfixe. Plus le rapport est élevé, plus l'indice est efficace.

(2)

SELECT COUNT(DISTINCT index_column)/COUNT(*) FROM table_name; -- index_column代表要添加前缀索引的列

Remarque : trouvez-le étape par étape à travers les phrases ci-dessus Le rapport de sélectivité le plus proche de l'index du préfixe en (1), puis la longueur de troncature des caractères correspondante peut être utilisée pour créer l'index du préfixe

5.2.2 Création du préfixe. index

SELECTCOUNT(DISTINCT LEFT(index_column,1))/COUNT(*),COUNT(DISTINCT LEFT(index_column,2))/COUNT(*),COUNT(DISTINCT
 LEFT(index_column,3))/COUNT(*)
 ...FROM table_name;

5.2.3 Points à noter lors de l'utilisation de l'index de préfixe

L'index de préfixe est un moyen efficace de rendre l'index plus petit et plus rapide. , mais MySql ne peut pas utiliser d'index de préfixe pour ORDER BY et GROUP BY et utiliser des index de préfixe pour les analyses de couverture .

5.3、选择合适的索引列顺序

在组合索引的创建中索引列的顺序非常重要，正确的索引顺序依赖于使用该索引的查询方式，对于组合索引的索引顺序可以通过经验

法则来帮助我们完成：将选择性最高的列放到索引最前列，该法则与前缀索引的选择性方法一致，但并不是说所有的组合索引的顺序

都使用该法则就能确定，还需要根据具体的查询场景来确定具体的索引顺序。

5.4 聚集索引与非聚集索引

1、聚集索引

聚集索引决定数据在物理磁盘上的物理排序，一个表只能有一个聚集索引，如果定义了主键，那么InnoDB会通过主键来聚集数据，如

果没有定义主键，InnoDB会选择一个唯一的非空索引代替，如果没有唯一的非空索引，InnoDB会隐式定义一个主键来作为聚集索

引。

聚集索引可以很大程度的提高访问速度，因为聚集索引将索引和行数据保存在了同一个B-Tree中，所以找到了索引也就相应的找到了

对应的行数据，但在使用聚集索引的时候需注意避免随机的聚集索引（一般指主键值不连续，且分布范围不均匀），如使用UUID来作

为聚集索引性能会很差，因为UUID值的不连续会导致增加很多的索引碎片和随机I/O，最终导致查询的性能急剧下降。

2、非聚集索引

与聚集索引不同的是非聚集索引并不决定数据在磁盘上的物理排序，且在B-Tree中包含索引但不包含行数据，行数据只是通过保存在

B-Tree中的索引对应的指针来指向行数据，如：上面在（user_name，city, age）上建立的索引就是非聚集索引。

5.5、覆盖索引

如果一个索引（如：组合索引）中包含所有要查询的字段的值，那么就称之为覆盖索引，如：

SELECT user_name, city, age FROM user_test WHERE user_name = 'feinik' AND age > 25;

因为要查询的字段（user_name, city, age）都包含在组合索引的索引列中，所以就使用了覆盖索引查询，查看是否使用了覆盖索引可

以通过执行计划中的Extra中的值为Using index则证明使用了覆盖索引，覆盖索引可以极大的提高访问性能。

5.6、如何使用索引来排序

在排序操作中如果能使用到索引来排序，那么可以极大的提高排序的速度，要使用索引来排序需要满足以下两点即可。

1、ORDER BY子句后的列顺序要与组合索引的列顺序一致，且所有排序列的排序方向（正序/倒序）需一致

2、所查询的字段值需要包含在索引列中，及满足覆盖索引

通过例子来具体分析

在user_test表上创建一个组合索引

ALTER TABLE user_test ADD INDEX index_user(user_name , city , age);

可以使用到索引排序的案例

1、SELECT user_name, city, age FROM user_test ORDER BY user_name;

2、SELECT user_name, city, age FROM user_test ORDER BY user_name, city;

3、SELECT user_name, city, age FROM user_test ORDER BY user_name DESC, city DESC;

4、SELECT user_name, city, age FROM user_test WHERE user_name = 'feinik' ORDER BY city;

注：第4点比较特殊一点，如果where查询条件为索引列的第一列，且为常量条件，那么也可以使用到索引

无法使用索引排序的案例

1、sex不在索引列中

SELECT user_name, city, age FROM user_test ORDER BY user_name, sex;

2、排序列的方向不一致

SELECT user_name, city, age FROM user_test ORDER BY user_name ASC, city DESC;

3、所要查询的字段列sex没有包含在索引列中

SELECT user_name, city, age, sex FROM user_test ORDER BY user_name;

4、where查询条件后的user_name为范围查询，所以无法使用到索引的其他列

SELECT user_name, city, age FROM user_test WHERE user_name LIKE 'feinik%' ORDER BY city;

5、多表连接查询时，只有当ORDER BY后的排序字段都是第一个表中的索引列（需要满足以上索引排序的两个规则）时，方可使用索

引排序。如：再创建一个用户的扩展表user_test_ext，并建立uid的索引。

DROP TABLE IF EXISTS user_test_ext;CREATE TABLE user_test_ext( id int AUTO_INCREMENT PRIMARY KEY,

 uid int NOT NULL,
 u_password VARCHAR(64) NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;ALTER TABLE user_test_ext ADD INDEX 
 index_user_ext(uid);

走索引排序

SELECT user_name, city, age FROM user_test u LEFT JOIN user_test_ext ue ON u.id = ue.uid ORDER BY u.user_name;

不走索引排序

SELECT user_name, city, age FROM user_test u LEFT JOIN user_test_ext ue ON u.id = ue.uid ORDER BY ue.uid;

6、总结

本文主要讲了B+Tree树结构的索引规则，不同索引的创建，以及如何正确的创建出高效的索引技巧来尽可能的提高查询速度，当然了

关于索引的使用技巧不单单只有这些，关于索引的更多技巧还需平时不断的积累相关经验。

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!