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
situliang
V2EX  ›  MySQL

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

  •  
  •   situliang · 2021-03-28 19:26:39 +08:00 · 2543 次点击
    这是一个创建于 1096 天前的主题,其中的信息可能已经有所发展或是发生改变。

    关于在 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
        1
    dongdongkun  
       2021-03-28 19:33:10 +08:00
    这个🤔,是不是可以先把 food 表中的数据提取到一个新表中?
    bringyou
        2
    bringyou  
       2021-03-28 19:59:37 +08:00
    提供几个可能的优化思路:
    1. food 表的 type 列有没有加索引
    2. server 表的 food_id 有没有索引
    3. 试着把子查询改成 join 形式,food 表连接 server 表(小表驱动大表)
    bringyou
        3
    bringyou  
       2021-03-28 20:01:15 +08:00   ❤️ 1
    #2 第三条修正:直接用 inner join
    brader
        4
    brader  
       2021-03-28 20:02:54 +08:00   ❤️ 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,都加下索引
    xupefei
        5
    xupefei  
       2021-03-28 21:05:09 +08:00 via iPhone   ❤️ 1
    这数据量还不太需要索引。
    先得看看 query plan 里子查询有没有做哈希优化。
    situliang
        6
    situliang  
    OP
       2021-03-28 21:12:48 +08:00
    @brader
    @bringyou
    感谢两位老哥指点,试了下 4L 老哥的做法,0.43s 就搞定了我一下午都没执行完的 SQL,泪目
    xuanbg
        7
    xuanbg  
       2021-03-28 21:34:39 +08:00
    @brader 去掉 or,修改条件执行两次速度更快哦
    zhangysh1995
        8
    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
        9
    brader  
       2021-03-29 16:25:27 +08:00
    @xuanbg 围绕解决实际问题为中心就可以了,该方式执行速度已经非常快,完全能满足要求了。没必要凭空增加难度以及步骤,何况执行两次,时间相加未必会比较短,就无需去过度考虑了。
    ch2
        10
    ch2  
       2021-03-29 17:22:05 +08:00 via iPhone
    加个索引的事
    512357301
        11
    512357301  
       2021-04-20 08:26:03 +08:00 via Android
    in 的执行效率挺低的,in 里面还套了一个子查询,效率会更低
    尽量用 join 的方式约束,避免写子查询,否则执行次数是笛卡尔积(外层执行次数*子查询执行次数)
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   2618 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 27ms · UTC 15:52 · PVG 23:52 · LAX 08:52 · JFK 11:52
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.