mysql必背知识点高级(MySQL 8.0 Online DDL快速加列的相关总结)
mysql必背知识点高级
MySQL 8.0 Online DDL快速加列的相关总结 问题描述前几天同事问了我一个问题:业务a从mysql迁移到mongodb的原因是什么?
说实话,这个问题还真不好回答,为什么要迁移,一定是遇到了某种瓶颈,可能是数据量也可能是数据类型等,于是我咨询了一下业务,最终得到了答案:这个业务中的某些表,要频繁的加字段。mongodb中加字段的成本几乎没有,而mysql低版本中加字段的成本还是挺高的。
那么常用的mysql添加字段的方法有哪些呢?这里我简单列举一下:
1、percona的pt-osc工具
2、github开源项目gh-ost工具
3、mysql原生online ddl
mysql online ddl加列的历史方法01 copy方法
mysql5.5版本及之前的加列方法:copy
它的执行示意图如下:
我们有一个原表a,只包含1个字段,它包含1、2、4、6这几条记录,当我们使用copy算法加列时:
1、创建了一个新的表tmp-a,新表包含2个字段,
2、然后我们把表a的数据全部逐行拷贝到tmp-a这个新表里面,
3、然后用tmp-a表和a表做个交换,
这样,我们的新表就包含2个字段了。同时需要注意,新表中的数据记录比原表更加紧凑了。原表中可能由于删除了3和5两条记录,使得表中间留下了空洞,或者叫空间碎片。
可以看到,copy算法需要拷贝一遍数据,需要额外的存储空间来存储tmp-a这个临时表。另外,在拷贝数据的过程中,表a的写入操作会丢失,也就是说,表a在alter table的过程中不能有数据更新。这可能是一个致命的缺点。
02 inplace方法
mysql5.6版本开始引入online ddl,这个功能使得上面的过程变成了下面这样:
它的过程和上面的copy算法有些不同:
1、online ddl过程中,从表a提取b+树,并存储到一个中间文件tmp-file,而不是中间表tmp-a
2、步骤1执行过程中,对表a的写入,都会记录到row log中
3、步骤1执行完毕后,对tmp-file应用所有的row log,得到一个与表a数据相同的数据文件
4、利用数据文件tmp-file替换表a的数据文件即可。
这个过程中,由于row log的存在,使得在整个该表过程中,表a是可以进行增删改查的操作的,因为这些操作不会丢失。这也就是为什么把这个过程叫做online ddl的原因。
另外,这里需要解释下,copy算法中生成的tmp-a临时表是在server层面创建的,而上述online ddl操作中的tmp-file是在插件式存储引擎innodb内部生成的,我们把这种在innodb内部完成的变更操作,称之为inplace(中文表示原地),也就是不需要将数据挪动到"server层的临时表"。
mysql8.0.12 引入的instant方法mysql8.0.12版本引入了instant的方法,它让加列变得更加简单。instant算法添加列时不再需要 rebuild 整个表,只需要在表的 metadata 中记录新增列的基本信息即可。
我们来看它的优势,首先我们创建一个表t1,并插入26w条数据,然后分别添加数据列col_1,col_2,col_3,并显示指定加列的算法为copy、inplace、和instant,结果如下:
|
[test] 23:42:45> select count (1) from t1; + ----------+ | count (1) | + ----------+ | 262144 | + ----------+ 1 row in set (0.06 sec) 方案一:copy [test] 23:43:29> alter table t1 add col_1 int ,algorithm=copy; query ok, 262144 rows affected (1.48 sec) records: 262144 duplicates: 0 warnings: 0 方案二:inplace [test] 23:43:46> alter table t1 add col_2 int ,algorithm=inplace; query ok, 0 rows affected (0.58 sec) records: 0 duplicates: 0 warnings: 0 方案三:instant [test] 23:44:08> alter table t1 add col_3 int ,algorithm=instant; query ok, 0 rows affected (0.01 sec) records: 0 duplicates: 0 warnings: 0 m5480:mysqlha_common@10.41.28.124 [test] 23:44:14> show create table t1\g *************************** 1. row *************************** table : t1 create table : create table `t1` ( `id` int not null auto_increment, ` name ` varchar (10) collate utf8mb4_general_ci default null , `age` int default null , `score` int default null , `col_1` int default null , `col_2` int default null , `col_3` int default null , primary key (`id`), key `idx_sco` (`score`) ) engine=innodb auto_increment=458730 default charset=utf8mb4 collate =utf8mb4_general_ci 1 row in set (0.01 sec) |
从结果不难看出,执行时间上:
copy> inplace > instant
与此同时,copy算法的受影响行数是全部表,而inplace和instant的算法影响的行数都是0,说明他们是online ddl操作。
最后,我们还可以通过下面的方法查看instant列的信息:
|
[test] 23:53:01> select * from information_schema.innodb_tables where name like 'test/t1' \g *************************** 1. row *************************** table_id: 1079 name : test/t1 flag: 33 n_cols: 10 space : 22 row_format: dynamic zip_page_size: 0 space_type: single instant_cols: 6 1 row in set (0.00 sec) |
可以看到,test.t1这个表的instant列序号是6,代表它是这个表的第7个列(列编号从0开始)。
当然,instant算法不支持删除普通列、无法设置列的顺序、还有一些其他的限制,详情可以查看官方文档:https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html
但这些限制并不影响它成为一个优秀的ddl功能。 相信通过mysql版本的不断迭代,在后面的版本中,有更多的变更操作可以用到instant这种高效的算法。
以上就是mysql 8.0 online ddl快速加列的相关总结的详细内容,更多关于mysql ddl快速加列的资料请关注开心学习网其它相关文章!
原文链接:https://mp.weixin.qq.com/s/jEDA6svpo9n7Dic8Y4xDrA
- 命令行如何使用xampp中的mysql(解决xampp自启动和mysql.sock问题)
- mysql怎么给查询权限(MySql设置指定用户数据库查看查询权限)
- mysql集群是用什么搭建的(MySQL5.7 集群配置的步骤)
- laravel数据表配置(laravel实现按月或天或小时统计mysql数据的方法)
- mysql错误代码之1064的解决方案(mysql错误代码之1064的解决方案)
- php添加数据到mysql数据库(PHP通过代码连接XAMPP数据库及MySQL数据库方法)
- mysql索引优化有哪些(MySQL如何基于Explain关键字优化索引功能)
- mysql触发器如何创建(MySQL 触发器的使用和理解)
- 查询按照部门分组的mysql语句(Mysql根据某层部门ID查询所有下级多层子部门的示例)
- mysql有哪些约束(MySQL完整性约束的定义与实例教程)
- 织梦源码安装数据库怎么填(Mysql修改端口号 织梦DedeCMS设置教程)
- mysqlsql按日期统计(sqlserver/mysql按天、按小时、按分钟统计连续时间段数据推荐)
- idea关联mysql数据库(IDEA无法连接mysql数据库的6种解决方法大全)
- navicat15.0.28注册码(Navicat for MySQL 11注册码激活码汇总)
- mysql重复插入数据教程(Mysql避免重复插入数据的4种方式)
- mysql中group_concat
- 门外之见 海蛎子味 的表演,能走多远(门外之见海蛎子味)
- 三部冷门谍战剧,第一部2014年拍摄,至今还未播出(三部冷门谍战剧)
- 《金陵秘事》的剧情跌宕起伏 给观众带来的怎样的感官体验(金陵秘事的剧情跌宕起伏)
- 少儿口才表达影响未来一生,50首经典绕口令和孩子玩出聪明大脑(少儿口才表达影响未来一生)
- 玩网游居然让人更友善 很难以让人置信(玩网游居然让人更友善)
- 学好汉语拼音,从娃娃绕口令抓起,平时还是要多练 收藏好(从娃娃绕口令抓起)
热门推荐
- dedecms独立页面怎么加(DEDECMS实现文章tag关键词自动生成列表页的方法)
- antdesignpro引入依赖如何使用(在Ant Design Pro登录功能中集成图形验证码组件的方法步骤)
- servu权限设置(Serv-U防溢出提权攻击解决设置方法)
- docker配置自己的环境(docker可视化工具Portainer部署并汉化的操作)
- html锚点定位
- 用html5做一个音乐的播放器(HTML5自定义mp3播放器源码)
- select 1 from 的作用
- python找到连续不重复最长英文串(Python实现简单查找最长子串功能示例)
- python字典的值排序(python 对字典按照value进行排序的方法)
- CSS中background-attachment
排行榜
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9