Beginner's note: One-Step InnoDB

The InnoDB engine has been an absolute main force since MySQL was developed to this day. It is applicable to many scenarios, except for the needs of special 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. Www.2cto.com 1. quick understanding of 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) it supports ACID and transaction integrity and consistency. B) it supports row locks and ORACLE-like consistent reads and multi-user concurrency. c) the unique primary key design method for clustered index can greatly improve the concurrent read/write performance; d) supports foreign keys; www.2cto.com e) supports crash data self-repair; InnoDB has so many features, it is much better than MyISAM. Do not hesitate to switch to the InnoDB engine. to modify the InnoDB configuration option, you can select an official version or a branch of Percona. If you do not know where to download it, 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 Storage Data Dictionary, 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 why many people think that InnoDB is not as useful as MyISAM because innodb_buffer_pool_size = 4G www.2cto.com # 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, but innodb_flush_log_at_trx_commit = 1 # Inno The DB log buffer is usually set to 64 MB, which 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 The program is PHP, which can be appropriately increased, 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 www.2cto.com # the maximum number of connection errors, which can be appropriately increased, after frequent connection errors are prevented, the front-end host is rejected by mysql. max_connect_errors = 100000 # Set the slow query threshold. We recommend that you set the minimum value of 1 second long_query_time = 1 # set the maximum value of the temporary table, this is allocated for each connection. It is not recommended to set a large value. max_heap_table_size and tmp_table_size must be set as large as max_heap_table_size = 96Mtmp_table_size = 96 M # Some sort and connection buffers will be 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 query cache is damaged by the performance. query_cache_size = 0 # if it is a DB dominated by the InnoDB engine, the key_buffer_size dedicated to the MyISAM engine can be set to a small value, 8 MB is enough # If it is dominated by the MyISAM engine, it can be set to a large value, but 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 www.2cto.com # is used to set the connection timeout threshold. If the frontend program uses short connections, it is recommended to shorten these two values # If the front-end program uses persistent connections, you can directly comment out these two options, using the default configuration (8 hours) interactive_timeout = 120wait_timeout = 120 3. start MySQL by modifying the configuration file using the InnoDB engine. After the startup, if the following files are generated in the datadir directory of MySQL, InnoDB engine is ready for use. -Rw ---- 1 mysql 1.0G Sep 21 ibdata1-rw-rw ---- 1 mysql 256 M Sep 21 ib_logfile0-rw-rw ---- 1 mysql MySQL 256 M Sep 21 ib_logfile1 after logging on to mysql, run the command to confirm that InnoDB engine is Enabled: (root: imysql.cn: Thu Oct 15 09:16:22 2009) [mysql]> show engines; + ------------ + --------- + hour + -------------- + ------ + ------------ + | Engine | Support | Comment | Transactions | XA | Savepoints | + ------------ + --------- + hour + -------------- + ------------ + | innoDB | YES | Supports transactions, row-level locking, and foreign keys | YES | 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; several MySQL (especially the InnoDB ENGINE) suggestions related to data table design, hope developers can follow: a) All InnoDB Data Tables create an auto-incrementing number type unrelated to the business 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 use TIMESTAMP for storage. d) we recommend that you use int unsigned to store IPV4 addresses. e. If you use TINYINT instead of CHAR (1); f) to store long text content, JSON/BSON format is recommended for storage