重庆分公司,新征程启航
为企业提供网站建设、域名注册、服务器等服务
1.DDL(Data Definition Language):数据定义语言,用来定义数据库对象,库、表、列等;创建、删除、修改:库,表结构;
成都创新互联公司-专业网站定制、快速模板网站建设、高性价比沽源网站开发、企业建站全套包干低至880元,成熟完善的模板库,直接使用。一站式沽源网站制作公司更省心,省钱,快速模板网站建设找我们,业务覆盖沽源地区。费用合理售后完善,10余年实体公司更值得信赖。
2.DML(Data Manipulation Language):数据操作语言,用来定义数据库记录(数据);增、删、改:表记录
3.DCL(Data Control Language):数据控制语言,用来定义访问权限和安全级别
4.DQL(Data Query Language):数据查询语言,用来查询记录(数据)
DDL之操作数据库
创建数据库:
create database mydb1;
1
1
删除数据库:
drop database mysql;
1
1
数据类型(列类型)
int :整型
double :浮点型,例如double(5,2)表示最多5位,其中必须有两位小数。即最大值为999.99
decimal:浮点型,在表单钱方面使用该类型,因为不会出现精度缺失问题
char:固定长度字符串类型 char(255),数据最长为255,数据的长度不足指定长度,补足到指定长度
varchar: 可变字符串类型,varchar(65535),不补
text(club):字符串类型(mysql独有)
tinytext小
text:
mediumtext:中
longtext:大
blob:字节型
data:日期类型:格式为yyyy-MM-dd;
time:时间类型:格式为hh:mm:ss
timestamp:时间戳类型,既有日期,又有时间
DDL之操作表
创建表:
create table student(
- sno char(5),
- sname varchar(10),
- sage int
- ),
1
2
3
4
5
1
2
3
4
5
查询表结构
desc student;
1
1
删除表:
drop table student;
1
1
表中增加列:
alter table student
- add(
- educadtion varchar(50)
- );
1
2
3
4
1
2
3
4
表中修改列属性:
alter table student
- modify sage varchar(20);
1
2
1
2
表中删除列:
alter table student
- drop sage;
1
2
1
2
表的重命名:
mysql alter table student
- rename to ss;
实际测试中add primary key 和修改字段类型是需要copy tmp table的并且阻塞dml操作,另外在5.6.17版本之前时候用alter table table_name engine=innodb 是需要 copy table的并且也阻塞dml。
本来打算翻译成中文,后来发现这些英文实在是太简单了,就直接贴出来吧。
请看下图:
MySQL8.0 开始支持原⼦ DDL(atomic DDL),数据字典的更新,存储引擎操作,写⼆进制日志结合成了一个事务。在没有原⼦DDL之前,DROP TABLE test1,test2;如遇到server crash,可能会有test1被drop了,test2没有被drop掉。下面来看下在MySQL8.0之前和MySQL8.0 数据字典的区别
在MySQL8.0 之前,Data Dictionary除了存在与.FRM, .TRG, .OPT ⽂件外,还存在于系统表中(MyISAM ⾮事务引擎表中),在MySQL8.0 ,Data Dictionary 全部存在于Data Dictionary Storage Engine(即 InnoDB表中),这使crash recovery 维持原⼦性成为了可能
存储引擎⽀持
目前,只有InnoDB存储引擎⽀持原子DDL,为了实现原子DDL,Innodb要写DDL logs 到 mysql.innodb_ddl_log 表,这是⼀个隐藏在mysql.ibd 数据字典表空间⾥的数据字典表。要看mysql.innodb_ddl_log 中的内容,需要
SET GLOBAL LOG_ERROR_VERBOSITY=3;(MySQL 8.0 默认为2,error log 记录Errors and
warnings,不不记录notes)
SET GLOBAL innodb_print_ddl_logs=1;
CREATE TABLE t1 (c1 INT) ENGINE = InnoDB;
查看error log
[Note] [MY-011066] InnoDB: DDL log insert : [DDL record: DELETE SPACE, id=30,
thread_id=25, space_id=9, old_file_path=./test/t1.ibd]
[Note] [MY-011066] InnoDB: DDL log delete : by id 30
[Note] [MY-011066] InnoDB: DDL log insert : [DDL record: REMOVE CACHE, id=31,
thread_id=25, table_id=1066, new_file_path=test/t1]
[Note] [MY-011066] InnoDB: DDL log delete : by id 31
[Note] [MY-011066] InnoDB: DDL log insert : [DDL record: FREE, id=32, thread_
id=25, space_id=9, index_id=143, page_no=4]
[Note] [MY-011066] InnoDB: DDL log delete : by id 32
[Note] [MY-011066] InnoDB: DDL log post ddl : begin for thread id : 25
[Note] [MY-011066] InnoDB: DDL log post ddl : end for thread id : 25
原子DDL 操作步骤
准备:创建所需的对象并将DDL⽇志写入 mysql.innodb_ddl_log表中。DDL日志定义了如何前滚和回滚DDL操作。
执行:执⾏DDL操作。例如,为CREATE TABLE操作执⾏创建。
提交:更新数据字典并提交数据字典事务。
Post-DDL:重播并从mysql.innodb_ddl_log表格中删除DDL⽇志。为确保回滚可以安全执⾏⽽不引⼊不⼀致性,在此最后阶段执⾏⽂件操作(如重命名或删除数据文件)。这一阶段还从 mysql.innodb_dynamic_metadata的数据字典表删除的动态元数据为了DROP TABLE,TRUNCATE和其它重建表的DDL操作。
⽆论事务是提交还是回滚,DDL日志都会mysql.innodb_ddl_log在Post-DDL阶段重播并从表中删除 。mysql.innodb_ddl_log如果服务器在DDL操作期间暂停,DDL⽇志应该只保留在表中。在这种情况下,DDL⽇志会在恢复后重播并删除。
在恢复情况下,当服务器重新启动时,可能会提交或回退DDL事务。如果在重做⽇志和⼆进制日志中存在DDL操作的提交阶段期间执⾏的数据字典事务,则该操作被认为是成功的并且被前滚。否则,在InnoDB重放数据字典重做日志时回滚不完整的数据字典事务 ,并且回滚DDL事务。
原⼦DDL ⽀持类型
• DROP TABLES , all tables dropped or none
• DROP SCHEMA, all entities in the schema are dropped, or none
• Note that atomic DDL statements will be rolled back or committed even in case of crash, e.g. RENAME TABLES
• CREATE TABLE would be successfully committed or rolled back (no orphan ibd left)
• TRUNCATE TABLE (including InnoDB tables with FTS AUX tables) would be successfully committed or rolled back
• RENAME TABLES, all or none
• ALTER TABLE successful or not done
示例
请点击输入图片描述
请点击输入图片描述
结论
在MySQL8.0之前,alter table 操作在server crash的情况下,会遗留.frm,.ibd文件。MySQL8.0 能实现原⼦DDL(包括 DROP TABLE, DROP SCHEMA, CREATE TABLE, TRUNCATE TABLE, ALTER TABLE),alter table 操作,在server crash的情况下,不会遗留.frm,.ibd临时文件。让我们⼀起期待MySQL8.0 GA的到来吧!
随着 MySQL 版本的不断更新,对 DDL 操作的支持也在不断的完善和更新:比如从 MySQL 5.6 引入 Online DDL ,在 MySQL 5.7 对 Online DDL 进一步完善,到现在的 8.0 版本,则对 DDL 的实现重新进行了设计,比如 DDL 操作支持原子特性,在 MySQL 8.0.27 引入并行 DDL 。本篇就来探究一下 MySQL 8.0.27 的并行 DDL 对于 DDL 操作速度的提升。
MySQL 8.0.14 引入了 innodb_parallel_read_threads 变量来控制扫描聚簇索引的并行线程。MySQL 8.0.27 引入了 innodb_ddl_threads 变量来控制用于创建二级索引时的并行线程数量,此参数一般和一并引入的 innodb_ddl_buffer_size 一起使用,innodb_ddl_buffer_size 用于指定进行并行 DDL 操作时能够使用的 buffer 大小,buffer 是在所有的 DDL 并行线程中平均分配的,所以一般如果调大 innodb_ddl_threads 变量时,也需要调大 innodb_ddl_buffer_size 的大小。
innodb_ddl_threads 、innodb_ddl_buffer_size 和 innodb_parallel_read_threads 的默认大小分别为:
接下来测试一下调大 innodb_ddl_threads 、innodb_ddl_buffer_size 和 innodb_parallel_read_threads 参数值对 DDL 操作的性能提升。
首先创建一张 5000 万的表:
分别测试不同的线程数量和缓冲区大小的 DDL 操作时间,例如:
通过不断调整相关参数得到以下结果:
可以看到,随着并发线程的增多和 buffer 的增加,DDL 操作所占用的资源也越多,而 DDL 操作所花费的时间则越少。不过通过对比资源的消耗和 DDL 速度的提升比例,最合理的并行线程数量为4-8个,而 buffer 大小可以根据情况进行调整。
参考链接: