系统共享表空间
共享表空间又称为system tablespace系统表空间,a small set of data files (the ibdata files) 。

  • 共享表空间ibdata1
    1、数据字典(data dictionary):记录数据库相关信息
    2、doublewrite write buffer:解决部分写失败(页断裂)
    3、insert buffer:内存insert buffer数据,周期写入共享表空间,防止意外宕机
    4、回滚段(rollback segments)
    5、undo空间:undo页
  • 关于ibdata1的增长考虑

    影响共享表空间增长的对象:insert buffer、undo空间
    1、什么时候undo暴涨:
     -- 大事务为主,例如修改了40万行才提交
     -- 长事务导致的undo持续增加
    2、insert buffer空间很大
     -- 索引建立过多
     -- 很多索引不怎么使用
     -- 索引和主键顺序严重不一致:主键的建立选择有问题


系统共享表空间及配置

  • InnoDB 系统表空间     
    -- 默认情况下,InnoDB 元数据、撤消日志和缓冲区存储在系统“表空间”中。
    -- 这是单个逻辑存储区域,可以包含一个或多个文件。     
    -- 每个文件可以是常规文件或原始分区。
    -- 最后的文件可以自动扩展。
  • 通过添加数据文件增加表空间大小。
    -- 在 my.cnf 文件中使用 innodb_data_file_path 选项。
    [mysqld]
    innodb_data_file_path=datafile_spec1[;datafile_spec2]...

     

  • 配置示例:创建一个表空间,其中包含一个名为 ibdata1 且大小为 50 MB(固定)的数据文件和一个名为 ibdata2 且大小为 50 MB(自动扩展)的数据文件:

    [mysqld]
    innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend

     

  • 默认情况下将文件放置在 data 目录中。

  • 如果需要,显式指定文件位置。

案例1(重要):
1.### show variables like 'innodb_data_file_path';  #查看当前表空间大小。

mysql> show variables like 'innodb_data_file_path';
+-----------------------+------------------------+
| Variable_name         | Value                  |
+-----------------------+------------------------+
| innodb_data_file_path | ibdata1:12M:autoextend |
+-----------------------+------------------------+
1 row in set (0.00 sec)

#ibdata1第一个表空间初始大小为12M
#autoextend 表示数据满了自动增加。
2.去/application/mysql/data/下查看表空间使用情况。

[root@db01 ~]# cd /application/mysql/data/
[root@db01 data]# du -ms ibdata1 
76	ibdata1

#du -ms 查看数据文件大小,单位为M
该数据文件已经超过12M,自动扩张为76M。
3.若想手动添加数据文件,则前面所有的数据文件大小必须固定为当前大小。则在配置文件/application/mysql/my.cnf中添加配置:innodb_data_file_path=ibdata1:76M;ibdata2:50M:autoextend

[root@db01 mysql]# vim my.cnf 

[mysqld]
innodb_data_file_path=ibdata1:76M;ibdata2:50M:autoextend

4.重启MySQL:

[root@db01 mysql]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS! 
[root@db01 mysql]# 

5.若所配置数据文件大小不为当前76M,测试为66M则无法启动数据库:

[root@db01 mysql]# vim my.cnf 

[mysqld]
innodb_data_file_path=ibdata1:66M;ibdata2:50M:autoextend

6.再次重启MySQL:

[root@db01 mysql]# /etc/init.d/mysqld restart
Shutting down MySQL.... SUCCESS! 
Starting MySQL... ERROR! The server quit without updating PID file (/application/mysql-5.6.36/data/db01.pid)

#具体错误信息,请查看mysql.log
#mysql数据库将数据分为pages(数据页)进行存储。每个page为16k:
  -- 12M :12*1024k/16k = 768 pages
  -- 76M :76*1024k/16k = 4864 pages

 

数据独立表空间

  • 除了系统表空间之外,InnoDB 还在数据库目录中创建另外的表空间,用于每个 InnoDB 表的 .ibd 文件。 
  • InnoDB 创建的每个新表在数据库目录中设置一个 .ibd 文件来搭配表的 .frm 文件。
  • 可以使用 innodb_file_per_table 选项控制此设置
  • 更改该设置仅会更改已创建的新表的默认值。

注:在mysql5.6开始,默认的配置为开启状态

mysql> show variables like 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set (0.00 sec)

每次创建一个表都会在/application/mysql/data/new_table/下产生.ibd  ,.frm文件,并且都会在共享表空间中存储相应的配置信息:

mysql> use world;

Database changed
mysql> create table testtab (id int,name varchar(20));
Query OK, 0 rows affected (0.05 sec)
[root@db01 data]# pwd 
/application/mysql/data
[root@db01 data]# cd world/
city.frm             country.frm          countrylanguage.frm  db.opt               testtab.ibd          
city.ibd             country.ibd          countrylanguage.ibd  testtab.frm          
[root@db01 data]# cd world/
[root@db01 world]# ll
total 1320
-rw-rw---- 1 mysql mysql   8710 Aug  7 09:27 city.frm
-rw-rw---- 1 mysql mysql 802816 Aug  7 09:27 city.ibd
-rw-rw---- 1 mysql mysql   9172 Aug  5 10:08 country.frm
-rw-rw---- 1 mysql mysql 163840 Aug  5 10:08 country.ibd
-rw-rw---- 1 mysql mysql   8702 Aug  5 10:08 countrylanguage.frm
-rw-rw---- 1 mysql mysql 229376 Aug  5 10:08 countrylanguage.ibd
-rw-rw---- 1 mysql mysql     61 Aug  5 10:08 db.opt
-rw-rw---- 1 mysql mysql   8586 Aug  8 11:12 testtab.frm
-rw-rw---- 1 mysql mysql  98304 Aug  8 11:12 testtab.ibd

#千万不可手动在shell下用rm删除.frm,.ibd文件。

#在mysql中手动删除表空间命令:

#删除表空间
mysql> alter table testtab discard tablespace;
Query OK, 0 rows affected (0.12 sec)

#删除后无法查询
mysql> select * from testtab;
ERROR 1814 (HY000): Tablespace has been discarded for table 'testtab'

##表名依然存在
mysql> show tables;
+-----------------+
| Tables_in_world |
+-----------------+
| city            |
| country         |
| countrylanguage |
| testtab         |
+-----------------+
4 rows in set (0.00 sec)
[root@db01 world]# ll  #testtab.idb文件已经不存在
total 1224
-rw-rw---- 1 mysql mysql   8710 Aug  7 09:27 city.frm
-rw-rw---- 1 mysql mysql 802816 Aug  7 09:27 city.ibd
-rw-rw---- 1 mysql mysql   9172 Aug  5 10:08 country.frm
-rw-rw---- 1 mysql mysql 163840 Aug  5 10:08 country.ibd
-rw-rw---- 1 mysql mysql   8702 Aug  5 10:08 countrylanguage.frm
-rw-rw---- 1 mysql mysql 229376 Aug  5 10:08 countrylanguage.ibd
-rw-rw---- 1 mysql mysql     61 Aug  5 10:08 db.opt
-rw-rw---- 1 mysql mysql   8586 Aug  8 11:12 testtab.frm
[root@db01 world]# 

 

最后修改于 2019-08-08 10:15:14
如果觉得我的文章对你有用,请随意赞赏
扫一扫支付
上一篇