mysql大表更新sql的优化策略
来源:互联网 发布:7z解压软件 linux 编辑:程序博客网 时间:2024/04/27 21:44
问题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的优化策略
- mysql大表更新sql的优化策略
- Mysql优化-大数据量下的分页策略
- MySQL 建表的优化策略
- MySQL 建表的优化策略
- SQL 语句的优化策略
- 优化sql语句的策略
- MySql的一些优化策略
- 千万级大数据的Mysql数据库SQL语句优化
- android 的listview 3大优化策略
- android 的listview 3大优化策略
- Mysql大表优化
- MySQL 大表的count()优化
- MySQL 建表的优化策略(转)
- 【转】MySQL 建表的优化策略 小结
- mysql 的sql优化
- mysql的sql优化
- MySQL大数据量分页SQL语句优化
- 【Leetcode】Range Sum Query 2D - Immutable
- 解决Delphi的TAnimate控件的Common AVI在Vista以后版本中不能正常工作的问题
- Oracle 数据库基本概念
- 【Leetcode】Maximal Square
- 长周末
- mysql大表更新sql的优化策略
- 【Leetcode】Unique Binary Search Trees II
- javaWeb使用siteMesh和adminLTE美观CRM系统搭建
- linux 解决man命令输出到文档打开乱码问题
- 【Leetcode】3Sum
- Archlinux MySQL PHP phpmyadmin Apache PHP-Apache Install
- android studio如何修改包名
- 【Leetcode】Permutation Sequence
- Hadoop之——hadoop2.5.2eclipse插件编译