未分類
22 3 月 2021

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

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

資深大佬 : situliang 1

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

大佬有話說 (10)

  • 資深大佬 : dongdongkun

    这个

  • 資深大佬 : 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

    加个索引的事