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 :
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
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 1 | Champ 2 | Champ 3 |
idx_date_station_driver
| start_date | station_id | driver_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=4
Aprè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
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.
|
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. |
|
Conditions de requête
Volume de données | Proportion |
|
Toutes les données | 63,67 millions | 100% |
start_timestamp_of_date&g t ;'1628442000' et start_timestamp_of_date17,42 millions | 27,35% |
station_id='1809'
🎜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会选择全表扫描。这个结论可能反常识,在我们印象中索引就是用来提高查询效率的。这里主要涉及两个因素:
做实验分析回表操作的开销是否是索引失效的直接原因?
去除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
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.
|
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
|
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.
|
| Type |
|
Extra
|
|
|
| TOUS
analyse complète de la table | Utilisation de l'index | filtrage de la couche de service ql | | index
Analyse complète de l'arborescence d'index | Utiliser Where | obtient 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. |
| range
Analyse de la plage de l'arbre d'index | Utilisation 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. |
| ref
Analyse d'index non unique, telle qu'un index non unique et un préfixe non unique d'index unique | Utilisation de la condition d'index | Utilisez 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 stockage |
| eq_ref
Analyse d'index unique, telle qu'un index unique, un index de clé primaire | Utilisation de temporaire | La table temporaire stocke les résultats, utilisée pour trier et regrouper les requêtes |
| const
Convertir les requêtes en constantes
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!