MySQL高级—谈谈MySQL的存储引擎

一、MySQL存储引用概述

​ 和大多数的数据库不同,MySQL中有一个存储引擎的概念,针对不同的存储需求可以选择最优的存储引擎。

​ 存储引擎就是存储数据、建立索引,更新阐述数据等等技术的实现方式。MySQL存储引擎是基于表的而不是基于数据库的,即在一个数据库中的不同表可以使用不同的存储引擎

​ Oracle、SqlServer等数据库只支持一种存储引擎。MySQL提供了插件式的引擎结构。所以MySQL有很多存储引擎,并且可以根据需求使用不同的存储引擎,甚至可以自定义编写存储引擎。

​ MySQL5.0支持的存储引擎包括:InnnoDB、MyISAM、MEMORY、BDB、MERGE、EXAMPLR、NDB CLUSTER、ARCHIVE、CSV等,其中InnoDB和BDB提供事务安全表,其他存储引擎是非事务安全表。

在mysql命令行,可以使用show engines;命令查看当前数据库支持的存储引擎,如下图所示:

从表中可以看到,我当前使用的数据库版本支持:MEMORY、InnoDB、PERFORMANCE SCHEMA、MyIsAM、MRG_MYISAM、BLACKHOLE、CSV、以及ARCHIVE,其中InnoDB是默认引擎,也即在创建数据表的时候如果没有指定存储引擎,就会默认使用InnoDB作为存储引擎。

当然我们还可以使用命令查看当前MySQL数据库默认的存储引擎:

mysql> show variables like '%storage_engine%';

二、各种存储引擎的特性

下面重点介绍几种常用的存储引擎,并比较各个存储引擎之间的区别,如下表所示

特点 InnoDB MyISAM MEMORY MERGE NDB
存储限制 64TB 256TB RAM大小
事务安全 支持
锁机制 行锁(适合高并发) 表锁 表锁 表锁 行锁
B树索引 支持 支持 支持 支持 支持
哈希索引 不支持 支持
全文索引 支持 支持
集群索引 支持 支持 支持
数据索引 支持 支持 支持 支持 支持
索引缓存 支持
空间使用
内存使用 中等
批量插入使用
支持外键 支持

对于我们来说,我们需要种地那关注的存储引擎特性是是否支持事物、锁机制以及是否支持外键。接下来我们详细的聊一下InnoDB、MyISAM,以及MEMORY这三种存储引擎的特性。

1、InnoDB存储引擎的特性

InnoDB是一种通用存储引擎,可兼顾高可靠性和高性能。 在MySQL 5.7中,InnoDB是默认的MySQL存储引擎。 除非你配置了其他存储引擎,否则发出不带ENGINE =子句的CREATE TABLE语句将创建一个InnoDB表。

InnoDB的优势
  • (1)支持事物:其DML操作遵循ACID模型,并具有具有提交,回滚和崩溃恢复功能的事务,以保护用户数据。
  • (2)支持行级锁,可以支持更大的并发:行级锁定可提高多用户并发性和性能。
  • (3)InnoDB表将数据存储在磁盘上,以基于主键优化查询。每个InnoDB表都有一个称为聚集索引的主键索引,该索引组织数据以最小化主键查找的I / O。
  • (4)支持外键,保证数据完整性:为了保持数据完整性,InnoDB支持FOREIGN KEY约束。使用外键检查插入,更新和删除,以确保它们不会导致不同表之间的不一致。
InnoDB和ACID模型

ACID模型是一组数据库设计原则,强调对业务数据和任务关键型应用程序非常重要的可靠性方面。MySQL包含了与ACID模型紧密结合的InnoDB存储引擎等组件,这样数据就不会被破坏,结果也不会被软件崩溃和硬件故障等异常情况所扭曲。当依赖于符合ACID的特性时,不需要重新发明一致性检查和崩溃恢复机制。如果有额外的软件保护措施、超可靠的硬件或能够容忍少量数据丢失或不一致的应用程序,则可以调整MySQL设置,以牺牲部分ACID可靠性,从而获得更高的性能或吞吐量。

ACID描述的是一个事务所应该具有的4大特性,即:

  • A: atomicity,原子性。一个事务就是一个不可在分隔的最小工作单元,事务中的操作要么全部执行,要么全部不执行。
  • C: consistency,一致性。数据库总是从一个一致性状态转移到另一个一致性性状态。
  • I:: isolation,隔离性。一个事务在做做的修改提交之前,对于其他事务是不可见的。
  • D: durability,持久性。一旦事务提交,则器所做的修改就会永久的保存到数据库中。

数据库中隔离性还有其对应的不同等级的隔离级别:

  • READ UNCOMMITED 读未提交:在此级别里,事务的修改,即使没有提交,对其他事务也都是可见的。事务可以读取未提交的数据,也就是会产生脏读,在实际应用中一般很少使用。
  • READ COMMITED 读以提交大多数数据库系统的默认隔离级别都是它,但是MySQL不是。它能够避免脏读问题,但是在一个事务里对同一条数据的多次查询可能会得到不同的结果,也就是会产生不可重复读问题。
  • REPEATABLE READ 可重复度MYSQL的默认隔离级别。它能够防止不可重复读问题,但是在一个事务里对一段数据的多次读取可能会导致不同的结果,也就是会有幻读的问题。
  • SERIALIZABLE 可串行化:该隔离级别是级别最高的,它通过锁来强制事务串行执行,避免了前面说的所有问题。在高并发下,可能导致大量的超时和锁争用问题。实际应用中也很少用到这个隔离级别,因为可重复度级别解决了所有问题。
InnoDB的外键约束 CASCADE、SET NULL、RESTRICT、NO ACTION

首先我们来了解一下几个外键约束属性的作用:

  • CASCADE:在父表上update/delete记录时,同步update/delete掉子表的匹配记录
  • SET NULL:在父表上update/delete记录时,将子表上匹配记录的列设为null (要注意子表的外键列不能为not null)
  • NO ACTION:如果子表中有匹配的记录,则不允许对父表对应候选键进行update/delete操作
  • RESTRICT:同no action, 都是立即检查外键约束

外键约束属性: RESTRICT | CASCADE | SET NULL | NO ACTION 外键的使用需要满足下列的条件:

  1. 两张表必须都是InnoDB表,并且它们没有临时表。
  2. 建立外键关系的对应列必须具有相似的InnoDB内部数据类型。
  3. 建立外键关系的对应列必须建立了索引。
  4. 假如显式的给出了CONSTRAINT symbol,那symbol在数据库中必须是唯一的。假如没有显式的给出,InnoDB会自动的创建。
InnoDB的文件存储方式

InnoDB存储表数据和索引有以下两种方式:

  • 使用共享表空间存储,这种方式下表结构存储在.frm文件中,数据和索引保存在innodb_data_home_dir和innodb_data_file_path定义的.ibdata文件中,可以是多个文件。
  • 使用多表空间存储,这种方式在表结构仍然存储在.frm文件中,但是每个表的数据和索引单独保存在.ibd文件中。

在Linux系统中,MySQL的这些文件被默认保存在/var/lib/mysql目录下了,如下图所示:

2、MyISAM存储引擎的特性

MyISAM是MySQL5.5版本之前的默认存储引擎,它不支持事务、不支持行级锁,只支持并发插入的表锁,主要用于高负载的select。

  • (1)不支持事务:因此不适合数据安全非常重要的场合下使用。
  • (2)不支持行级锁,最小的锁粒度是表锁,但是它就有较高的插入和查询速度,比较适合查询比较多的场合下使用。
  • (3)每个MyISAM类型的表都有一个AUTO_INCREMENT的内部列,当INSERT和UPDATE操作的时候该列被更新,同时AUTO_INCREMENT列将被刷新。所以说,MyISAM类型表的AUTO_INCREMENT列更新比InnoDB类型的AUTO_INCREMENT更快
MyISAM的文件存储方式

每个MyISAM表在磁盘上都会保存是3个文件,文件名和表名相同,单文件扩展名分别是:

  • .frm:用于存储表结构的定义
  • .MYD:用于存放数据
  • .MYI:用于存放索引,仅保存记录所在页的指针,索引的结构是B+树结构。

同时,MyISAM类型的表支持三种不同的存储结构:静态型、动态型、压缩型

(1)静态固定长度表

这种方式的优点在于存储速度非常快,容易发生缓存,而且表发生损坏后也容易修复。缺点是占空间。这也是默认的存储格式。

(2)动态可变长表

优点是节省空间,但是一旦出错恢复起来比较麻烦。

(3)压缩表

上面说到支持数据压缩,说明肯定也支持这个格式。在数据文件发生错误时候,可以使用check table工具来检查,而且还可以使用repair table工具来恢复。

3、MEMORY存储引擎的特性

MEMORY存储引擎相比前面的一些存储引擎,有点不一样,其使用存储在内从中的数据来创建表,而且所有的数据也都存储在内存中。

每个基于MEMORY存储引擎的表实际对应一个磁盘文件,该文件的文件名和表名是相同的,类型为.frm。该文件只存储表的结构,而其数据文件,都是存储在内存中,这样有利于对数据的快速处理,提高整个表的处理能力。

MEMORY存储引擎默认使用哈希(HASH)索引,其速度比使用BTree要快,如果希望使用B树型,则在创建的时候可以指定使用。

MEMORY存储引擎文件数据都存储在内存中,如果mysqld进程发生异常,重启或关闭机器这些数据都会消失。所以MEMORY存储引擎中的表的生命周期很短,一般只使用一次。因此MEMORY表可以用于存储临时表数据。

三、InnoDB、MyISAM和MEMORY三种存储引擎比较

  • InnoDB:支持事务处理,支持外键,支持崩溃修复能力和并发控制。如果需要对事务的完整性要求比较高(比如银行),要求实现并发控制(比如售票),那选择InnoDB有很大的优势。如果需要频繁的更新、删除操作的数据库,也可以选择InnoDB,因为支持事务的提交(commit)和回滚(rollback)。

  • MyISAM:插入数据快,空间和内存使用比较低。如果表主要是用于插入新记录和读出记录,那么选择MyISAM能实现处理高效率。如果应用的完整性、并发性要求比 较低,也可以使用。如果数据表主要用来插入和查询记录,则MyISAM引擎能提供较高的处理效率

  • MEMORY:所有的数据都在内存中,数据的处理速度快,但是安全性不高。如果需要很快的读写速度,对数据的安全性要求较低,可以选择MEMOEY。它对表的大小有要求,不能建立太大的表。所以,这类数据库只使用在相对较小的数据库表。如果只是临时存放数据,数据量不大,并且不需要较高的数据安全性,可以选择将数据保存在内存中的Memory引擎,MySQL中使用该引擎作为临时表,存放查询的中间结果

留言区

还能输入500个字符