The following are the known differences between the two, for reference only.
innodb
InnoDB gives MySQL transaction-safe
(transaction-safe) with commit, rollback, and crash recovery capabilities
(crash recovery capabilities). (InnoDB provides locking on row level and non-locking
read in SELECTs consistent with Oracle type. All of these features improve the performance of concurrent multi-user operations.
There is no need for lock escalation in InnoDB tables because InnoDB's row level
locks fit into a very small space. InnoDB was the first on MySQL to provide FOREIGN KEY
constraints. InnoDB was the first table engine on MySQL to offer FOREIGN KEY
constraints.
InnoDB was designed to handle high-volume database systems with CPU utilization unmatched by other disk-based relational database
engines. Technically, InnoDB is a complete database system that sits on the backend of MySQL.
InnoDB builds its own dedicated buffer pool in main memory to cache data and indexes. InnoDB stores data and indexes
in tablespaces, which may contain multiple files, unlike other, for example, MyISAM, where tables are stored
in separate files.The size of InnoDB tables is limited only by the operating system's file size, which is typically 2 GB.
All InnoDB tables are stored in the
All InnoDB tables are stored in the same data file, ibdata1 (or multiple files, or separate
tablespace files), which is not good enough for backups, but a free solution could be to copy the data file, backup the binlog,
or use mysqldump.
MyISAM
MyISAM is the MySQL default storage engine .
Each MyISAM table is stored in three files. frm file holds the table definition. The data file is MYD (MYData) .
The index file is the MYI (MYIndex) extension.
Because of its relative simplicity, MyISAM is superior to InnoDB in terms of efficiency.... MyISAM is a good choice for small applications.
MyISAM table is saved as a file , in the cross-platform data transfer in the use of MyISAM storage will save a lot of trouble
MyISAM is a new version of the ISAM table , there are the following extensions:
- binary level of portability .
-NULL column indexes.
-Fewer fragments for variable-length rows than ISAM tables.
-Support for large files.
-Better index compression.
-Better statistical distribution of keys?
-Better and faster auto_increment handling.
Here are some details and implementation-specific differences:
◆ 1. InnoDB does not support FULLTEXT type indexes.
◆2. InnoDB does not save the specific number of rows in the table, that is, when executing select count(*) from table,
InnoDB has to scan through the whole table to calculate how many rows there are, but MyISAM simply reads out the number of saved rows.
Note that when the count(*) statement includes the where condition, the operation is the same for both tables.
◆3. For a field of type AUTO_INCREMENT, InnoDB must contain an index for only that field, but in a MyISAM
table, you can create a joint index with other fields.
◆4. When DELETE FROM table, InnoDB does not recreate the table, but deletes it line by line.
◆5. LOAD TABLE FROM MASTER operation does not work for InnoDB. The solution is to first change the InnoDB table to a
MyISAM table, and then change it to an InnoDB table after importing the data, but this does not work for tables that use additional InnoDB features (such as foreign keys).
.
◆ MyISAM-type binary data files can be migrated across operating systems.
Also, row locks for InnoDB tables are not absolute. If MySQL is not sure of the
range to scan when executing a SQL statement, InnoDB tables will also lock the whole table, for example update table set num=1 where name like "%aaa% "
Additionally, there are two options: if your data performs a large number of INSERTs or UPDATES, for performance reasons,
InnoDB tables should be used. If a large number of SELECTs are performed, MyISAM is a better choice. If you need to use transaction processing,
but the original data table uses myisam, you need to change it to bdb or innodb, so that the myisam-based program,
after changing the type to innodb, its program doesn't have to change ......
To sum up, any kind of table is not a panacea, only appropriate for the type of business to choose the right type of table, in order to
maximize the performance advantages of MySQL.
MyISAM and InnoDB optimizations:
key_buffer_size - This is very important for MyISAM tables. If you are just using MyISAM tables, you can set it
to 30-40% of the available memory. A reasonable value depends on the index size, the amount of data, and the load -- remember that MyISAM tables use the operating system's cache to cache data, so you need to leave some memory for them, and in many cases the data is much larger
than the index. That said, it's always worth checking that all of the key_buffer is being utilized -- it's very rare for a .MYI file to be only 1GB
while the key_buffer is set to 4GB. That's just wasteful. If you rarely use
MyISAM tables, keep the key_buffer_size below 16-32MB to accommodate the temporary table indexes
given to disk.
innodb_buffer_pool_size - This is very important for Innodb tables, which are more
sensitive to buffering than MyISAM tables, and MyISAM can run just fine with the default key_buffer_size setting, whereas Innodb can run fine with the default
Innodb is more sensitive to buffering than MyISAM tables. p>innodb_buffer_pool_size setting at a snail's pace. Since Innodb caches both data and indexes,
it doesn't leave much memory for the operating system, so it can be set to use up to 70-80%
of its available memory if it's the only thing that needs to be used. Some of the rules that apply to key_buffer are -- if you don't have a lot of data and it won't skyrocket, there's no need to set
innodb_additional_pool_size - this option doesn't have much of an impact on performance, at least not on operating systems that have more or less enough memory
to allocate. However, if you still want to set it to 20MB (or larger), you will therefore need to look at how much memory
Innodb otherwise needs to allocate.
innodb_log_file_size is important in the case of high write loads especially with large datasets. The larger this value is the higher the performance
relatively, but be aware that it may increase recovery time. I often set it to 64-512MB, depending on the size of the server.
innodb_log_buffer_size The default setting of innodb_log_buffer_size is fine for server performance
under moderate write loads and short transactions. If there are spikes in update operations or if the load is high, it is time to consider increasing its value. If it is set
too high, memory may be wasted -- it is refreshed once per second, so there is no need to set more than the amount of memory space needed for 1 second.
Usually 8-16MB is enough. The smaller the system the smaller the value.
innodb_flush_logs_at_trx_commit Got a big head over Innodb being 1000x slower than MyISAM? It looks like maybe you forgot
to change this parameter. The default value is 1, which means that every update transaction committed (or every statement outside of a transaction)
will be flushed to disk, and that's quite resource-intensive, especially if there's no battery-backed cache. Many applications, especially
those that have moved from MyISAM, are fine with setting it to a value of 2, which means that instead of flushing the logs to disk,
they are only flushed to the operating system's cache. The logs are still flushed to disk every second, so the consumption of 1-
2 updates per second is usually not lost. Setting it to 0 is much faster, but it's also relatively insecure -- some transactions are lost when the MySQL server crashes
. Setting it to 2 directs the loss of those transactions that are flushed to the OS cache.
table_cache -- The overhead of opening a table can be significant. For example, MyISAM puts a MYI header to signify that the table is in use
. You certainly don't want to do this too often, so it's common to increase the amount of cache enough to maximize the cache for opened
tables. It uses operating system resources as well as memory, which is certainly not a problem for current hardware configurations.
If you have more than 200 tables then a setting of 1024 might be appropriate (each thread needs to open a table),
If the number of connections is large then increase it. I've seen it set to 100,000.
thread_cache - the overhead of thread creation and destruction can be high as it is needed for each thread connection/disconnection.
I usually set it to at least 16. If the application has a lot of jumping concurrent connections and the value of Threads_Created
is also large, then I increase its value. Its purpose is to eliminate the need to create new threads in normal operations.
query_cache -- This is
useful if your application has a lot of reads and no application-level cache. Don't set it too large, as trying to maintain it also requires quite a bit of overhead, which can cause MySQL to slow down. Typical settings
are 32-512Mb. It's a good idea to track it for a while after setting it up to see if it's working well. Under some load pressure,
enable the cache hit rate if it is too low.
sort_buffer_size - if you only have a few simple queries, there's no need to increase its value, even though you have
64GB of memory. Messing with it might reduce performance