MySQL évite les colonnes d'index en utilisant des conditions OR-tutoriel mysql-php.cn

MySQL évite les colonnes d'index en utilisant des conditions OR

黄舟

Libérer： 2017-02-21 10:31:11

original

1347 Les gens l'ont consulté

J'ai subi cette perte à plusieurs reprises dans le code SQL avant le développement, de nombreuses requêtes avec ou comme condition où étaient même mises à jour. Voici des exemples pour illustrer les inconvénients de l’utilisation de or et comment l’améliorer.

select f_crm_id from d_dbname1.t_tbname1 where f_xxx_id = 926067 and (f_mobile =&#39;1234567891&#39; or f_phone =&#39;1234567891&#39; ) limit 1

Copier après la connexion

Il est facile de voir à partir de l'instruction de requête que les champs f_mobile et f_phone peuvent stocker des numéros de téléphone. L'idée générale est d'utiliser ou d'accéder à un SQL pour résoudre le problème, mais la grande quantité. des données de table est tout simplement un désastre :

MySQL évite les colonnes d'index en utilisant des conditions OR

Il y a des index idx_id_mobile(f_xxx_id,f_mobile), idx_phone(f_phone), idx_id_email(f_id,f_email) sur t_tbanme1, mais l'explication le résultat utilise l'index idx_id_email, parfois Si vous avez de la chance, vous pouvez choisir idx_id_mobile f_xxx_id

car pour chaque requête de mysql, vous ne pouvez sélectionner qu'un seul index sur chaque table. Si l'index idx_id_mobile est utilisé et qu'il y a une seule donnée car il y a une limite de 1, alors félicitations pour avoir obtenu le résultat rapidement ; mais s'il n'y a pas de données pour f_mobile, alors le champ f_phone ne peut être recherché qu'un par un sous ; la condition f_id, analysant 120 000 lignes. ou est différent de et. Certains développeurs pensent même que l'ajout de (f_xxx_id,f_mobile,f_phone) serait parfait Je vais vomir du sang~

L'optimisation SQL est donc très simple (Notez qu'il doit y avoir des index correspondants sur f_mobile et f_phone), Méthode 1 :

(select f_crm_id from d_dbname1.t_tbname1 where f_xxx_id = 926067 and f_mobile =&#39;1234567891&#39; limit 1 ) UNION ALL 
(select f_crm_id from d_dbname1.t_tbname1 where f_xxx_id = 926067 and f_phone =&#39;1234567891&#39; limit 1 )

Copier après la connexion

MySQL évite les colonnes d'index en utilisant des conditions OR

Deux SQL indépendants peuvent utiliser l'index, et chaque requête a sa propre limite. Si les deux ensembles de résultats sont renvoyés, choisissez-en un.

Il existe une autre méthode d'optimisation. Si ce type de requête est particulièrement fréquent (et qu'il n'y a pas de cache), remplacez-la par une exécution SQL distincte. Par exemple, la plupart des valeurs numériques sont activées. f_mobile, puis exécutez d'abord sql1, et il y aura des résultats. Il se termine, puis exécute sql2 après avoir jugé qu'il n'y a aucun résultat, ce qui peut réduire la vitesse de requête de la base de données et permettre au code de gérer plus de choses Méthode 2<. 🎜> Pseudo code :

sql1 = select f_crm_id from d_dbname1.t_tbname1 where f_xxx_id = 926067 and f_mobile =&#39;1234567891&#39; limit 1;
sq1.execute();
if no result sql1:
  sql1 = select f_crm_id from d_dbname1.t_tbname1 where f_xxx_id = 926067 and f_phone =&#39;1234567891&#39; limit 1;
    sql1.execute();

Copier après la connexion

Un scénario plus complexe C'est aussi simple que de renvoyer un enregistrement, limite 2 :

select a.f_crm_id from d_dbname1.t_tbname1 as a where (a.f_create_time > from_unixtime(&#39;1464397527&#39;) or a.f_modify_time > from_unixtime(&#39;1464397527&#39;) ) limit 0,200

Copier après la connexion

Dans ce cas, les méthodes un et deux doivent être modifié, car f_create_time et f_modify_time peuvent répondre aux conditions de jugement, de sorte que les données en double seront renvoyées.

La méthode 1 doit être modifiée :

(select a.f_crm_id from d_dbname1.t_tbname1 as a where a.f_create_time > from_unixtime(&#39;1464397527&#39;) limit 0,200 ) UNION ALL
(select a.f_crm_id from d_dbname1.t_tbname1 as a where a.f_modify_time > from_unixtime(&#39;1464397527&#39;)and a.f_create_time <= from_unixtime(&#39;1464397527&#39;) limit 0,200)

Copier après la connexion

Certaines personnes disent que changer UNION ALL en UNION ne supprimera pas la duplication ? Si les requêtes sont fréquentes ou si la limite est relativement grande, la base de données sera toujours sous pression, un compromis est donc nécessaire.

Dans ce cas, la méthode 2 est plus adaptée, y compris les situations où un ordre par limite peut être requis. Modifier le pseudocode :

sql1 = (select a.f_crm_id from d_dbname1.t_tbname1 as a where a.f_create_time > from_unixtime(&#39;1464397527&#39;) limit 0,200 );
sql1.execute();
sql1_count = sql1.result.count
if sql1_count < 200 :
  sql2 = (select a.f_crm_id from d_dbname1.t_tbname1 as a where a.f_modify_time > from_unixtime(&#39;1464397527&#39;) 
  and a.f_create_time <= from_unixtime(&#39;1464397527&#39;) limit 0, (200 - sql1_count) );
  sql2.execute();

final_result = paste(sql1,sql2);

Copier après la connexion

ou les conditions sont difficiles à optimiser sur la base de données. La logique peut être optimisée dans le code pour ne pas faire tomber la base de données. Il n'est pris en compte que lorsqu'aucun index n'est nécessaire dans la condition ou (et que la quantité de données à comparer est faible).

Le même champ ou peut être modifié en in, tel que f_id=1 ou f_id=100 -> f_id in (1,100) . Pour les problèmes d'efficacité, consultez l'article Les problèmes d'efficacité de or et in dans MySQL.

Les scénarios d'optimisation ci-dessus sont tous des moteurs de stockage dans le cas d'InnoDB. Ils sont différents dans MyISAM ou les conditions dans lesquelles vous pouvez utiliser des index pour éviter le tableau complet.

Ce qui précède est le contenu de MySQL pour éviter d'utiliser des conditions OR dans les colonnes d'index. Pour plus de contenu connexe, veuillez faire attention au site Web PHP chinois (m.sbmmt.com) !