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

[孤单大眼鱼]

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 有加索引

[大雨将至]

is null提交，索引是不起作用的 设置个默认值，然后用默认值判断，如 where cmd_id=-1 cmd_id列如果不能改可以加个辅助字段

[LongRui888]

照理null也是会存在索引中的。 为什么要 limit 1000000，不能 limit 100000 试试吗？多运行几次就行了

[孤单大眼鱼]

我复制了一套表出来，字段默认为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;

[孤单大眼鱼]

引用 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;

[孤单大眼鱼]

引用 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语句不行吗？还是设计得不行。

[LongRui888]

引用 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信息，所以如果每次更新的数据量特别大，会导致 这另个文件的空间不够用，这个时候如果去扩展空间，就会要很长时间，在加上还有被阻塞的可能，所以问题还是比较复杂的。

[孤单大眼鱼]

引用 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多秒太慢了。

[rick-he]

到达1千万行的表，，更新数据一般是依赖主键索引的。是不是可以采用分表处理

[孤单大眼鱼]

引用 8 楼 u011575570 的回复:

没处理过这么大的数据量，分表可以解决这个问题吗？

[rick-he]

引用 9 楼 u012237615 的回复:

[quote=引用 8 楼 u011575570 的回复:]

没处理过这么大的数据量，分表可以解决这个问题吗？ [/quote] 分表就是将数据量减少，数据量少了，你这个问题不就解决了

[孤单大眼鱼]

引用 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;

[孤单大眼鱼]

引用 10 楼 u011575570 的回复:

[quote=引用 9 楼 u012237615 的回复:] [quote=引用 8 楼 u011575570 的回复:]

没处理过这么大的数据量，分表可以解决这个问题吗？ [/quote] 分表就是将数据量减少，数据量少了，你这个问题不就解决了[/quote] 对表的操作主要是插入 和 更新 时数据量比较大， 查询也有不过基本都是单挑查询，慢就慢在批量更新

[q451007453]

我这个100万数据就不行了。

[hellocdkjq]

引用 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,没有提升。