I used MySQL with Navicat for MySQL (Navicat for MySQL v9.0.15 registration code generator) Operation library, table operation, the default table is the InnoDB type, also do not care. Recently do the project found in addition to the InnoDB type outside there are MyISAM types, the last officer net looked after the following summary:
It turns out that MyISAM types do not support advanced processing such as transaction processing, and InnoDB type support. The MyISAM type of table emphasizes performance, which is performed more quickly than the InnoDB type, but does not provide transactional support, while InnoDB provides transactional support for advanced database functions such as external keys. In summary, it is possible to use different storage types depending on the data table. and MyISAM is file storage, can be used directly between the different operating system copies.
InnoDB:
InnoDB provides MySQL with transaction security with transactional (commit), rollback (rollback), and crash-repair capabilities (crash recovery capabilities) (Transaction-safe (ACID compliant )) Type table. The InnoDB provides a row lock (locking on row level) that provides an unlocked read (non-locking read in selects) that is consistent with the Oracle type. These features improve the performance of multi-user concurrency operations. There is no need to widen the lock (lock escalation) in the InnoDB table because the InnoDB column lock (Row level locks) is suitable for very small space. InnoDB is the first table engine on MySQL to provide a foreign key constraint (FOREIGN key constraints). InnoDB's design goal is to handle a large-capacity database system, which is not comparable to other disk-based relational database engines. Technically, InnoDB is a complete database system placed in the background of MySQL, InnoDB in the main memory to establish its dedicated buffer pool for caching data and indexes. InnoDB the data and index in the table space, may contain multiple files, which is different from other, for example, in MyISAM, the table is stored in a separate file. The size of the InnoDB table is limited only by the size of the operating system file, typically 2 GB. InnoDB All tables are stored in the same data file Ibdata1 (also possibly multiple files, or stand-alone tablespace files), relatively poorly backed up, can copy files or use Navicat for MySQL.
MyISAM
Each MyISAM table is stored in three files: the frm file is stored in a tabular definition. The data file is MyD (MYData). The index file is an myi (myindex) extension.
Because MyISAM is relatively simple, it is better to be efficient than InnoDB, and small applications using MyISAM are a good choice.
MyISAM tables are saved as files, and using MyISAM storage in cross-platform data transfer saves a lot of hassle
MyISAM and InnoDB explanation
InnoDB and MyISAM are the two most common table types used by many people when using MySQL, both of which have pros and cons, depending on the application. The basic difference is that the MyISAM type does not support advanced processing such as transaction processing, and InnoDB type support. The MyISAM type of table emphasizes performance, which is performed more quickly than the InnoDB type, but does not provide transactional support, while InnoDB provides advanced database functionality such as transactional support and external keys.
The following are some of the details and the specific implementation differences:
1. InnoDB does not support indexes of type Fulltext.
2. The exact number of rows in a table is not saved in 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.
5. The 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.
1, MyISAM does not support transactions, InnoDB is a transaction type of storage engine
When our table needs to use the transaction support, that must not choose MyISAM.
2, MyISAM only support table-level lock, BDB support page-level lock and table-level lock default to page-level lock, and INNODB support row-level lock and table-level lock default to row-level lock
Table-level Lock: locks the entire table directly, during lock-up, other processes cannot write to the table, and if a write lock is set, the other process is not allowed to read
MyISAM is a table-level locked storage engine that does not have a deadlock problem
For write, the table locking principle is as follows:
If there is no lock on the table, place a write lock on it, otherwise, place the lock request in the write lock queue.
For read, the table locking principle is as follows:
If there is no write lock on the table, place a read lock on top of it, or place the lock request in the Read lock queue
When a lock is released, the table can be obtained by a thread in the write lock queue, and then the thread in the lock queue is read. This means that if you have a lot of updates on a table, then your SELECT statement waits for all write locks
The thread finishes executing.
Row-level locks: locks only on the specified row, and other processes can operate on other rows in the table.
Row-level locks are the least granular type of MySQL lock, which can greatly reduce the conflict of database operations, but the smaller the granularity, the greater the cost of implementation.
Row-level locks can cause "deadlocks", and that is how it is caused, the reason for the analysis: MySQL row-level locks are not direct lock records, but lock index. Index is divided into primary key index and non-primary key index, if an SQL statement operates the primary
Key index, then MySQL will lock the primary key index, if the SQL statement operation is non-primary key index, then MySQL will first lock the non-primary key index, and then lock the primary key index.
In the update and delete operations, MySQL not only locks all the where conditions scanned indexes, but also locks adjacent key values.
"Deadlock" Example analysis:
Table test: (id,state,time) primary key index: ID non-primary key index: state
The state index is locked when the "UPDATE State =1011 WHERE state=1000" statement is executed, so MySQL also requests the Lock ID index because the state is not a primary key index
When another SQL statement is executed almost simultaneously with statement 1: "UPDATE state=1010 WHERE id=1" for statement 2 MySQL locks the ID index first, and because statement 2 operates the State field, MySQL also requests the lock
The state index is set. Then. Each other is locked in the desired index, and is waiting for each other to release the lock. So there's a "deadlock" situation.
Advantages of row-level locks:
There are many threads that have access to different rows, and there are only a few conflicts.
There are only a few changes when rolling back
A single row can be locked for a long time
Row-level Lock disadvantages:
More memory is consumed relative to page-level and table-level locks
When most rows of a table are in use, it is slower than page-level and table-level locks because you have to get more locks
When a group by operation is frequently used on most data, it is certainly slower than table-level and page-level locks.
Page-level Locks: table-level locks are fast, but conflict is high; row-level locks are slow, but conflict is low; page-level locks are the two of them, locking together a contiguous set of records at a time.
3, MyISAM engine does not support foreign keys, INNODB support foreign keys
4, the MyISAM engine table in a large number of high concurrent read and write will often appear table corruption situation
We are doing the project encountered this problem, the table insert and update operation is very frequent, the original use of the MyISAM engine, causing the table go out damage, and later replaced by the InnoDB engine.
Other causes of table corruption are:
Damage to data files due to abrupt server power outage, forced shutdown (mysqld not closed) causes table corruption
The mysqld process was killed during the write operation.
Disk failure
Table Damage Common symptoms:
Query tables cannot return data or return partial data
Failed to open table: Can ' t open file: ' xxx. MYI ' (errno:145).
Error:table ' P ' is marked as crashed and should be repaired.
Incorrect key file for table: ' ... '. Try to repair it
MySQL Table recovery:
For recovery of MyISAM tables:
You can use the Myisamchk tool that comes with MySQL: myisamchk-r tablename or Myisamchk-o tablename (more insured than the previous) to fix the table
5. MyISAM is more advantageous for count () query7. MyISAM supports full-text indexing (fulltext), InnoDB does not support
8, MyISAM engine table query, UPDATE, insert efficiency is higher than innodb
I did not do a detailed test, on the internet to intercept the predecessors test conclusions:
All performance tests are tested on computers with: Micrisoft window XP SP2, Intel (r) pentinum (r) M processor 1.6oGHz 1G memory.
Test method: Submit 10 consecutive query, table records total: 380,000, time unit s
Engine Type MyISAM InnoDB performance difference
Count 0.0008357 3.0163 3609
Query primary key 0.005708 0.1574 27.57
Querying for non-primary keys 24.01 80.37 3.348
Update primary key 0.008124 0.8183 100.7
Update non-primary key 0.004141 0.02625 6.338
Insertion 0.004188 0.3694 88.21
(1) After the index, for the MyISAM query can be accelerated: 4 206.09733 times times, the InnoDB query 510.72921 times times faster, while the MyISAM update slowed down to the original 1/2,innodb update speed slowed to the original 1/30. To see if the situation determines whether to index, such as the log table without querying, do not do any index.
(2) If your data volume is millions, and there is no transaction processing, then using MyISAM is the best choice for performance.
(3) The size of the InnoDB table is more large, with MyISAM can save a lot of hard disk space.
In this 38w table we tested, the table takes up space as follows:
Engine Type MyISAM InnoDB
Data 53,924 KB 58,976 KB
Index 13,640 KB 21,072 KB
Total space occupied 67,564 KB 80,048 KB
Another 176W record table, where the table occupies space, is as follows:
Engine Type MyIsam Innordb
Data 56,166 KB 90,736 KB
Index 67,103 KB 88,848 KB
Total space occupied 123,269 KB 179,584 KB
In addition, the row lock of the InnoDB table is not absolute, if MySQL cannot determine the scope 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%"
The main difference between the two types is that InnoDB supports transactional and foreign key and row level locks. and MyISAM does not support it. So MyISAM tend to be considered only suitable for use in small projects.
As a user of MySQL, InnoDB and MyISAM are more like, if the database platform to meet the requirements: 99.9% stability, convenient scalability and high availability, MyISAM is definitely the first choice.
The reasons are as follows:
1, most of the projects on the platform are read and write less projects, and MyISAM reading performance is stronger than InnoDB.
2, MyISAM index and data are separate, and the index is compressed, the memory usage of the corresponding improved a lot. Can load more indexes, and InnoDB is the index and the data is tightly bound, do not use compression which will cause innodb than MyISAM volume is large.
3, often 1, 2 months will occur application developers accidentally update a table where the scope of the wrong, resulting in this table can not be normal use, this time the superiority of the MyISAM is reflected, casually from the day of the copy of the compressed package out of the corresponding table file, casually put into a database directory, Then dump into SQL and then back to the main library, and the corresponding binlog complement. If it's InnoDB, I'm afraid it can't be so fast, don't tell me to let InnoDB regularly back up with an export xxx.sql mechanism, because the minimum amount of data for a database instance is roughly dozens of g in size.
4, from the application logic of contact, select COUNT (*) and order BY is the most frequent, probably can account for the entire SQL total statement of more than 60% of the operation, and this operation InnoDB actually will lock the table, many people think InnoDB is a row-level lock, That's just where the primary key is valid, and the non-primary key will lock the full table.
5, there is often a lot of application departments need me to give them regular data on some tables, MyISAM words are very convenient, as long as they correspond to the list of the frm. myd,myi files, let them in the corresponding version of the database to start the line, and InnoDB need to export xxx.sql, because the light to other people's files, by the dictionary data file, the other side is not available.
6, if and myisam than insert write operation, InnoDB also not up to MyISAM write performance, if is for index-based update operation, although MyISAM may be inferior innodb, but so high concurrency of write, from the library can chase is also a problem, It might as well be solved by a multi-instance sub-Library table architecture.
7, if it is used MyISAM, the merge engine can greatly speed up the development of the application department, they just do some select count (*) operation on this merge table, it is very suitable for a large project total of about hundreds of millions of rows of a type (such as log, survey statistics) business table.
Of course InnoDB is not absolutely not used, the project with the business is used InnoDB. In addition, someone might say that you myisam not be able to write too much, but you can make up for it through architecture.
The main difference between the two types is that InnoDB supports transactional and foreign key and row-level locks. MyISAM is not supported. So MyISAM tend to be considered only suitable for use in small projects.
I use MySQL as a user point of view, InnoDB and MyISAM are more like, but from my current operation of the database platform to meet the requirements: 99.9% stability, convenient scalability and high availability, MyISAM is definitely my first choice.
The reasons are as follows:
1, first of all, I am currently on the platform of the majority of projects are read more write less projects, and MyISAM reading performance is stronger than InnoDB.
2, MyISAM index and data are separate, and the index is compressed, the memory usage of the corresponding improved a lot. Can load more indexes, and InnoDB is the index and the data is tightly bound, do not use compression which will cause innodb than MyISAM volume is large.
3, from the platform point of view, often 1, 2 months will occur application developers accidentally update a table where the scope of the wrong, resulting in this table can not be normal use, this time MyISAM the superiority of the embodiment, casually from the day copy of the compressed package out of the corresponding table file, Put it in a database directory, then dump into SQL and back to the main library, and binlog the corresponding. If it's InnoDB, I'm afraid it can't be so fast, don't tell me to let InnoDB regularly back up with an export xxx.sql mechanism, because the smallest database instance on my platform has a size of dozens of g of data.
4, from my contact with the application logic, select COUNT (*) and order BY is the most frequent, probably can account for the entire SQL total statement of more than 60% of the operation, and this operation InnoDB actually will lock the table, many people think InnoDB is a row-level lock, That's just where the primary key is valid, and the non-primary key will lock the full table.
5, there is often a lot of application departments need me to give them regular data on some tables, MyISAM words are very convenient, as long as they correspond to the list of the frm. myd,myi files, let them in the corresponding version of the database to start the line, and InnoDB need to export xxx.sql, because the light to other people's files, by the dictionary data file, the other side is not available.
6, if and myisam than insert write operation, InnoDB also not up to MyISAM write performance, if is for index-based update operation, although MyISAM may be inferior innodb, but so high concurrency of write, from the library can chase is also a problem, It might as well be solved by a multi-instance sub-Library table architecture.
7, if it is used MyISAM, the merge engine can greatly speed up the development of the application department, they just do some select count (*) operation on this merge table, it is very suitable for a large project total of about hundreds of millions of rows of a type (such as log, survey statistics) business table.
Of course, InnoDB is not absolutely not, with business projects such as simulation stocks, I am using InnoDB, active users more than 200,000, is also very easy to cope with, so I personally also like InnoDB, but if from the database platform application, I would prefer MyISAM.
In addition, some people may say that you myisam can not resist too much write operation, but I can make up by the structure, say my existing database platform capacity: The total number of master and slave data in more than hundreds of T, more than 1 billion PV dynamic page per day, there are several large items are called by the data interface method is not counted into PV total, ( This includes a large project because the initial memcached was not deployed, resulting in a single database processing 90 million queries per day). My overall database server load averaged around 0.5-1.
The so-called transaction processing is atomic operation.
For example, support transaction processing of the InnoDB table, build one, post is to the points. You sent a post that executes an INSERT statement that inserts the content of the post, and then executes an UPDATE statement to increase your points. Assuming a special case occurs suddenly, insert succeeds and the update operation is not executed. This means that you posted a post without adding the corresponding points. This can cause user dissatisfaction. If transaction processing is used, both insert and update are put into the transaction to execute, this time, only when the INSERT and update two statements are executed to generate the data will be updated, written to, if any one of the statements fail, then rollback to the initial state, do not write. This ensures that the insert and update are definitely executed together.
The Mysiam table does not support transactional processing, while the Mysiam table does not support foreign keys. Foreign key Needless to say, right? If you don't know, check it online.
At the same time, when performing database write operations (Insert,update,delete), the Mysiam table locks the table, and the InnoDB table locks the rows. Popular point is that you execute an UPDATE statement, then the Mysiam table will lock the entire table, the other inserts and delete, update will be rejected, until the completion of the UPDATE statement execution will not be executed sequentially.
While the lock line, that is, you execute the UPDATE statement is, will only lock this record, only the other write to this record, update operations will be blocked and wait until the UPDATE statement is completed before execution, for other records of the write operation will not affect.
Therefore, when your database has a large number of write, update operations and less query or data integrity requirements are relatively high when you choose the InnoDB table. Select the Mysiam table when your database is primarily query-focused, compared with fewer updates and writes, and less stringent on business data integrity requirements. Because Mysiam table query operation efficiency and speed are faster than InnoDB
InnoDB and MyISAM Difference summary
Start building with 50+ products and up to 12 months usage for Elastic Compute Service
1 on 1 presale consultation
24/7 Technical Support 6 Free Tickets per Quarter Faster Response
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.
A comprehensive suite of global cloud computing services to power your business
Payment Methods We Support
