MySQL存储引擎基础知识

原作者: [db:作者] 来自: [db:来源] 收藏邀请

在之前的文章中我们说过MySQL事务，现在大家都应该知道了MySQL事务了吧，还记得事务的ACID原则吗？不记得的童鞋可以回顾一下《MySQL之事务初识》，其实呀，更严谨一点的话，应该是MySQL InnoDB存储引擎，因为在MySQL中，只有InnoDB存储引擎才支持事务。看到此处，有些朋友可能有以下疑问:

存储引擎是什么？

MySQL中有哪些存储引擎？

每个存储引擎有哪些特点以及区别？

下面，我们带着这些疑问，依次往下看：

存储引擎是什么？

通俗一点来说，MySQL是用来保存数据的对不对？我们可以把存储引擎看作是一种存储文件的方式以及该方式附属的一整套工具，其中每个存储文件方式的特点就是该存储引擎的特点。

例如：Memory存储引擎将数据保存至内存中，其优点是：读写快，但是数据不是持久到磁盘中的，非常容易丢失等等。

MySQL中的存储引擎

在MySQL 5.7版本中，MySQL支持的存储引擎有:

InnoDB

MyISAM

Memory

CSV

Archive

Blackhole

Merge:

Federated

Example

下面只介绍常用的存储引擎，其他没介绍的存储引擎，有兴趣的童鞋，可以自己自行搜索。

InnoDB：支持事务操作(如 begin， commit，rollback命令)，支持行级锁，行级锁相对于表锁，其粒度更细，允许并发量更大，这里面细节还挺多的，下次，我们单独写一篇)支持外键引用完整性约束。InnoDB存储引擎也是MySQL 5.7版本中默认的存储引擎。其缺点是：存储空间会占用比较大。

MyISAM：该存储引擎存储占用的空间相对与InnoDB存储引擎来说会少很多，但其支持的为表锁，其并发性能会低很多，而且不支持事务，通常只应用于只读模式的应用。它是MySQL最原始的存储引擎。

Memory：该存储引擎最大的特点是，所有数据均保存在内存中，之前还有个名字叫做「Heap」。
应用场景: 主要存储一些需要快速访且非关键数据，为什么不是关键数据呢？就因为其所有数据保存在内存中，也可以理解为不安全。

CSV：首先先认识一下CSV，CSV文件其实就是用逗号分隔开的文本文件，常用于数据转换，该类型平时用的比较少，不支持索引。

Archive：存档文件，主要用于存储很少用到的引用文件，

Example：该存储引擎主要用于展示如何自行编写一个存储引擎，一般不会用作生产环境使用。

如何选择存储引擎

大家通过上面的比较应该已经看出来了，InnoDB存储引擎是支持事务，支持外键并支持行级锁的。对于需要在线事务处理的应用最合适不过了，我们在选择存储引擎时，如果没有特别的理由，我的建议是选择InnoDB作为存储引擎。

1、我们可以在创建table时，指定存储引擎，如果未指定，则使用默认的存储引擎。

create table t_base_user(
oid bigint(20) not null primary key auto_increment comment "",
created_at datetime null comment ''
)engine=innodb

2、(方法一) 显示该表的存储引擎

mysql> show table status like "t_base_user" \G;
*************************** 1. row ***************************
    Name: t_base_user
   Engine: InnoDB
   Version: 10
 Row_format: Dynamic
    Rows: 0
Avg_row_length: 0
 Data_length: 16384
Max_data_length: 0
Index_length: 0
  Data_free: 0
Auto_increment: 1
 Create_time: 2017-12-17 20:10:24
 Update_time: NULL
 Check_time: NULL
  Collation: utf8_unicode_ci
  Checksum: NULL
Create_options: 
   Comment: 
1 row in set (0.01 sec)

3、(方法二) 显示表的存储引擎信息

mysql> show create table t_base_user\G;
*************************** 1. row ***************************
  Table: t_base_user
Create Table: CREATE TABLE `t_base_user` (
`oid` bigint(20) NOT NULL AUTO_INCREMENT,
`created_at` datetime DEFAULT NULL,
PRIMARY KEY (`oid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
1 row in set (0.00 sec)

另外需要注意的是: 不建议修改表的存储引擎，在创建表时，就要需要考虑好，使用什么存储引擎。

今日命令

命令： show engines;

标准语法：show stroage engines;

其中stroage为可选项。

作用：显示当前MySQL版本支持的存储引擎。

例子(MySQL版本: 5.7.20)：

mysql> show storage engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine       | Support | Comment                            | Transactions | XA  | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MRG_MYISAM     | YES   | Collection of identical MyISAM tables             | NO      | NO  | NO     |
| CSV        | YES   | CSV storage engine                       | 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     |
| MEMORY       | YES   | Hash based, stored in memory, useful for temporary tables   | NO      | NO  | NO     |
| InnoDB       | DEFAULT | Supports transactions, row-level locking, and foreign keys   | YES     | YES | YES    |
| 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)

使用场景：在查看当前数据库版本支持的存储引擎，查看默认存储引擎时非常实用。

Engine：存储引擎名称。

Support：表示MySQL当前服务器版本是否支持该存储引擎，YES为支持 NO 为不支持。

Comment：为该存储引擎的特性，如Innodb

支持事务，行级锁等。

Transactions：是否支持事务，YES为支持，No为不支持。

XA和Savepoints：这两个属性，与事务相关，当Transactions为Yes时，这两个属性才有意义，否则均为NO。