MySQL InnoDB engine Getting Started Tutorial _ MySQL

This article describes how to get started with the InnoDB engine of MySQL and provides a better summary of the storage structure of InnoDB. For more information, see MySQL development to this day, the InnoDB engine has been an absolute main force. it is applicable to many scenarios except for the special requirements in fields such as big data analysis. However, many developers still use the MyISAM engine and feel that they are not sure about InnoDB. MyISAM is simple and easy, and supports fast COUNT (*). This article is a result of some comments on how to help the discuz Forum in recent days, hoping to help developers.

1. quick introduction to InnoDB
InnoDB is the most widely used engine in MySQL. it is a high-scalability and high-performance storage engine based on MySQL. it has become the default engine since version 5.5.
The InnODB engine supports many features:

A) supports ACID. In short, it supports transaction integrity and consistency;
B) supports row locks and consistent reads similar to ORACLE and multi-user concurrency;
C) the unique primary key design method for clustered indexes can greatly improve concurrent read/write performance;
D) supports foreign keys;
E) support crash data self-repair;
InnoDB has so many features that it is much better than MyISAM. do not hesitate to switch to the InnoDB engine :)

2. modify InnoDB configuration options
You can select an official version or a branch of Percona. if you do not know where to download the version, google.
After installing MySQL, you need to modify the my. cnf configuration file appropriately and make some adjustments to InnoDB-related options to better run InnoDB.
Related options include:

# InnoDB stores the data dictionary and internal data structure buffer pool. 16 MB is large enough. Innodb_additional_mem_pool_size = 16 M # InnoDB is used to cache data, indexes, locks, insert buffering, data dictionaries, and so on # if it is a dedicated DB server and is dominated by the InnoDB engine, generally, you can set 50% of the physical memory # if it is not a dedicated DB server, you can first set it to 1/4 of the memory. if there is a problem, then adjust # The default value is 8 MB, which is very pitfall X, this is also why many people think that InnoDB is not as useful as MyISAM because innodb_buffer_pool_size = 4G # InnoDB shares the tablespace initialization size. the default value is 10 MB, which is also very bad X and changed to 1 GB, innodb_data_file_path = ibdata1: 1G: autoextend # If you do not know this option, we recommend that you set it to 1 to better protect data reliability and affect performance, however, innodb_flush_log_at_trx_commit = 1 # InnoDB log buffer can be controlled. generally, setting it to 64 MB is enough for innodb_log_buffer_size = 64 M # InnoDB redo log size, generally, setting 256 MB is enough for the innodb_log_file_size = 256 M # InnoDB redo log file group. generally, setting 2 is enough for innodb_log_files_in_group = 2 # enabling the independent tablespace mode of InnoDB, easy to manage innodb_file_per_table = 1 # enable InnoDB status file for administrators to view and monitor innodb_status_file = 1 # set the transaction isolation level to READ-COMMITED, improve transaction efficiency, generally, transaction_isolation = READ-COMMITTED meets the transaction consistency requirements. for other configuration options, note: # set the maximum number of concurrent connections. if the frontend program is PHP, increase the number of concurrent connections, but cannot be too large # if the front-end program uses a connection pool, you can adjust the size to avoid excessive connections. max_connections = 60 # the maximum number of connection errors can be increased appropriately to prevent frequent connection errors, the front-end host is rejected by mysql. max_connect_errors = 100000 # set the slow query threshold value. we recommend that you set the minimum value of 1 second long_query_time = 1 # set the maximum value of the temporary table, which is allocated for each connection, too large max_heap_table_size and tmp_table_size should be set as large as max_heap_table_size = 96Mtmp_table_size = 96 M # some sort and connection buffers allocated for each connection, generally, 2 MB is enough for sort_buffer_size = 2Mjoin_buffer_size = 2Mread_buffer_size = 2Mread_rnd_buffer_size = 2 M # It is recommended to disable the query cache, in some cases, the performance is compromised by query_cache_size = 0 # if the database is dominated by the InnoDB engine, the key_buffer_size dedicated to the MyISAM engine can be set to a smaller value, 8 MB is sufficient # if the MyISAM engine is the main engine, you can set a large value, but it cannot exceed 4 GB # Here, we strongly recommend that you do not use the MyISAM engine, by default, the InnoDB engine key_buffer_size = 8 M # sets the connection timeout threshold. if the frontend program uses short connections, we recommend that you shorten these two values. # if the frontend program uses persistent connections, you can directly comment out these two options, which are configured by default (8 hours) interactive_timeout = 120wait_timeout = 120

3. start using the InnoDB engine
After modifying the configuration file, you can start MySQL. After the startup, if the following files are generated in the datadir directory of MySQL, InnoDB engine is ready for use.

-rw-rw---- 1 mysql mysql 1.0G Sep 21 17:25 ibdata1-rw-rw---- 1 mysql mysql 256M Sep 21 17:25 ib_logfile0-rw-rw---- 1 mysql mysql 256M Sep 21 10:50 ib_logfile1

After logging on to MySQL, run the following command to check whether the InnoDB engine is enabled:

(root:imysql.cn:Thu Oct 15 09:16:22 2009)[mysql]> show engines;+------------+---------+----------------------------------------------------------------+--------------+------+------------+| Engine   | Support | Comment                            | Transactions | XA  | Savepoints |+------------+---------+----------------------------------------------------------------+--------------+------+------------+| InnoDB   | YES   | Supports transactions, row-level locking, and foreign keys   | YES     | YES | YES    |

Next, create an InnoDB table:

(root:imysql.cn:Thu Oct 15 09:16:22 2009)[mysql]> CREATE TABLE my_innodb_talbe(id INT UNSIGNED NOT NULL AUTO_INCREMENT,name VARCHAR(20) NOT NULL DEFAULT '',passwd VARCHAR(32) NOT NULL DEFAULT '',PRIMARY KEY(id),UNIQUE KEY `idx_name`(name)) ENGINE = InnoDB;

There are several suggestions related to the design of MySQL (especially the InnoDB engine) data tables. we hope developers can follow these suggestions:

A) all InnoDB data tables create a business-independent auto-incrementing number type as the primary key, which is helpful for ensuring performance;
B) do not use text/blob. if you really need to use it, split it into an independent table as much as possible;
C) we recommend that you store timestamps;
D) int unsigned storage is recommended for IPV4 addresses;
E) Non-logic such as gender. TINYINT is recommended for storage instead of CHAR (1 );
F) it is recommended to store long text in JSON/BSON format;

4. understand the storage structure of InnoDB
Physically, the InnoDB table consists of the shared tablespace, log file Group (redo file group), and table structure definition file. If innodb_file_per_table is set to on, the system generates a table_name.ibd file for each table. in this file, stores data, indexes, and internal data dictionary information related to the table. The table structure file ends with. frm, which is irrelevant to the storage engine.

The table space structure of InnoDB is as follows:

In the InnoDB storage engine, the default tablespace file is ibdata1, initialized to 10 M, and can be expanded, as shown in:

In fact, the tablespace file of InnoDB can be modified. you can use the following statement to modify it:

Innodb_data_file_path = ibdata1: 370 M; ibdata2: 50 M: autoextend

When using the shared tablespace storage method, all Innodb data is stored in a single tablespace, which can be composed of multiple files. a table can exist across multiple files, therefore, its size limit is no longer a file size limit, but its own limit. From the official Innodb documentation, we can see that the maximum tablespace limit is 64 TB. that is to say, the single table limit of Innodb is about 64 TB, of course, this size includes all indexes of the table and other related data.

When a single tablespace is used for storage, the data in each table is stored in a single file. at this time, the single table limit is changed to the file system size limit.

The following figure shows the maximum number of tablespace files on different platforms.

Operating System File-size Limit
Win32 w/FAT/FAT32 2 GB/4 GB
Win32 w/NTFS 2 TB (possibly larger)
Linux 2.4 + (using ext3 file system) 4 TB
Solaris 9/10 16 TB
MacOS X w/HFS + 2 TB
NetWare w/NSS file system 8 TB

The following is the content in the MySQL document:
For Windows users, note that FAT and VFAT (FAT32) are not suitable for MySQL production. NTFS should be used.

The shared and exclusive tablespaces can be converted using the innodb_file_per_table parameter. if the value is 1, the exclusive tablespace is enabled. otherwise, shared table storage is enabled.

When the server resources are limited and the data in a single table is not very large, the independent tablespace is much more efficient than the sharing method, but MySQL uses the shared tablespace by default.

The advantages and disadvantages of the shared and independent tablespaces are as follows:

Shared tablespace:
Advantages:
The tablespace can be divided into multiple files and stored on each disk (the tablespace file size is not limited by the table size, for example, a table can be distributed on non-synchronous files ). Put data and files together for convenient management.
Disadvantages:
All the data and indexes are stored in one file to store a very large file. although a large file can be divided into multiple small files, however, multiple tables and indexes are stored in a table space in a hybrid manner. after a large number of deletion operations are performed on a table, there will be a lot of gaps in the table space, especially for statistical analysis, applications such as the daily value system do not use the most shared tablespace.

Independent tablespace: in the configuration file (my. cnf), set innodb_file_per_table

Advantages:
1. Each table has its own independent tablespace.
2. Data and indexes of each table are stored in its own tablespace.
3. a single table can be moved in different databases.
4. space can be recycled (except for the drop table operation, the table cannot be recycled if it is empty)
A) the Drop table operation automatically recycles tablespaces. for statistical analysis or daily value tables, you can use alter table TableName engine = innodb to delete a large amount of data and reduce unnecessary space.
B) using truncate table for innodb-plugin will also shrink the space.
C) for tables that use independent tablespaces, no matter how they are deleted, the tablespace fragments will not seriously affect performance, and there is a chance to process them.
Disadvantages:
The increase in a single table is too large, for example, more than 100 GB.

After the innodb_file_per_table parameter option is enabled. the idb file only stores data, indexes, and insert buffering, while undo information, system transaction information, and secondary write buffering are still stored in the original shared tablespace.

The data segment is the leaf node of the B + tree, and the index segment is the non-index node of the B + tree.

The InnoDB storage engine is managed by the engine itself, and the tablespace is composed of scattered pages and segments.

A partition consists of 64 consecutive pages. each page is 16 kB, that is, the size of each partition is 1 MB. when creating a new table, a 32-page split page stores data, after use, it is the application of the zone. (InnoDB can apply for up to four zones each time to ensure data sequence performance)

Page types include: Data page, Undo page, system page, transaction data page, insert buffer bitmap page, and insert buffer idle list page.