MySQL Database engine--myisam,innodb

As a Java web Developer, the differences between the two types of engines are quite good for MySQL databases, and the following is an introduction to the understanding of the two types of engines I've accumulated at work.

MyISAM:

If you do not change the MySQL configuration file (My.ini), MyISAM is the default storage engine for MySQL, each MyISAM table corresponds to three local storage files: frm file storage table definition, data file MYD (MYData), index file MYI (myindex).

MyISAM type of table more emphasis on performance, it is faster than InnoDB, but does not support transactions, and does not support hot backup, but we can directly copy its three files (frm,myd,myi) to other libraries can be achieved by the spare parts, very convenient.

InnoDB:

InnoDB is a transactional engine that supports rollback, crash resiliency, multi-version concurrency control, and acid transactions. InnoDB corresponding local storage file: frm, IBD (Index and data), in the MySQL data directory has a ibdata1 file, which records the local

MySQL server in each library of the InnoDB table of the operation record, larger, without this file InnoDB table can not be viewed properly, and InnoDB table spare parts basically only with hot backup, unless the entire data directory is copied, it is very difficult to restore, MySQL

The new version is also constantly improving hot-standby functionality, but its efficient, dynamic-hot-standby software is paid for. I think for those tables that often have to perform update, insert operations, with a large amount of data, a complex structure, and a cautious operation, choose InnoDB.

　　

In general, I use the MyISAM engine, unless some I feel it necessary to use the rollback table in the repair process I will use the InnoDB, but for some of the project on the user information, product information table is to use InnoDB, after all, the project in the maintenance of

Each data table involves an incremental backup problem. In addition, recently found that the new version of MySQL in the number of characters Jianjian index, INNODB support better, before the single-field index of the maximum length of 767 bytes (data actually accounted for the long), using UTF-8

character sets, each character is stored with 3 bytes, and a prefix index of more than 255 characters is created on a field of TEXT or VARCHAR type, while the sum of the maximum length of the MyISAM index key cannot exceed 1000 and is not the sum of the actual data length. instead, the index key field defines the sum of the lengths.

Then, in specific to the length of the field, fixed type, or should be to understand the various encoding formats for the Chinese and English characters to store their own space to spend.

Many people say that the backstage to the last than is the SQL Foundation, think indeed, business code logic is not too complex, the actual development of most of the code is in the call of various interfaces, and then a little more complex is the project of various configurations, release, and later on the system of various

Performance testing. Web application, the core is the data, write good SQL statements, optimization is very important. The database can also solve a lot of life's small problems, such as the Excel document in the large quantities of data extraction, change and so on.

MySQL Database engine--myisam,innodb