场景和环境

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

总结

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

提醒

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