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

V2EX = way to explore

V2EX 是一个关于分享和探索的地方

现在注册

已注册用户请登录

› MySQL 5.5 Community Server

› MySQL 5.6 Community Server

› Percona Configuration Wizard

› XtraBackup 搭建主从复制

Great Sites on MySQL

› Percona

› MySQL Performance Blog

› Severalnines

推荐管理工具

› Sequel Pro

› phpMyAdmin

推荐书目

› MySQL Cookbook

MySQL 相关项目

› MariaDB

› Drizzle

参考文档

› http://mysql-python.sourceforge.net/MySQLdb.html

这是一个创建于 1341 天前的主题，其中的信息可能已经有所发展或是发生改变。

关于在 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 表有几万条数据的时候，执行了几个小时还没执行完，不知道老哥们有啥好的快速方案吗，可以加表什么的

11 条回复 • 2021-04-20 08:26:03 +08:00

dongdongkun

2021-03-28 19:33:10 +08:00

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

bringyou

2021-03-28 19:59:37 +08:00

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

bringyou

2021-03-28 20:01:15 +08:00

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

brader

2021-03-28 20:02:54 +08:00

```
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

2021-03-28 21:05:09 +08:00 via iPhone

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

situliang

2021-03-28 21:12:48 +08:00

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

xuanbg

2021-03-28 21:34:39 +08:00

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

zhangysh1995

2021-03-29 14:52:40 +08:00

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

brader

2021-03-29 16:25:27 +08:00

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

ch2

2021-03-29 17:22:05 +08:00 via iPhone

加个索引的事

512357301

2021-04-20 08:26:03 +08:00 via Android

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