`
hejiajunsh
  • 浏览: 414193 次
  • 性别: Icon_minigender_1
  • 来自: 天津
社区版块
存档分类
最新评论

名词解释system tablespace/ib_logfile/.ibd file/ibdata file/storage engine/tablespace

阅读更多

出自:http://dev.mysql.com/doc/refman/5.5/en/glossary.html

 

 

 

system tablespace

A small set of data files (the ibdata files) containing the metadata for InnoDB-related objects (the data dictionary), and the storage areas for the undo log, the change buffer, and the doublewrite buffer. Depending on the setting of the innodb_file_per_table, when tables are created, it might also contain table and index data for some or all InnoDB tables. The data and metadata in the system tablespace apply to all the databases in a MySQL instance.

Prior to MySQL 5.6.7, the default was to keep all InnoDB tables and indexes inside the system tablespace, often causing this file to become very large. Because the system tablespace never shrinks, storage problems could arise if large amounts of temporary data were loaded and then deleted. In MySQL 5.6.7 and higher, the default is file-per-tablemode, where each table and its associated indexes are stored in a separate .ibd file. This new default makes it easier to use InnoDB features that rely on the Barracuda file format, such as table compression and the DYNAMIC row format.

In MySQL 5.6 and higher, setting a value for the innodb_undo_tablespaces option splits the undo log into one or more separate tablespace files. These files are still considered part of the system tablespace.

Keeping all table data in the system tablespace or in separate .ibd files has implications for storage management in general. The MySQL Enterprise Backup product might back up a small set of large files, or many smaller files. On systems with thousands of tables, the filesystem operations to process thousands of .ibd files can cause bottlenecks.

 

 

ib_logfile

A set of files, typically named ib_logfile0 and ib_logfile1, that form the redo log. Also sometimes referred to as the log group. These files record statements that attempt to change data in InnoDB tables. These statements are replayed automatically to correct data written by incomplete transactions, on startup following a crash.

This data cannot be used for manual recovery; for that type of operation, use the binary log.

 

.ibd file

Each InnoDB table created using the file-per-table mode goes into its own tablespace file, with a .ibd extension, inside the database directory. This file contains the table data and any indexes for the table. File-per-table mode, controlled by the innodb_file_per_table option, affects many aspects of InnoDB storage usage and performance, and is enabled by default in MySQL 5.6.7 and higher.

This extension does not apply to the system tablespace, which consists of the ibdata files.

 

ibdata file

A set of files with names such as ibdata1ibdata2, and so on, that make up the InnoDB system tablespace. These files contain metadata about InnoDB tables, (the data dictionary), and the storage areas for the undo log, the change buffer, and the doublewrite buffer. They also can contain some or all of the table data also (depending on whether the file-per-table mode is in effect when each table is created). When the innodb_file_per_table option is enabled, data and indexes for newly created tables are stored in separate.ibd files rather than in the system tablespace.

The growth of the ibdata files is influenced by the innodb_autoextend_increment configuration option.

 

data files

The files that physically contain the InnoDB table and index data. There can be a one-to-many relationship between data files and tables, as in the case of the system tablespace, which can hold multiple InnoDB tables as well as the data dictionary. There can also be a one-to-one relationship between data files and tables, as when the file-per-table setting is enabled, causing each newly created table to be stored in a separate tablespace.

 

database

Within the MySQL data directory, each database is represented by a separate directory. The InnoDB system tablespace, which can hold table data from multiple databases within a MySQL instance, is kept in its data files that reside outside the individual database directories. When file-per-table mode is enabled, the .ibd files representing individual InnoDB tables are stored inside the database directories.

For long-time MySQL users, a database is a familiar notion. Users coming from an Oracle Database background will find that the MySQL meaning of a database is closer to what Oracle Database calls a schema.

 

storage engine

A component of the MySQL database that performs the low-level work of storing, updating, and querying data. In MySQL 5.5 and higher, InnoDB is the default storage engine for new tables, superceding MyISAM. Different storage engines are designed with different tradeoffs between factors such as memory usage versus disk usage, read speed versus write speed, and speed versus robustness. Each storage engine manages specific tables, so we refer to InnoDB tables, MyISAM tables, and so on.

 

tablespace

A data file that can hold data for one or more InnoDB tables and associated indexes. The system tablespace contains the tables that make up the data dictionary, and prior to MySQL 5.6 holds all the other InnoDB tables by default. Turning on the innodb_file_per_table option, the default in MySQL 5.6 and higher, allows newly created tables to each have their own tablespace, with a separate data file for each table.

 

 

 

 

 

 

分享到:
评论

相关推荐

    MySQL启动报错问题InnoDB:Unable to lock/ibdata1 error

    5. **InnoDB日志文件问题**:InnoDB使用重做日志文件(`ib_logfile0`和`ib_logfile1`)进行事务持久化。如果这些文件损坏或无法访问,也可能导致启动失败。检查日志文件的状态。 6. **数据文件损坏**:`ibdata1`...

    Mysql innodb tablespace 表空间实践

    ### MySQL Innodb Tablespace 表空间实践 #### 知识点概述 - **表空间(Tablespace)**:在MySQL的InnoDB存储引擎中,表空间是用来管理数据存储的一种方式。它提供了对物理磁盘上数据文件组织的一种逻辑视图。 - *...

    MySQL数据库文件放在哪里.docx

    6. **日志文件**:MySQL的日志文件通常有`ib_logfile0`和`ib_logfile1`,它们是InnoDB引擎的重做日志文件,用于记录事务操作,以便在系统崩溃后进行恢复。 7. **其他文件**:MySQL还有其他类型的文件,如二进制日志...

    MySQL数据库innodb启动失败无法重启的解决方法

    1. **不正常的关闭**:如果MySQL数据库在上次关闭时没有正常停止,可能会导致InnoDB的日志文件(redo log)和数据文件不一致,需要进行崩溃恢复。 2. **数据文件损坏**:磁盘问题或系统崩溃可能会导致数据文件或...

Global site tag (gtag.js) - Google Analytics