For string fields in MySQL, when using in, there is a performance trap when there are no quotation marks.-Mysql Tutorial-php.cn

For string fields in MySQL, when using in, there is a performance trap when there are no quotation marks.

迷茫

Release： 2017-01-23 14:59:17

Original

1582 people have browsed it

Scenarios and Environment

redhat6.5 + 64-bit + 12 cores + 16G

Number of tables 600w

MySQL 5.0

Problem Description

During the process of using in, a colleague wrote a simple in conditional query (the field is a common index, varchar). Since quotation marks were not used when assembling the sql, a large number of slow queries occurred

Problem SQL

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

Copy after login

Comparison of problem SQL and corrected writing

Execution time

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(&#39;1521xxx541&#39;,&#39;15845xxx412&#39;);
+-------+
| total |
+-------+
|     1 | 
+-------+
1 row in set (0.01 sec)
mysql> select count(*) total from member_phone where (phone=&#39;1521xxx541&#39; or phone=&#39;15845xxx412&#39;);
+-------+
| total |
+-------+
|     1 | 
+-------+
1 row in set (0.00 sec)

Copy after login

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(&#39;1521xxx541&#39;,&#39;15845xxx412&#39;) \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=&#39;1521xxx541&#39; or phone=&#39;15845xxx412&#39;) \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)

Copy after login

Summary

Among the three types of sql, the efficiency from high to low is or, in with quotes, and in without quotes. When you see no quotation marks in the explain, it shows that the index phone is used, and the type becomes index. It is almost the same as a full table scan, except that MySQL scans in the order of the index instead of rows.

Reminder

When there are multiple or in where, the number of conditions in in is relatively large, or there are multiple in conditions, the actual performance will be relatively poor. I personally only tested the above tests in MySQL 5.0. I don’t know whether the higher versions have been officially optimized.