MySQL之高性能alter table操作

一、alter table 原理

MySQL的alter table操作的性能对大表来说,是一个大问题,为什么呢?
我们来看看alter table的底层原理,大多数情况下(有的时候未必会重建表),当我们执行一个alter table操作后,其底层会执行如下几个操作:

  1. 用一个新的结构创建一个空表;
  2. 从旧表中查出所有数据插入新表;
  3. 然后删除旧表。

当数据表特别大的时候,这样的操作严重影响了效率,因此,在实际开发中,我们应该避免这样的耗时操作,采用高效的操作。
对于常见的场景,我们能使用的一般有两种方法:

  1. 一种是先在一台不提供服务的机器上执行alter table操作,然后和提供服务的主库进行切换;
  2. 另一种技巧是影子拷贝;

影子拷贝:用要求的表结构创捷一张和源表无关的表,然后通过重命名和删表操作交换两张表。
影子拷贝举例:

mysql> create table new_table like old_table; #创建一张和原表一样结构的表
mysql> rename table old_table to tem_table,new_table to old_table; #重命名表名
mysql> drop table if exists tem_tablel; #删除旧表

但是我们考虑这样一种情况,如果我们只是简单的该表某一列的默认值等情况,那么我们也要赋值整个表的话,那是不是很繁琐?如以下代码语句(修改默认值为5):

mysql> alter table table1
   -> modify column col1 tinyint(3) not null default 5;

这条语句会拷贝整个表到一个新表,但是就是做一个修改默认值的操作,其他都没有改变。
理论上,我们应该跳过建表的和复制操作,直接修改字段属性即可,这样将会大大加快了整个执行效率。

二、修改.frm文件

我们知道,表的结构是存储在.frm文件中,如果我们能直接修改这个文件,而不必该表表本身,然而,在MySQL中,所有的modify column都会导致表重建。
但是alter column 命令却可以修改.frm文件,因此,如上的sql语句可以改写为如下:

mysql> alter table table1
   -> alter column col1 tinyint(3) not null default 5;

这个语句直接修改.frm文件,不涉及表数据,所以,操作非常快。

三、扩展.frm文件的操作

注意对.frm的操作是比较危险的操作,建议操作前,先对原.frm文件做一个备份。虽然快速,但是危险不推荐。
为了实现高性能的sql,很多时候,我们就是简单的改变表结构的一些信息,不需要对整个表的大量数据进行操作,因此,我们可以以.frm文件作为切入点,直接操作
基本的技术是为想要的表结构创建一个新的.frm文件,然后用它替换掉已经存在的那张表的.frm文件,主要步骤如下:

  1. 创建一张有相同结构的空表,并进行修改(例如增加enum常量);
  2. 执行 flush with read lock;命令。(关闭所有正在使用的表,并禁止任何表被打开)
  3. 交换.frm文件;
  4. 执行unlock tables;命令。(释放第二步的读锁)

3.1 举例分析

下面以给sakila. film表的rating 列增加一一个常量为例来说明。当前列看起来如下::
在这里插入图片描述
假设我们需要增加一个PG-14的分级:

mysql> CREATE TABLE sakila.film new LIKE sakila.film;
mysql> ALTER TABLE sakila.film_new
   -> MODIFY COLUMN rating ENUM('G','PG','PG-13','R' ,'NC-17', 'PG-14')
   -> DEFAULT 'G' ;
mysql> FLUSH TABLES WITH READ LOCK;

注意,我们是在常量列表的末尾增加一个新的值。如果把新增的值放在中间,例如PG-13之后,则会导致已经存在的数据的含义被改变:已经存在的R值将变成PG-14,而已经存在的NC-17将成为R,等等。接下来用操作系统的命令交换.frm文件:

/var/lib/mysq1/sakila# mv film.frm film_tmp.frm
/var/lib/mysql/sakila# mv film_new.frm film.frm
/var/lib/mysql/sakila# mv film_tmp.frm film_new.frm

再回到MySQL命令行,现在可以解锁表并且看到变更后的效果了:

mysql> UNLOCK TABLES;
mysql> SHOW COLUMNS FROM sakila. film LIKE rating'\G

在这里插入图片描述

最后需要做的是删除为完成这个操作而创建的辅助表:

mysql> DROP TABLE sakila.film_new;
  • 0
    点赞
  • 2
    收藏
    觉得还不错? 一键收藏
  • 2
    评论
MySQL是一种开源的关系型数据库管理系统,它支持多种操作系统,并且广泛应用于Web应用程序的后端数据存储。MySQL的DDL(Data Definition Language)是用于定义和管理数据库结构的基础操作。 以下是MySQL中DDL的基础操作: 1. 创建数据库:使用CREATE DATABASE语句可以创建一个新的数据库。例如,CREATE DATABASE mydatabase; 2. 删除数据库:使用DROP DATABASE语句可以删除一个已存在的数据库。例如,DROP DATABASE mydatabase; 3. 创建表:使用CREATE TABLE语句可以创建一个新的数据表。在CREATE TABLE语句中,需要指定表名和表的列及其属性。例如,CREATE TABLE mytable (id INT, name VARCHAR(50)); 4. 删除表:使用DROP TABLE语句可以删除一个已存在的数据表。例如,DROP TABLE mytable; 5. 修改表结构:使用ALTER TABLE语句可以修改已存在的数据表的结构,包括添加、修改和删除列等操作。例如,ALTER TABLE mytable ADD COLUMN age INT; 6. 添加主键:使用ALTER TABLE语句可以为数据表添加主键约束,以确保每行数据的唯一性。例如,ALTER TABLE mytable ADD PRIMARY KEY (id); 7. 添加外键:使用ALTER TABLE语句可以为数据表添加外键约束,以确保与其他表的关联完整性。例如,ALTER TABLE mytable ADD FOREIGN KEY (customer_id) REFERENCES customers(id); 8. 创建索引:使用CREATE INDEX语句可以为数据表创建索引,以提高查询性能。例如,CREATE INDEX idx_name ON mytable (name); 这些是MySQL中DDL的基础操作,通过这些操作可以定义和管理数据库的结构。如果你有更具体的问题或者需要了解更多高级的DDL操作,请告诉我。
评论 2
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值