MySQL 5.7 General Tablespace学习(r11笔记第34天)

MySQL里面的文件蛮有意思,之前大体有两个参数来做基本的控制。一个是innodb_data_file_path就是一个共享表空间,数据都往这一个文件里放,也就是ibdata1,这个文件其实角色是有重复的,undo,数据都会放在一起。ibdata1会持续增长,无法收缩。另外一个参数是innodb_file_per_table,这样一来,就成了独立表空间,通俗一些就是每一个表都有独立的文件.frm和.ibd,而且实际中使用独立表空间还是比较普遍的,对于delete的操作影响MySQL和Oracle就大大不同。

后来到了MySQL 5.7.5,新增了在线阶段undo log的功能,让undo从原本的ibdata1剥离出来,而对于通用的独立表空间的应用场景,MySQL也提供了另外一种管理方式,就是General tablespace。其实这个特性在Oracle中已经非常普遍,换个角度来理解就很容易了,它没有库的概念,可以在多个库里建属于同一表空间的表。

为了支持这个特性,主要做了两部分改动:Innodb层的支持及Server层对MDL子模块的改动。这部分我们后面又时间再说。

创建一个表空间的语句很简单,语法如下:

CREATE TABLESPACE tablespace_name
    ADD DATAFILE 'file_name'
    [FILE_BLOCK_SIZE = value]
        [ENGINE [=] engine_name]

大体的格式就是create tablespace xxx add datafile 'xxxx' engine=innodb; 这样的方式,存储单位默认是16k。

create tablespace general_ts1 add datafile 'general_ts1_01.dbf' engine=innodb; ERROR 3121 (HY000): Incorrect File Name 'general_ts1_01.dbf'.

这里需要说明的一点是,文件路径可以是绝对的,也可以是相对的。但是文件名就得是.ibd的格式。

create tablespace general_ts1 add datafile 'general_ts1_01.ibd' engine=innodb; Query OK, 0 rows affected (0.06 sec)

当然我们可以使用create table xxx 指定tablespace的方式,或者是alter table 指定tablespace的方式。

下面这种方式在GTID下是不支持的,值得说明一下。

create table test_ts tablespace general_ts1 as select * from test ; ERROR 1786 (HY000): Statement violates GTID consistency: CREATE TABLE ... SELECT.

我们换一个姿势,创建一个表指定表空间。

create table test_ts (id int,name varchar(30)) tablespace general_ts1; Query OK, 0 rows affected (0.04 sec)查看表的建表语句就可以看得很清楚了。

> show create table test_ts; | test_ts | CREATE TABLE `test_ts` ( `id` int(11) DEFAULT NULL, `name` varchar(30) DEFAULT NULL ) /*!50100 TABLESPACE `general_ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8 |

我们来对比测试一下,重新指定一个表users,大概有80多万的数据量。

> select count(*)from users;
+----------+
| count(*) |
+----------+
|   817975 |
+----------+

可以看到在修改前的表usres是存在两个独立的文件。

-rw-r----- 1 mysql mysql       8606 Dec  4 22:48 users.frm
-rw-r----- 1 mysql mysql   41943040 Dec  4 22:48 users.ibd

使用alter语句来修改,整个过程很快

> alter table users tablespace general_ts1; Query OK, 0 rows affected (1.87 sec) Records: 0 Duplicates: 0 Warnings: 0

这个时候目录下只存在一个定义文件了,数据都放到新建的表空间了。

-rw-r----- 1 mysql mysql 8606 Jan 4 22:46 users.frm

我们简单解析一下这个定义文件,看看内容和原来有什么差别,可以看到有了新的表空间的标识。

# strings users.frm 
PRIMARY
InnoDB
general_ts1
)                                        
user_id
user_name
user_id
user_name

原本空白的数据文件马上就有了数据。

-rw-r----- 1 mysql mysql 41943040 Jan 4 22:46 general_ts1_01.ibd

如果我们修改表空间为独立表空间的方式,也是可以的。

> ALTER TABLE users TABLESPACE=innodb_file_per_table; Query OK, 0 rows affected (2.17 sec) Records: 0 Duplicates: 0 Warnings: 0

有一个差别就是建表DDL和原来的格式就不大一样了。

> show create table users;
| users | CREATE TABLE `users` (
  `user_id` int(11) unsigned NOT NULL,
  `user_name` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`user_id`)
) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8 |

修改完成后.ibd文件会重新生成。

如果要查看表空间的信息,在使用general tablespace的情况下查看数据字典就会有一些差别。比如数据库test下存在一个表users,在视图INNODB_SYS_TABLESPACES中是只能看到表空间的基础定义信息,general_ts1而找不到users的字样。

原文发布于微信公众号 - 杨建荣的学习笔记(jianrong-notes)

原文发表时间:2017-01-04

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏WindCoder

网易MySQL微专业学习笔记(二)-Mysql数据对象

这个系列属于个人学习网易云课堂MySQL数据库工程师微专业的相关课程过程中的笔记,本篇为其“MySQL数据库对象与应用”中的MySQL数据类型相关笔记。

691
来自专栏.NET技术

经典SQL语句大全之提升

1、说明:复制表(只复制结构,源表名:a 新表名:b) (Access可用) 法一:select * into b from a where 1<>1(仅用于S...

1963
来自专栏数据和云

【云和恩墨大讲堂】谈Oracle表新增字段的影响

作者简介 ? 刘晨,网名bisal,Oracle 10g/11g OCM,并国内首批Oracle YEP成员,博客:blog.itpub.net/bisal 很...

3037
来自专栏Java面试通关手册

MySQL常见的两种存储引擎:MyISAM与InnoDB的爱恨情仇

Java面试通关手册(Java学习指南,欢迎Star,会一直完善下去,欢迎建议和指导):https://github.com/Snailclimb/Java_G...

2267
来自专栏匠心独运的博客

数据库技术之记一次排查DB死锁的分析与思考

虽然很多童鞋在学数据库课程时都了解数据库隔离级别、死锁和事务等概念,但在测试/线上环境遇到死锁却不一定能够及时分析并解决这类问题。本文主要以作者在测试环境中遇到...

1891
来自专栏文渊之博

SQL Server 2014聚集列存储索引

  之前已经写过两篇介绍列存储索引的文章,但是只有非聚集列存储索引,今天再来简单介绍一下聚集的列存储索引,也就是可更新列存储索引。在SQL Server 201...

1064
来自专栏Linyb极客之路

MySQL锁

  MySQL的锁机制,就是数据库为了保证数据的一致性而设计的面对并发场景的一种规则。

811
来自专栏文渊之博

SQL Server 2014聚集列存储索引

 转发请注明引用和原文博客(https://cloud.tencent.com/developer/user/1217611/activities) 简介   ...

2169
来自专栏Java技术交流群809340374

MySQL有哪些存储引擎,各自的优缺点,应用场景

经常面试都会问到MYSQL有哪些存储引擎,以及各自的优缺点。今天主要分享常见的存储引擎:MyISAM、InnoDB、MERGE、MEMORY(HEAP)、BDB...

1813
来自专栏L宝宝聊IT

索引、视图、存储过程和触发器的应用

1588

扫码关注云+社区