大数据量表修改字段类型 mysql 大数据表加字段

转载

mob64ca140caeb2 2024-01-30 03:36:39

文章标签 大数据量表修改字段类型 mysql mysql 数据库 java 字段 文章分类 MySQL 数据库

文章目录

如何在MySQL数据库中给表加字段

直接加字段
复制原表结构加字段后拷贝数据

拓
拓2

之前淘宝提供的是nick用于淘宝的渠道的单店铺/多店铺的会员识别和打通
现在淘宝不在提供nick。对于单店铺淘宝提供ouid(淘宝提供nick转ouid的接口(这个接口淘宝可能仅提供一段时间)),该ouid在单店铺中唯一,保证在单店铺中的用户识别
对于品牌多店铺的情况，提供ouid转omid的转化接口，用于跨店识别同一用户做用户管理及分析。

这就对原始的数据库提出了新的要求，需要使用ouid以及omid作为用户识别的条件，需要在表中加字段ouid和omid。

如何在MySQL数据库中给表加字段

直接加字段

如果对于非线上环境的表加字段，这时候不考虑有新数据的引入而导致的数据不一致，所以可以直接添加表的字段，使用SQL语句

ALTER TABLE tmall_customer ADD (ouid VARCHAR(255),omid VARCHAR(255));

可以直接改变表的结构,对于数据量不大,且不是线上项目的表可以采取这种方式

复制原表结构加字段后拷贝数据

对于直接加字段的方式，一般情况下仅仅适用于非线上的项目，对于一些已经上线的项目，如果我们采用直接加字段，可能会因为表的数据量过大，而导致锁表，最终导致其他查询等不可用，影响线上项目。

这时就需要使用第二种方式，复制原表的表结构，加字段之后再拷贝数据。

① 创建一个临时的新表，首先复制旧表的结构（包含索引）

create table new_table like old_table;

② 给新表加上新增的字段，注意，此时新表是空表，加字段很快；

③ 把旧表的数据复制过来

insert into new_table(filed1,filed2) select filed1,filed2 from old_table;

④ 删除旧表，重命名新表的名字为旧表的名字

注意点:

需要注意的是:在执行第三步时，也是一个耗时的操作，此时为了避免新数据进来，最好停机操作，另外根据阿里的Java开发手册，最好在数据库的字段中添加记录的最后修改时间，可以根据该最后修改时间来修补该期间新插入的数据，保证其数据的一致性。

比如最近这个项目，他的会员数是4千万+，因此选择在晚上停机操作加字段。

拓

为何会锁表
首先我们了解一下数据库查询机制，首先我们用工具进行连接查询时，会创建一个connection，此时数据库会将查询语句解析成一棵“树”，各个引擎底层的结构不一样，mysql的话在innodb用的是b-tree，俗称b+树，那么再到执行器进行处理，最后返回给客户端。
锁表的原因：
当多个连接（数据库连接）同时对一个表的数据进行更新操作，那么速度将会越来越慢，持续一段时间后将出现数据表被锁的现象，从而影响到其它的查询及更新。尤其当我们使用循环的时候更加容易出现锁表这种情况，导致其他连接无法读写此表。