mysql表数据量超过1千万时,批量更新相当慢,查询也慢

孤单大眼鱼 2016-09-18 02:31:44
ld_bus_code_list 码值总表
ld_bus_code_management 编码管理表
页面点击分配码值会在编码管理表生成一条记录,并把对应的id更新到码值总表

之前是没问题的,但是ld_bus_code_list这张表的数据量超过1千万就卡死了,

更新语句如下
update ld_bus_code_list set cm_id = 22 where cm_id is null limit 1000000;

大神们帮忙分析一下原因,cm_id 有加索引
...全文
10389 14 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
14 条回复
切换为时间正序
请发表友善的回复…
发表回复
hellocdkjq 2020-06-22
  • 打赏
  • 举报
回复
引用 5 楼 孤单大眼鱼 的回复:
[quote=引用 2 楼 yupeigu 的回复:] 照理null也是会存在索引中的。 为什么要 limit 1000000,不能 limit 100000 试试吗?多运行几次就行了
表总数据量:14000000 执行时间:23秒、update ld_bus_code_list_test set cm_id = 999 where cm_id =0 limit 1000000; 执行时间:2秒、 update ld_bus_code_list_test set cm_id = 999 where cm_id =0 limit 100000; 这样好像并没有作用,是我这样的sql语句不行吗?还是设计得不行。[/quote] ============================= 少了10倍,是2秒,但执行守2*14=28S,没有提升。
q451007453 2017-01-04
  • 打赏
  • 举报
回复


我这个100万数据就不行了。
孤单大眼鱼 2016-09-22
  • 打赏
  • 举报
回复
引用 10 楼 u011575570 的回复:
[quote=引用 9 楼 u012237615 的回复:] [quote=引用 8 楼 u011575570 的回复:]
没处理过这么大的数据量,分表可以解决这个问题吗? [/quote] 分表就是将数据量减少,数据量少了,你这个问题不就解决了[/quote] 对表的操作主要是插入 和 更新 时数据量比较大, 查询也有不过基本都是单挑查询,慢就慢在批量更新
孤单大眼鱼 2016-09-22
  • 打赏
  • 举报
回复
引用 10 楼 u011575570 的回复:
[quote=引用 9 楼 u012237615 的回复:] [quote=引用 8 楼 u011575570 的回复:]
没处理过这么大的数据量,分表可以解决这个问题吗? [/quote] 分表就是将数据量减少,数据量少了,你这个问题不就解决了[/quote] 在网上看了一下分区分表有点凌乱,这是建表语句,帮忙看一下怎么分表,最好写出代码谢谢 CREATE TABLE `ld_bus_code_list_test` ( `id` INT(11) NOT NULL AUTO_INCREMENT COMMENT '逻辑主键', `timestamp_id` BIGINT(20) NULL DEFAULT NULL COMMENT '时间戳(跟码值记录表关联)', `code` VARCHAR(10) NOT NULL DEFAULT '' COMMENT '码值' COLLATE 'utf8_bin', `verify_code` VARCHAR(50) NULL DEFAULT NULL COMMENT '验证码', `cm_id` INT(11) NULL DEFAULT '0' COMMENT '编码id', `business_id` INT(11) NULL DEFAULT NULL COMMENT '企业id', `product_series_id` INT(11) NULL DEFAULT NULL COMMENT '产品系列id', `product_id` INT(11) NULL DEFAULT NULL COMMENT '产品id', `isValid` TINYINT(4) NULL DEFAULT '1' COMMENT '是否有效1有效0无效', `createdate` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', PRIMARY KEY (`id`), UNIQUE INDEX `code` (`code`), INDEX `timestamp_id` (`timestamp_id`), INDEX `cm_id` (`cm_id`) ) COMMENT='码值' COLLATE='utf8_general_ci' ENGINE=InnoDB AUTO_INCREMENT=14920152;
rick-he 2016-09-21
  • 打赏
  • 举报
回复
引用 9 楼 u012237615 的回复:
[quote=引用 8 楼 u011575570 的回复:]
没处理过这么大的数据量,分表可以解决这个问题吗? [/quote] 分表就是将数据量减少,数据量少了,你这个问题不就解决了
孤单大眼鱼 2016-09-20
  • 打赏
  • 举报
回复
引用 8 楼 u011575570 的回复:
没处理过这么大的数据量,分表可以解决这个问题吗?
rick-he 2016-09-20
  • 打赏
  • 举报
回复
到达1千万行的表,,更新数据一般是依赖主键索引的。是不是可以采用分表处理
孤单大眼鱼 2016-09-18
  • 打赏
  • 举报
回复
引用 6 楼 yupeigu 的回复:
[quote=引用 5 楼 u012237615 的回复:] [quote=引用 2 楼 yupeigu 的回复:] 照理null也是会存在索引中的。 为什么要 limit 1000000,不能 limit 100000 试试吗?多运行几次就行了
表总数据量:14000000 执行时间:23秒、update ld_bus_code_list_test set cm_id = 999 where cm_id =0 limit 1000000; 执行时间:2秒、 update ld_bus_code_list_test set cm_id = 999 where cm_id =0 limit 100000; 这样好像并没有作用,是我这样的sql语句不行吗?还是设计得不行。[/quote] 你上面说 就卡死了,可能是别的会话也在操作数据,导致阻塞住了这个update语句,在加上数据量很大,就卡死了。 为了减少被阻塞的可能,建议 把 limit n 这个n尽量减少,比如 1000,或者10000起,并且 及时 commit。 实际上innodb在进行update操作时,除了要记录日志,还要记录 undo信息,所以如果每次更新的数据量特别大,会导致 这另个文件的空间不够用,这个时候如果去扩展空间,就会要很长时间,在加上还有被阻塞的可能,所以问题还是比较复杂的。 [/quote] 嗯,卡死的问题应该是这样引起的,效率问题有没办法解决?20多秒太慢了。
LongRui888 2016-09-18
  • 打赏
  • 举报
回复
引用 5 楼 u012237615 的回复:
[quote=引用 2 楼 yupeigu 的回复:] 照理null也是会存在索引中的。 为什么要 limit 1000000,不能 limit 100000 试试吗?多运行几次就行了
表总数据量:14000000 执行时间:23秒、update ld_bus_code_list_test set cm_id = 999 where cm_id =0 limit 1000000; 执行时间:2秒、 update ld_bus_code_list_test set cm_id = 999 where cm_id =0 limit 100000; 这样好像并没有作用,是我这样的sql语句不行吗?还是设计得不行。[/quote] 你上面说 就卡死了,可能是别的会话也在操作数据,导致阻塞住了这个update语句,在加上数据量很大,就卡死了。 为了减少被阻塞的可能,建议 把 limit n 这个n尽量减少,比如 1000,或者10000起,并且 及时 commit。 实际上innodb在进行update操作时,除了要记录日志,还要记录 undo信息,所以如果每次更新的数据量特别大,会导致 这另个文件的空间不够用,这个时候如果去扩展空间,就会要很长时间,在加上还有被阻塞的可能,所以问题还是比较复杂的。
孤单大眼鱼 2016-09-18
  • 打赏
  • 举报
回复
引用 2 楼 yupeigu 的回复:
照理null也是会存在索引中的。 为什么要 limit 1000000,不能 limit 100000 试试吗?多运行几次就行了
表总数据量:14000000 执行时间:23秒、update ld_bus_code_list_test set cm_id = 999 where cm_id =0 limit 1000000; 执行时间:2秒、 update ld_bus_code_list_test set cm_id = 999 where cm_id =0 limit 100000; 这样好像并没有作用,是我这样的sql语句不行吗?还是设计得不行。
孤单大眼鱼 2016-09-18
  • 打赏
  • 举报
回复
引用 1 楼 autfish 的回复:
is null提交,索引是不起作用的 设置个默认值,然后用默认值判断,如 where cmd_id=-1 cmd_id列如果不能改可以加个辅助字段
我复制了一套表出来,字段默认为0,跟null是没有区别的 表总数据量:14000000 执行时间:23秒、update ld_bus_code_list_test set cm_id = 999 where cm_id =0 limit 1000000; 执行时间:2秒、 update ld_bus_code_list_test set cm_id = 999 where cm_id =0 limit 100000;
孤单大眼鱼 2016-09-18
  • 打赏
  • 举报
回复
我复制了一套表出来,字段默认为0,跟null是没有区别的 执行时间:23秒、update ld_bus_code_list_test set cm_id = 999 where cm_id =0 limit 1000000; 执行时间:2秒、 update ld_bus_code_list_test set cm_id = 999 where cm_id =0 limit 100000;
LongRui888 2016-09-18
  • 打赏
  • 举报
回复
照理null也是会存在索引中的。 为什么要 limit 1000000,不能 limit 100000 试试吗?多运行几次就行了
大雨将至 2016-09-18
  • 打赏
  • 举报
回复
is null提交,索引是不起作用的 设置个默认值,然后用默认值判断,如 where cmd_id=-1 cmd_id列如果不能改可以加个辅助字段

56,940

社区成员

发帖
与我相关
我的任务
社区描述
MySQL相关内容讨论专区
社区管理员
  • MySQL
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧