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

[请教] mysql 从一张表更新另一张表, sql 语句如何效率最高?

  •  
  •   caixiexin · 2014-11-22 08:44:54 +08:00 · 5711 次点击
    这是一个创建于 3645 天前的主题,其中的信息可能已经有所发展或是发生改变。
    最近工作上遇到的问题,有条sql语句执行时间一直很长,情况如下:
    a表记录电话通话情况的表,b表记录的是电话通话失败的原因,两表之间通过一个叫sessionid的字段关联在一起。两表结构大致如下:
    a表:
    id, 主键,自增
    caller-->主叫号码
    callee -->被叫号码
    state ->通话结果,成功=1,失败=2
    sessionid -->唯一标志一通电话的标记位
    fail_reason -->电话通话失败原因
    send_time --电话拨号的时间,unix时间戳

    b表:
    id -->主键,自增id
    reason ->电话通话失败的原因
    sessionid -->唯一标志一通电话的标记位
    create_time -->记录创建时间

    a表记录大概50W条,b表记录大概20w条,且b表中存在sessionid重复的记录
    由于某些原因,电话失败原因不能通过a表关联b表查询得到,而要定期(30分钟或1个小时)从b表更新到a表中,这个更新语句该如何写效率最高?
    最初我的写法是这样:
    update a表 t
    set t.fail_reason = (SELECT t2.reason from b表 t2
    where t.sessionid = t2.sessionid
    and t.status = 2
    order by t2.create_time desc
    limit 0,1
    )
    where t.status = 2
    and UNIX_TIMESTAMP() -t.send_time < 3600
    and (t.fail_reason = '' or t.fail_reason is null)
    -------------
    以上语句写成数据库事件,每隔半个小时执行一次,后期随着数据增大,感觉效率非常低,执行一次最少都几十分钟,
    后来百度google到另一种写法:
    update a表 t ,b表 t2
    set t.fail_reason=t2.reason
    where t.sessionid=t2.sessionid
    and t.status=2
    and UNIX_TIMESTAMP() -t.send_time < 3600
    and (t.fail_reason = '' or t.fail_reason is null)
    但还是很慢

    请问这类逻辑,mysql中sql语句有什么好的写法吗?还是我该去考虑增加索引,或者做数据拆分了?
    10 条回复    2016-06-06 10:57:49 +08:00
    liprais
        1
    liprais  
       2014-11-22 09:48:36 +08:00 via iPhone   ❤️ 1
    先看执行计划再谈优化
    a2z
        2
    a2z  
       2014-11-22 09:59:40 +08:00   ❤️ 1
    先说几个优化的地方,既然a表中sessionid不会重复,就用sessionid作为主键。
    a表status加索引,格式tinyint(1)或者enum(1,2)
    a表sendtime加索引
    a表failreason加索引
    b表sessionid加索引
    a2z
        3
    a2z  
       2014-11-22 10:01:28 +08:00
    (t.fail_reason = '' or t.fail_reason is null)

    空值统一用''或者null,要么'' 要么null,这样也能少一个判断快一点
    liprais
        4
    liprais  
       2014-11-22 10:03:38 +08:00 via iPad
    @a2z dml加索引不是更慢
    a2z
        5
    a2z  
       2014-11-22 10:23:01 +08:00
    @liprais

    看情况了。myisam引擎还可以。
    其实你这样完全没必要update a表,join b表做个view就可以了
    caixiexin
        6
    caixiexin  
    OP
       2014-11-22 11:14:19 +08:00
    @a2z 非常感谢!因为这个些表使用有段时间了,而且其他程序也在用,所以不方便改查表为查view
    关于索引的话,经常会被修改的字段是不是不适合加索引?a表的status和sendtime由于用户可能会重播,所以取值经常改动= =
    laoyuan
        7
    laoyuan  
       2014-11-22 11:57:05 +08:00   ❤️ 1
    两个表 sessionid 都做索引, a表 unique,b表 index。建一个中间表t3 俩字段, 主键 sessionid,还有reason

    TRUNCATE TABLE t3;
    INSERT INTO t3 (SELECT sessionid, reason FROM (SELECT sessionid,reason FROM t2 ORDER BY create_time DESC) as t22 GROUP BY sessionid);
    UPDATE t,t3 SET ... WHERE ...

    这点数据量秒秒钟的事。
    caixiexin
        8
    caixiexin  
    OP
       2014-11-24 17:06:06 +08:00
    @laoyuan 照你的方法,确实几秒钟就搞定了,太感谢了:)
    MonkeyDLuffy
        9
    MonkeyDLuffy  
       2016-06-06 09:57:28 +08:00
    @laoyuan 方法非常好用 为啥这样速度能提高这么多 求指点
    laoyuan
        10
    laoyuan  
       2016-06-06 10:57:49 +08:00
    @MonkeyDLuffy 和 LZ 写的完全是两个思路,主要是因为 LZ 没有掌握这一技能: GROUP BY 的时候如何选中某一项最大或最小的那条记录
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   3594 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 34ms · UTC 04:36 · PVG 12:36 · LAX 20:36 · JFK 23:36
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.