MySQL海量数据单一字段更新

前言

昨天发了一篇MySQL中索引对千万级数据查询效率优化实践的博文

文章链接：https://blog.csdn.net/Return_head/article/details/90699140

所以手上有着5000万的数据，很多以前想做的实验也有机会去尝试了。今天上课突然想起来一篇数据库的面试题。

大概是说：某公司有一张亿级的用户表，用户表有一个积分字段。这个字段会在每年新年凌晨0点的时候进行清零，要求给出解决的思路。

大概记得有两种思路：一是使用UPDATE语句直接遍历修改每一条记录的积分字段，将其修改为0；二是删除该字段，然后重新添加积分字段，并且设置该字段默认值为0。

答案是说第二种方式更优，但具体能优化多少并未说明。所以今天特地进行一下实验。

 

实验

1.使用test_database库

2.建立user表

同样，由于是进行实验，所以数据库引擎推荐使用MyISAM。批量插入更快。

3.创建存储过程

4.调用存储过程

5.查询所有记录数量

6.通过UPDATE将user_exp字段修改为0

7.删除user_exp字段

8.重新添加user_exp字段，并设置默认值为0

 

结论

通过以上实验不难发现，第二种方法在效率上要比第一种方法快出一倍左右。所以对于海量数据而言，修改操作若是重复性的，不推荐直接利用UPDATE进行修改，采用DDL语句修改表结构不失为一种更好的解决方案。

下面给出以上实验的SQL语句，方便大家进行测试。

#使用test_database库
USE test_database;

#创建user表
CREATE TABLE user(
    id bigint(10) PRIMARY KEY NOT NULL AUTO_INCREMENT,
    user_name varchar(10) DEFAULT NULL,
    user_exp int DEFAULT 0
)ENGINE=MyISAM AUTO_INCREMENT=100000001 DEFAULT CHARSET=utf8;

#定义存储过程userproc
DELIMITER // 
CREATE PROCEDURE userproc()
BEGIN
    DECLARE num int;
    SET num=1;
    WHILE num <=10000000 DO
        INSERT INTO user(user_name,user_exp) VALUES(num,num);
        SET num=num+1;
    END WHILE;
END //
DELIMITER ; 

#调用存储过程userproc
CALL  userproc();

#user表计数
SELECT COUNT(*) FROM user;

#修改所有记录user_exp字段为0
UPDATE user SET user_exp=0;

#删除user_exp列
ALTER TABLE user DROP COLUMN user_exp;

#新建user_exp列，并修改其默认值为0
ALTER TABLE user ADD COLUMN user_exp int DEFAULT 0;