Mysql's InnoDB engine starter Learning Tutorial _mysql

MySQL development to today, the InnoDB engine has as the absolute main force, in addition to such as large data analysis and other specific needs of the field, it applies to many scenarios. However, there are still a lot of developers in the "obsession" with the use of MyISAM engine, that InnoDB can not grasp the good, or MyISAM simple and easy, but also support fast count (*). This article is due to help deal with the Discuz forum in recent days, I hope to be able to help the vast number of developers.

1. Quick knowledge of InnoDB
InnoDB is the most widely used engine under MySQL, and it is a highly scalable and high-performance storage engine based on MySQL, starting with version 5.5, which has become the default engine.
The InnoDB engine supports many features:

A to support acid, simply to support transactional integrity, consistency;
b Support row lock, and similar to Oracle consistency read, multi-user concurrency;
c) Unique clustered index primary key design method, can greatly improve the concurrent read and write performance;
D) supporting foreign keys;
e) Support for crash data self-healing;
InnoDB has so many characteristics, more outstanding than MyISAM, also hesitate what, decisively switch to the InnoDB engine:)

2. Modify INNODB configuration options
You can choose the official version, or Percona branch, if you do not know where to download, Google it.
After the installation of MySQL, you need to modify the MY.CNF configuration file appropriately, for INNODB related options to do some adjustment, in order to better run the InnoDB.
The relevant options are:

#InnoDB存储数据字典, the internal data structure of the buffer pool, 16MB is big enough. Innodb_additional_mem_pool_size = 16M #InnoDB用于缓存数据, index, lock, insert buffer, data dictionary and so on #如果是专用的DB服务器, and InnoDB engine-oriented scenarios, usually set 50% of physical memory # If you are a non-dedicated DB server, you can first try to set the memory of 1/4, if there is a problem to adjust the #默认值是8M, very pit X, which also led to many people think InnoDB than MyISAM easy to use the sake of innodb_buffer_pool_size = 4G # InnoDB shared table space initialization size, default is 10MB, also very pit X, changed to 1GB, and automatically extended Innodb_data_file_path = Ibdata1:1g:autoextend #如果不了解本选项, recommended set to 1, Can better protect data reliability, has certain influence on performance, but controllable innodb_flush_log_at_trx_commit = 1 #InnoDB的log buffer, usually set to 64MB is enough innodb_log_buffer_size = 6 4M #InnoDB redo log size, usually set 256MB is enough innodb_log_file_size = 256M #InnoDB redo log filegroup, usually set to 2 is enough innodb_log_files_in_gr OUP = 2 #启用InnoDB的独立表空间模式, easy to manage innodb_file_per_table = 1 #启用InnoDB的status file, easy for administrators to view and monitor Innodb_status_file = 1 #设置事 The transaction isolation level is read-commited, which improves transaction efficiency and usually satisfies transactional consistency requirements transaction_isolation = read-committed Here, other configuration options also need to be noted: #设置最大并发连接数, If the front-end program is PHP, can be appropriate to increase, but not too large #如果前端程序采用连接池, can be appropriate to reduce the number of connections too large max_connections = #最大连接错误次数, can be appropriately increased to prevent frequent connection errors, front-end host by MySQL refused to lose Max _connect_errors = 100000 #设置慢查询阀值, builtTo set the minimum 1 seconds long_query_time = 1 #设置临时表最大值, this is each connection will be allocated, should not be set too large max_heap_table_size and tmp_table_size to set the same big Max_heap_table_siz E = 96M tmp_table_size = 96M #每个连接都会分配的一些排序, connection buffer, generally set to 2MB is enough sort_buffer_size = 2M join_buffer_size = 2M read_buffer_  Size = 2M Read_rnd_buffer_size = 2M #建议关闭query cache, sometimes it is a damage to performance query_cache_size = 0 #如果是以InnoDB引擎为主的DB, dedicated to the MyISAM engine Key_buffer_size can be set to a small, 8MB sufficient #如果是以MyISAM引擎为主, can be set larger, but not more than 4G #在这里, it is strongly recommended not to use the MyISAM engine, the default is to use the InnoDB engine key_buffer_size = 8M #

 Set the connection timeout threshold, if the front-end program with a short connection, proposed to shorten the 2 values #如果前端程序采用长连接, you can directly comment out these two options, is the default configuration (8 hours) Interactive_timeout = Wait_timeout = 120

3. Start using the InnoDB engine
After modifying the configuration file, you can start MySQL. After startup, in the MySQL datadir directory, if the following several files, it should be possible to use the InnoDB engine.

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

After you log in to MySQL, execute the command to confirm that 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 a 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 () is not null Default ',
passwd VARCHAR not NULL default ',
PRIMARY key (ID),
UNIQUE key ' Idx_name ' (name)
ENGINE = InnoDB;

There are several recommendations related to MySQL (especially the InnoDB engine) datasheet design that you want your developers to follow:

(a) All INNODB data tables Create a business-independent, self-increasing number as the primary key, which is helpful to ensure performance;
b Eliminate the use of Text/blob, do need to use, as far as possible split out into a separate table;
c) Timestamp is recommended to use TIMESTAMP type storage;
d) IPV4 addresses are recommended to be stored with the INT UNSIGNED type;
(e) Gender, i.e., non-TINYINT logic, recommends the use of storage rather than CHAR (1);
f) storing longer text content, it is recommended to use Json/bson format storage;

4. Understanding the storage structure of InnoDB
physically, the InnoDB table consists of shared table spaces, log file groups (redo filegroups), and table-structured definition files. If Innodb_file_per_table is set to ON, a TABLE_NAME.IBD file is generated separately for each table, in which the data, indexes, and internal data dictionary information related to the table are stored. The table structure file ends with. frm, regardless of the storage engine.

The following is the InnoDB table space map:

In the InnoDB storage engine, the default tablespace file is Ibdata1, initialized to 10M, and can be extended, as shown in the following illustration:

In fact, the InnoDB table space file is modifiable and can be modified using the following statement:

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

When using shared tablespace storage, all of InnoDB's data is stored in a separate table space, which can be composed of many files, and a table can exist across multiple files, so its size limit is no longer a limitation of file size, but its own. As you can see from the official documents of InnoDB, the maximum limit of its tablespace is 64TB, that is, the single table limit of InnoDB is about 64TB, of course, this size is to include all indexes of this table and other related data.

When using separate tablespace storage, the data for each table is stored in a separate file, and the single table limit becomes the size limit of the file system.

Below is the maximum of the individual table space files under different platforms.

Operating System file-size Limit
Win32 W/fat/fat32 2GB/4GB
Win32 W/ntfs 2TB (possibly larger)
Linux 2.4+ (using ext3 file system) 4TB
Solaris 9/10 16TB
MacOS X w/hfs+ 2TB
NetWare W/nss File System 8TB

Here's what's in the MySQL document:
Note To Windows users: Fat and VFAT (FAT32) are not suitable for use in MySQL production. You should use NTFS.

Shared tablespace and exclusive tablespace can be converted by parameter innodb_file_per_table, if 1, open Exclusive table space, otherwise, open shared table storage.

When the server resources are limited and the single table data is not very many, the independent tablespace is obviously more efficient than the sharing mode. But MySQL defaults to shared table space.

The advantages and disadvantages of the specific shared table space and independent table space are as follows:

To share a table space:
Advantages:
You can put the table space into multiple files on each disk (the size of the table space file is not limited by the size of the table, such as a table can be distributed on a different file). Data and files are put together for easy management.
Disadvantages:
All the data and indexes are stored in a file that thinks there will be a very large file, although a large file can be divided into small files, but multiple tables and indexes in the table space mixed storage, so for a table to do a large number of deletions after the table space will have a lot of voids, especially for statistical analysis, The application of daily value system is the most unsuitable for sharing table space.

Standalone tablespace: Set in configuration file (my.cnf): innodb_file_per_table

Advantages:
1. Each table has its own table space.
2. The data and indexes for each table exist in the table space of their own.
3. You can implement a single table to move in a different database.
4. Space can be recycled (except for the drop table operation, table null cannot be retrieved)
A The drop table operation automatically reclaims the tablespace, and if you delete a large amount of data for a statistical analysis or a daily value table, you can go through: ALTER TABLE TableName ENGINE=INNODB;
b The use of TRUNCATE table for the InnoDB of the Innodb-plugin also shrinks the space.
C for tables that use independent tablespaces, no matter how they are deleted, the fragmentation of the tablespace does not affect performance too badly, and there is an opportunity to handle it.
Disadvantages:
A single table increases too large, such as more than 100 g.

For innodb_file_per_table-enabled parameter options, the. idb file that corresponds to each table only holds data, indexes, and insert buffers, while undo (undo) information, system transaction information, two write buffers, etc. are stored in the original shared table space.

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

The management of the InnoDB storage engine is done by the engine itself, and the table space is made up of scattered pages and segments.

The area is made up of 64 consecutive pages, each page size is 16K, that is, each area size is 1MB, when creating a new table, the first 32 pages of debris page storage data, after use is the area of application, (InnoDB up to 4 per application, to ensure the sequential performance of data)

Page types are: Data pages, undo pages, System pages, transaction data pages, insert buffer bitmap pages, and insert buffered free list pages.