MySQL InnoDB storage Engine Introduction

InnoDB Storage Engine
1. Storage: Data directory. Can be modified by configuration
Store file: Frm,ibd end of file. FRM storage table structure, IBD stores indexes and data
Storage log: Ib_logfilen file

2.INNODB storage engine turned on or off:
Close innodb_fast_shutdown=
0 Complete all full purge and merge insert buffer operations (e.g. when doing InnoDB plugin upgrade)
1 default, do not need to complete the above operation, but will flush dirty pages in the buffer pool
2 does not complete these two operations, but instead writes the log to the log file, and the next time it is started, a recovery operation is performed recovery
A recovery operation is required when the database (for example: Kill)/innodb_fast_shutdown=2 is not properly closed.

Recovery innodb_force_recovery=
0 default, but all recovery operations are performed when recovery is required
1 ignore checked to corrupt page
2 Preventing the main thread from running, such as the main thread needs to perform full purge operation, will cause crash
3 do not perform a transaction rollback operation
4 merge operation with no insert buffering performed
5 Do not view undo log the undo Log,innodb storage Engine treats all uncommitted transactions as committed
6 Do not perform roll forward operation

3. Table Space:
Tablespace: Separate tablespace and shared table space. Table space has a maximum limit of 64TB

To view the current table space settings:
Mysql> Show variables like "innodb_file_per_table";
On stands for separate table space management, off for shared table space management, (see table Space management for single table, need to see if there is a separate data file for each table)

To modify the table space management of a database:
Modify the parameter values of the innodb_file_per_table, but the modifications do not affect the shared table space and individual table spaces that have been used before;
Innodb_file_per_table=1 for using separate table spaces
Innodb_file_per_table=0 to use shared table spaces

Mysql> Show variables like ' innodb_data% ';

Configuration parameters:
INNODB_DATA_FILE_PATH=IBDATA1:2G;IBDATA2:3G:AUTOEXTEND:MAX:4G//Specify two spaces. The sizes are 2G and 3G, respectively. When the two are full, the ibdata2 automatically grows 4G max.
You can also specify a storage path: innodb_data_file_path=/tmp/ibdata1:2g;ibdata2:3g:autoextend:max:4g
The innodb_data_home_dir= must be set to empty in advance.
Advantages of sharing table spaces:
Table spaces can be partitioned into multiple files, so tables can be split into multiple files on disk, and the size of the table is not limited by the size of the disk (many documents describe a problem).
Data and documents together for easy management

Disadvantages of sharing table spaces:
All data and indexes are stored in a file, although a large file can be divided into small files, but multiple tables and indexes are mixed in the table space, when the amount of data is very large,
There will be a lot of voids in the table space after a large number of deletions, especially for statistical analysis, which is most unsuitable for shared table spaces for applications that frequently delete operations.
Cannot retract after a shared tablespace allocation: When a temporary index is created or a temporary table is opened, it is not possible to delete the related table or to retract that part of the space (
Can be understood as a table space of MySQL 10G, but only use 10M, but the operating system shows that MySQL table space is 10G), the database is cold prepared very slow

Advantages of a single table space:
Each table has its own separate tablespace, and each table's data and indexes exist in its own table space, enabling a single table to move in different databases.
Space can be recycled (except for the drop table operation, the meter is not able to recycle)
The drop table operation automatically reclaims the tablespace, and if you delete large amounts of data for statistical analysis or a daily value table, you can pass: ALTER TABLE TableName ENGINE=INNODB;
The use of Turncate table for Innodb-plugin InnoDB also shrinks the space.
For tables that use separate tablespaces, no matter how they are deleted, the fragmentation of the tablespace does not affect performance too severely, and there is a chance to handle it.

Disadvantages of individual table spaces:
Single table increase is too large, when the single table occupied space is too large, storage space is not enough, only from the operating system level to think about the solution;
4. Transaction:
Four features of a transaction: atomicity, consistency, isolation, persistence.
Transaction open: Set autocommit=1//Open transaction, 0 is shutdown transaction

Commit a transaction:
Start Transaction//Open
commit;//Submit
Hermit submits:
Implicitly-Committed SQL statements
The following SQL statements produce an implicit commit operation, which is an implicit commit when the statements are executed.
1. DDL statement: ALTER DATABASE ... UPGRADE DATA DIRECTORY NAME 、。。。。
2, to modify the operation of the MySQL schema implicitly: CREATE user, DROP user, GRANT, RENAME user, REVOKE, SET PASSWORD.
3. Management statement: ANALYZE table, cache index, CHECK table, LOAD index into CACHE, OPTIMIZE table, REPAIR table.
Isolation level of the transaction:
set {global|session} transaction=
1. READ uncommited
2. READ commited
3. Repeatable READ
4, SERIALIZABLE
To view the transaction isolation level for the current session:
SELECT @ @tx_isolation;
Insert card to see the global transaction Isolation level:
SELECT @ @global. tx_isolation;

At the transaction isolation level of serializble, the InnoDB storage engine automatically adds lock in SHARE MODE after each SELECT statement, adding a shared lock to each read operation.
Therefore, under this transaction isolation level, read occupation locks, consistent non-lock read no longer support, generally no longer in the local transaction using the Serializble isolation level,
The transaction isolation level of the serializable is primarily used for distributed transactions that InnoDB the storage engine.
Under the transaction isolation level of read commited, the Gap lock algorithm will not be used by the LOCK,INNODB storage engine except for the unique constraint check and the check of the foreign KEY constraint.

Distributed transactions:
The implementation of distributed transactions can be supported through XA transactions, and the InnoDB storage engine must use the isolation level of serializable when using distributed transactions.
To see if XA transaction support is enabled (default on)
Show variables like ' Innodb_support_xa '
5. Foreign key
The use of foreign keys needs to meet the following conditions:
1. Two tables must all be innodb tables, and they have no temporary tables.
2. The corresponding column that establishes the foreign key relationship must have a similar INNODB internal data type.
3. The corresponding column establishing the foreign key relationship must be indexed.
4. If the constraint symbol is explicitly given, the symbol must be unique in the database. If not explicitly given, InnoDB will be created automatically.

If the child table attempts to create a foreign key value that does not exist in the parent table, InnoDB rejects any insert or update operations. If the parent table attempts to update or delete any foreign key values that exist or match in any of the child tables, the
final action depends on the on update and on delete options in the FOREIGN key constraint definition. The
InnoDB supports 5 different actions, and if no on delete or on UPDATE is specified, the default action is restrict:
1. CASCADE: Deletes or updates the corresponding row from the parent table, and automatically deletes or updates the matching row from the table. On DELETE Canscade and on UPDATE Canscade are supported by InnoDB.
2. Set NULL: Deletes or updates the corresponding row from the parent table, and sets the foreign key column in the child table to be empty. Note that these foreign key columns are not set to NOT NULL when they are valid. The
on DELETE set null and on UPDATE set set NULL are supported by INNODB.
3. NO Action:innodb refuses to delete or update the parent table.
4. RESTRICT: Refuses to delete or update the parent table. specifying Restrict (or no ACTION) is the same as ignoring the on delete or the on Update option.
5. SET Default:innodb is not currently supported.

FOREIGN KEY constraints have the most use of the two conditions are no different:
1) The parent table is updated when the child table is also updated, when the parent table is deleted if the child table has a matching item, the deletion fails; in the foreign key definition, use the on UPDATE CASCADE on delete RESTRICT;
2) When the parent table is updated, the child table is also updated, and the parent table is deleted when the child table matches the item. In the foreign key definition, you can use the on UPDATE CASCADE on DELETE CASCADE;

InnoDB allows you to add a new foreign key to an already existing table using ALTER TABLE:
ALTER TABLE Tbl_name
ADD [CONSTRAINT [symbol]] FOREIGN KEY
[Index_name] (Index_col_name, ...)
REFERENCES tbl_name (Index_col_name,...)
[on DELETE Reference_option]
[on UPDATE Reference_option]

InnoDB also supports the use of ALTER TABLE to delete foreign keys:
ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol;
6. Memory Utilization:
Parameter: innodb_buffer_pool_size
This parameter mainly caches the index of the InnoDB table, the data, and the buffer when the data is inserted. Optimize the primary parameters for InnoDB acceleration.
This parameter allocates memory principle: This parameter is allocated only 8M by default, if it is a dedicated DB server, then he can account for 70%-80% of memory. This parameter cannot be changed dynamically, so the allocation needs to be considered more. Allocated too large,
Will make the swap occupy too much, resulting in MySQL query very slow. If your data is small, then the assignable is your data size + 10% or so as the value of this parameter.
For example, if the data size is 50MB, assign this value to INNODB_BUFFER_POOL_SIZE=64MB
To view the parameter configuration:
Show global variables like '%inndb_buffer% '
Setup method:
Set Global innodb_buffer_pool_size=4g
The use of this parameter assignment value can be based on the show InnoDB status "G;
----------------------
BUFFER POOL and MEMORY
----------------------
Total memory Allocated 4668764894;
To confirm the use of the situation.

Parameter: Innodb_additional_mem_pool
Function: The internal directory used to store InnoDB
This value is not allocated too large, the system can be automatically tuned. Do not set too high.
Usually the larger data set 16M enough, if the table is more, can be appropriately increased. If this value is automatically incremented, it will be displayed in the error log.
To view the parameter configuration:
Show global variables like '%innodb_add% '
Distribution principle:
Use show InnoDB status "G; to see what state the DB is running in (refer to buffer POOL and memory segment), then you can adjust to the appropriate value.
----------------------
BUFFER POOL and MEMORY
----------------------
Total memory Allocated 4668764894; In additional pool allocated 16777216
Reference: In additional pool allocated 16777216
Depending on your parameter situation, you can adjust it appropriately.
Setup method:
innodb_additional_mem_pool=16m

Parameter: innodb_max_dirty_pages_pct
Function: Control the dirty page of the InnoDB in the buffer under that percentage, the value is in the range 1-100, the default is 90.
Another use of this parameter: when the memory allocation of the INNODB is too large, resulting in a significant swap occupancy, you can appropriately reduce the adjustment of this value, so that the swap space to release.
Build Justice: This value is maximum at 90%, minimum at 15%. Too large, each update in the cache requires too many data pages, too small, the data page is too small, the update operation is too slow.
To view the size of the wipe setting: Show global variables like '%pct% '
Setup method:
Innodb_max_dirty_pages_pct=90
Dynamic changes require Super permissions:
Set global innodb_max_dirty_pages_pct=50;
7. About logs:
Parameter: innodb_log_file_size
Role: Specify the size of the log
Allocation principle: Several day value member sizes add up to almost equal to your innodb_buffer_pool_size. The upper limit is a maximum of 4G per day value. General Control the addition of several log files is better than the size of 2G.
You need to look at the size of your transaction and the size of your data.
Note: The size of this value allocation is very much related to the database write speed, transaction size, and recovery after an abnormal restart.
Setup method:
innodb_log_file_size=256m

Parameter: Innodb_log_files_in_group
Role: Specifies that you have several day value groups.
Distribution principle: Generally we can use 2-3 Day value groups. The default is two.
Setup method:
Innodb_log_files_in_group=3

Parameter: innodb_log_buffer_size:
Function: A buffer in which transactions are stored.
Distribution principle: Control in 2-8m. This value doesn't have to be too much. The memory inside of him is usually written to disk once a second. The specific write method is related to how your transaction is submitted. The general maximum designation is 3M more appropriate.
Reference: Innodb_os_log_written (show global status can be obtained, if this value grows too fast, can be appropriately increased innodb_log_buffer_size
In addition, if you need to handle a large number of text, or BLOB fields, you can consider increasing the value of this parameter.
Setting Method: Innodb_log_buffer_size=3m
Parameter: Innodb_flush_logs_at_trx_commit
Role: Control how transactions are submitted
Allocation principle: This parameter has only 3 values, 0,1,2 Please confirm the level you can accept. The default is 1, please do not change the main library.
A higher performance can be set to 0 or 2, but a second transaction is lost.
Description
The setting of this parameter has a great effect on the performance of InnoDB, so give more instructions here.
When this value is 1 o'clock: InnoDB's transaction log writes a daily value file after each commit, and flushes the daily value to disk. This can be done without losing a single transaction.
When this value is 2 o'clock: at each commit, the log buffer is written to the file, but the log file does not flush with the disk operation, and the refresh of the log file at a value of 2 also occurs once per second.
However, it is important to note that due to the problem of process invocation, 100% per second is not guaranteed to occur. Thus, the performance is the fastest. But the operating system crashes or the power is dropped to delete the last second of the transaction.
When this value is 0 o'clock: The log buffer is written once per second to the log file, and the log file is refreshed with disk operations, but no action is taken on a transactional commit.
The crash of the MYSQLD process deletes the last second of the transaction before the crash.

From the above analysis, when this value is not 1 o'clock, you can achieve better performance, but encounter anomalies will have a loss, so it needs to be measured according to their own situation.
Setup method:
Innodb_flush_logs_at_trx_commit=1

MySQL InnoDB storage Engine Introduction