【进阶】存储引擎

骑猪看日落 2022-07-17 03:10 223阅读 0赞

# 前言 #

Github：[https://github.com/yihonglei/road-of-arch/tree/master/thinking-in-mysql][https_github.com_yihonglei_road-of-arch_tree_master_thinking-in-mysql]

# 一 概述 #

插件式存储引擎是MySql数据库最重要的特征之一，用户可以根据实际应用需要选择如何存储和索引数据，

是否使用事务等等。MySql支持多种存储引擎，以适应不同数据库领域的应用需要，用户可以选择不同的存储引擎，

提高效率，用户也可以按照自己的方式定制和使用自己的存储引擎。

# 二 存储引擎分类 #

MySql中注意包括以下存储引擎：

MyISAM, InnoDB,BDB,  MEMORY, MERGE, EXAMPLE, NDB Cluster, ACCHIVE, CSV, BLACKHOLE, FEDERATED等，

其中常用存储引擎为MyISAM, InnoDB, MEMORY, MERGE,NDB Cluster。

只有InnoDB和BDB提供事务安全表，其他的存储引擎均为非事务安全表。

创建表时如果不指定存储引擎，就使用数据库默认的存储引擎，MySql5.5之前默认提供存储引擎为MyISAM,

5.5之后为InnoDB存储引擎。如果需要修改存储引擎，可以修改MySql配置文件中'default-storage-engine=INNODB'  
或default-table-type。

**2.1  查看当前默认的存储引擎**

show variables like '%storage\_engine%';

![Center][]

**2.2 查看当前数据库支持哪些存储引擎**

SHOW ENGINES \\G

![Center 1][]

**2.3 修改表的存储引擎**

ALTER TABLE table\_name ENGINE = engine\_name;

**2.4 创建表时可以指定存储引擎**

CREATE TABLE `t_user_main` (
      `f_userId` int(10) NOT NULL AUTO_INCREMENT COMMENT '用户id,作为主键',
      `f_userName` varchar(5) DEFAULT NULL COMMENT '用户名',
      `f_age` int(3) DEFAULT NULL COMMENT '年龄',
      PRIMARY KEY (`f_userId`)
    ) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

通过ENGINE显示指定存储引擎。

# 三 常用存储引擎分析 #

MySql存储引擎各种各样，有些几乎很少用到，主要分析常用的，工作中经常需要面对的常用存储引擎，如图：

![Center 2][]

**3.1 MyISAM**

从上面表格中观察可以知道，该存储引擎不支持事务，也不支持外键，优点是访问速度快，如果对数据完整性没有

很高的要求或者以查询(select)，插入(insert)为主，很少去更新(update)和删除(delete)可以用该引擎创建表。

每个使用MyISAM引擎的表在磁盘上存储3个文件：

1）XXX.frm (存储表定义)

2）XXX.MYD (存储数据)

3）XXX.MYI (存储索引)

创建一个t\_user\_main表，使用MyISAM存储引擎，看看数据库存储文件eg:

![Center 3][]

值得注意的是，数据文件和索引文件可以放置在不同的目录，平分IO，以便获取更快的速度。

数据文件和索引放置路径可以通过DATA DIRECTORY，INDEX DIRECTORY分别指定，

使用绝对路径，同时具有访问权限。如果使用MySql图形化工具，工具中设计表时会带有路径指定选项，

如果数据文件和索引文件不分离，不需指定。如，使用Navicat设计表时：

![Center 4][]

MyISAM表可能出现损坏情况，损坏后的表将不能访问，访问会提示修复或错误结果。

通过CHECK语句检查表的状态，REPAIR TABLE修复一个MyISAM表。

如果表损坏，可能导致书库重启，需要尽快确定原因，想出解决办法。

对于更新频繁，表容易出现碎片，占用巨大内存，一般需要定期通过OPTIMIZE TABLE或

myisamchk-r命令改善性能，出现故障时恢复比较困难。

**3.2 InnoDB**

InnoDB存储引擎从图表中可以看到支持提交，回滚和崩溃恢复能力的事务安全，以及外键功能。

同时也可以看到，比MyISAM效率低，会占用更多的磁盘空间存储数据和索引。注意有一些特点：

**自动增长列**

InnoDB的自动增长列可以手动插入，如果插入的是null或0，则实际插入的是自动增长后的值。

创建一个InnoDB引擎表：

CREATE TABLE `t_user_person_info` (
      `f_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
      `f_userId` int(11) NOT NULL COMMENT '用户id',
      `f_salary` decimal(14,2) NOT NULL DEFAULT '0.00' COMMENT '工资',
      PRIMARY KEY (`f_id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

插入数据测试自增涨列插入null或0的情况:

![Center 5][]

通过ALTER TABLE table\_name AUTO\_INCREMENT = n修改自增长默认起始值。

![Center 6][]

可以使用last\_insert\_id()查询当前线程最后插入记录使用的值。如果插入时使用多条记录查询时，将返回第一条记录插入的值。

![Center 7][]

注意：自增长列必须为主键或唯一索引列，而且只能是int类型，每个表只能有一个自增长列。

**外键约束**

MySql中支持外键约束的引擎只有InnoDB。在创建外键的时候要求父表必须有对应的索引，

子表在创建外键的时候也会自动创建对应的索引。

父表

CREATE TABLE `t_student` (
      `f_userId` int(11) NOT NULL AUTO_INCREMENT,
      `f_userName` varchar(50) DEFAULT NULL,
      `f_age` int(3) DEFAULT NULL,
      PRIMARY KEY (`f_userId`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

子表

CREATE TABLE `t_student_class` (
      `f_id` int(11) NOT NULL AUTO_INCREMENT,
      `f_userId` int(11) DEFAULT NULL,
      `f_calssCode` int(11) DEFAULT NULL,
      `f_className` varchar(50) DEFAULT NULL,
      PRIMARY KEY (`f_id`),
      KEY `sc_fk` (`f_userId`),
      CONSTRAINT `sc_fk` FOREIGN KEY (`f_userId`) REFERENCES `t_student` (`f_userId`) ON DELETE CASCADE ON UPDATE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

父表操作对子表相应的操作有四种情况：

1）RESTRICT 和 NO ACTION含义相同，是指限制在子表有关记录的情况下父表不能更新。

2）CASCADE 父表在更新删除时，更新删除子表对应记录。

3）SET NULL 父表在更新删除时，子表对应的字段设置成空。

分别插入两条数据：

INSERT INTO t_student VALUES(1,'one',24),(2,'two',25);
    INSERT INTO t_student_class VALUES(1,1,1001,'nnnn'),(2,2,2002,'wwww');

测试一下上面创建的CASCADE是否是级联删除：

![Center 8][]

从测试结果可以看出级联删除的效果，可以更改子表t\_student\_class中外键与主表主键的操作关系，做相关测试。

**存储方式**

InnoDB有两种存储方式：

1）使用共享表空间存储，这种方式表结构会保存在.frm文件中，数据和索引报错在innodb\_data\_home\_dir和innodb\_data\_file\_path

定义的表空间中，可以是多个文件。

2）使用多表空间存储，使用这种方式存储表结构文件为.frm，数据和索引文件存储在.ibd文件中。

命名规则是表明加上.frm,.ibd格式，例如，t\_user\_person\_info.frm，t\_user\_person\_info.ibd

**3.3 MEMORY**

MEMORY(记忆)引擎使用存储与内存中的内容创建表。

在磁盘上只有一个.frm文件，检索非常快，默认使用HASH索引，但是，一旦数据库服务关闭，数据将丢失。

创建一个MEMORY引擎表:

CREATE TABLE `t_user_asset` (
      `f_id` int(11) NOT NULL AUTO_INCREMENT,
      `f_salary` decimal(14,2) DEFAULT NULL,
      PRIMARY KEY (`f_id`)
    ) ENGINE=MEMORY DEFAULT CHARSET=utf8;

磁盘文件格式.frm，只有一个文件:

![Center 9][]

在该表中插入一条数据:

INSERT INTO t_user_asset VALUES (1,20000);

查看数据，可以看到表中刚才插入的数据,然后我们把mysql服务器停掉，然后再重启mysql服务器，

再次查看t\_user\_asset表时，发现数据已经丢失了。所以使用MEMORY引擎时，对数据要谨慎。

既然数据都存储在内存中，服务器就需要足够的内存来维持一段时间MEMORY引擎表，当然了，

如果我们不需要数据，可以通过delete或truncate清空数据，有必要可以通过drop table把表删掉。

一般情况下，memory引擎表用于变化不是非常频繁的代码表，或者作为统计的中间结果表，因为其高效性，

可以提高统计的效率。切记对memory引擎表数据做修改，一定时刻提醒自己，找到合理的办法去解决mysql

服务重启后修改的数据何去何从。

**3.4 MERGE**

MERGE引擎是一组MyISAM表的组合，这些表结构必须完全相同，MERGE本身并没有数据，对于METGE进行

的增，删，改，查操作实质上操作的是其内部的MyISAM表。

对于MERGE类型的插入，是通过INSERT\_METHOD字句定义插入的表，可以拥有3个不同的值，使用FIRST或LAST

值使得插入操作相应作用在第一个表或最后一个表，不定义这个子句或定义为NO,表示不能对MERGE表进行插入操作。

可以通过drop对MERGE引擎表进行删除操作，这个删除只是删除了MERGE自身的定义，并不能影响其内部真实

存在的表。

MERGE磁盘上会有两文件，一个为.frm文件，包含存储定义，一个为.MRG文件包含组合表的信息，

主要包括组成表，插入设定的依据，可以通过修改.MRG文件修改MERGE的定义，需要通过flush tables刷新才能生效。

先创建2个表结构相同的MyISAM引擎表：

table1:

CREATE TABLE `t_test_merge1001` (
      `f_id` int(11) NOT NULL AUTO_INCREMENT,
      `f_userName` varchar(50) DEFAULT NULL,
      `f_age` int(3) DEFAULT NULL,
      PRIMARY KEY (`f_id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

table2:

CREATE TABLE `t_test_merge1002` (
      `f_id` int(11) NOT NULL AUTO_INCREMENT,
      `f_userName` varchar(50) DEFAULT NULL,
      `f_age` int(3) DEFAULT NULL,
      PRIMARY KEY (`f_id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

分别插入两条数据：

INSERT INTO t_test_merge1001 VALUES(1,'one',24),(2,'two',25);
    INSERT INTO t_test_merge1002 VALUES(1,'one',24),(2,'two',25);

创建MERGE引擎表，通过INSERT\_METHOD指定数据插入时插入第一个表:

CREATE TABLE `t_merge_all` (
      `f_id` int(11) NOT NULL AUTO_INCREMENT,
      `f_userName` varchar(50) DEFAULT NULL,
      `f_age` int(3) DEFAULT NULL,
      INDEX (`f_id`)
    ) ENGINE=MERGE UNION=(t_test_merge1001,t_test_merge1002) INSERT_METHOD=FIRST;

增，删，改，插入数据：

插入TEST:

![Center 10][]

同时删除TEST:

![Center 11][]

同时更新TEST：

![Center 12][]

集合查询TEST:

![Center 13][]

注意：如果t\_merge\_all表建立主键时，更新，删除与插入时操作表依据一样。

[https_github.com_yihonglei_road-of-arch_tree_master_thinking-in-mysql]: https://github.com/yihonglei/road-of-arch/tree/master/thinking-in-mysql
[Center]: /images/20220717/d61cb11cf82a467191f3a4a358a3c338.png
[Center 1]: /images/20220717/d3706eba6a0c498b8366e0434013e449.png
[Center 2]: /images/20220717/a70ee103d5c54c71aeb1c28dac92a32f.png
[Center 3]: /images/20220717/09201feb4595439893e2288bb16e53e0.png
[Center 4]: /images/20220717/1dc5db3a34be49cfbc497780c47cf601.png
[Center 5]: /images/20220717/96ad49204ad74fdd92404ff081f940f1.png
[Center 6]: /images/20220717/775e31355f2b460292a417a393e8c3fb.png
[Center 7]: /images/20220717/2d647364a8f74592ab92f68e8a069cd6.png
[Center 8]: /images/20220717/133159b1688e4efcb40d08ae000d2604.png
[Center 9]: /images/20220717/c03ab350f52a49d1977c3a5cd6834184.png
[Center 10]: /images/20220717/27df115022de45fd91f9cca9a0c14609.png
[Center 11]: /images/20220717/8777b95b968c4496a902c8ba19ce169a.png
[Center 12]: /images/20220717/5ec116c8aa5642d9838544c27d6fca26.png
[Center 13]: /images/20220717/be19688697194940addadf35a4092bf5.png