V2EX = way to explore V2EX 是一个关于分享和探索的地方
Sign Up Now For Existing Member Sign In
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
MySQL 相关项目 MariaDB Drizzle 参考文档 http://mysql-python.sourceforge.net/MySQLdb.html 
This topic created in 1859 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 表有几万条数据的时候，执行了几个小时还没执行完，不知道老哥们有啥好的快速方案吗，可以加表什么的
 | 1 dongdongkun Mar 28, 2021 这个，是不是可以先把 food 表中的数据提取到一个新表中？ |
 | 2 bringyou Mar 28, 2021 提供几个可能的优化思路： 1. food 表的 type 列有没有加索引 2. server 表的 food_id 有没有索引 3. 试着把子查询改成 join 形式，food 表连接 server 表（小表驱动大表） |
| 3 bringyou Mar 28, 2021  1 #2 第三条修正：直接用 inner join |
 | 4 brader Mar 28, 2021 2 ``` 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，都加下索引 |
 | 5 xupefei Mar 28, 2021 via iPhone 1 这数据量还不太需要索引。 先得看看 query plan 里子查询有没有做哈希优化。 |
 | 8 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 |
| 9 brader Mar 29, 2021 @xuanbg 围绕解决实际问题为中心就可以了，该方式执行速度已经非常快，完全能满足要求了。没必要凭空增加难度以及步骤，何况执行两次，时间相加未必会比较短，就无需去过度考虑了。 |
 | 10 ch2 Mar 29, 2021 via iPhone 加个索引的事 |
 | 11 512357301 Apr 20, 2021 via Android in 的执行效率挺低的，in 里面还套了一个子查询，效率会更低 尽量用 join 的方式约束，避免写子查询，否则执行次数是笛卡尔积(外层执行次数*子查询执行次数) |
Select Language