l凌晨1点开发迁移业务告知sql响应有问题,赶紧爬起来看看
6000w的大表,没走索引导致查询了50s,执行计划如下
desc select a, b, c, d from table where FIND_IN_SET(a,'MD5value1,MD5value2');
+----+-------------+------------+------+---------------+------+---------+------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+------+---------------+------+---------+------+----------+-------------+
| 1 | SIMPLE | table | ALL | NULL | NULL | NULL | NULL | 60435142 | Using where |
+----+-------------+------------+------+---------------+------+---------+------+----------+-------------+
1 row in set (0.01 sec)
使用了in导致查询没有走索引改写为union all sql秒出结果
select a, b, c, d from table where a='md5value1'
union all
select a, b, c, d from table where a='MD5value2';
看下执行计划
+----+--------------+------------+-------+---------------+---------+---------+-------+------+-----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------+------------+-------+---------------+---------+---------+-------+------+-----------------+
| 1 | PRIMARY | table | const | PRIMARY | PRIMARY | 302 | const | 1 | NULL |
| 2 | UNION | table | const | PRIMARY | PRIMARY | 302 | const | 1 | NULL |
| NULL | UNION RESULT | <union1,2> | ALL | NULL | NULL | NULL | NULL | NULL | Using temporary |
+----+--------------+------------+-------+---------------+---------+---------+-------+------+-----------------+
3 rows in set (0.03 sec)