问题:对于百万级或千万级的大表,如果需求修改表结构。要怎么做??
1)一般不建议修改大表的数据结构,可以采用扩展表或其他办法来解决业务问题。能不改就不改;
2)直接 ALTER TABLE 肯定是不行的~,ALTER TABLE 会锁表,会影响业务的正常运行。
注:即使加了ALGORITHM = INPLACE, LOCK = NONE选项,不锁表。对于大表的操作 ALTER TABLE 还是相当耗时、耗性能的,不建议直接使用这个操作。
ALTER TABLE table_name ADD COLUMN field_name INT (11) UNSIGNED NULL DEFAULT 0 COMMENT '新字段', ALGORITHM = INPLACE, LOCK = NONE;
不能直接修改表的话,那要怎么做呢??下面是我最近更新线上某大表表结构的操作,内容仅供参与~
一、创建副表、修改副表表结构
CREATE TABLE new_table LIKE old_table; ALTER TABLE new_table ADD COLUMN field_name INT (11) UNSIGNED NULL DEFAULT 0 COMMENT '新字段' AFTER xxx_field;
二、备份旧表数据
大数据的备份建议用 into outfile 命令来操作
SELECT * FROM old_table INTO OUTFILE '/temp/old_table_backup.txt';
1)先用 SHOW VARIABLES LIKE 'secure%'; 命令看一下有没有 into outfile 和 load data 操作权限;没有的话,得改一下mysql的配置;
注:当secure_file_priv,=null时表示没有outfile和infile的权限,=temp(目录名)时表示outfile只能输出到这个目录,=''时,outfile可以输出到任意目录~
2)上面命令有可能会报 ERROR 1045 (28000): Access denied for user 'xxx'@'%' (using password: YES) 错误;原因是当前mysql用户没有服务器目录的写权限(上面代码是写到/temp/目录里);
注:能使用 outfile 命令的尽量使用 outfile 命令,outfile命令的性能消耗要低一点
如果没有权限或者不想备份到服务器里,那怎么办呢?我们可以用本地的mysqldump工具远程连接导出数据到本地
mysqldump -h rr-xxxxxxxxxx.mysql.rds.aliyuncs.com -u webuser -t -f -p --set-gtid-purged=off --default-character-set=utf8 --skip-triggers --skip-lock-tables prod_xxx old_table > D:/backup/old_table.sql
注:-t 表示只要数据,不要结构;-f 表示忽略错误;--skip-triggers 表示跳过触发器,--skip-lock-tables 表示不要锁表。
mysqldump 命令选项详情:https://help.aliyun.com/knowledge_detail/41732.html?spm=a2c4g.11186631.2.13.2c047e53mfgl55
导出的时候,如果有从库的话,要用从库导出
三、删除新表索引(很重要)
带索引写入会很慢、性能消耗高,要先删除索引;网上用的 disable keys(禁用索引)亲测无效,还是删除索引吧。导入数据之后再重建索引;
ALTER TABLE `new_table` DROP INDEX `idx_xxx1`, DROP INDEX `idx_xxx2`, DROP INDEX `idx_xxx3`;
四、旧表数据导入到新表(如果是dump到本地的,本地建副表-》删除索引-》source导入数据-》select ... outfile-》得到备份数据文件)
登录远程主库(有写入权限)
mysql -h rm-xxxx.mysql.rds.aliyuncs.com -u webuser -p
set names utf8; use prod_xxx;
登录并选择好数据库后,现在导出数据(这是导入到线上的新表,不是本地的~~)
load data local infile 'D:/backup/old_table.txt' ignore into table new_table(field1,field2...);
注:注意要加上 local 关键字,不然可能会报错;
load data 命令详情说明:https://help.aliyun.com/document_detail/127794.html?spm=5176.13910061.sslink.19.58d45a49ChoPv5
五、导入成功后,重新索引
ALTER TABLE `new_table` ADD INDEX `idx_xxx1` (`xxx1`) USING BTREE;
ALTER TABLE `new_table` ADD INDEX `idx_xxx2` (`xxx2`) USING BTREE;
ALTER TABLE `new_table` ADD INDEX `idx_xxx3` (`xxx3`) USING BTREE;
建议分开建多个索引。当然一次性创建多个也是可以的,可能会慢一点~
六、停止旧表的写入
停止写入或找一个写入少的时间
七、处理差异数据导入到新表
在我们处理数据的这段时间内,又有新数据写入到报旧表
INSERT INTO new_table (field1, field2...) SELECT field1, field2... FROM old_table where id>=xxx
八、数据表重命名
ALTER TABLE old_table RENAME old_table_bak; ALTER TABLE new_table RENAME old_table;
旧表改一个临时名,新表改回旧表的表名~
到此,大表的结构修改就完成了。(已改临时名的旧表看情况可以删除掉~)完。