MyISAM & InnoDB

Yisam: This is the default type. It is based on the traditional isam type and is a standard method for storing records and files. compared with other storage engines, MyISAM has most tools for checking and repairing tables. myISAM tables can be compressed and support full-text search. they are not transaction-safe and do not support foreign keys. If a transaction is rolled back, incomplete rollback is not atomic. If you execute a large number of select statements, MyISAM is a better choice. In addition, binary data files of the MyISAM type can be migrated in different operating systems. That is, it can be copied directly from windows to Linux.

InnoDB: This type is transaction-safe. it has the same features as the bdb type. They also support foreign keys. the InnoDB table is fast. it has more features than bdb. Therefore, if you need a transaction-safe storage engine, we recommend that you use it. if your data executes a large number of insert or update operations, InnoDB tables should be used for performance considerations. For InnoDB-type tables that support transactions, autocommit is enabled by default andProgramNo explicit call of begin to start the transaction, resulting in automatic commit for each inserted entry, seriously affecting the speed. You can call begin before executing the SQL statement. Multiple SQL statements form a transaction (even if you open the autocommit statement), which greatly improves the performance.

Merge engine: Type allows you to merge many tables with the same structure into one table. Then you can execute the query. The results returned from multiple tables are the same as those returned from one table. Each merged table must have the same table definition. The merge storage engine is most useful in the following scenarios. If you need to keep logging into the MySQL database and create a single table every day, every week, or every month, in addition, to make a total query from multiple tables, the MERGE table will be very effective at this time. However, this feature has limitations. Only MyISAM tables can be merged and must comply with the same table definition restrictions. Although this seems to be a big problem, if another table type (such as InnoDB) is used, this merge may not be needed.

Memory (memory) storage engine (formerly known as Heap Storage engine) stores all data in the memory. Once the MySQL server is shut down, any information stored in the memory will be lost. However, the format of a single table is retained, allowing you to create a temporary table for storing information. In this way, every time the database server is started, you do not need to reinvent this table to quickly access information. Using the memory storage engine for a long time is generally not a good idea because data is easy to lose. However, if you have enough memory, it is very effective to use memory-based tables to execute complex queries in large datasets.MethodIt can greatly improve the performance. The best way to use a memory table is to use a "select" statement to select a large dataset from your original disk-based table, and then analyze the information you need. I used this technology to extract network record data for a month. In fact, it is the data extracted from tables created using the archive storage engine, then, query the specific URL, website, and other key points.

Example engine: it is actually an example of storage engine programming. It can be used as the basis for other engines in the MySQL system. Example does not support data insertion. It is not a practical engine for any form of database access questions. However, example is a good guide to how to develop your own storage engine, so it is an effective engine for programmers.

Bdb storage engine: it may have a larger chance to survive the crash and has the ability to perform transaction commit and rollback operations. The bdb engine supports page-level locking.