mysql大表更新sql的优化策略

页面导航:首页 > 数据库 > Mysql > mysql大表更新sql的优化策略

mysql大表更新sql的优化策略

来源: 作者: 时间:2016-02-03 08:56 【

问题sql背景:项目有6个表的要根据pid字段要写入对应的brand_id字段。但是这个其中有两个表是千万级别的。我的worker运行之后,线上的mysql主从同步立刻延迟了!运行了一个多小时之后

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

<!-- 根据商品id更新品牌id -->
	<update id="updateBrandIdByPid" parameterClass="com.jd.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。更严重的是,这个千万级的表里面有多少个不同的pid,我就要执行多少个这样的sql。

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

<!-- 根据商品id更新品牌id -->
    <update id="updateBrandIdByPid" parameterClass="com.jd.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后,主从没有任何延迟!而且经过监视,短短10分钟就更新了十几万数据,效率是之前的6倍!更重要的是数据库负载均衡,应用健康运行。

Tags:

文章评论

最 近 更 新
热 点 排 行
Js与CSS工具
代码转换工具

<