MySQL update ... where in(...) 为什么不走索引？

近期遇到一个简单的慢SQL，有多简单？

update t2
set name = ''
where code in	-- t2.code上有索引
(
  select code
  from t1
  where name = '合肥市' -- t1.name上有唯一索引
);

怎样才能走索引？

#1.改成等于
mysql> explain
    -> update t2 set name = '' where code = (select code from t1 where name = '合肥市');
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | UPDATE      | t2    | NULL       | range | ix_code       | ix_code | 78      | const |    1 |   100.00 | Using where |
|  2 | SUBQUERY    | t1    | NULL       | ref   | ix_name       | ix_name | 153     | const |    1 |   100.00 | NULL        |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+

#2.改成join
mysql> explain
    -> update t2 a inner join t1 b on a.code = b.code set a.name = '' where b.name = '合肥市';
+----+-------------+-------+------------+------+---------------+---------+---------+-------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key     | key_len | ref         | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+---------+---------+-------------+------+----------+-------------+
|  1 | SIMPLE      | b     | NULL       | ref  | ix_name       | ix_name | 153     | const       |    1 |   100.00 | Using where |
|  1 | UPDATE      | a     | NULL       | ref  | ix_code       | ix_code | 78      | csdn.b.code |    5 |   100.00 | NULL        |
+----+-------------+-------+------------+------+---------------+---------+---------+-------------+------+----------+-------------+

#3.拼SQL
mysql> select code from t1 where name = '合肥市';
+--------+
| code   |
+--------+
| 340000 |
+--------+
mysql> explain update t2 set name = '' where code = '340000';
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | UPDATE      | t2    | NULL       | range | ix_code       | ix_code | 78      | const |    1 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+

能否引导update+in走索引？

#1.加limit 1再次告诉优化器只返回一行数据
mysql> explain
    -> update t2 set name = '' where code in
    -> (
    ->   select code from (select code from t1 where name='合肥市' limit 1) tmp
    -> );
+----+--------------------+------------+------------+--------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type        | table      | partitions | type   | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+--------------------+------------+------------+--------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | UPDATE             | t2         | NULL       | index  | NULL          | PRIMARY | 4       | NULL  |  137 |   100.00 | Using where |
|  2 | DEPENDENT SUBQUERY | <derived3> | NULL       | system | NULL          | NULL    | NULL    | NULL  |    1 |   100.00 | NULL        |
|  3 | DERIVED            | t1         | NULL       | ref    | ix_name       | ix_name | 153     | const |    1 |   100.00 | NULL        |
+----+--------------------+------------+------------+--------+---------------+---------+---------+-------+------+----------+-------------+

#2.加索引提示
mysql> explain
    -> update t2 force index(ix_code) set name = '' where code in
    -> (
    ->   select code from t1 force index(ix_name) where name='合肥市'
    -> );
+----+--------------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type        | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+--------------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | UPDATE             | t2    | NULL       | index | NULL          | PRIMARY | 4       | NULL  |  137 |   100.00 | Using where |
|  2 | DEPENDENT SUBQUERY | t1    | NULL       | ref   | ix_name       | ix_name | 153     | const |    1 |    10.00 | Using where |
+----+--------------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+

update改成select会怎样？

mysql> explain
    -> select * from t2 where code in(select code from t1 where name = '合肥市');
+----+--------------+-------------+------------+------+---------------+---------+---------+------------------+------+----------+-------------+
| id | select_type  | table       | partitions | type | possible_keys | key     | key_len | ref              | rows | filtered | Extra       |
+----+--------------+-------------+------------+------+---------------+---------+---------+------------------+------+----------+-------------+
|  1 | SIMPLE       | <subquery2> | NULL       | ALL  | NULL          | NULL    | NULL    | NULL             | NULL |   100.00 | Using where |
|  1 | SIMPLE       | t2          | NULL       | ref  | ix_code       | ix_code | 78      | <subquery2>.code |    5 |   100.00 | NULL        |
|  2 | MATERIALIZED | t1          | NULL       | ref  | ix_name       | ix_name | 153     | const            |    1 |   100.00 | NULL        |
+----+--------------+-------------+------------+------+---------------+---------+---------+------------------+------+----------+-------------+

真像到底是怎样的？

使用Tosska SQL Tuning Expert for MySQL，看下图形结构的执行计划：

看下Full Index Scan为什么没走索引：

原来MySQL优化器，把update+in优化成了exists：

mysql> explain
    -> update t2
    -> set name = ''
    -> where exists
    -> (
    ->   select 1
    ->   from t1
    ->   where t1.name = '合肥市'
    ->     and t2.code = t1.code
    -> );
+----+--------------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type        | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+--------------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | UPDATE             | t2    | NULL       | index | NULL          | PRIMARY | 4       | NULL  |  137 |   100.00 | Using where |
|  2 | DEPENDENT SUBQUERY | t1    | NULL       | ref   | ix_name       | ix_name | 153     | const |    1 |    10.00 | Using where |
+----+--------------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

mysql> show warnings;
+-------+------+--------------------------------------------------------------------------+
| Level | Code | Message                                                                  |
+-------+------+--------------------------------------------------------------------------+
| Note  | 1276 | Field or reference 'csdn.t2.code' of SELECT #2 was resolved in SELECT #1 |
+-------+------+--------------------------------------------------------------------------+

MySQL对update+in语句的优化弱爆了！

小结：

1. 对于update+in，mysql优化器做的不好。
2. 对于select+in，mysql优化器可以优化走索引。
3. 能用join的尽量不要用子查询(当然也有特殊情况)。
4. 了解MySQL的join算法: NLJ，BNL，MMR，BKA，hash-join。