MyISAM Storage Engine
MyISAM is the default storage engine. It is based on older ISAM code, but there are many useful extensions. Some features of the MyISAM storage engine:
All data values are stored in low bytes first. This separates the data machine from the operating system. The only requirement for binary portability is that the machine uses the complement (as the machine has in the last 20 years) and the IEEE floating-point format (which is completely dominant in the mainstream machine). The only machines that do not support binary compatibility are embedded systems. These systems sometimes use a special processor.
Storing data in low bytes does not seriously affect speed; The bytes in the data row are generally not federated, and reading the unbound bytes from One Direction is no more resource-intensive than reading from reverse. The code that gets the column values on the server does not appear to be time-tight compared to other code.
Large files (up to 63-bit file lengths) are supported on file systems and operating systems that support large files.
When deleting and updating and inserting a mix, the rows of dynamic dimensions are less fragmented. This is done automatically by merging adjacent deleted blocks, and if the next block is deleted, it expands to the next one.
The maximum number of indexes per MyISAM table is 64. This can be changed by recompiling. The maximum number of columns per index is 16.
The maximum key length is 1000 bytes. This can also be changed by compiling. For cases where the key length exceeds 250 bytes, a key block of more than 1024 bytes is used.
The Blob and text columns can be indexed.
A null value is allowed in the column of the index. This accounts for 0-1 bytes per key.
All numeric key values are stored first in high bytes to allow a higher index compression.
When the records are inserted in a sequential order (as if you were using a auto_increment column), the index tree is split so that the high node contains only one key. This improves the spatial utilization of the index tree.
The internal processing of one auto_incremen column per table. MyISAM automatically updates this column for insert and update operations. This makes the Auto_increment column faster (at least 10%). The value at the top of the sequence cannot be exploited after it has been deleted. (When the Auto_increment column is defined as the last column of a multicolumn index, the use of values removed from the top of the sequence can occur). The auto_increment value can be reset using ALTER TABLE or MYISAMCH.
If the table in the middle of the data file has no free blocks, you can insert new rows into the table while other threads are reading from the table. (This is known as concurrent operations). The appearance of a free block is the result of deleting rows, or updating a dynamic length row with more data than the current content. When all free blocks are exhausted (filled), future insertions become concurrent.
You can put data files and index files in different directories, create table for a higher speed with Data directory and index directory options, see 13.1.5, "CREATE table Syntax".
Each character column can have a different character set.
Another flag in the MyISAM index file indicates whether the table is closed correctly. If you start the Mysqld,myisam table with the--myisam-recover option, it is automatically checked when it is opened, and the table is repaired if the table is improperly closed.
If you run Myisamchk with the--update-state option, it labels the table as checked. Myisamchk--fast Check only those tables that do not have this flag.
Myisamchk--analyze stores statistics for some keys, and also stores statistics for the entire key.
Myisampack can package blobs and varchar columns.
MyISAM also supports the following features:
True varchar types are supported, and varchar columns begin with a length stored in 2 bytes.
A table with varchar can have a fixed or dynamic record length.
varchar and char columns can be up to 64KB.
A messed up computed index pair can be used for unique. This allows you to have unique on the merge of any column within the table. (However, you cannot search on a unique computed index).
InnoDB Storage Engine
InnoDB provides MySQL with a transaction-safe (acid-compatible) storage engine with Commit, rollback, and crash resiliency. InnoDB locks the row-level and also provides an Oracle-style, non-locking read in the SELECT statement. These features add to multi-user deployment and performance. There is no need to widen the lock in the InnoDB because a row-level lock in InnoDB is suitable for very small spaces. InnoDB also supports foreign key coercion. In SQL queries, you are free to mix tables of the InnoDB type with other MySQL table types, even in the same query.
The InnoDB is designed for maximum performance when dealing with large amounts of data. Its CPU efficiency may be unmatched by any other disk-based relational database engine.
The InnoDB storage engine is fully integrated with the MySQL server, and the InnoDB storage engine maintains its own buffer pool to cache data and indexes in main memory. InnoDB stores its table and index in a table space, a tablespace can contain several files (or raw disk partitions). This is different from the MyISAM table, such as in the MyISAM table where each table is in a separate file. The InnoDB table can be any size, even if the file size is limited to 2GB on the operating system.
The InnoDB is included in the MySQL binary distribution by default. Windows Essentials Installer makes InnoDB a default table for MySQL on Windows.
InnoDB is used to generate large database sites that require high performance. The famous Internet news site slashdot.org runs on InnoDB. Mytrix, Inc. stores more than 1TB of data on InnoDB, and some other sites handle an average of 800 insertions/updates per second on InnoDB.
the difference between InnoDB and MyISAM
Overview of differences:
MyISAM is the default storage engine in MySQL, and generally not too many people are concerned about this thing. Deciding what kind of storage engine to use is a very tricky thing to do, but it's worth it to study, the article here only considers the two MyISAM and InnoDB, because these two are the most common.
Let's answer some questions first:
Do you have a foreign key in your database?
Do you need business support?
Do you need full-text indexing?
What query patterns do you often use?
How big is your data?
Thinking about these questions can help you find the right direction, but that's not absolute. If you need transaction processing or foreign keys, then InnoDB may be a good way. If you need full-text indexing, then generally speaking, MyISAM is a good choice because it is built in the system, however, we do not actually test 2 million rows of records in a regular manner. So, even slower, we can get full-text indexing from InnoDB by using Sphinx.
The size of the data is an important factor in what kind of storage engine you choose, and large datasets tend to choose the INNODB approach because they support transactional processing and failback. The small database determines the length of time to recover, and InnoDB can use the transaction log for data recovery, which is faster. While MyISAM may take hours or even days to do these things, InnoDB only takes a few minutes.
Your habit of manipulating database tables can also be a factor that has a significant impact on performance. For example, COUNT () can be very fast in the MyISAM table, and it can be painful under the InnoDB table. While the primary key query will be quite fast under InnoDB, it is important to be careful that if our primary key is too long it can cause performance problems. A large number of inserts statements will be faster under MyISAM, but updates will be faster under innodb-especially when concurrency is large.
So, which one do you use to check? From experience, if it is a small application or project, then MyISAM may be more appropriate. Of course, the use of MyISAM in large-scale environments can be a great success, but it's not always the case. If you are planning to use a project with a large amount of data and require transactional or foreign key support, then you should really use the InnoDB method directly. But it is important to remember that InnoDB tables require more memory and storage, and converting 100GB MyISAM tables to InnoDB tables may make you have a very bad experience.
Summary of differences:
1.InnoDB does not support indexes of type Fulltext.
The exact number of rows in the table is not saved in 2.InnoDB, that is, when you execute select COUNT (*) from table, InnoDB scans the entire table to calculate how many rows, but MyISAM simply reads the saved rows. Note that when the COUNT (*) statement contains a where condition, the operation of the two tables is the same.
3. For a field of type auto_increment, InnoDB must contain only the index of that field, but in the MyISAM table, you can establish a federated index with other fields.
4.DELETE from table, InnoDB does not reestablish the table, but deletes one row at a time.
The 5.LOAD table from master operation has no effect on InnoDB, and the workaround is to first change the InnoDB table to a MyISAM table, import the data and then change it to a InnoDB table, but not for tables that use additional InnoDB features, such as foreign keys.
In addition, the row lock of the InnoDB table is not absolute, and if MySQL cannot determine the range to scan when executing an SQL statement, the InnoDB table also locks the full table, such as the Update table set num=1 where name like "%aaa%"
Ways to improve InnoDB performance:
MyISAM and InnoDB Storage engine performance difference is not very large, for InnoDB, the main impact of performance is innodb_flush_log_at_trx_commit this option, if set to 1, then each time you insert data is automatically submitted, resulting in a sharp decline in performance, should be related to the refresh log, set to 0 efficiency can see a significant increase, of course, you can also submit "Set autocommit = 0" in SQL to set up to achieve good performance. In addition, I have heard that setting up Innodb_buffer_pool_size can improve the performance of InnoDB, but I have found no particularly significant improvement in my test.
Basically we can consider using InnoDB to replace our MyISAM engine, because InnoDB itself a lot of good features, such as transaction support, stored procedures, views, row-level locking and so on, in the case of a lot of concurrency, I believe InnoDB performance is certainly much stronger than MyISAM, of course , the corresponding configuration in the MY.CNF is also more critical, good configuration, can effectively accelerate your application.
Any kind of table is not omnipotent, only appropriate for the business type to choose the appropriate table type, to maximize the performance advantage of MySQL. Articles you may be interested in:
- MySQL Storage engine Summary
- A brief analysis of MySQL memory storage engine
- Mysql5.5 InnoDB Storage engine configuration and optimization
- The difference between MySQL storage engine InnoDB and MyISAM
- MyISAM and InnoDB differences in MySQL storage engine
- MySQL Date data type, Time type usage Summary
- MySQL data type and characters commonly used segment Properties summary
- MySQL data type varchar detailed
- MySQL Data Type Detailed
- MySQL storage engine and data type (ii)
The difference between myism and InnoDB in MSYQL
