Résoudre le problème de l'échec de l'index et des données inexactes causés par la 'conversion implicite' de Mysql 5.6

Tutoriel vidéo MySQLLa colonne présente comment résoudre le problème de l'échec de l'index et de l'inexactitude des données dans Mysql 5.6

Recommandations d'apprentissage gratuites associées : Tutoriel vidéo MySQL

Contexte

• Lors de la création d'une requête SQL, j'ai essayé pour définir la condition Where Lors de l'interrogation du champ de type vachar en supprimant les guillemets simples, j'ai constaté que cette instruction qui devrait être très rapide s'est avérée très lente. Ce champ varchar a un index composite. Le nombre total d'entrées est de 58 989 et les données trouvées, même sans guillemets simples, ne correspondent pas aux données souhaitées.
• MySQL version 5.6 est utilisé. La situation réelle du moteur innoDB est la suivante

Jetons un œil aux résultats d'exécution

Dans la description ci-dessus, nous devons également noter que la chaîne de votre condition Where doit être composée uniquement de nombres sans guillemets simples. Sinon, une erreur sera signalée

et les données trouvées risquent de ne pas être les données souhaitées. Comme indiqué ci-dessous

analyse

1. À partir des résultats d'exécution, l'index correspondant est supprimé lorsque des guillemets simples sont utilisés. Si les guillemets simples ne sont pas utilisés, il n’y aura pas d’indexation et une analyse complète de la table sera effectuée.
2. Pourquoi cela se produit-il ? Pourquoi l'optimiseur de MySQL n'effectue-t-il pas directement la conversion de type ?

• L'introduction de guillemets simples dans les instructions SQL signifie que ce type est le type de données chaîne CHAR, VARCHAR, BINARY, VARBINARY, BLOB, TEXT, ENUM et SET. .
• Ne pas ajouter de guillemets simples signifie qu'il s'agit d'un type autre qu'une chaîne, comme int, type bigDecimal, etc.
• Si vous donnez une chaîne avec des sous-titres et des symboles spéciaux sans ajouter de guillemets simples, Entre guillemets, la conséquence est que la conversion de type échoue et que SQL ne peut pas être exécuté.

Comme le montre l'image ci-dessus :

1054 - Unknown column '000w1993521' in 'where clause', Time: 0.008000s

Jetons d'abord un coup d'œil au processus d'exécution d'un SQL

(Schéma de réseau)

• Nous tirons d'abord la conclusion : si vous effectuez des opérations de fonction sur le champ d'index (dans ce cas, la fonction cast effectue une conversion implicite), cela peut détruire l'ordre de la valeur de l'index, l'optimiseur a donc décidé d'abandonner la fonction de recherche arborescente. (https://dev.mysql.com/doc/refman/5.7/en/cast-functions.html)
• [Le transfert de l'image du lien externe a échoué. Le site source peut avoir un mécanisme anti-sangsue. Il est recommandé de transférer l'image vers Enregistrer et de télécharger directement (img-l5AwT0xu-1607244327891)(http://note.youdao.com/yws/res/23689/CE6F785994E6476D816B23787CE65217)]
• Signification : Veuillez noter que si vous utilisez BINARY, CAST() ou CONVERT() pour convertir les colonnes d'index, MySQL risque de ne pas être en mesure d'utiliser l'index efficacement.
• Les données trouvées sont inexactes en raison d'une conversion implicite. Après la conversion, les types numériques sont différents, ce qui fait que l'inégalité devient égalité.
  

Conversion implicite

1. Générer la condition
lorsque l'opérateur est de type différent Lorsque le les opérandes sont utilisés ensemble, une conversion de type a lieu pour rendre les opérandes compatibles. Ensuite, la conversion implicite aura lieu
Conditions de conversion implicite :

1. Lorsqu'au moins un des deux paramètres est NULL, le résultat de la comparaison est également NULL, l'exception est l'utilisation de
2. Les deux paramètres sont des chaînes et seront comparés comme des chaînes sans conversion de type
3. Les deux paramètres sont. des entiers et sont comparés comme des entiers sans conversion de type
4. Lors de la comparaison de valeurs hexadécimales avec des valeurs non numériques, elles seront traitées comme des chaînes binaires
5. a un paramètre C'est TIMESTAMP ou DATETIME, et l'autre paramètre est une constante. La constante sera convertie en horodatage
6. Un paramètre est de type décimal. Si l'autre paramètre est décimal ou un entier, l'entier sera converti en décimal puis comparé. l'autre paramètre est un nombre à virgule flottante, la décimale sera convertie en nombre à virgule flottante pour comparaison
7. Dans tous les autres cas, les deux paramètres seront convertis en nombre à virgule flottante pour comparaison

2. Analyser la situation réelle rencontrée

1. Nous comprendrons ensuite que l'exemple que j'ai proposé ci-dessus est une comparaison d'entiers et de chaînes, qui appartient à d'autres situations. Alors analysons d’abord les raisons de l’échec de l’index

• En raison d'autres cas de conversion implicite, les valeurs de comparaison doivent être converties en nombres à virgule flottante pour comparaison
• Nous convertissons d'abord la valeur de la condition de requête en nombre à virgule flottante, puis convertissons la table enregistre Les valeurs doivent également être converties, donc le tri d'index qui a été créé auparavant n'est plus efficace pour le moment. Étant donné que la conversion implicite (fonction) a modifié la valeur d'origine, l'optimiseur n'utilise pas l'index ici et utilise directement l'analyse complète de la table.
  

2. Interrogez les valeurs sans correspondance (ou les valeurs partiellement correspondantes), telles que les résultats de la requête ci-dessus. Vous devez vraiment regarder le code source. C'est la règle de conversion implicite de MYsql. Je ne l'analyserai pas en détail ici (car aucun document pertinent n'a été trouvé)
Pour des raisons historiques, il doit être compatible avec l'ancienne conception. Vous pouvez utiliser les fonctions de conversion de type cast et convert de MySQL pour convertir explicitement.
Résumé

• L'utilisation de conversions et de fonctions implicites entraînera un échec de l'index et des données sélectionnées inexactes
• Les conditions de conversion implicite et les règles
• La raison spécifique pour laquelle la conversion implicite provoque l'échec de l'index est que les valeurs de comparaison doivent être converties en type, provoquant l'échec.
• Évitez la conversion de type implicite. Les types de conversion implicite incluent principalement des types de champs incohérents, dans des paramètres contenant plusieurs types, des types de jeux de caractères ou des règles de classement incohérents, etc.

Si vous souhaitez en savoir plus sur l'apprentissage de la programmation, faites attention à la rubrique formation php !

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!