(1)优化前
如下一条SQL,把从1985-05-21入职前的员工薪资都增加500,执行约20.70 s,
从执行计划中可以看出对表salaries进行的是索引全扫描,扫描行数约260W行。mysql> update salaries set salary=salary+500 where emp_no in (select emp_no from employees where hire_date<='1985-05-21');
Query OK, 151583 rows affected (20.70 sec)
Rows matched: 151583 Changed: 151583 Warnings: 0
mysql> desc update salaries set salary=salary+500 where emp_no in (select emp_no from employees where hire_date<='1985-05-21');
+----+--------------------+-----------+------------+-----------------+---------------+---------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+---------