MySQL は OR 条件を使用したインデックス列を回避します

開発前の SQL コードでは、where 条件を含む多くのクエリが更新されていても、この損失を何度も経験しました。ここでは、 または を使用することの欠点とそれを改善する方法を示す例を示します。

select f_crm_id from d_dbname1.t_tbname1 where f_xxx_id = 926067 and (f_mobile ='1234567891' or f_phone ='1234567891' ) limit 1

クエリ ステートメントから、f_mobile フィールドと f_phone フィールドの両方に電話番号が格納されていることが簡単にわかります。一般的な考え方は、SQL を使用するか、SQL を使用して解決することですが、大量のテーブル データは単なる災害です。

t_tbanme1 には idx_id_mobile(f_xxx_id,f_mobile)、idx_phone(f_phone)、idx_id_email(f_id,f_email) というインデックスがありますが、運が良ければ、idx_id_mobile f_xxx_id を使用できる場合もあります

。なぜなら、mysql のすべてのクエリ、テーブル上で選択できるインデックスは 1 つだけだからです。 idx_id_mobile インデックスが使用されており、制限 1 があるためデータが 1 つだけ存在する場合は、すぐに結果が得られますが、f_mobile のデータがない場合は、f_phone フィールドは 1 つずつ検索することしかできません。 f_id 条件、120,000 行のスキャン。 or は and とは異なります。(f_xxx_id,f_mobile,f_phone) を追加すれば完璧だとさえ考えています。

95712e0b0bc62c64b1fddfac486f9cb8

それでは SQL を最適化してはどうでしょうか。それは非常に簡単です (

f_mobile と f_phone に対応するインデックスが必要であることに注意してください)、方法 1:

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

両方の結果セットの場合、2 つの独立した SQL がインデックスを使用でき、別々のクエリに独自の制限があります。返されるものは何でもいいので、1 つだけ受け取ってください。

この種のクエリが特に頻繁に発生する場合 (キャッシュがない場合)、たとえば、数値のほとんどが f_mobile 上にある場合は、それを別の SQL 実行に変更して実行します。最初に sql1 を実行し、結果があれば終了します。 判定 結果がない場合は、sql2 を実行します。これにより、データベース クエリの速度が低下し、コードがより多くのことを処理できるようになります。複雑なシナリオは 1 つのレコードのみを返すだけの単純な制限 2:

sql1 = select f_crm_id from d_dbname1.t_tbname1 where f_xxx_id = 926067 and f_mobile ='1234567891' 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 ='1234567891' limit 1;
    sql1.execute();

この場合、f_create_time と f_modify_time の両方が判定条件を満たす可能性があるため、重複したデータが返される可能性があるため、メソッド 1 とメソッド 2 の両方を変更する必要があります。

方法 1 は変更する必要があります:

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

UNION ALL を UNION に変更しても重複は解消されないという人もいます。クエリが頻繁に行われる場合、または制限が比較的大きい場合、データベースには依然として負荷がかかるため、トレードオフが必要です。

この場合、指値による注文が必要になる可能性がある状況を含め、方法 2 の方が適しています。変換疑似コード:

(select a.f_crm_id from d_dbname1.t_tbname1 as a where a.f_create_time > from_unixtime('1464397527') 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)

またはデータベース上での最適化が難しい条件は、データベースをダウンさせないようにコード内で最適化できます。 or 条件でインデックスが必要ない場合 (比較するデータの量が少ない場合) にのみ考慮されます。

同じフィールド、または f_id=1 または f_id=100 -> f_id in (1,100) のように in に変更できます。 効率の問題については、「mysql の or および in の効率の問題」の記事を参照してください。

上記の最適化シナリオはすべて InnoDB の場合のストレージ エンジンです。MyISAM では異なります。テーブルがいっぱいになるのを回避するためにインデックスを使用できる条件を参照してください。

上記は、インデックス列での OR 条件の使用を避けるための MySQL の内容です。さらに関連する内容については、PHP 中国語 Web サイト (www.php.cn) に注目してください。