Mysql学习_InnoDB On-Disk Structures_TABLE

InnoDB On-Disk Structures
Table

Creating InnoDB Tables：
CREATE TABLE t1 (a INT, b CHAR (20), PRIMARY KEY (a)) ENGINE=InnoDB;

You do not need to specify the ENGINE=InnoDB clause if InnoDB is defined as the default storage engine, which it is by default. 
You might still use ENGINE=InnoDB clause if you plan to use mysqldump or replication to replay the CREATE TABLE statement on a server where the default storage engine is not InnoDB.
When innodb_file_per_table is enabled, which is the default, an InnoDB table is implicitly created in an individual file-per-table tablespace. Conversely,when innodb_file_per_table is disabled, an InnoDB table is implicitly created in the InnoDB system tablespace. To create a table in a general tablespace, use CREATE TABLE ... TABLESPACE syntax. 
When you create an InnoDB table, MySQL creates a .frm file in the database directory under the MySQL data directory. 
For a table created in a file-per-table tablespace, MySQL also creates an .ibd tablespace file in the database directory, by default. A table created in the InnoDB system tablespace is created in an existing ibdata file, which resides in the MySQL data directory. A table created in a general tablespace is created in an existing general tablespace .ibd file. General tablespace files can be created inside or outside of the MySQL data directory. 

InnoDB Tables and .frm Files：
MySQL stores data dictionary information for tables in .frm files in database directories. Unlike other MySQL storage engines, InnoDB also encodes information about the table in its own internal data dictionary inside the system tablespace. 
When MySQL drops a table or a database, it deletes one or more .frm files as well as the corresponding entries inside the InnoDB data dictionary. You cannot move InnoDB tables between databases simply by moving the .frm files.

InnoDB Tables and Row Formats：
The default row format for InnoDB tables is defined by the innodb_default_row_format configuration option, which has a default value of DYNAMIC. 
Dynamic and Compressed row format allow you to take advantage of InnoDB features such as table compression and efficient off-page storage of long column values. To use these row formats, innodb_file_per_table must be enabled (the default as of MySQL 5.6.6) and innodb_file_format must be set to Barracuda.

InnoDB Tables and Primary Keys：
Always define a primary key for an InnoDB table, specifying the column or columns that:
Are referenced by the most important queries、Are never left blank、Never have duplicate values、Rarely if ever change value once inserted.

Viewing InnoDB Table Properties：

mysql> show table status like '%dept1' \G;
*************************** 1. row ***************************
           Name: tab_dept1
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 3
 Avg_row_length: 5461
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2019-03-29 08:39:55
    Update_time: 2019-03-29 08:40:12
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.01 sec)

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME='test/tab_dept1' \G;
*************************** 1. row ***************************
     TABLE_ID: 58
         NAME: test/tab_dept1
         FLAG: 33
       N_COLS: 5
        SPACE: 66
  FILE_FORMAT: Barracuda
   ROW_FORMAT: Dynamic
ZIP_PAGE_SIZE: 0
   SPACE_TYPE: Single
1 row in set (0.13 sec)

相关参数：
default_storage_engine：
Dynamic Yes、DEFAULT VALUE InnoDB、Scope Global\Session
The default storage engine. This variable sets the storage engine for permanent tables only. 
To see which storage engines are available and enabled, use the SHOW ENGINES statement or query the INFORMATION_SCHEMA ENGINES table.

default_tmp_storage_engine：
Dynamic Yes、DEFAULT VALUE InnoDB、Scope Global\Session
The default storage engine for TEMPORARY tables (created with CREATE TEMPORARY TABLE).

innodb_file_per_table：
Dynamic Yes、DEFAULT VALUE ON、Scope Global
When innodb_file_per_table is enabled, tables are created in file-per-table tablespaces by default. When disabled, tables are created in the system tablespace by default

innodb_default_row_format：
Dynamic Yes、DEFAULT VALUE DYNAMIC、Scope Global、valid values(DYNAMIC\COMPACT\REDUNDANT)
The innodb_default_row_format option defines the default row format for InnoDB tables and user-created temporary tables

Moving or Copying InnoDB Tables：
On Windows, InnoDB always stores database and table names internally in lowercase. To move databases in a binary format from Unix to Windows or from Windows to Unix, create all databases and tables using lowercase names. 
A convenient way to accomplish this is to add the following line to the [mysqld] section of your my.cnf or my.ini file before creating any databases or tables:

[mysqld]
lower_case_table_names=1

Transportable Tablespaces：
The transportable tablespaces feature uses FLUSH TABLES ... FOR EXPORT to ready InnoDB tables for copying from one server instance to another. To use this feature, InnoDB tables must be created with innodb_file_per_table set to ON so that each InnoDB table has its own tablespace.

MySQL Enterprise Backup：
The MySQL Enterprise Backup product lets you back up a running MySQL database with minimal disruption to operations while producing a consistent snapshot of the database. When MySQL Enterprise Backup is copying tables, reads and writes can continue. 
In addition, MySQL Enterprise Backup can create compressed backup files, and back up subsets of tables. In conjunction with the MySQL binary log, you can perform point-in-time recovery. MySQL Enterprise Backup is included as part of the MySQL Enterprise subscription.

Copying Data Files (Cold Backup Method)：
InnoDB data and log files are binary-compatible on all platforms having the same floating-point number format. If the floating-point formats differ but you have not used FLOAT or DOUBLE data types in your tables, then the procedure is the same: simply copy the relevant files.
When you move or copy file-per-table .ibd files, the database directory name must be the same on the source and destination systems. The table definition stored in the InnoDB shared tablespace includes the database name. The transaction IDs and log sequence numbers stored in the tablespace files also differ between databases.

To move an .ibd file and the associated table from one database to another, use a RENAME TABLE statement:

mysql> rename table  test.tab_dept1 to  testrecovery.tab_dept1;
Query OK, 0 rows affected (0.19 sec)

If you have a 'clean' backup of an .ibd file, you can restore it to the MySQL installation from which it originated as follows:
1. The table must not have been dropped or truncated since you copied the .ibd file, because doing so changes the table ID stored inside the tablespace.
2. Issue this ALTER TABLE statement to delete the current .ibd file:
ALTER TABLE tbl_name DISCARD TABLESPACE;
3. Copy the backup .ibd file to the proper database directory.
4. Issue this ALTER TABLE statement to tell InnoDB to use the new .ibd file for the table:
ALTER TABLE tbl_name IMPORT TABLESPACE;

Note:the ALTER TABLE ... IMPORT TABLESPACE feature does not enforce foreign key constraints on imported data.
In this context, a 'clean' .ibd file backup is one for which the following requirements are satisfied:
1.There are no uncommitted modifications by transactions in the .ibd file.
2.There are no unmerged insert buffer entries in the .ibd file.
3.Purge has removed all delete-marked index records from the .ibd file.
4.mysqld has flushed all modified pages of the .ibd file from the buffer pool to the file.

You can make a clean backup .ibd file using the following method:
1. Stop all activity from the mysqld server and commit all transactions.
2. Wait until SHOW ENGINE INNODB STATUS shows that there are no active transactions in the database, and the main thread status of InnoDB is Waiting for server activity. Then you can make a copy of the .ibd file.

Another method for making a clean copy of an .ibd file is to use the MySQL Enterprise Backup product:
1. Use MySQL Enterprise Backup to back up the InnoDB installation.
2. Start a second mysqld server on the backup and let it clean up the .ibd files in the backup.

Export and Import (mysqldump):
You can use mysqldump to dump your tables on one machine and then import the dump files on the other machine. Using this method, it does not matter whether the formats differ or if your tables contain floating-point data.
One way to increase the performance of this method is to switch off autocommit mode when importing data, assuming that the tablespace has enough space for the big rollback segment that the import transactions generate. Do the commit only after importing a whole table or a segment of a table.

Converting Tables from MyISAM to InnoDB:

AUTO_INCREMENT Handling in InnoDB:
To use the AUTO_INCREMENT mechanism with an InnoDB table, an AUTO_INCREMENT column must be defined as part of an index such that it is possible to perform the equivalent of an indexed SELECT MAX(ai_col) lookup on the table to obtain the maximum column value. Typically, this is achieved by making the column the first column of some table index.

InnoDB AUTO_INCREMENT Lock Modes:
Auto-increment lock modes are configured at startup using the innodb_autoinc_lock_mode configuration parameter.

innodb_autoinc_lock_mode:
Dynamic No、DEFAULT VALUE 1、valid values(0、1、2)
The lock mode to use for generating auto-increment values. Permissible values are 0, 1, or 2, for traditional, consecutive, or interleaved, respectively. The default setting is 1 (consecutive)
InnoDB and FOREIGN KEY Constraints:

Limits on InnoDB Tables: