MYSQL大表改字段慢问题如何解决

对大型表而言,MYSQL的ALTER TABLE操作的性能会成为一个显著的挑战。MYSQL执行大部分修改表结构操作的方法是用新的表结构创建一个空表,从旧表中查出所有数据插入新表,然后删除旧表。如果内存不足且表很大,同时还有很多索引,那么这种操作可能会非常耗时。ALTER TABLE操作通常需要几个小时甚至几天才能完成,这是许多人都经历过的情况。

通常情况下,大多数ALTER TABLE操作会使MYSQL服务停止运行。对常见的场景,能使用的技巧只有两种:

  • 一种是先在一台不提供服务的机器上执行ALTER TABLE操作,然后和提供服务的主库进行切换;

  • MYSQL大表改字段慢怎么办三招解决!

    另外一种技巧就是“影子拷贝”。影子拷贝技巧包括创建一张新表并按照所需的表结构进行操作,然后通过重命名和删除表操作交换两张表的过程。

不是所有的ALTER TABLE操作都会引起表重建。有两种不同的方法可以更改或删除列的默认值,其中一种方法速度很快,而另一种方法则速度较慢。

假如要修改电影的默认租赁期限,从三天改到五天。下面是很慢的方式:

mysql>
ALTER TABLE film modify column rental_duration tinyint(3) not null default 5;

1000次读和1000次插入的操作数量可以从"SHOW STATUS"中得到。换句话说,它将整张表复制到了一张新表中,包括列的数据类型、大小和null属性都没有进行修改。

理论上,MYSQL可以跳过创建新表的吧步骤。表的默认值存储在.frm文件中,因此可以直接编辑该文件而无需修改表本身。尽管这种优化方法可行,但MYSQL目前尚未使用它,因此修改列操作都需要重建表。

另外一种方法是通过ALTER COLUMN操作来改变列的默认值;

mysql>
ALTER TABLE film ALTER COLUMN rental_duration set DEFAULT 5;

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

只修改.frm文件

虽然修改表的.frm文件速度非常快,但 MySQL 有时会在不必要的情况下重新建表,这一点我们可以从上述示例中看出。通过承担一定的风险,可以让MYSQL进行其他类型的修改而无需重建表。

注意 下面要演示的技巧是不受官方支持的,也没有文档记录,并且也可能不能正常工作,采用这些技术需要自己承担风险。>
建议在执行之前首先备份数据!

下面这些操作是有可能不需要重建表的:

  • 移除(不是增加)一个列的AUTO_INCREMENT属性。

  • 增加、移除,或更改ENUM和SET常亮。假如删除的是已在某行中使用过的常量值,查询将会返回一个空字符串。

步骤:

  • 创建一张有相同结构的空表,并进行所需要的修改(例如:增加ENUM常量)。

  • 执行FLUSH TABLES WITH READ LOCK。这将会关闭所有正在使用的表,并且禁止任何表被打开。

  • 交换.frm文件。

  • 执行UNLOCK TABLES 来释放第二步的读锁。

下面以给film表的rating列增加一个常量为例来说明。当前列看起来如下:

mysql>
SHOW COLUMNS FROM film LIKE '
rating'
;
FieldTypeNullKeyDefaultExtraratingenum('
G'
,'
PG'
,'
PG-13'
,'
R'
,'
NC-17'
)YESG

假设我们需要为那些对电影更加谨慎的父母们增加一个PG-14的电影分级:

mysql>
CREATE TABLE film_new like film;

mysql>
ALTER TABLE 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/mysql/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 film like '
rating'
\G

****************** 1. row*********************

Field: ratingType: enum('
G'
,'
PG'
,'
PG-13'
,'
R'
,'
NC-17'
,'
PG-14'
)

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

mysql>
DROP TABLE film_new;


随着业务的增长,MYSQL表中的数据量越来越大,而当你需要修改表字段时,可能会面临极其缓慢的情况,这种情况该怎么办呢?本文将从三个方面为你解答。
一、优化数据库结构
通常情况下,MYSQL表的数据表越大,修改字段所需的时间越长。因此,我们可以考虑优化数据库结构来缩短处理时间。具体方法包括:规范化数据表、分表分区、减少冗余字段等。这些操作可以在一定程度上降低数据库的存储空间,提高查询效率,并减少数据处理时间,从而为修改字段创建更好的基础。
二、适当增加硬件配置
如果数据库表数据量巨大,而且客户端并发访问较多,那么单个数据库服务器可能无法完全满足需求。优化措施之一是适当提高硬件配置,如增加内存、加速硬盘、将数据表移动到SSD等。这样,即使数据量很大,也能够保证修改字段的速度。
三、优化SQL语句
优化SQL语句可以减少执行查询的时间,从而加快修改字段的速度。具体方法包括:使用索引、优化查询条件、减少子查询、减少排序等。此外,SQL语句不同的执行计划也会导致不同的执行效率,因此建议使用mysqlprofiler等工具来优化执行计划。
总结
MYSQL大表修改字段所需的时间与数据量、硬件配置、SQL语句等都有关系。因此,我们可以从优化数据库结构、适当增加硬件配置、优化SQL语句三个方面入手,来解决这个问题。如果你遇到同样的问题,不妨试试这三个方法,相信一定会有所帮助。