MyISAM and InnoDB engine

Mysql provides a plug-in ( Pluggable Storage Engine , the storage engine is based on the table.

show engines; View Storage Engine

650) this.width=650; "src=" http://blog.itpub.net/attachment/201408/1/29272216_1406872527vuBN.jpg "width=" 700 " height= "401" style= "border:0px;"/>

Note : in themysqldoption Group of the My.ini configuration file, the parameter default-storage-engine configures the default storage engine for the MySQL service instance. After MySQL is installed by default, thedefault-storage-engine parameter value is InnoDB.

InnoDB Storage Engine

The InnoDB is transaction (transaction) secure and supports foreign keys (foreign key). If a table primarily provides OLTP support, you need to perform a number of additions and deletions (insert, update, delete). For InnoDB tables that support transactions, the main reason for the speed is that the auto-commit (autocommit) option is turned on, or the program does not show the call "BEGIN TRANSACTION;" and " Commitcommits the transaction, causing each insert,database , or update statement to automatically start the transaction and commit the transaction, severely affecting the update ( Insert,update,delete).

The MyISAM storage engine primarily provides OLAP support,MyISAM most tools with self-test and repair tables. The MyISAM table can be compressed. If a table requires a large number of select statements, MyISAMis used for performance reasons.

Using set Default_storage_engine=myisam, you can modify the storage engine;

If you want to permanently set the default storage engine, you need to modify the parameter values default-storage-engine in the "mysqld" option group in the my.ini configuration file and need to restart MySQL service.

Create Table My_table

Select the InnoDB engine, and after creating the table, generate a table name, a table structure definition file my_table.frm with the suffix named frm .

The Frm file records the my_table table structure definition.

Selecting the MyISAM engine, after creating the table, generates a table name, a table structure definition file with the suffix named frm , and automatically creates a table file named table name, suffix named MYD(that is, MYData The file name is the table name, and the suffix name is MYI(myindex) index file.

Table Records

NOTE:null and empty string " ' " are two concepts. For example, querying a record with a name value of null requires "name is null" and a record that queries the name value to be an empty string "" "needs to be used" Name= '". Similarly,NULL is not the same as the concept of the full number 0 and the space character " ' ".

InnoDB Table Space

The InnoDB table space is divided into shared table spaces and exclusive table spaces.

To share a table space:

The data information, index information, various meta-data information, and rollback (UNDO) information for all the InnoDB tables of all databases hosted by the Mysql service instance are all stored in the shared file. By default, the file is located at the root of the database, and the file name is ibdata1, and the initial size is 10M. You can use the mysql command "show variables like ' Innodb_data_file_path ';" to view the properties of the file (file name, initial size of the file, auto-grow, and other attribute information).

Exclusive space

If the position of the global system variable innodb_file_per_table is set to ON (innodb_file_per_table), the default value is OFF), Then when you create a new table for the InnoDB storage engine later, the underlying data information, the Lasso engine, is saved to the exclusive tablespace file.

Exclusive use of space

First, first use the mysql command "show variables like ' innodb_file_per_table ';" View the value of the global system variable, and then use the mysql command "set @ @global. innodb_file_per_table=on;" Set the parameter innodb_file_per_table in the Global system variable (@ @global) to ON. Next, use the mysql command "show variable like ' innodb_file_per_table ';" View the value of the global system variable.

Note: The Redo log information is recorded in the ib_logfile0 and ib_logfile1 redo log files in a round-robin strategy. Either the table for the MyISAM storage engine or the table for the InnoDB storage engine must have a single frm table structure file.

If the database also contains InnoDB tables, when backing up the database, you need to not only copy the entire database directory, but also copy the shared tablespace file ibdata1, as well as redo the log file ib_ Logfile0 and ib_logfie1.

Delete a table

DROP TABLE table_nameto delete the tables named table_name .