Mysql如何高效ALTER TABL

ALTER TABLE 缺点

MySQL 的ALTER TABLE 操作的性能对大表来说是个大问题。

MySQL MySQL 执行大部分修改表结构操作的方法是用新结构的 创建一个,空表从旧表中查出所有数据插入,新表然后删除旧。表这样操作可能需要花费很长,时间 如内果存不足而表又很大而且还有很多索引的情况下尤其如此。

此许多人 都有这样的经验,ALTER ALTER TABLE 操作需要花费数个小时其
数天才能完成。

对于MySQL 5.5 及更早版本
一般而言大部分ALTER TABLE 操作将导致MySQL 服务中断,常用的技巧有两种:

  • 一种是先在台一不提供服务的机上器执行ALTER TABLE 操作,然 后和提供服
    务的主库进行切换。
  • 另外 一技巧种 是““影子拷贝""。影拷贝子 的技巧是用老的表结构创建一张和源表一样表结构的新表,然后通过重命名和删表操作交换两张表

不是有所的ALTER TABLE 操作都会引起表重建的

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

  • 移除((不 是增加) 一个列的AUTO_INCREMENT 属性。
  • 增加、移除, 或更改ENUM 和SET 常量。如 果移除的是已经有行数据用到其值的常量, 查询将返回会 一个空字串值。

例:修改字段长度

假如要修改电影的默认租赁 期,限从三 天改到五天。下面传统方法

mysql> ALTER TABLE sakila.film 
-> MODIFY COLUMN rental_duration TINYINT(3) NOT NULL DEFAULT 5; 

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

当表数据比较大时,就会特别慢。

如何优化

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

可过如下ALTER COLUMN 操作来改变列的默认值

mysql> ALTER TABLE sakila.film 
-> ALTER COLUMN rental_duration SET DEFAULT 5; 

这个语句会直接修改. frm 文件。所以,这个操作是非常快的。

例: 给表的某列增加一个常量

比如:以给sakila.flim 表的rating 列增加一个常量

SHOW COLUMNS FROM sakila.film LIKE ‘rating’; 

在这里插入图片描述

rating 增加一个PG-14 的电影分级:

一般做法

ALTER TABLE sakila.film MODIFY COLUMN rating ENUM('G','PG','PG-13','PG-14','R','NC-17') DEFAULT 'G';

表数据量大得情况下,会特别慢,还可能长时间影响对外服务。

优化方案

  1. 创建一张有相同结构 的空表,并进行需要所的修改(例如增加ENUM 常量) 。
  2. 执行FLUSH TABLES WITH READ LOCK LOCK。这将会关闭所有正在使用的表 ,并且禁止任何表被打开。
  3. 交换 .frm 文件.
  4. 执行 UNLOCK TABLES 来释放第2步得读锁

备份数据

MUST: 一定要备份数据

执行命令创建表、锁表命令

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 之后则导致会已存在经 数据的的含义被改变: 已存在经 的了值将变成PG-14 ,而已存在经 的NC-17 将成为RR,等

交换.frm 文件

接下来用操作系统的命令交换.frm 文件

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

解锁、查看表字段

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

在这里插入图片描述

删除辅助表

确认没问题删除辅助表

mysql> DROP TABLE sakila.film_new ; 

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:/a/790207.html

如若内容造成侵权/违法违规/事实不符,请联系我们进行投诉反馈qq邮箱809451989@qq.com,一经查实,立即删除!

相关文章

剪画小程序:父辈的照片模糊不清晰,怎么变清晰!

在我们的记忆深处,父辈和爷爷辈的影像总是伴随着一些模糊不清晰的老照片。这些照片或许没有现代摄影技术的高清与细腻,但它们却承载着无比厚重的岁月痕迹和情感温度。 每一张模糊的老照片,都是时光的切片。它们可能是父辈年轻时的纯真笑容&am…

redis批量删除keys,用lua脚本。

文章目录 现象解决方法 现象 系统报错: misconf redis is configured to save ....后查看机器内存。 是内存满了,需要删除其中的key 解决方法 (1) 编写一个脚本,放在redis-cli.exe同一个目录 (2) 脚本内容如下: -- 使用Lua脚…

信息学奥赛初赛天天练-43-CSP-J2020基础题-链表、连通图、2进制转10进制、栈、队列、完全二叉树、哈希表应用

PDF文档公众号回复关键字:20240710 2020 CSP-J 选择题 单项选择题(共15题,每题2分,共计30分:每题有且仅有一个正确选项) 7.链表不具有的特点是() A.可随机访问任一元素 B.不必事先估计存储…

关于前端数据库可视化库的选择,vue3+antd+g2plot录课计划

之前:antdv 现在:g2plot https://g2plot.antv.antgroup.com/manual/introduction 录课内容:快速入门 图表示例: 选择使用比较广泛的示例类型,录课顺序如下: 1、折线图2、面积图3、柱形图4、条形图5、饼…

SpringCloud代码实战

项目结构 实例实现功能:实现通过id查询用户的订单信息 OrderCommon:公共的一些模块类型,此处为一个user对象 Eureka-Service:配置Eureka的启动类,服务端 Order-Service:提供查询功能的服务端 Order-Client:查询的客户端 OrderCommon代码…

西安明德理工学院师生莅临泰迪智能科技开展参观见习活动

为进一步深化校企合作,落实高校应用型人才培养。7月8日,西安明德理工学院与广东泰迪智能科技股份有限公司联合开展学生企业见习活动。西安明德理工学院金融产业学院副院长刘敏、金融学专业负责人张莉萍、金融学专业教师曹艳飞、赵浚妤、泰迪智能科技董事…

软件架构之架构风格

软件架构之架构风格 9.3 软件架构风格9.3.1 软件架构风格分类9.3.2 数据流风格9.3.3 调用/返回风格9.3.4 独立构件风格9.3.5 虚拟机风格9.3.6 仓库风格 9.4 层次系统架构风格9.4.1 二层及三层 C/S 架构风格9.4.2 B/S 架构风格9.4.3 MVC 架构风格9.4.4 MVP 架构风格 9.5 面向服务…

【日常记录】【插件】js 获取浏览器信息、操作系统等相关信息

文章目录 1. 原生方式2. 插件的方式2.1 Bowser 的基本使用2.2 UAParser2.3 Platform.js 参考链接 1. 原生方式 原生方式可以通过 navigator.userAgent 来获取 需要写一个正则来匹配,获取相关的信息 2. 插件的方式 获取浏览器版本相关信息的库主要有以下几个 Bowser&…

01MFC建立单个文件类型——画线

文章目录 选择模式初始化文件作用解析各初始化文件解析类导向创建鼠标按键按下抬起操作函数添加一个变量记录起始位置注意事项代码实现效果图虚实/颜色线选择模式 初始化文件作用解析 运行: 各初始化文件解析 MFC(Microsoft Foundation Classes)是一个C++类库,用于在Win…

力扣 双指针基础

class Solution {public void moveZeroes(int[] nums) {int l 0;//慢指针但先走for (int r 0; r < nums.length; r) {//快指针&#xff0c;遍历次数if (nums[r] 0) continue;//l比r先到&#xff0c;在此处定住l&#xff0c;r继续移动int t nums[l];nums[l] nums[r];num…

今天小编强烈推荐几款国产APP!

AI视频生成&#xff1a;小说文案智能分镜智能识别角色和场景批量Ai绘图自动配音添加音乐一键合成视频https://aitools.jurilu.com/ 今天小编强烈推荐几款国产APP,算得上是国产之光。如果能帮助到大家&#xff0c;别忘了给小编点点赞加关注哟&#xff01;更多精彩还在后面。 一、…

节点流与处理流:深入解析Java中的IO流

节点流与处理流&#xff1a;深入解析Java中的IO流 1、节点流&#xff08;Node Stream&#xff09;1.1 定义1.2 好处1.3 示例 2、处理流&#xff08;Processing Stream&#xff09;2.1 定义2.2 好处2.3 创建特征2.4 示例 3、总结 &#x1f496;The Begin&#x1f496;点点关注&…

关于string的‘\0‘与string,vector构造特点,反迭代器与迭代器类等的讨论

目录 问题一&#xff1a;关于string的\0问题讨论 问题二&#xff1a;C标准库中的string内存是分配在堆上面吗&#xff1f; 问题三&#xff1a;string与vector的capacity大小设计的特点 问题四&#xff1a;string的流提取问题 问题五&#xff1a;迭代器失效 问题六&#xf…

05_Shell索引数组

05_Shell索引数组 数组定义 #!/bin/basharr(1 2 3 "www.baidu.com")获取数组元素 #!/bin/basharr(1 2 3 "www.baidu.com")#通过下表获取元素 下标从1开始 ${arr[1]}#获取数组所有元素 ${arr[*]} 或者 ${arr[]}#获取数组长度 ${#arr[*]} 或者 ${#arr[*]}#获…

LeetCode(2)合并链表、环形链表的约瑟夫问题、链表分割

一、合并链表 . - 力扣&#xff08;LeetCode&#xff09; 题目描述&#xff1a; /*** Definition for singly-linked list.* struct ListNode {* int val;* struct ListNode *next;* };*/ typedef struct ListNode ListNode; struct ListNode* mergeTwoLists(struct …

选择最佳工具:评估8款顶级App界面设计软件

在如今的数字化时代&#xff0c;设计师也离不开各种界面设计软件来辅助自己的设计工作。在各种界面设计软件的帮助下&#xff0c;设计师们能够更好、更快地完成自己的设计工作。而今天本文要为大家推荐的这 8 款界面设计软件&#xff0c;分别是国产APP界面设计软件、Figma、Ske…

【数据库】Redis主从复制、哨兵模式、集群

目录 一、Redis的主从复制 1.1 主从复制的架构 1.2 主从复制的作用 1.3 注意事项 1.4 主从复制用到的命令 1.5 主从复制流程 1.6 主从复制实现 1.7 结束主从复制 1.8 主从复制优化配置 二、哨兵模式 2.1 哨兵模式原理 2.2 哨兵的三个定时任务 2.3 哨兵的结构 2.4 哨…

校园外卖系统带万字文档在线外卖管理系统java项目java课程设计java毕业设计

文章目录 校园外卖系统一、项目演示二、项目介绍三、万字项目文档四、部分功能截图五、部分代码展示六、底部获取项目源码带万字文档&#xff08;9.9&#xffe5;带走&#xff09; 校园外卖系统 一、项目演示 校园外卖服务系统 二、项目介绍 语言&#xff1a;java 数据库&…

ARM功耗管理标准接口之ACPI

安全之安全(security)博客目录导读 思考&#xff1a;功耗管理有哪些标准接口&#xff1f;ACPI&PSCI&SCMI&#xff1f; Advanced Configuration and Power Interface Power State Coordination Interface System Control and Management Interface ACPI可以被理解为一…

2023年高教杯数学建模2023B题解析(仅从代码角度出发)

前言 最近博主正在和队友准备九月的数学建模,在做往年的题目&#xff0c;博主主要是负责数据处理&#xff0c;运算以及可视化&#xff0c;这里分享一下自己部分的工作,相关题目以及下面所涉及的代码后续我会作为资源上传 问题求解 第一题 第一题的思路主要如下&#xff1a;…