MySQL's Database engine

MySQL has a lot of database engine, the most important is MyISAM, InnoDB, Heap (memory), in addition to BDB, archive data table, CSV, NDB, Federated

InnoDB Advantages:1. support for database transactions, supports four transaction levels (READ UNCOMMITTED, Read committed, Repeatable read, serializable) 2, provides row-level locks (built-in) and foreign keys, the purpose is to deal with a large number of database systems, row-level lock locking is only one transaction in the record, does not lock the data table, so other users can access the operational database, can greatly improve efficiency. In addition, it can identify deadlocks (two or more processes waiting for each other, dead loops), and automatically terminates one of them. 3, you can Crash Recovery, there is a buffers Disadvantages:1. Full-text indexing is not supported。 2. Large space consumption3. do not save table row Count, a full table scan is required to search the number of rows, slower than myiasm. myiasm engine (mysql default)Myiasm is divided into: MyISAM static (data column each has a fixed length, high access efficiency, high security), MyISAM dynamic (high space utilization, custom), MyISAM Compressed (access speed, but modified compressed data) 1, to the database Transaction not supported2, row-level locks and foreign keys are not supported, but support table Lock3. Database read/write separation selection of two engines for InnoDB and MyISAMDifferent engines can be used in different data tables, that is, the two engines can be used in the same database. 1, save space and time to choose MyISAM2, use to the transaction, security higher, need the user to operate the choice InnoDB3, no absolute optimal, concrete situation concrete view 1) Large-size datasets tend to select InnoDB engines because they support transactional processing and failback. The size of the database determines the length of time the recovery takes place, and InnoDB can use the transaction log (binary) for data recovery, which can be relatively fast with the InnoDB engine. primary key cannot be too long2) BULK INSERT use MyISAM fast because it does not support transactions and does not need to be rolled back when there is a problem HeapThe heap data table only exists MemoryThe hash index is used, so the speed of the main use is to act as Temporary Data Sheet(the MySQL service stops running, then disappears) is mainly suitable for small quantity, high access speed

MySQL Database engine