base de données
tutoriel mysql
Quelles sont les situations dans lesquelles MySQL provoque un échec d'index ?
Quelles sont les situations dans lesquelles MySQL provoque un échec d'index ?
1. PRÉPARATION
Préparez deux tableaux pour la démonstration:
CREATE TABLE `student_info` ( `id` int NOT NULL AUTO_INCREMENT, `student_id` int NOT NULL, `name` varchar(20) DEFAULT NULL, `course_id` int NOT NULL, `class_id` int DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8;rreerE
CREATE TABLE `course` ( `id` int NOT NULL AUTO_INCREMENT, `course_id` int NOT NULL, `course_name` varchar(40) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8;
2. Règles d'invalidation d'index
1. son efficacité de requête en construisant des index Il existe plusieurs options comme suit :
①Construire un index normal :#准备数据 select count(*) from student_info;#1000000 select count(*) from course; #100
②Sur la base de l'index normal, ajouter un index conjoint :
#平均耗时291毫秒 select * from student_info where name='123' and course_id=1 and class_id=1;
Vous pouvez voir. que
lorsque plusieurs index peuvent être utilisés, le système donne généralement la priorité à l'utilisation d'index conjoints plus longs, car les index conjoints sont plus rapides 
en comparaison. Cela devrait être facile à comprendre,
. Si vous créez un index commun composé de name, course_id, class_id, alors l'instruction SQL ci-dessus utilisera cet index commun avec un key_len plus long comme prévu (la surprise est que l'optimiseur peut choisir d'autres meilleures solutions, s'il s'agit de mots plus rapides ).
La vitesse de l'index conjoint n'est pas nécessairement meilleure que celle de l'index ordinaire
Par exemple, si la première condition filtre tous les enregistrements, alors il n'est pas nécessaire d'utiliser un index suivant.① Situation dans laquelle l'index conjoint correspond à tous :2. Principe de correspondance le plus à gauche
#建立普通索引 create index idx_name on student_info(name); #平均耗时25毫秒，查看explain执行计划，使用到的是idx_name索引查询 select * from student_info where name='MOKiKb' and course_id=1 and class_id=1;
#name，course_id组成的联合索引 create index idx_name_courseId on student_info(name,course_id); #该查询语句一般使用的是联合索引，而不是普通索引，具体看优化器决策 #平均耗时20ms select * from student_info where name='zhangsan' and course_id=1 and class_id=1;
Chaque condition de champ correspond à l'index conjoint, donc l'instruction SQL suit la règle de préfixe la plus à gauche. L'utilisation d'un index conjoint permet des recherches rapides et évite des requêtes supplémentaires, c'est donc la situation optimale.
#删除前例创建的索引，新创建三个字段的联合索引，name-course_id-cass_id create index idx_name_cou_cls on student_info(name,course_id,class_id);
Les conditions de cette instruction SQL ne contiennent pas toutes les conditions de l'index conjoint, mais la moitié droite est effacée. L'index utilisé. par cette instruction est toujours Cette requête de corrélation n'en utilise qu'une partie. En regardant key_len, on peut savoir qu'il manque 5 octets. Ces 5 octets correspondent à class_id, ce qui prouve que class_id n'est pas efficace (il n'est pas là, donc). bien sûr, il n'est pas utilisé).
De même, si vous supprimez le champ course_id dans Where, l'index conjoint prendra toujours effet, mais le key_len sera réduit.
#关联字段的索引比较完整 explain select * from student_info where name='11111' and course_id=10068 and class_id=10154;
L'instruction SQL ci-dessus utilise toujours l'index conjoint, mais son key_len devient plus petit. Seul le champ nom utilise l'index, et bien que le champ class_id soit présent. l'index conjoint, mais c'est GG car il ne répond pas au principe de correspondance le plus à gauche.
Le processus d'exécution de l'intégralité de l'instruction SQL est le suivant : recherchez d'abord tous les enregistrements portant le nom 11111 dans l'arbre B de l'index conjoint, puis filtrez le texte intégral de ces enregistrements dont l'identifiant de classe n'est pas 10154. Avec une étape supplémentaire de recherche en texte intégral, les performances seront pires qu'en ① et ②.
explain select * from student_info where name='11111' and course_id=10068;
Cette situation est un cas particulier de la situation précédente. Le champ le plus à gauche de l'index conjoint n'est pas trouvé, donc bien qu'il en existe d'autres. parties, elles sont toutes invalides. La méthode est la recherche en texte intégral.
Remarque : lors de la création d'un index conjoint, l'ordre des champs est fixe et la correspondance la plus à gauche est comparée selon cet ordre, mais dans l'instruction de requête, l'ordre des champs dans la condition Where est variable, ce qui Cela signifie qu'il n'est pas nécessaire de suivre l'ordre des champs d'index associés. Il suffit d'inclure la condition Where.
3. L'index de colonne sur le côté droit de la condition de plage n'est pas valide Reprenez l'index conjoint ci-dessus et utilisez la requête SQL suivante :
#联合索引中间的字段未使用，而左边和右边的都存在 explain select * from student_info where name='11111' and class_id=10154;;Le key_len n'est que de 68, ce qui signifie que le class_id dans l'index associé n'est pas utilisé, bien qu'il soit conforme au principe de correspondance le plus à gauche, mais parce que le symbole
> rend invalide l'index à droite du champ de condition dans l'index associé 
.
Mais si vous utilisez le signe >= : explain select * from student_info where class_id=10154 and course_id=10068;
结论：为了充分利用索引，我们有时候可以将>、<等价转为>=、<=的形式，或者将可能会有<、>的条件的字段尽量放在关联索引靠后的位置。
4.计算、函数导致索引失效
#删除前面的索引，新创建name字段的索引，方便演示 create index idx_name on student_info(name);
现有一个需求，找出name为li开头的学生信息：
#使用到了索引 explain select * from student_info where name like 'li%'; #未使用索引，花费时间更久 explain select * from student_info where LEFT(name,2)='li';
上面的两条sql语句都可以满足需求，然而第一条语句用了索引，第二条没有，一点点的改变真是天差地别。
结论：字段使用函数会让优化器无从下手，B树中的值和函数的结果可能不搭边，所以不会使用索引，即索引失效。字段能不用就不用函数。
类似：
#也不会使用索引 explain select * from student_info where name+''='lisi';
类似的对字段的运算也会导致索引失效。
5.类型转换导致索引失效
#不会使用name的索引 explain select * from student_info where name=123; #使用到索引 explain select * from student_info where name='123';
如上，name字段是VARCAHR类型的，但是比较的值是INT类型的，name的值会被隐式的转换为INT类型再比较，中间相当于有一个将字符串转为INT类型的函数。
6.不等于(!= 或者<>)索引失效
#创建索引 create index idx_name on student_info(name); #索引失效 explain select * from student_info where name<>'zhangsan'; explain select * from student_info where name!='zhangsan';
不等于的情况是不会使用索引的。因为!=代表着要进行全文的查找，用不上索引。
7.is null可以使用索引，is not null无法使用索引
#可以使用索引 explain select * from student_info where name is null; #索引失效 explain select * from student_info where name is not null;
和前一个规则类似的，!=null。同理not like也无法使用索引。
最好在设计表时设置NOT NULL约束，比如将INT类型的默认值设为0，将字符串默认值设为''。
8.like以%开头，索引失效
#使用到了索引 explain select * from student_info where name like 'li%'; #索引失效 explain select * from student_info where name like '%li';
只要以%开头就无法使用索引，因为如果以%开头，在B树排序的数据中并不好找。
9.OR前后存在非索引的列，索引失效
#创建好索引 create index idx_name on student_info(name); create index idx_courseId on student_info(course_id);
如果or前后都是索引：
#使用索引 explain select * from student_info where name like 'li%' or course_id=200;
如果其中一个没有索引：
explain select * from student_info where name like 'li%' or class_id=1;
那么索引就失效了，假设还是使用索引，那就变成了先通过索引查，然后再根据没有的索引的字段进行全表查询，这种方式还不如直接全表查询来的快。
10.字符集不统一
字符集如果不同，会存在隐式的转换，索引也会失效，所有应该使用相同的字符集，防止这种情况发生。
三、建议
对于单列索引，尽量选择针对当前query过滤性更好的索引
在选择组合索引时，query过滤性最好的字段应该越靠前越好
在选择组合索引时，尽量选择能包含当前query中where子句中更多字段的索引
在选择组合索引时，如果某个字段可能出现范围查询，尽量将它往后放
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!
Outils d'IA chauds
Undress AI Tool
Images de déshabillage gratuites
Undresser.AI Undress
Application basée sur l'IA pour créer des photos de nu réalistes
AI Clothes Remover
Outil d'IA en ligne pour supprimer les vêtements des photos.
Clothoff.io
Dissolvant de vêtements AI
Video Face Swap
Échangez les visages dans n'importe quelle vidéo sans effort grâce à notre outil d'échange de visage AI entièrement gratuit !
Article chaud
Outils chauds
Bloc-notes++7.3.1
Éditeur de code facile à utiliser et gratuit
SublimeText3 version chinoise
Version chinoise, très simple à utiliser
Envoyer Studio 13.0.1
Puissant environnement de développement intégré PHP
Dreamweaver CS6
Outils de développement Web visuel
SublimeText3 version Mac
Logiciel d'édition de code au niveau de Dieu (SublimeText3)
Sujets chauds
Effectuer des sauvegardes logiques à l'aide de mysqldump dans MySQL
Jul 06, 2025 am 02:55 AM
MySQLDump est un outil commun pour effectuer des sauvegardes logiques des bases de données MySQL. Il génère des fichiers SQL contenant des instructions de création et d'insertion pour reconstruire la base de données. 1. Il ne sauvegarde pas le fichier d'origine, mais convertit la structure de la base de données et le contenu en commandes SQL portables; 2. Il convient aux petites bases de données ou à la récupération sélective et ne convient pas à la récupération rapide des données de niveau TB; 3. 4. Utilisez la commande MySQL pour importer pendant la récupération et peut désactiver les vérifications des clés étrangères pour améliorer la vitesse; 5. Il est recommandé de tester régulièrement la sauvegarde, d'utiliser la compression et de régler automatiquement.
Implémentation de transactions et compréhension des propriétés acides dans MySQL
Jul 08, 2025 am 02:50 AM
MySQL prend en charge le traitement des transactions et utilise le moteur de stockage InNODB pour garantir la cohérence et l'intégrité des données. 1. Les transactions sont un ensemble d'opérations SQL, soit tous réussissent ou ne parviennent pas à reculer; 2. Les attributs acides comprennent l'atomicité, la cohérence, l'isolement et la persistance; 3. Les déclarations qui contrôlent manuellement les transactions sont StartTransaction, Commit and Rollback; 4. Les quatre niveaux d'isolement incluent la lecture non engagée, la lecture soumise, la lecture reproductible et la sérialisation; 5. Utilisez correctement les transactions pour éviter le fonctionnement à long terme, désactiver les validations automatiques et gérer raisonnablement les verrous et les exceptions. Grâce à ces mécanismes, MySQL peut obtenir une forte fiabilité et un contrôle simultané.
Configuration de la réplication asynchrone primaire-replica dans MySQL
Jul 06, 2025 am 02:52 AM
Pour configurer la réplication maître-esclave asynchrone pour MySQL, suivez ces étapes: 1. Préparez le serveur maître, activez les journaux binaires et définissez un serveur unique, créez un utilisateur de réplication et enregistrez l'emplacement du journal actuel; 2. Utilisez MySQLDump pour sauvegarder les données de la bibliothèque maître et l'importez-les au serveur esclave; 3. Configurez le serveur-ID et le log-log du serveur esclave, utilisez la commande Changemaster pour vous connecter à la bibliothèque maître et démarrer le thread de réplication; 4. Vérifiez les problèmes communs, tels que le réseau, les autorisations, la cohérence des données et les conflits d'auto-augmentation, et surveiller les retards de réplication. Suivez les étapes ci-dessus pour vous assurer que la configuration est terminée correctement.
Gestion des ensembles de personnages et des problèmes de collations dans MySQL
Jul 08, 2025 am 02:51 AM
Les problèmes de règles de jeu de caractères et de tri sont courants lors de la migration multiplateforme ou du développement multi-personnes, entraînant un code brouillé ou une requête incohérente. Il existe trois solutions principales: d'abord, vérifiez et unifiez le jeu de caractères de la base de données, de la table et des champs vers UTF8MB4, affichez via ShowCreateDatabase / Table, et modifiez-le avec une instruction alter; Deuxièmement, spécifiez le jeu de caractères UTF8MB4 lorsque le client se connecte et le définissez dans les paramètres de connexion ou exécutez SetNames; Troisièmement, sélectionnez les règles de tri raisonnablement et recommandez d'utiliser UTF8MB4_UNICODE_CI pour assurer la précision de la comparaison et du tri, et spécifiez ou modifiez-la via ALTER lors de la construction de la bibliothèque et du tableau.
Connexion à la base de données MySQL à l'aide du client de ligne de commande
Jul 07, 2025 am 01:50 AM
La façon la plus directe de se connecter à la base de données MySQL consiste à utiliser le client de la ligne de commande. Entrez d'abord le nom d'utilisateur MySQL-U -P et entrez correctement le mot de passe pour entrer l'interface interactive; Si vous vous connectez à la base de données distante, vous devez ajouter le paramètre -H pour spécifier l'adresse hôte. Deuxièmement, vous pouvez directement passer à une base de données spécifique ou exécuter des fichiers SQL lors de la connexion, tels que le nom de la base de données MySQL-U Username-P ou le nom de la base de données MySQL-U Username-P-P
Gérer les jeux de caractères et les collations dans MySQL
Jul 07, 2025 am 01:41 AM
Le réglage des jeux de caractères et des règles de collation dans MySQL est crucial, affectant le stockage des données, l'efficacité de la requête et la cohérence. Premièrement, le jeu de caractères détermine la gamme de caractères storable, telle que UTF8MB4 prend en charge les chinois et les emojis; Les règles de tri contrôlent la méthode de comparaison des caractères, telle que UTF8MB4_UNICODE_CI est sensible à la casse, et UTF8MB4_BIN est une comparaison binaire. Deuxièmement, le jeu de caractères peut être défini à plusieurs niveaux de serveur, de base de données, de table et de colonne. Il est recommandé d'utiliser UTF8MB4 et UTF8MB4_UNICODE_CI de manière unifiée pour éviter les conflits. En outre, le problème du code brouillé est souvent causé par des jeux de caractères incohérents de connexions, de stockage ou de terminaux de programme, et doit être vérifié par calque par calque et définir uniformément. De plus, les ensembles de caractères doivent être spécifiés lors de l'exportation et de l'importation pour éviter les erreurs de conversion
Concevoir une stratégie de sauvegarde de la base de données MySQL robuste
Jul 08, 2025 am 02:45 AM
Pour concevoir une solution de sauvegarde MySQL fiable, 1. Premièrement, clarifiez les indicateurs RTO et RPO, et déterminez la fréquence et la méthode de sauvegarde en fonction de la plage de temps d'arrêt et de perte de données acceptable de l'entreprise; 2. Adoptez une stratégie de sauvegarde hybride, combinant une sauvegarde logique (comme MySQLDump), une sauvegarde physique (telle que Perconaxtrabackup) et un journal binaire (binlog), pour obtenir une récupération rapide et une perte de données minimale; 3. Testez régulièrement le processus de récupération pour assurer l'efficacité de la sauvegarde et familiariser avec les opérations de récupération; 4. Faites attention à la sécurité du stockage, y compris le stockage hors site, la protection du chiffrement, la politique de rétention de version et la surveillance des tâches de sauvegarde.
En utilisant des expressions de table communes (CTES) dans MySQL 8
Jul 12, 2025 am 02:23 AM
Les CTES sont une fonctionnalité introduite par MySQL8.0 pour améliorer la lisibilité et la maintenance des requêtes complexes. 1. CTE est un ensemble de résultats temporaire, qui n'est valable que dans la requête actuelle, a une structure claire et prend en charge les références en double; 2. Comparé aux sous-requêtes, le CTE est plus lisible, réutilisable et prend en charge la récursivité; 3. Le CTE récursif peut traiter les données hiérarchiques, telles que la structure organisationnelle, qui doit inclure des requêtes initiales et des pièces de récursivité; 4. Les suggestions d'utilisation incluent l'évitement de l'abus, la dénomination des spécifications, la prête d'attention aux performances et aux méthodes de débogage.
