5.7拥有的6种Tablespaces
1. system tablespace
The InnoDB system tablespace contains the InnoDB data dictionary (metadata for InnoDB-related
objects) and is the storage area for the doublewrite buffer, the change buffer, and undo logs. The
system tablespace also contains table and index data for any user-created tables that are created in the system tablespace. The system tablespace is considered a shared tablespace since it is shared by multiple tables. The system tablespace is represented by one or more data files. By default, one system data file,named ibdata1, is created in the MySQL data directory. The size and number of system data files is controlled by the innodb_data_file_path startup option.
增大系统表空间
mysql下载什么版本的The easiest way to increase the size of the InnoDB system tablespace is to configure it from
the beginning to be auto-extending. Specify the autoextend attribute for the last data file in
the tablespace definition. Then InnoDB increases the size of that file automatically in 64MB
increments when it runs out of space. The increment size can be changed by setting the value of the innodb_autoextend_increment system variable, which is measured in megabytes
You can expand the system tablespace by a defined amount by adding another data file:
1. Shut down the MySQL server.
2. If the previous last data file is defined with the keyword autoextend, change its definitio
n to use a fixed size, based on how large it has actually grown. Check the size of the data file, round it down to the closest multiple of 1024 × 1024 bytes (= 1MB), and specify this rounded size explicitly in innodb_data_file_path.
3. Add a new data file to the end of innodb_data_file_path, optionally making that file autoextending.Only the last data file in the innodb_data_file_path can be specified as autoextending.
4. Start the MySQL server again.
For example, this tablespace has just one auto-extending data file ibdata1:
innodb_data_home_dir =
innodb_data_file_path = /ibdata/ibdata1:10M:autoextend
Suppose that this data file, over time, has grown to 988MB. Here is the configuration line after
modifying the original data file to use a fixed size and adding a new auto-extending data file:
innodb_data_home_dir =
innodb_data_file_path = /ibdata/ibdata1:988M;/disk2/ibdata2:50M:autoextend
When you add a new data file to the system tablespace configuration, make sure that the filename does not refer to an existing file. InnoDB creates and initializes the file when you restart the server.
缩小系统表空间
逻辑备份，导出导入。
2. file-per-table tablespaces
Historically, all InnoDB tables and indexes were stored in the system tablespace. This mo
nolithic
approach was targeted at machines dedicated entirely to database processing, with carefully planned data growth, where any disk storage allocated to MySQL would never be needed for other purposes.InnoDB's file-per-table tablespace feature provides a more flexible alternative, where each InnoDB table and its indexes are stored in a separate .ibd data file. Each such .ibd data file represents an individual tablespace. This feature is controlled by the innodb_file_per_table configuration option, which is enabled by default in MySQL 5.6.6 and higher.
开启独立表空间的优势：
• You can reclaim disk space when truncating or dropping a table stored in a file-per-table tablepace.Truncating or dropping tables stored in the shared system tablespace creates free space internally in the system tablespace data files (ibdata files) which can only be used for new InnoDB data.Similarly, a table-copying ALTER TABLE operation on table that resides in a shared tablespace can increase the amount of space used by the tables
pace. Such operations may require as much additional space as the data in the table plus indexes. The additional space required for the tablecopying ALTER TABLE operation is not released back to the operating system as it is for file-pertable tablespaces.
• The TRUNCATE TABLE operation is faster when run on tables stored in file-per-table tablepaces.
• You can store specific tables on separate storage devices, for I/O optimization, space management,or backup purposes. In previous releases, you had to move entire database directories to other drives and create symbolic links in the MySQL data directory. In MySQL 5.6.6 and higher, you can specify the location of each table using the syntax CREATE TABLE ... DATA DIRECTORY = absolute_path_to_directory.
• You can run OPTIMIZE TABLE to compact or recreate a file-per-table tablespace. When you run an OPTIMIZE TABLE, InnoDB creates a new .ibd file with a temporary name, using only the space
required to store actual data. When the optimization is complete, InnoDB removes the old .ibd file and replaces it with the new one. If the previous .ibd file grew significantly but the actual data only accounted for a portion of its size, running OPTIMIZE TABLE can reclaim the unused space.

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。