近期遇到一个简单的慢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语句的优化弱爆了！
对于update+in，mysql优化器做的不好。
对于select+in，mysql优化器可以优化走索引。