大树叶 技术专栏

骨灰攻城狮 架构师

mysql大表更新sql的优化策略

问题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倍!更重要的是数据库负载均衡,应用健康运行。



阅读更多
文章标签: mysql
个人分类: 数据库
上一篇MySQL 性能优化的最佳20多条经验分享
下一篇mysql优化之insert/delete/update
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

关闭
关闭