关闭

mysql大表更新sql的优化策略

标签: mysql
264人阅读 评论(0) 收藏 举报
分类:

问题sql背景:项目有6个表的要根据pid字段要写入对应的brand_id字段。但是这个其中有两个表是千万级别的。我的worker运行之后,线上的mysql主从同步立刻延迟了!运行了一个多小时之后,居然延迟到了40分钟,而且只更新了十几万行数据。问题sql如下:

<!-- 根据商品id更新品牌id -->
<update id="updateBrandIdByPid" parameterClass="com.**.chat.worker.domain.param.UpdateBrandIdParam">
	UPDATE $tableName$
	SET brand_id = #newBrandId#
	WHERE pid = #pid#
		AND brand_id = 0
</update>

    项目组的mysql专家帮我分析了下,因为pid字段没有索引,mysql引擎要逐行扫描出与传入的pid值相等的列,然后更新数据,也就是要扫描完1000W+行磁盘数据才能执行完这个sql。因为是update操作,没有用到索引,于是导致这个sql会占用表锁,其它的sql只能等这个sql执行完成之后才能开始执行。更严重的是,这个千万级的表里面有多少个不同的pid,我就要执行多少个这样的sql。

    同事给我的建议的根据id字段进行sql代码层次的横向分表。每次更新1000行的数据,这样mysql引擎就不用每次在扫全表了,数据库压力是之前的万分之一。而且id作为主键,是有索引的,这个时候占用的是这1000行数据的行级锁,不会影响其它的数据。有索引能大大优化查询性能,优化后的sql如下:

<!-- 根据商品id更新品牌id -->
<update id="updateBrandIdByPid" parameterClass="com.**.chat.worker.domain.param.UpdateBrandIdParam">
UPDATE $tableName$
SET brand_id = #newBrandId#
WHERE pid = #pid#
    AND brand_id = 0
    AND id BETWEEN #startNum# AND #endNum#
</update>

    仅仅用了id限区间的语句,将一个千万级的大表代码层次上进行横向切割。重新上线worker后,mysql主从没有任何延迟!而且经过监控,短短10分钟就更新了十几万数据,效率是之前的6倍!更重要的是数据库负载均衡,应用健康运行。



0
0
查看评论

mysql大表更新sql的优化策略

    问题sql背景:项目有6个表的要根据pid字段要写入对应的brand_id字段。但是这个其中有两个表是千万级别的。我的worker运行之后,线上的mysql主从同步立刻延迟了!运行了一个多小时之后,居然延迟到了40分钟,而且只更新了十几万行数据。问题sql如下:...
  • bruce128
  • bruce128
  • 2013-12-19 21:38
  • 10652

千万级的大表!MySQL这样优化更好

对于一个千万级的大表,现在可能更多的是亿级数据量,很多人第一反应是各种切分,可结果总是事半功倍,或许正是我们优化顺序的不正确。下面我们来谈谈怎样的优化顺序可以让效果更好。 MySQL数据库一般都是按照下面的步骤去演化,成本也是由低到高: 1/ SQL优化 1. 避免使用select * ...
  • bjash
  • bjash
  • 2017-02-21 10:14
  • 306

20亿与20亿表关联优化方法(超级大表与超级大表join优化方法)

记得5年前遇到一个SQL,就是一个简单的两表关联,SQL跑了差不多一天一夜,这两个表都非常巨大,每个表都有几十个G,数据量每个表有20多亿,表的字段也特别多。 相信大家也知道SQL慢在哪里了,单个进程的PGA 是绝对放不下几十个G的数据,这就会导致消耗大量temp tablespace,SQL慢就...
  • robinson1988
  • robinson1988
  • 2016-02-27 21:57
  • 13591

详解MySQL大表优化方案

当MySQL单表记录数过大时,增删改查性能都会急剧下降,可以参考以下步骤来优化:单表优化、字段、索引、查询SQL、引擎等。
  • yin767833376
  • yin767833376
  • 2016-08-04 18:00
  • 2960

MySQL 单表分页 Limit 性能优化

主要针对记录非常多的表常用分页sql语句:select * from product limit start, count当起始页较小时,查询没有性能问题,我们分别看下从10, 100, 1000, 10000开始分页的执行时间(每页取20条), 如下:select * from product l...
  • u013372487
  • u013372487
  • 2016-12-06 18:48
  • 684

sparksql优化1(小表大表关联优化 & union替换or)

----原语句(运行18min) INSERT into TABLE schema.dstable SELECT bb.ip FROM (SELECT ip, sum(click) click_num, round(sum(click)/sum(imp),4) user_click_r...
  • xjping0794
  • xjping0794
  • 2017-11-07 18:03
  • 284

SQL高级优化之常用的优化策略-1(The Return Of The King)

# ########################################### # 索引相关 # ########################################### – 查询(或更新,删除,可以转换为查询)没有用到索引     这是...
  • GreatElite
  • GreatElite
  • 2014-06-10 22:54
  • 1434

MYSQL一次千万级连表查询优化(一)

概述:交代一下背景,这算是一次项目经验吧,属于公司一个已上线平台的功能,这算是离职人员挖下的坑,随着数据越来越多,原本的SQL查询变得越来越慢,用户体验特别差,因此SQL优化任务交到了我手上。 这个SQL查询关联两个数据表,一个是攻击IP用户表主要是记录IP的信息,如第一次攻击时间,地址,IP...
  • Tim_phper
  • Tim_phper
  • 2017-10-25 18:02
  • 1152

Mysql 优化之小表驱动大表

  • hamov
  • hamov
  • 2017-08-16 23:51
  • 674

Sql server 列更新,值来自于另一表的列

在A中新增了一列后,值都为null,将A表复制到Excel中并修改新增列值,然而将excel导入Sql server为独立表,再用以下语句实现新增列值的更新。 update A set A.Pinyin = B.Pinyin From B where A.id= B.id
  • handsometone1982
  • handsometone1982
  • 2015-01-14 11:23
  • 1290
    个人资料
    • 访问:738771次
    • 积分:11583
    • 等级:
    • 排名:第1581名
    • 原创:388篇
    • 转载:610篇
    • 译文:1篇
    • 评论:35条
    最新评论