查看系統支持的存儲引擎
SHOW ENGINES;
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)
查看數據表所使用的存儲引擎
SHOW CREATE TABLE 表名 SHOW TABLE STATUS FROM 資料庫名 WHERE name = '表名';
mysql> SHOW CREATE TABLE user_info; +-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | user_info | CREATE TABLE `user_info` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `username` varchar(40) NOT NULL, `password` varchar(100) NOT NULL, `age` tinyint(3) unsigned DEFAULT NULL, `sex` tinyint(1) NOT NULL DEFAULT '0', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 | +-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> SHOW TABLE STATUS FROM user WHERE name = 'user_info'; +-----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +-----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+ | user_info | InnoDB | 10 | Dynamic | 3 | 5461 | 16384 | 0 | 0 | 0 | 9 | 2017-10-30 12:18:28 | 2017-10-30 22:10:26 | NULL | utf8_general_ci | NULL | | | +-----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+ 1 row in set (0.00 sec)
主要存儲引擎的對比
我看網上有很多博客文章對各存儲引擎都有瞭一個比較詳細的闡述,但是多數是文字,各個儲存引擎之間的對比不夠明顯。所以我上IMOOC網找瞭一個截圖方便對比。
存儲引擎的修改
MySQL初始默認引擎
MySQL初始默認引擎為InnoDB。
關於InnoDB的介紹從這篇文章中摘選瞭一些介紹【原文鏈接】:
InnoDB是一個健壯的事務型存儲引擎,這種存儲引擎已經被很多互聯網公司使用,為用戶操作非常大的數據存儲提供瞭一個強大的解決方案。我的電腦上安裝的MySQL5.6.13版,InnoDB就是作為默認的存儲引擎。InnoDB還引入瞭行級鎖定和外鍵約束,在以下場合下,使用InnoDB是最理想的選擇:
更新密集的表:InnoDB存儲引擎特別適合處理多重並發的更新請求。 事務:InnoDB存儲引擎是支持事務的標準MySQL存儲引擎。 自動災難恢復:與其它存儲引擎不同,InnoDB表能夠自動從災難中恢復。 外鍵約束:MySQL支持外鍵的存儲引擎隻有InnoDB。 支持自動增加列AUTO_INCREMENT屬性: 一般來說,如果需要事務支持,並且有較高的並發讀取頻率,InnoDB是不錯的選擇。
① 通過修改MySQL配置文件
default-storage-engine = engine_name
② 通過創建數據表命令
CREATE TABLE new_tb( … )ENGINE = engine_name;
mysql> CREATE TABLE new_tb( -> id INT PRIMARY KEY -> )ENGINE = MyISAM; Query OK, 0 rows affected (0.02 sec) mysql> SHOW CREATE TABLE new_tb; +--------+------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +--------+------------------------------------------------------------------------------------------------------------+ | new_tb | CREATE TABLE `new_tb` ( `id` int(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 | +--------+------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
③ 通過修改數據表命令
ALTER TABLE table_name ENGINE [=] engine_name
mysql> SHOW CREATE TABLE new_tb; +--------+------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +--------+------------------------------------------------------------------------------------------------------------+ | new_tb | CREATE TABLE `new_tb` ( `id` int(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 | +--------+------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> ALTER TABLE new_tb ENGINE = InnoDB; Query OK, 0 rows affected (0.07 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> SHOW CREATE TABLE new_tb; +--------+------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +--------+------------------------------------------------------------------------------------------------------------+ | new_tb | CREATE TABLE `new_tb` ( `id` int(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +--------+------------------------------------------------------------------------------------------------------------+ 1 row in set (0.01 sec)