文章详情

短信预约-IT技能 免费直播动态提醒

请输入下面的图形验证码

提交验证

短信预约提醒成功

MYSQL大表改字段慢问题的解决

2023-03-21 16:47

关注

mysql如何加快大表的ALTER TABLE操作速度

MYSQL的ALTER TABLE操作的性能对大表来说是个大问题。MYSQL执行大部分修改表结构操作的方法是用新的表结构创建一个空表,从旧表中查出所有数据插入新表,然后删除旧表。这样操作可能需要花费很长时间,如果内存不足而表又很大,而且还有很多索引的情况下尤其如此。许多人都有这样的经验,ALTER TABLE操作需要花费数个小时甚至数天才能完成。

一般而言,大部分ALTER TABLE操作将导致MYSQL服务中断。对常见的场景,能使用的技巧只有两种:

不是所有的ALTER TABLE操作都会引起表重建。例如,有两种方法可以改变或删除一个列的默认值(一种方法很快,另一种则很慢)。

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

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

SHOW STATUS显示这个语句做了1000次读和1000次插入操作。换句话说,它拷贝了整张表到一张新表,甚至列的类型、大小和可否为null属性都没有改变。

理论上,MYSQL可以跳过创建新表的吧步骤。列的默认值实际上存在表的.frm文件中,所以可以直接修改这个文件而不需要改动表本身。然而MYSQL还没有采用这种优化的方法,所以MODIFY COLUMN操作都将导致表重建。

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

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

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

只修改.frm文件

从上面的例子我们看到修改表的.frm文件是很快的,但MYSQL有时候会在没有必要的时候也重建表。如果愿意冒一些风险,可以让MYSQL做一些其他类型的修改而不用重建表。

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

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

步骤:

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

mysql> SHOW COLUMNS FROM film LIKE 'rating';
FieldTypeNullKeyDefaultExtra
ratingenum('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: rating
Type: enum('G','PG','PG-13','R','NC-17','PG-14')

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

mysql> DROP TABLE film_new;

到此这篇关于MYSQL大表改字段慢问题的解决的文章就介绍到这了,更多相关MYSQL大表改字段慢内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们!

阅读原文内容投诉

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

软考中级精品资料免费领

  • 历年真题答案解析
  • 备考技巧名师总结
  • 高频考点精准押题
  • 2024年上半年信息系统项目管理师第二批次真题及答案解析(完整版)

    难度     813人已做
    查看
  • 【考后总结】2024年5月26日信息系统项目管理师第2批次考情分析

    难度     354人已做
    查看
  • 【考后总结】2024年5月25日信息系统项目管理师第1批次考情分析

    难度     318人已做
    查看
  • 2024年上半年软考高项第一、二批次真题考点汇总(完整版)

    难度     435人已做
    查看
  • 2024年上半年系统架构设计师考试综合知识真题

    难度     224人已做
    查看

相关文章

发现更多好内容

猜你喜欢

AI推送时光机
位置:首页-资讯-数据库
咦!没有更多了?去看看其它编程学习网 内容吧
首页课程
资料下载
问答资讯