Comment créer des index hautes performances pour MySQL

1 Bases de l'index

1.1 Fonction d'index

Dans MySQL, lors de la recherche de données, recherchez d'abord la valeur correspondante dans l'index, puis recherchez la ligne de données correspondante en fonction de l'enregistrement d'index correspondant si vous le souhaitez. exécutez l'instruction de requête suivante :

SELECT	* FROM  USER  WHERE uid = 5;

S'il existe un index construit sur l'uid, MySQL utilisera l'index pour trouver d'abord la ligne avec l'uid 5, ce qui signifie que MySQL recherchera d'abord par valeur sur l'index, puis renverra toutes les données lignes contenant cette valeur.

1.2 Structures de données communes pour les index MySQL

Les index MySQL sont implémentés au niveau du moteur de stockage, pas sur le serveur. Par conséquent, il n’existe pas de norme d’indexation unifiée : les index des différents moteurs de stockage fonctionnent différemment.

1.2.1 B-Tree

La plupart des moteurs MySQL prennent en charge cet index B-Tree Même si plusieurs moteurs de stockage prennent en charge le même type d'index, leur implémentation sous-jacente peut être différente. Par exemple, InnoDB utilise B+Tree.

Les moteurs de stockage implémentent B-Tree de différentes manières, avec des performances et des avantages différents. Par exemple, MyISAM utilise la technologie de compression de préfixe pour réduire la taille des index, tandis qu'InnoDB stocke les données selon le format de données d'origine. Les index MyISAM font référence aux lignes indexées en fonction de l'emplacement physique des données, tandis qu'InnoDB applique les lignes indexées en fonction du composant. .

Toutes les valeurs de B-Tree sont stockées séquentiellement et la distance entre chaque page feuille et la racine est la même. La figure ci-dessous reflète approximativement le fonctionnement de l'index InnoDB. La structure utilisée par MyISAM est différente. Mais la mise en œuvre de base est similaire.

Explication du diagramme d'exemple :

Chaque nœud occupe un bloc de disque. Il y a deux clés de tri ascendant sur un nœud et trois pointeurs vers le nœud racine du sous-arbre. Les pointeurs stockent le bloc de disque où se trouve le nœud enfant. se trouve l'adresse. Les trois champs range divisés par les deux mots-clés correspondent aux champs range des données du sous-arbre pointé par les trois pointeurs. En prenant le nœud racine comme exemple, les mots-clés sont 16 et 34, la plage de données du sous-arbre pointé par le pointeur P1 est inférieure à 16, la plage de données du sous-arbre pointé par le pointeur P2 est de 16 à 34 et les données La plage du sous-arbre pointé par le pointeur P3 est supérieure à 34. Processus de recherche par mot clé :

• Trouvez le bloc de disque 1 en fonction du nœud racine et lisez-le en mémoire. [Opération E/S disque 1ère fois]

• Comparez le mot-clé 28 Dans l'intervalle (16,34), recherchez le pointeur P2 du bloc disque 1.

• Trouvez le bloc de disque 3 en fonction du pointeur P2 et lisez-le en mémoire. [Opération d'E/S disque 2ème fois]

• Comparez le mot-clé 28 Dans l'intervalle (25,31), recherchez le pointeur P2 du bloc disque 3.

• Trouvez le bloc de disque 8 basé sur le pointeur P2 et lisez-le en mémoire. [Opération d'E/S disque 3ème fois]

• Mot-clé 28 trouvé dans la liste de mots-clés du bloc de disque 8.

Inconvénients :

• Chaque nœud possède une clé et contient également des données, et l'espace de stockage de chaque page est limité si les données sont relativement volumineuses, le nombre de clés stockées dans chaque nœud deviendra plus petit. . ;

• Lorsque la quantité de données stockées est importante, cela entraînera une grande profondeur, augmentera le nombre d'E/S disque pendant la requête et affectera ainsi les performances de la requête.

1.2.2 Index B+Tree

L'arbre B+ est une variante du B-tree. Différence avec l'arbre B : l'arbre B+ stocke uniquement les données dans les nœuds feuilles, et les nœuds non-feuilles ne stockent que les valeurs clés et les pointeurs.

Il y a deux pointeurs sur l'arbre B+, l'un pointe vers le nœud feuille racine et l'autre pointe vers le nœud feuille avec le plus petit mot-clé, et il y a une structure en anneau entre tous les nœuds feuille (c'est-à-dire les nœuds de données), donc B+ peut L'arborescence effectue deux opérations de recherche : l'une est une recherche par plage de composants et l'autre est une recherche aléatoire à partir du nœud racine.

L'arbre B* est similaire au nombre B+. La différence est que le nombre B* a également une structure en anneau de chaîne entre les nœuds non-feuilles.

1.2.3 Index de hachage

L'index de hachage est implémenté sur la base d'une table de hachage Seules les requêtes qui correspondent avec précision à toutes les colonnes de l'index sont valides. Pour chaque ligne de données, le moteur de stockage calculera un code de hachage pour toutes les colonnes d'index. Le code de hachage est une valeur plus petite et les codes de hachage calculés pour les lignes avec des valeurs de clé différentes sont également différents. Un index de hachage stocke tous les codes de hachage dans l'index et un pointeur vers chaque ligne de données de la table de hachage.

Dans MySQL, seul le type d'index par défaut de Memory est l'index de hachage, et la mémoire prend également en charge les index B-Tree. Dans le même temps, le moteur de mémoire prend en charge les index de hachage non uniques. Si les valeurs de hachage de plusieurs colonnes sont identiques, l'index stockera plusieurs pointeurs dans la même entrée de hachage dans une liste chaînée. Similaire à HashMap.

Avantages :
L'index lui-même n'a besoin que de stocker la valeur de hachage correspondante, la structure de l'index est donc très compacte et la vitesse de recherche de hachage est très rapide.
Inconvénients :

• Si vous utilisez le stockage de hachage, vous devez ajouter tous les fichiers de données à la mémoire, ce qui consomme plus d'espace mémoire ;

• Les données d'index de hachage ne sont pas stockées dans l'ordre, elles ne peuvent donc pas être utilisées ; pour le tri ;

• Si toutes les requêtes sont des requêtes équivalentes, alors le hachage est en effet très rapide, mais dans une entreprise ou un environnement de travail réel, il y a plus de données à rechercher dans une plage plutôt que dans des requêtes équivalentes, donc le hachage n'est pas approprié ;

• S'il y a de nombreux conflits de hachage, le coût des opérations de maintenance de l'index sera également très élevé. C'est aussi le problème des conflits de hachage résolus par l'ajout d'arbres rouge-noir dans la phase ultérieure de HashMap

2 Index haute performance ; stratégie

2.1 Index clusterisé et index non cluster

L'index clusterisé

n'est pas un type d'index distinct, mais une méthode de stockage de données dans le moteur de stockage InnoDB, l'index clusterisé enregistre en fait les valeurs clés et les lignes de données dans le. même structure. Lorsqu'une table possède un index clusterisé, ses lignes de données sont en fait stockées dans les pages feuilles de l'index. Étant donné que les lignes de données ne peuvent pas être stockées simultanément à différents endroits, il ne peut y avoir qu'un seul index clusterisé dans une table (la couverture d'index peut simuler la situation de plusieurs index clusterisés).

Avantages de l'index clusterisé :

peut enregistrer les données associées ensemble ; l'accès aux données est plus rapide car l'index et les données sont enregistrés dans la même arborescence ; les requêtes utilisant l'analyse d'index de couverture peuvent utiliser directement la valeur clé du nœud de la page principale ;

Inconvénients : 

Les données clusterisées maximisent les performances des applications gourmandes en E/S. Si les données sont toutes en mémoire, alors l'index clusterisé n'a aucun avantage ; la vitesse d'insertion dépend fortement de l'ordre d'insertion, selon la clé primaire. L'insertion séquentielle est. le moyen le plus rapide ; la mise à jour des colonnes d'index clusterisé est coûteuse car elle oblige chaque ligne mise à jour à se déplacer vers un nouvel emplacement ; les tables basées sur des index clusterisés peuvent causer des problèmes lorsque de nouvelles lignes sont insérées ou que la clé primaire est mise à jour. vous pouvez être confronté au problème des fractionnements de pages ; les index clusterisés peuvent ralentir les analyses de tables complètes, en particulier lorsque les lignes sont clairsemées, ou que le stockage des données est discontinu en raison des fractionnements de pages

Index non clusterisé

Les fichiers de données et les fichiers d'index sont stocké séparément

2.2 Index de préfixe

Parfois, il est nécessaire d'indexer des chaînes très longues, ce qui rendra l'index volumineux et lent. Habituellement, vous pouvez utiliser une partie de la chaîne au début d'une colonne, ce qui économise considérablement de l'espace d'indexation. améliorant ainsi l'efficacité de l'index, mais cela réduira la sélectivité de l'index. La sélectivité de l'index fait référence : au rapport des valeurs d'index uniques (également appelées cardinalité) au nombre total d'enregistrements de la table de données, allant de 1/# entre T et 1. Plus la sélectivité de l'index est élevée, plus l'efficacité des requêtes est élevée, car un index plus sélectif permet à MySQL de filtrer davantage de lignes lors de la recherche.

Généralement, la sélectivité d'un certain préfixe de colonne est suffisamment élevée pour répondre aux performances de la requête. Cependant, pour les colonnes de types BLOB, TEXT et VARCHAR, des index de préfixe doivent être utilisés car MySQL ne permet pas d'indexer toute la longueur de. ces colonnes. , l'astuce de cette méthode est de choisir un préfixe suffisamment long pour garantir une sélectivité élevée, mais pas trop long.

Exemple

Structure de table et téléchargement de données depuis le site officiel de MySQL ou GitHub. Colonnes du tableau de la ville

country_id

Country ID

last_update :Heure de création ou de dernière mise à jour

--计算完整列的选择性
select count(distinct left(city,3))/count(*) as sel3,
    count(distinct left(city,4))/count(*) as sel4,
    count(distinct left(city,5))/count(*) as sel5,
    count(distinct left(city,6))/count(*) as sel6,
    count(distinct left(city,7))/count(*) as sel7,
    count(distinct left(city,8))/count(*) as sel8 
from citydemo;

可以看到当前缀长度到达7之后，再增加前缀长度，选择性提升的幅度已经很小了。由此最佳创建前缀索引长度为7。

2.3 回表

要理解回表需要先了解聚族索引和普通索引。聚族索引即建表时设置的主键索引，如果没有设置MySQL自动将第一个非空唯一值作为索引，如果还是没有InnoDB会创建一个隐藏的row-id作为索引（oracle数据库row-id显式展示，可以用于分页）；普通索引就是给普通列创建的索引。普通列索引在叶子节点中存储的并不是整行数据而是主键，当按普通索引查找时会先在B+树中查找该列的主键，然后根据主键所在的B+树中查找改行数据，这就是回表。

2.4 覆盖索引

覆盖索引在InnoDB中特别有用。MySQL中可以使用索引直接获取列的数据，如果索引的叶子节点中已经包含要查询的数据，那么就没必要再回表查询了，如果一个索引包含（覆盖）所有需要查询的字段的值，那么该索引就是覆盖索引。简单的说：不回表直接通过一次索引查找到列的数据就叫覆盖索引。

表信息

CREATE TABLE `t_user` (
  `uid` int(11) NOT NULL AUTO_INCREMENT,
  `uname` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  PRIMARY KEY (`uid`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

举例

--将uid设置成主键索引后通过下面的SQL查询 在explain的Extra列可以看到“Using index”
explain select uid from t_user where uid = 1;

覆盖索引在组合索引中用的比较多，举例

explain select age,uname from t_user where age = 10 ;

当不建立组合索引时，会进行回表查询

设置组合索引后再次查询

create index index_user on t_user(age,uname);

2.5 索引匹配方式

2.5.1 最左匹配

在使用组合索引中，比如设置(age,name)为组合索引，单独使用组合索引中最左列是可以匹配索引的，如果不使用最左列则不走索引。例如下面SQL

--走索引
explain select * from t_user where age=10 and uname='zhang';

下面的SQL不走索引

explain select * from t_user where  uname='zhang';

2.5.2 匹配列前缀

可以匹配某一列的值的开头部分，比如like 'abc%'。

2.5.3 匹配范围值

可以查找某一个范围的数据。

explain select * from t_user where age>18;

2.5.4 精确匹配某一列并范围匹配另外一列

可以查询第一列的全部和第二列的部分

explain select * from t_user where age=18 and uname like 'zhang%';

2.5.5 只访问索引的查询

查询的时候只需要访问索引，不需要访问数据行，本质上就是覆盖索引。

explain select age,uname,update_time from t_user 
            where age=18 and uname= 'zhang' and update_time='123';

3 索引优化最佳实践

1. 当使用索引列进行查询的时候尽量不要使用表达式，把计算放到业务层而不是数据库层。

--推荐
select uid,age,uname from t_user where uid=1;

--不推荐
select uid,age,uname from t_user where uid+9=10;

2. 尽量使用主键查询，而不是其他索引，因为主键查询不会触发回表查询

3. 使用前缀索引参考2.2 前缀索引
4. 使用索引扫描排序mysql有两种方式可以生成有序的结果：通过排序操作或者按索引顺序扫描，如果explain出来的type列的值为index,则说明mysql使用了索引扫描来做排序。
扫描索引本身是很快的，因为只需要从一条索引记录移动到紧接着的下一条记录。但如果索引不能覆盖查询所需的全部列，那么就不得不每扫描一条索引记录就得回表查询一次对应的行，这基本都是随机IO，因此按索引顺序读取数据的速度通常要比顺序地全表扫描慢。
mysql可以使用同一个索引即满足排序，又用于查找行，如果可能的话，设计索引时应该尽可能地同时满足这两种任务。
只有当索引的列顺序和order by子句的顺序完全一致，并且所有列的排序方式都一样时，mysql才能够使用索引来对结果进行排序，如果查询需要关联多张表，则只有当orderby子句引用的字段全部为第一张表时，才能使用索引做排序。order by子句和查找型查询的限制是一样的，需要满足索引的最左前缀的要求，否则，mysql都需要执行顺序操作，而无法利用索引排序。
举例表结构及数据MySQL官网或GItHub下载。

CREATE TABLE `rental` (
  `rental_id` int(11) NOT NULL AUTO_INCREMENT,
  `rental_date` datetime NOT NULL,
  `inventory_id` mediumint(8) unsigned NOT NULL,
  `customer_id` smallint(5) unsigned NOT NULL,
  `return_date` datetime DEFAULT NULL,
  `staff_id` tinyint(3) unsigned NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`rental_id`),
  UNIQUE KEY `rental_date` (`rental_date`,`inventory_id`,`customer_id`),
  KEY `idx_fk_inventory_id` (`inventory_id`),
  KEY `idx_fk_customer_id` (`customer_id`),
  KEY `idx_fk_staff_id` (`staff_id`),
  CONSTRAINT `fk_rental_customer` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`customer_id`) ON UPDATE CASCADE,
  CONSTRAINT `fk_rental_inventory` FOREIGN KEY (`inventory_id`) REFERENCES `inventory` (`inventory_id`) ON UPDATE CASCADE,
  CONSTRAINT `fk_rental_staff` FOREIGN KEY (`staff_id`) REFERENCES `staff` (`staff_id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=16050 DEFAULT CHARSET=utf8mb4;

rental表在rental_date,inventory_id,customer_id上有rental_date的索引。使用rental_date索引为下面的查询做排序

--该查询为索引的第一列提供了常量条件，而使用第二列进行排序，将两个列组合在一起，就形成了索引的最左前缀
explain select rental_id,staff_id from rental 
where rental_date='2005-05-25' order by inventory_id desc

--下面的查询不会利用索引
explain select rental_id,staff_id from rental 
where rental_date>'2005-05-25' order by rental_date,inventory_id

5. union all,in,or都能够使用索引，但是推荐使用in

explain select * from actor where actor_id = 1 union all select * from actor where actor_id = 2;
explain select * from actor where actor_id in (1,2);
explain select * from actor where actor_id = 1 or actor_id =2;

6. 范围列可以用到索引范围条件是：<、<=、>、>=、between。范围列可以用到索引，但是范围列后面的列无法用到索引，索引最多用于一个范围列。

7. 更新十分频繁，数据区分度不高的字段上不宜建立索引

• 更新会变更B+树，更新频繁的字段建议索引会大大降低数据库性能；

• 类似于性别这类区分不大的属性，建立索引是没有意义的，不能有效的过滤数据；

• 一般区分度在80%以上的时候就可以建立索引，区分度可以使用 count(distinct(列名))/count(*) 来计算；

8. 创建索引的列，不允许为null，可能会得到不符合预期的结果

9.当需要进行表连接的时候，最好不要超过三张表，如果需要join的字段，数据类型必须一致

10. 能使用limit的时候尽量使用limit

11. 单表索引建议控制在5个以内

12. 单索引字段数不允许超过5个（组合索引）

13. 创建索引的时候应该避免以下错误概念

• 索引越多越好

• 过早优化，在不了解系统的情况下进行优化

4 索引监控

show status like 'Handler_read%';

参数	说明
Handler_read_first	读取索引第一个条目的次数
Handler_read_key	通过index获取数据的次数
Handler_read_last	读取索引最后一个条目的次数
Handler_read_next	通过索引读取下一条数据的次数
Handler_read_prev	通过索引读取上一条数据的次数
Handler_read_rnd	从固定位置读取数据的次数
Handler_read_rnd_next	从数据节点读取下一条数据的次数

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!