Explanation of the configuration and use of MySQL storage engine InnoDB

MyISAM and InnoDB are the most common storage engines for MySQL. In the previous article, we talked about the difference between InnoDB and MyISAM. Since MyISAM does not support transactions, when we need to use a robust transactional storage engine, InnoDB must be the best choice.

Innodb achieves high concurrency through multi-version concurrency control (MVCC) and implements four SQL standard isolation levels, with the default being the REPEATABLE level. At the same time, a strategy called next-key locking is used to avoid phantom reading. In addition, the InnoDB storage engine also provides high-performance and high-availability functions such as insert buffer , double write , adaptive adaptive hash index index, and read ahead .

How to set InnoDB as the default engine:

1. Check the status of MySQL storage engine: mysql>show engines. InnoDB | YES, indicating that this MySQL database server supports the InnoDB engine.
2. Set InnoDB as the default engine: Add default-storage-engine=INNODB under [mysqld] in the my.ini configuration file
3. Restart MySQL server
4. Log in to the MySQL database, mysql>show engines. If InnoDB | DEFAULT appears, it means that InnoDB is set as the default engine successfully.

InnoDB common configuration parameters:

#InnoDB stores the data dictionary and internal data structure buffer pool. 16MB is large enough.
innodb_additional_mem_pool_size = 16M
#InnoDB is used to cache data, indexes, locks, insert buffers, data dictionaries, etc. #If it is a dedicated DB server and the InnoDB engine is the main scenario, you can usually set it to 50% of physical memory
#If it is a non-dedicated DB server, you can try to set it to 1/4 of the memory first, and adjust it if there is a problem. #The default value is 8M, which is very bad. This is also the reason why many people think that InnoDB is not as good as MyISAM. innodb_buffer_pool_size = 4G
#InnoDB shared tablespace initialization size, the default is 10MB, which is also very tricky, change it to 1GB, and automatically extend innodb_data_file_path = ibdata1:1G:autoextend
#If you don't understand this option, it is recommended to set it to 1, which can better protect data reliability and have a certain impact on performance, but it is controllable innodb_flush_log_at_trx_commit = 1
#InnoDB log buffer, usually set to 64MB is sufficient innodb_log_buffer_size = 64M
#InnoDB redo log size, usually 256MB is enough innodb_log_file_size = 256M
#InnoDB redo log file group, usually setting it to 2 is enough innodb_log_files_in_group = 2
#Enable InnoDB's independent tablespace mode for easy management innodb_file_per_table = 1
# Enable InnoDB's status file for administrators to view and monitor, etc. innodb_status_file = 1
#Set the transaction isolation level to READ-COMMITTED to improve transaction efficiency. Usually, transaction consistency requirements are met. transaction_isolation = READ-COMMITTED 
Other configuration options are also worth noting:
#Set the maximum number of concurrent connections. If the front-end program is PHP, you can increase it appropriately, but not too much. #If the front-end program uses a connection pool, you can adjust it down appropriately to avoid too many connections max_connections = 60
#The maximum number of connection errors can be increased appropriately to prevent the front-end host from being rejected by mysql after frequent connection errors max_connect_errors = 100000
#Set the slow query threshold. It is recommended to set the minimum value to 1 second long_query_time = 1
#Set the maximum value of the temporary table. This will be allocated for each connection. It is not advisable to set it too large. max_heap_table_size and tmp_table_size should be set to the same size max_heap_table_size = 96M
tmp_table_size = 96M
#Each connection will allocate some sorting, connection and other buffers, generally set to 2MB is enough sort_buffer_size = 2M
join_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 2M
#It is recommended to turn off the query cache, which may harm performance. query_cache_size = 0
#If the main DB is InnoDB, the key_buffer_size for MyISAM can be set smaller. 8MB is enough. #If the main DB is MyISAM, it can be set larger, but not more than 4G
#Here, 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 uses a short connection, it is recommended to shorten these two values. #If the front-end program uses a long connection, you can directly comment out these two options and use the default configuration (8 hours)
interactive_timeout = 120
wait_timeout = 120

Several suggestions on InnoDB engine data table design:

1. All InnoDB data tables create an auto-increment numeric primary key that is irrelevant to the business, which is very helpful in ensuring performance;
2. Avoid using text/blob. If you really need to use it, split it into an independent table as much as possible;
3. It is recommended to use TIMESTAMP type to store timestamps;
4. It is recommended to store IPV4 addresses using INT UNSIGNED type;
5. For binary logic such as gender, it is recommended to use TINYINT storage instead of CHAR(1);
6. When storing longer text content, it is recommended to use JSON/BSON format;

InnoDB Lock Types

The InnoDB storage engine implements the following two standard row-level locks

Shared lock (S Lock), allowing a transaction to read a row of data
Exclusive lock (X Lock), which allows a transaction to delete or update a row of data

Compatibility of shared locks and exclusive locks

Notice:

(1) Both S locks and X locks are row locks. Compatibility refers to the compatibility of locks on the same record (row).

(2) Transaction T1 has already obtained a shared lock on row R. Another transaction T2 can immediately obtain a shared lock on row R. This situation is called lock compatibility. If transaction T3 wants to obtain an exclusive lock on row R, it must wait for transactions T1 and T2 to release the shared lock on row R. This situation becomes lock incompatibility.

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM. If you want to learn more about this, please check out the following links

You may also be interested in:

Detailed explanation of memory management of MySQL InnoDB storage engine
Knowledge about MySQL Memory storage engine
Detailed explanation of storage engine in MySQL
Briefly describe the MySQL InnoDB storage engine
Summary of MySql storage engine and index related knowledge
MySQL MyISAM default storage engine implementation principle
MySQL learning summary: a preliminary understanding of the architectural design of the InnoDB storage engine
A brief discussion of four commonly used storage engines in MySQL
MySQL Advanced Learning Notes (Part 3): Introduction to MySQL Logical Architecture, Detailed Explanation of MySQL Storage Engine
Summary of the differences between MySQL storage engines MyISAM and InnoDB
Advantages and disadvantages of common MySQL storage engines

<<: React implements dynamic pop-up window component

>>: Implementing a simple calculator based on JavaScript