MySQL Storage engine learning (i)

What is the storage engine? For a database, the storage engine is the module or function that implements the data structure definition, storage and query of the database.

Different databases have different underlying storage mechanisms, which are different storage engines. When the database server receives the client's SQL request, it takes the action to the underlying storage engine for processing.

If you touch databases such as Oracle or MSSQL, you may not be paying too much attention to the storage engines of these databases because there is only one storage engine for both Oracle and MSSQL, and users cannot modify their storage engines. Compared to other databases, MySQL offers a variety of storage engines to meet different storage requirements.

The MySQL server's storage engine is an "pluggable" architecture that provides a standard interface for dynamic loading and unloading of the storage engine, which is the basis for MySQL's implementation of a variety of storage engines, and in addition to the storage engine provided by the MySQL database, MySQL supports the integration of third-party engines, With the standard interface provided by MySQL, third-party storage engines can be easily integrated into our MySQL server, which means that if the storage engine provided by MySQL does not meet our needs, we can customize our own storage engine to meet our needs.

In MySQL, the storage engine is also called a data table processor or table type, different storage engine implementation of the effect of the various, the same storage engine processing data tables have common attributes and characteristics, we can use the different scenarios for our application, storage data size to use different storage engine.

　　

After simply saying a little bit of theory, here we begin to learn the MySQL storage engine, the content mainly has the following aspects.
1. View the storage engines supported by the database

Before using the storage engine, we need to know which storage engines are supported by the MySQL server and can execute the following two statements.

SHOW ENGINES;

Or

SELECT *  from Information_schema. ENGINES;

The execution results are as follows:

The storage engine information supported by the MySQL server is stored in the engines table of the INFORMATION_SCHEMA database, and we can use the Select statement to query the results of the table. However, it is more convenient to use show ENGINES.

The engines table holds basic information about the storage engine, and the Engines table field has the following meanings:

Engine: The name of the storage engine. Support: The storage engine is available, yes is available, no is unavailable, and default represents the storage engine for the database. Comment: Information about the storage engine. Transactions: Whether the transaction is supported. XA: Whether distributed transactions are supported. Savepoints: Whether partial transaction rollback is supported.

2. Viewing and setting the default storage engine

When the Engines table support field value is default, it indicates that the storage engine is the defaults storage engine, in addition to other ways to view or set the default storage engine.

(1). Set the default storage engine when starting the MySQL server

When you start the server through the MYSQLD program, you can set the default storage engine for MySQL by adding the Default-storage_engine = engine_name option after the program.

(2). Setting the default storage engine through configuration files

Configurations such as My.ini or MY.CNF can also be configured as follows
　　

[mysqld] default - = engine_name;

This way, when the MySQL server starts, the configuration file is automatically read to set the default storage engine.

After starting the server, if you want to change the default storage engine, you can use the following four ways

(3). View the current session default storage engine

SELECT @ @SESSION. Storage_engine;

(4). Set the current session default storage engine

SET = engine_name;

(5). View the global default storage engine

SELECT @ @GLOBAL. Storage_engine;

(6). Set the global default storage engine

SET = engine_name;

Session level, that is, the setting is only valid for the current user, and does not affect other users, global represents the world level, if changed all users connected to the MySQL server after the default storage engine will use the value set by the global Storage_engine, So the execution of this statement requires Super privilege.

3. When creating a data table, specify the storage engine.

The storage engine is used for data tables, so when you create a table, you can specify any storage engine that the database supports, and the default storage engine is used if the CREATE statement does not use Engine=engine_name to specify the storage engine when the table is being built.

Example: Creating a data table with the name test, using the InnoDB storage engine

CREATE TABLE Test (    INT,    VARCHAR(= innodb;# Specifying the INNODB storage engine

The above table statements use the InnoDB storage engine, so the storage and querying of the table are implemented by InnoDB.

4. View the storage engine for the data table

You can specify the storage engine when you create a data table, and you can query the storage engine used by a data table by creating statements.

Example: Query the storage engine used by the data table test

The SHOW CREATE TABLE statement is used to query the SQL statement that creates the data, and by building a table statement, we can query the storage engine used by the data table.

5. Modifying the Storage data Table storage Engine

You can also modify it by using the ALTER statement after you have finished executing the Build table statement.

ALTER TABLE test ENGINE MyISAM;

MySQL Storage engine learning (i)