InnoDB and MyISAM are the two most commonly used table types in MySQL, each with its own advantages and disadvantages, depending on the specific application. The basic difference is that the MyISAM type does not support advanced processing such as transaction processing, while the InnoDB type does. MyISAM tables emphasize performance, and the execution speed is faster than that of InnoDB, but transactions are not supported. InnoDB provides advanced database functions such as external keys for transactions. Heap table is the fastest table type because it is stored in memory and uses hash indexes. Its disadvantage is that all data will be lost in case of problems because it is stored in the memory. They cannot keep too much data (unless you have a high budget for RAM ). MyIASM is a new version of the IASM table and has the following extensions:
1. Binary hierarchy portability.
2. NULL column index.
3. There are fewer shards for Long-modified rows than the ISAM table.
4. Support for large files.
5. Better index compression.
6. Better key code statistical distribution.
7. better and faster auto_increment processing. InnoDB is the first MySQL engine to provide foreign key constraints. In addition to providing transaction processing, InnoDB also supports row locks and provides consistent read without locks like Oracle, it can increase the number of concurrent read users and improve performance without increasing the number of locks.
InnoDB is designed to maximize performance when processing large data volumes. Its CPU utilization is the most efficient among all other disk-based relational database engines.
InnoDB is a complete database system in the MySQL background. InnoDB has its own buffer pool to buffer data and indexes. InnoDB also stores data and indexes in the tablespace, which may contain several files, this is completely different from MyISAM tables. In MyISAM, tables are stored in separate files. The InnoDB table size is limited by the operating system file size, generally 2 GB. The following are some differences between details and specific implementations: 1. InnoDB does not support FULLTEXT indexes.
2. InnoDB does not store the specific number of rows in the table. That is to say, when you execute select count (*) from table, InnoDB needs to scan the entire table to calculate the number of rows, however, MyISAM simply needs to read the number of lines saved. Note that when the count (*) statement contains the where condition, the operations on the two tables are the same.
3. For fields of the AUTO_INCREMENT type, InnoDB must contain only the index of this field. However, in the MyISAM table, you can create a joint index with other fields.
4. When deleting FROM table, InnoDB does not create a new table, but deletes a row.
5. The load table from master operation does not work for InnoDB. The solution is to change the InnoDB TABLE to the MyISAM TABLE first, and then the InnoDB TABLE after the data is imported, however, it is not applicable to tables that use additional InnoDB features (such as foreign keys.
6. the row lock of the InnoDB table is not absolute. If MySQL cannot determine the scan range when executing an SQL statement, the InnoDB table will also lock the entire table, for example, update table set num = 1 where name like "% aaa %" Optimization of MyISAM and InnoDB:
Key_buffer_size-This is very important for MyISAM tables. If you only use the MyISAM table, you can set it to 30-40% of the available memory. A reasonable value depends on the index size, data volume, and load. Remember, the MyISAM Table uses the operating system cache to cache data. Therefore, you need to leave some memory for them, in many cases, the data is much larger than the index. However, it is always necessary to check whether all key_buffer files are used -- The. MYI file only has 1 GB, while the key_buffer is set to 4 GB. This is a waste. If you seldom use the MyISAM table, keep the key_buffer_size smaller than 16-32 MB to meet the requirements for temporary table indexes on the disk.
Innodb_buffer_pool_size-This is very important for Innodb tables. Compared with MyISAM tables, Innodb is more sensitive to buffering. MyISAM can run in the default key_buffer_size setting. However, Innodb is similar to snail bait in the default innodb_buffer_pool_size setting. Because Innodb caches data and indexes, there is no need to leave too much memory for the operating system. Therefore, if you only need Innodb, you can set it to up to 70-80% of available memory. Some rules apply to key_buffer: If your data volume is small and does not increase rapidly, you do not need
Innodb_additional_pool_size-This option does not affect performance much, at least on an operating system with almost enough memory to allocate. However, if you still want to set it to 20 MB (or larger), you need to check the other Innodb memory to be allocated.
Innodb_log_file_sizeIt is important to write data at a high load, especially for large datasets. The larger the value, the higher the performance, but note that the recovery time may increase. I often set it to 64-512 MB, which is different from the server size.
Innodb_log_buffer_sizeBy default, the server performance can also be set to moderate write loads and short transactions. If there is a peak update operation or a large load, you should consider increasing the value. If its value is set too high, memory may be wasted-it will refresh once every second, so you do not need to set the memory space more than 1 second. Usually 8-16 MB is enough. The smaller the system, the smaller its value.
Innodb_flush_logs_at_trx_commitIs it because Innodb is 1000 times slower than MyISAM? Maybe you forgot to modify this parameter. The default value is 1, which means that each commit of the update transaction (or a statement other than each transaction) will be refreshed to the disk, which is quite resource-consuming, especially when there is no battery backup cache. Many applications, especially those transformed from MyISAM, set the value to 2, that is, do not refresh the log to the disk, instead, it is only refreshed to the operating system cache. Logs are still refreshed to the disk every second, so the consumption of 1-2 updates per second is usually not lost. If it is set to 0, it will be much faster, but it is relatively insecure-some transactions will be lost when the MySQL server crashes. Set to 2 to direct the part of the transaction that is lost and refreshed to the operating system cache.
Table_cache-- Opening a table may have a high overhead. For example, MyISAM marks the MYI file header that the table is in use. You certainly do not want this operation to be too frequent. Therefore, you usually need to increase the number of caches so that the opened tables can be cached to the maximum extent. It requires the resources and memory of the operating system, which is of course not a problem for the current hardware configuration. If you have more than 200 tables, it may be appropriate to set it to 1024 (each thread needs to open the Table). If the number of connections is large, it will increase its value. I have seen a 100,000 error.
Thread_cache-- The overhead of thread creation and destruction may be large because connection/disconnection is required for each thread. I usually set at least 16. If the application has a large number of skip concurrent connections andThreads_CreatedThe value is also relatively large, so I will increase its value. It does not need to create a new thread in common operations.
Query_cache-- This is useful if your application has a large number of reads without application-level caching. Don't set it too large, because it also requires a lot of overhead to maintain it, which causes MySQL to slow down. Usually set to 32-512 Mb. After setting, it is best to track for a period of time to check whether the operation is good. If the cache hit rate is too low under a certain load, enable it.
Sort_buffer_size-- If you only have some simple queries, you don't need to increase the value even though you have 64 GB memory. Poor performance may be reduced. MySQL Memory (Heap) Engine The MEMORY storage engine uses MEMORY content to create tables. These are previously recognized as HEAP tables. MEMORY is a preferred term. Although it is backward compatible, HEAP is still supported.
Each MEMORY table is associated with a disk file. The file name starts with the table name and is defined by a. frm extension.
You can use the ENGINE option to specify a MEMORY table:
Create table t (I INT) ENGINE = MEMORY; 1. The table is limited by the mysqld variable max_heap_table_size (16 MB by default );
2. the auto-increment column is allowed for the HEAP data table starting from MySQL4.1;
3. ENGINE = Heap; ENGINE = MEMORY preferred after MySQL4.1;
4. From MySQL 4.1 on, hash indexes are still the default, but you can specify explicitly that a MEMORY table index shocould be a HASH or BTREE;
5. unlike many other hash tables, the Heap table allows non-unique keys;
6. Memory tables are stored in a fixed-length row format.
7. BLOB or TEXT columns are not supported;
8. In addition to the max_heap_table_size limit and the computer memory limit, you can install max_heap_table_size to limit each table to 4 GB,
This restriction is imposed by the address space of a 32-bit computer.
9. If you are using replication, the MEMORY table of the master server becomes empty when the master server is shut down and restarted. However, the server does not realize that these tables have become empty, so if you select data from them, it will return outdated content. After the server is started, when a MEMORY table is used for the first time on the master server, a delete from statement is automatically written into the binary log of the master server, so synchronize the slave server with the master server again. Note that even if this policy is used, the slave server still has outdated data in the table at the interval between the restart of the master server and the first time it uses the table. However, if you use the -- init-file option to implement the MEMORY table on the master server when it is started. It ensures that the interval is zero. |
