MySql存储引擎使用及管理

一、查看支持的数据库引擎

• Engine：引擎名称
• Support：表示该数据库是否支持该引擎,DEFAULT为默认使用的引擎
• Comment：引擎的功能描述
• Transactions：是否支持事务
• XA：是否支持事务回滚
• Savepoints：是否支持外键

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

• 查看表使用的存储引擎
• MySQL> show create table 库.表 \G

mysql> show create table  tarena.departments \G

二、创建存储引擎

• 建表时指定表使用的存储引擎

# 创建innodb引擎的表
mysql> create table db10.b(
  name char(10)
)engine = innodb;
# 创建memory引擎的表
mysql> create  table db10.c(
  addr char(10)
)engine = memory;

三、修改存储引擎

• 修改数据库服务默认使用的存储引擎

[root@host61 ~]#vim /etc/my.cnf
[mysqld]
default-storage-engine=myisam  # 添加此行
:wq
            
[root@host61 ~]# systemctl restart mysqld
[root@host61 ~]# mysql  -uroot -p密码 

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB             | YES     | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MyISAM             | DEFAULT | MyISAM storage engine                                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

• alter修改表的存储引擎

# 将memory存储引擎修改为myisam，
mysql> alter table db10.c engine=myisam;

四、补充说明：

• 每种引擎，存储的文件数量也是不同的

# myisam存储引擎的表 每个表对应3个表文件
mysql> system ls /var/lib/mysql/db10/a.*
/var/lib/mysql/db10/a.frm  
/var/lib/mysql/db10/a.MYD  
/var/lib/mysql/db10/a.MYI

# innodb存储引擎的表 每个表对应2个表文件
mysql> system ls /var/lib/mysql/db10/b.*
/var/lib/mysql/db10/b.frm  
/var/lib/mysql/db10/b.ibd

# memory存储引擎的表 每个表对应1个表文件
mysql> system ls /var/lib/mysql/db10/c.*
/var/lib/mysql/db10/c.frm

• 在表存储存储数据之前修改储引擎，存储数据的位置也会改变，存储文件数量结构也会发生改变

# 将memory存储引擎修改为myisam，存储文件数量结构发生改变
mysql> alter table db10.c engine=myisam;
mysql> system ls /var/lib/mysql/db10/c.*
/var/lib/mysql/db10/c.frm  
/var/lib/mysql/db10/c.MYD  
/var/lib/mysql/db10/c.MYI