MySQL の文字列フィールドの場合、引用符がない場合に in を使用すると、パフォーマンス トラップが発生します。

シナリオと環境

redhat6.5 + 64ビット + 12コア+ 16G

テーブル数600w

MySQL 5.0

問題の説明

inを使用する過程で、同僚が簡単なin条件付きを書きましたquery (フィールドは通常のインデックス、varchar) SQLを組み立てるときに引用符が使用されないため、大量の遅いクエリが表示されます

問題のSQL

select count(*) total from member_phone where phone in(1521xxx541,15845xxx412)

問題のSQLと修正後の記述方法の比較

。実行時間

mysql> select count(*) total from member_phone where phone in(1521xxx541,15845xxx412);
+-------+
| total |
+-------+
|     1 | 
+-------+
1 row in set (2.76 sec)
mysql> select count(*) total from member_phone where phone in('1521xxx541','15845xxx412');
+-------+
| total |
+-------+
|     1 | 
+-------+
1 row in set (0.01 sec)
mysql> select count(*) total from member_phone where (phone='1521xxx541' or phone='15845xxx412');
+-------+
| total |
+-------+
|     1 | 
+-------+
1 row in set (0.00 sec)

EXPLAIN

mysql> explain select count(*) total from member_phone where phone in(1521xxx541,15845xxx412) \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: member_phone
         type: index
possible_keys: phone
          key: phone
      key_len: 18
          ref: NULL
         rows: 6307075
        Extra: Using where; Using index
1 row in set (0.00 sec)
mysql> explain select count(*) total from member_phone where phone in('1521xxx541','15845xxx412') \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: member_phone
         type: range
possible_keys: phone
          key: phone
      key_len: 18
          ref: NULL
         rows: 2
        Extra: Using where; Using index
1 row in set (0.00 sec)
mysql> explain select count(*) total from member_phone where (phone='1521xxx541' or phone='15845xxx412') \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: member_phone
         type: range
possible_keys: phone
          key: phone
      key_len: 18
          ref: NULL
         rows: 2
        Extra: Using where; Using index
1 row in set (0.01 sec)

概要

3種類のSQLのうち、効率が高いものから低いものまでは、or、引用符付き、引用符なしの順です。 Explain に引用符が表示されない場合は、インデックスフォンが使用されていることを示し、タイプはインデックスになります。ただし、MySQL は行ではなくインデックスの順序でスキャンする点が異なります。

注意事項

where に複数の or がある場合、in に多数の条件がある場合、または複数の in 条件がある場合、実際のパフォーマンスは相対的に低下します。私は個人的に上記のテストを MySQL 5.0 でのみテストしましたが、上位バージョンが正式に最適化されているかどうかはわかりません。