重学MySQL系列(五):谈谈对MySQL的存储引擎的理解

2,980

原创作者,公众号【程序员读书】,欢迎关注公众号,转载文章请注明出处哦。

MySQL关于存储引擎的架构设计,相较于其他关系数据库管理系统,比如Oracle,SQL Server等数据库,这是MySQL最大的特色,也是MySQL最不一样的地方,因此,在这篇文章中我们来简单地了解一下。

存储引擎架构

MySQL的存储引擎架构被设计为可插拔式架构模式,也就是在不影响任何业务逻辑的情况下,我们可以随时替换当前的存储引擎,以达到以存储和性能方面的需求。

其实,我们可以通过下面MySQL服务架构图来了解一下这个过程:

从上面的图我们可以看到,MySQLServer层与存储引擎层通过MySQL服务器提供统一的API接口与存储引擎进行交互,Server不关心底层存储引擎的实现细节,而不同的存储引擎对数据的存储与处理、查询都有自己的实现。

查看存储引擎

MySQL支持多种存储引擎,如果我们想查看自己的MySQL服务器支持多少种存储引擎,可以使用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)

上面是我的服务器所支持的存储引擎,当然不同的MySQL版本支持的存储引擎是不一样的,上面显示的表格中,每个字段的含义:

Engine:存储引擎名称。

Support:是否支持该引擎以及该引擎是否为默认存储引擎,YES表示支持,NO表示不支持,DEFAULT表示为默认存储引擎。

Comment:存储引擎的简单介绍。

Transactions:表示该引擎是否支持事务。

XA:说明该存储引擎是否支持分布事务。

Savepoints:说明该存储引擎是否支持部分事务回滚。

存储引擎介绍

在上面列出的存储引擎中,我们最常用便是InnoDBMyISAM这个存储引擎了,所以我们重点来介绍一下这两个存储引擎,并进行比较学习。

InnoDB

InnoDB存储引擎是MySQL的默认存储引擎,支持行级锁定、MVCC多版本控制、非锁定读、事务、外健等特性,对事务的支持,让MySQL成为更加完善的数据库管理系统系统,行级锁定使InnoDB可以支持更大的并发数。

一般来说,如果没有什么其他特殊的需求,InnoDB存储引擎是使用MySQL创建数据表的首选。

MyISAM

MySQL5.1以前,MyISAM存储引擎是MySQL是默认存储引擎,MyISAM支持表级锁定,并不支持事务,比较适合大量的select和insert操作。

InnoDB与MyISAM存储引擎之间的比较

下面我们以下几个方面来比较一下这两个存储引擎的不同。

事务的支持

InnoDB支持ACID的事务处理,MyISAM并不支持事务,这里需要注意,如果我们在程序需要进行事务处理,所有的数据都要使用Innodb存储引擎,如果中间有MyISAM存储引擎的数据表,如果进行事务回滚,MyISAM并不会回滚到之前的状态,因此MyISAM不支持事务。

索引与主键处理

InnoDB存储引擎使用的是聚集索引,InnoDB主键的叶子节点是该行的数据,而其他索引则指向主键,而MyISAM存储引擎使用的是非聚集索引,主键与其他索引的叶子节点都存储了指向数据的指针。

另外一个是MyISAM数据表允许没有主键和其他索引,而InnoDB数据表如果没有主键的话,而会生成一个用户不可见6字节的主键。

外键

MyISAM不支持外键,而Innodb则支持建立数据表之间的外键关联。

存储文件的不同

Innodb存储文件有frmibd,而MyISAMfrmMYDMYI,Innodb存储文件中frm是数据表结构定义文件,ibd是数据文件,MyISAM中frm是数据表结构定义文件,MYD是数据的文件,MYI则是存储索引的文件。

select count(*)

使用MyISAM存储引擎的数据表会记录一个数据表的总行数,所以对使用MyISAM存储引擎的数据表进行select count(*),可以很快得到一个数据表的总行数,而对于InnoDB存储引擎的数据表,想要查询总行数需要进行全表扫描才能得到。

锁的级别

InnoDB支持行级锁,而MyISAM只支持表级锁,因此InnoDB更能支持高并发。

设置默认存储引擎

上面我们说过,InnoDBMySQL的默认存储引擎,当然我们也可以设置其他存储引擎为默认存储引擎,有两种方式进行设置,如:

服务器启动时设置

在服务器启动时,使用--default-storage-engine命令可以设置默认存储引擎:

mysqld --default-storage-engine=MyISAM

上面指定的是正常数据表的存储引擎,临时表的默认存储也可以使用--default-tmp-storage-engine参数来设置:

mysqld --default-tmp-storage-engine=MyISAM

通过set命令设置

除了在启动MySQL服务器时设置外,在进行登录到服务器中后,也可通过SET命令来设置默认存储引擎:

指定普通数据表的默认存储引擎:

SET default_storage_engine=MyISAM;

指定临时数据表默认存储引擎:

SET default_tmp_storage_engine=MyISAM

为数据表设置存储引擎

为数据表指定存储引擎有两种情况,一种是在使用CREATE TABLE语句创建数据表的时,另外一种则是对于已经创建好的数据表。

建表时

当我们在创建数据表的时候,如果没有指定存储引擎,则会使用上面设置的默认存储引擎,比如我们下面的建表语句:

CREATE TABLE users(
    id INT NOT NULL,
    name VARCHAR(20) NOT NULL,
    gender TINYINT NOT NULL,
    email VARCARH(50) NOT NULL,
    PRIMARY KEY(id)
);

如果我们在创建数据表时,不想用默认的存储引擎,则可以在CREATE TABLE语句后指定ENGINE参数来设置存储引擎,如:

CREATE TABLE users(
    id INT NOT NULL,
    name VARCHAR(20) NOT NULL,
    gender TINYINT NOT NULL,
    email VARCARH(50) NOT NULL,
    PRIMARY KEY(id)
)ENGINE=MyISAM;

修改数据表存储引擎

对于已经创建好的数据表,则可以使用ALTER TABLE语句来修改存储引擎,比如我们将上面的users数据表重新改为Innodb存储引擎,可以这样做:

mysql> ALTER TABLE users engine=InnoDB;

小结

MySQL中,对数据的存储,查询、处理、修改都由于存储引擎来执行,MySQL的可插拔式存储引擎设计,使用我们的可以根据自己的业务需求,选择适合自己的存储引擎。


如果你觉得文章不错,欢迎扫码关注,你的关注就是我写作的最大动力