MySQL中的存储引擎

旧城等待， 2022-11-26 04:59 149阅读 0赞

# MySQL中的存储引擎 #

查看MySQL支持的存储引擎：

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

查看MySQL当前默认的存储引擎：

## MyISAM ##

MyISAM存储引擎是MySQL5.5之前默认的存储引擎，数据库存储文件由.MYD和.MYI组成，.MYD用来存储表的数据，.MYI用来存储表的索引，采用非聚簇索引。

创建一个表，并插入数据：

mysql> create table testmyisam (id int PRIMARY key) ENGINE=myisam;
    mysql> insert into testmyisam VAlUES(1),(2),(3);

在MySQL的数据目录下会生成下面三个文件：

-rw-r-----. 1 mysql mysql 8556 Aug 10 02:41 testmyisam.frm
    -rw-r-----. 1 mysql mysql   21 Aug 10 02:42 testmyisam.MYD
    -rw-r-----. 1 mysql mysql 2048 Aug 10 02:42 testmyisam.MYI

其中testmyisam.frm用来存储表的结构，testmyisam.MYD用来存储表的数据，testmyisam.MYI用来存储表的索引。

适用场景（绝大多数场景都不适合）：

*  非事务型应用（数据仓库，报表，日志数据）
 *  只读类应用
 *  空间类应用（空间函数，坐标）

由于现在InnoDB越来越强大，MyISAM已经停止维护。

## InnoDB ##

InnoDB存储引擎是一种事务性存储引擎，完全支持事务的ACID特性，支持行级锁（并发程度更高）。

MyISAM与InnoDB的对比：

<table> 
 <thead> 
  <tr> 
   <th>对比项</th> 
   <th>MyISAM</th> 
   <th>InnoDB</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>主外键</td> 
   <td>不支持</td> 
   <td>支持</td> 
  </tr> 
  <tr> 
   <td>锁的级别</td> 
   <td>表锁，即使操作一条记录也会锁住整张表，不适合高并发操作</td> 
   <td>行锁，操作一行就锁住这一行，不会影响其他行，适合高并发操作</td> 
  </tr> 
  <tr> 
   <td>事务</td> 
   <td>不支持</td> 
   <td>支持</td> 
  </tr> 
  <tr> 
   <td>索引</td> 
   <td>非聚簇索引</td> 
   <td>聚簇索引</td> 
  </tr> 
  <tr> 
   <td>缓存</td> 
   <td>只缓存索引，不缓存真实数据</td> 
   <td>不仅缓存索引还要缓存真实数据，对内存要求较高，而且内存大小对性能有决定性的影响</td> 
  </tr> 
  <tr> 
   <td>表空间</td> 
   <td>小</td> 
   <td>大</td> 
  </tr> 
 </tbody> 
</table>

## CSV ##

CSV存储引擎是以csv格式进行数据存储，所有列都不能为null的，不支持索引（不适合大表，不适合在线处理），可以对数据文件直接编辑。

mysql> create table csv(id int not null, c1 VARCHAR(10) not null, c2 char(10) not null) engine=csv;
    mysql> insert into csv values(1,'aaa','bbb'),(2,'cccc','dddd');
    mysql> select * from csv;
    +----+------+------+
    | id | c1   | c2   |
    +----+------+------+
    |  1 | aaa  | bbb  |
    |  2 | cccc | dddd |
    +----+------+------+

直接修改数据文件：

# vi /usr/local/mysql/data/test/csv.CSV
    # cat /usr/local/mysql/data/test/csv.CSV 
    1,"ggg","ooo"
    2,"cccc","dddd"

先刷新一下表，然后再查数据发现已经变了：

mysql> flush tables;
    mysql> select * from csv;
    +----+------+------+
    | id | c1   | c2   |
    +----+------+------+
    |  1 | ggg  | ooo  |
    |  2 | cccc | dddd |
    +----+------+------+

尝试创建索引，结果报错：

mysql> create index idx_id on csv(id);
    ERROR 1069 (42000): Too many keys specified; max 0 keys allowed

## Archive ##

Archive存储引擎用zlib对表数据进行压缩，磁盘I/O更少，数据存储在ARZ为后缀的文件中。

Archive存储引擎只支持insert和select操作，只允许在自增ID列上加索引，适用于日志和数据采集应用。

mysql> create table archive(id int auto_increment not null, c1 VARCHAR(10), c2 char(10), key(id))engine = archive;
    mysql> insert into archive(c1,c2) value('aa','bb'),('cc','dd');
    mysql> select * from archive;
    +----+------+------+
    | id | c1   | c2   |
    +----+------+------+
    |  1 | aa   | bb   |
    |  2 | cc   | dd   |
    +----+------+------+

尝试为非自增ID列创建索引：

mysql> create index idx_c1 on archive(c1);
    ERROR 1069 (42000): Too many keys specified; max 1 keys allowed

尝试删除数据：

mysql> delete from archive where id=1;
    ERROR 1031 (HY000): Table storage engine for 'archive' doesn't have this option

尝试修改数据：

mysql> update archive set c1='aaa' where id=1;
    ERROR 1031 (HY000): Table storage engine for 'archive' doesn't have this option

## Memory ##

Memory存储引擎也叫HEAP存储引擎，所有的数据都保存在内存中（不会持久化），支持HASH索引和BTree索引，所有字段都是固定长度，例如varchar(10)=char(10)，不支持BLOB和TEXT等大字段。

Memory存储引擎使用表级锁，表的最大大小由`max_heap_table_size`参数决定。

从表的状态可以看出`Row_format`为`Fixed`，在MySQL的数据目录下只会看到`memory.frm`表结构文件，没有数据文件。

Memory存储引擎中的hash索引适用于等值查找，例如邮编和地区的对应表。

Memory存储引擎适用于保存数据分析中产生的中间表，缓存周期性聚合数据的结果表（有点类似redis），不适用于数据需要持久化的场景。

## Ferderated ##

Ferderated存储引擎提供了访问远程MySQL服务器上表的方法，本地不存储数据，数据全部放到远程服务器上，但是本地需要保存表结构和远程服务器的连接信息。

Ferderated存储引擎适用于偶尔的统计分析及手工查询（某些游戏行业）。

Ferderated在MySQL默认是禁止的，需要在启动时增加`federated`参数。

Ferderated的开启，在`/etc/my.cnf`中增加`federated`参数：

# cat /etc/my.cnf 
    [mysqld]
    sql_mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER"
    federated

查看是否启动成功：

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

下面用两个数据库实例来演示Ferderated存储引擎的使用。

使用3307实例来充当远程数据库：

mysql> create database test;
    mysql> use test;
    mysql> create table ferderated(id int auto_increment primary key, c1 varchar(10) not null default '', c2 char(10) not null default '')engine=innodb;
    mysql> insert into ferderated(c1,c2) values('aaa','bbb'),('ccc','ddd'),('eee','fff');
    mysql> select * from ferderated;
    +----+-----+-----+
    | id | c1  | c2  |
    +----+-----+-----+
    |  1 | aaa | bbb |
    |  2 | ccc | ddd |
    |  3 | eee | fff |
    +----+-----+-----+

使用3308实例来充当本地数据库：

mysql> create database test;
    mysql> use test
    mysql> create table ferderated(id int auto_increment primary key, c1 varchar(10) not null default '', c2 char(10) not null default '')engine=federated  connection='mysql://root:root@localhost:3307/test/ferderated';
    mysql> select * from ferderated;
    +----+-----+-----+
    | id | c1  | c2  |
    +----+-----+-----+
    |  1 | aaa | bbb |
    |  2 | ccc | ddd |
    |  3 | eee | fff |
    +----+-----+-----+

在3308实例中并没有插入数据，却能查到来自3307实例中的数据。

更多精彩内容关注本人公众号：架构师升级之路  
![在这里插入图片描述][20200627175030665.jpg]

[20200627175030665.jpg]: /images/20221124/679ee07196a242109c4530cd73bc36bc.png