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

› 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

This topic created in 2049 days ago, the information mentioned may be changed or developed.

关于在 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 replies • 2021-04-20 08:26:03 +08:00

dongdongkun

Mar 28, 2021

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

bringyou

Mar 28, 2021

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

bringyou

Mar 28, 2021

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

brader

Mar 28, 2021

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

Mar 28, 2021 via iPhone

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

situliang

Mar 28, 2021

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

xuanbg

Mar 28, 2021

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

zhangysh1995

Mar 29, 2021

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

brader

Mar 29, 2021

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

ch2

Mar 29, 2021 via iPhone

加个索引的事

512357301

Apr 20, 2021 via Android

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