大数据之MYSQL进阶的知识点有哪些

共计 6115 个字符，预计需要花费 16 分钟才能阅读完成。

自动写代码机器人，免费开通

这篇文章主要介绍了大数据之 MYSQL 进阶的知识点有哪些，具有一定借鉴价值，感兴趣的朋友可以参考下，希望大家阅读完这篇文章之后大有收获，下面让丸趣 TV 小编带着大家一起了解一下。

文章目录

5 MySQL 体系结构

5.6.1 Federated 的特点

5.6.2 Federated 如何使用

5.6.3 Federated 的适用场景

5.5.1 文件系统存储特点

5.5.2 Memory 的功能特点

5.5.3 Memory 中容易混淆的概念

5.5.4 Memory 的使用场景

5.4.1 文件系统存储特点

5.4.2 Archive 存储引擎的特点

5.4.3 Archive 存储引擎使用场景

5.3.1 CSV 存储引擎的特点

5.3.2 CSV 存储引擎的适用场景

5.2.1 系统表空间和独立表空间要如何选择

5.2.2 Innodb 存储引擎的特性

5.2.3 Innodb 状态检查

5.2.4 适用场景

5.2.4 (拓展) 什么是锁

5.2.3.1 什么是锁？

5.2.3.2 锁的类型：

5.2.3.3 写锁和读锁的兼容关系（对一行的兼容性情况）

5.2.3.4 锁的粒度：

5.2.3.5 阻塞和死锁

5.1 MyISAM

5.2 Innodb

5.3 CSV 存储引擎

5.4 Archive 存储引擎

5.5 Memory 存储引擎

5.6 Federated 存储引擎

6 如何选择正确的存储引擎

接上一篇文章

5 MySQL 体系结构

下面我们选一些比较常用的存储引擎进行简单的说明，mysql 所使用的存储引擎会对数据库的性能产生直接的影响，还希望各位能仔细的了解存储引擎的一些特点，完了之后才使用存储引擎。

5.1 MyISAM

MyISAM 在 MySQL5.5 之前版本是默认的存储引擎。由于这个原因，还有很多服务器在使用 MyISAM 这个存储引擎。同时，MyISAM 目前是很多系统表，临时表所使用的存储引擎，这里说的临时表不是我们通过 create table 创建出来的表，是指在排序、分组等操作中，当数量超过一定的大小之后，有查询优化器建立的临时表。
MyISAM 存储引擎是由 MYD 和 MYI 组成，MYD 是数据文件的扩展名，MYI 是索引文件的扩展名，这个存储引擎是将表存储在以这两个为扩展名的数据文件和索引文件中。

特性：

并发性与锁级别
MyISAM 使用的是表级锁，并不是行级锁，这也就意味着对表中的数据进行修改时，需要对整个表进行加锁，而在对表的读取时也对所有的表加共享锁，从这里我们可以看到，使用 MyISAM 做引擎的表读取和写入两种操作是互斥的。由此可以看到 MyISAM 对于读写的并发操作并不会很好。如果只对于只读取操作的话，就并发性而言，性能也还不错，因为共享锁不会阻塞共享锁。

表损坏修复
MyISAM 支持由于任意意外关闭而损坏的 MyISAM 表，进行检查和修复操作，但是这里说的修复并不是数据的恢复，因为 MyISAM 并不是一种事务性的存储引擎，所以它不能进行事务恢复所需要的相关日志，所以要注意 MyISAM 表的恢复可能会造成数据的丢失。
我们可以通过 check table tablename 对表进行检查，通过 repair table tablename 对表进行恢复。

MyISAM 表支持的索引类型
MyISAM 支持全文索引，并且在 mysql5.7 版本之前唯一原生就支持全文索引的官方存储引擎。

MyISAM 表支持数据压缩
如果 MyISAM 表示一张很大的只读表，也就是在表创建完导入数据后，就不会对表进行任何修改操作了，那我们就可以对表进行压缩了，以减少磁盘 I /O。我们可以使用 myisampack 命令来对表进行压缩。压缩是对表独立进行压缩的，因此读取一行数据的时候，不必对整个表进行解压。

限制：

版本 mysql5.0 时默认表大小为 4G

如存储大表则要修改 MAX_Rows 和 AVG_ROW_LENGTH

版本 mysql5.0 时默认支持位 256TB

适用场景：

非事务性应用

只读类应用（报表之类）

空间类应用

5.2 Innodb

Innodb 是 MySQL5.5 及之后版本默认的存储引擎，Innodb 是事务存储的存储引擎，也就是说支持事务的处理。
Innodb 有自己的表空间的概念，然后数据是存储在表空间之中的，是由 innodb_file_per_table 这个参数来决定的，如果这个参数为 ON，则会对每个 Innodb 表建立一个扩展名为 ibd 的系统文件，如果这个参数为 OFF 时，则会把数据存储到系统的共享表空间，也就是 ibdataX，X 代表的时一个数字，默认从 1 开始。
查看这个参数的命令为：
show variables like innodb_file_per_table
修改这个参数的命令为：
set global innodb_file_per_table=off;

5.2.1 系统表空间和独立表空间要如何选择

比较：

系统表空间独立表空间无法简单的收缩文件大小可以通过 optimize table 命令收缩系统文件会产生 IO 瓶颈可以同时向多个文件刷新数据

建议：

对 Innodb 使用独立表空间

把原来存在于系统表空间中的表转移到独立表空间中的方法。
步骤：

使用 mysqldump 导出所有数据库表数据

停止 MySQL 服务，修改参数，并删除 Innodb 相关文件

重启 MySQL 服务，重建 Innodb 系统表空间

重新导入数据

5.2.2 Innodb 存储引擎的特性

Innodb 是一种事务性存储引擎

完全支持事务的 ACID 特性（之前介绍过的原子性，一致性等）

Redo Log 和 Undo Log
Redo Log 实现了事务的持久性，有两部分组成，其中一个是内存中的工作日志持久缓冲区，是由 innodb_log_buffer_size 决定它的大小，另一个是重构日志文件，也就是我们在文件系统中看到的 ib_logflie 的相关文件。Undo Log 实现了事务的原子性，在事务失败时进行回滚操作。Redo Log 是顺序读写的，Undo Log 是随机读写的，如果可以的话可以将数据存储在固态硬盘中，以提高性能。

Innodb 支持行级锁
行级锁和表级锁是不一样的，行级锁的特点就是可以最大程度的支持并发，行级锁是由存储引擎层实现的。

5.2.3 Innodb 状态检查

可以使用以下命令对 Innodb 状态进行检查：
show engine innodb status

5.2.4 适用场景

Innodb 适合于大多数 OLTP 应用，因为在 mysql5.7 版本之后，Innodb 已经支持了全文索引和空间函数。

5.2.4 (拓展) 什么是锁

5.2.3.1 什么是锁？

锁最主要作用是管理共享资源的并发访问

锁用于实现事务的隔离性

5.2.3.2 锁的类型：

共享锁（也称读锁）

独占锁（也称写锁）

5.2.3.3 写锁和读锁的兼容关系（对一行的兼容性情况）

写锁读锁写锁不兼容不兼容读锁不兼容兼容

在实际情况中，可能与上表的结果会有所不同，主要是因为 Innodb 中的锁机制是很复杂的一样东西，还有很多锁的存在影响最终的结果。

5.2.3.4 锁的粒度：

表级锁

行级锁

5.2.3.5 阻塞和死锁

阻塞：阻塞是因为不同锁之间的兼容性的关系，在有些时刻一个事务中的锁需要等待另一事务的锁释放，它所占用的资源形成了阻塞。
死锁：死锁是指两个或两个以上的事务执行过程中，相互占用了对方等待的资源而产生的一种异常。从定义中可以看到，处在阻塞中的多个事务占用了被阻塞的事务等待的资源，而死锁是多个阻塞的事务互相占用了对方等待的资源。

5.3 CSV 存储引擎

CSV 存储引擎可以将 csv 文件作为 mysql 的表文件来处理，这种存储引擎的存储格式就是普通的 csv 文件，在 csv 存储引擎的数据存储方式非常的由特点，如果我们把表存储在 MyISAM 或者 Innodb 中，其数据文件我们是不能直接查看的，因为这两种文件的存储是以二进制的格式来存储的，而 CSV 存储引擎则不同，CSV 的数据是以文本的方式存储在文件中的，也就是我们可以通过查看文件的命令来查看，如 more，或者使用 vi 命令来查看编辑 csv 存储引擎中的表，只要符合 CSV 文件的格式和要求，我们就不用担心损坏数据。
当我们在 mysql 中建立了 CSV 存储引擎表时，我们应该可以看到 3 个文件系统中的文件。这 3 个都是以表名为文件名，但是会分别以 csv，csm，frm 为后缀，其中 csv 文件就是 CSV 存储引擎中的数据文件。csm 文件存储表的元数据和表状态和数据量。frm 文件存储表结构信息。

5.3.1 CSV 存储引擎的特点

最大的特点是以 CSV 格式进行数据存储
CSV 中的每一列都是以, 来分隔的，并且文本的内容是以双引号来引起来的，如下图所示：

所有列必须都是不能为 NULL 的
在建表的时候所有的列都必须是非空的，不能存储为 NULL 的值

不支持索引
不适合大表，不适合在线处理

可以对数据文件直接编辑
保存文本文件内容

5.3.2 CSV 存储引擎的适用场景

CSV 存储引擎适合作为数据交换的中间表

5.4 Archive 存储引擎

5.4.1 文件系统存储特点

Archive 存储引擎会缓存所有的写，并且利用 zlib 对插入的行进行压缩，因此 Archive 存储引擎相对于 MyISAM 存储引擎的表更加节省磁盘 I /O，对于同样数量级的数据，Archive 存储引擎相对于 MyISAM 和 Innodb 更加节省存储空间。一个几 T 的 Innodb 的表存储在 Archive 存储引擎当中，可能只需要几百兆的存储空间。
Archive 存储引擎的表的数据是以 ARZ 为后缀的一个文件，和其他引擎一样，也存在一个以 frm 为后缀的系统文件，用于存储表的结构信息。

5.4.2 Archive 存储引擎的特点

只支持 insert 和 select 操作

只允许在自增 ID 列上加索引

5.4.3 Archive 存储引擎使用场景

场景 1：日志和数据采集类数据
因为 Archive 不支持修改和删除，而我们 ORDB 一定会对数据进行修改的，但是对于一些仓库类型的应用，或者一些特殊的表，还是有用的，比如说，记录日志的表或者是数据采集类的表，因为它需要采集大量数据，所以比较适合使用 Archive 存储引擎。因为 Archive 存储引擎在所有引擎中来说，它的存储空间是最小的，但是还是要注意，即使在数据采集或日志的应用中，Archive 存储引擎是无法对这些数据进行更新的，所以在记录日志或者在数据采集类应用中对数据进行修改的话，可能也无法使用 Archive 存储引擎。

5.5 Memory 存储引擎

5.5.1 文件系统存储特点

Memory 存储引擎也称之为 HEAP 存储引擎，所以数据都保存在内存中，这就意味着这中数据的表是一次性的，一旦 MySQL 服务重启，所有 Memory 存储引擎的数据都会消失，但是表结构会保留下来，因为在 Memory 存储引擎下创建表，只会生成一个 frm 系统文件，该文件是用于保存表结构的。这就是为什么重启 MySQL 服务器数据会丢失，表结构不会的原因。
从它的文件存储特点我们可以知道，Memory 存储引擎的 I / O 效率会比 MyISAM 高很多，因为 MyISAM 只有索引会保存在内存中，而数据则由操作系统来缓存的，而 Memory 存储引擎所有数据和索引都保存在内存中，下面我们看一下 Memory 存储引擎的功能特点。

5.5.2 Memory 的功能特点

功能特点：

支持 HASH 索引（默认）和 BTree 索引
如果是 HASH 索引在做等值查询的时候会非常的快，如果是做范围查询的话就无法使用 HASH 索引了，所以在表创建的时候我们需要注意，如果表需要大量的等值查询就用 HASH 索引，范围查询就使用 BTree 索引。不同索引类型会对性能产生很大的影响。

所有字段都为固定长度 varchar(10) = char(10)
这就要求我们在定义表结构时，一定要符合要求最小的字段长度，否则浪费大量的内存。

不支持 BLOG 和 TEXT 等大字段

Memory 存储引擎使用表级锁

最大大小由 max_heap_table_size 参数决定
这个参数的默认值只有 16 兆，如果我们要在 Memory 存储引擎表中存储大量数据，就要修改这个参数，而这个参数修改是对已经存在的 Memory 存储引擎的表是不生效的，如果需要对存在表生效的话就需要通过对已经存在的表进行重建。

5.5.3 Memory 中容易混淆的概念

Memory 存储引擎表：
对所有的系统都可以使用，它并不是一种临时表。
临时表：
临时表分为两种，一种是查询优化器在优化查询时所使用的系统使用临时表，也就是内部临时表，系统使用临时表在超过限制（使用 BLOB 或 TEXT 大字段）时使用 MyISAM 临时表，未超限制使用 Memory 表。
另一种是通过命令 create temporary table 建立的临时表，建立的表可以使用任何存储引擎。
无论是哪种临时表，只对内部可见。

5.5.4 Memory 的使用场景

用于查找或者映射表，例如邮编和地区的对应表

用于保存数据分析中产生的中间表

用于缓存周期聚合数据的结果表

Memory 数据易丢失，所以要求数据可再生。

5.6 Federated 存储引擎

5.6.1 Federated 的特点

提供了访问远程 MySQL 服务器上表的方法
由于 Federated 存储引擎只是在本地建立了到远程服务器的一个连接，所以可以说我们所要访问的表全部还是放在远程服务器上，在本地并不存储数据。每次访问 Federated 存储引擎表的时候，查询都会被发送到远程服务器上运行，并从远程的 MySQL 服务器上获取相关的数据。

本地不存储数据，数据全部放到远程服务器上

本地需要保存表结构和远程服务器的连接信息
因此也会在系统中存在一个 frm 文件，用于存于远程信息以及如何连接远程表的相关信息。

5.6.2 Federated 如何使用

Federated 存储引擎可以实现 SQL Server 连接服务器的功能，但是由于本身的性能并不太好，通常可以通过复制等实现相同的目的，所以在当前的 MySQL 版本中，Federated 存储引擎默认是禁止的。如果需要使用 Federated 存储引擎，则需要在 /usr/local/mysql/my.cnf 中加入 federated=1，接着重启 MySQL 服务器，我们可以通过 show engine 来确认当前 MySQL 服务器是否支持 Federated 存储引擎。
而在 create table 语句中使用下面的连接字符串，
mysql://user_name[:password]@host_name[:port_num]/db_name/tbl_name
远程服务器绑定连接：
grant select,update,insert,delete on remote.remote_fet to fred_link@ 127.0.0.1 identified by 123456
就可以决定查询的远程服务器的相关信息以及相关的数据库表的一些信息。

5.6.3 Federated 的适用场景

偶尔的统计分析及手工查询
由于 Federated 的性能较慢，只适用于偶尔的统计分析及手工查询。

6 如何选择正确的存储引擎

参考条件：

事务

备份

崩溃恢复

存储引擎的特有特性
尽量避免混合使用存储引擎。

感谢你能够认真阅读完这篇文章，希望丸趣 TV 小编分享的“大数据之 MYSQL 进阶的知识点有哪些”这篇文章对大家有帮助，同时也希望大家多多支持丸趣 TV，关注丸趣 TV 行业资讯频道，更多相关知识等着你来学习!

向 AI 问一下细节