大数据学习之二 MYSQL进阶

数据运维 2023-04-19 Escape 手机阅读

文章目录 5 MySQL体系结构 5.1 MyISAM 5.2 Innodb 5.2.1 系统表空间和独立表空间要如何选择 5.2.2 Innodb存储引擎的特性 5.2.3 Innodb状态检查 5.2.4 适用场景 5.2.4 (拓展) 什么是锁 5.2.3.1 什么是锁？

    <img src="https://img.mryunwei.com/uploads/2023/04/20230419025517844.jpg"><p>文章目录</p>5 MySQL体系结构5.1 MyISAM5.2 Innodb5.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.3 CSV存储引擎5.3.1 CSV存储引擎的特点5.3.2 CSV存储引擎的适用场景5.4 Archive存储引擎5.4.1 文件系统存储特点5.4.2 Archive存储引擎的特点5.4.3 Archive存储引擎使用场景5.5 Memory存储引擎5.5.1 文件系统存储特点5.5.2 Memory的功能特点5.5.3 Memory中容易混淆的概念5.5.4 Memory的使用场景5.6 Federated存储引擎5.6.1 Federated的特点5.6.2 Federated如何使用5.6.3 Federated的适用场景6 如何选择正确的存储引擎<blockquote><p>接上一篇文章</p></blockquote>

5 MySQL体系结构

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

5.1 MyISAM

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

特性：

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 系统表空间和独立表空间要如何选择

比较：

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

5.2.4 适用场景

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

5.2.4 (拓展) 什么是锁

5.2.3.1 什么是锁？

5.2.3.4 锁的粒度：

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

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存储引擎适合作为数据交换的中间表在这里插入图片描述在这里插入图片描述

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存储引擎的特点

场景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的功能特点

功能特点：

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

5.5.4 Memory的使用场景

5.6 Federated存储引擎

5.6.1 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的适用场景

参考条件：