MySQL storage structure MyISAM and InnoDB

Performance Comparison Test of MyISAM and InnoDB table storage structures in MySQL



MyISAM table. The MyISAM storage format is the default type in MySQL since MySQL version 3.23. It has the following features:

■ If the operating system itself allows larger files, the files are larger than the isam storage method.

■ Data is stored in an independent machine format with low byte priority. This means that tables can be copied from one machine to another, even if their architecture is different.

■ Value index values occupy less storage space, because they are stored preferentially by high bytes. The index value changes rapidly in the low byte, so the high byte is easier to compare.

■ The auto_increment process is better than the isam table. The details are discussed in Chapter 2.

■ Several index restrictions are reduced. For example, you can index columns with null values or blob and text columns.

■ To improve the table integrity check, each table has a flag. After checking the myisamchk table, set this flag. You can use myisamchk-fast to skip the check that the table has not been modified since the previous check, so that this management task is faster. The table also shows whether the table is normally disabled. If the server is shut down abnormally or the machine crashes, this flag can be used to detect the table to be checked when the server starts.


InnoDB introduction:
The InnoDB Storage engine supports transactions, row locks, and foreign keys. InnoDB is designed to provide the best performance when processing large amounts of data.
Performance.
InnoDB provides its own buffer pool to cache data and indexes. InnoDB stores data and indexes.
To the tablespace, The tablespace is a few disk files or is originally a device file (raw disk ).
Unlike the MyISAM storage engine, each table is just a file. (In this way, the maximum 2 GB limit is exceeded in some systems). InnoDB
Without this restriction, it can be expanded infinitely.
Later versions of mysql4.0 support InnoDB Storage.

InnoDB Configuration:
Two disk files managed by InnoDB are tablespace files and log files.
Table space definition:
Innodb_data_file_path = datafile_spec1 [; datafile_spec2]...
File declaration format: file_name: file_size [: autoextend [: Max: max_file_size]
Innodb_data_home_dir declares the file storage directory.

The order in which MySQL reads configuration files:
'/Etc/My. CNF' Global Options.
'Datadir/My. CNF 'server-specific options.
'Default-extra-file' the file specified with the -- defaults-extra-file option.
'~ /. My. CNF 'user-specific options.

The memory usage cannot exceed 2 GB:

Innodb_buffer_pool_size
+ Key_buffer_size
+ Max_connections * (sort_buffer_size + read_buffer_size + binlog_cache_size)
+ Max_connections * 2 MB

InnoDB parameter description:

Innodb_additional_mem_pool_size
Store Data Dictionary information and internal structure information. The more tables you need, the more memory you need. If you do not reserve enough space, the system begins to apply for memory. errlog has errors. the default value is 1 MB.
Innodb_autoextend_increment
When the tablespace is full, the field size is extended.

Innodb_buffer_pool_size
Data and index cache size. Generally, the system physical memory is 50 ~ 80%.