Default_storage_engine of MySQL performance optimization

1: View the storage engine information for MySQL

1.1 Use the show Engines command.

Mysql> show engines;

+--------------------+---------+----------------------------------------------------------------+-------------- +------+------------+

| Engine | Support | Comment | Transactions | XA | savepoints |

+--------------------+---------+----------------------------------------------------------------+-------------- +------+------------+

| Federated | NO | Federated MySQL Storage Engine | NULL | NULL | NULL |

| Mrg_myisam | YES | Collection of identical MyISAM tables | NO | NO | NO |

| MyISAM | YES | MyISAM Storage Engine | NO | NO | NO |

| Blackhole | YES | /dev/null Storage Engine (anything you write to it disappears) | NO | NO | NO |

| CSV | YES | CSV Storage Engine | NO | NO | NO |

| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |

| ARCHIVE | YES | Archive Storage Engine | NO | NO | NO |

| InnoDB | DEFAULT | Supports transactions, Row-level locking, and foreign keys | YES | YES | YES |

| Performance_schema | YES | Performance Schema | NO | NO | NO |

+--------------------+---------+----------------------------------------------------------------+-------------- +------+------------+

9 rows in Set (0.01 sec)

Support column, yes indicates that the current version supports this storage engine, and default indicates that the engine is the default engine. No indicates that the storage engine is not supported. As shown below, InnoDB is the default storage engine.

1.2 can view system variables Default_storage_engine or Storage_engine

1:default_storage_engine represents the default storage engine for a permanent table (permanent tables).

2:default_tmp_storage_engine represents the default storage engine for temporary tables.

Storage_engine This system variable is not recommended, it has been replaced by the system variable Default_storage_engine

Mysql> Show variables like '%storage_engine% ';

+----------------------------+--------+

| variable_name | Value |

+----------------------------+--------+

| Default_storage_engine | InnoDB |

| Default_tmp_storage_engine | InnoDB |

| Storage_engine | InnoDB |

+----------------------------+--------+

3 Rows in Set (0.00 sec)

2: How do I modify the default storage engine for MySQL?

2.1 Modify the MY.CNF, add the parameter default-storage-engine inside the configuration file, and then restart the database service.

[Mysqld]

Default-storage-engine=myisam

Then check the default storage engine and you'll see that MyISAM is the default storage engine.

2.2 Modifying System variables with commands (variables)

Mysql> set Default_storage_engine=innodb;

Query OK, 0 rows affected (0.09 sec)

Mysql> show engines;

+--------------------+---------+----------------------------------------------------------------+-------------- +------+------------+

| Engine | Support | Comment | Transactions | XA | savepoints |

+--------------------+---------+----------------------------------------------------------------+-------------- +------+------------+

| Federated | NO | Federated MySQL Storage Engine | NULL | NULL | NULL |

| Mrg_myisam | YES | Collection of identical MyISAM tables | NO | NO | NO |

| MyISAM | YES | MyISAM Storage Engine | NO | NO | NO |

| Blackhole | YES | /dev/null Storage Engine (anything you write to it disappears) | NO | NO | NO |

| CSV | YES | CSV Storage Engine | NO | NO | NO |

| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |

| ARCHIVE | YES | Archive Storage Engine | NO | NO | NO |

| InnoDB | DEFAULT | Supports transactions, Row-level locking, and foreign keys | YES | YES | YES |

| Performance_schema | YES | Performance Schema | NO | NO | NO |

+--------------------+---------+----------------------------------------------------------------+-------------- +------+------------+

9 Rows in Set (0.00 sec)

Mysql>

3: How do I view the storage engine used by the table?

There are several ways to view the storage engine used by a table:

3.1 Query information_schema. TABLES

Mysql> SELECT Table_schema,

TABLE_NAME,

Table_type,

ENGINE

From INFORMATION_SCHEMA. TABLES

-WHERE table_name = ' TEST ';

+--------------+------------+------------+--------+

| Table_schema | table_name | Table_type | ENGINE |

+--------------+------------+------------+--------+

| GSP | TEST | BASE TABLE | InnoDB |

+--------------+------------+------------+--------+

1 row in Set (0.00 sec)

Mysql>

3.2 Use the show CREATE TABLE TEST \g command.

mysql> SHOW CREATE TABLE TEST \g;

1. Row ***************************

Table:test

Create table:create Table ' TEST ' (

' ID ' int (one) DEFAULT NULL,

' COL1 ' varchar (6) DEFAULT NULL,

' COL2 ' varchar (6) DEFAULT NULL,

' COL3 ' varchar (6) DEFAULT NULL,

' COL4 ' varchar (6) DEFAULT NULL

) Engine=innodb DEFAULT charset=latin1

1 row in Set (0.00 sec)

ERROR:

No query specified

Mysql>

3.3 SHOW TABLE STATUS

mysql> SHOW TABLE STATUS WHERE name= ' TEST ';

ERROR 1046 (3d000): No Database selected

Mysql>

Mysql>

Mysql> use GSP;

Reading table information for completion of table and column names

Can turn off this feature to get a quicker startup with-a

Database changed

mysql> SHOW TABLE STATUS WHERE name= ' TEST ';

As shown above, this writing must be done in a database, otherwise you can only use the following syntax

SHOW TABLE STATUS [{from | in} Db_name]

[like ' Pattern ' | WHERE Expr]

Mysql> SHOW TABLE STATUS from GSP WHERE name= ' TEST ';

4: How do I modify the storage engine for a related table?

Modifying the table's storage engine is very simple and the syntax is as follows

ALTER TABLE my_table ENGINE = InnoDB;

mysql> ALTER TABLE TEST Engine=myisam;

Query OK, 0 rows affected (0.02 sec)

records:0 duplicates:0 warnings:0

mysql> SHOW CREATE TABLE TEST \g;

1. Row ***************************

Table:test

Create table:create Table ' TEST ' (

' ID ' int (one) DEFAULT NULL,

' COL1 ' varchar (6) DEFAULT NULL,

' COL2 ' varchar (6) DEFAULT NULL,

' COL3 ' varchar (6) DEFAULT NULL,

' COL4 ' varchar (6) DEFAULT NULL

) Engine=myisam DEFAULT charset=latin1

1 row in Set (0.00 sec)

ERROR:

No query specified

Mysql>

5: How to specify the storage engine when creating a table.

When creating a table, if you want to specify a storage engine, you only need to set the parameter engine. Very simple.

Mysql> CREATE TABLE TEST1 (ID INT) Engine=innodb;

Query OK, 0 rows affected (0.02 sec)

Mysql> CREATE TABLE TEST2 (ID INT) Engine=myisam;

Query OK, 0 rows Affected (0.00 sec)

Default_storage_engine of MySQL performance optimization