MySQL 中大数据量的情况下更新问题

[situliang]

关于在 MySQL 中几十万数据量的情况下更新问题，想请教一下各位大佬

以下面两个表为例

server 表

server_id	price	food_id
1	1600	5
2	12	12
3	4	1
4	2	3
5	6	5
..	..	..

food 表

food_id	type
1	Fruit
2	Vegetable
3	drink
4	Fruit
5	Vegetable
6	drink
..	..

目前的需求就是需要将 server 表中 food 类型为 Fruit 和 Vegetable 的 food_id 记录更新 price 为 0 常规的 SQL 可以下面这样

UPDATE server SET price = 0 WHERE food_id IN (SELECT food_id FROM food WHERE type = 'Fruit' OR type = 'Vegetable'); 

但是当 server 表有几十万条记录，food 表有几万条数据的时候，执行了几个小时还没执行完，不知道老哥们有啥好的快速方案吗，可以加表什么的

[dongdongkun]

这个🤔，是不是可以先把 food 表中的数据提取到一个新表中？

[bringyou]

提供几个可能的优化思路：
1. food 表的 type 列有没有加索引
2. server 表的 food_id 有没有索引
3. 试着把子查询改成 join 形式，food 表连接 server 表（小表驱动大表）

[bringyou]

#2 第三条修正：直接用 inner join

[brader]

```
UPDATE server a LEFT JOIN food b ON a.food_id = b.food_id SET a.price = 0 WHERE b.type = 'Fruit' OR b.type = 'Vegetable');
```
food_id 、type，都加下索引

[xupefei]

这数据量还不太需要索引。
先得看看 query plan 里子查询有没有做哈希优化。

[situliang]

@brader
@bringyou
感谢两位老哥指点，试了下 4L 老哥的做法，0.43s 就搞定了我一下午都没执行完的 SQL，泪目

[xuanbg]

@brader 去掉 or，修改条件执行两次速度更快哦

[zhangysh1995]

同意 5 楼，可以强制 optimizer hints 让 b.type 走 hash index 。因为这里是特殊值查询，哈希是最快的 https://dev.mysql.com/doc/refman/8.0/en/index-btree-hash.html @situliang

[brader]

@xuanbg 围绕解决实际问题为中心就可以了，该方式执行速度已经非常快，完全能满足要求了。没必要凭空增加难度以及步骤，何况执行两次，时间相加未必会比较短，就无需去过度考虑了。

[ch2]

加个索引的事

[512357301]

in 的执行效率挺低的，in 里面还套了一个子查询，效率会更低
尽量用 join 的方式约束，避免写子查询，否则执行次数是笛卡尔积(外层执行次数*子查询执行次数)