>데이터 베이스 >MySQL 튜토리얼 >대규모 MYSQL 테이블에서 필드 수정 속도가 느린 문제를 해결하는 방법

대규모 MYSQL 테이블에서 필드 수정 속도가 느린 문제를 해결하는 방법

WBOY
WBOY앞으로
2023-05-26 11:11:502534검색

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

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

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

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

不是所有的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';
Field Type Null Key Default Extra
rating enum('G','PG','PG-13','R','NC-17') YES
G

假设我们需要为那些对电影更加谨慎的父母们增加一个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: rating
Type: enum('G','PG','PG-13','R','NC-17','PG-14')

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

mysql> DROP TABLE film_new;

위 내용은 대규모 MYSQL 테이블에서 필드 수정 속도가 느린 문제를 해결하는 방법의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!

성명:
이 기사는 yisu.com에서 복제됩니다. 침해가 있는 경우 admin@php.cn으로 문의하시기 바랍니다. 삭제