MySQL8.0 如何快速加列_Mysql

来源:脚本之家  责任编辑:小易  

你是想改表结构还是插入数据2113?表结构5261:右键点中表,选设计表,移动到最底4102部一条,“创建栏位”将在这1653之后增加一列;“插入栏位”将在这列之前增加一列。插入数据:双击打开表,下面有个“+”的符号,点一下,增加一条记录,然后再点一下下面的对钩保存数据。navicat 很简单的。慢慢熟悉吧,记得去把它汉化哦,传统情况我们先回顾一下,在2113没有 "立刻5261加列" 功能时,加列操作是怎么4102完成的。我们也借此来熟悉一下本期1653的图例:当进行 加列操作 时,所有的数据行 都必须要 增加一段数据(图中的 列 4 数据)如上一期图解所讲,当改变数据行的长度,就需要 重建表空间(图中灰蓝的部分为发生变更的部分)数据字典中的列定义也会被更新以上操作的问题在于 每次加列 操作都需要重建表空间,这就需要大量 IO以及大量的时间立刻加列"立刻加列" 的过程如下图:请点击输入图片描述请点击输入图片描述"立刻加列" 时,只会变更数据字典中的内容,包括:在列定义中增加 新列的定义增加 新列的默认值"立刻加列" 后,当要读取表中的数据时:由于 "立刻加列" 没有 变更行数据,读取的行数据只有 3 列MySQL 会将 新增的第 4 列的默认值,追加到 读取的数据后以上过程描述了 如何读取 在 "立刻加列" 之前写入的数据,其实质是:在读取数据的过程中,"伪造" 了一个新列出来那么如何读取 在 "立刻加列" 之后 写入的数据呢 ? 过程如下图:当读取 行 4 时:请点击输入图片描述请点击输入图片描述通过判断 数据行的头信息中的instant 标志位,可以知道该行的格式是 "新格式":该行头信息后有一个新字段 "列数"通过读取 数据行的 "列数" 字段,可以知道 该行数据中多少列有 "真实" 的数据,从而按列数读取数据通过上图可以看到:读取 在"立刻加列" 前/后写入的数据是不同的流程通过以上的讨论,我们可以总结 "立刻加列" 之所以高效的原因是:在执行 "立刻加列" 时,不变更数据行的结构读取 "旧" 数据时,"伪造" 新增的列,使结果正确写入 "新" 数据时,使用了新的数据格式(增加了instant标志位 和 "列数" 字段),以区分新旧数据读取 "新" 数据时,可以如实读取数据那么 我们是否能一直 "伪造" 下去 ? "伪造" 何时会被拆穿 ?考虑以下场景:用 "立刻加列" 增加列 A写入数据行 1用 "立刻加列" 增加列 B写入数据行 2删除列 B我们推测一下 "删除列 B" 的最小代价:需要修改 数据行中的instant标志位或 "列数" 字段,这至少会影响到 "立刻加列" 之后写入的数据行,成本类似于重建数据从以上推测可知:当出现 与 "立刻加列" 操作不兼容 的 DDL 操作时,数据表需要进行重建,如下图所示:请点击输入图片描述请点击输入图片描述扩展思考题:是否能设计其他的数据格式,取代instant标志位和 "列数" 字段,使得 加列/删列 操作都能 "立刻完成" ?(提示:考虑 加列 - 删列 - 再加列 的情况)使用限制在了解原理之后,我们来看看 "立刻加列" 的使用限制,就很容易能理解其中的前两项:"立刻加列" 的加列位置只能在表的最后,而不能加在其他列之间在元数据中,只记录了 数据行 应有多少列,而没有记录 这些列 应出现的位置。所以无法实现指定列的位置"立刻加列" 不能添加主键列加列 不能涉及聚簇索引的变更,否则就变成了 "重建" 操作,不是 "立刻" 完成了"立刻加列"不支持压缩的表格式按照 WL 的说法:"COMPRESSED is no need to supported"(没必要支持不怎么用的格式)总结回顾我们总结一下上面的讨论:"立刻加列" 之所以高效的原因是:在执行 "立刻加列" 时,不变更数据行的结构读取 "旧" 数据时,"伪造" 新增的列,使结果正确写入 "新" 数据时,使用了新的数据格式 (增加了 instant 标志位 和 "列数" 字段),以区分新旧数据读取 "新" 数据时,可以如实读取数据"立刻加列" 的 "伪造" 手法,不能一直维持下去。当发生 与 "立刻加列" 操作不兼容 的 DDL 时,表数据就会发生重建回到之前遗留的两个问题:"立刻加列" 是如何工作的 ?我们已经解答了这个问题所谓 "立刻加列" 是否完全不影响业务,是否是真正的 "立刻" 完成 ?可以看到:就算是 "立刻加列",也需要变更 数据字典,那么 该上的锁还是逃不掉的。也就是说 这里的 "立刻" 指的是 "不变更数据行的结构",而并非指 "零成本地完成任务"www.zgxue.com防采集请勿采集本网。

前言:

很早就听说 MySQL8.0 支持快速加列,可以实现大表秒级加字段。笔者自己本地也有8.0环境,但一直未进行测试。本篇文章我们就一起来看下 MySQL8.0 快速加列到底要如何操作。

alter table questionlib modify id int(11) auto_increment; 注意事项: 修改后从下一条记录开始自动增长。如果想让原来的自动增长就得复制现有表的结构

1.了解背景信息

DROP TABLE IF EXISTS `jk`.`jkrecord`; CREATE TABLE `jk`.`jkrecord` ( `user1` varchar(45) NOT NULL, `user2` varchar(45) NOT NULL,

表结构的变更是业务运行过程中比较常见的需求之一,在 MySQL 的环境中,可以使用 Alter 语句来完成这些操作,这些 Alter 语句对应的操作通常也称之为 DDL 操作。通常情况下大表的 DDL 操作都会对业务有很明显的影响,需要在业务空闲,或者是维护的时候做。MySQL 5.7 支持 Online DDL,大部分 DDL 不影响对表的读取和写入,但是依然会消耗非常多的时间,且占用额外的磁盘空间,并会造成主从延迟。所以大表 DDL 仍是一件令 DBA 头痛的事。

CREATE TABLE phoneMst( id INT NOT NULL AUTO_INCREMENT, phone int NOT NULL, PRIMARY KEY (id) );

听闻 MySQL 8.0 解决了这件令 DBA 头痛的事,那让我们来详细了解下吧。想了解新功能,最简单的方法就是查阅官方文档。查阅官方文档得知,快速加列即 Instant Add Column ,该功能自 MySQL 8.0.12 版本引入,是由腾讯游戏DBA团队贡献。注意一下,此功能只适用于 InnoDB 表。

select a.*,b.tot `total` ,a.No/b.tot `percent` from temp a,(select sum(No) tot from temp) b

2.快速加列测试

在已有数据库中的表添加字段: 1、通用式: alter table [表名] add [字段名] 字段属性 default 缺省值 default 是可选参数 2、增加字段: alter

快速加列采用的是 instant 算法,使得添加列时不再需要 rebuild 整个表,只需要在表的 metadata 中记录新增列的基本信息即可。在 alter 语句后增加 ALGORITHM=INSTANT 即代表使用 instant 算法, 如果未明确指定,则支持 instant 算法的操作会默认使用。如果 ALGORITHM=INSTANT 指定但不支持,则操作立即失败并显示错误。

关于列的 DDL 操作,是否支持 instant 等算法,官方文档给出了一个表格,现整理如下,星号表示不是全部支持,有依赖项。

操作 Instant In Place Rebuilds Table 允许并发DML 仅修改元数据
添加列 Yes* Yes No* Yes* No
删除列 No Yes Yes Yes No
重命名列 No Yes No Yes* Yes
更改列顺序 No Yes Yes Yes No
设置列默认值 Yes Yes No Yes Yes
更改列数据类型 No No Yes No No
扩展VARCHAR列大小 No Yes No Yes Yes
删除列默认值 Yes Yes No Yes Yes
更改自动增量值 No Yes No Yes No*
设置列为null No Yes Yes* Yes No
设置列not null No Yes* Yes* Yes No
修改ENUM/SET列的定义 Yes Yes No Yes Yes

instant 算法使用最广泛的应该是添加列了,可以看到使用该算法还是有些限制的,一些限制如下: 如果 alter 语句包含了 add column 和其他的操作,其中有操作不支持 instant 算法的,那么 alter 语句会报错,所有的操作都不会执行。 只能顺序加列, 仅支持在最后添加列,而不支持在现有列的中间添加列。 不支持压缩表,即该表行格式不能是 COMPRESSED。 不支持包含全文索引的表。 不支持临时表。 不支持那些在数据字典表空间中创建的表。

说的再多不如实际来测下,下面我们以 8.0.19 版本为例来实际验证下:

# 利用sysbench生成一张1000W的大表mysql> select version();+-----------+| version() |+-----------+| 8.0.19 |+-----------+1 row in set (0.00 sec)mysql> select count(*) from sbtest1;+----------+| count(*) |+----------+| 10000000 |+----------+# 增加无默认值的列mysql> alter table sbtest1 add column col1 varchar(20), algorithm=instant;Query OK, 0 rows affected (0.63 sec)Records: 0 Duplicates: 0 Warnings: 0# 增加有默认值的列mysql> alter table sbtest1 add column create_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', algorithm=instant;Query OK, 0 rows affected (0.58 sec)Records: 0 Duplicates: 0 Warnings: 0# 不显式指定instant算法mysql> alter table sbtest1 add column col2 varchar(20);Query OK, 0 rows affected (0.55 sec)Records: 0 Duplicates: 0 Warnings: 0# 设置列的默认值mysql> alter table sbtest1 alter column col1 set default 'sql',algorithm=instant;Query OK, 0 rows affected (0.02 sec)Records: 0 Duplicates: 0 Warnings: 0# 指定In Place算法添加列,(5.7版本添加列使用该算法)mysql> alter table sbtest1 add column col_inplace varchar(20),algorithm=inplace;Query OK, 0 rows affected (1 min 23.30 sec)Records: 0 Duplicates: 0 Warnings: 0

通过以上测试,我们可以发现,使用 instant 算法添加列基本都在 1s 内完成,对于大表来说这个速度是非常快的,业务基本无感知。当使用 5.7 版本的 inplace 算法时,则添加列的时间上升至数分钟。对比看来 8.0 版本的快速加列功能确实非常实用!

总结:

虽然快速加列存在一些限制, instant 算法也只适用于部分 DDL 操作,但 8.0 的这项新功能已经足以令人兴奋,很大程度上解决了大表加字段的大难题。通过这篇文章,希望各位能了解到这项新功能,是不是想升级到 8.0 了呢,可以着手准确起来了。

以上就是MySQL8.0 如何快速加列的详细内容,更多关于MySQL8.0 快速加列的资料请关注真格学网其它相关文章! 您可能感兴趣的文章:解析MySQL8.0新特性——事务性数据字典与原子DDL详解MySQL8.0​ 字典表增强mysql8.0.21下载安装详细教程MySQL8.0.21.0社区版安装教程(图文详解)阿里云centos7中安装MySQL8.0.13的方法步骤mysql8.0.20安装与连接navicat的方法及注意事项源码编译安装MySQL8.0.20的详细教程CentOS7版本安装Mysql8.0.20版本数据库的详细教程mysql8.0.20下载安装及遇到的问题(图文详解)MySQL8.0.20安装教程及其安装问题详细教程CentOS8部署LNMP环境之编译安装mysql8.0.29的教程详解

test_id为自动2113增加的列insert into trst_increment(test_name) values('张三5261')test_id不为自动4102增加的列1653insert into trst_increment(test_id,test_name) values(1,'张三')追问请问创建好表,create table TEST_increase(TEST_id serial, TEST_name varchar(10));之后,按理来说,直接insert into TEST_increase values('张三')。应该会在表里出现 1,张三。1是自动生成的,对么?可是我输入不行,column对不上…追答TEST_increase 有两个栏位,你后面只有一个值,当然不对,应该改为insert into trst_increment(test_name) values('张三')你试试内容来自www.zgxue.com请勿采集。


  • 本文相关:
  • 详解mysql 30条军规
  • mysql中对查询结果排序和限定结果的返回数量的用法教程
  • mysql 表索引的一些要点
  • mysql无法连接 提示10055错误的解决方法
  • windows7下启动mysql服务出现服务名无效的原因及解决方法
  • 如何恢复mysql主从数据一致性
  • mysql每半小时平均值计算的sql语句
  • 浅谈mysql的索引设计原则以及常见索引的区别
  • 用sql语句解决mysql导入大数据文件的问题
  • mysql获取随机数据的方法
  • mysql 加入自动增加列的问题
  • navicat mysql怎么在表中添加列
  • mysql如何增加字段并将增加的该字段设为主键?
  • 想在mysql数据库中的表中插入一列,怎么做?
  • mySQL中如何修改列为自动增长?急!!!
  • Mysql中如何实现某字段数据自动加1
  • mysql如何自动增加一列序号
  • mysql 增加一列常数列,增加列
  • 如何在mysql数据库中添加新的字段
  • 用java向mysql数据库添加列的问题
  • 网站首页网页制作脚本下载服务器操作系统网站运营平面设计媒体动画电脑基础硬件教程网络安全mssqlmysqlmariadboracledb2mssql2008mssql2005sqlitepostgresqlmongodbredisaccess数据库文摘数据库其它首页解析mysql8.0新特性——事务性数据字典与原子ddl详解mysql8.0​ 字典表增强mysql8.0.21下载安装详细教程mysql8.0.21.0社区版安装教程(图文详解)阿里云centos7中安装mysql8.0.13的方法步骤mysql8.0.20安装与连接navicat的方法及注意事项源码编译安装mysql8.0.20的详细教程centos7版本安装mysql8.0.20版本数据库的详细教程mysql8.0.20下载安装及遇到的问题(图文详解)mysql8.0.20安装教程及其安装问题详细教程centos8部署lnmp环境之编译安装mysql8.0.29的教程详解详解mysql 30条军规mysql中对查询结果排序和限定结果的返回数量的用法教程mysql 表索引的一些要点mysql无法连接 提示10055错误的解决方法windows7下启动mysql服务出现服务名无效的原因及解决方法如何恢复mysql主从数据一致性mysql每半小时平均值计算的sql语句浅谈mysql的索引设计原则以及常见索引的区别用sql语句解决mysql导入大数据文件的问题mysql获取随机数据的方法mysql安装图解 mysql图文安装教程can""""t connect to mysql servwindows下mysql5.6版本安装及配置mysql字符串截取函数substring的mysql创建用户与授权方法mysql提示:the server quit withmysql日期数据类型、时间类型使用mysql——修改root密码的4种方法mysql update语句的用法详解mysql之timestamp(时间戳)用法mysql中union与union all的基本使用方法win2003服务器安装及设置教程 mysql安全设mysql出现error performing load commandmysql主键相关的sql语句集锦ubuntu16.04.1下 mysql安装和卸载图文教程mysql学习(七):innodb存储引擎索引的实mysql提示got timeout reading communicamysql select同时update同一张表问题发生centos安装配置mysql8.0的步骤详解mysql语句执行顺序和编写顺序实例解析
    免责声明 - 关于我们 - 联系我们 - 广告联系 - 友情链接 - 帮助中心 - 频道导航
    Copyright © 2017 www.zgxue.com All Rights Reserved