MySQL 中字符串字段,在使用 in 时,没有加引号时的性能陷阱

场景和环境#

  • redhat6.5 + 64 位 + 12 核心 + 16G
  • 表数量 600w
  • MySQL 5.0

问题描述#

在使用 in 过程中,同事写了一个简单的 in 条件查询 (字段是普通索引,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)

总结#

在三个类型的 sql 中,效率从高到低分别是 or,in 添加了引号,in 不加引号。在 explain 中看到不加引号时,显示的用上了索引 phone,type 变成了 index , 和全表扫描差不多了,只不过 MySQL 扫描时按索引的次序进行而不是行。

提醒#

在 where 多个 or,in 中条件个数比较多,或者多个 in 条件时,实际性能都比较差的。以上测试我个人仅在 MySQL5.0 中测试,高版本官方不知是否优化过。

原文地址:http://moell.cn/article/33

《L05 电商实战》
从零开发一个电商项目,功能包括电商后台、商品 & SKU 管理、购物车、订单管理、支付宝支付、微信支付、订单退款流程、优惠券等
《L01 基础入门》
我们将带你从零开发一个项目并部署到线上,本课程教授 Web 开发中专业、实用的技能,如 Git 工作流、Laravel Mix 前端工作流等。
讨论数量: 1

那么 laravel 对于 whereIn 参数绑定后处理的数据问题还是没有得到解决吗? 还是说 是我对这个问题的处理方式不对?

6年前 评论