MySQL数据存储引擎的使用

• 查看服务支持的存储引擎
• 查看默认存储类型
• 更改表的存储引擎
• 设置数据库服务默认使用的存储引擎

步骤一：查看存储引擎信息

登入MySQL服务器，查看当前支持哪些存储引擎。

使用mysql命令连接，以root用户登入：

[root@dbsvr1 ~]# mysql -u root –pEnter password:Welcome to the MySQL monitor.  Commands end with ; or \\g.Your MySQL connection id is 9Server version: 5.7.17 MySQL Community Server (GPL)Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type \'help;\' or \'\\h\' for help. Type \'\\c\' to clear the current input statement.mysql>

执行SHOW ENGINES\\G指令可列表查看，MySQL 5.6可用的存储引擎有9种（除最后的FEDERATED以外，其他8种都支持），其中默认采用的存储引擎为InnoDB：

mysql> SHOW ENGINES\\G*************************** 1. row ***************************Engine: InnoDBSupport: DEFAULT       //此存储引擎为默认Comment: Supports transactions, row-level locking, and foreign keysTransactions: YESXA: YESSavepoints: YES*************************** 2. row ***************************Engine: MRG_MYISAMSupport: YESComment: Collection of identical MyISAM tablesTransactions: NOXA: NOSavepoints: NO*************************** 3. row ***************************Engine: MEMORYSupport: YESComment: Hash based, stored in memory, useful for temporary tablesTransactions: NOXA: NOSavepoints: NO*************************** 4. row ***************************Engine: BLACKHOLESupport: YESComment: /dev/null storage engine (anything you write to it disappears)Transactions: NOXA: NOSavepoints: NO*************************** 5. row ***************************Engine: MyISAMSupport: YESComment: MyISAM storage engineTransactions: NOXA: NOSavepoints: NO*************************** 6. row ***************************Engine: CSVSupport: YESComment: CSV storage engineTransactions: NOXA: NOSavepoints: NO*************************** 7. row ***************************Engine: ARCHIVESupport: YESComment: Archive storage engineTransactions: NOXA: NOSavepoints: NO*************************** 8. row ***************************Engine: PERFORMANCE_SCHEMASupport: YESComment: Performance SchemaTransactions: NOXA: NOSavepoints: NO*************************** 9. row ***************************Engine: FEDERATEDSupport: NOComment: Federated MySQL storage engineTransactions: NULLXA: NULLSavepoints: NULL9 rows in set (0.00 sec)

步骤二：查看默认存储类型

查看系统变量default_storage_engine 的值，确认默认采用的存储引擎是InnoDB：

mysql> SHOW VARIABLES LIKE \'default_storage_engine\';+------------------------+--------+| Variable_name          | Value  |+------------------------+--------+| default_storage_engine | InnoDB |+------------------------+--------+1 row in set (0.00 sec)

步骤三：修改默认存储引擎

在 mysql> 环境中，可以直接通过SET指令更改默认的存储引擎（只在本次连接会话过程中有效，退出重进即失效） 。比如临时修改为MyISAM，可执行下列操作：

mysql> SET default_storage_engine=MyISAM;       //改用MyISAM引擎Query OK, 0 rows affected (0.00 sec)mysql> SHOW VARIABLES LIKE \'default_storage_engine\';     //确认结果+------------------------+--------+| Variable_name          | Value  |+------------------------+--------+| default_storage_engine | MyISAM |+------------------------+--------+1 row in set (0.00 sec)

若希望直接修改MySQL服务程序所采用的默认存储引擎，应将相关设置写入配置文件/etc/my.cnf，并重启服务后生效。比如：

[root@zhangyx ~]# vim /etc/my.cnf
[mysqld]
default_storage_engine=MEMORY //改用MEMORY引擎
[root@zhangyx ~]# systemctl restart mysqld.service

重新登入 mysql> 确认修改结果：

[root@zhangyx ~]# mysql -u root -pEnter password:Welcome to the MySQL monitor.  Commands end with ; or \\g.Your MySQL connection id is 3Server version: 5.7.17 MySQL Community Server (GPL)Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type \'help;\' or \'\\h\' for help. Type \'\\c\' to clear the current input statement.mysql> SHOW VARIABLES LIKE \'default_storage_engine\';+------------------------+--------+| Variable_name          | Value  |+------------------------+--------+| default_storage_engine | MEMORY |+------------------------+--------+1 row in set (0.00 sec)mysql> exitBye

步骤四：设置数据库服务默认使用的存储引擎

为了避免后续实验障碍，测试完后记得恢复原状（可在脚本文件中注释掉：用#）——移除默认引擎设置，或者将其修改为InnoDB即可：
[root@zhangyx ~]# vim /etc/my.cnf
[mysqld]
default_storage_engine=InnoDB
[root@zhangyx ~]# systemctl restart mysqld.service

确认恢复结果（选项 -e 可调用指定的SQL操作后返回Shell命令行）：

[root@zhangyx ~]# mysql -u root -p -e \"SHOW VARIABLES LIKE \'default_storage_engine\';\"Enter password:+------------------------+--------+| Variable_name          | Value  |+------------------------+--------+| default_storage_engine | InnoDB |+------------------------+--------+