mysql大表更新sql的优化策略

转载 2016年05月31日 00:57:09

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



MySQL5.7性能优化系列(二)——SQL语句优化(3)——使用物化策略优化子查询

优化器使用物化策略(Materialization)来实现更有效的子查询处理。通过生成子查询结果作为临时表,通常在内存中,实现加速查询执行。 MySQL首次需要子查询结果,将该结果实现为临时表。任何随...

SQL语法优化策略

  • 2014年08月21日 11:15
  • 40KB
  • 下载

使用SQL Server DMVs优化索引策略

使用SQL Server DMVs优化索引策略   SQL Server索引的目标是高效,太多太少都是走极端。一旦确定要创建索引,你必须要确保的是负载必须以最小的I/O开销来读取数据。   聚集索引与...

Spark-SQL介绍及优化策略

目前我们在使用SQL引擎时,月级的、天级的、小时级的查询都转到了Spark-SQL,速度及稳定性都有较好的表现。以下对Spark-SQL介绍及优化策略进行总结:...

优化sql语句的策略

本文是对近期sql学习的整理感悟。 1.对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。 2.应尽量避免在 where 子句中使用!=或操作符...

plsql知识梳理-sql优化策略

1  In or的效率说明        IN 的作用相当与OR,如语句: Select * from table1 where tid in (2,3) 和 Select * from table...
  • ddeagle
  • ddeagle
  • 2011年09月12日 21:17
  • 209

sql server 2005 优化策略集合

原文出自:http://bbs.51cto.com/thread-912645-1.html   sql server 2005 优化策略集合共享 1.对查询进行优化,应尽量避免全表扫描,首先应...
  • vae_cn
  • vae_cn
  • 2012年05月10日 11:15
  • 169

sql server优化策略

No.1 索引 索引对于查询效率的作用众所周知,它分为聚集索引和非聚集索引,每个表只能有一个聚集索引,因为它对查询帮助最大,它的价值也就突显出来了,在以下情况下适合用聚集索引 1、含有大量非重复值...

ORACLE SQL 优化策略-1

1. 访问Table的方式  ORACLE 采用两种访问表中记录的方式:  a. 全表扫描  全表扫描就是顺序地访问表中每条记录. ORACLE采用一次读入多个数据块(database bloc...

关于oracle优化的策略等SQL

关于oracle的优化是一个必然要做的动作也是对数据库的必然要做的一个事情,说白了就是对你前期对数据库的没有仔细的check等动作没有做到一个细致性的处理以及没有关注的地方,因而优化是一条很长的roa...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:mysql大表更新sql的优化策略
举报原因:
原因补充:

(最多只允许输入30个字)