If you want to modify the storage engine of MySQL database, you must understand the two engines, and clearly understand the difference between the two engines.
MySQL database supports two common storage engines:
InnoDB engine: Provides support for database ACID transactions, and implements four isolation levels of the SQL standard. The engine also provides row-level locks and foreign key constraints. Its design goal is to deal with large-capacity database system. It is actually a complete database system based on MySQL background. Innodb will build a buffer pool in memory to buffer data and index when MySQL runs. However, the engine does not support FULLTEXT-type indexes, and it does not save the number of rows in the table. When SELECT COUNT(*) FROM TABLE, it needs to scan the full table. This engine is of course preferred when database transactions are required. Because of the smaller granularity of locks and the fact that writes do not lock the entire table, Innodb engine can improve efficiency when concurrency is high. But using row-level locks is not absolute. If MySQL cannot determine the scope to scan when executing an SQL statement, InnoDB tables will lock the entire table as well.
MyIASM Engine: MySQL default engine, but it does not support database transactions, row-level locks and foreign keys, so when INSERT (insert) or UPDATE (update) data, write operations need to lock the entire table, which is less efficient. However, unlike Innodb, MyIASM stores the number of rows in the table, so SELECT COUNT(*) FROM TABLE only needs to read the saved values directly without having to scan the whole table. MyIASM is also a good choice if the table reads much more than writes and does not require database transaction support.
Main differences:
1. MyIASM is non-transactional security while InnoDB is transactional security.
2. The granularity of MyIASM locks is table-level, while InnoDB supports row-level locks.
3. MyIASM supports full-text type index, while InnoDB does not support full-text index.
4. MyIASM is relatively simple, and its efficiency is better than InnoDB. Small applications can consider using MyIASM.
5. MyIASM tables are saved as files, which makes cross-platform use more convenient.
Application scenarios:
1. MyIASM manages non-transactional tables, provides high-speed storage and retrieval as well as full-text search capabilities. If a large number of select operations are performed in the re-application, MyIASM should be selected.
2. InnoDB is used for transaction processing. It has ACID transaction support and other characteristics. If a large number of insert and update operations are performed in the application, InnoDB should be selected.
View engine information in mysql database
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) //InnoDB engine is the default storage engine.
Support column, YES indicates that the current version supports the storage engine, and DEFAULT indicates that the engine is the default engine. NO indicates that the storage engine is not supported.
View the system variable default_storage_engine or 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)
default_storage_engine represents the default storage engine for permanent tables. 2.
default_tmp_storage_engine represents the default storage engine for temporary tables.
The system variable storage_engine is not recommended and has been replaced by the system variable default_storage_engine.
Modify the default storage engine of MySQL database
[root@localhost ~]# Vim/etc/my.cnf//Write the main configuration file for mysql services
.................. //Omit some of the content and add the following
default-storage-engine=MyISAM
[root@localhost ~]# System CTL restart mysqld // restart mysql service
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 | DEFAULT | 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 | YES | 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)
//Look again and find that the MyISAM engine has become the default engineDirect modification of storage default engine in mysql database
mysql> set default_storage_engine=InnoDB; Query OK, 0 rows affected (0.00 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) //Look again and find that InnoDB engine has become the default engine
Note that the system variable default_storage_engine is BOTH (global and temporary) and can be dynamically modified. Note, however, that even if you change the default_storage_engine system variable, it will fail after restart. If you want to make permanent changes, it is better to set default-storage-engine value in my.cnf configuration file.
Default engine for viewing tables
mysql> show create table t1 \G;
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`id` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec
//You can see that the default engine used by the t1 table is MyISAM.Modify the default storage engine for tables
mysql> ALTER TABLE t1 ENGINE = InnoDB;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table t1 \G;
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
//You can see that the default engine used by the t1 table is InnoDB.Specify storage engine when creating tables
When creating a table, if you want to specify the storage engine, you only need to set the parameter ENGINE.
mysql> create table t2 (id int) engine=InnoDB; Query OK, 0 rows affected (0.00 sec) mysql> create table t3 (id int) engine=MyISAM; Query OK, 0 rows affected (0.00 sec)