Que dois-je faire si l'index Mysql échoue ? Brève analyse des raisons de l'échec

Cet article enregistrera pour tout le monde l'échec de l'index Mysql et analysera les raisons de l'échec de l'index Mysql. J'espère qu'il sera utile à tout le monde !

Cet article contient le processus d'exécution de requête de condition Where de Mysql, la requête de plage pour empêcher l'index conjoint de correspondre, l'analyse des opérations de retour de table, les scénarios courants d'échec d'index, l'analyse supplémentaire et d'autres connaissances. [Recommandations associées :

Tutoriel vidéo mysql]

Contexte

Une requête complète est apparue dans une table de données avec 60 millions de données En répliquant l'instruction SQL, il a été constaté que la requête ne passait pas par l'index mais par l'ensemble. requête de table. Découvrez la raison de l’échec de l’index. La table

# sql语句
EXPLAIN SELECT count(*) FROM order_recipient_extend_tab WHERE start_date>&#39;1628442000&#39; and start_date<&#39;1631120399&#39; and station_id=&#39;1809&#39; and status=&#39;2&#39;;

order_recipient_extend_tab contient 60 millions de données. Les champs de requête pour les requêtes lentes incluent start_date, station_id et status. Selon l'intention initiale de la conception de l'index, l'index qui échoue réellement est :

index conjoint. champ 1Champ 2Champ 3start_datestation_iddriver_id

Processus d'exécution des requêtes de condition Where

Comprenez comment Mysql exécute la requête de condition Where et vous pourrez obtenir rapidement et clairement un aperçu des raisons de l'échec de l'index. L'index avec un degré de correspondance élevé dans cette requête lente est idx_date_station_driver Analysez le processus d'exécution de la requête de condition Where dans cette requête lente.

Les règles d'extraction de conditions de MySQL peuvent être divisées en trois catégories principales : Clé d'index (Première clé et dernière clé), Filtre d'index, Filtre de table.

Index Key

Index Key est utilisée pour déterminer la portée de cette requête SQL sur l'arborescence d'index. Une plage comprend le début et la fin, Index First Key est utilisé pour localiser la plage de début de la requête d'index et Index Last Key est utilisé pour localiser la plage de fin de la requête d'index.

• Index First Key

  Règles d'extraction : à partir du premier champ de l'index, vérifiez si le champ existe dans la condition Where. S'il existe et que la condition est =, >=, ajoutez la condition correspondante à l'index. First Key, continuez à lire le champ suivant de l'index ; s'il existe et que la condition est >, ajoutez la condition correspondante à Index First Key, puis terminez l'extraction de Index First Key si elle n'existe pas, terminez également ; l’extraction de l’extrait Index First Key.

• Index Last Key

  est tout le contraire des règles d'extraction de l'Index First Key : à partir du premier champ de l'index, vérifiez s'il existe dans la condition Where. S'il existe et que la condition est =,

Selon les règles d'extraction de la clé d'index, la dernière clé de l'index extraite dans cette requête lente est : start_date>'1628442000', et la dernière clé de l'index est : start_date

Index First Key est uniquement utilisé pour localiser la plage de départ de l'index. Utilisez la condition Index First Key, en commençant par le nœud racine de l'arbre d'index B+, et utilisez la méthode de recherche binaire pour indexer rapidement jusqu'à la position correcte du nœud feuille. . Au cours du processus de requête Where, la première clé d'indexation n'est évaluée qu'une seule fois.

Index Last Key est utilisé pour localiser la plage de fin de l'index. Par conséquent, pour chaque enregistrement d'index lu après la plage de départ, il est nécessaire de déterminer s'il a dépassé la plage de l'Index Last Key. la requête se termine.

Filtre d'index

Dans la plage d'index déterminée par Index Key, tous les enregistrements d'index ne répondent pas aux conditions de requête. Par exemple, dans les plages Index Last Key et Index Last Key, tous les enregistrements d’index ne satisfont pas station_id = '1809'. À ce stade, vous devez utiliser le filtre d'index.

Index Filter, également connu sous le nom de index pushdown, est utilisé pour filtrer les enregistrements dans la plage de requête d'index qui ne répondent pas aux conditions de requête . Pour chaque enregistrement de la plage d'index, il doit être comparé au filtre d'index. S'il ne répond pas au filtre d'index, il sera directement supprimé et continuera à lire l'enregistrement suivant dans l'index.

Règles d'extraction du filtre d'index : commencez par le premier champ de l'index, vérifiez s'il existe dans la condition où, s'il existe et que la condition est uniquement =, sautez le premier champ et continuez à vérifier le champ suivant de l'index, les colonnes d'index suivantes adoptent les mêmes règles d'extraction (explication : les champs avec la condition = ont été filtrés dans la clé d'index) ; s'ils existent et que les conditions sont >=, >,

Selon les règles d'extraction d'Index Filter, le filtre d'index extrait dans cette requête lente est : station_id='1809'. Dans la plage de requête d'index déterminée par la clé d'index, station_id='1809' doit être comparé lors du parcours des enregistrements d'index. Si cette condition n'est pas remplie, il sera perdu directement et l'enregistrement suivant de l'index continuera à être lu. .

Filtre de table

Le filtre de table est utilisé pour filtrer les données qui ne peuvent pas être filtrées par l'index. Après avoir interrogé toute la ligne d'enregistrements via la clé primaire retour à la table dans l'index secondaire, il est jugé si l'enregistrement répond aux conditions du filtre de table. S'il ne remplit pas les conditions, il sera perdu ainsi que l'enregistrement suivant. continuera à être jugé.

Les règles d'extraction sont très simples : toutes les conditions de requête qui n'appartiennent pas aux champs d'index sont classées dans Table Filter. Selon les règles d'extraction de Table Filter, le Table Filter dans cette requête est : status='2'.

Résumé et supplément

La clé d'index est utilisée pour déterminer la portée de l'analyse d'index ; le filtre d'index est utilisé pour filtrer dans l'index. Le filtre de table doit être filtré sur le serveur Mysql après avoir renvoyé la table.

La clé d'index et le filtre d'index se produisent au niveau de la couche de stockage InnoDB, et le filtre de table se produit au niveau de la couche du serveur Mysql.

Avant MySQL5.6, il n'y avait aucune distinction entre le filtre d'index et le filtre de table. Tous les enregistrements d'index compris dans la plage Index First Key et Index Last Key étaient renvoyés à la table pour lire l'enregistrement complet, puis renvoyés au serveur MySQL. couche pour le filtrage.

Dans MySQL 5.6 et versions ultérieures, le filtre d'index est séparé du filtre de table. Le filtre d'index est transféré vers la couche du moteur de stockage d'InnoDB pour le filtrage, ce qui réduit la surcharge d'interaction liée au renvoi des tables et des enregistrements à la couche du serveur MySQL, et améliore l'efficacité d'exécution de SQL.

Analyser la cause de l'échec de l'index

Le premier est le nombre ( À ce stade, le caractère générique * ne développera pas toutes les colonnes après l'optimisation. En fait, toutes les colonnes seront ignorées et le nombre de lignes le sera). compté directement. Donc, si vous souhaitez simplement collecter le nombre de lignes, il est préférable d'utiliser count().

Ensuite, analysez l'instruction Where. Supposons que cette requête lente utilise l'index secondaire idx_date_station_driver. Selon le processus d'exécution de la requête de condition Where ci-dessus, la première clé d'index de la requête lente est start_date>'1628442000' et la dernière clé d'index. est : start_dateidx_date_station_driver，按照上面where条件查询的执行过程，该慢查询的Index First Key为start_date>'1628442000'，Index Last Key为： start_date

提取Index First Key后在索引B+树上定位索引起始范围就是索引匹配的过程，在索引B+树上使用二分搜索方法快速定位符合查询条件的起始叶子节点。通过上文Where条件查询执行过程，我们知道该慢查询的where条件（start_date>'1628442000' and start_date，只匹配了索引<code>idx_date_station_driver(start_date, station_id, driver_id)的第一个字段，即只匹配了idx_date_station_driver(start_date)，station_id='1809‘精确查询并没有作用到匹配索引上，而是在Index Filter即索引下推过程中发挥了作用。实际上这里是因为范围查询使联合索引停止匹配。

范围查询导致联合索引停止匹配

为什么范围查询会使联合索引停止匹配？这里涉及到最左前缀匹配原理。假设建立一个联合索引 index(a, b)，会先对a进行排序，在a相等的情况下对b进行排序，如下图所示。在该索引树上，a是全局有序的，而b则处于全局无序、局部有序状态。从全局来看，b的值为1、2、1、4、1、2，只有 b=2 查询条件无法直接使用该索引；从局部来看，当a的值确定时，b则是有序状态，a=2 && b=4Après avoir extrait la première clé d'index, la localisation de la plage de départ de l'index sur l'arbre d'index B+ est le processus de correspondance d'index

Utilisez la méthode de recherche binaire sur l'arbre d'index B+ pour localiser rapidement le nœud feuille de départ qui répond aux conditions de requête. . Grâce au processus d'exécution de requête de condition Where ci-dessus, nous connaissons la condition Where de la requête lente (start_date>'1628442000' et start_date , Seul le premier champ de l'index <code>idx_date_station_driver(start_date, station_id, driver_id) correspond, c'est-à-dire que seul idx_date_station_driver(start_date) correspond à la requête précise de station_id=. '1809' n'agit pas sur l'index correspondant, mais joue un rôle dans le filtre d'index, c'est-à-dire le processus de poussée vers le bas de l'index. Ce qui se passe réellement ici, c'est que la requête de plage

entraîne l'arrêt de la correspondance de l'index syndical .

La requête de plage entraîne l'arrêt de la correspondance de l'index conjoint

idx_date_station_driver

Pourquoi la requête de plage entraîne-t-elle l'arrêt de la correspondance de l'index commun ? Cela implique le principe de correspondance des préfixes les plus à gauche. En supposant qu'un index conjoint index(a, b) soit établi, a sera trié en premier, et si a est égal, b sera trié, comme le montre la figure ci-dessous. Sur cet arbre d'index, a est globalement ordonné, tandis que b est dans un état globalement non ordonné et localement ordonné. D'un point de vue global, la valeur de b est 1, 2, 1, 4, 1, 2, et seules les conditions de requête b=2 ne peuvent pas utiliser directement cet index d'un point de vue local, lorsque la valeur ; de a est déterminé Lorsque , b est un état ordonné, a=2 && b=4 peut utiliser cet index. Par conséquent, la raison fondamentale pour laquelle la requête par plage entraîne l'arrêt de la correspondance de l'index conjoint est que l'état ordonné du champ non premier de l'arborescence d'index dépend de l'égalité du champ précédent, et la requête par plage détruit l'état ordonné local de le champ d'index suivant, ce qui entraîne l'arrêt de la correspondance de l'index. Conditions de requêteVolume de donnéesProportionToutes les données63,67 millions100%start_timestamp_of_date&g t ;'1628442000' et start_timestamp_of_date17,42 millions 27,35%station_id='1809'

	La requête de plage empêche l'index commun de correspondre et ne peut pas filtrer les données dont l'ID de station n'est pas égal à « 1809 » lorsque l'index correspond, ce qui entraîne la plage d'analyse de Mysql Index First Key et Index Last Key sur l'index. être complètement déterminé par start_timestamp_of_date L'heure décide. La requête de plage start_timestamp_of_date peut filtrer 73 % du volume de données, tandis que la requête précise station_id='1809' peut filtrer 99 % du volume de données.

🎜80 000🎜🎜0,16%🎜🎜🎜🎜

Surcharge de l'opération de retour de table

Le champ status n'étant pas sur le champ d'index idx_date_station_driver, il est nécessaire de retourner la table pour interroger les données filtrées par l'index, et déterminer si les données répond aux conditions de requête au niveau de la couche de service Mysql. idx_date_station_driver字段上，所以需要回表查询索引过滤的数据，在Mysql服务层判数据是否符合查询条件。

Mysql的优化器在执行sql语句时会先估算走匹配度高的索引的开销，如果走索引的开销比查全表还大，那么Mysql会选择全表扫描。这个结论可能反常识，在我们印象中索引就是用来提高查询效率的。这里主要涉及两个因素：

• 当查询条件或查找的字段不在二级索引的字段上时，会执行回表操作，会走：二级索引+主键索引。

• 磁盘随机I/O的性能低于顺序I/O。回表查询在主键索引上是随机I/O，全表扫描在主键索引上是顺序I/O。

做实验分析回表操作的开销是否是索引失效的直接原因？

去除status='0'查询条件，explain查看该查询是否使用到了索引idx_date_station_driver

L'optimiseur de MySQL estimera d'abord le coût de l'indexation avec un degré de correspondance élevé lors de l'exécution d'une instruction SQL. Si le coût de l'indexation est supérieur à la recherche de la table entière, alors Mysql choisira une analyse complète de la table. Cette conclusion peut être contre-intuitive. À notre avis, les index sont utilisés pour améliorer l’efficacité des requêtes. Il y a principalement deux facteurs impliqués ici :

Lorsque la condition de requête ou le champ recherché n'est pas sur le champ de l'index secondaire, l'opération de retour de table sera effectuée, qui sera : index secondaire + index de clé primaire.

Les performances des E/S aléatoires du disque sont inférieures à celles des E/S séquentielles. Les requêtes de retour de table sont des E/S aléatoires sur l'index de clé primaire, et les analyses de table complètes sont des E/S séquentielles sur l'index de clé primaire.

Faire des expériences et analyser si le coût des opérations de retour de table est la cause directe de l'échec de l'index ?

Supprimez la condition de requête status='0' et expliquez pour voir si la requête utilise l'index idx_date_station_driver. Le résultat est tel qu'illustré dans la figure ci-dessous. La surcharge de l'opération de retour de table est réduite et l'index ne devient pas invalide.

Résumé

Combinée à l'analyse ci-dessus, la raison de l'échec de l'index est résumée : la requête de plage entraîne l'arrêt de la correspondance de l'index conjoint, et les données mises en correspondance et filtrées par l'index ne suffisent pas, ce qui entraîne l'optimiseur Mysql pour estimer que le coût de l'opération de retour de table du filtre de table est supérieur à la requête de table complète, la requête de table complète a donc été sélectionnée. La requête de plage provoquant l'arrêt de la correspondance de l'index conjoint est la cause de l'échec de l'index, et le coût des opérations de retour de table est la cause directe de l'échec de l'index.

Optimiser l'index

Le coupable de l'échec lent de l'index de requête est que la requête de plage empêche l'index conjoint de correspondre. Il vous suffit d'ajuster les champs de la requête de plage derrière les champs de la requête précise, c'est-à-dire. c'est-à-dire que

index commun
• idx_date_station_driver(start_date, station_id, driver_id)

  est modifié en

  idx_station_date_driver(station_id, start_date, driver_id)
. Les résultats optimisés sont présentés dans la figure ci-dessous.

• Extension

• Scénarios courants d'échec d'index

• violent le principe de correspondance des préfixes les plus à gauche. Par exemple, il existe un index index(a,b), mais la condition de requête n'a que le champ b.

• Effectuez toute opération sur la colonne d'index, y compris les calculs, les fonctions, les conversions de type, etc.

• Les requêtes de plage empêchent l'index syndical de correspondre.

• Réduire l'utilisation de select*. Pour éviter une surcharge inutile des opérations de retour de table, essayez d’utiliser des index de couverture.

• Utilisation différente de (!=, ) et utilisation ou fonctionnement.

L'index de chaîne sans guillemets simples n'est pas valide.

like commence par le caractère générique '%abc'. Notez que comme « abc% » peut être indexé.

order by viole le principe de correspondance le plus à gauche et inclut le tri des champs sans index, ce qui entraînera le tri des fichiers. L'analyse des requêtes lentes est indissociable de l'instruction MySQL Explain se concentre principalement sur deux champs : Type et Extra. Type représente le moyen d'accéder aux données et Extra représente le moyen de filtrer et d'organiser les données. Répertorié ici pour une recherche facile. TypeExtraTOUSanalyse complète de la tableUtilisation de l'index filtrage de la couche de service qlindexAnalyse complète de l'arborescence d'index Utiliser Whereobtient les données de la couche du moteur de stockage et utilise les conditions de requête Where pour filtrer les données dans la couche de service Mysql. rangeAnalyse de la plage de l'arbre d'indexUtilisation de où ; Utilisation de l'analyse de la plage d'index. Les analyses d'index sont similaires aux analyses de tables complètes, mais elles se produisent à différents niveaux. refAnalyse d'index non unique, telle qu'un index non unique et un préfixe non unique d'index uniqueUtilisation de la condition d'indexUtilisez le refoulement d'index pour utiliser pleinement les champs d'index de requête afin de filtrer les données au niveau couche de moteur de stockageeq_refAnalyse d'index unique, telle qu'un index unique, un index de clé primaireUtilisation de temporaireLa table temporaire stocke les résultats, utilisée pour trier et regrouper les requêtesconstConvertir les requêtes en constantes

	group by viole le principe de correspondance le plus à gauche et contient un regroupement de champs sans index, ce qui conduira à la génération de tables temporaires.		Explain Analysis

Utilisation du tri de fichiers Tri de fichiers, pour le tri

🎜🎜🎜NULL🎜🎜Pas besoin d'accéder aux tables ou aux index🎜🎜NULL🎜🎜Retour à la table🎜🎜🎜🎜🎜Pour plus de connaissances liées à la programmation, veuillez visiter : 🎜Introduction à la programmation🎜 ! ! 🎜

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!