Mysql dialect configuration in hibernate_mysqlsqldialect_Micrle_007’s blog

RDBMS dialect

Second, the difference between MySQL: InnoDB and MyISAM

(Note: MySQLInnoDBDialect and MySQLMyISAMDialect inherit from MySQLDialect.)

InnoDB and MyISAM are the two most important data storage engines of MySQL. Both of them can be used to store tables and indexes. Each has its advantages and disadvantages, depending on the specific application. The basic difference is: the MyISAM type does not support advanced processing such as transaction processing, while the InnoDB type supports it. The MyISAM type table emphasizes performance, and its execution is faster than the InnoDB type, but it does not provide transaction support, while InnoDB provides transaction support and advanced database functions such as external keys.

InnoDB provides MySQL with transaction (commit), rollback (rollback) and crash recovery capabilities (crash recovery capabilities), multi-version concurrency control (multi-versioned concurrency control) transaction security (transaction-safe (ACID compliant)) type table. InnoDB provides row-level locks (locking on row level), providing non-locking reads (non-locking read in SELECTs) similar to Oracle. InnoDB locks at the row level and also provides an Oracle-style consistent nonlocking read in the SELECT statement. In addition, InnoDB is designed for maximum performance when processing huge amounts of data. Its CPU efficiency is probably unmatched by any other disk-based relational database engine. Based on MySQLInnoDBDialect, it has the same function as InnoDB.

The InnoDB storage engine is fully integrated with the MySQL server. The InnoDB storage engine maintains its own buffer pool for caching data and indexes in main memory. InnoDB stores its tables & indexes in a tablespace, which can contain several files (or raw disk partitions). This is different from MyISAM tables, for example, where each table is stored in a separate file. InnoDB tables can be of any size, even on operating systems where file sizes are limited to 2GB.

InnoDB is transaction-safe. It has the same characteristics as BDB types, and they also support foreign keys. InnoDB tables are fast. It has richer features than BDB, so if you need a thingROM table, InnoDB will not recreate the table, but delete it row by row.

5. The 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 data. However, for additional InnoDB features used (such as foreign keys) The table does not apply.

In addition, the row lock of the InnoDB table is not absolute. If MySQL cannot determine the range to be scanned when executing a SQL statement, the InnoDB table will also lock the entire table. For example, update table set num=1 where name like “%aaa% “.

Any kind of table is not a panacea. Only by selecting the appropriate table type for the business type can the performance advantages of MySQL be maximized.

Attachment: The other two storage engines of MySQL, MEMORY and MERGE

The MEMORY storage engine uses the content stored in memory to create tables. Each MEMORY table actually corresponds to only one disk file. Table access of MEMORY type is very fast, because its data is placed in memory, and the HASH index is used by default. But once the service is closed, the data in the table will be lost.
The MERGE storage engine is a combination of a group of MyISAM tables, and these MyISAM tables must have exactly the same structure. The MERGE table itself has no data, and the query, update, and delete operations on the MERGE type table are performed on the internal MyISAM table.

The MEMORY type storage engine is mainly used for code tables whose content does not change frequently, or as an intermediate result table for statistical operations, which is convenient for efficient analysis of the intermediate results and obtaining the final statistical results. Be cautious when updating the table of the MEMORY storage engine, because the data is not actually written to the disk, so you must consider how to obtain the modified data after the next restart of the service.

MERGE is used to logically combine a series of equivalent MyISAM tables and refer to it as one object. The advantage of the MERGE table is that it can break through the limit on the size of a single MyISAM table. By distributing different tables on multiple disks, the access efficiency of the MERGE table can be effectively improved.

http://www.cnblogs.com/xiohao/ p/3633418.html