精华内容
下载资源
问答
  • 数据库大小和可用空间是什么意思
    千次阅读
    2021-01-19 00:27:38

    {"moduleinfo":{"card_count":[{"count_phone":1,"count":1}],"search_count":[{"count_phone":4,"count":4}]},"card":[{"des":"阿里云数据库专家保驾护航,为用户的数据库应用系统进行性能和风险评估,参与配合进行数据压测演练,提供数据库优化方面专业建议,在业务高峰期与用户共同保障数据库系统平稳运行。","link1":"https://www.aliyun.com/service/optimization/database","link":"https://www.aliyun.com/service/chiefexpert/database","icon":"https://img.alicdn.com/tfs/TB1a5ZfonnI8KJjy0FfXXcdoVXa-100-100.png","btn2":"数据库紧急救援服务","tip":"还有更多专家帮助您解决云上业务问题:立即查看","btn1":"云上数据库优化服务","link2":"https://www.aliyun.com/service/databaserescue","title":"数据库专家服务"}],"search":[{"txt":"安全首席专家","link":"https://www.aliyun.com/service/chiefexpert/security"},{"txt":"中间件首席专家","link&#

    更多相关内容
  • 查询指定 (数据库名shop,表名tb_goods) mysql> SELECT concat(round(SUM(DATA_LENGTH / 1024 / 1024), 2), 'MB') AS DATA_LENGTH, concat(round(SUM(INDEX_LENGTH / 1024 / 1024), 2), 'MB') AS DATA_INDEX,...
    1. 选择information_schema数据库(存储其他数据库信息)
      mysql> use information_schema;
      Reading table information for completion of table and column names
      You can turn off this feature to get a quicker startup with -A
      
      Database changed
      

       

    2. 查询所有数据库
      mysql> SELECT concat(round(SUM(DATA_LENGTH / 1024 / 1024), 2), 'MB') AS DATA_SIZE, concat(round(SUM(INDEX_LENGTH / 1024 / 1024), 2), 'MB') AS INDEX_SIZE, concat(round(SUM(DATA_FREE / 1024 / 1024), 2), 'MB') AS DATA_FREE FROM tables;
      +-----------+------------+-----------+
      | DATA_SIZE | INDEX_SIZE | DATA_FREE |
      +-----------+------------+-----------+
      | 4066.25MB | 2919.14MB  | 101.00MB  |
      +-----------+------------+-----------+
      1 row in set (0.02 sec)
      

       

    3. 查询指定数据库
      mysql> SELECT concat(round(SUM(DATA_LENGTH / 1024 / 1024), 2), 'MB') AS DATA_LENGTH, concat(round(SUM(INDEX_LENGTH / 1024 / 1024), 2), 'MB') AS DATA_INDEX, concat(round(SUM(DATA_FREE / 1024 / 1024), 2), 'MB') AS DATA_FREE FROM TABLES WHERE table_schema = 'shop';
      +-------------+------------+-----------+
      | DATA_LENGTH | DATA_INDEX | DATA_FREE |
      +-------------+------------+-----------+
      | 4062.64MB   | 2916.97MB  | 13.00MB   |
      +-------------+------------+-----------+
      1 row in set (0.00 sec)
      

       

    4. 查询指定表(数据库名shop)
      mysql> SELECT concat(round(SUM(DATA_LENGTH / 1024 / 1024), 2), 'MB') AS DATA_LENGTH, concat(round(SUM(INDEX_LENGTH / 1024 / 1024), 2), 'MB') AS DATA_INDEX, concat(round(SUM(DATA_FREE / 1024 / 1024), 2), 'MB') AS DATA_FREE FROM TABLES WHERE table_schema = 'shop' AND table_name = 'tb_goods';
      +-------------+------------+-----------+
      | DATA_LENGTH | DATA_INDEX | DATA_FREE |
      +-------------+------------+-----------+
      | 1.52MB      | 0.08MB     | 4.00MB    |
      +-------------+------------+-----------+
      1 row in set (0.00 sec)
      

       

    5. 查询指定表 (数据库名shop,表名tb_goods)
      mysql> SELECT concat(round(SUM(DATA_LENGTH / 1024 / 1024), 2), 'MB') AS DATA_LENGTH, concat(round(SUM(INDEX_LENGTH / 1024 / 1024), 2), 'MB') AS DATA_INDEX, concat(round(SUM(DATA_FREE / 1024 / 1024), 2), 'MB') AS DATA_FREE FROM TABLES WHERE table_schema = 'shop' AND table_name = 'tb_goods';
      +-------------+------------+-----------+
      | DATA_LENGTH | DATA_INDEX | DATA_FREE |
      +-------------+------------+-----------+
      | 1.52MB      | 0.08MB     | 4.00MB    |
      +-------------+------------+-----------+
      1 row in set (0.00 sec)
      

       

    展开全文
  • Mysql数据快速清理及表空间释放 1、TABLES主要字段说明: MySQL的 information_schema 数据库中的TABLES 记录了MySQL数据库中每个占用的空间记录的行数,更新时间,说明等,这个主要字段如下: TABLE...

    Mysql数据快速清理及表空间释放

    1、TABLES表主要字段说明:

    MySQL的 information_schema 数据库中的TABLES 表记录了MySQL数据库中每个表占用的空间、表记录的行数,更新时间,说明等,这个表主要字段如下:

    TABLE_SCHEMA : 数据库名

    TABLE_NAME:表名

    ENGINE:所使用的存储引擎

    TABLES_ROWS:记录数,即表的行数

    DATA_LENGTH:数据大小

    INDEX_LENGTH:索引大小

    CREATE_TIME:创建时间

    UPDATE_TIME:最近更新时间

    DATA_FREE:该参数与mysql碎片有关,如果是共享表空间,该字段表示共享表空间的大小而非数据的大小。只有使用独占表空间时,该字段才表示该表的剩余空间;

    说明:当MySQL从列表中删除一行内容,该段空间就会被留空。在一段时间内执行大量删除操作后,往往会使碎片空间变得比存储列表内容所使用的空间更大。

    通俗的讲:Data_free字段即为多占的物理空间,通过‘show table status’可以查看指定表的Data_free字段,对应的值就是多占用的物理空间,drop表重建或是重新导入可以释放这部分空间。

    mysql> use hellodb

    mysql> show table status like 'students%';

    +-----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+

    | Name      | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation       | Checksum | Create_options | Comment |

    +-----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+

    | students  | InnoDB |      10 | Compact    |   27 |            606 |       16384 |               0 |        32768 |         0 |             28 | 2017-11-28 15:31:15 | NULL        | NULL       | utf8_general_ci |     NULL |                |         |

    | students1 | InnoDB |      10 | Compact    |    8 |           2048 |       16384 |               0 |        32768 |         0 |              9 | 2018-06-11 20:16:53 | NULL        | NULL       | utf8_general_ci |     NULL |                |         |

    +-----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-----------------

    此时可以使用optimize整理表的碎片:

    注意:该操作执行的时候会把该表格先写入一个tmp临时表,所以磁盘剩余空间必须大于表空间,否则会执行失败。

    mysql> optimize table classes,students;

    5.6.X以前的版本会提示该表不支持optimize,5.6.X的版本已经支持Innodb了。

    2、生产案例:释放表空间

    思路1

    数据不删除:备份表,drop表,导入备份的表到数据库;

    show table status like 'oldtb%';  -->重点关注Data_free字段,对应的值就是多占用的物理空间,即碎片空间

    mysqldump -uroot -p123456  mydb oldtb >/mnt/oldtb.sql  -->导出可以恢复系统空间的表格。

    drop table oldtb;                        -->drop旧表释放表空间

    source /mnt/oldtb.sql;                -->导出备份的表到数据库。

    show table status like 'oldtb%';  -->对比恢复前后的状态,重点关注Data_free字段

    该操作通过多次单表操作,多次对表进行备份和恢复,从而释放了碎片空间,适用于不允许停机的业务。

    如果系统业务允许在一段时间内停止服务,可以备份整个数据库,然后清空,重建mysql数据根目录,然后恢复整个数据库。

    思路2

    删除部分旧数据:适用于可以删除部分旧数据的场景

    create table newtb like oldtb if no exist;      -->新建newtb表,表结构同旧表

    insert into newtb select * from oldtb where time > 'xxx' and time<='xxx';

    -->备份表的新数据,可以按时间段备份最近一个月或半个月

    drop table oldtb;                                        -->drop旧表释放表空间

    alter tables newtb rename to oldtb;           -->重命名新表“newdtb”

    思路3:直接优化表:optimize table 表名;

    select table_name,data_free,engine from information_schema.tables where table_schema='mydb';

    -->查看库中各表data_free值,单位是字节,data_free/1024/1024/1024即为理论上释放后可以恢复空间G。

    show table status like 'oldtb';                  -->单个表的data_free大小

    mysql> OPTIMIZE TABLE  hellodb.students;

    -->显示不支持,实际上已进行重建和分析,空间已经回收

    +------------------+----------+----------+-------------------------------------------------------------------+

    | Table            | Op       | Msg_type | Msg_text                                                          |

    +------------------+----------+----------+-------------------------------------------------------------------+

    | hellodb.students | optimize | note     | Table does not support optimize, doing recreate + analyze instead |

    | hellodb.students | optimize | status   | OK                                                                |

    +------------------+----------+----------+-------------------------------------------------------------------+

    2 rows in set (0.08 sec)

     

    展开全文
  • MySQL(九):InnoDB 表空间(Tables)

    千次阅读 2020-03-21 21:51:30
    本节着重分析一下表空间,通过本节我们将清楚以下几个问题: 1、什么是表空间(Tablespace)? 2、InnoDB 存储引擎有哪些表空间(Tablespace)? 3、InnoDB 存储引擎中的表空间(Tablespace)有什么用? 4、表空间...

    本节着重分析一下表空间,通过本节我们将清楚以下几个问题:

    1、什么是表空间(Tablespace)?

    2、InnoDB 存储引擎有哪些表空间(Tablespace)?

    3、InnoDB 存储引擎中的表空间(Tablespace)有什么用?

    4、表空间(Tablespace)有哪些组成部分?及各个表空间的作用?

    1、简述

    InnoDB 表空间(Tablespace)可以看做一个逻辑概念,InnoDB 把数据保存在表空间,本质上是一个或多个磁盘文件组成的虚拟文件系统。InnoDB 表空间不仅仅存储了表和索引,它还保存了回滚日志(redo log)、插入缓冲(insert buffer)、双写缓冲(doublewrite buffer)以及其他内部数据结构。

    默认情况下InnoDB存储引擎有一个共享表空间ibdata1,即所有数据都放在这个表空间内。如果我们配置了参数 innodb_file_per_table,则每张表内的数据可以单独放到一个表空间内。其对应的存储文件都放在 innodb_data_home_dir 指定的目录下。

    当启用了 innodb_file_per_table 参数选项,需要注意的是,每张表的表空间内存放的只是数据、索引和插入缓冲,其它的数据,如撤销(Undo)信息、系统事务信息、二次写缓冲(double write buffer)等还是存放在原来的共享表空间内。这也就说明了另一个问题:即使在启用了参数 innodb_file_per_table ,共享表空间还是会不断地增加其大小。

    2、InnoDB 逻辑存储结构

    在这里插入图片描述

    从 InnoDB 逻辑存储结构来看,所有的数据都被逻辑的存放在一个空间中,这个空间就叫做表空间(tablespace)。表空间有 段(segment)、区(extent)、页(page)组成。

    2.1、段(segment)

    段(Segment)分为索引段,数据段,回滚段等。其中索引段就是非叶子结点部分,而数据段就是叶子结点部分,回滚段用于数据的回滚和多版本控制。一个段包含256个区(256M大小)。

    2.2、区(extent)

    区是页的集合,一个区包含64个连续的页,默认大小为 1MB (64*16K)。

    2.3、页(page)

    页是 InnoDB 管理的最小单位,常见的有 FSP_HDR,INODE, INDEX 等类型。所有页的结构都是一样的,分为文件头(前38字节),页数据和文件尾(后8字节)。页数据根据页的类型不同而不一样。

    每个空间都分为多个页,通常每页16 KiB。空间中的每个页面都分配有一个32位整数页码,通常称为“偏移量”(offset),它实际上只是页面与空间开头的偏移量(对于多文件空间,不一定是文件的偏移量)。因此,页面0位于文件偏移量0,页面1位于文件偏移量16384,依此类推。 (InnoDB 的数据限制为64TiB,这实际上是每个空间的限制,这主要是由于页码是32位整数与默认页大小的组合: 2 32 2^{32} 232 x 16 KiB = 64 TiB。)

    如图所示,每个page都有38个字节的FIL header,以及8个字节的FIL trailer(FIL是file的简称)。FIL header包含了一个表示page type的字段,这个字段用来确定这个page数据的结构。

    FIL header和trailer示意图如下所示:

    在这里插入图片描述

    FIL header和trailer包含以下结构:

    • checksum: 4个字节32位checksum保存在header中。

    • Offset: 页面初始化后,页面编号就存储在header中。根据从文件中获得的偏移量检查从该字段读取的页码是否与应该匹配的页码相符,这有助于指示读取正确,并且此字段已初始化表示页面已初始化。

    • Previous/Next Page: 指向此页面类型的逻辑上一页和下一页的指针,被保存在header中。这允许建立页面的双向链接列表,并且它用于INDEX页面以及同一级别的所有页面。

    • page type: 页面类型存储在标题中。为了解析其余页面数据,这是必需的。页面被分配用于文件空间管理,范围管理,事务系统,数据字典,撤消日志,blob,当然还有索引(表数据)。

    • space ID: 保存在header中,space的32位整型唯一编号。

    • Old-style Checksum: 旧格式32位checksum被保存在header中,不过已经被废弃,这块空间被申明为一些指针。

    • LSN: 页的最后修改的64位日志序列号(LSN)存储在header中,而同一LSN的低32位存储在尾部中。

    3、表空间(Tablespace)分类

    3.1、系统表空间

    系统表空间是变更缓冲区的存储区。如果在系统表空间中创建表,而不是在每个表文件或常规表空间中创建表,则它也可能包含表和索引数据。在以前的MySQL版本中,系统表空间包含InnoDB数据字典。在MySQL 8.0中,InnoDB将元数据存储在MySQL数据字典中。在以前的MySQL版本中,系统表空间还包含doublewrite 缓冲区存储区。自MySQL 8.0.20起,此存储区位于单独的 doublewrite 文件中。

    系统表空间可以有一个或多个数据文件。默认情况下,在数据目录中创建一个名为 ibdata1 的系统表空间数据文件。系统表空间数据文件的大小和数量由 innodb_data_file_path 启动项定义。

    归纳一下,系统表空间包含 数据字典,双写缓冲,变更缓冲区、undo日志,以及在系统表空间创建的表的数据和索引。

    系统空间(space 0)在InnoDB中是特殊的,以固定页码分配的相当多页面,以存储对InnoDB操作至关重要的各种信息。由于系统空间是一个与其他空间一样的空间,它的前三个页面包括: FSP_HDR,IBUF_BITMAP 和 INODE页。

    在这里插入图片描述

    3.1、独占表空间

    独立表空间就是每个表单独创建一个 .ibd 文件,该文件存储着该表的索引和数据。由 innodb_file_per_table 变量控制。禁用 innodb_file_per_table 会导致InnoDB在系统表空间中创建表。

    innodb_file_per_table 设置可以在配置文件中指定,也可以在运行时使用 SET GLOBAL 语句进行配置。在运行时更改设置需要足够的权限来设置全局系统变量

    通过配置文件设置:

    [mysqld]
    innodb_file_per_table=ON
    

    运行时设置:

    mysql> SET GLOBAL innodb_file_per_table=ON;
    

    3.1.1、独占表空间文件结构

    在这里插入图片描述

    InnoDB 表空间文件 .ibd 初始大小为 96K,而InnoDB默认页大小为 16K,页大小也可以通过 innodb_page_size 配置。在ibd文件中,0-16KB偏移量即为0号数据页,16KB-32KB的为1号数据页,以此类推。页的头尾除了一些元信息外,还有C hecksum 校验值,这些校验值在写入磁盘前计算得到,当从磁盘中读取时,重新计算校验值并与数据页中存储的对比,如果发现不同,则会导致 MySQL 崩溃。

    InnoDB页分为INDEX页、Undo页、系统页,IBUF_BITMAP页, INODE页等多种。

    • 第0页是 FSP_HDR 页,主要用于跟踪表空间,空闲链表、碎片页以及区等信息。
    • 第1页是 IBUF_BITMAP 页,保存Change Buffer的位图。
    • 第2页是 INODE 页,用于存储区和单独分配的碎片页信息,包括FULL、FREE、NOT_FULL 等页列表的基础结点信息,这些结点指向的是 FSP_HDR 页中的项,用于记录页的使用情况,它们之间关系如下图所示。
    • 第3页开始是索引页 INDEX(B-tree node),从 0xc000(每页16K) 开始,后面还有些分配的未使用的页。

    3.1.2、独占表空间优点

    与共享表空间(例如系统表空间或常规表空间)相比,独占表空间具有以下优点。

    1、使用独占表空间,删除表后可以回收所占的磁盘空间。如果使用共享表空间的话,删除表后,共享表空间数据文件的大小不会缩小。

    2、对驻留在共享表空间中的表进行 ALTER TABLE 操作可能会增加该表空间占用的磁盘空间量。此类操作可能需要与表中的数据以及索引一样多的额外空间。该空间不会像独占表空间那样释放占用的磁盘。

    3、在驻留在独占表空间的表上执行操作时,TRUNCATE TABLE性能更好。

    4、可以在单独的存储设备上创建独占表空间数据文件,以进行I/O优化,空间管理或备份。

    5、可以从另一个MySQL实例导入独占表空间中的表。

    6、在独占表空间中创建的表支持与 DYNAMIC 和 COMPRESSED 行格式相关联的功能,而系统表空间不支持这些功能。

    7、当发生数据损坏,备份或二进制日志不可用或无法重新启动MySQL服务器实例时,存储在独占表空间数据文件中的表可以节省时间并提高成功恢复的机会。

    8、可以使用MySQL Enterprise Backup快速备份或还原在独占表空间中创建的表,而不会中断其他InnoDB表的使用。这对于具有不同备份计划的表或需要较少备份频率的表很有用。

    9、独占表空间允许通过监视表空间数据文件的大小来监视文件系统上的表大小。

    10、当 innodb_flush_method 设置为O_DIRECT时,常见的Linux文件系统不允许并发写入单个文件,例如共享表空间数据文件。因此,结合使用此表时,可以使用独占表空间来提高性能。

    11、共享表空间中的表的大小受64TB表空间大小限制。相比之下,每个表的每个文件表空间都有64TB的大小限制,这为单个表的大小增加提供了足够的空间。

    3.1.3、独占表空间缺点

    与共享表空间(例如系统表空间或常规表空间)相比,独占表空间具有以下缺点。

    1、使用独占表空间,每个表可能有未使用的空间,只能由同一表的行使用,如果管理不当,则会浪费空间。

    2、fsync 操作是对多个独占表空间数据文件而不是单个共享表空间数据文件执行的。由于fsync操作是针对每个文件的,因此无法合并多个表的写操作,这可能导致fsync操作的总数增加。

    3、mysqld 必须为每个表文件空间保留一个打开的文件句柄,如果每个表文件空间中有许多表,则可能会影响性能。

    4、每个表都有其自己的数据文件时,需要更多的文件描述符。

    5、可能存在更多碎片,这可能会妨碍 DROP TABLE和表扫描性能。但是,如果管理碎片,则独占表空间可以提高这些操作的性能。

    6、删除驻留在独占表空间中的表时,将扫描缓冲池,对于大型缓冲池可能要花费几秒钟。使用宽泛的内部锁定执行扫描,这可能会延迟其他操作。

    7、innodb_autoextend_increment 变量定义了增量大小,用于在自动扩展共享表空间文件已满时扩展其大小,该变量不适用于独占表空间文件,无论innodb_autoextend_increment 设置如何,该文件均会自动扩展。独占表空间的初始文件表扩展很小,此后扩展以4MB为增量。

    3.2、常规表空间

    常规表空间是使用 CREATE TABLESPACE 语法创建的共享InnoDB表空间.

    常规表空间提供了以下功能:

    • 类似于系统表空间,常规表空间是共享表空间,可以存储多个表的数据

    • 常规表空间比独占表空间具有潜在的内存优势。服务器在表空间的生存期内将表空间元数据保留在内存中。与独占表空间中的相同数量的表相比,常规表空间中的多个表元数据消耗的内存更少。

    • 常规表空间数据文件可以放置在相对于MySQL数据目录或独立于MySQL数据目录的目录中,该目录为您提供了许多数据文件和独占表空间的存储管理功能。与独占表空间一样,将数据文件放置在MySQL数据目录之外的功能使您可以分别管理关键表的性能,为特定表设置RAID或DRBD或将表绑定到特定磁盘。

    • 常规表空间支持所有表行格式和相关功能。

    • TABLESPACE选项可与CREATE TABLE一起使用,以在常规表空间,独占表空间或系统表空间中创建表。

    • TABLESPACE选项可与ALTER TABLE一起使用,以在常规表空间,独占表空间和系统表空间之间移动表。以前,不可能将表从独占表空间移动到系统表空间。使用常规表空间功能,您现在可以这样做。

    3.2.1、创建常规表空间

    常规表空间是使用CREATE TABLESPACE语法创建的。

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

    常规表空间可以在数据目录中或在其外部创建。为避免与隐式创建的独占表空间冲突,不支持在数据目录下的子目录中创建常规表空间。在数据目录之外创建常规表空间时,该目录必须存在并且在创建表空间之前InnoDB必须知道。要使InnoDB知道未知目录,请将目录添加到 innodb_directories 参数值。 innodb_directories 是只读的启动选项。配置它需要重新启动服务器。

    示例:

    1、在数据目录中创建常规表空间:

    mysql> CREATE TABLESPACE `ts1` ADD DATAFILE 'ts1.ibd' Engine=InnoDB;
    

    mysql> CREATE TABLESPACE `ts1` Engine=InnoDB;
    

    从MySQL 8.0.14开始,ADD DATAFILE 子句是可选的,在此之前是必需的。如果在创建表空间时未指定ADD DATAFILE 子句,则会隐式创建具有唯一文件名的表空间数据文件。唯一文件名是128位UUID,格式为五组十六进制数字,中间用破折号(aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeeeee)分隔。常规表空间数据文件包括.ibd 文件扩展名。在复制环境中,在主服务器上创建的数据文件名与在从属服务器上创建的数据文件名不同。

    2、在数据目录之外的目录中创建常规表空间:

    mysql> CREATE TABLESPACE `ts1` ADD DATAFILE '/my/tablespace/directory/ts1.ibd' Engine=InnoDB;
    

    您可以指定相对于数据目录的路径,只要表空间目录不在数据目录下即可。在此示例中,my_tablespace目录与数据目录处于同一级别:

    mysql> CREATE TABLESPACE `ts1` ADD DATAFILE '../my_tablespace/ts1.ibd' Engine=InnoDB;
    

    注意:ENGINE = InnoDB子句必须定义为CREATE TABLESPACE语句的一部分,或者InnoDB必须定义为默认存储引擎(default_storage_engine = InnoDB)。

    3.2.2、将表添加到常规表空间

    创建InnoDB常规表空间后,可以使用 CREATE TABLE tbl_name … TABLESPACE [=] tablespace_nameALTER TABLE tbl_name TABLESPACE [=] tablespace_name 将表添加到表空间,如以下示例所示:

    CREATE TABLE:

    mysql> CREATE TABLE t1 (c1 INT PRIMARY KEY) TABLESPACE ts1;
    

    ALTER TABLE:

    mysql> ALTER TABLE t2 TABLESPACE ts1;
    

    注意:在MySQL 5.7.24中弃用了将表分区添加到共享表空间的支持,在MySQL 8.0.13中不再支持。共享表空间包括InnoDB系统表空间和常规表空间。

    3.2.3、常规表空间支持的行格式

    常规表空间支持所有表行格式(冗余,紧凑,动态,压缩:【REDUNDANT, COMPACT, DYNAMIC, COMPRESSED】),但要注意的是,由于物理页大小不同,压缩表和未压缩表不能在同一常规表空间中共存。

    要使常规表空间包含压缩表(ROW_FORMAT = COMPRESSED),必须指定 FILE_BLOCK_SIZE,并且 FILE_BLOCK_SIZE 值必须是相对于 innodb_page_size 值的有效压缩页大小。另外,压缩表的物理页面大小(KEY_BLOCK_SIZE)必须等于 FILE_BLOCK_SIZE / 1024。例如,如果 innodb_page_size = 16KBFILE_BLOCK_SIZE = 8K,则表的 KEY_BLOCK_SIZE 必须为8。

    下表显示了允许的 innodb_page_sizeFILE_BLOCK_SIZEKEY_BLOCK_SIZE 组合。 FILE_BLOCK_SIZE 值也可以以字节为单位指定。要确定给定FILE_BLOCK_SIZE的有效KEY_BLOCK_SIZE值,请将FILE_BLOCK_SIZE值除以1024。表压缩不支持32K和64K InnoDB页面大小。

    在这里插入图片描述

    3.2.4、常规表空间限制

    1、生成的或现有的表空间无法更改为常规表空间。

    2、不支持创建临时常规表空间。

    3、常规表空间不支持临时表。

    4、与系统表空间类似,存储在常规表空间中的表被删除会在常规表空间 .ibd 数据文件内部创建可用空间,该空闲空间仅可用于新的InnoDB数据。独占表空间的空间不会释放回操作系统。

    此外,对驻留在共享表空间(常规表空间或系统表空间)中的表进行表复制ALTER TABLE操作可能会增加表空间使用的空间量。此类操作需要与表中的数据以及索引一样多的额外空间。复制表ALTER TABLE操作所需的额外空间不会像释放独占表空间那样释放回操作系统。

    5、属于常规表空间的表不支持ALTER TABLE … DISCARD TABLESPACE和ALTER TABLE … IMPORT TABLESPACE。

    6、在MySQL 5.7.24中弃用了将表分区放置在常规表空间中的支持,在MySQL 8.0.13中已删除。

    7、在主服务器和从服务器位于同一主机上的复制环境中,不支持ADD DATAFILE子句,因为它将导致主服务器和从服务器在同一位置创建同名的表空间,这不支持。但是,如果省略了ADD DATAFILE子句,则在数据目录中创建表空间,并使用允许的唯一生成的文件名。

    3.3、Undo 表空间

    Undo 表空间包含 undo log,Undo 表空间是撤消日志记录的集合,其中包含有关如何通过事务撤消对聚集索引记录的最新更改的信息。撤消日志存在于撤消日志段中,撤消日志段中包含撤消日志段。 innodb_rollback_segments 变量定义分配给每个撤消表空间的回滚段数。

    初始化MySQL实例时,会创建两个默认的Undo 表空间。默认的Undo 表空间是在初始化时创建的,以提供回滚段的位置,这些段必须存在才能接受SQL语句。至少需要两个Undo 表空间才能支持Undo 表空间的自动截断。

    默认的Undo 表空间在 innodb_undo_directory 变量定义的位置中创建。如果未定义 innodb_undo_directory 变量,则会在数据目录中创建默认的Undo 表空间。默认的Undo 表空间数据文件名为 undo_001 和 undo_002。数据字典中定义的相应Undo 表空间名称是 innodb_undo_001 和 innodb_undo_002。

    Undo 表空间数据文件的初始大小取决于 innodb_page_size 值。对于默认的16KB页面大小,初始撤消表空间文件大小为10MiB。对于4KB,8KB,32KB和64KB页面大小,初始Undo 表空间文件大小分别为7MiB,8MiB,20MiB和40MiB。

    关于Undo 表空间更多操作参见:https://dev.mysql.com/doc/refman/8.0/en/innodb-undo-tablespaces.html

    3.4、临时表空间

    InnoDB 临时表空间包括会话临时表空间和全局临时表空间

    3.4.1、会话临时表空间

    当将InnoDB配置为磁盘内部临时表的存储引擎时,会话临时表空间将存储用户创建的临时表和由优化程序创建的内部临时表。从MySQL 8.0.16开始,用于磁盘内部临时表的存储引擎始终是InnoDB。 (以前,存储引擎由internal_tmp_disk_storage_engine的值确定。)

    在创建磁盘临时表的第一个请求上,会话临时表空间从临时表空间池分配给会话。最多可将两个表空间分配给一个会话,一个用于用户创建的临时表,另一个用于由优化程序创建的内部临时表。分配给会话的临时表空间用于该会话创建的所有磁盘上的临时表。当会话断开连接时,其临时表空间将被删除并释放回池中。启动服务器时,将创建10个临时表空间的池。池的大小永远不会缩小,并且表空间会根据需要自动添加到池中。在正常关闭或初始化中止时,将删除临时表空间池。会话临时表空间文件在创建时大小为5页,并具有.ibt文件扩展名。

    为会话临时表空间保留了40万个空间ID。因为每次启动服务器时都会重新创建会话临时表空间池,所以在关闭服务器时,会话临时表空间的空间ID不会保留,并且可以重新使用。

    innodb_temp_tablespaces_dir 变量定义了创建会话临时表空间的位置。默认位置是数据目录中的#innodb_temp目录。如果无法创建临时表空间池,则拒绝启动。

    shell> cd BASEDIR/data/#innodb_temp
    shell> ls
    temp_10.ibt  temp_2.ibt  temp_4.ibt  temp_6.ibt  temp_8.ibt
    temp_1.ibt   temp_3.ibt  temp_5.ibt  temp_7.ibt  temp_9.ibt
    

    在基于语句的复制(SBR)模式下,在从属服务器上创建的临时表驻留在单个会话临时表空间中,该表空间仅在MySQL服务器关闭时才被中断。

    INNODB_SESSION_TEMP_TABLESPACES 表提供有关会话临时表空间的元数据。

    INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO 表提供有关在InnoDB实例中处于活动状态的用户创建的临时表的元数据。

    3.4.2、全局临时表空间

    全局临时表空间(ibtmp1)存储回滚段,以对用户创建的临时表进行更改。

    innodb_temp_data_file_path 变量定义全局临时表空间数据文件的相对路径,名称,大小和属性。如果没有为innodb_temp_data_file_path指定值,则默认行为是在 innodb_data_home_dir 目录中创建一个名为ibtmp1的自动扩展数据文件。初始文件大小略大于12MB。

    全局临时表空间在正常关闭或初始化中止时被删除,并在每次启动服务器时重新创建。全局临时表空间在创建时会接收动态生成的空间ID。如果无法创建全局临时表空间,则拒绝启动。如果服务器意外停止,则不会删除全局临时表空间。在这种情况下,数据库管理员可以手动删除全局临时表空间或重新启动MySQL服务器。重新启动MySQL服务器会自动删除并重新创建全局临时表空间。

    全局临时表空间不能驻留在原始设备上。

    INFORMATION_SCHEMA.FILES提供有关全局临时表空间的元数据。发出与此查询类似的查询以查看全局临时表空间元数据:

    mysql> SELECT * FROM INFORMATION_SCHEMA.FILES WHERE TABLESPACE_NAME='innodb_temporary'\G
    

    默认情况下,全局临时表空间数据文件是自动扩展的,并根据需要增加大小。要确定全局临时表空间数据文件是否正在自动扩展,请检查 innodb_temp_data_file_path 设置:

    mysql> SELECT @@innodb_temp_data_file_path;
    +------------------------------+
    | @@innodb_temp_data_file_path |
    +------------------------------+
    | ibtmp1:12M:autoextend        |
    +------------------------------+
    

    要检查全局临时表空间数据文件的大小,请使用与此类似的查询来查询INFORMATION_SCHEMA.FILES表:

    mysql> SELECT FILE_NAME, TABLESPACE_NAME, ENGINE, INITIAL_SIZE, TOTAL_EXTENTS*EXTENT_SIZE 
           AS TotalSizeBytes, DATA_FREE, MAXIMUM_SIZE FROM INFORMATION_SCHEMA.FILES 
           WHERE TABLESPACE_NAME = 'innodb_temporary'\G
    *************************** 1. row ***************************
          FILE_NAME: ./ibtmp1
    TABLESPACE_NAME: innodb_temporary
             ENGINE: InnoDB
       INITIAL_SIZE: 12582912
     TotalSizeBytes: 12582912
          DATA_FREE: 6291456
       MAXIMUM_SIZE: NULL
    

    TotalSizeBytes 显示全局临时表空间数据文件的当前大小。

    检查操作系统上的全局临时表空间数据文件大小。全局临时表空间数据文件位于 innodb_temp_data_file_path 变量定义的目录中。

    要回收全局临时表空间数据文件占用的磁盘空间,请重新启动MySQL服务器。重新启动服务器会根据 innodb_temp_data_file_path 定义的属性删除并重新创建全局临时表空间数据文件。

    要限制全局临时表空间数据文件的大小,请配置innodb_temp_data_file_path以指定最大文件大小。例如:

    [mysqld]
    innodb_temp_data_file_path=ibtmp1:12M:autoextend:max:500M
    

    配置 innodb_temp_data_file_path 要求重新启动服务器。

    4、参考文献

    1. 《高性能MySQL(第3版)》
    2. 《MySQL技术内幕:InnoDB存储引擎(第2版)》
    3. 《MySQL源码库》
    4. 《MySQL参考手册》
    5. 《MySQL实战45讲》
    6. 《数据库内核月报》
    7. The basics of InnoDB space file layout
    展开全文
  • select table , sum(rows) / 2, formatReadableSize(sum(bytes_on_disk)) AS size from system.parts_all where database = 'system' group by table order by size ASC;
  • Oracle表空间数据文件大小设置

    千次阅读 2018-06-13 15:01:50
     表空间数据文件容量与DB_BLOCK_SIZE有关,在初始建库时,DB_BLOCK_SIZE要根据实际需要,设置为 4K,8K、16K、32K、64K等几种大小,ORACLE的物理文件最大只允许4194304个数据块(由操作系统决定),表空间数据文件的...
  • 上执行任何大容量的Delete,DROP或Truncate操作时,可能会为数据文件提供大量可用空间。 也可以通过删除巨大的索引来回收可用空间。 Query as above should be performed in each database to monitor the free...
  • 由于临时在开发环境创建俩张表,总数据量大概400多万,测试完后把drop掉,但是发现服务器磁盘被占满了,还剩下几M可用,因为这个盘也存储oracle的数据文件,所以去检查了一下数据文件的大小,果然E:\app\...
  • mysql查询数据使用空间大小

    千次阅读 2018-09-19 16:09:30
    select truncate(sum(data_length)/1024/1024,2) as data_size,truncate(sum(index_length)/1024/1024,2) as index_size,truncate(sum(index_length+data_length)/1024/1024,2) as total,table_schema from informat...
  • Python3获取当前磁盘可用空间

    千次阅读 2020-06-01 20:28:34
    os.statvfs方法只适用于Linux系统 开发环境 RaspberryPi 4B ...print(f'可用磁盘空间:{free_size}MB') 与直接使用系统df命令查看到的结果一致 变量解读 f_bsize是当前文件系统中数据块的大小,单位是B(字节.
  • SQLServer数据库收缩/可用空间查询

    千次阅读 2018-06-27 11:11:12
    2.大多数数据库都需要一些可用空间,以供常规日常操作使用。如果反复收缩数据库并注意到数据库大小变大,则表明收缩的空间是常规操作所必需的。在这种情况下,反复收缩数据库是一种无谓的操作。3.收缩操作不会保留...
  • 解决oracle临时表空间的报错

    千次阅读 2021-05-02 00:49:42
    报错信息:[HY000](1652) [Oracle][ODBC][Ora]ORA-01652:无法通过128 (在表空间TEMP中)扩展temp段原因分析:oracle临时表空间不足,事务执行一般将会报ora-01652无法扩展临时段的错误。因为oracle总是尽量分配连续...
  • Oracle表空间数据文件

    万次阅读 2018-01-17 18:33:18
    读书使人充实,思考使人深邃,交谈使人清醒!-----------《富兰克林》 ...Oracle整个体系结构中,数据文件就是数据库的记忆,保存着修改的结果,所有的数据归根都要回到其中,数据文件一旦损坏就会导致数据丢失。
  • 有些小白在操作数据库时随便新建表空间,并且喜欢直接设置最大值把表空间文件直接设置成32G,而后在导库的时候又没进行正确操作导致新建的表空间并未使用,而把数据又全部导入users表空间了,这样就造成数据库服务器...
  • Oracle 表空间数据文件之间的关系

    千次阅读 2019-06-06 17:35:11
    首先,需要明白的一点是:数据库的物理结构是由数据库的操作系统文件所决定,每一个Oracle数据库是由三种类型的文件组成:数据文件、日志文件和控制文件。数据库的文件为数据库信息提供真正的物理存储。 每一个...
  • CDH角色的日志目录的可用空间不足

    千次阅读 2018-12-03 11:39:50
    此角色的日志目录所在的文件系统的可用空间小于10.0吉字节。 /var/log/hadoop-httpfs /dfs /tmp/xxx 到某台节点查看,df -h 可以看到由于数据都挂载在根目录,而home目录却空闲了100多个G 故在home目录下...
  • 修改表空间(修改数据文件的自动扩展性)  在创建表空间时,可以设置数据文件的自动扩展性。在为表空间增加新的数据文件时,也 可以设置新数据文件的自动扩展性。而对于已创建的表空间中的已有的数据文件,则...
  • SqlServer 2008 R2在还原数据库时提示所指定的文件夹内存不足,无法创建...解决方案:找电脑中一个磁盘空间足够大的盘(剩余内存大于报错信息提示的尚需内存)。新建一个文件夹data,文件夹中新建两个text文件。 ...
  • 其中一些磁盘会将一些文件数据放入其中d盘数据恢复,其容量将不是0字节,但是一些网友说,当我不能打开本地磁盘,磁盘属性显示磁盘可用空间为0字节,已用空间也为0字节. 我该怎么办?如果其中包含非常重要的数据,...
  • 创建表空间

    千次阅读 2018-04-15 11:15:23
    (四)、创建表空间1. 创建表空间要考虑的因素创建数据库完毕~ 创建所需的 ...数据文件数目 ~只能创建有限数的表空间表空间数据文件总和 ~ 不能超过 创建数据库时 ~指定的maxdatafiles参数的限制创建的表空间 ~...
  • Oracle表空间的作用

    千次阅读 2019-09-09 21:37:19
    Oracle表空间的作用 一 Oracle表空间的概念 ORACLE数据库被划分成称作为表空间的逻辑区域——形成ORACLE数据库的逻辑结构。 一个ORACLE数据库能够有一个或多个表空间,而一个表空间则对应着一个或多个物理的数据库...
  • 可用空间0字节说明这个盘的文件系统结构损坏了。在平时如果数据不重要,那么可以直接格式化就能用了。但是有的时候里面的数据很重要,那么就必须先恢复出数据再格式化。具体恢复方法可以看正文了解(不格式化的恢复...
  • Oracle之数据字典、表空间的操作、视图
  • 然后在安装Ubuntu过程中进行分区时,我首先选中200G的空闲分区,点击’+‘进行编辑分区,分完400M的/boot(主分区)后,剩下的空间就变成了不可用了。 原因分析: linux系统最多只能有4个主分区,说明此时你电脑...
  • oracle创建表空间

    千次阅读 2021-01-27 09:10:12
    不同于mysql有多个数据库, oracle新增了表空间和用户的概念,表空间是虚拟的,并且只有一个数据库,为了方便维护,一般一个表空间上只创建一个用户; 这里的用户名类似于mysql的数据库名,oracle的用户名.表名等价...
  • [GIS原理] 3 空间数据模型

    千次阅读 多人点赞 2018-11-22 20:31:05
    文章目录相关概念空间数据模型地理空间空间现象空间实体地理空间空间抽象概念数据模型逻辑数据模型物理数据模型空间数据结构GIS所考虑的对象地理空间数据的概念模型对象模型/要素模型场模型网络模型选择空间数据...
  • 数据库的逻辑存储结构(表空间等)决定了数据库的物理空间是如何被使用的,数据库对象如、索引等分布在各个表空间中。  Oracle 数据库的物理结构从操作系统一级查看,是由一个个的文件组成,从物理上可划分为:数据...
  • ORACLE查询每个占用空间大小

    千次阅读 2021-04-30 07:52:26
    SELECT 'select sum(bytes) FROM dba_segments WHERE owner ='|| '"TESTBAR"'||' AND segment_type ='|| '"TABLE"'||' AND segment_name = "'|| table_name...错误的,对于oracle而言,双引号要用单引号,可用可以用...
  • ORACLE 表空间操作和的删除(一)

    千次阅读 2018-03-28 23:17:48
    最近在用IMP 命令导入dmp数据的时候,发现表空间无法扩展,一查,才发现表空间SYSTEM已经使用了31.2G。 经过查找资料,总结如下: 1.一般不在SYSTEM表空间存放业务数据; 2.SYSTEM表空间最大值是32G;-----所有...
  • Oracle创建表空间

    万次阅读 2018-12-24 13:28:44
    创建表空间 ORACLE物理上是由磁盘上的以下几种文件:数据文件和控制文件和LOGFILE构成的 oracle中的就是一张存储...当然数据存放在磁盘最终是以文件形式,所以一盘一个数据表空间包含一个以上的物理文件 数据...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 680,860
精华内容 272,344
关键字:

表的数据可用空间