MySQL字符串字段使用 in 时,不加引号导致的性能陷阱

日期:2017-01-18 15:25:00

在使用in过程中,同事写了一个简单的in条件查询(字段是普通索引,varchar),由于拼装sql的时候,没有使用引号,导致出现大量慢查询

场景和环境

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

问题描述

在使用in过程中,同事写了一个简单的in条件查询(字段是普通索引,varchar),由于拼装sql的时候,没有使用引号,导致出现大量慢查询

问题SQL

  1. select * from member where phone in (1521xxx5541,15845xxx412)

问题SQL和纠正过的写法对比

执行时间

  1. mysql> select count(*) total from member_phone where phone in(1521xxx541,15845xxx412);
  2. +-------+
  3. | total |
  4. +-------+
  5. | 1 |
  6. +-------+
  7. 1 row in set (2.76 sec)
  8. mysql> select count(*) total from member_phone where phone in('1521xxx541','15845xxx412');
  9. +-------+
  10. | total |
  11. +-------+
  12. | 1 |
  13. +-------+
  14. 1 row in set (0.01 sec)
  15. mysql> select count(*) total from member_phone where (phone='1521xxx541' or phone='15845xxx412');
  16. +-------+
  17. | total |
  18. +-------+
  19. | 1 |
  20. +-------+
  21. 1 row in set (0.00 sec)

EXPLAIN

  1. mysql> explain select count(*) total from member_phone where phone in(1521xxx541,15845xxx412) \G;
  2. *************************** 1. row ***************************
  3. id: 1
  4. select_type: SIMPLE
  5. table: member_phone
  6. type: index
  7. possible_keys: phone
  8. key: phone
  9. key_len: 18
  10. ref: NULL
  11. rows: 6307075
  12. Extra: Using where; Using index
  13. 1 row in set (0.00 sec)
  14. mysql> explain select count(*) total from member_phone where phone in('1521xxx541','15845xxx412') \G;
  15. *************************** 1. row ***************************
  16. id: 1
  17. select_type: SIMPLE
  18. table: member_phone
  19. type: range
  20. possible_keys: phone
  21. key: phone
  22. key_len: 18
  23. ref: NULL
  24. rows: 2
  25. Extra: Using where; Using index
  26. 1 row in set (0.00 sec)
  27. mysql> explain select count(*) total from member_phone where (phone='1521xxx541' or phone='15845xxx412') \G;
  28. *************************** 1. row ***************************
  29. id: 1
  30. select_type: SIMPLE
  31. table: member_phone
  32. type: range
  33. possible_keys: phone
  34. key: phone
  35. key_len: 18
  36. ref: NULL
  37. rows: 2
  38. Extra: Using where; Using index
  39. 1 row in set (0.01 sec)

总结

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

提醒

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

让我们协助您将需求落地

*请填写需求信息,我们会在24小时内与您取得联系。