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



相关文章推荐

MySQL 对于千万级的大表要怎么优化

知乎讨论:https://www.zhihu.com/question/19719997 48 个回答 默认排序 ...

数据库优化<二>SQL优化之SELECT优化 ——mysql内部优化策略

数据库的瓶颈经常出现在查询 语句中,当出现这样的问题时,我们一般的步骤是查看是否运用了正确的 索引, 这个可以通过explain sql statement来查看,找到对应的字段,合理的索引将会增加你...

MySQL 对于千万级的大表要怎么优化? - MySQL - 知乎

很多人第一反应是各种切分;我给的顺序是: 第一优化你的sql和索引; 第二加缓存,memcached,redis; 第三以上都做了后,还是慢,就做主从复制或主主复制,读写分离,可以在应用...

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

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

sql优化策略

  • 2013-05-11 15:00
  • 3.07MB
  • 下载

SQL语法优化策略

  • 2014-08-21 11:15
  • 40KB
  • 下载

sql server优化策略

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

ORACLE SQL 优化策略-1

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

ORACLE SQL 优化策略-2

20. 联接列  对于有联接的列,即使最后的联接值为一个静态值,优化器是不会使用索引的。我们一起来看一个例子,假定有一个职工表(employee),对于一个职工的姓和名分成两列存放(FIRST_NA...

sql server 2005 优化策略集合

原文出自:http://bbs.51cto.com/thread-912645-1.html   sql server 2005 优化策略集合共享 1.对查询进行优化,应尽量避免全表扫描,首先应...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:深度学习:神经网络中的前向传播和反向传播算法推导
举报原因:
原因补充:

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